Thread: Getting started - Interfacing questions

Getting started - Interfacing questions

From
David
Date:
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.

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.

Thanks for any suggestions anyone can provide.

Re: Getting started - Interfacing questions

From
"Sean Davis"
Date:
----- 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.



Re: Getting started - Interfacing questions

From
John DeSoi
Date:
On Mar 6, 2005, at 2:07 AM, David wrote:

> 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.

If all you need is light reporting, browsing, and editing take a look
at phpPgAdmin (http://phppgadmin.sourceforge.net/). It is a nice web
based administration interface. If you want to extend it, all of the
source (PHP) is there.

If you want to do everything in psql, you might also want to take a
look at pgEdit (http://pgedit.com/). It provides front end for psql,
including a menu of recently executed files and management of your
connection information.

For reports I want to see on a regular basis, I have found it very
convenient to have a process that runs psql and emails the output to
me.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


Re: Getting started - Interfacing questions

From
David
Date:
Thanks for the reply.  This is my first _reply_ to the list, and IIUC,
this list advocates replying to both the list and individual.  If I'm in
error, please let me know.

On Sun, Mar 06, 2005 at 07:20:25AM -0500, Sean Davis wrote:

> ----- 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

> >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.

This seems like a very good idea.  I think I have another reply from
someone else who suggested php, (a quite similar idea, right?).  So
perhaps this would be a good idea.  FWIW, with my current application,
I'm using groff to do the formatting.

> 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.

With the above and below suggestions, I take it you suggest building the
interfacing into two separate modules, so to speak.. one for inputting
and one for outputting.  For the above, would it be best to create an
app or could all this be done from within psql, or another tool?

> 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.

Yes, I have many of them archived for future reference.  At this time, I
have to take it slowly because many of the concepts are still a bit
vague to me, and I'll have to gain a bit more familiarity before it all
becomes clear.


Re: Getting started - Interfacing questions

From
David
Date:
On Sun, Mar 06, 2005 at 08:20:01AM -0500, John DeSoi wrote:
>
> On Mar 6, 2005, at 2:07 AM, David wrote:
>
> >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.
>
> If all you need is light reporting, browsing, and editing take a look
> at phpPgAdmin (http://phppgadmin.sourceforge.net/). It is a nice web
> based administration interface. If you want to extend it, all of the
> source (PHP) is there.

Thanks for the reply.

I run Debian and this is available as a package.  I will examine it and
see what it can do for me.

> If you want to do everything in psql, you might also want to take a
> look at pgEdit (http://pgedit.com/). It provides front end for psql,
> including a menu of recently executed files and management of your
> connection information.

I've seen reference to this in the mailing lists.  I will examine it.

> For reports I want to see on a regular basis, I have found it very
> convenient to have a process that runs psql and emails the output to
> me.

I'm not sure I'd need to do this.  I may be wrong in my terminology, but
when I refer to reports, I mean listing the complete transactions and
summaries, which would be mostly making hard-copy on demand.  However,
the idea of mailing periodic reports regarding the status of the database
might be a good idea.

Re: Getting started - Interfacing questions

From
"Sean Davis"
Date:
----- Original Message -----
From: "David" <dbree@duo-county.com>
To: "Sean Davis" <sdavis2@mail.nih.gov>
Cc: <pgsql-novice@postgresql.org>
Sent: Sunday, March 06, 2005 2:48 PM
Subject: Re: [NOVICE] Getting started - Interfacing questions


> Thanks for the reply.  This is my first _reply_ to the list, and IIUC,
> this list advocates replying to both the list and individual.  If I'm in
> error, please let me know.

You are not in error. : )

>> 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.
>
> With the above and below suggestions, I take it you suggest building the
> interfacing into two separate modules, so to speak.. one for inputting
> and one for outputting.  For the above, would it be best to create an
> app or could all this be done from within psql, or another tool?

Most of the higher level languages make available (often very powerful)
methods for interacting with a database.  Choose the language you want to
use (perl, php, python, java) and then learn about the methods for designing
web-based applications in that language.  The choice of language will affect
how one designs such an application.  It will generally NOT involve using
psql.  And, in the world of web-based applications, you really can't
separate your logic into "input" and "output" because of the "statelessness"
of the web interaction.  Again, pick the language (php is probably the
easiest to get going with, but if you already have experience with one of
the other languages, use the language of most comfort instead) and then
learn how to do web and database programming with that language.

Sean




Re: Getting started - Interfacing questions

From
David
Date:
On Mon, Mar 07, 2005 at 09:12:53AM -0500, Sean Davis wrote:

> ----- Original Message -----
> From: "David" <dbree@duo-county.com>
> Sent: Sunday, March 06, 2005 2:48 PM

> >Thanks for the reply.  This is my first _reply_ to the list, and IIUC,
> >this list advocates replying to both the list and individual.  If I'm in
> >error, please let me know.
>
> You are not in error. : )

It just seems so strange to hit "g" (Mutt).  I'd get a very strong
chastizing for doing this on most lists  But.. when in Rome..  :)

> >>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.

> >With the above and below suggestions, I take it you suggest building the
> >interfacing into two separate modules, so to speak.. one for inputting
> >and one for outputting.  For the above, would it be best to create an
> >app or could all this be done from within psql, or another tool?

> Most of the higher level languages make available (often very powerful)
> methods for interacting with a database.  Choose the language you want to
> use (perl, php, python, java) and then learn about the methods for
> designing web-based applications in that language.  The choice of language
> will affect how one designs such an application.

OK.  I may be a little dense here, but I take it you're recommending
that perhaps it would be best to write a stand-alone application rather
than interfacing from one of the PL interfaces?

> It will generally NOT
> involve using psql.

That's actually what I was wondering about.  I have been reading the
archives from NOVICE, ADMIN, and USER(?), and was under the impression
that many were actually relying upon psql to do all their work.

This next statement may belong further down, but I'll insert it here..

What I'm looking toward is having the record data input into a form, and
then INSERT'ing the data into the correct table if it's accepted for
commit by the user.  (This was the gist of the discussion in a very
recent thread on one of the lists..  Again, I'll be the only one using
it, and although a bit cumbersome, I _could_ do this from an SQL INSERT
command.

I've looked at some of the general interfaces ( I haven't reviewed Mr De
Soi's interface yet), (hope I got his name right) - I have installed
pgaccess and looked into a couple of others - but there just seems to be
a layer of abstraction with all the ones I've looked at so far.  That
is, although they seem to be able to do some "keen" stuff, it just makes
it so much clearer to have a menu with an option that says, "Add a
transaction" or some such to add a record. It could be that I'm just not
familiar enough with what they can do, however.

> And, in the world of web-based applications, you
> really can't separate your logic into "input" and "output" because of the
> "statelessness" of the web interaction.

OK.  I thought you perhaps meant to write one application containing the
INSERT's, UPDATE's, etc, and then write another to handle the queries.

> Again, pick the language (php is
> probably the easiest to get going with, but if you already have experience
> with one of the other languages, use the language of most comfort instead)
> and then learn how to do web and database programming with that language.

I've never tried anything with php, perl, or python, but I've thought I
should become familiar with at least one.  I've been leaning toward
python.  Most of what I've dealt with in the past several years has been
"C".

In making my choice of the language and interfacing tools, if it were
possible, I'd like to avoid having to install a big lot of things I'd
not need for anything else (although I do have several things like this
already).  For example, the php-based front-ends require apache.  It
just seems that in my case, at least, it's asking a lot to install
apache on a machine that will never do any web-serving elsewhere in
order to access a local database -- but -- perhaps the same could be
said for using postgresql on a database that might see 300 entries at
most, but I "wanna do it"  :)

Re: Getting started - Interfacing questions

From
John DeSoi
Date:
On Mar 7, 2005, at 4:57 PM, David wrote:

> For example, the php-based front-ends require apache.  It
> just seems that in my case, at least, it's asking a lot to install
> apache on a machine that will never do any web-serving elsewhere in
> order to access a local database -- but -- perhaps the same could be
> said for using postgresql on a database that might see 300 entries at
> most, but I "wanna do it"  :)
>

It is definitely true that the web-based front ends need apache or some
other web server, but I want to point out that you don't need a web
server to make use of php. You can create and run php scripts just like
any other shell script. Using the \! command, you could have psql call
your script and I'm sure there is a way to run psql from php. Here is a
reference if you are interested:

http://us3.php.net/features.commandline

This is related to one of the features I'm working for pgEdit -- to use
php as preprocessor for the sql file before passing the output to psql.
This creates some interesting opportunities to parameterize sql scripts
and add a lot data I/O facilities.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


Re: Getting started - Interfacing questions

From
David
Date:
On Tue, Mar 08, 2005 at 01:53:06AM -0500, John DeSoi wrote:
>
> On Mar 7, 2005, at 4:57 PM, David wrote:
>
> >For example, the php-based front-ends require apache.  It
> >just seems that in my case, at least, it's asking a lot to install
> >apache on a machine that will never do any web-serving elsewhere in

> It is definitely true that the web-based front ends need apache or some
> other web server, but I want to point out that you don't need a web
> server to make use of php. You can create and run php scripts just like
> any other shell script. Using the \! command, you could have psql call
> your script and I'm sure there is a way to run psql from php. Here is a
> reference if you are interested:

That's good to know.  At this point I'm trying to settle on the route I
wish to take, and the options seem almost limitless.

One restriction (sort of) that I'm trying to follow is that my system is
Debian and I'm _trying_ to stay within the Debian packaging scheme as
much as possible, although it's not a strict requirement.

> http://us3.php.net/features.commandline.php

I have this page up at the moment and will study it.  Thanks for the link.


Re: Getting started - Interfacing questions

From
John DeSoi
Date:
On Mar 8, 2005, at 9:26 AM, David wrote:

> One restriction (sort of) that I'm trying to follow is that my system
> is
> Debian and I'm _trying_ to stay within the Debian packaging scheme as
> much as possible, although it's not a strict requirement.
>
>> http://us3.php.net/features.commandline.php
>
> I have this page up at the moment and will study it.  Thanks for the
> link.
>

Even if php is not compiled for the command line interface, you can
still achieve the same effect by setting the right parameters (see
table 43-1 on the listed page). So you could put those settings in a
php.ini file and then call php with the -c option.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


Re: Getting started - Interfacing questions

From
Sean Davis
Date:
On Mar 8, 2005, at 10:07 AM, David wrote:

> This came to me privately but not to the list.

Oops, my bad.

> On Tue, Mar 08, 2005 at 06:15:18AM -0500, Sean Davis wrote:
>> I didn't mean to complicate things.
>
> You didn't, really.  What I'm trying to do is to see all my options
> before I get too deeply involved in a certain path.
>
>> If all you are doing is a few
>> inserts a day at home, just use pgAdmin III and add rows to the tables
>> as you see fit.  I like using web-based front-ends to my databases,
>> but
>> that is just me and I (and users of my databases) have specific
>> hardware (multiple platforms, extremely varied performance) and
>> software (I do have apache available) constraints and requirements.
>
> My goal is to try to get a decent setup within reason.  In great part,
> it's basically a learning experience.  What I'm doing here is of no
> great import.  It will never be something that will be worth sharing
> either commercially or even as an open source thing.

> As far as the inputting of data goes, as I said, it would not be a
> tremendous inconvenience to do my INSERT's, etc, with SQL commands, but
> I'd have to remember the field names for a straight INSERT, or remember
> the order for the parameters if I do it through a function.  Probably
> the simplest solution would be to write a specific app, but actually,
> what I was originally wondering was if I could perhaps run from psql
> and
> then call a PL function, perhaps in plperl or whatever, that would put
> up a form and then either insert the record or return the record
> generated.  However, I saw some mention in the mailing list that you
> cannot get input from within a function?
>>

Getting input is the realm of the client application, not the server,
generally, yes.

>> The point of my last email is that there are many ways to accomplish
>> what you are proposing and the only "best" one is the one that teaches
>> you what you want to learn, accomplishes the goals of the project, and
>> has room and flexibility for extension and growth when and where you
>> need it.  Whatever route you choose will have specifics associated
>> with
>> it, some requiring more learning than others.
>
> Exactly.  What I'm seeing is that there are so many ways to go about
> it,
> and numerous languages that will get the job done.  And when it comes
> to
> making the decision upon which language, etc, it's something that no
> one
> can really tell me.
>
> Actually, my original question might resolve to this.  It appears that
> there are two ways to go about it..  You can start from within SQL, and
> work "outward", so to speak, using functions to do your work.  This
> would be the case with psql and perhaps the general front-ends.
> Alterantively, you can start from the "outside" and work "inward".
> That
> would be to create an application written in "C", perl, python, php, or
> whatever, and then do your database work using the calls provided with
> the language.  Is this a reasonable assessment?
>

Actually, all database transactions, etc., will rely on SQL to a large
degree.  I would start with pgAdmin III, as it will graphically allow
you to interact with your database.  Alternatively, you can use pgEdit
(a nice tool with many of the benefits of psql), but you will write .
That is likely all that you will need.  Then, you can go about choosing
the language, API, tool that you can experiment to your heart's
content.  Working with databases is quite well-worked-out for most of
the higher languages (including C), so you really just need to pick one
and start learning how to go about interacting with the database in
that language.  There are typically numerous websites of a tutorial
nature for building database-based scripts/programs/tools.  So, you
will have to commit to something and then let us know when you get hung
up.

Sean


Re: Getting started - Interfacing questions

From
brew@theMode.com
Date:
On Mon, 7 Mar 2005, David wrote:

> For example, the php-based front-ends require apache.  It just seems
> that in my case, at least, it's asking a lot to install apache on a
> machine that will never do any web-serving elsewhere in order to access
> a local database -- but -- perhaps the same could be said for using
> postgresql on a database that might see 300 entries at most, but I
> "wanna do it"  :)

You are thinking and planning too much about this!  IMO, anyway.

The very next step you should do is choose a type of computer (it is
generally agreed the *nix is best for postgreSQL - but some prefer
windows).  Hey, I think you said you've got postgreSQL and psql running
already somewhere, is that on a local windows machine?

Probably the quickest way to get your feet wet is to get an account on a
*nix machine out on the web with PG and the language of your choice
already set up.  Then you copy in one of the simple working sample
programs that use the language you choose and get it set up and running.
Yes, it will be on the web.  This is a good thing.  After all, PG is a
server database and there is a client built right into just about every
computer in the world, the ubiqitious web browser.

By that point you will see what changes you have to make to it for your
needs, you will understand by doing it.

Are you familiar with *nix?

There are those who use windows, but I think many more run PG on *nix
machines.  An analogy here is imagine that you want to go live in Paris
for a year (and you don't know French).  You could do it by seeking out
people of your own language to hang out with.  Or you could just bite the
bullet and learn French which would probably be less work in the long run
than trying to live in Paris without learning French.  And you'd learn
more in the long run.

Sorry for sounding so pompous, but that's the way I learned.  (hopefully
I'm still learning!!!!).

It depends on what your motives are..... if you only want to set up a
small database on a local machine you should probably be using some simple
windows database made for that.  If you want to learn about PostgreSQL you
should probably move to Paris and learn French.  (so to speak.... pun
intended).

Especially if you are doing this as a learning experience.

So what are you running PG on now?

> I've never tried anything with php, perl, or python, but I've thought I
> should become familiar with at least one.  I've been leaning toward
> python.  Most of what I've dealt with in the past several years has been
> "C".

php, perl or python are good choices.  C is good, too, but it will take
much longer to get it running.

But maybe I'm wrong.  If you've got PG running on a windows machine
already maybe someone else here can give you or point you to a simple
example of some C client code that will compile and talk to your database
server.

Or you could stick to the Admin applications that run on windows and talk
to a PG server.

It sort of depends on what your goals are.

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
  Check out my Stock Option Covered Call website  http://www.callpix.com
     and my Musician's Online Database Exchange http://www.TheMode.com
 ==========================================================================


Re: Getting started - Interfacing questions

From
Date:
--- David <dbree@duo-county.com> wrote:
> On Tue, Mar 08, 2005 at 01:53:06AM -0500, John DeSoi
> wrote:
> >
> > On Mar 7, 2005, at 4:57 PM, David wrote:
> >
> > >For example, the php-based front-ends require
> apache.  It
> > >just seems that in my case, at least, it's asking
> a lot to install
> > >apache on a machine that will never do any
> web-serving elsewhere in
>
> > It is definitely true that the web-based front
> ends need apache or some
> > other web server, but I want to point out that you
> don't need a web
> > server to make use of php. You can create and run
> php scripts just like
> > any other shell script. Using the \! command, you
> could have psql call
> > your script and I'm sure there is a way to run
> psql from php. Here is a
> > reference if you are interested:
>
> That's good to know.  At this point I'm trying to
> settle on the route I
> wish to take, and the options seem almost limitless.
>
> One restriction (sort of) that I'm trying to follow
> is that my system is
> Debian and I'm _trying_ to stay within the Debian
> packaging scheme as
> much as possible, although it's not a strict
> requirement.
>
> > http://us3.php.net/features.commandline.php
>
> I have this page up at the moment and will study it.
>  Thanks for the link.

dave,

i'm in a similar situation.  i'm developing a quality
db.  i settled on php/html, apache, adodb and
postgresql 7.4.5 (using cygwin of my dev box, linux
will run on the production box).  i'm also using
manuel lemos' forms class.

i've done a vb / access db and i've done two access /
access dbs - not as my primary job, but b/c they
needed to get done.

this is my first foray into open source application
development.  the approach i'm taking is *not* to try
and create the gold standard code on my first
application - it takes too much time to get anything
done.  i'm gaining valuable experience in why the gold
standard is gold as i'm hacking out bronze.  ;-)

i found something that worked well and then recreated
it as required knowing i could be using classes or
functions for lots of stuff - but not knowing exactly
how.  i will likely go back and update the code to get
it closer to gold standard - except i will then have
the experience and background to better understand
what the heck is going on.

is it more work, yes.  is it a better education?  i
think so.

i'd just pick one and go with it.  is apache really
that much of a drain on the system?  it sounds like
what you really need is something like access
(simplicity to just get the job done) - but i presume
you don't use windows.

what about the db tools available from open office?
i've never used them, but they may be able to help
you.  it may be worth investigating.




__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/

Re: Getting started - Interfacing questions

From
Bruno Wolff III
Date:
On Mon, Mar 07, 2005 at 15:57:51 -0600,
  David <dbree@duo-county.com> wrote:
>
> It just seems so strange to hit "g" (Mutt).  I'd get a very strong
> chastizing for doing this on most lists  But.. when in Rome..  :)

Mutt recognizes mail-followup-to headers. If people on those other lists
don't want personal copies, they should be adding the mail-followup-to
header to their posts. If you normally use 'r' to reply on those lists
their won't be a problem as they are reply-to munged in the first place
and 'g' will normally just reply to the list (unless there are cc
recipients listed) in any case. If you are used to using 'l' to reply to
lists, you won't get bothered too much here about. You should just be
aware that it is possible to post to the lists without being subscribed
and if you reply to someone who does this using 'l', they won't get
a copy of the response. And that sometimes the lists are running slow,
and if you are mostly communicating with one other person, sending them
email directly can speed things up in a back and forth exchange.

Re: Getting started - Interfacing questions

From
brew@theMode.com
Date:
David.....

> One restriction (sort of) that I'm trying to follow is that my system is
> Debian and I'm _trying_ to stay within the Debian packaging scheme as
> much as possible, although it's not a strict requirement.

Oh, good, you *ARE* running *nix!

I run Debian, too.  And I confess.... on all four of my Debian machines I
run packages straight from stable.  PostgreSQL 7.2.1 works fine, even on
my active production web server (although it's a fairly small
database).

The stable Debian packages would certainly do the job for you.

Here are the Apache, php, perl and postgresql packages I have installed,
straight from dpkg -l for your reference.

ii  apache         1.3.26-0woody6 Versatile, high-performance HTTP server
ii  apache-common  1.3.26-0woody6 Support files for all Apache webservers
ii  libdbi-perl    1.21-2woody2   The Perl5 Database Interface by Tim Bunce
ii  perl           5.6.1-8.8      Larry Wall's Practical Extraction and Report
ii  perl-base      5.6.1-8.8      The Pathologically Eclectic Rubbish Lister.
ii  perl-doc       5.6.1-8.8      Perl documentation.
ii  perl-modules   5.6.1-8.8      Core Perl modules.
ii  php4           4.1.2-7.0.1    A server-side, HTML-embedded scripting langu
ii  php4-cgi       4.1.2-7.0.1    A server-side, HTML-embedded scripting langu
ii  phpdoc         20020310-1     Documentation for PHP4 and PHP3
ii  postgresql     7.2.1-2woody7  Object-relational SQL database, descended fr
ii  postgresql-cli 7.2.1-2woody7  Front-end programs for PostgreSQL
ii  postgresql-doc 7.2.1-2woody7  Documentation for the PostgreSQL database.

Hope this helps.

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
  Check out my Stock Option Covered Call website  http://www.callpix.com
     and my Musician's Online Database Exchange http://www.TheMode.com
 ==========================================================================


Re: Getting started - Interfacing questions

From
David
Date:
On Tue, Mar 08, 2005 at 11:02:23AM -0500, Sean Davis wrote:
>
> On Mar 8, 2005, at 10:07 AM, David wrote:
>
> >This came to me privately but not to the list.
>
> Oops, my bad.

Looks like it's back on track :)

> >On Tue, Mar 08, 2005 at 06:15:18AM -0500, Sean Davis wrote:
> >>I didn't mean to complicate things.

> >You didn't, really.  What I'm trying to do is to see all my options
> >before I get too deeply involved in a certain path.

> Getting input is the realm of the client application, not the server,
> generally, yes.

> Actually, all database transactions, etc., will rely on SQL to a large
> degree.

Yes, I understand that.  What I've been concerned about is getting the
data to the SQL interface in a reasonably intuitive fashion.

> I would start with pgAdmin III, as it will graphically allow
> you to interact with your database.  Alternatively, you can use pgEdit
> (a nice tool with many of the benefits of psql), but you will write .

pgEdit would be a good choice, but if I understood what I read on the
web site, it is only available for Windows and Mac?  I took it that
there was no Linux package.  I much prefer to do everything under Linux
- well, actually I only have Win98 and thus no NTFS filesystem which,
AIUI, is practically a requirement.

As far as the GUI interfaces are concerned, although I've not tried
pgAdmin, I've played around with pgaccess a little, but unless I'm
missing something, all I can see it doing is allowing you to write SQL
scripts and does nothing I can't do from psql with little or no extra
effort.

> That is likely all that you will need.  Then, you can go about choosing
> the language, API, tool that you can experiment to your heart's
> content.

Probably that's what I'm in most need of doing, more experimenting.

> Working with databases is quite well-worked-out for most of
> the higher languages (including C),

Yes, I can see that from the descriptions in the documentation.
Practically all seem to be really quite well developed.

> so you really just need to pick one
> and start learning how to go about interacting with the database in
> that language.  There are typically numerous websites of a tutorial
> nature for building database-based scripts/programs/tools.

That's a fact.  There's so much, it's hard to find where to start.
Right now, I'm studying and restudying the HTML documentation that comes
with Postgresql.

> So, you
> will have to commit to something and then let us know when you get hung
> up.

I'm sure I can get hung up easily enough... :)

Re: Getting started - Interfacing questions

From
David
Date:
On Tue, Mar 08, 2005 at 11:54:03AM -0600, Bruno Wolff III wrote:
> On Mon, Mar 07, 2005 at 15:57:51 -0600,
>   David <dbree@duo-county.com> wrote:

> > It just seems so strange to hit "g" (Mutt).  I'd get a very strong
> > chastizing for doing this on most lists  But.. when in Rome..  :)

> Mutt recognizes mail-followup-to headers. If people on those other lists
> don't want personal copies, they should be adding the mail-followup-to
> header to their posts.

Yes, I've seen this recommended.  Also, I think some lists even do this
for you, don't they?  I've not bothered to do this setup because I don't
post to lists a great lot and it doesn't bother me to get mail in my
mailbox for these messages.

> If you normally use 'r' to reply on those lists
> their won't be a problem as they are reply-to munged in the first place
> and 'g' will normally just reply to the list (unless there are cc
> recipients listed) in any case.

It may be in setup, but I experimented with this message from you by
doing a reply and "sending" and after inspecting where it's going,
aborting..  anyway. IIRC, this message was To: me and CC: pgsql-novice
and Sean Davis.

"r" would send to you
"L" to psql-novice and and Sean Davis
"g" will be (I believe) To: psql-novice and Sean Davis and CC: you

[Correction as a PS.  I did a "g" reply for this reply and it still
skipped you.  I can't see the header at this time, but perhaps you have
set your header to do this?]

> If you are used to using 'l' to reply to
> lists, you won't get bothered too much here about.

Again, this may be in my setup, but I _think_ that would skip you (which
is what some lists insist upon).

> You should just be
> aware that it is possible to post to the lists without being subscribed
> and if you reply to someone who does this using 'l', they won't get
> a copy of the response.

IIUC, that's where the poster should set his Mail-Follow-Up To: header

> And that sometimes the lists are running slow,
> and if you are mostly communicating with one other person, sending them
> email directly can speed things up in a back and forth exchange.

Yes, that is a good argument for sending a copy to the sender.  There
are times when a list can be down for quite some time.

Re: Getting started - Interfacing questions

From
David
Date:
On Tue, Mar 08, 2005 at 09:27:39AM -0800, operationsengineer1@yahoo.com wrote:
>
> --- David <dbree@duo-county.com> wrote:
> > On Tue, Mar 08, 2005 at 01:53:06AM -0500, John DeSoi
> > wrote:

> > One restriction (sort of) that I'm trying to follow
> > is that my system is
> > Debian and I'm _trying_ to stay within the Debian
> > packaging scheme as
> > much as possible, although it's not a strict
> > requirement.
> >
> > > http://us3.php.net/features.commandline.php
> >
> > I have this page up at the moment and will study it.
> >  Thanks for the link.
>
> dave,
>
> i'm in a similar situation.

Yes, I've been following that thread closely.  I am keeping it archived
for when I can understand all that's being discussed. :)

> i'm developing a quality
> db.  i settled on php/html, apache, adodb and
> postgresql 7.4.5 (using cygwin of my dev box, linux
> will run on the production box).  i'm also using
> manuel lemos' forms class.
>
> i've done a vb / access db and i've done two access /
> access dbs - not as my primary job, but b/c they
> needed to get done.
>
> this is my first foray into open source application
> development.  the approach i'm taking is *not* to try
> and create the gold standard code on my first
> application - it takes too much time to get anything
> done.  i'm gaining valuable experience in why the gold
> standard is gold as i'm hacking out bronze.  ;-)

Yes.  I think I may be looking too hard at the "gold standard" you
mentioned.  I just need to do more experimenting.

> i found something that worked well and then recreated
> it as required knowing i could be using classes or
> functions for lots of stuff - but not knowing exactly
> how.  i will likely go back and update the code to get
> it closer to gold standard - except i will then have
> the experience and background to better understand
> what the heck is going on.

I think that is necessary to some extent.  We have to start somewhere.
The only problem is that if you get quite a large code base,  sometimes
you have to knock down a whole building and then start almost from the
ground up.  What I'm referring to here is seeing a major hunk of code
that needs to be completely redone.  If this is a production system, it
can get troublesome.

One case in point is the records-keeping system that I'm considering
converting to a postgresql database system.  I originally wrote it
several years ago.  It was and still is text based in its terminal
support.  It originally depended upon termcap support - written for an
entirely different system than Linux or Windows.  A while back, I
decided to convert it to curses terminal support.  Throughout the
program, there are several menus to deal with.  Each routine had to be
rewritten and it took quite a bit of time and work before everything was
working (practically) foolproof again.  Of course, I doubt if one would
come across such a drastic change as that with what we are concerned
with here.

> is it more work, yes.  is it a better education?  i
> think so.

You can certainly learn a lot.  And actually, in a case like this, much
of this can only be learned by hands-on experience.

> i'd just pick one and go with it.  is apache really
> that much of a drain on the system?  it sounds like
> what you really need is something like access
> (simplicity to just get the job done) - but i presume
> you don't use windows.

No, as I said in another message, I don't even have a Windows system
that supports NTFS, plus the fact that I prefer to stay away from
windows as much as possible.

> what about the db tools available from open office?
> i've never used them, but they may be able to help
> you.  it may be worth investigating.

Perhaps.  I have OOo 1.1.2 ...  I haven't looked at whether this version
has DB support or not, but I saw on a recent post in some ML where, as I
understood it, DB support is not until OO. 2.0  I will check it out.


Re: Getting started - Interfacing questions

From
David
Date:
On Tue, Mar 08, 2005 at 11:58:40AM -0500, brew@theMode.com wrote:

> On Mon, 7 Mar 2005, David wrote:

> > For example, the php-based front-ends require apache.  It just seems
> > that in my case, at least, it's asking a lot to install apache on a
> > machine that will never do any web-serving elsewhere in order to access
> > a local database -- but -- perhaps the same could be said for using
> > postgresql on a database that might see 300 entries at most, but I
> > "wanna do it"  :)

> You are thinking and planning too much about this!  IMO, anyway.

Probably so.

> The very next step you should do is choose a type of computer (it is
> generally agreed the *nix is best for postgreSQL - but some prefer
> windows).  Hey, I think you said you've got postgreSQL and psql running
> already somewhere, is that on a local windows machine?

Local Linux machine.

> Probably the quickest way to get your feet wet is to get an account on a
> *nix machine out on the web with PG and the language of your choice
> already set up.  Then you copy in one of the simple working sample
> programs that use the language you choose and get it set up and running.
> Yes, it will be on the web.  This is a good thing.  After all, PG is a
> server database and there is a client built right into just about every
> computer in the world, the ubiqitious web browser.
>
> By that point you will see what changes you have to make to it for your
> needs, you will understand by doing it.
>
> Are you familiar with *nix?

Somewhat.  As stated above, my machine is a Linux machine.

> It depends on what your motives are..... if you only want to set up a
> small database on a local machine you should probably be using some simple
> windows database made for that.  If you want to learn about PostgreSQL you
> should probably move to Paris and learn French.  (so to speak.... pun
> intended).
>
> Especially if you are doing this as a learning experience.
>
> So what are you running PG on now?
>
> > I've never tried anything with php, perl, or python, but I've thought I
> > should become familiar with at least one.  I've been leaning toward
> > python.  Most of what I've dealt with in the past several years has been
> > "C".
>
> php, perl or python are good choices.  C is good, too, but it will take
> much longer to get it running.

I could probably get it going in "C" about as easily as anything else.
That is, if you are talking about writing an app from the ground up.  I
actually have a working program to do what I'm wanting already, but it
is based upon a flat random-access file system.  This program uses
curses-based screen I/O for the menus, forms, etc.  So I think I could
use these and then rather than moving the data to/from the files, move
them to/from the SQL interface and let it handle the storage and
queries.

> But maybe I'm wrong.  If you've got PG running on a windows machine
> already maybe someone else here can give you or point you to a simple
> example of some C client code that will compile and talk to your database
> server.

I don't think I'd have that much problem with the code.

Re: Getting started - Interfacing questions

From
David
Date:
On Tue, Mar 08, 2005 at 10:58:33AM -0500, John DeSoi wrote:

> On Mar 8, 2005, at 9:26 AM, David wrote:

> >One restriction (sort of) that I'm trying to follow is that my system
> >is
> >Debian and I'm _trying_ to stay within the Debian packaging scheme as
> >much as possible, although it's not a strict requirement.
> >
> >>http://us3.php.net/features.commandline.php
> >
> >I have this page up at the moment and will study it.  Thanks for the
> >link.
> >
>
> Even if php is not compiled for the command line interface, you can
> still achieve the same effect by setting the right parameters (see
> table 43-1 on the listed page). So you could put those settings in a
> php.ini file and then call php with the -c option.

That would be quite helpful.  I have thus far merely skimmed over the
document and didn't see that part.  I have a php-cli package available
from my distro (Debian), and can install it all set up to go, I believe.

Re: Getting started - Interfacing questions

From
Bruno Wolff III
Date:
On Tue, Mar 08, 2005 at 19:55:09 -0600,
  David <dbree@duo-county.com> wrote:
> On Tue, Mar 08, 2005 at 11:54:03AM -0600, Bruno Wolff III wrote:
> > On Mon, Mar 07, 2005 at 15:57:51 -0600,
> >   David <dbree@duo-county.com> wrote:
>
> > Mutt recognizes mail-followup-to headers. If people on those other lists
> > don't want personal copies, they should be adding the mail-followup-to
> > header to their posts.
>
> Yes, I've seen this recommended.  Also, I think some lists even do this
> for you, don't they?  I've not bothered to do this setup because I don't
> post to lists a great lot and it doesn't bother me to get mail in my
> mailbox for these messages.

None that I've heard of, but a lot will mung the reply-to header.

> It may be in setup, but I experimented with this message from you by
> doing a reply and "sending" and after inspecting where it's going,
> aborting..  anyway. IIRC, this message was To: me and CC: pgsql-novice
> and Sean Davis.
>
> "r" would send to you
> "L" to psql-novice and and Sean Davis
> "g" will be (I believe) To: psql-novice and Sean Davis and CC: you
>
> [Correction as a PS.  I did a "g" reply for this reply and it still
> skipped you.  I can't see the header at this time, but perhaps you have
> set your header to do this?]

Appearantly I do have mail-followup-to set for the postgres lists. That is
a mistake, since on these lists I actually want the copies because they
are often slow. I will be fixing that.

> > You should just be
> > aware that it is possible to post to the lists without being subscribed
> > and if you reply to someone who does this using 'l', they won't get
> > a copy of the response.
>
> IIUC, that's where the poster should set his Mail-Follow-Up To: header

No you want to do this if you are on the list. You should be getting replies
by default if you aren't. The reason you don't want to make people do it
when they aren't on a list, is that they will have to manually add the
header (which is a bit error prone) since their client won't know that they
are sending to a list. When you are on a list you can tell the client once
(in your .muttrc file for mutt) and then you don't have to futz with it again
unless you unsubscribe from the list.

Re: Getting started - Interfacing questions

From
Date:
> I think that is necessary to some extent.  We have
> to start somewhere.
> The only problem is that if you get quite a large
> code base,  sometimes
> you have to knock down a whole building and then
> start almost from the
> ground up.  What I'm referring to here is seeing a
> major hunk of code
> that needs to be completely redone.  If this is a
> production system, it
> can get troublesome.
>
> One case in point is the records-keeping system that
> I'm considering
> converting to a postgresql database system.  I
> originally wrote it
> several years ago.  It was and still is text based
> in its terminal
> support.  It originally depended upon termcap
> support - written for an
> entirely different system than Linux or Windows.  A
> while back, I
> decided to convert it to curses terminal support.
> Throughout the
> program, there are several menus to deal with.  Each
> routine had to be
> rewritten and it took quite a bit of time and work
> before everything was
> working (practically) foolproof again.  Of course, I
> doubt if one would
> come across such a drastic change as that with what
> we are concerned
> with here.

i agree.  don't get me wrong, i've done a ton of
research and the good folks here have helped out
tremendously.  it does make sense to avoid the big
traps early on.  i'm thinking of issues like oop and
the use of functions.  the code is basically the same,
it is just how it is structured and called.  i'm doing
it inefficiently and i will likely redo it later at
som  cost.

in the meantime, though, i'm actually creating forms
that work instead of reading a textbook and banging my
head ;-)  i'm also learning why that cool stuff is
cool and when i look back into it, i will have a
practical knowledgebase that will provide some context
for me.

> No, as I said in another message, I don't even have
> a Windows system
> that supports NTFS, plus the fact that I prefer to
> stay away from
> windows as much as possible.

i hear ya!

> Perhaps.  I have OOo 1.1.2 ...  I haven't looked at
> whether this version
> has DB support or not, but I saw on a recent post in
> some ML where, as I
> understood it, DB support is not until OO. 2.0  I
> will check it out.

i hope this works out for you - it may be just what
you need.




__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/

Re: Getting started - Interfacing questions

From
David
Date:
On Wed, Mar 09, 2005 at 02:41:44AM -0600, Bruno Wolff III wrote:
> On Tue, Mar 08, 2005 at 19:55:09 -0600,
>   David <dbree@duo-county.com> wrote:
> > On Tue, Mar 08, 2005 at 11:54:03AM -0600, Bruno Wolff III wrote:
> > > On Mon, Mar 07, 2005 at 15:57:51 -0600,

> > [Correction as a PS.  I did a "g" reply for this reply and it still
> > skipped you.  I can't see the header at this time, but perhaps you have
> > set your header to do this?]

> Appearantly I do have mail-followup-to set for the postgres lists. That is
> a mistake, since on these lists I actually want the copies because they
> are often slow. I will be fixing that.

For some strange reason, this message from you has Mail-Follow-up-To
Sean Davis and pgsql-novice.  I believe your previous one was the same.

> > > You should just be
> > > aware that it is possible to post to the lists without being subscribed
> > > and if you reply to someone who does this using 'l', they won't get
> > > a copy of the response.
> >
> > IIUC, that's where the poster should set his Mail-Follow-Up To: header
>
> No you want to do this if you are on the list. You should be getting replies
> by default if you aren't. The reason you don't want to make people do it
> when they aren't on a list, is that they will have to manually add the
> header (which is a bit error prone) since their client won't know that they
> are sending to a list. When you are on a list you can tell the client once
> (in your .muttrc file for mutt) and then you don't have to futz with it again
> unless you unsubscribe from the list.

There are several options that deal with this.  There's the "subscribe"
and "lists" directive, both are somewhat similar but there's a subtle
difference I don't recall just now.  I have a "subscribe" directive in
my ~/.muttrc listing all the lists to which I am subscribed.

There are also options "followup_to", "ignore_list_reply_to", and
"reply_to" which all deal with how mutt sends mail and most affect how
it handles replies to messages from lists.  I don't have any of these in
my .muttrc