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("---------------------------------------------------------------------"); } }