APNS MySQL Tables-白红宇

Before you get started: You can download this source code .

NOTE: If you do not have full database privileges, you will need to use our . You will NOT need the `apns_device_history` table as it will no longer be used, so you can ignore the instructions below. See for further information.

There are only three tables you need to create to get Easy APNs up and running. `apns_device_history` is an optional table that should only be installed if you have full database privileges.

Table 1: apns_device_history (optional)

This table keeps track of each time an app is launched. This particular table only gets populated when a device already exists in the apns_devices table. With this data, you can keep track of when the user turned on/off notifications, how often then launch the app... etc. Pretty handy to have in our opinion.

                  1           CREATE TABLE `apns_device_history` (          
       
                  2             `pid` int(9) unsigned NOT NULL auto_increment,          
       
                  3             `appname` varchar(255) NOT NULL,          
       
                  4             `appversion` varchar(25) default NULL,          
       
                  5             `deviceuid` char(40) NOT NULL,          
       
                  6             `devicetoken` char(64) NOT NULL,          
       
                  7             `devicename` varchar(255) NOT NULL,          
       
                  8             `devicemodel` varchar(100) NOT NULL,          
       
                  9             `deviceversion` varchar(25) NOT NULL,          
       
                  10             `pushbadge` enum('disabled','enabled') default 'disabled',          
       
                  11             `pushalert` enum('disabled','enabled') default 'disabled',          
       
                  12             `pushsound` enum('disabled','enabled') default 'disabled',          
       
                  13             `development` enum('production','sandbox') character set latin1 NOT NULL default 'production',          
       
                  14             `status` enum('active','uninstalled') NOT NULL default 'active',          
       
                  15             `archived` datetime NOT NULL,          
       
                  16             PRIMARY KEY  (`pid`),          
       
                  17             KEY `devicetoken` (`devicetoken`),          
       
                  18             KEY `devicename` (`devicename`),          
       
                  19             KEY `devicemodel` (`devicemodel`),          
       
                  20             KEY `deviceversion` (`deviceversion`),          
       
                  21             KEY `pushbadge` (`pushbadge`),          
       
                  22             KEY `pushalert` (`pushalert`),          
       
                  23             KEY `pushsound` (`pushsound`),          
       
                  24             KEY `development` (`development`),          
       
                  25             KEY `status` (`status`),          
       
                  26             KEY `appname` (`appname`),          
       
                  27             KEY `appversion` (`appversion`),          
       
                  28             KEY `deviceuid` (`deviceuid`),          
       
                  29             KEY `archived` (`archived`)          
       
                  30           ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Store unique device history';

Table 2: apns_devices (lines 31-48 optional)

This table keeps track of all unique devices registering for push notifications. We also keep track of the applications name and version number in case you are running multiple apps, you can see who is using what.

                  1           CREATE TABLE `apns_devices` (          
       
                  2             `pid` int(9) unsigned NOT NULL auto_increment,          
       
                  3             `appname` varchar(255) NOT NULL,          
       
                  4             `appversion` varchar(25) default NULL,          
       
                  5             `deviceuid` char(40) NOT NULL,          
       
                  6             `devicetoken` char(64) NOT NULL,          
       
                  7             `devicename` varchar(255) NOT NULL,          
       
                  8             `devicemodel` varchar(100) NOT NULL,          
       
                  9             `deviceversion` varchar(25) NOT NULL,          
       
                  10             `pushbadge` enum('disabled','enabled') default 'disabled',          
       
                  11             `pushalert` enum('disabled','enabled') default 'disabled',          
       
                  12             `pushsound` enum('disabled','enabled') default 'disabled',          
       
                  13             `development` enum('production','sandbox') character set latin1 NOT NULL default 'production',          
       
                  14             `status` enum('active','uninstalled') NOT NULL default 'active',          
       
                  15             `created` datetime NOT NULL,          
       
                  16             `modified` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,          
       
                  17             PRIMARY KEY  (`pid`),          
       
                  18             UNIQUE KEY `appname` (`appname`,`appversion`,`deviceuid`),          
       
                  19             KEY `devicetoken` (`devicetoken`),          
       
                  20             KEY `devicename` (`devicename`),          
       
                  21             KEY `devicemodel` (`devicemodel`),          
       
                  22             KEY `deviceversion` (`deviceversion`),          
       
                  23             KEY `pushbadge` (`pushbadge`),          
       
                  24             KEY `pushalert` (`pushalert`),          
       
                  25             KEY `pushsound` (`pushsound`),          
       
                  26             KEY `development` (`development`),          
       
                  27             KEY `status` (`status`),          
       
                  28             KEY `created` (`created`),          
       
                  29             KEY `modified` (`modified`)          
       
                  30           ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Store unique devices';          
       
                  31          DELIMITER ;;         
       
                  32           CREATE TRIGGER `Archive` BEFORE UPDATE ON `apns_devices` FOR EACH ROW INSERT INTO `apns_device_history` VALUES (          
       
                  33               NULL,          
       
                  34               OLD.`appname`,          
       
                  35               OLD.`appversion`,          
       
                  36               OLD.`deviceuid`,          
       
                  37               OLD.`devicetoken`,          
       
                  38               OLD.`devicename`,          
       
                  39               OLD.`devicemodel`,          
       
                  40               OLD.`deviceversion`,          
       
                  41               OLD.`pushbadge`,          
       
                  42               OLD.`pushalert`,          
       
                  43               OLD.`pushsound`,          
       
                  44               OLD.`development`,          
       
                  45               OLD.`status`,          
       
                  46               NOW()          
       
                  47          );;         
       
                  48          DELIMITER ;

Table 3: apns_messages

This is where the messages you send to the user will go. By default, Easy APNs is setup to store the messages in queue and not actually deliver them until instructed elsewhere. This is where you would setup a cron job to process the data that still needs to be delivered.

                  1           CREATE TABLE `apns_messages` (          
       
                  2             `pid` int(9) unsigned NOT NULL auto_increment,          
       
                  3             `fk_device` int(9) unsigned NOT NULL,          
       
                  4             `message` varchar(255) NOT NULL,          
       
                  5             `delivery` datetime NOT NULL,          
       
                  6             `status` enum('queued','delivered','failed') character set latin1 NOT NULL default 'queued',          
       
                  7             `created` datetime NOT NULL,          
       
                  8             `modified` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,          
       
                  9             PRIMARY KEY  (`pid`),          
       
                  10             KEY `fk_device` (`fk_device`),          
       
                  11             KEY `status` (`status`),          
       
                  12             KEY `created` (`created`),          
       
                  13             KEY `modified` (`modified`),          
       
                  14             KEY `message` (`message`),          
       
                  15             KEY `delivery` (`delivery`)          
       
                  16           ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Messages to push to APNS';