def test_from_function(self): """Can we select from a function instead of a relation? """ view = self.db.create_view('sssrrrsly', {'objid':generate_series(Date(2010,1,1),Date(2013,1,1))} ) result = view.select() self.assertEquals(len(result),1097) self.assertEquals(type(result[0][0]).__name__,'date')
def test_from_function(self): """Can we select from a function instead of a relation? """ result = cloud.select(generate_series(Date(2010,1,1),Date(2013,1,1))) self.assertEquals(len(result),1097) self.assertEquals(type(result[0][0]).__name__,'date')
def make_site_timeseries_views(parent,name,site_table,data_table, daily_stats = None, compareTo = None, begin_month = 10): try: root = parent.create_view(name+'_internal',{'objid':site_table}) except: parent.View(name+'_internal').drop() root = parent.create_view(name+'_internal',{'objid':site_table}) site_cols = dict((x['name'],x) for x in site_table.columns()) data_cols = dict((x['name'],x) for x in data_table.columns()) obd, value = data_cols['date'], data_cols['value'] siteid = site_cols['siteid'] d_siteid = data_cols['siteid'] today = datetime.date.today() this_year = today.year if today.month >= begin_month: begin = this_year, begin_month, 1 end = this_year + 1, begin_month, 1 else: begin = this_year - 1, begin_month, 1 end = this_year, begin_month, 1 if daily_stats is None: # Aggregate the min, avg and max for each day of the year daily_stats = root.create_view('DailyStats', { 'objid':site_table, 'cols':[ '_adm-rowid',siteid, extract(obd,'month'), extract(obd,'day'), MIN(value),AVG(value),MAX(value) ], 'join':Join(data_table), 'group_by':[ '_adm-rowid','siteid', extract(data_cols['date'],'month'), extract(data_cols['date'],'day') ] }, materialized = True) indexall(daily_stats) else: daily_stats = root.create_view('DailyStats', { 'objid':daily_stats }) annual_stats = root.create_view('AnnualStats', { 'objid':site_table, 'cols':[ '_adm-rowid',siteid, extract(obd,'water_year'), MIN(value),AVG(value),MAX(value) ], 'join':Join(data_table), 'group_by':[ '_adm-rowid','siteid', extract(obd,'water_year'), ], 'order_by':[extract(obd,'water_year')] }, materialized = True) indexall(annual_stats) alldata = root.create_view('AllData',{ 'objid':site_table, 'join':Join(data_table) }) cycols = alldata.columns() currentday = root.create_view('CurrentDay',{ 'objid':alldata, 'cols':['_adm-rowid']+cycols[:-4]+[MAX(cycols[-2]),cycols[-1]], 'group_by':['_adm-rowid']+cycols[:-4]+cycols[-1:] }) s_siteid, mo, day, mi, av, ma = daily_stats.columns() currentstatdata = root.create_view('CurrentStatsData',{ 'objid':alldata, 'join':Join(daily_stats,{'all':[ (d_siteid == s_siteid), (extract(obd,'month') == mo), (extract(obd,'day') == day), ]} ) }, materialized = True) #}) indexall(currentstatdata) currentsites = root.create_view('CurrentSites',{ 'objid':currentday, 'cols':[unique(siteid)] #},materialized=True) }) #indexall(currentsites) site_view = root.create_view('CurrentSiteInfo',{ 'objid':site_table, 'cols':['_adm-rowid']+site_table.columns(), 'join':(currentsites,('siteid','=','unique(siteid)')) },materialized=True) indexall(site_view) sitepor = root.create_view('SitePOR',{ 'objid':alldata, 'cols':['_adm-rowid',MIN(obd),MAX(obd),count(obd),site_cols['geom']], 'group_by':['_adm-rowid',site_cols['geom']] },materialized=True) indexall(sitepor) valueaverage = root.create_view('ValueAverageCurrent',{ 'objid':alldata, 'cols':['_adm-rowid']+[siteid,AVG(value)], 'group_by':['_adm-rowid']+[siteid], 'order_by':[siteid] #},materialized=True) }) valuetoday = root.create_view('ValueAverageToday',{ 'objid':site_table, 'cols':['_adm-rowid']+[siteid,value], 'join':Join(currentday), 'order_by':[siteid] #},materialized=True) }) #indexall(valueaverage) dontcare, current_avg = valuetoday.columns() valueavghist = root.create_view('StatsHistorical',{ 'objid':site_table, 'cols':['_adm-rowid']+[ siteid, MIN(mi), AVG(av), MAX(ma), ], 'join':Join(daily_stats), 'group_by':['_adm-rowid',siteid], 'order_by':[siteid] },materialized=True) indexall(valueavghist) dontcare, hist_min, hist_avg, hist_max = valueavghist.columns() curdvstats = root.create_view('CurrentDayVsStats',{ 'objid':valueavghist, 'cols':['_adm-rowid']+[current_avg,hist_min,hist_avg,hist_max], 'join':(valuetoday,('siteid','=','siteid')) # Too many same name cols. #},materialized=True) }) #indexall(compositedaily) try: parent.View(name).drop() except: pass layer = parent.create_view(name,{ 'objid':curdvstats, 'cols':['_adm-rowid'] + site_view.columns()[:-1] + [ current_avg / hist_avg, 'geom' ], 'join':Join(site_view,('_adm-rowid','=','_adm-rowid')) },materialized=True) #}) layer.columns()[-2].modify({'name':'magnitude'}) indexall(layer) cydates = root.create_view('CYDates',{ 'objid':generate_series(Date(*begin),Date(*end)) }) snum, mo, da, miv, avv, mav = daily_stats.columns() datatab = layer.create_view('CurrentYear',{ 'objid':site_view, 'cols':['_adm-rowid']+[obd,value,miv,avv,mav], 'where':[obd >= Date(*begin)], 'join':[ LeftOuterJoin(cydates), Join(currentstatdata,[ ('_adm-rowid','=','_adm-rowid'), ('date','=','date') ]) ], 'order_by':[siteid,obd] #},materialized=True) }) datatab = layer.create_view('LastYear',{ 'objid':site_view, 'cols':['_adm-rowid']+[obd,value,miv,avv,mav], 'where':[obd >= Date(*[begin[0]-1]+list(begin[1:])), obd < Date(*begin)], 'join':Join(currentstatdata,('_adm-rowid','=','_adm-rowid')), 'order_by':[siteid,obd] #},materialized=True) }) #indexall(datatab) layer.create_view('POR',{'objid':sitepor}) return layer
def test_from_function(self): """Can we select from a function instead of a relation? """ result = cloud.select( generate_series(Date(2010, 1, 1), Date(2013, 1, 1))) self.assertEquals(len(result), 1097) self.assertEquals(type(result[0][0]).__name__, 'date')