Standard Database Setup with Perl and PostgreSQL: Part 3

1. Corrections and Updates
2. Introduction (way too long)
3. Standard Database Setup (SDS)
4. Perl script to setup my SDS environment with Perl and PostgreSQL.
5. How the perl script is setup to work.
6. Conclusion
7. References

Corrections and Updates

1. Search and replace "GES" with "SAMPLE" in the two files:
   misc/nielsen/Test/Templates/Set_Info.pm_header
   misc/nielsen/modules/Set_Info.pm
2. The update command for sql_users_insert should be:
   
   select sql_users_insert();
   select sql_users_update(1,'Username1','Password1',1,1);
   select sql_users_insert();
   select sql_users_update(2,'Username2','Password2',1,2);
   in the file misc/nielsen/Test/sample/Custom/Sql/users.sql

1. In order to get more than 16 variables submitted to a table, you have to change a compile option. Otherwise your tables are limited to 15 columns (because my stored procedures require an id to execute). When you are compiling postgresql, in the file
   src/include/config.h.in
   change this
   #define INDEX_MAX_KEYS 16
   to
   #define INDEX_MAX_KEYS 256

   And then configure, compile, and install it. I had one client which had 130 columns, so I figured I would push it up to 256 thinking that it is unlikely that I would see 250 columns.

2. I changed the database connection in Constants.pm for postgresql to this:

   $dbh ||= DBI->connect('dbi:Pg:dbname=DATABASE',"","", {PrintError=>0});
   
   if ($dbh ) {$dbh->{PrintError} = 1;}
   else
     {$dbh ||= DBI->connect('dbi:Pg:dbname=DATABASE;host=127.0.0.1',"","",
                          {PrintError=>1});}
   

   For chrooted services, you have to use the loopback device, or you should.

Introduction

1. Given a database design, create all the tables, sequences, stored procedures, views, backup tables, timestamps, and unique ids.
2. For the Perl language, create all the modules and sample perl scripts for a web server.
3. The database and webpages will assume there is user authentication. The users table, which will have a username and password for accounts, will be called "users".
4. Create all the "stuff" inbetween the database design and the sample webpages. Thus, it relieves you of creating the database information, perl modules, and even provides sample web script code about how to access the database using the perl modules. All you need to do is manipulate the perl scripts and customize them.
5. Remove the threat of the database administrators and non-programming web administrators strangling the life out of the programmers. Also, to make it possible for a novice programmer to setup the entire database, perl modules, and sample perl scripts so that they can start to experiment and learn (this is going to be valuable at my advanced web/databases classes at eastmont.net).
6. To setup a Standard Database Setup, which I will use for all further projects regardless of which database server and programming languages I use.

I have worked for many companies and many projects. Every one of them have their own programming style and their own ways of doing things. Usually not thought out very well because of pressure to get things out fast and worry about the consequences later. Part 3 of Perl and PostgreSQL is dedicated to me so that I will use a standard way of doing things with Perl and PostgreSQL so that everything remains professional (a professional database structure, professional perl modules, and semi-professional sample perl scripts).

When everything is standardized with good code, everything becomes easy. Personally, I am not ever going to take on another project that doesn't use a database system with standard stored procedures with 100% unique ids in every table. I just won't take the job (I get enough job offers as it is). I don't want to walk into an unprofessional environment anymore (unless they agree to make it professional). It wastes my time and their time. Bottom line. I am more interested in business aspects of a company that requires programming skills than to do the actual programming. I like to setup things up and do research and development to improve things, but I want other people to do the dirty work after I have done the R&D.

One large company that I worked for had great database design, but the perl programmers were at the mercy of the database administrators. One unwieldy popular database server is a nightmare to handle. Although I respected the database administrators, I feel as though a true programmer should be in charge of the database and the database administrator should be a guide rather than a god. Programmers might not know how to handle the database properly, but that is where the database admin comes in, to approve things, but not to prevent things from getting done. I find it extremely frustrating to fight with database administrators when they are suppose to serve the programmers. Network admins serve the database admins, database admins serve the programmers, programmers serve their boss, their boss serves the secretaries, accountants, customers, salespeople, and other people inside and outside of the corporation. In other words, the only justification for the people at the bottom is if they are serving the people at the top to help them get their work done. Lord knows I have seen many computer geeks who just didn't have a clue about how to run a business. Computer people are only valuable if they accomplish goals that other people can use in the company.

Having said that rant about how I fight on a daily basis with database admins, part 3 of Perl and PostgreSQL is also suppose to remove or severely reduce the need of a database admin. My perl script deletes the tables, which if you have live data on a live server, can be a bad thing which you might need a database admin for. However, I backup all tables deleted and I want to add the ability to repopulate data from one table to another when columns get changed or added. With that, a database admin looses power to stranglehold a programmer from getting his work done, or more accurately, if we can provide a professional system that a professional overpaid database admin approves of, there will be less work for the overpaid database admin and thus they become cheaper. With that said, overpaid programmers can cause just as much problems as an overpaid database admin! I just deal in a world where I install my operating system from scratch, I install Perl, Apache, Zope, Python, PostgreSQL, MySQL from scratch, and my end result is a pretty and/or functional webpage that people can use. Anything below the webpage that causes me problems to achieve my goal, whether it is the network admin, database admin, etc. is an obstacle that needs to be removed. That is how I think. I have great respect for all sorts of admins, but they serve the programmers, and I can seen countless times how programmers get restricted and choked to know when a company or department is in trouble. When network and database admins make the programmers happy, everyone is happy (they also have to know how to prevent the programmers from running amok and put their foot down when needed!).

With standard exact stored procedures, views, sequences, unique ids, timestamps, active/inactive status for all tables, the database admin should feel happy to let programmers design database tables as long as they approve the final database result. Afterall, if my perl script sets up the entire database, and the Perl modules to access the stored procedures in the database, there is nothing for the database admin to do other than approve database designs and make changes to the database design (because the programmer probably doesn't know how to setup a good database). Also, the web administrator can be a stranglehold if they are not a programmer. If the web administrator limits the programmers to use the Perl modules in accessing the database, then a non-programming web administrator can feel comfortable to not strangle the life out of a programmer. If everything is standardized, then the immature novice programmer dot-com wannabe who doesn't know how to do good programming and who knows nothing about installing operating systems or setting up database and web servers, can at least have something to work with as a standard so that they don't go off wild creating lots of interesting uncommented archaic code just because there were "cool" ways of doing things.

Bottom line, everybody involved can cause problems to get that Perl script to work. This hopefully is a starting point to get things moving. There are a lot of things I want to add. I am happy with this being called "Version 1". I am aiming for PHP, Python, and possibly JAVA modules and webpages for Version 2, modifying tables (rather than deleting them and recreating them) for lives systems for Version 3, and a GUI for Version 4 (though a GUI can be developed at the same time).

Obviously, to get to get to version 4 is going to take a long time. It has taken me 4 months just to get this far. Most of my work is debating with myself about what to do rather than actually doing work. In development, you scrap lots of code because you figure out a better way of doing it, and you also wish you can scrap more code. For now, I am consistently getting rid of code I don't like and redoing it, which takes a little longer but yields greater rewards, which is a completely different and refreshing mentality compared to the dot-com boom where you had a non-programming manager over your shoulder who just wanted to get things done no matter how horrible the code and setup was.

Standard Database Setup (SDS)

The two options, using a table TABLENAME_diff and select stored procedures are not done in my scripts. The reason, I just downloaded the beta version of PostgreSQL 7.2, and I haven't messed around with stored procedures returning multiple variables (or cursors). I am still using stored procedures that can return only one variable. When PostgreSQL 7.2 is out, I will be able to make really cool stored procedures that return lots of information.

SDS version 1.0 is the following:

Perl script to setup my SDS environment with Perl and PostgreSQL.

1. Install PostgresSQL 7.1, the Apache webserver, setup the Apache webserver to use persistent databases connections, make the Apache webserver execute *.pl files as perl scripts, install the Perl modules DBI, DBD::Pg, and BlowFish. If you have trouble with this, please look at the references section located in this article.
2. "cd" into the directory where you downloaded these two files and execute these commands.

   tar -zxvf Files.tgz
   mv misc/nielsen/Test /tmp/
   cd /tmp/Test/
   mv Create_Functions.pl.txt Create_Functions.pl
   chmod 755 Create_Functions.pl
   

3. Now in theory, you are ready to go. I have provided a sample database called "sample". You must change two Config.txt files. One located in the main directory, and another located in the directory "sample". I have setup certain web variables to point to /usr/local/apache which would be the standard installation of apache when you download and install it from scratch. If you are using apache that comes with your Linux distribution, please change the variables in those two files. In theory, all you have to do to get your database setup and use the webpages is to execute the one command

   /tmp/Test/Create_Functions.pl sample
   

   and then go to the webpage http://127.0.0.1/sample/sample/index.html on your computer.

Just in case you can't get the perl script to execute, here is the output of the SQL commands, Perl modules, and web scripts.

1. SQL commands
2. Perl Modules
3. HTML webpages

How the perl script is setup to work.

Conclusions.

I am very happy with my system. I can now pump out webpages like crazy. I no longer have to worry about what goes on in the middle, since it is all taken care of. I know of tons of things I would like to get done, change, add on, but I try to do the most important things first. Plus, it takes a while to think through problems because I often ask myself "do I really want to do that?" which takes up time.

I hope this lets you create webpages fast and relieves a lot of stress at developing your web/database system. My goals in the future are to have this Perl script create Php and Python code as well and to create a nice GUI interface (either web-based or Xwindows independent) to handle creating your database, checking for errors, generating reports, creating graphs, alter the database, etc. I see lots of systems out there that can do this, but I don't want a GNOME or KDE dependent interface. In my opinion, a nice Python/TK solution would be cool since you can compile Python and it will run under any XWindows environment. I want it to be useable by all, not just those who run GNOME or KDE. Plus, Python can output JAVA code, which may be useful. Also, I don't see other systems that try to force an implementation of standard ways of doing things. My (or our) gui interface will enforce the use of SDS or some other standard way of doing things. One thing I want to reiterate is: I want future versions to create PHP and Python modules and scripts. Why is this so important? It lets you change programming languages rapidly which can be really useful if you want to use one language for all needs (trust me, it is a headache to support multiple languages). If your programmer is an idiot, and the next guy who you want to hire uses a different programming language, guess what! No Problem! Again, my goal is to reduce the cost of having database administrators, network administrators, and programmers. Anything that is a bottleneck must go. If you don't advance your own skills to meet new challenges, you deserve to fall behind. I want my system to push the good guys forward and leave the bad guys in the dust.

The stuff I do here should be really easy to use with Mason, ASP, or other cool ways of making webpages. In ASP and Mason, you don't want to use the CGI module, so you can replace it with their own query handling methods. One thing I stress again, is the use of creating objects and concepts. Since I put all the good code into perl modules, they become useable to Mason, ASP, or whatever method you use to make webpages. By trying to create code that can be used under all circumstances, I hope to be able to use this stuff no matter where I go. I am thinking about my career and trying to reduce obstacles. I hope this makes sense! Enjoy!

P.S. I got heat for claiming the database administrators serve the programmers. I still claim it is true. The database administrator has no final product. The final product occurs with the programmer, who needs a web server (managed by the web admin), a database (managed by the database admin), and a network (managed by the network admin). In turn, that final product is given to the programmers boss, which is given to the customers or employees. Bottom line, there is a chain of support, and I view customers and employees at the top, and the computer geeks at the bottom. Anybody that you need to get your product done is a facilitator and is below you, even if they have more authority and they get paid more than you. Granted though, they must set reasonable guidelines and prevent the programmer from doing stupid things.

References

1. Part 2: PostgreSQL: Perl procedures with PL/pgSQL
2. Part 1: PostgreSQL: Perl procedures with PL/pgSQL.
3. An older article setting of Web and Databaser Servers.
4. Branden Williams articles on PostgreSQL.
5. http://techdocs.postgresql.org/oresources.php
6. http://techdocs.postgresql.org/
7. Some links which have nothing to do with this article, but I am considering for future articles.
   • http://authpg.sourceforge.net/.
   • http://www.digitalstratum.com/pglogd/
   • http://www.giuseppetanzilli.it/mod_auth_pgsql/
   • http://www.zort.ca/postgresql/ -- very nice and I will use this. I tried it, and it grouped all my tables together, so I had to ungroup it and separate out the tables.
   • http://www.brasileiro.net/postgres/cookbook/
8. If this article changes, it will be available here http://www.gnujobs.com/Articles/24/PP3.html

Mark works as an independent consultant donating time to causes like GNUJobs.com, writing articles, writing free software, and working as a volunteer at eastmont.net.

Copyright © 9/2001 Mark Nielsen
Article