4888 |
06 Apr 09 |
nicklas |
1 |
<?xml version="1.0" encoding="UTF-8" ?> |
2959 |
27 Nov 06 |
nicklas |
2 |
<!DOCTYPE predefined-queries SYSTEM "predefined-queries.dtd" > |
2959 |
27 Nov 06 |
nicklas |
3 |
<!-- |
2959 |
27 Nov 06 |
nicklas |
$Id$ |
2959 |
27 Nov 06 |
nicklas |
5 |
|
3675 |
16 Aug 07 |
jari |
Copyright (C) 2006 Nicklas Nordborg |
2959 |
27 Nov 06 |
nicklas |
7 |
|
2959 |
27 Nov 06 |
nicklas |
This file is part of BASE - BioArray Software Environment. |
2959 |
27 Nov 06 |
nicklas |
Available at http://base.thep.lu.se/ |
2959 |
27 Nov 06 |
nicklas |
10 |
|
2959 |
27 Nov 06 |
nicklas |
BASE is free software; you can redistribute it and/or |
2959 |
27 Nov 06 |
nicklas |
modify it under the terms of the GNU General Public License |
4474 |
05 Sep 08 |
jari |
as published by the Free Software Foundation; either version 3 |
2959 |
27 Nov 06 |
nicklas |
of the License, or (at your option) any later version. |
2959 |
27 Nov 06 |
nicklas |
15 |
|
2959 |
27 Nov 06 |
nicklas |
BASE is distributed in the hope that it will be useful, |
2959 |
27 Nov 06 |
nicklas |
but WITHOUT ANY WARRANTY; without even the implied warranty of |
2959 |
27 Nov 06 |
nicklas |
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
2959 |
27 Nov 06 |
nicklas |
GNU General Public License for more details. |
2959 |
27 Nov 06 |
nicklas |
20 |
|
2959 |
27 Nov 06 |
nicklas |
You should have received a copy of the GNU General Public License |
4508 |
11 Sep 08 |
jari |
along with BASE. If not, see <http://www.gnu.org/licenses/>. |
2959 |
27 Nov 06 |
nicklas |
23 |
--> |
2959 |
27 Nov 06 |
nicklas |
24 |
<!-- |
2959 |
27 Nov 06 |
nicklas |
This file contains HQL and SQL queries that have been modified |
2959 |
27 Nov 06 |
nicklas |
specifically for the MySQL database and overrides the queries |
2959 |
27 Nov 06 |
nicklas |
found in the common-queries.xml file. |
2959 |
27 Nov 06 |
nicklas |
28 |
--> |
2959 |
27 Nov 06 |
nicklas |
29 |
<predefined-queries> |
6981 |
08 Oct 15 |
nicklas |
30 |
<query id="DROP_NOT_NULL_CONSTRAINT" type="SQL"> |
6981 |
08 Oct 15 |
nicklas |
31 |
<sql> |
6981 |
08 Oct 15 |
nicklas |
32 |
ALTER TABLE [{1}] MODIFY [{2}] {3} NULL |
6981 |
08 Oct 15 |
nicklas |
33 |
</sql> |
6981 |
08 Oct 15 |
nicklas |
34 |
<description> |
6981 |
08 Oct 15 |
nicklas |
35 |
An SQL query that drops a NOT NULL contraint from column (2) with data type (3) |
6981 |
08 Oct 15 |
nicklas |
36 |
in a table (1). |
6981 |
08 Oct 15 |
nicklas |
37 |
</description> |
6981 |
08 Oct 15 |
nicklas |
38 |
</query> |
2959 |
27 Nov 06 |
nicklas |
39 |
|
7135 |
21 Apr 16 |
nicklas |
40 |
<query id="AB_INSERT_INTO_PARAMETERVALUES" type="SQL"> |
7135 |
21 Apr 16 |
nicklas |
41 |
<sql> |
7136 |
21 Apr 16 |
nicklas |
42 |
INSERT INTO [ParameterValues] ([discriminator], [version]) |
7135 |
21 Apr 16 |
nicklas |
43 |
VALUES (?, 0) |
7135 |
21 Apr 16 |
nicklas |
44 |
</sql> |
7135 |
21 Apr 16 |
nicklas |
45 |
<description> |
7135 |
21 Apr 16 |
nicklas |
46 |
SQL query for inserting rows into the ParameterValues table. |
7135 |
21 Apr 16 |
nicklas |
47 |
The primary key (id) must be auto-generated. |
7135 |
21 Apr 16 |
nicklas |
48 |
Parameters: discriminator |
7135 |
21 Apr 16 |
nicklas |
49 |
</description> |
7135 |
21 Apr 16 |
nicklas |
50 |
</query> |
7135 |
21 Apr 16 |
nicklas |
51 |
|
7121 |
19 Apr 16 |
nicklas |
52 |
<query id="AB_INSERT_INTO_ANNOTATIONS" type="SQL"> |
7121 |
19 Apr 16 |
nicklas |
53 |
<sql> |
7259 |
09 Dec 16 |
nicklas |
54 |
INSERT INTO [Annotations] ([version], [annotationset_id], [annotationtype_id], [unit_id], [value_id], [last_update], [source], [project_id], [override_id]) |
7259 |
09 Dec 16 |
nicklas |
55 |
VALUES (0, ?, ?, ?, ?, ?, 0, ?, ?) |
7121 |
19 Apr 16 |
nicklas |
56 |
</sql> |
7121 |
19 Apr 16 |
nicklas |
57 |
<description> |
7121 |
19 Apr 16 |
nicklas |
58 |
SQL query for inserting rows into the Annotations table. |
7121 |
19 Apr 16 |
nicklas |
59 |
The primary key (id) must be auto-generated. |
7259 |
09 Dec 16 |
nicklas |
60 |
Parameters: annotationset_id, annotationtype_id, unit_id, value_id, last_updated, project_id, override_id |
7121 |
19 Apr 16 |
nicklas |
61 |
</description> |
7121 |
19 Apr 16 |
nicklas |
62 |
</query> |
7121 |
19 Apr 16 |
nicklas |
63 |
|
7121 |
19 Apr 16 |
nicklas |
64 |
<query id="AB_INSERT_INTO_ANNOTATIONSETS" type="SQL"> |
7121 |
19 Apr 16 |
nicklas |
65 |
<sql> |
7121 |
19 Apr 16 |
nicklas |
66 |
INSERT INTO [AnnotationSets] ([version], [item_type], [item_id]) |
7121 |
19 Apr 16 |
nicklas |
67 |
VALUES (0, {1}, ?) |
7121 |
19 Apr 16 |
nicklas |
68 |
</sql> |
7121 |
19 Apr 16 |
nicklas |
69 |
<description> |
7121 |
19 Apr 16 |
nicklas |
70 |
SQL query for inserting rows into the AnnotationSets table. |
7121 |
19 Apr 16 |
nicklas |
71 |
The primary key (id) must be auto-generated. |
7121 |
19 Apr 16 |
nicklas |
72 |
Parameters: item_id |
7121 |
19 Apr 16 |
nicklas |
73 |
</description> |
7121 |
19 Apr 16 |
nicklas |
74 |
</query> |
7121 |
19 Apr 16 |
nicklas |
75 |
|
7132 |
21 Apr 16 |
nicklas |
76 |
<query id="DBLOG_INSERT_INTO_CHANGEHISTORYDETAILS" type="SQL"> |
7132 |
21 Apr 16 |
nicklas |
77 |
<sql> |
7132 |
21 Apr 16 |
nicklas |
78 |
INSERT INTO [ChangeHistoryDetails] |
7132 |
21 Apr 16 |
nicklas |
79 |
([version], [history_id], [change_type], [item_id], [item_type], [change_info], [old_value], [new_value]) |
7132 |
21 Apr 16 |
nicklas |
80 |
VALUES (0, ?, ?, ?, ?, ?, ?, ?) |
7132 |
21 Apr 16 |
nicklas |
81 |
</sql> |
7132 |
21 Apr 16 |
nicklas |
82 |
<description> |
7132 |
21 Apr 16 |
nicklas |
83 |
SQL query for inserting rows into the ChangeHistoryDetails table. |
7132 |
21 Apr 16 |
nicklas |
84 |
The primary key (id) must be auto-generated. |
7132 |
21 Apr 16 |
nicklas |
85 |
Parameters: history_id, change_type, item_id, item_type, change_info, old_value, new_value |
7132 |
21 Apr 16 |
nicklas |
86 |
</description> |
7132 |
21 Apr 16 |
nicklas |
87 |
</query> |
7121 |
19 Apr 16 |
nicklas |
88 |
|
7290 |
31 Jan 17 |
nicklas |
89 |
<query id="PS_FIND_OVERRIDE_ID_FOR_PROJECT_ANNOTATION" type="SQL"> |
7290 |
31 Jan 17 |
nicklas |
90 |
<sql> |
7290 |
31 Jan 17 |
nicklas |
91 |
UPDATE [Annotations] |
7290 |
31 Jan 17 |
nicklas |
92 |
SET [override_id] = COALESCE( |
7290 |
31 Jan 17 |
nicklas |
93 |
(SELECT [tmp].[id] FROM ( |
7290 |
31 Jan 17 |
nicklas |
94 |
SELECT [id] FROM [Annotations] |
7290 |
31 Jan 17 |
nicklas |
95 |
WHERE [annotationset_id] = :annotationSetId |
7290 |
31 Jan 17 |
nicklas |
96 |
AND [annotationtype_id] = :annotationTypeId |
7290 |
31 Jan 17 |
nicklas |
97 |
AND [source] = 0 AND [project_id] = 0 |
7290 |
31 Jan 17 |
nicklas |
98 |
) [tmp] ) |
7290 |
31 Jan 17 |
nicklas |
99 |
, 0) |
7290 |
31 Jan 17 |
nicklas |
100 |
WHERE id = :annotationId |
7290 |
31 Jan 17 |
nicklas |
101 |
</sql> |
7290 |
31 Jan 17 |
nicklas |
102 |
<description> |
7290 |
31 Jan 17 |
nicklas |
103 |
Updates the "override_id" column on a newly created project-specific |
7290 |
31 Jan 17 |
nicklas |
104 |
(PRIMARY) annotation to point to the existing default annotation id |
7290 |
31 Jan 17 |
nicklas |
105 |
or 0 if no default annotation exists. To make this query work in MySQL |
7290 |
31 Jan 17 |
nicklas |
106 |
we need a double inner subselect to force a temporary table for the |
7290 |
31 Jan 17 |
nicklas |
107 |
single value we are after. |
7290 |
31 Jan 17 |
nicklas |
108 |
</description> |
7290 |
31 Jan 17 |
nicklas |
109 |
</query> |
7290 |
31 Jan 17 |
nicklas |
110 |
|
7290 |
31 Jan 17 |
nicklas |
111 |
<query id="PS_UPDATE_PROJECT_ANNOTATION_OVERRIDE_REF_INHERITED" type="HQL"> |
7290 |
31 Jan 17 |
nicklas |
112 |
<sql> |
7290 |
31 Jan 17 |
nicklas |
113 |
UPDATE [Annotations] |
7290 |
31 Jan 17 |
nicklas |
114 |
SET [override_id] = :annotationId |
7290 |
31 Jan 17 |
nicklas |
115 |
WHERE [annotationset_id] = :annotationSetId |
7290 |
31 Jan 17 |
nicklas |
116 |
AND [inherited_id] IN ( |
7290 |
31 Jan 17 |
nicklas |
117 |
SELECT [tmp].[id] FROM ( |
7290 |
31 Jan 17 |
nicklas |
118 |
SELECT [id] FROM [Annotations] |
7290 |
31 Jan 17 |
nicklas |
119 |
WHERE [annotationset_id] = :parentSetId |
7290 |
31 Jan 17 |
nicklas |
120 |
AND [override_id] = :parentId |
7290 |
31 Jan 17 |
nicklas |
121 |
) [tmp] |
7290 |
31 Jan 17 |
nicklas |
122 |
) |
7290 |
31 Jan 17 |
nicklas |
123 |
</sql> |
7290 |
31 Jan 17 |
nicklas |
124 |
<description> |
7290 |
31 Jan 17 |
nicklas |
125 |
Updates the "override_id" column on project-specific annotations |
7290 |
31 Jan 17 |
nicklas |
126 |
to point to a newly created default annotation (INHERITED and |
7290 |
31 Jan 17 |
nicklas |
127 |
CLONED annotations). The subquery will find project-specific annotation |
7290 |
31 Jan 17 |
nicklas |
128 |
on the parent annotation set that is overriding the inherited annotation. |
7290 |
31 Jan 17 |
nicklas |
129 |
|
7290 |
31 Jan 17 |
nicklas |
130 |
To make this query work in MySQL we need a double inner subselect |
7290 |
31 Jan 17 |
nicklas |
131 |
to force a temporary table for the values we are after. |
7290 |
31 Jan 17 |
nicklas |
132 |
</description> |
7290 |
31 Jan 17 |
nicklas |
133 |
</query> |
7290 |
31 Jan 17 |
nicklas |
134 |
|
7290 |
31 Jan 17 |
nicklas |
135 |
<query id="PS_FIND_OVERRIDE_ID_FOR_PROJECT_ANNOTATION_INHERITED" type="SQL"> |
7290 |
31 Jan 17 |
nicklas |
136 |
<sql> |
7290 |
31 Jan 17 |
nicklas |
137 |
UPDATE [Annotations] |
7290 |
31 Jan 17 |
nicklas |
138 |
SET [override_id] = COALESCE( |
7290 |
31 Jan 17 |
nicklas |
139 |
(SELECT [tmp].[id] FROM ( |
7290 |
31 Jan 17 |
nicklas |
140 |
SELECT [id] FROM [Annotations] |
7290 |
31 Jan 17 |
nicklas |
141 |
WHERE [annotationset_id] = :annotationSetId |
7290 |
31 Jan 17 |
nicklas |
142 |
AND [inherited_id] = :parentOverrideId |
7290 |
31 Jan 17 |
nicklas |
143 |
AND [source] <> 0 |
7290 |
31 Jan 17 |
nicklas |
144 |
AND [project_id] = 0 |
7290 |
31 Jan 17 |
nicklas |
145 |
) [tmp]) |
7290 |
31 Jan 17 |
nicklas |
146 |
, 0) |
7290 |
31 Jan 17 |
nicklas |
147 |
WHERE id = :annotationId |
7290 |
31 Jan 17 |
nicklas |
148 |
</sql> |
7290 |
31 Jan 17 |
nicklas |
149 |
<description> |
7290 |
31 Jan 17 |
nicklas |
150 |
Updates the "override_id" column on a newly created project-specific |
7290 |
31 Jan 17 |
nicklas |
151 |
(CLONED or INHERITED) annotation to point to the existing default |
7290 |
31 Jan 17 |
nicklas |
152 |
annotation id or 0 if no default annotation exists. |
7290 |
31 Jan 17 |
nicklas |
153 |
|
7290 |
31 Jan 17 |
nicklas |
154 |
To make this query work in MySQL we need a double inner subselect |
7290 |
31 Jan 17 |
nicklas |
155 |
to force a temporary table for the values we are after. |
7290 |
31 Jan 17 |
nicklas |
156 |
</description> |
7290 |
31 Jan 17 |
nicklas |
157 |
</query> |
7121 |
19 Apr 16 |
nicklas |
158 |
|
7290 |
31 Jan 17 |
nicklas |
159 |
<query id="PS_RESET_OVERRIDE_ON_PROJECT_ANNOTATIONS" type="HQL"> |
7290 |
31 Jan 17 |
nicklas |
160 |
<sql> |
7290 |
31 Jan 17 |
nicklas |
161 |
UPDATE [Annotations] |
7290 |
31 Jan 17 |
nicklas |
162 |
SET [override_id] = 0 |
7290 |
31 Jan 17 |
nicklas |
163 |
WHERE [override_id] IN (:deletedAnnotations) |
7290 |
31 Jan 17 |
nicklas |
164 |
OR [inherited_id] IN ( |
7290 |
31 Jan 17 |
nicklas |
165 |
SELECT [tmp].[id] FROM ( |
7290 |
31 Jan 17 |
nicklas |
166 |
SELECT [id] FROM [Annotations] |
7290 |
31 Jan 17 |
nicklas |
167 |
WHERE [override_id] IN (:deletedPrimaryAnnotations) |
7290 |
31 Jan 17 |
nicklas |
168 |
) [tmp] |
7290 |
31 Jan 17 |
nicklas |
169 |
) |
7290 |
31 Jan 17 |
nicklas |
170 |
</sql> |
7290 |
31 Jan 17 |
nicklas |
171 |
<description> |
7290 |
31 Jan 17 |
nicklas |
172 |
Reset the "override_id" column on project-specific annotations |
7290 |
31 Jan 17 |
nicklas |
173 |
that points to a default annotation that has been removed. |
7290 |
31 Jan 17 |
nicklas |
174 |
|
7290 |
31 Jan 17 |
nicklas |
175 |
To make this query work in MySQL we need a double inner subselect |
7290 |
31 Jan 17 |
nicklas |
176 |
to force a temporary table for the values we are after. |
7290 |
31 Jan 17 |
nicklas |
177 |
</description> |
7290 |
31 Jan 17 |
nicklas |
178 |
</query> |
7290 |
31 Jan 17 |
nicklas |
179 |
|
2959 |
27 Nov 06 |
nicklas |
180 |
</predefined-queries> |