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 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 files
"csv-all": copy from databse to .csv 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 it if there are multiple versions of the model.

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

dbcopy -m modelOne

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.ToDatabase "Database=dst.sqlite;OpenMode=ReadWrite"

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

dbcopy -m modelOne -dbcopy.To csv

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

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

dbcopy -m modelOne -dbcopy.To csv-all

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

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 redModel -s Default
dbcopy -m redModel -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.FisrtRun

To copy only one modeling task metadata and run history:

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

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

dbcopy -m modelOne -dbcopy.Zip=true
dbcopy -m modelOne -dbcopy.Zip
dbcopy -m redModel -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 redModel -dbcopy.OutputDir red -s Default
dbcopy -m redModel -dbcopy.InputDir red -dbcopy.To db -dbcopy.ToDatabase "Database=dst.sqlite;OpenMode=ReadWrite"
dbcopy -m redModel -dbcopy.OutputDir red -s Default

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 redModel -dbcopy.OutputDir red -s Default

will place "Default" input set of parameters into directory red/redModel.set.Default.

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

dbcopy -m modelOne -dbcopy.SetId 2 -dbcopy.ParamDir two
dbcopy -m modelOne -dbcopy.SetId 2 -p two
dbcopy -m redModel -s Default -p 101 -dbcopy.To db -dbcopy.ToDatabase "Database=dst.sqlite;OpenMode=ReadWrite"

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

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 redModel -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 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

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"

OpenM++ using hash digest to compare models, input parameters and output values. By default float and double values converted into text with "%.15g" format. It is possible to specify other format for float values digest calculation:

dbcopy -m redModel -dbcopy.DoubleFormat "%.7G" -dbcopy.To db -dbcopy.ToDatabase "Database=dst.sqlite;OpenMode=ReadWrite"

By default dbcopy using SQLite database connection:

dbcopy -m modelOne

is equivalent of:

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

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.ToDatabaseDriver SQLite -dbcopy.ToDatabase "Database=dst.sqlite; Timeout=86400; OpenMode=ReadWrite;"

or skip default database driver name "SQLite":

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

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.

Also dbcopy support OpenM++ standard log settings (described in wiki at http://www.openmpp.org/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