1
# gozerbot/databse/alchemy.py
5
""" alchemy interface. """
7
__copyright__ = 'this file is in the public domain'
11
from gozerbot.utils.log import rlog
15
from sqlalchemy.ext.declarative import declarative_base
16
from sqlalchemy.ext.associationproxy import association_proxy
17
from sqlalchemy.ext.orderinglist import ordering_list
18
from sqlalchemy import Text, Integer, Sequence, ForeignKey, DateTime, Column, String, Table
19
from sqlalchemy.orm import relation
27
from datetime import datetime
31
def create_all(plugname='all', base=None):
32
rlog(10, 'alchemy', 'running create_all (%s)' % plugname)
33
if plugname not in created:
34
created.append(plugname)
37
try: base.metadata.create_all()
38
except Exception, ex: rlog(10, 'alchemy', 'problem creating %s tables: %s' % (str(base), str(ex)))
40
rlog(10, 'alchemy', '%s tables already created' % plugname)
44
Base = declarative_base()
48
user = Table('user', Base.metadata,
49
Column('name', String(255), primary_key=True)
52
email = Table('email', Base.metadata,
53
Column('name', String(255), ForeignKey(user.c.name), nullable=False),
54
Column('email', String(255), nullable=False),
55
Column('order', Integer, nullable=False)
60
_userhosts = relation("UserHost", backref="user", cascade="all, delete-orphan")
61
_perms = relation("Perms", backref="user", cascade="all, delete-orphan")
62
_permits = relation("Permits", backref="user",cascade="all, delete-orphan" )
63
_statuses = relation("Statuses", backref="user", cascade="all, delete-orphan")
64
_pasword = relation("Passwords", backref="user", cascade="all, delete-orphan")
65
_email = relation("Email", backref="user", collection_class=ordering_list('order'),
66
cascade="all, delete-orphan", order_by=[email.c.order])
67
email = association_proxy('_email', 'email')
68
userhosts = association_proxy('_userhosts', 'userhost')
69
perms = association_proxy('_perms', 'perm')
70
permits = association_proxy('_permits', 'permit')
71
statuses = association_proxy('_statuses', 'status')
72
password = association_proxy('_password', 'passwd')
76
__mapper_args__ = {'primary_key':[email.c.name,email.c.email]}
78
def __init__(self, email):
82
__tablename__ = 'userhosts'
83
userhost = Column('userhost', String(255), primary_key=True)
84
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
86
def __init__(self, userhost):
87
self.userhost = userhost
90
__tablename__ = 'perms'
91
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
92
perm = Column('perm', String(255), nullable=False)
93
__mapper_args__ = {'primary_key':[name,perm]}
95
def __init__(self, perm):
99
__tablename__ = 'permits'
100
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
101
permit = Column('permit', String(255), nullable=False)
102
__mapper_args__ = {'primary_key':[name,permit]}
104
def __init__(self, permit):
107
class Statuses(Base):
108
__tablename__ = 'statuses'
109
name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
110
status = Column('status', String(255), nullable=False)
111
__mapper_args__ = {'primary_key':[name,status]}
113
def __init__(self, status):
116
class Passwords(Base):
117
__tablename__ = 'passwords'
118
name = Column('name', String(255), ForeignKey('user.name'), primary_key=True)
119
passwd = Column('passwd', String(255), nullable=False)
121
def __init__(self, passwd):
126
class Birthday(Base):
127
__tablename__ = 'birthday'
128
__table_args__ = {'useexisting': True}
129
name = Column('name', String(255), primary_key=True)
130
birthday = Column('birthday', String(255), nullable=False)
132
def __init__(self, name, birthday):
134
self.birthday = birthday
140
__tablename__ = 'quotes'
141
__table_args__ = {'useexisting': True }
142
indx = Column('indx', Integer, Sequence('quotes_indx_seq', optional=True), primary_key=True)
143
quote = Column('quote', Text, nullable=False)
144
userhost = Column('userhost', String(255), ForeignKey('userhosts.userhost'), nullable=False)
145
createtime = Column('createtime', DateTime, nullable=False)
146
nick = Column('nick', String(255), nullable=False)
148
def __init__(self, quote, userhost, createtime, nick):
150
self.userhost = userhost
151
self.createtime = createtime
156
class InfoItems(Base):
157
__tablename__ = 'infoitems'
158
__table_args__ = {'useexisting': True}
159
indx = Column('indx', Integer, Sequence('infoitems_indx_seq', optional=True), primary_key=True)
160
item = Column('item', String(255), nullable=False)
161
description = Column('description', Text, nullable=False)
162
userhost = Column('userhost', String(255), ForeignKey('userhosts.userhost'), nullable=False)
163
time = Column('time', DateTime, nullable=False)
165
def __init__(self, item, description, userhost, ttime=None):
166
self.time = ttime and ttime or datetime.now()
167
self.item = item.lower()
168
self.description = description
169
self.userhost = userhost
175
__tablename__ = 'karma'
176
__table_args__ = {'useexisting': True}
177
item = Column('item', String(255), primary_key=True)
178
value = Column('value', Integer, nullable=False)
180
def __init__(self, item, value):
184
class WhyKarma(Base):
185
__tablename__ = 'whykarma'
186
__table_args__ = {'useexisting': True}
187
item = Column('item', String(255), nullable=False)
188
updown = Column('updown', String(10), nullable=False)
189
why = Column('why', Text, nullable=False)
190
__mapper_args__ = {'primary_key':[item,updown,why]}
192
def __init__(self, item, updown, why):
197
class WhoKarma(Base):
198
__tablename__ = 'whokarma'
199
__table_args__ = {'useexisting': True}
200
item = Column('item', String(255), nullable=False)
201
nick = Column('nick', String(255), nullable=False)
202
updown = Column('updown', String(10), nullable=False)
203
__mapper_args__ = {'primary_key': [item, nick, updown]}
205
def __init__(self, item, nick, updown):
216
__tablename__ = 'todo'
217
__table_args__ = {'useexisting': True}
218
indx = Column('indx', Integer, Sequence('todo_indx_seq', optional=True), primary_key=True)
219
name = Column('name', String(255), nullable=False)
220
time = Column('time', DateTime)
221
duration = Column('duration', Integer)
222
warnsec = Column('warnsec', Integer)
223
descr = Column('descr', Text, nullable=False)
224
priority = Column('priority', Integer)
226
def __init__(self, name, time, duration, warnsec, descr, priority):
229
self.duration = duration
230
self.warnsec = warnsec
232
self.priority = priority
237
__tablename__ = 'list'
238
__table_args__ = {'useexisting': True}
239
indx = Column('indx', Integer, Sequence('list_indx_seq', optional=True), primary_key=True)
240
username = Column('username', String(255), nullable=False)
241
listname = Column('listname', String(255), nullable=False)
242
item = Column('item', Text, nullable=False)
244
def __init__(self, username, listname, item):
245
self.username = username
246
self.listname = listname
252
__tablename__ = 'chatlog'
253
__table_args__ = {'useexisting': True}
254
id = Column('id', Integer, primary_key=True)
255
time = Column('time', DateTime, nullable=False, default=datetime.now)
256
network = Column('network', String(256), nullable=False, default='')
257
target = Column('target', String(256), nullable=False, default='')
258
nick = Column('nick', String(256), nullable=False, default='')
259
type = Column('type', String(256), nullable=False, default='')
260
message = Column('message', Text, nullable=False, default='')
262
def __init__(self, **kwargs):
263
for k, v in kwargs.items():