# Generic SQL statements

create-file-table=CREATE TABLE %s (ID BIGINT AUTO_INCREMENT, FILENAME VARCHAR(255), EXTENSION VARCHAR(10), DATA BLOB, PRIMARY KEY(ID))
insert-file=INSERT INTO %s (FILENAME, EXTENSION, DATA) VALUES (?,?,?)
select-filenames-by-extension=SELECT FILENAME, ID FROM %s WHERE EXTENSION=?
select-file-by-filename=SELECT ID, FILENAME, EXTENSION, DATA FROM %s WHERE fileName=?
append-to-file=SELECT DATA FROM %s WHERE ID=? FOR UPDATE
read-large-object=SELECT DATA FROM %s WHERE ID=?
delete-file=DELETE FROM %s WHERE ID=?
update-filename-by-id=UPDATE %s SET FILENAME=? WHERE ID=?
clone-file-record=INSERT INTO %s (FILENAME, EXTENSION, DATA) (SELECT FILENAME, EXTENSION, DATA FROM %s WHERE ID=?)
copy-file-record-by-id=UPDATE %1$s SET DATA = (SELECT DATA FROM %1$s WHERE ID=?) WHERE ID=?
drop-table=DROP TABLE %s
create-journal-table=CREATE TABLE %s(id BIGINT,recordType SMALLINT,compactCount SMALLINT,txId BIGINT,userRecordType SMALLINT,variableSize INTEGER,record BLOB,txDataSize INTEGER,txData BLOB,txCheckNoRecords INTEGER,seq BIGINT NOT NULL, PRIMARY KEY(seq))
create-journal-index=CREATE INDEX %1$s_IDX ON %1$s (id)
insert-journal-record=INSERT INTO %s(id,recordType,compactCount,txId,userRecordType,variableSize,record,txDataSize,txData,txCheckNoRecords,seq) VALUES (?,?,?,?,?,?,?,?,?,?,?)
select-journal-record=SELECT id,recordType,compactCount,txId,userRecordType,variableSize,record,txDataSize,txData,txCheckNoRecords,seq FROM %s ORDER BY seq ASC
delete-journal-record=DELETE FROM %s WHERE id = ?
delete-journal-tx-record=DELETE FROM %s WHERE txId=?
count-journal-record=SELECT COUNT(*) FROM %s

create-node-manager-store-table=CREATE TABLE %s (ID INT NOT NULL, HOLDER_ID VARCHAR(128), HOLDER_EXPIRATION_TIME TIMESTAMP, NODE_ID CHAR(36),STATE CHAR(1), PRIMARY KEY(ID))
create-state=INSERT INTO %s (ID) VALUES (%s)
try-acquire-lock=UPDATE %s SET HOLDER_ID = ?, HOLDER_EXPIRATION_TIME = ? WHERE (HOLDER_EXPIRATION_TIME IS NULL OR HOLDER_EXPIRATION_TIME < CURRENT_TIMESTAMP) AND ID = %s
try-release-lock=UPDATE %s SET HOLDER_ID = NULL, HOLDER_EXPIRATION_TIME = NULL WHERE HOLDER_ID = ? AND ID = %s
is-locked=SELECT HOLDER_ID, HOLDER_EXPIRATION_TIME FROM %s WHERE ID = %s
renew-lock=UPDATE %s SET HOLDER_EXPIRATION_TIME = ? WHERE HOLDER_ID = ? AND ID = %s
current-timestamp=SELECT CURRENT_TIMESTAMP FROM %s
write-state=UPDATE %s SET STATE = ? WHERE ID = %s
read-state=SELECT STATE FROM %s WHERE ID = %s
write-nodeId=UPDATE %s SET NODE_ID = ? WHERE ID = %s
initialize-nodeId=UPDATE %s SET NODE_ID = ? WHERE NODE_ID IS NULL AND ID = %s
read-nodeId=SELECT NODE_ID FROM %s WHERE ID = %s

# in KiB
max-blob-size=64512
close-connection-on-shutdown=true

# Derby SQL statements
close-connection-on-shutdown.derby=false
create-file-table.derby=CREATE TABLE %s (ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), FILENAME VARCHAR(255), EXTENSION VARCHAR(10), DATA BLOB, PRIMARY KEY(ID))
max-blob-size.derby=2147483647

# PostgreSQL SQL statements
create-file-table.postgresql=CREATE TABLE %s (ID BIGSERIAL, FILENAME VARCHAR(255), EXTENSION VARCHAR(10), DATA OID, PRIMARY KEY(ID))
create-journal-table.postgresql=CREATE TABLE %s(id BIGINT,recordType SMALLINT,compactCount SMALLINT,txId BIGINT,userRecordType SMALLINT,variableSize INTEGER,record BYTEA,txDataSize INTEGER,txData BYTEA,txCheckNoRecords INTEGER,seq BIGINT)
max-blob-size.postgresql=1073741824

# MySQL SQL statements
create-file-table.mysql=CREATE TABLE %s(ID BIGINT NOT NULL AUTO_INCREMENT, FILENAME VARCHAR(255), EXTENSION VARCHAR(10), DATA LONGBLOB, PRIMARY KEY(ID)) ENGINE=InnoDB
create-journal-table.mysql=CREATE TABLE %s(id BIGINT,recordType SMALLINT,compactCount SMALLINT,txId BIGINT,userRecordType SMALLINT,variableSize INTEGER,record LONGBLOB,txDataSize INTEGER,txData LONGBLOB,txCheckNoRecords INTEGER,seq BIGINT) ENGINE=InnoDB
copy-file-record-by-id.mysql=UPDATE %1$s, (SELECT DATA AS FROM_DATA FROM %1$s WHERE id=?) SELECT_COPY SET DATA=FROM_DATA WHERE id=?
max-blob-size.mysql=4294967296

# Oracle SQL statements
create-file-table.oracle=CREATE TABLE %s(ID NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY, FILENAME VARCHAR(255), EXTENSION VARCHAR(10), DATA BLOB, PRIMARY KEY(ID))
create-journal-table.oracle=CREATE TABLE %s(id NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY,recordType NUMBER(5),compactCount NUMBER(5),txId NUMBER(19),userRecordType NUMBER(5),variableSize NUMBER(10),record BLOB,txDataSize NUMBER(10),txData BLOB,txCheckNoRecords NUMBER(10),seq NUMBER(19))
max-blob-size.oracle=4294967296
