def __init__(self, *args, **kw):
"""base database class for underlying subpackages like cat, gbl, lbl, etc.
This class can also be used directly.
:param args: list of additional arguments: table_prefix, stmt_factory, error_tolerance
:keyword db_connection: The database connection to be used
:type db_connection: str, cx_oracle.Connection, pydodbc.Connection, sqlite3.Connection, sqlce.Connection
:param args: list of additional arguments: table_prefix, stmt_factory, error_tolerance
:keyword table_prefix: The table name prefix which is usually master schema name
:type table_prefix: str
:keyword stmt_factory: The SQL statement factory to be used
:type stmt_factory: GenericSQLStatementFactory
:keyword strIdent: string identifier used from subclass for identification
:type strIdent: str
:keyword error_tolerance: set log level for debugging purposes
:type error_tolerance: int
:keyword loglevel: being able to reduce logging, especially with scripts
:type loglevel: Integer, see logging class for reference
:keyword autocommit: boolean value to determine wether to commit after an insert or update automatically
:type autocommit: bool
:keyword foreign_keys: set to True when sqlite DB connection should take care of foreign key relations
:type foreign_keys: bool
:keyword arraysize: only for cxoracle: '...This read-write attribute specifies the number of rows to fetch
at a time internally...', default is 50, a good value should be about 500
:type arraysize: int
"""
opts = arg_trans(["db_connection", ("table_prefix", None), ("sql_factory", GenericSQLStatementFactory()),
("error_tolerance", ERROR_TOLERANCE_NONE), ("strIdent", "")], *args, **kw)
# self._log = Logger(self.__class__.__name__, kw.pop("loglevel", INFO))
self._table_prefix = "" if opts["table_prefix"] is None else opts["table_prefix"].rstrip('.')
self._sql_factory = opts["sql_factory"]
self._ident = opts["strIdent"]
self.error_tolerance = opts["error_tolerance"]
self._enable_fks = opts.pop("foreign_keys", False)
self._arraysize = None
self._db_type = None
self.db_func_map = {}
self.role = None
self.sync_slave_reload_all = False
self._connstr = [None, self._table_prefix]
self._func_params = recomp(r"(\(.*\))")
self._auto_commit = opts.pop("autocommit", False)
self._direct_conn = False
self._db_connection = None
# connect to DB and configure some DB specifics like role, date, time, etc.
db_connection = opts["db_connection"]
if type(db_connection) in StringTypes:
self._connstr[0] = db_connection
self._direct_conn = True
cslower = db_connection.lower()
if db_connection in CONN_STRING:
self._table_prefix = CONN_STRING[db_connection][1]
db_connection = CONN_STRING[db_connection][0]
cslower = db_connection.lower()
if any(cslower.endswith(ext) for ext in SQLITE_FILE_EXT) and path.isfile(cslower) or cslower == ":memory:":
try:
if not stat(db_connection)[ST_MODE] & S_IWRITE:
chmod(db_connection, S_IWRITE)
except:
pass
self._db_connection = sqconnect(db_connection)
self._db_connection.text_factory = str
register_adapter(long, lambda l: str(l) if long(l) > maxint else l)
register_converter("long", lambda l: long(l))
elif any(cslower.endswith(ext) for ext in SDF_FILE_EXT):
if cslower.startswith('data source='):
fname = db_connection[cslower.find("data source=") + 12:].partition(";")[0].strip()
else:
fname = cslower
db_connection = 'data source=' + db_connection
if not stat(fname)[ST_MODE] & S_IWRITE:
chmod(fname, S_IWRITE)
if "provider=" not in cslower:
db_connection += (";Provider=%s" % DEFAULT_SLAVE_DATA_PROVIDER)
self._db_connection = adoconnect(db_connection)
self._db_connection.adoConn.CursorLocation = adUseServer
else:
# ex: DBQ=racadmpe;Uid=DEV_MFC31X_ADMIN;Pwd=MFC31X_ADMIN
# init argument part, split up and use it for cxconnect
# try:
if cxconnect is not None and "uid" in cslower and "pwd" in cslower:
args = {}
for arg in db_connection.split(';'):
part = arg.split('=', 2)
args[part[0].strip().lower()] = part[1].strip()
self._db_connection = cxconnect(args['uid'], args['pwd'], args.pop('dbq', 'racadmpe'),
threaded=opts.pop('threaded', False))
else:
self.db_connection = pyconnect(db_connection)
# except:
# raise AdasDBError("couldn't open database")
self._arraysize = opts.pop("arraysize", None)
self._db_type = DBTYPE.index(str(self._db_connection)[1:].split('.')[0])
else:
self._db_connection = db_connection
self._connstr[0] = str(db_connection)
self._db_type = DBTYPE.index(str(self._db_connection)[1:].split('.')[0])
self.db_func_map[DB_FUNC_NAME_MIN] = "MIN"
self.db_func_map[DB_FUNC_NAME_MAX] = "MAX"
self.db_func_map[DB_FUNC_NAME_LOWER] = "LOWER"
self.db_func_map[DB_FUNC_NAME_UPPER] = "UPPER"
self.db_func_map[DB_FUNC_NAME_GETDATE] = "GETDATE()"
if self._db_type >= 2:
self._db_type = -1
if self._table_prefix != "":
self.execute("ALTER SESSION SET CURRENT_SCHEMA = %s" % self._table_prefix)
username = self._table_prefix if len(self._table_prefix) > 0 else "SELECT USER FROM DUAL"
self._tablequery = SQL_TABLENAMES[-1].replace("$NM", username).replace("$TS", self._ident[2:])
self._columnquery = SQL_COLUMNS[-1][1].replace("$NM", username)
# username = self.execute("SELECT sys_context('USERENV', 'SESSION_USER') FROM dual")#[0][0]
# username = '******'
# print "username --> ", username
self.role = ROLE_DB_ADMIN if ROLE_DB_ADMIN in username.upper() else ROLE_DB_USER
self.execute("ALTER SESSION SET NLS_COMP=LINGUISTIC")
self.execute("ALTER SESSION SET NLS_SORT=BINARY_CI")
else:
self.role = ROLE_DB_ADMIN
self._tablequery = SQL_TABLENAMES[self._db_type].replace("$TS", self._table_prefix)
self._columnquery = SQL_COLUMNS[self._db_type][1]
if self._enable_fks and self._db_type == 0: # off per default to keep unittests running
self.execute("PRAGMA FOREIGN_KEYS = ON")
self.date_time_format = DEFAULT_DATETIME_FORMAT
# retrieve the sub schema version
try:
self._sub_versions = {i[0]: i[1] for i in self.execute("SELECT SUBSCHEMEPREF, SUBSCHEMEVERSION "
"FROM VERSIONS")}
except:
self._sub_versions = {}
# retrieve the who is the current user gbl_user table
try:
self.current_gbluserid = self.execute("SELECT USERID FROM GBL_USERS WHERE LOGINNAME = $CU")[0][0]
except:
self.current_gbluserid = None
def _create_db():
lconn = sqconnect('./data.db')
cur = lconn.cursor()
try:
cur.execute('''
CREATE TABLE
datasets(
datasetID INTEGER PRIMARY KEY not null,
type TEXT,
name TEXT,
description TEXT,
license TEXT,
path text,
created_at INTEGER,
updated_at INTEGER,
url text)
''')
lconn.commit()
except Exception as e:
print(e)
try:
cur.execute('''
CREATE TABLE
sameAs(
sameAsID INTEGER PRIMARY KEY not null,
datasetID INT,
librisid TEXT)
''')
lconn.commit()
except Exception as e:
print(e)
try:
cur.execute('''
CREATE TABLE
distribution(
distID INTEGER PRIMARY KEY not null,
datasetID INT,
encodingFormat TEXT)
''')
lconn.commit()
except Exception as e:
print(e)
try:
cur.execute('''
CREATE TABLE
provider(
prodviderID INTEGER PRIMARY KEY not null,
datasetID INT,
name TEXT,
email TEXT)
''')
except Exception as e:
print(e)
try:
cur.execute('''
CREATE TABLE
users(
userID INTEGER PRIMARY KEY not null,
username text unique,
password text
)
''')
lconn.commit()
except Exception as e:
print(e)
try:
cur.execute('''
INSERT INTO users
(username, password)
VALUES
(?, ?)
''', ('admin', adminPWhashed)
)
lconn.commit()
print("Admin password: %s" % adminPW)
except Exception as e:
print(e)
cur.close()
#!/usr/bin/env python
from bottle import route, run, request, response
from sqlite3 import connect as sqconnect
from OpenSSL import crypto
from datetime import datetime
from ConfigParser import ConfigParser, NoOptionError
config = ConfigParser()
config.readfp(open('pyca.cfg'))
conn = sqconnect('./ca.db')
caCertfile = config.get('ca', 'cacert')
caKeyfile = config.get('ca', 'cakey')
caKeypass = config.get('ca', 'passphrase')
try:
caIntermediate = crypto.dump_certificate(
crypto.FILETYPE_PEM,
crypto.load_certificate(
crypto.FILETYPE_PEM,
open(config.get('ca', 'intermediates')).read()
)
)
except NoOptionError:
caIntermediate = ""
def _create_db():
cur = conn.cursor()
VALUES
(?, ?)
''', ('admin', adminPWhashed)
)
lconn.commit()
print("Admin password: %s" % adminPW)
except Exception as e:
print(e)
cur.close()
if options.initdb is True:
try:
_create_db()
print("Database initialized")
except Exception as e:
print("Could not initialize database: %s" % e)
if options.resetadmin is True:
try:
conn = sqconnect('./data.db')
cur = conn.cursor()
cur.execute('''
UPDATE users
SET password=?
WHERE ROWID=1
''', (adminPWhashed, )
)
conn.commit()
print("Admin password reset to %s" % adminPW)
except Exception as e:
print("Could not reset admin password %s" % e)