Re: Getting started - Interfacing questions

----- Original Message -----
From: "David" <dbree(at)duo-county(dot)com>
To: <pgsql-novice(at)postgresql(dot)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.