#!/usr/bin/env python
"""
A noddy database

"""

import CSV,string

class Table:
    def __init__(self,fname,newfieldnames=None):
        "Open a new CSV file as a table"
        self.table = CSV.CSV()
        try:
            self.table.load(fname,1)
            self.datafilename = fname
        except IOError:
            # couldn't find it, so create it
            if newfieldnames == None:
                raise Exception('Table '+fname+ \
				' doesn\'t exist, and no field names specified to create as new')
            else:
                fields = string.split(newfieldnames,',')
                newfields = []
                for field in fields:
                    newfields.append('"' + field + '"')
                fieldstring = string.join(newfields,',')
                f = open(fname,"w")
                f.write(fieldstring)
                f.close()
                self.table.load(fname,1)
                self.datafilename = fname
                


    def select(self,which="",where="",order=""):
        "SELECT <which> FROM self WHERE <where> ORDER BY <order>"

        tablecopy = self.table

        if which == "":
            raise Exception('No fields specified to select')
        elif which == "*":
            lwhich = self.table.fields__title
        else:
            lwhich = string.split(which,',')

        lwhere = self.parseWhereClause(where)
		
        if order <> "":
            if string.find(order,',') <> -1:
                raise Exception('Can\'t specify multiple order by fields')
            self.orderfield = order
            tablecopy.sort(self.compare_for_order)

        returnrs = []
        for entry in tablecopy: #self.table:
            row = {}
            match = 1
            for cond in lwhere:
                if not eval(cond):
                    match = 0
            if match:
                for field in lwhich:
                    row[field] = entry[field]
                returnrs.append(row)
        return returnrs

    def update(self,set,where=""):
        "UPDATE <self> SET set WHERE where"
        if set <> "":
            lset = []
            for clause in self.table.line__process(set,0,','):
                lset.append(string.split(set,' ',2)) # field,equals,value
        else:
            raise Exception('No set clause specified in update')
        
        #  We've got a valid set clause
        lwhere = self.parseWhereClause(where)
        
        madechange = 0
        # reload the table to pick up any recent changes
        self.table.load(self.datafilename,1)
        for entry in self.table:
            match = 1
            for cond in lwhere:
                if not eval(cond):
                    match = 0
            if match:
                for (field,equals,value) in lset:
                    if field in self.table.fields__title:
                        if equals == "=":
                            entry[field] = self.dequote(value)
                            madechange = 1
                        else:
                            raise Exception('Set clause must set fields equal to something')
                    else:
                        raise Exception('Trying to update non-existent field \''+field+'\'')
        if madechange:
            self.table.save(self.datafilename)
            

    def insert(self,values):
        "INSERT INTO <self> <self.fieldnames> VALUES <values>"
        lvalues = self.table.line__process(values,0,',')
        if len(lvalues) <> len(self.table.fields__title):
            raise Exception('Incorrect number of fields specified for insert')
        lvalues = map(self.dequote,lvalues)
        self.table.load(self.datafilename,1)
        entry = CSV.Entry(lvalues)
        self.table.append(entry)
        self.table.save(self.datafilename)
		
	# Internal utility functions
	
    def compare_for_order(self,r1,r2):
        return cmp(r1[self.orderfield],r2[self.orderfield])

    def parseWhereClause(self,where):
        lwhere = []
        if where <> "":
            for whereclause in string.split(where,','):
                (field,cond,rem) = string.split(whereclause,' ',2)
                field = self.safequote(field)
                rem = self.safequote(rem)
                if cond == '=':
                    lwhere.append('entry["'+field+'"]'+'=='+rem)
                elif cond == '!=':
                    lwhere.append('entry["'+field+'"]'+'!='+rem)
                elif cond == 'contains':
                    lwhere.append('string.find(entry["'+field+'"],'+rem+') != -1')
                elif cond == '!contains':
                    lwhere.append('string.find(entry["'+field+'"],'+rem+') == -1')
                else:
                    raise Exception('Unknown operator \''+cond+'\'')
        return lwhere

    def dequote(self,s):
        """Remove start and end quotes from a string, if balanced, and
           quote double quotes therein"""
        ss = string.strip(s)
        if (ss[0] == '"' and ss[-1] == '"') or \
           (ss[0] == "'" and ss[-1] == "'"):
            ss = ss[1:-1]
        return ss

    def safequote(self,s):
        "Quote internal quotemarks safely"
        ss = string.strip(s)
        doublequote = 0
        singlequote = 0
        if (ss[0] == '"' and ss[-1] == '"'):
            doublequote = 1
            ss = ss[1:-1]
        if (ss[0] == "'" and ss[-1] == "'"):
            singlequote = 1
            ss = ss[1:-1]
        if doublequote:
            ss = '"'+string.replace(ss,'"','\\"')+'"'
        if singlequote:
            ss = "'"+string.replace(ss,"'","\\'")+"'"
        return ss