回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p><strong>目标:</strong>从RRULE字符串(即<code>FREQ=WEEKLY;INTERVAL=2;COUNT=8;WKST=SU;BYDAY=TU,TH</code>)和起始时间戳生成一个时间戳列表,每个时间戳代表一个事件发生。
由于这些时间会根据用户的心血来潮而改变,postgresql需要(1)严格的业务逻辑质量和(2)触发器(值更改时自动更新行)</p>
<p><strong>替代方案:</strong></p>
<p>最后我使用了plpythonu(用于postgresql的python语言)。<a href="http://labix.org/python-dateutil" rel="nofollow">dateutil library</a>有一个很棒的rrule解析器。在</p>
<pre><code>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"}
</code></pre>
<p><strong>将<a href="https://github.com/Marketcircle/libical" rel="nofollow">C-library libical</a>与{a3}连接起来的(原始)努力:</strong>。这需要一个专门的C程序来执行以下操作:(1)将postgresql数据类型转换为C数据类型;(2)执行所有必需的C库函数;(3)使用头文件“postgres.h”以postgresql格式返回数据。在</p>
<p><strong>连接器文件:ical_recur.c</strong></p>
^{pr2}$
<p><strong>准备连接器文件:</strong></p>
<p>步骤1:编译到对象文件,创建共享对象,复制到postgresql查找C扩展的位置</p>
<pre><code>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/
</code></pre>
<p>步骤2:添加要由C找到的libical lib文件夹并重新加载配置</p>
<pre><code>sudo echo "/usr/local/libical/lib" >> /etc/ld.so.conf.d/libc.conf
sudo ldconfig
</code></pre>
<p><strong>测试连接器文件:</strong></p>
<p>步骤1:加载psql并创建函数</p>
<pre><code>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=#
</code></pre>
<p><strong>当前的障碍:</strong></p>
<p>C函数使postgresql服务器崩溃。在</p>
<pre><code>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.
!>
</code></pre>
<p>日志。。。在</p>
<pre><code>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
</code></pre>
<p><strong>更新:</strong></p>
<p>我已经更新了方法以解决一些建议。在</p>
<pre><code>#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);
}
}
</code></pre>
<p>用。。在</p>
<pre><code>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/
</code></pre>
<p>输入数据库并执行(&E)。。在</p>
<pre><code>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
</code></pre>
<p>相同的日志输出。<a href="http://libical.sourcearchive.com/documentation/0.43-3/icalrecur_8c_488780da8434731021af8d791b087b77.html#488780da8434731021af8d791b087b77" rel="nofollow">documentation</a>注意参数:</p>
<blockquote>
<p>int icalrecur_expand_recurrence(char * rule, time_t start, int count, time_t * array)</p>
</blockquote>
<p><strong>问题:</strong>我们如何在不让postgres崩溃的情况下实现这一点?在</p>