[Feature Request] Remove backticks from SQL queries to support PostgreSQL

[PhocaCz]

Remove backticks from SQL queries to support PostgreSQL: https://www.phoca.cz/forum/viewtopic.php?p=185444#p185444

[WasteNotWantNot]

In admin/models/phocamapsmaps.php - line 100 & 104
In admin/models/phocamapsicons.php - line 95 & 99
In admin/models/phocamapsmarkers.php - line 108 & 112

In addition SQL needs to change as follows to support PostgreSQL. The constraints need to be removed as trying to insert a 0 value into the database causes the query to fail as it breaches the constraints of must be bigger than 0.

I hope all of this information helps

CREATE TABLE IF NOT EXISTS #__phocamaps_map (
id int NOT NULL generated always as identity,
title varchar(255) NOT NULL default '',
alias varchar(255) NOT NULL default '',
width varchar(50) NOT NULL default '',
height varchar(50) NOT NULL default '',
latitude varchar(20) NOT NULL default '',
longitude varchar(20) NOT NULL default '',
zoom int NOT NULL default '0',
lang varchar(6) NOT NULL default '',
border smallint NOT NULL default '0',
continuouszoom smallint NOT NULL default '0',
doubleclickzoom smallint NOT NULL default '0',
scrollwheelzoom smallint NOT NULL default '0',
gesturehandling varchar(100) NOT NULL default '',
zoomcontrol smallint NOT NULL default '0',
scalecontrol smallint NOT NULL default '0',
typeid smallint NOT NULL default '0',
typecontrol smallint NOT NULL default '0',
typecontrolposition smallint NOT NULL default '0',
collapsibleoverview smallint NOT NULL default '0',
dynamiclabel smallint NOT NULL default '0',
googlebar smallint NOT NULL default '0',
displayroute smallint NOT NULL default '0',
kmlfile varchar(255) NOT NULL default '',
custom_options varchar(255) NOT NULL default '',
map_styles text,
trackfiles_osm varchar(255) NOT NULL default '',
trackcolors_osm varchar(255) NOT NULL default '',
fitbounds_osm smallint NOT NULL default '0',
autolocation smallint NOT NULL default '0',
description text,
published smallint NOT NULL default '0',
checked_out int NOT NULL default '0',
checked_out_time timestamp,
ordering int NOT NULL default '0',
access smallint NOT NULL default '0',
hits int NOT NULL default '0',
params text,
language char(7) NOT NULL default '',
PRIMARY KEY (id)
);

CREATE INDEX cat_idx ON #__phocamaps_map (published,access);
CREATE INDEX idx_access ON #__phocamaps_map (access);
CREATE INDEX idx_checkout ON #__phocamaps_map (checked_out);

CREATE TABLE IF NOT EXISTS #__phocamaps_marker (
id int check (id > 0) NOT NULL generated always as identity,
catid int NOT NULL default '0',
title varchar(250) NOT NULL default '',
alias varchar(255) NOT NULL default '',
latitude varchar(20) NOT NULL default '',
longitude varchar(20) NOT NULL default '',
gpslatitude varchar(50) NOT NULL default '',
gpslongitude varchar(50) NOT NULL default '',
displaygps smallint NOT NULL default '0',
icon smallint NOT NULL default '0',
iconext int NOT NULL default '0',
description text,

osm_icon varchar(100) NOT NULL default '',
osm_marker_color varchar(20) NOT NULL default '',
osm_icon_color varchar(20) NOT NULL default '',
osm_icon_prefix varchar(20) NOT NULL default '',
osm_icon_spin smallint NOT NULL default '0',
osm_icon_class text,

contentwidth varchar(8) NOT NULL default '',
contentheight varchar(8) NOT NULL default '',
markerwindow smallint NOT NULL default '0',
published smallint NOT NULL default '0',
checked_out int NOT NULL default '0',
checked_out_time timestamp,
ordering int NOT NULL default '0',
access smallint NOT NULL default '0',
hits int NOT NULL default '0',
params text,
language char(7) NOT NULL default '',
PRIMARY KEY (id)
);

CREATE INDEX catid ON #__phocamaps_marker (catid,published);

CREATE TABLE IF NOT EXISTS #__phocamaps_icon (
id int check (id > 0) NOT NULL generated always as identity,
title varchar(250) NOT NULL default '',
alias varchar(255) NOT NULL default '',
url text,
urls text,
object varchar(255) NOT NULL default '',
objects varchar(255) NOT NULL default '',
objectshape varchar(255) NOT NULL default '',
published smallint NOT NULL default '0',
checked_out int check NOT NULL default '0',
checked_out_time timestamp,
ordering int NOT NULL default '0',
access smallint NOT NULL default '0',
hits int NOT NULL default '0',
params text,
language char(7) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO #__phocamaps_icon (id, title, alias, url, urls, object, objects, objectshape, published, checked_out, checked_out_time, ordering, access, hits, params, language) VALUES
(1, 'Tree', 'tree', 'https://maps.google.com/mapfiles/ms/icons/tree.png', 'https://maps.google.com/mapfiles/ms/icons/tree.shadow.png', '', '59,32;0,0;16,34', 'rect;0,0,25,30', 1, 0, '0000-00-00 00:00:00', 1, 1, 0, '', ''),
(2, 'Pushpin', 'pushpin', 'https://maps.google.com/mapfiles/ms/icons/red-pushpin.png', '', '', '', 'rect;0,0,25,30', 1, 0, '0000-00-00 00:00:00', 2, 1, 0, '', ''),
(3, 'Blue Dot', 'blue-dot', 'https://maps.google.com/mapfiles/ms/icons/blue-dot.png', '', '', '', 'rect;0,0,25,30', 1, 0, '0000-00-00 00:00:00', 3, 1, 0, '', ''),
(4, 'Flag', 'flag', 'https://maps.google.com/mapfiles/ms/icons/flag.png', 'https://maps.google.com/mapfiles/ms/icons/flag.shadow.png', '', '59,32;0,0;16,34', 'rect;0,0,25,30', 1, 0, '0000-00-00 00:00:00', 3, 1, 0, '', ''),
(5, 'Info', 'info', 'https://maps.google.com/mapfiles/ms/icons/info.png', 'https://maps.google.com/mapfiles/ms/icons/info.shadow.png', '', '59,32;0,0;16,34', 'rect;0,0,25,30', 1, 0, '0000-00-00 00:00:00', 5, 1, 0, '', ''),
(6, 'Snack Bar', 'snack-bar', 'https://maps.google.com/mapfiles/ms/icons/snack_bar.png', 'https://maps.google.com/mapfiles/ms/icons/snack_bar.shadow.png', '', '59,32;0,0;16,34', 'rect;0,0,32,30', 1, 0, '0000-00-00 00:00:00', 6, 1, 0, '', '');