Standard Database Setup (SDS)

The standard database setup started after I noticed many companies for whom I worked for had similar goals and most of them were doing it badly. I quit my jobs (gracefully) in April, 2000 because of the dot-com bust and because I didn't need a job, much to my joy. Joy? I had time to do what I always wanted to do. Many companies don't think long-term, and that annoys me because programming is long-term. I wanted to set a standard of database programming which can be used for all applications and to continue to improve it to make it more efficient and flexible. Basically, what we do here is setup s standard way of setting up tables and creating their stored procedures. I have broken down the levels of the database setup and also added a "plus" status which is independent of the level.

I welcome all people who wish to add to this and who wish to sign up as a member who supports this concept!

1. Definitions
2. Level One SDS
3. Level One SDS Plus
4. Level Two SDS
5. Level Two SDS Plus
6. Level Three SDS
7. Level Three SDS Plus

Definitions

• "TABLENAME" referes to the name of an arbitrary table.
• "FUNCTION" referes to an arbitrary function name for a table.
• Stored procedures will save its actions on a backup table described in the different levels of SDS.

  For stored procedures:

  1. "insert" shall recieve no input and return the primary key created.
  2. "update" shall update the data for the entire row of a table. The first input variable shall be the primary key. All subsequent variables will correspond to the exact order the of the fields in the table. 1 shall be returned for a positive update and a negative number for an error.
  3. "delete" shall recieve one input, the primary key which will be deleted. It will return 1 for positive result, or -1 or any negative number for any error. "delete" will not delete a row, but set its state to "inactive".
  4. "undelete" shall recieve one input, the primary key which will be undeleted. It will return 1 for positive result, or -1 or any negative number for any error. "undelete" will not restore a row, but set its state to "active".
  5. "purge" shall recieve no input. It will remove all deleted rows from the table (but keep a copy in the backup table as usual). It will return at least one value. The first value returned will be the number of rows affected (or or greater) or a negative result if there was an error. If the stored procedure can return a second value, it must return a space delimited text field which contains all the primary keys affected.
  6. "unpurge" shall recieve no input. It shall take the latest entry into the backup table for all rows deleted and restore them to the table. It will return at least one value. The first value returned will be the number of rows affected (or or greater) or a negative result if there was an error. If the stored procedure can return a second value, it must return a space delimited text field which contains all the primary keys affected.
  7. "purgeone" shall recieve one input, the primary key which will be purged. It will return 1 for positive result, or -1 or any negative number for any error. "purge" will remove only a deleted row from the table.
  8. "unpurgeone" shall recieve one input, the primary key which will be unpurged. It will return 1 for positive result, or -1 or any negative number for any error. "unpurge" will restore the latest "purged" row for that primarykey in the backtup table.
  9. "copy" shall copy the entire contents of a table into the same table but in a different row, except for the primary key and the timestamps. It shall return the primary key of the new row or a negative number to indicate an error.
  10. "change" shall only recieve three values, the primary key, the field desired to be changed, and the value to change it to. It shall return 1 for a positive result, or a negative number to indicate an error.

Level One SDS

There exist some slight modifications in Level One compared to its original form.

1. All tables must contain the following:
1. Stored procedures must be able to input 1024 variables.
2. A primary key equal to TABLENAME_id.
3. Timestamps for the date of creation and modification, date_created and date_updated.
4. There will be a backup table named TABLENAME_backup which will be an exact replicate of the table TABLENAME, but using "backup_id" as its primary key and adding the field "error_code". "error_code" shall be the type of the stored procedure executed (insert, update, delete, etc) or an error value.
5. All fields are lower case.
6. An active field whose status of 0 is inactive and 1 is active.
7. All foreign keys must have the extension "_fk" except for fieldnames ending in "_id".
8. 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".
9. Views for active, inactive (deleted), and purged will be created.
10. Insert, Update, Delete, Undelete, Copy, Change, Purge, Unpurge, PurgeOne, and UnpurgeOne functions are too be used for all database modifications where the functions will be named "sql_TABLENAME_FUNCTION". This also applies to any custom made functions. The "change" function is not required for Level One.
11. 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.
12. 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:
   1. "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. (Drasticaly changed since the first version of Level One)
   1. The language must support references to hashes and be able to receive/submit values to functions by keys.
   2. All Perl Modules (or other programming languages) will create objects whose methods correspond to each table. The functions will be named "Set_TABLENAME" with exactly 3 values. Those three values shall be:
   1. "id" will be equal to the primary key being modified.
   2. "command" will be equal to a command (insert, update, delete, etc) which will correspond exactly to "FUNCTION" in "sql_TABLENAME_FUNCTION".
   3. "fields" will be a reference to a hash array containing the new values (for updates). "fields" is not required for all "command" options.
   3. All custom made perl methods must use stored procedures for all changing of data.
4. Standard practices of custom stored procedures.
   1. 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.
5. Standard practices of webpages/perl scripts.
   1. 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.

Level One SDS Plus

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

1. Stored procedures must be able to output 1024 variables.
2. 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.
3. There shall be a stored procedure "change" for a table which will recieve 3 inputs, the primary key of the row you which to change, the fieldname of the field you wish to change, and the value it should have. The function shall accept all types of input (numeric, text, complex, timstamp, etc).
4. 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.
5. No NULL entries are permitted. Instead, have an additional field called "null_status", which is 0 or 1. 0 meaning data has not been set for at least one field, 1 meaning all fields have been set. Numeric values will be 0 for undefined, and text fields will have '' as their value.
6. For all programming languages:
   1. Selecting data can only be done using stored procedures.
   2. All select statements must use select stored procedures (when the stored procedures can return multiple values).
7. An optional method for recording database inserts and changes with respect to differences may be used, but must follow the following standards.
   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.
      1. diff_id
      2. date_updated : Timestamp the data was entered.
      3. diff_data : This is the difference in data between this entry and the previous entry.
      4. 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.
      5. 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.
      6. fieldname : This is the field of the row which the data belongs to.
      7. primary : This is the primary key of the row we are looking at.
      8. 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.

Level Two

Everything in Level One plus the following. Does not have to include Level One Plus Items.

Level Two is not done yet.

1. All of Level One SDS.
2. All definitions of tables are to be stored in xml files. Use the following format:
   • All elements contain no data, just properties and other elements.
   • The main element of the xml with be "Tables". It will contain a list of tables in which all depedent tables are listed after the tables they are dependent on.
   • The element Tables can have optional properties.
   • The element "Table" will exist under "Tables".
     • This element can have optional properties. Such properties must start with "Custom_".
     • This element can optional elements which don't describe rows. Such elements must start with "Custom_".
     • If describing a row, the only element allowed to be used is the "Row" element. The "Row" element will be as follows:
       • Property caled "Name". Its value will be a single alphanumeric word starting with an alpha.
       • Property caled "Type". Its value will be int, text, float, etc.
       • Property caled "Null". Its value will be either 0 or 1.
       • Property caled "Default".
       • From here, you can add your own optional properties. Optional properties must start with "Custom_" to separate it from mandatory properties. This will make it so optional properties can be ignored when moving from one database system to the next.
3. Each field of each table will be unique throughout the whole entire database. Each field will be pre-appended with the name of the alphanumeric tablename. The only exception to the this is the primary key which will remain TABLENAME_id. However, in the specification in the XML files, the field names will not include the name of the tables.