Reinventing the Wheel Part II: The Database and Getting Stuff Out of It
26 Aug 2003
Continuing from Part I, here's a text file of my new database structure. I'm sure it's not perfectly in fourth normal form, and database design gurus everywhere can freely mock me. But I think it should work reasonably well. And I even have an unreadable ASCII diagram of the table relationships. Whee!
So I guess that's the database, in all its unholy entirety. What next? Babble about the code?
I wrote a little data module type thing. It basically builds SQL queries for me so I don't have to keep typing "SELECT blah blah blah FROM blah WHERE blah" all the time. It also contains a little function that automatically does some stuff with the Python postgresql module I use, like format the results of a query the way I want it. It's eloquently named "data." I'm not going to include any samples of that code because it's nothing particularly revolutionary.
I also wrote a package called DBObjects. I created a base class to grab stuff from the database, so that I could essentially have an object representation of a database table. Thus database fields become object attributes. In the original version of ARJLog I just wrote a bunch of functions that each made queries to the database and manipulated them by hand. Lots of repetition that was clumsy and inefficient. It got old really fast, especially since I'm well rehearsed in the principles of OOP. But at that stage I just wanted to get something working, and I was still really low on the Python learning curve. In the new version, all of my basic weblog pieces will inherit from DatabaseObject, which does 90% of the work. This makes changing stuff much easier. So here's some of my beautiful base class (stored in __init__.py). Please excuse the crummy coding style.
import data
class DatabaseObject:
"""Model an atomic object in the database.
Parameters:
Fields: List of strings, ['field1',..,'fieldn']
Links: Foreign key relationships {'linkedfield':(('ftable','fkey')...)}
IDField: Primary key (assumes only 1)
Table: string name of the table
ID: Get data from DB for this ID
Values: Data already retrieved, load these values"""
def __init__(self,Fields=[],Links={},IDField='',Table='',ID=None,Values={}):
dictvals = {}
self.__dict__['Fields'] = Fields
self.__dict__['Links'] = Links
self.__dict__['ID_Field'] = IDField
self.__dict__['Table'] = Table
if len(Links): self.__initlinks(Links)
if len(Values): dictvals = Values
elif ID:
sel = data.SelectQuery(self.__dict__['Table'],self.__dict__['Fields'],self.__dict__['ID_Field'] + '=' + str(ID))
dictvals = data.ExecuteQuery(sel,DBNAME)
else:
for fld in self.__dict__['Fields']: dictvals[fld] = [None]
self.__setvalues(dictvals)
def __setvalues(self,Values):
"""Private function that runs through a dictionary of fields/ value
lists and turns fields into object attributes that have the first value in the
value list.
This function assumes that the object only represents one row in a table."""
for fld in Values.keys():
if fld in self.__dict__['Fields']: self.__dict__[fld] = Values[fld][0]
def __initlinks(self,Links):
"""Links are the relationships between two tables in the database,
matched with foreign keys. This function stores the links in an
easy-to-look-up fashion."""
ftbls = {}
for fld in Links.keys():
linklst = Links[fld]
for tpl in linklst:
ftable,fkey = tpl
if ftbls.has_key(ftable): ftbls[ftable].append((fkey,fld))
else: ftbls[ftable] = [(fkey,fld)]
self.__dict__['__ftables'] = ftbls
def __getvalues(self,Fields):
"""Get a list of values for all the field-attributes in the object."""
Values = []
for fld in Fields:
if self.__dict__.has_key(fld):
Values.append(self.__dict__[fld])
else: Values.append(None)
return Values
def __getattr__(self,Key):
"""Implementing some access control on some of the object
attributes."""
if Key in self.__dict__['Fields'] or Key in self.__dict__.keys():
if self.__dict__.has_key(Key): return self.__dict__[Key]
else: return None
else: raise AttributeError,Key
def __setattr__(self,Key,Value):
"""Implementing some access control on some of the object
attributes."""
if Key == self.__dict__['ID_Field']: raise AttributeError,Key + ' is read-only'
if Key in self.__dict__['Fields']:
self.__dict__[Key] = Value
else: raise AttributeError,Key
def __str__(self):
""" __str__ dumps out a bunch of info on the object for
testing purposes. This makes testing/debugging easy. I'm probably not going to
use a string representation much for the actual blogging scripts, anyway."""
objstr = "Database Object from " + self.__dict__['Table']+":
" + ("=" * 20) + "
"
for key in self.__dict__.keys():
if key in self.__dict__['Fields']: objstr = objstr + str(key) + ' = ' + str(self.__dict__[key]) + '
'
return objstr
def __getlvals__(self,FTable,Field,Filter="",Order="",Function="",FFields=['*'],Limit=''):
"""Get a list of values from another table that's related by a
foreign key. The function GetValuesCollection() below is another SQL query
builder shortcut type function."""
FKey = ''
KeyFields = self.__dict__['__ftables'][FTable]
for tpl in KeyFields:
if Field in tpl: FKey,f=tpl
strFilter = Filter
if Filter: strFilter = strFilter + ' and '
strFilter = strFilter + self.Table + '.' + self.ID_Field + '=' + str(getattr(self,self.ID_Field))
if FKey: return GetValuesCollection(Tables={('',self.Table):[],(Function,FTable):FFields},Fields=[((self.Table,Field),(FTable,FKey))],Condition=strFilter,Order=Order,Limit=Limit)
else: return []
def AddLink(self,FTable,Key,FKey):
"""Not yet implemented -- I want a way to programmatically add
foreign key links, but I just haven't had time to do it yet. Output first-- I
can always insert stuff by hand into the DB for now."""
return None
def New(self):
"""Inserts a new row into the database. It barfs if the object
has already been "filled out" by the database-- I don't want to accidentally
duplicate rows."""
if getattr(self,self.ID_Field) is None:
shortfields = self.__dict__['Fields'][:]
shortfields.remove(self.__dict__['ID_Field'])
values = self.__getvalues(shortfields)
ins = data.InsertQuery(self.__dict__['Table'],shortfields,values)
oid = data.ExecuteQuery(ins,DBNAME)
sel = data.SelectQuery(self.__dict__['Table'],[],'oid = ' + str(oid))
dictvals = data.ExecuteQuery(sel,DBNAME)
self.__setvalues(dictvals)
def Update(self):
"""Copy all the attribute values into this row of the
database."""
if getattr(self,self.ID_Field):
shortfields = self.__dict__['Fields'][:]
shortfields.remove(self.__dict__['ID_Field'])
values = self.__getvalues(shortfields)
upd = data.UpdateQuery(self.__dict__['Table'],shortfields,values,self.__dict__['ID_Field'] + "=" + str(getattr(self,self.ID_Field)))
data.ExecuteQuery(upd,DBNAME)
def SearchField(self,Field,Value,Limit=1):
"""Instead of retrieving a row by the object's ID, get a row
by searching some other unique field. I may extend this function to return
more than one row, hence the Limit parameter..."""
if getattr(self,self.ID_Field) is None and Field in self.__dict__['Fields']:
search = data.SearchQuery(Table=self.__dict__['Table'],SearchFields=[Field],SearchString=Value,LimitRows=Limit)
vals = data.ExecuteQuery(search,DBNAME)
self.__setvalues(vals)
def DBInfo(self):
"""For testing purposes, I threw this one in, so I could see
what was going on under the hood of an object. It probably doesn't serve any
practical purpose in a live blog, and I will probably remove it once I put
stuff into production."""
return str(self.__dict__['Table']) + '
' + str(self.__dict__['ID_Field']) + '
' + str(self.__dict__['Fields'])
Hey, this is fun. I don't even have to think up stuff to write. I just post code. No wonder TechBloggers are so prolific. Except no one's probably reading this anymore...
[Link] Comments: [0] Categories: [Techo] Project: [Homegrown Blogging]
