View source for Adding and Removing Database Tables

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

== Creating Tables ==

=== 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 [http://en.wikipedia.org/wiki/Here_document heredoc]:

<source lang="sql">
register_tablecreate("password", <<'EOC');
CREATE TABLE password (
    userid    INT UNSIGNED NOT NULL PRIMARY KEY,
    password  VARCHAR(50)
)
EOC
</source>

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 <tt>update-db.pl</tt> 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.

<source lang="sql">
post_create("clients",
            "sqltry" => "INSERT INTO clients (client) SELECT DISTINCT client FROM logins");
</source>

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 <tt>update-db.pl</tt> 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 <tt>update-db-general.pl</tt> you'll see the following line:

<source lang="perl">mark_clustered(@LJ::USER_TABLES);</source>

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

== Modifying Tables ==

The <tt>bin/upgrading/update-db-general.pl</tt> 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:

<source lang="sql">
    # convert 'all' arguments to '*'
    if (table_relevant("priv_map") && !check_dbnote("privcode_all_to_*")) {
        do_sql("UPDATE priv_map SET arg='*' WHERE arg='all'");
        set_dbnote("privcode_all_to_*", 1);
    }
</source>

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 <tt>update-db.pl</tt> is run with the <tt>--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 <tt>bin/upgrading/base-data.sql</tt>, in spite of the warning at the top of the file not to edit it directly.

[[Category: Development]]
[[Category: Database Tables]]