public static ArrayList parseGrantSql(string p_sql, string p_filename) { SQLPlusScanner scanner = new SQLPlusScanner(new StringReader(p_sql), "parseGrantSql"); string currGrantType = ""; string currObjectClause = ""; string currGrantor = ""; string currTableName = ""; string currAdminOption = "NO"; string currGrantable = "NO"; string currHierarchy = "NO"; bool complete = false; StringBuilder grantPrivileges = new StringBuilder(); StringBuilder grantees = new StringBuilder(); try { string currentToken = scanner.getSkipComments(); if (currentToken != "GRANT") throw new ArgumentException("Sql does not contain grant statement"); while (true) { currentToken = scanner.getSkipComments(); if (currentToken == "ON" || currentToken == "TO") break; if (currentToken != "," && grantPrivileges.Length > 0) grantPrivileges.Append(" "); grantPrivileges.Append(currentToken); } if (grantPrivileges.ToString().Trim() == "") Log.Warning("GrantObject", "Incorrect grant, was expecting grant privileges: {0}", p_sql); if (currentToken == "ON") currGrantType = "OBJECT"; else currGrantType = "SYSTEM"; // read objectName if (currGrantType == "OBJECT") { currentToken = scanner.getSkipComments(); if (currentToken == "DIRECTORY") { currObjectClause = currentToken; currentToken = scanner.getSkipComments(); } else if (currentToken == "JAVA") { currObjectClause = currentToken; currentToken = scanner.getSkipComments(); if (currentToken != "SOURCE" || currentToken != "RESOURCE") SQLMake.Util.Log.Warning("GrantObject", "Incorrect grant, was expecting keyword SOURCE|RESOURCE: {0}", p_sql); currObjectClause += " " + currentToken; currentToken = scanner.getSkipComments(); } if (currentToken.IndexOf('.') > -1) { currGrantor = currentToken.Split('.')[0]; currTableName = currentToken.Split('.')[1]; } else { currTableName = currentToken; } currentToken = scanner.getSkipComments(); } if (currentToken != "TO") Log.Warning("GrantObject", "Incorrect grant, was expecting keyword TO: {0}", p_sql); // read grantees part while (true) { currentToken = scanner.getSkipComments(); grantees.Append(currentToken); complete = true; currentToken = scanner.getSkipComments(); if (currentToken != ",") break; else grantees.Append(currentToken); } // read with admin clause if (currGrantType == "SYSTEM") { currentToken += " " + scanner.getSkipComments() + " " + scanner.getSkipComments(); if (currentToken == "WITH ADMIN OPTION") currAdminOption = "YES"; else Log.Warning("GrantObject", "Incorrect grant, was expecting keyword WITH ADMIN OPTION: {0}", p_sql); } else { currentToken += " " + scanner.getSkipComments() + " " + scanner.getSkipComments(); if (currentToken == "WITH GRANT OPTION") currGrantable = "YES"; else if (currentToken == "WITH HIERARCHY OPTION") currHierarchy = "YES"; else Log.Warning("GrantObject", "Incorrect grant, was expecting keyword WITH GRANT OPTION|WITH HIERARCHY OPTION: {0}", p_sql); currentToken = scanner.getSkipComments(); currentToken += " " + scanner.getSkipComments() + " " + scanner.getSkipComments(); if (currentToken == "WITH GRANT OPTION") currGrantable = "YES"; else if (currentToken == "WITH HIERARCHY OPTION") currHierarchy = "YES"; else Log.Warning("GrantObject", "Incorrect grant, was expecting keyword WITH GRANT OPTION|WITH HIERARCHY OPTION: {0}", p_sql); } currentToken = scanner.getSkipComments(); Log.Warning("GrantObject", "Incorrect grant, keywords found after expected end of grant clause: {0}", p_sql); } catch (EOFException) { if (!complete) Log.Warning("GrantObject", "Incorrect grant, premature termination of sql statement: {0}", p_sql); } catch (EOBException) { if (!complete) Log.Warning("GrantObject", "Incorrect grant, premature termination of sql statement: {0}", p_sql); } ArrayList resultSet = new ArrayList(); if (complete) { foreach (string grantPrivilege in grantPrivileges.ToString().Split(',')) { foreach (string grantee in grantees.ToString().Split(',')) { GrantObject grantObject = new GrantObject(p_filename, currGrantType, currObjectClause, replaceWithSandbox(grantee.Trim()), currTableName, replaceWithSandbox(currGrantor.Trim()), grantPrivilege, currAdminOption, currGrantable, currHierarchy); // Console.WriteLine(grantObject.ToString()); resultSet.Add(grantObject); } } } return resultSet; }
public static SynonymObject parseSynonymSql(string p_sql, string p_filename) { SQLPlusScanner scanner = new SQLPlusScanner(new StringReader(p_sql), "parseSynonymSql"); string synonymSchema = ""; string synonymName = ""; string synonymTarget = ""; bool isPublic = false; bool complete = false; try { string currentToken = scanner.getSkipComments(); if (currentToken == "CREATE") currentToken = scanner.getSkipComments(); if (currentToken == "OR") { currentToken = scanner.getSkipComments(); if (currentToken != "REPLACE") throw new ArgumentException("REPLACE expected after CREATE OR tokens"); currentToken = scanner.getSkipComments(); } isPublic = false; if (currentToken == "PUBLIC") { isPublic = true; currentToken = scanner.getSkipComments(); } if (currentToken != "SYNONYM") throw new ArgumentException("Sql does not contain SYNONYM statement"); currentToken = scanner.getSkipComments(); // expecting schema.synonymName if (currentToken.IndexOf('.') > -1) { synonymSchema = currentToken.Split('.')[0]; synonymName = currentToken.Split('.')[1]; } else { synonymSchema = ""; synonymName = currentToken; } currentToken = scanner.getSkipComments(); // expecting FOR if (currentToken != "FOR") throw new ArgumentException("FOR statement expected in synonym statement"); // target schema: schema.object@db_link // we have to convert schema to sandbox name currentToken = scanner.getSkipComments(); if (currentToken.IndexOf('.') > -1) { synonymTarget = replaceWithSandbox(currentToken.Split('.')[0].Trim()) + "." + currentToken.Split('.')[1]; } else { synonymTarget = currentToken; } complete = true; // this should throw exception as we should be at the end currentToken = scanner.getSkipComments(); } catch (EOFException) { if (!complete) Log.Warning("SynonymObject", "Incorrect synonym, premature termination of sql statement: {0}", p_sql); } catch (EOBException) { if (!complete) Log.Warning("GrantObject", "Incorrect synonym, premature termination of sql statement: {0}", p_sql); } return new SynonymObject(p_filename, synonymSchema, synonymName, synonymTarget, isPublic); }
public static void Print(string p_dir, string p_include_list, SearchOption p_recurse_dir) { string[] files = new string[1]; string[] include_list = p_include_list.Split(','); foreach (string filetype in include_list) { string[] matchedFiles = Directory.GetFiles(p_dir, filetype, p_recurse_dir); if (matchedFiles.Length > 0) { int startPos = files.Length; if (startPos == 1) startPos = 0; // compensate files array[1] declaration Array.Resize(ref files, startPos + matchedFiles.Length); Array.Copy(matchedFiles, 0, files, startPos, matchedFiles.Length); } } foreach (string filename in files) { //Console.WriteLine("xxx"+filename); StreamReader r = new StreamReader(filename, Encoding.GetEncoding(Settings.getEncoding(false))); SQLPlusScanner scanner = new SQLPlusScanner(r, filename); try { do { try { do { scanner.get(); } while (true); } catch (EOBException) { if (scanner.currCommand.action != "REMARK") Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", filename, scanner.getModeDesc(), scanner.currCommand.action, scanner.currCommand.cmdName, scanner.currCommand.objectName, scanner.currCommand.alterType, scanner.currCommand.secondaryCmdName, scanner.currCommand.secondaryObjectName); scanner.resetBlockType(); } } while (true); } catch (EOFException) { } } }
public static void BasicStats(string p_dir, SearchOption p_recurse_dir) { // Read configuration string configSqlFileList = SQLMake.Util.Settings.getSqlFileList(true); // disable variable substitution as we are just scanning through files Settings.overrideSqlPlusVariableSubstitutionFlag(false); int dmlModeCount = 0; // insert, update, delete, select int ddlModeCount = 0; // all other SQL int tabColCommentsModeCount = 0; // int plsqlModeCount = 0; int sqlPlusModeCount = 0; int unknownModeCount = 0; int numberOfScripts = 0; string[] files = FolderSearch.Search(p_dir, Settings.getRecurseFlag(true), Settings.getSqlFileList(true), Settings.getIgnoreDirList(true), Settings.getIgnoreFileList(true)); foreach (string filename in files) { //Console.WriteLine(filename); numberOfScripts++; StreamReader r = new StreamReader(filename, Encoding.GetEncoding(Settings.getEncoding(false))); SQLPlusScanner scanner = new SQLPlusScanner(r, filename); try { do { try { do { scanner.get(); } while (true); } catch (EOBException) { switch (scanner.currCommand.cmdType) { case CommandTypes.Sql: if (scanner.currCommand.action == "SELECT" || scanner.currCommand.action == "INSERT" || scanner.currCommand.action == "UPDATE" || scanner.currCommand.action == "DELETE") dmlModeCount++; else if (scanner.currCommand.action == "COMMENT") tabColCommentsModeCount++; else ddlModeCount++; break; case CommandTypes.Plsql: plsqlModeCount++; break; case CommandTypes.SqlPlus: sqlPlusModeCount++; break; case CommandTypes.Unknown: //Console.WriteLine("Unknown BLOCK found in file {0} from line {1} to {2}", filename, scanner.startLineIndex + 1, scanner.currLineIndex + 1); //Console.WriteLine(scanner.getCurrentLine()); unknownModeCount++; break; } //Console.WriteLine(" mode {1}, action {0} {2}, plsql:{3}, sql:{4}, sqlplus:{5}", scanner.action, scanner.getModeDesc(), scanner.currSubMode, plsqlModeCount, ddlModeCount, sqlPlusModeCount); scanner.resetBlockType(); } } while (true); } catch (EOFException) { if (scanner.currCommand.cmdType != CommandTypes.Unknown) { Log.Warning("stat", "Last {0} in file {1} not correctly terminated", scanner.getModeDesc(), filename); Console.WriteLine("Last {0} in file {1} not correctly terminated", scanner.getModeDesc(), filename); } } } Console.WriteLine("Number of scripts: {0}", numberOfScripts); Console.WriteLine("Number of DDL commands: {0}", ddlModeCount); Console.WriteLine("Number of table/column comments: {0}", tabColCommentsModeCount); Console.WriteLine("Number of DML commands: {0}", dmlModeCount); Console.WriteLine("Number of PLSQL commands: {0}", plsqlModeCount); Console.WriteLine("Number of SQLPlus commands: {0}", sqlPlusModeCount); Console.WriteLine("Number of Unknown commands: {0}", unknownModeCount); }
// Find all dependencies between sql script files // Who is calling who /// /////////////////////////////////////////////////////////////////// public static void Run(string callerScript, string targetScript, string baseDir, int level) { Console.WriteLine("REM "+" ".PadLeft(level * 3) + Path.GetFileName(targetScript) + "(" + Path.GetDirectoryName(targetScript) + ")"); String normalisedTargetScript = ""; if (File.Exists(targetScript)) normalisedTargetScript = targetScript; else if (File.Exists(targetScript + ".sql")) normalisedTargetScript = targetScript + ".sql"; else { Log.Error("Crawler", "SP2-0310: unable to open file \"{0}\"", targetScript); return; } StreamReader r = new StreamReader(normalisedTargetScript, Encoding.GetEncoding(Settings.getEncoding(false))); SQLPlusScanner scanner = new SQLPlusScanner(r, targetScript); String token = null; try { // loop through all SQLPlus, SQL and PL/SQL commands in script do { try { token = scanner.get(); // Is this SQLPlus command one of: start, @, @@ // this is indicated by scanner mode SQLPlusStartMode if (scanner.currCommand.cmdType == CommandTypes.SqlPlusStart) { string scannerAction = scanner.currCommand.action; // We have found token start, @ or @@ // All text until end of the line is considered as a target String target = null; try { do { target += " " + scanner.get(); } while (true); } catch (EOBException) { scanner.resetBlockType(); } if (scannerAction != "@@") Run(targetScript, baseDir + "\\" + target.Trim(), baseDir, level + 1); else Run(targetScript, Path.GetDirectoryName(targetScript) + "\\" + target.Trim(), baseDir, level + 1); } else if (scanner.currCommand.cmdType == CommandTypes.SqlPlus && scanner.currCommand.action == "DEFINE") { string define_variable_name = scanner.get(); scanner.get(); // skip equal sign string define_variable_content = scanner.get(); // variable content can be optionally enclosed in 'xyz' if (define_variable_content.StartsWith("'") && define_variable_content.EndsWith("'")) define_variable_content = define_variable_content.Substring(1, define_variable_content.Length - 2); Settings.addSqlplusVariable(define_variable_name.ToUpper(), define_variable_content); } // This command is not one of: start, @, @@ // Skip it else { try { do { scanner.get(); } while (true); } catch (EOBException) { Console.WriteLine(scanner.currBlockText); scanner.resetBlockType(); } } } catch (EOBException) { scanner.resetBlockType(); } } while (true); } catch (EOFException) { } }
public static Object[] Load(string filename) { int sqlModeCount = 0; int plsqlModeCount = 0; int sqlPlusModeCount = 0; int unknownModeCount = 0; int seqInFile = 0; string commandType = ""; string objectType = ""; string action = ""; StreamReader r = new StreamReader(filename, Encoding.GetEncoding(Settings.getEncoding(false))); SQLPlusScanner scanner = new SQLPlusScanner(r, filename); ArrayList sqlObjectList = new ArrayList(); try { do { try { //Console.WriteLine(filename); do { scanner.get(); if ((scanner.currCommand.cmdType == CommandTypes.Plsql || scanner.currCommand.cmdType == CommandTypes.WrappedPlsql) && scanner.currCommand.objectName != "") break; } while (true); if (scanner.currCommand.cmdType == CommandTypes.Plsql || scanner.currCommand.cmdType == CommandTypes.WrappedPlsql) { do { scanner.getLine(); } while (true); } } catch (EOBException) { commandType = "Unknown"; seqInFile++; action = scanner.currCommand.action; // CREATE, ALTER, DROP, INSERT, ... objectType = scanner.currCommand.cmdName; //TABLE, PACKAGE, TRIGGER, INDEX, ... switch (scanner.currCommand.cmdType) { case CommandTypes.Sql: sqlModeCount++; commandType = "SQL"; if (action == "ALTER" && objectType == "TABLE" && scanner.currCommand.alterType == "ADD CONSTRAINT") { action = "CREATE"; objectType = scanner.currCommand.secondaryCmdName; if (objectType != "CHECK") objectType += " KEY"; } if (action == "COMMENT") { action = "CREATE"; objectType = "COMMENT"; } //Console.WriteLine("[{6},{7}] action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType, scanner.blockStartLine+1, scanner.currLineIndex+1); //Console.WriteLine(scanner.currBlock.ToString().Trim()); break; case CommandTypes.Plsql: commandType = "PLSQL"; plsqlModeCount++; //Console.WriteLine("[{6},{7}] action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType, scanner.blockStartLine+1, scanner.currLineIndex+1); break; case CommandTypes.WrappedPlsql: commandType = "PLSQL"; plsqlModeCount++; //Console.WriteLine("[{6},{7}] action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType, scanner.blockStartLine+1, scanner.currLineIndex+1); break; case CommandTypes.SqlPlus: commandType = "SQLPlus"; //Console.WriteLine("action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType); sqlPlusModeCount++; break; case CommandTypes.SqlPlusStart: commandType = "SQLPlus"; //Console.WriteLine("action {0}, currSubMode {1}, primaryObject {2}, alterType {3}, secondaryObject {4}, secondaryObjectType {5}", scanner.action, scanner.currSubMode, scanner.primaryObject, scanner.alterType, scanner.secondaryObject, scanner.secondaryObjectType); sqlPlusModeCount++; break; case CommandTypes.Unknown: commandType = "Unknown"; //Console.WriteLine("Unknown BLOCK found in file {0} from line {1} to {2}", filename, scanner.blockStartLine + 1, scanner.currLineIndex + 1); //Console.WriteLine(scanner.getCurrentLine()); unknownModeCount++; break; default: throw new NotImplementedException(); } sqlObjectList.Add(new SqlObject(commandType, scanner.currCommand.objectName, scanner.currCommand.secondaryObjectName, action, objectType, filename, seqInFile, scanner.blockStartLine + 1, scanner.currLineIndex + 1, scanner.currBlockText.ToString().Trim())); scanner.resetBlockType(); } } while (true); } catch (EOFException) { if (scanner.currCommand.cmdType != CommandTypes.Unknown) { Log.ExitError(String.Format("Last {0} in file {1} not correctly terminated", scanner.getModeDesc(), filename)); } } //foreach (SqlObject s in sqlObjectList) //{ // Console.WriteLine("[{0},{1}] action {2}, objectType {3}, objectName {4}, secondaryObjectName {5}", s.lineStart, s.lineEnd, s.action, s.objectType, s.objectName, s.secondaryObjectName); //} return sqlObjectList.ToArray(); }
public static string getPLSQLFromFile(string fileName) { // Console.WriteLine("Reading " + fileName + "..."); // string fileName = @"G:\ucg\Database\croo\plsql\BL_ATRIBUTFIZICKOGLICA.pkb"; StreamReader r = new StreamReader(fileName, Encoding.GetEncoding(Settings.getEncoding(false))); SQLPlusScanner scanner = new SQLPlusScanner(r, fileName); String token = null; CommandTypes mode = CommandTypes.Unknown; do { try { token = scanner.get(); mode = scanner.currCommand.cmdType; } // this is needed to filter out any SQLPlus commands like PROMPT // or line&block comments catch (EOBException) { mode = scanner.currCommand.cmdType; scanner.resetBlockType(); } } while (mode == CommandTypes.SqlPlus); // Console.WriteLine(token); while (token.ToUpper() != "FUNCTION" && token.ToUpper() != "LIBRARY" && token.ToUpper() != "PACKAGE" && token.ToUpper() != "PROCEDURE" && token.ToUpper() != "TRIGGER" && token.ToUpper() != "TYPE" && token.ToUpper() != "VIEW") { token = scanner.get(); // Console.WriteLine(token); } StringBuilder sb = new StringBuilder(); string currLine = null; try { currLine = scanner.getLineFromStartOfLastToken(); while (currLine != null) { if (sb.Length > 0) sb.Append("\n"); sb.Append(currLine.TrimEnd(null)); currLine = scanner.getLine(); } } catch (EOBException) { } // Example of plsql file, why this was added // Blank lines between end of stored procedure and block terminator // ... // end; // // // / // text += "\n"; r.Close(); //string fileName1 = @"c:\temp\" + Path.GetFileName(fileName) + ".sqlmake"; //System.IO.File.WriteAllText(fileName1, text); return sb.ToString().TrimEnd(); }
public static string getObjectNameFromFile(string fileName, ref string plsqlObjectType) { StreamReader r = new StreamReader(fileName, Encoding.GetEncoding(Settings.getEncoding(false))); SQLPlusScanner scanner = new SQLPlusScanner(r, fileName); String token = null; CommandTypes mode = CommandTypes.Unknown; do { try { token = scanner.get(); mode = scanner.currCommand.cmdType; } // this is needed to filter out any SQLPlus commands like PROMPT // or line&block comments catch (EOBException) { mode = scanner.currCommand.cmdType; scanner.resetBlockType(); } } while (mode == CommandTypes.SqlPlus); // This is cheap but perhaps not exactly correct // way to skip "create or replace" while (token.ToUpper() != "FUNCTION" && token.ToUpper() != "LIBRARY" && token.ToUpper() != "PACKAGE" && token.ToUpper() != "PROCEDURE" && token.ToUpper() != "TRIGGER" && token.ToUpper() != "TYPE" && token.ToUpper() != "VIEW") { token = scanner.get(); } plsqlObjectType = token.ToUpper(); token = scanner.getSkipComments(); if ((plsqlObjectType == "PACKAGE" || plsqlObjectType == "TYPE") && token.ToUpper() == "BODY") { plsqlObjectType += " BODY"; token = scanner.getSkipComments(); } // now the token contains plsql object name string plsqlObjectName = token; r.Close(); r.Dispose(); return plsqlObjectName; }
public static string extractPlsql(string text, string fileName) { StringReader r = new StringReader(text); SQLPlusScanner scanner = new SQLPlusScanner(r, fileName); String token = null; CommandTypes mode = CommandTypes.Unknown; do { try { token = scanner.get(); mode = scanner.currCommand.cmdType; } // this is needed to filter out any SQLPlus commands like PROMPT // or line&block comments catch (EOBException) { mode = scanner.currCommand.cmdType; scanner.resetBlockType(); } } while (mode == CommandTypes.SqlPlus); // Console.WriteLine(token); while (token.ToUpper() != "FUNCTION" && token.ToUpper() != "LIBRARY" && token.ToUpper() != "PACKAGE" && token.ToUpper() != "PROCEDURE" && token.ToUpper() != "TRIGGER" && token.ToUpper() != "TYPE" && token.ToUpper() != "VIEW") { token = scanner.get(); // Console.WriteLine(token); } // with views we use only select statement starting after keyword AS if (token.ToUpper() == "VIEW") { while (token.ToUpper() != "AS" ) { token = scanner.get(); } token = scanner.get(); } StringBuilder sb = new StringBuilder(); string currLine = null; try { currLine = scanner.getLineFromStartOfLastToken(); while (currLine != null) { if (sb.Length > 0) sb.Append("\n"); sb.Append(currLine.TrimEnd(null)); currLine = scanner.getLine(); } } catch (EOFException) { } // Example of plsql file, why this was added // Blank lines between end of stored procedure and block terminator // ... // end; // // // / // text += "\n"; r.Close(); //string fileName1 = @"c:\temp\" + Path.GetFileName(fileName) + ".sqlmake"; //System.IO.File.WriteAllText(fileName1, text); return sb.ToString().TrimEnd(); }