Thread: Optimal Postgres Development Process, Software

Hi,

First a little background:

I am an electrical engineer and have had a decent amount of
experience programming - but mainly languages like matlab, C++, pascal, VB. Over the last few years I have developed
andmaintained an ms access database for a business. It had up to 3 or 4 users at any one time, usually two. 

It was arranged in a split fashion, with a front end and a back end.
Although the backend reached between 10-20 MB in size, it was
extremely slow at times because of the involved business logic in
some of the queries. Some reports were based on > 30 queries,
including VB procedures.

Unfortunately, I had a cheapskate boss and was not permitted to get a
proper database. This was also 5 years ago and I was learning
databases from scratch.

As a result of that experience, I am in the position where I am going
to have to craft a new database from scratch to do the job of the
old. After several weeks/months of searching, I have decided on
Postgres as a backaend after starting to be skeptical of the MySQL
hype machine - it seems like a toy database for what I want. I also
have a pretty good idea what I want from Postgres, although anything
I haven't taken into consideration I'd like to know.

I have also spent a while searching the mailing list archives and
also just browsing the last 6 months of postings to try and find an
appropriate answer.

Please note: I will NOT be using my old Access frontend - I will
create a new frontend from scratch.

So, here are my requirements:

1. I don't want to be stuck with a product that is a pain in the ass
to update with a developer that could leave me stranded, as was the
case with MS Access 97. I want something that will only require me to
do work when the business needs changes, not as a result of another
company's need to design in obsolescence. I.e. Access as a frontend
will be a last resort.

2. I don't want to pay money to a company for no good reason (i.e.
M$) just because they want a regular income stream from me.

3. I want something that I can look at several years from now and
understand it with a minimum of hassle. Something self documenting
would be preferable, or at least something that is easy to document
or is easy to get an overview of. (Since there is a possibility that
one day I might inherit the business, I have the opposite need of
most employees: rather than make myself indispensible I want to make
the database I create something that any competent person can
understand/extend in the future, be it me or someone else.

4. I want something secure; I don't want just anybody accessing this
information.

5. I want something robust; I want something that I can get backups
made of regularly and will only die if the IT department does a poor
job of making sure the supply of power and quality of components is
not up to par.

6. I want something scalable; I don't want to be switching software
halfway down the track because I have 50 concurrent users instead of
3, or the database grows in the number of rows it has, and somehow a
query does not want to ever finish executing because of this. (As a
part of this, I will attempt to do as much work as possible on the
server end rather than client end, e.g. using views to get the
underlying queries the front end relies on.)

7. I want it to be easy to idiot proof it; I want to make it hard for
the end user to break it. E.g. Referential integrity, cascading
deletes, stuff the MS Access relationships window was able to do
well. Even the lookup tables, where adding a category involved the
user having to create a new category with a new autonumber instead of
just typing a word (leading to multiple instances of the same thing).

8. I want to be able to create reports and forms quickly and easily,
and be able to grok them easily in the future so that I can change
them or at least identify where they are.

9. I want to be able to create the queries (or SQL) the reports are
based on quickly and easily. I don't want to compromise on easy
querying at the expense of complex queries (which I suspect that MS
QBE was, though I could be wrong).

10. I want it to be easy to debug; I want to be able to logically
identify where something is going wrong so that I can fix it when it
breaks.

I figure that half the answer is using Postgres as the database
engine, and pushing as much business logic as possible onto it, as it
should be much faster to compare different chunks of RAM than to
send stuff back and forth on a network. I will continue with good
normalization procedure as per Codd, in the same way as I did with ms
access and table design. If I am wrong with any of this, please
suggest an alternative, I doubt I will get anyone telling me to use
something besides Postgres, this is after all a PG mailing list. :)

Now for the other half... how to do the development work as per my
requirements? What software would best satisfy my list of ten
features?

I'd also like to know the process involved... if there is a tradeoff
between learning curve and approaching my ideal development platform
as stated in items 1-10, I'd like to know it.

From the polls I have seen at the postgres site, it seems that
Jasper Reports are most popular for the reporting needs part of my
equation. However, as with Postgres vs MySQL, judging things by number of votes needs to be done with qualifications.
Nosense in blindly following the herd over a cliff. 

http://www.postgresql.org/community/survey.45

I would also like to know how best to map my process for development from ms access to Postgres + whatever you
recommend.To assist you, my process for development in Access was roughly (from memory): 

1. Figure out what tables I need and which columns are needed in the
tables. Do this on paper.

2. Create the said tables on the access backend.

3. Open up the relationships window, view the tables I had just
created and drag links across, then decide one to many etc, cascading
deletes/updates etc.

4. Open up the tables again and do things like add indexes, constrain
user input as appropriate in particular columns, etc, set up lookup
tables etc.

5. Create some input forms, enter some test data, see if they work
ok. If necessary, spend ages on the internet/ ms access help/
computer books looking for how to implement exactly what I want to do
with VB.

6. Create queries needed by reports. See if test data works out ok
and if it is my query or underlying table structure that is causing
problems if it doesn't (usually it is the query, as I was pretty good
at designing the tables). Debug the queries. If necessary, spend ages
on the internet/ ms access help/ computer books looking for how to
implement exactly what I want to do with VB.

7. Create the reports that go on top of the final query just debugged.

8. When a report breaks down, find the underlying query and keep
going down until the previous query doesn't give an error, then fix
the error.

This was all done within MS Access (except the first step, which was on paper).

I'd be curious how people do much the same thing while using Postgres
and either Access or other aftermarket software. Even with the
technical documentation of Postgres, the tutorial etc. I don't get a
good feel for what the analogues of the various processes I did
before are.

I think it would help not only me but anyone else who is
deciding to upgrade, as there are lots of Access holdovers in the same boat.

Lastly, any books to recommend? I have printed out the latest online
PG documentation plus ordering in "Beginning Databases with
PostgresSQL: From Novice to Professional 2E", as that seems to get the best reviews on Amazon.

So, in summary:
1. What software/languages to use for everything? (Even text editors,
if necessary.)
2. What development process to use? How do each of my steps (and any additional ones you might think of) map from
Accessto your solution? 
3. If there is a learning curve/development speed (or something else)
trade-off decision to be made depending on software/language choice, what is it?
4. Recommended books/resources?

Thanks in advance,
pgsqln00b
p.s. Hope I haven't left anything out... ;)

--
___________________________________________________
Play 100s of games for FREE! http://games.mail.com/

From Roger:

So, in summary:
1. What software/languages to use for everything?
(Even text editors,
if necessary.)
2. What development process to use? How do each of my
steps (and any
additional ones you might think of) map from Access to
your solution?
3. If there is a learning curve/development speed (or
something else)
trade-off decision to be made depending on
software/language choice,
what is it?
4. Recommended books/resources?

-----------------------

Roger, i think you picked the right db, for starters.
i wouldn't be so negative on mysql, though, it
obviously does a lot right.  i just prefer pgsql of
the two very functional dbs - probably for many of the
same reasons you did.

i think you'll enjoy pgadmin3 - it is a gui interface
to pgsql.  it provides methods to do almost anything
you'll ever need - especially the basics.

you choice of a front end language is really up to
you.

i started out with php - its syntax is c-ish, or so
i'm told.

php is primarily used for web based front ends.  i use
a forms class (very helpful) and the adodb db
abstraction layer class.

i'm currently learning ruby and some ruby on rails.
ruby is the language, and rails is the web db
framework.

if a web interface is what you desire...  research php
and ruby on rails.

if i understand correctly, ruby may also be used to
develop an executable type program with a gui.  i
don't know enough about your app (or ruby) to say it
would fit.

i pretty much roll my own reports at this point.  i
query the db and disply the results in a browser.

i am interested in feedback on reports, though.

i think i will use php to run the queries and export
to a csv file and then use ruby to take that file an
import it into a spreadsheet - the spreadsheet being
the report.  i'm on the edge of my skillset, though,
so i can't offer to much value here.

make the interface decisions and then decide on a
language that can get it done and *get started*.  stay
in touch here and with the forums, newsgroups, mailing
lists, etc. for the language / framework that you
choose.

good luck.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Roger Rasmussen schrieb:
> First a little background:
>
similar to mine, though you seem to have put more heart and brain at the
whole stuff.


> So, in summary:
> 1. What software/languages to use for everything? (Even text editors,
>
> if necessary.)
>
PG comes with PgAdmin which is a GUI tool to tweak and edit the database
for maintenance and data manipulation. A dba or a trusted and
knowledgeable user might find it handy.
You won't let yust any user run SQL at your db so you'll still need some
frontend that has nice dialogs an buttons that do the magic in everydays
work.

As of languages ... well it depends on your taste.
You allready got the hint to use PHP for a webfrontend. PHP as such has
no debugger - at least AFAIK - so one might run into some problems when
the stuff gets complex. I only used it for some tiny scipts dynamic
website, yet. So I might talk bullshot. There is a development
environment, that didn't really find my liking 2 years ago.

You could go with all other languages that can talk ODBC. JAVA, .net,
C++, Python and what ever you like.

You allready used PASCAL.
What about Borland's Delphy then ?

You mentioned "Jasper Reports". That's a JAVA project.
JAVA might be interesting as it is pretty platform independent, or
should be.

Thing is ... you have to learn JAVA first and get rather good at it to
produce some respectable code for your application. Even if you try to
use an IDE like Netbeans or Eclipse because of it's editor, debugger and
GUI designer you will have to learn even more to use those too before
you can do sensible work for your own app.

You should decide about your workplace first.
Will there allways be Windows or maybe something different. Webfrontends
arent as slick as native software but a client would only depend on a
browser.
In that case you'd like to learn about the server-os, application-server
and web-server stuff besides postgres.

You mentioned that you consider yourself capable to push most of the
business logic onto the server side. You might go with an office-suite
like OpenOffice. It is said to be sciptable even in Python and JAVA
besides its Basic dialect.

On the other side you allready have enough MS-Access licences for all
your users, I suppose.
You could consider to stay with Access as frontend for the time beeing.

> 2. What development process to use? How do each of my steps (and any additional ones you might think of) map from
Accessto your solution? 
>

There are tools that automatically extract the datastructure of at least
older Access versions and push the data from Access to PG. Easier said
than done.

 From your history I deduce that you have a grip at the db design. I'd
analyse the referntial dependencies by hand. Then create the tables in
the required order and link them into an Access application that that
has also linked your tables in the existing Access backend.
Use textfiles with CREATE TABLE queries for every table and dump them
into psql.
There is a VBA command that lets you define different internal names for
tables than in the backend.
Now pipe the data through.
You'll probaply run into data format issues or referential integrity
errors that Access didn't bother to complain about up until now.

Now you could use your existing application until you got cosy with
postgres.
You could even create a new Access application relying on your skills
without having to master postgres and additionally a lot of other stuff,
too.

Access VBA is far but perfect, I know and there are things to mind in
connection with Access and Postgres.
* DateTime is timestamp(0) <-- mind the (0)
* varchar have a default max length of 254 in the odbc settings, though
postgres supports up to 8196 (I think). You might have used Access's max
255 somewhere so change this value in the odbc settings.
* Access can't use ADODB pass-through queries as datasource for subforms
and much less for listboxes et.al. (THAT SUCKS) This is true with other
RDBMs than MS's SQL.
* You can use pass-though querydefs as datasource for forms and subforms
if it doesn't bother you that they are read only then.
* Access sees remote views as tables without primary key that are not
updatable.
You can link the relevant tables and do the view locally within Access.
Then its probaply updatable.
* booleans can be tricky
* Access ignores case while comparing strings. Postgres compares strict.
* tables where data not only is inserted but should be editable
afterwards need a primary key and should have a timestamp
* activate rowversioning in the odbc driver

In an idealistic world one would migrate everything away at once but in
cruel reality one stays with stuff one knows and do small steps ... JAVA
perhaps ... sigh ... but time is a expensive resource.


just my 2 €cents

Andreas

>> In an idealistic world one would migrate
>> everything away at once but in
>> cruel reality one stays with stuff one knows
>> and do small steps ...

Andreas gives excellent advice.

i think converting Access to a front end for PGSQL is
a great idea - then you can focus your energy learning
to administer PGSQL and spend some down time reading
up on and evaluating languages.

when the time comes, start working on the front end
and ditch Access once and for all.


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com