Re: Database interaction

On Wed, Apr 28, 2004 at 09:23:22 +1000, Daniel Kasak wrote:

Now seriously. A better way of handling DB updates is needed for 
large-scale projects. You can't expect people to hand-code the updates 
for every single field on every single window. I'm proposing an 
alternative that will same time, and ( God forbid ) encourage more 
people to use Perl & GTK in a corporate environment. Unfortunately for 
me, I simply don't understand enough about OO programming to complete 
the above ideas without spending considerable time, so I'm after 
*constructive* comments ( ie hold off on the 'lazy point-and-click' crap 
please ) to point me in the right direction so I don't waste my time.

Any takers?

Well, you'll most probably have to do most coding, as you are the one
who need it. However, it should not be that hard. But I can give you
some basic hints.

If I understood your previous mails correctly, you want to have a dialog
showing a single record in the database (which may be record from a view
or somesuch, of course) and allowing the user to update it, add new or
delete it alogether. Plus some way of telling it which record it should

Now doing some fancy layout automaticaly would be pretty hard (if not
impossible). But a simple two-column GtkTable with names of fileds in
the left column and GtkEntries (or other widgets where appropriate) for
values in the right is both doable and sufficient. So:

- You'll need a bunch of generic routines to set up appropriate widgets
  for various data types that appear in the database. These will
  - Create appropriate widgets (GtkEntry for text, GtkEntry or
    GtkSpinButton for numbers etc.)
  - Set up relevant signals, passing some "handle" object as the data.
    You could do without OO alltogether, but OO in perl is really,
    really simple.
  They will be called to add lines to the dialog. You should get the
  order and types by parsing the table schema.

- You will need an object for the whole dialog, that will tie the things
  together. All the signals should get it as the user data. It actualy
  don't need to be blessed. Just a reference where you can put the
  database data will do.

- You will need an object representing the record. Again, it don't need
  to be blessed -- the arrayref or hashref given by DBI should do. Just
  you need to be able to replace it in the dialog object (to swithc to
  another record) and you need to be able to construct update query from

- You will need functions (that may, but need not, be called as
  methods), that will do the database operations.
  - There is the updating operation. It should gather the keys from your
    object and issue appropriate update statement.
  - There is fetching next/previous record from a list.
    - You'll need to have some query results connected with the
      controling object.
  - There is searching.
    - For the first step, you can do very simple query with this
      interface -- fill in some fields, leave the rest blank and request
      all records, that match the filled fields. You will need a table
      to get OR.
    - You may want to allow giving SQL commands (depends on skills of
      future users).
  - You will need some buttons to fire the actions. You should make them
    sensitive/insensitive depending on what is currently possible (ie.
    only allow next/previous record if there are some records etc.).

- The generic routines should not be too hard. Most of them will
  probably create GtkEntry. Just bind the relevant signal to a handler,
  that will modify relevant field in the structure representing record.
  For each entry type, there would be one signal handler, that would be
  passed which field it operates on.
- You should check syntactic correctness of the entered values here. You
  may also run integrity-checks. These should of course be one routine
  for each table, run on any change. (In PosgreSQL I'd just have
  a transaction open, update right away and let the database do the
  checking -- and only commit when user clicks "Update". I don't think
  MySQL has integrity checks, but you can do them in perl).
- Generating the SQL update commands shouldn't be hard either. You just
  need to remember which table is the data from, the record ID and which
  fields have changed.

- Now making that to a list (GtkTreeView + custom GtkTreeModel), each
  line being a record won't be too hard either. You should be even able
  to do away with just Gtk2::SimpleList! When you do the select, you get
  (using selectall_arrayref) a list-of-lists. Just assign it to the
  SimpleList with appropriately set columns (again, columns should be
  set up from parsed table schema). You can either use editable cell
  renderers, or copy the values to a dialog described above on selection
  change (easier).

Well, I know it looks like a lot of work. But it's not THAT hard. Feel
free to ask for details, where you can't make sense of the docs.

                                                 Jan 'Bulb' Hudec <bulb ucw cz>

Attachment: signature.asc
Description: Digital signature

[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]