Example #1
0
        public DataTable GetSeriesIDsWithNoDataValueTable(IEnumerable <int?> themeIDs)
        {
            var themeList = themeIDs.ToList();

            var          hasNulls       = themeList.Any(value => !value.HasValue);
            var          notNullsFilter = new StringBuilder();
            const string separator      = ", ";

            foreach (var themeID in themeList.Where(themeID => themeID.HasValue))
            {
                notNullsFilter.Append(themeID + separator);
            }
            if (notNullsFilter.Length > 0)
            {
                notNullsFilter.Remove(notNullsFilter.Length - separator.Length, separator.Length);
            }

            var query = "SELECT v.NoDataValue, ds.SeriesID " +
                        "FROM DataSeries ds INNER JOIN variables v ON ds.VariableID = v.VariableID " +
                        "LEFT JOIN DataThemes t ON ds.SeriesID = t.SeriesID " +
                        "WHERE t.ThemeID in (" + notNullsFilter + ")";

            if (hasNulls)
            {
                query += " or t.ThemeID is null";
            }

            var dtSeries = DbOperations.LoadTable("series", query);

            return(dtSeries);
        }
        public DataTable GetAllOrderByLocalDateTime(long seriesID)
        {
            var query  = "SELECT * FROM DataValues WHERE SeriesID = " + seriesID + " ORDER BY LocalDateTime";
            var result = DbOperations.LoadTable(TableName, query);

            return(result);
        }
        /// <summary>
        /// Deletes a theme and all its series as long as the series don't belong to any other theme.
        /// </summary>
        /// <param name="themeID">The Theme ID</param>
        /// <returns>true if successful, false otherwise</returns>
        public bool DeleteTheme(int themeID)
        {
            string    sqlTheme  = "SELECT SeriesID FROM DataThemes where ThemeID = " + themeID;
            DataTable tblSeries = DbOperations.LoadTable("tblSeries", sqlTheme);

            foreach (DataRow seriesRow in tblSeries.Rows)
            {
                int seriesID = Convert.ToInt32(seriesRow["SeriesID"]);

                var seriesRepository = RepositoryFactory.Instance.Get <IDataSeriesRepository>();
                seriesRepository.DeleteSeries(seriesID);
            }

            //delete the actual theme
            string sqlDeleteTheme = "DELETE FROM DataThemeDescriptions WHERE ThemeID = " + themeID;

            try
            {
                DbOperations.ExecuteNonQuery(sqlDeleteTheme);
            }
            catch { };

            //re-check the number of series in the theme

            return(true);
        }
        public DataTable GetThemesTableForThemeManager(long?themeID)
        {
            var sql =
                "SELECT src.Organization as 'DataSource', ds.SeriesID, " +
                "s.SiteName as 'SiteName', s.Latitude as 'Latitude', s.Longitude as 'Longitude', s.SiteCode as 'SiteCode', " +
                "v.VariableName as 'VarName', v.DataType as 'DataType', v.SampleMedium as 'SampleMed', " +
                "v.VariableCode as 'VarCode', u.UnitsName as 'Units', " +
                "v.VariableCode as 'ServiceCode', " +
                "m.MethodDescription as 'Method', qc.Definition as 'QCLevel', " +
                "ds.BeginDateTime as 'StartDate', ds.EndDateTime as 'EndDate', ds.ValueCount as 'ValueCount', " +
                "null as 'ServiceURL' " +
                "FROM DataSeries ds " +
                "LEFT JOIN DataThemes dt on dt.SeriesID = ds.SeriesID " +
                "LEFT JOIN Sites s on ds.SiteID = s.SiteID " +
                "LEFT JOIN Variables v on ds.VariableID = v.VariableID " +
                "LEFT JOIN Units u on u.UnitsID = v.VariableUnitsID " +
                "LEFT JOIN Methods m on ds.MethodID = m.MethodID " +
                "LEFT JOIN Sources src on ds.SourceID = src.SourceID " +
                "LEFT JOIN QualityControlLevels qc on ds.QualityControlLevelID = qc.QualityControlLevelID " +
                (themeID.HasValue ? "WHERE dt.ThemeID = " + themeID : "WHERE dt.ThemeID is null");

            var table = DbOperations.LoadTable("ThemeTable", sql);

            return(table);
        }
        public DataTable GetAll(long seriesID)
        {
            var query  = "SELECT * FROM DataValues WHERE SeriesID = " + seriesID;
            var result = DbOperations.LoadTable(TableName, query);

            return(result);
        }
Example #6
0
        public Unit GetByName(string name)
        {
            var dt = DbOperations.LoadTable(TableName, string.Format("Select * FROM Units where UnitsName='{0}'", name));
            if (dt == null || dt.Rows.Count == 0)
                return null;

            var res = DataRowToEntity(dt.Rows[0]);
            return res;
        }
Example #7
0
        public DataTable GetDetailedSeriesTable()
        {
            string sql = DetailedSeriesSQLQuery();

            sql += " ORDER BY VariableName, SiteName";
            var table = DbOperations.LoadTable("SeriesListTable", sql);

            return(table);
        }
Example #8
0
        public DataTable GetSeriesTable(string seriesDataFilter)
        {
            string sql = DetailedSeriesSQLQuery() +
                         " WHERE " + seriesDataFilter;

            var table = DbOperations.LoadTable("SeriesListTable", sql);

            return(table);
        }
        public DataTable GetTableForEditView(long seriesID)
        {
            var query = "SELECT ValueID, SeriesID, DataValue, ValueAccuracy, LocalDateTime, UTCOffset, " +
                        "DateTimeUTC, QualifierCode, OffsetValue, OffsetTypeID, CensorCode, SampleID, " +
                        "FileID, 0 as Other FROM DataValues AS d LEFT JOIN Qualifiers AS q ON (d.QualifierID = q.QualifierID) " +
                        "WHERE SeriesID = " + seriesID;
            var table = DbOperations.LoadTable(TableName, query);

            return(table);
        }
        public DataTable GetTableForExportFromTimeSeriesPlot(long seriesID)
        {
            var query =
                "SELECT ds.SeriesID, s.SiteName, v.VariableName, dv.DataValue, dv.LocalDateTime, U.UnitsName, S.Citation " +
                "FROM DataSeries ds, Sites s, Variables v, DataValues dv, Units U, Sources S " +
                "WHERE v.VariableID = ds.VariableID AND s.SiteID = ds.SiteID AND dv.SeriesID = ds.SeriesID AND U.UnitsID = v.VariableUnitsID AND ds.SeriesID = " +
                seriesID + " AND S.SourceID = ds.SourceID ";

            return(DbOperations.LoadTable(TableName, query));
        }
        /// <summary>
        /// Delete a theme - a background worker and progress bar is used
        /// </summary>
        /// <param name="themeID">The themeID (this needs to be a valid ID)</param>
        /// <param name="worker">The background worker component</param>
        /// <param name="e">The arguments for background worker</param>
        /// <returns></returns>
        public bool DeleteTheme(int themeID, BackgroundWorker worker, DoWorkEventArgs e)
        {
            string    sqlTheme  = "SELECT SeriesID FROM DataThemes where ThemeID = " + themeID;
            DataTable tblSeries = DbOperations.LoadTable("tblSeries", sqlTheme);

            int numSeries = tblSeries.Rows.Count;
            int count     = 0;

            if (numSeries == 0)
            {
                return(false);
            }

            foreach (DataRow seriesRow in tblSeries.Rows)
            {
                if (worker != null)
                {
                    //check cancellation
                    if (e != null && worker.CancellationPending)
                    {
                        e.Cancel = true;
                        return(false);
                    }
                }

                int seriesID = Convert.ToInt32(seriesRow["SeriesID"]);

                var seriesRepository = RepositoryFactory.Instance.Get <IDataSeriesRepository>();
                seriesRepository.DeleteSeries(seriesID);

                //progress report
                count++;

                if (worker != null && worker.WorkerReportsProgress)
                {
                    var percent   = (int)(((float)count / (float)numSeries) * 100);
                    var userState = "Deleting series " + count + " of " + numSeries + "...";
                    worker.ReportProgress(percent, userState);
                }
            }

            //delete the actual theme

            string sqlDeleteTheme = "DELETE FROM DataThemeDescriptions WHERE ThemeID = " + themeID;

            try
            {
                DbOperations.ExecuteNonQuery(sqlDeleteTheme);
                e.Result = "Theme deleted successfully";
            }
            catch { };

            return(true);
        }
        public Qualifier FindByCode(string qualifierCode)
        {
            var res = DbOperations.LoadTable(string.Format("select * from {0} where QualifierCode='{1}'", TableName,
                                                           qualifierCode));

            if (res.Rows.Count == 0)
            {
                return(null);
            }
            return(DataRowToEntity(res.Rows[0]));
        }
Example #13
0
        public DataTable GetUnitSiteVarForFirstSeries(long seriesID)
        {
            var sqlQuery = string.Format("SELECT UnitsName, SiteName, VariableName FROM DataSeries " +
                                         "INNER JOIN Variables ON Variables.VariableID = DataSeries.VariableID " +
                                         "INNER JOIN Units ON Variables.VariableUnitsID = Units.UnitsID " +
                                         "INNER JOIN Sites ON Sites.SiteID = DataSeries.SiteID WHERE SeriesID = {0} limit 1",
                                         seriesID);
            var seriesNameTable = DbOperations.LoadTable("table", sqlQuery);

            return(seriesNameTable);
        }
Example #14
0
        public T GetByKey(object key)
        {
            var table = DbOperations.LoadTable(TableName,
                                               string.Format("select * from {0} where {1}={2} LIMIT 1", TableName, PrimaryKeyName, key));

            if (table.Rows.Count == 0)
            {
                return(default(T));
            }
            return(DataRowToEntity(table.Rows[0]));
        }
        public DataTable GetThemesForAllSeries()
        {
            var dtThemes = DbOperations.LoadTable(TableName, "SELECT ThemeID, ThemeName from DataThemeDescriptions");

            if (Int32.Parse(DbOperations.ExecuteSingleOutput("Select count(*) from DataSeries " +
                                                             "Where SeriesID not in (Select SeriesID from DataThemes)").
                            ToString()) > 0)
            {
                dtThemes.Rows.Add(DBNull.Value, OTHER_DATA_SERIES);
            }
            return(dtThemes);
        }
Example #16
0
        public UnitConversion GetConversion(Unit unitA, Unit unitB)
        {
            var table = DbOperations.LoadTable(
                string.Format("select * from UnitConversions where FromUnitsID = {0} and ToUnitsID = {1}",
                              unitA.Id, unitB.Id));

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

            return(DataRowToEntity(table.Rows[0]));
        }
Example #17
0
        public DataTable GetTableForAllFieldsInSequence(IList <int> seriesIDs, int valuesPerPage, int currentPage)
        {
            var whereClause = GetWhereClauseForIds(seriesIDs);
            var dataQuery   =
                "SELECT ValueID, SeriesID, DataValue, LocalDateTime, UTCOffset, CensorCode FROM DataValues WHERE " +
                whereClause;
            var limitQuery = string.Format("{0} limit {1} offset {2}", dataQuery, valuesPerPage,
                                           currentPage * valuesPerPage);

            var table = DbOperations.LoadTable(limitQuery);

            return(table);
        }
        public DataTable GetTableForGraphView(long seriesID, double nodatavalue, DateTime startDate, DateTime endDate)
        {
            var strStartDate   = startDate.ToString("yyyy-MM-dd HH:mm:ss");
            var strEndDate     = endDate.ToString("yyyy-MM-dd HH:mm:ss");
            var strNoDataValue = nodatavalue.ToString(CultureInfo.InvariantCulture);

            var query =
                "SELECT DataValue, LocalDateTime, CensorCode, strftime('%m', LocalDateTime) as DateMonth, strftime('%Y', LocalDateTime) as DateYear FROM DataValues WHERE (SeriesID = " +
                +seriesID + ") AND (DataValue <> " + strNoDataValue + ") AND (LocalDateTime between '" + strStartDate +
                "' AND '" + strEndDate + "')  ORDER BY LocalDateTime";
            var table = DbOperations.LoadTable("DataValues", query);

            return(table);
        }
Example #19
0
        public IList <Series> GetAllSeriesForSite(Site mySite)
        {
            if (mySite.Id <= 0)
            {
                throw new ArgumentException("The site must have a valid ID");
            }
            Contract.EndContractBlock();

            string sqlQuery = DetailedSeriesSQLQuery();

            sqlQuery += string.Format(" WHERE DataSeries.SiteID = {0}", mySite.Id);
            DataTable tbl = DbOperations.LoadTable(sqlQuery);

            return(SeriesListFromTable(tbl));
        }
        public DataTable GetTableForAllFieldsInSequence(IList <int> seriesIDs, int valuesPerPage, int currentPage)
        {
            var whereClause = GetWhereClauseForIds(seriesIDs);
            var dataQuery   =
                "SELECT ValueID, SeriesID, DataValue, LocalDateTime, UTCOffset, CensorCode, OffsetValue, OT.OffsetDescription, Un.UnitsAbbreviation as OffsetUnits FROM DataValues DV " +
                "LEFT JOIN OffsetTypes OT on DV.OffsetTypeId = OT.OffsetTypeId " +
                "LEFT JOIN Units Un on Un.UnitsID = OT.OffsetUnitsId " +
                "WHERE " +
                whereClause;
            var limitQuery = string.Format("{0} limit {1} offset {2}", dataQuery, valuesPerPage,
                                           currentPage * valuesPerPage);

            var table = DbOperations.LoadTable(limitQuery);

            return(table);
        }
        public DataTable GetTableForExport(long seriesID, double?noDataValue = null, string dateColumn = null, DateTime?firstDate = null, DateTime?lastDate = null)
        {
            var sql =
                "SELECT ds.SeriesID, s.SiteName, v.VariableName, dv.LocalDateTime, dv.DataValue, U1.UnitsName As VarUnits, v.DataType, s.SiteID, s.SiteCode, v.VariableID, v.VariableCode, " +
                "S.Organization, S.SourceDescription, S.SourceLink, S.Citation, v.ValueType, v.TimeSupport, U2.UnitsName As TimeUnits, v.IsRegular, v.NoDataValue, " +
                "dv.UTCOffset, dv.DateTimeUTC, s.Latitude, s.Longitude, dv.ValueAccuracy, dv.CensorCode, m.MethodDescription, q.QualityControlLevelCode, v.SampleMedium, v.GeneralCategory, " +
                "OffsetValue, OT.OffsetDescription, Un.UnitsAbbreviation as OffsetUnits, QL.QualifierCode " +
                "FROM DataSeries ds, Sites s, Variables v, DataValues dv, Units U1, Units U2, Methods m, QualityControlLevels q, Sources S " +
                "LEFT JOIN OffsetTypes OT on DV.OffsetTypeId = OT.OffsetTypeId " +
                "LEFT JOIN Units Un on Un.UnitsID = OT.OffsetUnitsId " +
                "LEFT JOIN Qualifiers QL on QL.QualifierID = dv.QualifierID " +
                "WHERE v.VariableID = ds.VariableID " +
                "AND s.SiteID = ds.SiteID " +
                "AND m.MethodID = ds.MethodID " +
                "AND q.QualityControlLevelID = ds.QualityControlLevelID " +
                "AND S.SourceID = ds.SourceID " +
                "AND dv.SeriesID = ds.SeriesID " +
                "AND U1.UnitsID = v.VariableUnitsID " +
                "AND U2.UnitsID = v.TimeUnitsID " +
                "AND ds.SeriesID = " + seriesID;

            if (noDataValue.HasValue)
            {
                sql += " AND dv.DataValue != " + noDataValue;
            }

            var cmd = DbOperations.CreateCommand(sql);

            // Append date range filter
            if (!string.IsNullOrEmpty(dateColumn) &&
                firstDate.HasValue && lastDate.HasValue)
            {
                cmd.CommandText += string.Format(" AND ({0} >=  @p1 and {0} <=  @p2)", dateColumn);
                var startDateParameter = DbOperations.AddParameter(cmd, "@p1", DbType.DateTime);
                var endDateParemater   = DbOperations.AddParameter(cmd, "@p2", DbType.DateTime);

                startDateParameter.Value = firstDate.Value;
                endDateParemater.Value   = lastDate.Value;
            }

            var tbl = DbOperations.LoadTable("values", cmd);

            return(tbl);
        }
Example #22
0
        public Method GetMethod(int methodID)
        {
            var dt = DbOperations.LoadTable(string.Format("select MethodID, MethodDescription,  MethodLink from Methods where MethodID = {0}", methodID));

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

            var methodRow = dt.Rows[0];

            return(new Method
            {
                Id = Convert.ToInt32(methodRow["MethodID"]),
                Code = Convert.ToInt32(methodRow["MethodID"]),
                Description = Convert.ToString(methodRow["MethodDescription"]),
                Link = Convert.ToString(methodRow["MethodLink"]),
            });
        }
Example #23
0
        public int InsertNewSeries(long sourceSeriesID, long variableID, long methodID, long qualityControlLevelID)
        {
            var newSeriesID = DbOperations.GetNextID("DataSeries", "SeriesID");
            var dt          = DbOperations.LoadTable("DataSeries", "SELECT * FROM DataSeries WHERE SeriesID = " + sourceSeriesID);
            var row         = dt.Rows[0];

            //Making the INSERT SQL string for the new data series
            var sqlString = new StringBuilder();

            sqlString.Append("INSERT INTO DataSeries(SeriesID, SiteID, VariableID, IsCategorical, MethodID, SourceID, ");
            sqlString.Append("QualityControlLevelID, BeginDateTime, EndDateTime, BeginDateTimeUTC, EndDateTimeUTC, ");
            sqlString.Append("ValueCount, CreationDateTime, Subscribed, UpdateDateTime, LastcheckedDateTime) Values (");
            //SeriesID value
            sqlString.Append(newSeriesID + ", ");
            //SiteID value
            sqlString.Append(Convert.ToInt64(row[1]) + ", ");
            //VariableID values
            sqlString.Append(variableID + ", ");
            //IsCategorical value
            sqlString.Append(row[3].ToString() == "True" ? "1, " : "0, ");
            //MethodID value
            sqlString.Append(methodID + ", ");
            //SourceID value
            sqlString.Append(Convert.ToInt64(row[5]) + ", ");
            //QualityControlLevelID value
            sqlString.Append(qualityControlLevelID + ", ");
            //BeginDateTime, EndDateTime, BeginDateTimeUTC and EndDateTimeUTC values
            for (int i = 7; i <= 10; i++)
            {
                var tempstring = Convert.ToDateTime(row[i]).ToString("yyyy-MM-dd HH:mm:ss");
                sqlString.Append("'" + tempstring + "', ");
            }
            var todaystring = DateTime.Today.ToString("yyyy-MM-dd HH:mm:ss");

            //ValueCount, CreationDateTime, Subscribed, UpdateDateTime and LastcheckedDateTime values
            sqlString.Append(row[11] + ", '" + todaystring + "', 0, '" + todaystring + "','" + todaystring +
                             "')");
            //Execute the SQL string
            DbOperations.ExecuteNonQuery(sqlString.ToString());

            return(newSeriesID);
        }
Example #24
0
        public IList <Site> GetSitesWithBothVariables(Variable variable1, Variable variable2)
        {
            if (variable1.Id <= 0)
            {
                throw new ArgumentException("variable1 must have a valid ID");
            }
            if (variable2.Id <= 0)
            {
                throw new ArgumentException("variable2 must have a valid ID");
            }

            string sqlQuery = String.Format("select s1.SeriesID as 'SeriesID1', s2.SeriesID as 'SeriesID2', " +
                                            "site.SiteID, site.SiteName, site.SiteCode, site.Latitude, site.Longitude " +
                                            "FROM DataSeries s1 INNER JOIN DataSeries s2 ON s1.SiteID = s2.SiteID " +
                                            "INNER JOIN Sites site ON s1.SiteID = site.SiteID " +
                                            "WHERE s1.VariableID = {0} AND s2.VariableID = {1}", variable1.Id, variable2.Id);

            DataTable   tbl      = DbOperations.LoadTable(sqlQuery);
            List <Site> siteList = new List <Site>();

            foreach (DataRow r in tbl.Rows)
            {
                Site s = new Site();
                s.Id        = (long)r["SiteID"];
                s.Code      = (string)r["SiteCode"];
                s.Latitude  = (double)r["Latitude"];
                s.Longitude = (double)r["Longitude"];
                s.Name      = (string)r["SiteName"];

                Series s1 = new Series(s, variable1, Method.Unknown, QualityControlLevel.Unknown, Source.Unknown);
                s1.Id = (long)r["SeriesID1"];
                s.AddDataSeries(s1);

                Series s2 = new Series(s, variable2, Method.Unknown, QualityControlLevel.Unknown, Source.Unknown);
                s2.Id = (long)r["SeriesID2"];
                s.AddDataSeries(s2);

                siteList.Add(s);
            }
            return(siteList);
        }
        public DataTable GetTableForJustValuesInParallel(IList <int> seriesIDs, int valuesPerPage, int currentPage)
        {
            /*
             * Example of builded query:
             *
             * select
             *   A.LocalDateTime as DateTime,
             *   (select  DV1.DataValue from DataValues DV1 where DV1.LocalDateTime = A.LocalDateTime and DV1.seriesId = 1 limit 1) as D1,
             *   (select  DV2.DataValue from DataValues DV2 where DV2.LocalDateTime = A.LocalDateTime and DV2.seriesId = 2 limit 1) as D2
             * from
             *   (select distinct LocalDateTime from DataValues where seriesId in (1,2)) A
             * order by LocalDateTime
             *
             */


            var whereClause      = GetWhereClauseForIds(seriesIDs);
            var dataQueryBuilder = new StringBuilder();

            dataQueryBuilder.Append("select A.LocalDateTime as DateTime");
            foreach (var id in seriesIDs)
            {
                dataQueryBuilder.AppendFormat(
                    ", (select DV{0}.DataValue from DataValues DV{0} where DV{0}.LocalDateTime = A.LocalDateTime and DV{0}.seriesId = {0} limit 1) as D{0}",
                    id);
            }
            dataQueryBuilder.AppendFormat(" from (select distinct LocalDateTime  from DataValues where {0}) A",
                                          whereClause);
            dataQueryBuilder.Append(" order by LocalDateTime");

            var dataQuery = dataQueryBuilder.ToString();

            var table = DbOperations.LoadTable(string.Format("{0} limit {1} offset {2}", dataQuery, valuesPerPage, currentPage * valuesPerPage));

            return(table);
        }
        /// <summary>
        /// Delete a theme - a background worker and progress bar is used
        /// </summary>
        /// <param name="themeID">The themeID (this needs to be a valid ID)</param>
        /// <param name="worker">The background worker component</param>
        /// <returns>True - on success, otherwise false.</returns>
        public bool DeleteTheme(long themeID, BackgroundWorker worker = null)
        {
            var sqlTheme        = "SELECT SeriesID FROM DataThemes where ThemeID = " + themeID;
            var sqlDeleteTheme  = "DELETE FROM DataThemeDescriptions WHERE ThemeID = " + themeID;
            var sqlDeleteTheme2 = "DELETE FROM DataThemes WHERE ThemeID = " + themeID;

            var tblSeries        = DbOperations.LoadTable("tblSeries", sqlTheme);
            var seriesRepository = RepositoryFactory.Instance.Get <IDataSeriesRepository>();

            for (var i = 0; i < tblSeries.Rows.Count; i++)
            {
                // Check cancellation
                if (worker != null && worker.CancellationPending)
                {
                    return(false);
                }

                var seriesRow = tblSeries.Rows[i];
                var seriesID  = Convert.ToInt32(seriesRow["SeriesID"]);
                seriesRepository.DeleteSeries(seriesID, themeID);

                // Progress report
                if (worker != null && worker.WorkerReportsProgress)
                {
                    var percent   = (int)(((i + 1) / (float)tblSeries.Rows.Count) * 100);
                    var userState = "Deleting series " + (i + 1) + " of " + tblSeries.Rows.Count + "...";
                    worker.ReportProgress(percent, userState);
                }
            }

            // Delete the actual theme
            DbOperations.ExecuteNonQuery(sqlDeleteTheme2);
            DbOperations.ExecuteNonQuery(sqlDeleteTheme);

            return(true);
        }
Example #27
0
        public DataTable AsDataTable()
        {
            var table = DbOperations.LoadTable(TableName, string.Format("Select * from {0}", TableName));

            return(table);
        }
Example #28
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
            {
                try
                {
                    File.Delete(originalDbPath);
                }
                catch (IOException ex)
                {
                    Trace.TraceError("Unable to delete temp file: {0}. Reason: {1}", originalDbPath, ex.Message);
                }
            }
        }
Example #29
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);
        }