Re: [Phpwiki-talk] Jeff's hacks are in the CVS repository.

[Reini U. <ru...@x-...>]

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/