DataSets - woveon/wovtools GitHub Wiki

< Home

Woveon Logo

It is often useful to manage multiple sets of data to push to databases. These data sets are stored in wovtools/data, with three file types: data, clear and schema. These files can be static or of the .wov extension, which have handlebars like options in them.

Create a DataSet

To create a data set named X, in the wovtools/data directory, create the following SQL files:

wovtools/data/X_schema.sql - place all CREATE statements for TYPE, TABLE, VIEW, etc. Use a DROP X IF EXISTS before each.
wovtools/data/X_clear.sql - place TRUNCATE and ALTER SEQUENCE statements here.
wovtools/data/X_data.sql - place all INSERT statements here.

Load a DataSet

DataSets are loaded with the wov-db-connect script, which requires you to specify the database server and dataset.

wov-db-connect MYDBSERVER --data-full MYDATASET

NOTE: You can load each file independently via --data-schema, --data-clear and --data-data.

Writing files

These are all just SQL files, or sql files with WOV extensions. Files with WOV extensions can have data inserted from the const file. This is useful for debugging and test cases which can read in the same const file. Here are some tips:

  • use '\echo' to add logging statements to your SQL files
  • Clear (DATASET_clear.sql[.wov])
    • use 'TRUNCATE table' followed by 'ALTER SEQUENCE table_id_seq RESTART WITH X' to make all ids start at a certian number (helps with debugging)
    • use '{{math C.id.X '+' 1}}' in .wov files to offset a start id when you are adding data to a table
  • Data (DATASET_clear.sql[.wov])
    • use '{{math C.id.X Y}}' to add Y to a constant id X
    • use '{{C.data.X.n.Y}}' to add data to insert statements for table X, index n, attribute Y
  • Schema (DATASET_schema.sql)
    • add 'SET client_min_messages TO WARNING;' at top to reduce messages
  • Const (DATASET_const.js)
    • this is a javascript file with 'id' and 'data' objects to use in your .wov files. The 'data' refers to tables, so data.team[{},...] is a good place to put data for rows.
    • ex. id.team : 120 can be used in a Data file INSERT statement as '{{math C.id.team +1}}'.
    • NOTE: by convention, data.TABLE[] stars with a {}, so row 1 in the database matches to data.TABLE[1].