Thread: Optimal Postgres Development Process, Software

Operationsengineer,

Thanks for the feedback.

To be honest, I'm not at all excited about having to use a web
browser as a means of data entry. Compared to an access frontend,
they are clunky and slow. I suppose that they might be able to
achieve some security through password and IP restriction? Again, I
am a novice so help me out here. But a browser interface would be
an absolute last resort. Rather than use a browser, it would be
preferable to do something similar to the procedure outlined here
with PG + Access97:

http://sevasoftware.com/access/index.html#Reasonforporting

I'm curious to hear from someone who had similar priorities, tried
a few different things, found something that worked well, and can
explain how to map from a typical access process to a postgres +
whatever process.

For example, when I hear that people can do all the backend table
creation stuff via the command line, I'd love to know how they can
see everything they have created and test that it works, as you can
easily do in Access. As I said, a mapping from Access to PG +
whatever. Surely it shouldn't be too hard for someone who made the
transition from Access to PG to give a brief explanation of what
the 8 steps in my database creation process are in their PG
environment. Even someone who just has a good grounding in PG and
making solid internal business database applications should be able
to do it.

It's not as if my needs are unique. Someone upgrading from access
needs the scalability, robustness, ability to handle complexity and
ease of interfacing that postgres provides. Databases used
internally by businesses tend to have a few users performing _lots_
of data entry/transactions/reporting, rather than lots of users
performing small chunks of work. There has to be some sort of open source front end solution that is tailored to this
typeof work, where a mouse is touched only when absolutely necessary. 

And I don't mind waiting a week or more before I make an
interface/language decision. IME it's rarely a good idea to dive in
at the deep end without getting a good sampling of opinions from
those who have already gone before. What's an extra week (or
month!) if 6 months (or 6 years) down the track you suddenly
realize that you have made the wrong decision and need to rework
everything? For example, I took a good 3 weeks or more to decide on
postgres. If you had asked me early last week which backend I was
going to use, I would have told you MySQL.

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


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

hi roger...
Sorry  havent followed this converstation from the start and Im afraid I
dont fit into the' have tried this and it works well' category, but it
DOES rate highly on my list of things to do:
www.dabodev.com and might be worth you while to look - more for
generating the user interface than the schema though.


On Wed, 2006-08-16 at 01:26 -0500, Roger Rasmussen wrote:
> Operationsengineer,
>
> Thanks for the feedback.
>
> To be honest, I'm not at all excited about having to use a web
> browser as a means of data entry. Compared to an access frontend,
> they are clunky and slow. I suppose that they might be able to
> achieve some security through password and IP restriction? Again, I
> am a novice so help me out here. But a browser interface would be
> an absolute last resort. Rather than use a browser, it would be
> preferable to do something similar to the procedure outlined here
> with PG + Access97:
>
> http://sevasoftware.com/access/index.html#Reasonforporting
>
> I'm curious to hear from someone who had similar priorities, tried
> a few different things, found something that worked well, and can
> explain how to map from a typical access process to a postgres +
> whatever process.
>
> For example, when I hear that people can do all the backend table
> creation stuff via the command line, I'd love to know how they can
> see everything they have created and test that it works, as you can
> easily do in Access. As I said, a mapping from Access to PG +
> whatever. Surely it shouldn't be too hard for someone who made the
> transition from Access to PG to give a brief explanation of what
> the 8 steps in my database creation process are in their PG
> environment. Even someone who just has a good grounding in PG and
> making solid internal business database applications should be able
> to do it.
>
> It's not as if my needs are unique. Someone upgrading from access
> needs the scalability, robustness, ability to handle complexity and
> ease of interfacing that postgres provides. Databases used
> internally by businesses tend to have a few users performing _lots_
> of data entry/transactions/reporting, rather than lots of users
> performing small chunks of work. There has to be some sort of open source front end solution that is tailored to this
typeof work, where a mouse is touched only when absolutely necessary. 
>
> And I don't mind waiting a week or more before I make an
> interface/language decision. IME it's rarely a good idea to dive in
> at the deep end without getting a good sampling of opinions from
> those who have already gone before. What's an extra week (or
> month!) if 6 months (or 6 years) down the track you suddenly
> realize that you have made the wrong decision and need to rework
> everything? For example, I took a good 3 weeks or more to decide on
> postgres. If you had asked me early last week which backend I was
> going to use, I would have told you MySQL.
>
> > 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.
>
>

> > 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
dayor 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,
ormake it platform independent. If one day open source does start ripping large swathes of market share away from M$,
I'dlike 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
haveto 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
technicalbooks. All you really need to know is the basics so that you can get your feet wet, then you can figure out
whatto 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
withoutaccess 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
frontends. Since then I have been adjusting queries from there. Eventually now it is broken and I can't fix it, as
thereis 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
comfortableworking in that environ, I plan to port the original backend to postgres and then build up a new frontend
fromscratch. 

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

operationsengineer wrote:
> 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.

I think a variation on this theme will work well. I will start coding the backend of a small database (e.g. stockroom)
withpostgres, to learn how it works. At the moment I am a bit scared at the magnitude of the task, so a small database
willgive me opportunity to experiment with types etc. As I have questions, I will ask here. 

I will then experiment with various front ends. One of which will be dabo, as Glenn suggests. Meanwhile maybe someone
herewill offer up a holy grail I hadn't thought of. 

If nothing else is suitable, I will continue with access - either 97 or 2003. I do like the pivot table/chart feature
thatit has, that functionality would be excellent for dabo to emulate. 

Thanks for all the help, and if anyone has more thoughts on this topic I am all ears. I think I will formulate some
questionsas to the first part of my project in another email. 

Cheers,
pgsqln00b

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

On 8/16/06, Roger Rasmussen <pgsqln00b@australiamail.com> wrote:

> > 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?
Yes for all of the above.

> 2. How fast is it to code up forms with them compared to
> access, for example?
Depends.  There's glade, for instance, as a GUI builder.  Or you
could use Qt (which has Postgres support) and it's tool designer
for the front-end.

> 3. How easy is it to remember what does what when it is time to
> maintain what you have created?
Depends on your memory ;)
No, serious, it just is a matter of how well you name your stuff,
and how good you document it.




> 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.
If Access is your thing you may want to look into rekall ... it's originally
designed under Linux (KDE), but I've seen notes of it being ported to
Windows as well.  It interfaces to MySQL, Postgres and xbase databases.


Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

hey roger - again something else I haven't played with, but given your
access and doutless vba background, this might be right up your alley

http://gambas.sourceforge.net/


On Wed, 2006-08-16 at 03:01 -0500, Roger Rasmussen wrote:
> operationsengineer wrote:
> > 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.
>
> I think a variation on this theme will work well. I will start coding the backend of a small database (e.g.
stockroom)with postgres, to learn how it works. At the moment I am a bit scared at the magnitude of the task, so a
smalldatabase will give me opportunity to experiment with types etc. As I have questions, I will ask here. 
>
> I will then experiment with various front ends. One of which will be dabo, as Glenn suggests. Meanwhile maybe someone
herewill offer up a holy grail I hadn't thought of. 
>
> If nothing else is suitable, I will continue with access - either 97 or 2003. I do like the pivot table/chart feature
thatit has, that functionality would be excellent for dabo to emulate. 
>
> Thanks for all the help, and if anyone has more thoughts on this topic I am all ears. I think I will formulate some
questionsas to the first part of my project in another email. 
>
> Cheers,
> pgsqln00b
>

Actually appologies - I forgot in my excitment that you need windows
clients. doh. too bad I'm apt-get installing into ubuntu as we speak.
sorry again
On Wed, 2006-08-16 at 23:41 +1000, Glenn Davy wrote:
> hey roger - again something else I haven't played with, but given your
> access and doutless vba background, this might be right up your alley
>
> http://gambas.sourceforge.net/
>
>
> On Wed, 2006-08-16 at 03:01 -0500, Roger Rasmussen wrote:
> > operationsengineer wrote:
> > > 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.
> >
> > I think a variation on this theme will work well. I will start coding the backend of a small database (e.g.
stockroom)with postgres, to learn how it works. At the moment I am a bit scared at the magnitude of the task, so a
smalldatabase will give me opportunity to experiment with types etc. As I have questions, I will ask here. 
> >
> > I will then experiment with various front ends. One of which will be dabo, as Glenn suggests. Meanwhile maybe
someonehere will offer up a holy grail I hadn't thought of. 
> >
> > If nothing else is suitable, I will continue with access - either 97 or 2003. I do like the pivot table/chart
featurethat it has, that functionality would be excellent for dabo to emulate. 
> >
> > Thanks for all the help, and if anyone has more thoughts on this topic I am all ears. I think I will formulate some
questionsas to the first part of my project in another email. 
> >
> > Cheers,
> > pgsqln00b
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Ok, a summary of the suggestions so far:
http://gambas.sourceforge.net/ (for linux)

JAVA (Netbeans/Eclipse as IDE), .net, C++, Python, all have ODBC and
can build native clients.

Glade, (for gnome only?)
http://glade.gnome.org/

or Qt (which has inbuilt  postgres support). - C++?
http://www.trolltech.com/products/qt

pgadmin3
http://www.pgadmin.org/

Rekall
http://www.thekompany.com/products/rekall/reviews.php3?PHPSESSID=1f9af609cd5ef61aece8004fb5cf1039
http://www.rekallrevealed.org/kbExec.py (but enable javascript if it
is turned off!)
Tutorial on rekall:
http://www.linuxmuse.com/articles.php?action=section&article=33&num=1

Dabo
www.dabodev.com

kexi
http://www.kexi-project.org/wiki/wikiview/index.php?KexiComparisons
(good list of comparisons between different access competitors)

Oo.org Base
www.openoffice.org

Delphi (Pascal) $

MS Access ($)

----
psqln00b

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

On 8/18/06, Roger Rasmussen <pgsqln00b@australiamail.com> wrote:

> JAVA (Netbeans/Eclipse as IDE), .net, C++, Python, all have ODBC and
> can build native clients.
I just can't stand that :} ... Java, that is.


> Glade, (for gnome only?)
> http://glade.gnome.org/
http://gladewin32.sourceforge.net/modules/news/
You'll have to tie the db stuff in using C.


> or Qt (which has inbuilt  postgres support). - C++?
> http://www.trolltech.com/products/qt
Indeed C++ ... very clean class-library, and I really like
their signal-/slot-concept


> pgadmin3
> http://www.pgadmin.org/
It's more of an administrative interface, I don't think you'd want
your users to use that.


> Rekall
http://www.thekompany.com/products/rekall/reviews.php3?PHPSESSID=1f9af609cd5ef61aece8004fb5cf1039
> http://www.rekallrevealed.org/kbExec.py (but enable javascript if it
> is turned off!)
> Tutorial on rekall:
> http://www.linuxmuse.com/articles.php?action=section&article=33&num=1
Looks better and better every time I test it.

> Delphi (Pascal) $
Or Borland C++ builder (does the same things, uses
C++) ...  they may even have a C# version these days,
I'm not keeping up with Windows stuff ;}

> MS Access ($)
But, but ... that's what you wanted to get away from! :}



Cheers,
Andrej

hey roger -have you checked www.mono-project.com

This vmware image will let you play :
www.mono-project.com/news/archive/2006/Jul-28.html

it seems it supports windows.forms now given you want a windows gui,
though apparently people have made cross platform gtk apps  with, so
perhaps you can lever glade yet! Also supports postgres

There's also a range of languages available - incl vb (albeit .net)
which isnt a qantam leap from vba which you'll already know from access.
But seeing as you in the mood for a change, well boo or ironPython might
tickle your fancy!
http://www.mono-project.com/Languages

have to admit, like everything else ive suggested in this thread - I
havent tried it yet.

glenn
On Thu, 2006-08-17 at 20:39 -0500, Roger Rasmussen wrote:
> Ok, a summary of the suggestions so far:
> http://gambas.sourceforge.net/ (for linux)
>
> JAVA (Netbeans/Eclipse as IDE), .net, C++, Python, all have ODBC and
> can build native clients.
>
> Glade, (for gnome only?)
> http://glade.gnome.org/
>
> or Qt (which has inbuilt  postgres support). - C++?
> http://www.trolltech.com/products/qt
>
> pgadmin3
> http://www.pgadmin.org/
>
> Rekall
> http://www.thekompany.com/products/rekall/reviews.php3?PHPSESSID=1f9af609cd5ef61aece8004fb5cf1039
> http://www.rekallrevealed.org/kbExec.py (but enable javascript if it
> is turned off!)
> Tutorial on rekall:
> http://www.linuxmuse.com/articles.php?action=section&article=33&num=1
>
> Dabo
> www.dabodev.com
>
> kexi
> http://www.kexi-project.org/wiki/wikiview/index.php?KexiComparisons
> (good list of comparisons between different access competitors)
>
> Oo.org Base
> www.openoffice.org
>
> Delphi (Pascal) $
>
> MS Access ($)
>
> ----
> psqln00b
>

Hi Roger,

it'd be really nice, if you could write some documentation on your
progress somewhere.

You should include the steps you followed in a direction an what
obstacles you mastered or which problems made you dismiss a certain system.

I guess this could prove helpful for others that want to follow.
There must be a lot who just keep using non opensource stuff because
they had to find lots of solutions for every kind of common task.
Opensource is all about choice but a lot of choice means risking a lot
of dead ends, too.


Roger Rasmussen schrieb:
> Ok, a summary of the suggestions so far:
> http://gambas.sourceforge.net/ (for linux)
>
> JAVA (Netbeans/Eclipse as IDE), .net, C++, Python, all have ODBC and
> can build native clients.
>
> Glade, (for gnome only?)
> http://glade.gnome.org/
>
> or Qt (which has inbuilt  postgres support). - C++?
> http://www.trolltech.com/products/qt
>
> pgadmin3
> http://www.pgadmin.org/
>
> Rekall
> http://www.thekompany.com/products/rekall/reviews.php3?PHPSESSID=1f9af609cd5ef61aece8004fb5cf1039
> http://www.rekallrevealed.org/kbExec.py (but enable javascript if it
> is turned off!)
> Tutorial on rekall:
> http://www.linuxmuse.com/articles.php?action=section&article=33&num=1
>
> Dabo
> www.dabodev.com
>
> kexi
> http://www.kexi-project.org/wiki/wikiview/index.php?KexiComparisons
> (good list of comparisons between different access competitors)
>
> Oo.org Base
> www.openoffice.org
>
> Delphi (Pascal) $
>
> MS Access ($)
>
> ----
> psqln00b
>
>