public static void status(OracleSql db)
{
// check for registry table
int regTableVersion = getRegistryTableVersion(db.GetMetaConnection());
if (regTableVersion == -1)
{
Console.ForegroundColor = ConsoleColor.Yellow;
Log.Warning("status", "Target schema is not using sqlmake tool");
Log.Warning("status", "No status available");
Console.ResetColor();
Log.ExitError("");
}
string dbModelVersion = getDatamodelVersion(db);
string dbBuildNumber = getBuildNumber(db);
int errorCount = getErrorCount(db, dbModelVersion);
int clearedCount = getClearedCount(db, dbModelVersion);
List<string> invalidObjects = db.GetInvalidObjectsList();
if (dbModelVersion == "-1") Log.Warning("status", "Current data model version : Unknown");
else if (dbModelVersion == "0") Log.Warning("status", "Current data model version : Last installation procedure was terminted");
else Log.Info("status", "Current data model version : {0}", dbModelVersion);
//if (dbBuildNumber == "-1") Log.Warning("status", "Current build number : Unknown");
//else if (dbBuildNumber == "0") Log.Warning("status", "Current build number : Last build deployment was terminted");
//else Log.Info("status", "Current build number : {0}", dbBuildNumber);
Log.Error("status", "Errors during last deployment: {0}", errorCount);
if (clearedCount > 0) Log.Info("status", "Errors cleared after install : {0}", clearedCount);
Log.Error("status", "Invalid object(s) found : {0}", invalidObjects.Count);
foreach (string invalidObject in invalidObjects) Log.Error("status", " "+invalidObject);
if (errorCount > 0 || invalidObjects.Count > 0)
{
Log.ExitError("Schema deployment is invalid");
}
else
{
Console.ForegroundColor = ConsoleColor.Green;
Log.Info("status", "Installation successful");
Console.ResetColor();
}
}
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");
}
}
}