def test_threaded_connections(self):
with pg_simple.PgSimple() as db:
self._drop_tables(db)
self._create_tables(db, fill=True)
threads = []
# Create new threads
for i in range(20):
t = PgSimpleThread(i, 'thread-' + str(i), i, self)
threads.append(t)
# Start new Threads
for t in threads:
t.start()
# Wait for all threads to complete
for t in threads:
t.join()
# Drop tables
with pg_simple.PgSimple() as db:
self._drop_tables(db)
print "Exiting Main Thread \n"
def database_operations(self):
with pg_simple.PgSimple() as db:
self.test_cls._check_table(db, 'pg_t1')
self.test_cls._truncate_tables(db)
self.test_cls._populate_tables(db)
time.sleep(1)
def query():
""" Load all users """
with pg_simple.PgSimple() as db:
users = db.fetchall('users',
fields=['id', 'name'],
order=['id', 'ASC'])
return {"content": users}
def load(id):
""" Load user from id """
with pg_simple.PgSimple() as db:
user = db.fetchone('users',
fields=['id', 'name'],
where=('id = %s', [id]))
return user
def login():
bcrypt = Bcrypt(current_app)
if request.method == 'GET':
return render_template('login.html')
# Get username from form
username = request.form['username']
# Get authentication details
with pg_simple.PgSimple() as db:
auths = db.fetchone(userTable,
fields=['password', 'api'],
where=('name = %s', [username]))
try: # Check password salt
if bcrypt.check_password_hash(auths.password,
request.form['password']):
session['user'] = auths.api # API key in session
session['username'] = username # username in session
return redirect(url_for('stats'))
except (ValueError, TypeError, AttributeError) as e:
# Non-existent username
pass
messages("401")
return redirect(url_for('authentication.login'))
def test_connection_auto_commit(self):
import code
import sys
with pg_simple.PgSimple() as db:
if sys.argv.count('--interact'):
db.log = sys.stdout
code.interact(local=locals())
else:
self._drop_tables(db)
self._create_tables(db, fill=True)
with pg_simple.PgSimple() as db:
try:
self._check_table(db, 'pg_t1')
finally:
self._drop_tables(db)
def check_granule_identifier(self, granule_identifier):
with pg_simple.PgSimple() as db:
collection = db.fetchone(self.schema + '.product',
fields=['"eoIdentifier"'],
where=('"eoIdentifier" = %s', [granule_identifier]))
if collection == None:
return False
else:
return True
def wrapper(*args):
arg = args[0] # Get the Record object since wrapper acquires a tuple
# Authentication: check if username exists and correct API key is
# provided
providedKey = arg.key
username = arg.username
try:
with pg_simple.PgSimple() as db:
key = db.fetchone(
'users', fields=['api'], where=('name = %s', [username])
)[0][
0] # delete the last zero once 24-char API keys are implemented
latest = db.fetchone('data',
fields=['datetime'],
where=('api = %s', [key]),
order=['datetime', 'DESC'])[0]
if providedKey != key:
return 401
except TypeError:
return 401
# Parse datetime or completely reject request if incorrect (because
# where would you place it on a graph?)
try:
d = datetime.strptime(arg.timestamp, '%Y-%m-%d,%H:%M:%S')
if not isinstance(d, datetime):
return 400
if d - latest < timedelta(minutes=15):
return 403
except (ValueError, TypeError):
return 400
# The speed data validation
# 0.0 values in case of an error will still indicate connection
# and point out a client-side problem with speedtest visually on a graph
# These ones are separate so in case one breaks the rest is still saved
try:
arg.download = float(re.findall('([0-9]+\.[0-9]+)', \
arg.download)[0])
except:
arg.download = 0.0
try:
arg.upload = float(re.findall('([0-9]+\.[0-9]+)', arg.upload)[0])
except:
arg.upload = 0.0
try:
arg.ping = float(re.findall('([0-9]+\.[0-9]+)', arg.ping)[0])
except:
arg.ping = 0.0
return func(arg)
def persist_product_search_params(self, dict_to_persist, collection_name):
with pg_simple.PgSimple() as db:
collection = db.fetchone(self.schema + '.collection',
fields=['"eoIdentifier"'],
where=('"eoIdentifier" = %s', [collection_name]))
if collection is None:
raise LookupError("ERROR: No related collection found!")
dict_to_persist['"eoParentIdentifier"'] = collection.eoIdentifier
row = db.insert(self.schema + '.product', data=dict_to_persist, returning='id')
db.commit()
return row.id
def saveToDatabase(record):
with pg_simple.PgSimple() as db:
db.insert(
"data", {
"datetime": record.timestamp,
"download": record.download,
"upload": record.upload,
"ping": record.ping,
"api": record.key,
"ip": record.ip,
"provider": record.provider
})
db.commit()
return 201
def execute_query(self,
query: str,
return_value: bool = True,
params: list = None) -> list:
logging.info(query)
try:
with pg_simple.PgSimple(self.connection_pool) as _db:
execute = _db.execute(query, params)
_db.commit()
if return_value:
return execute.fetchall()
except Exception as error:
logging.error(query)
logging.exception(error)
return []
def get_product_OGC_BBOX(self, granule_identifier):
with pg_simple.PgSimple(nt_cursor=False) as db:
result = db.execute('SELECT ST_Extent(footprint::geometry) as ext FROM ' + self.schema + '.product WHERE "eoIdentifier" = %s', [granule_identifier])
bbox = result.fetchone()[0]
bbox = bbox[4:-1]
bbox = bbox.replace(",", " ")
bbox = bbox.split(" ", -1)
if float(bbox[0]) > float(bbox[2]):
tmp = bbox[0]
bbox[0] = bbox[2]
bbox[2] = tmp
if float(bbox[1]) > float(bbox[3]):
tmp = bbox[1]
bbox[1] = bbox[3]
bbox[3] = tmp
return bbox
def test_basic_functions(self):
import code
import doctest
import sys
db = pg_simple.PgSimple()
if sys.argv.count('--interact'):
db.log = sys.stdout
code.interact(local=locals())
else:
try:
# Setup tables
self._drop_tables(db)
self._create_tables(db, fill=True)
# Run tests
doctest.testmod(optionflags=doctest.ELLIPSIS)
finally:
# Drop tables
self._drop_tables(db)
self.assertEqual(True, True)
def passwordChange():
bcrypt = Bcrypt(current_app)
if request.method == 'POST': # means someone wants to change password:
with pg_simple.PgSimple() as db:
# Fetch current password
auth = db.fetchone(userTable,
fields=['password'],
where=('name = %s', [session['username']]))
# If correct, generate hash for new one and update DB
if bcrypt.check_password_hash(auth.password,
request.form['oldPassword']):
np = bcrypt.generate_password_hash(request.form['newPassword'])
db.update(userTable,
data={'password': np},
where=('name = %s', [session['username']]))
db.commit()
messages('200')
else: # Invalid old password (hashes mismatch)
messages('401')
return redirect(url_for('userPanel'))
def get_products_id(self):
with pg_simple.PgSimple() as db:
id_list = db.fetchall(self.schema + '.product', fields=['"id"', '"eoIdentifier"'])
return id_list
import pg_simple
from config import host, port, database
pool = pg_simple.config_pool(host=host,
port=port,
database=database,
user="******",
password="******")
# logging in uses it's own initialization of PgSimple, this one is left for
# `stats`. `login` page needs to reestablish connection after logging out, but
# `stats` gets it only once and it doesn't get cleared in the meantime. `login`
# one does though, because it gets the session cleared?
db = pg_simple.PgSimple()
# insert into data (datetime, download, upload, ping, api, ip, provider) values ('2017-09-20 22:50:40', 31.29, 2.78, 22.371, '3', '109.173.147.181', 'INEA');
def query(host, start_time, end_time):
query, bind_params = buildquery(host, start_time, end_time)
start = time.time()
with pg_simple.PgSimple(CONNECTION_POOL) as db:
db.execute(query, bind_params)
return time.time() - start
def persist_collection(self, dict_to_persist):
with pg_simple.PgSimple() as db:
row = db.insert(self.schema + '.collection', data=dict_to_persist)
db.commit()
def persist_product_metadata(self, xml_doc, id):
with pg_simple.PgSimple() as db:
db.insert(self.schema + '.product_metadata', data={'metadata':xml_doc, 'mid':id})
db.commit()
def persist_thumb(self, thumb_blob, id):
with pg_simple.PgSimple() as db:
db.insert(self.schema + '.product_thumb', data={'thumb':Binary(thumb_blob), 'tid':id})
db.commit()
import pg_simple
import sys
import datetime
connection_pool = pg_simple.config_pool(
database='wms',
host='localhost',
port='54321',
user='******',
password='******',
expiration=120) # idle timeout = 120 seconds
db = pg_simple.PgSimple(connection_pool,
log=sys.stdout,
log_fmt=lambda x: '>> %s' %
(x if isinstance(x, str) else x.query),
nt_cursor=True)
# dropping and creating tables
db.drop('books')
db.create(
'books', '''
"id" SERIAL NOT NULL,
"genre" VARCHAR(20) NOT NULL,
"name" VARCHAR(40) NOT NULL,
"price" MONEY NOT NULL,
"published" DATE NOT NULL,
"modified" TIMESTAMP(6) NOT NULL DEFAULT now()
''')
def persist_ogc_links(self, list, id):
with pg_simple.PgSimple() as db:
for dict in list:
dict['product_id'] = id
db.insert(self.schema + '.product_ogclink', data=dict)
db.commit()
def update_original_package_location(self, safe_pkg_name, tile_id):
with pg_simple.PgSimple() as db:
db.update(self.schema + '.product',
data={'"originalPackageLocation"':wf.original_package_location_path + safe_pkg_name.replace("SAFE","zip")},
where=('"eoIdentifier" = %s', [tile_id]))
db.commit()
def db():
pg_simple.PgSimple._connect = lambda x: 0
return pg_simple.PgSimple(pool=None)
