SQL Reversing for Vertica

How we use it:

• For daily automatic reverse engineering of Vertica DB with object generation script saving in SVN or GIT version control systems;
• At any moment in time to open a file for required object in text editor or SQL client to see its structure, parameters, mapping (for tables), access rights assigned to users and roles;
• With version control system we can easily monitor DB objects variation over time and compare versions;
• We use one script to generate all objects transfer from one Vertica DB to another when needed.

How does it differ from EXPORT_OBJECTS function in Vertica?

In addition to metadata of physical database model catalog (schemas, tables, views, counters and functions), 'SQL reversing for Vertica' allows processing other metadata (resource pools, roles, users and object access rights). The utility can save the data in a filesystem hierarchically ('catalog/file'), thus enabling to break DB object script generation down to types and schema affiliation.

Can this utility replace SAP PowerDesigner?

No. PowerDesigner along with «PowerDesigner Vertica Plug-in» makes it possible to complete the full cycle of data warehouses' visual physical simulation and conduct reverse engineering as well as comparison of two database models aimed at changing receiver DB objects to match source objects using ALTER script generation. However, PowerDesigner does not support resource pools and cannot generate a set of files to ensure version control with file-based version control system (only a proprietary version control system, allowable at purchase of respective PowerDesigner license, is supported).

Is it a freeware?

Yes, it is a freeware distributed 'as is' and can be found under 'GNU General Public License v3.0' at: https://github.com/ascrus/verticareverseutilite

Which OS are supported? Launch requirements

The software is written in Java/Groovy using JavaFX, therefore Windows, Linux and MacOS platforms are supported. Proper operation in Linux requires X to be installed in the OS. 30 Mb free disc space and 1 GB RAM are required to launch the software.

Which Vertica versions are supported?

Vertica 7.2.3 and later. We do not guarantee support of earlier Vertica versions.

Is command line support available?

Yes, the software allows to configure installation for launching reverse engineering from the command line without any graphics interface.

Which database objects are supported?

The current version supports:

• Generation of scripts for resource pools and roles' and users' access rights;
• Generation of scripts to create roles and assign rights to them;
• Generation of scripts to create internal Vertica users and assign roles to them;
• Generation of scripts to create table schemas and assign owners and access rights;
• Generation of scripts to create permanent and temporary tables with constraints and projections description, as well as owners and access rights assignment;
• Generation of scripts to create views with assigning owners and respective access rights;
• Generation of scripts to create counters with assigning owners and respective access rights;
• Generation of scripts to create UDF functions with assigning owners and respective access rights.

Current version does not support:

• Database parameters;
• Database storage;
• Partitions storage policies;
• Users connected through LDAP;
• Schema objects' access rights inheritance;
• Flex tables;
• External tables;
• Aggregate projections;
• Service tables' data synchronization.