001: package net.sourceforge.squirrel_sql.client.session.schemainfo;
002:
003: import java.sql.DatabaseMetaData;
004: import java.util.HashSet;
005: import java.util.Set;
006: import java.util.regex.Matcher;
007: import java.util.regex.Pattern;
008:
009: import javax.swing.SwingUtilities;
010:
011: import net.sourceforge.squirrel_sql.client.gui.session.BaseSessionInternalFrame;
012: import net.sourceforge.squirrel_sql.client.gui.session.SQLInternalFrame;
013: import net.sourceforge.squirrel_sql.client.gui.session.SessionInternalFrame;
014: import net.sourceforge.squirrel_sql.client.session.ISQLEntryPanel;
015: import net.sourceforge.squirrel_sql.client.session.ISession;
016: import net.sourceforge.squirrel_sql.client.session.SQLExecutionInfo;
017: import net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectInfo;
018: import net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType;
019: import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
020: import net.sourceforge.squirrel_sql.fw.sql.ProcedureInfo;
021: import net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData;
022: import net.sourceforge.squirrel_sql.fw.sql.TableInfo;
023:
024: /**
025: * This class tries to update SchemaInfo after standard CREATE/ALTER statements.
026: * This way Syntax highlighting and code completion are available just after
027: * CREATE/ALTER statements were send to the DB.
028: */
029: public class SchemaInfoUpdateCheck {
030: private static final Pattern PATTERN_CREATE_TABLE = Pattern
031: .compile("CREATE\\s+TABLE\\s+([A-Z0-9_\\.\"]+)");
032: private static final Pattern PATTERN_ALTER_TABLE = Pattern
033: .compile("ALTER\\s+TABLE\\s+([A-Z0-9_\\.\"]+)");
034: private static final Pattern PATTERN_INSERT_INTO = Pattern
035: .compile("SELECT\\s+INTO\\s+([A-Z0-9_\\.\"]+)");
036:
037: private static final Pattern PATTERN_CREATE_VIEW = Pattern
038: .compile("CREATE\\s+VIEW\\s+([A-Z0-9_\\.\"]+)");
039: private static final Pattern PATTERN_CREATE_MATERIALIZED_VIEW = Pattern
040: .compile("CREATE\\s+MATERIALIZED\\s+VIEW\\s+([A-Z0-9_\\.\"]+)");
041: private static final Pattern PATTERN_CREATE_OR_REPLACE_VIEW = Pattern
042: .compile("CREATE\\s+OR\\s+REPLACE\\s+VIEW\\s+([A-Z0-9_\\.\"]+)");
043: private static final Pattern PATTERN_ALTER_VIEW = Pattern
044: .compile("ALTER\\s+VIEW\\s+([A-Z0-9_\\.\"]+)");
045:
046: private static final Pattern PATTERN_CREATE_PROCEDURE = Pattern
047: .compile("CREATE\\s+PROCEDURE\\s+([A-Z0-9_\\.\"]+)");
048: private static final Pattern PATTERN_CREATE_OR_REPLACE_PROCEDURE = Pattern
049: .compile("CREATE\\s+OR\\s+REPLACE\\s+PROCEDURE\\s+([A-Z0-9_\\.\"]+)");
050: private static final Pattern PATTERN_ALTER_PROCEDURE = Pattern
051: .compile("ALTER\\s+PROCEDURE\\s+([A-Z0-9_\\.\"]+)");
052:
053: private static final Pattern PATTERN_CREATE_FUNCTION = Pattern
054: .compile("CREATE\\s+FUNCTION\\s+([A-Z0-9_\\.\"]+)");
055: private static final Pattern PATTERN_CREATE_OR_REPLACE_FUNCTION = Pattern
056: .compile("CREATE\\s+OR\\s+REPLACE\\s+FUNCTION\\s+([A-Z0-9_\\.\"]+)");
057: private static final Pattern PATTERN_ALTER_FUNCTION = Pattern
058: .compile("ALTER\\s+FUNCTION\\s+([A-Z0-9_\\.\"]+)");
059:
060: private static final Pattern PATTERN_DROP_TABLE = Pattern
061: .compile("DROP\\s+TABLE\\s+([A-Z0-9_\\.\"]+)");
062: private static final Pattern PATTERN_DROP_VIEW = Pattern
063: .compile("DROP\\s+VIEW\\s+([A-Z0-9_\\.\"]+)");
064: private static final Pattern PATTERN_DROP_MATERIALIZED_VIEW = Pattern
065: .compile("DROP\\s+MATERIALIZED\\s+VIEW\\s+([A-Z0-9_\\.\"]+)");
066:
067: private static final Pattern PATTERN_DROP_PROCEDURE = Pattern
068: .compile("DROP\\s+PROCEDURE\\s+([A-Z0-9_\\.\"]+)");
069: private static final Pattern PATTERN_DROP_FUNCTION = Pattern
070: .compile("DROP\\s+FUNCTION\\s+([A-Z0-9_\\.\"]+)");
071:
072: private Set<IDatabaseObjectInfo> _updateDatabaseObjectInfos = new HashSet<IDatabaseObjectInfo>();
073: private Set<String> _dropTableSimpleNames = new HashSet<String>();
074: private Set<String> _dropProcedureSimpleNames = new HashSet<String>();
075:
076: private ISession _session;
077: private SQLDatabaseMetaData _dmd;
078:
079: public SchemaInfoUpdateCheck(ISession session) {
080: _session = session;
081: _dmd = _session.getSQLConnection().getSQLMetaData();
082: }
083:
084: public void addExecutionInfo(SQLExecutionInfo exInfo) {
085: if (null == exInfo || null == exInfo.getSQL()) {
086: return;
087: }
088:
089: TableInfo[] tis = getTableInfos(exInfo.getSQL());
090: for (int i = 0; i < tis.length; i++) {
091: _updateDatabaseObjectInfos.add(tis[i]);
092: }
093:
094: ProcedureInfo[] pi = getProcedureInfos(exInfo.getSQL());
095: for (int i = 0; i < pi.length; i++) {
096: _updateDatabaseObjectInfos.add(pi[i]);
097: }
098:
099: String dtsn = getDropTableSimpleName(exInfo.getSQL());
100: if (null != dtsn) {
101: _dropTableSimpleNames.add(dtsn);
102: }
103:
104: String dpsn = getDropProcedureSimpleName(exInfo.getSQL());
105: if (null != dpsn) {
106: _dropProcedureSimpleNames.add(dpsn);
107: }
108: }
109:
110: private String getDropProcedureSimpleName(String sql) {
111: sql = sql.trim();
112: String upperSql = sql.toUpperCase();
113:
114: Matcher matcher;
115:
116: matcher = PATTERN_DROP_PROCEDURE.matcher(upperSql);
117: if (matcher.find()) {
118: return createProcdureInfos(matcher, sql, true)[0]
119: .getSimpleName();
120: }
121:
122: matcher = PATTERN_DROP_FUNCTION.matcher(upperSql);
123: if (matcher.find()) {
124: return createProcdureInfos(matcher, sql, true)[0]
125: .getSimpleName();
126: }
127:
128: return null;
129: }
130:
131: private String getDropTableSimpleName(String sql) {
132: sql = sql.trim();
133: String upperSql = sql.toUpperCase();
134:
135: Matcher matcher;
136:
137: matcher = PATTERN_DROP_TABLE.matcher(upperSql);
138: if (matcher.find()) {
139: return createTableInfos(matcher, sql, "TABLE", true)[0]
140: .getSimpleName();
141: }
142:
143: matcher = PATTERN_DROP_MATERIALIZED_VIEW.matcher(upperSql);
144: if (matcher.find()) {
145: return createTableInfos(matcher, sql, "TABLE", true)[0]
146: .getSimpleName();
147: }
148:
149: matcher = PATTERN_DROP_VIEW.matcher(upperSql);
150: if (matcher.find()) {
151: return createTableInfos(matcher, sql, "VIEW", true)[0]
152: .getSimpleName();
153: }
154:
155: return null;
156:
157: }
158:
159: public void flush() {
160: if (60 < _updateDatabaseObjectInfos.size()
161: + _dropTableSimpleNames.size()
162: + _dropProcedureSimpleNames.size()) {
163: // reload complete SchemaInfo
164: SQLDatabaseMetaData dmd = _session.getSQLConnection()
165: .getSQLMetaData();
166: DatabaseObjectInfo sessionOI = new DatabaseObjectInfo(null,
167: null, "SessionDummy", DatabaseObjectType.SESSION,
168: dmd);
169: _session.getSchemaInfo().reload(sessionOI, false);
170:
171: } else {
172: for (IDatabaseObjectInfo doi : _updateDatabaseObjectInfos) {
173: _session.getSchemaInfo().reload(doi);
174: }
175:
176: for (String simpleTableName : _dropTableSimpleNames) {
177: _session.getSchemaInfo()
178: .refershCacheForSimpleTableName(
179: simpleTableName, false);
180: }
181:
182: for (String simpleProcName : _dropProcedureSimpleNames) {
183: _session.getSchemaInfo()
184: .refreshCacheForSimpleProcedureName(
185: simpleProcName, false);
186: }
187: }
188:
189: if (0 < _updateDatabaseObjectInfos.size()
190: + _dropTableSimpleNames.size()
191: + _dropProcedureSimpleNames.size()) {
192:
193: _session.getSchemaInfo().fireSchemaInfoUpdate();
194: SwingUtilities.invokeLater(new Runnable() {
195: public void run() {
196: repaintSqlEditor();
197: }
198: });
199: }
200:
201: }
202:
203: private void repaintSqlEditor() {
204: BaseSessionInternalFrame activeSessionWindow = _session
205: .getActiveSessionWindow();
206:
207: if (activeSessionWindow instanceof SQLInternalFrame) {
208: ISQLEntryPanel sqlEntryPanel = ((SQLInternalFrame) activeSessionWindow)
209: .getSQLPanelAPI().getSQLEntryPanel();
210: sqlEntryPanel.getTextComponent().repaint();
211: _session.getParserEventsProcessor(
212: sqlEntryPanel.getIdentifier()).triggerParser();
213: }
214:
215: if (activeSessionWindow instanceof SessionInternalFrame) {
216: ISQLEntryPanel sqlEntryPanel = ((SessionInternalFrame) activeSessionWindow)
217: .getSQLPanelAPI().getSQLEntryPanel();
218: sqlEntryPanel.getTextComponent().repaint();
219: _session.getParserEventsProcessor(
220: sqlEntryPanel.getIdentifier()).triggerParser();
221: }
222:
223: }
224:
225: private ProcedureInfo[] getProcedureInfos(String sql) {
226: sql = sql.trim();
227: String upperSql = sql.toUpperCase();
228:
229: Matcher matcher;
230:
231: matcher = PATTERN_CREATE_OR_REPLACE_PROCEDURE.matcher(upperSql);
232: if (matcher.find()) {
233: return createProcdureInfos(matcher, sql, false);
234: }
235:
236: matcher = PATTERN_CREATE_PROCEDURE.matcher(upperSql);
237: if (matcher.find()) {
238: return createProcdureInfos(matcher, sql, false);
239: }
240:
241: matcher = PATTERN_ALTER_PROCEDURE.matcher(upperSql);
242: if (matcher.find()) {
243: return createProcdureInfos(matcher, sql, true);
244: }
245:
246: matcher = PATTERN_CREATE_OR_REPLACE_FUNCTION.matcher(upperSql);
247: if (matcher.find()) {
248: return createProcdureInfos(matcher, sql, false);
249: }
250:
251: matcher = PATTERN_CREATE_FUNCTION.matcher(upperSql);
252: if (matcher.find()) {
253: return createProcdureInfos(matcher, sql, false);
254: }
255:
256: matcher = PATTERN_ALTER_FUNCTION.matcher(upperSql);
257: if (matcher.find()) {
258: return createProcdureInfos(matcher, sql, true);
259: }
260:
261: return new ProcedureInfo[0];
262:
263: }
264:
265: private ProcedureInfo[] createProcdureInfos(Matcher matcher,
266: String sql, boolean isAlterOrDrop) {
267: int endIx = matcher.end(1);
268: int len = matcher.group(1).length();
269: String proc = sql.substring(endIx - len, endIx);
270: String[] splits = proc.split("\\.");
271: String simpleName = splits[splits.length - 1];
272: simpleName = removeQuotes(simpleName);
273:
274: if (isAlterOrDrop) {
275: String buf = _session.getSchemaInfo()
276: .getCaseSensitiveProcedureName(simpleName);
277: if (null != buf) {
278: simpleName = buf;
279: }
280: return new ProcedureInfo[] { new ProcedureInfo(null, null,
281: simpleName, null,
282: DatabaseMetaData.procedureResultUnknown, _dmd) };
283: } else {
284: // DB2 stores all names in upper case.
285: // PostgreSQL stores all names in lower case.
286: // That's why we may not find proc as it was written in the create statement.
287: // So we try out the upper and lower case names too.
288: return new ProcedureInfo[] {
289: new ProcedureInfo(null, null, simpleName, null,
290: DatabaseMetaData.procedureResultUnknown,
291: _dmd),
292: new ProcedureInfo(null, null, simpleName
293: .toUpperCase(), null,
294: DatabaseMetaData.procedureResultUnknown,
295: _dmd),
296: new ProcedureInfo(null, null, simpleName
297: .toLowerCase(), null,
298: DatabaseMetaData.procedureResultUnknown,
299: _dmd) };
300: }
301: }
302:
303: private String removeQuotes(String simpleName) {
304: if (simpleName.startsWith("\"")) {
305: simpleName = simpleName.substring(1);
306: }
307:
308: if (simpleName.endsWith("\"")) {
309: simpleName = simpleName.substring(0,
310: simpleName.length() - 1);
311: }
312:
313: return simpleName;
314: }
315:
316: private TableInfo[] getTableInfos(String sql) {
317: sql = sql.trim();
318: String upperSql = sql.toUpperCase();
319:
320: Matcher matcher;
321:
322: matcher = PATTERN_CREATE_TABLE.matcher(upperSql);
323: if (matcher.find()) {
324: return createTableInfos(matcher, sql, "TABLE", false);
325: }
326:
327: matcher = PATTERN_ALTER_TABLE.matcher(upperSql);
328: if (matcher.find()) {
329: return createTableInfos(matcher, sql, "TABLE", true);
330: }
331:
332: matcher = PATTERN_INSERT_INTO.matcher(upperSql);
333: if (matcher.find()) {
334: return createTableInfos(matcher, sql, "TABLE", false);
335: }
336:
337: matcher = PATTERN_CREATE_OR_REPLACE_VIEW.matcher(upperSql);
338: if (matcher.find()) {
339: return createTableInfos(matcher, sql, "VIEW", false);
340: }
341:
342: matcher = PATTERN_CREATE_VIEW.matcher(upperSql);
343: if (matcher.find()) {
344: return createTableInfos(matcher, sql, "VIEW", false);
345: }
346:
347: matcher = PATTERN_CREATE_MATERIALIZED_VIEW.matcher(upperSql);
348: if (matcher.find()) {
349: return createTableInfos(matcher, sql, "TABLE", false);
350: }
351:
352: matcher = PATTERN_ALTER_VIEW.matcher(upperSql);
353: if (matcher.find()) {
354: return createTableInfos(matcher, sql, "VIEW", true);
355: }
356:
357: return new TableInfo[0];
358:
359: }
360:
361: private TableInfo[] createTableInfos(Matcher matcher, String sql,
362: String type, boolean isAlterOrDrop) {
363: int endIx = matcher.end(1);
364: int len = matcher.group(1).length();
365: String table = sql.substring(endIx - len, endIx);
366: String[] splits = table.split("\\.");
367: String simpleName = splits[splits.length - 1];
368: simpleName = removeQuotes(simpleName);
369:
370: if (isAlterOrDrop) {
371: String buf = _session.getSchemaInfo()
372: .getCaseSensitiveTableName(simpleName);
373: if (null != buf) {
374: simpleName = buf;
375: }
376: return new TableInfo[] { new TableInfo(null, null,
377: simpleName, type, null, _dmd) };
378: } else {
379: // DB2 stores all names in upper case.
380: // PostgreSQL stores table names in lower case.
381: // That's why we may not find table as it was written in the create statement.
382: // So we try out the upper and lower case names too.
383: return new TableInfo[] {
384: new TableInfo(null, null, simpleName, type, null,
385: _dmd),
386: new TableInfo(null, null, simpleName.toUpperCase(),
387: type, null, _dmd),
388: new TableInfo(null, null, simpleName.toLowerCase(),
389: type, null, _dmd) };
390: }
391: }
392:
393: }
|