SnakeDb ver 1.00

Introduction:

The purpose of SnakeDb is to provide an easy database API. You don't have to write SQL statements in your scripts to create databases or execute a query. You can access the fields in the tables through a dictionary. The API also allows you to execute complex SQL statements. SnakeDb is a wrapper around other python database modules. SnakeDb ver 1.00 supports Gadfly and MySQL databases. You can easily extend it to use other python database modules.

System Requirements:

• Python 2.0
• MySQL database system ver 3.22.25 and up
• Gadfly python module
• MySQLdb python module

SnakeDb Classes:

Below are a brief description of every class in the SnakeDb module. Normally, you should not have to modify any of these classes.

• SnakeDb - This is the base class of all SnakeDb database classes.
• SnakeDbTable - This is the base class for defining a database table. You will need to create one class per database table.
• SnakeDbObj - This base class provides access to the database tables. You will need to create one class per database table.
• SnakeDbQuery - You create an instance of this class when you want to find a subset of entries that match a certain criteria. You can also specify the page number you want to jump to and the size of each page. Let's say there were 100 entries in the table that matches your search criteria. If you only want entries 50 to 59, you set page number to 5 and page size to 10.
• GadflySnakeDb - This class inherits from SnakeDb classs. GadflySnakeDb provides the interface to a gadlfy database.
• MySQLdbSnakeDb - This class inherits from SnakeDb classs. MySQLdbSnakeDb provides the interface to a MySQL database.

Special Features:

class PersonTable(SnakeDbTable):
	tableName="person"

	def __init__(self):
		sqlFields={
		#sqlfield: fieldtype
		'personId':'varchar(100)',
		'personObjId':'varchar(100)',
		'creationTimeStamp':'timestamp(14)',
		'firstName':'varchar(100)',
		'lastName':'varchar(100)',
		'phone':'varchar(100)',
		SnakeDbTable.__init__(self, self.tableName, sqlFields)

• Notice the field "personId". This is a mandatory field which contains an unique id. At the creation of a new entry into the table, the unique id is generated from time.time(). So, the id might be something like "975423244.793". If you want to have a database table called "person", there must be a field called "personId".
• The "personObjId" field is the encrypted version of the "personId". The "personObjId" field is optional. So, if you have a database table called "person" and there is a field "contactObjId", then the encrypted value of "personId" stored in the "personObjId".
• The "creationTimeStamp" field will be automatically updated with a timestamp whenever the entry is modified with a "UPDATE" SQL statement. All fields ending with the string "TimeStamp" will be updated. The SnakeDb module actually puts the timestamp field in the following format: YYYYMMDDHHNNSS where YYYY is the year, MM is the month, DD is the day, HH is the hour, NN is the minute, and SS is the seconds. So if the table has the fields "field1timestamp" or "field2Timestamp" and the table entry is updated, then the fields will be updated.
• You can access the table fields through a dictionary. First define the following Person class that inherits from SnakeDbObj.

  
  class Person(SnakeDbObj):
  	def __init__(self, db, detailsDict={}):
  		SnakeDbObj.__init__(self, db, PersonTable.tableName, detailsDict)
  		
  

  For example, if xVar is an instance of Person, then you can use xVar.details to access the fields. xVar.details['firstName'] gets you the firstName.
• SnakeDb module also provides automatic paging for search query. If you want to go to the second page for all entries with the last name of "Lee", do the following:

  db=PhoneBookDb()
  db.connectDb()
  x=Person(db)
  q=SnakeDbQuery(snakeDb=db,pageSize=5,pageNumber=2)
  q.setCriteria({'lastName':'Lee'})
  results=x.find(q)
  

Installation:

1. Download the distribution file at http://www.scriptfoundry.com/modules/dist/
2. Uncompress the distribution file
3. change into the directory created during the uncompression of distribution file
4. execute the command:
   python setup.py install
   NOTE: Make sure you have the correct permissions before you run step 4. If the python interpreter was installed by ROOT, a regular user probably run into trouble.

Unix Installation Example:

• cd ~home
• tar xvfz snakedb-1.00.tar.gz
• cd snakedb-1.00
• python setup.py install

Code Example:

#!/usr/bin/env python

from snakedb import *
import sys
from string import lower, join, split

class PhoneBookDb(GadflySnakeDb):
	dbDir="/tmp"
	dbName="phonebook"

	def __init__(self):
		GadflySnakeDb.__init__(self,self.dbName, self.dbDir)

	def registerAllDbTables(self):
		self.registerDbTable(PersonTable())
		self.registerDbTable(AddressTable())



class PersonTable(SnakeDbTable):
	tableName="person"

	def __init__(self):
		sqlFields={
		#sqlfield: fieldtype
		'personId':'varchar',
		'personObjId':'varchar',
		'updatedOnTimeStamp':'varchar',
		'lastName':'varchar',
		'firstName':'varchar',
		'phone':'varchar'
		}
		SnakeDbTable.__init__(self, self.tableName, sqlFields)

class Person(SnakeDbObj):
	def __init__(self, db, detailsDict={}):
		SnakeDbObj.__init__(self, db, PersonTable.tableName, detailsDict)
		

class AddressTable(SnakeDbTable):
	tableName="address"

	def __init__(self):
		sqlFields={
		#sqlfield: fieldtype
		'addressId':'varchar',
		'addressObjId':'varchar',
		'updatedOnTimeStamp':'varchar',
		'personId':'varchar',
		'city':'varchar',
		'state':'varchar'
		}
		SnakeDbTable.__init__(self, self.tableName, sqlFields)

class Address(SnakeDbObj):
	def __init__(self, db, detailsDict={}):
		SnakeDbObj.__init__(self, db, AddressTable.tableName, detailsDict)
		


db=PhoneBookDb()
try:
	db.dropDb()
except:
	pass
db.createDb()
print "New Phone Book database created!"
personInfo={
'firstName':'Ming',
'lastName':'Huang',
'phone':'212-228-0933'
}
p=Person(db, personInfo)
p.create()

addressInfo={
'personId':p.details['personId'],
'city':'Brooklyn',
'state': 'NY'
}
a=Address(db, addressInfo)
a.create()
print "Added new entry: %s %s"%(p.details,a.details)

print "Updating the address of Ming Huang"
a2=Address(db)
a2.load(a.details['addressId'])
a2.details['city']='Brooklyn'
a2.save()

personInfo={
'firstName':'Thanos',
'lastName':'Vassilakis',
'phone':'212-228-0933'
}
p=Person(db, personInfo)
p.create()

addressInfo={
'personId':p.details['personId'],
'city':'New York',
'state': 'NY'
}
a=Address(db, addressInfo)
a.create()

print "Added new entry: %s %s"%(p.details,a.details)

personInfo={
'firstName':'John',
'lastName':'Lee',
'phone':'212-555-5555'
}
p=Person(db, personInfo)
p.create()

addressInfo={
'personId':p.details['personId'],
'city':'Queens',
'state': 'NY'
}
a=Address(db, addressInfo)
a.create()

print "Added new entry: %s %s"%(p.details,a.details)

personInfo={
'firstName':'Sue',
'lastName':'Lee',
'phone':'212-555-6666'
}
p=Person(db, personInfo)
p.create()

addressInfo={
'personId':p.details['personId'],
'city':'Queens',
'state': 'NY'
}
a=Address(db, addressInfo)
a.create()

print "Added new entry: %s %s"%(p.details,a.details)

print "\n\nListing of all person with telephone: 212-228-0933"
query=SnakeDbQuery(snakeDb=db,pageSize=5,pageNumber=1)
query.setCriteria({'phone':'212-228-0933'})
print p.find(query)

print "\n\nListing of all person with telephone: 212-228-0933 and last name 'Huang'"
query=SnakeDbQuery(snakeDb=db,pageSize=5,pageNumber=1)
query.setCriteria({'phone':'212-228-0933', 'lastName':'Huang'})
print p.find(query)

print "\n\nDeleting all person who live in Brooklyn"
#Testing the deletion of entries in two tables
pgNumber=1
query=SnakeDbQuery(snakeDb=db,pageSize=5,pageNumber=pgNumber)
query.setCriteria({'city':'Brooklyn'})
a3=Address(db)
p3=Person(db)
found=a3.find(query)
while (found):
	for record in found:
		a3.remove(addressId=record['addressId'])
		p3.remove(personId=record['personId'])
	pgNumber=pgNumber+1
	query.reset(pageSize=5,pageNumber=pgNumber)
	found=a3.find(query)


print "\n\nDeleting all person with last name Lee"
#Testing the deletion of entries in only one table
#NOTE: In the following exmaple, the address entries for the deleted persons are not affected
p4=Person(db)
p4.remove(lastName='Lee')

db.closeDb()