dbcopy

Documentation

Overview

dbcopy is command line tool for import-export OpenM++ model metadata, input parameters and run results.

Dbcopy support 5 possible -dbcopy.To directions:

"text":    copy from database to .json and .csv or .tsv files (this is default)
"db":      copy from .json and .csv files to database
"db2db":   copy from one database to other
"csv":     copy from databse to .csv or .tsv files
"csv-all": copy from databse to .csv or .tsv files

Dbcopy also can delete entire model or model run results, set of input parameters or modeling task from database (see dbcopy.Delete below). Dbcopy also can rename model run results, set of input parameters or modeling task in database (see dbcopy.Rename below).

Arguments for dbcopy can be specified on command line or through .ini file:

dbcopy -ini my.ini
dbcopy -OpenM.IniFile my-dbcopy.ini

Command line arguments take precedence over ini-file options.

Only model argument does not have default value and must be specified explicitly:

dbcopy -m modelOne
dbcopy -dbcopy.ModelName modelOne
dbcopy -dbcopy.ModelDigest 649f17f26d67c37b78dde94f79772445

Model digest is globally unique and you may want to use it if there are multiple versions of the model.

To produce TSV output files instead of CSV use -dbcopy.IntoTsv option.

Copy to "text": read from database and save into metadata .json and .csv or .tsv values (parameters and output tables):

dbcopy -m modelOne
dbcopy -m modelOne -dbcopy.IntoTsv

Copy to "db": read from metadata .json and .csv values and insert or update database:

dbcopy -m modelOne -dbcopy.To db

Copy to "db2db": direct copy between two databases:

dbcopy -m modelOne -dbcopy.To db2db -dbcopy.ToSqlite modelOne.sqlite

Copy to "csv": read entire model from database and save into .csv or .tsv files:

dbcopy -m modelOne -dbcopy.To csv
dbcopy -m modelOne -dbcopy.To csv -dbcopy.IntoTsv

Separate sub-directory created for each input set and each model run results.

Copy to "csv-all": read entire model from database and save into .csv or .tsv files:

dbcopy -m modelOne -dbcopy.To csv-all
dbcopy -m modelOne -dbcopy.To csv-all -dbcopy.IntoTsv

It dumps all input parameters sets into all_input_sets/parameterName.csv (or .tsv) files. And for all model runs input parameters and output tables saved into all_model_runs/tableName.csv (or .tsv) files.

By default if output directory already exist then dbdopy delete it first to create a clean output results. If you want to keep existing output directory then use -dbcopy.KeepOutputDir true:

dbcopy -m modelOne -dbcopy.KeepOutputDir true

By default entire model data is copied. It is also possible to copy only: model run results and input parameters, set of input parameters (workset), modeling task metadata and task run history.

To copy only one set of input parameters:

dbcopy -m modelOne -s Default
dbcopy -m modelOne -dbcopy.SetName Default

To copy only one model run results and input parameters:

dbcopy -m modelOne -dbcopy.RunId 101
dbcopy -m modelOne -dbcopy.RunDigest d722febf683992aa624ce9844a2e597d
dbcopy -m modelOne -dbcopy.RunName "My Model Run"

Model run name is not unique and by default first model run with such name is used. To use last model run or first model run do:

dbcopy -m modelOne -dbcopy.RunName "My Model Run" -dbcopy.LastRun
dbcopy -m modelOne -dbcopy.LastRun
dbcopy -m modelOne -dbcopy.FirstRun

To copy only one modeling task metadata and run history:

dbcopy -m modelOne -dbcopy.TaskId 1
dbcopy -m modelOne -dbcopy.TaskName taskOne

It is convenient to pack (unpack) text files into .zip archive:

dbcopy -m modelOne -dbcopy.Zip=true
dbcopy -m modelOne -dbcopy.Zip
dbcopy -m modelOne -dbcopy.SetName Default -dbcopy.Zip

By default model name is used to create output directory for text files or as input directory to import from. It may be a problem on Linux if current directory already contains executable "modelName".

To specify output or input directory for text files:

dbcopy -m modelOne -dbcopy.OutputDir one
dbcopy -m modelOne -dbcopy.OutputDir one -s Default
dbcopy -m modelOne -dbcopy.InputDir one -dbcopy.To db -dbcopy.ToSqlite oneModel.sqlite

If you are using InputDir or OutputDir result path combined with model name, model run name or name of input parameters set to prevent path conflicts. For example:

dbcopy -m modelOne -dbcopy.OutputDir one -s Default

will place "Default" input set of parameters into directory one/modelOne.set.Default.

If neccesary you can specify exact directory for input parameters by using "-dbcopy.ParamDir" or "-p":

dbcopy -m modelOne -s Custom -dbcopy.ParamDir two
dbcopy -m modelOne -s Custom -dbcopy.ParamDir two -dbcopy.Zip
dbcopy -m modelOne -s Custom -dbcopy.ParamDir two -dbcopy.To db
dbcopy -m modelOne -s Custom -dbcopy.ParamDir two -dbcopy.To db -dbcopy.Zip
dbcopy -m modelOne -s Custom -dbcopy.ParamDir two -dbcopy.OutputDir my-m1 -dbcopy.To db -dbcopy.Zip

Dbcopy create output directories (and json files) for model data by combining model name and run name or input set name. By default names may be combined with run id (set id) to make it unique. For example:

json file: modelName.run.1234.MyRun.json
directory: modelName/run.1234.MyRun

In case of output into csv by default directories and files combined with id's only if run name is not unique. To explicitly control usage of id's in directory and file names use IdOutputNames=true or IdOutputNames=false:

dbcopy -m modelOne -dbcopy.To csv
dbcopy -m modelOne -dbcopy.To csv -dbcopy.IdOutputNames=true
dbcopy -m modelOne -dbcopy.To csv -dbcopy.IdOutputNames=false

Dbcopy create csv files for model parameters, microdata output tables value(s) and accumulators. It is often accumulators or microdata not required and you can suppress by using NoAccumulatorsCsv=true or NoMicrodata=true:

dbcopy -m modelOne -dbcopy.NoAccumulatorsCsv
dbcopy -m modelOne -dbcopy.NoAccumulatorsCsv=true
dbcopy -m modelOne -dbcopy.NoAccumulatorsCsv -dbcopy.To csv
dbcopy -m modelOne -dbcopy.NoAccumulatorsCsv -dbcopy.LastRun
dbcopy -m modelOne -dbcopy.NoAccumulatorsCsv -dbcopy.TaskName taskOne
dbcopy -m modelOne -dbcopy.NoMicrodata
dbcopy -m modelOne -dbcopy.NoMicrodata=true
dbcopy -m modelOne -dbcopy.NoAccumulatorsCsv -dbcopy.NoMicrodata

By default parameters and output results .csv files contain codes in dimension column(s), e.g.: Sex=[Male,Female]. If you want to create csv files with numeric id's Sex=[0,1] instead then use IdCsv=true option:

dbcopy -m modelOne -dbcopy.IdCsv
dbcopy -m modelOne -dbcopy.IdCsv -dbcopy.To csv
dbcopy -m modelOne -dbcopy.IdCsv -s Default
dbcopy -m modelOne -dbcopy.IdCsv -dbcopy.RunId 101
dbcopy -m modelOne -dbcopy.IdCsv -dbcopy.RunDigest d722febf683992aa624ce9844a2e597d
dbcopy -m modelOne -dbcopy.IdCsv -dbcopy.TaskName taskOne

Dbcopy can transfer the data between differnt versions of the same model or even between different models. For example, it is possible create new input set of parameters just from csv file(s) with model data, nothing else is required. On the other hand dbcopy package output data with model metadata (e.g. parameter name, model name, model digest, etc.). If JSON metadata file(s) are supplied then dbcopy using it for validation to make sure input data match destination model. It may be neccessary to disable model digest validation In order to transfer data between diffrent versions of the model. You can do it by editing JSON file in text editor or by using NoDigestCheck=true:

dbcopy -m modelOne -dbcopy.To db -dbcopy.SetName MyData  -dbcopy.NoDigestCheck
dbcopy -m modelOne -dbcopy.To db -dbcopy.SetName MyData  -dbcopy.NoDigestCheck=true
dbcopy -m modelOne -dbcopy.To db -dbcopy.RunName MyResut -dbcopy.NoDigestCheck

To delete from database entire model, model run results, set of input parameters or modeling task:

dbcopy -m modelOne -dbcopy.Delete
dbcopy -m modelOne -dbcopy.Delete -dbcopy.RunId 101
dbcopy -m modelOne -dbcopy.Delete -dbcopy.RunName "My Model Run"
dbcopy -m modelOne -dbcopy.Delete -dbcopy.RunDigest d722febf683992aa624ce9844a2e597d
dbcopy -m modelOne -dbcopy.Delete -dbcopy.FirstRun
dbcopy -m modelOne -dbcopy.Delete -dbcopy.LastRun
dbcopy -m modelOne -dbcopy.Delete -dbcopy.SetId 2
dbcopy -m modelOne -dbcopy.Delete -s Default
dbcopy -m modelOne -dbcopy.Delete -dbcopy.TaskId 1
dbcopy -m modelOne -dbcopy.Delete -dbcopy.TaskName taskOne

To rename model run results, input set of parameters or modeling task:

dbcopy -m modelOne -dbcopy.Rename -dbcopy.RunId 101 -dbcopy.ToRunName New_Run_Name
dbcopy -m modelOne -dbcopy.Rename -dbcopy.RunName "My Model Run" -dbcopy.ToRunName "New Run Name"
dbcopy -m modelOne -dbcopy.Rename -dbcopy.RunDigest d722febf683992aa624ce9844a2e597d -dbcopy.ToRunName "New Run Name"
dbcopy -m modelOne -dbcopy.Rename -dbcopy.FirstRun -dbcopy.ToRunName "New Run Name"
dbcopy -m modelOne -dbcopy.Rename -dbcopy.LastRun  -dbcopy.ToRunName "New Run Name"
dbcopy -m modelOne -dbcopy.Rename -s Default -dbcopy.ToSetName "New Name"
dbcopy -m modelOne -dbcopy.Rename -dbcopy.SetName Default -dbcopy.ToSetName "New Name"
dbcopy -m modelOne -dbcopy.Rename -dbcopy.SetId 2 -dbcopy.ToSetName "New Name"
dbcopy -m modelOne -dbcopy.Rename -dbcopy.TaskName taskOne -dbcopy.ToTaskName "New Task Name"
dbcopy -m modelOne -dbcopy.Rename -dbcopy.TaskId 1 -dbcopy.ToTaskName "New Task Name"

By default float and double values converted into csv text with "%.15g" format. It is possible to specify other format for float values values:

dbcopy -m modelOne -dbcopy.DoubleFormat "%.7G"

You can suppress zero values and / or NULL (missing) values in output tables or microdata CSV files:

dbcopy -m modelOne -dbcopy.To csv -dbcopy.NoZeroCsv
dbcopy -m modelOne -dbcopy.To csv -dbcopy.NoZeroCsv=true
dbcopy -m modelOne -dbcopy.To csv -dbcopy.NoNullCsv
dbcopy -m modelOne -dbcopy.To csv -dbcopy.NoNullCsv=true
dbcopy -m modelOne -dbcopy.To csv -dbcopy.NoZeroCsv -dbcopy.NoNullCsv

Dbcopy do auto detect input files encoding to convert source text into utf-8. On Windows you may want to expliciltly specify encoding name:

dbcopy -m modelOne -dbcopy.To db -dbcopy.CodePage windows-1252

If you want to write utf-8 BOM into output csv file then:

dbcopy -m modelOne -dbcopy.Utf8BomIntoCsv
dbcopy -m modelOne -dbcopy.Utf8BomIntoCsv -dbcopy.To csv

By default dbcopy using SQLite database connection:

dbcopy -m modelOne

is equivalent of:

dbcopy -m modelOne -dbcopy.FromSqlite modelOne.sqlite
dbcopy -m modelOne -dbcopy.Database "Database=modelOne.sqlite; Timeout=86400; OpenMode=ReadOnly;"
dbcopy -m modelOne -dbcopy.Database "Database=modelOne.sqlite; Timeout=86400; OpenMode=ReadOnly;" -dbcopy.DatabaseDriver SQLite

Output database connection settings by default are the same as input database, which may not be suitable because you don't want to overwrite input database.

To specify output database connection string and driver:

dbcopy -m modelOne -dbcopy.To db -dbcopy.ToSqlite modelOne.sqlite
dbcopy -m modelOne -dbcopy.To db -dbcopy.ToDatabase "Database=modelOne.sqlite; Timeout=86400; OpenMode=ReadWrite;"
dbcopy -m modelOne -dbcopy.To db -dbcopy.ToDatabase "Database=modelOne.sqlite; Timeout=86400; OpenMode=ReadWrite;" -dbcopy.ToDatabaseDriver SQLite

Other supported database drivers are "sqlite3" and "odbc":

dbcopy -m modelOne -dbcopy.To db -dbcopy.ToDatabaseDriver odbc -dbcopy.ToDatabase "DSN=bigSql"
dbcopy -m modelOne -dbcopy.To db -dbcopy.ToDatabaseDriver sqlite3 -dbcopy.ToDatabase "file:dst.sqlite?mode=rw"

ODBC dbcopy tested with MySQL (MariaDB), PostgreSQL, Microsoft SQL, Oracle and DB2.

If dbcopy used for massive database copy it may be convenient to control it from shell script by process ID:

dbcopy -dbcopy.PidSaveTo some/dir/dbcopy.pid.txt

Also dbcopy support OpenM++ standard log settings (described in openM++ wiki):

-OpenM.LogToConsole: if true then log to standard output, default: true
-v:                  short form of: -OpenM.LogToConsole
-OpenM.LogToFile:    if true then log to file
-OpenM.LogFilePath:  path to log file, default = current/dir/exeName.log
-OpenM.LogUseTs:     if true then use time-stamp in log file name
-OpenM.LogUsePid:    if true then use pid-stamp in log file name
-OpenM.LogSql:       if true then log sql statements into log file