mysql’s --xml and some XSLT
Somebody asked in Freenode the other day how to get their data out of MySQL into a specific XML format.
Both mysqldump and the mysql client have --xml options. The output from the cli looks like this:
<resultset statement="SELECT here">
<row>
<field name="field1">value</field>
<field name="field2">value</field>
</row>
<row>
<field name="field1">value</field>
<field name="field2">value</field>
</row>
</resultset>
I wanted to change those <field name="fieldName"> into <fieldName> tags, and all it takes is a little XSLT, which I've uploaded here.
On Linux you can do something like this:
mysql -e "SELECT * FROM dbname.tablename" | xsltproc mysql.xsl -
Make an ALTER TABLE script with MySQL Workbench
So you have two versions of a database sitting around - maybe you've been trying things out on your development box, and now you want to move it all over to production. If it's just a new table here and an extra column there, you could do it by hand, but if you've made lots of changes, it would be nice to let the computer do it for you.
Enter MySQL Workbench.
Trigger on firing table
CREATE TABLE t1 (id int); CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t1 VALUES (NEW.id);
That's not allowed; it won't let you create the trigger.
CREATE TABLE t1 (id int); CREATE TABLE f1 (id int) ENGINE=FEDERATED CONNECTION='localhost/test/t1'; CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW INSERT INTO f1 VALUES (NEW.id);
The server lets that through. Don't do it.