001: package net.sourceforge.squirrel_sql.plugins.mssql.util;
002:
003: /*
004: * Copyright (C) 2004 Ryan Walberg <generalpf@yahoo.com>
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation; either
009: * version 2.1 of the License, or (at your option) any later version.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: *
016: * You should have received a copy of the GNU Lesser General Public
017: * License along with this library; if not, write to the Free Software
018: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: */
020:
021: import java.sql.CallableStatement;
022: import java.sql.Connection;
023: import java.sql.ResultSet;
024: import java.util.List;
025:
026: import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
027: import net.sourceforge.squirrel_sql.fw.sql.IProcedureInfo;
028: import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
029: import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
030: import net.sourceforge.squirrel_sql.fw.sql.IUDTInfo;
031: import net.sourceforge.squirrel_sql.plugins.mssql.sql.constraint.CheckConstraint;
032: import net.sourceforge.squirrel_sql.plugins.mssql.sql.constraint.DefaultConstraint;
033: import net.sourceforge.squirrel_sql.plugins.mssql.sql.constraint.ForeignKeyConstraint;
034: import net.sourceforge.squirrel_sql.plugins.mssql.sql.constraint.PrimaryKeyConstraint;
035: import net.sourceforge.squirrel_sql.plugins.mssql.sql.constraint.TableConstraints;
036: import net.sourceforge.squirrel_sql.plugins.mssql.sql.dbfile.DatabaseFile;
037: import net.sourceforge.squirrel_sql.plugins.mssql.sql.dbfile.DatabaseFileInfo;
038:
039: public class MssqlIntrospector {
040:
041: public final static int MSSQL_TABLE = 1;
042: public final static int MSSQL_VIEW = 2;
043: public final static int MSSQL_STOREDPROCEDURE = 3;
044: public final static int MSSQL_UDF = 4;
045: public final static int MSSQL_UDT = 5;
046: public final static int MSSQL_RULE = 6;
047: public final static int MSSQL_DEFAULT = 7;
048:
049: public final static int MSSQL_UNKNOWN = -1;
050:
051: public static TableConstraints getTableConstraints(
052: IDatabaseObjectInfo oi, ISQLConnection conn)
053: throws java.sql.SQLException {
054: TableConstraints constraints = new TableConstraints();
055:
056: Connection c = conn.getConnection();
057:
058: CallableStatement stmt = c
059: .prepareCall("{ call sp_helpconstraint ?, ? }");
060: stmt.setString(1, oi.getSimpleName());
061: stmt.setString(2, "nomsg");
062: ResultSet rs;
063:
064: try {
065: rs = stmt.executeQuery();
066: } catch (java.sql.SQLException ex) {
067: // probably just no results -- return it empty.
068: return constraints;
069: }
070:
071: while (rs.next()) {
072: String constraintType = rs.getString(1);
073: String constraintName = rs.getString(2);
074: //String deleteAction = rs.getString(3);
075: //String updateAction = rs.getString(4);
076: //String statusEnabled = rs.getString(5);
077: //String statusForReplication = rs.getString(6);
078: String constraintKeys = rs.getString(7);
079:
080: if (constraintType.startsWith("DEFAULT")) {
081: DefaultConstraint def = new DefaultConstraint();
082: String col = constraintType.substring(18).trim(); // chop off "DEFAULT on column ";
083:
084: def.setConstraintName(constraintName);
085: def.addConstraintColumn(col);
086: def.setDefaultExpression(constraintKeys);
087:
088: constraints.addConstraint(def);
089: } else if (constraintType.startsWith("CHECK")) {
090: CheckConstraint check = new CheckConstraint();
091: String col = constraintType.substring(16).trim(); // chop off "CHECK on column ";
092:
093: check.setConstraintName(constraintName);
094: check.addConstraintColumn(col);
095: check.setCheckExpression(constraintKeys);
096:
097: constraints.addConstraint(check);
098: } else if (constraintType.startsWith("FOREIGN KEY")) {
099: /* NOTE: there are two rows.
100: * NOTE: MssqlConstraint holds the columns in the table participating in the key.
101: * NOTE: ForeignKeyConstraint holds the columns in the referenced table IN THE SAME ORDER.
102: */
103: ForeignKeyConstraint fk = new ForeignKeyConstraint();
104:
105: fk.setConstraintName(constraintName);
106:
107: String foreignColumns[] = constraintKeys.split(", ");
108: for (int i = 0; i < foreignColumns.length; i++)
109: fk.addConstraintColumn(foreignColumns[i]);
110:
111: rs.next();
112:
113: constraintKeys = rs.getString(7);
114: // constraintKeys looks like this --> `REFERENCES pubs.dbo.foo (fooid, quuxid)'
115: constraintKeys = constraintKeys.substring(11); // chop off "REFERENCES "
116: String[] tableAndColumns = constraintKeys.split(" ", 2);
117: // now tableAndColumns[0] contains the table name and tableAndColumns[1] contains
118: // the bracketed list of columns.
119: fk.setReferencedTable(tableAndColumns[0]);
120: String primaryColumns[] = tableAndColumns[1].substring(
121: 1, tableAndColumns[1].length() - 2).split(",");
122: for (int i = 0; i < primaryColumns.length; i++)
123: fk.addPrimaryColumn(primaryColumns[i]);
124:
125: constraints.addConstraint(fk);
126: } else if (constraintType.startsWith("PRIMARY KEY")) {
127: PrimaryKeyConstraint pk = new PrimaryKeyConstraint();
128:
129: pk.setConstraintName(constraintName);
130: pk.setClustered(constraintType.endsWith("(clustered)"));
131:
132: String cols[] = constraintKeys.split(", ");
133: for (int i = 0; i < cols.length; i++)
134: pk.addConstraintColumn(cols[i]);
135:
136: constraints.addConstraint(pk);
137: }
138: }
139:
140: return constraints;
141: }
142:
143: public static DatabaseFileInfo getDatabaseFileInfo(
144: String catalogName, ISQLConnection conn)
145: throws java.sql.SQLException {
146: DatabaseFileInfo dbInfo = new DatabaseFileInfo();
147:
148: Connection c = conn.getConnection();
149:
150: CallableStatement stmt = c.prepareCall("{ call sp_helpdb ? }");
151: stmt.setString(1, catalogName);
152: ResultSet rs;
153:
154: if (!stmt.execute())
155: return null;
156: rs = stmt.getResultSet();
157: rs.next();
158:
159: dbInfo.setDatabaseName(rs.getString(1));
160: dbInfo.setDatabaseSize(rs.getString(2));
161: dbInfo.setOwner(rs.getString(3));
162: dbInfo.setCreatedDate(rs.getString(5));
163: String[] options = rs.getString(6).split(", ");
164: dbInfo.setCompatibilityLevel(rs.getShort(7));
165:
166: // dbStatus -> `Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistic'
167: for (int i = 0; i < options.length; i++) {
168: if (options[i].indexOf('=') != -1) {
169: String parts[] = options[i].split("=");
170: dbInfo.setOption(parts[0], parts[1]);
171: } else
172: dbInfo.setOption(options[i], "1");
173: }
174:
175: if (!stmt.getMoreResults())
176: return dbInfo;
177:
178: rs = stmt.getResultSet();
179:
180: while (rs.next()) {
181: String name = rs.getString(1).trim();
182: short id = rs.getShort(2);
183: String filename = rs.getString(3).trim();
184: String filegroup = rs.getString(4);
185: String size = rs.getString(5);
186: String maxSize = rs.getString(6);
187: String growth = rs.getString(7);
188: String usage = rs.getString(8);
189:
190: DatabaseFile file = new DatabaseFile();
191: file.setName(name);
192: file.setId(id);
193: file.setFilename(filename);
194: file.setFilegroup(filegroup);
195: file.setSize(size);
196: file.setMaxSize(maxSize);
197: file.setGrowth(growth);
198: file.setUsage(usage);
199:
200: if (filegroup == null)
201: dbInfo.addLogFile(file);
202: else
203: dbInfo.addDataFile(file);
204: }
205:
206: return dbInfo;
207: }
208:
209: public static int getObjectInfoType(IDatabaseObjectInfo oi) {
210: if (oi instanceof ITableInfo) {
211: String tableType = ((ITableInfo) oi).getType();
212: if (tableType.equals("TABLE"))
213: return MSSQL_TABLE;
214: else if (tableType.equals("VIEW"))
215: return MSSQL_VIEW;
216: else
217: return MSSQL_UNKNOWN;
218: } else if (oi instanceof IProcedureInfo) {
219: /* i do believe the getSimpleName() will end in ;1 if it's a procedure
220: * and ;0 if it's a UDF. */
221: String simpleName = oi.getSimpleName();
222: if (simpleName.endsWith(";0"))
223: return MSSQL_UDF;
224: else if (simpleName.endsWith(";1"))
225: return MSSQL_STOREDPROCEDURE;
226: else
227: return MSSQL_UNKNOWN;
228: } else if (oi instanceof IUDTInfo) {
229: return MSSQL_UDT;
230: } else
231: return MSSQL_UNKNOWN;
232: }
233:
234: public static String generateCreateScript(IDatabaseObjectInfo oi,
235: ISQLConnection conn, boolean withConstraints)
236: throws java.sql.SQLException {
237: StringBuffer buf = new StringBuffer();
238:
239: if (getObjectInfoType(oi) == MSSQL_TABLE)
240: buf.append(MssqlIntrospector.generateCreateTableScript(oi,
241: conn, withConstraints));
242: else {
243: Connection c = conn.getConnection();
244: buf
245: .append(getHelpTextForObject(MssqlIntrospector
246: .getFixedVersionedObjectName(oi
247: .getSimpleName()), c));
248: }
249:
250: buf.append("GO\n\n");
251: return buf.toString();
252: }
253:
254: public static String getHelpTextForObject(String objectName,
255: Connection c) throws java.sql.SQLException {
256: StringBuffer buf = new StringBuffer();
257:
258: CallableStatement stmt = c
259: .prepareCall("{ call sp_helptext (?) }");
260: stmt.setString(1, objectName);
261:
262: ResultSet helpText = stmt.executeQuery();
263:
264: while (helpText.next()) {
265: buf.append(helpText.getString(1));
266: }
267: return buf.toString();
268: }
269:
270: public static String generateCreateDatabaseScript(
271: String catalogName, ISQLConnection conn)
272: throws java.sql.SQLException {
273: StringBuffer buf = new StringBuffer();
274:
275: DatabaseFileInfo dbInfo = MssqlIntrospector
276: .getDatabaseFileInfo(catalogName, conn);
277: Object[] dataFiles = dbInfo.getDataFiles();
278: Object[] logFiles = dbInfo.getLogFiles();
279:
280: buf.append("CREATE DATABASE [");
281: buf.append(dbInfo.getDatabaseName());
282: buf.append("]\nON ");
283:
284: String lastFilegroup = "";
285: for (int i = 0; i < dataFiles.length; i++) {
286: DatabaseFile file = (DatabaseFile) dataFiles[i];
287:
288: String this Filegroup = file.getFilegroup();
289: if (!this Filegroup.equals(lastFilegroup)) {
290: // if it's PRIMARY, just write it without the FILEGROUP prefix.
291: if (this Filegroup.equals("PRIMARY"))
292: buf.append("PRIMARY");
293: else {
294: buf.append("FILEGROUP ");
295: buf.append(this Filegroup);
296: }
297: buf.append("\n");
298: lastFilegroup = this Filegroup;
299: }
300:
301: buf.append("( NAME = ");
302: buf.append(file.getName());
303: buf.append(",\n\tFILENAME = '");
304: buf.append(file.getFilename());
305: buf.append("',\n\tSIZE = ");
306: buf.append(file.getSize());
307: if (!file.getMaxSize().equals("Unlimited")) {
308: buf.append(",\n\tMAXSIZE = ");
309: buf.append(file.getMaxSize());
310: }
311: buf.append(",\n\tFILEGROWTH = ");
312: buf.append(file.getGrowth());
313: buf.append(" )");
314:
315: if (i < dataFiles.length - 1)
316: buf.append(",");
317: buf.append("\n");
318: }
319:
320: buf.append("LOG ON\n");
321: for (int i = 0; i < logFiles.length; i++) {
322: DatabaseFile file = (DatabaseFile) logFiles[i];
323:
324: buf.append("( NAME = ");
325: buf.append(file.getName());
326: buf.append(",\n\tFILENAME = '");
327: buf.append(file.getFilename());
328: buf.append("',\n\tSIZE = ");
329: buf.append(file.getSize());
330: if (!file.getMaxSize().equals("Unlimited")) {
331: buf.append(",\n\tMAXSIZE = ");
332: buf.append(file.getMaxSize());
333: }
334: buf.append(",\n\tFILEGROWTH = ");
335: buf.append(file.getGrowth());
336: buf.append(" )");
337:
338: if (i < logFiles.length - 1)
339: buf.append(",");
340:
341: buf.append("\n");
342: }
343:
344: buf.append("GO\n\n");
345:
346: return buf.toString();
347: }
348:
349: public static String generateCreateIndexesScript(
350: IDatabaseObjectInfo oi, ISQLConnection conn)
351: throws java.sql.SQLException {
352: Connection c = conn.getConnection();
353:
354: StringBuffer buf = new StringBuffer();
355:
356: CallableStatement stmt = c
357: .prepareCall("{ call sp_helpindex ? }");
358: stmt.setString(1, oi.getSimpleName());
359: ResultSet rs;
360:
361: try {
362: rs = stmt.executeQuery();
363: } catch (java.sql.SQLException e) {
364: // no indexes, i guess.
365: return "";
366: }
367:
368: while (rs.next()) {
369: String indexName = rs.getString(1);
370: // `clustered, unique, primary key located on PRIMARY'
371: String[] info = rs.getString(2).split(" located on ");
372: String[] keys = rs.getString(3).split(", ");
373: String[] attribs = info[0].split(", ");
374: boolean isUnique = false;
375: boolean isClustered = false;
376: for (int i = 0; i < attribs.length; i++) {
377: if (attribs[i].equals("clustered"))
378: isClustered = true;
379: else if (attribs[i].equals("unique"))
380: isUnique = true;
381: }
382:
383: buf.append("CREATE ");
384: if (isUnique)
385: buf.append("UNIQUE ");
386: buf.append(isClustered ? "CLUSTERED " : "NONCLUSTERED ");
387: buf.append("INDEX [");
388: buf.append(indexName);
389: buf.append("]\n\tON [");
390: buf.append(oi.getSimpleName());
391: buf.append("] (");
392: for (int i = 0; i < keys.length; i++) {
393: boolean isDesc = false;
394: String keyName = keys[i];
395: if (keyName.endsWith("(-)")) {
396: isDesc = true;
397: keyName = keyName
398: .substring(0, keyName.length() - 3);
399: }
400: buf.append(keyName);
401: if (isDesc)
402: buf.append(" DESC");
403: if (i < keys.length - 1)
404: buf.append(", ");
405: }
406: buf.append(")\n\tON [");
407: buf.append(info[1]);
408: buf.append("]\nGO\n\n");
409: }
410:
411: return buf.toString();
412: }
413:
414: public static String generateCreateTriggersScript(
415: IDatabaseObjectInfo oi, ISQLConnection conn)
416: throws java.sql.SQLException {
417: Connection c = conn.getConnection();
418:
419: StringBuffer buf = new StringBuffer();
420:
421: CallableStatement stmt = c
422: .prepareCall("{ call sp_helptrigger ? }");
423: stmt.setString(1, oi.getSimpleName());
424: ResultSet rs;
425:
426: try {
427: rs = stmt.executeQuery();
428: } catch (java.sql.SQLException e) {
429: // no triggers, i guess.
430: return "";
431: }
432:
433: while (rs.next()) {
434: String triggerName = rs.getString(1);
435: buf.append(MssqlIntrospector.getHelpTextForObject(
436: triggerName, c));
437: buf.append("\nGO\n\n");
438: }
439:
440: return buf.toString();
441: }
442:
443: public static String generatePermissionsScript(
444: IDatabaseObjectInfo oi, ISQLConnection conn)
445: throws java.sql.SQLException {
446: Connection c = conn.getConnection();
447:
448: StringBuffer buf = new StringBuffer();
449:
450: CallableStatement stmt = c
451: .prepareCall("{ call sp_helprotect ? }");
452: stmt.setString(1, MssqlIntrospector
453: .getFixedVersionedObjectName(oi.getSimpleName()));
454: ResultSet rs;
455:
456: try {
457: rs = stmt.executeQuery();
458: } catch (java.sql.SQLException e) {
459: // no permissions, i guess.
460: return "";
461: }
462:
463: while (rs.next()) {
464: /*
465: Owner Object Grantee Grantor ProtectType Action Column
466: ------ ---------------------- ------------------ ------- ----------- ------ ------------------
467: dbo billing_bak public dbo Grant Delete .
468: dbo billing_bak public dbo Grant Insert .
469: dbo billing_bak public dbo Grant Select (All+New)
470: dbo billing_bak public dbo Grant Update (All+New)
471: dbo billing_bak usbilling dbo Deny Insert .
472: */
473: //String owner = rs.getString(1);
474: String grantee = rs.getString(3);
475: //String grantor = rs.getString(4);
476: String protectType = rs.getString(5).trim();
477: String action = rs.getString(6);
478: //String column = rs.getString(7);
479:
480: /*
481: GRANT
482: { ALL [ PRIVILEGES ] | permission [ ,...n ] }
483: {
484: [ ( column [ ,...n ] ) ] ON { table | view }
485: | ON { table | view } [ ( column [ ,...n ] ) ]
486: | ON { stored_procedure | extended_procedure }
487: | ON { user_defined_function }
488: }
489: TO security_account [ ,...n ]
490: [ WITH GRANT OPTION ]
491: [ AS { group | role } ]
492:
493: REVOKE [ GRANT OPTION FOR ]
494: { ALL [ PRIVILEGES ] | permission [ ,...n ] }
495: {
496: [ ( column [ ,...n ] ) ] ON { table | view }
497: | ON { table | view } [ ( column [ ,...n ] ) ]
498: | ON { stored_procedure | extended_procedure }
499: | ON { user_defined_function }
500: }
501: { TO | FROM }
502: security_account [ ,...n ]
503: [ CASCADE ]
504: [ AS { group | role } ]
505: */
506:
507: if (protectType.equals("Grant"))
508: buf.append("GRANT ");
509: else if (protectType.equals("Deny"))
510: buf.append("REVOKE ");
511: buf.append(action.toUpperCase());
512: buf.append(" ON [");
513: buf.append(MssqlIntrospector.getFixedVersionedObjectName(oi
514: .getSimpleName()));
515: buf.append("] ");
516: if (protectType.equals("Grant"))
517: buf.append("TO ");
518: else if (protectType.equals("Deny"))
519: buf.append("FROM ");
520: buf.append(grantee);
521:
522: buf.append("\nGO\n\n");
523: }
524:
525: return buf.toString();
526: }
527:
528: protected static String generateCreateTableScript(
529: IDatabaseObjectInfo oi, ISQLConnection conn,
530: boolean withConstraints) throws java.sql.SQLException {
531: Connection c = conn.getConnection();
532:
533: StringBuffer buf = new StringBuffer();
534:
535: TableConstraints constraints = MssqlIntrospector
536: .getTableConstraints(oi, conn);
537:
538: CallableStatement stmt = c.prepareCall("{ call sp_help ? }");
539: stmt.setString(1, oi.getSimpleName());
540: ResultSet rs;
541:
542: if (!stmt.execute())
543: return null;
544:
545: /* since .execute() returned true, the first result is a ResultSet. */
546: rs = stmt.getResultSet();
547: /* Name Owner Type Created_datetime
548: * ---------------------------------------------------------
549: * billing dbo user table 2004-03-08 10:41:05.030
550: */
551: if (!rs.next())
552: return null;
553: buf.append("CREATE TABLE [");
554: buf.append(rs.getString(2));
555: buf.append("].[");
556: buf.append(rs.getString(1));
557: buf.append("] (");
558: buf.append("\n");
559:
560: if (!stmt.getMoreResults())
561: return null;
562: rs = stmt.getResultSet();
563: /* Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
564: * -------------------------------------------------------------------------------------------------------------------------------------------------------
565: * Location char no 2 yes no yes Latin1_General_CI_AS
566: * TotalBilledAmnt money no 8 19 4 yes (n/a) (n/a) NULL
567: */
568: while (rs.next()) {
569: String colName = rs.getString(1);
570: String colType = rs.getString(2);
571: buf.append("\t[");
572: buf.append(colName);
573: buf.append("] [");
574: buf.append(colType);
575: buf.append("] ");
576: if (colType.equals("char") || colType.equals("varchar")) {
577: buf.append("(");
578: buf.append(rs.getInt(4)); // length
579: buf.append(") COLLATE ");
580: buf.append(rs.getString(10)); // collation
581: buf.append(" ");
582: }
583: if (rs.getString(7).equals("yes"))
584: buf.append("NULL ");
585: else
586: buf.append("NOT NULL ");
587:
588: if (withConstraints) {
589: List<DefaultConstraint> defs = constraints
590: .getDefaultsForColumn(colName);
591: /* there can be only one default in truth, but the model allows more than one. */
592:
593: if (defs != null && defs.size() == 1) {
594: DefaultConstraint def = defs.get(0);
595: buf.append("CONSTRAINT [");
596: buf.append(def.getConstraintName());
597: buf.append("] DEFAULT ");
598: buf.append(def.getDefaultExpression());
599: buf.append(" ");
600: }
601: }
602:
603: buf.append(",\n");
604: }
605:
606: if (withConstraints) {
607: /* there can be only one PK in truth, but the model allows more than one. */
608: List<PrimaryKeyConstraint> pks = constraints
609: .getPrimaryKeyConstraints();
610: if (pks != null && pks.size() == 1) {
611: PrimaryKeyConstraint pk = pks.get(0);
612: buf.append("\tCONSTRAINT [");
613: buf.append(pk.getConstraintName());
614: buf.append("] PRIMARY KEY ");
615: buf.append(pk.isClustered() ? "CLUSTERED"
616: : "NONCLUSTERED");
617: buf.append("\n\t(\n\t\t");
618: Object[] cols = pk.getConstraintColumns();
619: for (int i = 0; i < cols.length; i++) {
620: buf.append("[");
621: buf.append((String) cols[i]);
622: buf.append("]");
623: if (i < cols.length - 1)
624: buf.append(", ");
625: }
626: buf.append("\n\t)\n");
627: /* TODO: FILLFACTOR, ON [PRIMARY], etc. */
628: }
629:
630: List<ForeignKeyConstraint> fks = constraints
631: .getForeignKeyConstraints();
632: for (int i = 0; i < fks.size(); i++) {
633: ForeignKeyConstraint fk = fks.get(i);
634: buf.append("\tFOREIGN KEY\n\t(\n\t\t");
635: Object[] foreignColumns = fk.getConstraintColumns();
636: for (int j = 0; j < foreignColumns.length; j++) {
637: buf.append("[");
638: buf.append((String) foreignColumns[j]);
639: buf.append("]");
640: if (j < foreignColumns.length - 1)
641: buf.append(", ");
642: }
643: buf.append("\n\t) REFERENCES [");
644: buf.append(fk.getReferencedTable());
645: buf.append("] (\n\t\t");
646: Object[] primaryColumns = fk.getPrimaryColumns();
647: for (int j = 0; j < primaryColumns.length; j++) {
648: buf.append("[");
649: buf.append((String) primaryColumns[j]);
650: buf.append("]");
651: if (j < primaryColumns.length - 1)
652: buf.append(",\n");
653: }
654: buf.append("\n\t),");
655: }
656:
657: for (CheckConstraint check : constraints
658: .getCheckConstraints()) {
659: buf.append("\tCONSTRAINT [");
660: buf.append(check.getConstraintName());
661: buf.append("] CHECK ");
662: buf.append(check.getCheckExpression());
663: buf.append(",\n");
664: }
665: }
666:
667: buf.append(")\n");
668: /* TODO: ON [PRIMARY] */
669:
670: return buf.toString();
671: }
672:
673: @SuppressWarnings("unused")
674: public static String generateUsersAndRolesScript(
675: String catalogName, ISQLConnection conn)
676: throws java.sql.SQLException {
677: StringBuffer buf = new StringBuffer();
678:
679: Connection c = conn.getConnection();
680:
681: CallableStatement stmt = c.prepareCall("{ call sp_helpuser }");
682: ResultSet rs = stmt.executeQuery();
683:
684: while (rs.next()) {
685: String userName = rs.getString(1);
686: String loginName = rs.getString(3);
687:
688: if (userName.equals("dbo"))
689: continue;
690:
691: buf
692: .append("if not exists (select * from dbo.sysusers where name = N'");
693: buf.append(userName);
694: buf
695: .append("' and uid < 16382)\n\tEXEC sp_grantdbaccess N'");
696: buf.append(loginName);
697: buf.append("', N'");
698: buf.append(userName);
699: buf.append("'\nGO\n\n");
700: }
701:
702: stmt = c.prepareCall("{ call sp_helprole }");
703: rs = stmt.executeQuery();
704:
705: while (rs.next()) {
706: String roleName = rs.getString(1);
707: short roleId = rs.getShort(2);
708:
709: if (roleId < 16400)
710: continue;
711:
712: buf
713: .append("if not exists (select * from dbo.sysusers where name = N'");
714: buf.append(roleName);
715: buf.append("' and uid > 16399)\n\tEXEC sp_addrole N'");
716: buf.append(roleName);
717: buf.append("'\nGO\n\n");
718:
719: /* add users to the role. */
720: CallableStatement userStmt = c
721: .prepareCall("{ call sp_helprolemember ? }");
722: userStmt.setString(1, roleName);
723: ResultSet userRs = userStmt.executeQuery();
724:
725: while (userRs.next()) {
726: String userInRole = userRs.getString(2);
727: buf.append("exec sp_addrolemember N'");
728: buf.append(roleName);
729: buf.append("', N'");
730: buf.append(userInRole);
731: buf.append("'\nGO\n\n");
732: }
733: }
734:
735: return buf.toString();
736: }
737:
738: public static String generateDropScript(IDatabaseObjectInfo oi) {
739: StringBuffer buf = new StringBuffer();
740: String useThisName;
741: int objectType = MssqlIntrospector.getObjectInfoType(oi);
742: // stored procedures and functions have that dangling ;version thing.
743: if (objectType == MSSQL_STOREDPROCEDURE
744: || objectType == MSSQL_UDF)
745: useThisName = oi.getSimpleName().split(";")[0];
746: else
747: useThisName = oi.getSimpleName();
748:
749: buf
750: .append("IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID('");
751: buf.append(oi.getSchemaName());
752: buf.append(".");
753: buf.append(useThisName);
754: buf.append("') )\n\tDROP ");
755: switch (objectType) {
756: case MssqlIntrospector.MSSQL_TABLE:
757: buf.append("TABLE");
758: break;
759: case MssqlIntrospector.MSSQL_VIEW:
760: buf.append("VIEW");
761: break;
762: case MssqlIntrospector.MSSQL_UDF:
763: buf.append("FUNCTION");
764: break;
765: case MssqlIntrospector.MSSQL_STOREDPROCEDURE:
766: buf.append("PROCEDURE");
767: break;
768: }
769: buf.append(" ");
770: buf.append(useThisName);
771: buf.append("\nGO\n\n");
772:
773: return buf.toString();
774: }
775:
776: public static String getFixedVersionedObjectName(String objectName) {
777: String[] parts = objectName.split(";");
778: return parts[0];
779: }
780:
781: public static String formatDataType(String dataType,
782: short dataLength, int dataPrec, int dataScale) {
783: StringBuffer buf = new StringBuffer();
784:
785: if (dataType.endsWith("char")) {
786: buf.append(dataType);
787: buf.append("(");
788: buf.append(dataLength);
789: buf.append(")");
790: } else if (dataType.equals("float")) {
791: buf.append(dataType);
792: buf.append("(");
793: buf.append(dataPrec);
794: buf.append(")");
795: } else if (dataType.equals("decimal")
796: || dataType.equals("numeric")) {
797: buf.append(dataType);
798: buf.append("(");
799: buf.append(dataPrec);
800: buf.append(",");
801: buf.append(dataScale);
802: buf.append(")");
803: } else
804: buf.append(dataType);
805:
806: return buf.toString();
807: }
808:
809: }
|