Actually I use three applications in my test systems used in my classes; the third is my PFCL ATK toolkit, my Audit ToolKit (ATK) which is developed and used in the one day class (and given away for free - actually a few thousand lines of PL/SQL and SQL code) - Designing practical audit trails in Oracle databases - But I am going to focus on one of the other two. I have two functional applications, one front of house - a public facing website driven out of an Oracle database and a second back office application that is used to manage customers, payments, products, orders and more. The front facing site is for the public and access is unauthenticated but back office customer processing is by employees and via http passworded access.
I want to focus on the back office application. This is called BOF, BackOFfice. This was written around 5 years ago and recently I am working on a test of some software and needed a sample application with data to test with. So I grabbed the scripts used to build my two (three) applications and installed them in the test database I am using to test this software.
The need to Install BOF by hand (via scripts) for one of my current pieces of work made me look at it again and understand how it works and was written. When I wrote it I wanted to not write the application at all but generate it completely from a schema in the database.
I love programs that write programs; I remember maybe 25 years ago reading about Ken Thompson and Dennis Ritchie doing just this; writing programs that write programs. I always thought this was cool. In fact the first ever PL/SQL program I wrote maybe 20 years ago was written in Pro*C. I needed to generate test data (wow, the same problem again!) and I decided rather than create a large bunch of SQL*loader files I would create a simple control schema that allowed PL/SQL to act upon it and read the rules and tables of data and generate test data in application schema tables that were specified. As the structure of the control programs (PL/SQL) was repeatable it made sense to generate the control programs with PL/SQL. Move forward some years to around 16 years ago and I was helping migrate an application from Dec Alpha, Rdb (owned by Oracle by then of course) and Cognos Powerhouse to Sun Solaris, Oracle and a later version of Cognos Powerhouse. One of the requirements that we needed to be able to do was revert the Oracle system back to the Dec/Rdb system if the migration after up to one was deemed to not be successful. So out came my code generating skills again. I wrote a SQL*Plus script that generated a trigger for update, insert and delete for every migrated table. In each trigger it called a PL/SQL package procedure. In each procedure I generated an update or insert or delete script for DEC Rdb so that all transactions could be re-applied to the original database if necessary. So in the case I generate a program that in turn generated a program. Fun!
Back to the plot. I needed to re-install my BOF sample application used in my training into a new Oracle database for use in a new piece of work. This lead me to understand how it was created and written again. As I said I wanted to not write the web application at all - the front end that is and I wanted to instead generate the web front end. The front end is php and runs in an Apache webserver and this webserver uses OCI8 to connect to the database and provide Oracle access.
The whole application is driven from a sample schema and sample data in a script; The data is not necessary and you can create the schema, generate the application and then fire up the application and add data that way and then extract it for subsequent reload later. In my case I also created some data and the schema. The schema needs to follow some simple rules for the application generator to work. These are:
o - The tables should have the same prefix - in my case they are all BOF_ but you can use any name you like
o - The tables must have a primary key column called ID and it must have sequence called {PREFIX}_your_table_name{_SEQ} again for the generator to find it.
o - If you need connected records then you must define foreign keys.
o - You must create an entity view - a summary view that dereferences the foreign keys and shows useful data from the child table. This view must be named {tabe_name}_ENTITY_V
o - You should create a foreign key view that supplies the meaningful data (not ID) from the foreign keyed table. These views can be used in the entity views and should be name {table_name}_F
I also created a drop script to be able to drop and re-create the sample application. Here is some examples next. The first is an example of one of my BOF application tables definitions:
And the custom rules tutorial is here (BTW: click on the images to get a bigger image). The next example is the creation of an entity view as listed above:
And finally for the application schema itself here is an example of creating a foreign key view:
The application schema for my sample is simple to create and of course I created my BOF schema for my training classes BUT I can now create any schema I need and generate a PHO application for it. The application includes some very basic styling with CSS to try and tidy it up but remember this is not production code and not intended for public consumption. The site does not include sessons, passwords or the like and limited error checking in the php. The site is styled in html5 and css and is structured using divs not tables. The toolkit generates separate header, footer and navigation sections. Each of these sections is generated by separate PL/SQL scripts that write the PHP code. The website is made up of grid based tables with a separate page for each table in the database schemae. From each row of data there is a link to edit that record and a global link at the top and bottom of the page to add a new record. This means that there are two main pages for each table in the schema; a view page in a grid and a form that allows a new record to be added and an existing record to be deleted. The menu navigation is also generated automatically from the database schema.
To create a website from a database schema as set up above I wrote a set PL/SQL scripts that generate the header, footer, nav bar and database config php files. Wrapped above this is a script that generates the view pages and also generates the edit/new pages from the database. The whole thing is controlled by a simple script called bof_conf.sql that you an edit. This is the only file that should be edited. Here it is:
This is simple to configure, you need to add the database schema user and password that will be used to access the database from PHP and also to access to generate the website. Also add the connection details, IP Address, port etc and details to appear in the site such as its name and copyright notice. Finally add a search string to be used to find the tables to have web pages generated from. Everything is generated from one script bof_gen_all.sql and part of this is shown here:
This shows the generation of the single files such as header, footer and nav and also shows the generated loop used to then generate the individual grid read pages for each table in the schema. The generate script bof_generate_edir_new.sql creates the pages for each table that include a form to edit a record or add new records. This also generates lookups for the foreign key records and check boxes for status records. A small section of this is shown here:
The generated code forms a complete website running against an Oracle database. An example of the shipping page based on the table BOF_SHIPPING is shown below:
And another example shows the update form for editing a shipping record:
It was fun to have a good look at this sample application again as I needed to generate it in another test database. This generator can be used to create any sample application BUT the emphasis is on sample; this should not e used in production as it has no security (well actually the php uses binds so that's good), no sessions, no users etc BUT it could be extended I guess if I had the time and inclination.
I like these scripts because they follow the tradition of generating code with code. In fact this is 4 deep. I have a SQL*Plus script bof_gen_all.sql that creates and writes a SQL*Plus script bof_run_all.sql that in turn runs bof_generate_web.sql for each table that itself generates a PHP page for each table and that PHP page when run in the web server generates HTML to be served to the end user. So code that generates code that generates code that generates code..
The whole tool is about 2000 odd lines of PL/SQL and if anyone would like a copy of the tool then please let me know by email at pete at petefinnigan dot com and I will send you a copy. The license is the same as all my tools, its free, its not GPL and you can use it internally but not remove my copyright or license and you cannot sell or give it away yourself.
