I just began using SQLite. I have an iPhone and want to write a Windows application to edit the database. After finding the database to be SQLite and researching SQLite, I made a good start at the application. I really like SQLite! It's the first alternative I've seen to Microsoft Jet, which is a single file with no install. I plan to use SQLite for all my new applications.
The question I have is in regard to the iPhone note database. There's a [Note] table and [note_bodies] table. The designer should have made just one table since [Note] and [note_bodies] are one-to-one, but didn't. The [note_bodies] table has an integer [note_id] field, but the [Note] table has no key or ID field to tie the records together. A delete trigger exists on the [Note] table containing: CREATE TRIGGER delete_note_bodies AFTER DELETE ON Note BEGIN DELETE FROM note_bodies WHERE note_id = OLD.ROWID; END; COMMIT; This would seem to inply there's a hidden or implied column named [ROWID] in the [Note] table. Create statements are: CREATE TABLE _SqliteDatabaseProperties (key TEXT, value TEXT, UNIQUE(key)); CREATE TABLE Note (creation_date INTEGER, title TEXT, summary TEXT); CREATE TABLE note_bodies (note_id INTEGER, data, UNIQUE(note_id)); The [note_bodies] table obviously contains the content, but it has XML tags around each paragraph. Also, the title of the note from [Note] exists at the front of the [data] field with no XML tags, which is then followed by the content which does have XML tags. To associate the tables I would scan and parse [note_bodies] to find the beginning text within the [data] field. Unfortunately, more than one [Note] / [note_bodies] pair can be made containing identical info, yet the proper [note_bodies] record is always associated with whatever [Note] record when using the iPhone application. I created a few notes with identical info on the iPhone. What a screwy way to make a database. All I can think of is that there's a hidden or implied field named [ROWID] in the [Note] table. Please help. Thanks _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
