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

Parent Directory | Revision Log | Patch

	revision 8488 by niro, Fri Feb 5 12:29:42 2016 UTC	revision 8531 by niro, Mon Feb 8 10:37:04 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 111  sql_create_table()	Line 111  sql_create_table()
111	create+=", ${column} ${opts}"	create+=", ${column} ${opts}"
112	fi	fi
113	done	done
	echo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );"
114	mysqldo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" || die "creating table '${table}'"	mysqldo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" || die "creating table '${table}'"
115
116	if [[ -n ${primary} ]]	if [[ -n ${primary} ]]
117	then	then
	echo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});"
118	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}'"
119	fi	fi
120	if [[ -n ${autoincrement} ]]	if [[ -n ${autoincrement} ]]
121	then	then
122	echo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;"	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
123		fi
124		}
125
126		# TABLE_DEFINITION=(
127		# "table_name"
128		# "column1_name column1_argvs"
129		# "column2_name column2_argvs"
130		# "columnN_name columnN_argvs"
131		#
132		# eg.    "description varchar(255) DEFAULT NULL AFTER value"
133		# )
134		#
135		sql_add_column()
136		{
137		local table
138		local count
139		local i
140		local line
141		local opts
142		local column
143		local autoincrement
144		local primary
145		local current_primary
146		local current_autoincrement
147		local current_opts
148
149		table="${TABLE_DEFINITION[0]}"
150		count="${#TABLE_DEFINITION[*]}"
151
152		for ((i=1; i<count; i++))
153		do
154		line=( ${TABLE_DEFINITION[${i}]} )
155		column="${line[0]}"
156		opts="${line[*]:1}"
157
158		case ${opts} in
159		*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
160		*"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
161		esac
162
163		case ${opts} in
164		*AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
165		*auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
166		esac
167
168		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts};" || die "add column '${table}'"
169		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} ]]
189		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}'"
198		fi
199		if [[ -n ${autoincrement} ]]
200		then
201		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
202		fi
203		}
204
205		# TABLE_DEFINITION=(
206		# "table_name"
207		# "column1_name column1_argvs"
208		# "column2_name column2_argvs"
209		# "columnN_name columnN_argvs"
210		# )
211		sql_modify_column()
212		{
213		local table
214		local count
215		local i
216		local line
217		local opts
218		local column
219		local autoincrement
220		local primary
221		local current_primary
222		local current_autoincrement
223		local current_opts
224
225		table="${TABLE_DEFINITION[0]}"
226		count="${#TABLE_DEFINITION[*]}"
227
228		for ((i=1; i<count; i++))
229		do
230		line=( ${TABLE_DEFINITION[${i}]} )
231		column="${line[0]}"
232		opts="${line[*]:1}"
233
234		case ${opts} in
235		*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
236		*"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
237		esac
238
239		case ${opts} in
240		*AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
241		*auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
242		esac
243
244		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'"
245		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} ]]
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}'"	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
278	fi	fi
279	}	}
#	Line 171  sql_set_value()	Line 323  sql_set_value()
323	if [[ -n ${id} ]]	if [[ -n ${id} ]]
324	then	then
325	# update value in database	# update value in database
	echo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';"
326	mysqldo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" || die "update value in table ${table}"	mysqldo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" || die "update value in table ${table}"
327	else	else
328	# insert into the database	# insert into the database
	echo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );"
329	mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}"	mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}"
330	fi	fi
331	done	done
332	}	}
333
	update_to_database_schema_version_2()
	{
	return 0
	}
	update_to_database_schema_version_3()
	{
	return 0
	}
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		echo "Updating database default values to version ${MCORE_SQL_DB_VALUES_VERSION}"
383	create_database_values	create_database_values
384		# update database values version
385		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"
386	}	}
387
388	case $1 in	case $1 in

---

Colored Diff Long Colored Diff Full Colored Diff Unidiff Context Diff Side by Side

Legend: Removed from v.8488   changed lines   Added in v.8531