/// Upgrade to version 2021-01 public static bool UpgradeDatabase202012_202101(TDataBase ADataBase) { // there are no changes to the database structure // but there is data we might need to add TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; string sql = String.Empty; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { sql = "SELECT COUNT(*) FROM PUB_p_banking_type"; if (Convert.ToInt32(ADataBase.ExecuteScalar(sql, SubmitChangesTransaction)) == 0) { sql = "INSERT INTO PUB_p_banking_type( p_id_i, p_type_c) VALUES(0, 'BANK ACCOUNT')"; ADataBase.ExecuteNonQuery(sql, SubmitChangesTransaction); } sql = "SELECT COUNT(*) FROM PUB_p_banking_details_usage_type"; if (Convert.ToInt32(ADataBase.ExecuteScalar(sql, SubmitChangesTransaction)) == 0) { sql = "INSERT INTO PUB_p_banking_details_usage_type(p_type_c, p_type_description_c) VALUES ('MAIN','The default banking detail that should be used for this partner')"; ADataBase.ExecuteNonQuery(sql, SubmitChangesTransaction); } SubmitOK = true; }); return(true); }
/// <summary> /// Returns the next sequence value for the given Sequence from the DB. /// </summary> /// <param name="ASequenceName">Name of the Sequence.</param> /// <param name="ATransaction">An instantiated Transaction in which the Query /// to the DB will be enlisted.</param> /// <param name="ADatabase">Database object that can be used for querying.</param> /// <returns>Sequence Value.</returns> public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase) { // TODO problem: sequence should be committed? separate transaction? // see also http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=44 // or use locking? see also http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=50 return(Convert.ToInt64(ADatabase.ExecuteScalar("SELECT NEXTVAL('" + ASequenceName + "')", ATransaction))); }
/// <summary> /// Restart a sequence with the given value. /// </summary> public void RestartSequence(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase, Int64 ARestartValue) { ADatabase.ExecuteScalar( "SELECT pg_catalog.setval('" + ASequenceName + "', " + ARestartValue.ToString() + ", true);", ATransaction); }
/// <summary> /// reserve a number of partner keys, to be used by the calling function. /// useful to create many partner at once, eg. for the demodata /// </summary> /// <param name="AFieldPartnerKey"></param> /// <param name="ANumberOfKeys"></param> /// <param name="ADataBase"></param> /// <returns>the first valid partner key to use</returns> public static System.Int64 ReservePartnerKeys(System.Int64 AFieldPartnerKey, ref Int32 ANumberOfKeys, TDataBase ADataBase = null) { Int64 NextPartnerKey = -1; Int32 NumberOfKeys = ANumberOfKeys; if (AFieldPartnerKey == -1) { AFieldPartnerKey = DomainManager.GSiteKey; } TDBTransaction ReadWriteTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("ReservePartnerKeys", ADataBase); bool SubmissionOK = true; db.WriteTransaction(ref ReadWriteTransaction, ref SubmissionOK, delegate { PPartnerLedgerTable PartnerLedgerDT = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadWriteTransaction); NextPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1; Int64 NextUsedKey = Convert.ToInt64(db.ExecuteScalar("SELECT MIN(p_partner_key_n) FROM PUB_p_partner WHERE p_partner_key_n >= " + NextPartnerKey.ToString(), ReadWriteTransaction)); if (NextUsedKey < NextPartnerKey + NumberOfKeys) { NumberOfKeys = Convert.ToInt32(NextUsedKey - NextPartnerKey); } PartnerLedgerDT[0].LastPartnerId = Convert.ToInt32((NextPartnerKey + NumberOfKeys - 1) - PartnerLedgerDT[0].PartnerKey); PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, ReadWriteTransaction); SubmissionOK = true; }); if (ADataBase == null) { db.CloseDBConnection(); } if (!SubmissionOK) { throw new Exception("ReservePartnerKeys failed"); } ANumberOfKeys = NumberOfKeys; return(NextPartnerKey); }
public void TestTimeStamp() { TDBTransaction t = new TDBTransaction(); TDataBase db = DBAccess.Connect("Test", FDataBase); bool SubmissionOK = true; db.WriteTransaction(ref t, ref SubmissionOK, delegate { string countSql = "SELECT COUNT(*) FROM PUB_s_system_defaults"; int count = Convert.ToInt32(db.ExecuteScalar(countSql, t)); string code = "test" + (count + 1).ToString(); string insertSql = String.Format( "INSERT INTO PUB_s_system_defaults(s_default_code_c, s_default_description_c, s_default_value_c, s_modification_id_t) VALUES('{0}', '{1}','{2}',NOW())", code, "test", "test"); Assert.AreEqual(1, db.ExecuteNonQuery(insertSql, t)); string getTimeStampSql = String.Format( "SELECT s_modification_id_t FROM PUB_s_system_defaults WHERE s_default_code_c = '{0}'", code); DateTime timestamp = Convert.ToDateTime(db.ExecuteScalar(getTimeStampSql, t)); string updateSql = String.Format( "UPDATE PUB_s_system_defaults set s_modification_id_t = NOW(), s_default_description_c = '{0}' where s_default_code_c = '{1}' AND s_modification_id_t = ?", "test2", code); OdbcParameter param = new OdbcParameter("timestamp", OdbcType.DateTime); param.Value = timestamp; Assert.AreEqual(1, db.ExecuteNonQuery(updateSql, t, new OdbcParameter[] { param }), "update by timestamp"); }); }
/// <summary> /// get current database version /// </summary> private static TFileVersionInfo GetCurrentDBVersion(TDataBase ADataBase) { TDBTransaction Transaction = new TDBTransaction(); string currentVersion = String.Empty; ADataBase.ReadTransaction( ref Transaction, delegate { currentVersion = (string)ADataBase.ExecuteScalar( "SELECT s_default_value_c FROM s_system_defaults where s_default_code_c='CurrentDatabaseVersion'", Transaction); }); return(new TFileVersionInfo(currentVersion)); }
public static string GetSetupAssistant() { TDBTransaction t = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetSetupAssistant"); string result = String.Empty; string sql = "SELECT COUNT(*) FROM PUB_s_user_module_access_permission p1 " + "WHERE p1.s_module_id_c = 'FINANCE-3' AND p1.s_can_access_l = true"; db.ReadTransaction(ref t, delegate { if (Convert.ToInt32(db.ExecuteScalar(sql, t)) == 0) { result = "SystemManager/SysManAssistantInit"; } }); db.CloseDBConnection(); return(result); }
/// <summary> /// Do we have any consent at all for this contact. /// This method has been copied to Ict.Petra.Server.MFinance.Gift.WebConnectors.TReceiptingWebConnector /// to avoid cyclic dependancies. /// </summary> private static bool UndefinedConsent(Int64 APartnerKey) { TDBTransaction T = new TDBTransaction(); TDataBase DB = DBAccess.Connect("Get Last known entry"); List <OdbcParameter> SQLParameter = new List <OdbcParameter>(); bool HasConsent = false; DB.ReadTransaction(ref T, delegate { string sql = "SELECT " + "COUNT(*)" + "FROM `p_consent_history` " + "WHERE `p_consent_history`.`p_partner_key_n` = ?"; SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.BigInt) { Value = APartnerKey }); HasConsent = (Convert.ToInt32(DB.ExecuteScalar(sql, T, SQLParameter.ToArray())) > 0); }); return(!HasConsent); }
public void T0_Consolidation() { // reset the database, so that there is no consolidated budget CommonNUnitFunctions.ResetDatabase(); TPetraServerConnector.Connect("../../etc/TestServer.config"); TDataBase db = DBAccess.Connect("T0_Consolidation"); string budgetTestFile = TAppSettingsManager.GetValue("GiftBatch.file", CommonNUnitFunctions.rootPath + "/csharp/ICT/Testing/lib/MFinance/SampleData/BudgetImport-All.csv"); int BudgetsAdded; int BudgetsUpdated; int BudgetsFailed; TVerificationResultCollection VerificationResult; BudgetTDS ImportDS = new BudgetTDS(); string ImportString = File.ReadAllText(budgetTestFile); // import budget from CSV decimal RowsImported = TBudgetMaintainWebConnector.ImportBudgets( FLedgerNumber, ImportString, budgetTestFile, new string[] { ",", "dmy", "American" }, ref ImportDS, out BudgetsAdded, out BudgetsUpdated, out BudgetsFailed, out VerificationResult); Assert.AreNotEqual(0, RowsImported, "expect to import several rows"); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult, "ImportBudgets has critical errors:"); BudgetTDSAccess.SubmitChanges(ImportDS, db); // check for value in budget table string sqlQueryBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_revision_i = 0 AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), ABudgetPeriodTable.GetTableDBName(), FLedgerNumber); TDBTransaction Transaction = new TDBTransaction(); decimal budgetValue = -1; db.ReadTransaction(ref Transaction, delegate { budgetValue = Convert.ToDecimal(db.ExecuteScalar(sqlQueryBudget, Transaction)); }); Assert.AreEqual(250m, budgetValue, "problem with importing budget from CSV"); // check for zero in glmperiod budget: that row does not even exist yet, so check that it does not exist string sqlQueryCheckEmptyConsolidatedBudget = String.Format( "SELECT COUNT(*) FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {2} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); Transaction = new TDBTransaction(); db.ReadTransaction(ref Transaction, delegate { Assert.AreEqual(0, db.ExecuteScalar(sqlQueryCheckEmptyConsolidatedBudget, Transaction), "budget should not be consolidated yet"); }); // consolidate the budget TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); // check for correct value in glmperiod budget string sqlQueryConsolidatedBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_glm_sequence_i = {2}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetBudgetBaseDBName(), AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); Transaction = new TDBTransaction(); decimal consolidatedBudgetValue = -1; db.ReadTransaction(ref Transaction, delegate { consolidatedBudgetValue = Convert.ToDecimal(db.ExecuteScalar(sqlQueryConsolidatedBudget, Transaction)); }); Assert.AreEqual(250m, consolidatedBudgetValue, "budget should now be consolidated"); // TODO: also check some summary account and cost centre for summed up budget values // check how reposting a budget works string sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 44 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); bool SubmissionOK = true; Transaction = new TDBTransaction(); db.WriteTransaction(ref Transaction, ref SubmissionOK, delegate { db.ExecuteNonQuery(sqlChangeBudget, Transaction); }); // post all budgets again TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); Transaction = new TDBTransaction(); db.ReadTransaction(ref Transaction, delegate { consolidatedBudgetValue = Convert.ToDecimal(db.ExecuteScalar(sqlQueryConsolidatedBudget, Transaction)); }); Assert.AreEqual(44.0m, consolidatedBudgetValue, "budget should be consolidated with the new value"); // post only a modified budget (testing UnPostBudget) sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 65 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); string sqlMarkBudgetForConsolidation = String.Format("UPDATE PUB_{0} SET {1} = false WHERE " + "a_ledger_number_i = {2} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetTable.GetTableDBName(), ABudgetTable.GetBudgetStatusDBName(), FLedgerNumber); SubmissionOK = true; Transaction = new TDBTransaction(); db.WriteTransaction(ref Transaction, ref SubmissionOK, delegate { db.ExecuteNonQuery(sqlChangeBudget, Transaction); db.ExecuteNonQuery(sqlMarkBudgetForConsolidation, Transaction); }); // post only modified budget again TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, false); Transaction = new TDBTransaction(); db.ReadTransaction( ref Transaction, delegate { consolidatedBudgetValue = Convert.ToDecimal(db.ExecuteScalar(sqlQueryConsolidatedBudget, Transaction)); }); Assert.AreEqual(65.0m, consolidatedBudgetValue, "budget should be consolidated with the new value, after UnPostBudget"); // TODO: test forwarding periods. what happens to next year values, when there is no next year glm record yet? }
/// <summary> /// Returns the current sequence value for the given Sequence from the DB. /// </summary> /// <param name="ASequenceName">Name of the Sequence.</param> /// <param name="ATransaction">An instantiated Transaction in which the Query /// to the DB will be enlisted.</param> /// <param name="ADatabase">Database object that can be used for querying.</param> /// <returns>Sequence Value.</returns> public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase) { return(Convert.ToInt64(ADatabase.ExecuteScalar("SELECT last_value FROM " + ASequenceName + "", ATransaction))); }
/// <summary> /// Returns the next Sequence Value for the given Sequence from the DB. - IMPORTANT: This increasing of the /// Value of the Sequence PERSISTS in the PostgreSQL implmentation even if the DB Transction gets rolled back!!! /// --> See https://wiki.openpetra.org/index.php/PostgreSQL:_Sequences_Not_Tied_to_DB_Transactions /// </summary> /// <param name="ASequenceName">Name of the Sequence.</param> /// <param name="ATransaction">An instantiated Transaction in which the Query /// to the DB will be enlisted.</param> /// <param name="ADatabase">Database object that can be used for querying.</param> /// <returns>Sequence Value.</returns> public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase) { return(Convert.ToInt64(ADatabase.ExecuteScalar("SELECT NEXTVAL('" + ASequenceName + "')", ATransaction))); }
/// <summary> /// Called by a Client to request connection to the Petra Server. /// /// Authenticate the user and create a sesssion for the user. /// /// </summary> /// <param name="AUserName">Username with which the Client connects</param> /// <param name="APassword">Password with which the Client connects</param> /// <param name="AClientComputerName">Computer name of the Client</param> /// <param name="AClientExeVersion"></param> /// <param name="AClientIPAddress">IP Address of the Client</param> /// <param name="AClientServerConnectionType">Type of the connection (eg. LAN, Remote)</param> /// <param name="AClientID">Server-assigned ID of the Client</param> /// <param name="AWelcomeMessage"></param> /// <param name="ASystemEnabled"></param> /// <param name="ASiteKey"></param> /// <param name="ADataBase"></param> public static TConnectedClient ConnectClient(String AUserName, String APassword, String AClientComputerName, String AClientIPAddress, System.Version AClientExeVersion, TClientServerConnectionType AClientServerConnectionType, out System.Int32 AClientID, out String AWelcomeMessage, out Boolean ASystemEnabled, out System.Int64 ASiteKey, TDataBase ADataBase = null) { TDataBase DBConnectionObj = null; TDBTransaction ReadWriteTransaction = new TDBTransaction(); bool SystemEnabled = true; string WelcomeMessage = String.Empty; Int64 SiteKey = -1; TConnectedClient ConnectedClient = null; if (TLogging.DL >= 10) { TLogging.Log( "Loaded Assemblies in AppDomain " + Thread.GetDomain().FriendlyName + " (at call of ConnectClient):", TLoggingType.ToConsole | TLoggingType.ToLogfile); foreach (Assembly tmpAssembly in Thread.GetDomain().GetAssemblies()) { TLogging.Log(tmpAssembly.FullName, TLoggingType.ToConsole | TLoggingType.ToLogfile); } } /* * Every Client Connection request is coming in in a separate Thread * (.NET Remoting does that for us and this is good!). However, the next block * of code must be executed only by exactly ONE thread at the same time to * preserve the integrity of Client tracking! */ try { // TODORemoting if (Monitor.TryEnter(UConnectClientMonitor, TSrvSetting.ClientConnectionTimeoutAfterXSeconds * 1000)) { if (Thread.CurrentThread.Name == String.Empty) { Thread.CurrentThread.Name = "Client_" + AUserName + "__CLIENTCONNECTION_THREAD"; } #region Logging if (TLogging.DL >= 4) { Console.WriteLine(FormatClientList(false)); Console.WriteLine(FormatClientList(true)); } if (TLogging.DL >= 4) { TLogging.Log("Client '" + AUserName + "' is connecting...", TLoggingType.ToConsole | TLoggingType.ToLogfile); } else { TLogging.Log("Client '" + AUserName + "' is connecting...", TLoggingType.ToLogfile); } #endregion // check for username, if it is an email address if (AUserName.Contains('@')) { AUserName = GetUserIDFromEmail(AUserName); } #region Variable assignments // we are not really using the ClientID anymore, but the session ID! AClientID = (short)0; string ClientName = AUserName.ToUpper() + "_" + AClientID.ToString(); #endregion ConnectedClient = new TConnectedClient(AClientID, AUserName.ToUpper(), ClientName, AClientComputerName, AClientIPAddress, AClientServerConnectionType, ClientName); #region Client Version vs. Server Version check if (TLogging.DL >= 9) { Console.WriteLine( "Client EXE Program Version: " + AClientExeVersion.ToString() + "; Server EXE Program Version: " + TSrvSetting.ApplicationVersion.ToString()); } if (TSrvSetting.ApplicationVersion.Compare(new TFileVersionInfo(AClientExeVersion)) != 0) { ConnectedClient.SessionStatus = TSessionStatus.adsStopped; #region Logging if (TLogging.DL >= 4) { TLogging.Log( "Client '" + AUserName + "' tried to connect, but its Program Version (" + AClientExeVersion.ToString() + ") doesn't match! Aborting Client Connection!", TLoggingType.ToConsole | TLoggingType.ToLogfile); } else { TLogging.Log( "Client '" + AUserName + "' tried to connect, but its Program Version (" + AClientExeVersion.ToString() + ") doesn't match! Aborting Client Connection!", TLoggingType.ToLogfile); } #endregion throw new EClientVersionMismatchException(String.Format(StrClientServerExeProgramVersionMismatchMessage, AClientExeVersion.ToString(), TSrvSetting.ApplicationVersion.ToString())); } #endregion #region Login request verification (incl. User authentication) DBConnectionObj = DBAccess.Connect("ConnectClient (User Login)", ADataBase); bool SubmitOK = false; DBConnectionObj.WriteTransaction(ref ReadWriteTransaction, ref SubmitOK, delegate { // Perform login checks such as User authentication and Site Key check try { PerformLoginChecks(AUserName, APassword, AClientComputerName, AClientIPAddress, out SystemEnabled, ReadWriteTransaction); } #region Exception handling catch (EPetraSecurityException) { #region Logging if (TLogging.DL >= 4) { TLogging.Log( "Client '" + AUserName + "' tried to connect, but it failed the Login Checks. Aborting Client Connection!", TLoggingType.ToConsole | TLoggingType.ToLogfile); } else { TLogging.Log( "Client '" + AUserName + "' tried to connect, but it failed the Login Checks. Aborting Client Connection!", TLoggingType.ToLogfile); } #endregion ConnectedClient.SessionStatus = TSessionStatus.adsStopped; // We need to set this flag to true here to get the failed login to be stored in the DB!!! SubmitOK = true; throw; } catch (Exception) { ConnectedClient.SessionStatus = TSessionStatus.adsStopped; throw; } #endregion // Login Checks were successful! ConnectedClient.SessionStatus = TSessionStatus.adsConnectingLoginOK; // Retrieve Welcome message and SiteKey try { if (UMaintenanceLogonMessage != null) { WelcomeMessage = UMaintenanceLogonMessage.GetLogonMessage(AUserName, true, ReadWriteTransaction); } else { WelcomeMessage = "Welcome"; } // we could do this directly, or via an interface, similar to LogonMessage, see above string sql = "SELECT s_default_value_c FROM s_system_defaults WHERE s_default_code_c = 'SiteKey'"; try { SiteKey = Convert.ToInt64(DBConnectionObj.ExecuteScalar(sql, ReadWriteTransaction)); } catch (EOPDBException) { // there is no site key defined yet. SiteKey = -1; } } catch (Exception) { ConnectedClient.SessionStatus = TSessionStatus.adsStopped; throw; } SubmitOK = true; }); #endregion /* * Uncomment the following statement to be able to better test how the * Client reacts when it tries to connect and receives a * ELoginFailedServerTooBusyException. */ // Thread.Sleep(7000); /* * Notify all waiting Clients (that have not timed out yet) that they can * now try to connect... */ // TODORemoting Monitor.PulseAll(UConnectClientMonitor); } // TODORemoting else { /* * Throw Exception to tell any timed-out connecting Client that the Server * is too busy to accept connect requests at the moment. */ // TODORemoting throw new ELoginFailedServerTooBusyException(); } } finally { // TODORemoting Monitor.Exit(UConnectClientMonitor); } ConnectedClient.StartSession(); #region Logging // // Assemblies successfully loaded into Client AppDomain // if (TLogging.DL >= 4) { TLogging.Log( "Client '" + AUserName + "' successfully connected. ClientID: " + AClientID.ToString(), TLoggingType.ToConsole | TLoggingType.ToLogfile); } else { TLogging.Log("Client '" + AUserName + "' successfully connected. ClientID: " + AClientID.ToString(), TLoggingType.ToLogfile); } #endregion ASystemEnabled = SystemEnabled; AWelcomeMessage = WelcomeMessage; ASiteKey = SiteKey; return(ConnectedClient); }
public static void Main(string[] args) { new TLogging("delivery/bin/Ict.Tools.DataMigrateStatistics.log"); new TAppSettingsManager(false); string row_count_location = TAppSettingsManager.GetValue("fulldumpPath", "delivery/bin/fulldump") + Path.DirectorySeparatorChar + "_row_count.txt"; if (File.Exists(row_count_location)) { TDataBase db = DBAccess.Connect("DataMigrateStatistics"); TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted); TCmdOpts cmdLine = new TCmdOpts(); string xmlfile = cmdLine.GetOptValue("petraxml"); TDataDefinitionParser parserNew = new TDataDefinitionParser(xmlfile, false); TDataDefinitionStore storeNew = new TDataDefinitionStore(); parserNew.ParseDocument(ref storeNew, false, true); List <TTable> newTables = storeNew.GetTables(); // table names and row numbers on alternate lines string[] checkRows = File.ReadAllLines(row_count_location); Console.WriteLine(); Console.WriteLine("--- Testing all rows have loaded successfully ---"); int totalRows = 0; // total number of rows in database int totalCheckRows = 0; // total number of rows there should be in the database bool rowsMissing = false; foreach (TTable newTable in newTables) { if (newTable.strName != "s_login") // ignore this table as the row count changes everytime a connection is made to the database { int i = 0; int rowCount; // number of rows actually in table int rowCountCheck = 0; // number of rows there should be in table // count rows in table string sql = "SELECT count(*) from " + newTable.strName + ";"; rowCount = Convert.ToInt32(db.ExecuteScalar(sql, Transaction)); // read how many rows there should be in the same table while (i < checkRows.Length) { if (checkRows[i] == newTable.strName) { rowCountCheck = Convert.ToInt32(checkRows[i + 1]); break; } i += 2; } totalRows += rowCount; totalCheckRows += rowCountCheck; // if there are rows missing if (rowCount != rowCountCheck) { Console.Write(newTable.strName + " is incomplete. "); Console.WriteLine((rowCountCheck - rowCount) + " out of " + rowCountCheck + " rows did not load!"); rowsMissing = true; } } } // if all rows are present if (!rowsMissing) { Console.WriteLine("All rows successfully loaded."); } // write a summary of the number of rows successfully loaded Console.WriteLine(); Console.WriteLine("--- Total number of rows loaded ---"); Console.WriteLine(totalRows + " out of " + totalCheckRows + " rows loaded successfully."); string percentage = (decimal.Divide(totalRows * 100, totalCheckRows)).ToString("#.##"); Console.WriteLine(percentage + "%"); Console.WriteLine(); db.CloseDBConnection(); } else { TLogging.Log("Warning: unable to perform a row count test. _row_count.txt is missing from the folder .../delivery/bin/fulldump."); TLogging.Log(""); } }
/// <summary> /// Returns the next sequence value for the given Sequence from the DB. /// </summary> /// <param name="ASequenceName">Name of the Sequence.</param> /// <param name="ATransaction">An instantiated Transaction in which the Query /// to the DB will be enlisted.</param> /// <param name="ADatabase">Database object that can be used for querying.</param> /// <returns>Sequence Value.</returns> public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase) { // TODO problem: sequence should be committed? separate transaction? // see also http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=44 // or use locking? see also http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=50 return Convert.ToInt64(ADatabase.ExecuteScalar("SELECT NEXTVAL('" + ASequenceName + "')", ATransaction)); }
/// <summary> /// Returns the current sequence value for the given Sequence from the DB. /// </summary> /// <param name="ASequenceName">Name of the Sequence.</param> /// <param name="ATransaction">An instantiated Transaction in which the Query /// to the DB will be enlisted.</param> /// <param name="ADatabase">Database object that can be used for querying.</param> /// <returns>Sequence Value.</returns> public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase) { return Convert.ToInt64(ADatabase.ExecuteScalar("SELECT last_value FROM " + ASequenceName + "", ATransaction)); }
/// throws an exception if the current user does not have enough permission to access the table (to read or write) static public bool CheckUserPermissionsForTable(string ATableName, TTablePermissionEnum APermissionRequested, bool ADontThrowException = false) { string UserID = string.Empty; bool Result = false; if (UserInfo.GetUserInfo() != null && UserInfo.GetUserInfo().UserID != null) { UserID = UserInfo.GetUserInfo().UserID; } if (UserID != string.Empty) { string sql = "SELECT COUNT(*) FROM PUB_s_module_table_access_permission mt, PUB_s_user_module_access_permission um " + "WHERE um.s_user_id_c = '" + UserID + "' " + "AND um.s_module_id_c = mt.s_module_id_c " + "AND mt.s_table_name_c = '" + ATableName + "' "; if ((APermissionRequested & TTablePermissionEnum.eCanRead) > 0) { sql += "AND mt.s_can_inquire_l = True "; } if ((APermissionRequested & TTablePermissionEnum.eCanCreate) > 0) { sql += "AND mt.s_can_create_l = True "; } if ((APermissionRequested & TTablePermissionEnum.eCanModify) > 0) { sql += "AND mt.s_can_modify_l = True "; } if ((APermissionRequested & TTablePermissionEnum.eCanDelete) > 0) { sql += "AND mt.s_can_delete_l = True "; } TDBTransaction t = new TDBTransaction(); TDataBase db = DBAccess.Connect("CheckUserPermissionsForTable"); db.ReadTransaction(ref t, delegate { Result = Convert.ToInt32(db.ExecuteScalar(sql, t)) > 0; }); db.CloseDBConnection(); } if (Result) { return(true); } if (!ADontThrowException) { throw new EOPAppException( "No " + APermissionRequested.ToString() + " permission for table " + ATableName + " for user " + UserID); } return(false); }