예제 #1
0
        private static bool LoadTable(string ATableName, TSimpleYmlParser AYmlParser, TDBTransaction ATransaction)
        {
            if (!AYmlParser.StartParseList(StringHelper.UpperCamelCase(ATableName, false, false) + "Table"))
            {
                // TLogging.Log("empty table " + ATableName + " " + StringHelper.UpperCamelCase(ATableName, false, false) + "Table");
                return false;
            }

            DataTable table = DBAccess.GDBAccessObj.SelectDT("Select * from " + ATableName, ATableName, ATransaction);
            List <OdbcParameter>Parameters = new List <OdbcParameter>();

            string OrigInsertStatement = "INSERT INTO pub_" + ATableName + "() VALUES ";
            StringBuilder InsertStatement = new StringBuilder(OrigInsertStatement);

            ConvertColumnNames(table.Columns);

            SortedList <string, string>RowDetails;
            int count = 0;

            while ((RowDetails = AYmlParser.GetNextLineAttributes()) != null)
            {
                count++;

                if (count != 1)
                {
                    if ((CommonTypes.ParseDBType(DBAccess.GDBAccessObj.DBType) == TDBType.SQLite) || ((count % 500) == 0))
                    {
                        // SQLite does not support INSERT of several rows at the same time
                        try
                        {
                            DBAccess.GDBAccessObj.ExecuteNonQuery(InsertStatement.ToString(), ATransaction, Parameters.ToArray());
                        }
                        catch (Exception e)
                        {
                            TLogging.Log("error in ResetDatabase, LoadTable " + ATableName + ":" + e.Message);
                            throw;
                        }

                        InsertStatement = new StringBuilder(OrigInsertStatement);
                        Parameters = new List <OdbcParameter>();
                    }
                    else
                    {
                        InsertStatement.Append(",");
                    }
                }

                InsertStatement.Append("(");

                // needed for workaround for version 0.2.24, AAPDocument.BaseCurrency and AAPPayment.BaseCurrency;
                if (ATableName == "a_ledger")
                {
                    FCurrencyPerLedger.Add(Convert.ToInt32(RowDetails["LedgerNumber"]), RowDetails["BaseCurrency"]);
                }

                bool firstColumn = true;

                foreach (DataColumn col in table.Columns)
                {
                    if (!firstColumn)
                    {
                        InsertStatement.Append(",");
                    }

                    firstColumn = false;

                    if (RowDetails.ContainsKey(col.ColumnName))
                    {
                        string strValue = RowDetails[col.ColumnName];

                        if (col.DataType == typeof(DateTime))
                        {
                            OdbcParameter p;

                            if (strValue.Length == "yyyy-MM-dd".Length)
                            {
                                p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Date);
                                p.Value = DateTime.ParseExact(strValue, "yyyy-MM-dd", CultureInfo.InvariantCulture);
                            }
                            else
                            {
                                p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.DateTime);
                                p.Value = DateTime.ParseExact(strValue, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
                            }

                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(String))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
                            p.Value = strValue.Replace("&quot;", "\"");
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(Int32))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Int);
                            p.Value = Convert.ToInt32(strValue);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(Int64))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
                            p.Value = Convert.ToInt64(strValue);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(double))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
                            p.Value = Convert.ToDouble(strValue, CultureInfo.InvariantCulture);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(bool))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Bit);
                            p.Value = Convert.ToBoolean(strValue);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(Decimal))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
                            p.Value = Convert.ToDecimal(strValue, CultureInfo.InvariantCulture);
                            Parameters.Add(p);
                        }
                        else
                        {
                            // should not get here?
                            throw new Exception("error in ResetDatabase, LoadTable: " + col.DataType.ToString() + " has not yet been implemented");
                        }

                        InsertStatement.Append("?");
                    }
                    else
                    {
                        // the following statements are for the demo databases generated before version 0.2.24.
                        // CurrencyCode was added to a_ap_document and a_ap_payment.
                        // it is impossible during the load, to get the correct currencycode, via the supplier, because a_ap_supplier is loaded after a_ap_document.
                        // as a temporary workaround, and because we are still in Alpha, we are using the Base currency of the ledger
                        if ((ATableName == "a_ap_document") && (col.ColumnName == "CurrencyCode"))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
                            p.Value = FCurrencyPerLedger[Convert.ToInt32(RowDetails["LedgerNumber"])];
                            Parameters.Add(p);
                            InsertStatement.Append("?");
                        }
                        else if ((ATableName == "a_ap_payment") && (col.ColumnName == "CurrencyCode"))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
                            p.Value = FCurrencyPerLedger[Convert.ToInt32(RowDetails["LedgerNumber"])];
                            Parameters.Add(p);
                            InsertStatement.Append("?");
                        }
                        else
                        {
                            InsertStatement.Append("NULL"); // DEFAULT
                        }
                    }
                }

                InsertStatement.Append(")");
            }

            try
            {
                DBAccess.GDBAccessObj.ExecuteNonQuery(InsertStatement.ToString(), ATransaction, Parameters.ToArray());
            }
            catch (Exception e)
            {
                TLogging.Log("error in ResetDatabase, LoadTable " + ATableName + ":" + e.Message);
                throw;
            }
            return true;
        }
예제 #2
0
        public static bool ResetDatabase(string AZippedNewDatabaseData)
        {
            List <string>tables = TTableList.GetDBNames();

            TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
                Catalog.GetString("Importing database"),
                tables.Count + 3);

            TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);

            try
            {
                tables.Reverse();

                TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
                    Catalog.GetString("deleting current data"),
                    0);

                foreach (string table in tables)
                {
                    DBAccess.GDBAccessObj.ExecuteNonQuery("DELETE FROM pub_" + table, Transaction);
                }

                if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
                {
                    TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
                    DBAccess.GDBAccessObj.RollbackTransaction();
                    return false;
                }

                TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData));

                ymlParser.ParseCaptions();

                tables.Reverse();

                TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
                    Catalog.GetString("loading initial tables"),
                    1);

                // one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails?
                bool success = true;
                success = success && LoadTable("s_user", ymlParser, Transaction);
                success = success && LoadTable("s_module", ymlParser, Transaction);
                success = success && LoadTable("s_user_module_access_permission", ymlParser, Transaction);
                success = success && LoadTable("s_system_defaults", ymlParser, Transaction);
                success = success && LoadTable("s_system_status", ymlParser, Transaction);

                // make sure we have the correct database version
                TFileVersionInfo serverExeInfo = new TFileVersionInfo(TSrvSetting.ApplicationVersion);
                DBAccess.GDBAccessObj.ExecuteNonQuery(String.Format(
                        "UPDATE PUB_s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion'",
                        serverExeInfo.ToString()), Transaction);

                if (!success)
                {
                    DBAccess.GDBAccessObj.RollbackTransaction();
                    return false;
                }

                if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
                {
                    TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
                    DBAccess.GDBAccessObj.RollbackTransaction();
                    return false;
                }

                DBAccess.GDBAccessObj.CommitTransaction();

                tables.Remove("s_user");
                tables.Remove("s_module");
                tables.Remove("s_user_module_access_permission");
                tables.Remove("s_system_defaults");
                tables.Remove("s_system_status");

                FCurrencyPerLedger = new SortedList <int, string>();

                Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);

                int tableCounter = 2;

                foreach (string table in tables)
                {
                    TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
                        String.Format(Catalog.GetString("loading table {0}"), table),
                        tableCounter);

                    tableCounter++;

                    if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
                    {
                        TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
                        DBAccess.GDBAccessObj.RollbackTransaction();
                        return false;
                    }

                    LoadTable(table, ymlParser, Transaction);
                }

                TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
                    Catalog.GetString("loading sequences"),
                    tables.Count + 5 + 3);

                // set sequences appropriately, not lagging behind the imported data
                foreach (string seq in TTableList.GetDBSequenceNames())
                {
                    LoadSequence(seq, ymlParser, Transaction);
                }

                TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
                    Catalog.GetString("finish import"),
                    tables.Count + 5 + 4);

                DBAccess.GDBAccessObj.CommitTransaction();

                // reset all cached tables
                TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing();

                TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
            }
            catch (Exception e)
            {
                TLogging.Log("Problem in ResetDatabase: " + e.Message);
                TLogging.Log(e.StackTrace);
                DBAccess.GDBAccessObj.RollbackTransaction();
                return false;
            }

            return true;
        }
예제 #3
0
        private static bool LoadSequence(string ASequenceName, TSimpleYmlParser AYmlParser, TDBTransaction ATransaction)
        {
            if (!AYmlParser.StartParseList("Sequences"))
            {
                return false;
            }

            SortedList <string, string>RowDetails;

            while ((RowDetails = AYmlParser.GetNextLineAttributes()) != null)
            {
                if (RowDetails["RowName"] == StringHelper.UpperCamelCase(ASequenceName, false, false))
                {
                    DBAccess.GDBAccessObj.RestartSequence(
                        ASequenceName,
                        ATransaction,
                        Convert.ToInt64(RowDetails["value"]));

                    return true;
                }
            }

            return false;
        }
예제 #4
0
        public static bool ResetDatabase(string AZippedNewDatabaseData)
        {
            List <string>tables = TTableList.GetDBNames();
            bool SubmissionResult = false;
            TDBTransaction Transaction = null;

            string ClientID = "ClientID";

            try
            {
                ClientID = DomainManager.GClientID.ToString();
            }
            catch (Exception)
            {
            }

            TProgressTracker.InitProgressTracker(ClientID,
                Catalog.GetString("Restoring Database..."),
                tables.Count + 3);

            DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction,
                ref SubmissionResult,
                delegate
                {
                    try
                    {
                        tables.Reverse();

                        TProgressTracker.SetCurrentState(ClientID,
                            Catalog.GetString("Deleting current data..."),
                            0);

                        foreach (string table in tables)
                        {
                            DBAccess.GDBAccessObj.ExecuteNonQuery("DELETE FROM pub_" + table, Transaction);
                        }

                        if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
                        {
                            TProgressTracker.FinishJob(ClientID);

                            // As SubmissionResult is still false, a DB Transaction Rollback will get
                            // executed automatically and the Method will be exited with return value 'false'!
                            return;
                        }

                        TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData));

                        ymlParser.ParseCaptions();

                        tables.Reverse();

                        TProgressTracker.SetCurrentState(ClientID,
                            Catalog.GetString("Loading initial tables..."),
                            1);

                        // one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails?
                        bool success = true;
                        success = success && LoadTable("s_user", ymlParser, Transaction);
                        success = success && LoadTable("s_module", ymlParser, Transaction);
                        success = success && LoadTable("s_user_module_access_permission", ymlParser, Transaction);
                        success = success && LoadTable("s_system_defaults", ymlParser, Transaction);
                        success = success && LoadTable("s_system_status", ymlParser, Transaction);

                        // make sure we have the correct database version
                        TFileVersionInfo serverExeInfo = new TFileVersionInfo(TSrvSetting.ApplicationVersion);
                        DBAccess.GDBAccessObj.ExecuteNonQuery(String.Format(
                                "UPDATE PUB_s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion'",
                                serverExeInfo.ToString()), Transaction);

                        if (!success)
                        {
                            // As SubmissionResult is still TSubmitChangesResult.scrError, a DB Transaction Rollback will get
                            // executed automatically and the Method will be exited with return value 'false'!
                            return;
                        }

                        if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
                        {
                            TProgressTracker.FinishJob(ClientID);

                            // As SubmissionResult is still false, a DB Transaction Rollback will get
                            // executed automatically and the Method will be exited with return value 'false'!
                            return;
                        }

                        tables.Remove("s_user");
                        tables.Remove("s_module");
                        tables.Remove("s_user_module_access_permission");
                        tables.Remove("s_system_defaults");
                        tables.Remove("s_system_status");

                        FCurrencyPerLedger = new SortedList <int, string>();

                        int tableCounter = 2;

                        foreach (string table in tables)
                        {
                            TProgressTracker.SetCurrentState(ClientID,
                                String.Format(Catalog.GetString("Loading Table {0}..."), table),
                                tableCounter);

                            tableCounter++;

                            if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
                            {
                                TProgressTracker.FinishJob(ClientID);

                                // As SubmissionResult is still false, a DB Transaction Rollback will get
                                // executed automatically and the Method will be exited with return value 'false'!
                                return;
                            }

                            LoadTable(table, ymlParser, Transaction);
                        }

                        TProgressTracker.SetCurrentState(ClientID,
                            Catalog.GetString("Loading Sequences..."),
                            tables.Count + 5 + 3);

                        // set sequences appropriately, not lagging behind the imported data
                        foreach (string seq in TTableList.GetDBSequenceNames())
                        {
                            LoadSequence(seq, ymlParser, Transaction);
                        }

                        TProgressTracker.SetCurrentState(ClientID,
                            Catalog.GetString("Finishing Restore..."),
                            tables.Count + 5 + 4);

                        SubmissionResult = true;

                        // reset all cached tables
                        TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing();

                        TProgressTracker.FinishJob(ClientID);
                    }
                    catch (Exception e)
                    {
                        TLogging.Log("Problem in ResetDatabase: " + e.ToString());
                        TLogging.LogStackTrace(TLoggingType.ToLogfile);

                        throw;
                    }
                });

            return SubmissionResult;
        }