def createTableAndIndexes(self, tabledef, cursor, createTable=True,
createIndexes=True):
if not tabledef.Name:
raise
#Create the table
if createTable:
if not tabledef.IsTemp:
sql = "CREATE TABLE "
else:
sql = "CREATE TEMP TABLE "
sql = sql + tabledef.Name + " ("
for fld in tabledef.Fields:
dont_esc = False
sql = sql + fld.Name + " "
if fld.DataType == "Numeric":
sql = sql + "INTEGER "
elif fld.DataType == "Float":
sql = sql + "REAL "
elif fld.DataType == "Decimal":
sql = sql + "TEXT "
elif fld.DataType == "String":
sql = sql + "TEXT "
elif fld.DataType == "Date":
sql = sql + "TEXT "
elif fld.DataType == "Time":
sql = sql + "TEXT "
elif fld.DataType == "DateTime":
sql = sql + "TEXT "
elif fld.DataType == "Stamp":
sql = sql + "TEXT "
fld.Default = dNoEQ("CURRENT_TIMESTAMP")
elif fld.DataType == "Binary":
sql = sql + "BLOB "
if fld.IsPK:
sql = sql + "PRIMARY KEY "
if fld.IsAutoIncrement:
sql = sql + "AUTOINCREMENT "
if not fld.AllowNulls:
sql = sql + "NOT NULL "
sql = "%sDEFAULT %s," % (sql, self.formatForQuery(fld.Default))
if sql[-1:] == ",":
sql = sql[:-1]
sql = sql + ")"
cursor.execute(sql)
if createIndexes:
#Create the indexes
for idx in tabledef.Indexes:
if idx.Name.lower() != "primary":
sql = "CREATE INDEX " + idx.Name + " ON " + tabledef.Name + "("
for fld in idx.Fields:
sql = sql + fld + ","
if sql[-1:] == ",":
sql = sql[:-1]
sql = sql + ")"
cursor.execute(sql)
def createTableAndIndexes(self, tabledef, cursor, createTable=True,
createIndexes=True):
if not tabledef.Name:
raise
toExc = []
#Create the table
if createTable:
if not tabledef.IsTemp:
sql = "CREATE TABLE "
else:
sql = "CREATE TEMPORARY TABLE "
sql = sql + tabledef.Name + " ("
for fld in tabledef.Fields:
pks = []
sql = sql + fld.Name + " "
if fld.DataType == "Numeric":
if fld.Size == 0:
sql = sql + "BIT "
elif fld.Size == 1:
sql = sql + "TINYINT "
elif fld.Size == 2:
sql = sql + "SMALLINT "
elif fld.Size in (3,4):
sql = sql + "INT "
elif fld.Size in (5,6,7,8):
sql = sql + "BIGINT "
else:
raise #what should happen?
elif fld.DataType == "Float":
if fld.Size in (0,1,2,3,4):
sql = sql + "FLOAT(" + ustr(fld.TotalDP) + "," + ustr(fld.RightDP) + ") "
elif fld.Size in (5,6,7,8):
sql = sql + "DOUBLE(" + ustr(fld.TotalDP) + "," + ustr(fld.RightDP) + ") "
else:
raise #what should happen?
elif fld.DataType == "Decimal":
sql = sql + "DECIMAL(" + ustr(fld.TotalDP) + "," + ustr(fld.RightDP) + ") "
elif fld.DataType == "String":
if fld.Size <= 255:
sql = sql + "VARCHAR(" + ustr(fld.Size) + ") "
elif fld.Size <= 65535:
sql = sql + "TEXT "
elif fld.Size <= 16777215:
sql = sql + "MEDIUMTEXT "
elif fld.Size <= 4294967295:
sql = sql + "LONGTEXT "
else:
raise #what should happen?
elif fld.DataType == "Date":
sql = sql + "DATE "
elif fld.DataType == "Time":
sql = sql + "TIME "
elif fld.DataType == "DateTime":
sql = sql + "DATETIME "
elif fld.DataType == "Stamp":
sql = sql + "TIMESTAMP "
fld.Default = dNoEQ("CURRENT_TIMESTAMP")
elif fld.DataType == "Binary":
if fld.Size <= 255:
sql = sql + "TINYBLOB "
elif fld.Size <= 65535:
sql = sql + "BLOB "
elif fld.Size <= 16777215:
sql = sql + "MEDIUMBLOB "
elif fld.Size <= 4294967295:
sql = sql + "LONGBLOB "
else:
raise #what should happen?
if fld.IsPK:
sql = sql + "PRIMARY KEY "
pks.append(fld.Name)
if fld.IsAutoIncrement:
sql = sql + "AUTO_INCREMENT "
if not fld.AllowNulls:
sql = sql + "NOT NULL "
if not fld.IsPK:
sql = "%sDEFAULT %s," % (sql, self.formatForQuery(fld.Default))
else:
sql = sql + ","
if sql.count("PRIMARY KEY ") > 1:
sql = sql.replace("PRIMARY KEY ","") + "PRIMARY KEY(" + ",".join(pks) + "),"
if sql[-1:] == ",":
sql = sql[:-1]
sql = sql + ")"
try:
cursor.execute(sql)
except dException.DBNoAccessException:
toExc.append(sql)
if createIndexes:
#Create the indexes
for idx in tabledef.Indexes:
if idx.Name.lower() != "primary":
sql = "CREATE INDEX " + idx.Name + " ON " + tabledef.Name + "("
for fld in idx.Fields:
sql = sql + fld + ","
if sql[-1:] == ",":
sql = sql[:-1]
sql = sql + ")"
if toExc == []:
try:
cursor.execute(sql)
except dException.DBNoAccessException:
toExc.append(sql)
else:
toExc.append(sql)
if toExc != []:
return toExc
def createTableAndIndexes(self,
tabledef,
cursor,
createTable=True,
createIndexes=True):
if not tabledef.Name:
raise
#Create the table
if createTable:
if not tabledef.IsTemp:
sql = "CREATE TABLE "
else:
sql = "CREATE TEMP TABLE "
sql = sql + tabledef.Name + " ("
for fld in tabledef.Fields:
dont_esc = False
sql = sql + fld.Name + " "
if fld.DataType == "Numeric":
sql = sql + "INTEGER "
elif fld.DataType == "Float":
sql = sql + "REAL "
elif fld.DataType == "Decimal":
sql = sql + "TEXT "
elif fld.DataType == "String":
sql = sql + "TEXT "
elif fld.DataType == "Date":
sql = sql + "TEXT "
elif fld.DataType == "Time":
sql = sql + "TEXT "
elif fld.DataType == "DateTime":
sql = sql + "TEXT "
elif fld.DataType == "Stamp":
sql = sql + "TEXT "
fld.Default = dNoEQ("CURRENT_TIMESTAMP")
elif fld.DataType == "Binary":
sql = sql + "BLOB "
if fld.IsPK:
sql = sql + "PRIMARY KEY "
if fld.IsAutoIncrement:
sql = sql + "AUTOINCREMENT "
if not fld.AllowNulls:
sql = sql + "NOT NULL "
sql = "%sDEFAULT %s," % (sql, self.formatForQuery(fld.Default))
if sql[-1:] == ",":
sql = sql[:-1]
sql = sql + ")"
cursor.execute(sql)
if createIndexes:
#Create the indexes
for idx in tabledef.Indexes:
if idx.Name.lower() != "primary":
sql = "CREATE INDEX " + idx.Name + " ON " + tabledef.Name + "("
for fld in idx.Fields:
sql = sql + fld + ","
if sql[-1:] == ",":
sql = sql[:-1]
sql = sql + ")"
cursor.execute(sql)
def createTableAndIndexes(self,
tabledef,
cursor,
createTable=True,
createIndexes=True):
if not tabledef.Name:
raise
toExc = []
#Create the table
if createTable:
if not tabledef.IsTemp:
sql = "CREATE TABLE "
else:
sql = "CREATE TEMPORARY TABLE "
sql = sql + tabledef.Name + " ("
for fld in tabledef.Fields:
pks = []
sql = sql + fld.Name + " "
if fld.DataType == "Numeric":
if fld.Size == 0:
sql = sql + "BIT "
elif fld.Size == 1:
sql = sql + "TINYINT "
elif fld.Size == 2:
sql = sql + "SMALLINT "
elif fld.Size in (3, 4):
sql = sql + "INT "
elif fld.Size in (5, 6, 7, 8):
sql = sql + "BIGINT "
else:
raise #what should happen?
elif fld.DataType == "Float":
if fld.Size in (0, 1, 2, 3, 4):
sql = sql + "FLOAT(" + ustr(fld.TotalDP) + "," + ustr(
fld.RightDP) + ") "
elif fld.Size in (5, 6, 7, 8):
sql = sql + "DOUBLE(" + ustr(fld.TotalDP) + "," + ustr(
fld.RightDP) + ") "
else:
raise #what should happen?
elif fld.DataType == "Decimal":
sql = sql + "DECIMAL(" + ustr(fld.TotalDP) + "," + ustr(
fld.RightDP) + ") "
elif fld.DataType == "String":
if fld.Size <= 255:
sql = sql + "VARCHAR(" + ustr(fld.Size) + ") "
elif fld.Size <= 65535:
sql = sql + "TEXT "
elif fld.Size <= 16777215:
sql = sql + "MEDIUMTEXT "
elif fld.Size <= 4294967295:
sql = sql + "LONGTEXT "
else:
raise #what should happen?
elif fld.DataType == "Date":
sql = sql + "DATE "
elif fld.DataType == "Time":
sql = sql + "TIME "
elif fld.DataType == "DateTime":
sql = sql + "DATETIME "
elif fld.DataType == "Stamp":
sql = sql + "TIMESTAMP "
fld.Default = dNoEQ("CURRENT_TIMESTAMP")
elif fld.DataType == "Binary":
if fld.Size <= 255:
sql = sql + "TINYBLOB "
elif fld.Size <= 65535:
sql = sql + "BLOB "
elif fld.Size <= 16777215:
sql = sql + "MEDIUMBLOB "
elif fld.Size <= 4294967295:
sql = sql + "LONGBLOB "
else:
raise #what should happen?
if fld.IsPK:
sql = sql + "PRIMARY KEY "
pks.append(fld.Name)
if fld.IsAutoIncrement:
sql = sql + "AUTO_INCREMENT "
if not fld.AllowNulls:
sql = sql + "NOT NULL "
if not fld.IsPK:
sql = "%sDEFAULT %s," % (sql,
self.formatForQuery(fld.Default))
else:
sql = sql + ","
if sql.count("PRIMARY KEY ") > 1:
sql = sql.replace(
"PRIMARY KEY ",
"") + "PRIMARY KEY(" + ",".join(pks) + "),"
if sql[-1:] == ",":
sql = sql[:-1]
sql = sql + ")"
try:
cursor.execute(sql)
except dException.DBNoAccessException:
toExc.append(sql)
if createIndexes:
#Create the indexes
for idx in tabledef.Indexes:
if idx.Name.lower() != "primary":
sql = "CREATE INDEX " + idx.Name + " ON " + tabledef.Name + "("
for fld in idx.Fields:
sql = sql + fld + ","
if sql[-1:] == ",":
sql = sql[:-1]
sql = sql + ")"
if toExc == []:
try:
cursor.execute(sql)
except dException.DBNoAccessException:
toExc.append(sql)
else:
toExc.append(sql)
if toExc != []:
return toExc
