Some people have asked so here is a prototype of the new DAL. It is
far come complete. The tests at the end work. It cannot jet be
integrated in web2py because the part for auto-closing transactions if
missing.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
import os
import datetime
import logging
logging.level=0
drivers=[]
try:
from pysqlite2 import dbapi2 as sqlite3
drivers.append('pysqlite2')
except:
try:
import sqlite3
drivers.append('SQLite3')
except:
logging.debug('no sqlite3 or pysqlite2.dbapi2 driver')
class AdaptorSQLite:
name = 'sqlite'
def __init__(self,db,uri):
"""
connects to database and allocates
self.connection
self.cursor
"""
(self._db,self.uri)=(db,uri)
def execute(self,command):
logging.info('web2py.gluon.AdaptorSQLite:'+command)
if isinstance(command, str): self.cursor.execute(command)
else: raise SyntaxError, "invalid command:, ", repr(command)
def quote(self,value):
return "'%s'" % value.replace("'","''")
def r(self,value,type='s'):
if isinstance(value,Field):
return value._truefullname
if isinstance(value,(Query, Expression)):
return value.command
if value==None:
return 'NULL'
if isinstance(value, unicode):
value = value.encode('utf-8')
if type[0] == 'i':
return str(int(value))
if type[0] == 'd':
return str(float(value))
if type[0] == 'r':
return str(int(value))
if isinstance(value,(datetime.date,datetime.datetime,datetime.time)):
return "'%s'" % value.isoformat()
if type=='boolean':
if value and not str(value).upper()[0] in ('0','F'): return 'T'
return 'F'
if type=='blob':
value = base64.b64encode(str(value))
if not isinstance(value,str):
value=str(value)
return self.quote(value)
def migrate(self,table):
"""
creates or alters the table as requires
"""
self.execute(self.CREATE(table)) ### do migrations here
self.COMMIT()
def lastrowid(self,table):
return self.cursor.lastrowid
def rowcount(self):
return self.cursor.rowcount
def insert(self,table,**fields):
[self.execute(q) for q in self.INSERT(table,**fields)]
return self.lastrowid(table)
def drop(self,table):
[self.execute(q) for q in self.DROP(table)]
def truncate(self,table):
[self.execute(q) for q in self.TRUNCATE(table)]
def count(self,query):
[self.execute(q) for q in self.COUNT(query)]
return self.cursor.fetchone()[0]
def update(self,query,**fields):
[self.execute(q) for q in self.UPDATE(query,**fields)]
return self.rowcount()
def select(self,query,*fields,**args):
items=self.SELECT(query,*fields,**args)
[self.execute(q) for q in items[1:]]
response=self.convert(self.cursor.fetchall(),items[0])
return Rows(self.db,response,items[0])
def convert(self,items,columns):
nc=range(len(columns))
for i in range(len(items)):
item=items[i]
newitem=[]
for j in nc:
value=item[j]
column=columns[j]
### ADD MAMBO JUMBO
newitem.append(value)
items[i]=tuple(newitem)
return items
def BEGIN(self):
pass
def AND(self,first,second):
return '(%s) AND (%s)' % (self.r(first),self.r(second))
def OR(self,first,second):
return '(%s) OR (%s)' % (self.r(first),self.r(second))
def NOT(self,first,second):
return 'NOT (%s)' % (self.r(first))
def COMMA(self,first,second):
return '%s, %s' % (self.r(first),self.r(second))
def DESC(self,first):
return '%s DESC' % (self.r(first))
def EQ(self,first,second):
return '%s = %s' % (self.r(first),self.r(second,first.type))
def NEQ(self,first,second):
return '%s <> %s' % (self.r(first),self.r(second,first.type))
def LT(self,first,second):
return '%s < %s' % (self.r(first),self.r(second,first.type))
def GT(self,first,second):
return '%s > %s' % (self.r(first),self.r(second,first.type))
def LE(self,first,second):
return '%s <= %s' % (self.r(first),self.r(second,first.type))
def GE(self,first,second):
return '%s >= %s' % (self.r(first),self.r(second,first.type))
def ADD(self,first,second):
return '%s + %s' % (self.r(first),self.r(second,first.type))
def SUM(self,first,second):
return '%s - %s' % (self.r(first),self.r(second,first.type))
def MUL(self,first,second):
return '%s * %s' % (self.r(first),self.r(second,first.type))
def DIV(self,first,second):
return '%s / %s' % (self.r(first),self.r(second,first.type))
def LIKE(self,first,second):
return '%s LIKE %s' % (self.r(first),self.r(second))
def BELONGS(self,first,second):
return '%s IN %s' % (self.r(first),self.r(second))
def SUM(self,first):
return 'SUM(%s)' % (self.r(first))
def MIN(self,first):
return 'MIN(%s)' % (self.r(first))
def MAX(self,first):
return 'MAX(%s)' % (self.r(first))
def COUNT(self,first):
return 'COUNT(%s)' % (self.r(first))
def YEAR(self,first):
return "EXTRACT('year',%s)" % (self.r(first))
def MONTH(self,first):
return "EXTRACT('month',%s)" % (self.r(first))
def DAY(self,first):
return "EXTRACT('day',%s)" % (self.r(first))
def HOUR(self,first):
return "EXTRACT('hour',%s)" % (self.r(first))
def MINUTE(self,first):
return "EXTRACT('minute',%s)" % (self.r(first))
def SECOND(self,first):
return "EXTRACT('second',%s)" % (self.r(first))
def UPPER(self,first):
return 'UPPER(%s)' % (self.r(first))
def LOWER(self,first):
return 'LOWER(%s)' % (self.r(first))
def ORDERBY(self,query,first):
return '%s ORDER BY %s' % (query, self.r(first))
def GROUPBY(self,query,first):
return '%s GROUP BY %s' % (query, self.r(first))
def LIMITBY(self,query,mimimum,maximum):
return '%s LIMIT %s OFFSET %s' % (query, maximum-minimum,minimum+1)
def ISNULL(self):
return 'IS NULL'
def ISNOTNULL(self):
return 'IS NOT NULL'
def NOTNULL(self,default):
return 'NOT NULL DEFAULT %s' % self.r(default)
def UNIQUE(self):
return 'UNIQUE'
def SUBSTR(self,first,start,stop):
return 'SUBSTR(%s,%s,%s)' % (self.r(first),start+1,stop-start)
def RANDOM(self):
return 'RANDOM()'
def INSERT(self,table,**fields):
keys = ', '.join(fields.keys())
values = ', '.join([self.r(v,table[k].type) for (k,v) in fields.items()])
return ('INSERT INTO %s(%s) VALUES(%s);' % (table._tablename,keys,values),)
def DROP(self,table):
return ('DROP TABLE %s;' % table._tablename,)
def TRUNCATE(self,table,mode=None):
if self.name == 'sqlite':
return ('DELETE FROM %s;' % table._tablename,
"DELETE FROM sqlite_sequence WHERE name='%s';" \
% table._tablename)
return ('TRUNCATE %s%s;' % (table._tablename, (mode and ' '+mode) or ''),)
def COUNT(self,query):
tables=', '.join(query._tbs) ### WAY THSES ARE NOT REALNAMES
command = query.command and ' WHERE '+query.command or ''
return ('SELECT COUNT(*) FROM %s%s;' % (tables,command),)
def UPDATE(self,query,**fields):
if len(query._tbs)!=1: raise SyntaxError, "You cannot update a join"
s=', '.join(['%s=%s' % (k,self.r(v)) for k,v in fields.items()])
command = query.command and ' WHERE '+query.command or ''
return ('UPDATE %s SET %s%s;' % (query._tbs[0],s,command),)
def SELECT(self,query,*fields,**args):
### FIX THIS TO CONSIDER ALIAS AND TRUENAMES
if not fields: fields=[query._db[t].ALL for t in query._tbs]
newfields=[]
for field in fields:
if isinstance(field,list): newfields.extend(field)
elif isinstance(field,str): newfields.append(field)
else: newfields.append(self.r(field))
tables=[field.split('.')[0] for field in newfields]
fieldnames=', '.join(newfields)
if query._tbs: tables=list(set(tables).union(set(query._tbs)))
##### WAIT, NOT REAL NAMES
command = query.command and ' WHERE '+query.command or ''
return (newfields, 'SELECT %s FROM %s%s;' % (fieldnames,tables[0],command))
def CREATE(self,table):
items=[]
for fieldname in table.fields:
field=table[fieldname]
items.append('%s %s' % (field._truename,
self.FIELD_TYPES[field.type] % field.__dict__))
sql='CREATE TABLE %s (\n\t%s\n);' % (table._truename,',\n\t'.join(items))
return sql
def COMMIT(self):
self.connection.commit()
def ROLLBACK(self):
self.connection.rollback()
def CLOSE(self):
self.connection.close()
FIELD_TYPES = {
'boolean': 'CHAR(1)',
'string': 'CHAR(%(length)s)',
'text': 'TEXT',
'password': 'CHAR(%(length)s)',
'blob': 'BLOB',
'upload': 'CHAR(128)',
'integer': 'INTEGER',
'double': 'DOUBLE',
'date': 'DATE',
'time': 'TIME',
'datetime': 'TIMESTAMP',
'id': 'INTEGER PRIMARY KEY AUTOINCREMENT',
'reference': 'REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s',
}
@staticmethod
def extract(lookup, s):
table = {
'year': (0, 4), 'month': (5, 7), 'day': (8, 10),
'hour': (11, 13), 'minute': (14, 16), 'second': (17, 19),
}
try:
(i, j) = table[lookup]
return int(s[i:j])
except:
return None
def __init__(self,db,uri,pool_size=0,folder='./'):
self.db=db
self.uri = uri
self.pool_size = pool_size
self.folder = folder
path = uri[9:]
if path[0]!='/': path = os.path.join(folder,path)
#self.pool_connection(lambda : sqlite3.Connection(path,
# check_same_thread=False))
self.connection=sqlite3.Connection(path)
self.connection.create_function('EXTRACT', 2, self.extract)
self.cursor = self.connection.cursor()
#self.register_instance()
self.BEGIN()
class Storage(dict):
def __getattr__(self,key):
return self.get(key,None)
def __setattr__(self,key,value):
dict.__setattr__(self,key,value)
class DB(Storage):
adaptors={
'sqlite':AdaptorSQLite,
#'mysql':AdaptorMySQL,
#'postgres':AdaptorPostgres,
#'mssql':AdaptorMSSQL,
#'firebird':AdaptorFireBird,
#'oracle':AdaptorOracle,
#'informix':AdaptorInformix,
#'db2':AdaptorDB2,
}
def __init__(self, uri='sqlite://storage.sqlite'):
adaptor=DB.adaptors.get(uri.split('://')[0],None)
if not adaptor: raise SyntaxError, "database not supported"
self._adaptor=adaptor(self,uri)
self.tables=[]
def define_table(self,name,*fields,**args):
self[name]=table=Table(self,name,*fields,**args)
self._adaptor.migrate(table)
self.tables.append(table._tablename)
def __call__(self,query=None):
if not query: return Query(None,self,[])
return query
def commit(self):
self._adaptor.COMMIT()
def rollback(self):
self._adaptor.ROLLBACK()
def close(self):
self._adaptor.CLOSE()
def __str__(self):
return self._adaptor.uri
class Table(Storage):
def __init__(self,db,name,*fields,**args):
self._db=db
self._tablename=name
self._truename=args.get('real_name',name)
self._args=args
fields=[Field('id','id')]+list(fields)
self.fields=[field._name for field in fields]
self.ALL=[]
for field in fields:
self.__dict__[field._name]=field
field._tablename=self._tablename
field._tbs=[field._tablename]
field._fullname=self._tablename+'.'+field._name
field._truefullname=self._truename+'.'+field._truename
field._db=db
self.ALL.append(field._fullname)
def with_alias(self,name):
### this changes the name of tables as they apper in fields realnames
### and SELECT tables
pass
def _insert(self,**fields):
return self._db._adaptor.INSERT(self,**fields)
def insert(self,**fields):
return self._db._adaptor.insert(self,**fields)
def _drop(self):
return self._db._adaptor.DROP(self)
def drop(self):
return self._db._adaptor.drop(self)
def _truncate(self):
return self._db._adaptor.TRUNCATE(self)
def truncate(self):
return self._db._adaptor.truncate(self)
def __getitem__(self,id):
try:
id=int(id)
except:
return self.__dict__[id]
else:
rows=self._db(self.id==id).select()
if rows: return rows[0]
return None
def __setitem__(self,id,fields):
self._db(self.id==id).update(**fields)
def __delitem__(self,id):
self._db(self.id==id).delete()
def __str__(self):
return self._tablename
class Expression(object):
def __init__(self,command,db=None,tbs=[]):
(self.command,self._db,self._tbs)=(command,db,tbs)
def __or__(self,other):
return Expression(self._db._adaptor.COMMA(self,other),self._db,self._tbs)
def __invert__(self):
return Expression(self._db._adaptor.DESC(self),self._db,self._tbs)
def __eq__(self,other):
return Query(self._db._adaptor.EQ(self,other),self._db,self._tbs)
def __ne__(self,other):
return Query(self._db._adaptor.NEQ(self,other),self._db,self._tbs)
def __lt__(self,other):
return Query(self._db._adaptor.LT(self,other),self._db,self._tbs)
def __gt__(self,other):
return Query(self._db._adaptor.GT(self,other),self._db,self._tbs)
def __le__(self,other):
return Query(self._db._adaptor.LE(self,other),self._db,self._tbs)
def __gr__(self,other):
return Query(self._db._adaptor.GE(self,other),self._db,self._tbs)
def like(self,other):
return Query(self._db._adaptor.LIKE(self,other),self._db,self._tbs)
def belongs(self,other):
return Query(self._db._adaptor.BELONGS(self,other),self._db,self._tbs)
def __add__(self,other):
return Query(self._db._adaptor.ADD(self,other),self._db,self._tbs)
def __sub__(self,other):
return Query(self._db._adaptor.SUB(self,other),self._db,self._tbs)
def __mul__(self,other):
return Query(self._db._adaptor.MUL(self,other),self._db,self._tbs)
def __div__(self,other):
return Query(self._db._adaptor.DIV(self,other),self._db,self._tbs)
def __str__(self):
return self.command
class Query(object):
def __init__(self,command,db=None,tbs=[]):
(self.command,self._db,self._tbs)=(command,db,tbs)
def __and__(self,other):
return Query(self._db._adaptor.AND(self,other),self._db,self._tbs)
def __or__(self,other):
return Query(self._db._adaptor.OR(self,other),self._db,self._tbs)
def __invert__(self,other):
return Query(self._db._adaptor.NOT(self),self._db,self._tbs)
def __call__(self,other):
return Query(self._db._adaptor.AND(self,other),self._db,self._tbs)
def _count(self):
return self._db._adaptor.COUNT(self)
def count(self):
return self._db._adaptor.count(self)
def _update(self,**fields):
return self._db._adaptor.UPDATE(self,**fields)
def update(self,**fields):
return self._db._adaptor.update(self,**fields)
def _select(self,*fields,**args):
return self._db._adaptor.SELECT(self,*fields,**args)[1:]
def select(self,*fields,**args):
return self._db._adaptor.select(self,*fields,**args)
def __str__(self):
return self.command
class Field(Expression):
def __init__(self,name,type='string',**args):
self._db=None
self._name=name
self.type=type
self.args=args ### FIX THIS
self._truename=args.get('truename',name)
self.length=128
def sum(self):
return Expression(self._db._adaptor.SUM(self),self._db,self._tbs)
def count(self):
return Expression(self._db._adaptor.COUNT(self),self._db,self._tbs)
def max(self):
return Expression(self._db._adaptor.MAX(self),self._db,self._tbs)
def min(self):
return Expression(self._db._adaptor.MIN(self),self._db,self._tbs)
def year(self):
return Expression(self._db._adaptor.YEAR(self),self._db,self._tbs)
def month(self):
return Expression(self._db._adaptor.MONTH(self),self._db,self._tbs)
def day(self):
return Expression(self._db._adaptor.DAY(self),self._db,self._tbs)
def hour(self):
return Expression(self._db._adaptor.HOUR(self),self._db,self._tbs)
def minute(self):
return Expression(self._db._adaptor.MINUTE(self),self._db,self._tbs)
def second(self):
return Expression(self._db._adaptor.SECOND(self),self._db,self._tbs)
def upper(self):
return Expression(self._db._adaptor.UPPER(self),self._db,self._tbs)
def lower(self):
return Expression(self._db._adaptor.LOWER(self),self._db,self._tbs)
def __str__(self):
return self._fullname
class Row:
def __init__(self,items,columns):
self._items=items
self._columns=columns
#self._parents=parents
self._rn=range(len(columns))
self._extra=dict([(self._columns[j],self._items[j]) for j in self._rn])
def __getitem__(self,column):
return self._extra[str(column)]
def __str__(self):
return str(self._extra)
class Rows:
def __init__(self,db,response,columns):
self.db=db
self.response=response
self.columns=columns
#self.parents=list(set([x.split('.')[0] for x in columns]).intersection(db.tables))
def __getitem__(self,i):
return Row(self.response[i],self.columns)
def __iter__(self):
for i in range(len(self.response)):
yield self[i]
def __getslice__(self,i,j):
return Rows(self.response[i:j],self.columns)
def __nonzero__(self):
return self.response!=[]
def __len__(self):
return len(self.response)
def __str__(self):
## FIX THIS!
return ',\n'.join([str(line) for line in self])
#
# Compatibility Layer
#
SQLDB=DB
SQLField=Field
SQLTable=Table
#
# Tests
#
def test():
try: os.unlink('storage.sqlite')
except: pass
db=SQLDB('sqlite://storage.sqlite')
db.define_table('person',SQLField('name'),SQLField('age','integer'))
print db['person']
print db['person']['name']
print db.person.ALL
print db.person.fields
print db.person.insert(name='Massimo',age=37)
print db.person.insert(name='Claudia',age=36)
print db.person.insert(name='Marco',age=4)
print db.person.name.like('M%').count()
print db(db.person.id==1).update(age=db.person.age+1)
for row in db((db.person.id>0)&(db.person.id<10)).select():
print row, row['person.id']
print db((db.person.id>0)&(db.person.id<10)).select(db.person.ALL,
db.person.age.sum())
print db().select(db.person.ALL)
db.person.truncate()
db.person.drop()
db.commit()
db.close()
test()
"""
sqlite> pragma table_info(people);
0|first_name|varchar|0||0
1|last_name|varchar|0||0
2|email_address|varchar|0||0
mysql>SHOW COLUMNS FROM City;
postgresql> SELECT column_name FROM information_schema.columns WHERE table_name ='table';
oracle>SELECT * FROM tabs;
firebird>show table City;
reflection
new is the new description of table
old is the old description of table
table is the description of table as is exists in db
if not table:
if not old:
create table
create old
else:
for field in old not in new:
FYI: Some issues to note as you are rewriting the DAL:
1. For mssql connections, I want to write:
db = SQLDB('mssql://dlypka:p...@word1@localhost/TMoMilestones')
The '@' delimiter is a problem for me because '@' is a legal, often
used character in SQL Server passwords
So please at least change the regular expression in sql.py line 768 to
allow enclosing the password in perhaps square brackets
So for example, it would allow
db = SQLDB('mssql://dlypka: [p...@word1]@localhost/TMoMilestones')
The re at line 768 is
re.compile('^(?P<user>[^:@]+)(\:(?P<passwd>[...@]
*))?
@(?P<host>[^\:/]+)(\:(?P<port>[0-9]+))?/(?P<db>.+)$'
).match(self._uri[skip:])
2. Please change sql.py to use pymssql rather than pyodbc
pymssql Version 1.0.2 was released on 2009-04-28. It has major bug
fixes and works well now.
I got it from herehttp://pymssql.sourceforge.net/
I suggest changing sql.py line 791:
# self._pool_connection(lambda : pyodbc.connect(cnxn))
self._pool_connection(lambda : pymssql.connect(cnxn)
3. Please provide a way to specify options such as trusted=True
For example, I need to call it this way:
self._pool_connection(lambda : pymssql.connect
(host='',trusted=True,database=db))
Thank you.
- Dave Lypka.
"""
