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