Dan, >> I'm using DM on a legacy sqlite3 database where the primary keys are >> defined as: "id INTEGER PRIMARY KEY" >> >> I'm trying to match this in the property definition for id, but I can't >> get it quite right. Below are the property definitions I've tried, the >> generated SQL, and why they don't work the way I need. >> >> 1) property :id, Serial # id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >> 2) property :id, Integer, :key => true # id INTEGER NOT NULL, PRIMARY >> KEY(id) >> >> Method 1 would be great if there was a way to turn off AUTOINCREMENT. >> That keyword causes a slightly different and less efficient to be used >> than the default key selection algorithm. > > The "Serial" type is for auto-increment primary keys. More loosely it > can be thought of as "this field value is generated by the system > during record creation". >
With SQLite3, the db any field that is defined as INTEGER PRIMARY KEY is auto-populated during record creation. The presence or absence of the AUTOINCREMENT keyword only serves to determine which algorithm is used to generate the next value. According to the SQLite3 website, it's better to not use the AUTOINCREMENT keyword unless necessary for a particular design, as it causes more work for the database. I would submit that the default Serial type for SQLite3 should be INTEGER NOT NULL PRIMARY KEY. Here's a link for more info: http://www.sqlite.org/autoinc.html >> Method 2 causes :id to be nil unless I specify it, which is not >> acceptable for a number of reasons. > > Really? Shouldn't the NOT NULL prevent nil values from being > inserted? If you're using dm-validations it should not allow you to > create a record where a key is nil either. > The legacy database has keys defined as INTEGER PRIMARY KEY. Auto-validations for nil values in dm-validations don't trigger on :key, only on :nullable => false. I'm not explicitly testing primary keys because the db ought to be generating them. >> Is there a way (apart from hacking DM) to generate either of the >> following SQL with a property definition? >> >> id INTEGER PRIMARY KEY >> - or - >> id INTEGER NOT NULL PRIMARY KEY > > The second statement above appears to be the same as what's generated > for: property :id, Integer, :key => true > Not quite. property :id, Integer, :key => true generates this: id INTEGER NOT NULL, PRIMARY KEY(id) Note that in this case, the PRIMARY KEY is defined on the table, not on the column. Whether or not this makes any real difference in DM or the db, I don't know. When defining the primary key using property :id, Integer, :key => true, the :id property is nil after doing a create (SQLite3 allows null values for primary keys unless otherwise specified). > Also, if this is a legacy database, does it matter what properties it > uses when doing auto-migrate, provided you have something equivalent? > I'm using auto_migrate to generate a clean db with a schema as close to the original for testing purposes. We have a different mechanism for doing production upgrades. Earle --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "DataMapper" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/datamapper?hl=en -~----------~----~----~----~------~----~------~--~---
