Re: Database interaction

[Daniel Kasak <dkasak nusconsulting com au>]

A. Pagaltzis wrote:

* Daniel Kasak <dkasak nusconsulting com au> [2004-04-27 04:33]:
  

A tabular view would also be nice, but would be a 2nd priority for me.
    

Ah. Well then there isn't any need to throw Gtk2 into the mix.
You're not doing anything graphical, so you shouldn't be using a
widget toolkit in the first place. That would be a crutch by
systems which train the user to do point-n-click programming.

  

Not doing anything graphical? I think you misunderstood what I'm doing. I want an automated ( or at least semi-automated ) way of binding input fields on MANY forms to a database. I'm trying to rewrite one of our simpler DB front-ends which is currently in Access ( with a SQL Server and MySQL backend ) to Perl GTK2.

Create a custom object - lets call it a DBO. A DBO has:
    

No, let's call it a DBI handle. :-)

  

You're missing the point. I want an object that will automate the binding of input fields to data in our database. It should perform tasks such as fetching records from the DB ( yes, through DBI ), and sending any updates necessary back to the DB ( also through DBI ). Coding all this manually is all very well for simple dialogs, but when you're looking at thousands of Access forms which themselves have relatively complicated logic behind them, it's just not feasible to use DBI directly. It *needs* some form of automation.

- name
- DB connection string
- SQL
- Primary Key
- command [ refresh, execute, update, delete ]
    

Well, it doesn't have SQL, you give it SQL, and that is the way
the "command" bit is offered. That would be pretty much what you
get with DBI.

  

So if I created a DBO object and called it 'customers', I would
then create fields named:

dbo_customers_customer_id
dbo_customers_first_name
dbo_customers_last_name
    

Sounds like designing the database around the GUI.. ugh.

  

Other way around dude. The database is very much in existance already. I'm simply using object names as a way of associating particular input fields with database fields. I could use a mapping table - in the database or in an xml file or something - to overcome this need, but frankly I'm more interested in getting something up and running.

And the DBO object would loop through each object on the
window, looking for things which were associated with it by
name ( ie started with 'dbo_customers' ) and interact with them
accordingly ( update their values, update the DB with their
values, delete, etc ).
    

Here's where you lost me. Do you mean "present input widgets"
when you say "interact"? And what objects are looping over?

  

OK.

Say I have fields in a table called 'parts' ( which I don't, but it suits my example, OK? ):

id
part_name
part_description
supplierid
cost

I then go into glade and create entry fields, and call them:

dbo_parts_id
dbo_parts_part_name
dbo_parts_part_description
dbo_parts_supplierid
dbo_parts_cost

Then I add my DB interaction object to the window. When the windows opens, I call the 'refresh' method, which looks at it's SQL and DB string and fetches the data via DBI. It then looks at the list of fields is has, and looks at the fields that are on the same window that it's on, and sets their value accordingly. See how it saves programming time?

The object has an 'update' method, which will do the opposite. It will loop through all fields on the form associated with it by name, and run an update command on the DB based on the primary key.

Is this the right way to be going about things?
    

For what you need, I'd simply slap a few widgets on a dialog,
then connect a few signals to fire off SQL queries as needed.
There's a number of modules on CPAN to make abstracting SQL
queries (aka business logic) away from your frontend easier.

  

I disagree. That's just far too much work. I seriously need a way of automating things, or I'm going to be pissing around with redundant code until next century.

I realise that the bit where I name objects with dbo_customers
is a little dodgy, but it would require the least mucking
around with other things that I don't currently understand.
    

Database design is something you should at least have a basic
grasp of before you go designing a database application.
Seriously, you'll do yourself a huge favour in the long run if
you take a bit of time to learn about good database design
practices. You don't have to become an expert, but you should
know a few ground rules.
  

Thanks for the tips. I've been at this for 7 years now, 5 of which I've spent mostly with MySQL. I'm certainly no beginner wanting to do a home recipe collection. I've even working on a HOW-TO for Access & MySQL newbies, up at: http://enthalpy.homelinux.org/MySQL/ - it has some basic setup instructions, along with some tips on optimising MySQL, and a TCO analysis ( in the works ).

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?

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak nusconsulting com au
website: http://www.nusconsulting.com.au

Title: CanIt Vote for ID 35134

The following links have been placed here by the NUS Consulting internal spam filter and are for use by NUS Consulting staff only.
Please ingore these links.
Spam
Not spam
Forget previous vote