Re: Optimal Postgres Development Process, Software

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