All runnable pages of php files must begin with
to load the session cookies.
New users should start with /start/registration_landing.php . This page is an on-campus page-restricted page. It allows users to sign up. After signing up, users are automatically redirected to the login_landing.php page by the page register.php. The registration landing's page name should also be altered to prevent malicious logins once everyone in the lab has been registered. It can be renamed back if someone else joins the lab. (There is no facility yet if someone leaves the lab, but the hash could be altered to something other than what's there and that will throw off malicious login attempts.
login_landing.php is directed by login.php to the start/start.php page where all the action is.
classes_database.php references a config file stored outside the web root (/users/maurice/peri-password/db_settings.php) to retrieve the database name (this file path is on my local Mac, and I've created an analogous path on the server), and mysql credentials to open and maintain a session to the database.
Notice that the reference to the database is a static class variable, and the option PDO::ATTR_PERSISTENT is passed to PDO to ensure we keep the same database connection for the entire time the user is logged in.
Also, most of the database program utilizes object-oriented code. There are three main classes, the GeneticElement set of classes, the LoadGeneticElement set of classes and the Search classes.
The LoadGeneticElement classes serve mainly to load elements of a mysql table into a select structure, which is managed by the third-party selectize.js.
The GeneticElement classes do the heavy work of manipulating the database. Of note, all entries that manipulate multiple tables (whenever there is a one to many mapping relationship, linking tables are used, for example, to reference items like alleles and transgenes belonging to strains fluoro/tags to plasmids) use transactions to ensure the operations are atomic. (For the freezer/nitrogen locations, these columns are unique in the database. You can see all these relationships by dumping the database). Another thing is for the editing entities, the program is careful to compare the original to the edited version (even across subentries like the alleles and transgenes of a strain) and only update if there was a change. This is done because we want edited entries to save the identity of the most recent edit. When a change occurs anywhere in the entity, the entire entity is resaved. This requires deleting all the existing entries (including alleles and transgenes for strains, for example) and resaving the new (whether or not they are new) ones.
The edit pages serve a dual role for entering new data and editing existing entries. For editing, they retrieve the info from selected list item (from the listed selected by the radio button in the edit entries screen) and populate the appropriate edit fields. Each edit page checks each entry if it's being edited or not to know whether to display an empty entry or filled in from the retrieved info. When saving we pass both the initial info and any edited values so that we can determine if the user made any changes. Only if there were changes do we resave everything.
The Search classes serve to handle searches. There is a separate set of joiner classes to handle joins.
Part of the search builds the columns, thesearchstring, and the from clause. Then the joiner classes is used to build up the various different joins.
The different fields of the search are ANDed with each other with the exception of the comment search. That search is UNIONed with the rest of the search. Comment search can also be limited to just strains for the strains search or it can include (ORed) the comments of genes and transgenes associated with the strain.
The buildquery array contains the values where there are question mark placeholders in the rest of the search string. Everything must be in the same order. This is how PDO works. Among fields with multiple entries, by default the search is currently ORed. When there is more than one element, the checkbox undims and it can be unhchecked. This makes the search for that field an AND search. Doing an AND search is more complex. It requires searching "in" for the elements (transgene.transgene.id in (elements go here)), group by and an having clause with the count of distinct items.
The table classes outputting the search serve a dual role now. They also setup for saving all the results to a csv file.
There is one other major class, Logger. The logger class is responsible for logging every action taken to the database, both new entries and editing of existing entries. The way logging works is that it is a randomly-named file stored in the folder logging_files. The file is created per-user per-session and records the actions just described. It's 36-character filename that is shuffled based on all the digits and lowercase letters. Very unlikely we will hit a conflict. However, over time the filenames will build up. We will eventually have a script that runs once a month or so to cleanup the files that are over 30 days old. So this way, this won't build up forever. We could store all this in the database and have a PHP script do the deleting.