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
alchemylocked = 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)
51
def geturi(ddir=None, mainconfig=None):
53
""" determine database URI from config file """
61
from gozerbot.config import config
63
# if dburi not provided in config file construct it
64
if not config['dburi']:
66
if not 'sqlite' in config['dbtype'] and not 'mysql' in config['dbtype']:
67
dburi = "%s://%s:%s@%s/%s" % (config['dbtype'], config['dbuser'], \
68
config['dbpasswd'], config['dbhost'], config['dbname'])
69
elif 'mysql' in config['dbtype']:
70
dburi = "%s://%s:%s@%s/%s?charset=utf8&use_unicode=0" % (config['dbtype'], config['dbuser'], \
71
config['dbpasswd'], config['dbhost'], config['dbname'])
73
if not os.path.isdir(d + os.sep + 'db'):
74
os.mkdir(d + os.sep + 'db')
75
dburi = "sqlite:///%s/%s" % (ddir or datadir, config['dbname'])
78
# dburi found in config
79
dburi = config['dburi']
83
dbtype = dburi.split(':')[0]
85
rlog(10, 'alchemy', "can't extract db data from dburi")
89
if config['dbtype'] != dbtype:
90
config['dbtype'] = dbtype
95
def dbstart(ddir=None, mainconfig=None, base=None):
97
""" start the database connection setting Session and engine. """
99
dburi = geturi(ddir, mainconfig)
101
# only show dburi if it doesn't contain a password
103
rlog(10, 'alchemy', 'starting database %s' % dburi)
105
rlog(10, 'alchemy', 'starting database')
108
engine = create_engine(dburi, strategy='threadlocal', pool_recycle=60, pool_size=50, max_overflow=0)
110
engine = create_engine(dburi, strategy='threadlocal', pool_recycle=3600)
112
# engine = create_engine(dburi, pool_recycle=60, pool_size=50, max_overflow=0)
114
# engine = create_engine(dburi, pool_recycle=3600)
116
# setup metadata and session
119
base.metadata.bind = engine
121
rlog(10, 'alchemy', 'done')
122
Session = scoped_session(sessionmaker(bind=engine, autocommit=True))
123
stats.up('alchemy', 'engines')
125
return (Session, engine)
128
Base = declarative_base()
129
#Session, engine = dbstart(datadir)
130
Session = engine = None
132
def startmaindb(ddir=None, mainconfig=None):
134
""" start the main database. """
139
Session, engine = dbstart(ddir, mainconfig)
142
def dblocked(func, *args, **kwargs):
145
def dofunc(*args, **kwargs):
147
Session.begin(subtransactions=True)
148
res = func(*args, **kwargs)
151
except AttributeError: idnr = None
154
except Exception, ex:
155
Session.rollback() ; Session.close()
159
def create_all(plugname='all', base=None):
160
rlog(10, 'alchemy', 'running create_all (%s)' % plugname)
161
if plugname not in created:
162
created.append(plugname)
165
try: base.metadata.create_all()
166
except Exception, ex: rlog(10, 'alchemy', 'problem creating %s tables: %s' % (str(base), str(ex)))
168
rlog(10, 'alchemy', '%s tables already created' % plugname)
172
user = Table('user', Base.metadata,
173
Column('name', String(255), primary_key=True)
176
email = Table('email', Base.metadata,
177
Column('name', String(255), ForeignKey(user.c.name), nullable=False),
178
Column('email', String(255), nullable=False),
179
Column('order', Integer, nullable=False)
184
_userhosts = relation("UserHost", backref="user", cascade="all, delete-orphan")
185
_perms = relation("Perms", backref="user", cascade="all, delete-orphan")
186
_permits = relation("Permits", backref="user",cascade="all, delete-orphan" )
187
_statuses = relation("Statuses", backref="user", cascade="all, delete-orphan")
188
_pasword = relation("Passwords", backref="user", cascade="all, delete-orphan")
189
_email = relation("Email", backref="user", collection_class=ordering_list('order'),
190
cascade="all, delete-orphan", order_by=[email.c.order])
191
email = association_proxy('_email', 'email')
192
userhosts = association_proxy('_userhosts', 'userhost')
193
perms = association_proxy('_perms', 'perm')
194
permits = association_proxy('_permits', 'permit')
195
statuses = association_proxy('_statuses', 'status')
196
password = association_proxy('_password', 'passwd')
200
__mapper_args__ = {'primary_key':[email.c.name,email.c.email]}
202
def __init__(self, email):
205
class UserHost(Base):
206
__tablename__ = 'userhosts'
207
userhost = Column('userhost', String(255), primary_key=True)
208
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
210
def __init__(self, userhost):
211
self.userhost = userhost
214
__tablename__ = 'perms'
215
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
216
perm = Column('perm', String(255), nullable=False)
217
__mapper_args__ = {'primary_key':[name,perm]}
219
def __init__(self, perm):
223
__tablename__ = 'permits'
224
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
225
permit = Column('permit', String(255), nullable=False)
226
__mapper_args__ = {'primary_key':[name,permit]}
228
def __init__(self, permit):
231
class Statuses(Base):
232
__tablename__ = 'statuses'
233
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
234
status = Column('status', String(255), nullable=False)
235
__mapper_args__ = {'primary_key':[name,status]}
237
def __init__(self, status):
240
class Passwords(Base):
241
__tablename__ = 'passwords'
242
name = Column('name', String(255), ForeignKey('user.name'), primary_key=True)
243
passwd = Column('passwd', String(255), nullable=False)
245
def __init__(self, passwd):
251
""" do a query on the database. """
252
stats.up('alchemy', 'query')
253
res = Session.query(q)
256
def douser(userhost):
257
user = query(UserHost).filter_by(userhost=userhost).first()
259
res = query(User).filter_by(name=user.name.lower()).first()
264
def getuser(userhost):
266
""" get a user based on userhost. """
268
stats.up('alchemy', 'getuser')
270
return douser(userhost)
271
except sqlalchemy.exc.OperationalError, ex:
272
if 'server has gone away' in str(ex):
273
rlog(10, 'alchemy', 'mysql server has gone away')
275
return douser(userhost)
278
except Exception, ex:
279
rlog(10, 'alchemy', 'error getting %s user: %s' % (str(userhost), str(ex)))
282
res = Session.query(User).filter_by(name=name.lower()).first()
286
""" get a users based on name. """
289
except Exception, ex:
290
if 'server has gone away' in str(ex):
291
rlog(10, 'alchemy', 'error: %s' % str(ex))
298
def dbupgrade(mainconfig=None):
300
""" upgrade the database. """
303
print 'upgrading users'
304
users = Session.query(UserHost).all()
307
# populate the User table
315
newuser = User(name=name)
317
upgraded.append(name)
318
except sqlalchemy.exc.IntegrityError, ex:
325
print "upgraded: %s" % ' .. '.join(upgraded)
326
print 'upgrading email table'
327
from gozerbot.database.db import Db
329
# upgrade email table
331
db = Db(config=mainconfig)
332
if db.dbtype == 'mysql':
333
db.execute("ALTER TABLE email ADD COLUMN email.order INT")
335
db.execute("ALTER TABLE email ADD COLUMN 'order' INT")
336
except Exception, ex:
337
if 'already exists' in str(ex) or 'duplicate column name' in \
added
removed
build/lib/gozerbot/database/alchemy.py
