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.
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.
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
/tmp/Test/Create_Functions.pl sampleand 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.
|This explains how the Standard Database Setup is designed.
|This has two variables used for the perl script.
|These are the modules to be used for all databases.
|These are the two files which are modified and then executed in the database. Generic.fun gets executed for each table.
|These two webpages/perl scripts are used for every table.
|These 4 files are for user authentication.
|This is the main file that gets executed and needs to be renamed "Create_Functions.pl".
|These are the custom sql commands to be executed after the perl script creates the default database setup.
|This is the configuration for this database.
|This is the text file that defines all the tables for our database.
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.