connect 'jdbc:derby:/tmp/db4;create=true';

CREATE SCHEMA FOO;
CREATE SCHEMA BAR;

DROP TABLE USER_SCHEMA;
CREATE TABLE USER_SCHEMA(S VARCHAR(128) WITH DEFAULT CURRENT SCHEMA, U VARCHAR(8) WITH DEFAULT USER, X VARCHAR(8));
PREPARE US_IMPLICIT AS 'INSERT INTO USER_SCHEMA(X) VALUES(''implicit'')';
execute US_IMPLICIT;

SET SCHEMA FOO;
execute US_IMPLICIT;

SET SCHEMA BAR;
execute US_IMPLICIT;

SET SCHEMA APP;
PREPARE US_EXPLICIT AS 'INSERT INTO USER_SCHEMA(S, U, X) VALUES(default, default, ''explicit'')';
execute US_EXPLICIT;

SET SCHEMA FOO;
execute US_EXPLICIT;

SET SCHEMA BAR;
execute US_EXPLICIT;

SET SCHEMA APP;
SELECT * FROM USER_SCHEMA;

-- test current timestamp as default
DROP TABLE TIME_STAMP;
CREATE TABLE TIME_STAMP(TS TIMESTAMP WITH DEFAULT CURRENT_TIMESTAMP, X VARCHAR(8));
PREPARE TS_IMPLICIT AS 'INSERT INTO TIME_STAMP(X) VALUES(''implicit'')';

execute TS_IMPLICIT;
execute TS_IMPLICIT;
execute TS_IMPLICIT;

PREPARE TS_EXPLICIT AS 'INSERT INTO TIME_STAMP(TS, X) VALUES(default, ''explicit'')';
execute TS_EXPLICIT;
execute TS_EXPLICIT;
execute TS_EXPLICIT;
SELECT * FROM TIME_STAMP;


-- test int generated by default columns
DROP TABLE INT_GENERATED_BY_DEFAULT;
CREATE TABLE INT_GENERATED_BY_DEFAULT(I INT GENERATED BY DEFAULT AS IDENTITY (START WITH 5, INCREMENT BY 10), X VARCHAR(8));
PREPARE INT_IMPLICIT AS 'INSERT INTO INT_GENERATED_BY_DEFAULT(X) VALUES(''implicit'')';
execute INT_IMPLICIT;
execute INT_IMPLICIT;
execute INT_IMPLICIT;

PREPARE INT_EXPLICIT AS 'INSERT INTO INT_GENERATED_BY_DEFAULT(I,X) VALUES(DEFAULT, ''explicit'')';
execute INT_EXPLICIT;
execute INT_EXPLICIT;
execute INT_EXPLICIT;
SELECT * FROM INT_GENERATED_BY_DEFAULT;

-- test smallint generated by default columns
DROP TABLE SMALLINT_GENERATED_BY_DEFAULT;
CREATE TABLE SMALLINT_GENERATED_BY_DEFAULT(S SMALLINT GENERATED BY DEFAULT AS IDENTITY (START WITH 5, INCREMENT BY 10), X VARCHAR(8));
PREPARE SMALLINT_IMPLICIT AS 'INSERT INTO SMALLINT_GENERATED_BY_DEFAULT(X) VALUES(''implicit'')';
execute SMALLINT_IMPLICIT;
execute SMALLINT_IMPLICIT;
execute SMALLINT_IMPLICIT;

PREPARE SMALLINT_EXPLICIT AS 'INSERT INTO SMALLINT_GENERATED_BY_DEFAULT(S,X) VALUES(DEFAULT, ''explicit'')';
execute SMALLINT_EXPLICIT;
execute SMALLINT_EXPLICIT;
execute SMALLINT_EXPLICIT;

SELECT * FROM SMALLINT_GENERATED_BY_DEFAULT;

-- test bigint generated by default columns
DROP TABLE BIGINT_GENERATED_BY_DEFAULT;
CREATE TABLE BIGINT_GENERATED_BY_DEFAULT(B BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 5, INCREMENT BY 10), X VARCHAR(8));
PREPARE BIGINT_IMPLICIT AS 'INSERT INTO BIGINT_GENERATED_BY_DEFAULT(X) VALUES(''implicit'')';
execute BIGINT_IMPLICIT;
execute BIGINT_IMPLICIT;
execute BIGINT_IMPLICIT;

PREPARE BIGINT_EXPLICIT AS 'INSERT INTO BIGINT_GENERATED_BY_DEFAULT(B,X) VALUES(DEFAULT, ''explicit'')';
execute BIGINT_EXPLICIT;
execute BIGINT_EXPLICIT;
execute BIGINT_EXPLICIT;
SELECT * FROM BIGINT_GENERATED_BY_DEFAULT;

-- test int generated always columns
DROP TABLE INT_GENERATED_ALWAYS;
CREATE TABLE INT_GENERATED_ALWAYS(I INT GENERATED ALWAYS AS IDENTITY (START WITH 6, INCREMENT BY 100), X VARCHAR(8));
PREPARE INT_ALWAYS_IMPLICIT AS 'INSERT INTO INT_GENERATED_ALWAYS(X) VALUES(''implicit'')';
execute INT_ALWAYS_IMPLICIT;
execute INT_ALWAYS_IMPLICIT;
execute INT_ALWAYS_IMPLICIT;

PREPARE INT_ALWAYS_EXPLICIT AS 'INSERT INTO INT_GENERATED_ALWAYS(I,X) VALUES(DEFAULT,''explicit'')';
execute INT_ALWAYS_EXPLICIT;
execute INT_ALWAYS_EXPLICIT;
execute INT_ALWAYS_EXPLICIT;

SELECT * FROM INT_GENERATED_ALWAYS;

-- test smallint generated always columns
DROP TABLE SMALLINT_GENERATED_ALWAYS;
CREATE TABLE SMALLINT_GENERATED_ALWAYS(S SMALLINT GENERATED ALWAYS AS IDENTITY (START WITH 6, INCREMENT BY 100), X VARCHAR(8));
PREPARE SMALLINT_ALWAYS_IMPLICIT AS 'INSERT INTO SMALLINT_GENERATED_ALWAYS(X) VALUES(''implicit'')';
execute SMALLINT_ALWAYS_IMPLICIT;
execute SMALLINT_ALWAYS_IMPLICIT;
execute SMALLINT_ALWAYS_IMPLICIT;

PREPARE SMALLINT_ALWAYS_EXPLICIT AS 'INSERT INTO SMALLINT_GENERATED_ALWAYS(S,X) VALUES(DEFAULT, ''explicit'')';
execute SMALLINT_ALWAYS_EXPLICIT;
execute SMALLINT_ALWAYS_EXPLICIT;
execute SMALLINT_ALWAYS_EXPLICIT;

SELECT * FROM SMALLINT_GENERATED_ALWAYS;

-- test bigint generated always columns
DROP TABLE BIGINT_GENERATED_ALWAYS;
CREATE TABLE BIGINT_GENERATED_ALWAYS(B BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 6, INCREMENT BY 100), X VARCHAR(8));
PREPARE BIGINT_ALWAYS_IMPLICIT AS 'INSERT INTO BIGINT_GENERATED_ALWAYS(X) VALUES(''implicit'')';
execute BIGINT_ALWAYS_IMPLICIT;
execute BIGINT_ALWAYS_IMPLICIT;
execute BIGINT_ALWAYS_IMPLICIT;

PREPARE BIGINT_ALWAYS_EXPLICIT AS 'INSERT INTO BIGINT_GENERATED_ALWAYS(B,X) VALUES(DEFAULT, ''explicit'')';
execute BIGINT_ALWAYS_EXPLICIT;
execute BIGINT_ALWAYS_EXPLICIT;
execute BIGINT_ALWAYS_EXPLICIT;

SELECT * FROM BIGINT_GENERATED_ALWAYS;