APNS MySQL Tables

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

NOTE: If you do not have full database privileges, you will need to use our alternate database file. You will NOT need the `apns_device_history` table as it will no longer be used, so you can ignore the instructions below. See Installation: MySQL Database 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';
时间: 2024-11-20 01:15:06

APNS MySQL Tables的相关文章

Generation and Handling of Metadata Locks on RDS for MySQL Tables

Generation and Handling of Metadata Locks on RDS for MySQL Tables Source Article: www.alibabacloud.com/articles/93807 Introduction ApsaraDB for RDS, also known as Alibaba Cloud ApsaraDB for RDS (Relational Database System) is an on-demand database se

MySQL中文参考手册5(安装MySQL下)

mysql|参考|参考手册|中文 MySQL中文参考手册5(安装MySQL下)转载 译者:晏子 [返回][转发] 译者:晏子 (clyan@sohu.com)主页:http://linuxdb.yeah.net 4.12 Win32 注意事项这节描述在Win32上安装和使用MySQL,这也在MySQL Win32分发所带的"readme"文件中描述.  4.12.1 在Win32上安装MySQL如果你没有一个注册的MySQL版本,你应该首先下载共享软件版本,从:  MySQL 3.21

不用发愁 - 谈MySQL数据库的最大体积

mysql|数据|数据库 不用发愁 - 谈MySQL数据库的最大体积 在MySQL的参考手册"How Big Can MySQL Tables Be?"一节我们可以知道如下信息.MySQL 3.22版最大表尺寸限制为4GB(4 gigabyte).MySQL 3.23版本使用MySQL表格式,此限制则为800TB(2^63字节)的庞大规模!!800TB是一个什么量级概念?实际比较下Yahoo庞大的搜索引擎数据库目前仍没有(绝不可能)超过过30TB,那么我们就算作30TB,这就是说MyS

Linux下用C语言API连接MySQL数据库

像PHP和perl一样,MySQL也提供的C语言使用的API. C代码的API是随MySQL一起发布的. 它包含在mysqlclient库中, 可以使C程序来访问数据库. MySQL源码包中的许多客户端都是用C写的. 如果你正在找使用这些C API的例子, 可以看看客户端的写法.你可以在MySQL源码包的clients目录找到这些例子. 软件包 请确保你已经安装了必要的开发环境,比如gcc, mysql等等. 下面是编译一个程序所需要安装的软件包的列表 (Ubuntu为例): mysql-cli

巧用MySQL InnoDB引擎锁机制解决死锁问题

最近,在项目开发过程中,碰到了数据库死锁问题,在解决问题的过程中,笔者对MySQL InnoDB引擎锁机制的理解逐步加深. 案例如下: 在使用Show innodb status检查引擎状态时,发现了死锁问题: *** (1) TRANSACTION: TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read mysql tables in use 1, loc

使用mysqldump工具备份还原Mysql数据库

我们在网站数据维护中经常会遇到备份数据库,还原数据库的情况,我们一般用一下两种方式来处理: 1.使用into outfile 和 load data infile导入导出备份数据 这种方法的好处是,导出的数据可以自己规定格式,并且导出的是纯数据,不存在建表信息,你可以直接导入另外一个同数据库的不同表中,相对于mysqldump比较灵活机动. 我们来看下面的例子: (1)下面的mysql命令是把select的mytable表中的数据导出到/home/db_bak2012文件. select * f

MySQL数据库规范化设计

建立一个数据库表维护规范 在一个定期基础而非等到问题出现才实施数据库表的检查是一个好主意.应该考虑到建立一个预防性维护的时间表,以协助自动问题,是你可以采取措施进行修正: 执行常规的数据库备份并允许更新日志. 安排定期的常规表检查.通过检查表,将减少使用备份的机会.这个工作,在Windows下使用计划任务,Unix使用cron作业(一般从运行服务器所示用的该帐号的crontab文件中调用),并且很容易实现. 例如,你作为mysql用户运行服务器,则可以从mysql的crontab文件中建立定期检

[MySQL学习] Innodb锁系统(4) Insert/Delete 锁处理及死锁示例分析

A.INSERT 插入操作在函数btr_cur_optimistic_insert->btr_cur_ins_lock_and_undo->lock_rec_insert_check_and_lock这里进行锁的判断,我们简单的看看这个函数的流程: 1.首先先看看欲插入记录之后的数据上有没有锁,    next_rec = page_rec_get_next_const(rec);    next_rec_heap_no = page_rec_get_heap_no(next_rec);  

MySQL update use index merge(Using intersect) increase chances for deadlock

昨天一同事发现线上系统在并发更新的时候出现了死锁,通过排查定位于update更新使用了两个索引导致,死锁信息如下: *** (1) TRANSACTION: TRANSACTION 29285454235, ACTIVE 0.001 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 6 lock struct(s), heap size 1184, 4 row lock(s) MySQL thread id 6641616,