class dbgetter:
def __init__(self, dbchoice=None):
self.db = DAL('sqlite://storage.db', folder=path.join('../database'))
self.hasdb = (not dbchoice == None)
self.mydb = 0
if self.hasdb:
if dbchoice == 'trenddates' or dbchoice == 'trends':
self.mydb = self.db.define_table('trenddates',
Field('trend', 'text'),
Field('date', 'date'))
elif dbchoice == 'words':
self.mydb = self.db.define_table('words',
Field('word', 'string'),
Field('freq', 'integer'),
Field('dates', 'list:string'))
else:
raise NameError(
"Your options from dbgetter are 'trends' or 'words'.")
else:
raise NameError(
"You must provide a database choice from dbgetter ('trends' or 'words')."
)
def getInfo(self, data):
toRet = []
dbdata = self.db(self.mydb).select().as_list()
for d in dbdata:
toRet.append(d.get(data))
return toRet
def getTable(self):
return self.db(self.mydb).select()
def close(self):
self.db.close()
def testRun(self):
db = DAL(DEFAULT_URI, check_reserved=['all'])
dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245',
None)
self.assertEqual(dt.microsecond, 223245)
self.assertEqual(dt.hour, 12)
dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245Z',
None)
self.assertEqual(dt.microsecond, 223245)
self.assertEqual(dt.hour, 12)
dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245-2:0',
None)
self.assertEqual(dt.microsecond, 223245)
self.assertEqual(dt.hour, 10)
dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36+1:0', None)
self.assertEqual(dt.microsecond, 0)
self.assertEqual(dt.hour, 13)
dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.123', None)
self.assertEqual(dt.microsecond, 123000)
dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.00123',
None)
self.assertEqual(dt.microsecond, 1230)
dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.1234567890',
None)
self.assertEqual(dt.microsecond, 123456)
db.close()
def testRun(self):
db = DAL(DEFAULT_URI, check_reserved=['all'])
dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245', None)
self.assertEqual(dt.microsecond, 223245)
self.assertEqual(dt.hour, 12)
dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245Z', None)
self.assertEqual(dt.microsecond, 223245)
self.assertEqual(dt.hour, 12)
dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245-2:0', None)
self.assertEqual(dt.microsecond, 223245)
self.assertEqual(dt.hour, 10)
dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36+1:0', None)
self.assertEqual(dt.microsecond, 0)
self.assertEqual(dt.hour, 13)
dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.123', None)
self.assertEqual(dt.microsecond, 123000)
dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.00123', None)
self.assertEqual(dt.microsecond, 1230)
dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.1234567890', None)
self.assertEqual(dt.microsecond, 123456)
db.close()
class trenddb:
def __init__(self):
self.db = DAL('sqlite://storage.db', folder=path.join("../database"))
try:
self.db.define_table('trenddates', Field('trend', 'text'),
Field('date', 'date'))
except:
print("Trenddates exists...")
if len(self.db(self.db.trenddates).select().as_list()) > 0:
print("It has entries, somehow.")
def add(self, trends, trenddate):
if trends is None:
return
f = open('runningTrends.txt', 'a')
d = date(int(trenddate[:4]), int(trenddate[5:7]), int(trenddate[8:]))
for t in trends:
self.db.trenddates.insert(trend=t, date=d)
f.write(str(d) + ": " + t)
f.write("\n")
f.close()
def addParsed(self, trends, trenddate):
d = date(int(trenddate[:4]), int(trenddate[5:7]), int(trenddate[8:]))
for t in trends:
self.db.trenddates.insert(trend=t, date=d)
def end(self):
self.db.close()
def update_database(self):
db = DAL('sqlite://courses.db', folder='dbs')
try:
response = requests.get(
'http://localhost:5000/api/v1.0/courses/all/2000')
response.raise_for_status()
jsonResponse = response.json()
db.define_table('courses', Field('class_id', type='integer'),
Field('class_name'), Field('date_time'),
Field('descriptive_link'), Field('enrolled'),
Field('instructor'), Field('link_sources'),
Field('location'), Field('status'))
for key in jsonResponse:
db.courses.insert(class_id=key['class_id'],
class_name=key['class_name'],
date_time=key['date_time'],
descriptive_link=key['descriptive_link'],
enrolled=key['enrolled'],
instructor=key['instructor'],
link_sources=key['link_sources'],
location=key['location'],
status=key['status'])
rows = db().select(db.courses.ALL)
finally:
if db:
db.close()
def testRun(self):
db = DAL(DEFAULT_URI, check_reserved=['all'])
#: skip for adapters that use drivers for datetime parsing
if db._adapter.parser.registered.get('datetime') is None:
return
parse = lambda v: db._adapter.parser.parse(v, 'datetime', 'datetime')
dt = parse('2015-09-04t12:33:36.223245')
self.assertEqual(dt.microsecond, 223245)
self.assertEqual(dt.hour, 12)
dt = parse('2015-09-04t12:33:36.223245Z')
self.assertEqual(dt.microsecond, 223245)
self.assertEqual(dt.hour, 12)
dt = parse('2015-09-04t12:33:36.223245-2:0')
self.assertEqual(dt.microsecond, 223245)
self.assertEqual(dt.hour, 10)
dt = parse('2015-09-04t12:33:36+1:0')
self.assertEqual(dt.microsecond, 0)
self.assertEqual(dt.hour, 13)
dt = parse('2015-09-04t12:33:36.123')
self.assertEqual(dt.microsecond, 123000)
dt = parse('2015-09-04t12:33:36.00123')
self.assertEqual(dt.microsecond, 1230)
dt = parse('2015-09-04t12:33:36.1234567890')
self.assertEqual(dt.microsecond, 123456)
db.close()
def testRun(self):
for ref, bigint in [("reference", False), ("big-reference", True)]:
db = DAL(DEFAULT_URI, check_reserved=["all"], bigint_id=bigint)
if bigint and "big-id" not in db._adapter.types:
continue
db.define_table("tt", Field("vv"))
db.define_table(
"ttt",
Field("vv"),
Field("tt_id", "%s tt" % ref, unique=True),
Field("tt_uq", "integer", unique=True),
)
id_1 = db.tt.insert(vv="pydal")
id_2 = db.tt.insert(vv="pydal")
# Null tt_id
db.ttt.insert(vv="pydal", tt_uq=1)
# first insert is OK
db.ttt.insert(tt_id=id_1, tt_uq=2)
self.assertRaises(Exception, db.ttt.insert, tt_id=id_1, tt_uq=3)
self.assertRaises(Exception, db.ttt.insert, tt_id=id_2, tt_uq=2)
# The following is mandatory for backends as PG to close the aborted transaction
db.commit()
drop(db.ttt)
drop(db.tt)
db.close()
def testRun(self):
db = DAL(DEFAULT_URI, check_reserved=["all"])
db.define_table("tt", Field("vv"))
vv = "ἀγοραζε"
id_i = db.tt.insert(vv=vv)
row = db(db.tt.id == id_i).select().first()
self.assertEqual(row.vv, vv)
db.commit()
drop(db.tt)
db.close()
def testRun(self):
db = DAL(DEFAULT_URI, check_reserved=['all'])
db.define_table('tt', Field('vv'))
db.define_table('ttt', Field('vv'), Field('tt_id', 'reference tt', notnull=True))
self.assertRaises(Exception, db.ttt.insert, vv='pydal')
# The following is mandatory for backends as PG to close the aborted transaction
db.commit()
drop(db.ttt)
drop(db.tt)
db.close()
def testRun(self):
db = DAL(DEFAULT_URI, check_reserved=['all'])
db.define_table('tt', Field('vv'))
vv = 'ἀγοραζε'
id_i = db.tt.insert(vv=vv)
row = db(db.tt.id == id_i).select().first()
self.assertEqual(row.vv, vv)
db.commit()
drop(db.tt)
db.close()
def testRun(self):
for ref, bigint in [('reference', False), ('big-reference', True)]:
db = DAL(DEFAULT_URI, check_reserved=['all'], bigint_id=bigint)
db.define_table('tt', Field('vv'))
db.define_table('ttt', Field('vv'),
Field('tt_id', '%s tt' % ref, notnull=True))
self.assertRaises(Exception, db.ttt.insert, vv='pydal')
# The following is mandatory for backends as PG to close the aborted transaction
db.commit()
drop(db.ttt)
drop(db.tt)
db.close()
def testRun(self):
for ref, bigint in [("reference", False), ("big-reference", True)]:
db = DAL(DEFAULT_URI, check_reserved=["all"], bigint_id=bigint)
if bigint and "big-id" not in db._adapter.types:
continue
db.define_table("tt", Field("vv"))
db.define_table("ttt", Field("vv"),
Field("tt_id", "%s tt" % ref, notnull=True))
self.assertRaises(Exception, db.ttt.insert, vv="pydal")
# The following is mandatory for backends as PG to close the aborted transaction
db.commit()
drop(db.ttt)
drop(db.tt)
db.close()
def testRun(self):
for ref, bigint in [('reference', False), ('big-reference', True)]:
db = DAL(DEFAULT_URI, check_reserved=['all'], bigint_id=bigint)
if bigint and 'big-id' not in db._adapter.types:
continue
db.define_table('tt', Field('vv'))
db.define_table('ttt', Field('vv'), Field('tt_id', '%s tt' % ref,
notnull=True))
self.assertRaises(Exception, db.ttt.insert, vv='pydal')
# The following is mandatory for backends as PG to close the aborted transaction
db.commit()
drop(db.ttt)
drop(db.tt)
db.close()
def testRun(self):
for ref, bigint in [('reference', False), ('big-reference', True)]:
db = DAL(DEFAULT_URI, check_reserved=['all'], bigint_id=bigint)
db.define_table('tt', Field('vv'))
db.define_table('ttt', Field('vv'), Field('tt_id', '%s tt' % ref,
unique=True))
id_i = db.tt.insert(vv='pydal')
# Null tt_id
db.ttt.insert(vv='pydal')
# first insert is OK
db.ttt.insert(tt_id=id_i)
self.assertRaises(Exception, db.ttt.insert, tt_id=id_i)
# The following is mandatory for backends as PG to close the aborted transaction
db.commit()
drop(db.ttt)
drop(db.tt)
db.close()
def index():
from pydal import DAL, Field
dbConnect = DAL('mysql://*****:*****@localhost/web2py2')
try:
dbConnect.define_table(
'list_event',
Field('event_name', type='text'),
Field('dia_diem', type='text'),
Field('thoi_gian', type='date'),
)
# dbConnect.list_event.insert(event_name='Phao Hoa', dia_diem='Cau rong', thoi_gian='2018/05/06')
# dbConnect.list_event.insert(event_name='Cau phun lua', dia_diem='Cau rong', thoi_gian='2018/05/07')
# dbConnect.list_event.insert(event_name='Duong pho', dia_diem='Hai Chau', thoi_gian='2018/05/06')
getAllEvents = dbConnect().select(dbConnect.list_event.ALL)
finally:
if dbConnect:
dbConnect.close()
return dict(list=getAllEvents)
def testRun(self):
for ref, bigint in [('reference', False), ('big-reference', True)]:
db = DAL(DEFAULT_URI, check_reserved=['all'], bigint_id=bigint)
if bigint and 'big-id' not in db._adapter.types:
continue
db.define_table('tt', Field('vv'))
db.define_table('ttt', Field('vv'), Field('tt_id', '%s tt' % ref,
unique=True,
notnull=True))
self.assertRaises(Exception, db.ttt.insert, vv='pydal')
db.commit()
id_i = db.tt.insert(vv='pydal')
# first insert is OK
db.ttt.insert(tt_id=id_i)
self.assertRaises(Exception, db.ttt.insert, tt_id=id_i)
# The following is mandatory for backends as PG to close the aborted transaction
db.commit()
drop(db.ttt)
drop(db.tt)
db.close()
def sqlDump(self, filename):
try:
rmtree("./sqldump")
os.mkdir("./sqldump")
except OSError as e:
if e.errno != errno.ENOENT:
raise
else:
os.mkdir("./sqldump")
db = DAL("sqlite://" + filename, folder="sqldump")
for klass in (
Server,
ExternalEntity,
Dataflow,
Datastore,
Actor,
Process,
SetOfProcesses,
Boundary,
TM,
Threat,
Lambda,
Data,
Finding,
):
self.get_table(db, klass)
for e in TM._threats + TM._data + TM._elements + self.findings + [
self
]:
table = self.get_table(db, e.__class__)
row = {}
for k, v in serialize(e).items():
if k == "id":
k = "SID"
row[k] = ", ".join(v) if isinstance(v, list) else v
db[table].bulk_insert([row])
db.close()
class Db:
def __init__(self):
self.uri = 'sqlite://database.sqlite'
self.folder = os.path.join(current_dir, "sqlite")
self.db = None
def connect(self):
if not self.db:
self.db = DAL(self.uri, folder=self.folder, pool_size=5, lazy_tables=False)
#, migrate_enabled=False, migrate=False, lazy_tables=True
self.tables()
else:
print("Error: db already open")
def close(self):
self.db.close()
self.db = None
def tables(self):
self.tableUsers()
def tableUsers(self):
try:
#Note: id is created automattically if omited
self.db.define_table('users',
Field('username', type='string'),
Field('email', type='string'),
Field('password', type='string'),
Field('uid', type='integer', default=0),
Field('userRole', type='string', defaul='user'),#user, admin
Field('secureKey', type='string', default='0', writable=False, readable=False),#secet key token - should be used if in server side
Field('dateRegistration', type='datetime', writable=False, readable=False)
)
except:
print('models db.DB() tableUsers Error')
def tableLogs(self):
pass
def insertUser(self, username, email, password, dateRegistration):
self.connect()
import secrets#generate url safe token
secureKey = secrets.token_urlsafe()
self.db.users.insert(username=username, email=email, password=password, uid=0, userRole="user", secureKey=secureKey, dateRegistration=dateRegistration)
self.db.commit()
self.db.close()
def loginUser(self, username, password):
self.connect()
self.db._adapter.reconnect()#gives thread error without
user = self.db( (self.db.users.username == username) & (self.db.users.password == password) ).select().first()
self.db.close()
#if no user, it will return user = None
return user
def testRun(self):
db = DAL(DEFAULT_URI, check_reserved=['all'])
db.define_table('aa',Field('name'))
db.define_table('bb',Field('aa','reference aa'),Field('name'))
for k in ('x','y','z'):
i = db.aa.insert(name=k)
for j in ('u','v','w'):
db.bb.insert(aa=i,name=k+j)
db.commit()
rows = db(db.aa).select()
rows.join(db.bb.aa, fields=[db.bb.name], orderby=[db.bb.name])
self.assertEqual(rows[0].bb[0].name, 'xu')
self.assertEqual(rows[0].bb[1].name, 'xv')
self.assertEqual(rows[0].bb[2].name, 'xw')
self.assertEqual(rows[1].bb[0].name, 'yu')
self.assertEqual(rows[1].bb[1].name, 'yv')
self.assertEqual(rows[1].bb[2].name, 'yw')
self.assertEqual(rows[2].bb[0].name, 'zu')
self.assertEqual(rows[2].bb[1].name, 'zv')
self.assertEqual(rows[2].bb[2].name, 'zw')
rows = db(db.bb).select()
rows.join(db.aa.id, fields=[db.aa.name])
self.assertEqual(rows[0].aa.name, 'x')
self.assertEqual(rows[1].aa.name, 'x')
self.assertEqual(rows[2].aa.name, 'x')
self.assertEqual(rows[3].aa.name, 'y')
self.assertEqual(rows[4].aa.name, 'y')
self.assertEqual(rows[5].aa.name, 'y')
self.assertEqual(rows[6].aa.name, 'z')
self.assertEqual(rows[7].aa.name, 'z')
self.assertEqual(rows[8].aa.name, 'z')
rows_json = rows.as_json()
drop(db.bb)
drop(db.aa)
db.close()
def testRun(self):
db = DAL(DEFAULT_URI, check_reserved=["all"])
db.define_table("aa", Field("name"))
db.define_table("bb", Field("aa", "reference aa"), Field("name"))
for k in ("x", "y", "z"):
i = db.aa.insert(name=k)
for j in ("u", "v", "w"):
db.bb.insert(aa=i, name=k + j)
db.commit()
rows = db(db.aa).select()
rows.join(db.bb.aa, fields=[db.bb.name], orderby=[db.bb.name])
self.assertEqual(rows[0].bb[0].name, "xu")
self.assertEqual(rows[0].bb[1].name, "xv")
self.assertEqual(rows[0].bb[2].name, "xw")
self.assertEqual(rows[1].bb[0].name, "yu")
self.assertEqual(rows[1].bb[1].name, "yv")
self.assertEqual(rows[1].bb[2].name, "yw")
self.assertEqual(rows[2].bb[0].name, "zu")
self.assertEqual(rows[2].bb[1].name, "zv")
self.assertEqual(rows[2].bb[2].name, "zw")
rows = db(db.bb).select()
rows.join(db.aa.id, fields=[db.aa.name])
self.assertEqual(rows[0].aa.name, "x")
self.assertEqual(rows[1].aa.name, "x")
self.assertEqual(rows[2].aa.name, "x")
self.assertEqual(rows[3].aa.name, "y")
self.assertEqual(rows[4].aa.name, "y")
self.assertEqual(rows[5].aa.name, "y")
self.assertEqual(rows[6].aa.name, "z")
self.assertEqual(rows[7].aa.name, "z")
self.assertEqual(rows[8].aa.name, "z")
rows_json = rows.as_json()
drop(db.bb)
drop(db.aa)
db.close()
def testRun(self):
db = DAL(None)
db.define_table("no_table", Field("aa"))
self.assertIsInstance(db.no_table.aa, Field)
self.assertIsInstance(db.no_table["aa"], Field)
db.close()
class DataBase:
def __init__(self, dbname='postgres', schema='Acervo', pool_size=5):
load_dotenv()
self.dbname = dbname
self.schema = schema
self.username = os.getenv("DBUSERNAME")
self.password = os.getenv("DBPASS")
self.host = os.getenv("DBHOST")
self.port = os.getenv("DBPORT")
self.folder = 'Resources' + os.sep + 'database'
self.dbinfo = \
'postgres://' + str(self.username) + ':' + str(self.password) + '@' \
+ str(self.host) + ':' + str(self.port) + '/' + str(self.dbname)
self.db = DAL(
self.dbinfo,
folder=self.folder,
pool_size=pool_size,
migrate=False,
attempts=1
)
self.connection = None
def execute_sql(self, sql, as_dict=True):
retorno = list()
try:
retorno = self.db.executesql(query=sql, as_dict=as_dict)
self.db.commit()
logging.debug('[DataBase] status=' + str(True))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] retorno=' + str(retorno))
prc = True, retorno, str(self.db._lastsql)
except Exception as e:
self.db.rollback()
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception=' + str(e))
retorno.append(e)
prc = False, retorno, str(sql)
except:
e = 'Exceção não tratada'
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception2=' + str(e))
retorno.append(e)
prc = False, e, str(sql)
return prc
def __conectar_banco__(self):
try:
self.connection = self.db.__call__()
except Exception as e:
logging.debug('[DataBase] ' + str(e))
return self
def definir_schema(self, schema):
self.schema = schema
self.execute_sql("SET search_path TO " + self.schema, as_dict=False)
def fechar_conexao(self):
self.db.close()
def testRun(self):
db = DAL(None)
db.define_table('no_table', Field('aa'))
self.assertIsInstance(db.no_table.aa, Field)
self.assertIsInstance(db.no_table['aa'], Field)
db.close()
def testRun(self):
db = DAL(None)
db.define_table('no_table', Field('aa'))
self.assertIsInstance(db.no_table.aa, Field)
self.assertIsInstance(db.no_table['aa'], Field)
db.close()
class DBClient:
log = customlogger(globalconfig.logging_level)
def __init__(self, uri_db):
self.log.info("creating instance of DBClient: {0}".format(uri_db))
self.db = DAL(uri_db, migrate_enabled=False)
self.wells = self.db.define_table('wells',
Field('uuid'),
Field('project_uuid'),
Field('well_name'),
Field('uwi'),
Field('created_at'),
Field('modified_at'),
primarykey=['uuid'])
self.clients = self.db.define_table('clients',
Field('uuid'),
Field('company_name'),
Field('created_at'),
Field('modified_at'),
primarykey=['uuid'])
self.projects = self.db.define_table('projects',
Field('uuid'),
Field('client_uuid'),
Field('name'),
Field('created_at'),
Field('modified_at'),
Field('basin'),
Field('shapefile'),
primarykey=['uuid'])
@add_post_func_delay(0.5)
def insert_well(self, *args):
self.log.info("inserting well into DB..")
self.wells.insert(uuid=uuid.uuid4(),
well_name=args[0],
uwi=args[1],
created_at=datetime.datetime.now(),
modified_at=datetime.datetime.now())
self.db.commit()
@add_post_func_delay(0.5)
def insert_client(self, *args):
self.log.info("inserting client into DB..")
self.clients.insert(uuid=uuid.uuid4(),
company_name=args[0],
created_at=datetime.datetime.now(),
modified_at=datetime.datetime.now())
self.db.commit()
# TODO insert project has been modified on the front end such that there is no field called project type on DB table
# def insert_project(self, *args):
# self.insert_client(args[0])
# c_uuid = self.clients(company_name=args[0])['uuid']
# # self.db.projects.insert(uuid=uuid.uuid4(), name=args[1], client_uuid=c_uuid, basin='Midland', created_at=datetime.datetime.now(), modified_at=datetime.datetime.now())
# self.projects.insert(uuid=uuid.uuid4(), name=args[1], client_uuid=c_uuid, basin='midland', created_at=datetime.datetime.now(), modified_at=datetime.datetime.now())
# self.db.commit()
@add_post_func_delay(0.5)
def delete_table(self, tablename):
self.log.info("deleteing table {0}".format(tablename))
table = self.db.get(tablename)
table.truncate(mode='CASCADE')
self.db.commit()
def execute_sql(self, sql):
self.log.info("executing sql: '{0}'".format(sql))
return self.db.executesql(sql)
def close(self):
self.log.info("closing DB instance..")
self.db.close()
class KeyValueStore(object):
def __init__(self):
self.db = DAL('sqlite://keyvalstore.db', folder=get_config_dir())
self.db.define_table(
'store', Field('key', type='string', required=True, unique=True),
Field('value', type='blob'),
Field('version', type='integer', default=0))
def __del__(self):
self.db.close()
def set(self, key, value):
# We have to execute pure SQL statements because pydal does not support blobs as it seems
res = self.db.executesql('SELECT value from store where key=?',
placeholders=[key])
if len(res) > 0:
self.db.executesql('UPDATE store SET value=? where key=?',
placeholders=[pickle.dumps(value), key])
else:
self.db.executesql(
'INSERT INTO "store"("key","value") VALUES (?, ?)',
placeholders=[key, pickle.dumps(value)])
self.db.commit()
def get(self, key, default=""):
res = self.db.executesql('SELECT value from store where key=?',
placeholders=[key])
return default if len(res) == 0 else pickle.loads(res[0][0])
def set_versioned(self, key, value, version):
response = requests.post(
'https://dibser.vserverli.de/php/keyvaluestore.php?key={}&version={}'
.format(key, version),
data=pickle.dumps(value),
headers={'Content-Type': 'application/octet-stream'})
response.raise_for_status()
res = self.db.executesql('SELECT value from store where key=?',
placeholders=[key])
if len(res) > 0:
self.db.executesql(
'UPDATE store SET value=?, version=? where key=?',
placeholders=[pickle.dumps(value), version, key])
else:
self.db.executesql(
'INSERT INTO "store"("key","value", "version") VALUES (?, ?, ?)',
placeholders=[key, pickle.dumps(value), version])
self.db.commit()
def get_versioned(self, key, default=""):
res = self.db.executesql('SELECT value,version from store where key=?',
placeholders=[key])
value = None
if len(res) > 0:
newest_version = local_version = res[0][1]
if not local_version: local_version = 1
value = res[0][0]
# check remote version and update if remote is newer (status code == 200 if a newer version is available, otherwise status code 404 if not available or 304 if older/same version)
response = requests.get(
'https://dibser.vserverli.de/php/keyvaluestore.php?key={}&version={}'
.format(key, local_version))
if response.status_code == 200:
newest_version = int(response.headers['X-Keyvalstore-Version'])
value = response.content
print(
"Remote version is newer for key={}. Local version={} remote version={}"
.format(key, local_version, newest_version))
return (default, -1) if value == None else (pickle.loads(value),
newest_version)
print("You Want to regist in event!input 1 >> ok 2 >> not ")
check = int(input())
while check < 4:
print("Welcome to festival in Da nang city!")
print(" --------------------- ")
print("You can choose the option!")
print(
"You are 1 : member or 2: not member or 3 :find 4 :delete ..> 5to exist"
)
position = int(input())
if position == 1:
print("--List of festival in Da Nang city---")
list_festival()
elif position == 2:
print("--Please regist member--")
regist_member()
elif position == 3:
print("--List the people have been registed--")
list_registedById()
elif position == 4:
print("--List the people have been registed by Id--")
delete_registedPerson()
else:
check = 5
finally:
if dbConnect:
dbConnect.close()
if l in punctuation:
checkTrend = checkTrend.replace(l, '')
if not checkTrend.replace(" ",
"").isalnum(): #if the trend is not alphanumeric
db.words.insert(word=trend, freq=1,
trends=[]) #just add it to the db and move on
failed += 1
print(trend, "is not alpha or digit")
continue
splitTrend = parse(trend)
splitTrend = [st.lower() for st in splitTrend]
tdate = str(entry.get('date'))
for word in splitTrend:
#print(word)
trendDates = []
if word in myWords: #if this word already exists,
wordEntry = db(db.words.word == word).select(
db.words.ALL)[0] #just update the freq and associated words
wordEntry.update_record(freq=wordEntry.freq + 1)
wordEntry.update_record(dates=wordEntry.dates + [tdate])
else: #if the word doesn't already exist
db.words.insert(word=word, freq=1,
dates=[tdate]) #add it to the new db
myWords.append(word)
count += 1
print("Added " + str(count) + " entries.")
print("Failed with " + str(failed) + " entries.")
db.close()
class DataBase:
def __init__(self,
username,
password,
host='localhost',
dbname='postgres',
port=5432,
pool_size=5):
self.schema = 'soad'
self.username = username
self.host = host
self.port = port
self.dbname = dbname
self.folder = 'Resources' + os.sep + 'database'
os.environ["PGPASSWORD"] = password
self.password = password
self.dbinfo = 'postgres://' + username + ':' + password + '@' + host + ':' + str(
port) + '/' + self.dbname
self.db = DAL(self.dbinfo,
folder=self.folder,
pool_size=pool_size,
migrate=False,
attempts=1)
self.connection = None
self.threadpool = QThreadPool()
def busca_registro(self,
nome_tabela,
coluna,
valor='',
operador='=',
filtro=None):
if filtro == '' or filtro is None:
filtro = '1=1'
sql = "select * from " + self.schema + ".fnc_buscar_registro(" \
+ "p_tabela=>" + "'" + nome_tabela + "'" \
+ ", p_coluna=>" + "'" + coluna + "'" \
+ ", p_valor=>" + "'" + valor + "'" \
+ ", p_operador=>" + "'" + operador + "'" \
+ ", p_filtro=>" + "'" + filtro + "'" \
+ ");"
return self.execute_sql(sql)
def get_registro(self, fnc, campo, valor):
sql = "select * from " + self.schema + "." + fnc + "(" \
+ "p_" + campo + "=>" + "'" + str(valor) + "'" \
+ ");"
return self.execute_sql(sql)
def call_procedure(self, schema='soad', params=None):
if not params:
return
# Remove parametros vazios
vazio = []
for param in params["params"].items():
if param[1] == '':
vazio.append(param[0])
logging.info('[DataBase] Parâmetros vazios: ' + str(vazio))
for i in range(len(vazio)):
del params["params"][vazio[i]]
params = json.dumps(params, ensure_ascii=False)
sql = "select * from " + schema + ".fnc_chamada_de_metodo(" \
+ "p_json_params=>" + "'" + params + "'" \
+ ");"
return self.execute_sql(sql)
def execute_sql(self, sql, as_dict=True):
retorno = list()
try:
retorno = self.db.executesql(query=sql, as_dict=as_dict)
self.db.commit()
logging.debug('[DataBase] status=' + str(True))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] retorno=' + str(retorno))
prc = True, retorno, str(self.db._lastsql)
except Exception as e:
self.db.rollback()
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception=' + str(e))
retorno.append(e)
prc = False, retorno, str(sql)
except:
e = 'Exceção não tratada'
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception2=' + str(e))
retorno.append(e)
prc = False, e, str(sql)
return prc
def __conectar_banco__(self, progress_callback):
try:
self.connection = self.db.__call__()
#progress_callback.emit(100)
except Exception as e:
logging.debug('[DataBase] ' + str(e))
os.environ["PGPASSWORD"] = ''
pass
#progress_callback.emit(0)
return self
def definir_schema(self, schema):
self.schema = schema
self.execute_sql("SET search_path TO " + self.schema, as_dict=False)
def fechar_conexao(self):
self.db.close()
def progress_fn(self, n):
print("%d%% done" % n)
def retorno_conexao(self, s):
self.connection = s
def thread_complete(self):
logging.debug('[DataBase] Thread Completed')
def abrir_conexao(self):
# Pass the function to execute
worker = Worker(
self.db.__call__
) # Any other args, kwargs are passed to the run function
worker.signals.result.connect(self.retorno_conexao)
worker.signals.finished.connect(self.thread_complete)
#worker.signals.progress.connect(self.progress_fn)
# Execute
self.threadpool.start(worker)
class CalDav(object):
def __init__(self):
self.db = DAL('sqlite://caldav.db', folder=BaseDirectory.save_data_path('pyhttpserver'))
self.db.define_table('collections', Field('url', type='string', required=True, unique=True),
Field('displayname', type='string'),
Field('subscribed', type='boolean', default=True))
self.db.define_table('colitems', Field('href', type='string', required=True),
Field('etag', type='string'),
Field('collection', type='reference collections'),
Field('content', type='blob'),
Field('local_status', type='integer', default=0))
self.davStorages = {}
def __del__(self):
self.db.close()
def sync_collections(self):
deletedCollections = set([ x.url for x in self.db(self.db.collections.url != None).select() ])
cfg = common.get_davconfig()
for serverCfg in cfg.get('servers', []):
config = dict(username=serverCfg['username'], password=serverCfg['password'], url=serverCfg['url'])
config['url'] = requests.get(config['url']).url
storage = CalDAVStorage(**config)
# Fetch all locally available collections, then fetch the collections from the server side and add them locally if they are not available yet
# Afterwards delete all collections from the database that were not available on the server side anymore
for s in storage.discover(**config):
subStorage = CalDAVStorage(**s)
displayname = subStorage.get_meta('displayname')
deletedCollections.discard(s['url'])
self.db.collections.update_or_insert(self.db.collections.url==s['url'], url=s['url'], displayname=displayname)
self.davStorages[s['url']] = subStorage
for deletedCollection in deletedCollections:
print("Collection with url={} was deleted on the server side".format(deletedCollection))
self.db(self.db.collections.url == deletedCollection).delete()
self.db.commit()
def sync(self, socketio=None):
#~ if socketio: socketio.sleep(3)
self.sync_collections()
# sync only subsribed collections...
for collectionRow in self.db(self.db.collections.subscribed == True).select():
davUrl = collectionRow.url
davStorage = self.davStorages[davUrl]
id = collectionRow.id
# Sync local changes to the server
self.sync_local_changes(davUrl)
# get changes from server
updated = set()
new = set()
deleted = set([ x['href'] for x in self.db(self.db.colitems.collection == id).select() ])
for href,etag in davStorage.list():
if href in deleted:
deleted.remove(href)
if self.db((self.db.colitems.collection == id) & (self.db.colitems.href == href)).select()[0].etag != etag:
updated.add(href)
else:
new.add(href)
if len(new)>0: print("New objects at server: ", new)
if len(updated)>0: print("Updated objects at server: ", updated)
if len(deleted)>0: print("Deleted objects at server: ", deleted)
# Delete server deleted items also from local database
for deletedItem in deleted:
print("Deleted object with href={} on the server side".format(deletedItem))
self.db(self.db.colitems.href == deletedItem).delete()
# Fetch server created/modified items and update in local db
for href, item, etag in davStorage.get_multi(updated|new):
self.db.colitems.update_or_insert((self.db.colitems.collection==id) & (self.db.colitems.href==href), href=href, etag=etag, collection=id, content=item.raw, local_status=0)
for collectionRow in self.db(self.db.collections.subscribed == False).select():
self.db(self.db.colitems.collection==collectionRow.id).delete();
self.db.commit()
def get_collection_id(self, url):
return self.db(self.db.collections.url == url).select()[0].id
def sync_local_changes(self, davStorageUrl):
davStorage = self.davStorages[davStorageUrl]
collectionId = self.get_collection_id(davStorageUrl)
for deletedItem in self.db((self.db.colitems.local_status == 2) & (self.db.colitems.collection == collectionId)).select():
print("Deleting locally removed item with etag={} from server".format(deletedItem.etag))
davStorage.delete(deletedItem.href, deletedItem.etag)
deletedItem.delete_record()
for modifiedItem in self.db((self.db.colitems.local_status == 1) & (self.db.colitems.collection == collectionId)).select():
print("Updating locally modified item with etag={} at server".format(modifiedItem.etag))
newEtag = davStorage.update(modifiedItem.href, Item(modifiedItem.content), modifiedItem.etag)
modifiedItem.update_record(etag=newEtag, local_status=0)
for newItem in self.db((self.db.colitems.local_status == 3) & (self.db.colitems.collection == collectionId)).select():
print("Adding a new ical to the server")
href, etag = davStorage.upload(Item(newItem.content))
newItem.update_record(etag=etag, href=href, local_status=0)
self.db.commit()
def get_subscribed_collections(self):
return [ {'url': x.url, 'id': x.id, 'name': x.displayname} for x in self.db(self.db.collections.subscribed == True).select() ]
def get(self, id):
return vcard21_to_vcard30(vobject.readOne(self.db(self.db.colitems.id == id).select()[0].content))
def get_structured(self, id):
vcard = self.get(id)
ret = {}
vcardAsDict = vcard.contents
if 'fn' not in vcardAsDict:
print("vCard with id={} does not have the field FN set".format(id))
else:
if len(vcardAsDict['fn']) > 1: print("vCard with id={} has more than one FN field. Ignoring all but the first".format(id))
ret['fn'] = vcardAsDict['fn'][0].value
if 'n' not in vcardAsDict:
print("vCard with id={} does not have the field N set".format(id))
ret['fn'] = dict(given="",family="",prefix="",suffix="",additional="")
else:
if len(vcardAsDict['n']) > 1: print("vCard with id={} has more than one N field. Ignoring all but the first".format(id))
ret['n'] = vcardAsDict['n'][0].value.__dict__
if len(vcardAsDict.get('bday', [])) > 1: print("vCard with id={} has more than one BDAY field. Ignoring all but the first".format(id))
ret['bday'] = ""
if 'bday' in vcardAsDict and vcardAsDict['bday'][0].value:
bday_datetime = parse(vcardAsDict['bday'][0].value).replace(tzinfo=None)
ret['bday'] = '%02d. %02d. %04d' % (bday_datetime.day, bday_datetime.month, bday_datetime.year)
for a in ['adr','tel','email']:
for i in range(len(vcardAsDict.get(a, []))):
setattr(vcardAsDict[a][i], 'orig_id', i)
for attr in ['adr', 'tel', 'email']:
ret[attr] = [ {'value': (x.value if type(x.value)==str else x.value.__dict__),
'type': [t.lstrip('x-') for t in x.params.get('TYPE', []) if t.lower() != 'pref'],
'pref': (vcard_get_pref_value(x) != 101),
'orig_id': x.orig_id
} for x in sorted(vcardAsDict.get(attr, []), key=vcard_get_pref_value) ]
ret['itemid'] = id
ret['colid'] = self.db(self.db.colitems.id == id).select()[0].collection;
return ret
def get_serialized(self, id):
vcard = self.get_structured(id)
vcard_formatted = 'Name: {}\n'.format(vcard['fn'])
vcard_formatted += 'Full name: {} {} {} {} {}\n\n'.format(vcard['n']['prefix'],vcard['n']['given'],vcard['n']['additional'],vcard['n']['family'],vcard['n']['suffix'])
if vcard['bday']: vcard_formatted += 'Birthday: {}\n\n'.format(vcard['bday'])
for email in vcard['email']:
vcard_formatted += 'Email {}{}:\n\t{}\n'.format(",".join(email['type']), " (pref)" if email['pref'] else "", email['value'])
vcard_formatted += '\n'
for adr in vcard['adr']:
vcard_formatted += 'Address {}{}:\n\t{}\n\t{} {}\n\t{}\n\n'.format(",".join(adr['type']), " (pref)" if adr['pref'] else "", adr['value']['street'], adr['value']['code'], adr['value']['city'], adr['value']['country'])
for tel in vcard['tel']:
vcard_formatted += 'Telephone {}{}:\n\t{}\n'.format(",".join(tel['type']), " (pref)" if tel['pref'] else "", tel['value'])
return vcard_formatted
def get_all_items(self):
def mk_item(db_item):
ret = {"id": db_item.id, "colid": db_item.collection}
vItem = self.get_structured(db_item.id)
ret['fn'] = vItem['fn']
ret['email'] = "<br />".join((x['value'] for x in vItem['email']))
ret['tel'] = "<br />".join((x['value'] for x in vItem['tel']))
ret['bday'] = vItem['bday']
ret['bday_diff'] = 400
if vItem['bday']:
bday_datetime = datetime.strptime(vItem['bday'], "%d. %m. %Y")
ret['bday_diff'] = (rrule(YEARLY, bymonth=bday_datetime.month, bymonthday=bday_datetime.day).after(bday_datetime, True) - datetime.today()).days
return ret
return ( mk_item(x) for x in self.db(self.db.colitems.local_status != 2).select() )
def _merge_dict_to_vcard(self, itemDict, vcard):
def get_vcard_item(vcard, item, append=False, cls=str):
entry = vcard.add(item) if append else getattr(vcard, item) if hasattr(vcard, item) else vcard.add(item)
if append: entry.value = cls()
assert cls == type(entry.value)
return entry
get_vcard_item(vcard, 'n', cls=vobject.vcard.Name).value.__dict__.update(itemDict['n'])
get_vcard_item(vcard, 'fn').value = "{0} {1}".format(itemDict['n']['given'], itemDict['n']['family'])
if itemDict['bday']:
get_vcard_item(vcard, 'bday').value = datetime.strptime(itemDict['bday'], "%d. %m. %Y").strftime("%Y%m%d")
del vcard.contents['bday'][1:]
elif 'bday' in vcard.contents: del vcard.contents['bday']
for (attr, cls) in [('adr',vobject.vcard.Address), ('tel',str), ('email',str)]:
entriesBefore = frozenset(range(len(vcard.contents.get(attr, []))))
entriesNow = frozenset([ x['orig_id'] for x in itemDict[attr] if 'orig_id' in x ])
for item in itemDict[attr]:
orig_id = item.get('orig_id', None)
entry = vcard.contents[attr][orig_id] if orig_id != None else get_vcard_item(vcard, attr, append="True", cls=cls)
if (cls == str): entry.value = item['value']
else: entry.value.__dict__.update(item['value'])
knownTypes = frozenset(item['type']).intersection(defined_types[attr])
userTypes = [ 'x-{}'.format(x) for x in frozenset(item['type']).difference(defined_types[attr]) ]
pref = ['pref'] if item['pref'] else []
entry.params['TYPE'] = list(knownTypes)+userTypes+pref
if attr=='adr' and 'LABEL' in entry.params: del entry.params['LABEL']
for deletedIdx in sorted(entriesBefore.difference(entriesNow), reverse=True): del vcard.contents[attr][deletedIdx]
d = datetime.now()
get_vcard_item(vcard, 'rev').value = "%04d%02d%02dT%02d%02d%02d"%(d.year,d.month,d.day,d.hour,d.minute,d.second)
return vcard
def update_item(self, id, itemDict, socketio=None):
self.db(self.db.colitems.id == id).update(content=self._merge_dict_to_vcard(itemDict, self.get(id)).serialize(), local_status=1)
self.db.commit()
if socketio: socketio.start_background_task(self.sync, socketio)
else: self.sync()
return self.get_structured(id)
def add_item(self, itemDict, collectionId=1, socketio=None):
id = self.db.colitems.insert(content=self._merge_dict_to_vcard(itemDict, vobject.vCard()).serialize(), local_status=3, collection=collectionId, href="")
self.db.commit()
if socketio: socketio.start_background_task(self.sync, socketio)
else: self.sync()
return self.get_structured(id)
def delete_item(self, id, socketio=None):
item = self.db(self.db.colitems.id == id).select()[0]
if item.local_status == 3: item.delete_record()
else: item.update_record(local_status=2)
self.db.commit()
if socketio: socketio.start_background_task(self.sync, socketio)
else: self.sync()
def get_collections(self, user, pw, url):
config = dict(username=user, password=pw, url=url)
if not pw:
keyringData = keyring.DbusKeyring().FindItem({'dav-url': url, 'dav-user': user})
config['password'] = keyringData[1]
config['url'] = requests.get(config['url']).url # do redirection magic
storage = CalDAVStorage(**config)
ret = []
for s in storage.discover(**config):
subscribed = False
subStorage = CalDAVStorage(**s)
displayname = subStorage.get_meta('displayname')
dbSet = self.db(self.db.collections.url==s['url'])
if not dbSet.isempty(): subscribed = dbSet.select()[0].subscribed
ret.append({'url': s['url'], 'subscribed': subscribed, 'name': displayname})
return ret
def update_collection_subscriptions(self, incomingJson):
for server in incomingJson['servers']:
for caldav in server['caldav_collections']:
self.db.collections.update_or_insert(self.db.collections.url==caldav['url'], url=caldav['url'], subscribed=caldav["subscribed"]);
