0.2.0 Voitsy

Recent changes

• Bringing names of variables, methods, and object properties in line with PSR-1 rules;
• Bringing code in line with PSR-12 rules;
• Adding the IF EXISTS condition to the ALTER TABLE clause condition for the SQL query collector;
• Adding case sensitivity to selectable columns in the SELECT clause for the SQL query collector;
• Added a check for the presence of a group by the user with the same technical name as the one being edited;
• Adding an argument with the type of database management system for initializing the database query collector;
• Implemented a method in the system kernel class for correct checking of the HTTPS connection, including checking for cloud environments and proxies;
• Implemented a method in the system kernel class for automatic file connection (previously, the connection was not moved to a separate method);
• Implemented a common interface for localization classes of the system kernel, modules, and system themes;
• Implementation of MySQL DBMS support;
• Adding an alias for the aggregate SQL function count() when determining the number of records in database tables for adaptation to various DBMS;
• Implementation of the method for obtaining the previous URL (Referrer) for the address bar parser class;
• Fixed a bug due to which the feed collector could not perform assembly due to the presence of HTML entities in the content description;
• Replacing the system logo in the primary site template with a new one;
• Updated localizations: ru_RU, en_US;
• Refactoring the system code, primary site templates and the admin panel;
• Minor stylistic corrections to the primary site template;
• Other minor fixes;

Adaptation of the old database

The following SQL queries will help to correctly adapt the PostgreSQL database generated by the GIRVAS content management system before the 0.2.0 Voitsy update.

Renaming columns

ALTER TABLE configurations RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE configurations RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

ALTER TABLE entries RENAME COLUMN author_id to "authorID";

ALTER TABLE entries RENAME COLUMN category_id to "categoryID";

ALTER TABLE entries RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE entries RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

ALTER TABLE entries_categories RENAME COLUMN parent_id to "parentID";

ALTER TABLE entries_categories RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE entries_categories RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

ALTER TABLE entries_comments RENAME COLUMN author_id to "authorID";

ALTER TABLE entries_comments RENAME COLUMN entry_id to "entryID";

ALTER TABLE entries_comments RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE entries_comments RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

ALTER TABLE entries_samples RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE entries_samples RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

ALTER TABLE metrics RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE metrics RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

ALTER TABLE pages_static RENAME COLUMN author_id to "authorID";

ALTER TABLE pages_static RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE pages_static RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

ALTER TABLE reports RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE users RENAME COLUMN password_hash to "passwordHash";

ALTER TABLE users RENAME COLUMN security_hash to "securityHash";

ALTER TABLE users RENAME COLUMN email_is_submitted to "emailIsSubmitted";

ALTER TABLE users RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE users RENAME COLUMN updated_unix_timestamp to updatedUnixTimestamp;

ALTER TABLE users_groups RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE users_groups RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

ALTER TABLE users_registration_submits RENAME COLUMN user_id to "userID";

ALTER TABLE users_registration_submits RENAME COLUMN submit_token to "submitToken";

ALTER TABLE users_registration_submits RENAME COLUMN refusal_token to "refusalToken";

ALTER TABLE users_registration_submits RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE users_sessions RENAME COLUMN user_id to "userID";

ALTER TABLE users_sessions RENAME COLUMN user_ip to "userIP";

ALTER TABLE users_sessions RENAME COLUMN type_id to "typeID";

ALTER TABLE users_sessions RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE users_sessions RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

ALTER TABLE web_channels RENAME COLUMN entries_category_id to "entriesCategoryID";

ALTER TABLE web_channels RENAME COLUMN type_id to "typeID";

ALTER TABLE web_channels RENAME COLUMN created_unix_timestamp to "createdUnixTimestamp";

ALTER TABLE web_channels RENAME COLUMN updated_unix_timestamp to "updatedUnixTimestamp";

Renaming JSON object properties in metadata

UPDATE users SET metadata = jsonb_set( metadata, "{groupID}", metadata->"group_id" ) || jsonb_build_object("metadata", metadata - "group_id");

UPDATE entries SET metadata = jsonb_set( metadata, "{isPublish}", metadata->"is_publish" ) || jsonb_build_object("metadata", metadata - "is_publish");

UPDATE entries SET metadata = jsonb_set( metadata, "{previewURL}", metadata->"preview_url" ) || jsonb_build_object("metadata", metadata - "preview_url");

UPDATE pages_static SET metadata = jsonb_set( metadata, "{isPublish}", metadata->"is_publish" ) || jsonb_build_object("metadata", metadata - "is_publish");

UPDATE pages_static SET metadata = jsonb_set( metadata, "{previewURL}", metadata->"preview_url" ) || jsonb_build_object("metadata", metadata - "preview_url");

UPDATE metrics SET "data" = jsonb_set( "data", "{metrics}", jsonb_set( "data"->"metrics", "{views}", ( SELECT jsonb_object_agg( view.key, jsonb_set( view.value, "{URLTransfers}", ( SELECT jsonb_agg( jsonb_set( transfer_elem, array[transfer_key], jsonb_set( transfer_elem->transfer_key, "{isVisitedNew}", (transfer_elem->transfer_key)->"is_visited_new" ) - "is_visited_new" ) ) FROM jsonb_array_elements(view.value->"url_transfers") AS transfer_elem, jsonb_object_keys(transfer_elem) AS transfer_key ) ) - "url_transfers" ) FROM jsonb_each("data"->"metrics"->"views") AS view ) ) ) WHERE "data" @? "$.metrics.views..url_transfers";