Toad World Blog

Let’s Automate with Toad Edge!

Dec 31, 2018 11:44:45 AM by Jeff Surretsky

Toad Edge is the newest edition to the Toad family of products. You can greatly reduce your development and DBA efforts when managing MySQL and Postgres by using Toad Edge (it can run on Windows and MacOS). See the product documentation for a detailed support matrix: https://support.quest.com/toad-edge/2.0.6/technical-documents.

In addition, Toad Edge can support your DevOps initiatives by allowing you to integrate into your Continuous Integration platforms. An excellent video discussing how this works can be found here.

What if your goals are much simpler and you just want to automate specific tasks within Toad Edge, similar to what you can do in other Toad products?

You most definitely can and that’s what I want to show you …

Currently, Toad Edge allows you create a batch or shell script to do the following actions:

  • Empty Schema – Drop all objects from a selected schema to prepare it for a clean script deployment
  • Create Snapshot – Save your database schema structure to a file for point-in-time recovery
  • Create Baseline – Set the current state of your schema structure as the baseline in order to detect the most recent changes that have been made
  • Generate create SQL Script – Generate a create SQL script from your repository, saved snapshot or database
  • Generate change SQL Script – Generate an alter SQL script based upon a previous Schema Compare operation
  • Compare schemas – Compare two different schemas (from database, repository or snapshot) in preparation for running a report or alter script
  • Compare schema with baseline – Compare your current schema with the baseline as one of the steps to assure that a deployment script is successful
  • Deploy SQL Script – Execute any arbitrary SQL on a selected database
  • Generate HTML comparison report – Generate an HTML report based upon a previous schema compare operation 

Note: More detail on each action can be found in the product documentation referred to in the hyperlink above.

These batch or shell scripts can then be composed to do more complex operations. Once finished, you can put these scripts into Jenkins or Bamboo or whatever CI server you may be using.

You can also put these scripts into CRON or the Windows Task Scheduler (depending on where you want these scripts to reside and run). Make sure these scripts execute in their respective environments and meet specific criteria outlined in the product documentation.

Let me show you how this works through a simple example.

In my example, I’m going to show you how to schedule a nightly snapshot of a specific Postgres schema (Public). Again, a snapshot is a point-in-time backup of the schema structure (metadata only, no actual data). This is useful should you need to recover the structure back to a point in time or just create a report of what changes were done during the day.

This example assumes you have a working knowledge of Toad Edge. In case you do not or would like a refresher, please refer to this video series to get started.

Let’s begin!

Once you launch Toad Edge and make a connection to your database, notice the CI/CD icon in the upper right hand corner of your desktop.

Figure 1. CI CD icon

Figure 1. CI/CD icon

Select this icon and a pop-up window will appear with all the different actions available (described above):

Figure 2. CI CD actions available

Figure 2. CI/CD actions available

You’ll also notice that I have already selected the “Create Snapshot” action. Note that the “Path to toadedge.jar” file needs to be filled in also. You can download this from our website (https://support.quest.com/toad-edge/2.0.6). Once you navigate to this page, you’ll see the following:

Figure 3. Download Software links

Figure 3. Download Software links

Click on the “Command Line Tool” to download and place this file in a directory that you will specify in the “Path to toadedge.jar” file. I created a directory and placed mine as follows:

Figure 2. CI-CD actions available

Figure 4. Specify the path to toadedge.jar

Once done, you can click Next> to see the following:

Figure 5. Define the Create Snapshot command line parameters

Figure 5. Define the Create Snapshot command line parameters

You’ll also notice that I selected the Public schema as the schema I want to take a snapshot of. Once the correct schema is selected, select the correct connection string and location of the JSON file (snapshot output). Then select Next> to see the following:

Figure 6. Create Snapshot export file options

Figure 6. Create Snapshot export file options

The default Command File Name is a *.sh file to execute on Linux. By simply changing the name to have a *.bat extension, you will be able to produce a batch file that can be executed on a Windows Server. After doing this, you select Finish and you’ll see two scripts placed in the directory specified by “Export Path” in the dialog window.

Before selecting Finish>, you can check the box to “Open Export Folder after finish”, the window will pop up automatically as follows:

Figure 7. Two scripts have been placed in your specified directory

Figure 7. Two scripts have been placed in your specified directory

The first item is the actual batch script with the following contents:

java -jar "C:\Program Files\Quest Software\ToadEdgeJenkinsPlugintoadedge.jar"  -snapshot  -in "./enterprisedb@localhost_5444 (enterprisedb).xml"  -out  "./snapshot_enterprisedb@localhost_5444 (enterprisedb).json

This batch script will create a snapshot using the enterprisedb@localhost_5444(enterprisedb).xml file and output the snapshot in JSON format.

The XML file specifies the connection, schema(s), and object(s). The specific XML file for my example can be seen below:

<?xml version="1.0" encoding="UTF-8"?><Connections>

  <Connection SubType="EDB" autoCommit="" type="Jdbc">

    <Path>EDB</Path>

    <Name>enterprisedb@localhost:5444 (enterprisedb)</Name>

    <Host>localhost</Host>

    <Port>5444</Port>

    <User>enterprisedb</User>

    <SavePassword>true</SavePassword>

    <Password>1ybsDyeisRARCaWAsPLdiw==</Password>

    <RestoreWork>true</RestoreWork>

    <LastConnectionDate>2018-12-20T06:43:50</LastConnectionDate>

    <Color>#8000FF</Color>

    <Database>edb</Database>

    <Schema>public</Schema>

    <Options>

      <UUID>dbb28330-0029-4a31-883b-695dd0c227c1</UUID>

      <Origin>toadedge</Origin>

      <AutoLogon>false</AutoLogon>

      <RestoreState>true</RestoreState>

      <Performance>1</Performance>

      <DriverPath>C:\Program Files\Quest Software\Toad Edge\lib\drivers\edb-jdbc.jar</DriverPath>

      <PerformanceOptions>

        <PerformanceOption>

          <Key>TYPES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>TRIGGERS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>FOREIGN_TABLES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>TABLE_COLUMNS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>PACKAGES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>PROCEDURES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>INDEXES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>MATERIALIZED_VIEW_RULES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>AGGREGATES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>VIEW_COLUMNS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>TABLE_CONSTRAINTS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>TABLE_INDEXES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>CONSTRAINTS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>DOMAINS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>MATERIALIZED_VIEW_COLUMNS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>SYNONYMS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>TABLE_TRIGGERS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>TRIGGER_FUNCTIONS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>VIEW_RULES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>FUNCTIONS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>TABLE_RULES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>MATERIALIZED_VIEWS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>MATERIALIZED_VIEW_INDEXES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>TABLES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>COLLATIONS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>SEQUENCES</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>VIEWS</Key>

          <Value>false</Value>

        </PerformanceOption>

        <PerformanceOption>

          <Key>VIEW_TRIGGERS</Key>

          <Value>false</Value>

        </PerformanceOption>

      </PerformanceOptions>

      <SSH>

        <Enable>false</Enable>

      </SSH>

      <SSL>

        <Enable>false</Enable>

      </SSL>

    </Options>

  </Connection>

</Connections>

You can now use your Windows Task Scheduler to execute your newly created batch file. Remember that the Windows client needs connectivity to the database in question when the script is scheduled to fire off. Here is a great link on how to use the Windows Task Scheduler: https://www.e-education.psu.edu/geog485/node/143.

The output will be a snapshot in JSON format and look like the following (note that only a portion of the JSON file is shown since it is quite large):

Figure 8. The snapshot output in JSON format

Figure 8. The snapshot output in JSON format

While this operation is very useful, it is quite simple to set up. Once you have success with this, you may want to extrapolate upon this example to schedule more complex operations.

I hope you found this helpful. Happy automation!

Tags: Toad Edge

Jeff Surretsky

Written by Jeff Surretsky

Jeffrey Surretsky has been working at Quest since 2000 as a Sales Engineer focusing on a wide variety of solutions including Foglight, SharePlex and the TOAD Family of Products. Before working at Quest, he was a DBA for various organizations spanning a diverse range of industries. With over 30 years of experience in Information Technology, Jeffrey has a Bachelor’s Degree in Computer Science and an MBA in Management of Information Systems.