The Basic Meeting List Toolbox

ABSTRACT

The BMLT uses SQL as its database language, but does not focus on any particular database. SQL is a language that can be applied to a number of different databases.

NOTE: Due to SQL syntax peculiarities, we currently only work with MySQL. This is a known issue, but not one we give priority to fixing. To properly “fix” the issue, we’d need to make major structural changes to the Model, and that could cause big disruptions.

PHP PDO

The reason that we can use different databases (However, to our knowledge, only MySQL has ever been used to power the BMLT), is that we use the PHP PDO API.

The PDO gives us the ability to run the BMLT over a number of different databases through the use of PDO drivers.

Another tremendous advantage of using PDO Prepared Statements, is that they greatly increase security by ensuring that no SQL injection attacks can take place (To be fair, using PDO doesn’t automatically impart this advantage; you need to use it properly. We use it properly). SQL injection attacks are some of the most common and dangerous Internet exploits in use today. However, the technique is well understood, and no Web sites should be designed that are vulnerable to them.

If you will be accessing the database yourself, then we strongly suggest that you use PDO for this operation. Don’t just go straight to the DB.

THE DATABASE STRUCTURE

The BMLT database is, in reality, the heart of the system. It contains all the data that is modified or displayed by the rest of the system. The entire BMLT is all about sharing the contents of this database with users and services.

The BMLT database was designed to be flexible. A great deal of the data is kept in a fairly generic “dictionary” or “key-value pair” design pattern. This is a flexible method of assigning data to keyed indexes (a PHP associative array is a dictionary). We take this one step further, and store it in the database in this fashion. That basically means that we don’t need to know what kind of data is being stored. You can establish that, yourself.

Here is a diagram of the basic SQL schema for the BMLT database:

The meetings are held in 3 different related database tables. There is a main one, with required and/or common fields that all meetings have (*_comdef_meetings_main), and two dictionary tables that store other data (*_comdef_meetings_data stores data up to 255 bytes in length, and *_comdef_meetings_longdata stores longer data -to date, we have never had to store anything in the second table).

The meeting formats are kept in their own table (*_comdef_formats). These can be edited at will by the server administrator.

As an aside, the “*_” at the beginning of each table indicates that the prefix can be changed, so that multiple BMLT root servers can share the same database (another condition that we have never encountered, but we prepared for it). The “comdef” name is a historical artifact. The BMLT started off as “CoMDEF”, which stood for Common Meeting Data Exchange Format. We thought “BMLT” was a bit catchier).

The Service bodies are saved in their own table (*_comdef_service_bodies), which is also edited by the server administrator, and the users (authorized administrator logins) are kept in the *_comdef_users table.

Of particular relevance, is the *_comdef_changes table. This stores records of changes made to the BMLT database, so you know what was changed, how, when and by whom.

The database is the absolute heart of the system. It is almost never changed. The design is flexible enough to allow a great deal of variance in implementation without a need to change the structure.

SQL CODE OF THE DATABASE STRUCTURE

For the pedantic, here is the structure of the database, expressed in SQL. It can also be found in the installer “seed” SQL files:

CREATE TABLE IF NOT EXISTS `na_comdef_meetings_main` (
  `id_bigint` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `worldid_mixed` varchar(255) DEFAULT NULL,
  `shared_group_id_bigint` bigint(20) DEFAULT NULL,
  `service_body_bigint` bigint(20) unsigned NOT NULL,
  `weekday_tinyint` tinyint(4) unsigned DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `duration_time` time DEFAULT NULL,
  `formats` varchar(255) DEFAULT NULL,
  `lang_enum` varchar(7) DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `published` tinyint(4) NOT NULL DEFAULT '0',
  `email_contact` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_bigint`),
  KEY `weekday_tinyint` (`weekday_tinyint`),
  KEY `service_body_bigint` (`service_body_bigint`),
  KEY `start_time` (`start_time`),
  KEY `duration_time` (`duration_time`),
  KEY `formats` (`formats`),
  KEY `lang_enum` (`lang_enum`),
  KEY `worldid_mixed` (`worldid_mixed`),
  KEY `shared_group_id_bigint` (`shared_group_id_bigint`),
  KEY `longitude` (`longitude`),
  KEY `latitude` (`latitude`),
  KEY `published` (`published`),
  KEY `email_contact` (`email_contact`)
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `na_comdef_meetings_data` (
  `meetingid_bigint` bigint(20) unsigned NOT NULL,
  `key` varchar(32) NOT NULL,
  `field_prompt` tinytext,
  `lang_enum` varchar(7) DEFAULT NULL,
  `visibility` int(1) DEFAULT NULL,
  `data_string` tinytext,
  `data_bigint` bigint(20) DEFAULT NULL,
  `data_double` double DEFAULT NULL,
  KEY `data_bigint` (`data_bigint`),
  KEY `data_double` (`data_double`),
  KEY `meetingid_bigint` (`meetingid_bigint`),
  KEY `lang_enum` (`lang_enum`),
  KEY `key` (`key`),
  KEY `visibility` (`visibility`)
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `na_comdef_meetings_longdata` (
  `meetingid_bigint` bigint(20) unsigned NOT NULL,
  `key` varchar(32) NOT NULL,
  `field_prompt` varchar(255) DEFAULT NULL,
  `lang_enum` varchar(7) DEFAULT NULL,
  `visibility` int(1) DEFAULT NULL,
  `data_longtext` text,
  `data_blob` blob,
  KEY `meetingid_bigint` (`meetingid_bigint`),
  KEY `lang_enum` (`lang_enum`),
  KEY `field_prompt` (`field_prompt`),
  KEY `key` (`key`),
  KEY `visibility` (`visibility`)
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `na_comdef_formats` (
  `shared_id_bigint` bigint(20) unsigned NOT NULL,
  `key_string` varchar(255) DEFAULT NULL,
  `icon_blob` longblob,
  `worldid_mixed` varchar(255) DEFAULT NULL,
  `lang_enum` varchar(7) NOT NULL DEFAULT 'en',
  `name_string` tinytext,
  `description_string` text,
  `format_type_enum` varchar(7) DEFAULT NULL,
  KEY `shared_id_bigint` (`shared_id_bigint`),
  KEY `worldid_mixed` (`worldid_mixed`),
  KEY `format_type_enum` (`format_type_enum`),
  KEY `lang_enum` (`lang_enum`),
  KEY `key_string` (`key_string`),
  FULLTEXT KEY `description_string` (`description_string`),
  FULLTEXT KEY `name_string` (`name_string`)
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `na_comdef_service_bodies` (
  `id_bigint` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name_string` tinytext NOT NULL,
  `description_string` text NOT NULL,
  `lang_enum` varchar(7) NOT NULL DEFAULT 'en',
  `worldid_mixed` varchar(255) DEFAULT NULL,
  `kml_file_uri_string` varchar(255) DEFAULT NULL,
  `principal_user_bigint` bigint(20) unsigned DEFAULT NULL,
  `editors_string` varchar(255) DEFAULT NULL,
  `uri_string` varchar(255) DEFAULT NULL,
  `sb_type` varchar(32) DEFAULT NULL,
  `sb_owner` bigint(20) unsigned DEFAULT NULL,
  `sb_owner_2` bigint(20) unsigned DEFAULT NULL,
  `sb_meeting_email` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id_bigint`),
  KEY `worldid_mixed` (`worldid_mixed`),
  KEY `kml_file_uri_string` (`kml_file_uri_string`),
  KEY `principal_user_bigint` (`principal_user_bigint`),
  KEY `editors_string` (`editors_string`),
  KEY `lang_enum` (`lang_enum`),
  KEY `uri_string` (`uri_string`),
  KEY `sb_type` (`sb_type`),
  KEY `sb_owner` (`sb_owner`),
  KEY `sb_owner_2` (`sb_owner_2`),
  KEY `sb_meeting_email` (`sb_meeting_email`),
  FULLTEXT KEY `name_string` (`name_string`),
  FULLTEXT KEY `description_string` (`description_string`)
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `na_comdef_users` (
  `id_bigint` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_level_tinyint` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `name_string` tinytext NOT NULL,
  `description_string` text NOT NULL,
  `email_address_string` varchar(255) NOT NULL,
  `login_string` varchar(255) NOT NULL,
  `password_string` varchar(255) NOT NULL,
  `last_access_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lang_enum` varchar(7) NOT NULL DEFAULT 'en',
  PRIMARY KEY (`id_bigint`),
  UNIQUE KEY `login_string` (`login_string`),
  KEY `user_level_tinyint` (`user_level_tinyint`),
  KEY `email_address_string` (`email_address_string`),
  KEY `last_access_datetime` (`last_access_datetime`),
  KEY `lang_enum` (`lang_enum`),
  FULLTEXT KEY `name_string` (`name_string`),
  FULLTEXT KEY `description_string` (`description_string`)
) CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `na_comdef_changes` (
  `id_bigint` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id_bigint` bigint(20) unsigned NOT NULL,
  `service_body_id_bigint` bigint(20) unsigned NOT NULL,
  `lang_enum` varchar(7) NOT NULL,
  `change_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `object_class_string` varchar(64) NOT NULL,
  `change_name_string` tinytext,
  `change_description_text` text,
  `before_id_bigint` bigint(20) unsigned DEFAULT NULL,
  `before_lang_enum` varchar(7) DEFAULT NULL,
  `after_id_bigint` bigint(20) unsigned DEFAULT NULL,
  `after_lang_enum` varchar(7) DEFAULT NULL,
  `change_type_enum` varchar(32) NOT NULL,
  `before_object` blob,
  `after_object` blob,
  PRIMARY KEY (`id_bigint`),
  KEY `user_id_bigint` (`user_id_bigint`),
  KEY `service_body_id_bigint` (`service_body_id_bigint`),
  KEY `lang_enum` (`lang_enum`),
  KEY `change_type_enum` (`change_type_enum`),
  KEY `change_date` (`change_date`),
  KEY `before_id_bigint` (`before_id_bigint`),
  KEY `after_id_bigint` (`after_id_bigint`),
  KEY `before_lang_enum` (`before_lang_enum`),
  KEY `after_lang_enum` (`after_lang_enum`),
  KEY `object_class_string` (`object_class_string`),
  FULLTEXT KEY `change_name_string` (`change_name_string`),
  FULLTEXT KEY `change_description_text` (`change_description_text`)
) CHARSET=utf8;

• Other Topics
• An Illustration of the BMLT System
• The BMLT Database
• BMLT Roles
• Is Your Server Set Up Correctly?
• Test Site Links
• The BMLT Satellite Base Class
• The BMLTiOSLib iOS Framework
• Configuration Management And Release