Yuzem wrote:
> I thought sqlite didn't handle foreign keys correctly, I am using
> triggers to automatically delete tags when a movie gets deleted.
There's a new thing, 'PRAGMA foreign_keys=1', that causes SQLite to
enforce them. Check out the docs on that.
> Anyway, to use integers in the "tags" table is very complicated because
> I will have to assign the corresponding number to each tag that I insert
> and I have to insert lots of keywords for every movie.
Well, technically you can have SQLite do that assignment for you. But
yes, you'll have to deal with that mapping somewhere. You can abstract
that away at a very low level in your infrastructure though.
It's not so terrible: again it's something like (in one high level
language)
set tagId [db one {SELECT tagId FROM tagNames WHERE tagName=$tagName}]
db eval {INSERT INTO tags VALUES($movieId, $tagId)}
That one SELECT lookup will probably save you tons of string comparisons
later on. Depends of course on the nature of your data set and query
stats.
> Does sqlite has any function to convert a text string into an unique
> number?
If you run (again correcting for your preferred language)
db eval {INSERT INTO tagNames(tagName) VALUES($tagName)}
then SQLite will generate the tagId column value for you if the column
is declared INTEGER PRIMARY KEY. Again, you can get it back out at some
point later using SELECT as above, or if it's needed immediately (which
it probably is) you can use last_insert_rowid.
So, again in Tcl, your whole insertion path will probably look something
like:
db eval {INSERT OR IGNORE INTO tagNames(tagName) VALUES($tagName)}
if {[db changes]} {
set tagId [db last_insert_rowid]
} else {
set tagId [db one {SELECT tagId FROM tagNames WHERE tagName=$tagName}]
}
assert {$tagId!=""}
db eval {INSERT OR IGNORE INTO tags VALUES($movieId, $tagId)}
That could possibly be optimized further. But you could give it a go as
a first pass and see how far it takes you. It's not so complicated, and
in 7 lines (plus 1 sanity-check line) we've abstracted the ID<->text
mapping.
Eric
--
Eric A. Smith
Carperpetuation (kar' pur pet u a shun), n.:
The act, when vacuuming, of running over a string at least a
dozen times, reaching over and picking it up, examining it, then
putting it back down to give the vacuum one more chance.
-- Rich Hall, "Sniglets"
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
