Re: Application using PostgreSQL as a back end (experienced programmers please)

Thanks Nikola.

Your explanation of layering was helpful. The consensus from you experts
seems to be that I should keep the business rules and logic out of the
database, so I will do that.

As far as using Openoffice goes, I don't think it will provide enough
flexibility and power in the design of forms and reports. I've looked at the
Base forms and reports capabilities in Openoffice 2.0 and don't think that
it is even up to the abilities of MS Access. And MS Access does not have the
form capabilities that I will need. Besides that it (Access) can't be GPL'd.
For business logic, I'm leaning towards Python (because of its computational
abilities) and Tkinter or wxPython for a GUI. Either that or PHP and a web
interface.

BTW. Why are Canadian income taxes so complicated? I don't know. But I can
tell you that I've been a Canadian professional accountant for about 25
years and the Canadian income tax act gets more complicated every
year.Thegovernment is continually adding new provisions and
grand-fathering out
older provisions. Income taxes in Canada are not used solely to finance the
government. They are used to stimulate certain sectors of the economy, to
redistribute wealth and to provide various incentives to individuals and
corporations for any number of reasons (for example there are significant
tax incentives to get individuals to save for retirement). All this adds to
the complexity of the tax provisions. In any case, I imagine that many
countries use their income tax system for more than 'just financing the
government'. :-)

On 11/19/05, Nikola Milutinovic <Nikola(dot)Milutinovic(at)ev(dot)co(dot)yu> wrote:

>
>
> Hi Bill.
>
> Being a programmer of some experience in PgSQL and Java (Tomcat,...) I
> could give you some ideas. Of course, it would be nice if you decided to
> go the Java route, but Java requires considerable learning. If you're
> not planning to really dig into Java, it might not be a good option.
> I'll give you clue why I am mentioning Java, later on. I could be wrong,
> since I have not investigated the matter thoroughly, so if someone has
> more info, please correct me - below.
>
> OK, first of all, the most "correct" way of designing an application
> today is to do it in layers. Typically, you have 3 layers (big
> applications have 4 or 5):
>
> - data storage layer -
>
> This is the database. Be it PostgreSQL, Oracle (en expensive beast),
> MySQL (a DB we all here love to hate :-)) or MS Access, matters not.
> This layer is supposed to store data and allow other layers to store,
> retrieve and query the data. All of the DBs mentioned above do a pretty
> fair job, but some are better than the others. Although most consultants
> will say that PG is mid to large business DB, don't be scared. I think
> it is easier to administer and maintain than MySQL.
>
> Almost all of them allow for some sort of stored procedures. Oracle has
> PL-SQL, MySQL (as of version 5) has SQL stored procedures, MS Access has
> VBA, PostgreSQL has myriads (plSQL, plPgSQL, plPerl, plPython, plRuby,
> plJava, any other?). What is the recommended scope of usage of these
> languages?
>
> Older designs suggest that those procedures should be used for
> implementation of business logic. Current ideology states that business
> logic should be separate from the DB, making the application more
> portable. Stored procedures should be used for data integrity operations
> (triggers) and exceptionally, for utility or facade function. It is
> sometimes much better to store a huge batch processing in a DB
> procedure, instead of placing it in the application layer, thus
> cluttering the communication channel (ODBC, JDBC, DB's native).
>
> - business logic layer -
>
> This is the layer doing the actual work, the "engine" of your
> application. This layer should be outside your DB, allowing you to
> choose the language of your choice and convenience. There at least two
> general purpose mechanisms of connecting to a DB - ODBC and JDBC, plus
> many popular languages have their DB modules for many DBs, PG included.
> Just to mention Perl's Perl::DBD:Pg and PHP's PgSQL module. I believe
> Python and Ruby have their own interfaces. Java uses JDBC.
>
> - presentation layer -
>
> This is your GUI (Graphical User Interface). Again, choose your
> favorite, most likely the one you chose for business logic.
>
> - THE CHOICE -
>
> So, what to choose? Being a Java enthusiast, I would choose Java.
> Depending on the expected usage scenario, I'd choose a web application
> (Tomcat, Struts/JSF, maybe heavy artillery, like Spring+Hibernate) or a
> pure Swing client. Or one other interesting option.
>
> This is why I mentioned Java, remember the clue-clue at the beginning?
>
> Since you're talking Linux, I'd expect that OpenOffice would come into
> the picture. OpenOffice has it's own language, called "StarBasic"
> (OpenOffice is a open source freeware version of Sun's StarOffice). If I
> recall correctly - and experts, please correct me if I'm wrong -
> OpenOffice 1.1 could use JDBC interface to connect it's spreadsheet
> (Calc) to any database. OpenOffice 2.0 has a new module, known as
> "OpenBase". Basically, it is a form/report application intended to be a
> DB front-end. It comes with an embedded HyperSonic SQL DB (a pure Java
> DB), but it can and should connect to any JDBC compliant DB (that's
> about every DB in the world, except MS Access).
>
> How does that sound to you all? OpenOffice, using StarBasic, OpenCalc as
> a spreadsheet, OpenBase as Form/Report generator. It might even have no
> dependency on Java, at all.
>
> > I didn't know that income taxes was a 'holy grail' to linux
> > enthusiasts. The only reason I have been thinking of doing a program
> > myself was that I couldn't find anything on the internet regarding
> > linux and Canadian income taxes and secondly, I have some experience
> > with the subject matter.
>
>
> Why are Canadian taxes so complex, BTW?
>
> Nix.
>