CREATE TABLE person (personid INTEGER PRIMARY KEY AUTOINCREMENT, firstName, lastname, address1, address2, zip, phone, email, spouseid, gender)

-----------------------------------

CREATE TABLE sqlite_sequence(name, seq)

-----------------------------------

CREATE TABLE eventpdf (event, flyer)

-----------------------------------

CREATE TABLE eventurl (event, url)

-----------------------------------

CREATE TABLE venue (venueid INTEGER PRIMARY KEY AUTOINCREMENT, latlong, name, address1, address2, zip, shortname, url)

-----------------------------------

CREATE TABLE club (clubid INTEGER PRIMARY KEY AUTOINCREMENT, name, url, gif)

-----------------------------------

CREATE TABLE clubofficer (club, position, person)

-----------------------------------

CREATE TABLE weekday(day, dayname)

-----------------------------------

CREATE TABLE number(n INTEGER)

-----------------------------------

CREATE TABLE rafclubs(clubid)

-----------------------------------

CREATE TABLE specialdances(eventid)

-----------------------------------

CREATE TABLE monthname(number, monthname)

-----------------------------------

CREATE TABLE eventclub(eventid INT, clubid)

-----------------------------------

CREATE TABLE eventcaller(eventid INT, callerid)

-----------------------------------

CREATE TABLE eventcuer(eventid INT, cuerid)

-----------------------------------

CREATE TABLE event (eventid INTEGER PRIMARY KEY AUTOINCREMENT, date, venue, time, flags, eventname, comment)

-----------------------------------

CREATE TABLE scripts(pagepart, position, script, comment)

-----------------------------------

CREATE TABLE pagesets(pageset, position, pagepart, comment)

-----------------------------------

CREATE TABLE substitutions(pageset, substitutions, comment)

-----------------------------------

CREATE TABLE styles(selector, property, value, comment)

-----------------------------------

CREATE TABLE clubnews(date, clubid, news, information, reporters)

-----------------------------------

CREATE TABLE articlecontent (articleid, position, type, content)

-----------------------------------

CREATE TABLE views(tablename, position, column)

-----------------------------------

CREATE TABLE vieworder(tablename, tableorder)

-----------------------------------

CREATE TABLE classifieds(startdate, sortdate, enddate, name)

-----------------------------------

CREATE TABLE promenaderstaff(rank, position, person)

-----------------------------------

CREATE TABLE singlepage(location, type, heading, content)

-----------------------------------

CREATE TABLE federationstaff(rank, section, position, person)

-----------------------------------

CREATE TABLE callerscuers(type, personid)

-----------------------------------

CREATE TABLE content(page, part, content, comment)

-----------------------------------

CREATE TABLE minutes(minuteid INTEGER PRIMARY KEY AUTOINCREMENT, date, content)

-----------------------------------

CREATE TABLE faq(rank, question, answer)

-----------------------------------

CREATE TABLE clubnewsproof(date, clubid, news, information, reporters)

-----------------------------------

CREATE TABLE article( articleid INT, date, rank, type, heading, subheading, comment)

-----------------------------------

CREATE TABLE articleproof(articleid, date, rank, type, heading, subheading, comment)

-----------------------------------

CREATE VIEW people as select * from person order by lastname, firstname

-----------------------------------

CREATE VIEW clubs as
    select eventid, group_concat(club.name, ', ') as name
    from eventclub
    left outer join club on eventclub.clubid = club.clubid
    group by eventclub.eventid

-----------------------------------

CREATE VIEW orphanclubs as
  select clubs.eventid, name
  from clubs
  left outer join event on clubs.eventid = event.eventid
  where event.eventid isnull

-----------------------------------

CREATE VIEW orphaneventpdf as
  select eventpdf.event, flyer
  from eventpdf
  left outer join event on eventpdf.event = event.eventid
  where event.eventid isnull

-----------------------------------

CREATE VIEW orphancallers as
  select callers.eventid, name
  from callers
  left outer join event on callers.eventid = event.eventid
  where event.eventid isnull

-----------------------------------

CREATE VIEW orphancuers as
  select cuers.eventid, name
  from cuers
  left outer join event on cuers.eventid = event.eventid
  where event.eventid isnull

-----------------------------------

CREATE VIEW callers as
select eventid, group_concat(callers.name, ', ') as name from
    (select eventid, people.lastname as name
      from eventcaller
      left outer join people on eventcaller.callerid = people.personid
      order by lastname) as callers
    group by eventid

-----------------------------------

CREATE VIEW cuers as
select eventid, group_concat(cuers.name, ', ') as name from
    (select eventid, people.lastname as name
      from eventcuer
      left outer join people on eventcuer.cuerid = people.personid
      order by lastname) as cuers
    group by eventid

-----------------------------------

CREATE VIEW automation as
select * from substitutions
  join pagesets using (pageset)
  join scripts using (pagepart)
  order by pageset, pagesets.position, pagepart, scripts.position

-----------------------------------

CREATE VIEW schedule as
    select event.eventid, date, clubs.name as clubname,
           venue.name as venuename, venue.address1 as venueadd1,
           venue.address2 as venueadd2, venue.latlong as latlong,
           callers.name as caller,
           cuers.name as cuer, time, flags, eventname, comment,
           group_concat('<A HREF="flyers/' || flyer
                        || '"><b>flyer</b></A>',', ') as pdfs,
           group_concat('<A HREF="../flyers/' || flyer
                        || '"><b>flyer</b></A>',', ') as subpdfs,
           event.eventid as _rowid_
    from event
    left outer join venue on venueid = venue
    left outer join eventpdf on event.eventid = eventpdf.event
    left outer join clubs on clubs.eventid = event.eventid
    left outer join callers on callers.eventid = event.eventid
    left outer join cuers on cuers.eventid = event.eventid
    group by event.eventid
    order by date, clubname, time

-----------------------------------

CREATE TRIGGER schcuernull instead of update of cuer on schedule
 for each row when new.cuer isnull
 begin delete from eventcuer where old.eventid = eventcuer.eventid ; end

-----------------------------------

CREATE TRIGGER schcuer instead of update of cuer on schedule
 for each row when new.cuer notnull
 begin insert into eventcuer values(old.eventid, new.cuer) ; end

-----------------------------------

CREATE TRIGGER schcallernull instead of update of caller on schedule
 for each row when new.caller isnull
 begin delete from eventcaller where old.eventid = eventcaller.eventid ; end

-----------------------------------

CREATE TRIGGER schcaller instead of update of caller on schedule
 for each row when new.caller notnull
 begin insert into eventcaller values(old.eventid, new.caller) ; end

-----------------------------------

CREATE TRIGGER schcomment instead of update of comment on schedule for each row begin update event set comment = new.comment where event.eventid = old.eventid ; end

-----------------------------------

CREATE TRIGGER schclub instead of update of clubname on schedule
 for each row when new.clubname notnull
 begin insert into eventclub values(old.eventid, new.clubname) ; end

-----------------------------------

CREATE TRIGGER schclubnull instead of update of clubname on schedule
 for each row when new.clubname isnull
 begin delete from eventclub where old.eventid = eventclub.eventid ; end

-----------------------------------

CREATE TRIGGER schdate instead of update of date on schedule for each row begin update event set date = new.date where event.eventid = old.eventid ; end

-----------------------------------

CREATE TRIGGER schdelete instead of delete on schedule for each row begin delete from event where event.eventid = old.eventid ; end

-----------------------------------

CREATE TRIGGER scheventname instead of update of eventname on schedule for each row begin update event set eventname = new.eventname where event.eventid = old.eventid ; end

-----------------------------------

CREATE TRIGGER schflags instead of update of flags on schedule for each row begin update event set flags = new.flags where event.eventid = old.eventid ; end

-----------------------------------

CREATE TRIGGER schtime instead of update of time on schedule for each row begin update event set time = new.time where event.eventid = old.eventid ; end

-----------------------------------

CREATE TRIGGER schvenue instead of update of venuename on schedule for each row begin update event set venue = new.venuename where event.eventid = old.eventid ; end

-----------------------------------

CREATE VIEW articlecontentview as
  select *,  _rowid_ as _rowid_ from articlecontent
  order by (articleid + 0), position

-----------------------------------

CREATE TRIGGER articlecontentinsert
  instead of insert on articlecontentview
  for each row
    begin insert into articlecontent values(new.articleid,new.position,new.type,new.content);
    end

-----------------------------------

CREATE TRIGGER articlecontentdelete
  instead of delete on articlecontentview
  for each row
  begin delete from articlecontent
          where articlecontent._rowid_ = old._rowid_;
  end

-----------------------------------

CREATE VIEW pagesetsview as
  select *,  _rowid_ as _rowid_ from pagesets
  order by pageset,position

-----------------------------------

CREATE TRIGGER pagesetsinsert
  instead of insert on pagesetsview
  for each row
    begin insert into pagesets values(new.pageset,new.position,new.pagepart,new.comment);
    end

-----------------------------------

CREATE TRIGGER pagesetsdelete
  instead of delete on pagesetsview
  for each row
  begin delete from pagesets
          where pagesets._rowid_ = old._rowid_;
  end

-----------------------------------

CREATE VIEW personview as
  select *,  _rowid_ as _rowid_ from person
  order by lastname, firstname

-----------------------------------

CREATE TRIGGER personinsert
  instead of insert on personview
  for each row
    begin insert into person values(new.personid,new.firstname,new.lastname,new.address1,new.address2,new.zip,new.phone,new.email,new.spouseid,new.gender);
    end

-----------------------------------

CREATE TRIGGER persondelete
  instead of delete on personview
  for each row
  begin delete from person
          where person._rowid_ = old._rowid_;
  end

-----------------------------------

CREATE VIEW scriptsview as
  select *,  _rowid_ as _rowid_ from scripts
  order by pagepart,position

-----------------------------------

CREATE TRIGGER scriptsinsert
  instead of insert on scriptsview
  for each row
    begin insert into scripts values(new.pagepart,new.position,new.script,new.comment);
    end

-----------------------------------

CREATE TRIGGER scriptsdelete
  instead of delete on scriptsview
  for each row
  begin delete from scripts
          where scripts._rowid_ = old._rowid_;
  end

-----------------------------------

CREATE VIEW stylesview as
  select *,  _rowid_ as _rowid_ from styles
  order by selector,property,value

-----------------------------------

CREATE TRIGGER stylesinsert
  instead of insert on stylesview
  for each row
    begin insert into styles values(new.selector,new.property,new.value,new.comment);
    end

-----------------------------------

CREATE TRIGGER stylesdelete
  instead of delete on stylesview
  for each row
  begin delete from styles
          where styles._rowid_ = old._rowid_;
  end

-----------------------------------

CREATE VIEW substitutionsview as
  select *,  _rowid_ as _rowid_ from substitutions
  order by pageset

-----------------------------------

CREATE TRIGGER substitutionsinsert
  instead of insert on substitutionsview
  for each row
    begin insert into substitutions values(new.pageset,new.substitutions,new.comment);
    end

-----------------------------------

CREATE TRIGGER substitutionsdelete
  instead of delete on substitutionsview
  for each row
  begin delete from substitutions
          where substitutions._rowid_ = old._rowid_;
  end

-----------------------------------

CREATE TRIGGER substitutionspageset
  instead of update of pageset on substitutionsview
  for each row
    begin update substitutions set pageset = new.pageset
            where substitutions._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER substitutionssubstitutions
  instead of update of substitutions on substitutionsview
  for each row
    begin update substitutions set substitutions = new.substitutions
            where substitutions._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER substitutionscomment
  instead of update of comment on substitutionsview
  for each row
    begin update substitutions set comment = new.comment
            where substitutions._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER pagesetspageset
  instead of update of pageset on pagesetsview
  for each row
    begin update pagesets set pageset = new.pageset
            where pagesets._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER pagesetsposition
  instead of update of position on pagesetsview
  for each row
    begin update pagesets set position = new.position
            where pagesets._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER pagesetspagepart
  instead of update of pagepart on pagesetsview
  for each row
    begin update pagesets set pagepart = new.pagepart
            where pagesets._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER pagesetscomment
  instead of update of comment on pagesetsview
  for each row
    begin update pagesets set comment = new.comment
            where pagesets._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER scriptspagepart
  instead of update of pagepart on scriptsview
  for each row
    begin update scripts set pagepart = new.pagepart
            where scripts._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER scriptsposition
  instead of update of position on scriptsview
  for each row
    begin update scripts set position = new.position
            where scripts._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER scriptsscript
  instead of update of script on scriptsview
  for each row
    begin update scripts set script = new.script
            where scripts._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER scriptscomment
  instead of update of comment on scriptsview
  for each row
    begin update scripts set comment = new.comment
            where scripts._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER stylesselector
  instead of update of selector on stylesview
  for each row
    begin update styles set selector = new.selector
            where styles._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER stylesproperty
  instead of update of property on stylesview
  for each row
    begin update styles set property = new.property
            where styles._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER stylesvalue
  instead of update of value on stylesview
  for each row
    begin update styles set value = new.value
            where styles._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER stylescomment
  instead of update of comment on stylesview
  for each row
    begin update styles set comment = new.comment
            where styles._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER personpersonid
  instead of update of personid on personview
  for each row
    begin update person set personid = new.personid
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER personfirstname
  instead of update of firstname on personview
  for each row
    begin update person set firstname = new.firstname
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER personlastname
  instead of update of lastname on personview
  for each row
    begin update person set lastname = new.lastname
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER personaddress1
  instead of update of address1 on personview
  for each row
    begin update person set address1 = new.address1
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER personaddress2
  instead of update of address2 on personview
  for each row
    begin update person set address2 = new.address2
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER personzip
  instead of update of zip on personview
  for each row
    begin update person set zip = new.zip
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER personphone
  instead of update of phone on personview
  for each row
    begin update person set phone = new.phone
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER personemail
  instead of update of email on personview
  for each row
    begin update person set email = new.email
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER personspouseid
  instead of update of spouseid on personview
  for each row
    begin update person set spouseid = new.spouseid
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER persongender
  instead of update of gender on personview
  for each row
    begin update person set gender = new.gender
            where person._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER articlecontentarticleid
  instead of update of articleid on articlecontentview
  for each row
    begin update articlecontent set articleid = new.articleid
            where articlecontent._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER articlecontentposition
  instead of update of position on articlecontentview
  for each row
    begin update articlecontent set position = new.position
            where articlecontent._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER articlecontenttype
  instead of update of type on articlecontentview
  for each row
    begin update articlecontent set type = new.type
            where articlecontent._rowid_ = old._rowid_;
    end

-----------------------------------

CREATE TRIGGER articlecontentcontent
  instead of update of content on articlecontentview
  for each row
    begin update articlecontent set content = new.content
            where articlecontent._rowid_ = old._rowid_;
    end