Pil Ho Kim > Project > E-Log > Data Structure

Data Structure

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