You have:
CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id));
CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag));
You can solve your problem, using pure SQL. No need to resort to the
application layer. Just execute the SQL transaction below. It takes care of
everything, including normalizing, assigning integer IDs to unique tags,
filtering our duplicates and auto indexes. I haven't tested on your tables
specifically, but I do similar all the time. Any errors should be simple name
errors.
begin immediate;
create temp table "Movies Old" as select * from Movies;
create temp table "Tags Old" from Tags;
drop table Movies;
drop table Tags;
create table Movies
( Movie_ID integer primary key not null
, Title text collate no case not null
);
create table Tags
( Tag_ID integer primary key not null
, Name text not null unique collate nocase
);
create table Movie_Tags
( ID integer primary key not null
, Movie_ID integer not null
references Movies (Movie_ID) on delete cascade
, Tag_ID integer not null
references Tags (Tag_ID) on delete cascade
, unique (Movie_ID, Tag_ID)
);
insert into Movies (Movie_ID, Title) select Movie_ID, Title from "Movies Old";
insert or ignore into Tags (Name) select distinct Tag from "Tags Old";
insert into Movie_Tags (Movie_ID, Tag_ID)
select distinct Movie_ID, (select Tag_ID from Tags where Name = "Tags Old".Tag)
from "Tags Old";
drop table "Movies Old";
drop table "Tags Old";
commit or rollback;
Note that the last line "commit or rollback" is not an actual SQLite command.
You will need to manually decide at that point whether to issue a commit or
rollback. If there are any errors in previous lines, use rollback.
Unfortunately, SQLite does not automatically rollback all commands in a
transaction if one of the commands fails (specifically create and drop
commands).
Then you should be able to count tags very fast by:
select count(*) from Tags;
In order to make foreign keys work, you need to have SQLite version 3.6.19 or
later, and use this pragma when you open a connection to SQLite (ie before any
commands that require use of foreign keys):
pragma foreign_keys = yes;
A normalized database using integer keys is fast, small and elegant. Viewing
integers by users is ugly. So for viewing the Movie-Tag combinations (and data
entry), you might want to create a view to make it user friendly. It depends on
your user interface. Reply here if you need that. Just something like this:
create view "Movie Tags View"
as select
Movie_Tags.ID as ID
, Movies.Title as "Movie"
, Tags.Name as "Tag"
from Movie_Tags
join Movies on Movie_Tags.Movie_ID = Movies.Movie_ID
join Tags on Movie_Tags.Tag_ID = Tags.Tag_ID
;
Hope this helps,
Tom
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
