\documentclass {article}
%\documentclass [twocolumn]{article}
\usepackage{amsmath}
\usepackage{indentfirst}
\usepackage{blkarray}
                             % The preamble begins here.
\title{The Federation Website}  % Declares the document's title.
\author{Sidney Marshall}      % Declares the author's name.
\date{June 21, 2012 (updated \today)}  % Deleting this command produces today's date.




%allow other brackets in bordermatrix using [] arguments

\makeatletter
\newif\if@borderstar
\def\bordermatrix{\@ifnextchar*{%
  \@borderstartrue\@bordermatrix@i}{\@borderstarfalse\@bordermatrix@i*}%
}
\def\@bordermatrix@i*{\@ifnextchar[{\@bordermatrix@ii}{\@bordermatrix@ii[()]}}
\def\@bordermatrix@ii[#1]#2{%
\begingroup
 \m@th\@tempdima8.75\p@\setbox\z@\vbox{%
  \def\cr{\crcr\noalign{\kern 2\p@\global\let\cr\endline }}%
  \ialign {$##$\hfil\kern 2\p@\kern\@tempdima & \thinspace %
  \hfil $##$\hfil && \quad\hfil $##$\hfil\crcr\omit\strut %
  \hfil\crcr\noalign{\kern -\baselineskip}#2\crcr\omit %
  \strut\cr}}%
 \setbox\tw@\vbox{\unvcopy\z@\global\setbox\@ne\lastbox}%
 \setbox\tw@\hbox{\unhbox\@ne\unskip\global\setbox\@ne\lastbox}%
 \setbox\tw@\hbox{%
  $\kern\wd\@ne\kern -\@tempdima\left\@firstoftwo#1%
   \if@borderstar\kern2pt\else\kern -\wd\@ne\fi%
  \global\setbox\@ne\vbox{\box\@ne\if@borderstar\else\kern 2\p@\fi}%
  \vcenter{\if@borderstar\else\kern -\ht\@ne\fi%
   \unvbox\z@\kern-\if@borderstar2\fi\baselineskip}%
   \if@borderstar\kern-2\@tempdima\kern2\p@\else\,\fi\right\@secondoftwo#1 $%
 }\null \;\vbox{\kern\ht\@ne\box\tw@}%
\endgroup
}
\makeatother

\DeclareMathOperator{\sech}{sech}
\DeclareMathOperator{\csch}{csch}
\DeclareMathOperator{\arc}{arc}
\DeclareMathOperator{\real}{real}
\DeclareMathOperator{\imag}{imag}
\DeclareMathOperator{\AGM}{AGM}
%\DeclareMathOperator{\Re}{Re}
%\DeclareMathOperator{\Im}{Im}


\usepackage{enumitem}

\newcommand{\ip}[2]{(#1, #2)}
                             % Defines \ip{arg1}{arg2} to mean
                             % (arg1, arg2).

%\newcommand{\ip}[2]{\langle #1 | #2\rangle}
                             % This is an alternative definition of
                             % \ip that is commented out.

% from latex.ltx

\begin{document}             % End of preamble and beginning of text.

\maketitle                   % Produces the title.
%\addappheadtotoc
\tableofcontents

\newpage

\begin{abstract}

The Federation website is described with details on how the website
is put together. Also described are the tools used to automatically
build the website. All of the software that is used for maintaining
the website is free.

Add-on domains are also on the Federation website and the
administration of these is also described.

\end{abstract}

\section{Introduction}

The Federation website contains information about square and round
dance clubs in the Rochester area. One of the most important parts of
the site is the dance schedule for the area clubs. Most of the current
website pages are generated automatically from a database by special
scripts. Because of this, changes to the content and format are easy
to accomplish.

All of the information to create the website are contained in a
database along with the database statements needed to actually create
the website. Schedules, club officers etc. are updated by changing
the appropriate database table. Structural changes to the website are
made by editing the tables containing the SQL statements used in
building the website.

The database can be updated using the database editor
\verb|dbe.jar|. The website is updated by running
\verb|makeweb.jar|. Modified files are copied to the website image
area using \verb|copyfiles.jar|. Then either \verb|copyfiles.jar| or a
program such as \verb|WinSCP| is used to actually update the ``live''
website. Either of the local copies of the website can be browsed with
a browser to check if the updates were correctly made.

Add-on domains can be updated by editing their raw \verb|.html| pages
but these pages are usually updated by someone else.

\section{Top-Level View of the Website}

The \verb|squaredancingrochester.org| website contains web pages and
additional files such as \verb|.jpg| files and \verb|.pdf| files. Most
of the \verb|.html| pages that are not contained in subdomains or
specific to clubs are generated by scripts, especially pages
containing data like dance schedules and club information.

The various types of generated web pages are:

\begin{description}[style=multiline,leftmargin=2in]
  \item[Announcements] Federation news
  \item[Articles] Various news articles, roving reporter, obituaries
    of interest to the square dance community
  \item[Awards] A description of some awards in the square dance
    community
  \item[Callers and Cuers] A list of callers and cuers in the area
  \item[Class/Club dances] A list of class/club dances in the area
  \item[Club Dance List] A list of dances sorted by club
  \item[Club News] News for each club in the federation
  \item[Clubs] A list of clubs in the area
  \item[Editor's Message] A message from the editor
  \item[FAQ Page] A list of general questions and answers
  \item[Federation Home Page] The home page for the Rochester
    Federation
  \item[Federation Staff] A list of federation staff members
  \item[History] Currently contains scanned past issues of the
    Promenader
  \item[Home Page] The initial page of the website
  \item[Caller Dance List] A list of dances sorted by caller and cuer
  \item[Dance List by Day] A list of dances sorted by month day
  \item[Dance List keys] A list of letter codes used on dance lists
  \item[Dance List by Month] A list of dances in a month calendar
    format
  \item[Dance List by Weekday] A list of dances sorted by day of week
  \item[Federation Meetings] A list of federation meeting days
  \item[Federation Minutes] Minutes of federation meetings
  \item[New Dancer Information] Information for the new dancer
  \item[President's Message] A message from the federation president
  \item[Promenader] Top page listing promenader sections and a list of
    special dances
  \item[Promenader Staff] Listing of staff members of the Promenader
  \item[Single Page] Many odd pages have this type and are just copied
    from the database
  \item[Cascading Style Sheet] A cascading style sheet setting the
    styles for the website pages
  \item[Flyers] A page containing images of flyers sorted by date
  \item[Site Map] A site map for the federation website
\end{description}

There is also a proofreading section containing some experimental pages and:

\begin{description}[style=multiline,leftmargin=2in]
  \item[Articles for Proofing] Articles for proofreading before going
    on the website proper
  \item[Club News for Proofing] Club news for proofing before going on
    the website proper
  \item[Sandbox Home] The home page for the proofreaders and
    experimental pages
  \item[Full Site Map] An experimental site map for the entire website
    including all subdomains
\end{description}

The three main top pages are (currently) the home page, the promenader
home page, and the federation home page. Most pages have direct links
to these pages.

The dance schedules link to a view of the current month's dance
schedule. There are three different monthly views available --- one
with the days of the month in order, one with the days grouped by
weekday, and one with a layout like a conventional calendar. Also
available is a list of dances sorted by caller/cuer and another page
sorted by club.  All data comes from the same database so all of the
information is consistent.

The clubs page lists the square dance clubs and other information from
the database.

There are five directories containing content not in the database:
\begin{description}[style=multiline,leftmargin=1in]
  \item[compressed] Compressed files for articles, club news, and
    flyers
  \item[flyers] All flyers - both \verb|pdf|s and \verb|.jpg|s
  \item[gifs] Logos for the club news
  \item[jpg] Various \verb|.jpg|s for page headers etc.
  \item[png] Various \verb|.jpg|s for articles
\end{description}

\section{Workflow for Building the Website}

All of the data necessary for creating the html pages of the website
is in the database \verb|squaredance.db| except for \verb|.pdf| files,
\verb|.jpg| files, and other image-type documents. The tools need a
\verb|Java| environment to run. You do not need the \verb|JDK| if you
are not rebuilding the tools.

\subsection{Programs Used}

Several programs are used to build and maintain the website. They can
be run in either a linux or Microsoft (or probably Apple)
environment. All of the special software is stored on the website so
it can be easily downloaded.

For making thumbnail images for the flyers page I use
\verb|imagemagick| under linux but any appropriate program can be
used. Photos for the articles section generally are submitted with
very high resolution so I reduce the resolution to 500 pixels wide
using \verb|gimp| under linux, but again, any suitable image
processing program can be used. This saves a lot of space on the
website and the images download quicker.


\subsection{Useful Java Programs}

There are several Java programs used in maintaining the website. All
of these programs are on the website.

\begin{description}[style=multiline,leftmargin=1.2in]
  \item[Clean.class] Checks a file for characters that annoy a web
    browser
  \item[dbe.jar] A display editor for editing the database
  \item[makeweb.jar] This program recreates the entire website from
    the database
  \item[copyfiles.jar] Copies files that have been changed from one
    directory to another
  \item[filebrowser.jar] A very general file transfer program
\end{description}

\subsubsection{Clean}

Usage: \verb|java Clean files...|.

All of the files on the command line are scanned for ``funny''
bytes. A ``funny'' byte is any byte less than \verb|0x20| (control
character other than \verb|0x0a| (new-line) or any character greater
than \verb|0x7e|. If one is found the line in which it appears is
printed with a \verb|^| pointing to the offending byte. This program
is used to find characters that need to be replaced in a web page.

Here is a useful table of character replacements:
\begin{description}[style=multiline,leftmargin=1.6in]
\item[Quotation mark] \verb|&quot;|
\item[Ampersand] \verb|&amp;|
\item[Less-than sign] \verb|&lt;|
\item[Greater-than sign] \verb|&gt;|
\item[Open single quote] \verb|&lsquo;|
\item[Close single quote] \verb|&rsquo;|
\item[Open double quotes] \verb|&ldquo;|
\item[Close double quotes] \verb|&rdquo;|
\item[En dash] \verb|&ndash;|
\item[Em dash] \verb|&mdash;|
\item[Bullet] \verb|&bull;|
\item[Non-breaking space] \verb|&nbsp;|
\item[Horizontal ellipsis] \verb|&hellip;|
\end{description}

\subsubsection{dbe.jar}

Usage: \verb|java -jar dbe.jar [database-name]|.

If the optional database name is not included a file browser is
displayed to choose the database to edit. Complete documentation of
this editor can be found in \verb|DBEditor.ps|, and
\verb|DBEditor.pdf|.

All of the data required for generating the website are stored in an
SQLite database called \verb|squaredance.db|. You can modify this
database with the command
\begin{verbatim}
java -jar dbe.jar squaredance.db &
\end{verbatim}
You can modify tables or execute SQL commands with this editor. The
documentation for using this editor is in \verb|DBEditor.ps| and
\verb|DBEditor.pdf|.

\subsubsection{makeweb.jar}

Usage: \verb|java -jar makeweb [-map [root]]|.

If the \verb|-map| option is used alone the \verb|makeweb| program
first creates in the sitemap table an image of the current directory
and subdirectories. If a directory name appears after the \verb|-map|
option then a map is made of that directory. I generally use
\begin{verbatim}
java -jar makeweb.jar -map ../upload
\end{verbatim}
so that the dates are closer to what is actually up on the web. (You
would have to run the makeweb, copyfiles sequence twice to be more
accurate.)

The database used is hard coded to be \verb|squaredance.db|. The
\verb|makeweb| program reads SQL statements from the database and
creates several web pages in the current directory and
subdirectories. Any errors in the process such as SQL statement errors
will terminate the process with an obscure error message. You will
also get an error message if the scripts try to create files in a
nonexistant directory so you will have to make any new directories
manually.

\subsubsection{copyfiles.jar}

Usage: \verb|java -jar copyfiles.jar [-m] source-dir target-dir|.

This copies all files that do not have the same contents from the
source directory to the target directory. If the \verb|-m| flag is
used then extra files in the target directory will be deleted and the
file dates in the target directory will be set equal to the file dates
in the source directory. You probably don't want to use the \verb|-m|
flag for website operations.

The Java program \verb|copyfiles.jar| copies all changed files from
one directory to another. I keep the working website files in a
directory called \verb|squaredance| and the files that are an image of
the actual website in a directory called \verb|upload|. Then, when I
am finished updating the database and adding \verb|.pdf| and
\verb|.jpg| files to the working copy in \verb|squaredance| I execute
the command
\begin{verbatim}
java -jar copyfiles.jar . ../upload
\end{verbatim}
to copy the changed files to the \verb|upload| directory.

\subsubsection{filebrowser.jar}

The program, \verb|filebrowser.jar|, is a general purpose file
transfer program that mimics WinSCP or FileZilla. It has the
capability of comparing and transfering between two local file
systems, two remote file systems, or a local and remote file
system. It also has editors for editing files and a diff program for
comparing two files. It also has a remote shell capability. The
documentation for this program is in a separate file.

\subsubsection{Java / sqlite-jdbc}

The sqlitejdbc project has a free sqlite3 interface totally
implemented in Java. I used this interface to write Java programs to
use \verb|SQLite3| databases. The two programs used for maintaining
the website are \verb|dbe.jar| and \verb|makeweb.jar|.  The program
\verb|dbe.jar| is used to enter and update data for the website. The
program can also execute raw sql commands for changing the structure
of the database or you can use other tools (like the SQLite shell) for
this. The program \verb|makeweb.jar| uses the information in the
\verb|squaredance.db| database and creates most of the \verb|.html|
files on the website.

\subsection{WinSCP and ftp}

\verb|WinSCP| is a free program that provides a windows interface to
the unix scp program. It can be downloaded from \verb|winscp.net|. It
allows a quick way to copy files between machines and indicate which
files need to be updated. I generally use the \verb|filebrowser.jar|
for transferring files. I use this program to upload files to the web
server. This program works on most platforms.

Do not upload or change any files in the subdomains
\verb|callers/MikeCallahan|, \verb|clubs/CloverleafSquares|, or
\verb|SSDUSA| as these pages are managed by someone else. If you
refresh in both directions you won't overwrite changes to
subdomains. You will want to propagate changes to these subdomain
websites to the squaredance directory so that the next copyfiles won't
change them. This will allow backing up the subdomains easily.

\subsection{emacs}

I use emacs for all of my text editing. Any plain-text editor can be
used including a text editor window in the Database Editor. Note that
\verb|test.db| is a scratch db so editing anything in this database is
OK and can be used for trying out new ideas. The main editing tasks
are to edit the content in the database. This could be done using the
database editor but some extra editing tools may be useful. I also use
emacs to edit the documentation (in the \verb|.tex| files).

\section{Building The Website}

I keep an image of the website in two parallel directories:
\verb|squaredance| and \verb|upload|.

The directory \verb|squaredance| is where I do my modifications. The
date is set to the current date so you don't have to set
anything. (The database engine apparently runs on GMT so it will
``see'' the next day after 7 or 8 p.m. of the current day. To edit the
database type
\begin{verbatim}
java -jar dbe.jar squaredance.db &
\end{verbatim}
or click on the \verb|dbe.jar| icon and select \verb|squaredance.db|
to run the database editor.

After editing the database (and any other files) I run
\begin{verbatim}
java -jar makeweb.jar -map ../upload
\end{verbatim}
This will update the sitemap table and then rebuild the website using
the contents of the database. You should then be able to browse the
updated site in the squaredance directory.

After making the \verb|squaredance| directory the way you want it to
be, execute
\begin{verbatim}
java .jar copyfiles.jar . ../upload
\end{verbatim}
to copy the changed files to the \verb|upload| directory.  I generally
run
\begin{verbatim}
java -jar makeweb.jar -map ../upload
java -jar copyfiles.jar . ../upload
\end{verbatim}
again to get the full site map updated.

After copying the changed files these two directories will be
identical (except for the dates on the files that have been remade but
whose contents haven't changed). I do this copying so that files that
have been remade with identical content won't have to be uploaded to
the website.

Now the directory \verb|upload| contains all of the files that the
website needs. I then run
\begin{verbatim}
java -jar filebrowser.jar
 /home/sidney/home/upload/
 //squaree8@squaredancingrochester.org/home1/squaree8/public_html
\end{verbatim}
(all on one line).

The copying process defaults to downloading files on the website that
are not in \verb|upload|. You should make sure you do not upload files
to any of the add-on domains. Just click on the green arrow to delete
the local copy.

\subsection{Add-on Domains}

Add-on domain web sites could be edited in the \verb|squaredance|
directory and then proceed as above. But these files are generally
maintained by someone else. If someone else is maintaining these pages
you must be careful not to upload pages to the add-on website pages as
this will revert any changes that they may have made. Generally you
want to only download or delete local files in a subdomain.

\subsection{Transferring ``Ownership'' of the Website}

Only one person should be updating the website at a time. If several
people attempt to update the website at the same time chaos will
result. The tools require an exact local image of the website (e.g.,
in directory \verb|upload|). This copy will be stale if someone else
updates the website causing the other person's updates to be lost when
the original person does another update. Therefore the following
ritual should be followed for transferring ``ownership''.

Only one person can have the ``baton'' at a time. This is the person
currently updating the website. To pass the baton this person finishes
all updates and unambiguously gives up the baton to another person. At
this point the website contains the ``truth'' about the contents of
the website.

The person receiving the baton then downloads changes to mirror the
website on his/her machine in the \verb|upload| directory. This
directory is then (locally) mirrored to the \verb|squaredance|
directory preserving dates and removing files that do not appear in
the \verb|upload| directory. The new baton owner is now free to manage
the website.

Note that this process of handing over the baton only requires
downloading the changed files since the last time the baton was
owned. (A new baton owner will have to download the entire site.)

\subsection{Typical Website Operations}

Most operations can be done by clicking on the \verb|dbe.jar| icon or
running
\begin{verbatim}
java -jar dbe.jar squaredance.db &
\end{verbatim}
and editing the various database tables. Note that views cannot be
edited but triggers have been added to some views to give the
appearance of editing a table. The \verb|schedule| view has such
triggers associated with it so some editing functions can be done by
editing this view although not in an obvious way. You must click the
\verb|Update Table| button to preserve your edits or they will be
lost. If you make a mistake you can click \verb|Revert Table| or \verb|Revert Selected| to
abandon your edit(s). For views the \verb|Update Table| button also
refreshes the view with any updated data from the tables after
executing the appropriate triggers.

After completing a sequence of edits the command
\begin{verbatim}
java -jar makeweb.jar -map ../upload
\end{verbatim}
should be executed to update the website. The \verb|-map ../upload|
just updates the \verb|fullsitemap| table in the \verb|squaredance.db|
database from the specified directory.

\subsubsection{Adding A Dance}

I add a dance by first adding a row to the \verb|event| table and
filling in the date (the date format must be exact - four-digit year,
hyphen, two-digit month, hyphen, two-digit day). Multiple lines can be
added at the same time to enter multiple dances. You must click
\verb|Update Table| on the \verb|event| table before proceeding. You
must also click \verb|Update Table| on the \verb|schedule| view to see
the new dates.

I then edit the \verb|schedule| view to enter the various facts about
the dance. Then click \verb|Update Table| to verify your changes. You
must enter the \verb|clubid| from the \verb|club| table in the
\verb|clubname| column. Each \verb|clubid| is added to the list of
clubs sponsoring the dance. You must click \verb|Update Table| after
each \verb|clubid| is added. To remove a club you must delete all
clubs associated with the event by dragging a \verb|null| to the
\verb|clubname| column and starting over.

The \verb|venuename| should be filled in with the \verb|venueid|
(found on the \verb|venue| table). Only one venue is allowed with an
event. The \verb|venueadd1|, \verb|venueadd2|, and \verb|latlong|
fields will be filled in automatically (from the venue table) when
\verb|Update Table| is clicked.

The \verb|caller| and \verb|cuer| fields work like the \verb|clubname|
field and allow multiple callers and cuers to be associated with an
event. You must enter the \verb|personid| from the \verb|personview|
view. Just like the \verb|clubname| column you must drag a \verb|null|
to the \verb|caller| or \verb|cuer| field and start over if you wish
to remove a name.

The fields \verb|time|, \verb|flags|, \verb|eventname|, and
\verb|comment| are just text fields and can be edited normally.

\subsubsection{Adding A Flyer to a Dance}

You should make the flyer a \verb|.pdf| file and put it in the flyers
directory (with a name describing the dance and date). It is important
that the extension \verb|.pdf| be in lower case. Then edit the
\verb|eventpdf| table adding a row and putting the \verb|eventid| in
the \verb|eventid| field and the filename of the flyer in the
\verb|flyer| field. It is OK to have the same flyer associated with
several events or several flyers associated with the same
event. Flyers should be named consistently. I generally do all work in
the \verb|tmp| directory and move the results to the \verb|flyers|
directory.

\subsubsection{Adding A Flyer or Ad to the Classifieds}

After putting the \verb|.pdf| file in the flyers directory as above
you should make a \verb|.jpg| of the first page of the flyer with a
height of 500 pixels. It is important that the extensions \verb|.pdf|
and \verb|.jpg| be in lower case. I use \verb|convert| from the
\verb|ImageMagick| suite of tools although any program capable of
converting a \verb|.pdf| to a \verb|.jpg| with a height of 500 pixels
can be used. The \verb|.jpg| should have the same base name as the
\verb|.pdf| and be stored in the flyers directory. I use the command
\verb|convert -thumbnail x500 x.pdf[0] -flatten x.jpg|

Then add a row to the \verb|classifieds| table. The \verb|startdate|
is the starting date the flier should be displayed. The
\verb|sortdate| is the date used for sorting the classifieds. The
\verb|enddate| is the date after which the flyer should not appear. I
generally set this to be one day after the event. The \verb|name| is
the root name of the \verb|.pdf| and \verb|.jpg| --- the thumbnail
will be linked to the \verb|.pdf| file.

\subsubsection{Updating a Flyer}

Since all of the database tables and flyer information are already on
the website you just need to replace the flyer. To update a flyer,
make a new \verb|.jpg| of the new flyer and replace the flyer
\verb|.pdf| and \verb|.jpg| files in the flyers directory and run
\verb|java -jar copyfiles.jar . ../upload/|. Then upload the
\verb|upload| directory to the website. The full site map won't have
the correct date for the new flyers but I don't think anyone will
notice.

\subsubsection{Adding A Venue}

To add a new venue just add a row to the \verb|venue| table and enter
the appropriate data in the proper fields. As a hint, if you go to
\verb|maps.google.com| and right click on the location, and select
\verb|Whats here?|. This will display the latitude and longitude of
the clicked location. Left-click on it then copy and paste this value
into the \verb|latlong| field.

\subsubsection{Adding A Person or Changing a Person's Information}

To add a new person to the database just add a row to the
\verb|person| table or \verb|personview|. The \verb|personview| view
has triggers to allow editing all fields except the \verb|personid|
column. This column will be filled in by the database when the
table/view is updated.

If a person changes their name/email etc., just edit the appropriate
cell in the table or view. When
\verb|java -jar makeweb.jar -map ../upload/| is run all instances of
that person's information will be updated.

\subsubsection{Releasing a new edition of the promenader}

For proofreading, the new articles, along with the editor's message
and the presitent's message, go in the \verb|articleproof| table and
\verb|articlecontent| tables. All of the new club news goes in the
\verb|clubnewsproof| table.

This will put the new articles, editor's message, president's message,
and club news in the sandbox for proofreading.

To release a new issue of the promenader

\begin{description}[style=multiline,leftmargin=.5in]
  \item[] Copy all new articles from articleproof to article. (Note
    that articlecontent does not change.)
\begin{verbatim}
  insert into article select * from articleproof
  order by date, rank, articleid
\end{verbatim}
  \item[] Copy all club news from clubnewsproof to clubnews.
\begin{verbatim}
  insert into clubnews select * from clubnewsproof
\end{verbatim}
  \item[] Update table content --- row promenader/date --- to reflect
    the current issue number. There are 4 issues a year. Increment the
    roman numeral for the WINTER edition.
\end{description}

Then remake the website as usual. The entries in the
\verb|articleproof| and \verb|clubnewsproof| can be deleted sometime
later in preparation for the next issue. The editor's message and
presidents message will go in the proper place and the other articles
will be collected in the photos and features section.

\section{The Database}

Most of the changeable data on the website is stored in a
database. This database includes information about people, clubs,
events, dance locations, and other kinds of information that can be
reduced to a table form. The database engine that is used is
\verb|sqlite3| which is a free relational database engine that is used
in many applications.

To generate most of the pages on the website a script is run that
queries the database and generates the pages. Common modifications to
the website are made by updating the database and running the
script. This insures that all of the pages are consistent with each
other. Modifications to website styles can be made in one place.

\subsection{Automatically Generated Views}

Sometimes it is convenient to have a view on a table that is sorted in
a particular order. \verb|SQLite| does not permit operations on views
but it does allow triggers on views that can update a backing table
instead of updating the view. For simple cases there are scripts in
the database machinery that makes this easy. There are two tables that
implement this functionality: \verb|vieworder| and \verb|views|. Only
one view per table is allowed using this mechanism because of name
collisions. This restriction could be lifted if there were a need.

The table \verb|vieworder| contains a column \verb|tablename| giving
the name of the table which to view and a column \verb|tableorder|
giving the sort order as a comma-separated list of columns in the base
table to sort on.

The table \verb|views| has a column \verb|tablename| with the name of
the table, a column \verb|position| giving the relative position of a
column in the view, and a column \verb|column| giving the name of the
column in the table and view.

The scripts create the view with the specified column order (which can
be different from the base table order) and triggers that handle
inserting rows into the view, deleting rows from the view, and
updating cells in the view.

\subsection{The Main Tables in the Square Dance Database}

\subsection{Tables, Views, and Triggers}

Here are the tables and views used in the database with a short
description. All tables with an
\begin{verbatim}
INTEGER PRIMARY KEY AUTOINCREMENT
\end{verbatim}
designation use this value as a key for other tables to reference an
entry in this table. These keys are guaranteed never to be changed or
reused. You should not attempt to set this kind of field.

\subsubsection{Table article}

This table contains the published articles for the photos and features
section of the promenader.

The \verb|date| field is the date of the promenader issue.

The \verb|rank| field is the rank order for articles in a particular
date.

The \verb|type| field is one of news, president, reporter, obit, or
editor. The news, reporter, obit types go in the photos and features
section. The president type goes in the President's Corner under the
federation page. The editor type goes to the Editor's page linked from
the Promenater page.

The \verb|heading| field is the title of the piece.

The \verb|subheading| field (if needed) is the subheading of the
piece.

The content of the article is in the \verb|articlecontent| or
\verb|articleproof| table.

\subsubsection{Table articlecontent}

This table contains the actual contents of an article. An article can
contain text, photos, or headings.

The \verb|articleid| field is the articleid of the article in the
\verb|articlecontent| or \verb|articleproof| table.

The \verb|position| field is the position in the article where this
part of the article should appear.

The \verb|type| field is one of jpg for pictures, h6 for a heading, or
text for text, or author for an author line.

The \verb|content| field is the .jpg file name for jpgs (these jpgs
belong in the \verb|png| directory), the heading for type h6, or the
text for type text.

\subsubsection{View articlecontentview}

Automatically generated editable view on the \verb|articlecontent|
table.

\subsubsection{Table articleproof}

This table is identical in format to the \verb|article| table but the
articles etc. will appear in the sandbox for proofing.

\subsubsection{View automation}

The \verb|automation| view is there to help understand how the
\verb|mkkeweb| program builds the website and is a view on the
\verb|substitution|, \verb|pagesets|, and \verb|scripts| tables
ordered by how the automation process will process these tables. The
\verb|automation| view is not editable (maybe in the future). This
view is not used by any of the machinery but this view might be
helpful if you are trying to figure out how a particular web page is
made.

\subsubsection{Table breakingnews}

This is where ``breaking news'' on the home page is stored. The
\verb|sortdate| column stores the date of the ``breaking news''. The
\verb|enddate| stores the date when the news should disappear. The
\verb|heading| column contains the headline for the news. The
\verb|news| column contains the news. Double newlines indicate
paragraph breaks.

If there is no news to display then the breaking news section does not
appear.

\subsubsection{View callers}

This view is used by the schedule view.

\subsubsection{Table callerscuers}

Which people are callers or cuerers and if they are members of the
CCR.

\subsubsection{Table classifieds}

This table puts flyers in the ads and flyers section of the
promenader. The automation process uses this table to generate the ads
and flyers page in sorted order and remove outdated flyers.

The \verb|startdate| field is the date at which the flyer should start
being displayed. It can be set to sometime in the past to immediately
display a flyer.

The \verb|sortdate| field is the date used to order the flyers. For
dances I set this to the date of the dance. For flyers with multiple
dances I set this to the first dance on the flyer.

The \verb|enddate| field is the date when the flyer should no longer
be displayed. For dances I set this to the day after the dance. Note
that the date is GMT so it will be the next day 4 or 5 hours before
midnight.

The \verb|name| field is the name of the pdf and jpg of the flyer with
the \verb|.pdf| or \verb|.jpg| suffix removed. The \verb|.pdf| and
\verb|.jpg| files should be in the \verb|flyers| directory.

\subsubsection{Table club}

This table lists all of the clubs and organizations that put on a
dance.

The \verb|name| field is the name of the club or organization.

The \verb|url| field is a link to the club's web page if it has one.

The \verb|gif| field is the name of a \verb|.gif| file logo in the
\verb|gifs| directory for the club. This is the logo used in the club
news.

\subsubsection{Table clubnews}

This table contains all of the club news for the promenader.

The \verb|date| field is for the date of the issue.

The \verb|clubid| field is for the clubid (from the club table) of the
club.

The \verb|news| field contains the actual text of the club news.

The \verb|information| field is for where to contact for more
information about the club.

The \verb|reporters| field is for the reporters of this club news.

\subsubsection{Table clubnewsproof}

The format for this table is identical to that of the \verb|clubnews|
table except the result will show up in the proofreaders part of the
website.

\subsubsection{Table clubofficer}

The clubid, position (like \verb|president|), \verb|personid| of a
club officer, and place (indicating order of presentation.

\subsubsection{View cuers}

This view is used by the schedule view.

\subsubsection{View clubs}

This view presents the club names associated with an event. Multiple
clubs are separated by commas. Used by the schedule view.


\subsubsection{Table content}

This table holds fragments of web pages that are assembled by various
scripts.

The \verb|page| field indicates what web page is using this content.

The \verb|part| field contains a string to allow several fragments on
a single page.

The \verb|content| field is intended to contain the actual web page
content. You need to examine the actual SQL using this fragment to see
how it is used.

\subsubsection{Table dancechanges}

This table tracks updates to the dance schedules. It is populated via
triggers on the \verb|event|, \verb|eventcaller|, \verb|eventclub|,
and \verb|eventpdf| tables. This table should be cleaned out as the
updates contained in it are applied to other websites.

\subsubsection{Table event}

This table lists all of the square dance events.

The \verb|date| field is the date of the event and must be in the
format \verb|YYYY-MM-DD| with leading zeros if necessary for weeks and
days.

The \verb|venue| field is the venueid of the venue of the dance.

The \verb|time| field is text indicating the time of the dance (with
other information about times).

The \verb|flags| field is a text field containing the flags for the
dance. A ``O'' (not in the word ``DEMO'') in the flags indicates an
open house. A ``*'' indicates a class/club dance. These special flags
are used to add dances to special lists on the website.

The \verb|eventname| field is the name of the event if any.

The \verb|comment| field is used for contact information for open
houses.

\subsubsection{Table eventcaller}

This table associates a caller with an event. More than one caller can
be associated with an event (even no callers).

\subsubsection{Table eventclub}

This table associates a club with an event. More than one club can be
associated with an event (even no clubs).

\subsubsection{Table eventcuer}

This table associates a cuer with an event. More than one cuer can be
associated with an event (even no cuers).

\subsubsection{Table eventpdf}

If an event has a flyer (or even more than one) that information is
entered into this table. The event is the eventid and the flyer is the
short name for the pdf file (x.pdf). Multiple flyers can be associated
with an event.

\subsubsection{Table eventurl}

If an event has a web page (or even more than one) that information is
entered into this table. The event is the eventid and the url is the
complete url. Multiple urls can be associated with an event.

\subsubsection{Table faq}

This table contains all of the Frequently Asked Questions. The
contents of this table are used to generate the FAQ page on the
website.

The \verb|rank| field indicates the order of the questions and
answers.

The \verb|question| field is the question.

The \verb|answer| field is the associated answer to the question.

\subsubsection{Table federationemail}

This table is for email addresses of federation officer positions (not
a person's email address). These addresses will (hopefully) remain
static when officers change.

The \verb|section| field refers to the section of the officer table
(see the table \verb|federationstaff|).

The \verb|position| field refers to the position of the officer (see
the table\\ \verb|federationstaff|).

The \verb|email| field is the email address.

\subsubsection{Table federationmeetings}

This table contains all of the meeting places and times and meeting
minutes of the federation. Meetings without minutes are listed as
future meetings followed by the minutes of the meetings that have been
published.

The \verb|date| field is the date (past or future) of the meeting.

The \verb|venue| field is the venueid (same as a dance) where the
meeting will/did take place.

The \verb|room| field is a text field for the room information.

The \verb|time| field is the time of the meeting - a text field).

The \verb|refreshments| field is a text field for who should bring
refreshments.

The \verb|minutes| field contains the minutes of meetings that have
happened.

\subsubsection{Table federationstaff}

This table contains all of the federation officers. Currently there
are two sections - officer and committee.

The \verb|rank| field indicates the order for listing officers and
committee members.

The \verb|section| field (currently) is either ``officer'' or
committee''.

The \verb|position| field is the name of the position.

The \verb|person| field contains the personid of the person with this
position.

\subsubsection{Table monthname}

This is a utility table mapping month number to month name.

\subsubsection{Table number}

This table contains consecutive numbers used for generating the
calendar month view.

\subsubsection{View orphancallers}

This table gives the eventid of events that have been deleted but
still have a event-caller association in the \verb|eventcaller|
table. These entries in the\\ \verb|eventcaller| table can be
deleted. There should probably be a trigger to do this automatically.

\subsubsection{View orphanclubs}

This table gives the eventid of events that have been deleted but
still have a event-club association in the \verb|eventclub|
table. These entries in the \verb|eventclub| table can be
deleted. There should probably be a trigger to do this automatically.

\subsubsection{View orphancuers}

This table gives the eventid of events that have been deleted but
still have a event-cuer association in the \verb|eventcuer|
table. These entries in the \verb|eventcuer| table can be
deleted. There should probably be a trigger to do this automatically.

\subsubsection{View orphanspecial}

This table gives the eventid of events that have been deleted but
still are listed in the \verb|specialdances|
table. These entries in the \verb|specialdances| table can be
deleted. There should probably be a trigger to do this automatically.

\subsubsection{View orphanpdfs}

This table gives the eventid of events that have been deleted but
still have a event-pdf association in the \verb|eventpdf| table. These
entries in the \verb|eventpdf| table can be deleted. There should
probably be a trigger to do this automatically.

\subsubsection{Table pagesets}

This table contains information for the website building automation
process. A full explanation of this table is in the Automation Process
section below.

The \verb|pageset| field gives the pageset this row belongs to and
matches the corresponding \verb|pageset| field in the
\verb|substitutions| table.

The \verb|position| field gives the order within a pageset.

The \verb|pagepart| field matches the \verb|pagepart| field in the
\verb|scripts| table.

\subsubsection{View pagesetsview}

Automatically generated editable view on the \verb|pageset| table.

\subsubsection{Table person}

All references to people in the database are referenced by their
personid and the actual information about the person is stored in this
table. Updating information for a person in this table will spread
updated information everywhere on the website.

The \verb|personid| field is automatically generated and is unique.

The \verb|firstname| field is the person's first name.

The \verb|lastname| field is the person's last name.

The \verb|address1| field is the street address (if available) of the
person.

The \verb|address2| field is the city, state part of the address (if
available) of the person.

The \verb|zip| field is the zipcode part of the address (if available)
of the person.

The \verb|phone| field is the phone number with area code (if
available) of the person.

The \verb|email| field is the email address (if available) of the
person.

The \verb|spouseid| field is the personid of the spouse (if available)
of the person. This field is currently unused by any of the scripts.

The \verb|gender| field is the gender (M or F) of the person. This
field is currently unused by any of the scripts.

\subsubsection{personview}

Automatically generated editable view on the \verb|person| table.

\subsubsection{Table promenaderstaff}

This table lists the people on the staff of the Promenader.

The \verb|rank| field tells the order to display the staff members.

The \verb|position| field is the name of the position.

The \verb|person| field is a personid from the \verb|personview| view>

\subsubsection{Table rafclubs}

This table lists the clubids of clubs that are members of the
Rochester Area Federation.

\subsubsection{View schedule}

This view assembles useful information about events from multiple
tables and views. Some but not all columns have triggers to allow
editing of the underlying table. Some of these triggers do nonobvious
things so you need to read the details below.

The \verb|date| field is the date of the event. This field is editable
and will edit the date field of the event in the \verb|event| table.

The \verb|clubname| field is a list of clubs sponsoring the event. To
add a club to this list replace the contents of this field with the
clubid from the \verb|club| table. To delete all clubs drag a ``NULL''
entry to this field.

The \verb|venuename| field is the name of the venue of the event. To
change a venue replace this field with the venueid from the
\verb|venue| table. This will update the venuename, venueadd1, venue2,
and latlong fields from the \verb|venue| table.

The \verb|venueadd1| field is the address1 field from the \verb|venue|
table.

The \verb|venueadd2| field is the address2 field from the \verb|venue|
table.

The \verb|latlong| field is the latlong field from the \verb|venue|
table.

The \verb|caller| field is a list of callers for this event. To add a
caller to this list replace the contents of this field with the
personid from the \verb|personview| table. To delete all callers drag
a ``NULL'' entry to this field.

The \verb|cuer| field is a list of cuers for this event. To add a cuer
to this list replace the contents of this field with the personid from
the \verb|personview| table. To delete all cuers drag a ``NULL'' entry
to this field.

The \verb|time| field is an editable text field indicating the time
for this event. Editing this field will edit the underlying time field
in the \verb|event| table.

The \verb|flags| field is an editable text field indicating the flags
for this event. Editing this field will edit the underlying flags
field in the \verb|event| table.

The \verb|eventname| field is an editable text field containing the
name of this event. Editing this field will edit the underlying
eventname field in the \verb|event| table.

The \verb|comment| field is an editable text field containing a
comment about this event. It is used for contact information for open
houses. Editing this field will edit the underlying comment field in
the \verb|event| table.

The \verb|pdfs| field is not editable and is a list of html-formatted
pdf references associated with this event. The information for this
field comes from the \verb|eventpdf| table which must be edited
directly.

The \verb|subpdfs| field is not editable and is a list of
html-formatted pdf references associated with this event. The url
assumes that this will be used one level down in the directory
structure of the website. The information for this field comes from
the \verb|eventpdf| table which must be edited directly.

\subsubsection{Table scripts}

This table contains information for the website building automation
process. A full explanation of this table is in the Automation Process
section below.

The \verb|pagepart| field is the pagepart selected by the pagepart
field in the \verb|pagesets| table.

The \verb|position| field gives the relative ordering within a
pagepart.

The \verb|script| field contains the actual SQL command that is run
during the automation process. All occurances of \$$n$ are replaced by
the corresponding result of the select statement from the
\verb|substitutions| table.

\subsubsection{View scriptsview}

Automatically generated editable view on the \verb|scripts| table.

\subsubsection{Table singlepage}

This table makes single pages on the website. The automation process
surrounds the content with appropriate html to make a valid page.

The \verb|location| field contains the name of the file of the web
page. It should be at the top level of the directory hierarchy.

The \verb|type| field is not currently used.

The \verb|heading| field contains the headline for the page.

The \verb|content| field contains the html page content. This field is
copied to the web page.

\subsubsection{Table sitemap}

This table is automatically generated by the \verb|makeweb.jar|
program when the \verb|-map| option is used. The previous table
contents are deleted and replaced with the result of a tree-walk of
the website.

This table is used to automatically generate the full site map set of
pages and the list of scanned promenaders.

The \verb|entryid| field is a unique identifier for this
file/directory for this run of the program. It will change with
successive runs of the program.

The \verb|name| field is the base name of the file or directory

The \verb|type| field is 0 for a file, 1 for a directory and -1 for
something else.

The \verb|path| field is a relative path (including the file/directory
name) from the top of the hierarchy.

The \verb|parentid| field is the entryid of the directory containing
this entry. An entryid of 0 is used for the top of the hierarchy and
there is no table entry with an entryid of 0.

The \verb|length| field is the length of the file in bytes.

The \verb|date| field is the last-modified time of the file/directory
as milliseconds past the unixepoch in GMT. The automation process does
not understand daylight savings time (yet).

\subsubsection{Table specialdances}

This table lists the eventids of special dances.

\subsubsection{Table styles}

This table is used to generate the cascading style sheet used on the
website. The automation process collects information from this table
and makes a correctly formatted file \verb|style.css|.

The \verb|selector| field sets the selector.

The \verb|property| field sets the property.

The \verb|value| field sets the value of the property.

\subsubsection{View stylesview}

Automatically generated editable view on the \verb|styles| table.

\subsubsection{Table substitutions}

This table contains information for the website building automation
process. A full explanation of this table is in the Automation Process
section below. Each row specifies a group of similar pages to be made
or specifies initialization code.

The \verb|pageset| field specifies the pageset to be built.

The \verb|substitutions| field contains an SQL select statement. The
first column of the result set is the name of the file to be built and
the following columns are strings used for replacing the \$$n$ in the
script column of the \verb|scripts| table. If the first column of the
result set is NULL then no file is generated but the pageset code is
still run, e.g., for initialization of temp tables.

\subsubsection{View substitutionsview}

Automatically generated editable view on the \verb|substitutions| table.

\subsubsection{Table venue}

This table holds information about event locations locations.

The \verb|venueid| field is a unique identifier for this venue.

The \verb|latlong| field is the latitude and longitude of the venue in a format acceptable to google maps.

The \verb|name| field is the name of the venue.

The \verb|address1| field is the street address of the venue.

The \verb|address2| field is the city and state of the venue.

The \verb|zip| field is the zipcode of the venue.

The \verb|shortname| field is not currently used.

The \verb|url| field is the url of the venue.

\subsubsection{Table vieworder}

This table is used to create editable views on tables.

The \verb|tablename| field is the name of the table on which the view is to be constructed.

The \verb|tableorder| field is a list of fields specifying the desired
sort order of the view.

\subsubsection{Table views}

This table is used to create editable views on tables. It specifies
the columns in the view and the column order. The name of the view
will be called [tablename]view and the triggers will be called
[table-name] [field-name] and [table-name] [insert] and [table-name] [delete].

\verb|tablename| field is the name of the table.

\verb|position| field is the relative order of the columns for this
table.

\verb|column| field is the name of the column.

\subsubsection{Table weekday}

This is a utility table mapping day number to day name.

\subsubsection{Trigger sch...}

These triggers are used for updating the appropriate tables when
editing the \verb|schedule| view.

\subsection{Overall File Structure}

The Makefile makes the Website.pdf and associated files and runs the
makeall bash script. The makeall script sets the dates to make
calendars and the current month and then runs scripts for each type of
web page. The calendar pages are divided into day, week, and month
views and a script is run for each month and view combination.

The last thing that makeall does is copy files whose contents differ
from the update directory or don't exist in the upload directory.

The current directory structure of the website is:
\begin{description}[style=multiline,leftmargin=1.2in]
\item[./articles] News articles and federation president messages
\item[./awards] Awards-related pages
\item[./cgi-bin] I don't use this yet except for two experimental scripts
\item[./clubs] Club web pages for clubs without their own website
\item[./compressed] Compressed \verb|.pdf|s of club news and articles
\item[./flyers] \verb|pdf| flyers and their \verb|jpg| thumbnails
\item[./fullsitemap] pages for the experimental full site map
\item[./gifs] logos for the clubs used in club news
\item[./history] scanned images of the paper promenader are in the subdirectory \verb|history/scanned|
\item[./jpg] various \verb|jpg| files used by various web pages
\item[./photo] remnant of old website - could be deleted
\item[./plesk-stat] no idea - it is empty
\item[./png] \verb|jpg| files used in articles
\item[./sandbox] proofreading and experimental area
\item[./schedule] all of the dance schedule pages
\item[./thumbnails] left over from the conversion process - should be deleted
\item[./tmp] random stuff in process for making a new edition of the website
\item[./tools] some useful information for setting up a cygwin environment
\end{description}

\section{The Automation Process}

The new automation process keeps more data in the database. In
particular, all scripts, club news, article texts, and more, are now
in the database. Only flyers and club websites and pictures still
reside in files. This should allow for a more flexible operation. By
editing the contents of the database, the number and kind of generated
pages can be easily changed. All of the code that accesses the
database and writes the files is written in Java and uses libraries
that work on windows, Apple, and linux platforms. Maintenance of the
website can be easily done on any of these platforms. Only Apple lacks
the ability to run \verb|WinSCP| for file transfer although ftp can be
used. (A future version of this software may include an ftp-like
client written in Java.)

There are three special database tables used by the automation process:
\verb|substitutions|, \verb|pagesets|, and \verb|scripts|.

\subsection{The substitutions Table}

\begin{tabbing}
xxx\=xxx\=xxx\=xxx\=xxx\=\kill
Table \verb|substitutions| columns \\
\> \verb|pageset| - the set of pages controlled by this row \\
\> \verb|substitutions| - text substitutions performed on pageset scripts \\
\> \verb|comment| - a human readable comment \\
\end{tabbing}

The \verb|pageset| column is a tag denoting the type of page(s)
generated in this page set. The name is arbitrary but will be matched
in the \verb|pagesets| table. The page sets will be executed in
collating order so some scripts can be made to run before other
scripts. Multiple rows with the same \verb|pageset| will work but are
probably not useful.

The \verb|substitutions| column contains an sql statement that returns
a table whose first column is the file name of the file to be created
and the remaining columns are strings to be substituted into the page
generation scripts. If the first field is ``null'' then the scripts
will still be run but no file is written. This is useful for scripts
that set up temporary tables for use by the following scripts. The
substitutions are performed by looking for \verb|$0| through \verb|$9|
in the page generating script and replacing these two characters by
the contents of the corresponding column 0 through 9 from the returned
table. (Note that \verb|$0| will be replaced by the file name field.)
The sequence \verb|$$| will be replaced by a single \verb|$|.

The \verb|comment| column contains a human readable comment explaining
the purpose of this row and how the substitutions are intended to be
used.

Recommended substitutions are:
\begin{tabbing}
xxx\=xxx\=xxx\=xxx\=xxx\=\kill
\>\>Table \verb|pagesets| columns \\
\>\>\> \verb|$0| - the filename \\
\>\>\> \verb|$1| - the current date (for top-level menus referring to schedules) \\
\>\>\> \verb|$2| - the distance to the home directory, e.g., \verb|../| or empty. \\
\>\>\> \verb|$3| - the date before this calendar page \\
\>\>\> \verb|$4| - the date of this calendar page \\
\>\>\> \verb|$5| - the date after this calendar page \\
\end{tabbing}

\subsection{The pagesets Table}

\begin{tabbing}
xxx\=xxx\=xxx\=xxx\=xxx\=\kill
Table \verb|pagesets| columns \\
\> \verb|pageset| - the pageset we are creating \\
\> \verb|position| - the collating order of this field determines the execution order \\
\> \verb|pagepart| - the name of the pagepart scripts to be run to calculate insertions \\
\> \verb|comment| - a human readable comment \\
\end{tabbing}

The \verb|pageset| field labels the page group of this line and is
used to link with the corresponding row(s) of the \verb|substitutions|
table.

The \verb|position| field contains a string indicating the ordering of
pageset rows belonging to the same page set. Rows will be ordered by
the collating order of the database.

The \verb|pagepart| field references the rows in the \verb|scripts| table
that contain scripts that generate content for the piece. A generated
page results from concatenating all if the pieces in order.

The \verb|comment| column contains a human readable comment explaining
the purpose of this row and how the substitutions are intended to be
used.

\subsection{The scripts Table}

\begin{tabbing}
xxx\=xxx\=xxx\=xxx\=xxx\=\kill
Table \verb|scripts| columns \\
\> \verb|pagepart| - the page part this script belongs to \\
\> \verb|position| - the collating order of this field determines the order \\
\> \verb|script| - an sql statement generating the content of part of a page \\
\> \verb|comment| - a human readable comment \\
\end{tabbing}

The \verb|pagepart| field labels the page group of this line and is
used to link with the corresponding row(s) of the \verb|pagesets|
table.

The \verb|position| field is used to order multiple lines in the same
page part. Rows will be ordered by the collating order of the
database.

The \verb|script| field contains a database sql statement that
generates a part of a page. The substitutions from the
\verb|substitutions| table are applied before executing the
statement. For generating similar pages the same script is used with
different substitutions. All of the table output generated by running
this script (generally a select statement) is written to the current
file. All of the fields of each table output are concatenated and each
resulting string is written to the current file followed by a
new-line.

The \verb|comment| column contains a human readable comment explaining
the purpose of the script in this row.

\subsection{The Algorithm}

\begin{tabbing}
xxx\=xxx\=xxx\=xxx\=xxx\=\kill
for each substitution row in table \verb|substitutions| \\
\>run substitutions sql and get table of substitutions \\
\>for each substitution row \\
\>\>get and open file (if not null) \\
\>\>get matching pagegroup rows in order from table \verb|scripts| \\
\>\>\>for each script (after substitutions) get table \\
\>\>\>\>for each row of table \\
\>\>\>\>\>concatenate fields and write as line to file \\
\>\>close file (if not null) \\
\end{tabbing}

If the script does not result in a table, i.e., a \verb|select|
statement, or the file name is \verb|null|, the script is still run
for side effects but no data is written.

The recommended way of using these tables is to group similar pages to
be generated in a pageset. Each page of the pageset will be generated
by an identical set of scripts but writing a different file and with
different substitutions. These alternatives are generated by the
select statement contained in the substitutions column of the
\verb|substitutions| table. For example, all of the month schedules
can be generated by substituting the month to be generated and the
filename for the month.

\subsection{The Code}

\begin{tabbing}
xx\=xx\=xx\=xx\=xx\=xx\=xx\=xx\=xx\=xx\=xx\=xx\=xx\=\kill
// Get and process pagegroups from master table substitutions \\
Table subs = new Table("select * from substitutions order by pagegroup", \\
\>\>\>\>\>\>\>\>\>\>\> "pagegroup", "substitutions"); \\
ArrayList$<$String$>$ subsLine; \\
while((subsLine = subs.next()) != null) \{ \\
\>//System.out.println("subsLine: " + subsLine); \\
\>String pagegroup = subsLine.get(0); \\
\> // run substitution sql to get list of substitutions for pagegroup \\
\>Table subsList = new Table(subsLine.get(1)); \\
\>ArrayList$<$String$>$ subsListLine; // actual substitutions vector \\
\>while((subsListLine = subsList.next()) != null) \{ \\
\>\>//System.out.println("subsListLine: " + subsListLine); \\
\>\>String filename = subsListLine.get(0); \\
\>\>FileWriter out = null; \\
\>\>if(filename != null) \{ \\
\>\>\>out = new FileWriter(filename); \\
\>\>\} \\
\>\>// get scripts for pagegroup \\
\>\>Table scripts \\
\>\>\>= new Table("select script from scripts where pagegroup = '" \\
\>\>\>\>\>\>\>\>\>+ pagegroup + "' order by piece", \\
\>\>\>\>\>\>\>\>\>"script"); \\
\>\>ArrayList$<$String$>$ scriptsLine; \\
\>\>while((scriptsLine = scripts.next()) != null) \{ \\
\>\>\>// run substituted sql to get tables to write out \\
\>\>\>if(filename != null) \{ \\
\>\>\>\>Table page = \\
\>\>\>\>\>new Table(substitute(scriptsLine.get(0), subsListLine)); \\
\>\>\>\>ArrayList$<$String$>$ pageLine; \\
\>\>\>\>while((pageLine = page.next()) != null) \{ \\
\>\>\>\>\>// write scriptsLine concatenated \\
\>\>\>\>\>StringBuilder sb = new StringBuilder(); \\
\>\>\>\>\>for(int i = 0; i $<$ pageLine.size(); i++) \{ \\
\>\>\>\>\>\>sb.append(pageLine.get(i)); \\
\>\>\>\>\>\} \\
\>\>\>\>\>sb.append('\verb|\|n'); \\
\>\>\>\>\>out.write(sb.toString()); \\
\>\>\>\>\} \\
\>\>\>\} else \{ \\
\>\>\>\>String s = substitute(scriptsLine.get(0), subsListLine); \\
\>\>\>\>Statement stat = conn.createStatement(); \\
\>\>\>\>stat.execute(s); \\
\>\>\>\>stat.close(); \\
\>\>\>\} \\
\>\>\} \\
\>\>if(out != null) \{ \\
\>\>\>out.close(); \\
\>\>\} \\
\>\} \\
\} \\
\end{tabbing}

\section{New Promenader Website}

\subsection{New Automation Process}


\subsection{Current Promenader}

Front cover - breaking news and special dances

list of club presidents etc.

classified ads

promenader info - deadlines - promenader officers

Ads and ad info

federation officers

President's corner article

News with pictures - Daphne-Norma news - Letter from Halsted

Club news - logos - pictures

Flyers in order with ads - both local and nonlocal dances

Club schedules with flags - symbol key

Inside cover - callers and cuers - both CCR and non-CCR

Back cover - index - other notes

\subsection{Top-Level Pages}

\subsubsection{Home Page}

Listing of breaking news and special dances

Listing of class/club dances

Miscellaneous links

\subsubsection{Clubs}

schedule with month layout

schedule with week layout

schedule with day layout

schedule page for each club

Club news

roving reporter

federation news

\subsubsection{Wanted Pages}

history of caller / cuer schedules?

history of club schedules?

\section{Tools For Maintaining the Federation Website}

This section explains how the tools used to maintain the website can
be modified and rebuilt. If you are just maintaining the website you
do not need anything in this section.

The tools need a \verb|Java| and \verb|latex| environment to run. I
compile all Java programs with Java8.  You can run the tools in unix,
linux or windows (or the mac --- I haven't tried this but it should
work). For maintaining the documentation on the website I use latex in
a linux texlive environment. Using latex in a linux environment also
works.

If you want to directly access the database from the command line you
can use the sqlite3 shell program or you can use a command window in
the database editor. For most uses the command window is quite
adequate. If you need to use the sqlite3 shell program it must be
recompiled for each environment. The sources for this program are on
the website. You need to recompile the shell.c and sqlite3.c programs
for each different environment. The \verb|Makefile| has some comments
that might be useful for compiling from the sources.

\subsection{make}

The \verb|make| program is used to automatically remake the
documentation and the Java programs.  A version of \verb|make| needs
to be in the path if you want to remake the documentation or any of
the \verb|.jar| files. The makefile is \verb|Makefile|. It makes the
documentation and all of the \verb|.jar| files.

You only need \verb|make| if you want to remake the documentation
files or recompile the \verb|.jar| files.

The Java Database Editor can be compiled by executing
\verb|make dbe.jar|. The build process deletes all old \verb|.class|
files, compiles \verb|DatabaseEditor.java|, uses \verb|manifest.txt|
as the manifest, and creates the \verb|dbe.jar| file with the newly
created \verb|.class| files and \verb|DatabaseEditor.java| file, and
then deletes the \verb|.class| files. All of this is specified in the
\verb|Makefile|. To experiment with another sqlite3 version just edit
the \verb|manifest.txt| file and do a \verb|make|.

\subsection{latex and Friends}

The documentation is generated using latex. The source for the website
documentation is in \verb|Website.tex|. The source for the database
editor documentation is in \verb|DBEditor.tex|. The source for the file browser documentation is in \verb|Filebrowser.tex|. If you are not
remaking the documentation you do not need latex.

\subsection{Rebuilding the Documentation}

The documentationfor the website is contained in \verb|.dvi|,
\verb|.ps|, and \verb|.pdf| files. To rebuild either the
\verb|Website| or the \verb|DBEditor| documentation files you edit the
corresponding \verb|.tex| files and then run \verb|make|. This will
generate the \verb|.dvi|, \verb|.ps|, and \verb|.pdf| files (as well
as some other files used by \verb|latex|). You must have \verb|latex|
installed for this to work.

\subsection{Rebuilding the Tools}

There are five \verb|java| programs used for maintaining the web. They are:

\begin{description}[style=multiline,leftmargin=1.5in]
\item[Clean.java] A program that detects ``funny'' characters in a text file
\item[DatabaseEditor.java] An editor for a \verb|SQLite| database
\item[MakeWeb.java] Creates the entire website from the database
\item[Copyfiles.java] Copies changed files from one directory to another
\item[FileBrowser.java] A program for inspecting and moving files
\end{description}

To change or augment the behavior of any of these programs, edit the
\verb|.java| and run \verb|make|.  The ``executables'' made from these
source code files are:

\begin{description}[style=multiline,leftmargin=1.5in]
\item[Clean.java] \verb|Clean.class| (run with \verb|java Clean files ...|)
\item[DatabaseEditor.java] \verb|dbe.jar|
\item[MakeWeb.java] \verb|makeweb.jar|
\item[Copyfiles.java] \verb|copyfiles.jar|
\item[FileBrowser.java] \verb|filebrowser.jar|
\end{description}

The database editor and the makeweb programs require a
\verb|sqlite-jdbc| \verb|.jar| file to access the database. The way
this works is that in the manifest of the \verb|.jar| files
\verb|dbe.jar| and \verb|makeweb.jar| have a class-path entry pointing
to the proper \verb|sqlite-jdbc| \verb|.jar| file. This is set from
the file \verb|manifest.txt| and is currently set to
\verb|Class-Path: sqlite-jdbc-3.27.2.jar|. To change the
\verb|sqlite-jdbc| version used edit the \verb|manifest.txt| file.

The file browser program requires a \verb|myjsch.jar| file to access
remote files. The way this works is that in the manifest of
\verb|filebrowser.jar| there is a class-path entry pointing to the
\verb|myjsch.jar| file. This is set from the file
\verb|jschmanifest.txt|. This is an edited version of \verb|jsch|
located in the \verb|jsch-0.1.54| directory. It's \verb|Makefile| is
at \verb|jsch-0.1.54/src/main/java|. To change the \verb|jsch| version
used, download the new jsch version and edit the appropriate
files. Also edit the \verb|Makefile| to copy the appropriate
\verb|.jar| file. WARNING: I have added several methods and made other
corrections to the released version of \verb|jsch| so you will have to
track down these changes.

\appendix

\section{Setting Up The Tools}

\subsection{Java}

The tools will work with any of the Java versions Java8 or higher. I
compile all Java programs with Java8. The
\verb|sqlite-jdbc-3.27.2.jar| file is needed to supply the interface
between Java and sqlite3. Included in the .jar file are native
libraries that speed up the interface. The supplied \verb|Makefile|
has a target for \verb|dbe.jar| that will compile
\verb|DatabaseEditor.java| and add these files into a copy of the
sqlite jar file with the correct manifest. The file
\verb|manifest.txt| contains the manifest that contains the class path
of the sqlite \verb|.jar| file.

\subsection{SQLite3}

\verb|SQLite3| is a free relational database system used in many
applications. All of the tables of an sqlite database are stored in a
single file so backing up the database is as simple as copying this
file. The code for accessing the database is contained in a
\verb|sqlite-jdbc-xxx.jar| file which must be in the same directory as
the Java code accessing the database. The \verb|dbe.jar| and
\verb|makeweb.jar| files contain a class path pointer to this file. I
am using version \verb|sqlite-jdbc-3.27.2.jar| although most versions
are compatible with each other. Versions of the \verb|Java|
\verb|jdbc| can be downloaded from the maven repository at
\verb|mvnrepository.com/artifact/org.xerial/sqlite-jdbc|.

\section{Overview of the Internet and Websites}

(This section is for a fuller understanding of how websites work. It
is not necessary for day-to-day maintaining of the website.)

The world-wide-web is a collection of web pages hosted on web servers
spread around the world. A web browser given the name of a web page,
e.g., \verb|squaredancingrochester.org|, first looks up the name using
a domain name server to get an IP address, e.g., 69.195.124.252, and
sends a GET request to this address. This request contains the name of
the page desired and the host name. The web server then sends the
requested page to the web browser (with additional information).

In order for this to work the domain name server needs to have a
correct map from names to IP addresses and the web server needs to be
able to process requests and deliver requested web pages, images etc.

For a yearly fee a domain registrar will add a mapping from a
specified domain name to an IP address. Currently, the federation is
paying for four domain registrations:
\verb|squaredancingrochester.org|, \verb|ssdusa.org|,
\verb|callermike.com|, and \verb|cloverleafsquares.org|. They all
point to the same IP address at bluehost. Mike Callahan, Cloverleaf
Squares, and SSDUSA then reimburse the federation for the cost of his
domain name registration. SSDUSA also has dar2018.ssdusa,
dar2019.ssdusa, dar2020.ssdusa, and dar2021.ssdusa as subdomains for
which the cost is free.

The web pages for the federation and all add-on domains are currently
hosted by bluehost. We have ``unlimited'' space for web
pages. Bluehost says that ``unlimited'' means less than 50,000 inodes
(or files) and less than 50 GBytes of storage but they only get
excited at around 200,000 inodes. We are currently using about 8,000
inodes and are using about 4.5 GBytes.

If, for some reason we want to change our website host, all we have to
do is transfer the website files to the new host and make the domain
name servers point to the IP address of the new host. This is
conceptually easy but requires several bureaucratic steps (proving we
own the domain, unlocking the domain, etc.). The subdomains, unless
they want to be transferred too, will have to make other arrangements.

\subsection{Domains on the Federation Website}

The following table lists the domains hosted on the federation website
and where the content for these domains is located. Each domain
appears, from the outside, as a totally independent website.

\begin{description}[style=multiline,leftmargin=2in]
  \item[squaredancingrochester.org] \verb|public_html/|
  \item[callermike.com] \verb|public_html/callers/MikeCallahan/|
  \item[cloverleafsquares.org]
    \verb|public_html/clubs/CloverleafSquares/|
  \item[ssdusa.org] \verb|public_html/SSDUSA/|
  \item[dar2018.ssdusa.org] \verb|public_html/SSDUSA/dar2018/|
  \item[dar2019.ssdusa.org] \verb|public_html/SSDUSA/dar2019/|
  \item[dar2020.ssdusa.org] \verb|public_html/SSDUSA/dar2020/|
  \item[dar2020.ssdusa.org] \verb|public_html/SSDUSA/dar2021/|
\end{description}

The first domain \verb|squaredancingrochester.org| is the master
domain for the website and contains the content for all domains. The
locations of the content for the other domains is set in bluehost's
cpanel accessed by a user with the master password.

Only the maintenance of the \verb|squaredancingrochester.org| website
is described here as the other websites are administered by someone
else.

\subsection{Domain and Website Fees}

Currently \verb|sidneym@frontiernet.net| is listed as the domain owner
for all domains on the website. All domains are renewed using the
credit card of Sidney Marshall. This means that no one else will be
notified if the renewal process fails for some reason and the domain
names will then go into redemption and someone else could acquire
these domain names. This is a Federation issue that I currently don't
know how to resolve. The website itself has the same problem.

It would be nice if the Federation had an email address, not
associated with the website, for these notifications and a Federation
credit card.






\end{document}               % End of document.
