5 # Bootstrap the database
7 # Mark Huang <mlhuang@cs.princeton.edu>
8 # Copyright (C) 2006 The Trustees of Princeton University
10 # $Id: db,v 1.4 2007/01/24 21:05:30 mlhuang Exp $
13 # Source function library and configuration
14 . /etc/plc.d/functions
15 . /etc/planetlab/plc_config
20 # Export so that we do not have to specify -p to psql invocations
21 export PGPORT=$PLC_DB_PORT
23 ### updates the subversion column in plc_db_version by applying
24 ### all migrations scripts in /usr/share/plc_api/migrations/nnn-up-*.sql
25 ### between the current_subversion+1 and the last one
26 ### migration indices must be contiguous
27 function migrate_db () {
29 ### check for (or create) the subversion column in plc_db_version
30 psql -U $PLC_DB_USER -c 'select (subversion) from plc_db_version' $PLC_DB_NAME > /dev/null
31 [ "$?" = 0 ] || psql -U $PLC_DB_USER -c 'ALTER TABLE plc_db_version ADD subversion integer DEFAULT 0' $PLC_DB_NAME
33 # get current subversion - OUCH, THIS IS UGLY
34 current_subversion=$(psql -U $PLC_DB_USER -c 'select (subversion) from plc_db_version' $PLC_DB_NAME | \
35 grep '^ *[0-9][0-9]*$')
36 current_subversion=$(($current_subversion))
37 upgrade_index=$current_subversion
40 upgrade_index=$(printf "%03d" $(($upgrade_index+1)))
41 upgrade_scripts=$(ls -1 /usr/share/plc_api/migrations/${upgrade_index}-up-*.sql 2> /dev/null)
42 if [ -z "$upgrade_scripts" ] ; then
43 echo "DB current migration is $current_subversion"
44 psql -U $PLC_DB_USER -c "UPDATE plc_db_version SET subversion=$current_subversion" $PLC_DB_NAME
47 for script in $upgrade_scripts; do
48 echo "Applying migration script $script"
49 psql -U $PLC_DB_USER -f $script $PLC_DB_NAME
51 current_subversion=$(($upgrade_index))
56 #### dumps the database for future restoration
57 # uses /root/dumps since /root is mounted on /data/root
60 dump=/root/dumps/$(date +'%Y-%m-%d-%H-%M.sql')
61 mkdir -p $(dirname $dump)
62 pg_dump --user=$PLC_DB_USER $PLC_DB_NAME > $dump
66 function clean_dumps () {
67 find /root/dumps -name '[0-9]*.sql' -atime +15 | xargs rm
71 if [ "$PLC_DB_ENABLED" != "1" ] ; then
78 MESSAGE=$"Bootstrapping the database"
81 # Update the maintenance account username. This can't be
82 # done through the api-config script since it uses the
83 # maintenance account to access the API. The maintenance
84 # account should be person_id 1 since it is created by the
86 psql -U $PLC_DB_USER -c "UPDATE persons SET email='$PLC_API_MAINTENANCE_USER' WHERE person_id=1" $PLC_DB_NAME
88 # Update the Drupal site_name variable
89 psql -U $PLC_DB_USER drupal <<EOF
90 DELETE FROM variable WHERE name = 'site_name';
91 INSERT INTO variable (name, value) VALUES ('site_name', 's:${#PLC_NAME}:"$PLC_NAME";');
106 MESSAGE=$"Migrating the database"
115 MESSAGE=$"Dumping the database"
124 MESSAGE=$"Cleaning old database dumps"
132 echo "Usage: $0 [start|migrate|dump|clean-dump]"