Re: Getting started - Interfacing questions

----- Original Message -----
From: "David" <dbree@duo-county.com>
To: <pgsql-novice@postgresql.org>
Sent: Sunday, March 06, 2005 2:07 AM
Subject: [NOVICE] Getting started - Interfacing questions


> I'm quite a newbie with Postgresql - or any databases whatsoever, for
> that matter.  I've been studying the archives and documentation for the
> past couple of weeks and find it quite fascinating.
>
> I must say that my interest is totally personal. My initial project is
> to convert my personal record-keeping system from a flat random-access
> disk structure to a genuine DB.  My needs for this are quite modest,
> probably a couple or so hundred records for the year - not really in the
> category for needing a true database, but it appears that the
> possibilities for making reports would be limitless.  The current
> structure of my system is something like this.
>
> a payor/payee file, containing the names of people/businesses with which
>   I do business.
> Two Categories files, (income, expense) - the various categories such as
>   Labor, Supplies, etc.
> A data file describing the date, descr, payor/payee, Category,
>   cost/income value
>
> The payor/payee and the Category file entries also have fields for the
>   current totals for the respective element, which was updated with
>   each transaction.  This is no longer needed in my current system, nor
>   would be for Postgresql, but the program was originally written for
>   a 2 Mhz 8-bit computer, and I didn't want to wait for that system to
>   scan over the whole system each time I did a query. Oh, FWIW, the
>   program is written in "C".
>
>
> Currently, the reports I do are:
>
> 1) An itemized list for each transaction, broken down by category
> 2) A summary for the totals for each payor/payee
> 3) A summary for each category
> 4) A summary with incomes/expenses broken down by the month.
>
> Actually, at this point, I've been able to basically get most of what I
> want with psql, with my experimentation, although I'd need to do some
> honing to what I've got before I could get a final working model.
>
> The question I've not yet been able to satisfactorily answer - I suppose
> I could answer it myself with enough experimentation, but would appreciate
> a little jump-start if possible - is the correct interfacing method.  As
> I said, it appears that psql _may_ give me all I need, but what is the
> normal approach to operating a database?  Are real databases actually
> run from psql, or is it better to develop a front-end to the whole
> setup?  One note, in my case, what I'm developing is strictly
> single-user.  If need be, I could do my inserts straight from the SQL
> command, although a forms-type interface (as I have in my current setup)
> would be nice.

While only a single user, you might find building a web-based interface
straightforward, and you can modify the "look and feel" quite easily,
because HTML is built for just that.  If I were you, I would build a
"business logic" component, allowing for querying, inserting, updating,
deleting.  Once you have the interface to the database, you can build a
controller for the application.  Finally, you can use a tool for generating
views of the data.  I use perl and find Class::DBI a good start for the data
model, CGI::application for the controller, and Template::Toolkit (all
available from http://search.cpan.org).  There are numerous other posts with
different answers recently.  Try searching for GUI or forms in the archives.

Sean


> The biggest drawback I'm seeing with psql, unless there's something I've
> not discovered yet, is in making neat-looking reports.  I'm not looking
> for anything extremely fancy - just easily readable and easy to
> interpret.
>
> I installed pgaccess but I'm not sure it will do what I want.  I
> downloaded the datavision sources, but am missing some classes and I
> don't know if I want to fool with java or not.
>
> So, it all boils down to this - can a serious database be run from psql
> or is it better to use something else?  I believe I could write an app
> in "C" without much trouble, using my current program for a pattern for
> the user interfacing, and I'm even thinking about trying my hand
> at perl or python, but it would be nice to do it all from psql if
> possible.