Database Scripts

This article explains the details and usage of SQL database scripts stored in the SPE installation directory in /data/database subdirectory. These scripts are intended for the setup and maintenance of Speech engine database for supported database types, currently SQLite and MariaDB (from SPE 3.46) / MySQL (up to SPE 3.45).

Script types

For each database type, there are two directories with two types of scripts:

• vXXXX (e.g. v1601 or 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 a 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 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 database 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 a new empty DB (created manually, e.g. using phpMyAdmin, or MySQL command line client)
  • use create_schema.sql script
  • then use init_data.sql script
• when you need to clean your SPE DB (and don't want to delete/re-create the entire DB for some reason)
  • use drop.sql to completely erase the DB content, followed by re-creating the content using create_schema.sql and init_data.sql
  • or use clean.sql to 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 an older SPE version to a newer version, the database content needs to be updated. Therefore, the database structure is versioned – the 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 an 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.

E.g. script 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 a newer version is:

• Check the SPE changelog and get the DB version number
  • used by your current SPE
  • used by the SPE you are updating to
• Run every script starting with the DB version from which you are updating, until the database version to which you are updating

tip

Detailed update instructions are always described in UPDATE.txt file in doc directory see Speech engine - directory structure article.

Update example

When updating • 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:

1. v1401-v1500.sql
2. v1500-v1600.sql
3. v1600-v1601.sql
4. v1601-v1700.sql
5. v1700-v1701.sql