"Linux Gazette...making Linux just a little more fun!"

1. Introduction (way too long)
2. Standard Database Setup (SDS)
3. Perl script to setup my SDS environment with Perl and PostgreSQL
4. How the perl script is setup to work
5. Conclusion
6. 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, 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:

Standard Database Setup (SDS) version 1.0. 
Copyright by Mark Nielsen, 9/2001. 

1. All tables must contain the following:
   a. A primary key equal to TABLENAME_id.
   b. Timestamps for the date of creation and modification, date_created
      and date_updated. 
   c. There will be a backup table named TABLENAME_backup. 
   d. All fields are lower case.
   e. An active field whose status of 0 is inactive and 1 is active. 
   f. All foreign keys must have the extension "_fk" except for fieldnames
      ending in "_id".
   g. All fieldnames with "_id" in the table definition will automatically
      get a foreign keys constraint. A fieldname of "TABLENAME_id" will
      have a foreign constraint to the table "TABLENAME" with its primary key
      "TABLENAME_id". 
   h. Views for active, inactive (deleted), and purged will be created. 
   i. Insert, Update, Delete, Copy, Change, Purge, Unpurge, PurgeOne, 
       and UnpurgeOne functions are too be used for all database 
       modifications where the functions will be 
       named "TABLENAME_FUNCTION_sql".     
   j. When stored procedures can return multiple variables, then all
      select statements will be executed through stored procedures as well. 
      All select stored procedures will end in "_select_sql".
      The two types of select stored procedures will be:
      1. Given a unique id number, the select statement will require
         all fields for that row. This stored procedures will be named
         "TABLENAME_select_sql". 
      2. All other select statements will be custom made. Select statements
         can get very complicated. It is mandatory that all custom select
         statements be created with custom stored procedures. 
   k. All modifications to any table will be recorded in "TABLENAME_backup".
      Modifications will occur through standard stored procedures and
      the stored procedures will handle recording of these modifications. 
      "backup_id" will the primary key of all backup tables.  
   l. All selects of tables will be recorded as an option using the table
      "TABLENAME_select" if it is desired when the table is created. 
      "TABLENAME_select" will have the fields select_id, date_created, 
      date_updated, TABLENAME_id, error_code, and misc.   
   m. An optional method for recording database inserts and changes
      with respect to differences may be used, but must follow the following
      standards. This will not be included by default in MAPPS. 
      1. The backup table must be named TABLENAME_diff.  
      2. "diff_id" will be the primary key for each table. 
      3. Each diff table will have exactly these fields.
         a. diff_id 
         b. date_updated : Timestamp the data was entered. 
         c. diff_data : This is the difference in data between this
            entry and the previous entry. 
         d. diff_method : This describes the difference method used.
             This is arbitrary and dependent upon the programmer. Here are
             a list of pre-defined methods:
            1. "diff" or "gnudiff" will correspond to GNU diff. The version
               must be supplied and recorded somewhere. 
            2. "subversion" in relation to subversion. 
            3. cvs will not be an option for a standard. "subversion" replaces
               cvs, and hence, by the time SDS is really solid, subversion
               from subversion.org will be ready. 
         e. diff_prev : This is the previous diff_id which the data is 
            being compared to. This is done so that if a diff_id gets
            deleted, you can state there is an error. 
         f. fieldname : This is the field of the row which the data
            belongs to.  
         g. primary : This is the primary key of the row we are looking at. 
         h. error_code : "start" means there is no diff_id previous
            to this one. "diff" means there is a previous entry to
            be compared to. "stop" means the primary key has been deleted. 
            Other error codes are possible. 
   n. An entry of 0 into a foreign key means null. All rows with a primary
      key of 0 should have empty space for text and 0 for numeric values
      or the default value (if supplied). 

2. The unique names which cannot be used are:
   a. "error_code", "backup_id", "date_created", "date_updated",
      , "diff_id" and "active" are reserved fields. 

3. Standard practices with Perl Modules and other programming languages. 
   a. All Perl Modules (or other programming languages) will create
      objects whose methods correspond to each stored procedure. The naming
      convention of each method shall be exactly the sql function name minus
      the tablename and "_sql". Thus, "TABLENAME_FUNCTION_sql" becomes
      "FUNCTION" in the Perl Module. There will be an exact one to one
      correspondence between all sql functions and perl methods, minus
      custom made sql functions or perl methods. 
    b. All custom made perl methods must use stored procedures for all
       changing of data, and selecting data (when stored procedures
       can return multiple variables). 
     
4. Standard practices of custom stored procedures.
   a. All custom stored procedures may only change data using one of the
      predefined stored procedures. Custom stored procedures may not
      use custom made stored procedures for changing/inserting data. 
   b. All select statements must use select stored procedures (when the
      stored procedures can return multiple values). 

5. Standard practices of webpages/perl scripts.
   a. All perl scripts or other programming languages will always
      use perl modules for all interactivity with database. All changing,
      inserting, or viewing data will occur through a module. 

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. Branden Williams articles on PostgreSQL.
4. http://techdocs.postgresql.org/oresources.php
5. http://techdocs.postgresql.org/
6. 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/
7. If this article changes, it will be available here http://www.gnujobs.com/Articles/24/PP3.html

Mark Nielsen

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 © 2001, Mark Nielsen.
Copying license http://www.linuxgazette.net/copying.html
Published in Issue 72 of Linux Gazette, November 2001

---