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 8509 by niro, Mon Feb 8 08:52:47 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		echo "DEBUG: i='${i[*]}'"
252		current_autoincrement="${i[0]}"
253		if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
254		then
255		current_opts=( "${i[1]}" ) # type eg int(11)
256		# null or not
257		case "${i[2]}" in
258		NO|no) current_opts+=( "NOT NULL" ) ;;
259		YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
260		esac
261		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
262		fi
263		fi
264
265		if [[ -n ${primary} ]]
266		then
267		# only run this if the primary key is not already the same column
268		i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
269		current_primary="${i[0]}"
270		if [[ -n ${current_primary} ]]
271		then
272		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
273		fi
274		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
275		fi
276		if [[ -n ${autoincrement} ]]
277		then
278	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}'"
279	fi	fi
280	}	}
#	Line 171  sql_set_value()	Line 324  sql_set_value()
324	if [[ -n ${id} ]]	if [[ -n ${id} ]]
325	then	then
326	# 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}';"
327	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}"
328	else	else
329	# insert into the database	# insert into the database
	echo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );"
330	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}"
331	fi	fi
332	done	done
333	}	}
334
	update_to_database_schema_version_2()
	{
	return 0
	}
	update_to_database_schema_version_3()
	{
	return 0
	}
335	create_database()	create_database()
336	{	{
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	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.8509