Magellan Linux

  • Back to homepage
    • /[alx-svn]/alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh

    Diff of /alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh

    Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

    revision 8490 by niro, Fri Feb 5 12:31:10 2016 UTC revision 8552 by niro, Mon Feb 8 13:33:18 2016 UTC
    # Line 1  Line 1 
    1  #!/bin/bash  #!/bin/bash
    2    
    3    # read sql settings from configuration file
    4    if [ -f ./conf/mcore-sql-schema.conf ]
    5    then
    6     source ./conf/mcore-sql-schema.conf
    7    fi
    8    
    9  # include everything from include  # include everything from include
    10  for inc in $(find ./include -type f -name \*.sh)  for inc in $(find ./include -type f -name \*.sh)
    11  do  do
    # Line 90  sql_create_table() Line 96  sql_create_table()
    96    
    97   for ((i=1; i<count; i++))   for ((i=1; i<count; i++))
    98   do   do
    99   line="${TABLE_DEFINITION[${i}]}"   line=( ${TABLE_DEFINITION[${i}]} )
    100   column="${line%% *}"   column="${line[0]}"
    101   opts="${line/${column} /}"   opts="${line[*]:1}"
    102    
    103   case ${opts} in   case ${opts} in
    104   *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;   *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
    # Line 123  sql_create_table() Line 129  sql_create_table()
    129   fi   fi
    130  }  }
    131    
    132    # TABLE_DEFINITION=(
    133    # "table_name"
    134    # "column1_name column1_argvs"
    135    # "column2_name column2_argvs"
    136    # "columnN_name columnN_argvs"
    137    #
    138    # eg.    "description varchar(255) DEFAULT NULL AFTER value"
    139    # )
    140    #
    141    sql_add_column()
    142    {
    143     local table
    144     local count
    145     local i
    146     local line
    147     local opts
    148     local column
    149     local autoincrement
    150     local primary
    151     local current_primary
    152     local current_autoincrement
    153     local current_opts
    154    
    155     table="${TABLE_DEFINITION[0]}"
    156     count="${#TABLE_DEFINITION[*]}"
    157    
    158     for ((i=1; i<count; i++))
    159     do
    160     line=( ${TABLE_DEFINITION[${i}]} )
    161     column="${line[0]}"
    162     opts="${line[*]:1}"
    163    
    164     case ${opts} in
    165     *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
    166     *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
    167     esac
    168    
    169     case ${opts} in
    170     *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
    171     *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
    172     esac
    173    
    174     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts};" || die "add column '${table}'"
    175     done
    176    
    177     # delete an existing auto_increment first
    178     if [[ -n ${autoincrement} ]]
    179     then
    180     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
    181     current_autoincrement="${i[0]}"
    182     if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
    183     then
    184     current_opts=( "${i[1]}" ) # type eg int(11)
    185     # null or not
    186     case "${i[2]}" in
    187     NO|no) current_opts+=( "NOT NULL" ) ;;
    188     YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
    189     esac
    190     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
    191     fi
    192     fi
    193    
    194     if [[ -n ${primary} ]]
    195     then
    196     # only run this if the primary key is not already the same column
    197     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
    198     current_primary="${i[0]}"
    199     if [[ -n ${current_primary} ]]
    200     then
    201     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
    202     fi
    203     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
    204     fi
    205     if [[ -n ${autoincrement} ]]
    206     then
    207     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
    208     fi
    209    }
    210    
    211    # TABLE_DEFINITION=(
    212    # "table_name"
    213    # "column1_name column1_argvs"
    214    # "column2_name column2_argvs"
    215    # "columnN_name columnN_argvs"
    216    # )
    217    sql_modify_column()
    218    {
    219     local table
    220     local count
    221     local i
    222     local line
    223     local opts
    224     local column
    225     local autoincrement
    226     local primary
    227     local current_primary
    228     local current_autoincrement
    229     local current_opts
    230    
    231     table="${TABLE_DEFINITION[0]}"
    232     count="${#TABLE_DEFINITION[*]}"
    233    
    234     for ((i=1; i<count; i++))
    235     do
    236     line=( ${TABLE_DEFINITION[${i}]} )
    237     column="${line[0]}"
    238     opts="${line[*]:1}"
    239    
    240     case ${opts} in
    241     *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
    242     *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
    243     esac
    244    
    245     case ${opts} in
    246     *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
    247     *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
    248     esac
    249    
    250     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'"
    251     done
    252    
    253     # delete an existing auto_increment first
    254     if [[ -n ${autoincrement} ]]
    255     then
    256     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
    257     current_autoincrement="${i[0]}"
    258     if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
    259     then
    260     current_opts=( "${i[1]}" ) # type eg int(11)
    261     # null or not
    262     case "${i[2]}" in
    263     NO|no) current_opts+=( "NOT NULL" ) ;;
    264     YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
    265     esac
    266     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
    267     fi
    268     fi
    269    
    270     if [[ -n ${primary} ]]
    271     then
    272     # only run this if the primary key is not already the same column
    273     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
    274     current_primary="${i[0]}"
    275     if [[ -n ${current_primary} ]]
    276     then
    277     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
    278     fi
    279     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
    280     fi
    281     if [[ -n ${autoincrement} ]]
    282     then
    283     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
    284     fi
    285    }
    286    
    287  # VALUE_DEFINITION=(  # VALUE_DEFINITION=(
    288  # "table_name, value_column_name, bool_column_name, id_column_name"  # "table_name, value_column_name, bool_column_name, id_column_name"
    289  # "value1, bool1"  # "value1, bool1"
    # Line 178  sql_set_value() Line 339  sql_set_value()
    339    
    340  create_database()  create_database()
    341  {  {
    342     echo "Creating database schema version ${MCORE_SQL_DB_SCHEMA_VERSION}"
    343   create_database_schema   create_database_schema
    344    
    345     # update database schema version
    346     mysqldo "INSERT INTO ${MCORE_SQL_DB}.meta_schema(version) VALUES ('${MCORE_SQL_DB_SCHEMA_VERSION}') ON DUPLICATE KEY UPDATE version='${MCORE_SQL_DB_SCHEMA_VERSION}';" || die "updating schema version"
    347  }  }
    348    
    349  update_database()  update_database()
    350  {  {
    351   local current_sql_db_version   local current_sql_db_schema_version
    352   local i   local i
    353    
    354   current_sql_db_version=$(mysqldo "SELECT MAX(version) from meta;")   current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;")
    355    
    356   if [[ ${current_sql_db_version} = ${MCORE_SQL_DB_VERSION} ]]   if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]]
    357   then   then
    358   echo "Database is already up to date"   echo "Database schema is already up to date"
    359   return 0   return 0
    360   fi   fi
    361    
    362   # run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_VERSION   # run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_SCHEMA_VERSION
    363   for i in $(seq $((${current_sql_db_version}+1)) ${MCORE_SQL_DB_VERSION})   for i in $(seq $((${current_sql_db_schema_version}+1)) ${MCORE_SQL_DB_SCHEMA_VERSION})
    364   do   do
    365   if [[ -n $(typeset -f update_to_database_version_${i}) ]]   if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]]
    366   then   then
    367   echo "Running update_to_database_schema_version_${i}()"   echo "Running update_to_database_schema_version_${i}()"
    368   update_to_database_schema_version_${i}   update_to_database_schema_version_${i} || die
    369   fi   fi
    370   done   done
    371    
    372     # update database schema version
    373     mysqldo "INSERT INTO ${MCORE_SQL_DB}.meta_schema(version) VALUES ('${MCORE_SQL_DB_SCHEMA_VERSION}') ON DUPLICATE KEY UPDATE version='${MCORE_SQL_DB_SCHEMA_VERSION}';" || die "updating schema version"
    374  }  }
    375    
    376  fill_database()  fill_database()
    377  {  {
    378     local current_sql_db_values_version
    379     local i
    380    
    381     current_sql_db_values_version=$(mysqldo "SELECT MAX(version) from meta_values;")
    382    
    383     if [[ ${current_sql_db_values_version} = ${MCORE_SQL_DB_VALUES_VERSION} ]]
    384     then
    385     echo "Database values are already up to date"
    386     return 0
    387     fi
    388    
    389     echo "Updating database default values to version ${MCORE_SQL_DB_VALUES_VERSION}"
    390   create_database_values   create_database_values
    391     # update database values version
    392     mysqldo "INSERT INTO ${MCORE_SQL_DB}.meta_values(version) VALUES ('${MCORE_SQL_DB_VALUES_VERSION}') ON DUPLICATE KEY UPDATE version='${MCORE_SQL_DB_VALUES_VERSION}';" || die "updating values version"
    393  }  }
    394    
    395  case $1 in  case $1 in
    Legend:
    Removed from v.8490  
    changed lines
      Added in v.8552