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

Parent Directory | Revision Log | Patch

	revision 8485 by niro, Fri Feb 5 12:06:33 2016 UTC	revision 8497 by niro, Fri Feb 5 13:48:23 2016 UTC
#	Line 64  mysqldo()	Line 64  mysqldo()
64	&& return 0 || return 1	&& return 0 || return 1
65	}	}
66
	mysql_insert()
	{
	local cmdline="$@"
	#local table="$1"
	local table="${cmdline%%,*}"
	local argv
	local insert_line
	local update_line
	local key
	local value
	local counter
	OLD_IFS=${IFS}
	IFS=,
	declare -i counter=0
	#for argv in ${cmdline/$1/} # first value is the table_name
	for argv in ${cmdline#*,} # first value is the table_name
	do
	key="${argv%=*}"
	value="${argv#*=}"
	if [ ${counter} -eq 0 ]
	then
	insert_keys="${key}"
	insert_values="'${value}'"
	update_line="${key}='${value}'"
	else
	insert_keys+=",${key}"
	insert_values+=",'${value}'"
	update_line+=",${key}='${value}'"
	fi
	(( counter ++ ))
	done
	IFS=${OLD_IFS}
	echo "INSERT INTO ${table} (${insert_keys}) VALUES(${insert_values}) ON DUPLICATE KEY UPDATE ${update_line};"
	#mysqldo "insert into ${table} (${insert_keys}) values(${insert_values}) on duplicate key update ${update_line};"
	}
67	# creates a mysql table with help of a defined array TABLE_DEFINITION:	# creates a mysql table with help of a defined array TABLE_DEFINITION:
68	# array values are a struct like this:	# array values are a struct like this:
69	# first array element is the table name, everything else are column descriptions	# first array element is the table name, everything else are column descriptions
#	Line 130  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 151  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 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
143		table="${TABLE_DEFINITION[0]}"
144		count="${#TABLE_DEFINITION[*]}"
145
146		for ((i=1; i<count; i++))
147		do
148		line=( ${TABLE_DEFINITION[${i}]} )
149		column="${line[0]}"
150		after="${line[1]}"
151		opts="${line[*]:2}"
152
153		case ${opts} in
154		*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
155		*"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
156		esac
157
158		case ${opts} in
159		*AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
160		*auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
161		esac
162
163		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts}) AFTER ${after_column};" || die "add column '${table}'"
164		done
165
166		if [[ -n ${primary} ]]
167		then
168		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
169		fi
170		if [[ -n ${autoincrement} ]]
171		then
172		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
173		fi
174		}
175
176		# TABLE_DEFINITION=(
177		# "table_name"
178		# "column1_name column1_argvs"
179		# "column2_name column2_argvs"
180		# "columnN_name columnN_argvs"
181		# )
182		sql_modify_column()
183		{
184		local table
185		local count
186		local i
187		local line
188		local opts
189		local column
190		local autoincrement
191		local primary
192
193		table="${TABLE_DEFINITION[0]}"
194		count="${#TABLE_DEFINITION[*]}"
195
196		for ((i=1; i<count; i++))
197		do
198		line=( ${TABLE_DEFINITION[${i}]} )
199		column="${line[0]}"
200		opts="${line[*]:1}"
201
202		case ${opts} in
203		*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
204		*"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
205		esac
206
207		case ${opts} in
208		*AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
209		*auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
210		esac
211
212		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts});" || die "modify table '${table}'"
213		done
214
215		if [[ -n ${primary} ]]
216		then
217		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
218		fi
219		if [[ -n ${autoincrement} ]]
220		then
221	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}'"
222	fi	fi
223	}	}
#	Line 211  sql_set_value()	Line 267  sql_set_value()
267	if [[ -n ${id} ]]	if [[ -n ${id} ]]
268	then	then
269	# 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}';"
270	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}"
271	else	else
272	# insert into the database	# insert into the database
	echo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );"
273	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}"
274	fi	fi
275	done	done
276	}	}
277
	update_to_database_schema_version_2()
	{
	return 0
	}
	update_to_database_schema_version_3()
	{
	return 0
	}
278	create_database()	create_database()
279	{	{
280	create_database_schema	create_database_schema
281
282		# update database version
283		mysqldo "INSERT INTO ${MCORE_SQL_DB}.meta(version) VALUES ('${MCORE_SQL_DB_SCHEMA_VERSION}') ON DUPLICATE KEY UPDATE version='${MCORE_SQL_DB_SCHEMA_VERSION}';" || die "updating version"
284	}	}
285
286	update_database()	update_database()
287	{	{
288	local current_sql_db_version	local current_sql_db_schema_version
289	local i	local i
290
291	current_sql_db_version=$(mysqldo "SELECT MAX(version) from meta;")	current_sql_db_schema_version=$(mysqldo "SELECT MAX(schema_version) from meta;")
292
293	if [[ ${current_sql_db_version} = ${MCORE_SQL_DB_VERSION} ]]	if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]]
294	then	then
295	echo "Database is already up to date"	echo "Database is already up to date"
296	return 0	return 0
297	fi	fi
298
299	# 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
300	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})
301	do	do
302	if [[ -n $(typeset -f update_to_database_version_${i}) ]]	if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]]
303	then	then
304	echo "Running update_to_database_schema_version_${i}()"	echo "Running update_to_database_schema_version_${i}()"
305	update_to_database_schema_version_${i}	update_to_database_schema_version_${i} || die
306	fi	fi
307	done	done
308
309		# update database version
310		mysqldo "INSERT INTO ${MCORE_SQL_DB}.meta(version) VALUES ('${MCORE_SQL_DB_SCHEMA_VERSION}') ON DUPLICATE KEY UPDATE version='${MCORE_SQL_DB_SCHEMA_VERSION}';" || die "updating version"
311	}	}
312
313	fill_database()	fill_database()

---

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

Legend: Removed from v.8485   changed lines   Added in v.8497