We recently installed a server dedicated to unit tests, which deploys
updates automatically via Jenkins when commits are done, and sends
mails when a regression is noticed
> This requires our database to always be up-to-dateSince the database-schema-reference is our MWB, we added some scripts
during deploy, which export the .mwb to a .sql (using python) This
worked fine… but still has some issues
Our main concern is that the functions attached to the schema are not exported at all, which makes the DB unusable.
We'd like to hack into the python code to make it export scripts… but didn't find enough informations about it.
Here is the only piece of documentation we found. It's not very clear for us. We didn't find any information about exporting scripts.
All we found is that a db_Script class exists. We don't know where we can find its instances in our execution context, nor if they can be exported easily. Did we miss something ?
For reference, here is the script we currently use for the mwb to sql conversion (mwb2sql.sh).
It calls the MySqlWorkbench from command line (we use a dummy x-server to flush graphical output.)
What we need to complete is the python part passed in our command-line call of workbench.
# generate sql from mwb
# usage: sh mwb2sql.sh {mwb file} {output file}
# prepare: set env MYSQL_WORKBENCH
if [ "$MYSQL_WORKBENCH" = "" ]; then
export MYSQL_WORKBENCH="/usr/bin/mysql-workbench"
fi
export INPUT=$(cd $(dirname $1);pwd)/$(basename $1)
export OUTPUT=$(cd $(dirname $2);pwd)/$(basename $2)
"$MYSQL_WORKBENCH" \
--open $INPUT \
--run-python "
import os
import grt
from grt.modules import DbMySQLFE as fe
c = grt.root.wb.doc.physicalModels[0].catalog
fe.generateSQLCreateStatements(c, c.version, {})
fe.createScriptForCatalogObjects(os.getenv('OUTPUT'), c, {})" \
--quit-when-done
set -e
note: posted on SO last week, but with 13 views in 4 days (and 1 up!), I doubt it was the right place.
I'll remove it from SO if I get answers here
@Rick James's comment :
MySQLDump was our previous option, but we'd like something which doesn't require an action from the developper anymore.
It doesn't only save the time taken to export / commit the .sql, but more importantly time spent due to mistakes, debugging problems which happen only because one forgot to update the .sql.
- An other "alternative" could be to manually export the .mwb via the
GUI, since it features a checkbox to "also export scripts", but
again, it's not mistake-proof, and we know it will cause problems - We might also end-up with an export of the functions in a separate
(manually created) .sql, and import separately both structure and
scripts files, since our functions are rarely modified… but this isn't the safest / more efficient way either
We thought MySQL Workbench would be easier to use with command line. It's quite disappointing to see that such tool is missing accessibility for features… which are accessible via the GUI !
Best Answer
Switch to
mysqldump
; it is aimed at scripting, whereas Workbench is aimed at UI.Use the option
--routines
in addition to whatever else you need.--no-data
gives you just the schema.