1
# gozerbot/databse/alchemy.py
5
""" alchemy interface. """
7
__copyright__ = 'this file is in the public domain'
12
from gozerbot.stats import stats
13
from gozerbot.datadir import datadir
14
from gozerbot.config import config
15
from gozerbot.utils.locking import lockdec
16
from gozerbot.utils.log import rlog
17
from gozerbot.utils.exception import handle_exception
20
from sqlalchemy.ext.declarative import declarative_base
21
from sqlalchemy.ext.associationproxy import association_proxy
22
from sqlalchemy.ext.orderinglist import ordering_list
23
from sqlalchemy import Text, Integer, Sequence, ForeignKey, DateTime
24
from sqlalchemy import create_engine, Column, String, Table
25
from sqlalchemy.orm import scoped_session, sessionmaker, relation, eagerload
26
from sqlalchemy.orm import create_session as cs
29
import sqlalchemy, thread, os, time, logging
35
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
38
alchemylock = thread.allocate_lock()
39
dblocked = lockdec(alchemylock)
40
sessionlock = thread.allocate_lock()
41
sessionlocked = lockdec(sessionlock)
42
querylock = thread.allocate_lock()
43
querylocked = lockdec(querylock)
44
createlock = thread.allocate_lock()
45
createlocked = lockdec(createlock)
50
def create_all(plugname='all', base=None):
51
rlog(10, 'alchemy', 'running create_all (%s)' % plugname)
52
if plugname not in created:
53
created.append(plugname)
56
base.metadata.create_all()
58
rlog(10, 'alchemy', '%s tables already created' % plugname)
60
def geturi(ddir=None, mainconfig=None):
62
""" determine database URI from config file """
70
from gozerbot.config import config
72
# if dburi not provided in config file construct it
73
if not config['dburi']:
75
if not 'sqlite' in config['dbtype'] and not 'mysql' in config['dbtype']:
76
dburi = "%s://%s:%s@%s/%s" % (config['dbtype'], config['dbuser'], \
77
config['dbpasswd'], config['dbhost'], config['dbname'])
78
elif 'mysql' in config['dbtype']:
79
dburi = "%s://%s:%s@%s/%s?charset=utf8&use_unicode=0" % (config['dbtype'], config['dbuser'], \
80
config['dbpasswd'], config['dbhost'], config['dbname'])
82
if not os.path.isdir(d + os.sep + 'db'):
83
os.mkdir(d + os.sep + 'db')
84
dburi = "sqlite:///%s/%s" % (ddir or datadir, config['dbname'])
87
# dburi found in config
88
dburi = config['dburi']
92
dbtype = dburi.split(':')[0]
94
rlog(10, 'alchemy', "can't extract db data from dburi")
98
if config['dbtype'] != dbtype:
99
config['dbtype'] = dbtype
104
def dbstart(ddir=None, mainconfig=None, base=None):
106
""" start the database connection setting Session and engine. """
108
dburi = geturi(ddir, mainconfig)
110
# only show dburi if it doesn't contain a password
112
rlog(10, 'alchemy', 'starting database %s' % dburi)
114
rlog(10, 'alchemy', 'starting database')
117
if 'mysql' in config['dbtype']:
118
engine = create_engine(dburi, strategy='threadlocal', pool_recycle=3600, max_overflow=-1)
120
engine = create_engine(dburi, strategy='threadlocal')
122
# setup metadata and session
125
base.metadata.bind = engine
127
rlog(10, 'alchemy', 'done')
128
Session = scoped_session(sessionmaker(autoflush=True))
129
Session.configure(bind=engine)
130
stats.up('alchemy', 'engines')
132
return (Session, engine)
135
Base = declarative_base()
136
#Session, engine = dbstart(datadir)
137
Session = engine = None
139
def startmaindb(ddir=None, mainconfig=None):
141
""" start the main database. """
146
Session, engine = dbstart(ddir, mainconfig)
152
user = Table('user', Base.metadata,
153
Column('name', String(255), primary_key=True)
156
email = Table('email', Base.metadata,
157
Column('name', String(255), ForeignKey(user.c.name), nullable=False),
158
Column('email', String(255), nullable=False),
159
Column('order', Integer, nullable=False)
164
_userhosts = relation("UserHost", backref="user", cascade="all, delete-orphan")
165
_perms = relation("Perms", backref="user", cascade="all, delete-orphan")
166
_permits = relation("Permits", backref="user",cascade="all, delete-orphan" )
167
_statuses = relation("Statuses", backref="user", cascade="all, delete-orphan")
168
_pasword = relation("Passwords", backref="user", cascade="all, delete-orphan")
169
_email = relation("Email", backref="user", collection_class=ordering_list('order'),
170
cascade="all, delete-orphan", order_by=[email.c.order])
171
email = association_proxy('_email', 'email')
172
userhosts = association_proxy('_userhosts', 'userhost')
173
perms = association_proxy('_perms', 'perm')
174
permits = association_proxy('_permits', 'permit')
175
statuses = association_proxy('_statuses', 'status')
176
password = association_proxy('_password', 'passwd')
180
__mapper_args__ = {'primary_key':[email.c.name,email.c.email]}
182
def __init__(self, email):
185
class UserHost(Base):
186
__tablename__ = 'userhosts'
187
userhost = Column('userhost', String(255), primary_key=True)
188
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
190
def __init__(self, userhost):
191
self.userhost = userhost
194
__tablename__ = 'perms'
195
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
196
perm = Column('perm', String(255), nullable=False)
197
__mapper_args__ = {'primary_key':[name,perm]}
199
def __init__(self, perm):
203
__tablename__ = 'permits'
204
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
205
permit = Column('permit', String(255), nullable=False)
206
__mapper_args__ = {'primary_key':[name,permit]}
208
def __init__(self, permit):
211
class Statuses(Base):
212
__tablename__ = 'statuses'
213
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
214
status = Column('status', String(255), nullable=False)
215
__mapper_args__ = {'primary_key':[name,status]}
217
def __init__(self, status):
220
class Passwords(Base):
221
__tablename__ = 'passwords'
222
name = Column('name', String(255), ForeignKey('user.name'), primary_key=True)
223
passwd = Column('passwd', String(255), nullable=False)
225
def __init__(self, passwd):
230
def trans(func, ismethod=True):
232
""" transaction function attribute. """
235
def transaction(*args, **kwargs):
237
""" the tranasction wrapper .. works on methods. """
242
stats.up('alchemy', 'transactions')
243
session = create_session()
246
session.begin(subtransactions=True)
247
except sqlalchemy.exc.InvalidRequestError, ex:
248
rlog(10, 'alchemy', 'error %s: %s' % (str(arglist), str(ex)))
251
session = create_session()
252
session.begin(subtransactions=True)
255
arglist.insert(0, session)
257
arglist.insert(1, session)
259
res = func(*arglist, **kwargs)
266
except sqlalchemy.exc.InvalidRequestError, ex:
267
rlog(10, 'alchemy', 'error %s: %s' % (str(arglist), str(ex)))
270
except sqlalchemy.exc.TimeoutError:
271
rlog(10, 'alchemy', 'timeout occured')
274
except Exception ,ex:
278
res = func(*arglist, **kwargs)
287
""" transaction wrapper for functions. """
289
return trans(func, ismethod=False)
291
def rollback(session):
293
""" rollback on provided session. """
295
rlog(10, 'alchemy', 'rollback on %s' % str(session))
300
""" close provided session. """
304
rlog(10, 'alchemy', '%s closed' % str(session))
307
def create_session():
309
""" create a session ready for use. """
311
stats.up('alchemy', 'sessions')
316
def query(q, session=None):
318
""" do a query on the database. """
322
session = create_session()
324
stats.up('alchemy', 'query')
327
# session.begin(subtransactions=True)
328
#except sqlalchemy.exc.InvalidRequestError, ex:
329
# rlog(10, 'alchemy', 'error %s: %s' % (str(q), str(ex)))
332
# session = create_session()
333
# session.begin(subtransactions=True)
335
res = session.query(q)
338
except sqlalchemy.exc.TimeoutError:
339
rlog(10, 'alchemy', 'timeout occured')
342
except Exception ,ex:
346
session = create_session()
347
res = session.query(q)
350
def getuser(userhost, session=None):
352
""" get a user based on userhost. """
354
stats.up('alchemy', 'getuser')
355
session = create_session()
357
session.begin(subtransactions=True)
358
except sqlalchemy.exc.InvalidRequestError, ex:
361
session = create_session()
362
session.begin(subtransactions=True)
365
user = query(UserHost, session).filter_by(userhost=userhost).first()
367
session = create_session()
368
res = query(User, session).filter_by(name=user.name.lower()).first()
372
rlog(10, 'alchemy', 'error getting %s user: %s' % (str(userhost), str(ex)))
375
except sqlalchemy.orm.exc.ConcurrentModificationError, ex:
376
rlog(10, 'alchemy', 'error getting %s user: %s' % (str(userhost), str(ex)))
379
except sqlalchemy.exc.InvalidRequestError, ex:
380
rlog(10, 'alchemy', 'error getting %s user: %s' % (str(userhost), str(ex)))
384
def byname(name , session=None):
386
""" get a users based on name. """
388
session = create_session()
390
res = query(User, session).filter_by(name=name.lower()).first()
393
rlog(10, 'alchemy', 'error getting %s user: %s' % (str(name), str(ex)))
395
except sqlalchemy.exc.InvalidRequestError, ex:
396
rlog(10, 'alchemy', 'error getting %s user: %s' % (str(name), str(ex)))
400
def dbupgrade(session, mainconfig=None):
402
""" upgrade the database. """
405
print 'upgrading users'
406
users = session.query(UserHost).all()
409
# populate the User table
416
newuser = User(name=name)
418
upgraded.append(name)
419
except sqlalchemy.exc.IntegrityError, ex:
425
print "upgraded: %s" % ' .. '.join(upgraded)
426
print 'upgrading email table'
427
from gozerbot.database.db import Db
429
# upgrade email table
431
db = Db(config=mainconfig)
432
if db.dbtype == 'mysql':
433
db.execute("ALTER TABLE email ADD COLUMN email.order INT")
435
db.execute("ALTER TABLE email ADD COLUMN 'order' INT")
436
except Exception, ex:
437
if 'already exists' in str(ex) or 'duplicate column name' in \
added
removed
gozerbot/database/alchemy.py
