From: Reini U. <ru...@x-...> - 2001-09-18 20:20:51
|
Jeff Dairiki schrieb: > WARNING: all database schemas (currently MySQL, Postgres and DBA > support is working) use completely revised schema, so you must > start this new code with a new blank database... db_upgrade-1.3.sh or db_upgrade-1.3.php anyone? In a recent mysql upgrade for another e-commerce project I wrote a shell script which ran a mysql query which created a mysql query which updated the mysql db. I'll try to use this trick for PHPWIKI. Simplier than a php script. p2c_simplify: #!/bin/sh TEP_DB=catalog MYSQL_USER=rurban MYSQL_PASSWD= MYSQL_DATA=/var/mysql # create an update query on the fly. MYSQL_CMD="mysql -u$MYSQL_USER" if [ ! -z $MYSQL_PASSWD ]; then MYSQL_CMD="$MYSQL_CMD -p$MYSQL_PASSWD"; fi TMPFILE=$MYSQL_DATA/tmp.sql rm $TMPFILE ./tmp.sql $MYSQL_CMD $TEP_DB <p2c_simplify.sql mv $TMPFILE . $MYSQL_CMD $TEP_DB <tmp.sql p2c_simplify.sql: ## move essentially unsupported n:m products : categories into 1:n ## remove products_to_categories ## Usage: mysql db <p2c_simplify.sql ALTER TABLE products ADD COLUMN categories_id int(5) default '0' not null; ALTER TABLE products ADD INDEX categories_id (categories_id); ## hacking around stupid mysql limitations: ##UPDATE products AS p LEFT JOIN products_to_categories AS p2c USING (products_id) SET p.categories_id = p2c.categories_id; SELECT "UPDATE products SET categories_id=",p2c.categories_id," WHERE products_id=",p.products_id,";" INTO OUTFILE './tmp.sql' FROM products as p LEFT JOIN products_to_categories as p2c ON p.products_id = p2c.products_id; # Got the idea? -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ |