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
Example #2
0
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()
Example #3
0
#!/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()
Example #4
0
            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)