Standard Database Setup with Perl and PostgreSQL: Part 3

1. Introduction (way to long)
2. Standard Database Setup (SDS)
3. Perl script to setup my SDS environment with Perl and PostgreSQL.
4. Setting up your working environment and executing the Perl script
5. Considerations to explore.
6. Conclusion
7. References

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 that 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 get the entire database, perl modules, and sample perl scripts setup 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 thigns 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 adminstrators. One unwiedly popular database server is a nightmare to handle. Although I respected the database amdinistrators, I feel as though a true programmer should be in charge of the database and the database adminstrator 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. Netowork admins serve the database amdins, database admins serve the programmers, programmers server 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 siad that rant about how I fight on a daily basis with databas 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 columsn 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 to have 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 doesn't know how to setup a good dataabse). Also, the web administrator can be a stranglehold if they are not a programmer. If the web adminstrator 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 was a "cool" way 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 posisbly JAVA modules and webpages for Version 2, modiyfing 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).

If you want to run a Perl script of mine to create on the web to create all the files that this Perl script outputs, then please go to my MAPPS homepage.

Perl script to create tables, procedures, backup tables, and sequences.

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 Test /tmp/
   cd /tmp/Test/
   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 tow 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/index.html on your computer.

Configuration files and what they mean.

Considerations to explore.

References

1. Part 2: PostgreSQL: Perl procedures with PL/pgSQL
2. Part 1: PostgreSQL: Perl procedures with PL/pgSQL.
3. 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