> IMDb Pro offers additional information and services, all the information I > am grabbing is already available to the public and I am not publishing this > information, there are a lot of programs that do this.
OK, good to know, thanks. >>> The script is written in bash and I can give you the code but I think it >>> would be very complicated to adapt it to your schema. >> >> Complicated? Why? In what format is the grabbed data (ie what tables, >> columns, rows)? It usually just takes an insert select to move data from >> one table's schema to another. >> > Well, you would have to replace the insert commands, lets say that I have > table movies with columns id,title,year,plot > In the script I have to set the columns as variables: > id=tt12345 > title="movie title" > year=2011 > plot="this is the plot" > > Ant then I call the command insert: > insert movies > > The command insert knows the columns for each table, it goes column by > column, if the column is declared as a variable it is inserted so you would > have to replace all those commands with something like: > echo "INSERT INTO movies" etc... Oh, I see, so the "complication" is that you would have to change the columns used in your bash script. That shouldn't be a big problem, but I'll leave the bash script to you. If for some reason rewriting the insert command in the bash script is insurmountable, you can just create a view in SQL to match the expectations of the bash script. That view can funnel each insert to the underlying SQL schema table columns using an "instead of insert" trigger. Let me know if you need more info on this. > Let me know if you are interested in the script, the script is included in > https://launchpad.net/figuritas/+download the application , but I can post > and explain the relevant functions if you want. No, that's fine, I just couldn't understand why changing the insert statement in the bash script is a problem. >> What you want, I think, however, is how many people there are who are >> directors (counting each person just once, even if they direct multiple >> movies), given by this: >> >> select count(*) from "Capacity People Statistics" >> where Capacity_ID = (select ID from Capacity where Name = 'director') >> ; >> > Ok then, yes I wanted to count directors counting each person just once, I > think that that code will not count much faster. I expect it will be *much* faster than count(distinct), almost instant. >> Hopefully now that I've given you the query you actually needed, it now >> makes sense ;-) >> > Yes it does, but then I don't need the Count column since I want to speed up > counting distinct directors but counting each person just once. Well, you will actually need the Count column if you plan on facilitating deletion of directors, since you will need to track when the count of a person's director roles drops to zero (so they're no longer counted). In that case, you will also need a slightly modified select: select count(*) from "Capacity People Statistics" where Capacity_ID = (select ID from Capacity where Name = 'director') and Count > 0 ; or you can change the delete trigger to remove statistics that drop to a zero count: begin immediate ; drop trigger if exists "Movie People delete" ; create trigger "Movie People delete" on "Movie People" after delete begin insert or replace into "Capacity People Statistics" (Capacity_ID, People_ID, Count) select old.Capacity_ID , old.People_ID , ( select coalesce(Count, 0) - 1 from "Capacity People Statistics" where Capacity_ID = old.Capacity_ID and People_ID = old.People_ID ) ; delete from "Capacity People Statistics" where Count = 0 ; end ; commit ; >> No, I don't think you need more tables to achieve the above. >> > Yes you can do it with the above but I want to make it very fast, with those > two additional tables I think it will be much faster. Great, I'm glad we finally got there :-) 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
