def test_columns_task_creates_columns_only_when_run():
task = TestColumnsTask()
with session_scope() as session:
assert_equals(session.query(OBSColumn).count(), 0)
runtask(task)
with session_scope() as session:
assert_equals(session.query(OBSColumn).count(), 2)
assert_equals(session.query(OBSColumnToColumn).count(), 1)
assert_equals(task.output()['pop'].get(session).id,
'test_util.population')
assert_equals(task.output()['foobar'].get(session).id,
'test_util.foobar')
pop = session.query(OBSColumn).get('test_util.population')
foobar = session.query(OBSColumn).get('test_util.foobar')
assert_equals(len(pop.sources), 1)
assert_equals(len(foobar.targets), 1)
assert_equals(list(pop.sources.keys())[0].id, foobar.id)
assert_equals(list(foobar.targets.keys())[0].id, pop.id)
assert_equals(True, task.complete())
table = OBSTable(id='table', tablename='tablename')
with session_scope() as session:
coltable = OBSColumnTable(column=task.output()['pop'].get(session),
table=table,
colname='colnamaste')
session.add(table)
session.add(coltable)
with session_scope() as session:
assert_equals(session.query(OBSColumnTable).count(), 1)
def test_table_target_many_inits():
pop_col = ColumnTarget(
"tests", "population",
OBSColumn(type='Numeric',
name="Total Population",
description='The total number of all',
aggregate='sum',
weight=10), FakeTask())
foo_col = ColumnTarget(
"tests", "foo",
OBSColumn(type='Numeric',
name="Foo Bar",
description='moo boo foo',
aggregate='median',
weight=8), FakeTask())
pop_col.update_or_create()
foo_col.update_or_create()
assert_equals(current_session().query(OBSColumn).count(), 2)
columns = {'population': pop_col, 'foobar': foo_col}
table_target = TableTarget('test', 'foobar', OBSTable(), columns,
FakeTask())
assert_equals(False, table_target.exists())
assert_equals(current_session().query(OBSTable).count(), 0)
assert_equals(current_session().query(OBSColumn).count(), 2)
table_target.update_or_create_table()
table_target.update_or_create_metadata()
assert_equals(current_session().query(OBSColumn).count(), 2)
assert_equals(current_session().query(OBSTable).count(), 1)
obs_table = table_target.get(current_session())
tablename = 'observatory.' + obs_table.tablename
assert_in(tablename, metadata.tables)
sqlalchemy_table = metadata.tables[tablename]
assert_equals(len(sqlalchemy_table.columns), 2)
assert_equals(table_target.exists(), False)
# should 'exist' once rows inserted
current_session().execute(
'INSERT INTO {tablename} VALUES (0, 0)'.format(tablename=tablename))
current_session().commit()
assert_equals(table_target.exists(), True)
# new session, old object
assert_equals(True, table_target.exists())
assert_equals(current_session().query(OBSTable).count(), 1)
current_session().rollback()
table_target.update_or_create_table()
table_target.update_or_create_metadata()
assert_equals(current_session().query(OBSTable).count(), 1)
assert_in(tablename, metadata.tables)
sqlalchemy_table = metadata.tables[tablename]
assert_equals(len(sqlalchemy_table.columns), 2)
# forcing update_or_create_table again will end up wiping the table
assert_equals(False, table_target.exists())
def test_column_target_relations_create_update():
col = ColumnTarget(
OBSColumn(
id='tests.foobar',
type='Numeric',
name="Total Population",
description=
'The total number of all people living in a given geographic area. This is a very useful catch-all denominator when calculating rates.',
aggregate='sum',
weight=10), FakeTask())
with session_scope() as session:
assert_equals(session.query(OBSColumn).count(), 0)
col.update_or_create()
rawcol = col._column
tag = OBSTag(id='tag',
name='some tag',
description='some tag',
type='some type')
session.add(tag)
rawcol.tags.append(TagTarget(tag, FakeTask()))
session.add(rawcol)
table = OBSTable(id='table', tablename='foobar')
session.add(table)
coltable = OBSColumnTable(column=rawcol, table=table, colname='col')
session.add(coltable)
with session_scope() as session:
assert_equals(session.query(OBSTag).count(), 1)
assert_equals(session.query(OBSColumnTag).count(), 1)
assert_equals(session.query(OBSColumn).count(), 1)
assert_equals(session.query(OBSColumnTable).count(), 1)
assert_equals(session.query(OBSTable).count(), 1)
col._column.name = 'foo bar baz'
with session_scope() as session:
col.update_or_create()
with session_scope() as session:
rawcol = session.query(OBSColumn).get('tests.foobar')
assert_equals(rawcol.name, 'foo bar baz')
assert_equals(session.query(OBSTag).count(), 1)
assert_equals(session.query(OBSColumnTag).count(), 1)
assert_equals(session.query(OBSColumn).count(), 1)
assert_equals(session.query(OBSColumnTable).count(), 1)
assert_equals(session.query(OBSTable).count(), 1)
def targets(self):
return {
OBSTable(id='.'.join([self.schema(), self.name()])): GEOM_REF,
}
def populate():
with session_scope() as session:
population_tag = OBSTag(id='population',
name='population',
type='catalog')
source_tag = OBSTag(id='us_census', name='US Census', type='source')
session.add(population_tag)
session.add(source_tag)
datacols = {
'median_rent':
OBSColumn(id='"us.census.acs".median_rent', type='numeric'),
'total_pop':
OBSColumn(id='"us.census.acs".total_pop', type='numeric'),
'male_pop':
OBSColumn(id='"us.census.acs".male_pop', type='numeric'),
'female_pop':
OBSColumn(id='"us.census.acs".female_pop', type='numeric'),
}
for numerator_col in (
'male_pop',
'female_pop',
):
datacol = datacols[numerator_col]
datacol.targets[datacols['total_pop']] = 'denominator'
session.add(datacol)
tract_geoid = OBSColumn(id='"us.census.acs".tract_2013_geoid',
type='text')
puma_geoid = OBSColumn(id='"us.census.acs".puma_2013_geoid',
type='text')
tract_geom = OBSColumn(id='"us.census.tiger".tract', type='geometry')
tables = {
'tract':
OBSTable(id='"us.census.acs".extract_2013_5yr_tract',
tablename='us_census_acs2013_5yr_tract'),
'puma':
OBSTable(id='"us.census.acs".extract_2013_5yr_puma',
tablename='us_census_acs2013_5yr_puma')
}
geom_table = OBSTable(id='"us.census.acs".tract_geoms',
tablename='tract_geoms')
session.add(
OBSColumnTable(table=tables['tract'],
column=tract_geoid,
colname='geoid'))
session.add(
OBSColumnTable(table=tables['puma'],
column=puma_geoid,
colname='geoid'))
session.add(
OBSColumnTable(table=geom_table,
column=tract_geoid,
colname='geoid'))
geom_coltable = OBSColumnTable(table=geom_table,
column=tract_geom,
colname='the_geom')
session.add(geom_coltable)
session.add(
OBSColumnTableTile(column_id=geom_coltable.column.id,
table_id=geom_coltable.table.id,
tile_id=1,
tile=EMPTY_RASTER))
for colname, datacol in datacols.items():
if colname.endswith('pop'):
datacol.tags.append(TagTarget(population_tag, FakeTask()))
datacol.tags.append(TagTarget(source_tag, FakeTask()))
for table in list(tables.values()):
coltable = OBSColumnTable(column=datacol,
table=table,
colname=colname)
session.add(coltable)
session.add(datacol)
for table in list(tables.values()):
session.add(table)
