Automated Application Generation

Overview
AAG is a system written in PHP, HTML, and PEAR. It is intended for handling standard forms-based
data management tasks. There are two main objectives for this project
1. to attempt to bypass coding repetition in database programming through regex search and replace
2. to apply macro functions found in PEAR html packages.






Features
1. Automatically generates a set of files at the time of table creation
   based on preexisting files
   search and replace rules:
   - default search pattern is varible name + ABCDEFGHIJKLMOPQRSTUVWXYZ0123456789
   - default file name is selected $database underscore selected $table underscore inputfile
   At table creation time the script generates the following database+table specific files:

   (file name => function)
   navbar2.php  =>  navbar  for the specific table
   insert.php  =>  insert entry
   display.php  =>  display entry
   delete.php  =>  delete entry
   edit.php  =>  displays entry for edit
   edit_selected.php  =>  edit selected entry 


2. Basic database crud functionality
   - Create
   - Review
   - Update
   - Delete

3. Additional features
   - server side file upload
     - the blobs associated with a file is copied over to the project folder

   - server side file removal
     - all generated files are removed at the time of deletion.

4. Supported data types:
TEXT TYPES
CHAR()     A fixed section from 0 to 255 characters long.
VARCHAR()     A variable section from 0 to 255 characters long.
TINYTEXT     A string with a maximum length of 255 characters.
TEXT     A string with a maximum length of 65535 characters.
BLOB     A string with a maximum length of 65535 characters.
MEDIUMTEXT     A string with a maximum length of 16777215 characters.
MEDIUMBLOB     A string with a maximum length of 16777215 characters.
LONGTEXT     A string with a maximum length of 4294967295 characters.
LONGBLOB     A string with a maximum length of 4294967295 characters.

NUMBER TYPES
SMALLINT()     -32768 to 32767 normal
0 to 65535 UNSIGNED.
MEDIUMINT()     -8388608 to 8388607 normal
0 to 16777215 UNSIGNED.
INT()     -2147483648 to 2147483647 normal
0 to 4294967295 UNSIGNED.
BIGINT()     -9223372036854775808 to 9223372036854775807 normal
0 to 18446744073709551615 UNSIGNED.
FLOAT( , )     A small number with a floating decimal point.
DOUBLE( , )     A large number with a floating decimal point.
DECIMAL( , )     A DOUBLE stored as a string , allowing for a fixed decimal point.

OTHERS
DATE     YYYY-MM-DD.
ENUM () Short for ENUMERATION which means that each column may have one of a specified possible values.
DATETIME
TIME





Architecture








Challenges (Design and Implementation)
1. Implementation: PEAR
   - installation
   - learning to use PEAR's html form and table features

2. Implementation: String parsing
   - displayed everything as strings and reg-matched
   - special cases

3. Design: Connecting individual features together into a system
   - where to split up to diff. features
   - where to join into common files

4. Design: finding appropriate file generation points
   - the project has a single level of file generation: at the point of table creation.
   - more creatively, a higher level of abstraction, have generated files generate additional files






Limitations (Suggested steps for future versions)
Because of time constraint this version only supports basic functionalities of a database. This version :
1. Supports common data types but not all
   - does not support data types for set or timestamp.
   - because MySQL does not have a represenation for bool types, tinyint is reserved for representing boolean values
2. Does not enforce referencial integrity. Does not enforce auto-increments.
3. No access control in this version.
   - related to acl is the fact that direct modificaiton of reserved tables is allowed in this version
     which is not secure. Reserved tables in mySQL are information_schema, phpmyadmin, and webauth
4. One table at a time creation (Use tools such as MYSQL Command Line Prompt to create multiple tables at once)
5. Cannot process tables containing more than 1 blob types per table (blob types include blob, mediumblob, and longblob).
6. No functionality for displaying and rendering the blobs that are stored in the database.
7. Only allows one primary key in a given table. This primary key can appear anywhere in the table, not just as the first
   attribute. If a composite key is used, the first primary key specified will be used.
8. Users should be careful in the case of deletions because the System does not prompt for confirmation or provide 
   any undo features. 









How to install
1. install xampp server under directory C:\Program Files\
2. untar source code into C:\Program Files\xampp\htdocs\
   where you can give  any name, like 'project' or 'my folder';
3. entry point into the project is create_db.php







Source code
1. server package: xampp
2. project: aa12.tar.gz





User Manual
1. The first line in the .sql file must contain the keyword 'table' followed by 
   the name of the table

2. Database name and table cannot be )(*#&# and other non-alphanumerics

3. Applications:
   create a new database
   - enter a name for the database and click "submit"

   create a new table in the database
   - first select an existing database
   - then specify the name of a single-table .sql file.
     this sql file should contain a sql query that creates a table. In addition,
     this sql file should be saved into the untarred folder.

   create a table in the database
   - removes all server side files that have been generated
     as well as drops the specified table
   remove a database
   - seeks out all server side files generated in the database
     and removes them, as well as drops the specified database
     and all the tables in this database

   To administer database crud functions
   1. first select a database and table
   2. after this selection is made, a second navbar will appear with the options to:
      a) create a new entry
      b) retrieve an entry (display) - the word "Null" is reserved, it represents Null values in the database. 
                                       The following values also indicate nulls: 
				       (Date) 0000-00-00
				       (Datetime) 0000-00-00 00:00:00
				       (Time) 00:00:00
      c) update (single attribute edit)
      d) delete an entry - the only clickable attribute is the primary key. Click on the highlighted link to delete
                           a particular row.

4. Refer to the sections under Features, Limitations, and Architecture for more information.

5. Platform support: Windows (Must change slashes in file paths for linux directories)

6. Note: in MYSQL, the follow database names are considered reserved
   - information_schema 
   - phpmyadmin 
   - webauth








What I liked about the project
Values can be directly injected into code in-text instead of passing them around as 
environment variables as assoc arrays, which is what's typically done in php mysql 
processing, or many other projects. This increased modularity and code reuse, without
paintstaking amount of copy and pasting. (Although in this version I wasn't able to 
completely do away with the copy-and-pastes)






What I didn't like...
1. the limitations
2. did not communicate timely, could have made a number of short cuts
3. not a very high level of abstraction and automation - some repetition in the default
   file templates, a higher level of modularization would be to generate the templates from
   shorter file pieces.








Lessons learned
1. start early
2. frequent communication
3. might be helpful to do this in a team