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 8505 by niro, Fri Feb 5 15:16:07 2016 UTC
    # Line 90  sql_create_table() Line 90  sql_create_table()
    90    
    91   for ((i=1; i<count; i++))   for ((i=1; i<count; i++))
    92   do   do
    93   line="${TABLE_DEFINITION[${i}]}"   line=( ${TABLE_DEFINITION[${i}]} )
    94   column="${line%% *}"   column="${line[0]}"
    95   opts="${line/${column} /}"   opts="${line[*]:1}"
    96    
    97   case ${opts} in   case ${opts} in
    98   *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;   *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
    # Line 123  sql_create_table() Line 123  sql_create_table()
    123   fi   fi
    124  }  }
    125    
    126    # TABLE_DEFINITION=(
    127    # "table_name"
    128    # "column1_name after_column column1_argvs"
    129    # "column2_name after_column column2_argvs"
    130    # "columnN_name after_column columnN_argvs"
    131    # )
    132    sql_add_column()
    133    {
    134     local table
    135     local count
    136     local i
    137     local line
    138     local opts
    139     local column
    140     local autoincrement
    141     local primary
    142     local current_primary
    143     local current_autoincrement
    144     local current_opts
    145    
    146     table="${TABLE_DEFINITION[0]}"
    147     count="${#TABLE_DEFINITION[*]}"
    148    
    149     for ((i=1; i<count; i++))
    150     do
    151     line=( ${TABLE_DEFINITION[${i}]} )
    152     column="${line[0]}"
    153     after="${line[1]}"
    154     opts="${line[*]:2}"
    155    
    156     case ${opts} in
    157     *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
    158     *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
    159     esac
    160    
    161     case ${opts} in
    162     *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
    163     *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
    164     esac
    165    
    166     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts} AFTER ${after_column};" || die "add column '${table}'"
    167     done
    168    
    169     # delete an existing auto_increment first
    170     if [[ -n ${autoincrement} ]]
    171     then
    172     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
    173     current_autoincrement="${i[0]}"
    174     if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
    175     then
    176     current_opts=( "${i[1]}" ) # type eg int(11)
    177     # null or not
    178     case "${i[2]}" in
    179     NO|no) current_opts+=( "NOT NULL" ) ;;
    180     YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
    181     esac
    182     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
    183     fi
    184     fi
    185    
    186     if [[ -n ${primary} ]]
    187     then
    188     # only run this if the primary key is not already the same column
    189     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
    190     current_primary="${i[0]}"
    191     if [[ -n ${current_primary} ]]
    192     then
    193     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
    194     fi
    195     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
    196     fi
    197     if [[ -n ${autoincrement} ]]
    198     then
    199     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
    200     fi
    201    }
    202    
    203    # TABLE_DEFINITION=(
    204    # "table_name"
    205    # "column1_name column1_argvs"
    206    # "column2_name column2_argvs"
    207    # "columnN_name columnN_argvs"
    208    # )
    209    sql_modify_column()
    210    {
    211     local table
    212     local count
    213     local i
    214     local line
    215     local opts
    216     local column
    217     local autoincrement
    218     local primary
    219     local current_primary
    220     local current_autoincrement
    221     local current_opts
    222    
    223     table="${TABLE_DEFINITION[0]}"
    224     count="${#TABLE_DEFINITION[*]}"
    225    
    226     for ((i=1; i<count; i++))
    227     do
    228     line=( ${TABLE_DEFINITION[${i}]} )
    229     column="${line[0]}"
    230     opts="${line[*]:1}"
    231    
    232     case ${opts} in
    233     *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
    234     *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
    235     esac
    236    
    237     case ${opts} in
    238     *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
    239     *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
    240     esac
    241    
    242     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'"
    243     done
    244    
    245     # delete an existing auto_increment first
    246     if [[ -n ${autoincrement} ]]
    247     then
    248     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
    249     echo "DEBUG: i='${i[*]}'"
    250     current_autoincrement="${i[0]}"
    251     if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
    252     then
    253     current_opts=( "${i[1]}" ) # type eg int(11)
    254     # null or not
    255     case "${i[2]}" in
    256     NO|no) current_opts+=( "NOT NULL" ) ;;
    257     YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
    258     esac
    259     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
    260     fi
    261     fi
    262    
    263     if [[ -n ${primary} ]]
    264     then
    265     # only run this if the primary key is not already the same column
    266     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
    267     current_primary="${i[0]}"
    268     if [[ -n ${current_primary} ]]
    269     then
    270     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
    271     fi
    272     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
    273     fi
    274     if [[ -n ${autoincrement} ]]
    275     then
    276     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
    277     fi
    278    }
    279    
    280  # VALUE_DEFINITION=(  # VALUE_DEFINITION=(
    281  # "table_name, value_column_name, bool_column_name, id_column_name"  # "table_name, value_column_name, bool_column_name, id_column_name"
    282  # "value1, bool1"  # "value1, bool1"
    # Line 179  sql_set_value() Line 333  sql_set_value()
    333  create_database()  create_database()
    334  {  {
    335   create_database_schema   create_database_schema
    336    
    337     # update database schema version
    338     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"
    339  }  }
    340    
    341  update_database()  update_database()
    342  {  {
    343   local current_sql_db_version   local current_sql_db_schema_version
    344   local i   local i
    345    
    346   current_sql_db_version=$(mysqldo "SELECT MAX(version) from meta;")   current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;")
    347    
    348   if [[ ${current_sql_db_version} = ${MCORE_SQL_DB_VERSION} ]]   if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]]
    349   then   then
    350   echo "Database is already up to date"   echo "Database schema is already up to date"
    351   return 0   return 0
    352   fi   fi
    353    
    354   # 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
    355   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})
    356   do   do
    357   if [[ -n $(typeset -f update_to_database_version_${i}) ]]   if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]]
    358   then   then
    359   echo "Running update_to_database_schema_version_${i}()"   echo "Running update_to_database_schema_version_${i}()"
    360   update_to_database_schema_version_${i}   update_to_database_schema_version_${i} || die
    361   fi   fi
    362   done   done
    363    
    364     # update database schema version
    365     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"
    366  }  }
    367    
    368  fill_database()  fill_database()
    369  {  {
    370     local current_sql_db_values_version
    371     local i
    372    
    373     current_sql_db_values_version=$(mysqldo "SELECT MAX(version) from meta_values;")
    374    
    375     if [[ ${current_sql_db_values_version} = ${MCORE_SQL_DB_VALUES_VERSION} ]]
    376     then
    377     echo "Database values are already up to date"
    378     return 0
    379     fi
    380    
    381   create_database_values   create_database_values
    382     # update database values version
    383     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"
    384  }  }
    385    
    386  case $1 in  case $1 in
    Legend:
    Removed from v.8490  
    changed lines
      Added in v.8505