from dbiface import IFaceDB,CustomRecordFetcher
import apsw
import sys

class APSWRecordFetcher(CustomRecordFetcher):
    
    def _GetRecord(self):
        lrow=None
        try:
            lrow=self.Cur.next()
        except:
            pass
        if lrow:
            if not self.Desc:
                self.Desc=self.Cur.getdescription()
                for s in self.Desc:
                    self.FieldNames.append(s[0])
            row={}
            for idx,col in enumerate(self.Desc):
                row[col[0]]=lrow[idx]
            return row


class APSWDB(IFaceDB):

    def __init__(self,Path):
        IFaceDB.__init__(self,Path)

    def _CreateConnection(self):
        con=apsw.Connection(self.Path)
        return con

    def _AfterOpen(self):
        self._InTran=0
        self.StartTr()

    def StartTr(self):
        if not self._InTran:
            self.Cur.execute('begin')
            self._InTran=1

    def CommitTr(self,Restart=False):
        if self._InTran:
            self.Cur.execute('end')
        self._InTran=0
        if Restart: self.StartTr()

    def RollBackTr(self,Restart=False):
        if self._InTran:
            self.Cur.execute('rollback')
        self._InTran=0
        if Restart: self.StartTr()

    def _CloseConnection(self):
        self.Cur=None
        self.Con=None
        self._InTran=0
        
    def CreateFetcher(self,SQL,Params=()):
        return APSWRecordFetcher(self,SQL,Params)


if __name__=="__main__":
    # Create the table
    pdb=APSWDB('_test.db3')
    pdb.Open(1)
    pdb.Exec('create table t(a integer,b varchar(20))')
    pdb.CommitTr(1)
    for i in range(1,11):
        pdb.InsRec('t',{'a':i,'b':'xxx'})
    rf=APSWRecordFetcher(pdb,'select * from t')
    while 1:
        r=rf.Fetch()
        if r: print r
        else: break
    print
    recs=pdb.Fetch('select * from t')
    for r in recs:
        print r
    sys.exit()
    #rec=pdb.GetRec('t',{'a':1})
    #print rec
    #rec['b']='yyy'
    #pdb.UpdRec('t',rec,'a')
    recs=pdb.Fetch('select * from t')
    for r in recs:
        print r
    pdb.CommitTr(0)
    recs=pdb.Fetch('select * from sqlite_master')
    print recs
    sys.exit()
    print pdb.GetRec('t',{'a':1})
    sys.exit()
    print sql
    print dtup
    pdb.Exec(sql,dtup)
    pdb.CommitTr(1)
    rows=pdb.Fetch('select * from t')
    row=rows[0]
    row['b']=1
    sql,dtup=pdb.GetUpdSQL('t',row,'a')
    print sql
    print dtup
    pdb.Exec(sql,dtup)
    sql,dtup=pdb.GetDelSQL('t',row,'a')
    print sql
    print dtup
    pdb.Exec(sql,dtup)
    sys.exit()
    #for s in dir(con): print s
    # Fill the table
    pdb.Close()
    pdb.Open()
    print pdb.Fetch('select * from t')
    pdb.Close()