| 1 | -- |
|---|
| 2 | -- Copyright (C) 2013 DASISH |
|---|
| 3 | -- |
|---|
| 4 | -- This program is free software; you can redistribute it and/or |
|---|
| 5 | -- modify it under the terms of the GNU General Public License |
|---|
| 6 | -- as published by the Free Software Foundation; either version 2 |
|---|
| 7 | -- of the License, or (at your option) any later version. |
|---|
| 8 | -- |
|---|
| 9 | -- This program is distributed in the hope that it will be useful, |
|---|
| 10 | -- but WITHOUT ANY WARRANTY; without even the implied warranty of |
|---|
| 11 | -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
|---|
| 12 | -- GNU General Public License for more details. |
|---|
| 13 | -- |
|---|
| 14 | -- You should have received a copy of the GNU General Public License |
|---|
| 15 | -- along with this program; if not, write to the Free Software |
|---|
| 16 | -- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. |
|---|
| 17 | -- |
|---|
| 18 | |
|---|
| 19 | INSERT INTO principal (principal_name, external_id) VALUES ('a user', '00000000-0000-0000-0000-000000000003'); -- 1 |
|---|
| 20 | INSERT INTO principal (principal_name, external_id) VALUES ('b user', '00000000-0000-0000-0000-000000000004');-- 2 |
|---|
| 21 | |
|---|
| 22 | INSERT INTO notebook (title, owner_id, external_id) VALUES ('a notebook', 2, '00000000-0000-0000-0000-000000000001'); -- 1 |
|---|
| 23 | -- INSERT INTO notebook (title, owner_id, external_id) VALUES ('a notebook', 1, 1); |
|---|
| 24 | INSERT INTO annotation (owner_id,headline,body_xml, external_id) VALUES (1, 'a headline','<html><body>some html</body></html>', '00000000-0000-0000-0000-000000000005'); --1 |
|---|
| 25 | |
|---|
| 26 | INSERT INTO notebook (title, owner_id, external_id) VALUES ('a second notebook', 2, '00000000-0000-0000-0000-000000000002'); --2 |
|---|
| 27 | -- INSERT INTO notebook (title, owner_id, external_id) VALUES ('a second notebook', 1, 2); |
|---|
| 28 | INSERT INTO notebooks_annotations (notebook_id,annotation_id) VALUES (1,1); |
|---|
| 29 | INSERT INTO notebooks_annotations (notebook_id,annotation_id) VALUES (2,1); |
|---|
| 30 | |
|---|
| 31 | |
|---|
| 32 | -- Test data for jdbcAnnotationDao -- |
|---|
| 33 | |
|---|
| 34 | INSERT INTO principal (principal_name, external_id) VALUES ('Twan', '00000000-0000-0000-0000-000000000111'); --3 |
|---|
| 35 | INSERT INTO principal (principal_name, external_id) VALUES ('Peter', '00000000-0000-0000-0000-000000000112'); -- 4 |
|---|
| 36 | INSERT INTO principal (principal_name, external_id) VALUES ('Olha', '00000000-0000-0000-0000-000000000113'); -- 5 |
|---|
| 37 | |
|---|
| 38 | INSERT INTO notebook (title, owner_id, external_id) VALUES ('Notebook 3', 3, '00000000-0000-0000-0000-000000000011'); -- 3 |
|---|
| 39 | INSERT INTO notebook (title, owner_id, external_id) VALUES ('Notebook 4', 4, '00000000-0000-0000-0000-000000000012'); --4 |
|---|
| 40 | INSERT INTO notebook (title, owner_id, external_id) VALUES ('Notebook 5', 5, '00000000-0000-0000-0000-000000000013'); --5 |
|---|
| 41 | INSERT INTO notebook (title, owner_id, external_id) VALUES ('Notebook 6', 5, '00000000-0000-0000-0000-000000000014'); --6 |
|---|
| 42 | |
|---|
| 43 | INSERT INTO annotation (owner_id,headline,body_xml, external_id, time_stamp) VALUES (3, 'Sagrada Famiglia','<html><body>some html 1</body></html>', '00000000-0000-0000-0000-000000000021', '2013-08-12 11:25:00.383+02:00'); --2 |
|---|
| 44 | INSERT INTO annotation (owner_id,headline,body_xml, external_id) VALUES (4, 'Gaudi','<html><body>some html 2 </body></html>', '00000000-0000-0000-0000-000000000022'); --3 |
|---|
| 45 | INSERT INTO annotation (owner_id,headline,body_xml, external_id) VALUES (5, 'Art Nuveau','<html><body>some html 3</body></html>', '00000000-0000-0000-0000-000000000023'); --4 |
|---|
| 46 | INSERT INTO annotation (owner_id,headline,body_xml, external_id) VALUES (3, 'Annotation to delete','<html><body>some html 4</body></html>', '00000000-0000-0000-0000-000000000024'); --5 |
|---|
| 47 | |
|---|
| 48 | |
|---|
| 49 | |
|---|
| 50 | INSERT INTO notebooks_annotations (notebook_id,annotation_id) VALUES (3,2); |
|---|
| 51 | INSERT INTO notebooks_annotations (notebook_id,annotation_id) VALUES (3,3); |
|---|
| 52 | INSERT INTO notebooks_annotations (notebook_id,annotation_id) VALUES (4,4); |
|---|
| 53 | INSERT INTO notebooks_annotations (notebook_id,annotation_id) VALUES (5,5); |
|---|
| 54 | |
|---|
| 55 | |
|---|
| 56 | |
|---|
| 57 | |
|---|
| 58 | -- CREATE TABLE version ( |
|---|
| 59 | -- version_id SERIAL UNIQUE NOT NULL, |
|---|
| 60 | -- external_id UUID UNIQUE NOT NULL, |
|---|
| 61 | -- version text, |
|---|
| 62 | -- -- SOUNDNESS: there must be at least one row with this version_id in the verions_cached_representations table |
|---|
| 63 | -- ); |
|---|
| 64 | |
|---|
| 65 | INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000041', 'SF-version 2013'); -- 1 |
|---|
| 66 | INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000042', 'SF-version 2012'); -- 2 |
|---|
| 67 | INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000043', 'Gaudi wiki -version 2013'); -- 3 |
|---|
| 68 | INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000044', 'Art Nuveau wiki -version 2013'); --4 |
|---|
| 69 | INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000045', 'Art Nuveau wiki -version 2012'); --5 |
|---|
| 70 | INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000046', 'Art Nuveau wiki -version 2011'); --6 not used |
|---|
| 71 | INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000047', 'Art Nuveau wiki -version 2010'); --7 |
|---|
| 72 | |
|---|
| 73 | -- CREATE TABLE target_source ( |
|---|
| 74 | -- source_id SERIAL UNIQUE NOT NULL, |
|---|
| 75 | -- external_id UUID UNIQUE NOT NULL, |
|---|
| 76 | -- time_stamp timestamp with time zone default now(), |
|---|
| 77 | -- link_uri text, |
|---|
| 78 | -- version_id integer REFERENCES version(version_id), ---- DIFFERS from the xml structure, |
|---|
| 79 | -- -- SOUNDNESS: there must be exactly version at the version table ++ |
|---|
| 80 | -- -- soundness: there must be at least one annotation referring to this source |
|---|
| 81 | -- ); |
|---|
| 82 | |
|---|
| 83 | |
|---|
| 84 | INSERT INTO target_source (external_id, link_uri, version) VALUES ('00000000-0000-0000-0000-000000000031', 'http://nl.wikipedia.org/wiki/Sagrada_Fam%C3%ADlia', '00000000-0000-0000-0000-000000000041'); -- 1 |
|---|
| 85 | INSERT INTO target_source (external_id, link_uri, version) VALUES ('00000000-0000-0000-0000-000000000032', 'http://nl.wikipedia.org/wiki/Antoni_Gaud%C3%AD', '00000000-0000-0000-0000-000000000043'); --2 |
|---|
| 86 | INSERT INTO target_source (external_id, link_uri, version) VALUES ('00000000-0000-0000-0000-000000000033', 'http://en.wikipedia.org/wiki/Art_Nouveau', '00000000-0000-0000-0000-000000000044'); --3 |
|---|
| 87 | INSERT INTO target_source (external_id, link_uri, version) VALUES ('00000000-0000-0000-0000-000000000034', '???', '00000000-0000-0000-0000-000000000045'); --4 |
|---|
| 88 | INSERT INTO target_source (external_id, link_uri, version) VALUES ('00000000-0000-0000-0000-000000000035', '???', '00000000-0000-0000-0000-000000000045'); --5 |
|---|
| 89 | |
|---|
| 90 | -- CREATE TABLE annotations_target_sources ( |
|---|
| 91 | -- annotation_id integer REFERENCES annotation(annotation_id), -- defining a foreign key: there must be a uniquely defined row in "annotation", that is defined by "annotation_id" |
|---|
| 92 | -- source_id integer REFERENCES target_source(source_id), |
|---|
| 93 | -- unique(annotation_id, source_id), |
|---|
| 94 | -- ); |
|---|
| 95 | |
|---|
| 96 | INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (2, 1); |
|---|
| 97 | INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (2, 2); |
|---|
| 98 | INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (3, 2); |
|---|
| 99 | INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (4, 3); |
|---|
| 100 | INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (5, 3); -- source 3 should not be deleted when annot 5 is deleted |
|---|
| 101 | INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (5, 4); -- source 4 to be deleted when annot 5 is deleted |
|---|
| 102 | |
|---|
| 103 | ---------------------------------------------------------------- |
|---|
| 104 | |
|---|
| 105 | -- CREATE TABLE sources_versions ( |
|---|
| 106 | -- source_id integer REFERENCES target_source(source_id), |
|---|
| 107 | -- version_id integer REFERENCES version(version_id), |
|---|
| 108 | -- unique(source_id, version_id), |
|---|
| 109 | -- ); |
|---|
| 110 | |
|---|
| 111 | INSERT INTO sources_versions (source_id, version_id) VALUES (1, 1); |
|---|
| 112 | INSERT INTO sources_versions (source_id, version_id) VALUES (1, 2); |
|---|
| 113 | INSERT INTO sources_versions (source_id, version_id) VALUES (2, 3); |
|---|
| 114 | INSERT INTO sources_versions (source_id, version_id) VALUES (3, 4); |
|---|
| 115 | INSERT INTO sources_versions (source_id, version_id) VALUES (4, 5); |
|---|
| 116 | INSERT INTO sources_versions (source_id, version_id) VALUES (5, 7); |
|---|
| 117 | |
|---|
| 118 | -------------------------------------------------------------- |
|---|
| 119 | |
|---|
| 120 | -- CREATE TABLE cached_representation_info ( |
|---|
| 121 | -- cached_representation_id SERIAL UNIQUE NOT NULL, |
|---|
| 122 | -- external_id UUID UNIQUE NOT NULL, |
|---|
| 123 | -- mime_type text, |
|---|
| 124 | -- tool text, |
|---|
| 125 | -- type_ text, |
|---|
| 126 | -- where_is_the_file text -- DIFFERS FROM the schema |
|---|
| 127 | -- ); |
|---|
| 128 | |
|---|
| 129 | INSERT INTO cached_representation_info (external_id, mime_type, tool, type_, where_is_the_file) VALUES ('00000000-0000-0000-0000-000000000051', 'text/html', 'latex', 'text', 'corpus1'); --1 |
|---|
| 130 | INSERT INTO cached_representation_info (external_id, mime_type, tool, type_, where_is_the_file) VALUES ('00000000-0000-0000-0000-000000000052', 'text/html', 'vi', 'text', 'corpus1'); -- 2 |
|---|
| 131 | INSERT INTO cached_representation_info (external_id, mime_type, tool, type_, where_is_the_file) VALUES ('00000000-0000-0000-0000-000000000053', 'image/png', 'screenshooter', 'image', 'corpus1'); -- 3 |
|---|
| 132 | INSERT INTO cached_representation_info (external_id, mime_type, tool, type_, where_is_the_file) VALUES ('00000000-0000-0000-0000-000000000054', 'text/html', 'oxygen', 'text', 'corpus1'); --4 |
|---|
| 133 | INSERT INTO cached_representation_info (external_id, mime_type, tool, type_, where_is_the_file) VALUES ('00000000-0000-0000-0000-000000000055', 'image/jpg', 'photomaster', 'image', 'TLAscratch'); --5 |
|---|
| 134 | INSERT INTO cached_representation_info (external_id, mime_type, tool, type_, where_is_the_file) VALUES ('00000000-0000-0000-0000-000000000056', 'text/plain', 'some tool', 'text', 'TLAscratch'); --6 |
|---|
| 135 | INSERT INTO cached_representation_info (external_id, mime_type, tool, type_, where_is_the_file) VALUES ('00000000-0000-0000-0000-000000000057', 'text/html', 'some tool 2', 'text', 'TLAscratch'); --7 |
|---|
| 136 | |
|---|
| 137 | -------------------------------------------------------------- |
|---|
| 138 | |
|---|
| 139 | |
|---|
| 140 | |
|---|
| 141 | -- CREATE TABLE versions_cached_representations ( |
|---|
| 142 | -- version_id integer REFERENCES version(version_id), |
|---|
| 143 | -- cached_representation_id integer REFERENCES cached_representation_info(cached_representation_id), |
|---|
| 144 | -- unique(version_id, cached_representation_id), |
|---|
| 145 | -- ); |
|---|
| 146 | |
|---|
| 147 | INSERT INTO versions_cached_representations (version_id, cached_representation_id) VALUES (1, 1); |
|---|
| 148 | INSERT INTO versions_cached_representations (version_id, cached_representation_id) VALUES (2, 3); |
|---|
| 149 | INSERT INTO versions_cached_representations (version_id, cached_representation_id) VALUES (3, 2); |
|---|
| 150 | INSERT INTO versions_cached_representations (version_id, cached_representation_id) VALUES (4, 4); |
|---|
| 151 | INSERT INTO versions_cached_representations (version_id, cached_representation_id) VALUES (1, 5); |
|---|
| 152 | INSERT INTO versions_cached_representations (version_id, cached_representation_id) VALUES (5, 5); |
|---|
| 153 | INSERT INTO versions_cached_representations (version_id, cached_representation_id) VALUES (6, 5); |
|---|
| 154 | |
|---|
| 155 | |
|---|
| 156 | ---- PERMISSIONS -------------------------------------------------------------------------------------------- |
|---|
| 157 | |
|---|
| 158 | INSERT INTO permission_(permission_mode) VALUES ('owner'); |
|---|
| 159 | INSERT INTO permission_(permission_mode) VALUES ('writer'); |
|---|
| 160 | INSERT INTO permission_(permission_mode) VALUES ('reader'); |
|---|
| 161 | |
|---|
| 162 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (2, 3, 'owner'); |
|---|
| 163 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (2, 4, 'writer'); |
|---|
| 164 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (2, 5, 'reader'); |
|---|
| 165 | |
|---|
| 166 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (3, 4, 'owner'); |
|---|
| 167 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (3, 3, 'reader'); |
|---|
| 168 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (3, 5, 'writer'); |
|---|
| 169 | |
|---|
| 170 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (4, 5, 'owner'); |
|---|
| 171 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (4, 3, 'reader'); |
|---|
| 172 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (4, 4, 'reader'); |
|---|
| 173 | |
|---|
| 174 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (5, 3, 'owner'); |
|---|
| 175 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (5, 4, 'writer'); |
|---|
| 176 | INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (5, 5, 'writer'); |
|---|
| 177 | -- checking integrity control: |
|---|
| 178 | -- INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (5, 5, 'reader'); |
|---|
