Re: [sqlite] GUID in place of AUTO_INCREMENT

[Mike Ashmore]

On Jan 25, 2006, at 10:00 AM, Kurt Welgehausen wrote:


... but Rails doesn't seem to support composite keys.


I don't know much about RoR. How does RoR keep you from creating
a multi-column key on an SQLite table?

Regards


In point of fact, it doesn't prevent me from *creating* a multi- 
column key, just from using it effectively. It's just that Rails'  
implementation of ActiveRecord doesn't understand multi-column PKs.  
As you might suspect, ActiveRecord handles the relationships between  
records, and that's a lot easier when PKs and FKs are single-column  
and follow a reasonable naming convention.


At any rate, Dr. Hipp's suggestion of the AFTER INSERT trigger was  
exactly what I needed. Thanks!


-Mike Ashmore

Re: [sqlite] GUID in place of AUTO_INCREMENT

[drh]

Mike Ashmore <[EMAIL PROTECTED]> wrote:
> So, I've got what I think is a good reason to modify the behavior of  
> primary keys in my (Ruby on Rails-based) application.
> 
> What I need is a modifier, say 'GUID', that can be applied in place  
> of (mutually exclusive with) the AUTOINCREMENT modifier [1]. On a  
> field with the GUID modifier, if an INSERT statement proffers a NULL  
> value for that field, an RFC4122-compliant GUID generator should be  
> invoked to create a globally unique value for that field.
> 

You can do this with an insert trigger.  First create 
a user-defined function to generate your guid. Suppose 
that function is named new_guid().  Further suppose that
the column that wants the guid is called "pk".  Then write
the trigger like this:

  CREATE TRIGGER AFTER INSERT ON table1 WHEN new.pk IS NULL
  BEGIN
UPDATE table1 SET pk=new_guid() WHERE rowid=new.rowid;
  END;

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] GUID in place of AUTO_INCREMENT

[Kurt Welgehausen]

> ... but Rails doesn't seem to support composite keys.

I don't know much about RoR. How does RoR keep you from creating
a multi-column key on an SQLite table?

Regards

[sqlite] GUID in place of AUTO_INCREMENT

[Mike Ashmore]

So, I've got what I think is a good reason to modify the behavior of  
primary keys in my (Ruby on Rails-based) application.


What I need is a modifier, say 'GUID', that can be applied in place  
of (mutually exclusive with) the AUTOINCREMENT modifier [1]. On a  
field with the GUID modifier, if an INSERT statement proffers a NULL  
value for that field, an RFC4122-compliant GUID generator should be  
invoked to create a globally unique value for that field.


If a patch to do something like this already exists, could someone  
point me in its direction? And if not, would anybody care to work  
with me on creating one? And in any case, am I about to do something  
that was tried before, found to be a bad idea, and maybe replaced  
with a better idea? GUIDs tend to smell like bad design to me, but I  
can't seem to think of any alternatives.


Thanks,
-Mike Ashmore

[1] Why, you ask? Well, I've got a view, and that view is an  
aggregate of multiple tables. Those tables each exist in a separate  
database file. When I INSERT into that view, an INSTEAD OF trigger is  
called that invokes a user-defined function that asks a separate  
process to open the appropriate database file and execute the insert  
there [2]. But with a  primary key field called 'id' that's declared  
AUTOINCREMENT, you can see that we quickly get duplicates in the 'id'  
field as we insert into multiple files. According to my database  
design class in college, the primary key could be the combined key of  
'id' and 'origin'[3], but Rails doesn't seem to support composite  
keys. I think modifying SQLite would be more generally useful than  
changing Rails anyway.


[2] Why? As a mechanism for implementing record-level access control  
using filesystem permissions. Long story. I might do a white paper on  
it if anyone's interested.


[3] I attach all the database files I'm compositing, then create a  
composite view of a table that exists in each of those files. The  
composite view consists of a bunch of "SELECT *, [original database]  
as origin FROM [original database].table" statements UNIONed together.