Hello,
I've been working on adding the ability to create tables easily on any
database to Dabo.
I have created a diff and put it at the end of this email. It fully
works with SQLite. I haven't started on any of the other databases
yet.
So I was wondering if the way I created it was good or not. I would
like to know your opinions before I started the support for other
databases.
--
-Echo
Here is the diff:
Index: dabo/db/__init__.py
===================================================================
--- dabo/db/__init__.py (revision 1873)
+++ dabo/db/__init__.py (working copy)
@@ -30,3 +30,4 @@
from dCursorMixin import dCursorMixin
from dMemento import dMemento
from dConnectInfo import dConnectInfo
+from dTable import dTable
\ No newline at end of file
Index: dabo/db/dBackend.py
===================================================================
--- dabo/db/dBackend.py (revision 1873)
+++ dabo/db/dBackend.py (working copy)
@@ -364,7 +364,19 @@
auxCrs.execute(tmpsql)
return auxCrs.FieldDescription
+ def CreateJustTable(self, tabledef, cursor):
+ self.CreateTableAndIndex(tabledef, cursor, CreateIndexes=False)
+
+ def CreateJustIndexes(self, tabledef, cursor):
+ self.CreateTableAndIndexes(tabledef, cursor, CreateTable=False)
+ def CreateTableAndIndexes(self, tabledef, cursor, CreateTable=True,
CreateIndex=True):
+ """Creates a table and/or indexes based on the dTable passed to
it.
+ """
+
+ # OVERRIDE IN SUBCLASSES!
+ pass
+
###########################################
# The following methods by default simply return the text
# supplied to them. If a particular backend (Firebird comes
Index: dabo/db/dbSQLite.py
===================================================================
--- dabo/db/dbSQLite.py (revision 1873)
+++ dabo/db/dbSQLite.py (working copy)
@@ -19,9 +19,12 @@
return self.dbapi.Cursor
def escQuote(self, val):
+ if val is None:
+ return self.formatNone()
+
sl = "\\"
qt = "\'"
- return qt + val.replace(sl, sl+sl).replace(qt, qt+qt) + qt
+ return qt + str(val).replace(sl, sl+sl).replace(qt, qt+qt) + qt
def formatDateTime(self, val):
""" We need to wrap the value in quotes. """
@@ -165,3 +168,68 @@
fields.append( (rec["name"], fldType, False))
ret = tuple(fields)
return ret
+
+ def CreateTableAndIndexes(self, tabledef, cursor, CreateTable=True,
CreateIndexes=True):
+ if tabledef.Name == '':
+ raise
+
+ #Create the table
+ if CreateTable == True:
+ if tabledef.IsTemp == False:
+ sql = 'CREATE TABLE '
+ else:
+ sql = 'CREATE TEMP TABLE '
+
+ sql = sql + tabledef.Name + ' ('
+
+ for f in tabledef.Fields:
+ sql = sql + f.Name + ' '
+
+ if f.DataType == 'Numeric':
+ sql = sql + 'INTEGER '
+ if f.IsPK:
+ sql = sql + 'PRIMARY KEY '
+ if f.IsAutoIncrement == True:
+ sql = sql +
'AUTOINCREMENT '
+ elif f.DataType == 'Float':
+ sql = sql + 'REAL '
+ elif f.DataType == 'String':
+ sql = sql + 'TEXT '
+ elif f.DataType == 'Date':
+ sql = sql + 'TEXT '
+ elif f.DataType == 'Time':
+ sql = sql + 'TEXT '
+ elif f.DataType == 'DateTime':
+ sql = sql + 'TEXT '
+ elif f.DataType == 'Stamp':
+ sql = sql + 'TEXT '
+ elif f.DataType == 'Binary':
+ sql = sql + 'BLOB '
+
+ if f.AllowNulls == False:
+ sql = sql + 'NOT NULL '
+
+ sql = sql + 'DEFAULT ' +
self.escQuote(f.Default) + ','
+
+ if sql[-1:] == ',':
+ sql = sql[:-1]
+ sql = sql + ')'
+
+ cursor.execute(sql)
+
+ if CreateIndexes == True:
+ #Create the indexes
+ for i in tabledef.Indexes:
+ if i.Name.lower() != 'primary':
+ sql = 'CREATE INDEX ' + i.Name + ' ON '
+ tabledef.Name + '('
+
+ for f in i.Fields:
+ sql = sql + f + ','
+
+ if sql[-1:] == ',':
+ sql = sql[:-1]
+ sql = sql + ')'
+
+ cursor.execute(sql)
+
+
\ No newline at end of file
Index: dabo/db/dCursorMixin.py
===================================================================
--- dabo/db/dCursorMixin.py (revision 1873)
+++ dabo/db/dCursorMixin.py (working copy)
@@ -1335,6 +1335,20 @@
ret =
self.BackendObject.rollbackTransaction(self.AuxCursor)
return ret
+ def CreateTable(self, tabledef):
+ """Create a table based on the table definition.
+ """
+ self.BackendObject.CreateJustTable(tabledef, self)
+
+ def CreateIndexes(self, tabledef):
+ """Create indexes based on the table definition.
+ """
+ self.BackendObject.CreateJustIndexes(tabledef, self)
+
+ def CreateTableAndIndexes(self, tabledef):
+ """Create a table and its indexes based on the table definition.
+ """
+ self.BackendObject.CreateTableAndIndexes(tabledef, self)
### SQL Builder methods ########
def getFieldClause(self):
Index: dabo/db/dTable.py
===================================================================
--- dabo/db/dTable.py (revision 0)
+++ dabo/db/dTable.py (revision 0)
@@ -0,0 +1,325 @@
+from dabo.dLocalize import _
+import dabo.dException as dException
+from dabo.dObject import dObject
+
+class dTable(dObject):
+ """This class is used to hold information about a table so it can be
created on any database.
+
+ For example:
+ To define a temporary table named 'mytemp' that has ? fields,
where
the fields are:
+ field 1: 'theid', it is an autoincrementing field that
uses a 2 byte integer
+ field 2: 'first_name', it is a string field that has a
max of 25
charactors, part of an indexes 'idx_first' and 'idx_name'
+ field 3: 'last_name', it is a string field that has a
max of 25
charactors, NULL's are not allowed,
+ part of an indexes
'idx_last' and 'idx_name'
+ field 4: 'amount_owes', it is a float that has a
percision of 2
and uses 8 bytes, the default is 0
+
+ Code:
+ from dabo.db import dTable
+
+ mytable = dTable(Name='mytemp', IsTemp=True)
+ mytable.AddField(Name='theid', IsPK=True,
DataType='int', Size=2,
IsAutoIncrement=True)
+ mytable.AddField(Name='first_name', DataType='string',
Size=25,
Index='idx_first')
+ mytable.AddField(Name='last_name', DataType='string',
Size=25,
AllowNulls=False, Index='idx_last')
+ mytable.AddField(Name='amount_owes', DataType='float',
Percision=2, Size=8, Default=0)
+
+ #When you want to have more than one field in an index,
use AddIndex().
+ mytable.AddIndex(Name='idx_name',
Fields=('last_name','first_name'))
+
+ """
+ def __init__(self, *args, **kwargs):
+ self._baseClass = dTable
+
+ self._name = ''
+ self._is_temp = False
+ self._fields = []
+ self._indexes = []
+ self._pk = None
+ dTable.doDefault(*args, **kwargs)
+
+ def __str__(self):
+ t = ''
+
+ t = t + 'Table Name: ' + self._name
+ if self._is_temp == True:
+ t = t + '\n is TEMPOARY\n'
+
+ t = t + '\nFields:\n'
+ for f in self._fields:
+ t = t + ' ' + str(f) + '\n'
+
+ t = t + '\nIndexes:\n'
+ for i in self._indexes:
+ t = t + ' ' + str(i) + '\n'
+
+ return t
+
+ def AddField(self, *args, **kwargs):
+ """Add a field to the table.
+
+ Properties:
+
+ """
+
+ #Check if adding an index
+ try:
+ i = kwargs['Index']
+ name = kwargs['Name']
+ except:
+ pass
+ else:
+ self._indexes.append(dIndex(Name=i,Fields=name))
+ del kwargs['Index']
+
+ #Check if setting PK
+ try:
+ pk = kwargs['IsPK']
+ name = kwargs['Name']
+ except:
+ pass
+ else:
+ if pk == True:
+ self._pk = name
+
+ self._fields.append(dField(*args, **kwargs))
+
+ def AddIndex(self, *args, **kwargs):
+ """Add an index to the table.
+ """
+ self._indexes.append(dIndex(*args, **kwargs))
+
+ def _SetName(self, name):
+ self._name = name
+ def _GetName(self):
+ return self._name
+
+ def _SetIsTemp(self, value):
+ self._is_temp = value
+ def _GetIsTemp(self):
+ return self._is_temp
+
+ def _GetFields(self):
+ return self._fields
+
+ def _GetIndexes(self):
+ return self._indexes
+
+ def _GetPK(self):
+ return self._pk
+
+ Name = property(_GetName, _SetName, None, _('The name of the table.
(str)'))
+ IsTemp = property(_GetIsTemp, _SetIsTemp, None, _('Weather or not
the table is temporary. (bool)'))
+ Fields = property(_GetFields, None, None, _('List of the fields in
the table. (list)'))
+ Indexes = property(_GetIndexes, None, None, _('List of the indexes
in the table. (list)'))
+ PK = property(_GetPK, None, None, _('The primary key of the table.
(str)'))
+
+class dIndex(dObject):
+ def __init__(self, *args, **kwargs):
+ self._baseClass = dIndex
+
+ self._name = ''
+ self._fields = None
+
+ dIndex.doDefault(*args, **kwargs)
+
+ def __str__(self):
+ t = self._name + ' ('
+
+ for f in self._fields:
+ t = t + f + ','
+
+ if t[-1:] == ',':
+ t = t[:-1]
+
+ return t + ')'
+
+ def _SetName(self, name):
+ self._name = name
+ def _GetName(self):
+ return self._name
+
+ def _SetFields(self, fields):
+ if isinstance(fields, str):
+ f = fields.split()
+ self._fields = tuple(f)
+ elif isinstance(fields, list):
+ self._fields = tuple(f)
+ else:
+ self._fields = fields
+ def _GetFields(self):
+ return self._fields
+
+ Name = property(_GetName, _SetName)
+ Fields = property(_GetFields, _SetFields)
+
+class dField(dObject):
+ def __init__(self, *args, **kwargs):
+ self._baseClass = dField
+
+ self._name = ''
+ self._type = fType()
+ self._allow_nulls = True
+ self._default = None
+ self._autoincrement = False
+ self._pk = False
+ dField.doDefault(*args, **kwargs)
+
+ def __str__(self):
+ if self._allow_nulls:
+ allowednulls = 'Nulls Allowed'
+ else:
+ allowednulls = 'Nulls Not Allowed'
+
+ if self._autoincrement:
+ autoi = ' Auto Incrementing'
+ else:
+ autoi = ''
+
+ if self._pk:
+ pk = ' PK'
+ else:
+ pk = ''
+
+ return '%s%s (%s, Size:%i, Percision:%i)%s %s Default:%s' %
(self._name, pk, self._type.DataType, self._type.Size,
self._type.Percision, autoi, allowednulls, self._default)
+
+ def _SetName(self, name):
+ self._name = name
+ def _GetName(self):
+ return self._name
+
+ def _SetType(self, type):
+ self._type = type
+ def _GetType(self):
+ return self._type
+
+ def _SetDataType(self, datatype):
+ self._type.DataType = datatype
+ def _GetDataType(self):
+ return self._type.DataType
+
+ def _SetSize(self, size):
+ self._type.Size = size
+ def _GetSize(self):
+ return self._type.Size
+
+ def _SetPercision(self, value):
+ self._type.Percision = value
+ def _GetPercision(self):
+ return self._type.Percision
+
+ def _SetAllowNulls(self, allow):
+ self._allow_nulls = allow
+ def _GetAllowNulls(self):
+ return self._allow_nulls
+
+ def _SetDefault(self, default):
+ self._default = default
+ def _GetDefault(self):
+ return self._default
+
+ def _SetAutoIncrement(self, auto):
+ self._autoincrement = auto
+ def _GetAutoIncrement(self):
+ return self._autoincrement
+
+ def _SetPK(self, value):
+ self._pk = value
+ def _GetPK(self):
+ return self._pk
+
+ Name = property(_GetName, _SetName, None, _('The name of the table.
(str)'))
+ Type = property(_GetType, _SetType, None, _('The type of the column.
(class)'))
+ DataType = property(_GetDataType, _SetDataType, None, _('The type of
the column. (str)'))
+ Size = property(_GetSize, _SetSize, None, _('The size required for
the column in bytes. (int)'))
+ Percision = property(_GetPercision, _SetPercision, None, _('The
number of decimal places to the right of the period. (int)'))
+ AllowNulls = property(_GetAllowNulls, _SetAllowNulls, None,
_('Weather or not nulls are allowed. Default:True (bool)'))
+ Default = property(_GetDefault, _SetDefault, None, _('The default
value for the field. Default:None (str)'))
+ IsAutoIncrement = property(_GetAutoIncrement, _SetAutoIncrement,
None, _('Weather or not the field is an auto incrementing field.
Default:False (bool)'))
+ IsPK = property(_GetPK, _SetPK, None, _('Weather or not the field
has the primary key. (bool)'))
+
+class fType(dObject):
+ """Dabo DB Field Type - Used to hold the information about types of
fields in any database.
+ """
+ def __init__(self, *args, **kwargs):
+ self._baseClass = fType
+
+ self._data_type = 'Numeric'
+ self._size = 1
+ self._percision = 0
+
+ fType.doDefault(*args, **kwargs)
+
+ def _SetDataType(self, datatype):
+ """Allowed types:
+ Numeric
+ Float
+ String
+ Date
+ Time
+ DateTime
+ Stamp
+ Binary
+ """
+
+ check = {'numeric': 'Numeric',
+ 'int': 'Numeric',
+ 'integer': 'Numeric',
+
+ 'float': 'Float',
+ 'decimal': 'Float',
+
+ 'string': 'String',
+ 'varchar': 'String',
+ 'char': 'String',
+
+ 'date': 'Date',
+
+ 'time': 'Time',
+
+ 'datetime': 'DateTime',
+
+ 'stamp': 'Stamp',
+
+ 'binary': 'Binary',
+ }
+
+ self._data_type = check[datatype.lower()]
+ def _GetDataType(self):
+ return self._data_type
+
+ def _SetSize(self, size):
+ self._size = size
+ def _GetSize(self):
+ return self._size
+
+ def _SetPercision(self, percision):
+ self._percision = percision
+ def _GetPercision(self):
+ return self._percision
+
+ DataType = property(_GetDataType, _SetDataType)
+ Size = property(_GetSize, _SetSize)
+ Percision = property(_GetPercision, _SetPercision)
+
+
+if __name__ == '__main__':
+ print '\n\nstarting\n'
+
+ #type = fType(DataType='String', Size=25)
+ #print type.getProperties(('DataType','Size'))
+
+ #col = dField(Name='colname', Type=fType(DataType='String',Size=50))
+ #print col
+ #col = dField(Name='colname', DataType='String', Size=50)
+ #print col
+
+ mytable = dTable(Name='mytemp', IsTemp=True)
+ mytable.AddField(Name='theid', IsPK=True, DataType='int', Size=2,
IsAutoIncrement=True)
+ mytable.AddField(Name='first_name', DataType='string', Size=25,
Index='idx_first')
+ mytable.AddField(Name='last_name', DataType='string', Size=25,
AllowNulls=False, Index='idx_last')
+ mytable.AddField(Name='amount_owes', DataType='float', Percision=2,
Size=8, Default=0)
+
+ #When you want to have more than one field in an index, use AddIndex().
+ mytable.AddIndex(Name='idx_name', Fields=('last_name','first_name'))
+
+ print mytable
+
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
