dabo Commit
Revision 2567
Date: 2006-12-20 10:02:12 -0800 (Wed, 20 Dec 2006)
Author: Carl
Changed:
U branches/carl/dabo/db/dbMySQL.py
Log:
refactord getFields() and related functions
Diff:
Modified: branches/carl/dabo/db/dbMySQL.py
===================================================================
--- branches/carl/dabo/db/dbMySQL.py 2006-12-20 17:32:34 UTC (rev 2566)
+++ branches/carl/dabo/db/dbMySQL.py 2006-12-20 18:02:12 UTC (rev 2567)
@@ -1,3 +1,5 @@
+# dabo/db/dbMySQL.py
+
import datetime
try:
import decimal
@@ -97,99 +99,118 @@
tempCursor.execute("select count(*) as ncount from %s" %
tableName)
return tempCursor.fetchall()[0][0]
+ """
+Note about data types: MySQL (and proably others) support synonyms:
+"The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym
for DECIMAL."
+and many others.
+mysql> create table foo ( f1 numeric(3,1), f2 decimal(3,1) );
+
+mysql> SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION,NUMERIC_SCALE FROM
INFORMATION_SCHEMA.COLUMNS WHERE table_name =
'foo';+-------------+-----------+-------------------+---------------+
+| COLUMN_NAME | DATA_TYPE | NUMERIC_PRECISION | NUMERIC_SCALE |
++-------------+-----------+-------------------+---------------+
+| f1 | decimal | 3 | 1 |
+| f2 | decimal | 3 | 1 |
+
+mysql> show create table foo;
+| Table | Create Table
|
++-------+--------------------------------------------------------------------------------------------------------------------------------+
+| foo | CREATE TABLE `foo` (
+ `f1` decimal(3,1) default NULL,
+ `f2` decimal(3,1) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+
+Point is, just because the server will support it in CREATE,
+we don't need to support it here. we just need to support the ...native? ones.
+
+"""
+
+ def _fieldTypeNativeToDabo(self, nativeType):
+ try:
+ ret = {
+ "BINARY": "I",
+ "BIT": "I",
+ "BIGINT": "I",
+ "BLOB": "M",
+ "CHAR": "C",
+ "DATE": "D",
+ "DATETIME": "T",
+ "DECIMAL": "N",
+ "DOUBLE": "G",
+ "ENUM": "C",
+ "FLOAT": "F",
+ "GEOMETRY": "?",
+ "INT": "I",
+ "INTERVAL": "?",
+ "LONG": "G",
+ "LONGBLOB": "M",
+ "LONGTEXT": "M",
+ "MEDIUMBLOB": "M",
+ "MEDIUMINT": "I",
+ "MEDIUMTEXT": "M",
+ "NEWDATE": "?",
+ "NULL": "?",
+ "SET": "?",
+ "SHORT": "I",
+ "SMALLINT": "I",
+ "STRING": "C",
+ "TEXT": "M",
+ "TIME": "?",
+ "TIMESTAMP": "T",
+ "TINYINT": "I",
+ "TINYBLOB": "M",
+ "TINYTEXT": "M",
+ "VARBINARY": "I",
+ "VARCHAR": "C",
+ "YEAR": "?"}[nativeType.upper()]
+ except KeyError:
+ print 'KeyError:', nativeType
+ ret = '?'
+ return ret
+
def getFields(self, tableName):
- if not tableName:
- return tuple()
+ """ Returns the list of fields of the passed table
+ field: ( fieldname, dabo data type, key )
+ handy URL:
http://mysql.org/doc/refman/5.1/en/columns-table.html
+ """
tempCursor = self._connection.cursor()
- tempCursor.execute("describe %s" % tableName)
+ # fairly standard way of getting column settings
+ # this may be standard wnough to put in the super class
+ tempCursor.execute(
+ "select COLUMN_NAME, DATA_TYPE, COLUMN_KEY"
+ " from INFORMATION_SCHEMA.COLUMNS"
+ " where table_name = '%s'"
+ " order by ORDINAL_POSITION"
+ % tableName)
rs = tempCursor.fetchall()
- fldDesc = tempCursor.description
- # The field name is the first element of the tuple. Find the
- # first entry with the field name 'Key'; that will be the
- # position for the PK flag
- pkPos = 0
- for i in range(len(fldDesc)):
- if fldDesc[i][0] == "Key":
- pkPos = i
- break
-
fields = []
for r in rs:
- name = r[0]
- ft = r[1]
- if ft.split()[0] == "tinyint(1)" or "bit" in ft:
- ft = "B"
- elif "int" in ft or ft == "long":
- ft = "I"
- elif "varchar" in ft:
- # will be followed by length
- ln = int(ft.split("(")[1].split(")")[0])
- if ln > 255:
- ft = "M"
- else:
- ft = "C"
- elif "char" in ft :
- ft = "C"
- elif "text" in ft:
- ft = "M"
- elif "blob" in ft:
- ft = "L"
- elif "decimal" in ft:
- ft = "N"
- elif "float" in ft:
- ft = "F"
- elif "datetime" in ft:
- ft = "T"
- elif "date" in ft:
- ft = "D"
- elif "enum" in ft:
- ft = "C"
- else:
- ft = "?"
- pk = (r[pkPos] == "PRI")
-
- fields.append((name.strip(), ft, pk))
+ name = r[0].strip()
+ ft = self._fieldTypeNativeToDabo(r[1])
+ pk = (r[2] == "PRI")
+ fields.append((name, ft, pk))
return tuple(fields)
-
def getDaboFieldType(self, backendFieldType):
+ """ converts a numeric fieldtype to a Dabo field type.
+ numeric fieldtypes are what cursor.describe uses.
+ """
import MySQLdb.constants.FIELD_TYPE as ftypes
typeMapping = {}
for i in dir(ftypes):
if i[0] != "_":
v = getattr(ftypes, i)
typeMapping[v] = i
- # typeMapping[16]='BIT'
+ typeMapping[16]='BIT'
- daboMapping = {"BIT": "I",
- "BLOB": "M",
- "CHAR": "C",
- "DATE": "D",
- "DATETIME": "T",
- "DECIMAL": "N",
- "DOUBLE": "G",
- "ENUM": "C",
- "FLOAT": "F",
- "GEOMETRY": "?",
- "INT24": "I",
- "INTERVAL": "?",
- "LONG": "G",
- "LONGLONG": "G",
- "LONG_BLOB": "M",
- "MEDIUM_BLOB": "M",
- "NEWDATE": "?",
- "NULL": "?",
- "SET": "?",
- "SHORT": "I",
- "STRING": "C",
- "TIME": "?",
- "TIMESTAMP": "T",
- "TINY": "I",
- "TINY_BLOB": "M",
- "VAR_STRING": "C",
- "YEAR": "?"}
- return daboMapping[typeMapping[backendFieldType]]
+ try:
+ ReadableType = typeMapping[backendFieldType]
+ ret = self._fieldTypeNativeToDabo(ReadableType)
+ except KeyError:
+ print 'KeyError: backendFieldType = ', backendFieldType
+ ret='?'
+
+ return ret
def getWordMatchFormat(self):
@@ -321,3 +342,11 @@
if toExc != []:
return toExc
+
+def _unit_tests():
+ # next time....
+ pass
+
+if __name__ == "__main__":
+ print _unit_tests()
+
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
