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 8502 by niro, Fri Feb 5 14:32:57 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 125  sql_create_table() Line 131  sql_create_table()
    131    
    132  # TABLE_DEFINITION=(  # TABLE_DEFINITION=(
    133  # "table_name"  # "table_name"
    134  # "column1_name after_column column1_argvs"  # "column1_name column1_argvs"
    135  # "column2_name after_column column2_argvs"  # "column2_name column2_argvs"
    136  # "columnN_name after_column columnN_argvs"  # "columnN_name columnN_argvs"
    137    #
    138    # eg.    "description varchar(255) DEFAULT NULL AFTER value"
    139  # )  # )
    140    #
    141  sql_add_column()  sql_add_column()
    142  {  {
    143   local table   local table
    # Line 139  sql_add_column() Line 148  sql_add_column()
    148   local column   local column
    149   local autoincrement   local autoincrement
    150   local primary   local primary
    151     local current_primary
    152     local current_autoincrement
    153     local current_opts
    154    
    155   table="${TABLE_DEFINITION[0]}"   table="${TABLE_DEFINITION[0]}"
    156   count="${#TABLE_DEFINITION[*]}"   count="${#TABLE_DEFINITION[*]}"
    # Line 147  sql_add_column() Line 159  sql_add_column()
    159   do   do
    160   line=( ${TABLE_DEFINITION[${i}]} )   line=( ${TABLE_DEFINITION[${i}]} )
    161   column="${line[0]}"   column="${line[0]}"
    162   after="${line[1]}"   opts="${line[*]:1}"
      opts="${line[*]:2}"  
    163    
    164   case ${opts} in   case ${opts} in
    165   *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;   *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
    # Line 160  sql_add_column() Line 171  sql_add_column()
    171   *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;   *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
    172   esac   esac
    173    
    174   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}'"
    175   done   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} ]]   if [[ -n ${primary} ]]
    195   then   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}'"   mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
    204   fi   fi
    205   if [[ -n ${autoincrement} ]]   if [[ -n ${autoincrement} ]]
    # Line 190  sql_modify_column() Line 225  sql_modify_column()
    225   local autoincrement   local autoincrement
    226   local primary   local primary
    227   local current_primary   local current_primary
    228     local current_autoincrement
    229     local current_opts
    230    
    231   table="${TABLE_DEFINITION[0]}"   table="${TABLE_DEFINITION[0]}"
    232   count="${#TABLE_DEFINITION[*]}"   count="${#TABLE_DEFINITION[*]}"
    # Line 213  sql_modify_column() Line 250  sql_modify_column()
    250   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}'"
    251   done   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} ]]   if [[ -n ${primary} ]]
    271   then   then
    272   # only run this if the primary key is not already the same column   # 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"') )   i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
    274   current_primary="${i[0]}"   current_primary="${i[0]}"
    275   if [[ ${current_primary} != ${primary} ]]   if [[ -n ${current_primary} ]]
    276   then   then
    277   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} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
    278   fi   fi
    279     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
    280   fi   fi
    281   if [[ -n ${autoincrement} ]]   if [[ -n ${autoincrement} ]]
    282   then   then
    # Line 284  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   # update database schema version
    # Line 299  update_database() Line 355  update_database()
    355    
    356   if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_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    
    # Line 330  fill_database() Line 386  fill_database()
    386   return 0   return 0
    387   fi   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   # 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"   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"
    Legend:
    Removed from v.8502  
    changed lines
      Added in v.8552