Firebird

From Dabo Wiki
Jump to: navigation, search

From http://firebird.sourceforge.net/:

Firebird is a relational database offering many ANSI SQL-92 features that runs on Linux, Windows, and a variety of Unix platforms. Firebird offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers. It has been used in production systems, under a variety of names since 1981.

Firebird is a commercially independent project of C and C++ programmers, technical advisors and supporters developing and enhancing a multi-platform relational database management system based on the source code released by Inprise Corp (now known as Borland Software Corp) under the InterBase? Public License v.1.0 on 25 July, 2000.

Below are two simple Python scripts (not involving dabo) just showing how to connect to Firebird and do a simple task using wxPython.

(1) The first script lets the user go into the File menu and select a Firebird database, and then it displays (do-nothing) buttons in a wxPython Sizer object down the left side of the window, one for each table (and view) in the database.

(2) The second script is hard-coded to open a particular Firebird database and display a particular table in a grid. (Before running, you will need to go into this script and change the hard-coded sections so they are appropriate for your system. You'll need to change the host and database parameters and the SQL statement - and maybe also the user and password parameters. Look for the lines preceded by a comment starting with ###.)

Before running these scripts, you'll need to do the following using instructions available elsewhere on the web.

(a) Install wxPython on your system (typically in the python/lib/site-packages directory).

(b) Install KinterbasDB on your system (typically in the python/lib/site-packages directory)

(c) Install the Firebird open-source database on your system.

(d) Create a Firebird database (*.FDB file).


#########################################
## SCRIPT 1 - Put this in a separate .py file.
##########################################

# You need to have KinterbasDB and wxPython installed 
# (typically in your python/Lib/site-packages/ directory) for this script to work.
# This Python script displays a simple window with a simple file-open menu.
# If you use te file-open menu to navigate to a Firebird database, then it will connect to that database
# and display a bunch of buttons down the left side of your window in a Sizer wxControl.

# The buttons *don't* do anything yet. (I've only been using Python and wxPython for about a week!)
# A possible suggested button-click action would be to display the clicked 
# table's records in a grid in a panel in the main window.


from wxPython.wx import *
import kinterbasdb
import os
ID_ABOUT = 101
ID_OPEN = 102
ID_BUTTON = 110
ID_EXIT = 9999


class MainWindow(wxFrame):
    def __init__(self,parent,wxID_ANY,title):
        self.dirname=''
        wxFrame.__init__(self,
                         parent,
                         wxID_ANY,
                         title,
                         style=wxDEFAULT_FRAME_STYLE|wxNO_FULL_REPAINT_ON_RESIZE)
        self.control = wxTextCtrl(self,1,style=wxTE_MULTILINE)
        self.CreateStatusBar()
        fileMenu=wxMenu()
        fileMenu.Append(ID_OPEN,"&Open"," Open a file to edit")
        fileMenu.AppendSeparator()
        fileMenu.Append(ID_ABOUT,"&About"," Information about this program")
        fileMenu.AppendSeparator()
        fileMenu.Append(ID_EXIT, "E&xit"," Terminate the program")
        menuBar=wxMenuBar()
        menuBar.Append(fileMenu,"&File")
        self.SetMenuBar(menuBar)
        EVT_MENU(self,ID_ABOUT,self.OnAbout)
        EVT_MENU(self,ID_EXIT,self.OnExit)
        EVT_MENU(self,ID_OPEN,self.OnOpen)
        self.sizer=wxBoxSizer(wxHORIZONTAL)
        self.sizer2 = wxBoxSizer(wxVERTICAL)
        self.buttons=[]


        # Use some sizers to see layout options
        self.sizer.Add(self.sizer2,0,wxEXPAND)
        self.sizer.Add(self.control,1,wxEXPAND)


        #Layout sizers
        self.SetSizer(self.sizer)
        self.SetAutoLayout(1)
        self.sizer.Fit(self)


        self.Show(1)


    def OnAbout(self,e):
        d=wxMessageDialog(self,"A sample editor \n"
                          " in wxPython","About Sample Editor", wxOK)
        d.ShowModal()
        d.Destroy()


    def OnExit(self,e):
        self.Close(true)


    def OnOpen(self,e):
        """ (self:MainWindow,e:Event) Open a Firebird database file *.FDB """
        dlg=wxFileDialog(self,"Choose a Firebird database *.FDB", self.dirname,"","*.FDB",wxOPEN)
        if dlg.ShowModal() == wxID_OK:
            self.filename=dlg.GetFilename()
            self.dirname=dlg.GetDirectory()
            # f=open(os.path.join(self.dirname,self.filename),'r')
        dlg.Destroy
        # self.control.SetValue(f.read())
        # f.close


        # Some debugging code here, to make sure we've got the pathname right... 
        print self.dirname + "\\" + self.filename
        ### HERE'S WHERE YOU CONNECT TO YOUR FIREBIRD DATABSE (THIS WAS DONE ON WINDOWS).
        ### YOU MAY NEED TO FIDDLE WITH '/' VERSUS '\' IF YOU'RE ON LINUX.
        self.cxn = kinterbasdb.connect(host='localhost',database=self.dirname + "\\" + self.filename,
                                        user='sysdba',password='masterkey')
        self.sel = "SELECT RDB$RELATION_NAME FROM RDB$RELATIONS \
        WHERE RDB$RELATION_NAME NOT STARTING WITH 'RDB$' ORDER BY RDB$RELATION_NAME"


        self.cur = self.cxn.cursor()
        self.cur.execute(self.sel)


        for b in self.buttons:
            self.sizer2.Remove(b)
            b.Destroy()


        self.buttons = []
        self.ix=0
        for t in self.cur:
            self.buttons.append(wxButton(self,ID_BUTTON+self.ix,t[0].strip()))
            self.sizer2.Add(self.buttons[self.ix],1,wxEXPAND)
            self.ix=self.ix+1


        self.sizer.Fit(self)
        self.Show(1)




app = wxPySimpleApp()
frame = MainWindow(None,-1,"Sample Editor with Sizer with Tables")
frame.Show(1)
app.MainLoop()


#########################################
## SCRIPT 2 - Put this in a separate .py file.
##########################################


# Make sure have KinterbasDB and wxPython installed on your machine 
# (typically in your python/Lib/site-packages/ directory) 
# so the 'import' calls will work.


# This is a very primitive Python script that displays a Firebird table's records in a HugeTableGrid.


# The user gets the *impression* that they're editing the records in the grid,
# but unfortunately the edits are *not* currently sent back to the Firebird database yet.
# (I need to write some "SQL builder" code to do that.)


# YOU NEED TO MODIFY THIS SCRIPT BEFORE IT WILL RUN ON YOUR MACHINE BECAUSE
# THE PATHNAME TO THE FIREBIRD DATABASE AND THE NAME OF THE TABLE 
# TO OPEN ARE CURRENTLY HARD-CODED IN THIS SCRIPT.
# Look for the two lines set of by comments starting with triple-pound-signs ### below - 
# those are the lines you need to modify.
# I've only been using Python and wxPython for about a week, 
# so this script is just "proof of concept" to connect to Firebird.


import  wx
import wx.grid as  gridlib
import kinterbasdb


#
 
class HugeTable(gridlib.PyGridTableBase): 
    """ 
    This is all it takes to make a custom data table to plug into a 
    wxGrid.  There are many more methods that can be overridden, but 
    the ones shown below are the required ones.  This table simply 
    provides strings containing the row and column values. 
    """ 
    def __init__(self, log): 
        gridlib.PyGridTableBase.__init__(self) 
        self.log = log 
        self.odd=gridlib.GridCellAttr() 
        self.odd.SetBackgroundColour('#FFCCFF') 
        self.even=gridlib.GridCellAttr() 
        self.even.SetBackgroundColour('#FFFFCC')
        ### YOU NEED TO EDIT THE host AND database (AND LIKELY THE user AND password) VALUES
        ### IN THE FOLLOWING STATEMENT FOR *YOUR* ENVIRONMENT.
        self.cxn = kinterbasdb.connect(host='localhost',
                                       database='e:/firebird/isis/isis_1.fdb',
                                       user='sysdba',
                                       password='masterkey')


        ### YOU NEED TO EDIT THE select STATEMENT BELOW FOR *YOUR* ENVIRONMENT.
        self.sel = "select * from Invoice"
        self.curs = self.cxn.cursor()


        self.curs.execute(self.sel)


        # We call list(...) inside the list comprehension [...] to change from list of tuples to list of lists.
        # This is needed because the data will be edited, and the tuples returned by fetchall(...) aren't mutable.


        self.data = [list(i) for i in self.curs.fetchall()]


        # Note: This PyGridTableBase does NOT update the Firebird database with the user's edits.
        # This will require additional code which builds an UPDATE statement based on the user's edits.


        # By the way, this self.data property above - a list of lists - 
        # is a simple example of what www.dabodev.com calls a 'bizobj'.
        # I am new to 3-tier programming (former Access programmer here) 
        # but I'm starting to understand the need for '3-tier':
        # You simply need a place on the local machine to store the data 
        # you're displaying until you're ready to send it back to the backend.
        # I'm reading an O'Reilly book called 'J2EE Design Patterns' which 
        # has a pretty good explanation of Business Objects.


    def GetAttr(self, row, col, kind): 
        attr = [self.even, self.odd][row % 2] 
        attr.IncRef() 
        return attr


    def GetNumberRows(self):
        return len(self.data) + 1


    def GetNumberCols(self):
        return len(self.data[0])


    def IsEmptyCell(self, row, col):
        try:
            return not self.data[row][col]
        except IndexError:
            return 1


    def GetValue(self, row, col):
        try:
            return self.data[row][col]
        except IndexError:
            return ''


    def SetValue(self, row, col, value):
        try:
            self.data[row][col] = value
        except IndexError:
            # add a new row
            self.data.append([''] * self.GetNumberCols())
            self.SetValue(row, col, value)
            # tell the grid we've added a row
            msg = wxGridTableMessage(self,                             # The table
                                     wxGRIDTABLE_NOTIFY_ROWS_APPENDED, # what we did to it
                                     1)                                # how many
            # I DON'T KNOW IF THE ABOVE STATEMENT WORKS OR WHAT IT'S SUPPOSED TO DO - 
            # I JUST FOUND IT IN SOME CODE. 
            self.GetView().ProcessTableMessage(msg)


    def GetColLabelValue(self,col):
        # I actually wrote the following line MYSELF! It figures out the column heads for the grid,
        # based on the .description attribute of the Firebird Cursor
        return [x[0] for x in self.curs.description][col].title()




#
 
class HugeTableGrid(gridlib.Grid): 
    def __init__(self, parent, log): 
        gridlib.Grid.__init__(self, parent, -1) 
        table = HugeTable(log) 
        # The second parameter means that the grid is to take ownership of the 
        # table and will destroy it when done.  Otherwise you would need to keep 
        # a reference to it and call it's Destroy method later. 
        self.SetTable(table, True) 
        self.Bind(gridlib.EVT_GRID_CELL_RIGHT_CLICK, self.OnRightDown)
    def OnRightDown(self, event): 
        print "hello" 
        print self.GetSelectedRows() 
#
 
class TestFrame(wx.Frame): 
    def __init__(self, parent, log): 
        wx.Frame.__init__(self, parent, -1, "Huge (virtual) Table Demo", size=(800,600)) 
        grid = HugeTableGrid(self, log) 
        # grid.SetReadOnly(5,5, True) 
#
 
import sys 
app = wx.PySimpleApp() 
frame = TestFrame(None, sys.stdout) 
frame.Show(True) 
app.MainLoop() 
#


This hasn't been updated in a while. Stay tuned