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::Int)

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_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_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::Int)

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::Int: 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

closest_date_query(db::DatabaseSQLite, attribute::Attribute, dim_value::DateTime)

Query the closest date that is less than or equal to the specified date in a time series attribute table.

Arguments

• db::DatabaseSQLite: The database connection
• attribute::Attribute: The time series attribute
• dim_value::DateTime: The target date to search for

Returns

• DateTime: The closest date on or before dim_value, or DateTime(0) if no such date exists

end_date_query(db::DatabaseSQLite, attribute::Attribute)

Query the maximum (most recent) date available in a time series attribute table.

Arguments

• db::DatabaseSQLite: The database connection
• attribute::Attribute: The time series attribute

Returns

• DateTime: The most recent date in the time series, or DateTime(0) if no data exists

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_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 (e.g., "id", "group")

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 (e.g., "id", "group")

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_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.