Adding and Removing Database Tables

Tables go into and out of the database by way of the script bin/upgrading/update-db-general.pl. The functions used in that script are defined in bin/upgrading/update-db.pl.

register_tablecreate
The function you'll actually use to create a new table is called register_tablecreate. It takes two arguments: the name of the table, and the SQL query that creates the table. It's commonly called like so, with the query expressed as a heredoc:

It doesn't actually create the table; rather, it saves the data into a hash to be acted upon later by the create_table function when update-db.pl is run.

post_create
If any SQL queries need to be executed after a particular table is created, say to populate a table using data from other tables, this is done with the post_create function.

If you need to load data defined in a file, this is done elsewhere (see below).

populate_database
This function and its helper functions load external data such as mood themes, userprops, & style layouts. If you have some data to load in your new table, you should define a new helper function in update-db.pl and add a call to it to the definition of populate_database.

mark_clustered
This is the function that tells the database whether a table is clustered or global. However, it is not called directly. If you look at the top of update-db-general.pl you'll see the following line:

The @LJ::USER_TABLES array is near the top of cgi-bin/ljlib.pl, and you'll want to add or remove the names of clustered tables there.

Modifying Tables
The bin/upgrading/update-db-general.pl script contains one giant register_alter call, which acts on a subroutine that tests for any needed modifications.

Conditional Functions
Within that subroutine are conditional code blocks that run tests using functions like table_relevant, column_type, index_name and check_dbnote.

table_relevant
If the named table exists in the database, this returns true.

column_type
Takes two arguments, table name & column name, and returns the SQL data type of that column. Useful for checking to see if a column is defined the way you expect.

index_name
Takes two arguments, table name & index name, and returns true if the named index is defined on the table. Useful for adding and removing indices.

check_dbnote
Takes as an argument an arbitrary string. If it's been set before with set_dbnote, it returns true. Usually called like so:

In other words, this code will be run once, and thereafter the conditional check will see that the dbnote is set and skip ahead. The assumption is after you've converted your data once, you won't need to do it again.

Action Functions
There are four functions used for arbitrary actions on tables.

do_alter
Takes as arguments the table name and the ALTER TABLE query to modify the schema of the table. Dies if it encounters a database error.

try_alter
Just like do_alter, except it doesn't fail if an error is encountered.

do_sql
Takes as arguments the table name and a query to modify the table's data. do_alter is actually a special case of do_sql. Dies if it encounters a database error.

try_sql
Just like do_sql, except it doesn't fail if an error is encountered.

Dropping Tables
Dropping is the mirror image of creating. You replace the table's register_tablecreate statement with a register_tabledrop statement, and remove any alters that act on the table. When update-db.pl</tt> is run with the --drop</tt> option, the drop_table function will be called to do the dirty work.

It is also current practice to remove any lines relevant to the removed table from bin/upgrading/base-data.sql</tt>, in spite of the warning at the top of the file not to edit it directly.