Пример #1
0
        public void DeriveInsertAggregateDataValues(DataTable dt,
                                                    long newSeriesID,
                                                    DateTime currentdate, DateTime lastdate, DeriveAggregateMode mode,
                                                    DeriveComputeMode computeMode,
                                                    double nodatavalue, IProgressHandler progressHandler)
        {
            const string insertQuery =
                "INSERT INTO DataValues(ValueID, SeriesID, DataValue, ValueAccuracy, LocalDateTime, UtcOffset, DateTimeUtc, OffsetValue, OffsetTypeID, CensorCode, QualifierID, SampleID, FileID) " +
                "VALUES ({0}, {1}, {2}, {3}, '{4}', {5}, '{6}', {7}, {8}, '{9}', {10}, {11}, {12});";

            const int chunkLength = 400;
            var       index       = 0;

            while (currentdate <= lastdate)
            {
                // Save values by chunks

                var newValueID = DbOperations.GetNextID("DataValues", "ValueID");
                var query      = new StringBuilder("BEGIN TRANSACTION; ");


                for (int i = 0; i <= chunkLength - 1; i++)
                {
                    double newvalue  = 0.0;
                    string sqlString = string.Empty;
                    double UTC       = 0.0;

                    switch (mode)
                    {
                    case DeriveAggregateMode.Daily:
                        sqlString = "LocalDateTime >= '" + currentdate.ToString(CultureInfo.InvariantCulture) + "' AND LocalDateTime <= '" +
                                    currentdate.AddDays(1).AddMilliseconds(-1).ToString(CultureInfo.InvariantCulture) + "' AND DataValue <> " +
                                    nodatavalue.ToString(CultureInfo.InvariantCulture);
                        break;

                    case DeriveAggregateMode.Monthly:
                        sqlString = "LocalDateTime >= '" + currentdate.ToString(CultureInfo.InvariantCulture) + "' AND LocalDateTime <= '" +
                                    currentdate.AddMonths(1).AddMilliseconds(-1).ToString(CultureInfo.InvariantCulture) + "' AND DataValue <> " +
                                    nodatavalue.ToString(CultureInfo.InvariantCulture);
                        break;

                    case DeriveAggregateMode.Quarterly:
                        sqlString = "LocalDateTime >= '" + currentdate.ToString(CultureInfo.InvariantCulture) +
                                    "' AND LocalDateTime <= '" +
                                    currentdate.AddMonths(3).AddMilliseconds(-1).ToString(
                            CultureInfo.InvariantCulture) + "' AND DataValue <> " +
                                    nodatavalue.ToString(CultureInfo.InvariantCulture);
                        break;
                    }
                    try
                    {
                        switch (computeMode)
                        {
                        case DeriveComputeMode.Maximum:
                            newvalue = Convert.ToDouble(dt.Compute("Max(DataValue)", sqlString));
                            break;

                        case DeriveComputeMode.Minimum:
                            newvalue = Convert.ToDouble(dt.Compute("MIN(DataValue)", sqlString));
                            break;

                        case DeriveComputeMode.Average:
                            newvalue = Convert.ToDouble(dt.Compute("AVG(DataValue)", sqlString));
                            break;

                        case DeriveComputeMode.Sum:
                            newvalue = Convert.ToDouble(dt.Compute("Sum(DataValue)", sqlString));
                            break;
                        }

                        UTC = Convert.ToDouble(dt.Compute("AVG(UTCOffset)", sqlString));
                    }
                    catch (Exception)
                    {
                        newvalue = nodatavalue;
                    }

                    query.AppendFormat(insertQuery,
                                       newValueID + i,
                                       newSeriesID,
                                       newvalue,
                                       0,
                                       Convert.ToDateTime(dt.Rows[index]["LocalDateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
                                       UTC.ToString(CultureInfo.InvariantCulture),
                                       currentdate.AddHours(UTC).ToString("yyyy-MM-dd HH:mm:ss"),
                                       "NULL",
                                       "NULL",
                                       "nc",
                                       "NULL",
                                       "NULL",
                                       "NULL");
                    query.AppendLine();

                    switch (mode)
                    {
                    case DeriveAggregateMode.Daily:
                        currentdate = currentdate.AddDays(1);
                        break;

                    case DeriveAggregateMode.Monthly:
                        currentdate = currentdate.AddMonths(1);
                        break;

                    case DeriveAggregateMode.Quarterly:
                        currentdate = currentdate.AddMonths(3);
                        break;
                    }

                    if (currentdate > lastdate)
                    {
                        break;
                    }
                    index = index + 1;

                    //Report progress
                    progressHandler.ReportProgress(index - 1, null);
                }

                query.AppendLine("COMMIT;");
                DbOperations.ExecuteNonQuery(query.ToString());

                progressHandler.ReportProgress(index - 1, null);
            }
        }
Пример #2
0
        public void DeriveInsertDataValues(double A, double B, double C, double D, double E, double F,
                                           DataTable dt,
                                           long newSeriesID, long sourceSeriesID, bool isAlgebraic, IProgressHandler progressHandler)
        {
            const int chunkLength = 400;
            var       nodatavalue = GetNoDataValueForSeriesVariable(newSeriesID);

            const string insertQuery =
                "INSERT INTO DataValues(ValueID, SeriesID, DataValue, ValueAccuracy, LocalDateTime, UtcOffset, DateTimeUtc, OffsetValue, OffsetTypeID, CensorCode, QualifierID, SampleID, FileID) " +
                "VALUES ({0}, {1}, {2}, {3}, '{4}', {5}, '{6}', {7}, {8}, '{9}', {10}, {11}, {12});";

            var index = 0;

            while (index != dt.Rows.Count - 1)
            {
                //Save values by chunks

                var newValueID = DbOperations.GetNextID("DataValues", "ValueID");
                var query      = new StringBuilder("BEGIN TRANSACTION; ");


                for (int i = 0; i < chunkLength; i++)
                {
                    // Calculating value
                    double newvalue = 0.0;
                    if (isAlgebraic)
                    {
                        var currentvalue = Convert.ToDouble(dt.Rows[index]["DataValue"]);
                        if (currentvalue != nodatavalue)
                        {
                            //NOTE:Equation = Fx ^ 5 + Ex ^ 4 + Dx ^ 3 + Cx ^ 2 + Bx + A
                            newvalue = (F * (Math.Pow(currentvalue, 5))) + (E * (Math.Pow(currentvalue, 4))) +
                                       (D * (Math.Pow(currentvalue, 3))) + (C * (Math.Pow(currentvalue, 2))) +
                                       (B * currentvalue) +
                                       A;
                            newvalue = Math.Round(newvalue, 5);
                        }
                        else
                        {
                            newvalue = nodatavalue;
                        }
                    }
                    else
                    {
                        newvalue = Convert.ToDouble(dt.Rows[index]["DataValue"]);
                    }

                    var row = dt.Rows[index];
                    query.AppendFormat(insertQuery,
                                       newValueID + i,
                                       newSeriesID,
                                       newvalue,
                                       row["ValueAccuracy"].ToString() == "" ? "NULL" : row["ValueAccuracy"].ToString(),
                                       Convert.ToDateTime(row["LocalDateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
                                       row["UTCOffset"].ToString(),
                                       Convert.ToDateTime(row["DateTimeUTC"]).ToString("yyyy-MM-dd HH:mm:ss"),
                                       row["OffsetValue"].ToString() == "" ? "NULL" : row["OffsetValue"].ToString(),
                                       row["OffsetTypeID"].ToString() == "" ? "NULL" : row["OffsetTypeID"].ToString(),
                                       row["CensorCode"].ToString(),
                                       row["QualifierID"].ToString() == "" ? "NULL" : row["QualifierID"].ToString(),
                                       row["SampleID"].ToString() == "" ? "NULL" : row["SampleID"].ToString(),
                                       row["FileID"].ToString() == "" ? "NULL" : row["FileID"].ToString());
                    query.AppendLine();

                    if (index == dt.Rows.Count - 1)
                    {
                        break;
                    }
                    index = index + 1;
                }

                query.AppendLine("COMMIT;");
                DbOperations.ExecuteNonQuery(query.ToString());

                progressHandler.ReportProgress(index, null);
            }
        }
Пример #3
0
        public DataTable AsDataTable()
        {
            var table = DbOperations.LoadTable(TableName, string.Format("Select * from {0}", TableName));

            return(table);
        }
Пример #4
0
        public Series GetSeriesByID(long seriesID)
        {
            var seriesTable = DbOperations.LoadTable("seriesTable", "select * from DataSeries where seriesID=" + seriesID);

            if (seriesTable.Rows.Count == 0)
            {
                return(null);
            }

            var     series    = new Series();
            DataRow seriesRow = seriesTable.Rows[0];

            series.BeginDateTime    = Convert.ToDateTime(seriesRow["BeginDateTime"]);
            series.EndDateTime      = Convert.ToDateTime(seriesRow["EndDateTime"]);
            series.BeginDateTimeUTC = Convert.ToDateTime(seriesRow["BeginDateTimeUTC"]);
            series.EndDateTimeUTC   = Convert.ToDateTime(seriesRow["EndDatetimeUTC"]);
            series.Id                  = seriesID;
            series.IsCategorical       = Convert.ToBoolean(seriesRow["IsCategorical"]);
            series.LastCheckedDateTime = Convert.ToDateTime(seriesRow["LastCheckedDateTime"]);
            series.UpdateDateTime      = Convert.ToDateTime(seriesRow["UpdateDateTime"]);
            series.Subscribed          = Convert.ToBoolean(seriesRow["Subscribed"]);
            series.ValueCount          = Convert.ToInt32(seriesRow["ValueCount"]);

            int siteID = Convert.ToInt32(seriesRow["SiteID"]);

            string sqlSites = "SELECT SiteID, SiteCode, SiteName, Latitude, Longitude, Elevation_m, " +
                              "VerticalDatum, LocalX, LocalY, State, County, Comments FROM Sites where SiteID = " + siteID;

            DataTable siteTable = DbOperations.LoadTable("siteTable", sqlSites);

            if (siteTable.Rows.Count == 0)
            {
                return(null);
            }

            DataRow siteRow = siteTable.Rows[0];
            var     newSite = new Site();

            newSite.Id            = Convert.ToInt32(siteRow[0]);
            newSite.Code          = Convert.ToString(siteRow[1]);
            newSite.Name          = Convert.ToString(siteRow[2]);
            newSite.Latitude      = Convert.ToDouble(siteRow[3]);
            newSite.Longitude     = Convert.ToDouble(siteRow[4]);
            newSite.Elevation_m   = Convert.ToDouble(siteRow[5]);
            newSite.VerticalDatum = Convert.ToString(siteRow[6]);
            newSite.LocalX        = Convert.ToDouble(siteRow["LocalX"]);
            newSite.LocalY        = Convert.ToDouble(siteRow["LocalY"]);
            series.Site           = newSite;

            int variableID = Convert.ToInt32(seriesRow["VariableID"]);

            series.Variable = RepositoryFactory.Instance.Get <IVariablesRepository>().GetByKey(variableID);

            var newMethod = new Method {
                Id = Convert.ToInt32(seriesRow["MethodID"])
            };

            series.Method = newMethod;

            var newSource = new Source {
                Id = Convert.ToInt32(seriesRow["SourceID"])
            };

            series.Source = newSource;

            var newQC = new QualityControlLevel {
                Id = Convert.ToInt32(seriesRow["QualityControlLevelID"])
            };

            series.QualityControlLevel = newQC;


            return(series);
        }
Пример #5
0
 public long GetNextID()
 {
     return(DbOperations.GetNextID(TableName, PrimaryKeyName));
 }
Пример #6
0
        public bool Exists(string name)
        {
            var res = DbOperations.ExecuteSingleOutput("Select count(*) FROM Units where UnitsName=?", new object[] { name });

            return(Convert.ToInt64(res) > 0);
        }
Пример #7
0
        /// <summary>
        /// Validate database schema.
        /// If schema not valid - InvalidDatabaseSchemaException throws.
        /// </summary>
        /// <param name="databaseToCheck">Path to SQLite checked database.</param>
        /// <param name="databaseType">Database type.</param>
        /// <exception cref="InvalidDatabaseSchemaException">Throws if database schema not valid.</exception>
        public static void CheckDatabaseSchema(string databaseToCheck, DatabaseType databaseType)
        {
            if (!DatabaseExists(databaseToCheck))
            {
                throw new InvalidDatabaseSchemaException("Database not exists");
            }

            var originalDbPath = Path.GetTempFileName();

            try
            {
                bool dbCreated;
                switch (databaseType)
                {
                case DatabaseType.DefaulDatabase:
                    dbCreated = CreateSQLiteDatabase(originalDbPath);
                    break;

                case DatabaseType.MetadataCacheDatabase:
                    dbCreated = CreateMetadataCacheDb(originalDbPath);
                    break;

                default:
                    dbCreated = false;
                    break;
                }
                if (!dbCreated)
                {
                    throw new InvalidDatabaseSchemaException("Unable to create original database");
                }

                var originalDbOperations = new DbOperations(GetSQLiteConnectionString(originalDbPath),
                                                            Interfaces.DatabaseTypes.SQLite);

                var checkedlDbOperations = new DbOperations(GetSQLiteConnectionString(databaseToCheck),
                                                            Interfaces.DatabaseTypes.SQLite);

                const string allTablesQuery = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";

                var originalTables = originalDbOperations.LoadTable(allTablesQuery);
                var checkedTables  = checkedlDbOperations.LoadTable(allTablesQuery);

                var sbErrors = new StringBuilder();
                foreach (DataRow row in originalTables.Rows)
                {
                    var tableName = row["name"].ToString();

                    // Check for table existing
                    var rows = checkedTables.Select(string.Format("name = '{0}'", tableName));
                    if (rows.Length == 0)
                    {
                        sbErrors.AppendLine(string.Format("Table '{0}' not found", tableName));
                        continue;
                    }

                    // Check for table schema
                    var schemaQuery    = string.Format("PRAGMA table_info({0})", tableName);
                    var originalSchema = originalDbOperations.LoadTable(schemaQuery);
                    var checkedSchema  = checkedlDbOperations.LoadTable(schemaQuery);

                    foreach (DataRow schemaRow in originalSchema.Rows)
                    {
                        var columnName = schemaRow["name"].ToString();

                        var columnRows = checkedSchema.Select(string.Format("name = '{0}'", columnName));
                        if (columnRows.Length == 0)
                        {
                            sbErrors.AppendLine(string.Format("Table '{0}': column '{1}' not found", tableName, columnName));
                        }
                    }
                }

                if (sbErrors.Length != 0)
                {
                    sbErrors.Remove(sbErrors.Length - Environment.NewLine.Length, Environment.NewLine.Length); // remove last new line delimeter
                    throw new InvalidDatabaseSchemaException(sbErrors.ToString());
                }
            }catch (Exception ex)
            {
                if (ex is InvalidDatabaseSchemaException)
                {
                    throw;
                }
                throw new InvalidDatabaseSchemaException("Unable to check database schema", ex);
            }
            finally
            {
                File.Delete(originalDbPath);
            }
        }
Пример #8
0
        public void UpdateValuesForEditView(DataTable table)
        {
            const string updateFormatString = "UPDATE DataValues SET DataValue = {0}, QualifierID = {1} WHERE ValueID = {2}; ";
            const string insertFormatString = "INSERT INTO DataValues (ValueID,SeriesID,DataValue,ValueAccuracy,LocalDateTime,UTCOffset,DateTimeUTC, " +
                                              "OffsetValue, OffsetTypeID, CensorCode, QualifierID, SampleID, FileID) VALUES (" +
                                              "{0},{1},{2},'{3}','{4}',{5},'{6}',{7},{8},'{9}',{10},{11},{12}) ;";

            var sqLstring2 = new StringBuilder();

            sqLstring2.Append("BEGIN TRANSACTION; ");

            var qualifierRepo = RepositoryFactory.Instance.Get <IQualifiersRepository>();

            for (int i = 0; i < table.Rows.Count; i++)
            {
                var row     = table.Rows[i];
                var valueID = Convert.ToInt64(row["ValueID"]);
                var other   = Convert.ToInt64(row["Other"]);
                if (other != 0)
                {
                    //Deleteing point
                    if (other == -1)
                    {
                        sqLstring2.AppendFormat("DELETE FROM DataValues WHERE ValueID = {0}; ", valueID);
                    }
                    else if (other == 1)
                    {
                        if (
                            String.IsNullOrEmpty(
                                DbOperations.ExecuteSingleOutput(
                                    "Select ValueID FROM DataValues WHERE ValueID = " + valueID).ToString()))
                        {
                            sqLstring2.AppendFormat(insertFormatString,
                                                    row[0],
                                                    row[1],
                                                    Convert.ToString(row[2], CultureInfo.InvariantCulture),
                                                    row[3] == DBNull.Value ? "NULL" : row[3],
                                                    Convert.ToDateTime(row[4]).ToString("yyyy-MM-dd HH:mm:ss",
                                                                                        CultureInfo.InvariantCulture),
                                                    row[5],
                                                    Convert.ToDateTime(row[6]).ToString("yyyy-MM-dd HH:mm:ss",
                                                                                        CultureInfo.InvariantCulture),
                                                    row[8] == DBNull.Value
                                                            ? "NULL"
                                                            : Convert.ToString(row[8], CultureInfo.InvariantCulture),
                                                    row[9] == DBNull.Value ? "NULL" : row[9],
                                                    row[10] == DBNull.Value ? "NULL" : row[10],
                                                    row[7] == DBNull.Value
                                                            ? "NULL"
                                                            : qualifierRepo.FindByCodeOrCreate(row[7].ToString()).Id.
                                                    ToString(CultureInfo.InvariantCulture),
                                                    row[11] == DBNull.Value ? "NULL" : row[11],
                                                    row[12] == DBNull.Value ? "NULL" : row[12]);
                        }
                    }
                    else if (other == 2)
                    {
                        sqLstring2.AppendFormat(updateFormatString,
                                                Convert.ToString(row["DataValue"], CultureInfo.InvariantCulture),
                                                qualifierRepo.FindByCodeOrCreate(row["QualifierCode"].ToString()).Id,
                                                valueID);
                    }
                }
            }

            if (!sqLstring2.ToString().TrimEnd().EndsWith(";"))
            {
                sqLstring2.Append(";");
            }


            sqLstring2.Append("COMMIT;");
            DbOperations.ExecuteNonQuery(sqLstring2.ToString());
        }
Пример #9
0
 public void DeleteById(long valueID)
 {
     DbOperations.ExecuteNonQuery("DELETE FROM DataValues WHERE ValueID =" + valueID);
 }