public void SiteInfoDesignTest()
{
var fn = FileUtility.GetTempFileName(".pdb");
SQLiteServer svr = new SQLiteServer(fn);
var db = new TimeSeriesDatabase(svr,false);
var siteCatalog = db.GetSiteCatalog();
siteCatalog.AddsitecatalogRow("BOII", "Boise station", "ID");
svr.SaveTable(siteCatalog);
var sc = db.GetSeriesCatalog();
var s = new Series();
s.SiteID = "BOII";
sc.AddSeriesCatalogRow(s, sc.NextID(), db.GetRootObjects()[0].ID);
sc.Save();
var si = db.SiteInfo("BOII");
bool idaho = si.state == "ID"; //idaho
var timezone = si.timezone;
TimeSeriesDatabaseDataSet.SeriesCatalogDataTable d = si.SeriesList();
TimeSeriesDatabaseDataSet.SeriesCatalogRow row = d[0];
row.Parameter = "Asce ET #5";
d.Save();
Console.WriteLine(si.SeriesList()[0].Parameter);
//var goodStats = (si.Parameters()[0].statistic == "Avg");
}
public TimeSeriesDatabaseDelete(TimeSeriesDatabase db, PiscesObject o)
{
this.m_db = db;
sdi = o.ID;
catalog = m_db.GetSeriesCatalog();
m_seriesProperties = m_db.GetSeriesProperties();
//catalog.PrimaryKey = new DataColumn[] { catalog.Columns["id"]};
}
public IEnumerable <Series> GetSeries(TimeInterval interval, string filter = "", string propertyFilter = "")
{
string sql = " timeinterval = '" + interval.ToString() + "'";
if (filter != "")
{
sql += " AND " + filter;
}
var sc = db.GetSeriesCatalog(sql, propertyFilter);
foreach (var sr in sc)
{
var s = GetSeries(sr);
yield return(s);
}
}
public static void BasicDatabaseTest(TimeSeriesDatabase db)
{
Assert.IsTrue(db.GetSeriesCatalog().Rows.Count ==1 , " initial catalog should have root");
Reclamation.TimeSeries.Hydromet.HydrometInfoUtility.AutoUpdate = true;
DateTime t2 = DateTime.Now.Date.AddDays(-10);
DateTime t1 = DateTime.Now.Date.AddDays(-30);
Series s = new HydrometDailySeries("jck", "af");
int id = db.AddSeries(s);
s = db.GetSeries(id);
s.Read(t1, t2);
s.WriteToConsole();
Assert.AreEqual(21, s.Count);
s.Read(t1, DateTime.Now.Date.AddDays(-9)); //force auto update.(HydrometDaily supports this)
// check if auto update worked.
Assert.AreEqual(22, s.Count);
Assert.AreEqual(2, db.GetSeriesCatalog().Rows.Count, "Catalog row count");
var por = s.GetPeriodOfRecord();
Assert.AreEqual(22, por.Count,"period of record");
}
public void SortByName(PiscesFolder parent)
{
var sc = m_db.GetSeriesCatalog("parentid = " + parent.ID + " and isfolder = 1 ", "", " order by name");
int sortOrder = 10;
for (int i = 0; i < sc.Rows.Count; i++)
{
if (sc[i].siteid == "")
{
sc[i].siteid = sc[i].Name; // fix... siteid might be handy on a folder
}
sc[i].SortOrder = sortOrder;
sortOrder += 10;
}
m_db.Server.SaveTable(sc);
}
public void LoadTree()
{
m_db.SuspendTreeUpdates();
var colNames = xls.ColumnNames("Run0");
var sc = m_db.GetSeriesCatalog();
var root = m_db.AddFolder("URGWOM");
var sr = m_db.GetNewSeriesRow();
int id = sr.id;
foreach (var item in colNames)
{
Series s = new UrgwomSeries(excelFilename, "Run0", item);
s.ConnectionString = ConnectionStringUtility.MakeFileNameRelative(s.ConnectionString, m_db.DataSource);
s.Name = item;
s.Parameter = item;
sc.AddSeriesCatalogRow(s, id, root.ID, "");
id++;
}
m_db.Server.SaveTable(sc);
m_db.ResumeTreeUpdates();
}
public TimeSeriesDatabaseUtility(TimeSeriesDatabase db)
{
m_db = db;
m_seriesCatalog = m_db.GetSeriesCatalog();
m_siteCatalog = m_db.GetSiteCatalog();
}
/// <summary>
/// Insert the contents of another time series database
/// at the specified folder.
/// </summary>
/// <param name="folder"></param>
/// <param name="db"></param>
public void InsertDatabase(PiscesFolder folder, TimeSeriesDatabase db)
{
var sc = GetSeriesCatalog();
var new_sc = db.GetSeriesCatalog();
for (int i = 0; i < new_sc.Rows.Count; i++)
{
var row = new_sc[i];
var path = new_sc.GetPath(row.id);
var folderID = sc.GetOrCreateFolder(path);
if (row.IsFolder == 0)
{// add series
var s = db.Factory.GetSeries(row.id);
var sr = sc.AddSeriesCatalogRow(s, sc.NextID(), folderID, s.Table.TableName);
// get time series data
s.Read();
ImportTimeSeriesTable(s.Table,sr, DatabaseSaveOptions.Insert);
}
}
Server.SaveTable(sc);
// Add Sites.
var sites = GetSiteCatalog();
var new_sites = db.GetSiteCatalog();
for (int i = 0; i < new_sites.Count; i++)
{
if( !sites.Exists(new_sites[i].siteid))
{
var newRow =sites.NewsitecatalogRow();
newRow.ItemArray = new_sites[i].ItemArray;
sites.AddsitecatalogRow(newRow);
}
}
Server.SaveTable(sites);
// add properties. TO DO.
}
public static void WriteWithDataTable(string filename, Watershed watershed, bool compress = false, bool createPiscesDB = false)
{
var server = SqLiteEnsemble.GetServer(filename);
byte[] uncompressed = null;
Reclamation.TimeSeries.TimeSeriesDatabase db;
int locIdx = 1;
int WatershedFolderIndex = 1;
int scIndex = 0;
int rowCounter = 0;
Reclamation.TimeSeries.TimeSeriesDatabaseDataSet.SeriesCatalogDataTable sc = null;
if (createPiscesDB)
{
db = new Reclamation.TimeSeries.TimeSeriesDatabase(server);
// limit how much we query.
//var where = "id = (select max(id) from seriescatalog) or id = parentid";
var where = "id = (select max(id) from seriescatalog)";
sc = db.GetSeriesCatalog(where);
WatershedFolderIndex = sc.AddFolder(watershed.Name); // creates root level folder
scIndex = WatershedFolderIndex + 2;
}
else
{
}
var timeSeriesTable = GetBlobTable(server);
int index = server.NextID("timeseries_blob", "id");
foreach (Location loc in watershed.Locations)
{
if (createPiscesDB)
{
locIdx = sc.AddFolder(loc.Name, ++scIndex, WatershedFolderIndex);
}
foreach (Forecast f in loc.Forecasts)
{
var t = f.IssueDate;
var timeseries_start_date = f.TimeStamps[0];
index++;
var row = timeSeriesTable.NewRow();// create rows in separate loop first
row["id"] = index;
row["issue_date"] = f.IssueDate;
row["watershed"] = watershed.Name;
row["location_name"] = loc.Name;
row["timeseries_start_date"] = timeseries_start_date;
row["member_length"] = f.Ensemble.GetLength(1);
row["member_count"] = f.Ensemble.GetLength(0);
row["compressed"] = compress ? 1 : 0;
row["byte_value_array"] = ConvertToBytes(f.Ensemble, compress, ref uncompressed);
if (createPiscesDB)
{
string connectionString = "timeseries_blobs.id=" + index
+ ";member_length=" + f.Ensemble.GetLength(1)
+ ";ensemble_member_index={member_index}"
+ ";timeseries_start_date=" + timeseries_start_date.ToString("yyyy-MM-dd HH:mm:ss");
scIndex = AddPiscesSeries(loc.Name, scIndex, sc, f, locIdx, connectionString);
}
timeSeriesTable.Rows.Add(row);
rowCounter++;
if (rowCounter % 1000 == 0)
{
server.SaveTable(timeSeriesTable);
timeSeriesTable.Rows.Clear();
timeSeriesTable.AcceptChanges();
}
}
}
if (createPiscesDB)
{
server.SaveTable(sc);
}
server.SaveTable(timeSeriesTable);
}
static void SiteInventory(Arguments args, TimeSeriesDatabase db)
{
var site = args["siteinventory"].ToString().ToLower();
var siteCatalog = db.GetSiteCatalog("lower(description) LIKE '%" + site + "%'");
if (siteCatalog.Rows.Count < 1)
{
Console.WriteLine("Site " + site + " not found... ");
return;
}
else
{
foreach (var siteRow in siteCatalog)
{
var seriesCatalog = db.GetSeriesCatalog("isfolder = 0 AND siteid = '" + siteRow["siteid"] + "'");
Console.WriteLine("---------------------------------------------------------------------");
Console.WriteLine(siteRow["siteid"] + " | " + siteRow["description"] + " | " + siteRow["state"] +
" | " + siteRow["agency_region"]);
foreach (var row in seriesCatalog)
{ Console.WriteLine(" -- " + row["id"] + " | " + row["name"]); }
}
Console.WriteLine("---------------------------------------------------------------------");
}
}
