@corpsmap/sql_build_generator

sqlbuild

A cli git/oracle tool for writing build scripts

sqlbuild is a commandline tool that utilizes commit history either using branch names or comparing two commits for file changes. The files are then searched for object names (tables, views, materialized, views, packages, etc.) and then attempts to send the names of these objects to a provided database where an order of execution can be establishd. This assumes you are sending it to the current dev database, where all objects are already built. After attempting to create an ordered list, it is sent back to the command, and creates an ordered list of calls to sql files. Options are available for adding static files at the beginning and end of the build file, and please check below for more details on file ordering or handling files that are not ordered by the database.

install sqlbuild

To install globally, so it can be used at your command-line:

npm install @corpsmap/sql_build_generator -g

To install locally, so it can be utilized just by your local npm package.json:

npm install @corpsmap/sql_build_generator

To run it via npm scripts, add an entry to the scripts in your project's package.json such as :

"sqlbuild": "sqlbuild"

Then you can run it via (don't forget the -- to allow for arguments to be passed to the underlying command):

npm run sqlbuild -- (arguments here)

At this point, if run as is, it would work off the current branch compared to the master branch, and then return applicable files.

Major design considerations

Determining applicable files

Depending on options, a list of files should be returned to the program of files between the first and second branches/commits provided. The builder will only keep those that are listed as Modified or Added. Deleted files will be expected to include a build script that will handle the deletion. Handling of script files will be detailed later in the help file.

Why scan the "Development" schema?

This tool has been developed with the understanding that there are separate schemas, and that the development schema should overall represent the future of the test, production, etc. schemas. So to determine object build ordering, it will use the established dependencies in the dev schema to determine ordering for deploying to the subsequent schemas. If this is not how your environment or project is structured, then this tool might not be suitable for usage. Alternatively, use it without the db connection, and use it as best you can, it will at a minimum capture the files changed.

How is the object dependency order determined?

Oracle provides the data dictionary view user_dependencies, which presents parent-child relationships of objects owned by the current schema to either objects within the schema or other schemas. During runtime, sqlbuild will search all found files for CREATE statements, which includes tables, views, materialized views, packages, functions, and procedures. These names will be collected and sent to the database. Then the list of object names is processed as follows:

1. Find all object names that are not dependent on any other objects in the list (first order objects).
2. Remove all first order objects from the list to the result list.
3. Repeat to find second, third, fourth order objects.
4. A hard limit of 10 iterations is currently specified to prevent infinite loops
5. The list of ordered objects is returned to the sqlbuild process

Usage

Providing start .. end points for comparison

• -s, --start <value>
  • Start range commit or branch to use for capturing list of changed files, defaults to the master branch.
• -e, --end <value>
  • End range commit or branch to use for capturing list of changed files, defaults to current branch.

Database connection

• -d, --db <value>
  • Name of JSON file (ex. config.json) with db connection info

If a DB file is not passed, it will attempt to order the file based on whatever information is provided.

If a DB file is passed and a successful connection is able to return information, it will use that in priority over all other ordering commands provided. Files not associated with the returned db objects will be ordered off the commands provided, see below for further details on options for ordering files.

Example of expected format for the db connection JSON file :

	{
  		"user"          : "username",
  		"password"      : "password",
  		"connectString" :  "192.1.1.1:1521/SID"
	}

Inputs to the build file

• -p, --pre-file <value>
  • Filename for file that the user wishes to have the contents added to the beginning of the build file.
  • This file will not be added to the build file as a called file.
• -t, --post-file <value>
  • Filename for file that the user wishes to have the contents added to the end of the build file.
  • This file will not be added to the build file as a called file.

Output

• -o, --output <value>
  • Name of the build file, defaults to buildAll.sql
• -c, --console
  • Flag for printing file to console instead of to a file.
• -v, --verbose
  • Flag for detailed console logging of process output.

Ordering non-object files

• -r, --ordering <value>
  • Order by filename ascending: a asc
  • Order by filename descending: d desc
  • Formatted instructional comments in the file: s scan
  • By default, will place files at the beginning of the build file unordered.

How scan works

Concept

The intention of the scan is to allow for an elegant in-file specification methodology. It will not prioritize over any ordering instructions provided by the returned database ordering. However, if you omit the db connection, you can manually specify the order entirely via formatted instructions.

Adding instructions to a file

As sqlbuild expects sql type files, the instructions should be added as a comment to the files you desire instructions utilized.

Basic Format:

  -- buildsql instruction: [IGNORE|BEFORE|AFTER|PRE|POST]:[filename|number]

• [PRE|POST]:[number]
  • To be added to either the beginning or end, respectively of the file list. The number will determine the relative ordering with other files marked [PRE|POST]
• [BEFORE|AFTER]:[filename]
  • To be added to either before or after, respectively, the provided filename. The filename should not include any path information.
• [IGNORE]
• To ignore any file from being considered, regardless of being included in the database ordering, or any other consideration

Order of content in the build file

  > Content of pre-file
  > Ordered PRE files of scan
  > DB Ordered Files w/ any BEFORE|AFTER files from scan
  > Ordered POST files of scan
  > Content of post-file

Future goals:

• Allow sqlplus to be run afterwards to run said build file, with optional review.
• Allow ordering of BEFORE and AFTER instruction scripts.
• JSON build files to manually construct order.