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
