Postgresql中的RRULE解析 [英] RRULE parsing in Postgresql
问题描述
目标：从RRULE字符串生成时间戳列表，每个时间戳代表一个事件发生（即 FREQ = WEEKLY; INTERVAL = 2; COUNT = 8; WKST = SU; BYDAY = TU，TH ）和开始时间戳。
由于这些时间将根据用户的需求而改变，因此需要使用postgresql，因为（1）它具有严格的业务逻辑质量，并且（2）触发器（值更改时自动更新行）
The goal: to generate a list of timestamps, each representing an event occurrance, from an RRULE string (i.e. FREQ=WEEKLY;INTERVAL=2;COUNT=8;WKST=SU;BYDAY=TU,TH) and a starting timestamp.
Since these times will be changed at the whim of a user, postgresql is desired for (1) it's quality of strict business logic and (2) triggers (auto-update rows when a value changes)
替代解决方案：
我最终使用了plpythonu（PostgreSQL的Python语言）。 dateutil库具有出色的解析器解析器。
I ended up using plpythonu (python language for postgresql). The dateutil library has a great rrule parser.
mydatabase=# CREATE FUNCTION parse_occurrences(rule text, start timestamp) RETURNS timestamp[] AS
mydatabase-# 'from dateutil.rrule import *
mydatabase'# from dateutil.parser import *
mydatabase'# import datetime
mydatabase'# dates = list(rrulestr(rule, dtstart=parse(start)))
mydatabase'# return [str(a)[0:10] for a in dates]'
mydatabase-# LANGUAGE plpythonu;
CREATE FUNCTION
mydatabase=# SELECT parse_occurrences('FREQ=WEEKLY;INTERVAL=2;COUNT=8;WKST=SU;BYDAY=TU,TH'::text, now()::timestamp);
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"2013-02-14 00:00:00","2013-02-26 00:00:00","2013-02-28 00:00:00","2013-03-12 00:00:00","2013-03-14 00:00:00","2013-03-26 00:00:00","2013-03-28 00:00:00","2013-04-09 00:00:00"}
（原始）尝试：连接 C-library libical > Postgresql C-Extensions 。这需要专门的C程序执行以下操作：（1）将postgresql数据类型转换为C数据类型，（2）执行所有必需的C库函数，并且（3）使用头文件 postgres.h。
The (original) endeavor: to connect the C-library libical with Postgresql C-Extensions. This requires a specialized C program to do the following: (1) convert postgresql data types to C-data types, (2) execute all necessary C-library functions, and (3) return the data in postgresql formats using the header file "postgres.h".
连接器文件：ical_recur.c
#include "postgres.h"
#include "icalrecur.h"
#include <time.h> /* for time() */
#include "fmgr.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(get_occurrences);
Datum
get_occurrences(PG_FUNCTION_ARGS)
{
//char* rule; /* rule string */
time_t start; /* start time */
int count;
char *rrule;
// *rule = PG_GETARG_CHAR(0);
start = (time_t) PG_GETARG_INT32(1);
count = (int) PG_GETARG_INT32(2);
*rrule = PG_GETARG_CHAR(0);
time_t *result[count]; /* output array */
icalrecur_expand_recurrence(rrule, start, count, *result);
PG_RETURN_INT32(*result);
}
准备连接器文件：
步骤1：编译为目标文件，创建共享对象，复制到位置PostgreSQL查找C扩展名
Step 1: Compile to the object file, create the shared object, copy to the location postgresql looks for C-extensions
sudo gcc -I/usr/local/libical/lib -lical -I/usr/include/postgresql/9.2/server -fpic -c ical_recur.c
sudo gcc -shared -L/usr/local/libical/lib -lical -o ical_recur.so ical_recur.o
sudo cp ical_recur.so /usr/lib/postgresql/9.2/lib/
步骤2：添加要由C&重新加载配置
Step 2: Add the libical lib folder to be found by C & reload the configuration
sudo echo "/usr/local/libical/lib" >> /etc/ld.so.conf.d/libc.conf
sudo ldconfig
测试连接器文件：
第1步：加载psql并创建功能
Step 1: Load up psql and CREATE the FUNCTION
psql
mydatabase=# CREATE FUNCTION get_occurrences(text, integer, integer) RETURNS int[]
mydatabase=- AS '$libdir/ical_recur', 'get_occurrences'
mydatabase=- LANGUAGE C STRICT;
CREATE FUNCTION
mydatabase=#
当前障碍：
C函数使PostgreSQL服务器崩溃。
The C-function has crashed the postgresql server.
psql (9.2.3)
Type "help" for help.
mydatabase=# SELECT get_occurrences('FREQ=WEEKLY;INTERVAL=2;COUNT=8;WKST=SU;BYDAY=TU,TH', now()::timestamp, 5);
The connection to the server was lost. Attempting reset: Failed.
!>
日志...
2013-02-11 22:03:33 UTC LOG: server process (PID 22733) was terminated by signal 11: Segmentation fault
2013-02-11 22:03:33 UTC DETAIL: Failed process was running: SELECT get_occurrences('FREQ=WEEKLY;INTERVAL=2;COUNT=8;WKST=SU;BYDAY=TU,TH', now()::timestamp, 5);
2013-02-11 22:03:33 UTC LOG: terminating any other active server processes
2013-02-11 22:03:33 UTC WARNING: terminating connection because of crash of another server process
2013-02-11 22:03:33 UTC DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2013-02-11 22:03:33 UTC HINT: In a moment you should be able to reconnect to the database and repeat your command.
2013-02-11 22:03:33 UTC LOG: all server processes terminated; reinitializing
2013-02-11 22:03:33 UTC FATAL: the database system is in recovery mode
2013-02-11 22:03:33 UTC LOG: database system was interrupted; last known up at 2013-02-11 21:47:26 UTC
2013-02-11 22:03:33 UTC LOG: database system was not properly shut down; automatic recovery in progress
2013-02-11 22:03:33 UTC LOG: redo starts at 0/1903A0C
2013-02-11 22:03:33 UTC LOG: record with zero length at 0/190E1E0
2013-02-11 22:03:33 UTC LOG: redo done at 0/190E1B8
2013-02-11 22:03:33 UTC LOG: last completed transaction was at log time 2013-02-11 22:03:29.641161+00
2013-02-11 22:03:33 UTC LOG: database system is ready to accept connections
2013-02-11 22:03:33 UTC LOG: autovacuum launcher started
已更新：
我已经更新了方法以解决一些建议。
I've updated the method to address some suggestions.
#include "postgres.h"
#include "icalrecur.h"
#include <time.h> /* for time() */
#include "fmgr.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(get_occurrences);
Datum
get_occurrences(PG_FUNCTION_ARGS)
{
time_t start = (time_t) PG_GETARG_INT32(1); /* convert int to time_t */
int count = PG_GETARG_INT32(2);
char rrule = PG_GETARG_CHAR(0);
char *_rrule = &rrule; /* icalrecur wants a pointer to the rrule */
time_t result[count]; /* instantiate the output array */
int success = icalrecur_expand_recurrence(_rrule, start, count, result);
/* convert time_t values to int */
int *output = malloc(sizeof(result));
int i;
for(i = 0; i < (int) (sizeof(result) / sizeof(result[0]) - 1); i++){
output[i] = (int) result[i];
}
if(success != 1){
PG_RETURN_INT32(0);
} else {
PG_RETURN_INT32(output);
}
}
与...一起建造。
sudo gcc -Wall -Wextra -l/usr/local/libical/lib/ical -I/usr/local/libical/include/libical -I/usr/include/postgresql/9.2/server -fpic -c ical_recur.c
sudo gcc -Wall -shared -static -L/usr/local/libical/lib -lical -o ical_recur.so ical_recur.o
sudo cp libical.so /usr/lib/postgresql/9.2/lib/
输入数据库& ;
Enter database & execute with..
mydb=# CREATE FUNCTION get_occurrences(text, integer, integer) RETURNS int[]
AS '$libdir/ical_recur', 'get_occurrences'
LANGUAGE C STRICT;
CREATE FUNCTION
mydb=# SELECT get_occurrences('FREQ=WEEKLY;INTERVAL=2;COUNT=8;WKST=SU;BYDAY=TU,TH', 1360690024, 5);
The connection to the server was lost. Attempting reset: Failed.
!> \q
相同的日志输出。 文档注意以下参数：
Same log output. The documentation notes the parameters:
int icalrecur_expand_recurrence（char * rule，time_t start，int count，time_t * array）
int icalrecur_expand_recurrence(char * rule, time_t start, int count, time_t * array)
问题：：如何在不使postgres崩溃的情况下使其正常工作？
Question: How can we get this to work without postgres crashing?
推荐答案
所示代码中有两个与指针相关的明显缺陷：
There are two obvious pointer-related flaws in the code shown:
第一：
*rrule = PG_GETARG_CHAR(0);
在此执行点，规则为一个未初始化的指针，该指令尝试将char写入其指向的随机位置。
At this point of execution, rrule is a non-initialized pointer, and this instruction tries to write a char into the random location it points to. This generally causes the kind of crash you're seeing.
第二个：
time_t *result[count]; /* output array */
icalrecur_expand_recurrence(rrule, start, count, *result);
结果应该为<$的数组c $ c> time_t ，而不是 time_t * 的数组，应将其作为 result ，而不是 * result ，该结果此时仍未初始化。
result should be an array of time_t, not an array of time_t*, and it should be passed as result, not *result which again is not initialized at this point.
这篇关于Postgresql中的RRULE解析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
