def adapt_range(pgrange, pyrange):
if isinstance(pgrange, ustr):
pgrange = pgrange.encode("utf8")
if not isinstance(pyrange, range_):
raise ValueError((
"Trying to adapt range {range.__class__.__name__} which does not "
"extend base range type.").format(range=pyrange))
if not pyrange:
return AsIs((b"'empty'::" + pgrange).decode("utf8"))
lower = b"NULL"
if not pyrange.lower_inf:
lower = adapt(pyrange.lower).getquoted()
upper = b"NULL"
if not pyrange.upper_inf:
upper = adapt(pyrange.upper).getquoted()
return AsIs(b"".join([
pgrange,
b"(",
lower,
b", ",
upper,
b", '",
b"[" if pyrange.lower_inc else b"(",
b"]" if pyrange.upper_inc else b")",
b"')",
]).decode("utf8"))
def getquoted(self):
if self.name is None:
raise NotImplementedError(
'RangeAdapter must be subclassed overriding its name '
'or the getquoted() method')
r = self.adapted
if r.isempty:
return b"'empty'::" + self.name.encode('utf8')
if r.lower is not None:
a = adapt(r.lower)
if hasattr(a, 'prepare'):
a.prepare(self._conn)
lower = a.getquoted()
else:
lower = b'NULL'
if r.upper is not None:
a = adapt(r.upper)
if hasattr(a, 'prepare'):
a.prepare(self._conn)
upper = a.getquoted()
else:
upper = b'NULL'
return self.name.encode('utf8') + b'(' + lower + b', ' + upper \
+ b", '" + r._bounds.encode('utf8') + b"')"
def getquoted(self):
if self.name is None:
raise NotImplementedError(
"RangeAdapter must be subclassed overriding its name " "or the getquoted() method"
)
r = self.adapted
if r.isempty:
return b"'empty'::" + self.name.encode("utf8")
if r.lower is not None:
a = adapt(r.lower)
if hasattr(a, "prepare"):
a.prepare(self._conn)
lower = a.getquoted()
else:
lower = b"NULL"
if r.upper is not None:
a = adapt(r.upper)
if hasattr(a, "prepare"):
a.prepare(self._conn)
upper = a.getquoted()
else:
upper = b"NULL"
return self.name.encode("utf8") + b"(" + lower + b", " + upper + b", '" + r._bounds.encode("utf8") + b"')"
def getSingleTableSql(host, name, interval=None):
if interval==None:
interval = "AND tsd_timestamp > ('now'::timestamp - '8 days'::interval)"
else:
if 'interval' in interval:
interval = "AND tsd_timestamp > %s::interval" % (adapt(interval['interval']), )
else:
interval = "AND tsd_timestamp BETWEEN %s::timestamp and %s::timestamp" % (adapt(interval['from']),adapt(interval['to']), )
sql = """
SELECT tsd_table_id,
tsd_timestamp,
tsd_table_size,
tsd_index_size,
tsd_seq_scans,
tsd_index_scans,
tsd_tup_ins,
tsd_tup_upd,
tsd_tup_del,
tsd_tup_hot_upd
FROM monitor_data.table_size_data
WHERE tsd_table_id = ( SELECT t_id FROM monitor_data.tables WHERE t_schema || '.' || t_name = """ + str(adapt(name)) + """ AND t_host_id = """ + str(adapt(host)) + """ )
AND tsd_host_id = """ + str(adapt(host)) + """
"""+interval+"""
ORDER BY tsd_timestamp ASC
"""
return sql
def updateRequest(self, team_id, requestDict):
'''
Method to a update a request in missions_request table
@param team_id: team_id code e.g index in DB table of the team... as returned by rest api
@param requestDict: request dictiionary - keys have to be the same key of Db
'''
self.checkConnection()
# preare dictionary to be used for DB
requestOdered = self.prepareRequestDict(team_id, requestDict)
# create query
sqlquery = "UPDATE missions_request SET "
for k,v in requestOdered.items():
if k == "id":
continue
sqlquery += '%s=%s, ' % (k,adapt(v))
sqlquery = sqlquery[0:-2] + " "
sqlquery += "WHERE id=%s" % adapt(requestOdered["id"])
QgsLogger.debug(self.tr("Aggiorna request con la query: %s" % sqlquery), 1 )
try:
self.cursor.execute(sqlquery)
except Exception as ex:
raise(ex)
def updateTeam(self, teamDict):
'''
Method to a update a team in organization_team table
@param teamDict: team dictiionary - keys have to be the same key of Db
'''
self.checkConnection()
# preare dictionary to be used for DB
teamOdered = self.prepareTeamDict(teamDict)
# create query
sqlquery = "UPDATE organization_team SET "
for k,v in teamOdered.items():
if k == "id":
continue
sqlquery += '%s=%s, ' % (k,adapt(v))
sqlquery = sqlquery[0:-2] + " "
sqlquery += "WHERE id=%s" % adapt(teamOdered["id"])
QgsLogger.debug(self.tr("Aggiorna team con la query: %s" % sqlquery), 1 )
try:
self.cursor.execute(sqlquery)
except Exception as ex:
raise(ex)
def getquoted(self):
if self.name is None:
raise NotImplementedError(
"RangeAdapter must be subclassed overriding its name " "or the getquoted() method"
)
r = self.adapted
if r.isempty:
return b("'empty'::" + self.name)
if r.lower is not None:
a = adapt(r.lower)
if hasattr(a, "prepare"):
a.prepare(self._conn)
lower = a.getquoted()
else:
lower = b("NULL")
if r.upper is not None:
a = adapt(r.upper)
if hasattr(a, "prepare"):
a.prepare(self._conn)
upper = a.getquoted()
else:
upper = b("NULL")
return b(self.name + "(") + lower + b(", ") + upper + b(", '%s')" % r._bounds)
def add_command(self,command):
TAG = 'add_command'
if isinstance(command, dict):
if command.get(C.SAMSUNG_COMMANDS_TABLE_ACTION) and \
command.get(C.SAMSUNG_COMMANDS_TABLE_DEVICE) \
and command.get(C.SAMSUNG_COMMANDS_TABLE_UUID):
if not command[C.SAMSUNG_COMMANDS_TABLE_ATTRIBUTE]:
command[C.SAMSUNG_COMMANDS_TABLE_ATTRIBUTE]= "{}"
self.cursor.execute(""" insert into samsung_commands
(device_id, action,attribute,command_uuid)
values( %s,%s,%s,%s) returning id""",
[command[C.SAMSUNG_COMMANDS_TABLE_DEVICE],
command[C.SAMSUNG_COMMANDS_TABLE_ACTION],
adapt(command[C.SAMSUNG_COMMANDS_TABLE_ATTRIBUTE]),
adapt(command[C.SAMSUNG_COMMANDS_TABLE_UUID])])
if self.cursor.rowcount>0:
row = self.cursor.fetchone();
return row[0]
else:
return None
else:
self.log.i(TAG, 'Not all required parameters sent in '+command.__repr__())
return None
def adapt_range(pgrange, pyrange):
if not isinstance(pyrange, range_):
raise ValueError((
"Trying to adapt range {range.__class__.__name__} which does not "
"extend base range type.").format(range=pyrange))
if not pyrange:
return AsIs("'empty'::" + pgrange)
lower = b("NULL")
if not pyrange.lower_inf:
lower = adapt(pyrange.lower).getquoted()
upper = b("NULL")
if not pyrange.upper_inf:
upper = adapt(pyrange.upper).getquoted()
return AsIs(b"".join([
b(pgrange),
b("("),
lower,
b(", "),
upper,
b(", '"),
b("[" if pyrange.lower_inc else "("),
b("]" if pyrange.upper_inc else ")"),
b("')")
]).decode("utf8"))
def execute(self, operation, parameters=None):
if self.closed():
raise errors.Error('Cursor is closed')
if parameters:
# optional requirement
from psycopg2.extensions import adapt
if isinstance(parameters, dict):
for key in parameters:
v = adapt(parameters[key]).getquoted()
operation = operation.replace(':' + key, v)
elif isinstance(parameters, tuple):
operation = operation % tuple(adapt(p).getquoted() for p in parameters)
else:
raise errors.Error("Argument 'parameters' must be dict or tuple")
self.rowcount = 0
self.buffered_rows = collections.deque()
self.last_execution = operation
self.connection.write(messages.Query(operation))
self.fetch_rows()
if self.error is not None:
raise self.error
def add_device(self, device):
TAG = 'add_device'
## if user_id, udid and os is present then only we can do entry
if type(device) == dict and C.DEVICE_TABLE_USER in device:
if UserDBHelper().is_user_valid(str(device.get('user_id'))):
if device.has_key(C.DEVICE_TABLE_OS):
if device.has_key(C.DEVICE_TABLE_UDID):
try:
self.cursor.execute("""INSERT INTO devices
(os, udid, user_id) VALUES({0}, {1}, {2})
RETURNING id""".format(adapt(device.get('os')),
adapt(device.get('udid')),
adapt(device.get('user_id'))))
except Exception,err:
self.log.e(TAG,'Exception : ' + repr(err))
return None
if self.cursor.rowcount > 0:
row = self.cursor.fetchone()
return row[0]
else:
self.log.e(TAG,'Not abe to insert in device table')
return None
else:
self.log.e(TAG,'UDID not found in dictionary')
return None
else:
self.log.e(TAG, 'OS is not found in dictionary')
return None
else:
self.log.e(TAG, 'USER ID not found in dictionary')
return None
def topologyInsert(con, simulationId, atomNum, atomName, atomType, residueCode, residueNumber, chainCode, description):
sql_insert_topology="INSERT INTO dnamodel.topology (simulationId, atomNum, atomName, atomType, residueNum, residueCode, chainCode, description) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING topologyId;"
cur = con.cursor()
cur.execute(sql_insert_topology, (simulationId, atomNum, adapt(atomName), adapt(atomType), residueNumber, adapt(residueCode), adapt(chainCode), adapt(description)))
topologyId = cur.fetchone()[0]
cur.close()
return topologyId
def adapt_box(box):
return AsIs("'(%s,%s),(%s,%s)'::box" % (
adapt(box.init_point.x),
adapt(box.init_point.y),
adapt(box.end_point.x),
adapt(box.end_point.y)
))
def adapt_lseg(l):
return AsIs("'[(%s,%s), (%s,%s)]'::lseg" % (\
adapt(l.init_point.x),
adapt(l.init_point.y),
adapt(l.end_point.x),
adapt(l.end_point.y)
))
def adapt_box(box):
return AsIs("'(%s,%s),(%s,%s)'::box" % (
adapt(box.first_vertex.x),
adapt(box.first_vertex.y),
adapt(box.second_vertex.x),
adapt(box.second_vertex.y)
))
def test_adapt_subtype(self):
from psycopg2.extensions import adapt
class Sub(str):
pass
s1 = "hel'lo"
s2 = Sub(s1)
self.assertEqual(adapt(s1).getquoted(), adapt(s2).getquoted())
def getquoted(self):
r = []
for k in self.attnames:
if self.adapted.has_key(k):
r.append(adapt(self.adapted[k]))
else:
r.append(None)
return adapt(r)
def prepare_select_processes(server_url, identifier):
return (
"SELECT id FROM " + tables["processes"] + " "
"WHERE wps_server_id IN ("
"SELECT id FROM " + tables["wpsServers"] + " "
"WHERE url = " + str(adapt(server_url)) + " "
")"
"AND identifier = " + str(adapt(identifier))
)
def on_get(self, req, resp):
'''Return json object related to GET with lat & lon HTTP parameters
'''
raw_lat = req.get_param('lat')
raw_lon = req.get_param('lon')
if raw_lat is None or raw_lon is None:
resp.status = falcon.HTTP_400
lat = None
lon = None
else:
lat = decimal.Decimal(raw_lat)
lon = decimal.Decimal(raw_lon)
if not (self.lat_is_valid(lat) and self.lon_is_valid(lon)):
resp.status = falcon.HTTP_400
else:
dbc = db.connect()
cur = dbc.cursor()
nb_maps_raw = req.get_param('nb_maps')
nb_addr_raw = req.get_param('nb_addr')
if nb_maps_raw is None:
nb_maps_raw = '20'
if nb_addr_raw is None:
nb_addr_raw = '10'
nb_maps = decimal.Decimal(nb_maps_raw)
nb_addr = decimal.Decimal(nb_addr_raw)
#id (uuid), path (str), geom (geom), address (str), level (str), building (str)
loc = "st_setsrid(st_makepoint(%s,%s),4326)" % (lon,lat)
query = """SELECT array_to_json(array_agg(row_to_json(t)))::text FROM (
SELECT floor(st_distance(geom::geography, %s::geography)) as dist,
ST_X(geom) as lon, ST_Y(geom) as lat, * FROM (
(select * from ban_nomap where ST_DWithin(geom, %s, 0.001) order by st_distance(geom,%s) limit %s)
union
(select * from map_info where ST_DWithin(geom, %s, 0.1) limit %s)) as d ORDER BY ST_Distance(geom,%s), level) t
""" % (loc, loc,loc,adapt(nb_addr),loc,adapt(nb_maps),loc)
cur.execute(query)
what_is_around = cur.fetchone()[0]
resp.set_header('X-Powered-By', 'OpenEvacMap')
if what_is_around is None:
resp.status = falcon.HTTP_204
else:
resp.status = falcon.HTTP_200
resp.set_header('Access-Control-Allow-Origin', '*')
resp.set_header('Access-Control-Allow-Headers', 'X-Requested-With')
resp.body = (what_is_around)
query = """INSERT INTO log (loc, ip) VALUES (ST_SetSRID(ST_GeometryFromText('POINT(%s %s)'),4326),'%s');""" % (lon,lat,req.env['REMOTE_ADDR'])
cur.execute(query)
dbc.commit()
cur.close()
dbc.close()
def snmpResult2RRD2(host):
connection = psycopg2.connect(
host='10.0.10.111', port=54321, database='GIS_2010',
user='******', password='******'
)
cursor = connection.cursor()
dOid_to_Res = dict()
for i, o in enumerate(host.oids):
dOid_to_Res[o] = host.results[i]
data = {}
for oid, results in dOid_to_Res.items():
for port, result in results.items():
if data.get(port, None):
data[port][oid.name] = int(result)
else:
data[port] = { oid.name: int(result) }
for port, values in data.items():
insert = "INSERT INTO netstats.traf (%s) VALUES (%s)"
query = insert % (
', '.join([ '"%s"' % col for col in
['dev_name', 'port'] \
+ values.keys() \
+ ['inserted',]
]),
', '.join(
[adapt(host.hostname).getquoted(), str(port)] \
+ [str(adapt(v)) for v in values.values()] \
# + ['now()',]
+ ["'%s'" % datetime.now(),]
)
)
cursor.execute(query)
# print query
connection.commit()
cursor.close()
connection.close()
rrdbase = '%s/%s' % (host.hostdir, host.perfbase)
updstr = 'N:%s:%s' % (host.snmptime, host.errors)
#sys.stderr.write('%s, %s\n' % (rrdbase, updstr))
#sys.stderr.flush()
try:
rrdtool.update(rrdbase, updstr)
except Exception, why:
print "Error in update (%s, %s) - %s" % (
host.hostname,
index,
why )
def _getquoted_9(self):
"""Use the hstore(text[], text[]) function."""
if not self.wrapped:
return b"''::hstore"
k = _ext.adapt(self.wrapped.keys())
k.prepare(self.conn)
v = _ext.adapt(self.wrapped.values())
v.prepare(self.conn)
return b"hstore(" + k.getquoted() + b", " + v.getquoted() + b")"
def _getquoted_9(self):
"""Use the hstore(text[], text[]) function."""
if not self.wrapped:
return b("''::hstore")
k = _ext.adapt(self.wrapped.keys())
k.prepare(self.conn)
v = _ext.adapt([unicode(x) if x is not None else None for x in self.wrapped.values()])
v.prepare(self.conn)
return b("hstore(") + k.getquoted() + b(", ") + v.getquoted() + b(")")
def getSizeTrendSQL(host_id=None, days='8'):
days += 'days'
host_id = str(adapt(host_id)) if host_id else 'NULL'
sql = """
SELECT
tsda_timestamp AS tsd_timestamp,
tsda_host_id AS tsd_host_id,
tsda_db_size AS size,
tsda_tup_ins AS s_ins,
tsda_tup_upd AS s_upd,
tsda_tup_del AS s_del
FROM
monitor_data.table_size_data_agg
WHERE
tsda_timestamp > now() - """ + str(adapt(days)) + """::interval
AND tsda_timestamp < now() - '2 hours'::interval
AND (""" + host_id + """ IS NULL OR tsda_host_id = """ + host_id + """)
UNION ALL
SELECT
*
FROM
( SELECT
tsd_timestamp,
tsd_host_id,
SUM(tsd_table_size)+SUM(tsd_index_size) AS size,
SUM(tsd_tup_ins) AS s_ins,
SUM(tsd_tup_upd) AS s_upd,
SUM(tsd_tup_del) AS s_del
FROM
monitor_data.table_size_data
WHERE
tsd_timestamp >= now() - '2 hours'::interval
AND (""" + host_id + """ IS NULL OR tsd_host_id = """ + host_id + """)
GROUP BY
tsd_host_id, tsd_timestamp
ORDER BY
tsd_host_id, tsd_timestamp
) a
"""
if not tplE._settings.get('run_aggregations'):
sql = """SELECT
tsd_timestamp,
tsd_host_id,
( SUM(tsd_table_size)+SUM(tsd_index_size) ) AS size,
SUM(tsd_tup_ins) AS s_ins,
SUM(tsd_tup_upd) AS s_upd,
SUM(tsd_tup_del) AS s_del
FROM monitor_data.table_size_data
WHERE tsd_timestamp > now() - """ + str(adapt(days)) + """::interval
AND (""" + host_id + """ IS NULL OR tsd_host_id = """ + host_id + """)
GROUP BY tsd_host_id, tsd_timestamp
ORDER BY tsd_host_id, tsd_timestamp
"""
return sql
def create_recovery_conf(dirpath, site, *,
port=PGHOARD_PORT,
primary_conninfo=None,
recovery_end_command=None,
recovery_target_action=None,
recovery_target_name=None,
recovery_target_time=None,
recovery_target_xid=None,
restore_to_master=None):
restore_command = [
"pghoard_postgres_command",
"--mode", "restore",
"--port", str(port),
"--site", site,
"--output", "%p",
"--xlog", "%f",
]
lines = [
"# pghoard created recovery.conf",
"recovery_target_timeline = 'latest'",
"trigger_file = {}".format(adapt(os.path.join(dirpath, "trigger_file"))),
"restore_command = '{}'".format(" ".join(restore_command)),
]
if not restore_to_master:
lines.append("standby_mode = 'on'")
if primary_conninfo:
lines.append("primary_conninfo = {}".format(adapt(primary_conninfo)))
if recovery_end_command:
lines.append("recovery_end_command = {}".format(adapt(recovery_end_command)))
if recovery_target_action:
with open(os.path.join(dirpath, "PG_VERSION"), "r") as fp:
pg_version = fp.read().strip()
if LooseVersion(pg_version) >= "9.5":
lines.append("recovery_target_action = '{}'".format(recovery_target_action))
elif recovery_target_action == "promote":
pass # default action
elif recovery_target_action == "pause":
lines.append("pause_at_recovery_target = True")
else:
print("Unsupported recovery_target_action {!r} for PostgreSQL {}, ignoring".format(
recovery_target_action, pg_version))
if recovery_target_name:
lines.append("recovery_target_name = '{}'".format(recovery_target_name))
if recovery_target_time:
lines.append("recovery_target_time = '{}'".format(recovery_target_time))
if recovery_target_xid:
lines.append("recovery_target_xid = '{}'".format(recovery_target_xid))
content = "\n".join(lines) + "\n"
filepath = os.path.join(dirpath, "recovery.conf")
filepath_tmp = filepath + ".tmp"
with open(filepath_tmp, "w") as fp:
fp.write(content)
os.rename(filepath_tmp, filepath)
return content
def update_login_password(self, login_id, password):
TAG = 'update_login_password'
try:
self.cursor.execute("""UPDATE logins set password={0}
WHERE id={1};""".format(adapt(password),
adapt(login_id)))
except Exception, err:
self.log.e(TAG, 'Exception: '+ repr(err))
return False
def getBlockedProcessesCounts(hostId, days='8'):
ret = []
days += 'days'
sql = """
with
q_wait_startpoints as (
select
date_trunc('hour'::text, query_start) + floor(date_part('minute'::text, query_start) / 15::double precision) * '00:15:00'::interval as wait_start_timestamp,
count(1) as wait_starts
from
(
select
query_start,
query,
min(bp_timestamp) as wait_start,
max(bp_timestamp) as wait_end,
count(1)
from
monitor_data.blocking_processes
where
bp_host_id = """ + str(adapt(hostId)) + """
and bp_timestamp > now() - """ + str(adapt(days)) + """::interval
and waiting
group by
1, 2
order by
1, 2
) a
where
wait_end - wait_start >= '5 seconds'::interval
group by
date_trunc('hour'::text, query_start) + floor(date_part('minute'::text, query_start) / 15::double precision) * '00:15:00'::interval
),
q_timeline as (
select * from (select generate_series(current_date - """ + str(adapt(days)) + """::interval, now(), '00:15:00'::interval) AS ts) a
where ts > now() - """ + str(adapt(days)) + """::interval
)
SELECT
date_trunc('hour'::text, q_timeline.ts) + floor(date_part('minute'::text, q_timeline.ts) / 15::double precision) * '00:15:00'::interval AS ts,
coalesce(wait_starts, 0) as count
FROM
q_timeline
left join
q_wait_startpoints on q_wait_startpoints.wait_start_timestamp = q_timeline.ts
ORDER BY
1 ASC
"""
for record in datadb.execute(sql):
ret.append( (record['ts'] , record['count'] ) )
return ret
def insert(uid, nickname, password):
verify = is_present("uid", 'user_db', uid)
if verify['boolean'] == False:
query_string = """INSERT INTO user_db (uid, nickname, password) \
VALUES ({0}, {1}, {2})""".format(adapt(uid), adapt(nickname), adapt(password))
verify["cursor"].execute(query_string);
end(verify["conn"])
return jsonify({"message":"new user created"})
else:
end(verify["conn"])
return jsonify({"message":"uid already exists"}), 404
def create_recovery_conf(dirpath, site, primary_conninfo):
content = """# pghoard created recovery.conf
standby_mode = 'on'
primary_conninfo = {primary_conninfo}
trigger_file = {trigger_file}
restore_command = 'pghoard_restore get %f %p --site {site}'
recovery_target_timeline = 'latest'
""".format(primary_conninfo=adapt(primary_conninfo),
trigger_file=adapt(os.path.join(dirpath, "trigger_file")),
site=site)
filepath = os.path.join(dirpath, "recovery.conf")
open(filepath, "w").write(content)
def execute(self, operation, parameters=None):
if self.closed():
raise errors.Error("Cursor is closed")
self.flush_to_query_ready()
if parameters:
# # optional requirement
from psycopg2.extensions import adapt
if isinstance(parameters, dict):
for key in parameters:
param = parameters[key]
# Make sure adapt() behaves properly
if isinstance(param, str):
v = adapt(param.encode("utf8")).getquoted()
else:
v = adapt(param).getquoted()
# Using a regex with word boundary to correctly handle params with similar names
# such as :s and :start
match_str = u":%s\\b" % str(key)
operation = re.sub(match_str, v.decode("utf-8"), operation, flags=re.UNICODE)
elif isinstance(parameters, tuple):
tlist = []
for p in parameters:
if isinstance(p, str):
tlist.append(adapt(p.encode("utf8")).getquoted())
else:
tlist.append(adapt(p).getquoted())
operation = operation % tuple(tlist)
else:
raise errors.Error("Argument 'parameters' must be dict or tuple")
self.rowcount = -1
self.connection.write(messages.Query(operation))
# read messages until we hit an Error, DataRow or ReadyForQuery
while True:
message = self.connection.read_message()
# save the message because there's no way to undo the read
self._message = message
if isinstance(message, messages.ErrorResponse):
raise errors.QueryError.from_error_response(message, operation)
elif isinstance(message, messages.RowDescription):
self.description = list(map(lambda fd: Column(fd, self.unicode_error), message.fields))
elif isinstance(message, messages.DataRow):
break
elif isinstance(message, messages.ReadyForQuery):
break
else:
self.connection.process_message(message)
def test_mro_required(self):
import psycopg2
from psycopg2.extensions import adapt, register_adapter, AsIs
# Intentionally old-style, they don't expose their MRO.
class A:
pass
class B(A):
pass
register_adapter(A, lambda a: AsIs("a"))
with self.assertRaises(psycopg2.ProgrammingError):
adapt(B())
geoname = ast.literal_eval(result)
geonames = geoname['geonames'][0]
countryName = geonames[u'countryName']
adminName1 = geonames[u'adminName1']
adminName2 = geonames[u'adminName2']
adminName3 = geonames[u'adminName3']
near = geonames[u'toponymName']
distance = geonames[u'distance']
population = geonames[u'population']
tz = geonames[u'timezone']
countrycode = geonames[u'countryCode']
continentcode = geonames[u'continentCode']
print countryName, adminName1, adminName2, adminName3, near, distance, population, tz, countrycode, continentcode
cat_src = 'glc'
cmd = "UPDATE planet_osm_point SET (countryname, adminname1, adminname2, adminname3, near, distance, population, cat_src, cat_id, tz, countrycode, continentcode ) = (%s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s, %s ) WHERE id= %d;" % \
(adapt(countryName), adapt(adminName1), adapt(adminName2), adapt(adminName3), adapt(near), distance, population, adapt(cat_src), rid, adapt(json.dumps(tz)), adapt(countrycode), adapt(continentcode), rid)
print cmd
cursor.execute(cmd)
time.sleep(2)
count += 1
#if count == 2:
# break
connection.commit()
cursor.close()
connection.close()
sys.exit(0)
def sqlesc(value):
adapted = adapt(value.encode('utf-8'))
if hasattr(adapted, 'getquoted'):
adapted = adapted.getquoted()
return adapted
def adapt_macaddr(maddr):
from psycopg2.extensions import adapt, AsIs
return AsIs("{0}::macaddr".format(adapt(str(maddr))))
def _adapt_datetime(dt):
return AsIs("%s" % adapt(DateTime.iso8601(dt)))
def as_sql(self, qn, connection):
t = ",".join(str(adapt(x)) for x in self.tags)
return 'ARRAY[%s] <@ "%s".%s' % (t, self.table, qn("tags")), []
def as_sql(self, compiler, connection):
tags = ",".join(
str(adapt(str(x).strip())) for x in self.rhs if str(x).strip())
return "(ARRAY[%s] <@ %s)" % (
tags, self.lhs.as_sql(compiler, connection)[0]), []
def create_recovery_conf(dirpath,
site,
*,
port=PGHOARD_PORT,
primary_conninfo=None,
recovery_end_command=None,
recovery_target_action=None,
recovery_target_name=None,
recovery_target_time=None,
recovery_target_xid=None,
restore_to_master=None):
restore_command = [
"pghoard_postgres_command",
"--mode",
"restore",
"--port",
str(port),
"--site",
site,
"--output",
"%p",
"--xlog",
"%f",
]
with open(os.path.join(dirpath, "PG_VERSION"), "r") as fp:
pg_version = LooseVersion(fp.read().strip())
if pg_version >= "12":
trigger_file_setting = "promote_trigger_file"
else:
trigger_file_setting = "trigger_file"
lines = [
"# pghoard created recovery.conf",
"recovery_target_timeline = 'latest'",
"{} = {}".format(trigger_file_setting,
adapt(os.path.join(dirpath, "trigger_file"))),
"restore_command = '{}'".format(" ".join(restore_command)),
]
use_recovery_conf = (pg_version < "12"
) # no more recovery.conf in PG >= 12
if not restore_to_master:
if use_recovery_conf:
lines.append("standby_mode = 'on'")
else:
# Indicates the server should start up as a hot standby
create_signal_file(os.path.join(dirpath, "standby.signal"))
if primary_conninfo:
lines.append("primary_conninfo = {}".format(adapt(primary_conninfo)))
if recovery_end_command:
lines.append("recovery_end_command = {}".format(
adapt(recovery_end_command)))
if recovery_target_action:
if pg_version >= "9.5":
lines.append(
"recovery_target_action = '{}'".format(recovery_target_action))
elif recovery_target_action == "promote":
pass # default action
elif recovery_target_action == "pause":
lines.append("pause_at_recovery_target = True")
else:
print(
"Unsupported recovery_target_action {!r} for PostgreSQL {}, ignoring"
.format(recovery_target_action, pg_version))
if recovery_target_name:
lines.append(
"recovery_target_name = '{}'".format(recovery_target_name))
if recovery_target_time:
lines.append(
"recovery_target_time = '{}'".format(recovery_target_time))
if recovery_target_xid:
lines.append("recovery_target_xid = '{}'".format(recovery_target_xid))
content = "\n".join(lines) + "\n"
if use_recovery_conf:
# Overwrite the recovery.conf
open_mode = "w"
filepath = os.path.join(dirpath, "recovery.conf")
else:
# Append to an existing postgresql.auto.conf (PG >= 12)
open_mode = "a"
lines.insert(
0, "\n") # in case postgresql.conf does not end with a newline
filepath = os.path.join(dirpath, "postgresql.auto.conf")
filepath_tmp = filepath + ".tmp"
with open(filepath_tmp, open_mode) as fp:
fp.write(content)
os.rename(filepath_tmp, filepath)
return content
def adapt_money(m):
return AsIs('(%s,%s)::currency_amount' %
(adapt(m.amount), adapt(m.currency)))
def make_query_string(params):
s = []
for k, v in params.items():
s.append("%s = %s" % (k, adapt(v)))
return " and ".join(s)
def truncate(self):
cursor = connections[self.database].cursor()
if self.verbosity:
logger.info("Emptying database plan tables...")
starttime = time()
if self.cluster == -1:
# Complete export for the complete model
cursor.execute("truncate table out_problem, out_resourceplan, out_constraint")
cursor.execute('''
update operationplan
set owner_id = null
where owner_id is not null
and exists (
select 1
from operationplan op2
where op2.reference = operationplan.owner_id
and (op2.status is null or op2.status = 'proposed')
)
''')
cursor.execute('''
delete from operationplanmaterial
using operationplan
where operationplanmaterial.operationplan_id = operationplan.reference
and (
operationplan.status='proposed'
or operationplan.status is null
or operationplanmaterial.status = 'proposed'
or operationplanmaterial.status is null
)
''')
cursor.execute('''
delete from operationplanresource
using operationplan
where operationplanresource.operationplan_id = operationplan.reference
and (
(operationplan.status='proposed' or operationplan.status is null)
or operationplan.type = 'STCK'
or operationplanresource.status = 'proposed'
or operationplanresource.status is null
)
''')
cursor.execute('''
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK'
''')
else:
# Partial export for a single cluster
cursor.execute('create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))')
for i in frepple.items():
if i.cluster == self.cluster:
cursor.execute(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute("delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)")
cursor.execute('''
delete from operationplanmaterial
using cluster_keys
where operationplan_id in (
select reference from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
union
select reference from operationplan where owner_id in (
select reference from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
)
and operationplanmaterial.status = 'proposed'
''')
cursor.execute('''
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
)
''')
cursor.execute('''
delete from out_problem
where entity = 'material'
and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id)
''')
cursor.execute('''
delete from operationplanresource
where operationplan_id in (
select reference
from operationplan
inner join cluster_keys on cluster_keys.name = operationplan.item_id
where status = 'proposed' or status is null or oplan_parent.type='STCK'
union
select reference from operationplan where owner_id in (
select reference
from operationplan parent_opplan
inner join cluster_keys on cluster_keys.name = parent_opplan.item_id
)
where status = 'proposed' or status is null
)
and operationplanresource.status = 'proposed'
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where owner_id in (
select oplan_parent.reference
from operationplan as oplan_parent
where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK')
and oplan_parent.item_id = cluster_keys.name
)
''')
cursor.execute('''
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name
''')
# TODO next subqueries are not efficient - the exists condition triggers a sequential scan
cursor.execute("delete from out_constraint where exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_constraint.demand like forecast.name || ' - %')")
cursor.execute("delete from out_problem where entity = 'demand' and exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_problem.owner like forecast.name || ' - %')")
cursor.execute("truncate table cluster_keys")
for i in frepple.resources():
if i.cluster == self.cluster:
cursor.execute(("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute("delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)")
cursor.execute('delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name')
cursor.execute('delete from out_resourceplan using cluster_keys where resource = cluster_keys.name')
cursor.execute("delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name")
cursor.execute('truncate table cluster_keys')
for i in frepple.operations():
if i.cluster == self.cluster:
cursor.execute(("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode(self.encoding)))
cursor.execute("delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name")
cursor.execute("delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.name = cluster_keys.name")
cursor.execute("drop table cluster_keys")
if self.verbosity:
logger.info("Emptied plan tables in %.2f seconds" % (time() - starttime))
def __conform__(self, protocol):
return AsIs(
"('fernet',%s,%s)::encrypted" % (adapt(self.payload), adapt(self.ts))
)
def adapt_proxy(proxy):
return adapt(proxy._get_current_object())
def test_encoding_error(self):
from psycopg2.extensions import adapt
snowman = "\u2603"
a = adapt(snowman)
self.assertRaises(UnicodeEncodeError, a.getquoted)
def test_encoding_default(self):
from psycopg2.extensions import adapt
a = adapt("hello")
self.assertEqual(a.encoding, 'latin1')
self.assertEqual(a.getquoted(), b"'hello'")
def quote(x):
return adapt(x).getquoted()
def Q(self):
"""
Returns Q object which can be applied to
ManagedObject.objects.filter
"""
# Exclude NOC internal objects
q = ~Q(profile__in=list(Profile.objects.filter(name__startswith="NOC.")))
# Exclude objects being wiped
q &= ~Q(name__startswith="wiping-")
# Filter by is_managed
if self.filter_managed is not None:
q &= Q(is_managed=self.filter_managed)
# Filter by ID
if self.filter_id:
q &= Q(id=self.filter_id)
# Filter by pool
if self.filter_pool:
q &= Q(pool=self.filter_pool)
# Filter by name (regex)
if self.filter_name:
q &= Q(name__regex=self.filter_name)
# Filter by profile
if self.filter_profile:
q &= Q(profile=self.filter_profile)
# Filter by vendor
if self.filter_vendor:
q &= Q(vendor=self.filter_vendor)
# Filter by platform
if self.filter_platform:
q &= Q(platform=self.filter_platform)
# Filter by version
if self.filter_version:
q &= Q(version=self.filter_version)
# Filter by ttsystem
if self.filter_tt_system:
q &= Q(tt_system=self.filter_tt_system)
# Filter by object's profile
if self.filter_object_profile:
q &= Q(object_profile=self.filter_object_profile)
# Filter by address (regex)
if self.filter_address:
q &= Q(address__regex=self.filter_address)
# Filter by prefix table
if self.filter_prefix:
q &= SQL(
"""
EXISTS (
SELECT * FROM main_prefixtableprefix p
WHERE table_id=%d
AND address::inet <<= p.prefix)"""
% self.filter_prefix.id
)
# Filter by administrative domain
if self.filter_administrative_domain:
dl = AdministrativeDomain.get_nested_ids(self.filter_administrative_domain)
q &= SQL(
"""
"sa_managedobject"."administrative_domain_id" IN (%s)
"""
% ", ".join(str(x) for x in dl)
)
# Filter by VRF
if self.filter_vrf:
q &= Q(vrf=self.filter_vrf)
# Filter by VC domain
if self.filter_vc_domain:
q &= Q(vc_domain=self.filter_vc_domain)
# Filter by termination group
if self.filter_service_group:
q &= Q(effective_service_groups=self.filter_service_group.id)
# Filter by termination group
if self.filter_client_group:
q &= Q(effective_client_groups=self.filter_client_group.id)
# Filter by username
if self.filter_user:
q &= Q(user__regex=self.filter_user)
# Filter by remote path
if self.filter_remote_path:
q &= Q(remote_path__regex=self.filter_remote_path)
# Filter by description
if self.filter_description:
q &= Q(description__regex=self.filter_description)
# Restrict to tags when necessary
if self.filter_tags:
q &= Q(tags__contains=self.filter_tags)
# Restrict to attributes when necessary
for s in self.managedobjectselectorbyattribute_set.all():
q &= SQL(
"""
("sa_managedobject"."id" IN (
SELECT managed_object_id
FROM sa_managedobjectattribute
WHERE
key ~ %s
AND value ~ %s
))
"""
% (adapt(s.key_re).getquoted(), adapt(s.value_re).getquoted())
)
# Restrict to sources
if self.sources.count():
if self.source_combine_method == "A":
# AND
for s in self.sources.all():
q &= s.Q
else:
# OR
ql = list(self.sources.all())
q = ql.pop(0).Q
for qo in ql:
q |= qo.Q
return q
def adapt_proxy(proxy):
"""Get current object and try to adapt it again."""
return adapt(proxy._get_current_object())
def getquoted(self):
res = "'(%s,%s,%s)'::sgas_memory" % (adapt(
self.amount * self.unit), self.metric, self.type)
return res
k int4, d date, t time, dt timestamp, z interval)""")
except:
conn.rollback()
curs.execute("DROP TABLE test_dt")
curs.execute("""CREATE TABLE test_dt (
k int4, d date, t time, dt timestamp, z interval)""")
conn.commit()
# build and insert some data using mx.DateTime
mx1 = (1, mx.DateTime.Date(2004, 10, 19), mx.DateTime.Time(0, 11, 17.015),
mx.DateTime.Timestamp(2004, 10, 19, 0, 11, 17.5),
mx.DateTime.DateTimeDelta(13, 15, 17, 59.9))
from psycopg2.extensions import adapt
import psycopg2.extras
print adapt(mx1)
print "Inserting mx.DateTime values..."
curs.execute("INSERT INTO test_dt VALUES (%s, %s, %s, %s, %s)", mx1)
# build and insert some values using the datetime adapters
dt1 = (2, datetime.date(2004, 10, 19), datetime.time(0, 11, 17, 15000),
datetime.datetime(2004, 10, 19, 0, 11, 17, 500000),
datetime.timedelta(13, 15 * 3600 + 17 * 60 + 59, 900000))
print "Inserting Python datetime values..."
curs.execute("INSERT INTO test_dt VALUES (%s, %s, %s, %s, %s)", dt1)
# now extract the row from database and print them
print "Extracting values inserted with mx.DateTime wrappers:"
curs.execute("SELECT d, t, dt, z FROM test_dt WHERE k = 1")
def adapt_cube(cube):
""" Function required by Postgres for inserting/searching cube extension colors """
return AsIs("'(%s, %s, %s)'" %
(adapt(cube.red), adapt(cube.green), adapt(cube.blue)))
def __init__(self, fields, table_name):
self.table_name = table_name
self.fields = [adapt(field) for field in fields]
def adapt(self, value):
"""Adapt any value into SQL safe string"""
return adapt(value)
def adapt_point(point):
lat = adapt(point.lat).getquoted()
lng = adapt(point.lng).getquoted()
return AsIs("'(%s, %s)'" % (lat, lng))
def moneyvalue_adapter(value):
return AsIs("(%s,%s)::money_value" %
(adapt(value.amount), adapt(value.currency.code)))
p2 = copy.deepcopy(p1)
p2['type'] = 'json'
pprint(p2)
cur.execute("SELECT test_json(%s)", [p2])
pprint(cur.fetchone()[0])
p3 = {
'type': 'hstore',
'key1': 'val1',
'key2': 'val2',
'key3': None,
#'key4': 123,
#'key5': [1,2,3]
}
pprint(p3)
print cur.mogrify("SELECT test_hstore(%s)", [p3])
cur.execute("SELECT test_hstore(%s)", [p3])
pprint(cur.fetchone()[0])
try:
print adapt({}).getquoted()
except Exception, e:
print e
try:
print adapt({'type': 'nonexistent'}).getquoted()
except Exception, e:
print e
def run(cls, cluster=-1, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
cursor = connections[database].cursor()
if cluster == -1:
# Complete export for the complete model
cursor.execute(
"truncate table out_problem, out_resourceplan, out_constraint")
cursor.execute("""
update operationplan
set owner_id = null
where owner_id is not null
and exists (
select 1
from operationplan op2
where op2.reference = operationplan.owner_id
and (op2.status is null or op2.status = 'proposed')
)
""")
cursor.execute("""
truncate operationplanmaterial, operationplanresource
""")
cursor.execute("""
delete from operationplan
where (status='proposed' or status is null) or type = 'STCK'
""")
else:
# Partial export for a single cluster
cursor.execute(
"create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))"
)
for i in frepple.items():
if i.cluster == cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s);\n" %
adapt(i.name).getquoted().decode("UTF8")))
cursor.execute("""
delete from out_constraint
where demand in (
select demand.name
from demand
inner join cluster_keys
on cluster_keys.name = demand.item_id
)
""")
cursor.execute("""
delete from out_problem
where entity = 'demand' and owner in (
select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id
)
""")
cursor.execute("""
delete from out_problem
where entity = 'material'
and owner in (
select buffer.item_id || ' @ ' || buffer.location_id
from buffer
inner join cluster_keys on cluster_keys.name = buffer.item_id
)
""")
cursor.execute("""
delete from operationplan
using cluster_keys
where owner_id in (
select oplan_parent.reference
from operationplan as oplan_parent
where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK')
and oplan_parent.item_id = cluster_keys.name
)
""")
cursor.execute("""
delete from operationplan
using cluster_keys
where (status='proposed' or status is null or type='STCK')
and item_id = cluster_keys.name
""")
cursor.execute("truncate table cluster_keys")
for i in frepple.resources():
if i.cluster == cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode("UTF8")))
cursor.execute("""
delete from out_problem
where entity = 'demand'
and owner in (
select demand.name
from demand
inner join cluster_keys on cluster_keys.name = demand.item_id
)
""")
cursor.execute(
"delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name"
)
cursor.execute(
"delete from out_resourceplan using cluster_keys where resource = cluster_keys.name"
)
cursor.execute(
"delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name"
)
cursor.execute("truncate table cluster_keys")
for i in frepple.operations():
if i.cluster == cluster:
cursor.execute(
("insert into cluster_keys (name) values (%s)" %
adapt(i.name).getquoted().decode("UTF8")))
cursor.execute(""""
delete from out_problem
using cluster_keys
where entity = 'operation' and owner = cluster_keys.name
""")
cursor.execute("""
delete from operationplan
using cluster_keys
where (status='proposed' or status is null)
and operationplan.name = cluster_keys.name
""")
cursor.execute("drop table cluster_keys")
def adapt_set(s):
return adapt(tuple(s))
def __adapt_entity(entity):
return AsIs(
"ROW(%s, %s, %s)::entity_sync" %
(adapt(entity.type), adapt(entity.local_id), adapt(entity.remote_id)))
def quoted(o):
a = adapt(o)
if hasattr(a, 'prepare'):
a.prepare(self.conn)
return a.getquoted().decode('utf-8')
if not (check):
sql = """UPDATE "Exten" SET "DND" = %s where ("BIND" = %s) and ("Exten" = %s);""" % (
adapt(enable), adapt(BIND), adapt(cid))
# syslog.syslog(sql)
try:
cur.execute(sql)
connect.commit()
res = cur.rowcount
cur.close()
return (res)
except psycopg2.Error, e:
syslog.syslog("SQL ERROR: " + str(e.pgerror))
else:
sql = """SELECT "DND" FROM "Exten" where ("BIND" = %s) and ("Exten" = %s);""" % (
adapt(BIND), adapt(cid))
try:
cur.execute(sql)
if (cur.rowcount < 1):
return -1
row = cur.fetchone()
syslog.syslog("%s" % row['DND'])
cur.close()
if row['DND']:
return 1
else:
return 0
except psycopg2.Error, e:
syslog.syslog("SQL ERROR: " + str(e.pgerror))
