« March 2009 | Main | May 2009 »
April 15, 2009
Building Application DB Tables with tables.ini and create_database.php
The framework requires that each table in the database has a core set of columns in order to work. Also, there are some rules about how tables should be defined.
- Primary and foreign key columns should be set as
int(10)and listed at the top. - All columns apart from the primary key should be set as
nullby default. - Date columns are set as
int(20)and dates should be inserted as unix timestamps. - Varchar columns are always set as
varchar(255). - Enumerations shouldn't be used with the framework.
- Every table should contain an expired_date column which will normally be set to
null. If you want to remove a row from view, set theexpired_dateto a timestamp.
For speed tables for an application can be defined with a couple of framework tools. In each application directory, there is an /sql sub-directory. You will find two files in here, create_database.php and tables.ini.
The script parses the tables.ini file which contains the table definitions in Windows .ini format and prints the SQL necessary to create the tables out to the browser.
N.B. The script does not create the tables, it only generates the SQL to do so. This can then be pasted into PHPMyAdmin.
The tables.ini file looks like this:
[table_list] panel = 0 panel_group = 0 panel_content = 0 panel_location = 0 ; The above defines the tables to be created. The 1 or 0 value is ; significant. 1 means that a corresponding type table is created and ; a foreign key column for it is included in the main table. ; 0 means that no type table is created. Also the type table foreign ; key column is dropped after the table is created thus: ; alter table `panel` drop `panel_type_id`; [panel_location] panel_location_name = text; [link_tables] panel:panel_group = 0 user:panel_group = 0 panel:panel_content = 0 panel_group:panel_content = 0 ; If you want to create link tables between others defined herein, you ; can using the syntax shown above. Table names are separated by a ; colon. [panel] panel_location_id = "int(10)" panel_name = "varchar(255)" panel_description = text ip_address = "varchar(255)" ; Each table has a section which defines extra columns to be included ; above and beyond the default. In the resulting SQL these columns are ; indented to differenciate them from the defaults: ; ; drop table if exists `panel`; ; create table `panel` ; ( ; `panel_id` int(10) primary key auto_increment not null, ; `panel_type_id` int(10) default null, ; `panel_location_id` int(10) default null, ; `panel_name` varchar(255) default null, ; `panel_description` text default null, ; `ip_address` varchar(255) default null, ; `insert_by` int(10) default null, ; `insert_date` int(20) default null, ; `update_by` int(10) default null, ; `update_date` int(20) default null, ; `expired_date` int(20) default null ); ; [panel_group] panel_group_name = "varchar(255)" panel_group_description = text [panel_content] panel_id = "int(10)" panel_group_id = "int(10)" content_title = text content_description = text content_html = text content_html_url = text content_stylesheet_url = text content_image_url = text content_image_type = "varchar(255)" content_image_name = text content_image_size = "int(20)" content_image_data = longblob priority "int(1)" start_date = "int(20)" end_date = "int(20)"
This generates the following output:
drop table if exists `panel`;
create table `panel`
(
`panel_id` int(10) primary key auto_increment not null,
`panel_type_id` int(10) default null,
`panel_location_id` int(10) default null,
`panel_name` varchar(255) default null,
`panel_description` text default null,
`ip_address` varchar(255) default null,
`insert_by` int(10) default null,
`insert_date` int(20) default null,
`update_by` int(10) default null,
`update_date` int(20) default null,
`expired_date` int(20) default null );
alter table `panel` drop `panel_type_id`;
drop table if exists `panel_group`;
create table `panel_group`
(
`panel_group_id` int(10) primary key auto_increment not null,
`panel_group_type_id` int(10) default null,
`panel_group_name` varchar(255) default null,
`panel_group_description` text default null,
`insert_by` int(10) default null,
`insert_date` int(20) default null,
`update_by` int(10) default null,
`update_date` int(20) default null,
`expired_date` int(20) default null );
alter table `panel_group` drop `panel_group_type_id`;
drop table if exists `panel_content`;
create table `panel_content`
(
`panel_content_id` int(10) primary key auto_increment not null,
`panel_content_type_id` int(10) default null,
`panel_id` int(10) default null,
`panel_group_id` int(10) default null,
`content_title` text default null,
`content_description` text default null,
`content_html` text default null,
`content_html_url` text default null,
`content_stylesheet_url` text default null,
`content_image_url` text default null,
`content_image_type` varchar(255) default null,
`content_image_name` text default null,
`content_image_size` int(20) default null,
`content_image_data` longblob default null,
`priority` int(1) default null,
`start_date` int(20) default null,
`end_date` int(20) default null,
`insert_by` int(10) default null,
`insert_date` int(20) default null,
`update_by` int(10) default null,
`update_date` int(20) default null,
`expired_date` int(20) default null );
alter table `panel_content` drop `panel_content_type_id`;
drop table if exists `panel_location`;
create table `panel_location`
(
`panel_location_id` int(10) primary key auto_increment not null,
`panel_location_type_id` int(10) default null,
`panel_location_name` text default null,
`insert_by` int(10) default null,
`insert_date` int(20) default null,
`update_by` int(10) default null,
`update_date` int(20) default null,
`expired_date` int(20) default null );
alter table `panel_location` drop `panel_location_type_id`;
drop table if exists `panel_panel_group_link`;
create table `panel_panel_group_link`
(
`panel_panel_group_link_id` int(10) primary key auto_increment not null,
`panel_id` int(10) default null,
`panel_group_id` int(10) default null,
`insert_by` int(10) default null,
`insert_date` int(20) default null,
`update_by` int(10) default null,
`update_date` int(20) default null,
`expired_date` int(20) default null );
drop table if exists `user_panel_group_link`;
create table `user_panel_group_link`
(
`user_panel_group_link_id` int(10) primary key auto_increment not null,
`user_id` int(10) default null,
`panel_group_id` int(10) default null,
`insert_by` int(10) default null,
`insert_date` int(20) default null,
`update_by` int(10) default null,
`update_date` int(20) default null,
`expired_date` int(20) default null );
drop table if exists `user_panel_link`;
create table `user_panel_link`
(
`user_panel_link_id` int(10) primary key auto_increment not null,
`user_id` int(10) default null,
`panel_id` int(10) default null,
`insert_by` int(10) default null,
`insert_date` int(20) default null,
`update_by` int(10) default null,
`update_date` int(20) default null,
`expired_date` int(20) default null );
drop table if exists `panel_panel_content_link`;
create table `panel_panel_content_link`
(
`panel_panel_content_link_id` int(10) primary key auto_increment not null,
`panel_id` int(10) default null,
`panel_content_id` int(10) default null,
`insert_by` int(10) default null,
`insert_date` int(20) default null,
`update_by` int(10) default null,
`update_date` int(20) default null,
`expired_date` int(20) default null );
drop table if exists `panel_group_panel_content_link`;
create table `panel_group_panel_content_link`
(
`panel_group_panel_content_link_id` int(10) primary key auto_increment not null,
`panel_group_id` int(10) default null,
`panel_content_id` int(10) default null,
`insert_by` int(10) default null,
`insert_date` int(20) default null,
`update_by` int(10) default null,
`update_date` int(20) default null,
`expired_date` int(20) default null );
Note the drop table statement before each creation statement. If you want to change the structure of a table after the fact, ensure you dump the data out before you do otherwise it will be destroyed.
Posted by pj at 11:19 AM | Comments (0)
Using prototype.js For Faster JS Development and AJAX
The prototype.js JavaScript framework is used extensively throughout the framework. Further information can be found on the website:
http://www.prototypejs.org/learn
The single most useful feature is the $('blah') function which replaces document.getElementById('blah').
Posted by pj at 11:04 AM | Comments (0)
April 14, 2009
Using JavaScript Validation For Mandatory Form Elements
When forms are built using the interface_functions.php functions a mandatory red asterisk is added to the markup but hidden in the HTML this can be used to flag mandatory elements and also for validation checking:
<script type="text/javascript">
var els = new Object();
els['start'] = 'Start Time';
els['duration'] = 'Duration';
els['purpose'] = 'Purpose of booking';
els['course_code'] = 'Course Code';
els['booker_name'] = 'Your Name';
els['email1'] = 'Your email address';
function check_mandatory(els){
var popper = new Array();
for(i in els){ if($(i).value == ''){ popper.push("<strong>" + els[i] + "</strong>"); $(i).style.border = '1px solid red'; } }
if(popper.length > 0){ poppy("<p>You must provide the following information:</p><ol><li>" + popper.join('</li><li>') + "</li></ol><p>[Click in this box to hide it.]</p>"); return false; }
else{ return true; }
}
</script>
Call the function with an onsubmit="" event handler:
<form
class="content nothing_added"
style="width: 900px; text-align: left; height: 320px; vertical-align: top; clear: both; display: block;"
action="booking_details_doozer.php"
method="post"
id="booking_details_form"
onsubmit="return check_mandatory(els)"
>
To mark the required form elements as mandatory use the following code (make sure your JS is called after all the relevant form elements have been rendered, i.e. at the bottom of the page):
<script type="text/javascript">
for(i in els){ $(i + '_mandatory').style.display = 'inline'; }
</script>
Posted by pj at 02:29 PM | Comments (0)
Populating Select Element Option Lists with option_switcher.php
<select/> form elements that are included with a select or select_noadd directive in a .skini need to be populated with options.
This is achieved via the common_php/option_switcher.php script and the options_switcher() function.
switch($form){
case 'room_details':
switch($label){
case 'Location': return get_options(just_sql("select ID as location_id, name from location order by name"), "location_id", "name");
case 'Bookable?' : return '<option value="1"/> Yes'."\n".'<option value="-1"/> Staff Only';
case 'Equipment': return get_options(just_sql("select * from equipment where expired_date is null order by name"), "equipment_id", "name");
case 'Remove from view?' : return '<option value="'.mktime().'" style="color: red;"/> Yes'."\n".'<option value="nulled"/> No';
}
case 'booking_details':
switch($label){
case 'Start Time': return get_start_times($day);
case 'Duration': return get_durations();
case 'Your email address' : return '<option value="@cumbria.ac.uk" selected="true"/> @cumbria.ac.uk';
}
...........................
}
You will need to add a case with your .skini file name stump and then a switch / case block for each of the select form elements in the form. These are designated by the label you gave them in the .skini file.
Most of the examples you will come across make use of the get_options(just_sql("select * from blah"), $value, $label) function. This takes a just_sql() function call reply and an argument for the column names in the results which go to populate the option value and label respectively.
Posted by pj at 11:53 AM | Comments (0)
April 13, 2009
Sarmatian Connection
Historical basis for King Arthur - Wikipedia, the free encyclopedia
Posted by pj at 10:36 PM | Comments (0)
Cycling in the Trossachs
http://www.walkhighlands.co.uk/lochlomond/glen-ogle.shtml
Posted by pj at 08:19 PM | Comments (0)