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 8493 by niro, Fri Feb 5 13:18:40 2016 UTC revision 8547 by niro, Mon Feb 8 13:09:17 2016 UTC
    # Line 125  sql_create_table() Line 125  sql_create_table()
    125    
    126  # TABLE_DEFINITION=(  # TABLE_DEFINITION=(
    127  # "table_name"  # "table_name"
    128  # "column1_name after_column column1_argvs"  # "column1_name column1_argvs"
    129  # "column2_name after_column column2_argvs"  # "column2_name column2_argvs"
    130  # "columnN_name after_column columnN_argvs"  # "columnN_name columnN_argvs"
    131    #
    132    # eg.    "description varchar(255) DEFAULT NULL AFTER value"
    133  # )  # )
    134    #
    135  sql_add_column()  sql_add_column()
    136  {  {
    137   local table   local table
    # Line 139  sql_add_column() Line 142  sql_add_column()
    142   local column   local column
    143   local autoincrement   local autoincrement
    144   local primary   local primary
    145     local current_primary
    146     local current_autoincrement
    147     local current_opts
    148    
    149   table="${TABLE_DEFINITION[0]}"   table="${TABLE_DEFINITION[0]}"
    150   count="${#TABLE_DEFINITION[*]}"   count="${#TABLE_DEFINITION[*]}"
    # Line 147  sql_add_column() Line 153  sql_add_column()
    153   do   do
    154   line=( ${TABLE_DEFINITION[${i}]} )   line=( ${TABLE_DEFINITION[${i}]} )
    155   column="${line[0]}"   column="${line[0]}"
    156   after="${line[1]}"   opts="${line[*]:1}"
      opts="${line[*]:2}"  
    157    
    158   case ${opts} in   case ${opts} in
    159   *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;   *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
    # Line 160  sql_add_column() Line 165  sql_add_column()
    165   *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;   *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
    166   esac   esac
    167    
    168   mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts}) AFTER ${after_column};" || die "add column '${table}'"   mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts};" || die "add column '${table}'"
    169   done   done
    170    
    171     # delete an existing auto_increment first
    172     if [[ -n ${autoincrement} ]]
    173     then
    174     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
    175     current_autoincrement="${i[0]}"
    176     if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
    177     then
    178     current_opts=( "${i[1]}" ) # type eg int(11)
    179     # null or not
    180     case "${i[2]}" in
    181     NO|no) current_opts+=( "NOT NULL" ) ;;
    182     YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
    183     esac
    184     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
    185     fi
    186     fi
    187    
    188   if [[ -n ${primary} ]]   if [[ -n ${primary} ]]
    189   then   then
    190     # only run this if the primary key is not already the same column
    191     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
    192     current_primary="${i[0]}"
    193     if [[ -n ${current_primary} ]]
    194     then
    195     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
    196     fi
    197   mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"   mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
    198   fi   fi
    199   if [[ -n ${autoincrement} ]]   if [[ -n ${autoincrement} ]]
    # Line 189  sql_modify_column() Line 218  sql_modify_column()
    218   local column   local column
    219   local autoincrement   local autoincrement
    220   local primary   local primary
    221     local current_primary
    222     local current_autoincrement
    223     local current_opts
    224    
    225   table="${TABLE_DEFINITION[0]}"   table="${TABLE_DEFINITION[0]}"
    226   count="${#TABLE_DEFINITION[*]}"   count="${#TABLE_DEFINITION[*]}"
    # Line 209  sql_modify_column() Line 241  sql_modify_column()
    241   *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;   *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
    242   esac   esac
    243    
    244   mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts});" || die "modify table '${table}'"   mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'"
    245   done   done
    246    
    247     # delete an existing auto_increment first
    248     if [[ -n ${autoincrement} ]]
    249     then
    250     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
    251     current_autoincrement="${i[0]}"
    252     if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
    253     then
    254     current_opts=( "${i[1]}" ) # type eg int(11)
    255     # null or not
    256     case "${i[2]}" in
    257     NO|no) current_opts+=( "NOT NULL" ) ;;
    258     YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
    259     esac
    260     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
    261     fi
    262     fi
    263    
    264   if [[ -n ${primary} ]]   if [[ -n ${primary} ]]
    265   then   then
    266     # only run this if the primary key is not already the same column
    267     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
    268     current_primary="${i[0]}"
    269     if [[ -n ${current_primary} ]]
    270     then
    271     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
    272     fi
    273   mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"   mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
    274   fi   fi
    275   if [[ -n ${autoincrement} ]]   if [[ -n ${autoincrement} ]]
    # Line 277  sql_set_value() Line 333  sql_set_value()
    333    
    334  create_database()  create_database()
    335  {  {
    336     echo "Creating database schema version ${MCORE_SQL_DB_SCHEMA_VERSION}"
    337   create_database_schema   create_database_schema
    338    
    339     # update database schema version
    340     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"
    341  }  }
    342    
    343  update_database()  update_database()
    344  {  {
    345   local current_sql_db_version   local current_sql_db_schema_version
    346   local i   local i
    347    
    348   current_sql_db_version=$(mysqldo "SELECT MAX(version) from meta;")   current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;")
    349    
    350   if [[ ${current_sql_db_version} = ${MCORE_SQL_DB_VERSION} ]]   if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]]
    351   then   then
    352   echo "Database is already up to date"   echo "Database schema is already up to date"
    353   return 0   return 0
    354   fi   fi
    355    
    356   # 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
    357   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})
    358   do   do
    359   if [[ -n $(typeset -f update_to_database_version_${i}) ]]   if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]]
    360   then   then
    361   echo "Running update_to_database_schema_version_${i}()"   echo "Running update_to_database_schema_version_${i}()"
    362   update_to_database_schema_version_${i}   update_to_database_schema_version_${i} || die
    363   fi   fi
    364   done   done
    365    
    366     # update database schema version
    367     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"
    368  }  }
    369    
    370  fill_database()  fill_database()
    371  {  {
    372     local current_sql_db_values_version
    373     local i
    374    
    375     current_sql_db_values_version=$(mysqldo "SELECT MAX(version) from meta_values;")
    376    
    377     if [[ ${current_sql_db_values_version} = ${MCORE_SQL_DB_VALUES_VERSION} ]]
    378     then
    379     echo "Database values are already up to date"
    380     return 0
    381     fi
    382    
    383     echo "Updating database default values to version ${MCORE_SQL_DB_VALUES_VERSION}"
    384   create_database_values   create_database_values
    385     # update database values version
    386     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"
    387  }  }
    388    
    389  case $1 in  case $1 in
    Legend:
    Removed from v.8493  
    changed lines
      Added in v.8547