How do I create an auto updateing sqlite table?

Question

I am using a python script to create and maintain an sqlite database for anime shows I watch to help me keep better track of them and the episodes I have and need to get.

I use the script to create a table for each series eg Bleach, Black Lagoon... and in each of these tables the following information is stored:

Series table:

Season_Num         # Unique season number
I_Have_Season      # yes or no to say I have a directory for that season
Season_Episodes    # Amount of episodes according to the TVDB that are in that season
Episodes_I_Have    # The numer of episodes I have for that season

the same table is created for every series I have and a row for each season in that series that seems to work fine.

Now what I'm trying to do is create a summary table which takes the information from the tables for each series and creates just 1 table with all the information I need it has the following information:

Summary table:

Series          # Unique Series name
Alt_Name        # Alternate name (The series name in english)
Special_Eps     # The amount of Special episodes (Season 0 in the series table)
Special_Eps_Me  # The number of Special Episodes I have
Tot_Ses         # The total count of the Seasons (excluding season 0)
Tot_Ses_Me      # The total count of Seasons that have yes in I_Have_Season column
Tot_Episodes    # Total Episodes excluding season 0 episodes
Tot_Eps_Me      # Total Episodes I have excluding season 0 episodes

I think what I want to do can be done using triggers but I am unsure how to implement them so that the summary table will automatically update if for example a new season is added to a series table or the values of a series table are changed.

UPDATE:

Fabian's idea for a view instead of a table after some more thaught and research sounds like it could be what i want but if it's possible i would like to keep each series seperate in its own table for updateing instead of haveing just 1 table with every series and every season mixed in.

UPDATE 2:

I have gone ahead and put in the triggers for INSERT, UPDATE and DELETE i added them in the initial create loop of my script using variable's for the table names and the summary table appears to be updateing fine (after fixing how some of the values in it were calculated). I will test it further and hopefully it will keep working. Now i just need to get my script to add and delete tables for new series and for a series i delete.

Answer 1

This could be achieved using triggers. But this kind of thing is usually better done declaratively, using a view:

For example,

create table series (
  series_name,
  alt_name,
  special_eps,
  special_eps_me,
  primary key(series_name)
);

create table seasons (
  series_name,
  season_num,         
  i_have_season, 
  episodes, 
  episodes_i_have,
  primary key (series_name,season_num),
  foreign key (series_name) references series (series_name),
  check (i_have_season in ('F','T'))
);

create view everything_with_counts as 
select series_name, 
       alt_name, 
       special_eps, 
       special_eps_me, 
       (select count(*) from seasons  where seasons.series_name = series.series_name) as tot_ses,
       (select count(*) from seasons  where seasons.series_name = series.series_name and i_have_season = 'T') as tot_ses_me,
       (select sum(episodes) from seasons where seasons.series_name = series.series_name) as tot_epsiodes,
       (select sum(episodes_i_have) from seasons where seasons.series_name = series.series_name and i_have_season = 'T') as tot_epsiodes_me
from series;

EDIT

Since you want to stick to the trigger design: Assuming you have your series tables like this:

create table series_a (
  season_num,         
  i_have_season, 
  episodes, 
  episodes_i_have
);

create table series_b (
  season_num,         
  i_have_season, 
  episodes, 
  episodes_i_have
);

and so on, and your summary table like this:

create table summary (
  series_name,
  alt_name,
  special_eps,
  special_eps_me,
  tot_ses,
  tot_ses_me,
  tot_episodes,
  tot_episodes_me,
  primary key(series_name));

You have to create three triggers (insert, update, delete) for each series table, e.g.:

  create trigger series_a_ins after insert on series_a 
  begin
    update summary set tot_ses = (select count(*) from series_a ),
                       tot_ses_me = (select count(*) from series_a  where  i_have_season = 'T'),
                       tot_episodes = (select sum(episodes) from series_a ),
                       tot_episodes_me = (select sum(episodes_i_have) from series_a where  i_have_season = 'T') 
    where series_name = 'a';
  end;

/* create trigger series_a_upd after update on series_a ... */
/* create trigger series_a_del after delete on series_a ... */

With this version, you have to add your summary entry manually in the summary table, and the counters get updated automatically afterwards when you modify your series_... tables.

You could use also INSERT OR REPLACE (see documentation) to create summary entries on demand.