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.
- [sqlite] GUID in place of AUTO_INCREMENT Mike Ashmore
-
