Create database on server via mysql-Workbench (.mwb) template via command line

Asked

Viewed 2,208 times

8

I am trying to assemble a bat to create the database in the mysql server from an EER (.mwb) mysql-Workbench model, that is, in the command line, is it possible to perform this process? Someone could shed a light?

Editing:

Opening it in cmd I have the following:

MySQLWorkbench [<options>] [<name of a model file or sql script>]
Options:
  --query [<connection>|<connection string>]
                          Open a query tab and ask for connection if nothing is specified.
                          If named connection is specified it will be opened,
                          else connection will be created based on the given connection string,
                          which should be in form <user>@<host>:<port>
  --admin <instance>      Open a administration tab to the named instance
  --upgrade-mysql-dbs     Open a migration wizard tab
  --model <model file>    Open the given EER model file
  --script <sql file>     Open the given SQL file in an connection, best in conjunction 
                          with a query parameter
  --run-script <file>     Execute Python code from a file
  --run <code>            Execute the given Python code
  --run-python <code>     Execute the given Python code
  --migration             Open the Migration Wizard tab
  --quit-when-done        Quit Workbench when the script is done
  --log-to-stderr         Also log to stderr
  --help, -h              Show command line options and exit
  --log-level=<level>     Valid levels are: error, warning, info, debug1, debug2, debug3
  --verbose, -v           Enable diagnostics output
  --version               Show Workbench version number and exit
  --open <file>           Open the given file at startup (deprecated, use script, model etc.)

So you could use --model plus --run-script and then --quit-when-done, but in this case how would this script look, in what language? Any tips on how to write it?

Issue 2

I found a similar question in the OS in English, there really seems to be no way to do this.

https://stackoverflow.com/questions/9556330/command-line-foward-engineering-using-a-mwb-file

Issue 3

Of course there is answer, after researching I put her down there in case anyone needs.

mysql relational-model batch mysql-workbench command-line

  • In the Mysql Workbench documentation you can find some examples of using Python scripts: https://dev.mysql.com/doc/workbench/en/wb-scripting-shell.html. And also this link from the Workbench development team: https://dev.mysql.com/doc/workbench/en/wb--scripting-shell.html. I noticed that although Workbench accepts command line parameters, it does not run on the command line only in graphical mode.

  • @Celsomarigojr I’ll take a look, but if you really don’t have how I spent my 50 points but now I’m sure it’s not possible.

  • @Artur_indio showed that it is possible and how to get there if you still need help in how to treat the xml file or how to mount a bat or sh file to automate the dump into mysql opens another question not to get too broad

  • Guys I managed to do, it’s really necessary the python code, and the guy I picked up put up the github code, I had even used it once but I thought I was using the methodology proposed by @Sneepsninja, then when I went to look at the source there was the python code all the time. I will wait there for someone else to try to answer, if not at the end of the bount I put my answer even without being worth anything just to help others. Thanks for all your help.

3 answers

3

I will give you the path of the stones, but I will not write here the file . whole bat no.

  1. the.mwb file is a compressed file, renames it to . zip and decodes it.
  2. search for the . xml file and open it in a text editor

Inside this file has all the tag markings needed to build your . sql which then dump into mysql.

So there’s a way, and no, I’m not going to manipulate this file, you choose the best technique and apply it on top of this xml file, replace it in hand, use php xml to array, do it with json, your way, I’ll leave the example of how the mwb file looks after uncompressed:

    <?xml version="1.0"?>
<data grt_format="2.0" document_type="MySQL Workbench Model" version="1.4.4">
  <value type="object" struct-name="workbench.Document" id="{A6AEAF1F-556E-433D-9483-5253F37D41EE}" struct-checksum="0x7131bf99">
    <value type="object" struct-name="workbench.logical.Model" id="{3183BE53-D18E-4B6F-AD8A-AE08B7BD19A2}" struct-checksum="0xf4220370" key="logicalModel">
      <value _ptr_="0B831168" type="list" content-type="object" content-struct-name="workbench.logical.Diagram" key="diagrams"/>
      <value _ptr_="0B8313E8" type="dict" key="customData"/>
      <value _ptr_="0B832C98" type="list" content-type="object" content-struct-name="model.Marker" key="markers"/>
      <value _ptr_="0B8308F8" type="dict" key="options"/>
      <value type="string" key="name"></value>
      <link type="object" struct-name="GrtObject" key="owner">{A6AEAF1F-556E-433D-9483-5253F37D41EE}</link>
    </value>
    <value _ptr_="0B831348" type="list" content-type="object" content-struct-name="workbench.OverviewPanel" key="overviewPanels"/>
    <value _ptr_="0B830E98" type="list" content-type="object" content-struct-name="workbench.physical.Model" key="physicalModels">
      <value type="object" struct-name="workbench.physical.Model" id="{2E273679-67EA-48F4-A08F-92A272B4D13F}" struct-checksum="0x5f896d18">
        <value type="object" struct-name="db.mysql.Catalog" id="{ECE8278F-2DB8-4352-B627-6552022356F5}" struct-checksum="0x82ad3466" key="catalog">
          <value _ptr_="0B831398" type="list" content-type="object" content-struct-name="db.mysql.LogFileGroup" key="logFileGroups"/>
          <value _ptr_="0B830858" type="list" content-type="object" content-struct-name="db.mysql.Schema" key="schemata">
            <value type="object" struct-name="db.mysql.Schema" id="{5278BE8F-6DD9-4F97-978C-C55DFCEE2B47}" struct-checksum="0x20b94c22">
              <value _ptr_="0B8312F8" type="list" content-type="object" content-struct-name="db.mysql.RoutineGroup" key="routineGroups"/>
              <value _ptr_="0B831C58" type="list" content-type="object" content-struct-name="db.mysql.Routine" key="routines">
                <value type="object" struct-name="db.mysql.Routine" id="{7BB0B591-6EA6-409B-8F04-24B8E9076FA4}" struct-checksum="0x991f611c">
                  <value _ptr_="0B8318E8" type="list" content-type="object" content-struct-name="db.mysql.RoutineParam" key="params">
                    <value type="object" struct-name="db.mysql.RoutineParam" id="{C7FBF5C3-B6D8-43F5-9927-68FCC119125F}" struct-checksum="0x3f238dee">
                      <value type="string" key="datatype">INT</value>
                      <value type="string" key="paramType">IN</value>
                      <value type="string" key="name">cpf</value>
                      <link type="object" struct-name="GrtObject" key="owner">{7BB0B591-6EA6-409B-8F04-24B8E9076FA4}</link>
                    </value>
                  </value>
                  <value type="string" key="returnDatatype"></value>
                  <value type="string" key="security"></value>
                  <value type="string" key="name">duplicadoCpf</value>
                  <value type="string" key="routineType">procedure</value>
                  <value type="int" key="sequenceNumber">0</value>
                  <value type="string" key="definer">marcio`@`%</value>
                  <value type="string" key="sqlBody">BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
    SELECT 'CPF CADASTRADO PARA OUTRO MOTORISTA' AS Msg;
    END;
    INSERT INTO tab_proprietario SET prop_cpf = cpf;
    END</value>
                  <value type="string" key="sqlDefinition">

CREATE DEFINER=`marcio`@`%` PROCEDURE `duplicadoCpf`(IN cpf INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
    SELECT 'CPF CADASTRADO PARA OUTRO MOTORISTA' AS Msg;
    END;
    INSERT INTO tab_proprietario SET prop_cpf = cpf;
    END</value>
                  <value type="int" key="commentedOut">0</value>
                  <value type="string" key="createDate">2013-04-17 09:17</value>
                  <value _ptr_="0B831578" type="dict" key="customData"/>
                  <value type="string" key="lastChangeDate">2013-09-05 08:06</value>
                  <value type="int" key="modelOnly">0</value>
                  <link type="object" struct-name="GrtNamedObject" key="owner">{5278BE8F-6DD9-4F97-978C-C55DFCEE2B47}</link>
                  <value type="string" key="temp_sql"></value>
                  <value type="string" key="comment"></value>
                  <value type="string" key="oldName">duplicadoCpf</value>
                </value>
.
.
.

Good luck!

And here the quote from the source give credit to those who deserve https://stackoverflow.com/questions/19956525/how-to-convert-mwb-into-sql-without-workbench

  • @Artur_indio if you use Workbench then the answer of Edgarmunizberlinck this certain already has tools (graphics) for this, if you want to use command line my answer or this your link ai resolve, it was not clear what you need yet ( at least for me)

  • In my question I put "Then I could use --model plus --run-script and then --quit-when-done, but in this case how would this script be, in what language? Any hint of how to write it?" would be the code used in --run-script, probably in python.

  • I did, but thank you very much, now I know that the mwb file is a zip with xml, vlw.

3


So after a lot of research I was able to do the bat, really the script to be used was in python, I had to learn some python. I know I can’t answer my own response that I’m rewarded, but since I didn’t get a satisfactory answer, I’m going to put it here and also to help if anyone needs it. Follow the main lines, you can customize and create a bat with arguments and other things depending on your preferred language. Thank you all for your help. 

"C:\Program Files (x86)\MySQL\MySQL Workbench CE 6.1.7\MySQLWorkbench.exe" -model "C:\ModelosERR.mwb" -run-python "import os;import grt;from grt.modules import DbMySQLFE as MySQLFE;c = grt.root.wb.doc.physicalModels[0].catalog;MySQLFE.generateSQLCreateStatements(c, c.version, {});MySQLFE.createScriptForCatalogObjects(r'C:\temp.sql', c, {})" -quit-when-done

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe" -u root -proot < "C:\temp.sql"

1

My answer may not be satisfactory for you. I’ve been working with Mysql Workbench since beta and I’ve never seen anything like this. I don’t think that’s even possible.

One thing I usually use is Synchronize Model, which takes your mwb and compares it to the database of the connection you selected. This is useful for both replicating change and creating the whole model from scratch.

In case I have any questions about this process comment here that I explain better.

It is now possible to run an sql file using the mysql cli, so you would need to export your mwb to an sql file.

  • Just did not want to perform this step in exporting to sql, wanted to play the mwb on the server and there in the process in bat or other create the database on the mysql server. But if it’s not possible via line then I spent my 50 points.

  • But because you do not connect to the server by Workbench and do what I told you?

  • Ah, and maybe it’s possible. I’ve at least never seen this kind of thing.

  • I really had to do the forward Engineering and play in mysql via code, which I put up there, vlw by help.

Browser other questions tagged mysql relational-model batch mysql-workbench command-line

You are not signed in. Login or sign up in order to post.