# Copyright (c) 2008-2012 Nuxeo SA (http://nuxeo.com/) and others.
#
# All rights reserved. This program and the accompanying materials
# are made available under the terms of the Eclipse Public License v1.0
# which accompanies this distribution, and is available at
# http://www.eclipse.org/legal/epl-v10.html
#
# Contributors:
#     Florent Guillaume

# Variables used:
# ${idType} varchar(36)


############################################################


#CATEGORY: testUpgrade

# here use a TEXT instead of a VARCHAR to test compatibility
CREATE TABLE testschema2 (
  id ${idType} NOT NULL PRIMARY KEY,
  title text
);

# create old table locks with constraints

CREATE TABLE locks (
  id ${idType} NOT NULL,
  lock VARCHAR(255)
);

ALTER TABLE locks ADD CONSTRAINT locks_pk PRIMARY KEY (id);

# create old-style fulltext table with pre-tsvectored columns

#IF: testUpgradeFulltext
CREATE TABLE fulltext (
  id ${idType} NOT NULL PRIMARY KEY, -- foreign key below
  jobid varchar(250),
  fulltext tsvector,
  simpletext tsvector,
  binarytext tsvector
);

#IF: testUpgradeFulltext
CREATE INDEX "fulltext_fulltext_idx" ON "fulltext" USING GIN("fulltext");


############################################################


#CATEGORY: testUpgradeOldTables

# old foreign keys

ALTER TABLE locks ADD CONSTRAINT locks_id_hierarchy_fk FOREIGN KEY (id)
  REFERENCES hierarchy ON DELETE CASCADE;

#IF: testUpgradeFulltext
ALTER TABLE fulltext ADD CONSTRAINT fulltext_id_hierarchy_fk FOREIGN KEY (id)
  REFERENCES hierarchy ON DELETE CASCADE;

# create old values in old tables

# root & repository
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype)
  VALUES ('00000000-0000-0000-0000-000000000000', null, '', false, 'Root')

INSERT INTO repositories (id, name) VALUES ('00000000-0000-0000-0000-000000000000', 'default')

# fake doc to tag
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype)
  VALUES ('dddddddd-dddd-dddd-dddd-dddddddddddd', null, 'mydoc', false, 'TestDoc')

# tags root
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype)
  VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '00000000-0000-0000-0000-000000000000', 'tags', false, 'HiddenFolder')

# tag hier
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype)
  VALUES ('11111111-2222-3333-4444-555555555555', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'mytag', false, 'Tag')

# tag
INSERT INTO tag (id, label) VALUES ('11111111-2222-3333-4444-555555555555', 'mytag');

# tag dublincore
INSERT INTO dublincore (id, creator, title) VALUES ('11111111-2222-3333-4444-555555555555', 'Administrator', 'mytag');

# tag 2 (unused) hier
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype)
  VALUES ('ffffffff-ffff-ffff-1111-111111111111', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'othertag', false, 'Tag')

# tag 2
INSERT INTO tag (id, label) VALUES ('ffffffff-ffff-ffff-1111-111111111111', 'othertag');

# tag 2 dublincore
INSERT INTO dublincore (id, creator, title) VALUES ('ffffffff-ffff-ffff-1111-111111111111', 'Administrator', 'othertag');


# ver 1
#IF: testUpgradeVersions
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype, majorversion, minorversion)
  VALUES ('11111111-0000-0000-2222-000000000000', null, 'mydoc2', false, 'TestDoc', 0, 1);

# ver 1 version
#IF: testUpgradeVersions
INSERT INTO versions (id, versionableid, created)
  VALUES ('11111111-0000-0000-2222-000000000000', '11111111-0000-0000-1111-000000000000', DATE '2010-01-01');

# ver 2
#IF: testUpgradeVersions
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype, majorversion, minorversion)
  VALUES ('11111111-0000-0000-2222-000000000001', null, 'mydoc2', false, 'TestDoc', 1, 0);

# ver 2 version
#IF: testUpgradeVersions
INSERT INTO versions (id, versionableid, created)
  VALUES ('11111111-0000-0000-2222-000000000001', '11111111-0000-0000-1111-000000000000', DATE '2010-01-02');

# ver 3
#IF: testUpgradeVersions
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype, majorversion, minorversion)
  VALUES ('11111111-0000-0000-2222-000000000002', null, 'mydoc2', false, 'TestDoc', 1, 1);

# ver 3 version
#IF: testUpgradeVersions
INSERT INTO versions (id, versionableid, created)
  VALUES ('11111111-0000-0000-2222-000000000002', '11111111-0000-0000-1111-000000000000', DATE '2010-01-04');

# ver 4 for other doc
#IF: testUpgradeVersions
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype, majorversion, minorversion)
  VALUES ('11111111-0000-0000-3333-000000000001', null, 'mydoc3', false, 'TestDoc', 1, 0);

# ver 4 version
#IF: testUpgradeVersions
INSERT INTO versions (id, versionableid, created)
  VALUES ('11111111-0000-0000-3333-000000000001', '11111111-0000-0000-3333-000000000000', DATE '2010-01-03');

# fake doc 2
#IF: testUpgradeLastContributor
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype)
  VALUES ('12121212-dddd-dddd-dddd-000000000000', null, 'mynddoc', false, 'Tag');

# fake doc 2 dublincore
#IF: testUpgradeLastContributor
INSERT INTO dublincore (id, creator, title) VALUES ('12121212-dddd-dddd-dddd-000000000000', 'Administrator', 'othertag');

# fake doc 2 contributors
#IF: testUpgradeLastContributor
INSERT INTO dc_contributors (id, pos, item) VALUES ('12121212-dddd-dddd-dddd-000000000000', 0, 'Administrator');

# fake doc 3
#IF: testUpgradeLastContributor
INSERT INTO hierarchy (id, parentid, name, isproperty, primarytype)
  VALUES ('12121212-dddd-dddd-dddd-000000000001', null, 'myrddoc', false, 'Tag');

# fake doc 3 dublincore
#IF: testUpgradeLastContributor
INSERT INTO dublincore (id, creator, title) VALUES ('12121212-dddd-dddd-dddd-000000000001', 'Administrator', 'othertag');

# fake doc 3 contributors
#IF: testUpgradeLastContributor
INSERT INTO dc_contributors (id, pos, item) VALUES ('12121212-dddd-dddd-dddd-000000000001', 0, 'Administrator');
INSERT INTO dc_contributors (id, pos, item) VALUES ('12121212-dddd-dddd-dddd-000000000001', 1, 'FakeOne');

# lock doc
#IF: testUpgradeLocks
INSERT INTO locks (id, lock)
  VALUES ('dddddddd-dddd-dddd-dddd-dddddddddddd', 'bob:Jan 26, 2011');

#IF: testUpgradeLocks
INSERT INTO locks (id, lock)
  VALUES ('11111111-2222-3333-4444-555555555555', NULL);
