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);
}
}
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);
}
}
public DataTable AsDataTable()
{
var table = DbOperations.LoadTable(TableName, string.Format("Select * from {0}", TableName));
return(table);
}
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);
}
public long GetNextID()
{
return(DbOperations.GetNextID(TableName, PrimaryKeyName));
}
public bool Exists(string name)
{
var res = DbOperations.ExecuteSingleOutput("Select count(*) FROM Units where UnitsName=?", new object[] { name });
return(Convert.ToInt64(res) > 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);
}
}
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());
}
public void DeleteById(long valueID)
{
DbOperations.ExecuteNonQuery("DELETE FROM DataValues WHERE ValueID =" + valueID);
}