def save(self, knowl, who):
"""who is the ID of the user, who wants to save the knowl"""
most_recent = self.get_knowl(knowl.id, ['id'] + self._default_fields, allow_deleted=False)
new_knowl = most_recent is None
if new_knowl:
authors = []
else:
authors = most_recent.pop('authors', [])
if who and who not in authors:
authors = authors + [who]
search_keywords = make_keywords(knowl.content, knowl.id, knowl.title)
cat = extract_cat(knowl.id)
# When renaming, source is set explicitly on the knowl
if knowl.type == 0 and knowl.source is not None:
typ, source, name = 0, knowl.source, knowl.source_name
else:
typ, source, name = extract_typ(knowl.id)
links = extract_links(knowl.content)
defines = extract_defines(knowl.content)
# id, authors, cat, content, last_author, timestamp, title, status, type, links, defines, source, source_name
values = (knowl.id, authors, cat, knowl.content, who, knowl.timestamp, knowl.title, knowl.status, typ, links, defines, source, name, search_keywords)
with DelayCommit(self):
inserter = SQL("INSERT INTO kwl_knowls (id, {0}, _keywords) VALUES ({1})")
inserter = inserter.format(SQL(', ').join(map(Identifier, self._default_fields)), SQL(", ").join(Placeholder() * (len(self._default_fields) + 2)))
self._execute(inserter, values)
self.cached_titles[knowl.id] = knowl.title
def save(self, knowl, who):
"""who is the ID of the user, who wants to save the knowl"""
new_history_item = self.get_knowl(knowl.id, ['id'] + self._default_fields + ['history'])
new_knowl = new_history_item is None
if new_knowl:
history = []
authors = []
else:
history = new_history_item.pop('history')
if history is not None:
history += [new_history_item]
else:
history = []
authors = new_history_item.pop('authors', [])
if authors is None:
authors = []
if who and who not in authors:
authors = authors + [who]
search_keywords = make_keywords(knowl.content, knowl.id, knowl.title)
cat = extract_cat(knowl.id)
values = (authors, cat, knowl.content, who, knowl.quality, knowl.timestamp, knowl.title, history, search_keywords)
with DelayCommit(self):
insterer = SQL("INSERT INTO kwl_knowls (id, {0}, history, _keywords) VALUES (%s, {1}) ON CONFLICT (id) DO UPDATE SET ({0}, history, _keywords) = ({1})")
insterer = insterer.format(SQL(', ').join(map(Identifier, self._default_fields)), SQL(", ").join(Placeholder() * (len(self._default_fields) + 2)))
self._execute(insterer, (knowl.id,) + values + values)
self.save_history(knowl, who)
def search(self,
category="",
filters=[],
types=[],
keywords="",
author=None,
sort=[],
projection=['id', 'title'],
regex=False):
"""
INPUT:
- ``category`` -- a knowl category such as "ec" or "mf".
- ``filters`` -- a list, giving a subset of "beta", "reviewed", "in progress" and "deleted".
Knowls in the returned list will have their most recent status among the provided values.
- ``types`` -- a list, giving a subset of ["normal", "annotations"]
- ``keywords`` -- a string giving a space separated list of lower case keywords from the id, title and content. If regex is set, will be used instead as a regular expression to match against content, title and knowl id.
- ``author`` -- a string or list of strings giving authors
- ``sort`` -- a list of strings or pairs (x, dir) where x is a column name and dir is 1 or -1.
- ``projection`` -- a list of column names, not including ``_keywords``
- ``regex`` -- whether to use regular expressions rather than keyword search
"""
restrictions = []
values = []
if category:
restrictions.append(SQL("cat = %s"))
values.append(category)
if 'in progress' not in filters:
restrictions.append(SQL("status != %s"))
values.append(-1)
if keywords:
if regex:
restrictions.append(
SQL("content ~ %s OR title ~ %s OR id ~ %s"))
values.extend([keywords, keywords, keywords])
else:
keywords = [w for w in keywords.split(" ") if len(w) >= 3]
if keywords:
restrictions.append(SQL("_keywords @> %s"))
values.append(keywords)
if author is not None:
restrictions.append(SQL("authors @> %s"))
values.append([author])
# In order to be able to sort by arbitrary columns, we have to select everything here.
# We therefore do the projection in Python, which is fine for the knowls table since it's tiny
fields = ['id'] + self._default_fields
sqlfields = SQL(", ").join(map(Identifier, fields))
projfields = [(col, fields.index(col)) for col in projection]
if restrictions:
restrictions = SQL(" WHERE ") + SQL(" AND ").join(restrictions)
else:
restrictions = SQL("")
selecter = SQL(
"SELECT DISTINCT ON (id) {0} FROM kwl_knowls{1} ORDER BY id, timestamp DESC"
).format(sqlfields, restrictions)
secondary_restrictions = []
if filters:
secondary_restrictions.append(
SQL("knowls.{0} = ANY(%s)").format(Identifier("status")))
values.append([
knowl_status_code[q] for q in filters if q in knowl_status_code
])
else:
secondary_restrictions.append(SQL("status >= %s"))
values.append(0)
if not types:
# default to just showing normal knowls
types = ["normal"]
if len(types) == 1:
secondary_restrictions.append(SQL("type = %s"))
values.append(knowl_type_code[types[0]])
else:
secondary_restrictions.append(SQL("type = ANY(%s)"))
values.append([knowl_type_code[typ] for typ in types])
secondary_restrictions = SQL(" AND ").join(secondary_restrictions)
if sort:
sort = SQL(" ORDER BY ") + self._sort_str(sort)
else:
sort = SQL("")
selecter = SQL("SELECT {0} FROM ({1}) knowls WHERE {2}{3}").format(
sqlfields, selecter, secondary_restrictions, sort)
cur = self._execute(selecter, values)
return [{k: res[i] for k, i in projfields} for res in cur]
def get_create_sql(self):
return SQL(' ').join(
filter(not_empty, (Identifier(
self.name), self.get_type_sql(), self.get_constraints_sql())))
def update(self, model_name: str, since_date):
log.info('Updating index {} with changes since {}'.format(
model_name, since_date))
query = SQL('SELECT * FROM {} WHERE updated_on >= \'{}\''.format(
model_name, since_date))
self._replicate(model_name, model_name, query)
return await self.enqueue(message.topic, message.partition, message.value)
async def enqueue(self, topic: str, partition: int, binary: bytes) -> int:
"""Insert row into queue table.
Retrieves number of affected rows and row ID.
Args:
topic: Kafka topic. Example: "TicketAdded"
partition: Kafka partition number.
binary: Broker Message in bytes.
Returns:
Queue ID.
Example: 12
Raises:
Exception: An error occurred inserting record.
"""
row = await self.submit_query_and_fetchone(_INSERT_QUERY, (topic, partition, binary))
await self.submit_query(_NOTIFY_QUERY.format(Identifier(topic)))
return row[0]
_INSERT_QUERY = SQL("INSERT INTO consumer_queue (topic, partition, data) VALUES (%s, %s, %s) RETURNING id")
_NOTIFY_QUERY = SQL("NOTIFY {}")
def get_constraints_sql(self, extra=()):
return super().get_constraints_sql(
(SQL('PRIMARY KEY') if self.primary_key else None, *extra))
def sent_messages_report(realm: str) -> str:
title = "Recently sent messages for " + realm
cols = [
"Date",
"Humans",
"Bots",
]
query = SQL("""
select
series.day::date,
humans.cnt,
bots.cnt
from (
select generate_series(
(now()::date - interval '2 week'),
now()::date,
interval '1 day'
) as day
) as series
left join (
select
date_sent::date date_sent,
count(*) cnt
from zerver_message m
join zerver_userprofile up on up.id = m.sender_id
join zerver_realm r on r.id = up.realm_id
where
r.string_id = %s
and
(not up.is_bot)
and
date_sent > now() - interval '2 week'
group by
date_sent::date
order by
date_sent::date
) humans on
series.day = humans.date_sent
left join (
select
date_sent::date date_sent,
count(*) cnt
from zerver_message m
join zerver_userprofile up on up.id = m.sender_id
join zerver_realm r on r.id = up.realm_id
where
r.string_id = %s
and
up.is_bot
and
date_sent > now() - interval '2 week'
group by
date_sent::date
order by
date_sent::date
) bots on
series.day = bots.date_sent
""")
cursor = connection.cursor()
cursor.execute(query, [realm, realm])
rows = cursor.fetchall()
cursor.close()
return make_table(title, cols, rows)
def sql(self):
return SQL('CREATE INDEX {} ON {} ({})').format(
Identifier(self.name),
Identifier(self.table.name),
Identifier(self.column_name)
), ()
def sql_injection_ignored_cases(self, ids, cr2):
# This cr.execute2 or cr2.execute should not be detected
self._cr.execute2('SELECT name FROM account WHERE id IN %s' %
(tuple(ids), ))
cr2.execute('SELECT name FROM account WHERE id IN %s' % (tuple(ids), ))
# Ignore when the query is built using private attributes
self._cr.execute('DELETE FROM %s WHERE id IN %%s' % self._table,
(tuple(ids), ))
# Ignore string parsed with "".format() if args are psycopg2.sql.* calls
query = "SELECT * FROM table"
# imported from pyscopg2 import sql
self._cr.execute(
sql.SQL("""CREATE or REPLACE VIEW {} as ({})""").format(
sql.Identifier(self._table), sql.SQL(query)))
self._cr.execute(
sql.SQL("""CREATE or REPLACE VIEW {table} as ({query})""").format(
table=sql.Identifier(self._table),
query=sql.SQL(query),
))
# imported from pyscopg2.sql import SQL, Identifier
self._cr.execute(
SQL("""CREATE or REPLACE VIEW {} as ({})""").format(
Identifier(self._table),
SQL(query),
))
self._cr.execute(
SQL("""CREATE or REPLACE VIEW {table} as ({query})""").format(
table=Identifier(self._table),
query=SQL(query),
))
# imported from pyscopg2 direclty
self._cr.execute(
psycopg2.SQL("""CREATE or REPLACE VIEW {} as ({})""").format(
psycopg2.sql.Identifier(self._table),
psycopg2.sql.SQL(query),
))
self._cr.execute(
psycopg2.sql.SQL(
"""CREATE or REPLACE VIEW {table} as ({query})""").format(
table=Identifier(self._table),
query=SQL(query),
))
# Variables build using pyscopg2.sql.* callers
table = Identifier('table_name')
sql_query = SQL(query)
# format params
self._cr.execute(
SQL("""CREATE or REPLACE VIEW {} as ({})""").format(
table,
sql_query,
))
# format dict
self._cr.execute(
SQL("""CREATE or REPLACE VIEW {table} as ({query})""").format(
table=table,
query=sql_query,
))
self._cr.execute('SELECT name FROM %(table)s' % {'table': self._table})
from usaspending_api.common.cache_decorator import cache_response
from usaspending_api.common.helpers.sql_helpers import execute_sql_to_ordered_dictionary
from usaspending_api.common.validator.award import get_internal_or_generated_award_id_model
from usaspending_api.common.validator.tinyshield import validate_post_request
ROLLUP_SQL = SQL("""
with gather_financial_accounts_by_awards as (
select a.awarding_agency_id,
a.funding_agency_id,
nullif(faba.transaction_obligated_amount, 'NaN') transaction_obligated_amount,
faba.treasury_account_id
from awards a
inner join financial_accounts_by_awards faba on faba.award_id = a.id
where {award_id_column} = {award_id}
)
select
coalesce(sum(gfaba.transaction_obligated_amount), 0.0) total_transaction_obligated_amount,
count(distinct aa.toptier_agency_id) awarding_agency_count,
count(distinct af.toptier_agency_id) funding_agency_count,
count(distinct taa.agency_id || '-' || taa.main_account_code) federal_account_count
from
gather_financial_accounts_by_awards gfaba
left outer join treasury_appropriation_account taa on
taa.treasury_account_identifier = gfaba.treasury_account_id
left outer join agency aa on aa.id = gfaba.awarding_agency_id
left outer join agency af on af.id = gfaba.funding_agency_id
""")
@validate_post_request([get_internal_or_generated_award_id_model()])
class AwardFundingRollupViewSet(APIView):
"""
def createDF_byRace_anySUD(logger, race):
'''Creates a dataframe with comorbid mental disorder diagnoses with SUD
This function creates a dataframe of users from each race, with the first
column being having any sud, the dependent variable, and the rest of the
columns being the independent variables of the other mental disorder diagnoses.
Decorators:
lD.log
Arguments:
logger {logging.Logger} -- logs error information
race {str} -- 'AA', 'NHPI', or 'MR'
'''
try:
query = SQL('''
SELECT
t2.sud,
t2.mood,
t2.anxiety,
t2.adjustment,
t2.adhd,
t2.psyc,
t2.pers,
t2.childhood,
t2.impulse,
t2.cognitive,
t2.eating,
t2.smtf,
t2.disso,
t2.sleep,
t2.fd
FROM
sarah.test2 t1
INNER JOIN
sarah.test3 t2
ON
t1.patientid = t2.patientid
WHERE
t1.age BETWEEN 12 AND 100
AND
t1.race = {}
''').format(Literal(race))
data = pgIO.getAllData(query)
sud_data = [d[0] for d in data]
mood_data = [d[1] for d in data]
anxiety_data = [d[2] for d in data]
adjustment_data = [d[3] for d in data]
adhd_data = [d[4] for d in data]
psyc_data = [d[5] for d in data]
pers_data = [d[6] for d in data]
childhood_data = [d[7] for d in data]
impulse_data = [d[8] for d in data]
cognitive_data = [d[9] for d in data]
eating_data = [d[10] for d in data]
smtf_data = [d[11] for d in data]
disso_data = [d[12] for d in data]
sleep_data = [d[13] for d in data]
fd_data = [d[14] for d in data]
d = {
'sud': sud_data,
'mood': mood_data,
'anxiety': anxiety_data,
'adjustment': adjustment_data,
'adhd': adhd_data,
'psyc': psyc_data,
'pers': pers_data,
'childhood': childhood_data,
'impulse': impulse_data,
'cognitive': cognitive_data,
'eating': eating_data,
'smtf': smtf_data,
'disso': disso_data,
'sleep': sleep_data,
'fd': fd_data
}
df = pd.DataFrame(data=d)
# Change all columns to binary
df.replace({False: 0, True: 1}, inplace=True)
df['intercept'] = 1.0
except Exception as e:
logger.error('createDF_byRace_anySUD failed because of {}'.format(e))
return df
seminar = seminars_lookup(seminar_id)
else:
resp, seminar = can_edit_seminar(seminar_id, new=False)
if resp is not None:
return resp, None, None
if seminar.new:
# TODO: This is where you might insert the ability to create a talk without first making a seminar
flash_error("You must first create the seminar %s" % seminar_id)
return redirect(url_for("edit_seminar", shortname=seminar_id), 301)
if new:
talk = WebTalk(seminar_id, seminar=seminar, editing=True)
else:
talk = WebTalk(seminar_id, seminar_ctr, seminar=seminar)
return None, seminar, talk
_selecter = SQL("SELECT {0} FROM (SELECT DISTINCT ON (seminar_id, seminar_ctr) {0} FROM {1} ORDER BY seminar_id, seminar_ctr, id DESC) tmp{2}")
_counter = SQL("SELECT COUNT(*) FROM (SELECT 1 FROM (SELECT DISTINCT ON (seminar_id, seminar_ctr) {0} FROM {1} ORDER BY seminar_id, seminar_ctr, id DESC) tmp{2}) tmp2")
_maxer = SQL("SELECT MAX({0}) FROM (SELECT DISTINCT ON (seminar_id, seminar_ctr) {1} FROM {2} ORDER BY seminar_id, seminar_ctr, id DESC) tmp{3}")
def _construct(rec):
if isinstance(rec, str):
return rec
else:
return WebTalk(rec['seminar_id'], rec['seminar_ctr'], data=rec)
def _iterator(cur, search_cols, extra_cols, projection):
for rec in db.talks._search_iterator(cur, search_cols, extra_cols, projection):
yield _construct(rec)
def talks_count(query={}):
"""
Replacement for db.talks.count to account for versioning and so that we don't cache results.
"""
def get_all_licenses(in_app_db) -> dict:
with DatabaseContext(in_app_db, cursor_factory=RealDictCursor) as cursor:
command = SQL("select * from License")
cursor.execute(command)
return cursor.fetchall()
def resurrect(self, knowl):
"""Sets the status for all deleted copies of the knowl to beta"""
updator = SQL(
"UPDATE kwl_knowls SET status=%s WHERE status=%s AND id=%s")
self._execute(updator, [0, -2, knowl.id])
self.cached_titles[knowl.id] = knowl.title
def delete(self, knowl):
"""deletes this knowl from the db. This is effected by setting the status to -2 on all copies of the knowl"""
updator = SQL("UPDATE kwl_knowls SET status=%s WHERE id=%s")
self._execute(updator, [-2, knowl.id])
if knowl.id in self.cached_titles:
self.cached_titles.pop(knowl.id)
def add_extent(self, xmin: float, ymin: float, xmax: float, ymax: float, srid=3877) -> None:
self.where_parts.append(
SQL('{geom} && ST_MakeEnvelope(%(xmin)s, %(ymin)s, %(xmax)s, %(ymax)s, %(srid)s)').format(
geom=self.layer_wrapper.geom_field)
)
self.vars.update({'xmin': xmin, 'ymin': ymin, 'xmax': xmax, 'ymax': ymax, 'srid': srid})
def __init__(self, model, module_name=None, history=False):
super(TableHandler, self).__init__(model,
module_name=module_name,
history=history)
self._columns = {}
self._constraints = []
self._fk_deltypes = {}
self._indexes = []
transaction = Transaction()
cursor = transaction.connection.cursor()
# Create sequence if necessary
if not transaction.database.sequence_exist(transaction.connection,
self.sequence_name):
transaction.database.sequence_create(transaction.connection,
self.sequence_name)
# Create new table if necessary
if not self.table_exist(self.table_name):
cursor.execute(
SQL('CREATE TABLE {} ()').format(Identifier(self.table_name)))
self.table_schema = transaction.database.get_table_schema(
transaction.connection, self.table_name)
cursor.execute(
'SELECT tableowner = current_user FROM pg_tables '
'WHERE tablename = %s AND schemaname = %s',
(self.table_name, self.table_schema))
self.is_owner, = cursor.fetchone()
if model.__doc__ and self.is_owner:
cursor.execute(
SQL('COMMENT ON TABLE {} IS %s').format(
Identifier(self.table_name)), (model.__doc__, ))
self._update_definitions(columns=True)
if 'id' not in self._columns:
if not self.history:
cursor.execute(
SQL("ALTER TABLE {} ADD COLUMN id INTEGER "
"DEFAULT nextval(%s) NOT NULL").format(
Identifier(self.table_name)),
(self.sequence_name, ))
cursor.execute(
SQL('ALTER TABLE {} ADD PRIMARY KEY(id)').format(
Identifier(self.table_name)))
else:
cursor.execute(
SQL('ALTER TABLE {} ADD COLUMN id INTEGER').format(
Identifier(self.table_name)))
self._update_definitions(columns=True)
if self.history and '__id' not in self._columns:
cursor.execute(
SQL("ALTER TABLE {} ADD COLUMN __id INTEGER "
"DEFAULT nextval(%s) NOT NULL").format(
Identifier(self.table_name)), (self.sequence_name, ))
cursor.execute(
SQL('ALTER TABLE {} ADD PRIMARY KEY(__id)').format(
Identifier(self.table_name)))
else:
default = "nextval('%s'::regclass)" % self.sequence_name
if self.history:
if self._columns['__id']['default'] != default:
cursor.execute(
SQL("ALTER TABLE {} "
"ALTER __id SET DEFAULT nextval(%s::regclass)"
).format(Identifier(self.table_name)),
(self.sequence_name, ))
if self._columns['id']['default'] != default:
cursor.execute(
SQL("ALTER TABLE {} "
"ALTER id SET DEFAULT nextval(%s::regclass)").format(
Identifier(self.table_name)),
(self.sequence_name, ))
self._update_definitions()
GET_CHILD_IDVS_SQL = SQL(
"""
select
ac.id award_id,
ac.type_description award_type,
ac.description,
tta.name funding_agency,
ttb.name awarding_agency,
ac.funding_agency_id,
ac.awarding_agency_id,
ac.generated_unique_award_id,
tf.ordering_period_end_date last_date_to_order,
pac.rollup_total_obligation obligated_amount,
ac.period_of_performance_current_end_date,
ac.period_of_performance_start_date,
ac.piid
from
parent_award pap
inner join parent_award pac on pac.parent_award_id = pap.award_id
inner join awards ac on ac.id = pac.award_id
inner join transaction_fpds tf on tf.transaction_id = ac.latest_transaction_id
left outer join agency a on a.id = ac.funding_agency_id
left outer join agency b on b.id = ac.awarding_agency_id
left outer join toptier_agency tta on tta.toptier_agency_id = a.toptier_agency_id
left outer join toptier_agency ttb on ttb.toptier_agency_id = b.toptier_agency_id
where
pap.{award_id_column} = {award_id}
order by
{sort_column} {sort_direction}, ac.id {sort_direction}
limit {limit} offset {offset}
"""
)
def add_column(self, column_name, sql_type, default=None, comment=''):
cursor = Transaction().connection.cursor()
database = Transaction().database
column_type = database.sql_type(sql_type)
match = VARCHAR_SIZE_RE.match(sql_type)
field_size = int(match.group(1)) if match else None
def add_comment():
if comment and self.is_owner:
cursor.execute(
SQL('COMMENT ON COLUMN {}.{} IS %s').format(
Identifier(self.table_name), Identifier(column_name)),
(comment, ))
if self.column_exist(column_name):
if (column_name in ('create_date', 'write_date')
and column_type[1].lower() != 'timestamp(6)'):
# Migrate dates from timestamp(0) to timestamp
cursor.execute(
SQL('ALTER TABLE {} ALTER COLUMN {} TYPE timestamp'
).format(Identifier(self.table_name),
Identifier(column_name)))
add_comment()
base_type = column_type[0].lower()
if base_type != self._columns[column_name]['typname']:
if (self._columns[column_name]['typname'], base_type) in [
('varchar', 'text'),
('text', 'varchar'),
('date', 'timestamp'),
('int4', 'int8'),
('int4', 'float8'),
('int4', 'numeric'),
('int8', 'float8'),
('int8', 'numeric'),
('float8', 'numeric'),
]:
self.alter_type(column_name, base_type)
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from %s to %s.', column_name, self.table_name,
self._columns[column_name]['typname'], base_type)
if (base_type == 'varchar'
and self._columns[column_name]['typname'] == 'varchar'):
# Migrate size
from_size = self._columns[column_name]['size']
if field_size is None:
if from_size:
self.alter_size(column_name, base_type)
elif from_size == field_size:
pass
elif from_size and from_size < field_size:
self.alter_size(column_name, column_type[1])
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from varchar(%s) to varchar(%s).', column_name,
self.table_name,
from_size if from_size and from_size > 0 else "",
field_size)
return
column_type = column_type[1]
cursor.execute(
SQL('ALTER TABLE {} ADD COLUMN {} {}').format(
Identifier(self.table_name), Identifier(column_name),
SQL(column_type)))
add_comment()
if default:
# check if table is non-empty:
cursor.execute('SELECT 1 FROM "%s" limit 1' % self.table_name)
if cursor.rowcount:
# Populate column with default values:
cursor.execute(
SQL('UPDATE {} SET {} = %s').format(
Identifier(self.table_name), Identifier(column_name)),
(default(), ))
self._update_definitions(columns=True)
def main():
# Preserve encoding to UTF-8
os.environ['SHAPE_ENCODING'] = "utf-8"
newFields = [
NewFieldDef("mow_n", ogr.OFTInteger, "0"),
NewFieldDef("m1_dstart", ogr.OFTString, "0"),
NewFieldDef("m1_dend", ogr.OFTString, "0"),
NewFieldDef("m1_conf", ogr.OFTReal, "0"),
NewFieldDef("m1_mis", ogr.OFTString, "0"),
NewFieldDef("m2_dstart", ogr.OFTString, "0"),
NewFieldDef("m2_dend", ogr.OFTString, "0"),
NewFieldDef("m2_conf", ogr.OFTReal, "0"),
NewFieldDef("m2_mis", ogr.OFTString, "0"),
NewFieldDef("m3_dstart", ogr.OFTString, "0"),
NewFieldDef("m3_dend", ogr.OFTString, "0"),
NewFieldDef("m3_conf", ogr.OFTReal, "0"),
NewFieldDef("m3_mis", ogr.OFTString, "0"),
NewFieldDef("m4_dstart", ogr.OFTString, "0"),
NewFieldDef("m4_dend", ogr.OFTString, "0"),
NewFieldDef("m4_conf", ogr.OFTReal, "0"),
NewFieldDef("m4_mis", ogr.OFTString, "0"),
NewFieldDef("proc", ogr.OFTInteger, "0"),
NewFieldDef("compl", ogr.OFTInteger, "0")
]
parser = argparse.ArgumentParser(
description="Creates the grassland mowing input shapefile")
parser.add_argument('-c',
'--config-file',
default='/etc/sen2agri/sen2agri.conf',
help="configuration file location")
parser.add_argument('-s',
'--site-id',
type=int,
help="site ID to filter by")
parser.add_argument('-p', '--path', default='.', help="working path")
parser.add_argument('-y', '--year', help="year")
parser.add_argument('-f',
'--filter-ctnum',
default="",
help="Filtering CTnum fields")
parser.add_argument(
'-a',
'--add-decl-cols',
default="",
help=
"Additional columns from declarations table to be added to the output shapefile"
)
parser.add_argument('--force', help="overwrite field", action='store_true')
parser.add_argument('--filter-ids-table',
help="A table name containing filter ids")
parser.add_argument(
'--srid', help="EPSG projection to be used for the output shapefile")
#parser.add_argument('--dynamic-srid', default=False, help="Compute dynamically the srid from the NDVI products")
args = parser.parse_args()
config = Config(args)
pool = multiprocessing.dummy.Pool(1)
pg_path = 'PG:dbname={} host={} port={} user={} password={}'.format(
config.dbname, config.host, config.port, config.user, config.password)
with psycopg2.connect(host=config.host,
port=config.port,
dbname=config.dbname,
user=config.user,
password=config.password) as conn:
site_name = get_site_name(conn, config.site_id)
year = args.year or date.today().year
lpis_table = "decl_{}_{}".format(site_name, year)
lut_table = "lut_{}_{}".format(site_name, year)
commands = []
shp = args.path
ctnumFilter = ""
if not os.path.exists(os.path.dirname(shp)):
try:
os.makedirs(os.path.dirname(shp))
except OSError as exc: # Guard against race condition
if exc.errno != errno.EEXIST:
raise
ctnums = []
str_ctnums = ""
if args.filter_ctnum:
ctnums = list(map(int, args.filter_ctnum.split(',')))
str_ctnums = ', '.join(str(x) for x in ctnums)
# Additional column names to be added from the declarations table
add_col_names = []
str_add_col_names = ""
if args.add_decl_cols:
add_col_names = [x.strip() for x in args.add_decl_cols.split(',')]
str_add_col_names = ', '.join(str(x) for x in add_col_names)
str_add_col_names = ", " + str_add_col_names
if len(ctnums) > 0:
if args.filter_ids_table:
sql = "select \"NewID\", ctnum as \"CTnum\", ori_hold as \"Ori_hold\", ori_id as \"Ori_id\", ori_crop as \"Ori_crop\", \"Area_meters\" as \"Area_meter\", wkb_geometry {} from {} natural join {} where ctnum in ({}) and \"NewID\" in (select newid from {})".format(
str_add_col_names, lpis_table, lut_table, str_ctnums,
args.filter_ids_table)
else:
sql = "select \"NewID\", ctnum as \"CTnum\", ori_hold as \"Ori_hold\", ori_id as \"Ori_id\", ori_crop as \"Ori_crop\", \"Area_meters\" as \"Area_meter\", wkb_geometry {} from {} natural join {} where ctnum in ({})".format(
str_add_col_names, lpis_table, lut_table, str_ctnums)
else:
if args.filter_ids_table:
sql = SQL(
'select \"NewID\", ori_hold as \"Ori_hold\", ori_id as \"Ori_id\", ori_crop as \"Ori_crop\", \"Area_meters\" as \"Area_meter\", wkb_geometry {} from {} where \"NewID\" in (select newid from {})'
)
sql = sql.format(SQL(str_add_col_names),
Identifier(lpis_table),
Identifier(args.filter_ids_table))
else:
sql = SQL(
'select \"NewID\", ori_hold as \"Ori_hold\", ori_id as \"Ori_id\", ori_crop as \"Ori_crop\", \"Area_meters\" as \"Area_meter\", wkb_geometry {} from {}'
)
sql = sql.format(SQL(str_add_col_names),
Identifier(lpis_table))
sql = sql.as_string(conn)
command = []
command += ["ogr2ogr"]
command += ["-sql", sql]
command += [shp]
command += [pg_path]
command += ["-lco", "ENCODING=UTF-8"]
if args.srid:
command += ["-t_srs", "EPSG:" + str(args.srid)]
commands.append(command)
print("Starting executing the commands ...")
pool.map(lambda c: run_command(c), commands)
print("Finished executing commands!")
dataset = ogr.Open(shp, gdalconst.GA_Update)
layer = dataset.GetLayer()
feature_count = layer.GetFeatureCount()
print("{} feature(s) found".format(feature_count))
schema = []
ldefn = layer.GetLayerDefn()
print("Updating existing values (if needed) ...")
for n in range(ldefn.GetFieldCount()):
fdefn = ldefn.GetFieldDefn(n)
name = fdefn.GetName()
fieldTypeCode = fdefn.GetType()
fieldType = fdefn.GetFieldTypeName(fieldTypeCode)
print("Field name = {}, fieldTypeCode= {}, fieldType = {}".format(
name, fieldTypeCode, fieldType))
schema.append(fdefn.name)
print("Existing column names: ".format(schema))
for newField in newFields:
dataset.StartTransaction()
print("Adding new field {}, type {} with default value {}".format(
newField.name, newField.type, newField.defVal))
field_idx = layer.FindFieldIndex(newField.name, False)
if field_idx != -1:
if args.force:
print("Field {} already exists, removing it".format(
newField.name))
layer.DeleteField(field_idx)
else:
print("Field {} already exists, ignoring".format(
newField.name))
continue
layer.CreateField(ogr.FieldDefn(newField.name, newField.type))
dataset.CommitTransaction()
print("Setting default values ...")
dataset.StartTransaction()
for feature in layer:
for newField in newFields:
field_idx = layer.FindFieldIndex(newField.name, False)
feature.SetField(field_idx, newField.defVal)
layer.SetFeature(feature)
dataset.CommitTransaction()
print("Done!")
def add_comment():
if comment and self.is_owner:
cursor.execute(
SQL('COMMENT ON COLUMN {}.{} IS %s').format(
Identifier(self.table_name), Identifier(column_name)),
(comment, ))
def sql(self):
return SQL('CREATE INDEX {} ON {} USING GIST ({} gist_trgm_ops)').format(
Identifier(self.name),
Identifier(self.table.name),
Identifier(self.column_name)
), ()
mark INT,
review text
);"""
TABLE_answer_loyalty = """CREATE TABLE answer_loyalty (
id serial PRIMARY KEY,
user_id BIGINT references users(id),
answering_date DATE,
loyalty INT,
manager INT,
delivery INT,
cooking INT,
dietetics INT,
review text
);"""
with psycopg2.connect(
host=DB_HOST,
database=DB_NAME,
user=DB_USER,
password=DB_PASS,
) as conn:
with conn.cursor(cursor_factory=DictCursor) as cur:
cur.execute(SQL(TABLE_users))
cur.execute(SQL(TABLE_menu))
cur.execute(SQL(TABLE_dish))
cur.execute(SQL(TABLE_feedback))
cur.execute(SQL(TABLE_answer_loyalty))
def move_expired_personal_and_huddle_messages_to_archive(
realm: Realm,
chunk_size: int = MESSAGE_BATCH_SIZE,
) -> int:
message_retention_days = realm.message_retention_days
assert message_retention_days != -1
check_date = timezone_now() - timedelta(days=message_retention_days)
# This function will archive appropriate messages and their related objects.
internal_realm = get_realm(settings.SYSTEM_BOT_REALM)
cross_realm_bot_ids = [
get_user_including_cross_realm(email, internal_realm).id
for email in settings.CROSS_REALM_BOT_EMAILS
]
recipient_types = (Recipient.PERSONAL, Recipient.HUDDLE)
# Archive expired personal and huddle Messages in the realm, except cross-realm messages.
# The condition zerver_userprofile.realm_id = {realm_id} assures the row won't be
# a message sent by a cross-realm bot, because cross-realm bots have their own separate realm.
query = SQL("""
INSERT INTO zerver_archivedmessage ({dst_fields}, archive_transaction_id)
SELECT {src_fields}, {archive_transaction_id}
FROM zerver_message
INNER JOIN zerver_recipient ON zerver_recipient.id = zerver_message.recipient_id
INNER JOIN zerver_userprofile ON zerver_userprofile.id = zerver_message.sender_id
WHERE zerver_userprofile.realm_id = {realm_id}
AND zerver_recipient.type in {recipient_types}
AND zerver_message.date_sent < {check_date}
LIMIT {chunk_size}
ON CONFLICT (id) DO UPDATE SET archive_transaction_id = {archive_transaction_id}
RETURNING id
""")
message_count = run_archiving_in_chunks(
query,
type=ArchiveTransaction.RETENTION_POLICY_BASED,
realm=realm,
cross_realm_bot_ids=Literal(tuple(cross_realm_bot_ids)),
realm_id=Literal(realm.id),
recipient_types=Literal(recipient_types),
check_date=Literal(check_date.isoformat()),
chunk_size=chunk_size,
)
# Archive cross-realm personal messages to users in the realm. We
# don't archive cross-realm huddle messages via retention policy,
# as we don't support them as a feature in Zulip, and the query to
# find and delete them would be a lot of complexity and potential
# performance work for a case that doesn't actually happen.
query = SQL("""
INSERT INTO zerver_archivedmessage ({dst_fields}, archive_transaction_id)
SELECT {src_fields}, {archive_transaction_id}
FROM zerver_message
INNER JOIN zerver_userprofile recipient_profile ON recipient_profile.recipient_id = zerver_message.recipient_id
WHERE zerver_message.sender_id IN {cross_realm_bot_ids}
AND recipient_profile.realm_id = {realm_id}
AND zerver_message.date_sent < {check_date}
LIMIT {chunk_size}
ON CONFLICT (id) DO UPDATE SET archive_transaction_id = {archive_transaction_id}
RETURNING id
""")
message_count += run_archiving_in_chunks(
query,
type=ArchiveTransaction.RETENTION_POLICY_BASED,
realm=realm,
cross_realm_bot_ids=Literal(tuple(cross_realm_bot_ids)),
realm_id=Literal(realm.id),
check_date=Literal(check_date.isoformat()),
chunk_size=chunk_size,
)
return message_count
import contextlib
import csv
import lzma
import os
from importlib.resources import open_binary, open_text
from sys import stderr
from django.db import transaction
from django.db.transaction import get_connection
from psycopg2.sql import SQL, Identifier
from data_france.type_noms import TypeNom
COPY_SQL = SQL(
"""COPY {table} ({columns}) FROM STDIN WITH NULL AS '\\N' CSV QUOTE AS '"';"""
)
CREATE_TEMP_TABLE_SQL = SQL("""
CREATE TEMPORARY TABLE {temp_table} AS
SELECT {columns} FROM {reference_table} LIMIT 0;
""")
DROP_TEMPORARY_TABLE_SQL = SQL("""
DROP TABLE IF EXISTS {temp_table};
""")
COPY_FROM_TEMP_TABLE = SQL("""
INSERT INTO {table} ({all_columns})
SELECT {all_columns}
FROM {temp_table}
ON CONFLICT({id_column}) DO UPDATE SET {setters}
def get_constraints_sql(self):
other_table = self.source.other.Meta.table_name
return super().get_constraints_sql(
(SQL('REFERENCES {}(id)').format(Identifier(other_table)), ))
def do_aggregate_to_summary_table(stat: CountStat,
end_time: datetime,
realm: Optional[Realm] = None) -> None:
cursor = connection.cursor()
# Aggregate into RealmCount
output_table = stat.data_collector.output_table
if realm is not None:
realm_clause = SQL("AND zerver_realm.id = {}").format(Literal(
realm.id))
else:
realm_clause = SQL("")
if output_table in (UserCount, StreamCount):
realmcount_query = SQL("""
INSERT INTO analytics_realmcount
(realm_id, value, property, subgroup, end_time)
SELECT
zerver_realm.id, COALESCE(sum({output_table}.value), 0), %(property)s,
{output_table}.subgroup, %(end_time)s
FROM zerver_realm
JOIN {output_table}
ON
zerver_realm.id = {output_table}.realm_id
WHERE
{output_table}.property = %(property)s AND
{output_table}.end_time = %(end_time)s
{realm_clause}
GROUP BY zerver_realm.id, {output_table}.subgroup
""").format(
output_table=Identifier(output_table._meta.db_table),
realm_clause=realm_clause,
)
start = time.time()
cursor.execute(realmcount_query, {
'property': stat.property,
'end_time': end_time,
})
end = time.time()
logger.info(
"%s RealmCount aggregation (%dms/%sr)",
stat.property,
(end - start) * 1000,
cursor.rowcount,
)
if realm is None:
# Aggregate into InstallationCount. Only run if we just
# processed counts for all realms.
#
# TODO: Add support for updating installation data after
# changing an individual realm's values.
installationcount_query = SQL("""
INSERT INTO analytics_installationcount
(value, property, subgroup, end_time)
SELECT
sum(value), %(property)s, analytics_realmcount.subgroup, %(end_time)s
FROM analytics_realmcount
WHERE
property = %(property)s AND
end_time = %(end_time)s
GROUP BY analytics_realmcount.subgroup
""")
start = time.time()
cursor.execute(installationcount_query, {
'property': stat.property,
'end_time': end_time,
})
end = time.time()
logger.info(
"%s InstallationCount aggregation (%dms/%sr)",
stat.property,
(end - start) * 1000,
cursor.rowcount,
)
cursor.close()
def get_type_sql(self):
return SQL(self.type)
def _set_initial_parts(self):
self.select_parts.append(SQL('SELECT {pk}').format(pk=self.layer_wrapper.pk))
self.from_parts.append(SQL('FROM {table}').format(table=self.layer_wrapper.table))
def get_recent_private_conversations(user_profile: UserProfile) -> Dict[int, Dict[str, Any]]:
"""This function uses some carefully optimized SQL queries, designed
to use the UserMessage index on private_messages. It is
significantly complicated by the fact that for 1:1 private
messages, we store the message against a recipient_id of whichever
user was the recipient, and thus for 1:1 private messages sent
directly to us, we need to look up the other user from the
sender_id on those messages. You'll see that pattern repeated
both here and also in zerver/lib/events.py.
Ideally, we would write these queries using Django, but even
without the UNION ALL, that seems to not be possible, because the
equivalent Django syntax (for the first part of this query):
message_data = UserMessage.objects.select_related("message__recipient_id").filter(
user_profile=user_profile,
).extra(
where=[UserMessage.where_private()]
).order_by("-message_id")[:1000].values(
"message__recipient_id").annotate(last_message_id=Max("message_id"))
does not properly nest the GROUP BY (from .annotate) with the slicing.
We return a dictionary structure for convenient modification
below; this structure is converted into its final form by
post_process.
"""
RECENT_CONVERSATIONS_LIMIT = 1000
recipient_map = {}
my_recipient_id = user_profile.recipient_id
query = SQL('''
SELECT
subquery.recipient_id, MAX(subquery.message_id)
FROM (
(SELECT
um.message_id AS message_id,
m.recipient_id AS recipient_id
FROM
zerver_usermessage um
JOIN
zerver_message m
ON
um.message_id = m.id
WHERE
um.user_profile_id=%(user_profile_id)s AND
um.flags & 2048 <> 0 AND
m.recipient_id <> %(my_recipient_id)s
ORDER BY message_id DESC
LIMIT %(conversation_limit)s)
UNION ALL
(SELECT
m.id AS message_id,
sender_profile.recipient_id AS recipient_id
FROM
zerver_message m
JOIN
zerver_userprofile sender_profile
ON
m.sender_id = sender_profile.id
WHERE
m.recipient_id=%(my_recipient_id)s
ORDER BY message_id DESC
LIMIT %(conversation_limit)s)
) AS subquery
GROUP BY subquery.recipient_id
''')
with connection.cursor() as cursor:
cursor.execute(query, {
"user_profile_id": user_profile.id,
"conversation_limit": RECENT_CONVERSATIONS_LIMIT,
"my_recipient_id": my_recipient_id,
})
rows = cursor.fetchall()
# The resulting rows will be (recipient_id, max_message_id)
# objects for all parties we've had recent (group?) private
# message conversations with, including PMs with yourself (those
# will generate an empty list of user_ids).
for recipient_id, max_message_id in rows:
recipient_map[recipient_id] = dict(
max_message_id=max_message_id,
user_ids=list(),
)
# Now we need to map all the recipient_id objects to lists of user IDs
for (recipient_id, user_profile_id) in Subscription.objects.filter(
recipient_id__in=recipient_map.keys()).exclude(
user_profile_id=user_profile.id).values_list(
"recipient_id", "user_profile_id"):
recipient_map[recipient_id]['user_ids'].append(user_profile_id)
# Sort to prevent test flakes and client bugs.
for rec in recipient_map.values():
rec['user_ids'].sort()
return recipient_map
def query(self, startindex=0, limit=10, resulttype='results',
bbox=[], datetime_=None, properties=[], sortby=[],
select_properties=[], skip_geometry=False, q=None):
"""
Query Postgis for all the content.
e,g: http://localhost:5000/collections/hotosm_bdi_waterways/items?
limit=1&resulttype=results
:param startindex: starting record to return (default 0)
:param limit: number of records to return (default 10)
:param resulttype: return results or hit limit (default results)
:param bbox: bounding box [minx,miny,maxx,maxy]
:param datetime_: temporal (datestamp or extent)
:param properties: list of tuples (name, value)
:param sortby: list of dicts (property, order)
:param select_properties: list of property names
:param skip_geometry: bool of whether to skip geometry (default False)
:param q: full-text search term(s)
:returns: GeoJSON FeaturesCollection
"""
LOGGER.debug('Querying PostGIS')
if resulttype == 'hits':
with DatabaseConnection(self.conn_dic,
self.table, context="hits") as db:
cursor = db.conn.cursor(cursor_factory=RealDictCursor)
where_clause = self.__get_where_clauses(
properties=properties, bbox=bbox)
sql_query = SQL("SELECT COUNT(*) as hits from {} {}").\
format(Identifier(self.table), where_clause)
try:
cursor.execute(sql_query)
except Exception as err:
LOGGER.error('Error executing sql_query: {}: {}'.format(
sql_query.as_string(cursor), err))
raise ProviderQueryError()
hits = cursor.fetchone()["hits"]
return self.__response_feature_hits(hits)
end_index = startindex + limit
with DatabaseConnection(self.conn_dic, self.table) as db:
cursor = db.conn.cursor(cursor_factory=RealDictCursor)
where_clause = self.__get_where_clauses(
properties=properties, bbox=bbox)
sql_query = SQL("DECLARE \"geo_cursor\" CURSOR FOR \
SELECT DISTINCT {},ST_AsGeoJSON({}) FROM {}{}").\
format(db.columns,
Identifier(self.geom),
Identifier(self.table),
where_clause)
LOGGER.debug('SQL Query: {}'.format(sql_query.as_string(cursor)))
LOGGER.debug('Start Index: {}'.format(startindex))
LOGGER.debug('End Index: {}'.format(end_index))
try:
cursor.execute(sql_query)
for index in [startindex, limit]:
cursor.execute("fetch forward {} from geo_cursor"
.format(index))
except Exception as err:
LOGGER.error('Error executing sql_query: {}'.format(
sql_query.as_string(cursor)))
LOGGER.error(err)
raise ProviderQueryError()
row_data = cursor.fetchall()
feature_collection = {
'type': 'FeatureCollection',
'features': []
}
for rd in row_data:
feature_collection['features'].append(
self.__response_feature(rd))
return feature_collection
