123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110 |
- """MySQL Database implementation."""
- from __future__ import print_function
- from pwman.data.database import Database, __DB_FORMAT__
- import MySQLdb as mysql
- class MySQLDatabase(Database):
- @classmethod
- def check_db_version(cls, dburi):
- port = 3306
- credentials, host = dburi.netloc.split('@')
- user, passwd = credentials.split(':')
- if ':' in host:
- host, port = host.split(':')
- port = int(port)
- con = mysql.connect(host=host, port=port, user=user, passwd=passwd,
- db=dburi.path.lstrip('/'))
- cur = con.cursor()
- try:
- cur.execute("SELECT VERSION FROM DBVERSION")
- version = cur.fetchone()
- cur.close()
- con.close()
- return version[-1]
- except mysql.ProgrammingError:
- con.rollback()
- def __init__(self, mysqluri, dbformat=__DB_FORMAT__):
- self._mysqluri = mysqluri
- self.dbversion = dbformat
- def _open(self):
- port = 3306
- credentials, host = self.dburi.netloc.split('@')
- user, passwd = credentials.split(':')
- if ':' in host:
- host, port = host.split(':')
- port = int(port)
- self._con = mysql.connect(host=host, port=port, user=user,
- passwd=passwd,
- db=self.dburi.path.lstrip('/'))
- self._cur = self._con.cursor()
- self._create_tables()
- def _create_tables(self):
- try:
- self._cur.execute("SELECT 1 from DBVERSION")
- version = self._cur.fetchone()
- if version:
- return
- except mysql.ProgrammingError:
- self._con.rollback()
- try:
- self._cur.execute("CREATE TABLE NODE(ID SERIAL PRIMARY KEY, "
- "USERNAME TEXT NOT NULL, "
- "PASSWORD TEXT NOT NULL, "
- "URL TEXT NOT NULL, "
- "NOTES TEXT NOT NULL"
- ")")
- self._cur.execute("CREATE TABLE TAG"
- "(ID SERIAL PRIMARY KEY,"
- "DATA TEXT NOT NULL UNIQUE)")
- self._cur.execute("CREATE TABLE LOOKUP ("
- "nodeid SERIAL REFERENCES NODE(ID),"
- "tagid SERIAL REFERENCES TAG(ID)"
- ")")
- self._cur.execute("CREATE TABLE CRYPTO "
- "(SEED TEXT, DIGEST TEXT)")
- self._cur.execute("CREATE TABLE DBVERSION("
- "VERSION TEXT NOT NULL DEFAULT {}"
- ")".format(__DB_FORMAT__))
- self._cur.execute("INSERT INTO DBVERSION VALUES(%s)",
- (self.dbversion,))
- self._con.commit()
- except mysql.ProgrammingError:
- self._con.rollback()
|