I watched the google? video presentation DRH gave (very nice, thank you). I was interested to hear him suggest using SQLite Tables as persistent storage for Objects/UDTs. I want to do this for an application that has a very well defined UDT that is used throughout the application. It would make sense to simply store the UDT array locally using SQLite.
The part I am not clear about is how to integrate this with the main database on the server. This database is a larger superset of the local database and requires all the elements of the UDT be broken out into a seperate table, one per column, so that a query can be made against the individual elements. The return would be the rows of the UDT table NOT all the individual elements. Lets say the main server database contains things like, name, address, contact info, personal metrics (like height and weight) etc etc for a total of say 60 fields. The client will issue a query for all the Rows of people over a certain height, under a certain weight and living in one of 13 specified zip codes. Easy, but instead of returning all 66 fields I want to return the UDT for the records. This implies the second UDT table that has two columns?, a foreign key and a UDT Blob for each Row of the primary table. To take it a step further, the UDT would be compressed to minimize xfer time and server memory overhead. I do not have a lot of experience with SQL but it seems a query would contain a JOIN to include the UDT Table? Does this design have any unforseen problems thus far? The next part of the problem is that new data Rows are updated with say 10 additional fields. For example the address is processed to find the Zip+5 code (an existing field created by the initial create string) Calculations are performed to determine other field values. After each one of these steps the corresponding UDT table must be updated. This would require pulling every fragment of data out of the Row in question from the primary table, populating the UDT and then upddating the corresponding Row in the secondary UDT table. This is not a problem so much as an implied step with this method of using a Table for the UDT storage. (comments?) Finally there is the choice of the Primary Key. In my (inexperienced) thinking, I would like to use something other than the RecID (INTEGER PRIMARY KEY) as this is more database dependant than data dependant. There is a field in the data that is unique, it is a combination of numbers and letters and dashes, but it is missing in one in a thousand records unfortunatly. I could create a fake one, but then I run the risk of a duplicate later. So I was thinking of using Date (Julian format) to generate a base number (all the records have some date attached to them) and then use something else (???) added to that. That would provide a convenient INTEGER Primary key I could Hash the record and use that. This would provide a unique 32byte binary hash. I would choose the fields to make the hash carefully, but if two records have empty fields for all that I chose, I no longer have a unique Key. I could use a random number generator and simply check to see if the new random number has been assigned before... but I might as well use the RecID. Now I suspect you may be thinking "just use the RecID" because it is guaranteed unique. I could. But the first batch of data will be chronologically after the final batch of data. So all my RecID's will be counter intuitive. (not good at 2am when some disaster has to be handled) Also, if I transfere the database to another system down the road, that database will be creating its own RecID unless I force it to use the existing RecIDs. Its just messy. I would rather create a Data driven Primary key, that I also use in the application, that is independant for the RecID used by the Database. I would appreciate any thoughts on this from this group. I am sure none of these issues are new to you guys! --------------------------------- Never miss a thing. Make Yahoo your homepage. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users