Re: Optimal Postgres Development Process, Software

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

I don't know about that. :) I just hate wasting hundreds of hours of work, that's all.

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

Ok. I have downloaded it, will have to use it.

> You won't let just 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.

Exactly.

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

1. Can they be used to build non-browser type applications?
2. How fast is it to code up forms with them compared to access, for example?
3. How easy is it to remember what does what when it is time to maintain what you have created?

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

I'd rather go with something free, if possible.

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

Thanks.

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

EXACTLY. With Access once started, creating a simple form usually took less than an hour. A complex form might take a day or two, but usually there is only a small few of these.

> You should decide about your workplace first.
> Will there allways be Windows or maybe something different.

Good question. I would say windows for the forseable future. However, I'd like to migrate as much as possible to linux, or make it platform independent. If one day open source does start ripping large swathes of market share away from M$, I'd like to be there to adopt relatively early.

It is likely that Office will continue to be purchased.

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

As stated in the previous email, from what I've seen of web interfaces, native software is really a must.

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

Hmmm. I did download it before... it just seems so slow. I suppose it might be worth a try though. I guess you'd only have to load it once.

I suppose if most of the queries and stuff is server side, and a lot of the input checking is also server side (triggers?), then there really isn't a lot for an access clone to do. You wouldn't need to query (it would be done from views), reporting would be pretty simple, although some of the complex forms might need some work.

As far as me being capable... well, I don't know how to do it yet, but that's what mailing lists like this are for. And technical books. All you really need to know is the basics so that you can get your feet wet, then you can figure out what to google to look for more.

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

Yes. This is my default position. If someone out there is pumping out excellent, internal business type database apps without access but using postgres, I'd love to know how.

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

Yes. One thing I haven't said... a few years ago, I lost the original database file and the only thing left was .mdb front ends. Since then I have been adjusting queries from there. Eventually now it is broken and I can't fix it, as there is no way to hack into the mdb and find your VB code etc.

My plan is to code a pilot type database with PG and whatever frontend/language I decide to go with. After I feel comfortable working in that environ, I plan to port the original backend to postgres and then build up a new frontend from scratch.

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

Thanks.

> 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

Thank you very much for the detailed information!!!

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

Yes, exactly. Thank you very much for the insight, Andreas.

-postgreqln00b

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