dbget

Documentation

Overview

dbget is a command line tool to export OpenM++ model metadata, input parameters and run results. It is reading from model database and produce CSV, TSV or JSON output.

Most generic format to specify source data is to use connection string and driver name:

dbget
  -dbget.Do model-list
  -dbget.Database "Database=model.sqlite; Timeout=86400; OpenMode=ReadOnly;"
  -dbget.DatabaseDriver SQLite

Dget can read model data from SQLite, MySQL, PostgreSQL, MS SQL, Oracle and DB2.

By default openM++ is using SQLite database and it is enough to specife path to model.sqlite file:

dbget -do model-list -db some/dir/model.sqlite
dbget -do model-list -dbget.Sqlite some/dir/model.sqlite

If SQLite database file name is the same as model name and located in current directory then it is enough to specify model name only:

dbget -m modelOne -do run-list

As result of above command dbget will open modelOne.sqlite database file in current directory and do "run-list" output list of model runs into CSV file.

Most often used options of dbget do have a short form to reduce typing on command line. For example: -db is a short version of: -dbget.Sqlite option and -do is a short of -dbget.Do. Longer version of options can be used on command line and ini files.

For example, if there is my.ini file:

[dbget]
Do     = model-list             ; dbget action: 'model-iist' = get list of the models
Sqlite = some/dir/model.sqlite  ; path to model SQLite database file

then commands below are equal:

dbget -ini           my.ini
dbget -OpenM.IniFile my.ini
dbget -do       model-list -db           some/dir/model.sqlite
dbget -dbget.Do model-list -dbget.Sqlite some/dir/model.sqlite

By default dbget produce .csv output file(s), e.g. commands above will create model-list.csv file. It is also possible to produce .tsv output and, for some commands, .json output:

dbget -db modelOne.sqlite -do model-list
dbget -db modelOne.sqlite -do model-list -csv
dbget -db modelOne.sqlite -do model-list -tsv
dbget -db modelOne.sqlite -do model-list -json
dbget -db modelOne.sqlite -do model-list -dbget.As csv
dbget -db modelOne.sqlite -do model-list -dbget.As tsv
dbget -db modelOne.sqlite -do model-list -dbget.As json

By default dbget write results into the file and user can redirect it to console:

dbget -db modelOne.sqlite -do model-list -dbget.ToConsole
dbget -db modelOne.sqlite -do model-list -pipe

It is convenient to use -pipe as a short form of: -dbget.ToConsole -OpenM.LogToConsole=false to produce output suitable for command pipes.

**Important:** By using -pipe you are suppressing any console error message output and therefore you must check dbget exit code or enable additonal log output to file by using -OpenM.LogToFile option.

By default dbget produces language specific output based on match of user OS language to model languages. For example, if user OS language is fr-CA then output will be created from model FR language, if it is exists in the model database. If there are no laguage matched then output created in default model language.

dbget -m modelOne -do all-runs

Above -do all-runs option produce output of all modelOne model runs input parameters and output tables data into .csv files. Dimension labels in those .csv files are language specific, for example it can be Männlich, Weiblich for Deutsche OS version.

User can override default OS language:

dbget -m modelOne -do all-runs -lang FR
dbget -m modelOne -do all-runs -lang fr-CA
dbget -m modelOne -do all-runs -lang isl
dbget -m modelOne -do all-runs -dbget.Language EN
dbget -m modelOne -do all-runs -dbget.Language en-CA
dbget -m modelOne -do all-runs -dbget.Language isl

If isl = Icelandic language not found in model database then closest languge will be used, for example: DA, or, if no match found in database then it is a default model language.

If user do not want language specific labels in the output then -dbget.NoLanguage option can be used. In that case dimension items will be M, F codes instead of Male, Female lables.

dbget -m modelOne -do all-runs -dbget.NoLanguage

If user want language neutral output with dimension items id's: 0, 1 instead codes: M, F then -dbget.IdCsv option can be used. In that case dimension items will be M, F codes instead of Male, Female lables.

dbget -m modelOne -do all-runs -dbget.IdCsv

**dbget commands (actions)**

model-list       list of the models in database
model            model metadata
run-list         list of model runs
set-list         list of model input scenarios (a.k.a. "input set" or workset)
run              model run results: all parameters, output tables and microdata
all-runs         all model runs, all parameters, output tables and microdata
set              input scenario parameters
all-sets         all input scenarios, all parameter values
parameter        model run parameter values
parameter-set    input scenario parameter values
table            output table values (expressions)
sub-table        output table sub-values (a.k.a. sub-samples or accumulators)
sub-table-all    output table sub-values, including derived
micro            microdata values from model run results
micro-compare    compare or aggregate microdata between model runs
old-model        model metadata in Modgen compatible form
old-run          first model run results in Modgen compatible form
old-parameter    parameter values in Modgen compatible form
old-table        output table values in Modgen compatible form

Get list of the models from database:

dbget -db modelOne.sqlite -do model-list

dbget -db modelOne.sqlite -do model-list -dbget.As csv
dbget -db modelOne.sqlite -do model-list -dbget.As tsv
dbget -db modelOne.sqlite -do model-list -dbget.As json

dbget -db modelOne.sqlite -do model-list -csv  -dbget.ToConsole
dbget -db modelOne.sqlite -do model-list -tsv  -dbget.ToConsole
dbget -db modelOne.sqlite -do model-list -json -dbget.ToConsole
dbget -db modelOne.sqlite -do model-list -tsv  -pipe

dbget -db modelOne.sqlite -do model-list -dbget.Language EN
dbget -db modelOne.sqlite -do model-list -lang fr-CA
dbget -db modelOne.sqlite -do model-list -lang isl

dbget -db modelOne.sqlite -do model-list -dbget.Notes -lang en-CA
dbget -db modelOne.sqlite -do model-list -dbget.Notes -lang fr-CA
dbget -db modelOne.sqlite -do model-list -dbget.Notes -lang isl
dbget -db modelOne.sqlite -do model-list -dbget.NoLanguage

dbget -dbget.Sqlite my/dir/modelOne.sqlite -dbget.Do model-list

dbget
  -dbget.Do model-list
  -dbget.Database "Database=model.sqlite; Timeout=86400; OpenMode=ReadOnly;"
  -dbget.DatabaseDriver SQLite

Get model metadata from database:

dbget -m modelOne -do model
dbget -m modelOne -do model -csv
dbget -m modelOne -do model -tsv
dbget -m modelOne -do model -json
dbget -m modelOne -do model -pipe
dbget -m modelOne -do model -lang en-CA
dbget -m modelOne -do model -lang fr-CA
dbget -m modelOne -do model -lang isl
dbget -m modelOne -do model -lang fr-CA -dbget.Notes
dbget -m modelOne -do model -dbget.NoLanguage
dbget -m modelOne -do model -dir my/output/dir
dbget -m modelOne -do model -f my-model.csv

dbget -dbget.ModelName modelOne -dbget.Do model -dbget.As csv -dbget.ToConsole -dbget.Language FR

Get list of model runs:

dbget -m modelOne -do run-list
dbget -m modelOne -do run-list -csv
dbget -m modelOne -do run-list -tsv
dbget -m modelOne -do run-list -json
dbget -m modelOne -do run-list -lang fr-CA
dbget -m modelOne -do run-list -dbget.NoLanguage
dbget -m modelOne -do run-list -dir my/output/dir
dbget -m modelOne -do run-list -f my-runs.csv
dbget -m modelOne -do run-list -pipe
dbget -m modelOne -do run-list -lang fr-CA -dbget.Notes

dbget -db my/dir/modelOne.sqlite -dbget.ModelName modelOne -dbget.Do run-list

Get all model runs parameters and output table values:

dbget -m modelOne -do all-runs
dbget -m modelOne -do all-runs -lang fr-CA
dbget -m modelOne -do all-runs -dbget.NoLanguage
dbget -m modelOne -do all-runs -dbget.IdCsv
dbget -m modelOne -do all-runs -tsv
dbget -m modelOne -do all-runs -dir my/output/dir
dbget -m modelOne -do all-runs -pipe
dbget -m modelOne -do all-runs -dbget.NoZeroCsv
dbget -m modelOne -do all-runs -dbget.NoNullCsv
dbget -m modelOne -do all-runs -dbget.NoZeroCsv -dbget.NoNullCsv

dbget -dbget.ModelName modelOne -dbget.Do all-runs

Get model run parameters and output table values:

dbget -m modelOne -do run -dbget.FirstRun
dbget -m modelOne -do run -dbget.LastRun
dbget -m modelOne -do run -r Default-4
dbget -m modelOne -do run -r Default-4 -lang fr-CA
dbget -m modelOne -do run -r Default-4 -dbget.NoLanguage
dbget -m modelOne -do run -r Default-4 -dbget.IdCsv
dbget -m modelOne -do run -r Default-4 -tsv
dbget -m modelOne -do run -r Default-4 -pipe
dbget -m modelOne -do run -r Default-4 -dbget.NoZeroCsv
dbget -m modelOne -do run -r Default-4 -dbget.NoNullCsv
dbget -m modelOne -do run -r Default-4 -dbget.NoZeroCsv -dbget.NoNullCsv

dbget -dbget.ModelName modelOne -dbget.Do run -dbget.Run Default

Get parameter run values:

dbget -m modelOne -r Default -parameter ageSex
dbget -m modelOne -r Default -parameter ageSex -lang fr-CA
dbget -m modelOne -r Default -parameter ageSex -dbget.NoLanguage
dbget -m modelOne -r Default -parameter ageSex -dbget.IdCsv
dbget -m modelOne -r Default -parameter ageSex -tsv
dbget -m modelOne -r Default -parameter ageSex -pipe

dbget -m modelOne -dbget.FirstRun -parameter ageSex
dbget -m modelOne -dbget.LastRun  -parameter ageSex

dbget -dbget.ModelName modelOne -dbget.Do parameter -dbget.Run Default -dbget.Parameter ageSex

Get output table values:

dbget -m modelOne -r Default -table ageSexIncome
dbget -m modelOne -r Default -table ageSexIncome -lang fr-CA
dbget -m modelOne -r Default -table ageSexIncome -dbget.NoLanguage
dbget -m modelOne -r Default -table ageSexIncome -dbget.IdCsv
dbget -m modelOne -r Default -table ageSexIncome -tsv
dbget -m modelOne -r Default -table ageSexIncome -pipe
dbget -m modelOne -r Default -table ageSexIncome -dbget.NoZeroCsv
dbget -m modelOne -r Default -table ageSexIncome -dbget.NoNullCsv

dbget -m modelOne -dbget.FirstRun -table ageSexIncome
dbget -m modelOne -dbget.LastRun  -table ageSexIncome

dbget -dbget.ModelName modelOne -dbget.Do table -dbget.Run Default -dbget.Table ageSexIncome

Get output table sub-values (get accumulators):

dbget -m modelOne -r Default -sub-table ageSexIncome
dbget -m modelOne -r Default -sub-table ageSexIncome -lang fr-CA
dbget -m modelOne -r Default -sub-table ageSexIncome -dbget.NoLanguage
dbget -m modelOne -r Default -sub-table ageSexIncome -dbget.IdCsv
dbget -m modelOne -r Default -sub-table ageSexIncome -tsv
dbget -m modelOne -r Default -sub-table ageSexIncome -pipe
dbget -m modelOne -r Default -sub-table ageSexIncome -dbget.NoZeroCsv
dbget -m modelOne -r Default -sub-table ageSexIncome -dbget.NoNullCsv

dbget -m modelOne -dbget.FirstRun -sub-table ageSexIncome
dbget -m modelOne -dbget.LastRun  -sub-table ageSexIncome

dbget -dbget.ModelName modelOne -dbget.Do sub-table -dbget.Run Default -dbget.Table ageSexIncome

Get output table all sub-values, including derived (get all accumulators):

dbget -m modelOne -r Default -sub-table-all ageSexIncome
dbget -m modelOne -r Default -sub-table-all ageSexIncome -lang fr-CA
dbget -m modelOne -r Default -sub-table-all ageSexIncome -dbget.NoLanguage
dbget -m modelOne -r Default -sub-table-all ageSexIncome -dbget.IdCsv
dbget -m modelOne -r Default -sub-table-all ageSexIncome -tsv
dbget -m modelOne -r Default -sub-table-all ageSexIncome -pipe
dbget -m modelOne -r Default -sub-table-all ageSexIncome -dbget.NoZeroCsv
dbget -m modelOne -r Default -sub-table-all ageSexIncome -dbget.NoNullCsv

dbget -m modelOne -dbget.FirstRun -sub-table-all ageSexIncome
dbget -m modelOne -dbget.LastRun  -sub-table-all ageSexIncome -tsv -pipe
dbget -m modelOne -dbget.LastRun  -sub-table-all ageSexIncome -tsv -pipe -dbget.NoZeroCsv -dbget.NoNullCsv

dbget -dbget.ModelName modelOne -dbget.Do sub-table-all -dbget.Run Default -dbget.Table ageSexIncome

Get list of input parameters sets (list of input scenarios, list of worksets):

dbget -m modelOne -do set-list
dbget -m modelOne -do set-list -csv
dbget -m modelOne -do set-list -tsv
dbget -m modelOne -do set-list -json
dbget -m modelOne -do set-list -lang fr-CA
dbget -m modelOne -do set-list -dbget.NoLanguage
dbget -m modelOne -do set-list -dir my/output/dir
dbget -m modelOne -do set-list -f my-scenarios.csv
dbget -m modelOne -do set-list -pipe
dbget -m modelOne -do set-list -lang fr-CA -dbget.Notes

dbget -db my/dir/modelOne.sqlite -dbget.ModelName modelOne -dbget.Do set-list

Get all parameters from all input sets (a.k.a. input scenarios or worksets):

dbget -m modelOne -do all-sets
dbget -m modelOne -do all-sets -lang fr-CA
dbget -m modelOne -do all-sets -dbget.NoLanguage
dbget -m modelOne -do all-sets -dbget.IdCsv
dbget -m modelOne -do all-sets -tsv
dbget -m modelOne -do all-sets -pipe

dbget -dbget.ModelName modelOne -dbget.Do all-sets

Get parameter input set (a.k.a. input scenario or workset) values:

dbget -m modelOne -s Default -parameter-set ageSex
dbget -m modelOne -s Default -parameter-set ageSex -lang fr-CA
dbget -m modelOne -s Default -parameter-set ageSex -dbget.NoLanguage
dbget -m modelOne -s Default -parameter-set ageSex -dbget.IdCsv
dbget -m modelOne -s Default -parameter-set ageSex -tsv
dbget -m modelOne -s Default -parameter-set ageSex -pipe

dbget -dbget.ModelName modelOne -dbget.Do parameter-set -dbget.Set Default -dbget.Parameter ageSex

Get all parameters from input set (a.k.a. input scenario or workset):

dbget -m modelOne -s Default -do set
dbget -m modelOne -s Default -do set -lang fr-CA
dbget -m modelOne -s Default -do set -dbget.NoLanguage
dbget -m modelOne -s Default -do set -dbget.IdCsv
dbget -m modelOne -s Default -do set -tsv
dbget -m modelOne -s Default -do set -pipe

dbget -dbget.ModelName modelOne -dbget.Do set -dbget.Set Default

Get entity microdata:

dbget -m modelOne -r "Microdata in database" -micro Person
dbget -m modelOne -r "Microdata in database" -micro Person -lang fr-CA
dbget -m modelOne -r "Microdata in database" -micro Person -dbget.NoLanguage
dbget -m modelOne -r "Microdata in database" -micro Person -dbget.IdCsv
dbget -m modelOne -r "Microdata in database" -micro Person -tsv
dbget -m modelOne -r "Microdata in database" -micro Person -pipe
dbget -m modelOne -r "Microdata in database" -micro Person -dbget.NoZeroCsv
dbget -m modelOne -r "Microdata in database" -micro Person -dbget.NoNullCsv

dbget -dbget.ModelName modelOne -dbget.Do micro -dbget.Run "Microdata in database" -dbget.Entity Person

Compare or aggregate values for model run output tables

Compare first and last RiskPaths model runs: calculate differnce of T04_FertilityRatesByAgeGroup.Expr0 values

dbget -m RiskPaths -do table-compare
  -dbget.FirstRun
  -dbget.WithLastRun
  -dbget.Table     T04_FertilityRatesByAgeGroup
  -dbget.Calculate Expr0[variant]-Expr0[base]

Or:

dbget -m RiskPaths -do table-compare
  -dbget.FirstRun
  -dbget.WithLastRun
  -dbget.Table  T04_FertilityRatesByAgeGroup
  -calc         Expr0[variant]-Expr0[base]

Aggregate sub-values: calculate variance of T04_FertilityRatesByAgeGroup.acc0, using RiskPaths model last run:

dbget -m RiskPaths -do table-compare
  -dbget.LastRun
  -dbget.Table     T04_FertilityRatesByAgeGroup
  -dbget.Aggregate OM_VAR(acc0)

Or:

dbget -m RiskPaths -do table-compare
  -dbget.LastRun
  -dbget.Table T04_FertilityRatesByAgeGroup
  -aggr        OM_VAR(acc0)

Compare and aggregate Riskpaths output table T04_FertilityRatesByAgeGroup: - output Expr0 measure values as-is, without any transformation - output the differnce between Expr0 variant and base run values (between last and first model runs) - output standard deviation of acc0 and acc1

dbget -m RiskPaths -do table-compare
  -dbget.FirstRun
  -dbget.WithLastRun
  -dbget.Table  T04_FertilityRatesByAgeGroup
  -calc         "Expr0       , Expr0[variant] - Expr0[base]"
  -aggr         "OM_SD(acc0) , OM_SD(acc1)"

Default output lables for comparison and aggreagtion expessions are generated automatically, use -dbget.CalcName or -dbget.AggrName to specify desired labels:

dbget -m RiskPaths -do table-compare
  -dbget.FirstRun
  -dbget.WithLastRun
  -dbget.Table    T04_FertilityRatesByAgeGroup
  -calc           "Expr0       , Expr0[variant] - Expr0[base]"
  -dbget.CalcName "Expr0       , Diffrence of Expr0 in last and first run"
  -aggr           "OM_SD(acc0) , OM_SD(acc1)"
  -dbget.AggrName "SD of Acc0  , SD of Acc1"

Model run can be specfied by run id or by name, run stamp or run digest:

dbget -m RiskPaths -do table-compare
  -dbget.Run        RiskPaths_Default
  -dbget.WithRunIds 108,209,310
  -dbget.Table      T04_FertilityRatesByAgeGroup
  -calc             "Expr0       , Expr0[variant] - Expr0[base]"
  -aggr             "OM_SD(acc0) , OM_SD(acc1)"

Compare or aggregate microdata run values.

Aggregate: average AgeGroup Income of entity Person in model run with id 219:

dbget -m modelOne -do micro-compare
  -dbget.RunId     219
  -dbget.Entity    Person
  -dbget.GroupBy   AgeGroup
  -dbget.Aggregate OM_AVG(Income)

Model run can be specfied by run id or by name, run stamp, run digest:

dbget -m modelOne -do micro-compare
  -pipe
  -tsv
  -dbget.Run     "Microdata in database"
  -dbget.Entity  Person
  -dbget.GroupBy AgeGroup
  -aggr          OM_AVG(Income)

Compare microdata first and last model run microdata by calculating for each Person.AgeGroup average of: Income[base] - Income[variant]:

dbget -m MyModel -do micro-compare
  -dbget.FirstRun
  -dbget.WithLastRun
  -dbget.Entity  Person
  -dbget.GroupBy AgeGroup
  -aggr          OM_AVG(Income[base]-Income[variant])

For each Person.AgeGroup calculate: - average Income model runs with id 219, 221, 222 - average between Income[base] run id 219 and Income[variant] runs id: 221, 222 - ratio of average Income[variant] / Income[base] model run

dbget -m modelOne -do micro-compare
  -tsv
  -dbget.RunId      219
  -dbget.WithRunIds 221,222
  -dbget.Entity     Person
  -dbget.GroupBy    AgeGroup
  -aggr  "OM_AVG(Income), OM_AVG(Income[base] - Income[variant]), OM_AVG(Income[variant]) / OM_AVG(Income[base])"

Default lables for aggreagtion expessions are generated automatically, use -dbget.AggrName to specify desired labels:

dbget -m modelOne
  -do micro-compare
  -r "Microdata in database"
  -dbget.Entity   Person
  -dbget.GroupBy  AgeGroup,Sex
  -aggr          "OM_AVG(Income), OM_VAR(Income)"
  -dbget.AggrName "Average Income, Income Variance"

Backward compatibility (Modgen).

Get model metadata from compatibility (Modgen) views:

dbget -m modelOne -do old-model
dbget -m modelOne -do old-model -csv
dbget -m modelOne -do old-model -tsv
dbget -m modelOne -do old-model -json
dbget -m modelOne -do old-model -pipe

dbget -dbget.ModelName modelOne -dbget.Do old-model -dbget.As csv -dbget.ToConsole -dbget.Language FR

Get model run parameters and output tables values from compatibility (Modgen) views:

dbget -m modelOne -do old-run
dbget -m modelOne -do old-run -csv
dbget -m modelOne -do old-run -tsv
dbget -m modelOne -do old-run -lang fr-CA
dbget -m modelOne -do old-run -dbget.NoLanguage
dbget -m modelOne -do old-run -dbget.IdCsv
dbget -m modelOne -do old-run -pipe
dbget -m modelOne -do old-run -dir my/dir
dbget -m modelOne -do old-run -dbget.NoZeroCsv
dbget -m modelOne -do old-run -dbget.NoNullCsv

dbget -dbget.ModelName modelOne -dbget.Do old-run -dbget.As csv -dbget.ToConsole -dbget.Language FR

Get parameter run values from compatibility (Modgen) views:

dbget -m modelOne -do old-parameter -dbget.Parameter ageSex
dbget -m modelOne -do old-parameter -dbget.Parameter ageSex -csv
dbget -m modelOne -do old-parameter -dbget.Parameter ageSex -tsv
dbget -m modelOne -do old-parameter -dbget.Parameter ageSex -lang fr-CA
dbget -m modelOne -do old-parameter -dbget.Parameter ageSex -dbget.NoLanguage
dbget -m modelOne -do old-parameter -dbget.Parameter ageSex -dbget.IdCsv
dbget -m modelOne -do old-parameter -dbget.Parameter ageSex -pipe

dbget -dbget.ModelName modelOne -dbget.Do old-parameter -dbget.Parameter ageSex -dbget.As csv -dbget.ToConsole -dbget.Language FR

Get output table values from compatibility (Modgen) views:

dbget -m modelOne -do old-table -dbget.Table salarySex
dbget -m modelOne -do old-table -dbget.Table salarySex -csv
dbget -m modelOne -do old-table -dbget.Table salarySex -tsv
dbget -m modelOne -do old-table -dbget.Table salarySex -lang fr-CA
dbget -m modelOne -do old-table -dbget.Table salarySex -dbget.NoLanguage
dbget -m modelOne -do old-table -dbget.Table salarySex -dbget.IdCsv
dbget -m modelOne -do old-table -dbget.Table salarySex -pipe
dbget -m modelOne -do old-table -dbget.Table salarySex -dbget.NoZeroCsv
dbget -m modelOne -do old-table -dbget.Table salarySex -dbget.NoNullCsv

dbget -dbget.ModelName modelOne -dbget.Do old-table -dbget.Table ageSexIncome -dbget.As csv -dbget.ToConsole -dbget.Language FR

Also dbget 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

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

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