Setup with postgres DBMS

[Shushandr]

I think that SQL script doesn't conform to SQL standards:

# PGPASSWORD="password" psql -U user -d beyondpaste <./config/structure.sql
ERROR:  syntax error at or near "("
LINE 2:     id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
                  ^
ERROR:  syntax error at or near "("
LINE 2:     id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,

[Shushandr]

DROP TABLE IF EXISTS paste;
DROP SEQUENCE IF EXISTS paste_id_seq;
DROP TABLE IF EXISTS access;
DROP SEQUENCE IF EXISTS access_id_seq;

CREATE SEQUENCE access_id_seq;

CREATE TABLE access (
 id INTEGER NOT NULL DEFAULT nextval('access_id_seq'),
 type SMALLINT NOT NULL DEFAULT 0,
 parameter VARCHAR(120),
 PRIMARY KEY(id)
);

CREATE SEQUENCE paste_id_seq;

CREATE TABLE paste ( 
 id INTEGER NOT NULL DEFAULT nextval('paste_id_seq'),
 owner_ip INTEGER,
 title VARCHAR(50),
 creation_epoch INTEGER,
 expiration_epoch INTEGER,
 autodestroy SMALLINT NOT NULL DEFAULT 0,
 syntax_highlighting VARCHAR(32),
 content TEXT NOT NULL,
 access_id INTEGER,
 views INTEGER NOT NULL DEFAULT 0,
 deleted SMALLINT NOT NULL DEFAULT 0,
 PRIMARY KEY(id)
);

It's impossible to have one script for both MySQL and PostgreSQL (without additional macro tool)

[RaJiska]

Yes, it's not about being standard, it's that all the SQL code was designed to work with MariaDB. You can sort the code from the structure file, but you might or might not have issues with the requests issued by the application itself.

[Shushandr]

php have PDO (PHP Data Objects) technology to have the same application code for different DBMS.

[RaJiska]

Indeed, PDO is what I used for this project. You should however note that PDO only offers an abstraction over the database it connects to, and not over the queries that are passed to this database. Since queries are "hand-crafted", some of them may or may not work on other database engines than MariaDB for which they were initially designed for.