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 8504 by niro, Fri Feb 5 15:14:21 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     echo "DEBUG: i='${i[*]}'"
    174     current_autoincrement="${i[0]}"
    175     if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
    176     then
    177     current_opts=( "${i[1]}" ) # type eg int(11)
    178     # null or not
    179     case "${i[2]}" in
    180     NO|no) current_opts+=( "NOT NULL" ) ;;
    181     YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
    182     esac
    183     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
    184     fi
    185     fi
    186    
    187     if [[ -n ${primary} ]]
    188     then
    189     # only run this if the primary key is not already the same column
    190     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
    191     current_primary="${i[0]}"
    192     if [[ -n ${current_primary} ]]
    193     then
    194     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
    195     fi
    196     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
    197     fi
    198     if [[ -n ${autoincrement} ]]
    199     then
    200     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
    201     fi
    202    }
    203    
    204    # TABLE_DEFINITION=(
    205    # "table_name"
    206    # "column1_name column1_argvs"
    207    # "column2_name column2_argvs"
    208    # "columnN_name columnN_argvs"
    209    # )
    210    sql_modify_column()
    211    {
    212     local table
    213     local count
    214     local i
    215     local line
    216     local opts
    217     local column
    218     local autoincrement
    219     local primary
    220     local current_primary
    221     local current_autoincrement
    222     local current_opts
    223    
    224     table="${TABLE_DEFINITION[0]}"
    225     count="${#TABLE_DEFINITION[*]}"
    226    
    227     for ((i=1; i<count; i++))
    228     do
    229     line=( ${TABLE_DEFINITION[${i}]} )
    230     column="${line[0]}"
    231     opts="${line[*]:1}"
    232    
    233     case ${opts} in
    234     *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
    235     *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
    236     esac
    237    
    238     case ${opts} in
    239     *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
    240     *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
    241     esac
    242    
    243     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'"
    244     done
    245    
    246     # delete an existing auto_increment first
    247     if [[ -n ${autoincrement} ]]
    248     then
    249     i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
    250     echo "DEBUG: i='${i[*]}'"
    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} ]]
    265     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}'"
    274     fi
    275     if [[ -n ${autoincrement} ]]
    276     then
    277     mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
    278     fi
    279    }
    280    
    281  # VALUE_DEFINITION=(  # VALUE_DEFINITION=(
    282  # "table_name, value_column_name, bool_column_name, id_column_name"  # "table_name, value_column_name, bool_column_name, id_column_name"
    283  # "value1, bool1"  # "value1, bool1"
    # Line 179  sql_set_value() Line 334  sql_set_value()
    334  create_database()  create_database()
    335  {  {
    336   create_database_schema   create_database_schema
    337    
    338     # update database schema version
    339     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"
    340  }  }
    341    
    342  update_database()  update_database()
    343  {  {
    344   local current_sql_db_version   local current_sql_db_schema_version
    345   local i   local i
    346    
    347   current_sql_db_version=$(mysqldo "SELECT MAX(version) from meta;")   current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;")
    348    
    349   if [[ ${current_sql_db_version} = ${MCORE_SQL_DB_VERSION} ]]   if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]]
    350   then   then
    351   echo "Database is already up to date"   echo "Database schema is already up to date"
    352   return 0   return 0
    353   fi   fi
    354    
    355   # 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
    356   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})
    357   do   do
    358   if [[ -n $(typeset -f update_to_database_version_${i}) ]]   if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]]
    359   then   then
    360   echo "Running update_to_database_schema_version_${i}()"   echo "Running update_to_database_schema_version_${i}()"
    361   update_to_database_schema_version_${i}   update_to_database_schema_version_${i} || die
    362   fi   fi
    363   done   done
    364    
    365     # update database schema version
    366     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"
    367  }  }
    368    
    369  fill_database()  fill_database()
    370  {  {
    371     local current_sql_db_values_version
    372     local i
    373    
    374     current_sql_db_values_version=$(mysqldo "SELECT MAX(version) from meta_values;")
    375    
    376     if [[ ${current_sql_db_values_version} = ${MCORE_SQL_DB_VALUES_VERSION} ]]
    377     then
    378     echo "Database values are already up to date"
    379     return 0
    380     fi
    381    
    382   create_database_values   create_database_values
    383     # update database values version
    384     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"
    385  }  }
    386    
    387  case $1 in  case $1 in
    Legend:
    Removed from v.8490  
    changed lines
      Added in v.8504