def GET(self, series_id, group_id, subgroup_name_id, date, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT mv.id,
country.id AS country_id, country.iso2code, country.iso3code, country.name AS country_name,
mv.value, to_char(mv.date, mv.date_precision) AS date
FROM gestalt_frontend_country_data AS mv
INNER JOIN gestalt_country_with_name AS country
ON mv.country_id = country.id
INNER JOIN gestalt_series AS series
ON mv.series_id = series.id
INNER JOIN gestalt_subgroup AS subgroup
ON mv.country_id = subgroup.country_id
WHERE mv.series_id = """ + series_id + """
AND subgroup.group_id = """ + group_id + """
AND subgroup.name_id = """ + subgroup_name_id + """
AND to_char(mv.date, mv.date_precision) = '""" + date + """'
ORDER BY country.name;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self, series_id, low_val, high_val, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT mv.id,
country.id AS country_id, country.iso2code, country.iso3code, country.name AS country_name,
mv.value, to_char(mv.date, mv.date_precision) AS date
FROM gestalt_frontend_country_data AS mv
INNER JOIN gestalt_country_with_name AS country
ON mv.country_id = country.id
INNER JOIN gestalt_series AS series
ON mv.series_id = series.id
WHERE mv.series_id = """ + series_id + """
AND mv.date = (SELECT max(date) FROM gestalt_frontend_country_data WHERE series_id = """ + series_id + """)
AND (CASE WHEN """ + low_val + """ IS NULL THEN 1 ELSE """ + low_val + """ END) < mv.value
AND mv.value <= """ + high_val + """
ORDER BY country.name;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self,
series_id,
low_val,
high_val,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT mv.id,
country.id AS country_id, country.iso2code, country.iso3code, country.name AS country_name,
mv.value, to_char(mv.date, mv.date_precision) AS date
FROM gestalt_frontend_country_data AS mv
INNER JOIN gestalt_country_with_name AS country
ON mv.country_id = country.id
INNER JOIN gestalt_series AS series
ON mv.series_id = series.id
WHERE mv.series_id = """ + series_id + """
AND mv.date = (SELECT max(date) FROM gestalt_frontend_country_data WHERE series_id = """
+ series_id + """)
AND (CASE WHEN """ + low_val + """ IS NULL THEN 1 ELSE """ +
low_val + """ END) < mv.value
AND mv.value <= """ + high_val + """
ORDER BY country.name;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self,
workspace_url_name,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT DISTINCT ON (w.id) w.id, wn.name, p.name AS persona_name, w.url_name
FROM """ + helper.table_prefix + """workspace AS w
LEFT JOIN """ + helper.table_prefix + """workspace_name AS wn
ON w.workspace_name_id = wn.id
LEFT JOIN """ + helper.table_prefix + """persona AS p
ON w.persona_id = p.id
WHERE w.id IS NOT NULL
AND w.url_name = '""" + workspace_url_name + """'
ORDER BY w.id;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, workspace_url_name, persona_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select distinct on (p.name, vd.name)
pps.panel_id,
p.url_name,
w.url_name as workspace_url_name,
p.name,
pps.persona_id,
vd.name as default_vis
from """ + helper.table_prefix + """persona_panel_story pps
left join """ + helper.table_prefix + """workspace_panel wp on wp.panel_id = pps.panel_id
left join """ + helper.table_prefix + """story s on s.id = pps.story_id
left join """ + helper.table_prefix + """vis v on v.id = s.vis_id
left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id
left join """ + helper.table_prefix + """panel p on p.id = wp.panel_id
left join """ + helper.table_prefix + """workspace w on w.id = wp.workspace_id
where w.url_name = '""" + workspace_url_name + """'
and pps.persona_id = """ + persona_id + """
and vd.name is not null
order by p.name asc,
vd.name;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self,
series_id,
group_id,
subgroup_name_id,
date,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT mv.id,
country.id AS country_id, country.iso2code, country.iso3code, country.name AS country_name,
mv.value, to_char(mv.date, mv.date_precision) AS date
FROM gestalt_frontend_country_data AS mv
INNER JOIN gestalt_country_with_name AS country
ON mv.country_id = country.id
INNER JOIN gestalt_series AS series
ON mv.series_id = series.id
INNER JOIN gestalt_subgroup AS subgroup
ON mv.country_id = subgroup.country_id
WHERE mv.series_id = """ + series_id + """
AND subgroup.group_id = """ + group_id + """
AND subgroup.name_id = """ + subgroup_name_id + """
AND to_char(mv.date, mv.date_precision) = '""" + date + """'
ORDER BY country.name;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self, source_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select gn.name as source_name,
cy.iso2code as source,
gnt.name as target_name,
cyt.iso2code as target,
cy.id as source_id,
count(cdis.target_id) as value
from """ + helper.table_prefix + """cdis cdis
left join """ + helper.table_prefix + """geography_name gn on gn.id = cdis.source_id
left join """ + helper.table_prefix + """geography_name gnt on gnt.id = cdis.target_id
left join """ + helper.table_prefix + """country cy on cy.id = cdis.source_id
left join """ + helper.table_prefix + """country cyt on cyt.id = cdis.target_id
where source_id = """ + source_id + """ and cyt.iso2code is not null
group by gn.name,
cy.iso2code,
cy.id,
gnt.name,
cyt.iso2code
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select distinct on (w.persona_id)
w.id as workspace_id,
w.url_name as workspace_url_name,
p.id,
p.name,
p.description,
pt.name as persona_type,
pl.url_name as default_panel,
vd.name as default_vis
from """ + helper.table_prefix + """workspace w
left join """ + helper.table_prefix + """persona p on p.id = w.persona_id
left join """ + helper.table_prefix + """persona_type pt on pt.id = p.persona_type
left join """ + helper.table_prefix + """workspace_panel wp on wp.workspace_id = w.id
left join """ + helper.table_prefix + """panel pl on pl.id = wp.panel_id
left join """ + helper.table_prefix + """persona_panel_story pps on pps.panel_id = wp.panel_id
left join """ + helper.table_prefix + """story s on s.id = pps.story_id
left join """ + helper.table_prefix + """vis v on v.id = s.vis_id
left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id
where w.is_default = true
order by w.persona_id;
""")
# obtain the data
data = self.cursor.fetchall()
# close cursor and connection
connection.close()
self.cursor.close()
# convert data to a string
return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select distinct on (w.persona_id)
w.id as workspace_id,
w.url_name as workspace_url_name,
p.id,
p.name,
p.description,
pt.name as persona_type,
pl.url_name as default_panel,
vd.name as default_vis
from gestalt_workspace w
left join gestalt_persona p on p.id = w.persona_id
left join gestalt_persona_type pt on pt.id = p.persona_type
left join gestalt_workspace_panel wp on wp.workspace_id = w.id
left join gestalt_panel pl on pl.id = wp.panel_id
left join gestalt_persona_panel_story pps on pps.panel_id = wp.panel_id
left join gestalt_story s on s.id = pps.story_id
left join gestalt_vis v on v.id = s.vis_id
left join gestalt_vis_directive vd on vd.id = v.vis_directive_id
where w.is_default = true
order by w.persona_id;
""")
# obtain the data
data = self.cursor.fetchall()
# close cursor and connection
connection.close()
self.cursor.close()
# convert data to a string
return json.dumps(data)
def GET(self,
persona_id,
panel_id,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select pps.*,
s.name,
s.url_name,
s.intro,
array_agg(row_to_json(si)) as ideas
from """ + helper.table_prefix + """persona_panel_story pps
left join """ + helper.table_prefix + """story s on s.id = pps.story_id
left join (
select sti.*,
vt.url_name as vis_type_name,
array_agg(row_to_json(c)) as controls
from """ + helper.table_prefix + """story_idea sti
left join """ + helper.table_prefix +
"""vis_type vt on vt.id = sti.vis_type_id
left join (
select sac.*,
case
when sac.story_action_id = 1 then g.name
when sac.story_action_id = 2 then g.name
when sac.story_action_id = 3 then g.name
when sac.story_action_id = 4 then h.name
when sac.story_action_id = 5 then g.name
when sac.story_action_id = 6 then g.name
when sac.story_action_id = 7 then g.name
end
as name
from """ + helper.table_prefix + """story_action_control sac
left join """ + helper.table_prefix + """group g on g.id = sac.name_id
left join """ + helper.table_prefix + """heuristic h on h.id = sac.name_id
) c on c.story_action_id = sti.action_id
group by sti.id,
vt.url_name
) si on si.story_id = pps.story_id
where pps.persona_id = """ + persona_id + """ and pps.panel_id = """ +
panel_id + """
group by pps.id,
s.name,
s.url_name,
s.intro,
s.story_order
order by s.story_order;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self,
story_idea_id,
control_id,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute(
"""
select sac.id,
sim.label,
sim.description,
si.id as story_idea_id,
case
when sac.story_action_id = 1 then g.name
when sac.story_action_id = 2 then g.name
when sac.story_action_id = 5 then g.name
when sac.story_action_id = 6 then g.name
when sac.story_action_id = 7 then g.name
else f.name
end
as control_name,
sa.name as action_name,
sim.name as metric_name,
array_agg(row_to_json(m)) as metrics
from """ + helper.table_prefix + """story_action_control sac
left join """ + helper.table_prefix +
"""story_action sa on sa.id = sac.story_action_id
left join """ + helper.table_prefix +
"""story_idea si on si.action_id = sac.story_action_id
left join """ + helper.table_prefix + """group g on g.id = sac.name_id
left join """ + helper.table_prefix + """vertex v on v.id = sac.name_id
left join """ + helper.table_prefix + """flow f on f.id = sac.name_id
left join """ + helper.table_prefix +
"""story_idea_metric sim on sim.control_id = sac.id
left join (
select * from """ + helper.table_prefix + """story_idea_metric_value
) m on m.control_id = sac.id
where si.id = """ + story_idea_id + """ and sac.id = """ + control_id + """
group by sac.id,
sim.label,
sim.description,
si.id,
g.name,
v.name,
f.name,
sa.name,
sim.name;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select g.*,
array_agg(row_to_json(s)) as subgroups
from """ + helper.table_prefix + """group g
left join (
select distinct on (sg.name_id, sg.group_id) sg.name_id,
sg.group_id,
case
when gt.id = 1 then gn.name
else sgn.name
end as name,
case when gt.id = 1 then gt.id || '_' || gn.id
else gt.id || '_' || sgn.id
end as id,
geo.hexagon_center_x as center_x,
geo.hexagon_center_y as center_y,
array_agg(row_to_json(n)) as nodes
from """ + helper.table_prefix + """subgroup sg
left join """ + helper.table_prefix + """geography_name gn on gn.id = sg.name_id
left join """ + helper.table_prefix + """subgroup_name sgn on sgn.id = sg.name_id
left join """ + helper.table_prefix + """group g on g.id = sg.group_id
left join """ + helper.table_prefix + """group_type gt on gt.id = g.type_id
left join """ + helper.table_prefix + """geography geo on geo.name_id = sg.name_id and gt.id = 1
left join (
select gn.name,
gcy.id,
gcy.iso2code as iso
from """ + helper.table_prefix + """country gcy
left join """ + helper.table_prefix + """geography_name gn on gn.id = gcy.name_id
) n on n.id = sg.country_id
group by sg.name_id,
sg.group_id,
gn.name,
sgn.name,
geo.hexagon_center_x,
geo.hexagon_center_y,
gt.id,
g.id,
gn.id,
sgn.id
) s on s.group_id = g.id group by g.id,g.name, g.type_id;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT * FROM gestalt_panel;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, persona_id, panel_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select pps.*,
s.name,
s.url_name,
s.intro,
array_agg(row_to_json(si)) as ideas
from """ + helper.table_prefix + """persona_panel_story pps
left join """ + helper.table_prefix + """story s on s.id = pps.story_id
left join (
select sti.*,
vt.url_name as vis_type_name,
array_agg(row_to_json(c)) as controls
from """ + helper.table_prefix + """story_idea sti
left join """ + helper.table_prefix + """vis_type vt on vt.id = sti.vis_type_id
left join (
select sac.*,
case
when sac.story_action_id = 1 then g.name
when sac.story_action_id = 2 then g.name
when sac.story_action_id = 3 then g.name
when sac.story_action_id = 4 then h.name
when sac.story_action_id = 5 then g.name
when sac.story_action_id = 6 then g.name
when sac.story_action_id = 7 then g.name
end
as name
from """ + helper.table_prefix + """story_action_control sac
left join """ + helper.table_prefix + """group g on g.id = sac.name_id
left join """ + helper.table_prefix + """heuristic h on h.id = sac.name_id
) c on c.story_action_id = sti.action_id
group by sti.id,
vt.url_name
) si on si.story_id = pps.story_id
where pps.persona_id = """ + persona_id + """ and pps.panel_id = """ + panel_id + """
group by pps.id,
s.name,
s.url_name,
s.intro,
s.story_order
order by s.story_order;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, vis_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select v.*
from """ + helper.table_prefix + """vis v
where v.id = """ + vis_id + """;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self,
persona_id,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute(
"""
select distinct on (p.url_name) w.id,
w.persona_id,
w.url_name,
w.is_default,
wn.name,
p.url_name as default_panel,
vd.name as default_vis
from """ + helper.table_prefix + """workspace w
left join """ + helper.table_prefix +
"""workspace_name wn on wn.id = w.workspace_name_id
left join """ + helper.table_prefix +
"""workspace_panel wp on wp.workspace_id = w.id
left join """ + helper.table_prefix + """panel p on p.id = wp.panel_id
left join """ + helper.table_prefix +
"""persona_panel_story pps on pps.panel_id = wp.panel_id and pps.persona_id = """
+ persona_id + """
left join """ + helper.table_prefix + """story s on s.id = pps.story_id
left join """ + helper.table_prefix + """vis v on v.id = s.vis_id
left join """ + helper.table_prefix +
"""vis_directive vd on vd.id = v.vis_directive_id
where w.persona_id = """ + persona_id + """ and wp.is_default = true
group by w.id,
w.persona_id,
w.url_name,
w.url_name,
wn.name,
p.url_name,
vd.name
order by default_panel,
wn.name asc;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, story_idea_id, control_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select sac.id,
sim.label,
sim.description,
si.id as story_idea_id,
case
when sac.story_action_id = 1 then g.name
when sac.story_action_id = 2 then g.name
when sac.story_action_id = 5 then g.name
when sac.story_action_id = 6 then g.name
when sac.story_action_id = 7 then g.name
else f.name
end
as control_name,
sa.name as action_name,
sim.name as metric_name,
array_agg(row_to_json(m)) as metrics
from """ + helper.table_prefix + """story_action_control sac
left join """ + helper.table_prefix + """story_action sa on sa.id = sac.story_action_id
left join """ + helper.table_prefix + """story_idea si on si.action_id = sac.story_action_id
left join """ + helper.table_prefix + """group g on g.id = sac.name_id
left join """ + helper.table_prefix + """vertex v on v.id = sac.name_id
left join """ + helper.table_prefix + """flow f on f.id = sac.name_id
left join """ + helper.table_prefix + """story_idea_metric sim on sim.control_id = sac.id
left join (
select * from """ + helper.table_prefix + """story_idea_metric_value
) m on m.control_id = sac.id
where si.id = """ + story_idea_id + """ and sac.id = """ + control_id + """
group by sac.id,
sim.label,
sim.description,
si.id,
g.name,
v.name,
f.name,
sa.name,
sim.name;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, region_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
cursor = connection.cursor()
# execute query
cursor.execute("""
TRUNCATE gestalt_frontend_country_data;
""")
# make changes to the database persistent
connection.commit()
# close
cursor.close()
connection.close()
return
def GET(self,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT * FROM gestalt_panel;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'gestalt_source%'
ORDER BY table_name;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, table_name, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT DISTINCT ON (src.series_id) src.series_id, series.code, series.description
FROM """ + table_name + """ AS src
INNER JOIN gestalt_series AS series
ON src.series_id = series.id
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, group_id, subgroup_name_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT country.id, country.name
FROM gestalt_subgroup AS subgroup
INNER JOIN gestalt_country_with_name AS country
ON subgroup.country_id = country.id
WHERE subgroup.group_id = """ + group_id + """
AND subgroup.name_id = """ + subgroup_name_id + """
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, table_name, series_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
cursor = connection.cursor()
# execute query
cursor.execute("""
INSERT INTO gestalt_frontend_country_data (source_name, source_id, series_id, country_id, date, date_precision, value)
SELECT '""" + table_name + """' AS source_name, src.id, src.series_id, src.country_id, src.date, src.date_precision, src.value
FROM """ + table_name + """ AS src
WHERE src.series_id = """ + series_id + """;
""")
# make changes to the database persistent
connection.commit()
# close
cursor.close()
connection.close()
return
def GET(self,
region_id,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
cursor = connection.cursor()
# execute query
cursor.execute("""
TRUNCATE gestalt_frontend_country_data;
""")
# make changes to the database persistent
connection.commit()
# close
cursor.close()
connection.close()
return
def GET(self,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'gestalt_source%'
ORDER BY table_name;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, persona_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT DISTINCT ON (pl.id) pl.id, pl.name, pl.url_name
FROM gestalt_panel AS pl
RIGHT JOIN gestalt_persona_panel_story AS pps
ON pl.id = pps.panel_id
AND pps.persona_id = """ + persona_id + """
WHERE pl.id IS NOT NULL
ORDER BY pl.id;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self,
vis_id,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select v.*
from """ + helper.table_prefix + """vis v
where v.id = """ + vis_id + """;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self, story_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT DISTINCT ON (tag.id) tag.id, tag.name
FROM gestalt_tag AS tag
RIGHT JOIN gestalt_story_tag AS story_tag
ON tag.id = story_tag.tag_id
AND story_tag.story_id = """ + story_id + """
WHERE tag.id IS NOT NULL
ORDER BY tag.id;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self,
table_name,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT DISTINCT ON (src.series_id) src.series_id, series.code, series.description
FROM """ + table_name + """ AS src
INNER JOIN gestalt_series AS series
ON src.series_id = series.id
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, workspace_url_name, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT DISTINCT ON (w.id) w.id, wn.name, p.name AS persona_name, w.url_name
FROM """ + helper.table_prefix + """workspace AS w
LEFT JOIN """ + helper.table_prefix + """workspace_name AS wn
ON w.workspace_name_id = wn.id
LEFT JOIN """ + helper.table_prefix + """persona AS p
ON w.persona_id = p.id
WHERE w.id IS NOT NULL
AND w.url_name = '""" + workspace_url_name + """'
ORDER BY w.id;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self,
group_id,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT DISTINCT ON (subgroup.name_id) subgroup.id, subgroup.name_id, subgroup_name.name
FROM gestalt_subgroup AS subgroup
INNER JOIN gestalt_subgroup_name AS subgroup_name
ON subgroup.name_id = subgroup_name.id
WHERE subgroup.group_id = """ + group_id + """
ORDER BY subgroup.name_id;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self,
persona_id,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
SELECT DISTINCT ON (pl.id) pl.id, pl.name, pl.url_name
FROM gestalt_panel AS pl
RIGHT JOIN gestalt_persona_panel_story AS pps
ON pl.id = pps.panel_id
AND pps.persona_id = """ + persona_id + """
WHERE pl.id IS NOT NULL
ORDER BY pl.id;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data)
def GET(self, table, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
select distinct on (gn.name) gn.name,
cy.iso2code as iso,
cy.id,
count(distinct cdis.target_id) as count
from """ + helper.table_prefix + """country cy
left join """ + helper.table_prefix + """geography_name gn on gn.id = cy.name_id
left join """ + helper.table_prefix + """geography geo on geo.name_id = cy.name_id
left join """ + helper.table_prefix + """cdis cdis on source_id = cy.id
group by gn.name,
cy.iso2code,
cy.id
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self,
table_name,
series_id,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
cursor = connection.cursor()
# execute query
cursor.execute(
"""
INSERT INTO gestalt_frontend_country_data (source_name, source_id, series_id, country_id, date, date_precision, value)
SELECT '""" + table_name +
"""' AS source_name, src.id, src.series_id, src.country_id, src.date, src.date_precision, src.value
FROM """ + table_name + """ AS src
WHERE src.series_id = """ + series_id + """;
""")
# make changes to the database persistent
connection.commit()
# close
cursor.close()
connection.close()
return
def GET(self, data_type, polygon_type, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# check param
if (data_type == "xy"):
# execute query to return svg simple x/y coordinate system
self.cursor.execute("""
select geo.id,
gn.name,
cty.iso2code as iso,
geo.hexagon_center_x as center_x,
geo.hexagon_center_y as center_y
from """ + helper.table_prefix + """geography geo
left join """ + helper.table_prefix + """geography_name gn on gn.id = geo.name_id
left join """ + helper.table_prefix + """country cty on cty.id = geo.name_id
where geo.hexagon_center_x is not null and geo.hexagon_center_y is not null;
""")
else:
# execute query to return mercator projection in geojson coordinate system
self.cursor.execute("""
select 'FeatureCollection' as type,
array_agg(row_to_json(r)) as features
from (
with t as (
select 'Feature'::text
)
select t.text as type,
row_to_json(f) as properties,
row_to_json(c) as geometry
from t,
""" + helper.table_prefix + """geography geo
left join (
select geo.id,
gn.name,
cy.iso2code as iso
from """ + helper.table_prefix + """geography geo
left join """ + helper.table_prefix + """geography_name gn on gn.id = geo.name_id
left join """ + helper.table_prefix + """country cy on cy.id = geo.name_id
) f on f.id = geo.id
left join (
with t as (
select 'Polygon'::text
)
select t.text as type,
geo.""" + polygon_type + """_polygon as coordinates
from t,
""" + helper.table_prefix + """geography geo
) c on c.coordinates = geo.""" + polygon_type + """_polygon
where geo.""" + polygon_type + """_polygon is not null
) r
group by type;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
with data_array as (
select dd.vis_id,
array_agg(row_to_json((select r from (select ddl.name, dd.value) r))) as data
from """ + helper.table_prefix + """vis_dummy_data dd
left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id
group by dd.vis_id
),
-- attributes
attrs_array as (
select vca.vis_id,
array_agg(row_to_json((select r from (select vca.*, va.name as attr_name, va.value as default_value) r ))) as attrs
from """ + helper.table_prefix + """vis_code_attr vca
left join """ + helper.table_prefix + """vis_attr va on va.id = vca.attr_id
group by vca.vis_id
),
-- do guidance
dos_array as (
select doa.vis_id,
array_agg(row_to_json((
select r
from (select doa.*) r
))) as dos
from """ + helper.table_prefix + """vis_do_attr doa
group by doa.vis_id
),
-- dont guidance
donts_array as (
select donta.vis_id,
array_agg(row_to_json((
select r
from (select donta.*) r
))) as donts
from """ + helper.table_prefix + """vis_dont_attr donta
group by donta.vis_id
),
-- alternatives
alts_array as (
select val.vis_id,
array_agg(row_to_json((select r from (select val.*, vd.name as alt_name, vt.url_name as alt_type) r ))) as alts
from """ + helper.table_prefix + """vis_alt_attr val
left join """ + helper.table_prefix + """vis_directive vd on vd.id = val.alt_vis_directive_id
left join """ + helper.table_prefix + """vis v on v.id = val.alt_vis_directive_id
left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id
group by val.vis_id
)
select v.*,
vt.name as vis_type_name,
vt.url_name as vis_type_urlname,
vd.name as directive,
dd.data,
vca.attrs,
doa.dos,
donta.donts,
val.alts
from """ + helper.table_prefix + """vis v
left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id
left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id
left join data_array dd on dd.vis_id = v.id
left join attrs_array vca on vca.vis_id = v.id
left join dos_array doa on doa.vis_id = v.id
left join donts_array donta on donta.vis_id = v.id
left join alts_array val on val.vis_id = v.id
where vt.url_name = 'hierarchy';
""")
# obtain the data
data = self.cursor.fetchall()
# temp open from file for now
with open("data/hierarchy.json") as json_data:
# dict
fc = json.load(json_data)
# loop through meta info
for obj in data:
# add dummy data until db is updated
obj["data"] = fc
# convert data to a string
return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
-- dummy data
with data_array as (
-- nodes
with node_array as (
select dd.vis_id,
array_agg(row_to_json((select r from (select ddl.name) r))) as nodes
from """ + helper.table_prefix + """vis_dummy_data dd
left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id
group by dd.vis_id
),
-- links
link_array as (
select ddv.data_id,
array_agg(row_to_json((select r from (select ddl.name) r))) as links
from """ + helper.table_prefix + """vis_dummy_data_values ddv
left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = ddv.value
group by ddv.data_id
)
select dd.vis_id,
array_agg(row_to_json((select r from (select ddl.name, na.nodes, la.links) r))) as data
from """ + helper.table_prefix + """vis_dummy_data dd
left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id
left join node_array na on na.vis_id = dd.id
left join link_array la on la.data_id = dd.id
group by dd.vis_id
)
select v.*,
vt.name as vis_type_name,
vt.url_name as vis_type_urlname,
vd.name as directive--,
--dd.data
from """ + helper.table_prefix + """vis v
left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id
left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id
--left join data_array dd on dd.vis_id = v.id
where vt.url_name = 'relatedness';
""")
# obtain the data
data = self.cursor.fetchall()
# temp open from file for now
with open("data/related.json") as json_data:
# dict
fc = json.load(json_data)
# loop through meta info
for obj in data:
# add dummy data until db is updated
obj["data"] = fc
# convert data to a string
return json.dumps(data)
def GET(self,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute(
"""
-- dummy data
with data_array as (
-- values
with value_array as (
select ddv.data_id,
array_agg(row_to_json((select r from (select ddv.value, ddv.date as timestamp) r))) as values
from """ + helper.table_prefix + """vis_dummy_data_values ddv
group by ddv.data_id
)
select dd.vis_id,
array_agg(row_to_json((select r from (select ddl.name, va.values
) r))) as data
from """ + helper.table_prefix + """vis_dummy_data dd
left join """ + helper.table_prefix +
"""vis_dummy_data_label ddl on ddl.id = dd.name_id
left join value_array va on va.data_id = dd.id
group by dd.vis_id
),
-- attributes
attrs_array as (
select vca.vis_id,
array_agg(row_to_json((select r from (select vca.*, va.name as attr_name, va.value as default_value) r ))) as attrs
from """ + helper.table_prefix + """vis_code_attr vca
left join """ + helper.table_prefix + """vis_attr va on va.id = vca.attr_id
group by vca.vis_id
),
-- do guidance
dos_array as (
select doa.vis_id,
array_agg(row_to_json((
select r
from (select doa.*) r
))) as dos
from """ + helper.table_prefix + """vis_do_attr doa
group by doa.vis_id
),
-- dont guidance
donts_array as (
select donta.vis_id,
array_agg(row_to_json((
select r
from (select donta.*) r
))) as donts
from """ + helper.table_prefix + """vis_dont_attr donta
group by donta.vis_id
),
-- alternatives
alts_array as (
select val.vis_id,
array_agg(row_to_json((select r from (select val.*, vd.name as alt_name, vt.url_name as alt_type) r ))) as alts
from """ + helper.table_prefix + """vis_alt_attr val
left join """ + helper.table_prefix +
"""vis_directive vd on vd.id = val.alt_vis_directive_id
left join """ + helper.table_prefix +
"""vis v on v.id = val.alt_vis_directive_id
left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id
group by val.vis_id
)
select v.*,
vt.name as vis_type_name,
vt.url_name as vis_type_urlname,
vd.name as directive,
dd.data,
vca.attrs,
doa.dos,
donta.donts,
val.alts
from """ + helper.table_prefix + """vis v
left join """ + helper.table_prefix +
"""vis_directive vd on vd.id = v.vis_directive_id
left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id
left join data_array dd on dd.vis_id = v.id
left join attrs_array vca on vca.vis_id = v.id
left join dos_array doa on doa.vis_id = v.id
left join donts_array donta on donta.vis_id = v.id
left join alts_array val on val.vis_id = v.id
where vt.url_name = 'time-series';
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute(
"""
select g.*,
array_agg(row_to_json(s)) as subgroups
from """ + helper.table_prefix + """group g
left join (
select distinct on (sg.name_id, sg.group_id) sg.name_id,
sg.group_id,
case
when gt.id = 1 then gn.name
else sgn.name
end as name,
case when gt.id = 1 then gt.id || '_' || gn.id
else gt.id || '_' || sgn.id
end as id,
geo.hexagon_center_x as center_x,
geo.hexagon_center_y as center_y,
array_agg(row_to_json(n)) as nodes
from """ + helper.table_prefix + """subgroup sg
left join """ + helper.table_prefix +
"""geography_name gn on gn.id = sg.name_id
left join """ + helper.table_prefix +
"""subgroup_name sgn on sgn.id = sg.name_id
left join """ + helper.table_prefix + """group g on g.id = sg.group_id
left join """ + helper.table_prefix +
"""group_type gt on gt.id = g.type_id
left join """ + helper.table_prefix +
"""geography geo on geo.name_id = sg.name_id and gt.id = 1
left join (
select gn.name,
gcy.id,
gcy.iso2code as iso
from """ + helper.table_prefix + """country gcy
left join """ + helper.table_prefix +
"""geography_name gn on gn.id = gcy.name_id
) n on n.id = sg.country_id
group by sg.name_id,
sg.group_id,
gn.name,
sgn.name,
geo.hexagon_center_x,
geo.hexagon_center_y,
gt.id,
g.id,
gn.id,
sgn.id
) s on s.group_id = g.id group by g.id,g.name, g.type_id;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
-- dummy data
with data_array as (
-- nodes
with node_array as (
select dd.vis_id,
array_agg(row_to_json((select r from (select ddl.name) r))) as nodes
from """ + helper.table_prefix + """vis_dummy_data dd
left join """ + helper.table_prefix +
"""vis_dummy_data_label ddl on ddl.id = dd.name_id
group by dd.vis_id
),
-- links
link_array as (
select ddv.data_id,
array_agg(row_to_json((select r from (select ddl.name) r))) as links
from """ + helper.table_prefix + """vis_dummy_data_values ddv
left join """ + helper.table_prefix +
"""vis_dummy_data_label ddl on ddl.id = ddv.value
group by ddv.data_id
)
select dd.vis_id,
array_agg(row_to_json((select r from (select ddl.name, na.nodes, la.links) r))) as data
from """ + helper.table_prefix + """vis_dummy_data dd
left join """ + helper.table_prefix +
"""vis_dummy_data_label ddl on ddl.id = dd.name_id
left join node_array na on na.vis_id = dd.id
left join link_array la on la.data_id = dd.id
group by dd.vis_id
)
select v.*,
vt.name as vis_type_name,
vt.url_name as vis_type_urlname,
vd.name as directive--,
--dd.data
from """ + helper.table_prefix + """vis v
left join """ + helper.table_prefix +
"""vis_directive vd on vd.id = v.vis_directive_id
left join """ + helper.table_prefix +
"""vis_type vt on vt.id = v.vis_type_id
--left join data_array dd on dd.vis_id = v.id
where vt.url_name = 'relatedness';
""")
# obtain the data
data = self.cursor.fetchall()
# temp open from file for now
with open("data/related.json") as json_data:
# dict
fc = json.load(json_data)
# loop through meta info
for obj in data:
# add dummy data until db is updated
obj["data"] = fc
# convert data to a string
return json.dumps(data)
def GET(self,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute(
"""
with data_array as (
select dd.vis_id,
array_agg(row_to_json((select r from (select ddl.name, dd.value) r))) as data
from """ + helper.table_prefix + """vis_dummy_data dd
left join """ + helper.table_prefix +
"""vis_dummy_data_label ddl on ddl.id = dd.name_id
group by dd.vis_id
),
-- attributes
attrs_array as (
select vca.vis_id,
array_agg(row_to_json((select r from (select vca.*, va.name as attr_name, va.value as default_value) r ))) as attrs
from """ + helper.table_prefix + """vis_code_attr vca
left join """ + helper.table_prefix + """vis_attr va on va.id = vca.attr_id
group by vca.vis_id
),
-- do guidance
dos_array as (
select doa.vis_id,
array_agg(row_to_json((
select r
from (select doa.*) r
))) as dos
from """ + helper.table_prefix + """vis_do_attr doa
group by doa.vis_id
),
-- dont guidance
donts_array as (
select donta.vis_id,
array_agg(row_to_json((
select r
from (select donta.*) r
))) as donts
from """ + helper.table_prefix + """vis_dont_attr donta
group by donta.vis_id
),
-- alternatives
alts_array as (
select val.vis_id,
array_agg(row_to_json((select r from (select val.*, vd.name as alt_name, vt.url_name as alt_type) r ))) as alts
from """ + helper.table_prefix + """vis_alt_attr val
left join """ + helper.table_prefix +
"""vis_directive vd on vd.id = val.alt_vis_directive_id
left join """ + helper.table_prefix +
"""vis v on v.id = val.alt_vis_directive_id
left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id
group by val.vis_id
)
select v.*,
vt.name as vis_type_name,
vt.url_name as vis_type_urlname,
vd.name as directive,
dd.data,
vca.attrs,
doa.dos,
donta.donts,
val.alts
from """ + helper.table_prefix + """vis v
left join """ + helper.table_prefix +
"""vis_directive vd on vd.id = v.vis_directive_id
left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id
left join data_array dd on dd.vis_id = v.id
left join attrs_array vca on vca.vis_id = v.id
left join dos_array doa on doa.vis_id = v.id
left join donts_array donta on donta.vis_id = v.id
left join alts_array val on val.vis_id = v.id
where vt.url_name = 'hierarchy';
""")
# obtain the data
data = self.cursor.fetchall()
# temp open from file for now
with open("data/hierarchy.json") as json_data:
# dict
fc = json.load(json_data)
# loop through meta info
for obj in data:
# add dummy data until db is updated
obj["data"] = fc
# convert data to a string
return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# execute query
self.cursor.execute("""
-- dummy data
with data_array as (
-- values
with value_array as (
select ddv.data_id,
array_agg(row_to_json((select r from (select ddv.value, ddv.date as timestamp) r))) as values
from """ + helper.table_prefix + """vis_dummy_data_values ddv
group by ddv.data_id
)
select dd.vis_id,
array_agg(row_to_json((select r from (select ddl.name, va.values
) r))) as data
from """ + helper.table_prefix + """vis_dummy_data dd
left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id
left join value_array va on va.data_id = dd.id
group by dd.vis_id
),
-- attributes
attrs_array as (
select vca.vis_id,
array_agg(row_to_json((select r from (select vca.*, va.name as attr_name, va.value as default_value) r ))) as attrs
from """ + helper.table_prefix + """vis_code_attr vca
left join """ + helper.table_prefix + """vis_attr va on va.id = vca.attr_id
group by vca.vis_id
),
-- do guidance
dos_array as (
select doa.vis_id,
array_agg(row_to_json((
select r
from (select doa.*) r
))) as dos
from """ + helper.table_prefix + """vis_do_attr doa
group by doa.vis_id
),
-- dont guidance
donts_array as (
select donta.vis_id,
array_agg(row_to_json((
select r
from (select donta.*) r
))) as donts
from """ + helper.table_prefix + """vis_dont_attr donta
group by donta.vis_id
),
-- alternatives
alts_array as (
select val.vis_id,
array_agg(row_to_json((select r from (select val.*, vd.name as alt_name, vt.url_name as alt_type) r ))) as alts
from """ + helper.table_prefix + """vis_alt_attr val
left join """ + helper.table_prefix + """vis_directive vd on vd.id = val.alt_vis_directive_id
left join """ + helper.table_prefix + """vis v on v.id = val.alt_vis_directive_id
left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id
group by val.vis_id
)
select v.*,
vt.name as vis_type_name,
vt.url_name as vis_type_urlname,
vd.name as directive,
dd.data,
vca.attrs,
doa.dos,
donta.donts,
val.alts
from """ + helper.table_prefix + """vis v
left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id
left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id
left join data_array dd on dd.vis_id = v.id
left join attrs_array vca on vca.vis_id = v.id
left join dos_array doa on doa.vis_id = v.id
left join donts_array donta on donta.vis_id = v.id
left join alts_array val on val.vis_id = v.id
where vt.url_name = 'time-series';
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
def GET(self,
data_type,
polygon_type,
connection_string=helper.get_connection_string(
os.environ['DATABASE_URL'])):
# connect to postgresql based on configuration in connection_string
connection = psycopg2.connect(connection_string)
# get a cursor to perform queries
self.cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
# check param
if (data_type == "xy"):
# execute query to return svg simple x/y coordinate system
self.cursor.execute("""
select geo.id,
gn.name,
cty.iso2code as iso,
geo.hexagon_center_x as center_x,
geo.hexagon_center_y as center_y
from """ + helper.table_prefix + """geography geo
left join """ + helper.table_prefix +
"""geography_name gn on gn.id = geo.name_id
left join """ + helper.table_prefix +
"""country cty on cty.id = geo.name_id
where geo.hexagon_center_x is not null and geo.hexagon_center_y is not null;
""")
else:
# execute query to return mercator projection in geojson coordinate system
self.cursor.execute("""
select 'FeatureCollection' as type,
array_agg(row_to_json(r)) as features
from (
with t as (
select 'Feature'::text
)
select t.text as type,
row_to_json(f) as properties,
row_to_json(c) as geometry
from t,
""" + helper.table_prefix + """geography geo
left join (
select geo.id,
gn.name,
cy.iso2code as iso
from """ + helper.table_prefix + """geography geo
left join """ + helper.table_prefix +
"""geography_name gn on gn.id = geo.name_id
left join """ + helper.table_prefix +
"""country cy on cy.id = geo.name_id
) f on f.id = geo.id
left join (
with t as (
select 'Polygon'::text
)
select t.text as type,
geo.""" + polygon_type + """_polygon as coordinates
from t,
""" + helper.table_prefix + """geography geo
) c on c.coordinates = geo.""" + polygon_type + """_polygon
where geo.""" + polygon_type + """_polygon is not null
) r
group by type;
""")
# obtain the data
data = self.cursor.fetchall()
# convert data to a string
return json.dumps(data, default=helper.decimal_encoder)
