Working with Databases

Stella Enterprise Desktop supports importing and exporting directly with most SQL databases.

The core to working with databases is to establish one or more database connections. This names the database and characteristics of it that are important for transferring data, including the Schema in use. These are set up from the Database Connections Dialog with details for each connection (you can have 1 or more database connections) provided in the ImEx Database Connection Dialog.

Some sample SQL for creating database tables is available in Database Table Definitions. These definitions use the default names for both tables and fields making it easier to set up database connections.

Notes on installing support for different databases are available in Database Setup Notes.

Database Connection

Location

The following are kept with the model with a copy kept in preferences to make it easy to reuse database connections between models.

Name is the name of the connection. It is created automatically based on the type of connection and the name of the database being connected to and is unique.
Database - is the name of the database you will be working with.
Server - if the location of the database. If blank, it will be assumed to be on your local computer (localhost).
Extra - provides extra information related to connecting to the database. This is often blank.

Credentials

The following are only kept in preferences so that different people using the same database can log in with their own credentials and have data (specifically runs) attributed to them.

Nickname - when storing runs this will be include in the run information. Full name or first name is often appropriate.
Username - used to log into the database.
Password - the password to log into the database.

Run Schema

In order to work with databases Stella needs to know the schema in use for importing and exporting data. Some of that schema is setup for each import and export link in the Export Data dialog box and the Import Data dialog box as described below, but the portion relating to runs is set up directly in the ImEx Database Connection Dialog. This allows Stella to give you easy access to runs made by other users for the same model.

Run Table

The run table is where information about each run is kept, including the model that made it, the user that made it and the date it was saved. The run table is not necessary for imports (or exports that override or update table contents), but it is used to manage external runs so that can be loaded. It needs the following fields:

Run ID - a field that contains a unique ID for each run in run table. This should be an integer that auto increments on insertion. Examples of how this is done for different databases are shown in Database Table Definitions
Run GUID - a text field (at least 64 characters long) that holds a unique identifier for the run. Stella generates these identifiers during the insertion process, and uses them until the database itself generates the unique ID above. the Run GUID is associated with the Stella run for the duration of the Stella session and is used to help manage run use as described below.
Model GUID - a text field (at least 64 characters long) that holds a unique identifier for the model. Each time a model is created in Stella it is assigned what should be a globally (or universally) unique identifier that can be used to identify it. This identifier is used by Stella to organize runs in the database that can be used by the model.
.Run detail - a text field (at least 256 characters long) that hold information about the run including the creator, export time and run name. This content is managed by Stella.

Run Use Table

Each export from a model run is detailed in a run use table. This table exists in addition to the run table because there may be more than one export from a run. This table contains the information necessary to actually get the run results. It needs the following fields:

Run Use ID - a field that contains a unique ID for each run use in the run use table. This should be an integer that auto increments on insertion. Examples of how this is done for different databases are shown in Database Table Definitions
Run ID - the ID in the run table that is connected to this run use.
Model GUID - a text field (at least 64 characters long) that holds a unique identifier for the model just as it does in the run table. This information is actually redundant since the value is in the run table, but it dramatically simplifies the queries Stella needs to do in order to manage runs in the database.
.Run use detail - a text field (at least 256 characters long) that hold information about the run, including the table to which results are written and the fields in that table. This is information specified in the Export Data dialog box and discussed below.

Import settings

Imports are made by selecting an import source as configured above and specifying a table and the fields containing variables names and values in the Import Data dialog box. For time varying data it is also necessary to specify the name of the "Time" variable. There is also an optional sequence field that can be used to guarantee the order in which changes are read - this is only important if some changes apply to array slices. The table can contain additional fields which can be used in a where statement.

When there are multiple values for a variable name, as happens with time varying data, graphical inputs, and array slices, the values should be , separated and use . as he decimal delimiter.

After specifying the database connection (as described above) the following entries are available when importing data:

Table - The database table to import from.
Variable field - the filed containing the name of the variable. This is a text field that is sufficiently long to hold the name (including array elements) or any variable in the model. Typically 256 characters is sufficient for this.
Values field - the field in the table that has the variable values in it. This can either be a number, if only importing constants, or a comma separated list of numbers if importing vectors, graphicals, or time varying data. If it will contain multiple values, the field needs to be wide enough to hold all values so 8 x the number of potential points is a good rule of thumb.
Sequence field (optional) - an integer field that specifies the order in which the database rows should be processed. If left off the rows will be processed alphabetically. This is only important if more than one entry impacts the same variable.
Where (optional) - a string the specifies any conditions that should be met in the database table in order to import value (for example "SCENARIO = 7" would only import values from scenario 7).
Time name - the name to be interpreted as time in the variable field. This is necessary when importing time varying values and specifies which row contains the time values.

imports do not reference the run or run use table, though exports can afterward be imported through action in the Database Data Manager (or Database Manager(Interface)).

Export settings

Exports are made by selecting an export target as configured above and specifying a table and the fields with variable names and values as well as the run use ID in the Export Data dialog box.

Table - the database table to export to.
Use id field- the field in the table which holds the ID value for the run use table in the database connection.
Variable field - This is a text field that is sufficiently long to hold the name (including array elements) or all variables in the model. Typically 256 characters is sufficient for this.
Values field - the field in the table that has the variable values in it. This can be a number if you are only exporting a single value and no graphicals (or if you are using the optional time field). Otherwise it should be a text field wide enough to hold all values (8 x the total number of save periods is a good rule of thumb).
Time field- the field in the table that stores the time value. This should be a number. When this is not empty each time will be written to a separate record. In this case the Values field should also be a number.
Write action must be one of
- replace - will replace the contents of the table with the exported values. Any old contents will simply be lost. The table is effectively the current run.
- append - will export the current updating any values already there, but leave all other values in the table alone. This allows exporting from multiple source but may cause confusion (using another table is probably safer).
- insert- will insert a run into the run table and run use table as described above and then values keyed to the inserted run use ID.

Managing database runs

When you export from a model using the insert action described above it will create an entry in the Run use table, and possibly a new entry in the tun table. Since the run table contains a model GUID it allows anyone using that model (or a variation of it) to access runs from either the Database Data Manager or Database Manager(Interface). The database data manager allows you to load run results (if time varying results were stored), load run parameters (if constant or constant and graphical values were stored), and also delete runs stored in the database. When you load run values it will create a run in your local session, that you can save when you save the model on the desktop (for a published interface the loaded runs will not persist, but may be loaded in again from the database data manager). When run parameters are loaded the model can be rerun using those parameters, or they can be further modified before running again.