SQL tables not created because of usage of reserved keywords in column names #77
Description
Hi,
Trying to re-enable the Complianz GDPR plugin, I discovered it can happen some plugins are using PostgreSQL keywords as column names, thus creating an error.
For example, I've the case when the SQL table below is trying to be created:
2023-11-26 11:19:24.456 CET [pid=2340706]: [sid=65631bab.23b762/tid=0] client=,user=,db=,app=[inconnu] ERREUR: erreur de syntaxe sur ou près de « default » au caractère 178
2023-11-26 11:19:24.456 CET [pid=2340706]: [sid=65631bab.23b762/tid=0] client=,user=,db=,app=[inconnu] INSTRUCTION : CREATE TABLE wp_cmplz_cookiebanners (
"ID" int NOT NULL DEFAULT nextval('wp_cmplz_cookiebanners_seq'::text),
banner_version int NOT NULL,
default int NOT NULL,
archived int NOT NULL,
title text NOT NULL,
position text NOT NULL,
theme text NOT NULL,
checkbox_style text NOT NULL,
use_logo text NOT NULL,
logo_attachment_id text NOT NULL,
close_button text NOT NULL,
revoke text NOT NULL,
manage_consent_options text NOT NULL,
header text NOT NULL,
dismiss text NOT NULL,
save_preferences text NOT NULL,
view_preferences text NOT NULL,
category_functional text NOT NULL,
category_all text NOT NULL,
category_stats text NOT NULL,
category_prefs text NOT NULL,
accept text NOT NULL,
message_optin text NOT NULL,
use_categories text NOT NULL,
disable_cookiebanner int NOT NULL,
banner_width int NOT NULL,
soft_cookiewall int NOT NULL,
dismiss_on_scroll int NOT NULL,
dismiss_on_timeout int NOT NULL,
dismiss_timeout text NOT NULL,
accept_informational text NOT NULL,
message_optout text NOT NULL,
use_custom_cookie_css text NOT NULL,
custom_css text NOT NULL,
statistics text NOT NULL,
functional_text text NOT NULL,
statistics_text text NOT NULL,
statistics_text_anonymous text NOT NULL,
preferences_text text NOT NULL,
marketing_text text NOT NULL,
colorpalette_background text NOT NULL,
colorpalette_text text NOT NULL,
colorpalette_toggles text NOT NULL,
colorpalette_border_radius text NOT NULL,
border_width text NOT NULL,
font_size text NOT NULL,
colorpalette_button_accept text NOT NULL,
colorpalette_button_deny text NOT NULL,
colorpalette_button_settings text NOT NULL,
buttons_border_radius text NOT NULL,
animation text NOT NULL,
use_box_shadow int NOT NULL,
header_footer_shadow int NOT NULL,
hide_preview int NOT NULL,
disable_width_correction int NOT NULL,
legal_documents int NOT NULL,
PRIMARY KEY ( "ID" )
) ;
CREATE SEQUENCE wp_cmplz_cookiebanners_seq;
As you can see above, the third column has default for column name.
However, default is a reserved keyword that is used to automatically assign a default value to a column when data are inserted.
For such cases, the de facto way to use to avoid a PostgreSQL database error is to put the column name inside double quotes, so doing this:
CREATE TABLE wp_cmplz_cookiebanners (
"ID" int NOT NULL DEFAULT nextval('wp_cmplz_cookiebanners_seq'::text),
banner_version int NOT NULL,
"default" int NOT NULL,
instead of this:
CREATE TABLE wp_cmplz_cookiebanners (
"ID" int NOT NULL DEFAULT nextval('wp_cmplz_cookiebanners_seq'::text),
banner_version int NOT NULL,
default int NOT NULL,