Understand SPE database scripts
This article explains details and usage of SQL database scripts stored in SPE installation directory in
/data/database subdirectory. These scripts are intended for setup and maintenance of SPE database for supported database types, currently SQLite and MariaDB (from SPE 3.46) / MySQL (up to SPE 3.45).
For each database type, there are two directories with two types of scripts:
v1700, etc.) – scripts for SPE database initial setup and maintenance, i.e. when SPE is set up for the first time, or re-installed
update– scripts for the SPE database content update, i.e. when SPE is updated to newer version
database ├── MariaDB │ ├── update │ └── v1900 ├── SQLite │ ├── v1900 │ └── update └── MySQL - obsolete ├── v1800 └── update
Scripts for SQLite are used automatically by SPE, i.e. users using SPE with default SQLite database don’t really need to worry about it.
This is because SPE has complete control over the SQLite database and therefore can perform all needed operations without any limitation.
Scripts for MariaDB and MySQL are intended to be used manually, e.g. from phpMyAdmin, or from MariaDB/MySQL command line client.
The reason for having to execute the scripts manually is that in typical setups SPE does not have complete control over the MariaDB/MySQL database – the database is typically hosted on a separate databse server, SPE is typically only one of many other applications using the database server, and/or SPE has only limited access rights to the database for security reasons. Therefore we provide the SQL scripts and leave the SPE/database updates completely on the SPE administrators.
Scripts for SPE database initial setup and maintenance
create_schema.sql – script for creation of DB schema required by SPE in a freshly created empty DB
init_data.sql – script for filling initial data to DB created by ‘create_schema’ script
drop.sql – script for quick complete DB cleanup by dropping all SPE tables
clean.sql – script for DB cleanup by cleaning data from tables, e.g. when your DB rights don’t allow you to drop tables
The intended usage is:
- when starting with new empty DB (created manually, e.g. using phpMyAdmin, or MySQL command line client)
- then use
- when you need to clean your SPE DB (and don’t want to delete/re-create the entire DB for some reason)
drop.sqlto completely erase the DB content, followed by re-creating the content using
- or use
clean.sqlto clean “rest_directory_type”, “rest_role”, “rest_user”, “rest_technology_model” and “rest_model_lid” tables
Scripts for SPE database content update
As the SPE evolves and new features come or functionality gets improved, the database structure needs to change from time to time. So, when updating from older SPE version to newer version, the database content needs to be updated.
Therefore, the database structure is versioned – database version is listed in SPE changelog together with SPE version, like on the following example (SPE 3.35.9 uses DB version 1602, SPE 3.40.7 uses DB version 1701):
... ## 3.40.7, DB v1701, BSAPI 3.40.4 (2021-06-30) ### Fixed - Fixes from 3.35.9 ## 3.35.9, DB v1602, BSAPI 3.35.5 (2021-06-30) ### Fixed - [DEL-2050] Invalid SQL statement on update of SPE - fixed SQLite update script from v1601 to v1602 ...
To allow updating from any DB version to any DB version, the
update directory contains scripts which “describe” the changes in DB structure between two consecutive versions.
v1500-v1600.sql “describes” difference between DB version 1500 and 1600. Executing that script would update DB with version 1500 structure to a DB with version 1600 structure.
The intended usage when updating SPE to newer version is:
- Check the SPE changelog and get DB version number
- used by your current SPE
- used by the SPE you are updating to
- Run every script starting with DB version from which you are updating, until the database version to which you are updating
ⓘ Detailed update instructions are always described in
UPDATE.txt file in
doc directory (see Understanding SPE directory structure article).
• from SPE version 3.30.14 (which uses DB version 1401)
• to SPE version 3.40.8 (which uses DB version 1701)
you need to execute the following scripts – one after another in that precise order – before launching the new SPE version: