Company "Karelian Developer" presents update 0.4.0 "Segezha" for the "GIRVAS" content management system. The version replaces the code stage "Shuya" and is named after the Segezha River — a water body of the Republic of Karelia, continuing the tradition of naming CMS versions after Karelian rivers and lakes. This tradition emphasizes the regional identity of the product and its integration into the landscape of domestic technological developments.
Release 0.4.0 is the largest in the entire history of the project since 2021. It combines several strategic directions: bringing the system into compliance with the requirements of Federal Law No. 152-FZ "On Personal Data", laying the foundation for OAuth provider functionality, introducing a SQL dialect layer with support for MySQL and PostgreSQL, as well as a number of improvements to the administrative panel and the public part of the site.
152-FZ: Full Technical Coverage at the Core Level
One of the key directions of the release was bringing the "GIRVAS" CMS into compliance with the requirements of Federal Law No. 152-FZ "On Personal Data." In version 0.4.0, full technical coverage of the key requirements of the law is implemented at the core level of the system — without the need to install third-party plugins or develop custom solutions.
Personal data legislation imposes a whole range of requirements on operators: the need to record the fact of obtaining the subject"s consent, to provide evidence of that consent, to store data on the territory of the Russian Federation, to provide the subject with access to their data and the ability to withdraw consent. For sites that collect visitor data through feedback forms, registration, or cookie banners, compliance with these requirements is mandatory.
Version 0.4.0 implements the following mechanisms:
- Versioning of legal documents. Each publication of a legal document — privacy policy, user agreement, consent to data processing — creates an immutable snapshot with a version number, effective date, and author. The version history is immutable, which meets audit requirements. A site visitor can view the specific version of the document to which they consented.
- Recording of consents. The system records the user"s consent to a specific document and version of its revision, taking into account the locale in which the user viewed the document, as well as the IP address and User-Agent. Consents are recorded when submitting dynamic forms, when registering on the site, and through the cookie banner — including consents of anonymous visitors.
- Withdrawal of consent. The user can withdraw consent through their profile. The system administrator can also withdraw consent indicating a reason. Both actions are recorded in the log with a distinction between withdrawal by the subject and withdrawal by the administrator.
- Export of subject data. The system generates an archive with user data upon request: profile, consents, reports, and a manifest with metadata about who exported the data and when. This ensures compliance with the requirements of Article 14 of the law.
- Protection against IP address spoofing. A mechanism for determining the real IP address of a visitor has been implemented, taking into account trusted proxies. If the server is not in the trusted list, X-Forwarded-For headers are ignored — this prevents IP spoofing during logging.
- Anonymization of personal data. Instead of deleting data, the system performs depersonalization: login, email, password, and metadata are replaced with anonymous values. This complies with the requirements of Article 5 of the law and preserves the data structure for auditing.
- Log rotation. Reports older than a specified period are archived in a separate table and deleted from the main one. Configuration is performed through the administrative panel, and rotation can be launched automatically on a schedule.
- Cookie banner. A built-in banner for all site visitors, including anonymous ones, with consent recording and protection against duplicates. The consent of an anonymous visitor is recorded with identifier 0, which allows it to be distinguished from the consents of registered users.
- Logging of all events. The "GIRVAS" CMS, as part of implementing full technical coverage of 152-FZ at the core level, has been supplemented with a new section "CMS Reports," which displays all events in the system, including tracking views and interactions with personal data.
For site owners, this means that when using "GIRVAS" they will not have to look for external solutions for compliance tasks — the system provides the technical side of the matter, leaving the operator with organizational measures: appointing a person responsible for data processing, developing internal documents, and training employees.
It is worth noting that none of the popular CMS on the market implements the full cycle of linking consent to a version of a legal document at the core level. Individual plugins cover part of the task, but these are always third-party extensions that do not cover the entire spectrum of 152-FZ requirements. "GIRVAS" is one of the few systems where this is implemented at the core level.
New Section "CMS Reports"
This is the first introduction of a new section in the past year, which effectively became the foundation for technical coverage of 152-FZ requirements. The section has been supplemented with three subsections:
- General Summary. The report displays general information on all subsections of the system"s reporting: from the top active users to the latest events.
- Content. Statistics on content in the system and the latest events related to it.
- Security. Statistics on interaction with the system from a security standpoint: successful/unsuccessful logins to the administrative panel or site, blocks/unblocks, views of personal data and reports, a list of IP addresses (from which successful login to the administrative panel occurred), etc.
New Interactive Elements and Capabilities of "NadvoTE"
The "GIRVAS" CMS in version 0.4.0 has been supplemented with a number of new interactive elements:
Galleries
Galleries in the "GIRVAS" CMS allow embedding an interactive image carousel — in the future they will begin to support different image display modes. The MarkDown parser "NadvoParse" also allows embedding galleries using the following syntax:
[gallery]
[/gallery]Example of Gallery Display
Data Finder
The interactive element for data search has been successfully integrated into the subsections of the administrative panel of the "GIRVAS" CMS and allows finding the necessary elements of system entities on their list pages (entries, static pages, categories, user groups, etc.).
Tabs
Tabs allow systematizing data and sorting it within a common container to save space.
New Tools of the "NadvoTE" Interactive Editor
The "NadvoTE" interactive editor, developed specifically for the "GIRVAS" CMS by the company "Karelian Developer," has been supplemented with new tools:
- Undo — undo the last change.
- Redo — redo the undone change.
- Gallery — embed an interactive image gallery.
- Emoji — insert emoji from the primary set.
In addition, the editor now supports hotkeys for working with the toolkit in quick mode:
Ctrl + Z— undo the last change.Ctrl + Y— redo the last change.Ctrl + B— make the selection bold (will be wrapped in**text**).Ctrl + I— make the selection italic (will be wrapped in*text*).Ctrl + U— make the selection underlined (will be wrapped in~~text~~).
The "NadvoTE" editor now also allows wrapping selections in various quotes and brackets — just select the text and press a key or combination on the keyboard to type a quote or bracket. "NadvoTE" is becoming even easier to use.
OAuth Provider: Foundation for Single Sign-On
Version 0.4.0 lays the foundation for a built-in OAuth provider — a mechanism that will allow any site on the "GIRVAS" CMS to act as an authorization server for third-party applications.
OAuth is an open authorization standard that allows users to grant third-party services access to their data without transmitting a password. For example, a user can log in to an external service through the company"s website without creating a separate account for this. This opens the path to single sign-on between sites on "GIRVAS" and connecting external services without the need to develop custom solutions.
The laid architecture includes support for the Authorization Code Grant protocol with mandatory support for PKCE (S256) — a mechanism that increases security when working with public clients. Strict validation of redirect_uri, one-time authorization codes with a short lifetime, refresh token rotation on each refresh, and hashing of client_secret have been implemented.
The presence of a built-in OAuth provider in a free CMS is a rarity for the Russian market. Usually such capabilities are implemented through paid modules or custom development. "GIRVAS" lays this functionality at the core level, which opens the path to single sign-on between sites on this CMS.
The server part of the provider has been implemented and tested. Interfaces for the administrator and user — the consent form, the section for managing issued accesses, the client verification interface — will appear in subsequent updates.
SQL Dialects: MySQL and PostgreSQL Support
One of the most significant technical changes of the version was the introduction of a SQL dialect layer — an abstraction mechanism over database management systems.
Previously, the "GIRVAS" CMS worked exclusively with PostgreSQL and MySQL. Now both DBMSs work through a unified abstraction layer — this also opens the path for introducing other DBMSs. This means that the site owner can choose the DBMS that is more convenient for them, and the system will work equally stably in both cases.
The dialect architecture is built on logical data types: instead of physical types of a specific DBMS, the system operates with abstract concepts — identifier, string, text, JSON, boolean value, timestamp. Each dialect independently converts the logical type into a physical one for its DBMS. This allows adding support for new DBMSs without modifying the core — it is enough to add one dialect class. The list of supported DBMSs will grow with subsequent updates, among which will be: MariaDB, SQLite, and others — now this will be easier to do with SQL dialects.
For developers, this means that extending the system and adding new modules has become easier: the code is not tied to a specific DBMS but works through a unified interface. For site owners — that "GIRVAS" can be deployed on the hosting they already use, without the need to migrate to PostgreSQL or MySQL.
Multilingual Site Settings
Version 0.4.0 implements multilingual site settings. Previously, multilingualism was limited to content — entries, pages, categories. Now site settings can also have different values for different languages.
The following have become multilingual: site name, site description, and keywords. The storage format is a JSON object, where the key is the locale code and the value is the text in the corresponding language. On first save, old values are automatically migrated to the new format, which ensures backward compatibility.
For sites operating in multiple languages, this means that the site title and description can be displayed in the visitor"s language, rather than being the same for everyone. This improves both user experience and search engine optimization for multilingual sites.
Administrative Panel: Search and Sorting
In version 0.4.0, search and sorting have been implemented in the administrative panel in all sections with content lists: users, groups, consents, entries, static pages, categories, comments, samples, forms, and content blocks.
Search works via a GET parameter in the same section, without navigating to separate pages. Sorting is available by six rules: by creation and update date in both directions, as well as alphabetically in both directions. Search and sorting parameters are preserved when navigating through pagination pages — this means that the user does not lose context when viewing large lists.
For site administrators, this means that working with large volumes of content has become noticeably more convenient. Previously, to find the desired entry among hundreds, one had to scroll through pages. Now it is enough to enter part of the title or select a sorting rule.
Integration with Yandex.Metrica
Version 0.4.0 implements built-in integration with Yandex.Metrica. The administrator specifies the numeric counter ID in the search engine optimization settings, after which the system automatically outputs the standard Metrica script on all pages of the public part of the site.
Double validation has been implemented: at the stage of saving the setting, all non-numeric characters are removed from the entered value, and at the output stage, a check is performed that the value is a number. This protects against input errors and attempts to inject arbitrary code through the settings field.
The integration already works in the «default» theme, and the parameter for embedding Metrica is available in the "Search Engine Optimization" subsection of the "CMS Settings" section.
Technical Improvements of the Core
In addition to the above, version 0.4.0 contains significant improvements to the QueryBuilder component — the internal SQL query builder.
Support for JOINs (INNER, LEFT, RIGHT, FULL) with adaptive condition generation for MySQL and PostgreSQL has been added. A class for building CASE expressions has been implemented, including specialized methods for JSON search. Full support for indexes has been added: creation and deletion taking into account the types BTREE, HASH, GIST, GIN, SPGIST, BRIN, FULLTEXT, as well as UNIQUE, CONCURRENTLY, IF NOT EXISTS, and partial indexes with WHERE conditions.
During CMS installation, optimizing indexes are now automatically created for all tables, including GIN indexes for JSONB fields in PostgreSQL and a partial index for published entries. Index creation errors are logged without interrupting the installation process.
For site owners, this means that the system works faster and more stably even with growing content volume. For developers — that extending the system and adding new modules has become easier due to a more flexible query builder.
Fixes
Version 0.4.0 eliminates a number of errors:
- The cookie banner was shown again when the allowCookies cookie was present — the cookie check logic has been fixed.
- Batch insertion on MySQL could work incorrectly under certain DBMS settings — replaced with a transaction with row-by-row insertion of records.
- Leakage of sensitive fields in debug output — masking of client_secret, code_verifier, access_token, refresh_token, and passwords has been added.
- Regeneration of the OAuth client secret did not save the new secret — fixed.
- Empty scope when refreshing the token via refresh — fixed.
- Periodic inability to work in multilingual mode in the administrative panel.
- Inability to send requests from the frontend to the backend when working simultaneously from several browser tabs.
Migration
File System Update
We strongly recommend obtaining the update through the official repository of the "GIRVAS" CMS. If you use it by default, then in the root of your project execute the console command git pull, having previously switched to the production branch (if you previously used a different branch).
After updating the file system, be sure to clear the system cache: rm -R ./cache/* (the path may differ depending on your actual location in the system).
Database Migration
After successfully updating the CMS files, you need to perform the database migration. To do this, you will need to execute several SQL queries against your database, depending on the type of your DBMS.
MySQL Migration
Unlike PostgreSQL, MySQL migration is not transactional — each ALTER TABLE is committed immediately. If the migration fails in the middle, part of the changes will remain. Therefore, the process is divided into three stages:
- Pre-check — we make sure that the data fits into the new types.
- Migration — we apply the changes.
- Post-check — we make sure everything is in place.
Before starting, be sure to make a backup:
mysqldump -h HOST -u USER -p DBNAME > backup_before_migration.sqlMySQL Migration Limitations
Due to differences between DBMSs, some PostgreSQL objects have no analogues in MySQL:
- GIN indexes for JSONB fields (
idx_entries_texts_gin,idx_entries_metadata_gin,idx_pages_static_texts_gin,idx_pages_static_versions_texts_gin) — are not created in MySQL. JSON search will work, but slower. - Partial indexes (
idx_entries_published,idx_users_consents_active,idx_users_consents_revoked_by) — are created in MySQL as regular ones, withoutWHERE. Functionality is preserved, but filtering efficiency is slightly lower.
These are DBMS limitations, not CMS limitations. The system"s functionality is fully preserved.
Pre-check
-- =====================================================================
-- PRE-CHECK before migration of CMS "GIRVAS" 0.4.0 "Segezha"
-- DBMS: MySQL 8.0+
-- Run BEFORE migration
--
-- If at least one check returns PROBLEM rows — STOP,
-- migration must not be launched.
-- =====================================================================
SET NAMES utf8mb4;
-- ---------------------------------------------------------------------
-- CHECK 1. MAX(id) — does it exceed 2^31-1 (2147483647)
-- If it is greater somewhere — casting id to int will truncate data.
-- ---------------------------------------------------------------------
SELECT "configurations" AS table_name, MAX(id) AS max_id FROM configurations
UNION ALL SELECT "content_blocks", MAX(id) FROM content_blocks
UNION ALL SELECT "entries", MAX(id) FROM entries
UNION ALL SELECT "entries_categories", MAX(id) FROM entries_categories
UNION ALL SELECT "entries_comments", MAX(id) FROM entries_comments
UNION ALL SELECT "entries_samples", MAX(id) FROM entries_samples
UNION ALL SELECT "forms", MAX(id) FROM forms
UNION ALL SELECT "forms_data", MAX(id) FROM forms_data
UNION ALL SELECT "metrics", MAX(id) FROM metrics
UNION ALL SELECT "pages_static", MAX(id) FROM pages_static
UNION ALL SELECT "reports", MAX(id) FROM reports
UNION ALL SELECT "users", MAX(id) FROM users
UNION ALL SELECT "users_groups", MAX(id) FROM users_groups
UNION ALL SELECT "users_registration_submits", MAX(id) FROM users_registration_submits
UNION ALL SELECT "users_sessions", MAX(id) FROM users_sessions
UNION ALL SELECT "web_channels", MAX(id) FROM web_channels;
-- ---------------------------------------------------------------------
-- CHECK 2. Are there any data longer than the limits for text → varchar(N)
-- All values must be 0.
-- ---------------------------------------------------------------------
SELECT "configurations.name" AS column_path, COUNT(*) AS too_long
FROM configurations WHERE CHAR_LENGTH(name) > 255
UNION ALL
SELECT "entries.name", COUNT(*) FROM entries WHERE CHAR_LENGTH(name) > 255
UNION ALL
SELECT "entries_categories.name", COUNT(*) FROM entries_categories WHERE CHAR_LENGTH(name) > 255
UNION ALL
SELECT "entries_samples.name", COUNT(*) FROM entries_samples WHERE CHAR_LENGTH(name) > 255
UNION ALL
SELECT "forms.name", COUNT(*) FROM forms WHERE CHAR_LENGTH(name) > 255
UNION ALL
SELECT "pages_static.name", COUNT(*) FROM pages_static WHERE CHAR_LENGTH(name) > 255
UNION ALL
SELECT "users.login", COUNT(*) FROM users WHERE CHAR_LENGTH(login) > 255
UNION ALL
SELECT "users.email", COUNT(*) FROM users WHERE CHAR_LENGTH(email) > 255
UNION ALL
SELECT "users_groups.name", COUNT(*) FROM users_groups WHERE CHAR_LENGTH(name) > 255
UNION ALL
SELECT "users_registration_submits.submitToken",
COUNT(*) FROM users_registration_submits WHERE CHAR_LENGTH(submitToken) > 255
UNION ALL
SELECT "users_registration_submits.refusalToken",
COUNT(*) FROM users_registration_submits WHERE CHAR_LENGTH(refusalToken) > 255
UNION ALL
SELECT "users_sessions.token", COUNT(*) FROM users_sessions WHERE CHAR_LENGTH(token) > 255
UNION ALL
SELECT "users_sessions.userIP", COUNT(*) FROM users_sessions WHERE CHAR_LENGTH(userIP) > 64
UNION ALL
SELECT "web_channels.name", COUNT(*) FROM web_channels WHERE CHAR_LENGTH(name) > 255;
-- ---------------------------------------------------------------------
-- CHECK 3. Are there any duplicates before creating UNIQUE indexes
-- Empty = OK.
-- ---------------------------------------------------------------------
SELECT "entries.name" AS unique_candidate, name AS value, COUNT(*) AS cnt
FROM entries GROUP BY name HAVING cnt > 1 LIMIT 10;
SELECT "entries_categories.name" AS unique_candidate, name AS value, COUNT(*) AS cnt
FROM entries_categories GROUP BY name HAVING cnt > 1 LIMIT 10;
SELECT "pages_static.name" AS unique_candidate, name AS value, COUNT(*) AS cnt
FROM pages_static GROUP BY name HAVING cnt > 1 LIMIT 10;
SELECT "users_groups.name" AS unique_candidate, name AS value, COUNT(*) AS cnt
FROM users_groups GROUP BY name HAVING cnt > 1 LIMIT 10;
SELECT "web_channels.name" AS unique_candidate, name AS value, COUNT(*) AS cnt
FROM web_channels GROUP BY name HAVING cnt > 1 LIMIT 10;
SELECT "users.login" AS unique_candidate, login AS value, COUNT(*) AS cnt
FROM users GROUP BY login HAVING cnt > 1 LIMIT 10;
SELECT "users.email" AS unique_candidate, email AS value, COUNT(*) AS cnt
FROM users GROUP BY email HAVING cnt > 1 LIMIT 10;
-- ---------------------------------------------------------------------
-- CHECK 4. Server environment
-- ---------------------------------------------------------------------
SELECT
VERSION() AS mysql_version,
@@sql_mode AS sql_mode,
@@lower_case_table_names AS lower_case_table_names,
@@character_set_database AS charset,
@@collation_database AS collation,
@@default_storage_engine AS engine;
-- ---------------------------------------------------------------------
-- CHECK 5. Duplicate base_site_title in configurations (informational)
-- If there are two rows — this is an installer bug, not critical.
-- ---------------------------------------------------------------------
SELECT id, name, LEFT(value, 60) AS value_preview
FROM configurations
WHERE name = "base_site_title"
ORDER BY id;Migration
-- =====================================================================
-- MIGRATION of CMS "GIRVAS" 0.4.0 "Segezha"
-- DBMS: MySQL 8.0+
-- Idempotent: can be run repeatedly
--
-- IMPORTANT: make a backup before running:
-- mysqldump -h HOST -u USER -p DBNAME > backup_before_migration.sql
--
-- IMPORTANT: DO NOT wrap in START TRANSACTION.
-- DDL in MySQL is not transactional.
-- =====================================================================
SET NAMES utf8mb4;
SET @db := DATABASE();
-- =====================================================================
-- STEP 1. ALTER COLUMN: bigint unsigned → int for id
-- =====================================================================
-- 1.1. configurations.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "configurations"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE configurations MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: configurations.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.2. content_blocks.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "content_blocks"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE content_blocks MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: content_blocks.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.3. entries.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "entries"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE entries MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: entries.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.4. entries_categories.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "entries_categories"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE entries_categories MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: entries_categories.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.5. entries_comments.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "entries_comments"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE entries_comments MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: entries_comments.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.6. entries_samples.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "entries_samples"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE entries_samples MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: entries_samples.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.7. forms.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "forms"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE forms MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: forms.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.8. forms_data.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "forms_data"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE forms_data MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: forms_data.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.9. metrics.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "metrics"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE metrics MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: metrics.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.10. pages_static.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "pages_static"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE pages_static MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: pages_static.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.11. reports.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "reports"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE reports MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: reports.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.12. users.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE users MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: users.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.13. users_groups.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users_groups"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE users_groups MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: users_groups.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.14. users_registration_submits.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users_registration_submits"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE users_registration_submits MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: users_registration_submits.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.15. users_sessions.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users_sessions"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE users_sessions MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: users_sessions.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 1.16. web_channels.id
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "web_channels"
AND column_name = "id" AND column_type = "bigint unsigned"),
"ALTER TABLE web_channels MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT",
"SELECT ""skip: web_channels.id already int"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- =====================================================================
-- STEP 2. ALTER COLUMN: text → varchar(N)
-- =====================================================================
-- 2.1. configurations.name
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "configurations"
AND column_name = "name" AND data_type = "text"),
"ALTER TABLE configurations MODIFY COLUMN name VARCHAR(255) NOT NULL",
"SELECT ""skip: configurations.name already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.2. entries.name
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "entries"
AND column_name = "name" AND data_type = "text"),
"ALTER TABLE entries MODIFY COLUMN name VARCHAR(255) NOT NULL",
"SELECT ""skip: entries.name already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.3. entries_categories.name
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "entries_categories"
AND column_name = "name" AND data_type = "text"),
"ALTER TABLE entries_categories MODIFY COLUMN name VARCHAR(255) NOT NULL",
"SELECT ""skip: entries_categories.name already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.4. entries_samples.name
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "entries_samples"
AND column_name = "name" AND data_type = "text"),
"ALTER TABLE entries_samples MODIFY COLUMN name VARCHAR(255) NOT NULL",
"SELECT ""skip: entries_samples.name already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.5. forms.name
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "forms"
AND column_name = "name" AND data_type = "text"),
"ALTER TABLE forms MODIFY COLUMN name VARCHAR(255) NOT NULL",
"SELECT ""skip: forms.name already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.6. pages_static.name
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "pages_static"
AND column_name = "name" AND data_type = "text"),
"ALTER TABLE pages_static MODIFY COLUMN name VARCHAR(255) NOT NULL",
"SELECT ""skip: pages_static.name already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.7. users.login
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users"
AND column_name = "login" AND data_type = "text"),
"ALTER TABLE users MODIFY COLUMN login VARCHAR(255) NOT NULL",
"SELECT ""skip: users.login already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.8. users.email
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users"
AND column_name = "email" AND data_type = "text"),
"ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL",
"SELECT ""skip: users.email already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.9. users_groups.name
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users_groups"
AND column_name = "name" AND data_type = "text"),
"ALTER TABLE users_groups MODIFY COLUMN name VARCHAR(255) NOT NULL",
"SELECT ""skip: users_groups.name already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.10. users_registration_submits.submitToken
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users_registration_submits"
AND column_name = "submitToken" AND data_type = "text"),
"ALTER TABLE users_registration_submits MODIFY COLUMN submitToken VARCHAR(255) NOT NULL",
"SELECT ""skip: submitToken already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.11. users_registration_submits.refusalToken
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users_registration_submits"
AND column_name = "refusalToken" AND data_type = "text"),
"ALTER TABLE users_registration_submits MODIFY COLUMN refusalToken VARCHAR(255) NOT NULL",
"SELECT ""skip: refusalToken already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.12. users_sessions.token
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users_sessions"
AND column_name = "token" AND data_type = "text"),
"ALTER TABLE users_sessions MODIFY COLUMN token VARCHAR(255) NOT NULL",
"SELECT ""skip: users_sessions.token already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.13. users_sessions.userIP
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users_sessions"
AND column_name = "userIP" AND data_type = "text"),
"ALTER TABLE users_sessions MODIFY COLUMN userIP VARCHAR(64) NOT NULL",
"SELECT ""skip: users_sessions.userIP already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2.14. web_channels.name
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "web_channels"
AND column_name = "name" AND data_type = "text"),
"ALTER TABLE web_channels MODIFY COLUMN name VARCHAR(255) NOT NULL",
"SELECT ""skip: web_channels.name already varchar"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- =====================================================================
-- STEP 3. ALTER COLUMN: text → longtext
-- =====================================================================
-- 3.1. configurations.value
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "configurations"
AND column_name = "value" AND data_type = "text"),
"ALTER TABLE configurations MODIFY COLUMN value LONGTEXT",
"SELECT ""skip: configurations.value already longtext"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 3.2. entries_comments.content
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "entries_comments"
AND column_name = "content" AND data_type = "text"),
"ALTER TABLE entries_comments MODIFY COLUMN content LONGTEXT",
"SELECT ""skip: entries_comments.content already longtext"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 3.3. users.passwordHash
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users"
AND column_name = "passwordHash" AND data_type = "text"),
"ALTER TABLE users MODIFY COLUMN passwordHash LONGTEXT NOT NULL",
"SELECT ""skip: users.passwordHash already longtext"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 3.4. users.securityHash
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.columns
WHERE table_schema = @db AND table_name = "users"
AND column_name = "securityHash" AND data_type = "text"),
"ALTER TABLE users MODIFY COLUMN securityHash LONGTEXT NOT NULL",
"SELECT ""skip: users.securityHash already longtext"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- =====================================================================
-- STEP 4. DROP INDEX: remove duplicate UNIQUE(id)
-- =====================================================================
-- 4.1. configurations
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "configurations"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE configurations DROP INDEX id",
"SELECT ""skip: configurations.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.2. content_blocks
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "content_blocks"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE content_blocks DROP INDEX id",
"SELECT ""skip: content_blocks.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.3. entries
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE entries DROP INDEX id",
"SELECT ""skip: entries.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.4. entries_categories
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries_categories"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE entries_categories DROP INDEX id",
"SELECT ""skip: entries_categories.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.5. entries_comments
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries_comments"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE entries_comments DROP INDEX id",
"SELECT ""skip: entries_comments.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.6. entries_samples
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries_samples"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE entries_samples DROP INDEX id",
"SELECT ""skip: entries_samples.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.7. forms
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "forms"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE forms DROP INDEX id",
"SELECT ""skip: forms.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.8. forms_data
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "forms_data"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE forms_data DROP INDEX id",
"SELECT ""skip: forms_data.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.9. metrics
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "metrics"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE metrics DROP INDEX id",
"SELECT ""skip: metrics.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.10. pages_static
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "pages_static"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE pages_static DROP INDEX id",
"SELECT ""skip: pages_static.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.11. reports
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "reports"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE reports DROP INDEX id",
"SELECT ""skip: reports.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.12. users
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE users DROP INDEX id",
"SELECT ""skip: users.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.13. users_groups
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users_groups"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE users_groups DROP INDEX id",
"SELECT ""skip: users_groups.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.14. users_registration_submits
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users_registration_submits"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE users_registration_submits DROP INDEX id",
"SELECT ""skip: users_registration_submits.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.15. users_sessions
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users_sessions"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE users_sessions DROP INDEX id",
"SELECT ""skip: users_sessions.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4.16. web_channels
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "web_channels"
AND index_name = "id" AND column_name = "id"),
"ALTER TABLE web_channels DROP INDEX id",
"SELECT ""skip: web_channels.id index not exists"""
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- =====================================================================
-- STEP 5. CREATE TABLE IF NOT EXISTS: 6 new tables
-- =====================================================================
-- 5.1. oauth_clients
CREATE TABLE IF NOT EXISTS oauth_clients (
id int NOT NULL AUTO_INCREMENT,
clientID varchar(255) NOT NULL,
clientSecret longtext NOT NULL,
name varchar(255) NOT NULL,
description longtext,
redirectURI longtext NOT NULL,
grantTypes varchar(255) NOT NULL DEFAULT "authorization_code refresh_token",
scopes varchar(255) NOT NULL DEFAULT "profile email read",
userID bigint NOT NULL DEFAULT 0,
isActive tinyint(1) NOT NULL DEFAULT 1,
isVerified tinyint(1) NOT NULL DEFAULT 0,
verifiedAt bigint DEFAULT NULL,
verifiedBy bigint DEFAULT NULL,
ownerEmail varchar(255) NOT NULL DEFAULT "",
maxTokens int NOT NULL DEFAULT 100,
tokenTTL int NOT NULL DEFAULT 3600,
allowedIPs longtext,
createdUnixTimestamp int NOT NULL DEFAULT 0,
updatedUnixTimestamp int NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY idx_oauth_clients_client_id (clientID),
KEY idx_oauth_clients_user_id (userID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 5.2. oauth_auth_codes
CREATE TABLE IF NOT EXISTS oauth_auth_codes (
id int NOT NULL AUTO_INCREMENT,
code varchar(255) NOT NULL,
clientID bigint NOT NULL DEFAULT 0,
userID bigint NOT NULL DEFAULT 0,
scopes varchar(255) NOT NULL DEFAULT "",
redirectURI longtext NOT NULL,
codeChallenge longtext,
codeChallengeMethod varchar(16) NOT NULL DEFAULT "S256",
expiresAt bigint NOT NULL DEFAULT 0,
isRevoked tinyint(1) NOT NULL DEFAULT 0,
createdUnixTimestamp int NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY idx_oauth_auth_codes_code (code),
KEY idx_oauth_auth_codes_client_id (clientID),
KEY idx_oauth_auth_codes_expires_at (expiresAt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 5.3. oauth_access_tokens
CREATE TABLE IF NOT EXISTS oauth_access_tokens (
id int NOT NULL AUTO_INCREMENT,
accessToken varchar(255) NOT NULL,
refreshToken varchar(255) DEFAULT NULL,
clientID bigint NOT NULL DEFAULT 0,
userID bigint NOT NULL DEFAULT 0,
scopes varchar(255) NOT NULL DEFAULT "",
expiresAt bigint NOT NULL DEFAULT 0,
isRevoked tinyint(1) NOT NULL DEFAULT 0,
revokedAt bigint DEFAULT NULL,
createdUnixTimestamp int NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY idx_oauth_access_tokens_access_token (accessToken),
UNIQUE KEY idx_oauth_access_tokens_refresh_token (refreshToken),
KEY idx_oauth_access_tokens_client_id (clientID),
KEY idx_oauth_access_tokens_user_id (userID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 5.4. pages_static_versions
CREATE TABLE IF NOT EXISTS pages_static_versions (
id int NOT NULL AUTO_INCREMENT,
pageStaticID bigint NOT NULL DEFAULT 0,
version varchar(64) NOT NULL,
locale varchar(16) NOT NULL,
texts json NOT NULL,
effectiveFrom int NOT NULL DEFAULT 0,
createdUnixTimestamp int NOT NULL DEFAULT 0,
createdByID bigint NOT NULL DEFAULT 0,
isCurrent tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY idx_pages_static_versions_unique (pageStaticID, version, locale),
KEY idx_pages_static_versions_current (pageStaticID, locale, isCurrent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 5.5. reports_archive
CREATE TABLE IF NOT EXISTS reports_archive (
id bigint NOT NULL,
variables json DEFAULT NULL,
metadata json DEFAULT NULL,
createdUnixTimestamp int NOT NULL DEFAULT 0,
archivedUnixTimestamp int NOT NULL DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 5.6. users_consents
CREATE TABLE IF NOT EXISTS users_consents (
id int NOT NULL AUTO_INCREMENT,
userID bigint NOT NULL DEFAULT 0,
formID bigint NOT NULL DEFAULT 0,
formReportID bigint NOT NULL DEFAULT 0,
pageStaticID bigint NOT NULL DEFAULT 0,
documentVersion varchar(64) NOT NULL,
locale varchar(16) NOT NULL,
ip varchar(64) NOT NULL,
userAgent varchar(512) DEFAULT NULL,
source varchar(64) NOT NULL DEFAULT "form",
consentedAt bigint NOT NULL DEFAULT 0,
revokedAt bigint DEFAULT NULL,
revokeReason longtext,
revokedByID bigint NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY idx_users_consents_user (userID, pageStaticID),
KEY idx_users_consents_recent (userID, pageStaticID, consentedAt),
KEY idx_users_consents_active (userID),
KEY idx_users_consents_document (pageStaticID, documentVersion),
KEY idx_users_consents_form (formID),
KEY idx_users_consents_revoked_by (revokedByID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- =====================================================================
-- STEP 6. CREATE INDEX: ~21 indexes on existing tables
-- =====================================================================
-- 6.1. entries.idx_entries_name_unique
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries"
AND index_name = "idx_entries_name_unique"),
"SELECT ""skip: idx_entries_name_unique exists""",
"CREATE UNIQUE INDEX idx_entries_name_unique ON entries (name)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.2. entries_categories.idx_entries_categories_parent
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries_categories"
AND index_name = "idx_entries_categories_parent"),
"SELECT ""skip: idx_entries_categories_parent exists""",
"CREATE INDEX idx_entries_categories_parent ON entries_categories (parentID)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.3. entries_categories.idx_entries_categories_name_unique
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries_categories"
AND index_name = "idx_entries_categories_name_unique"),
"SELECT ""skip: idx_entries_categories_name_unique exists""",
"CREATE UNIQUE INDEX idx_entries_categories_name_unique ON entries_categories (name)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.4. entries_comments.idx_entries_comments_entry
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries_comments"
AND index_name = "idx_entries_comments_entry"),
"SELECT ""skip: idx_entries_comments_entry exists""",
"CREATE INDEX idx_entries_comments_entry ON entries_comments (entryID)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.5. entries_comments.idx_entries_comments_author
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries_comments"
AND index_name = "idx_entries_comments_author"),
"SELECT ""skip: idx_entries_comments_author exists""",
"CREATE INDEX idx_entries_comments_author ON entries_comments (authorID)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.6. entries_comments.idx_entries_comments_created
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "entries_comments"
AND index_name = "idx_entries_comments_created"),
"SELECT ""skip: idx_entries_comments_created exists""",
"CREATE INDEX idx_entries_comments_created ON entries_comments (createdUnixTimestamp DESC)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.7. forms_data.idx_forms_data_form
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "forms_data"
AND index_name = "idx_forms_data_form"),
"SELECT ""skip: idx_forms_data_form exists""",
"CREATE INDEX idx_forms_data_form ON forms_data (formID)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.8. forms_data.idx_forms_data_created
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "forms_data"
AND index_name = "idx_forms_data_created"),
"SELECT ""skip: idx_forms_data_created exists""",
"CREATE INDEX idx_forms_data_created ON forms_data (createdUnixTimestamp DESC)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.9. metrics.idx_metrics_date
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "metrics"
AND index_name = "idx_metrics_date"),
"SELECT ""skip: idx_metrics_date exists""",
"CREATE INDEX idx_metrics_date ON metrics (date)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.10. pages_static.idx_pages_static_author
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "pages_static"
AND index_name = "idx_pages_static_author"),
"SELECT ""skip: idx_pages_static_author exists""",
"CREATE INDEX idx_pages_static_author ON pages_static (authorID)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.11. pages_static.idx_pages_static_created
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "pages_static"
AND index_name = "idx_pages_static_created"),
"SELECT ""skip: idx_pages_static_created exists""",
"CREATE INDEX idx_pages_static_created ON pages_static (createdUnixTimestamp DESC)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.12. pages_static.idx_pages_static_name_unique
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "pages_static"
AND index_name = "idx_pages_static_name_unique"),
"SELECT ""skip: idx_pages_static_name_unique exists""",
"CREATE UNIQUE INDEX idx_pages_static_name_unique ON pages_static (name)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.13. reports.idx_reports_created
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "reports"
AND index_name = "idx_reports_created"),
"SELECT ""skip: idx_reports_created exists""",
"CREATE INDEX idx_reports_created ON reports (createdUnixTimestamp)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.14. users.idx_users_login_unique
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users"
AND index_name = "idx_users_login_unique"),
"SELECT ""skip: idx_users_login_unique exists""",
"CREATE UNIQUE INDEX idx_users_login_unique ON users (login)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.15. users.idx_users_email_unique
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users"
AND index_name = "idx_users_email_unique"),
"SELECT ""skip: idx_users_email_unique exists""",
"CREATE UNIQUE INDEX idx_users_email_unique ON users (email)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.16. users_sessions.idx_users_sessions_user
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users_sessions"
AND index_name = "idx_users_sessions_user"),
"SELECT ""skip: idx_users_sessions_user exists""",
"CREATE INDEX idx_users_sessions_user ON users_sessions (userID)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.17. users_sessions.idx_users_sessions_token
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users_sessions"
AND index_name = "idx_users_sessions_token"),
"SELECT ""skip: idx_users_sessions_token exists""",
"CREATE INDEX idx_users_sessions_token ON users_sessions (token)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.18. web_channels.idx_web_channels_name_unique
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "web_channels"
AND index_name = "idx_web_channels_name_unique"),
"SELECT ""skip: idx_web_channels_name_unique exists""",
"CREATE UNIQUE INDEX idx_web_channels_name_unique ON web_channels (name)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.19. users_registration_submits.idx_users_registration_submits_user
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users_registration_submits"
AND index_name = "idx_users_registration_submits_user"),
"SELECT ""skip: idx_users_registration_submits_user exists""",
"CREATE INDEX idx_users_registration_submits_user ON users_registration_submits (userID)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.20. users_registration_submits.idx_users_registration_submits_submit_token
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users_registration_submits"
AND index_name = "idx_users_registration_submits_submit_token"),
"SELECT ""skip: idx_users_registration_submits_submit_token exists""",
"CREATE INDEX idx_users_registration_submits_submit_token ON users_registration_submits (submitToken)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6.21. users_registration_submits.idx_users_registration_submits_refusal_token
SET @sql := IF(
EXISTS(SELECT 1 FROM information_schema.statistics
WHERE table_schema = @db AND table_name = "users_registration_submits"
AND index_name = "idx_users_registration_submits_refusal_token"),
"SELECT ""skip: idx_users_registration_submits_refusal_token exists""",
"CREATE INDEX idx_users_registration_submits_refusal_token ON users_registration_submits (refusalToken)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;Post-check
-- =====================================================================
-- POST-CHECK after migration of CMS "GIRVAS" 0.4.0 "Segezha"
-- DBMS: MySQL 8.0+
-- Run AFTER migration (02-migration.sql)
-- =====================================================================
-- The database is determined automatically: DATABASE() is used
-- (the DB to which you are connected in the current session).
-- =====================================================================
SET NAMES utf8mb4;
-- ---------------------------------------------------------------------
-- 0. Diagnostics: where the script is looking
-- ---------------------------------------------------------------------
SELECT
DATABASE() AS current_database,
"OK: checks are running against the current DB" AS status;
-- ---------------------------------------------------------------------
-- 1. Are all tables in place? Empty = OK.
-- ---------------------------------------------------------------------
SELECT "MISSING_TABLE" AS problem, t.name AS detail
FROM (
SELECT "configurations" AS name UNION ALL
SELECT "content_blocks" UNION ALL
SELECT "entries" UNION ALL
SELECT "entries_categories" UNION ALL
SELECT "entries_comments" UNION ALL
SELECT "entries_samples" UNION ALL
SELECT "forms" UNION ALL
SELECT "forms_data" UNION ALL
SELECT "metrics" UNION ALL
SELECT "oauth_access_tokens" UNION ALL
SELECT "oauth_auth_codes" UNION ALL
SELECT "oauth_clients" UNION ALL
SELECT "pages_static" UNION ALL
SELECT "pages_static_versions" UNION ALL
SELECT "reports" UNION ALL
SELECT "reports_archive" UNION ALL
SELECT "users" UNION ALL
SELECT "users_consents" UNION ALL
SELECT "users_groups" UNION ALL
SELECT "users_registration_submits" UNION ALL
SELECT "users_sessions" UNION ALL
SELECT "web_channels"
) AS t
LEFT JOIN information_schema.tables it
ON it.table_schema = DATABASE() AND it.table_name = t.name
WHERE it.table_name IS NULL;
-- ---------------------------------------------------------------------
-- 2. Are there any leftover bigint unsigned on id? Empty = OK.
-- ---------------------------------------------------------------------
SELECT "LEFTOVER_BIGINT_ID" AS problem, table_name, column_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND column_name = "id"
AND column_type = "bigint unsigned"
ORDER BY table_name;
-- ---------------------------------------------------------------------
-- 3. Are there any leftover UNIQUE(id)? Empty = OK.
-- ---------------------------------------------------------------------
SELECT "LEFTOVER_UNIQUE_ID" AS problem, table_name, index_name
FROM information_schema.statistics
WHERE table_schema = DATABASE()
AND index_name = "id"
AND column_name = "id"
ORDER BY table_name;
-- ---------------------------------------------------------------------
-- 4. Are all text → varchar columns converted? Empty = OK.
-- ---------------------------------------------------------------------
SELECT "LEFTOVER_TEXT" AS problem, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND data_type = "text"
AND (
(table_name = "configurations" AND column_name = "name") OR
(table_name = "entries" AND column_name = "name") OR
(table_name = "entries_categories" AND column_name = "name") OR
(table_name = "entries_samples" AND column_name = "name") OR
(table_name = "forms" AND column_name = "name") OR
(table_name = "pages_static" AND column_name = "name") OR
(table_name = "users_groups" AND column_name = "name") OR
(table_name = "web_channels" AND column_name = "name") OR
(table_name = "users" AND column_name IN ("login", "email")) OR
(table_name = "users_registration_submits" AND column_name IN ("submitToken", "refusalToken")) OR
(table_name = "users_sessions" AND column_name IN ("token", "userIP"))
)
ORDER BY table_name, column_name;
-- ---------------------------------------------------------------------
-- 5. Is longtext in place where needed? Empty = OK.
-- ---------------------------------------------------------------------
SELECT "NOT_LONGTEXT" AS problem, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND (
(table_name = "configurations" AND column_name = "value" AND data_type != "longtext") OR
(table_name = "entries_comments" AND column_name = "content" AND data_type != "longtext") OR
(table_name = "users" AND column_name = "passwordHash" AND data_type != "longtext") OR
(table_name = "users" AND column_name = "securityHash" AND data_type != "longtext")
)
ORDER BY table_name, column_name;
-- ---------------------------------------------------------------------
-- 6. Is AUTO_INCREMENT in place?
-- ---------------------------------------------------------------------
SELECT table_name, auto_increment
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_type = "BASE TABLE"
AND auto_increment IS NOT NULL
ORDER BY table_name;
-- ---------------------------------------------------------------------
-- 7. Final list of indexes
-- ---------------------------------------------------------------------
SELECT table_name, index_name, non_unique,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols
FROM information_schema.statistics
WHERE table_schema = DATABASE()
GROUP BY table_name, index_name, non_unique
ORDER BY table_name, index_name;
-- ---------------------------------------------------------------------
-- 8. Final list of tables + engine + collation
-- ---------------------------------------------------------------------
SELECT table_name, engine, table_collation
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_type = "BASE TABLE"
ORDER BY table_name;PostgreSQL Migration
Before running, make sure the data fits into the new types. If your DB contains values longer than 255 characters in the name, login, email columns, the migration will fail. The transaction will roll back entirely, nothing will break, but you will receive an error.
Pre-check
SELECT "configurations.name" AS col, COUNT(*) AS too_long
FROM configurations WHERE length(name) > 255
UNION ALL SELECT "content_blocks.name", COUNT(*) FROM content_blocks WHERE length(name) > 255
UNION ALL SELECT "entries.name", COUNT(*) FROM entries WHERE length(name) > 255
UNION ALL SELECT "entries_categories.name", COUNT(*) FROM entries_categories WHERE length(name) > 255
UNION ALL SELECT "entries_samples.name", COUNT(*) FROM entries_samples WHERE length(name) > 255
UNION ALL SELECT "forms.name", COUNT(*) FROM forms WHERE length(name) > 255
UNION ALL SELECT "pages_static.name", COUNT(*) FROM pages_static WHERE length(name) > 255
UNION ALL SELECT "users.login", COUNT(*) FROM users WHERE length(login) > 255
UNION ALL SELECT "users.email", COUNT(*) FROM users WHERE length(email) > 255
UNION ALL SELECT "users_groups.name", COUNT(*) FROM users_groups WHERE length(name) > 255
UNION ALL SELECT "users_registration_submits.submitToken", COUNT(*) FROM users_registration_submits WHERE length("submitToken") > 255
UNION ALL SELECT "users_registration_submits.refusalToken", COUNT(*) FROM users_registration_submits WHERE length("refusalToken") > 255
UNION ALL SELECT "users_sessions.token", COUNT(*) FROM users_sessions WHERE length(token) > 255
UNION ALL SELECT "users_sessions.userIP", COUNT(*) FROM users_sessions WHERE length("userIP") > 64
UNION ALL SELECT "web_channels.name", COUNT(*) FROM web_channels WHERE length(name) > 255;BEGIN;
-- ---------------------------------------------------------------------
-- STEP 1. ALTER COLUMN TYPE: text → varchar(N)
-- In PG ALTER TYPE rebuilds dependent indexes automatically.
-- ---------------------------------------------------------------------
ALTER TABLE public.configurations
ALTER COLUMN name TYPE varchar(255);
ALTER TABLE public.content_blocks
ALTER COLUMN name TYPE varchar(255);
ALTER TABLE public.entries
ALTER COLUMN name TYPE varchar(255);
ALTER TABLE public.entries_categories
ALTER COLUMN name TYPE varchar(255);
ALTER TABLE public.entries_samples
ALTER COLUMN name TYPE varchar(255);
ALTER TABLE public.forms
ALTER COLUMN name TYPE varchar(255);
ALTER TABLE public.pages_static
ALTER COLUMN name TYPE varchar(255);
ALTER TABLE public.users
ALTER COLUMN login TYPE varchar(255);
ALTER TABLE public.users
ALTER COLUMN email TYPE varchar(255);
ALTER TABLE public.users_groups
ALTER COLUMN name TYPE varchar(255);
ALTER TABLE public.users_registration_submits
ALTER COLUMN "submitToken" TYPE varchar(255);
ALTER TABLE public.users_registration_submits
ALTER COLUMN "refusalToken" TYPE varchar(255);
ALTER TABLE public.users_sessions
ALTER COLUMN token TYPE varchar(255);
ALTER TABLE public.users_sessions
ALTER COLUMN "userIP" TYPE varchar(64);
ALTER TABLE public.web_channels
ALTER COLUMN name TYPE varchar(255);
-- ---------------------------------------------------------------------
-- STEP 2. New indexes for existing tables
-- ---------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_entries_updated
ON public.entries USING btree ("updatedUnixTimestamp");
CREATE INDEX IF NOT EXISTS idx_forms_data_created
ON public.forms_data USING btree ("createdUnixTimestamp" DESC);
CREATE INDEX IF NOT EXISTS idx_forms_data_form
ON public.forms_data USING btree ("formID");
CREATE INDEX IF NOT EXISTS idx_metrics_date
ON public.metrics USING btree (date);
CREATE INDEX IF NOT EXISTS idx_pages_static_author
ON public.pages_static USING btree ("authorID");
CREATE INDEX IF NOT EXISTS idx_pages_static_created
ON public.pages_static USING btree ("createdUnixTimestamp" DESC);
CREATE UNIQUE INDEX IF NOT EXISTS idx_pages_static_name_unique
ON public.pages_static USING btree (name);
CREATE INDEX IF NOT EXISTS idx_pages_static_texts_gin
ON public.pages_static USING gin (texts);
CREATE INDEX IF NOT EXISTS idx_reports_created
ON public.reports USING btree ("createdUnixTimestamp");
CREATE INDEX IF NOT EXISTS idx_users_sessions_token
ON public.users_sessions USING btree (token);
CREATE INDEX IF NOT EXISTS idx_users_sessions_user
ON public.users_sessions USING btree ("userID");
CREATE UNIQUE INDEX IF NOT EXISTS idx_web_channels_name_unique
ON public.web_channels USING btree (name);
-- ---------------------------------------------------------------------
-- STEP 3. New tables
-- ---------------------------------------------------------------------
CREATE SEQUENCE IF NOT EXISTS oauth_clients_id_seq AS integer START 1 INCREMENT 1;
CREATE SEQUENCE IF NOT EXISTS oauth_access_tokens_id_seq AS integer START 1 INCREMENT 1;
CREATE SEQUENCE IF NOT EXISTS oauth_auth_codes_id_seq AS integer START 1 INCREMENT 1;
CREATE SEQUENCE IF NOT EXISTS pages_static_versions_id_seq AS integer START 1 INCREMENT 1;
CREATE SEQUENCE IF NOT EXISTS users_consents_id_seq AS integer START 1 INCREMENT 1;
-- 3.1. OAuth: clients
CREATE TABLE IF NOT EXISTS public.oauth_clients (
id integer NOT NULL DEFAULT nextval("oauth_clients_id_seq"::regclass),
"clientID" varchar(255) NOT NULL,
"clientSecret" text NOT NULL,
name varchar(255) NOT NULL,
description text,
"redirectURI" text NOT NULL,
"grantTypes" varchar(255) NOT NULL DEFAULT "authorization_code refresh_token"::character varying,
scopes varchar(255) NOT NULL DEFAULT "profile email read"::character varying,
"userID" bigint NOT NULL DEFAULT 0,
"isActive" boolean NOT NULL DEFAULT true,
"isVerified" boolean NOT NULL DEFAULT false,
"verifiedAt" bigint DEFAULT 0,
"verifiedBy" bigint DEFAULT 0,
"ownerEmail" varchar(255) NOT NULL DEFAULT ""::character varying,
"maxTokens" integer NOT NULL DEFAULT 100,
"tokenTTL" integer NOT NULL DEFAULT 3600,
"allowedIPs" text,
"createdUnixTimestamp" integer NOT NULL DEFAULT 0,
"updatedUnixTimestamp" integer NOT NULL DEFAULT 0,
CONSTRAINT oauth_clients_pkey PRIMARY KEY (id)
);
-- 3.2. OAuth: access tokens
CREATE TABLE IF NOT EXISTS public.oauth_access_tokens (
id integer NOT NULL DEFAULT nextval("oauth_access_tokens_id_seq"::regclass),
"accessToken" varchar(255) NOT NULL,
"refreshToken" varchar(255),
"clientID" bigint NOT NULL DEFAULT 0,
"userID" bigint NOT NULL DEFAULT 0,
scopes varchar(255) NOT NULL DEFAULT ""::character varying,
"expiresAt" bigint NOT NULL DEFAULT 0,
"isRevoked" boolean NOT NULL DEFAULT false,
"revokedAt" bigint DEFAULT 0,
"createdUnixTimestamp" integer NOT NULL DEFAULT 0,
CONSTRAINT oauth_access_tokens_pkey PRIMARY KEY (id)
);
-- 3.3. OAuth: authorization codes
CREATE TABLE IF NOT EXISTS public.oauth_auth_codes (
id integer NOT NULL DEFAULT nextval("oauth_auth_codes_id_seq"::regclass),
code varchar(255) NOT NULL,
"clientID" bigint NOT NULL DEFAULT 0,
"userID" bigint NOT NULL DEFAULT 0,
scopes varchar(255) NOT NULL DEFAULT ""::character varying,
"redirectURI" text NOT NULL,
"codeChallenge" text,
"codeChallengeMethod" varchar(16) NOT NULL DEFAULT "S256"::character varying,
"expiresAt" bigint NOT NULL DEFAULT 0,
"isRevoked" boolean NOT NULL DEFAULT false,
"createdUnixTimestamp" integer NOT NULL DEFAULT 0,
CONSTRAINT oauth_auth_codes_pkey PRIMARY KEY (id)
);
-- 3.4. Static page versions (152-FZ)
CREATE TABLE IF NOT EXISTS public.pages_static_versions (
id integer NOT NULL DEFAULT nextval("pages_static_versions_id_seq"::regclass),
"pageStaticID" bigint NOT NULL DEFAULT 0,
version varchar(64) NOT NULL,
locale varchar(16) NOT NULL,
texts jsonb NOT NULL,
"effectiveFrom" integer NOT NULL DEFAULT 0,
"createdUnixTimestamp" integer NOT NULL DEFAULT 0,
"createdByID" bigint NOT NULL DEFAULT 0,
"isCurrent" boolean NOT NULL DEFAULT false,
CONSTRAINT pages_static_versions_pkey PRIMARY KEY (id)
);
-- 3.5. Reports archive
CREATE TABLE IF NOT EXISTS public.reports_archive (
id bigint NOT NULL,
variables jsonb,
metadata jsonb,
"createdUnixTimestamp" integer NOT NULL DEFAULT 0,
"archivedUnixTimestamp" integer NOT NULL DEFAULT 0,
CONSTRAINT reports_archive_pkey PRIMARY KEY (id)
);
-- reports_archive has NO sequence — id is copied from reports.
-- 3.6. User consents (152-FZ)
CREATE TABLE IF NOT EXISTS public.users_consents (
id integer NOT NULL DEFAULT nextval("users_consents_id_seq"::regclass),
"userID" bigint NOT NULL DEFAULT 0,
"formID" bigint NOT NULL DEFAULT 0,
"formReportID" bigint NOT NULL DEFAULT 0,
"pageStaticID" bigint NOT NULL DEFAULT 0,
"documentVersion" varchar(64) NOT NULL,
locale varchar(16) NOT NULL,
ip varchar(64) NOT NULL,
"userAgent" varchar(512),
source varchar(64) NOT NULL DEFAULT "form"::character varying,
"consentedAt" bigint NOT NULL DEFAULT 0,
"revokedAt" bigint DEFAULT 0,
"revokeReason" text,
"revokedByID" bigint NOT NULL DEFAULT 0,
CONSTRAINT users_consents_pkey PRIMARY KEY (id)
);
-- ---------------------------------------------------------------------
-- STEP 4. Indexes of new tables
-- ---------------------------------------------------------------------
-- oauth_clients
CREATE UNIQUE INDEX IF NOT EXISTS idx_oauth_clients_client_id
ON public.oauth_clients USING btree ("clientID");
CREATE INDEX IF NOT EXISTS idx_oauth_clients_user_id
ON public.oauth_clients USING btree ("userID");
-- oauth_access_tokens
CREATE UNIQUE INDEX IF NOT EXISTS idx_oauth_access_tokens_access_token
ON public.oauth_access_tokens USING btree ("accessToken");
CREATE UNIQUE INDEX IF NOT EXISTS idx_oauth_access_tokens_refresh_token
ON public.oauth_access_tokens USING btree ("refreshToken");
CREATE INDEX IF NOT EXISTS idx_oauth_access_tokens_client_id
ON public.oauth_access_tokens USING btree ("clientID");
CREATE INDEX IF NOT EXISTS idx_oauth_access_tokens_user_id
ON public.oauth_access_tokens USING btree ("userID");
-- oauth_auth_codes
CREATE UNIQUE INDEX IF NOT EXISTS idx_oauth_auth_codes_code
ON public.oauth_auth_codes USING btree (code);
CREATE INDEX IF NOT EXISTS idx_oauth_auth_codes_client_id
ON public.oauth_auth_codes USING btree ("clientID");
CREATE INDEX IF NOT EXISTS idx_oauth_auth_codes_expires_at
ON public.oauth_auth_codes USING btree ("expiresAt");
-- pages_static_versions
CREATE UNIQUE INDEX IF NOT EXISTS idx_pages_static_versions_unique
ON public.pages_static_versions USING btree ("pageStaticID", version, locale);
CREATE INDEX IF NOT EXISTS idx_pages_static_versions_current
ON public.pages_static_versions USING btree ("pageStaticID", locale, "isCurrent");
CREATE INDEX IF NOT EXISTS idx_pages_static_versions_texts_gin
ON public.pages_static_versions USING gin (texts);
-- users_consents
CREATE INDEX IF NOT EXISTS idx_users_consents_user
ON public.users_consents USING btree ("userID", "pageStaticID");
CREATE INDEX IF NOT EXISTS idx_users_consents_document
ON public.users_consents USING btree ("pageStaticID", "documentVersion");
CREATE INDEX IF NOT EXISTS idx_users_consents_form
ON public.users_consents USING btree ("formID");
CREATE INDEX IF NOT EXISTS idx_users_consents_recent
ON public.users_consents USING btree ("userID", "pageStaticID", "consentedAt");
CREATE INDEX IF NOT EXISTS idx_users_consents_active
ON public.users_consents USING btree ("userID") WHERE ("revokedAt" IS NULL);
CREATE INDEX IF NOT EXISTS idx_users_consents_revoked_by
ON public.users_consents USING btree ("revokedByID") WHERE ("revokedByID" > 0);
COMMIT;Alternative: Reinstalling the System
If your site does not contain important data — or you are ready to lose it — you can reinstall the CMS instead of migrating. This guarantees a correct database schema without the risks associated with migration.
When this is appropriate:
- the site is at the development or testing stage;
- the content can be easily restored or recreated;
- you want to start with a "clean slate" with the new CMS version.
When it is NOT suitable:
- a production site with content, users, forms, reports;
- if there is data that cannot be restored;
- if there is no way to export content before reinstalling.
Reinstallation procedure:
- Export the content you want to keep: entries, pages, users, forms. This can be done through the administrative panel, or manually — via SQL.
- Make a backup of the entire database — in case you change your mind.
- Delete the CMS files from the root directory.
- Delete the database entirely (or create a new empty one with the same name).
- Unpack the new CMS version.
- Go through the installation again: fill in the data about the domain, DB, create an administrator.
- Import the previously exported content.
- Clear the cache:
rm -R ./cache/*.
Warning: reinstallation leads to complete data loss if it is not exported in advance. Do not use this path for "production" sites without first exporting the content.
Conclusion
Update 0.4.0 "Segezha" for the "GIRVAS" CMS is the largest in the entire history of the project. It combines several strategic directions: full technical coverage of 152-FZ requirements at the core level, laying the foundation for OAuth provider functionality, introducing a SQL dialect layer with support for MySQL and PostgreSQL, multilingual site settings, and a number of improvements to the administrative panel.
We recommend that all users perform the update in order to evaluate the increased capabilities of the system. Separately, it is worth noting the directions that determine the development of the product for the future: compliance with personal data legislation and support for multiple DBMSs. These are not one-time improvements, but systematic work to ensure that "GIRVAS" remains a modern, secure, and sought-after tool for sites of any scale — from a small blog to a corporate portal.
Comments