Foreign Constraint Triggers Across Attached Databases

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Foreign Constraint Triggers Across Attached Databases

scott_mcdonald
I have been trying to implement the paradigm of using Triggers to emulate referential integrity, for example cascading updates and
deletes between two database tables. This works when the two database tables are in “main” but when I try to create the triggers
between database tables in attached database tables, the create doesn’t work. Tried several iterations and couldn’t come up with the
proper SQL syntax to do this. Is there a way to do this, add referential integrity triggers with database tables in attached databases?

Even better, if/when is SQLite going to support built-in referential integrity using foreign key constraints in the SQL when creating
the tables? Can it support referential integrity with attached database tables?

Example use case: “main” containing an “AccountTable”, and a daily transactional table “YYYY-MM-DD.db3” that will be attached to “main”
that contains a TransactionTable that has a “foreign key relation” to the “AccountTable” by having an account primary key as a foreign
key in the transaction table.


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Foreign Constraint Triggers Across Attached Databases

D. Richard Hipp

On Jan 23, 2008, at 10:07 PM, [hidden email] wrote:

> I have been trying to implement the paradigm of using Triggers to  
> emulate referential integrity, for example cascading updates and
> deletes between two database tables. This works when the two  
> database tables are in “main” but when I try to create the triggers
> between database tables in attached database tables, the create  
> doesn’t work. Tried several iterations and couldn’t come up with the
> proper SQL syntax to do this. Is there a way to do this, add  
> referential integrity triggers with database tables in attached  
> databases?
>
> Even better, if/when is SQLite going to support built-in  
> referential integrity using foreign key constraints in the SQL when  
> creating
> the tables? Can it support referential integrity with attached  
> database tables?

Triggers between two separate databases are not allowed since if
you DETACH one of the databases, the triggers obviously will no
longer work.  If two separate databases are so inseparably bound
that they need triggers between them, why not just make them a
single database?

The same goes for foreign key constraints.  There are *severe*
implementation difficulties trying to get this to work across
separate database.  If you have a foreign key in a separate
database, that really argues that the two databases ought to
be one.


>
> Example use case: “main” containing an “AccountTable”, and a daily  
> transactional table “YYYY-MM-DD.db3” that will be attached to “main”
> that contains a TransactionTable that has a “foreign key relation”  
> to the “AccountTable” by having an account primary key as a foreign
> key in the transaction table.
>
>
> ----------------------------------------------------------------------
> -------
> To unsubscribe, send email to [hidden email]
> ----------------------------------------------------------------------
> -------
>
>

D. Richard Hipp
[hidden email]




-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: Foreign Constraint Triggers Across Attached Databases

Samuel Neff

I've run into two situations recently where I would have preferred to write
triggers across databases.  Both related to audit tracking of data.

The first situation is that for every table, I have a corresponding history
table that records the history of every record.  So let's say I have

CREATE TABLE Users (UserID, UserName);

then I also have

CREATE TABLE Users_History (UserHistoryID, TransactionID, ChangeType,
UserID, UserName)

and to track transactions I have

CREATE TABLE Transactions(TransactionID, TrasnactionTS, UserID)

and to track the single active transaction I have

CREATE TABLE ActiveTransaction(TransactionID)

which is always blank except when in the middle of a transaction (which
always starts with inserting a record to that table, and then deleting it
right before commit).


So I have triggers on the Users table that whenever a record is inserted,
updated, or deleted, the corresponding new values for insert/update and old
values for delete are inserted into the history table.  The triggers look
like this:

CREATE TRIGGER HI_Users
AFTER INSERT ON Users
FOR EACH ROW BEGIN

        SELECT RAISE(ABORT, 'Can not update database when no transaction is
active.  Create a new transaction in the Transactions table and create an
associated record in the ActiveTransaction table.')
        WHERE (SELECT COUNT(*) FROM ActiveTransaction) = 0;
       
        INSERT INTO
                Users_History (
                        TransactionID,
                        ChangeType,
                        UserID, UserName
                )
        SELECT
                        (SELECT MIN(TransactionID) FROM ActiveTransaction),
                        'I',
                        NEW.UserID, NEW.UserName
                ;
END;


Due to the restriction that triggers cannot span databases, I have my main
data tables, history tables, and the ActiveTransaction table all in the same
database.  I'd really rather the history tables be in a separate database
because they can grow quite large and when I ask a customer to e-mail me
their database, I'd like them to be able to easily e-mail the main data only
without the extra history info.

Also, it would be much cleaner if the ActiveTransaction table was in TEMP
instead of in MAIN so each connection clearly has it's own table (except
where now they share the same table definition, just the data is never
shared due to convention of being populated only within a transaction).

I hope these examples are helpful.  I would like to see the ability to
create a trigger that spans database some day and would expect that the
trigger could be defined and simply would error out if at runtime the
required database was not present.

Thanks,

Sam

-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [hidden email]
 
-----Original Message-----
From: D. Richard Hipp [mailto:[hidden email]]
Sent: Thursday, January 24, 2008 6:56 AM
To: [hidden email]
Subject: Re: [sqlite] Foreign Constraint Triggers Across Attached Databases


Triggers between two separate databases are not allowed since if
you DETACH one of the databases, the triggers obviously will no
longer work.  If two separate databases are so inseparably bound
that they need triggers between them, why not just make them a
single database?

The same goes for foreign key constraints.  There are *severe*
implementation difficulties trying to get this to work across
separate database.  If you have a foreign key in a separate
database, that really argues that the two databases ought to
be one.



-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Foreign Constraint Triggers Across Attached Databases

Nicolas Williams
On Thu, Jan 24, 2008 at 10:59:17AM -0500, Samuel R. Neff wrote:
> Due to the restriction that triggers cannot span databases, I have my main
> data tables, history tables, and the ActiveTransaction table all in the same
> database.  I'd really rather the history tables be in a separate database
> because they can grow quite large and when I ask a customer to e-mail me
> their database, I'd like them to be able to easily e-mail the main data only
> without the extra history info.

You could always copy the users_history table records to an attached DB
and "delete from users_history;" after every, or every N, transactions
on your main DB.  This ways your users_history table size is bounded in
the main DB and you still get to keep all your history in a separate DB.

Triggers across DBs could only work if there was a way to create a
persistent DB ATTACHment -- a way to tell SQLite to ATTACH dependencies
of such triggers whenever you open the DB.  I imagine that wouldn't be
too hard, but I'm not writing nor maintaining the code.

Nico
--

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: Foreign Constraint Triggers Across Attached Databases

Fowler, Jeff
In reply to this post by Samuel Neff
Hi Sam,

Brainstorming your suggestion a bit, rather than allowing triggers to
function across databases (which is understandably not practical given
the architecture), perhaps there could be a way to define a "partition"
within a database (similar to creating a folder on a drive). Borrowing
from Oracle, maybe this could be called a "Schema", and a "create
schema" command could be used to create it. Queries referencing a table
in a different schema must preface the table name with the schema name -
e.g., "SELECT * FROM Sam.users".

Just some rainy day ideas..

- Jeff

-----Original Message-----
From: Samuel R. Neff [mailto:[hidden email]]
Sent: Thursday, January 24, 2008 10:59 AM
To: [hidden email]
Subject: RE: [sqlite] Foreign Constraint Triggers Across Attached
Databases


I've run into two situations recently where I would have preferred to
write triggers across databases.  Both related to audit tracking of
data.

The first situation is that for every table, I have a corresponding
history table that records the history of every record.  So let's say I
have

CREATE TABLE Users (UserID, UserName);

then I also have

CREATE TABLE Users_History (UserHistoryID, TransactionID, ChangeType,
UserID, UserName)

and to track transactions I have

CREATE TABLE Transactions(TransactionID, TrasnactionTS, UserID)

and to track the single active transaction I have

CREATE TABLE ActiveTransaction(TransactionID)

which is always blank except when in the middle of a transaction (which
always starts with inserting a record to that table, and then deleting
it right before commit).


So I have triggers on the Users table that whenever a record is
inserted, updated, or deleted, the corresponding new values for
insert/update and old values for delete are inserted into the history
table.  The triggers look like this:

CREATE TRIGGER HI_Users
AFTER INSERT ON Users
FOR EACH ROW BEGIN

        SELECT RAISE(ABORT, 'Can not update database when no transaction
is active.  Create a new transaction in the Transactions table and
create an associated record in the ActiveTransaction table.')
        WHERE (SELECT COUNT(*) FROM ActiveTransaction) = 0;
       
        INSERT INTO
                Users_History (
                        TransactionID,
                        ChangeType,
                        UserID, UserName
                )
        SELECT
                        (SELECT MIN(TransactionID) FROM
ActiveTransaction),
                        'I',
                        NEW.UserID, NEW.UserName
                ;
END;


Due to the restriction that triggers cannot span databases, I have my
main data tables, history tables, and the ActiveTransaction table all in
the same database.  I'd really rather the history tables be in a
separate database because they can grow quite large and when I ask a
customer to e-mail me their database, I'd like them to be able to easily
e-mail the main data only without the extra history info.

Also, it would be much cleaner if the ActiveTransaction table was in
TEMP instead of in MAIN so each connection clearly has it's own table
(except where now they share the same table definition, just the data is
never shared due to convention of being populated only within a
transaction).

I hope these examples are helpful.  I would like to see the ability to
create a trigger that spans database some day and would expect that the
trigger could be defined and simply would error out if at runtime the
required database was not present.

Thanks,

Sam

-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building
Flex based products. Position is in the Washington D.C. metro area. If
interested contact [hidden email]
 
-----Original Message-----
From: D. Richard Hipp [mailto:[hidden email]]
Sent: Thursday, January 24, 2008 6:56 AM
To: [hidden email]
Subject: Re: [sqlite] Foreign Constraint Triggers Across Attached
Databases


Triggers between two separate databases are not allowed since if
you DETACH one of the databases, the triggers obviously will no
longer work.  If two separate databases are so inseparably bound
that they need triggers between them, why not just make them a
single database?

The same goes for foreign key constraints.  There are *severe*
implementation difficulties trying to get this to work across
separate database.  If you have a foreign key in a separate
database, that really argues that the two databases ought to
be one.



------------------------------------------------------------------------
-----
To unsubscribe, send email to [hidden email]
------------------------------------------------------------------------
-----


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: Foreign Constraint Triggers Across Attached Databases

Griggs, Donald-2
In reply to this post by Nicolas Williams
 

From Nico:
You could always copy the users_history table records to an attached DB
and "delete from users_history;" after every, or every N, transactions
on your main DB.  This ways your users_history table size is bounded in
the main DB and you still get to keep all your history in a separate DB.
...
=============

You know your app better than I, but I would think that if you used
Nico's suggestion, but kept the most-recent X history records in the
main database, that might be very useful to you in debugging a user's
problem.




This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking.
This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------