SQLines Command Line Reference

You can run SQLines from a command line or shell script. Executable file is sqlines.exe on Windows, and sqlines on Linux/Unix.

Any option, including user-defined options, can be specified in command line and configuration file (config.txt, by default).

SQLines Directory Structure

SQLines has data directory that contains configuration files, conversion and report templates. By default, data directory is a sub-directory of SQLines executable directory (not the current working directory).

Conversion templates, reports and configuration files are supposed to be often modified and customized. Depending on your installation option, it can be inconvenient to store data directory with SQLines executable files. For example, if you use .msi installation on Windows, and executables are stored in Program Files directory.

Although, you can specify absolute paths for configuration files, templates and reports in the command line, this can be also inconvenient.

So if you move data directory to another location, you can specify default_data_dir option in data_dir.txt file located in the SQLines executables directory:

default_data_dir = <new_path>\data

When you set this option SQLines will be able to find data directory and you do not need to specify absolute paths in options every time you run SQLines command line tool.

Note: If SQLines cannot find data_dir.txt file in the SQLines executable directory, it tries to read it in the current working directory.

data directory has the following structure:

  • templates directory - contains conversion and assessment templates for various databases and applications
  • reports directory - contains report templates
  • config.txt file - SQLines configuration file

Configuration file - config.txt

The configuration file config.txt contains SQLines and user-defined options that are used in the conversion templates.

By default, SQLines looks for the configuration file config.txt in data directory (see above), and if the file does not exist there, SQLines tries to read config.txt in the current working directory.

You can use -cfg command line option to specify different directory and file name for the configuration file.

Configuration File Syntax and Recommendations

In the configuration file, you can specify options and their values in the following format (one option per line):

option_name = value

You can specify a dash (-) before the option name, but it is not mandatory (dash is required in command line)

All options are case-insensitive, and you can specify them in any order.

The configuration file supports 2 types of comments: single-line comments specified by – or //, and multi-line comments enclosed by /* */

Configuration file does not support sections or namespaces in order to allow you specifying any option in the command line. But this may potentially cause option name conflicts.

For this reason, it is recommended to use the database name for database specific options (for example, oracle_refcursor). If an option is specific to a a project, use the project or company name when you name your options.

Option Priority

  • If you specify an option with the same name multiple times in the configuration file, the first value is taken
  • Options specified in the command line have priority over the configuration file options

Using Command Line

Any option including user-defined options can be specified in the command line. You have to use a dash (-) before the option name.

If an option accepts yes or no values, you may specify its name without a value. It is equivalent to specifying yes. So the following is the same in the command line (but not in the configuration file):

-trace
-trace = yes

If an option is specified in both command line and configuration file, the value from the command line is taken.

SQLines Engine Options

A list of general options used by the tool:

source

source option specifies source files for conversion or assessment. You can specify single or multiple files/paths separated by comma, and you can use wildcard characters * and ? both in file and directory names.

If you specify a directory for an item in the list, but do not specify directory for the following item, then the directory from the previous item is taken (see examples below for more details).

If search_source_subdir option is set to yes (this is the default), SQLines searches files in the sub-directories as well. But this option does not apply if wildcards are not specified in the source option (single file conversion i.e)

If directory is only specified, SQLines converts all files from it (*.* for files is applied). It is not required to end the path with a slash in this case.

If the last wildcard (for example, a* in d:\pro*\a*\) is for directories, end it with a slash, otherwise SQLines considers it as a file wildcard (searches for a* files, not directories).

This option is mandatory, you have to specify it either in the command line or the configuration file.

Examples:

-source = script.sql script.sql located in the working directory
-source = d:\projects\abc\script.sql script.sql located in d:\projects\abc\
-source = d:\projects\abc\script.sql, script2.sql script.sql and script2.sql both located in d:\projects\abc\
-source = d:\projects\abc
-source = d:\projects\abc\
-source = d:\projects\abc\*.*
all files in d:\projects\abc directory
-source = d:\projects\abc\*.sql, *.txt all *.sql and *.txt files in d:\projects\abc
-source = d:\projects\abc\*.*, d:\projects\def\*.* all files in d:\projects\abc and d:\projects\def directories
-source = d:\pro*\a*\*.*
-source = d:\pro*\a*\
all files in directories matching wildcard d:\pro*\a* (project1\abc, project2\adf i.e.)
-source = d:\pro*\a* files matching a* in directories matching wildcard d:\pro*

target

target option specifies the location for the converted files, report and SQLines log.

If you convert a single file (no wildcards are specified in source), you can specify:

  • a file name without directory (the converted file is stored in the working directory)
  • a directory with file name
  • a directory only (it must be ended with slash (\ for Windows and / for Unix/Linux, and the converted file has the same name as the source file).

If you convert multiple files, you have to specify directory name in the target option.

This option is mandatory, you have to specify it either in the command line or the configuration file.

Examples:

-target = script_result.sql target file script_result.sql located in the working directory
-target = result\script_tgt.sql target file is located in sub-directory result of the working directory
-target = d:\projects\abc\result\script_tgt.sql absolute path with file name is specified
-target = result for single file conversion, the target file is saved to result file
-target = result\ for single file conversion, the target file is saved in the result directory (file name is the same)
-target = result
-target = result\
for multiple file conversion, the target files are saved in the result directory (files names remain the same)

source_type and target_type

source_type and target_type options specify the types (names) of the source and target database and application (Oracle, SQL Server, C#, Java i.e).

You can use any name and synonym (as well as add your own synonyms or abbreviations) defined in names.txt file located in the templates directory of SQLines.

You can also easily add new database and application types and develop conversion templates for them.

If target_type option is not specified, SQLines uses source_type for the target as well.

assess_only

assess_only option specifies to run assessment only. When this option is set, SQLines does not convert the source files, but assesses them, collects information and generates the report file using a report template defined by report option.

During assessment, SQLines does not generate output (target) files for each source file.

Values - yes or no. Default value is no

report_template

report_template option specifies the location and name of the file describing the appearance and content of the conversion or assessment report (report template).

By default, as a report template SQLines uses sqlines_report.html file located in the reports subdirectory of the data directory.

If you specify a relative path in report_template option, it is relative to the current working directory (not SQLines data and executable directories).

After conversion or assessment is completed, SQLines generates the report file in the target directory (see -target option). The file has the same name as the report template (sqlines_report.html, by default).

You can specify a different location and file name for the generated report using -report option.

For more information on assessment and conversion reports, see Report Reference

template_dir

template_dir specifies the directory where the conversion templates are located. By default, the conversion templates are located in templates sub-directory of the SQLines executable path.

The template directory contains templates.txt file that specifies directories with the conversion templates for each source and target databases and applications. When SQLines initializes, it load templates for specified source and target databases and applications only.

quick_template

quick_template specifies a file containing conversion templates. Besides storing conversion templates in the templates directory, you can also supply an additional file with templates.

This template file can be useful during developing, testing and debugging new templates.

This option is also used by SQLines Online Conversion and SQLines Studio for Quick Templates window.

cfg

cfg option specifies the configuration file location and name. By default, SQLines reads config.txt file in data directory, and if the file is not found there, SQLines reads it in the current working directory.

If you specify directory only in the cgf option, the default file name is config.txt.

Examples:

-cfg = d:\projects\abc\abc_config.cfg
-cfg = d:\projects\abc d:\projects\abc\config.txt is used

log

log option specifies the location and name of the log file. By default, SQLines writes log to sqlines.log file in the target directory specified by target option or the current working directory if this option is not specified.

Using log option you can specify:

  • a file name without directory (log file is written to the target directory)
  • directory without file name. The directory must be ended with slash (\ for Windows and / for Unix/Linux), the default sqlines.log name is used for the log file
  • file name with directory

Examples:

-log = sqlines_20100917.log change log file name only
-log = d:\project1\ log is written to d:\project1\sqlines.log
-log = d:\project1\sqlines_20100917.log log is written to d:\project1\sqlines_20100917.log

report

report option specifies the location and name of the report file. By default, SQLines uses the report template file name (see report_template option), and creates the file in the target directory specified by target option or the current working directory if this option is not specified.

Using report option you can specify:

  • a file name without directory (report file is written to the target directory)
  • directory without file name. The directory must be ended with slash (\ for Windows and / for Unix/Linux), the default name is used
  • file name with directory

Examples:

-report = report_20100917.html change report file name only
-report = d:\project1\ report is written to d:\project1\<default_name>
-report = d:\project1\report_20100917.html report is written to d:\project1\report_20100917.html

all_reports

all_reports options specifies a file name containing a list of reports to generate. If the option is specified without file name, then all_reports.txt located in the data/reports directory is used.

session

session option defines the session name for SQLines.

trace

trace option specifies to run SQLines in the trace mode and write detailed information the log file.

If you specify trace option in the configuration file, you may not trace some initialization steps such as reading the configuration file, so it is recommended to specify trace option in the command line.

Values - yes and no. Default value is no.

search_source_subdir

search_source_subdir option specifies whether or not to search source files in sub-directories. When a single file is converted this option does not apply.

Values - yes and no. Default value is yes.

max_lines

max_lines option specifies the maximum number of lines per file to convert. By default, SQLines converts all lines of code.

max_files

max_files option specifies the maximum number of files to convert. By default, SQLines converts all source files that meet the file template specified by -source option.

test

test option specifies to run SQLines in the test mode for the specified source and target databases or applications specified (see options -source_type and -target_type)

In this mode, you do not supply SQLines with any source files. Instead, SQLines processes all conversion templates, extracts code samples from @examples directives and creates test files:

source2target_src.txt source test file, for example, sqlserver2mysql_src.txt
source2target_tgt.txt tests for validation, for example, sqlserver2mysql_tgt.txt

Then SQLines runs the test using source file source2target_src.txt (for example, sqlserver2mysql_src.txt if source is SQL Server and target is MySQL) and generates the result:

source2target_res.txt the current conversion result, for example, sqlserver2mysql_res.txt

You can compare source2target_res.txt and source2target_tgt.txt files to find any differences between the current conversion result and validated script.

All test files, logs and reports are stored in the target directory specified by option -target

doc

doc option is used to generate documentation for the specified source and target databases or applications (see options -source_type and -target_type)

When this option is specified, you do not need to supply SQLines with any source files. Instead, SQLines processes all conversion templates, extracts documentation from @doc and @doc_case directives, code samples from @examples directives and creates documentation in DokuWiki format.

A documentation file is created for each template file, and the documentation directory structure has the same hierarchy and file names as the templates files.

The documentation is created in the target directory specified by option -target.

There are 2 modes to generate documentation:

  • Source to all

When -source_type is only specified then SQLines generates documentation for migration from source to all other types specified in the templates.

  • Source to target

When both -source_type and -target_type are specified, SQLines generates documentation for migration from source to target (the source and target types, respectively).

Examples

1) The following command line generates documentation for MySQL to Oracle conversion:

   sqlines -source_type=mysql -target_type=oracle -target=d:\doc -doc 

As a result, documentation files are created in d:\doc\mysql-to-oracle directory.

2) The following command line generates conversion documentation from MySQL to all other databases:

   sqlines -source_type=mysql -target=d:\doc -doc 

As a result, documentation files are created in d:\doc\mysql directory.

User-Defined Options

You can define any custom options and then use them in the conversion templates. The custom options can be specified either in command line (use dash - before the option name) or in configuration file.

For example, you can set an option:

-sql_server_datetime = DATETIME2

and then use it in the conversion templates

  $if($option("sql_server_datetime") == DATETIME2) 
  {
     // ...  
  }

company, application_name and project_name

Company, application and project names to be used in reports i.e. You can add any other options describing your project (authors, version, contacts etc).

schema

schema option specifies the target schema name.

remove_schema

When remove_schema option is set to yes the schema name is removed in the target scripts.