| 1 |
from DataStructures import *
|
| 2 |
|
| 3 |
class SqlGenerator:
|
| 4 |
"""
|
| 5 |
A simple sql for inserting into a table. The sql generated depends on the keys provided in the object
|
| 6 |
"""
|
| 7 |
def getInsertSql(self, objType, object):
|
| 8 |
object = getTrimmedObject(object)
|
| 9 |
sql = 'insert into ' + objType + ' ('
|
| 10 |
sqlValue = 'values ( '
|
| 11 |
index = 1
|
| 12 |
objKeys = object.keys()
|
| 13 |
if 'id' in objKeys: index = 2
|
| 14 |
for aKey in objKeys:
|
| 15 |
if aKey == 'id': raise 'You should not provide id when inserting a record in the database. Ids are generated internally'
|
| 16 |
sql += aKey + ' '
|
| 17 |
sqlValue += ':' + aKey + ' '
|
| 18 |
if index != len(objKeys):
|
| 19 |
sql += ' , '
|
| 20 |
sqlValue += ' , '
|
| 21 |
index += 1
|
| 22 |
|
| 23 |
sql += ') ' + sqlValue + ')'
|
| 24 |
return sql
|
| 25 |
|
| 26 |
def getSelectSql(self, objType, object):
|
| 27 |
"""
|
| 28 |
Sql for selecting every column from a table depending on the constraints provided by the object keys.
|
| 29 |
"""
|
| 30 |
validate(objType, object, True)
|
| 31 |
object = getTrimmedObject(object)
|
| 32 |
objKeys = object.keys()
|
| 33 |
|
| 34 |
sql = "select * from " + objType
|
| 35 |
index = 1
|
| 36 |
if len(objKeys) > 0:
|
| 37 |
sql += ' where '
|
| 38 |
for aKey in objKeys:
|
| 39 |
sql += aKey + ' like :' + aKey + ' '
|
| 40 |
if index != len(objKeys): sql += ' and '
|
| 41 |
index += 1
|
| 42 |
index = 1
|
| 43 |
uniqueKeys = getRequiredKeys(objType)
|
| 44 |
if len(uniqueKeys) > 0:
|
| 45 |
sql += ' order by '
|
| 46 |
for aUKey in uniqueKeys:
|
| 47 |
sql += aUKey + ' '
|
| 48 |
if index != len(uniqueKeys): sql += ' , '
|
| 49 |
index += 1
|
| 50 |
|
| 51 |
return sql
|
| 52 |
|
| 53 |
def getUpdateSql(self, objType, object):
|
| 54 |
"""
|
| 55 |
To generate sql to update row/s in a table . The constraints are generated according to the unique keys in the object. Rest of the keys are used in update set clause
|
| 56 |
"""
|
| 57 |
object = getTrimmedObject(object)
|
| 58 |
sql = 'update ' + objType + ' set '
|
| 59 |
index = 1
|
| 60 |
objKeys = object.keys()
|
| 61 |
uniqueKeys = getRequiredKeys(objType)
|
| 62 |
if 'id' in objKeys: index = 2
|
| 63 |
if len(uniqueKeys) > 0: index += len(uniqueKeys)
|
| 64 |
for aKey in objKeys:
|
| 65 |
if aKey not in uniqueKeys and aKey != 'id':
|
| 66 |
sql += aKey + ' = :' + aKey
|
| 67 |
if index != len(objKeys): sql += ' , '
|
| 68 |
index += 1
|
| 69 |
index = 1
|
| 70 |
if len(uniqueKeys) > 0:
|
| 71 |
sql += ' where '
|
| 72 |
for aUKey in uniqueKeys:
|
| 73 |
sql += aUKey + ' = :' + aUKey
|
| 74 |
if index != len(uniqueKeys): sql += ' and '
|
| 75 |
index += 1
|
| 76 |
if 'id' in objKeys:
|
| 77 |
sql += ' and id = :id'
|
| 78 |
return sql
|
| 79 |
|
| 80 |
|
| 81 |
def getTrimmedObject(object) :
|
| 82 |
print 'inside from getTrimmedObject'
|
| 83 |
objKeys = object.keys()
|
| 84 |
newObject = {}
|
| 85 |
for aKey in objKeys:
|
| 86 |
if object[aKey] not in ['', 0, 0.0, None, 'None']: newObject[aKey] = object[aKey]
|
| 87 |
print 'returng from getTrimmedObject'
|
| 88 |
return newObject
|
| 89 |
|
