Source code: org/hsqldb/DatabaseScript.java
1 /* Copyrights and Licenses
2 *
3 * This product includes Hypersonic SQL.
4 * Originally developed by Thomas Mueller and the Hypersonic SQL Group.
5 *
6 * Copyright (c) 1995-2000 by the Hypersonic SQL Group. All rights reserved.
7 * Redistribution and use in source and binary forms, with or without modification, are permitted
8 * provided that the following conditions are met:
9 * - Redistributions of source code must retain the above copyright notice, this list of conditions
10 * and the following disclaimer.
11 * - Redistributions in binary form must reproduce the above copyright notice, this list of
12 * conditions and the following disclaimer in the documentation and/or other materials
13 * provided with the distribution.
14 * - All advertising materials mentioning features or use of this software must display the
15 * following acknowledgment: "This product includes Hypersonic SQL."
16 * - Products derived from this software may not be called "Hypersonic SQL" nor may
17 * "Hypersonic SQL" appear in their names without prior written permission of the
18 * Hypersonic SQL Group.
19 * - Redistributions of any form whatsoever must retain the following acknowledgment: "This
20 * product includes Hypersonic SQL."
21 * This software is provided "as is" and any expressed or implied warranties, including, but
22 * not limited to, the implied warranties of merchantability and fitness for a particular purpose are
23 * disclaimed. In no event shall the Hypersonic SQL Group or its contributors be liable for any
24 * direct, indirect, incidental, special, exemplary, or consequential damages (including, but
25 * not limited to, procurement of substitute goods or services; loss of use, data, or profits;
26 * or business interruption). However caused any on any theory of liability, whether in contract,
27 * strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this
28 * software, even if advised of the possibility of such damage.
29 * This software consists of voluntary contributions made by many individuals on behalf of the
30 * Hypersonic SQL Group.
31 *
32 *
33 * For work added by the HSQL Development Group:
34 *
35 * Copyright (c) 2001-2002, The HSQL Development Group
36 * All rights reserved.
37 *
38 * Redistribution and use in source and binary forms, with or without
39 * modification, are permitted provided that the following conditions are met:
40 *
41 * Redistributions of source code must retain the above copyright notice, this
42 * list of conditions and the following disclaimer, including earlier
43 * license statements (above) and comply with all above license conditions.
44 *
45 * Redistributions in binary form must reproduce the above copyright notice,
46 * this list of conditions and the following disclaimer in the documentation
47 * and/or other materials provided with the distribution, including earlier
48 * license statements (above) and comply with all above license conditions.
49 *
50 * Neither the name of the HSQL Development Group nor the names of its
51 * contributors may be used to endorse or promote products derived from this
52 * software without specific prior written permission.
53 *
54 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
55 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
56 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
57 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
58 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
59 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
60 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
61 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
62 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
63 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
64 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
65 */
66
67
68 package org.hsqldb;
69
70 import java.sql.SQLException;
71 import java.sql.Types;
72 import java.util.Hashtable;
73 import java.util.Vector;
74 import java.util.Enumeration;
75
76 /**
77 * Script generation.
78 *
79 * @version 1.7.0
80 */
81 class DatabaseScript {
82
83 /**
84 * Method declaration
85 *
86 *
87 * @param bDrop
88 * @param bInsert
89 * @param bCached
90 * @param session
91 *
92 * @return
93 *
94 * @throws SQLException
95 */
96 static Result getScript(Database dDatabase, boolean bDrop,
97 boolean bInsert, boolean bCached,
98 Session session) throws SQLException {
99
100 Vector tTable = dDatabase.getTables();
101 Vector forwardFK = new Vector();
102 Vector forwardFKSource = new Vector();
103
104 session.checkAdmin();
105
106 Result r = new Result(1);
107
108 r.colType[0] = Types.VARCHAR;
109 r.sTable[0] = "SYSTEM_SCRIPT";
110 r.sLabel[0] = "COMMAND";
111 r.sName[0] = "COMMAND";
112
113 StringBuffer a;
114
115 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
116 Table t = (Table) tTable.elementAt(i);
117
118 // fredt@users 20020221 - patch 513005 by sqlbob@users (RMP)
119 if (t.isTemp() || t.isView()) {
120 continue;
121 }
122
123 if (bDrop) {
124 addRow(r, "DROP TABLE " + t.getName().statementName);
125 }
126
127 a = new StringBuffer(128);
128
129 getTableDDL(dDatabase, t, i, forwardFK, forwardFKSource, a);
130 addRow(r, a.toString());
131
132 for (int j = 1; j < t.getIndexCount(); j++) {
133 Index index = t.getIndex(j);
134
135 if (HsqlName.isReservedName(index.getName().name)) {
136
137 // the following are autocreated with the table
138 // indexes for primary keys
139 // indexes for unique constraints
140 // own table indexes for foreign keys
141 continue;
142 }
143
144 a = new StringBuffer(64);
145
146 a.append("CREATE ");
147
148 if (index.isUnique()) {
149 a.append("UNIQUE ");
150 }
151
152 a.append("INDEX ");
153 a.append(index.getName().statementName);
154 a.append(" ON ");
155 a.append(t.getName().statementName);
156
157 int col[] = index.getColumns();
158 int len = index.getVisibleColumns();
159
160 getColumnList(t, col, len, a);
161 addRow(r, a.toString());
162 }
163
164 if (t.isText() && t.isDataReadOnly()) {
165 a = new StringBuffer("SET TABLE ");
166
167 a.append(t.getName().statementName);
168 a.append(" READONLY TRUE");
169 addRow(r, a.toString());
170 }
171
172 // sqlbob@users 04/27/2002 Added data source support
173 String dataSource = getDataSource(t);
174
175 if (dataSource != null) {
176 addRow(r, dataSource);
177 }
178
179 // trigger script
180 int numTrigs = TriggerDef.numTrigs();
181
182 for (int tv = 0; tv < numTrigs; tv++) {
183 Vector trigVec = t.vTrigs[tv];
184 int trCount = trigVec.size();
185
186 for (int k = 0; k < trCount; k++) {
187 a = ((TriggerDef) trigVec.elementAt(k)).toBuf();
188
189 addRow(r, a.toString());
190 }
191 }
192 }
193
194 for (int i = 0, tSize = forwardFK.size(); i < tSize; i++) {
195 Constraint c = (Constraint) forwardFK.elementAt(i);
196
197 a = new StringBuffer(128);
198
199 a.append("ALTER TABLE ");
200 a.append(c.getRef().getName().statementName);
201 a.append(" ADD ");
202 getFKStatement(c, a);
203 addRow(r, a.toString());
204 }
205
206 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
207 Table t = (Table) tTable.elementAt(i);
208
209 if (bCached && t.isCached()) {
210 addRow(r, getIndexRootsDDL((Table) tTable.elementAt(i)));
211 }
212 }
213
214 Vector uv = dDatabase.getUserManager().getUsers();
215
216 for (int i = 0, vSize = uv.size(); i < vSize; i++) {
217 User u = (User) uv.elementAt(i);
218
219 // todo: this is not a nice implementation
220 if (u == null) {
221 continue;
222 }
223
224 String name = u.getName();
225
226 if (!name.equals("PUBLIC")) {
227 a = new StringBuffer(128);
228
229 a.append("CREATE USER ");
230 a.append(name);
231 a.append(" PASSWORD ");
232 a.append('"');
233 a.append(u.getPassword());
234 a.append('"');
235
236 if (u.isAdmin()) {
237 a.append(" ADMIN");
238 }
239
240 addRow(r, a.toString());
241 }
242
243 Hashtable rights = u.getRights();
244
245 if (rights == null) {
246 continue;
247 }
248
249 Enumeration e = rights.keys();
250
251 while (e.hasMoreElements()) {
252 String object = (String) e.nextElement();
253 int right = ((Integer) (rights.get(object))).intValue();
254
255 if (right == 0) {
256 continue;
257 }
258
259 a = new StringBuffer(64);
260
261 a.append("GRANT ");
262 a.append(UserManager.getRight(right));
263 a.append(" ON ");
264 a.append(object);
265 a.append(" TO ");
266 a.append(u.getName());
267 addRow(r, a.toString());
268 }
269 }
270
271 if (dDatabase.isIgnoreCase()) {
272 addRow(r, "SET IGNORECASE TRUE");
273 }
274
275 Hashtable h = dDatabase.getAlias();
276 Enumeration e = h.keys();
277
278 while (e.hasMoreElements()) {
279 String alias = (String) e.nextElement();
280 String java = (String) h.get(alias);
281 StringBuffer buffer = new StringBuffer(64);
282
283 buffer.append("CREATE ALIAS ");
284 buffer.append(alias);
285 buffer.append(" FOR \"");
286 buffer.append(java);
287 buffer.append('"');
288 addRow(r, buffer.toString());
289 }
290
291 // fredt@users 20020420 - patch523880 by leptipre@users - VIEW support
292 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
293 Table t = (Table) tTable.elementAt(i);
294
295 if (t.isView()) {
296 View v = (View) tTable.elementAt(i);
297
298 if (bDrop) {
299 addRow(r, "DROP VIEW " + v.getName().name);
300 }
301
302 a = new StringBuffer(128);
303
304 a.append("CREATE ");
305 a.append("VIEW ");
306 a.append(v.getName().statementName);
307 a.append(" AS ");
308 a.append(v.getStatement());
309 addRow(r, a.toString());
310 }
311 }
312
313 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
314 Table t = (Table) tTable.elementAt(i);
315
316 if (bInsert == false || t.isTemp() || t.isView()
317 || (t.isCached &&!bCached)
318 || (t.isText() && t.isDataReadOnly())) {
319 continue;
320 }
321
322 Index primary = t.getPrimaryIndex();
323 Node x = primary.first();
324 boolean integrity = true;
325
326 if (x != null) {
327 integrity = false;
328
329 // fredt@users - comment - is this necessary?
330 // tables are in order and no rows break FK constraints
331 // addRow(r, "SET REFERENTIAL_INTEGRITY FALSE");
332 }
333
334 while (x != null) {
335 addRow(r, t.getInsertStatement(x.getData()));
336
337 x = primary.next(x);
338 }
339
340 /*
341 if (!integrity) {
342 addRow(r, "SET REFERENTIAL_INTEGRITY TRUE");
343 }
344 */
345
346 // fredt@users 20020221 - patch 513005 by sqlbob@users (RMP)
347 if (t.isDataReadOnly()) {
348 a = new StringBuffer("SET TABLE ");
349
350 a.append(t.getName().statementName);
351 a.append(" READONLY TRUE");
352 addRow(r, a.toString());
353 }
354 }
355
356 return r;
357 }
358
359 static String getIndexRootsDDL(Table t) throws SQLException {
360
361 StringBuffer a = new StringBuffer(128);
362
363 a.append("SET TABLE ");
364 a.append(t.getName().statementName);
365 a.append(" INDEX '");
366 a.append(t.getIndexRoots());
367 a.append('\'');
368
369 return a.toString();
370 }
371
372 static void getTableDDL(Database dDatabase, Table t, int i,
373 Vector forwardFK, Vector forwardFKSource,
374 StringBuffer a) throws SQLException {
375
376 a.append("CREATE ");
377
378 // fredt@users 20020221 - patch 513005 by sqlbob@users (RMP)
379 // required for Text Table support
380 if (t.isText()) {
381 a.append("TEXT ");
382 } else if (t.isCached()) {
383 a.append("CACHED ");
384 }
385
386 a.append("TABLE ");
387 a.append(t.getName().statementName);
388 a.append('(');
389
390 int columns = t.getColumnCount();
391 Index pki = t.getIndex(0);
392 int pk[] = pki.getColumns();
393
394 for (int j = 0; j < columns; j++) {
395 Column column = t.getColumn(j);
396 String colname = column.columnName.statementName;
397
398 a.append(colname);
399 a.append(' ');
400
401 // fredt@users 20020130 - patch 491987 by jimbag@users
402 String sType = Column.getTypeString(column.getType());
403
404 a.append(sType);
405
406 // append the size and scale if > 0
407 if (column.getSize() > 0) {
408 a.append('(');
409 a.append(column.getSize());
410
411 if (column.getScale() > 0) {
412 a.append(',');
413 a.append(column.getScale());
414 }
415
416 a.append(')');
417 }
418
419 // fredt@users 20020218 - patch 1.7.0 by fredt - DEFAULT keyword
420 if (column.getDefaultString() != null) {
421 a.append(" DEFAULT ");
422 a.append(Column.createSQLString(column.getDefaultString()));
423 }
424
425 if (!column.isNullable()) {
426 a.append(" NOT NULL");
427 }
428
429 if (j == t.getIdentityColumn()) {
430 a.append(" IDENTITY");
431 }
432
433 if ((pk.length == 1) && (j == pk[0])) {
434 a.append(" PRIMARY KEY");
435 }
436
437 if (j < columns - 1) {
438 a.append(',');
439 }
440 }
441
442 if (pk.length > 1) {
443 a.append(",CONSTRAINT ");
444 a.append(pki.getName().statementName);
445 a.append(" PRIMARY KEY");
446 getColumnList(t, pk, pk.length, a);
447 }
448
449 Vector v = t.getConstraints();
450
451 for (int j = 0, vSize = v.size(); j < vSize; j++) {
452 Constraint c = (Constraint) v.elementAt(j);
453
454 if (c.getType() == Constraint.UNIQUE) {
455 a.append(",CONSTRAINT ");
456 a.append(c.getName().statementName);
457 a.append(" UNIQUE");
458
459 int col[] = c.getMainColumns();
460
461 getColumnList(c.getMain(), col, col.length, a);
462 } else if (c.getType() == Constraint.FOREIGN_KEY) {
463
464 // forward referencing FK
465 Table maintable = c.getMain();
466 int maintableindex = dDatabase.getTableIndex(maintable);
467
468 if (maintableindex > i) {
469 if (i >= forwardFKSource.size()) {
470 forwardFKSource.setSize(i + 1);
471 }
472
473 forwardFKSource.setElementAt(c, i);
474 forwardFK.addElement(c);
475 } else {
476 a.append(',');
477 getFKStatement(c, a);
478 }
479 }
480 }
481
482 a.append(')');
483 }
484
485 /**
486 * Method declaration
487 *
488 *
489 * @param t
490 *
491 * @return
492 */
493 static String getDataSource(Table t) throws SQLException {
494
495 String dataSource = t.getDataSource();
496
497 if (dataSource == null) {
498 return null;
499 }
500
501 boolean isDesc = t.isDescDataSource();
502 StringBuffer a = new StringBuffer(128);
503
504 a.append("SET TABLE ");
505 a.append(t.getName().statementName);
506 a.append(" SOURCE \"");
507 a.append(dataSource);
508 a.append('"');
509
510 if (isDesc) {
511 a.append(" DESC");
512 }
513
514 return a.toString();
515 }
516
517 /**
518 * Method declaration
519 *
520 *
521 * @param t
522 * @param col
523 * @param len
524 * @param a
525 *
526 * @return
527 */
528 private static void getColumnList(Table t, int col[], int len,
529 StringBuffer a) {
530
531 a.append('(');
532
533 for (int i = 0; i < len; i++) {
534 a.append(t.getColumn(col[i]).columnName.statementName);
535
536 if (i < len - 1) {
537 a.append(',');
538 }
539 }
540
541 a.append(')');
542 }
543
544 /**
545 * Method declaration
546 *
547 *
548 * @param c
549 * @param a
550 *
551 * @return
552 */
553 private static void getFKStatement(Constraint c, StringBuffer a) {
554
555 a.append("CONSTRAINT ");
556 a.append(c.getName().statementName);
557 a.append(" FOREIGN KEY");
558
559 int col[] = c.getRefColumns();
560
561 getColumnList(c.getRef(), col, col.length, a);
562 a.append(" REFERENCES ");
563 a.append(c.getMain().getName().statementName);
564
565 col = c.getMainColumns();
566
567 getColumnList(c.getMain(), col, col.length, a);
568
569 if (c.isCascade()) {
570 a.append(" ON DELETE CASCADE");
571 }
572 }
573
574 /**
575 * Method declaration
576 *
577 *
578 * @param r
579 * @param sql
580 */
581 private static void addRow(Result r, String sql) {
582
583 String s[] = new String[1];
584
585 s[0] = sql;
586
587 r.add(s);
588 }
589 }