#CREATE DATABASE fcftest CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP TABLE IF EXISTS single_types2;
DROP TABLE IF EXISTS single_types1;
DROP TABLE IF EXISTS single_types_update2;
DROP TABLE IF EXISTS single_types_update1;
DROP TABLE IF EXISTS single_types_insert1;
DROP TABLE IF EXISTS single_types_joininsert_slave;
DROP TABLE IF EXISTS single_types_joininsert;
DROP TABLE IF EXISTS single_types_joininsert_master;
DROP TABLE IF EXISTS file_table;
DROP TABLE IF EXISTS file_table_join;
DROP TABLE IF EXISTS file_table_refjoin;


DROP TABLE IF EXISTS externref_test;
DROP TABLE IF EXISTS externref_test_join;
DROP TABLE IF EXISTS externref_test_ref;
DROP TABLE IF EXISTS externref_test_entity;
DROP TABLE IF EXISTS externref_test_dynamic;
DROP TABLE IF EXISTS externref_test_dynamic_join;
DROP TABLE IF EXISTS externref_test_ref_dynamic;

DROP TABLE IF EXISTS singleref_test_entity;
DROP TABLE IF EXISTS singleref_test;
DROP TABLE IF EXISTS singleref_test_dynamic;
DROP TABLE IF EXISTS singleref_test_join;
DROP TABLE IF EXISTS singleref_test_dynamic_join;
DROP TABLE IF EXISTS singleref_test_dynamic_join;






DROP USER IF EXISTS 'fcftest'@'localhost';
CREATE USER 'fcftest'@'localhost' IDENTIFIED BY 'fcftest';
GRANT ALL ON fcftest.* TO 'fcftest'@'localhost';
FLUSH PRIVILEGES;

CREATE TABLE single_types1 (
  `id`         serial PRIMARY KEY,
  `str1`       varchar(255),
  `text1`      text,
  `int1`       int,
  `bigint1`    bigint,
  `bool1`      char(1)
);

INSERT INTO single_types1 (`str1`, `text1`, `int1`, `bigint1`, `bool1`) VALUES ('string_1', 'test_1', 1, 12, 1);
INSERT INTO single_types1 (`str1`, `text1`, `int1`, `bigint1`, `bool1`) VALUES ('string_2', 'test_2', 2, 12, 1);

CREATE TABLE single_types2 (
  `id`         serial PRIMARY KEY,
  `key`        bigint,
  `str2`       varchar(255),
  `text2`      text,
  `int2`       int,
  `bigint2`    bigint,
  `bool2`      char(1)
);
INSERT INTO single_types2 (`key`, `str2`, `text2`, `int2`, `bigint2`, `bool2`) VALUES (1, 'string_2_1', 'test_2_1', 1, 12, 1);
INSERT INTO single_types2 (`key`, `str2`, `text2`, `int2`, `bigint2`, `bool2`) VALUES (2, 'string_2_2', 'test_2_2', 2, 12, 1);

CREATE TABLE single_types_update1 (
  `id`         serial PRIMARY KEY,
  `str1`       varchar(255),
  `text1`      text,
  `int1`       int,
  `bigint1`    bigint,
  `bool1`      char(1)
);
INSERT INTO single_types_update1 (`str1`, `text1`, `int1`, `bigint1`, `bool1`) VALUES ('string_1', 'test_1', 1, 12, 1);
INSERT INTO single_types_update1 (`str1`, `text1`, `int1`, `bigint1`, `bool1`) VALUES ('string_2', 'test_2', 2, 12, 1);


CREATE TABLE single_types_update2 (
  `id`         serial PRIMARY KEY,
  `key`        bigint,
  `str2`       varchar(255),
  `text2`      text,
  `int2`       int,
  `bigint2`    bigint,
  `bool2`      char(1)
);
INSERT INTO single_types_update2 (`key`, `str2`, `text2`, `int2`, `bigint2`, `bool2`) VALUES (1, 'string_2_1', 'test_2_1', 1, 12, 1);
INSERT INTO single_types_update2 (`key`, `str2`, `text2`, `int2`, `bigint2`, `bool2`) VALUES (2, 'string_2_2', 'test_2_2', 2, 12, 1);

CREATE TABLE single_types_insert1 (
  `id`         serial PRIMARY KEY,
  `str1`       varchar(255),
  `text1`      text,
  `int1`       int,
  `bigint1`    bigint,
  `bool1`      char(1)
);

CREATE TABLE single_types_joininsert (
  `id`          serial PRIMARY KEY,
  `str`         varchar(255),
  `refmaster`   bigint
);

CREATE TABLE single_types_joininsert_slave (
  `id`   serial PRIMARY KEY,
  `ref`  bigint,
  `str`  varchar(255)
);

CREATE TABLE single_types_joininsert_master (
  `id`    serial PRIMARY KEY,
  `str`   varchar(255)
);

CREATE TABLE file_table (
  `id`            serial PRIMARY KEY,
  `file1`         varchar(255),
  `file1@alias`   varchar(255),
  `file2`         varchar(255),
  `file2@xalias`  varchar(255),
  `ref`           BIGINT

);

CREATE TABLE file_table_join (
  `index`            serial PRIMARY KEY,
  `file_ref`      BIGINT NOT NULL
);

CREATE TABLE file_table_refjoin (
  `index`            serial PRIMARY KEY
);


CREATE TABLE externref_test (
  `id`            serial PRIMARY KEY,
  `name`          varchar(255)
);

CREATE TABLE externref_test_join (
  `index`         serial PRIMARY KEY,
  `name`          varchar(255),
  `ref`           bigint
);


CREATE TABLE externref_test_ref (
  `id`          serial PRIMARY KEY,
  `self_ref`    bigint,
  `entity_ref`  bigint
);

CREATE TABLE externref_test_entity (
  `id`            serial PRIMARY KEY,
  `name`          varchar(255)
);

CREATE TABLE externref_test_dynamic (
  `id`            serial PRIMARY KEY,
  `name`          varchar(255)
);


CREATE TABLE externref_test_dynamic_join (
  `index`         serial PRIMARY KEY,
  `name`          varchar(255),
  `ref`           bigint
);

CREATE TABLE externref_test_ref_dynamic (
  `id`                  serial PRIMARY KEY,
  `self_ref`            bigint,
  `entity_ref`          bigint
);


INSERT INTO externref_test_entity (id, name) VALUES (1, "entity1");
INSERT INTO externref_test_entity (id, name) VALUES (2, "entity2");
INSERT INTO externref_test_entity (id, name) VALUES (3, "entity3");
INSERT INTO externref_test_entity (id, name) VALUES (4, "entity4");

INSERT INTO externref_test (id, name) VALUES (1, "first");
INSERT INTO externref_test_ref (self_ref, entity_ref) VALUES (1, 1);
INSERT INTO externref_test_ref (self_ref, entity_ref) VALUES (1, 3);


INSERT INTO externref_test (id, name) VALUES (2, "second");
INSERT INTO externref_test_ref (self_ref, entity_ref) VALUES (2, 3);
INSERT INTO externref_test_ref (self_ref, entity_ref) VALUES (2, 4);

INSERT INTO externref_test (id, name) VALUES (3, "third");
INSERT INTO externref_test_ref (self_ref, entity_ref) VALUES (3, 2);

INSERT INTO externref_test (id, name) VALUES (4, "fourth");
INSERT INTO externref_test_ref (self_ref, entity_ref) VALUES (4, 3);
INSERT INTO externref_test_ref (self_ref, entity_ref) VALUES (4, 4);


INSERT INTO externref_test_join (`index`, ref, name) VALUES (1, 1, "join_first");
INSERT INTO externref_test_join (`index`, ref, name) VALUES (2, 2, "join_second");
INSERT INTO externref_test_join (`index`, ref, name) VALUES (3, 3, "join_third");
INSERT INTO externref_test_join (`index`, ref, name) VALUES (4, 4, "join_fourth");


CREATE TABLE singleref_test (
  `id`            serial PRIMARY KEY,
  `name`          varchar(255),
  `ref`           bigint
);

CREATE TABLE singleref_test_join (
  `index`         serial PRIMARY KEY,
  `name_join`     varchar(255),
  `ref_join`      bigint
);


CREATE TABLE singleref_test_entity (
  `index`         serial PRIMARY KEY,
  `entity_name`   varchar(255)
);

INSERT INTO singleref_test_entity (`index`, entity_name) VALUES (1, "first entity");
INSERT INTO singleref_test_entity (`index`, entity_name) VALUES (2, "second entity");
INSERT INTO singleref_test_entity (`index`, entity_name) VALUES (3, "thrid entity");
INSERT INTO singleref_test_entity (`index`, entity_name) VALUES (4, "fourth entity");
INSERT INTO singleref_test_entity (`index`, entity_name) VALUES (5, "fifth entity");

INSERT INTO singleref_test (`id`, ref, name) VALUES (1, 2, "first");
INSERT INTO singleref_test (`id`, ref, name) VALUES (2, 5, "second");
INSERT INTO singleref_test (`id`, ref, name) VALUES (3, 1, "thrid");
INSERT INTO singleref_test (`id`, ref, name) VALUES (4, 3, "fourth");
INSERT INTO singleref_test (`id`, ref, name) VALUES (5, 2, "fifth");

INSERT INTO singleref_test_join (`index`, ref_join, name_join) VALUES (1, 1, "first");
INSERT INTO singleref_test_join (`index`, ref_join, name_join) VALUES (2, 2, "second");
INSERT INTO singleref_test_join (`index`, ref_join, name_join) VALUES (3, 3, "thrid");
INSERT INTO singleref_test_join (`index`, ref_join, name_join) VALUES (4, 4, "fourth");
INSERT INTO singleref_test_join (`index`, ref_join, name_join) VALUES (5, 5, "fifth");


CREATE TABLE singleref_test_dynamic (
  `id`            serial PRIMARY KEY,
  `name`          varchar(255),
  `ref`           bigint
);

CREATE TABLE singleref_test_dynamic_join (
  `index`         serial PRIMARY KEY,
  `ref_join`      bigint
);




CREATE TABLE backref_test (
  `id_`      serial PRIMARY KEY,
  `title_`   varchar(255)
);

CREATE TABLE backref_test_ref (
  `id_`      serial PRIMARY KEY,
  `ref_`     bigint NOT NULL,
  `title_`   varchar(255) NOT NULL
);

CREATE TABLE backref_test_dynamic (
  `id_`      serial PRIMARY KEY,
  `title_`   varchar(255)
);

CREATE TABLE backref_test_ref_dynamic (
  `id_`      serial PRIMARY KEY,
  `ref_`     bigint NOT NULL,
  `title_`   varchar(255) NOT NULL
);


INSERT INTO backref_test (id_, title_) VALUES (1, "first");
INSERT INTO backref_test (id_, title_) VALUES (2, "second");
INSERT INTO backref_test (id_, title_) VALUES (3, "third");

INSERT INTO backref_test_ref (ref_, title_) VALUES (1, "first reference");

INSERT INTO backref_test_ref (ref_, title_) VALUES (2, "first reference");
INSERT INTO backref_test_ref (ref_, title_) VALUES (2, "second reference");
INSERT INTO backref_test_ref (ref_, title_) VALUES (2, "third reference");

