public static void Run(OracleSql db, string p_dir, bool debugFlag, bool syncToFilesystem, bool syncToDb, string outputFile)
{
Log.Info("plsqlmake", "Loading plsql command list");
ArrayList list = buildFileList(p_dir, db.GetMetaConnection());
Log.Info("plsqlmake", "");
Log.Info("plsqlmake", "PL/SQL differences");
Log.Info("plsqlmake", " Object only on filesystem");
bool missingIndicator = false;
foreach (PlsqlObject obj in list)
{
if (obj.filename != null && obj.existsInSchema == false)
{
//Console.WriteLine(" {0} {1} ({2})", obj.name.ToLower(), obj.filetype, obj.filename);
missingIndicator = true;
if (!syncToFilesystem && !syncToDb)
Log.Info("plsqlmake", " @{2}", obj.objectName.ToLower(), obj.objectType, obj.filename);
if (syncToFilesystem)
{
Log.Info("plsqlmake", " Deleting {0}", obj.filename);
FileInfo fi = new FileInfo(obj.filename);
fi.MoveTo(obj.filename + ".bak");
}
if (syncToDb)
{
Log.Info("plsqlmake", " Creating {0} {1}", obj.objectType, obj.objectName);
if (obj.objectType != "VIEW") db.Exec("create or replace " + extractPlsql(obj.plsqlText, obj.filename), "syncToDb", "Create " + obj.objectType + " " + obj.objectName);
else db.Exec("create or replace " + obj.objectType + " " + obj.objectName + " as\n" + extractPlsql(obj.plsqlText, obj.filename), "syncToDb", "Create " + obj.objectType + " " + obj.objectName);
}
}
}
if (!missingIndicator) Log.Info("plsqlmake", " None");
Log.Info("plsqlmake", " Object only in DB");
bool extraIndicator = false;
foreach (PlsqlObject obj in list)
{
if (obj.filename == null && obj.existsInSchema == true)
{
extraIndicator = true;
if (!syncToFilesystem && !syncToDb)
Log.Info("plsqlmake", " {0} {1}", obj.objectName.ToLower(), obj.objectType);
if (syncToFilesystem)
{
Log.Info("plsqlmake", " Added {0}\\{1}{2}", p_dir, obj.objectName.ToLower(), obj.getExtFromObjectType().ToLower());
System.IO.File.WriteAllText(p_dir + "\\" + obj.objectName.ToLower() + obj.getExtFromObjectType().ToLower(), "create or replace " + db.getPLSQLfromDB(obj.objectName, obj.objectType) + "\n/", Encoding.Default);
}
if (syncToDb)
{
Log.Info("plsqlmake", " Droping {0} {1}", obj.objectType, obj.objectName);
try
{
if (obj.objectType == "TYPE" && getDependantTypesCount(obj.objectName, db) > 0)
{
db.Exec("drop " + obj.objectType + " " + obj.objectName + " force", "syncToDb", "Drop " + obj.objectType + " " + obj.objectName);
}
else db.ExecUnmanaged("drop " + obj.objectType + " " + obj.objectName, "syncToDb", "Drop " + obj.objectType + " " + obj.objectName);
}
catch (OracleException e)
{
// handle ORA-04043: object {objectName} does not exist
// when package is droped, package body is automatically droped
// resulting in ORA-04043
if (!(e.Code == 2303 || e.Code == 4043 || e.Code == 4042))
{
Log.Warning("oracleSql", "Error when executing SQL command\r\n{0}\r\n{1}", e.Message, "Drop " + obj.objectType + " " + obj.objectName);
}
}
}
}
}
if (!extraIndicator) Log.Info("plsqlmake", " None");
Log.Info("plsqlmake", " Different objects");
bool diffIndicator = false;
foreach (PlsqlObject obj in list)
{
if (obj.filename != null && obj.existsInSchema == true)
{
obj.file_md5 = getMd5Hash(extractPlsql(obj.plsqlText, obj.filename));
string dbText = db.getPLSQLfromDB(obj.objectName, obj.objectType);
obj.plsql_md5 = getMd5Hash(dbText);
//Console.WriteLine(" File hash: {0} Db hash: {1}", obj.file_md5, obj.plsql_md5);
if (obj.file_md5 != obj.plsql_md5)
{
diffIndicator = true;
if (!syncToFilesystem && !syncToDb)
Log.Info("plsqlmake", " {0} {1} ({2})", obj.objectName.ToLower(), obj.objectType, obj.filename);
if (syncToFilesystem)
{
Log.Info("plsqlmake", " Modified {0}", obj.filename);
FileInfo fi = new FileInfo(obj.filename);
FileInfo fiBak = new FileInfo(obj.filename + ".bak");
fiBak.Delete();
fi.MoveTo(obj.filename + ".bak");
System.IO.File.WriteAllText(obj.filename, "create or replace " + dbText + "\n/", Encoding.Default);
}
if (syncToDb)
{
ArrayList grantsList = new ArrayList();
db.Prompt(String.Format(" Modified {0} {1}", obj.objectType, obj.objectName));
if (obj.objectType == "TYPE" && getDependantTypesCount(obj.objectName, db) > 0)
{
/*foreach (PlsqlObject typeBody in list)
{
if (typeBody.objectType == "TYPE BODY" && typeBody.objectName = obj.objectName)
{
typeBody.
}
}*/
grantsList = Grants.buildObjectGrantsList(db.GetMetaConnection(), obj.objectName);
db.Exec("drop " + obj.objectType + " " + obj.objectName + " force", "syncToDb", "Drop " + obj.objectType + " " + obj.objectName);
}
try
{
if (obj.objectType != "VIEW") db.ExecUnmanaged("create or replace " + extractPlsql(obj.plsqlText, obj.filename), "syncToDb", "Replace " + obj.objectType + " " + obj.objectName);
else db.ExecUnmanaged("create or replace " + obj.objectType + " " + obj.objectName + " as\n" + extractPlsql(obj.plsqlText, obj.filename), "syncToDb", "Replace " + obj.objectType + " " + obj.objectName);
Grants.executeGrantList(db, grantsList);
}
catch (OracleException e)
{
// handle ORA-02303: cannot drop or replace a type with type or table dependents
// we check for type dependency but it is not working when type is referenced via private synony
// in another schema
if (e.Code != 2303)
{
string lastErrm = e.Message;
Log.Warning("oracleSql", "Error when executing SQL command\r\n{0}\r\n{1}", e.Message, "create or replace " + extractPlsql(obj.plsqlText, obj.filename));
}
// retry operation
grantsList = Grants.buildObjectGrantsList(db.GetMetaConnection(), obj.objectName);
db.Exec("drop " + obj.objectType + " " + obj.objectName + " force", "syncToDb", "Drop " + obj.objectType + " " + obj.objectName);
db.Exec("create or replace " + extractPlsql(obj.plsqlText, obj.filename), "syncToDb", "Replace " + obj.objectType + " " + obj.objectName);
}
if (obj.objectType == "TYPE")
{
Grants.executeGrantList(db, grantsList);
}
}
if (debugFlag)
{
Log.Debug("plsqlMake","Write db and filesystem PLSQL text to file:");
Log.Debug("plsqlMake", " {0}", obj.objectName + " " + obj.objectType + ".file.log");
Log.Debug("plsqlMake", " {0}", obj.objectName + " " + obj.objectType + ".db.log");
Log.Debug("plsqlMake", " File hash: {0} Db hash: {1}", obj.file_md5, obj.plsql_md5);
System.IO.File.WriteAllText(obj.objectName + " " + obj.objectType + ".file.log", extractPlsql(obj.plsqlText, obj.filename), Encoding.Default);
System.IO.File.WriteAllText(obj.objectName + " " + obj.objectType + ".db.log", dbText, Encoding.Default);
}
}
// if (getMd5Hash(getPLSQLfromDB(obj.objectName, obj.objectType, con)) != obj.plsql_md5)
// {
// System.IO.File.WriteAllText(obj.objectName + " " + obj.objectType + ".db.log", dbText, Encoding.Default);
// Console.WriteLine(obj.objectName + " " + obj.objectType + " File hash: {0} Db hash: {1}", obj.file_md5, getPLSQLfromDBCryptoAPI(obj.objectName, obj.objectType, con));
// }
}
}
if (!diffIndicator) Log.Info("plsqlmake", " None");
int equalObjectsCount = 0;
foreach (PlsqlObject obj in list)
{
if (obj.filename != null && obj.existsInSchema == true)
{
if (obj.file_md5 == obj.plsql_md5) equalObjectsCount++;
}
}
Log.Info("plsqlmake", " Equal objects count:");
Log.Info("plsqlmake", " {0} object(s)", equalObjectsCount);
if (syncToDb)
{
bool failedInstall = false;
int invalidCount = db.RecompileInvalidObjects();
if (invalidCount > 0)
{
failedInstall = true;
Log.Error("sync2db", "{0} invalid object(s) found after recompilation", invalidCount);
foreach (string objectName in db.GetInvalidObjectsList())
Log.Error("sync2db", " {0}", objectName);
}
if (failedInstall)
{
db.Close();
Log.ExitError("Sync plsql to database failed");
}
}
}
public static void Go(string connectString, string outputScript, string sourceScriptsDir, bool recurseFlag)
{
Log.Verbose("install", "Install start");
OracleSql db = new OracleSql();
RegistryTable registryTable = new RegistryTable();
Settings.overrideSqlPlusVariableSubstitutionFlag(false);
if (connectString != "")
{
// Open connections
if (connectString != "")
{
db.OpenMetaConnection(connectString);
db.OpenConnection(connectString);
}
Log.Verbose("install", "Check registry table");
// Check if registry files are already created
registryTable.checkRegistryTables(db);
string currDatamodelVersion = RegistryTable.getDatamodelVersion(db);
if (currDatamodelVersion == "-1")
{
db.Close();
Log.Error("install", "Target schema is installed but does not contain version information", currDatamodelVersion);
Log.Error("install", "Only manual upgrade is possible");
Log.ExitError("Installation failed");
}
if (RegistryTable.compareVersion(currDatamodelVersion, "0") == 1)
{
db.Close();
Log.Error("install", "Target schema already contains version {0} of datamodel", currDatamodelVersion);
Log.Error("install", "Use upgrade instead of install");
Log.ExitError("Installation failed");
}
}
else if (outputScript != "")
db.SpoolOn(outputScript);
else
{
Log.Error("install", "Either connect string or output script must be specified");
Log.ExitError("Install did not start");
}
Log.Info("install", "Searching for sql scripts ...");
Log.Verbose("install", "Allowed file types {0}", Settings.getSqlFileList(true));
Log.Verbose("install", "Ignore directories {0}", Settings.getIgnoreDirList(true));
Log.Verbose("install", "Ignore files {0}", Settings.getIgnoreFileList(true));
ArrayList sqlCommandList = new ArrayList();
string[] fileList = FolderSearch.Search(sourceScriptsDir, recurseFlag, Settings.getSqlFileList(true), Settings.getIgnoreDirList(true), Settings.getIgnoreFileList(true));
Log.Info("install", "Loading sql commands from scripts ... ");
//Load SQL commands from scripts
foreach (string scriptname in fileList)
{
Log.Verbose("install", "Loading script {0}", scriptname);
sqlCommandList.AddRange(SqlScript.Load(scriptname));
}
Log.Verbose("install", "Sort loaded sql commands");
sqlCommandList.Sort();
//Extract datamodel version
Log.Verbose("Install", "Looking for datamodel version...");
string datamodelVersion = "-1";
if (Settings.getDatamodelVersionLocation(true) == "FILE")
{
Log.Verbose("Install", "Extracting datamodel version from file {0}", Settings.getDatamodelVersionFilename(true));
datamodelVersion = VersionStringManipulation.extractVersionStringFromTextualFile(sourceScriptsDir, recurseFlag, Settings.getDatamodelVersionFilename(true), Settings.getDatamodelVersionSearchPattern(true), Settings.getDatamodelVersionIdDefinition(true));
}
if (Settings.getDatamodelVersionLocation(true) == "DIRECTORY")
{
Log.Verbose("Install", "Extracting datamodel version from directory name {0}", sourceScriptsDir);
datamodelVersion = VersionStringManipulation.extractVersionStringFromDirectoryName(sourceScriptsDir, Settings.getDatamodelVersionSearchPattern(true), Settings.getDatamodelVersionIdDefinition(true));
}
if (datamodelVersion == "-1") Log.Warning("Install", " Target datamodel version not found. Setting version to -1.");
//foreach (SqlObject s in sqlCommandList)
//{
// Console.WriteLine("{6} [{0},{1}] action {2}, objectType {3}, objectName {4}, secondaryObjectName {5}", s.lineStart, s.lineEnd, s.action, s.objectType, s.objectName, s.secondaryObjectName, s.filename);
//}
Log.Info("install", "Executing SQL commands in predefined order...");
// execute in predefined order
string installOrder = Settings.getInstallOrder(true);
int sqlCount = 0;
int cmdSequence = 0;
int errCount = 0;
foreach (string objectType in installOrder.Split(','))
{
sqlCount = 0;
Log.Verbose("install", "Looking for SQL commands where type is {0}", objectType);
foreach (SqlObject s in sqlCommandList)
{
if (s.commandType != "SQLPlus" &&
(
((s.action.Trim() == "CREATE" || s.action == "CREATE OR REPLACE") && s.objectType == objectType.Trim()) ||
((s.action.Trim() == "GRANT" || s.action.Trim() == "INSERT") && s.action == objectType.Trim())
)
)
{
if (sqlCount == 0) db.Prompt("=========== " + objectType + " ======================================");
db.Comment(String.Format("[Line {0} to {1}] {2}", s.lineStart, s.lineEnd, s.filename));
string installingWhat = "";
if (s.secondaryObjectName == "") installingWhat = String.Format("{0} {1} {2}", s.action.ToLower(), s.objectType.ToLower(), s.objectName.ToLower());
else installingWhat = String.Format("{0} {1} {2} on {3}", s.action.ToLower(), s.objectType.ToLower(), s.secondaryObjectName.ToLower(), s.objectName.ToLower());
db.Prompt(installingWhat + "...");
cmdSequence++;
int sqlCode = db.Exec(s.sqlText, "install", "");
if (sqlCode != 0)
{
errCount++;
registryTable.addError(db, datamodelVersion, cmdSequence, s.filename, s.lineStart, db.lastErrm, s.sqlText, installingWhat);
}
s.isInstalled = true;
sqlCount++;
}
}
Log.Verbose("install", "{0} found", sqlCount);
}
Log.Verbose("install", "Check for leftover commands");
// check if any of sqlObjects were left unatended
// we can safely ignore: COMMIT
bool lefotverFlag = false;
foreach (SqlObject s in sqlCommandList)
{
if (s.commandType != "SQLPlus" && !s.isInstalled && s.action.Trim() != "COMMIT")
{
lefotverFlag = true;
string installingWhat = "";
if (s.secondaryObjectName == "") installingWhat = String.Format("Action={0} ObjectType={1} ObjectName={2}...", s.action.ToLower(), s.objectType.ToLower(), s.objectName.ToLower());
else installingWhat = String.Format("Action={0} ObjectType={1} {2} on {3}...", s.action.ToLower(), s.objectType.ToLower(), s.secondaryObjectName.ToLower(), s.objectName.ToLower());
Log.Warning("install", "Leftover: [Line {0} to {1}] {2}", s.lineStart, s.lineEnd, s.filename);
Log.Warning("install", installingWhat);
}
}
// check for errors during install
Log.Verbose("install", "Check for erros during install");
bool failedInstall = false;
if (errCount > 0)
{
failedInstall = true;
Log.Error("install", "{0} error(s) occured during install", errCount);
}
if (lefotverFlag)
{
failedInstall = true;
Log.Error("install", "There are lefover SQL commands that did not get installed. See sqlmake log file for details");
}
/*
Log.Info("install", "Recompile invalid objects");
int invalidCount = db.RecompileInvalidObjects();
if ( invalidCount > 0)
{
failedInstall = true;
Log.Error("install", "{0} invalid object(s) found after recompilation", invalidCount);
Console.WriteLine("{0} invalid object(s) found after recompilation", invalidCount);
}
*/
Log.Info("install", "");
Log.Verbose("install", "Set datamodel version in registry table");
if (datamodelVersion == "0")
{
failedInstall = true;
registryTable.setDatamodelVersion(db, "-1");
Log.Error("install", "Unknown version of datamodel installed");
}
else
{
registryTable.setDatamodelVersion(db, datamodelVersion);
Log.Info("install", "Version {0} of datamodel installed", datamodelVersion);
}
db.Close();
// Done
if (failedInstall)
{
Log.ExitError("Installation failed");
}
else
{
Console.ForegroundColor = ConsoleColor.Green;
Log.Info("install", "Installation successful");
Console.ResetColor();
}
}
public static void Run(string p_conn, string p_dir, bool debugFlag, bool syncToFilesystem, bool syncToDb, string outputFile)
{
OracleSql db = new OracleSql();
db.OpenMetaConnection(p_conn);
if (outputFile == "") db.OpenConnection(p_conn);
else db.SpoolOn(outputFile);
Run(db, p_dir, debugFlag, syncToFilesystem, syncToDb, outputFile);
// Close and Dispose OracleConnection object
db.Close();
}