Structure of the web site

Database tables

Person
  personID
  firstName
  lastname
  address
  zip
  phone
  email
  spouse (personID)

event
  eventID
  date
  time
  venue
  club
  flags
  name (promenader, hip boot boyz...)
  type (special, (federation), schedule, master calendar, tentative)

*********************
We need to classify events

schedule - appears in the monthly schedule
special - appears on the front of the promenader and home page
tentative - appears on calendar - maybe only tentative view
master - master calendar - is this the same as all the above?
*********************

eventcaller
  eventid
  personid

eventcuer
  eventid
  personid

eventinfo
  eventid
  url    // if http:// then link else flyer ?

*********************
All relative links are relative to the top of the hierarchy. The
software must relativize the links when it is known what page the link
appears on.
*********************


eventcode (or maybe a table per type??? - yes - no)
  eventid
  eventtype

flags          this is for explaining MPRCA+ type of codes
  flagID
  name (equals primary key?)
  description

venue database
  venueID
  lat,long
  name
  address
  short name (for listings?)
  text - driving directions
  text - other info

club database (federation is a pseudo club? - no because not listed with clubs)
  clubID
  name
  officers (or in separate database)
  text
  contact
  web page
  (venues?)
  (typical schedule)

club venues ???
  club
  venue

club officers (also federation)
  clubID
  person
  title (president is special?)

caller database
  personID
  status (CCR member, area)

cuer database
  personID
  status (CCR member, area)



CREATE TABLE t(x INTEGER PRIMARY KEY AUTOINCREMENT, y, z);

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);

SELECT city,address,city FROM customers order by country

SELECT column_name(s) FROM table_name AS alias_name

SELECT column_name AS alias_name FROM table_name

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName                [asc | desc] (put person last)

INSERT INTO table_name
VALUES (value1, value2, value3,...)

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

DELETE FROM table_name
WHERE some_column=some_value

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);     [lists all from LEFT even if missing from right)

PRAGMA foreign_keys = TRUE;

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

PRAGMA foreign_keys = TRUE;

create table persons (pid INTEGER PRIMARY KEY AUTOINCREMENT);

create table orders (person, foreign key(person) references persons(pid));

insert into orders values(5);

SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table x(a,b);
sqlite> insert into x values('a', 'c');
sqlite> insert into x values('a', 'd');
sqlite> insert into x values('b', 'd');
sqlite> select group_concat(a) from x  group by b;
a
a,b
sqlite>

select _rowid_,* from x;


Square dance database schema create functions

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

create table person (personid INTEGER PRIMARY KEY AUTOINCREMENT, firstName, lastname, address1, address2, zip, phone, email, spouseid);

create table event (eventid INTEGER PRIMARY KEY AUTOINCREMENT, date, time, venue, club, flags, eventname, comment);

create table eventcaller (event, caller);

create table eventcuer (event, cuer);

create table eventpdf (event, flyer);

create table eventurl (event, url);

create table eventcode (code, meaning);

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);

create table clubofficer (club, position, person);

create table federationofficer (position, person);

create table caller (person, status);

create table cuer (person, status);

create view schedule as select eventid, date, club.name as clubname, venue.name as venuename, venue.address1 as venueadd1, venue.address2 as venueadd2, callers.lastname as caller, cuers.lastname as cuer, time, flags from event left outer join club on clubid = club left outer join venue on venueid = venue left outer join person as callers on callers.personid = caller left outer join person as cuers on cuers.personid = cuer order by date, clubname;


[date, club, caller, cuer, venue, time, flags]

//create table e as select * from event;

//drop table event;

//create table event (eventid INTEGER PRIMARY KEY AUTOINCREMENT, date, club, caller, cuer, venue, time, flags, eventname, comment);

//insert into event (eventid, date, club, venue, time, flags) select eventid, date, club, venue, time, flags from e;

//select strftime("%w",date) from schedule;

//select * from schedule order by strftime("%w",date), date, 3;





drop table if exists temp.sch;

create temp table sch as select * from schedule where substr(schedule.date, 1,7) = '2012-07';

drop table if exists temp.datelist;

create temp table datelist (i INTEGER PRIMARY KEY AUTOINCREMENT, event, date, dayname);

insert into temp.datelist(event, date, dayname) select eventid, date, weekday.dayname from sch join weekday on strftime('%w', date) = day order by date;

--we now have a sorted by date list of events with consecutive i values.

drop table if exists temp.datecount;

create temp table datecount as select date, min(i) as minimum, count() as count from temp.datelist group by date;

--we now have a count and the minimum event number for each date

drop table if exists temp.weekbeginning;

create temp table weekbeginning as select strftime('%w', date) as day, min(i) as weekmin from temp.datelist group by strftime('%w', date);

drop table if exists temp.datedesc;

create temp table datedesc as select datelist.event as eventid, datelist.date as date, i as neweventid, i - minimum as rank, count, dayname, weekmin from datelist join datecount on datelist.date = datecount.date join weekbeginning on weekbeginning.day = strftime('%w', datelist.date);

--datedesc is, for each event, the number of events on the same date and the order of this event on this date.

.separator ''
.output xxx.html

select '<TR>',
         case when weekbeginning.weekmin = datedesc.neweventid then '<TR><TH colspan="6">' || dayname || '</TR>' else '' end,
         case rank when 0 then
         '<TR><TD align=right valign=top rowspan="' || count || '">'
         || ltrim(strftime('%d',datedesc.date),'0') else '' end,
       '<TD>' , clubname,
       '<TD>' , caller, ' / ' , cuer,
       '<TD>' , venuename, ', ', venueadd1, ', ', venueadd2,
       '<TD>' , time ,
       '<TD>' , flags , '</TR>'
         from sch
         join datedesc on sch.eventid = datedesc.eventid
         join weekbeginning on weekbeginning.day = strftime('%w', sch.date)
         order by strftime('%w', sch.date), sch.date, clubname;

.output stdout
.separator '|'

----------------------------------------------------------------
Calendar scripts
----------------------------------------------------------------

strftime('%w', THEDATE)%7 is the first span

6-strftime('%d', THEDATE, '1 month', '-1 day')%7 is the last span

strftime('%d', THEDATE, '1 month', '-1 day') is number of days in month

drop table if exists temp.days;

create temp table days as select n from number where n <= 29;

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

Promenader Publication Process

CLUB NEWS

Collect all club news text into an emacs document. Separate paragraphs with a single blank line.

Normalize line lengths for convenience.

Substitute for & first.

Substitute for dumb quotes, smart quotes, ..., bullet, <, >

Remove previous items from last time from table clubnewsproof.

Copy and paste into table clubnewsproof the various fields. You have to look up the clubid in table club.

Make the web site: java -jar makeweb

Copy to upload area: java -jar copyfiles.jar . ../upload

Post to the web and let the editor know.

FLYERS

convert using following command on linux:

for i in *.pdf; do convert -thumbnail x500 ${i}[0] -flatten ${i/.pdf}.jpg; done;

Then update classifieds table with:

startdate: date flyer should appear in ads and flyers
sortdate: date used to sort order of flyers - generally event date
enddate: date to stop displaying - usually day after event

dates for paid flyers that are not event related are start of month to end of month.

ARTICLES

use gimp and set width of pictures to 500 if bigger

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

RELEASE PROCESS

Copy all new articles from articleproof to article. (Note that articlecontent does not change.)

Copy all club news from clubnewsproof to clubnews.

Update table content, line promenader/date to reflect current issue number. There are 4 issues a year. Increment the roman numeral in WINTER.

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