Difference between revisions of "Adding and Removing Database Tables"

Latest revision as of 20:13, 12 August 2015

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.

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 heredoc:

register_tablecreate("password", <<'EOC');
CREATE TABLE password (
    userid    INT UNSIGNED NOT NULL PRIMARY KEY,
    password  VARCHAR(50)
)
EOC

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.

post_create("clients",
            "sqltry" => "INSERT INTO clients (client) SELECT DISTINCT client FROM logins");

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:

mark_clustered(@LJ::USER_TABLES);

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:

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

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 is run with the --drop 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, in spite of the warning at the top of the file not to edit it directly.