Configure

Home

Configure release

This page guides you how to configure a release inside the drm-cli database. The drm-cli has two types of databases styles, decided when running the install.py:

• JSON style
• SQLite style

Both do not require any installation of any 3rd' party software. Note!!! for SQLite UI experience, you can install & use any client you wish.

See full configuration fields properties in releases tables.

Content

• Connecting to the drm-cli database
• Create a release
• Add a solution to the release
• Add a connection string to the solution
• Add a SQL script variable to the solution
• Add a target databases list SQL script to the solution
• Add a project to the solution
• Add a pre & post deployment script to the project
• Next steps

Connecting to the drm-cli database

JSON style

• Browse to the drm-cli installed folder.
• Browse into db folder.
• Using any editor you wish, open file drm_db.json.

SQLite style

• Browse to the drm-cli installed folder.
• Browse into db folder.
• Using any SQLite client, open file drm_db.sqlite.

Create a release

See fields properties in of table releases.

JSON style

• Seek for releases object within the JSON file.
• Add new release object to the releases array.

Example:

{
   "id": 10,
   "name": "My new release"
}

SQLite style

• Insert using SQL command a new record to releases table. Example:

INSERT INTO releases (id, name)
VALUES (10, 'My new release');

Add a solution to the release

See fields properties in of table solution. Note!!! release supports multiple solutions.

Solution Types

Options for solution_type_id:

{
	"id": "1",
	"name": "Microsoft SQL Server" (drm-cli v1.0)
},
{
	"id": "2",
	"name": "Liquibase" (drm-cli v1.1)
},
{
	"id": "3",
	"name": "Flyway" (drm-cli v1.1)
}

JSON style

• Inside your new release, add new key solutions that is an array of objects.

Example:

{
   "id": 10,
   "name": "My new release",
   "solutions": []
}

• Inside your solutions array, add new solution object. Note!!! in JSON style, no need to define fields id or ordinal.

Example:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 1,
	 "path": "/mssql/my-solution"
      }
   ]
}

SQLite style

• Insert using SQL command a new record to solutions table. Example:

INSERT INTO solutions (id, name, release_id, ordinal, solution_type_id, path)
VALUES (100, 'My new solution', 10, 1, 1, '/mssql/my-solution');

Add a connection string to the solution

See fields properties in of table connection. Note!!! solution supports multiple connections.

Note!!! If the drm-cli is encrypted, the connection string you add must be encrypted using the encryption key.
See example Encrypt clear text connection string in Securing sensitive data.

Connection Types

Options for connection_type:

{
	"id": "1",
	"name": "Microsoft SQL Server"
},
{
	"id": "2",
	"name": "Oracle"
},
{
	"id": "3",
	"name": "PostgreSQL"
},
{
	"id": "4",
	"name": "MySql"
},
{
	"id": "5",
	"name": "Google BigQuery"
}

SQL Server [drm-cli v1.0]

JSON style

• Inside your new solution, add new key connections that is an array of objects.\

Example:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 1,
	 "path": "/mssql/my-solution",
         "connections": []
      }
   ]
}

• Inside your connections array, add new connection object. Note!!! in JSON style, no need to define field id.

Note!!! In case the drm-cli is encrypted, use the drm_crypto.py utility to convert your clear text connection string to encrypted. Example:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 1,
	 "path": "/mssql/my-solution",
         "connections": [
            {
		"name": "My new connection",
		"connection_type_id": 1,
		"connection_string": "Server=127.0.0.1;User Id=MyUser;Password=MyPassword;Encrypt=False;"
            }
         ]
      }
   ]
}

SQLite style

• Insert using SQL command a new record to solutions table.

Note!!! In case the drm-cli is encrypted, use the drm_crypto.py utility to convert your clear text connection string to encrypted.

Example:

INSERT INTO connections (id, name, solution_id, connection_type_id, connection_string)
VALUES (1000, 'My new connection', 100, 1, 'Server=127.0.0.1;User Id=MyUser;Password=MyPassword;Encrypt=False;');

Oracle [drm-cli v1.1]

Note!!! This feature is relevant only starting drm-cli 1.1.0.

JSON style

• Inside your new solution, add new key connections that is an array of objects.

• Inside your connections array, add new connection object. Note!!! in JSON style, no need to define field id.

Note!!! In case the drm-cli is encrypted, use the drm_crypto.py utility to convert your clear text connection string to encrypted.

Example for Liquibase:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 2,
	 "path": "../liquibase",
         "connections": [
            {
		"name": "My new connection",
		"connection_type_id": 2,
		"connection_string": "url=jdbc:oracle:thin:@//127.0.0.1:1521/ORCL;username=drm;password=drm;"
            }
         ]
      }
   ]
}

Example for Flyway:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 3,
	 "path": "../flyway",
         "connections": [
            {
		"name": "My new connection",
		"connection_type_id": 2,
		"connection_string": "url=jdbc:oracle:thin:@//127.0.0.1:1521/ORCL;username=drm;password=drm;"
            }
         ]
      }
   ]
}

SQLite style

• Insert using SQL command a new record to connections table.

Note!!! In case the drm-cli is encrypted, use the drm_crypto.py utility to convert your clear text connection string to encrypted.

Example:

INSERT INTO connections (id, name, solution_id, connection_type_id, connection_string)
VALUES (1001, 'My new Oracle connection', 100, 2, 'url=jdbc:oracle:thin:@//127.0.0.1:1521/ORCL;username=drm;password=drm;');

PostgreSQL [drm-cli v1.1]

Note!!! This feature is relevant only starting drm-cli 1.1.0.

JSON style

• Inside your new solution, add new key connections that is an array of objects.

• Inside your connections array, add new connection object. Note!!! in JSON style, no need to define field id.

Note!!! In case the drm-cli is encrypted, use the drm_crypto.py utility to convert your clear text connection string to encrypted.

Example for Liquibase:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 2,
	 "path": "../liquibase",
         "connections": [
            {
		"name": "My new connection",
		"connection_type_id": 3,
		"connection_string": "url=jdbc:postgresql://127.0.0.1:5432/postgres;username=drm;password=drm;"
            }
         ]
      }
   ]
}

Example for Flyway:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 3,
	 "path": "../flyway",
         "connections": [
            {
		"name": "My new connection",
		"connection_type_id": 3,
		"connection_string": "url=jdbc:postgresql://127.0.0.1:5432/postgres;username=drm;password=drm;"
            }
         ]
      }
   ]
}

SQLite style

• Insert using SQL command a new record to solutions table.

Note!!! In case the drm-cli is encrypted, use the drm_crypto.py utility to convert your clear text connection string to encrypted.

Example:

INSERT INTO connections (id, name, solution_id, connection_type_id, connection_string)
VALUES (1000, 'My new connection', 100, 3, 'url=jdbc:postgresql://127.0.0.1:5432/postgres;username=drm;password=drm;');

Add a SQL script variable to the solution

See fields properties in of table sql_scripts_variables.
Note!!! solution supports multiple sql_scripts_variables.

JSON style

• Inside your new solution, add new key sql_scripts_variables that is an array of objects.\

Example:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 1,
	 "path": "/mssql/my-solution",
         "connections": [
            {
		"name": "My new connection",
		"connection_type_id": 1,
		"connection_string": "Server=127.0.0.1;User Id=MyUser;Password=MyPassword;Encrypt=False;"
            }
         ],
         "sql_scripts_variables": []
      }
   ]
}

• Inside your connections array, add new connection object. Note!!! in JSON style, no need to define field id.

Example:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 1,
	 "path": "/mssql/my-solution",
         "connections": [
            {
		"name": "My new connection",
		"connection_type_id": 1,
		"connection_string": "Server=127.0.0.1;User Id=MyUser;Password=MyPassword;Encrypt=False;"
            }
         ],
         "sql_scripts_variables": 
         [
            {
		"name": "my_var",
		"value": "My Value"
            }
         ]
      }
   ]
}

SQLite style

• Insert using SQL command a new record to solutions table. Example:

INSERT INTO sql_scripts_variables (id, name, solution_id, value)
VALUES (10000, 'my_var', 100, 'My Value');

Add a target databases list SQL script to the solution

Note!!! This table is used only in SQLite style.
In JSON style, it is explicitly configured inside the project object.

Note!!! Relevant for SQL Server solution only!

When defining multiple targets deployments for the same project, it's often preferred to use SQL query that extracts a list of target database to deploy into, instead of an explicit list of target database.
This is often used when the list of the target databases is too long or dynamically changes.

See fields properties in of table sql_scripts.
Note!!! solution supports multiple sql_scripts.

SQLite style

• Insert using SQL command a new record to solutions table. Example:

INSERT INTO sql_scripts (id, name, solution_id, sql_text)
VALUES (100000, 'All user databases', 100, 'select name from sys.databases where name like 'DB_C%' order by name');

Add a project to the solution

See fields properties in of table project.
Note!!! solution supports multiple projects.

JSON style

• Inside your new solution, add new key projects that is an array of objects.\

Example:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 1,
	 "path": "/mssql/my-solution",
         "connections": [
            {
		"name": "My new connection",
		"connection_type_id": 1,
		"connection_string": "Server=127.0.0.1;User Id=MyUser;Password=MyPassword;Encrypt=False;"
            }
         ],
         "sql_scripts_variables": 
         [
            {
		"name": "my_var",
		"value": "My Value"
            }
         ],
         "projects": []
      }
   ]
}

• Inside your connections array, add new connection object. Note!!! in JSON style, no need to define fields id or ordinal.

Example:

{
   "id": 10,
   "name": "My new release",
   "solutions": [
      {
         "name": "My new solution",
	 "solution_type_id": 1,
	 "path": "/mssql/my-solution",
         "connections": [
            {
		"name": "My new connection",
		"connection_type_id": 1,
		"connection_string": "Server=127.0.0.1;User Id=MyUser;Password=MyPassword;Encrypt=False;"
            }
         ],
         "sql_scripts_variables": 
         [
            {
		"name": "my_var",
		"value": "My Value"
            }
         ],
         "projects": 
         [
	    {
	        "name": "Project_B",
		"targets_compare_db": "DB_B1",
		"targets_type_id": 1,
		"deployment_properties": "[\"CreateNewDatabase=False\", \"ScriptDatabaseOptions=False\", \"CommentOutSetVarDeclarations=True\"]",
		"targets_list": "[\"DB_B1\", \"DB_B2\", \"DB_B3\", \"DB_B4\"]",
		"max_degree_in_parallel": 2,
		"timeout_in_min": 10,
		"sleep_time_in_sec": 2,
		"fail_on_error": false
	    },
	    {
		"name": "Project_C",
		"targets_compare_db": "DB_C1",
		"targets_type_id": 2,
		"deployment_properties": "[\"CreateNewDatabase=False\", \"ScriptDatabaseOptions=False\", \"CommentOutSetVarDeclarations=True\"]",
		"targets_sql_text": "select name from sys.databases where name like 'DB_C%' order by name",
		"targets_priority": "[\"DB_C3\"]",
		"targets_exclude": "[\"DB_C2\", \"DB_C4\"]",
		"max_degree_in_parallel": 5,
		"timeout_in_min": 30,
		"sleep_time_in_sec": 2,
		"fail_on_error": true
	    }
         ]
      }
   ]
}

SQLite style

• Insert using SQL command a new record to solutions table. Example:

INSERT INTO projects (id, name, solution_id, ordinal, targets_compare_db, targets_type_id, targets_list, max_degree_in_parallel, timeout_in_min, sleep_time_in_sec, deployment_properties, fail_on_error)
VALUES (1000001, 'Project_B', 100, 1, 'DB_B1', 1, '["DB_B1", "DB_B2", "DB_B3", "DB_B4"]', 2, 10, 2, '["CreateNewDatabase=False", "ScriptDatabaseOptions=False", "CommentOutSetVarDeclarations=True"]', 0);

INSERT INTO projects (id, name, solution_id, ordinal, targets_compare_db, targets_type_id, targets_sql_script_id, targets_priority, targets_exclude, max_degree_in_parallel, timeout_in_min, sleep_time_in_sec, deployment_properties, fail_on_error)
VALUES (1000002, 'Project_C', 100, 2, 'DB_C1', 2, 100000, '["DB_C3"]', '["DB_C2", "DB_C4"]', 5, 30, 2, '["CreateNewDatabase=False", "ScriptDatabaseOptions=False", "CommentOutSetVarDeclarations=True"]', 1);

Next steps

• drm-cli architecture
• Deploy release
• Uninstall drm-cli

Add a pre & post deployment script to the project

Note!!! This feature is relevant only starting drm-cli 1.1.0.

The drm-cli supports running scripts before and after each project deployment against the component database. These scripts will always run as part of the deployment process for the project.

See fields properties in of table pre_post_deployment_projects_scripts. Note!!! project supports multiple pre_post_deployment_projects_scripts.

Script Types

Options for script_type_id:

{
	"id": "0",
	"name": "Pre-deploy"
},
{
	"id": "1",
	"name": "Post-deploy"
},
{
	"id": "2",
	"name": "All"
}

JSON style

• Inside your project object, add a new key pre_post_deployment_scripts which is an array of objects.

Example:

"pre_post_deployment_scripts": [
    {
        "path": "/path/to/pre_script.sql",
        "script_type_id": 1
    },
    {
        "path": "/path/to/post_script.sql",
        "script_type_id": 2
    }
]

SQLite style

• Insert new records into the pre_post_deployment_projects_scripts table. Example:

INSERT INTO pre_post_deployment_projects_scripts (project_id, path, script_type_id)
VALUES (1000001, '/path/to/pre_script.sql', 1);

INSERT INTO pre_post_deployment_projects_scripts (project_id, path, script_type_id)
VALUES (1000001, '/path/to/post_script.sql', 2);

Home