This pages manages the most recent e-Log data structure -- mostly focused on the detail structure of logging data objects.

Data structure

iPhone

  • iPhone photos
 CREATE TABLE `iphone_photos` (
  `filename` VARCHAR(255) NOT NULL,
  `iphone_unix_timestamp` BIGINT(20) NOT NULL DEFAULT '0',
  `content` mediumblob,
  `iphone_timestamp` TIMESTAMP NULL DEFAULT NULL,
  `iphone_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`filename`,`iphone_unix_timestamp`),
  KEY `timestamp_key` (`iphone_timestamp`),
  KEY `unix_timestamp_index` (`iphone_unix_timestamp`),
  KEY `filename` (`filename`),
  KEY `sensecam_timestamp` (`iphone_timestamp`,`filename`),
  KEY `image_UTC_timestamp` (`iphone_UTC_timestamp`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=21204;
  • iPhone photo EXIF information
 CREATE TABLE `iphone_photos_exif` (
  `filename` VARCHAR(100) NOT NULL,
  `iphone_unix_timestamp` BIGINT(20) NOT NULL DEFAULT '0',
  `section` VARCHAR(50) NOT NULL DEFAULT '',
  `section_key` VARCHAR(50) NOT NULL DEFAULT '',
  `key_value` text,
  PRIMARY KEY (`filename`,`iphone_unix_timestamp`,`section`,`section_key`),
  KEY `name` (`section`),
  FULLTEXT KEY `value` (`key_value`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=21204;


  • iPhone GPS
 CREATE TABLE `iphone_gps` (
  `latitude` DOUBLE NOT NULL,
  `longitude` DOUBLE NOT NULL,
  `elevation` DOUBLE NOT NULL,
  `gps_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `gps_timezone` VARCHAR(255) DEFAULT NULL,
  `gps_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`latitude`,`longitude`,`elevation`,`gps_timestamp`),
  KEY `timestamp_key` (`gps_timestamp`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`),
  KEY `elevation` (`elevation`),
  KEY `gps_timestamp` (`gps_timestamp`),
  KEY `gps_timezone` (`gps_timezone`),
  KEY `gps_UTC_timestamp` (`gps_UTC_timestamp`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Communication

  • GMail IMAP
  CREATE TABLE `gmail_allmail` (
  `gmail_msgno` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `gmail_subject` VARCHAR(255) DEFAULT NULL,
  `gmail_from` VARCHAR(255) DEFAULT NULL,
  `gmail_to` VARCHAR(255) DEFAULT NULL,
  `gmail_UTC_date` datetime DEFAULT NULL,
  `gmail_message_id` tinytext,
  `gmail_references` text,
  `gmail_in_reply_to` text,
  `gmail_size` INT(11) DEFAULT NULL,
  `gmail_uid` INT(11) DEFAULT NULL,
  `gmail_recent` INT(11) DEFAULT NULL,
  `gmail_flagged` INT(11) DEFAULT NULL,
  `gmail_answered` INT(11) DEFAULT NULL,
  `gmail_deleted` INT(11) DEFAULT NULL,
  `gmail_seen` INT(11) DEFAULT NULL,
  `gmail_draft` INT(11) DEFAULT NULL,
  `gmail_body` longtext,
  `gmail_body_striped` longtext,
  PRIMARY KEY (`gmail_msgno`),
  KEY `gmail_uid` (`gmail_uid`),
  KEY `gmail_to` (`gmail_to`),
  KEY `gmail_from` (`gmail_from`),
  FULLTEXT KEY `gmail_subject` (`gmail_subject`),
  FULLTEXT KEY `gmail_body` (`gmail_body`),
  FULLTEXT KEY `gmail_message_id` (`gmail_message_id`),
  FULLTEXT KEY `gmail_references` (`gmail_references`),
  FULLTEXT KEY `gmail_in_reply_to` (`gmail_in_reply_to`),
  FULLTEXT KEY `gmail_body_striped` (`gmail_body_striped`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


  • Phone record
CREATE TABLE `phone_record` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `call_type` VARCHAR(255) DEFAULT NULL,
  `call_name` VARCHAR(255) DEFAULT NULL,
  `call_phone number` VARCHAR(255) DEFAULT NULL,
  `call_duration` VARCHAR(255) DEFAULT NULL,
  `call_timestamp` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
  `call_date` VARCHAR(255) DEFAULT NULL,
  `call_time` VARCHAR(255) DEFAULT NULL,
  `call_am_pm` VARCHAR(3) DEFAULT NULL,
  `call_message` VARCHAR(255) DEFAULT NULL,
  `call_date_backup` VARCHAR(255) DEFAULT NULL,
  `call_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=244 DEFAULT CHARSET=utf8;

People

  • Sensecam people information face-identified from pictures
CREATE TABLE `sensecam_people` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` CHAR(100) NOT NULL,
  `filename` CHAR(125) NOT NULL,
  `face` mediumblob,
  PRIMARY KEY (`id`),
  UNIQUE KEY `filename` (`filename`,`name`)
 ) ENGINE=MyISAM AUTO_INCREMENT=7115 DEFAULT CHARSET=utf8;
  • Google contacts
CREATE TABLE `gmail_contacts` (
  `Name` VARCHAR(255) DEFAULT NULL,
  `Given Name` VARCHAR(255) DEFAULT NULL,
  `Additional Name` VARCHAR(255) DEFAULT NULL,
  `Family Name` VARCHAR(255) DEFAULT NULL,
  `Yomi Name` VARCHAR(255) DEFAULT NULL,
  `Given Name Yomi` VARCHAR(255) DEFAULT NULL,
  `Additional Name Yomi` VARCHAR(255) DEFAULT NULL,
  `Family Name Yomi` VARCHAR(255) DEFAULT NULL,
  `Name Prefix` VARCHAR(255) DEFAULT NULL,
  `Name Suffix` VARCHAR(255) DEFAULT NULL,
  `Initials` VARCHAR(255) DEFAULT NULL,
  `Nickname` VARCHAR(255) DEFAULT NULL,
  `Short Name` VARCHAR(255) DEFAULT NULL,
  `Maiden Name` VARCHAR(255) DEFAULT NULL,
  `Birthday` VARCHAR(255) DEFAULT NULL,
  `Gender` VARCHAR(255) DEFAULT NULL,
  `Location` VARCHAR(255) DEFAULT NULL,
  `Billing Information` VARCHAR(255) DEFAULT NULL,
  `Directory Server` VARCHAR(255) DEFAULT NULL,
  `Mileage` VARCHAR(255) DEFAULT NULL,
  `Occupation` VARCHAR(255) DEFAULT NULL,
  `Hobby` VARCHAR(255) DEFAULT NULL,
  `Sensitivity` VARCHAR(255) DEFAULT NULL,
  `Priority` VARCHAR(255) DEFAULT NULL,
  `Subject` VARCHAR(255) DEFAULT NULL,
  `Notes` text,
  `Group Membership` VARCHAR(255) DEFAULT NULL,
  `E-mail 1 - Type` VARCHAR(255) DEFAULT NULL,
  `E-mail 1 - Value` VARCHAR(255) DEFAULT NULL,
  `E-mail 2 - Type` VARCHAR(255) DEFAULT NULL,
  `E-mail 2 - Value` VARCHAR(255) DEFAULT NULL,
  `E-mail 3 - Type` VARCHAR(255) DEFAULT NULL,
  `E-mail 3 - Value` VARCHAR(255) DEFAULT NULL,
  `Phone 1 - Type` VARCHAR(255) DEFAULT NULL,
  `Phone 1 - Value` VARCHAR(255) DEFAULT NULL,
  `Phone 2 - Type` VARCHAR(255) DEFAULT NULL,
  `Phone 2 - Value` VARCHAR(255) DEFAULT NULL,
  `Phone 3 - Type` VARCHAR(255) DEFAULT NULL,
  `Phone 3 - Value` VARCHAR(255) DEFAULT NULL,
  `Phone 4 - Type` VARCHAR(255) DEFAULT NULL,
  `Phone 4 - Value` VARCHAR(255) DEFAULT NULL,
  `Phone 5 - Type` VARCHAR(255) DEFAULT NULL,
  `Phone 5 - Value` VARCHAR(255) DEFAULT NULL,
  `Address 1 - Type` VARCHAR(255) DEFAULT NULL,
  `Address 1 - Formatted` VARCHAR(255) DEFAULT NULL,
  `Address 1 - Street` VARCHAR(255) DEFAULT NULL,
  `Address 1 - City` VARCHAR(255) DEFAULT NULL,
  `Address 1 - PO Box` VARCHAR(255) DEFAULT NULL,
  `Address 1 - Region` VARCHAR(255) DEFAULT NULL,
  `Address 1 - Postal Code` VARCHAR(255) DEFAULT NULL,
  `Address 1 - Country` VARCHAR(255) DEFAULT NULL,
  `Address 1 - Extended Address` VARCHAR(255) DEFAULT NULL,
  `Organization 1 - Type` VARCHAR(255) DEFAULT NULL,
  `Organization 1 - Name` VARCHAR(255) DEFAULT NULL,
  `Organization 1 - Yomi Name` VARCHAR(255) DEFAULT NULL,
  `Organization 1 - Title` VARCHAR(255) DEFAULT NULL,
  `Organization 1 - Department` VARCHAR(255) DEFAULT NULL,
  `Organization 1 - Symbol` VARCHAR(255) DEFAULT NULL,
  `Organization 1 - Location` VARCHAR(255) DEFAULT NULL,
  `Organization 1 - Job Description` VARCHAR(255) DEFAULT NULL,
  `Website 1 - Type` VARCHAR(255) DEFAULT NULL,
  `Website 1 - Value` VARCHAR(255) DEFAULT NULL,
  `Custom Field 1 - Type` VARCHAR(255) DEFAULT NULL,
  `Custom Field 1 - Value` VARCHAR(255) DEFAULT NULL,
  `Jot 1 - Type` VARCHAR(255) DEFAULT NULL,
  `Jot 1 - Value` VARCHAR(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  • String normalization for comparison
CREATE TABLE `people_name` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `source` VARCHAR(100) DEFAULT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `normalized_name` VARCHAR(255) DEFAULT NULL,
  `name_hex` VARCHAR(255) DEFAULT NULL,
  `normalized_name_hex` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2571 DEFAULT CHARSET=utf8;

See the usage

SELECT
	BINARY gmail.`name` AS gmail_name,
	BINARY sensecam.name AS sensecam_name
FROM people_name AS gmail,
	people_name AS sensecam
WHERE gmail.source = 'gmail' AND
	sensecam.source = 'sensecam' AND
	gmail.normalized_name = sensecam.normalized_name
ORDER BY sensecam.name

ViconRevue

  • ViconRevue image data structure
CREATE TABLE `sensecam_images` (
  `filename` VARCHAR(255) NOT NULL,
  `content` mediumblob,
  `sensecam_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sensecam_unix_timestamp` BIGINT(20) DEFAULT NULL,
  `image_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`sensecam_timestamp`,`filename`),
  KEY `timestamp_key` (`sensecam_timestamp`),
  KEY `unix_timestamp_index` (`sensecam_unix_timestamp`),
  KEY `filename` (`filename`),
  KEY `sensecam_timestamp` (`sensecam_timestamp`,`filename`),
  KEY `image_UTC_timestamp` (`image_UTC_timestamp`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=21204;
  • Accelerometer
CREATE TABLE `sensecam_sensor_acc` (
  `sensecam_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `axis_1` DOUBLE NOT NULL,
  `axis_2` DOUBLE NOT NULL,
  `axis_3` DOUBLE NOT NULL,
  `sensecam_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`sensecam_timestamp`,`axis_1`,`axis_2`,`axis_3`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  • Battery level
CREATE TABLE `sensecam_sensor_bat` (
  `sensecam_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `batterylevel` DOUBLE NOT NULL,
  `sensecam_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`sensecam_timestamp`,`batterylevel`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  • Camera information
CREATE TABLE `sensecam_sensor_cam` (
  `sensecam_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `filename` VARCHAR(255) NOT NULL,
  `switch` VARCHAR(2) DEFAULT NULL,
  `sensecam_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`sensecam_timestamp`,`filename`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  • light level
CREATE TABLE `sensecam_sensor_clr` (
  `sensecam_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lightlevel` DOUBLE NOT NULL,
  `sensecam_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`sensecam_timestamp`,`lightlevel`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  • Megnetometer
CREATE TABLE `sensecam_sensor_mag` (
  `sensecam_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mag_1` DOUBLE NOT NULL,
  `mag_2` DOUBLE NOT NULL,
  `mag_3` DOUBLE NOT NULL,
  `sensecam_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`sensecam_timestamp`,`mag_1`,`mag_2`,`mag_3`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  • Infrared
CREATE TABLE `sensecam_sensor_pir` (
  `sensecam_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `pir` INT(11) NOT NULL,
  `sensecam_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`sensecam_timestamp`,`pir`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  • Temperature
CREATE TABLE `sensecam_sensor_tmp` (
  `sensecam_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `temperature` DOUBLE NOT NULL,
  `sensecam_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`sensecam_timestamp`,`temperature`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Time zone

 CREATE TABLE `time_zone` (
  `ISO3166_CountryCode` CHAR(2) DEFAULT NULL,
  `latitude` DOUBLE DEFAULT NULL,
  `longitude` DOUBLE DEFAULT NULL,
  `zonename` VARCHAR(255) DEFAULT NULL,
  `comments` VARCHAR(255) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Database configuration

As of now, I use MySQL 5.1.44 on Mac OS X 10.6.4 and all table configurations above are working on it. See the below my.cnf dump for detail server configurations.

# Example MySQL config file FOR very LARGE systems.
#
# This IS FOR a LARGE system WITH memory OF 1G-2G WHERE the system runs mainly
# MySQL.
#
# You can copy this file TO
# /etc/my.cnf TO SET global options,
# mysql-data-dir/my.cnf TO SET server-specific options (IN this
# installation this directory IS /Applications/MAMP/db/mysql) OR
# ~/.my.cnf TO SET user-specific options.
#
# IN this file, you can USE ALL long options that a program supports.
# IF you want TO know which options a program supports, run the program
# WITH the "--help" OPTION.
 
# The following options will be passed TO ALL MySQL clients
[client]
#password	= your_password
port		= 3306
socket		= /Applications/MAMP/tmp/mysql/mysql.sock
 
# Here follows entries FOR SOME specific programs
 
# The MySQL server
[mysqld]
 
# Pilho configuration
max_sp_recursion_depth = 255
innodb_file_per_table
 
# original configuration
port		= 3306
socket		= /Applications/MAMP/tmp/mysql/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 10M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
 
server-id	= 1
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# Remove the NEXT comment CHARACTER IF you are NOT familiar WITH SQL
#safe-updates
 
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout