SUMMARY: A first stab at establishing a workable database schema for storing tasks. Learned how to use WordPress’ dbDelta() function to create and modify tables in the database. Figured out the basic SQL statements to create tables with the appropriate data types and indexes.
I know, I know…I should have a better attitude about this. This is just the slow part of the learning: figuring out all the gotchyas about actually making something happen in your new language of choice. I have a shaky big-picture view of SQL, and now it’s time to batten down the hatches, ARR!
Today’s exciting code excerpt is creating a table via WordPress’ finicky dbDelta() function. Here’s the basic setup I’m using:
function dstest_create_tables() {
global $wpdb;
SDBG::out(".... creating tables...");
// create names of table
$table_prefix = $wpdb->prefix."second_";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
// TASK TABLE
// the main wrapper of task details
$table = $table_prefix . "tasks";
$sql = "CREATE TABLE " . $table . " (
id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
user_id mediumint(8) unsigned,
task_name char,
task_detail_id mediumint(8) unsigned,
task_progress ENUM ('idea','spec','plan','assign','produce','delivered','reviewed'),
task_priority ENUM ('immediate','shortwindow','deadline','flexdate','nicetohave','nopriority'),
task_status ENUM ('queued','active','bumped','starved','inprogress','closing','complete'),
task_terminus ENUM ('cancelled','nomoreneed','superseded','badfaith','delearly','delontime','dellate'),
task_followup ENUM ('rjnonfunction','rjnotspec','rjlate','okexceed','okworks','okproblems'),
PRIMARY KEY (id)
);";
$result = dbDelta($sql);
SDBG::out(" $table");
SDBG::out($result);
// TASK DETAILS TABLE
// these identify a pre-defined task assignment, possibly recurring
// they're referred to by the TASK table
$table = $table_prefix . "task_details";
$sql = "CREATE TABLE " . $table . " (
id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
name char,
description char,
action char,
deliverable char,
PRIMARY KEY (id)
);";
$result = dbDelta($sql);
SDBG::out(" $table");
SDBG::out($result);
// PREREQUISITE TASKS
// relationship between a task and prerequisite tasks
$table = $table_prefix . "task_prereqs";
$sql = "CREATE TABLE " . $table . " (
task_id mediumint(8) unsigned NOT NULL,
preq_id mediumint(8) unsigned NOT NULL,
INDEX (task_id)
);";
$result = dbDelta($sql);
SDBG::out(" $table");
SDBG::out($result);
// PREREQUISITE TASKS
// relationship between a task and dependent tasks
$table = $table_prefix . "task_deps";
$sql = "CREATE TABLE " . $table . " (
task_id mediumint(8) unsigned NOT NULL,
dep_id mediumint(8) unsigned NOT NULL,
INDEX (task_id)
);";
$result = dbDelta($sql);
SDBG::out(" $table");
SDBG::out($result);
}
This code creates a table with what are, I think, the right features. The TASK table is what stores the definition of a task, which I define as:
- the task id
- a stored user_id of the creator
- a task name, up to 255 characters
- a task detail id, which points to the actual task information
- task progress enumeration
- task priority enumeration
- task status enumeration
- task terminating circumstances enum
- task followup enum
I don’t have time tracking in here, and I am not sure I need to track all this state. I don’t know exactly what I’m doing with SQL…I figure once I get the skeleton engine going and can start USING the app, I’ll be playing all kinds of games to figure out how to best organize this. Right now, I don’t have enough experience to tell one way or the other. First responsibility I owe to myself is to learn it the hard way.
Inserting Data
Here’s my half-assed test code:
function dstest_init_tables() {
global $wpdb;
$table_prefix = $wpdb->prefix.'second_';
$table = $table_prefix . 'tasks';
// add data
SDBG::out(".... inserting data into $table...");
$rows_affected
= $wpdb->insert ( $table,
array ('task_name' => 'first task', 'task_detail_id' => '23', 'task_progress' => 'assign', 'task_followup' => 'okexceed' ),
array ('%s','%d','%s','%s')
);
SDBG::out("---- dumping rows affected ----");
SDBG::out($rows_affected);
}
It uses the built-in wpdb database object’s insert() method, which does nice things like sanitizing the sql input for us. The parameters:
- table name
- column and data in array format
- data format (optional)
I wasn’t sure what would happen with the enums, but they work both as strings (just match the name of the enum) and as decimals.
To see what happened on the server side, I used the free MySQL Workbench Community Edition tool to inspect the database as I was running these commands. So far, so good.
0 Comments