PSRDatabase API

close!(db::DatabaseSQLite)

Close the database connection.

This function closes the SQLite database connection and releases any associated resources. Always call this function when you're done working with a database to ensure proper cleanup.

Arguments

• db::DatabaseSQLite: The database connection to close

Returns

• Nothing

Examples

# Open a database
db = PSRDatabase.load_db("my_database.sqlite")

# Work with the database
# ... perform operations ...

# Close the database when done
PSRDatabase.close!(db)

Notes

After closing, the database connection cannot be used for further operations. You'll need to call load_db again if you want to work with the database.

create_migration(path_migrations_directory::String, version::Integer)

Creates a new migration in the migrations folder with the current date, the correct version and the name given in this function

generate_current_schema_file(db::SQLite.DB, file::String)

Generates a .sql file based in sqlite_master that indicates the statements to create a new db from scratch.

test_migrations(path_migrations_directory::String)

Function to put in the test suite of the module to verify that the migrations are behaving correctly.

Collection

This struct stores the definition of a collection

Attribute

Abstract type for attributes, the building blocks of collections.

const READ_METHODS_BY_CLASS_OF_ATTRIBUTE

A dictionary mapping attribute classes to their corresponding read method names in PSRDatabase.

_PSRDatabase_null_value(::Type{Float64})
_PSRDatabase_null_value(::Type{Int64})
_PSRDatabase_null_value(::Type{String})
_PSRDatabase_null_value(::Type{DateTime})

Get the null/missing value representation for a specific type in PSRDatabase.

Arguments

• Type parameter: The data type to get the null value for

Returns

• For Float64: NaN
• For Int64: typemin(Int64)
• For String: ""
• For DateTime: typemin(DateTime)

_get_id(db::DatabaseSQLite, collection_id::String, label::String)::Integer

Internal function to retrieve the numeric ID for an element in a collection based on its label.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• label::String: The label of the element to find

Returns

• Integer: The numeric ID of the element

Throws

• Error if the label does not exist in the collection

_get_scalar_relation_map(db::DatabaseSQLite, collection_from::String, collection_to::String, relation_type::String)

Internal function to retrieve the scalar relation mapping as a vector of indices.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the source collection
• collection_to::String: The identifier of the target collection
• relation_type::String: The type of relation

Returns

• Vector{Int}: A vector of indices mapping each element in collection_from to elements in collection_to

_get_set_relation_map(db::DatabaseSQLite, collection_from::String, collection_to::String, relation_type::String)

Internal function to retrieve the set relation mapping as a vector of index vectors.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the source collection
• collection_to::String: The identifier of the target collection
• relation_type::String: The type of relation

Returns

- `Vector{Vector{Int}}`: A vector of vectors of indices mapping each element in `collection_from` to elements in `collection_to`

_get_vector_relation_map(db::DatabaseSQLite, collection_from::String, collection_to::String, relation_type::String)

Internal function to retrieve the vector relation mapping as a vector of index vectors.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the source collection
• collection_to::String: The identifier of the target collection
• relation_type::String: The type of relation

Returns

• Vector{Vector{Int}}: A vector of vectors of indices mapping each element in collection_from to elements in collection_to

_is_null_in_db(value::Float64)
_is_null_in_db(value::Int64)
_is_null_in_db(value::String)
_is_null_in_db(value::DateTime)

Check if a value represents a null/missing value in PSRDatabase.

Arguments

• value: The value to check

Returns

• Bool: true if the value is null, false otherwise

Details

• For Float64: checks if isnan(value)
• For Int64: checks if value == typemin(Int64)
• For String: checks if isempty(value)
• For DateTime: checks if value == typemin(DateTime)

_query_set(db::DatabaseSQLite, attribute::SetParameter, id::Integer; default::Union{Nothing, Any} = nothing)

Internal function to query set parameter values for a specific element.

Arguments

• db::DatabaseSQLite: The database connection
• attribute::SetParameter: The set parameter attribute
• id::Integer: The numeric ID of the element
• default::Union{Nothing, Any}: Optional default value for missing data

Returns

- `Vector`: The vector of set parameter values, ordered by rowid

_query_vector(db::DatabaseSQLite, attribute::VectorParameter, id::Integer; default::Union{Nothing, Any} = nothing)

Internal function to query vector parameter values for a specific element.

Arguments

• db::DatabaseSQLite: The database connection
• attribute::VectorParameter: The vector parameter attribute
• id::Integer: The numeric ID of the element
• default::Union{Nothing, Any}: Optional default value for missing data

Returns

• Vector: The vector of parameter values, ordered by vector_index

_read_time_series_table(db::DatabaseSQLite, attribute::Attribute, id::Integer)

Internal function to read the complete time series table for a specific element.

Arguments

• db::DatabaseSQLite: The database connection
• attribute::Attribute: The time series attribute
• id::Integer: The numeric ID of the element

Returns

• DataFrame: A DataFrame containing all time series data for the element

_treat_query_result(query_results::Vector{<:Union{Missing, String}}, attribute::Attribute, default::Union{Nothing, Any})

Internal function to process string query results, replacing missing values with appropriate defaults. Handles both String and DateTime types (DateTime values are stored as strings in the database).

Arguments

• query_results::Vector{<:Union{Missing, String}}: The query results that may contain missing values
• attribute::Attribute: The attribute being queried
• default::Union{Nothing, Any}: The default value to use for missing data

Returns

• A vector with missing values replaced by the specified default, with DateTime conversion if applicable

_treat_query_result(query_results::Vector{Missing}, attribute::Attribute, default::Union{Nothing, Any})

Internal function to process query results that are all missing values, replacing them with appropriate defaults.

Arguments

• query_results::Vector{Missing}: The query results containing only missing values
• attribute::Attribute: The attribute being queried
• default::Union{Nothing, Any}: The default value to use for missing data

Returns

• A vector filled with the appropriate default values

_treat_query_result(query_results::Vector{Union{Missing, T}}, attribute::Attribute, default::Union{Nothing, Any}) where {T <: Union{Int64, Float64}}

Internal function to process numeric query results, replacing missing values with appropriate defaults.

Arguments

• query_results::Vector{Union{Missing, T}}: The query results that may contain missing values
• attribute::Attribute: The attribute being queried
• default::Union{Nothing, Any}: The default value to use for missing data

Returns

• A vector with missing values replaced by the specified default

_treat_query_result(results::Vector{T}, ::Attribute, ::Union{Nothing, Any}) where {T <: Union{Int64, Float64}}

Internal function to process numeric query results that contain no missing values. Returns the results unchanged.

Arguments

• results::Vector{T}: The query results with no missing values
• ::Attribute: The attribute being queried (unused)
• ::Union{Nothing, Any}: The default value (unused)

Returns

• The original results vector unchanged

number_of_elements(db::DatabaseSQLite, collection_id::String)::Int

Return the total number of elements in the specified collection.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection to count elements from

Returns

• Int: The number of elements in the collection

read_scalar_parameter(db::DatabaseSQLite, collection_id::String, attribute_id::String, id::Integer; default::Union{Nothing, Any} = nothing)

Read the value of a scalar parameter attribute for a specific element identified by numeric ID.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the scalar parameter attribute to read
• id::Integer: The numeric ID of the element to read from
• default::Union{Nothing, Any}: Optional default value to use for missing data. If nothing, uses type-specific null values

Returns

• The scalar parameter value for the specified element. Type matches the attribute type (Float64, Int64, String, or DateTime)

Example

capacity = PSRDatabase.read_scalar_parameter(db, "Plant", "capacity", 1)  # 2.02

read_scalar_parameter(db::DatabaseSQLite, collection_id::String, attribute_id::String, label::String; default::Union{Nothing, Any} = nothing)

Read the value of a scalar parameter attribute for a specific element identified by label.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the scalar parameter attribute to read
• label::String: The label of the element to read from
• default::Union{Nothing, Any}: Optional default value to use for missing data. If nothing, uses type-specific null values

Returns

• The scalar parameter value for the specified element. Type matches the attribute type (Float64, Int64, String, or DateTime)

Examples

# Read a string label
name = PSRDatabase.read_scalar_parameter(db, "Resource", "label", "Resource 1")  # "Resource 1"

# Read a numeric value
capacity = PSRDatabase.read_scalar_parameter(db, "Plant", "capacity", "Plant 3")  # 54.0

Throws

• DatabaseException if the label does not exist in the collection

read_scalar_parameters(db::DatabaseSQLite, collection_id::String, attribute_id::String; default::Union{Nothing, Any} = nothing)

Read all values of a scalar parameter attribute for all elements in a collection.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the scalar parameter attribute to read
• default::Union{Nothing, Any}: Optional default value to use for missing data. If nothing, uses type-specific null values (NaN for Float64, typemin(Int64) for Int64, "" for String, typemin(DateTime) for DateTime)

Returns

• Vector: A vector containing the scalar parameter values for all elements, ordered by ID. The element type matches the attribute type (Float64, Int64, String, or DateTime)

Examples

# Read labels (returns Vector{String})
labels = PSRDatabase.read_scalar_parameters(db, "Plant", "label")  # ["Plant 1", "Plant 2", "Plant 3"]

# Read numeric values (returns Vector{Float64})
capacities = PSRDatabase.read_scalar_parameters(db, "Plant", "capacity")  # [2.02, 53.0, 54.0]

# Read dates (returns Vector{DateTime})
dates = PSRDatabase.read_scalar_parameters(db, "Configuration", "date_initial")  # [DateTime(2020, 1, 1)]

# With default value for missing data
values = PSRDatabase.read_scalar_parameters(db, "Cost", "value_without_default"; default = 2.0)  # [2.0, 2.0]

read_scalar_relation(db::DatabaseSQLite, collection_from::String, collection_to::String, relation_type::String, collection_from_label::String)

Read the scalar relation mapping for a specific element from one collection to another.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the source collection
• collection_to::String: The identifier of the target collection
• relation_type::String: The type of relation (e.g., "id", "group", "turbine_to")
• collection_from_label::String: The label of the element in the source collection

Returns

• String: The label from collection_to that the specified element relates to. Empty string ("") indicates a null relation (no connection).

Examples

# Get which resource "Plant 1" is connected to
resource = PSRDatabase.read_scalar_relation(db, "Plant", "Resource", "id", "Plant 1")  # "Resource 1"

# Get which plant "Plant 3" connects to via turbine
turbine = PSRDatabase.read_scalar_relation(db, "Plant", "Plant", "turbine_to", "Plant 3")  # "Plant 2"

read_scalar_relations(db::DatabaseSQLite, collection_from::String, collection_to::String, relation_type::String)

Read all scalar relation mappings from one collection to another.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the source collection
• collection_to::String: The identifier of the target collection
• relation_type::String: The type of relation (e.g., "id", "group", "turbine_to")

Returns

• Vector{String}: A vector of labels from collection_to representing the relation for each element in collection_from, ordered by ID. Empty strings ("") indicate null relations (no connection).

Examples

# Get which resource each plant is connected to
resources = PSRDatabase.read_scalar_relations(db, "Plant", "Resource", "id")
# ["Resource 1", "", ""]  # Plant 1 → Resource 1, Plant 2 and 3 → no resource

# Get turbine connections between plants
turbines = PSRDatabase.read_scalar_relations(db, "Plant", "Plant", "turbine_to")
# ["", "", "Plant 2"]  # Only Plant 3 connects to Plant 2

Throws

• DatabaseException if the relation is not a scalar relation (e.g., trying to read a vector relation)

read_set_parameter(db::DatabaseSQLite, collection_id::String, attribute_id::String, label::String; default::Union{Nothing, Any} = nothing)

Read the values of a set parameter attribute for a specific element identified by label.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the set parameter attribute to read
• label::String: The label of the element to read from
• default::Union{Nothing, Any}: Optional default value to use for missing data. If nothing, uses type-specific null values

Returns

- `Vector`: A vector containing the parameter values for the specified element. Type matches the attribute type (Float64, Int64, String, or DateTime). Returns an empty vector if no data exists.

read_set_parameters(db::DatabaseSQLite, collection_id::String, attribute_id::String; default::Union{Nothing, Any} = nothing)

Read all values of a set parameter attribute for all elements in a collection.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the set parameter attribute to read
• default::Union{Nothing, Any}: Optional default value to use for missing data. If nothing, uses type-specific null values

Returns

- `Vector{Vector}`: A vector of vectors, where each inner vector contains the parameter values for one element. Inner vector type matches the attribute type (Float64, Int64, String, or DateTime). Empty vectors are returned for elements with no data.

read_set_relation(db::DatabaseSQLite, collection_from::String, collection_to::String, collection_from_label::String, relation_type::String)

Read the set relation mapping for a specific element from one collection to another.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the source collection
• collection_to::String: The identifier of the target collection
• collection_from_label::String: The label of the element in the source collection
• relation_type::String: The type of relation

Returns

- `Vector{String}`: A vector of labels from `collection_to` that the specified element relates to. Returns an empty vector if no relations exist. Empty strings within the vector indicate null relations.

Examples

# Get which costs "Plant 1" is associated with
costs = PSRDatabase.read_set_relation(db, "Plant", "Cost", "Plant 1", "id")  # ["Cost 2"]
# Get multiple related elements
costs = PSRDatabase.read_set_relation(db, "Plant", "Cost", "Plant 2", "id")  # ["Cost 1", "Cost 2"]
# Element with no relations
costs = PSRDatabase.read_set_relation(db, "Plant", "Cost", "Plant 3", "id")  # String[]

read_set_relation(db::DatabaseSQLite, collection_from::String, collection_to::String, collection_from_label::String, relation_type::String)

Read the set relation mapping for a specific element from one collection to another.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the source collection
• collection_to::String: The identifier of the target collection
• collection_from_label::String: The label of the element in the source collection
• relation_type::String: The type of relation

Returns

• Vector{String}: A vector of labels from collection_to that the specified element relates to. Returns an empty vector if no relations exist. Empty strings within the vector indicate null relations.

Examples

# Get which costs "Plant 1" is associated with
costs = PSRDatabase.read_set_relations(db, "Plant", "Cost", "Plant 1", "id")  # ["Cost 2"]

# Get multiple related elements
costs = PSRDatabase.read_set_relations(db, "Plant", "Cost", "Plant 2", "id")  # ["Cost 1", "Cost 2"]

# Element with no relations
costs = PSRDatabase.read_set_relations(db, "Plant", "Cost", "Plant 3", "id")  # String[]

read_time_series_file(db::DatabaseSQLite, collection_id::String, attribute_id::String)::String

Read the file path stored in a time series file attribute.

Time series file attributes store references to external files containing time series data. This function retrieves the file path string.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the time series file attribute

Returns

• String: The file path stored in the attribute, or an empty string ("") if not set

Examples

# Read time series file paths
wind_file = PSRDatabase.read_time_series_file(db, "Plant", "wind_speed")  # "some_file.txt"
direction_file = PSRDatabase.read_time_series_file(db, "Plant", "wind_direction")  # "some_file2"

# After updating
PSRDatabase.set_time_series_file!(db, "Plant"; wind_speed = "some_file3.txt")
wind_file = PSRDatabase.read_time_series_file(db, "Plant", "wind_speed")  # "some_file3.txt"

Throws

• DatabaseException if the attribute is not a time series file attribute
• DatabaseException if the table has more than one row (should only have one row for time series file attributes)

read_time_series_row(db::DatabaseSQLite, collection_id::String, attribute_id::String; date_time::DateTime)

Read a row of time series data for all elements in a collection at a specific date/time.

This function is optimized for read-only databases and uses caching for efficient access to time series data.

Arguments

• db::DatabaseSQLite: The database connection (must be read-only)
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the time series attribute
• date_time::DateTime: The date/time to query data for

Returns

• Vector: A vector containing the time series values for all elements at the specified date/time

Note

This function only works with read-only databases and will throw an error if called on a writable database.

Example

generation = PSRDatabase.read_time_series_row(db, "Thermal", "generation"; date_time = DateTime(2025, 1, 1))

read_time_series_table(db::DatabaseSQLite, collection_id::String, attribute_id::String, label::String)

Read the complete time series table for a specific element identified by label.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the time series attribute
• label::String: The label of the element to read data for

Returns

• DataFrame: A DataFrame containing all time series data (dimensions and values) for the specified element

Example

generation_table = PSRDatabase.read_time_series_table(db, "Thermal", "generation", "Plant1")

read_vector_parameter(db::DatabaseSQLite, collection_id::String, attribute_id::String, label::String; default::Union{Nothing, Any} = nothing)

Read the values of a vector parameter attribute for a specific element identified by label.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the vector parameter attribute to read
• label::String: The label of the element to read from
• default::Union{Nothing, Any}: Optional default value to use for missing data. If nothing, uses type-specific null values

Returns

• Vector: A vector containing the parameter values for the specified element. Type matches the attribute type (Float64, Int64, String, or DateTime). Returns an empty vector if no data exists.

Examples

# Read vector with data
factors = PSRDatabase.read_vector_parameter(db, "Plant", "some_factor", "Plant 1")  # [1.0]
factors = PSRDatabase.read_vector_parameter(db, "Plant", "some_factor", "Plant 2")  # [1.0, 2.0]

# Read empty vector
factors = PSRDatabase.read_vector_parameter(db, "Plant", "some_factor", "Plant 3")  # Float64[]

# Read date vectors
dates = PSRDatabase.read_vector_parameter(db, "Plant", "date_some_date", "Plant 2")
# [DateTime(2020, 1, 1), DateTime(2020, 1, 2)]

# Elements with null dates return typemin(DateTime)
dates = PSRDatabase.read_vector_parameter(db, "Plant", "date_some_date", "Plant 4")
# [typemin(DateTime), typemin(DateTime)]

Throws

• DatabaseException if the label does not exist in the collection

read_vector_parameters(db::DatabaseSQLite, collection_id::String, attribute_id::String; default::Union{Nothing, Any} = nothing)

Read all values of a vector parameter attribute for all elements in a collection.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection
• attribute_id::String: The identifier of the vector parameter attribute to read
• default::Union{Nothing, Any}: Optional default value to use for missing data. If nothing, uses type-specific null values

Returns

• Vector{Vector}: A vector of vectors, where each inner vector contains the parameter values for one element. Inner vector type matches the attribute type (Float64, Int64, String, or DateTime). Empty vectors are returned for elements with no data.

Examples

# Read numeric vector parameters
values = PSRDatabase.read_vector_parameters(db, "Resource", "some_value")
# [[1, 2, 3.0], [1, 2, 4.0]]

# Read vector parameters with some empty elements
factors = PSRDatabase.read_vector_parameters(db, "Plant", "some_factor")
# [[1.0], [1.0, 2.0], Float64[], [1.0, 2.0]]

# Read date vectors
dates = PSRDatabase.read_vector_parameters(db, "Plant", "date_some_date")
# [[DateTime(2020, 1, 1)], [DateTime(2020, 1, 1), DateTime(2020, 1, 2)], DateTime[], ...]

read_vector_relation(db::DatabaseSQLite, collection_from::String, collection_to::String, collection_from_label::String, relation_type::String)

Read the vector relation mapping for a specific element from one collection to another.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the source collection
• collection_to::String: The identifier of the target collection
• collection_from_label::String: The label of the element in the source collection
• relation_type::String: The type of relation

Returns

• Vector{String}: A vector of labels from collection_to that the specified element relates to. Returns an empty vector if no relations exist. Empty strings within the vector indicate null relations.

Examples

# Get which costs "Plant 1" is associated with
costs = PSRDatabase.read_vector_relation(db, "Plant", "Cost", "Plant 1", "id")  # ["Cost 2"]

# Get multiple related elements
costs = PSRDatabase.read_vector_relation(db, "Plant", "Cost", "Plant 2", "id")  # ["Cost 1", "Cost 2"]

# Element with no relations
costs = PSRDatabase.read_vector_relation(db, "Plant", "Cost", "Plant 3", "id")  # String[]

read_vector_relations(db::DatabaseSQLite, collection_from::String, collection_to::String, relation_type::String)

Read all vector relation mappings from one collection to another.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the source collection
• collection_to::String: The identifier of the target collection
• relation_type::String: The type of relation

Returns

• Vector{Vector{String}}: A vector of vectors, where each inner vector contains labels from collection_to representing the relations for one element in collection_from, ordered by ID. Empty vectors indicate no relations. Empty strings within vectors indicate null relations.

Examples

# Get which costs each plant is associated with
costs = PSRDatabase.read_vector_relations(db, "Plant", "Cost", "id")
# [["Cost 1"], ["Cost 1", "Cost 2"], String[]]
# Plant 1 → Cost 1, Plant 2 → Cost 1 and Cost 2, Plant 3 → no costs

# After updating Plant 1's costs
PSRDatabase.set_vector_relation!(db, "Plant", "Cost", "Plant 1", ["Cost 2"], "id")
costs = PSRDatabase.read_vector_relations(db, "Plant", "Cost", "id")
# [["Cost 2"], ["Cost 1", "Cost 2"], String[]]

Throws

• DatabaseException if the relation is not a vector relation (e.g., trying to read a scalar relation)

add_time_series_row!(db::DatabaseSQLite, collection_id::String, attribute_id::String, label::String, val; dimensions...)

Add or update a value in a time series attribute for a specific element and dimension combination.

This function performs an "upsert" operation - if a row with the specified dimensions already exists, it updates the value; otherwise, it inserts a new row.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection containing the element
• attribute_id::String: The identifier of the time series attribute
• label::String: The label of the element to add/update the time series value for
• val: The value to set for the time series at the specified dimensions
• dimensions...: Named arguments specifying the dimension values (e.g., date_time = DateTime(2020, 1, 1), stage = 1)

Returns

• nothing

Throws

• DatabaseException if the attribute is not a time series
• DatabaseException if the number of dimensions doesn't match the attribute definition
• DatabaseException if dimension names don't match the attribute definition

Examples

# Add time series value with date_time dimension
PSRDatabase.add_time_series_row!(
    db,
    "Plant",
    "generation",
    "Plant 1",
    100.5;
    date_time = DateTime(2020, 1, 1),
)

# Add time series value with multiple dimensions
PSRDatabase.add_time_series_row!(
    db,
    "Plant",
    "cost",
    "Plant 1",
    50.0;
    date_time = DateTime(2020, 1, 1),
    stage = 1,
)

# Update existing time series value (same dimensions)
PSRDatabase.add_time_series_row!(
    db,
    "Plant",
    "generation",
    "Plant 1",
    120.0;
    date_time = DateTime(2020, 1, 1),  # This will update the existing value
)

create_element!(db::DatabaseSQLite, collection_id::String; kwargs...)

Create a new element in the specified collection with the given attributes.

Arguments

• db::DatabaseSQLite: The database connection

• collection_id::String: The identifier of the collection to add the element to

• kwargs...: Named arguments specifying the attribute values for the new element

  • Scalar parameters: Single values (Int, Float64, String, DateTime)
  • Vector parameters: Arrays of values that must all have the same length within a group
  • Scalar relations: String labels of related elements or integer IDs
  • Vector relations: Arrays of string labels of related elements
  • Time series: File paths as strings

Returns

• nothing

Throws

• DatabaseException if the collection doesn't exist
• DatabaseException if an attribute doesn't exist or has invalid type
• DatabaseException if vector parameters in the same group have different lengths
• DatabaseException if a required attribute is missing (e.g., label)
• SQLiteException if a label already exists (violates unique constraint)

Examples

# Create element with scalar parameters
PSRDatabase.create_element!(db, "Configuration"; label = "Toy Case", value1 = 1.0)

# Create element with vector parameters
PSRDatabase.create_element!(
    db,
    "Resource";
    label = "Resource 1",
    type = "E",
    some_value = [1.0, 2.0, 3.0],
)

# Create element with scalar relation (using label)
PSRDatabase.create_element!(db, "Plant"; label = "Plant 1", capacity = 50.0, resource_id = "Resource 1")

# Create element with vector relations
PSRDatabase.create_element!(
    db,
    "Process";
    label = "Sugar Mill",
    product_input = ["Sugarcane"],
    factor_input = [1.0],
    product_output = ["Sugar", "Molasse", "Bagasse"],
    factor_output = [0.3, 0.3, 0.4],
)

const UPDATE_METHODS_BY_CLASS_OF_ATTRIBUTE

A dictionary mapping attribute classes to their corresponding update method names in PSRDatabase.

set_scalar_relation!(db::DatabaseSQLite, collection_from::String, collection_to::String, label_collection_from::String, label_collection_to::String, relation_type::String)

Set a scalar relation between two elements, linking an element from one collection to an element in another collection (or the same collection).

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the collection containing the element to set the relation from
• collection_to::String: The identifier of the collection containing the element to set the relation to
• label_collection_from::String: The label of the element to set the relation from
• label_collection_to::String: The label of the element to set the relation to
• relation_type::String: The type/name of the relation (e.g., "id", "turbineto", "spillto")

Returns

• nothing

Throws

• DatabaseException if the attribute is not a scalar relation
• DatabaseException if either element label doesn't exist
• DatabaseException if trying to set a relation between the same element (when both collections are the same)
• DatabaseException if the relation type doesn't exist

Examples

# Set a relation to a different collection
PSRDatabase.set_scalar_relation!(
    db,
    "Plant",
    "Resource",
    "Plant 1",
    "Resource 1",
    "id",
)

# Set a relation within the same collection
PSRDatabase.set_scalar_relation!(
    db,
    "Plant",
    "Plant",
    "Plant 3",
    "Plant 1",
    "turbine_to",
)

# Update an existing relation
PSRDatabase.set_scalar_relation!(
    db,
    "Plant",
    "Resource",
    "Plant 1",
    "Resource 2",  # Changes from Resource 1 to Resource 2
    "id",
)

set_time_series_file!(db::DatabaseSQLite, collection_id::String; kwargs...)

Set or update time series file paths for a collection.

This function sets the file paths for time series attributes that store their data in external files. There can only be one set of time series files per collection.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection to set time series files for
• kwargs...: Named arguments where keys are time series file attribute names and values are file paths (strings)

Returns

• nothing

Throws

• DatabaseException if the collection doesn't exist
• DatabaseException if any attribute is not a time series file attribute
• DatabaseException if any value is not a string
• DatabaseException if there are multiple time series file entries (database corruption)

Examples

# Set time series files for a collection
PSRDatabase.set_time_series_file!(
    db,
    "Plant";
    generation = "generation_data.csv",
    cost = "cost_data.csv",
)

# Update a single time series file
PSRDatabase.set_time_series_file!(
    db,
    "Plant";
    generation = "new_generation_data.csv",
)

# Set multiple time series files at once
PSRDatabase.set_time_series_file!(
    db,
    "Resource";
    availability = "availability.txt",
    price = "price.txt",
)

set_vector_relation!(db::DatabaseSQLite, collection_from::String, collection_to::String, label_collection_from::String, labels_collection_to::Vector{String}, relation_type::String)

Set a vector relation between an element and multiple elements, linking an element from one collection to multiple elements in another collection.

This function replaces all existing relations for the vector with the new relations provided.

Arguments

• db::DatabaseSQLite: The database connection
• collection_from::String: The identifier of the collection containing the element to set the relation from
• collection_to::String: The identifier of the collection containing the elements to set the relation to
• label_collection_from::String: The label of the element to set the relation from
• labels_collection_to::Vector{String}: A vector of labels of elements to set the relation to
• relation_type::String: The type/name of the relation

Returns

• nothing

Throws

• DatabaseException if the attribute is not a vector relation
• DatabaseException if any element label doesn't exist
• DatabaseException if the relation type doesn't exist
• DatabaseException if the number of relations doesn't match other vectors in the same group

Examples

# Set a vector relation
PSRDatabase.set_vector_relation!(
    db,
    "Plant",
    "Cost",
    "Plant 1",
    ["Cost 1", "Cost 2"],
    "some_relation_type",
)

# Update vector relation with different elements
PSRDatabase.set_vector_relation!(
    db,
    "Plant",
    "Cost",
    "Plant 1",
    ["Cost 2", "Cost 3", "Cost 4"],  # Now relates to 3 costs instead of 2
    "some_relation_type",
)

# Clear all relations (empty vector)
PSRDatabase.set_vector_relation!(
    db,
    "Plant",
    "Cost",
    "Plant 1",
    String[],
    "some_relation_type",
)

update_parameter!(db::DatabaseSQLite, collection_id::String, label::String; kwargs...)

Update multiple parameter attributes for a specific element in a collection. The function can update multiple types of parameters

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection containing the element
• label::String: The label of the element to update
• kwargs...: Named arguments where keys are attribute names and values are the new values for those attributes

Returns

- `nothing`

update_scalar_parameter!(db::DatabaseSQLite, collection_id::String, attribute_id::String, label::String, val)

Update the value of a scalar parameter attribute for a specific element in a collection.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection containing the element
• attribute_id::String: The identifier of the scalar parameter attribute to update
• label::String: The label of the element to update
• val: The new value for the attribute (must match the attribute's type: Float64, Int64, String, or DateTime)

Returns

• nothing

Throws

• DatabaseException if the collection or attribute doesn't exist
• DatabaseException if the attribute is not a scalar parameter
• DatabaseException if the value type doesn't match the attribute type
• DatabaseException if the element label doesn't exist

Examples

# Update a string parameter
PSRDatabase.update_scalar_parameter!(db, "Resource", "type", "Resource 1", "D")

# Update a numeric parameter
PSRDatabase.update_scalar_parameter!(db, "Resource", "some_value_1", "Resource 1", 1.0)

# Update a date parameter
PSRDatabase.update_scalar_parameter!(db, "Configuration", "date_initial", "Toy Case", DateTime(2021, 1, 1))

update_time_series_row!(db::DatabaseSQLite, collection_id::String, attribute_id::String, label::String, val; dimensions...)

Update an existing value in a time series attribute for a specific element and dimension combination.

Unlike add_time_series_row!, this function only updates existing rows and will throw an error if the specified dimension combination doesn't exist.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection containing the element
• attribute_id::String: The identifier of the time series attribute
• label::String: The label of the element to update the time series value for
• val: The new value for the time series at the specified dimensions
• dimensions...: Named arguments specifying the dimension values that identify the row to update

Returns

• nothing

Throws

• DatabaseException if the attribute is not a time series
• DatabaseException if the number of dimensions doesn't match the attribute definition
• DatabaseException if dimension names don't match the attribute definition
• DatabaseException if the specified dimension combination doesn't exist

Examples

# Update an existing time series value
PSRDatabase.update_time_series_row!(
    db,
    "Plant",
    "generation",
    "Plant 1",
    150.0;
    date_time = DateTime(2020, 1, 1),
)

# Update with multiple dimensions
PSRDatabase.update_time_series_row!(
    db,
    "Plant",
    "cost",
    "Plant 1",
    75.0;
    date_time = DateTime(2020, 1, 1),
    stage = 1,
)

update_vector_parameters!(db::DatabaseSQLite, collection_id::String, attribute_id::String, label::String, vals::Vector)

Update all values of a vector parameter attribute for a specific element in a collection.

This function replaces all existing values for the vector with the new values provided.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection containing the element
• attribute_id::String: The identifier of the vector parameter attribute to update
• label::String: The label of the element to update
• vals::Vector: A vector containing the new values (must match the attribute's type)

Returns

• nothing

Throws

• DatabaseException if the collection or attribute doesn't exist
• DatabaseException if the attribute is not a vector parameter
• DatabaseException if the value types don't match the attribute type
• DatabaseException if the element label doesn't exist
• DatabaseException if updating vectors in a group and the new length doesn't match other vectors in the group

Examples

# Update a vector of numeric values
PSRDatabase.update_vector_parameters!(
    db,
    "Resource",
    "some_value_1",
    "Resource 1",
    [10.0, 20.0, 30.0],
)

# Update to a different number of values (if not constrained by vector group)
PSRDatabase.update_vector_parameters!(
    db,
    "Resource",
    "some_value_1",
    "Resource 1",
    [5.0, 15.0],
)

delete_element!(db::DatabaseSQLite, collection_id::String, id::Integer)

Delete an element from a collection by its numeric ID.

This function removes an element and all its associated data from the database. Due to CASCADE DELETE foreign key constraints, any references to this element from other collections will also be deleted.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection containing the element
• id::Integer: The numeric ID of the element to delete

Returns

• nothing

Examples

# Delete an element by ID
PSRDatabase.delete_element!(db, "Plant", 3)

# Typically used internally after looking up an ID from a label
id = PSRDatabase._get_id(db, "Plant", "Plant 1")
PSRDatabase.delete_element!(db, "Plant", id)

See Also

• delete_element!(db, collection_id, label): Delete by label instead of numeric ID

delete_element!(db::DatabaseSQLite, collection_id::String, label::String)

Delete an element from a collection by its label.

This function removes an element and all its associated data from the database. Due to CASCADE DELETE foreign key constraints, any references to this element from other collections will also be deleted.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection containing the element
• label::String: The label of the element to delete

Returns

• nothing

Throws

• DatabaseException if the collection doesn't exist
• DatabaseException if the element label doesn't exist
• SQLiteException if the deletion violates database constraints (e.g., foreign key without cascade)

Examples

# Delete an element by label
PSRDatabase.delete_element!(db, "Plant", "Plant 3")

# After deletion, the same label can be reused
PSRDatabase.create_element!(db, "Plant"; label = "Plant 3", capacity = 100.0)

# Deleting an element that has relations will also delete those relations
PSRDatabase.delete_element!(db, "Resource", "Resource 1")  # Also removes Plant->Resource relations

See Also

• delete_element!(db, collection_id, id): Delete by numeric ID instead of label

delete_time_series!(db::DatabaseSQLite, collection_id::String, group_id::String, label::String)

Delete all time series data for a specific element in a time series group.

This function removes all rows from the time series table for a given element, effectively deleting all time series values across all dimensions for that element and group.

Arguments

• db::DatabaseSQLite: The database connection
• collection_id::String: The identifier of the collection containing the element
• group_id::String: The identifier of the time series group
• label::String: The label of the element to delete time series data for

Returns

• nothing

Throws

• DatabaseException if the collection doesn't exist
• DatabaseException if the element label doesn't exist

Examples

# Delete all time series data for an element in a specific group
PSRDatabase.delete_time_series!(db, "Plant", "generation_group", "Plant 1")

# After deletion, you can add new time series data for the same element
PSRDatabase.add_time_series_row!(
    db,
    "Plant",
    "generation",
    "Plant 1",
    100.0;
    date_time = DateTime(2021, 1, 1),
)

Notes

This function only deletes time series data, not the element itself. To delete the entire element, use delete_element! instead.

create_empty_db_from_migrations(database_path::String, path_migrations::String; force::Bool = false)

Create a new empty database by applying migration files.

This function creates a new SQLite database file and applies all migration files found in the specified directory to build up the database structure incrementally.

Arguments

• database_path::String: The file path where the database will be created
• path_migrations::String: The path to the directory containing migration SQL files
• force::Bool: If true, overwrites an existing database file at the same path. If false (default), throws an error if the file already exists

Returns

• DatabaseSQLite: A database connection object to the newly created database

Throws

• DatabaseException if the database file already exists and force=false
• DatabaseException if the migrations directory is not found or migrations are invalid
• DatabaseException if the resulting database structure is invalid
• SQLiteException if SQL statements in migrations are invalid

Examples

# Create a new database from migrations
db = PSRDatabase.create_empty_db_from_migrations(
    "my_database.sqlite",
    "migrations/",
)

# Overwrite existing database
db = PSRDatabase.create_empty_db_from_migrations(
    "my_database.sqlite",
    "migrations/";
    force = true,
)

See Also

• create_empty_db_from_schema: Create database from a single schema file
• load_db: Load and migrate an existing database

create_empty_db_from_schema(database_path::String, path_schema::String; force::Bool = false)

Create a new empty database from a SQL schema file.

This function creates a new SQLite database file and executes all SQL statements from the schema file to set up the database structure (tables, constraints, etc.).

Arguments

• database_path::String: The file path where the database will be created
• path_schema::String: The path to the SQL schema file containing CREATE TABLE statements
• force::Bool: If true, overwrites an existing database file at the same path. If false (default), throws an error if the file already exists

Returns

• DatabaseSQLite: A database connection object to the newly created database

Throws

• DatabaseException if the database file already exists and force=false
• DatabaseException if the schema file is not found
• DatabaseException if the database structure is invalid (e.g., missing Configuration table)
• SQLiteException if SQL statements in the schema are invalid

Examples

# Create a new database from a schema file
db = PSRDatabase.create_empty_db_from_schema(
    "my_database.sqlite",
    "schema.sql",
)

# Overwrite existing database
db = PSRDatabase.create_empty_db_from_schema(
    "my_database.sqlite",
    "schema.sql";
    force = true,
)

See Also

• create_empty_db_from_migrations: Create database using migration files
• load_db: Load an existing database

execute_statements(db::SQLite.DB, file::String)

Execute all statements in a .sql file against a database.

load_db(database_path::String, path_migrations::String)

Load an existing database and apply any pending migrations.

Opens a connection to an existing SQLite database file and applies any migration files that haven't been applied yet.

Arguments

• database_path::String: The file path to the database to load
• path_migrations::String: The path to the directory containing migration SQL files

Returns

• DatabaseSQLite: A database connection object with all migrations applied

Throws

• DatabaseException if the database file doesn't exist
• DatabaseException if migrations are invalid or cannot be applied
• SQLiteException if the file is not a valid SQLite database or migration SQL is invalid

Examples

# Load a database and apply pending migrations
db = PSRDatabase.load_db("my_database.sqlite", "migrations/")

# Work with the database
labels = PSRDatabase.read_scalar_parameters(db, "Plant", "label")

# Close when done
PSRDatabase.close!(db)

See Also

• load_db(database_path; read_only): Load without applying migrations
• create_empty_db_from_migrations: Create new database with migrations

load_db(database_path::String; read_only::Bool = false)

Load an existing database from a file.

Opens a connection to an existing SQLite database file. The database structure is validated and metadata is loaded into memory for fast access.

Arguments

• database_path::String: The file path to the database to load
• read_only::Bool: If true, opens the database in read-only mode (immutable). If false (default), opens with read-write access

Returns

• DatabaseSQLite: A database connection object

Throws

• DatabaseException if the database file doesn't exist
• DatabaseException if the database structure is invalid
• SQLiteException if the file is not a valid SQLite database

Examples

# Load a database with read-write access
db = PSRDatabase.load_db("my_database.sqlite")

# Load a database in read-only mode
db = PSRDatabase.load_db("my_database.sqlite"; read_only = true)

# Use the database and close when done
PSRDatabase.close!(db)

See Also

• load_db(database_path, path_migrations): Load and apply migrations
• create_empty_db_from_schema: Create a new database

_validate_database_pragmas(db::SQLite.DB)

Internal function to validate that required SQLite pragmas are properly set in the database.

Currently validates:

• User version must be defined and not set to zero

Arguments

• db::SQLite.DB: The SQLite database connection to validate

Returns

• nothing

Throws

• DatabaseException if user_version is 0 or not defined

Notes

This is an internal validation function used during database loading to ensure the database has proper version information. The user_version pragma is used for schema versioning and migrations.

GroupMap

Internal struct to store information about a time series group for docstring generation.

Fields

• group_id::String: The identifier of the time series group
• parameters::Vector{TimeSeries{<:Number}}: Vector of time series parameters in the group
• dimensions::Vector{String}: Names of the dimensions for this time series group

collection_docstring(model_folder::String, collection::String; ignore_id::Bool = true)

Generate automatic documentation for a collection's attributes.

This function creates documentation text that lists all required and optional parameters, vector attributes, relations, and time series for a specific collection in a PSR model. It reads the database schema from migrations and attribute metadata from TOML files.

Arguments

• model_folder::String: Path to the model directory containing migrations and UI metadata
• collection::String: Name of the collection to document
• ignore_id::Bool: If true (default), excludes the id field from documentation

Returns

• String: Formatted documentation string with all collection attributes

Examples

# Use in a model-specific add function to automatically generate parameter documentation
"""
    add_hydro_unit!(db::DatabaseSQLite; kwargs...)

Add a Hydro Unit to the database.

# Arguments
$(PSRDatabase.collection_docstring(model_directory(), "HydroUnit"))

# Examples
    db = PSRDatabase.load_db("path/to/model.db")
    add_hydro_unit!(
        db;
        label = "Plant1",
        initial_volume = 100.0,
    )
"""
function add_hydro_unit!(db::DatabaseSQLite; kwargs...)
    PSRDatabase.create_element!(db, "HydroUnit"; kwargs...)
end

Notes

Creates a temporary database from migrations to extract schema information. The temporary database is automatically cleaned up after generating the docstring.

time_series_files_docstrings(model_folder::String, ignore_id::Bool = true)

Generate documentation for all time series file attributes across all collections.

This function creates documentation text listing all time series file attributes organized by collection. It reads the database schema from migrations and attribute metadata from TOML files.

Arguments

• model_folder::String: Path to the model directory containing migrations and UI metadata
• ignore_id::Bool: If true (default), excludes the id field from documentation

Returns

• String: Formatted documentation string with all time series file attributes organized by collection

Examples

# Use in a model's link function to automatically document available time series files
"""
    link_time_series_to_file(db::DatabaseSQLite, table_name::String; kwargs...)

Links a time series to a file in the database.

Each collection in the database can be linked to different time series files.

The possible files for each collection are:

$(PSRDatabase.time_series_files_docstrings(model_directory()))

# Examples
    db = PSRDatabase.load_db("path/to/model.db")
    link_time_series_to_file(
        db,
        "HydroUnit";
        file_path = "generation.csv",
    )
"""
function link_time_series_to_file(db::DatabaseSQLite, table_name::String; kwargs...)
    PSRDatabase.set_time_series_file!(db, table_name; kwargs...)
end

Notes

Creates a temporary database from migrations to extract schema information. The temporary database is automatically cleaned up after generating the docstring.

compare_databases(db1::DatabaseSQLite, db2::DatabaseSQLite)

Compare two databases to ensure they have the same data across all collections.

This function performs a comprehensive comparison of two PSRDatabase databases by iterating through all collections and comparing their:

• Number of elements
• Scalar parameters
• Vector parameters
• Scalar relations
• Vector relations
• Set parameters
• Set relations
• Time series data
• Time series file references

The comparison is thorough and identifies specific differences at the element, attribute, and value level, making it useful for validating database migrations, testing database operations, or ensuring data consistency after transformations.

Arguments

• db1::DatabaseSQLite: The first database to compare
• db2::DatabaseSQLite: The second database to compare

Returns

A vector of strings describing all differences found. Each string is a human-readable error message that identifies:

• The collection where the difference was found
• The attribute or data type being compared
• The specific element (by index or label)
• The exact values that differ

If the databases are completely identical, returns an empty vector.

Examples

# Compare two identical databases
db1 = create_empty_db_from_schema("db1.sqlite", "schema.sql"; force = true)
db2 = create_empty_db_from_schema("db2.sqlite", "schema.sql"; force = true)

create_element!(db1, "Configuration"; label = "Config1", value1 = 100.0)
create_element!(db2, "Configuration"; label = "Config1", value1 = 100.0)

differences = compare_databases(db1, db2)
# Returns: []

# Compare databases with differences
db3 = create_empty_db_from_schema("db3.sqlite", "schema.sql"; force = true)
db4 = create_empty_db_from_schema("db4.sqlite", "schema.sql"; force = true)

create_element!(db3, "Configuration"; label = "Config1", value1 = 100.0)
create_element!(db4, "Configuration"; label = "Config1", value1 = 200.0)

differences = compare_databases(db3, db4)
# Returns: ["Collection 'Configuration', attribute 'value1', element 1: values differ (db1: 100.0, db2: 200.0)"]

compare_scalar_parameters(db1::DatabaseSQLite, db2::DatabaseSQLite, collection_id::String)

Compare scalar parameters between two databases for a specific collection.

This function iterates through all scalar parameters (excluding the id field) in the specified collection and compares their values element-by-element between the two databases. It checks for differences in the number of elements, null values, and actual value mismatches.

Arguments

• db1::DatabaseSQLite: The first database to compare (used as the reference for reading collection structure)
• db2::DatabaseSQLite: The second database to compare against the first
• collection_id::String: The name of the collection (table) to compare scalar parameters for

Returns

A vector of strings describing differences found in scalar parameters. Each string includes the collection name, attribute name, element index, and the differing values. Returns an empty vector if all scalar parameters are identical.

Example

db1 = create_empty_db_from_schema("db1.sqlite", "schema.sql"; force = true)
db2 = create_empty_db_from_schema("db2.sqlite", "schema.sql"; force = true)

create_element!(db1, "Configuration"; label = "Config1", value1 = 100.0)
create_element!(db2, "Configuration"; label = "Config1", value1 = 200.0)

differences = compare_scalar_parameters(db1, db2, "Configuration")
# Returns: ["Collection 'Configuration', attribute 'value1', element 1: values differ (db1: 100.0, db2: 200.0)"]

compare_scalar_relations(db1::DatabaseSQLite, db2::DatabaseSQLite, collection_id::String)

Compare scalar relations between two databases for a specific collection.

This function iterates through all scalar relations (foreign key references to other collections) in the specified collection and compares them element-by-element between the two databases. It verifies that each element points to the same related element in both databases.

Arguments

• db1::DatabaseSQLite: The first database to compare (used as the reference for reading collection structure)
• db2::DatabaseSQLite: The second database to compare against the first
• collection_id::String: The name of the collection (table) to compare scalar relations for

Returns

A vector of strings describing differences found in scalar relations. Each string includes the collection name, relation attribute name, target collection name, element index, and the labels of the related elements that differ. Returns an empty vector if all scalar relations are identical.

Example

db1 = create_empty_db_from_schema("db1.sqlite", "schema.sql"; force = true)
db2 = create_empty_db_from_schema("db2.sqlite", "schema.sql"; force = true)

# Create resources
create_element!(db1, "Resource"; label = "Resource1", type = "D")
create_element!(db1, "Resource"; label = "Resource2", type = "E")
create_element!(db2, "Resource"; label = "Resource1", type = "D")
create_element!(db2, "Resource"; label = "Resource2", type = "E")

# Create plants with different scalar relations
create_element!(db1, "Plant"; label = "Plant1", capacity = 100.0)
create_element!(db2, "Plant"; label = "Plant1", capacity = 100.0)
set_scalar_relation!(db1, "Plant", "Resource", "Plant1", "Resource1", "id")
set_scalar_relation!(db2, "Plant", "Resource", "Plant1", "Resource2", "id")

differences = compare_scalar_relations(db1, db2, "Plant")
# Returns: ["Collection 'Plant', scalar relation 'resource_id' to 'Resource', element 1: relations differ (db1: Resource1, db2: Resource2)"]

compare_set_parameters(db1::DatabaseSQLite, db2::DatabaseSQLite, collection_id::String)

Compare set parameters between two databases for a specific collection.

This function iterates through all set parameters in the specified collection and compares the sets of values for each element between the two databases. For each set attribute, it checks:

• The number of elements in the collection
• The size of each set
• The values within each set (order-independent comparison)

Arguments

• db1::DatabaseSQLite: The first database to compare (used as the reference for reading collection structure)
• db2::DatabaseSQLite: The second database to compare against the first
• collection_id::String: The name of the collection (table) to compare set parameters for

Returns

A vector of strings describing differences found in set parameters. Each string includes the collection name, set attribute name, element index, and the differing set contents. Returns an empty vector if all set parameters are identical.

Example

db1 = create_empty_db_from_schema("db1.sqlite", "schema.sql"; force = true)
db2 = create_empty_db_from_schema("db2.sqlite", "schema.sql"; force = true)

# Create elements with different set parameter values (provided as arrays)
create_element!(db1, "Resource"; label = "Resource1", type = "D", some_set_value1 = [1.0, 2.0], some_set_value2 = [5.0, 4.0])
create_element!(db2, "Resource"; label = "Resource1", type = "D", some_set_value1 = [1.0, 2.0], some_set_value2 = [5.0, 6.0])

differences = compare_set_parameters(db1, db2, "Resource")
# Returns: ["Collection 'Resource', set attribute 'some_set_value2', element 2: sets differ (db1: 4.0, db2: 6.0)"]

compare_set_relations(db1::DatabaseSQLite, db2::DatabaseSQLite, collection_id::String)

Compare set relations between two databases for a specific collection.

This function iterates through all set relations (sets of foreign key references to other collections) in the specified collection and compares them element-by-element between the two databases. For each set relation, it checks:

• The number of elements in the collection
• The size of each relation set
• Individual relation references within each set (order-independent comparison)

Arguments

• db1::DatabaseSQLite: The first database to compare (used as the reference for reading collection structure)
• db2::DatabaseSQLite: The second database to compare against the first
• collection_id::String: The name of the collection (table) to compare set relations for

Returns

A vector of strings describing differences found in set relations. Each string includes the collection name, set relation attribute name, target collection name, element index, and the labels of the related elements that differ. Returns an empty vector if all set relations are identical.

Example

db1 = create_empty_db_from_schema("db1.sqlite", "schema.sql"; force = true)
db2 = create_empty_db_from_schema("db2.sqlite", "schema.sql"; force = true)

# Create costs
for (db, label, value) in [(db1, "Cost1", 10.0), (db1, "Cost2", 20.0), (db1, "Cost3", 30.0),
                            (db2, "Cost1", 10.0), (db2, "Cost2", 20.0), (db2, "Cost3", 30.0)]
    create_element!(db, "Cost"; label = label, value = value)
end

# Create resources with different set relations (provided as separate arrays)
create_element!(db1, "Resource"; label = "Resource1", some_set_factor = [1.0, 2.0], cost_id = ["Cost1", "Cost2"])
create_element!(db2, "Resource"; label = "Resource1", some_set_factor = [1.0, 2.0], cost_id = ["Cost1", "Cost3"])

differences = compare_set_relations(db1, db2, "Resource")
# Returns: ["Collection 'Resource', set relation 'cost_id' to 'Cost', element 1, set index 2: relation sets differ (db1: Cost2, db2: Cost3)"]

compare_time_series(db1::DatabaseSQLite, db2::DatabaseSQLite, collection_id::String)

Compare time series data between two databases for a specific collection.

This function iterates through all time series attributes in the specified collection, grouped by their group_id, and compares the data for each element between the two databases. For each time series, it checks:

• The size of the time series tables (number of rows and columns)
• The column names and their order
• Individual values in each cell of the time series data

The comparison handles missing values and null values appropriately, ensuring that null states match between databases.

Arguments

• db1::DatabaseSQLite: The first database to compare (used as the reference for reading collection structure and element labels)
• db2::DatabaseSQLite: The second database to compare against the first
• collection_id::String: The name of the collection (table) to compare time series data for

Returns

A vector of strings describing differences found in time series data. Each string includes the collection name, time series attribute name, element label, column name, row index, and the differing values. Returns an empty vector if all time series data is identical.

Example

using DataFrames, Dates

db1 = create_empty_db_from_schema("db1.sqlite", "schema.sql"; force = true)
db2 = create_empty_db_from_schema("db2.sqlite", "schema.sql"; force = true)

df1 = DataFrame(
    date_time = [DateTime(2020), DateTime(2021), DateTime(2022)],
    some_vector1 = [1.0, 2.0, 3.0],
    some_vector2 = [10.0, 20.0, 30.0],
)
df2 = DataFrame(
    date_time = [DateTime(2020), DateTime(2021), DateTime(2022)],
    some_vector1 = [1.0, 5.0, 3.0],
    some_vector2 = [10.0, 20.0, 30.0],
)

create_element!(db1, "Resource"; label = "Resource1", type = "D", group1 = df1)
create_element!(db2, "Resource"; label = "Resource1", type = "D", group1 = df2)

differences = compare_time_series(db1, db2, "Resource")
# Returns: ["Collection 'Resource', time series 'some_vector1', label 'Resource1', column 'some_vector1', row 2: values differ (db1: 2.0, db2: 5.0)"]

compare_time_series_files(db1::DatabaseSQLite, db2::DatabaseSQLite, collection_id::String)

Compare time series file paths between two databases for a specific collection.

This function compares the file paths stored in the timeseriesfiles table for each element in the specified collection. It checks whether file paths are present in one database but not the other, and whether the file paths match when present in both databases.

Note that this function only compares the file path strings stored in the database, not the contents of the files themselves.

Arguments

• db1::DatabaseSQLite: The first database to compare (used as the reference for reading collection structure and element labels)
• db2::DatabaseSQLite: The second database to compare against the first
• collection_id::String: The name of the collection (table) to compare time series file references for

Returns

A vector of strings describing differences found in time series file paths. Each string includes the collection name, time series file attribute name, and information about whether file paths are missing or differ between databases. Returns an empty vector if all time series file references are identical.

Example

db1 = create_empty_db_from_schema("db1.sqlite", "schema.sql"; force = true)
db2 = create_empty_db_from_schema("db2.sqlite", "schema.sql"; force = true)

create_element!(db1, "Plant"; label = "Plant1", capacity = 100.0)
create_element!(db2, "Plant"; label = "Plant1", capacity = 100.0)

set_time_series_file!(db1, "Plant"; generation = "generation1.csv")
set_time_series_file!(db2, "Plant"; generation = "generation2.csv")

differences = compare_time_series_files(db1, db2, "Plant")
# Returns: ["Collection 'Plant', time series file 'generation': file paths differ (db1: generation1.csv, db2: generation2.csv)"]

compare_vector_parameters(db1::DatabaseSQLite, db2::DatabaseSQLite, collection_id::String)

Compare vector parameters between two databases for a specific collection.

This function iterates through all vector parameters in the specified collection and compares their values element-by-element between the two databases. For each vector attribute, it checks:

• The number of elements in the collection
• The length of each vector
• Individual values within each vector

Arguments

• db1::DatabaseSQLite: The first database to compare (used as the reference for reading collection structure)
• db2::DatabaseSQLite: The second database to compare against the first
• collection_id::String: The name of the collection (table) to compare vector parameters for

Returns

A vector of strings describing differences found in vector parameters. Each string includes the collection name, vector attribute name, element index, vector index, and the differing values. Returns an empty vector if all vector parameters are identical.

Example

db1 = create_empty_db_from_schema("db1.sqlite", "schema.sql"; force = true)
db2 = create_empty_db_from_schema("db2.sqlite", "schema.sql"; force = true)

create_element!(db1, "Resource"; label = "Resource1", type = "D", some_value1 = [1.0, 2.0, 3.0])
create_element!(db2, "Resource"; label = "Resource1", type = "D", some_value1 = [1.0, 5.0, 3.0])

differences = compare_vector_parameters(db1, db2, "Resource")
# Returns: ["Collection 'Resource', vector attribute 'some_value1', element 1, index 2: values differ (db1: 2.0, db2: 5.0)"]

compare_vector_relations(db1::DatabaseSQLite, db2::DatabaseSQLite, collection_id::String)

Compare vector relations between two databases for a specific collection.

This function iterates through all vector relations (arrays of foreign key references to other collections) in the specified collection and compares them element-by-element between the two databases. For each vector relation, it checks:

• The number of elements in the collection
• The length of each relation vector
• Individual relation references within each vector

Arguments

• db1::DatabaseSQLite: The first database to compare (used as the reference for reading collection structure)
• db2::DatabaseSQLite: The second database to compare against the first
• collection_id::String: The name of the collection (table) to compare vector relations for

Returns

A vector of strings describing differences found in vector relations. Each string includes the collection name, vector relation attribute name, target collection name, element index, vector index, and the labels of the related elements that differ. Returns an empty vector if all vector relations are identical.

Example

db1 = create_empty_db_from_schema("db1.sqlite", "schema.sql"; force = true)
db2 = create_empty_db_from_schema("db2.sqlite", "schema.sql"; force = true)

# Create costs
for (db, label, value) in [(db1, "Cost1", 10.0), (db1, "Cost2", 20.0), (db1, "Cost3", 30.0),
                            (db2, "Cost1", 10.0), (db2, "Cost2", 20.0), (db2, "Cost3", 30.0)]
    create_element!(db, "Cost"; label = label, value = value)
end

# Create plants with different vector relations
create_element!(db1, "Plant"; label = "Plant1", capacity = 100.0, some_factor = [1.0, 2.0])
create_element!(db2, "Plant"; label = "Plant1", capacity = 100.0, some_factor = [1.0, 2.0])
set_vector_relation!(db1, "Plant", "Cost", "Plant1", ["Cost1", "Cost2"], "id")
set_vector_relation!(db2, "Plant", "Cost", "Plant1", ["Cost1", "Cost3"], "id")

differences = compare_vector_relations(db1, db2, "Plant")
# Returns: ["Collection 'Plant', vector relation 'cost_id' to 'Cost', element 1, index 2: relations differ (db1: Cost2, db2: Cost3)"]