Git Integration Procedures

The CData Virtuality Server supports Git integration enabling you to set up and use Git as a version control system if you want a convenient way to record changes to CData Virtuality objects.

Currently supported list of objects:

Data sources
Views
Procedures
Remarks
Roles
Permissions
Users
Jobs
Schedules of type INTERVAL, CRON, and CHAINED
Default options
Data catalog attributes
Publications
Optimizations
Recommended indexes
Recommended optimization symbols
Materialized tables
Queue handlers

SYSADMIN.gitInit

This procedure initiates a local Git repository with the server objects as its content.

SQL

CALL "SYSADMIN.gitInit"(
  "repoFolder" => '/some/file/path'
);;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`repoFolder`	In	string	No	System path where the repository will be initialized
`result`	Out	string	No	Result of the operation

When importing a large amount of jobs and schedules, we recommend to initiate Git repository after the import to make the schedules.sql file creation faster.

SYSADMIN.gitCheckout

This procedure switches the branch or tag on which Git is working and informs about the result.

SQL

-- With branch
CALL "SYSADMIN.gitCheckout"(
  "branch" => 'branchName'
);;

-- With tag
CALL "SYSADMIN.gitCheckout"(
  "tag" => '1.0.0'
);;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`branch`	In	string	Yes	Branch name Git will use. If omitted, the procedure will return the current branch
`tag`	In	string	Yes	The tag name to checkout. It is mutually exclusive with a branch parameter. When doing a checkout of a tag, it is recommended to avoid creating new objects in the server
`result`	Out	string	No	Result of the operation if `branch` is set; current branch in case it is omitted

SYSADMIN.gitClone

This procedure performs a “git clone” with the specified parameters.

CODE

CALL "SYSADMIN.gitClone"(
  "remoteUrl" => 'remoteUrl', 
  "repoFolder" => 'repoFolder',
  "sshKeyId" => 1,
  "branchToCheckout" => 'branchName',
  "username" => 'username',
  "password" => 'password',
  "accessToken" => 'string_accessToken'
);;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`remoteUrl`	In	string	No	URL of the remote repository in the SSH or HTTPS format
`repoFolder`	In	string	No	System path the repo should be cloned into
`sshKeyId`	In	integer	Yes	SSH key ID to be used as authentication in the remote repository. See documentation for the `SYSADMIN_VDB.createSSHKeyPair` procedure for SSH key generation
`branchToCheckout`	In	string	Yes	Branch to be checked out. If omitted, the master branch will be used
username	In	string	Yes	Username for connecting via HTTPS
password	In	string	Yes	Password or token for connecting via HTTPS
accessToken	In	string	Yes	Your personal access token for connecting via HTTPS
`result`	Out	string	No	Result of the operation

Examples

1. Connection with the SSH key pair, where SSH key pair Id is the Id in the CData Virtuality Server (created or imported and added to the git provider, git-lab in this example)

SQL

CALL "SYSADMIN.gitClone"(
    "remoteUrl" => 'git@git-lab.data-virtuality.com:<user>/app.git',
    "repoFolder" => 'repoFolderPath',
    "branchToCheckout" => 'main',
    "sshKeyId" => 1
);;

2. Connection with username and password:

SQL

CALL "SYSADMIN.gitClone"(
    "remoteUrl" => 'https://git-lab.data-virtuality.com/<user>/app.git',
    "repoFolder" => 'repoFolderPath',
    "branchToCheckout" => 'main',
    "username" => 'git_user',
    "password" => 'git_password'
);;

3. Connection with the personal access token, configured in git (for some providers, username should be set as well):

SQL

CALL "SYSADMIN.gitClone"(
    "remoteUrl" => 'https://git-lab.data-virtuality.com/<user>/app.git',
    "repoFolder" => 'repoFolderPath',
    "branchToCheckout" => 'main',
    "accessToken" => 'git_personal_accessToken'
);;

Connection via HTTPS and the username, password, and accessToken parameters are available since v25.3

SYSADMIN.gitExclude

This procedure specifies a comma-separated list of objects or object name patterns that should be excluded during Git integration operations.

If replaceInsteadOfAppend is set to FALSE (default), the procedure appends the pattern or object name from the objectOrPattern value to the existing list of exclusions; otherwise, replaces it.

SQL

CALL "SYSADMIN.gitExclude"(
    "objectOrPattern" => 'view:views.v1, procedure:views.proc_%',
    "replaceInsteadOfAppend" => true
);;

It has the following parameters:

Parameter	In/Out	Type	Nullable?	Description
`objectOrPattern`	In	string	No	The object name(s) or pattern(s) (SQL LIKE) to be excluded during Git integration operations. Should be provided as a comma-separated list of values in the following format: `object_type:object_name`. Object type and pattern values are case insensitive.
`replaceInsteadOfAppend`	In	string	Yes	Changes the behaviour from appending new patterns to replacing the current exclude list with the `objectPattern` parameter value. Default: `FALSE`
`result`	Out	string	No	Result of the operation

List of patterns for available objects:

DataSource
Schema
View
Procedure
Remark
Role
User
Permission
Job
Default Option
Schedule
Data Catalog Attribute
Queue Handler
Optimization
Recommended Index
Web Business Data Shop (name of the published object, could be found in the SYSADMIN.WebBusinessDataShopPublished table)

SYSADMIN.purgeObjects

This procedure purges objects from the server depending on the provided list of object types.

SQL

CALL "SYSADMIN.purgeObjects"(
    "objectTypes" => 'views,procedures',
    "dryRun" => false,
    "ignorePurgeInGit" => true,
    "bypassGitExclude" => true
);;

It has the following parameters:

Parameter	In/Out	Type	Nullable?	Description
`objectTypes`	In	string	Yes	Filter objects to be purge by type in CSV format. Default: `all`. Supported values: `datasources`, `default options`, `schemas`, `procedures`, `views`, `optimizations`, `remarks`, `roles`, `permissions`, `users`, `queue handlers`, `jobs`, `optimization jobs`, `copy over table jobs`, `copy over sql jobs`, `gather statistics jobs`, `backup jobs`, `all jobs`^, `recommended indexes`, `schedules`, `web business data shops`, `data catalog attributes` () The `all jobs` value allows purging all deletable jobs in one call (optimization, copy over, gather statistics, custom backup jobs, etc).
`dryRun`	In	boolean	Yes	If set to `TRUE`, no change is done in the server but dry run of the operation is performed; If set to `FALSE`, all objects are purged. Default: `TRUE`
`ignorePurgeInGit`	In	boolean	Yes	If set to `TRUE`, purging of objects is not reflected on Git files. Default: `FALSE`
`bypassGitExclude`	In	boolean	Yes	If set to `TRUE`, `purgeObjects` will ignore the `gitExclude` list. Default: `FALSE`
`result`	Out	string	No	Result of the operation

SYSADMIN.gitCommit

This procedure commits files to the local repository.

SQL

CALL "SYSADMIN.gitCommit"( "message" => 'Commit message',
  "files" => Array('virtual_schema/views/create.sql')
);;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`message`	In	string	No	Message to be used in the commit operation
`files`	In	string	Yes	Array-formatted list of files to be committed. If omitted, the entire repository is committed
`result`	Out	string	No	Result of the commit operation containing the commit hash ID

SYSADMIN.gitStatus

This procedure returns a list of files and its Git status.

SQL

CALL "SYSADMIN.gitStatus"();;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`status`	ResultsSet	string	No	Status of the given file. Options are `ADDED`, `CHANGED`, `REMOVED`, `MISSING`, `MODIFIED`, `UNTRACKED`, and `CONFLICTING`
`path`	ResultsSet	string	No	Relative path of the file

SYSADMIN.gitDiff

This procedure shows the Git diff of a given file.

SQL

 CALL "SYSADMIN.gitDiff"(
  "file" => 'virtual_schema/views/create.sql'
);;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`file`	In	string	No	Individual file to check the differences for
`result`	Out	string	No	Output of git diff for the requested file

SYSADMIN.gitFixConflict

This procedure fixes a Git conflict using git checkout --ours/theirs path-to-file, choosing between the local or remote version to fix the conflict.

SQL

CALL "SYSADMIN.gitFixConflict"(
  "file" => 'virtual_schema/views/create.sql', "strategy" => 'OURS'
);;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`file`	In	string	No	File to check
`strategy`	In	string	No	Strategy to fix the conflict. Accepted values are `OURS` (to use the local file and discard remote changes) or `THEIRS` (to use the remote file and discard local changes)
`result`	Out	string	No	Result of the operation

SYSADMIN.gitPull

This procedure performs a git pull origin on the remote Git repository and will throw informative exceptions in case of partial success. It is usually called together with gitDeploy to apply the changes from the repository to CData Virtuality.

SQL

CALL "SYSADMIN.gitPull"();;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`result`	Out	string	No	Result of the operation

SYSADMIN.gitPush

This procedure performs a git push origin CURRENT_BRANCH on the remote Git repository and will throw informative exceptions in case of partial success.

SQL

CALL "SYSADMIN.gitPush"("additionalProperties" => 'atomic=false');;"

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`result`	Out	string	No	Result of the operation
`additionalProperties`	In	string	Yes	Allows to disable/enable atomic flag in git push. Default value of atomic flag: `TRUE`

SYSADMIN.gitSetRemote

This procedure adds a remote for the current Git repository. It will communicate with the server to validate successful authentication in the remote repository.

SQL

CALL "SYSADMIN.gitSetRemote"(
  "remoteUrl" => 'remoteUrl', 
  "sshKeyId" => 1,
  "username" => 'username',
  "password" => 'password',
  "accessToken" => 'accessToken'
);;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`remoteUrl`	In	string	No	URL of the remote repository in the SSH or HTTPS format
`sshKeyId`	In	integer	Yes	SSH key ID to be used as authentication in the remote repository. See documentation for the `SYSADMIN_VDB.createSSHKeyPair` procedure for SSH key generation
username	In	string	Yes	Username for connecting via HTTPS
password	In	string	Yes	Password or token for connecting via HTTPS
accessToken	In	string	Yes	Your personal access token for connecting via HTTPS
`result`	Out	string	No	Result of the operation

Examples

1. Connection with the SSH key pair, where SSH key pair Id is the Id in CData Virtuality Server (created or imported and added to the git provider, git-lab in this example)

SQL

call "SYSADMIN.gitSetRemote"(
    "remoteUrl" => 'git@git-lab.data-virtuality.com:<user>/app.git',
    "sshKeyId" => 1
);;

2. Connection with username and password:

SQL

call "SYSADMIN.gitSetRemote"(
    "remoteUrl" => 'https://git-lab.data-virtuality.com/<user>/app.git',
    "username" => 'git_user',
    "password" => 'git_password'
);;

3. Connection with the personal access token, configured in git (for some providers, username should be set as well):

SQL

call "SYSADMIN.gitSetRemote"(
    "remoteUrl" => 'https://git-lab.data-virtuality.com/<user>/app.git',
    "accessToken" => 'git_personal_accessToken'
);;

Connection via HTTPS and the username, password, and accessToken parameters are available since v25.3

SYSADMIN.gitDeploy

GitDeploy synchronizes the CData Virtuality Server status with the local repository. This procedure reads the local Git repository and executes the SQL statements contained in the files in the CData Virtuality Server.

While running:

For each object on the CData Virtuality Server:

If an object does not exist, GitDeploy deploys it by executing the SQL statement from the repository;
If the object exists, GitDeploy updates it to match the repository.

If the object exists on the CData Virtuality Server but is missing from the repository, GitDeploy removes it from the CData Virtuality Server.

Be aware of objects that are present in the CData Virtuality Server, but not in the repository. To prevent losing them, use the SYSADMIN.gitExclude procedure.

SQL

CALL "SYSADMIN.gitDeploy"( "dryRun" => 'true');;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`dryRun`	In	boolean	Yes	Default value is `FALSE`. If set to `TRUE`, no actual task is performed in the CData Virtuality Server. The procedure will iterate all files and try to parse the SQL statements, but will not execute them
`result`	Out	string	No	Result of the operation

SYSADMIN.gitTag

This procedure creates a local tag pointing to the last commit in the repository.

SQL

CALL "SYSADMIN.gitTag"(
    "name" => 'tag_name',
    "message" => 'some message'
    );;

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	In/Out	Type	Nullable?	Description
`name`	In	string	No	Name of the tag (like ‘1.0.0’)
`message`	In	string	No	Description of the tag and its details
`result`	Out	string	No	Result of the operation