-- ******************************
-- Table channels
-- ------------------------------
DROP TABLE channels CASCADE;
CREATE TABLE channels (
id serial,
title varchar(255) NOT NULL default '',
url varchar(255) NOT NULL default '',
siteurl varchar(255) default NULL,
parent integer default '0',
descr varchar(255) default NULL,
dateadded timestamp default NULL,
icon varchar(255) default NULL,
position integer NOT NULL default '0',
mode integer NOT NULL default '1',
itemsincache text NULL,
daterefreshed timestamp default now(),
refreshinterval integer NOT NULL default '60',
etag varchar(255) NULL,
lastmodified varchar(255) default NULL,
PRIMARY KEY (id)
);
-- Table folders
-- ------------------------------
DROP TABLE folders CASCADE;
CREATE TABLE folders (
id serial,
name varchar(127) NOT NULL default '',
position integer NOT NULL default '0',
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX folders_name_u
ON folders (name);
INSERT INTO folders (id,name) VALUES ('0','');
-- *Sigh*
update folders set id=0 where id=1;
-- Table item
-- ------------------------------
DROP TABLE item CASCADE;
CREATE TABLE item (
id serial,
cid integer NOT NULL default '0',
added timestamp NOT NULL default 'January 1, 1',
title varchar(255) default NULL,
url varchar(255) default NULL,
enclosure varchar(255) default NULL,
description text,
unread integer default '1',
pubdate timestamp default NULL,
author varchar(255) default NULL,
md5sum varchar(32) NULL,
guid text NULL,
PRIMARY KEY (id)
);
CREATE INDEX item_url_i
ON item (url);
CREATE INDEX item_guid_i
ON item (guid);
CREATE INDEX item_cid_i
ON item (cid);
CREATE INDEX item_author_i
ON item (author);
-- Table config
-- ------------------------------
DROP TABLE config CASCADE;
CREATE TABLE config (
key_ varchar(127) NOT NULL default '',
value_ text NOT NULL,
default_ text NOT NULL,
type_ varchar(30) check (type_ in ('string','num','boolean','array','enum')) NOT NULL default 'string',
desc_ text,
export_ varchar(127),
PRIMARY KEY (key_)
);
-- Table tag
-- ------------------------------
DROP TABLE tag CASCADE;
CREATE TABLE tag (
id serial,
tag varchar(63) NOT NULL default '',
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX tag_tag_u
ON tag (tag);
-- Table metatag
-- ------------------------------
DROP TABLE metatag CASCADE;
CREATE TABLE metatag (
fid integer NOT NULL default '0',
tid integer NOT NULL default '0',
ttype varchar(30) check (ttype in ('item','folder','channel')) NOT NULL default 'item',
tdate timestamp NULL
);
CREATE INDEX metatag_fid_i
ON metatag (fid);
CREATE INDEX metatag_tid_i
ON metatag (tid);
CREATE INDEX metatag_ttype_i
ON metatag (ttype);
DROP TABLE rating CASCADE;
CREATE TABLE rating (
iid integer NOT NULL,
rating integer default '0'
);
DROP TABLE cache CASCADE;
CREATE TABLE cache (
cachekey VARCHAR( 128 ) NOT NULL ,
timestamp timestamp NOT NULL ,
cachetype varchar(4) check (cachetype in ( 'ts', 'icon', 'feed' )) NOT NULL default 'ts',
data bytea,
PRIMARY KEY ( cachekey )
);
INSERT INTO cache (cachekey,timestamp,cachetype,data) VALUES ('data_ts',now(),'ts',null);
CREATE TABLE users (
uid serial,
uname varchar(255) NOT NULL,
password varchar(255) NOT NULL,
ulevel integer NOT NULL default '1',
realname varchar(255) default NULL,
lastip varchar(255) default NULL,
userips text default '',
lastlogin timestamp NULL,
PRIMARY KEY (uid)
);
CREATE UNIQUE INDEX users_uname_u
ON users (uname);
INSERT INTO users (uname,password,ulevel,realname) VALUES ('admin','',99,'Administrator');
CREATE TABLE properties (
fk_ref_object_id text NOT NULL,
proptype varchar(20) check (proptype in ('item','feed','folder','category','plugin','tag','theme','misc')) NOT NULL default 'item',
property varchar(128) NOT NULL default '',
value text NOT NULL,
primary key (fk_ref_object_id,property,proptype)
);
CREATE TABLE dashboard (
id serial,
title text NOT NULL default '',
url text NOT NULL default '',
position integer NOT NULL default 0,
obj text not NULL default '',
daterefreshed timestamp default NULL,
itemcount integer NOT NULL default 3,
PRIMARY KEY (id)
);
CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(TIMESTAMP WITHOUT TIME ZONE)
RETURNS BIGINT
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(EPOCH FROM $1)::bigint;';
CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(TIMESTAMP WITH TIME ZONE)
RETURNS BIGINT
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(EPOCH FROM $1)::bigint;';
CREATE OR REPLACE FUNCTION FROM_UNIXTIME(INTEGER)
RETURNS TIMESTAMP WITH TIME ZONE
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT TIMESTAMP WITH TIME ZONE \'epoch\' + $1 * interval \'1 second\';';
CREATE OR REPLACE FUNCTION YEAR(TIMESTAMP WITHOUT TIME ZONE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(YEAR FROM $1)::integer;';
CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP WITHOUT TIME ZONE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(MONTH FROM $1)::integer;';
CREATE OR REPLACE FUNCTION DAYOFMONTH(TIMESTAMP WITHOUT TIME ZONE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(DAY FROM $1)::integer;';
CREATE OR REPLACE FUNCTION DATE_SUB(TIMESTAMP WITH TIME ZONE, INTERVAL)
RETURNS TIMESTAMP WITH TIME ZONE
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT $1-$2';
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.cachedir','/tmp/magpierss','/tmp/magpierss','string','Where should magpie store its temporary files? (Apache needs write permissions on this dir.)','MAGPIE_CACHE_DIR');
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.encoding','UTF-8','UTF-8','string','Output encoding for the PHP XML parser.','MAGPIE_OUTPUT_ENCODING');
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.itemsinchannelview','10','10','num','Number of read items shown on for a single channel.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.showfavicons','true','true','boolean','Display the favicon for the channels that have one. Due to a IE bug, some icons do not render correctly. You can either change the URL to the icon in the admin screen, or turn the display of favicons off globally here.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.usemodrewrite','true','true','boolean','Make use of apache''s mod_rewrite module to return sexy urls. Turn this off if your host doesn''t allow you to change this apache setting.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.dateformat','F jS, Y, g:ia T','F jS, Y, g:ia T','string','Format to use when displaying dates. See here for help on the format: http://ch.php.net/manual/en/function.date.php \n\nNote that direct access to a given feed\'s month and day archives more or less depends on the fact that this date format contains the \"F\" (Month) and \"jS\" (day) elements in this form. So feel free to change the order of the elements, but better leave those two tokens in :)',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.showdevloglink','false','false','boolean','Show a link to the gregarius devlog. This is mainly useful on the actual\n live gregarius site. You can safely set this to \'false\'\n if you don\'t want to display a link back.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.meta.debug','false','false','boolean',' When in debug mode some extra debug info is shown and the error \n reporting is a bit more verbose.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.compression','true','true','boolean','This variable turns output compression on and off. Output compression is handled by most browsers.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.channelcollapse','true','true','boolean','Allow collapsing of channels on the main page. ',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.usepermalinks','true','true','boolean','Display a permalink icon and allow linking a given item directly.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.markreadonupdate','false','false','boolean','Mark all old unread feeds as read when updating if new unread feeds\n are found.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.lang','en_US,zh_CN,de,da,es,fr,he,it,ja,pt_BR,pt,ru,sv,0','en_US,zh_CN,de,da,es,fr,he,it,ja,pt_BR,pt,ru,sv,0','enum','Language pack to use.', NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.lang.force','false','false','boolean','When false, Gregarius will negotiate the display language with the browser and will fall back to the language defined in rss.output.lang if the negotiation fails. When true, Gregarius won''t negotiate and will always use the language defined in rss.output.lang.',null);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.deadthreshhold', '24', '24', 'num', 'Sets the threshold for when a feed is marked as dead, in hours', NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.search.maxitems', 500, 500, 'num', 'Sets the maximum number of items returned on a search', NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.restrictrefresh', 'false','false','boolean','Restrict refresh to command line only (eg php -f update.php). Useful for busy sites with multiple users.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.absoluteordering','true','true','boolean','Allow ordering of channels and folders in the admin section. If false, channels and folders will be organized alphabetically by their titles.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.robotsmeta','index,follow','index,follow','string','How should spiders crawl us?\n (see http://www.robotstxt.org/wc/meta-user.html for more info).',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.serverpush','true','true','boolean','Use server push on update.php for a more user-friendly experience.\n This is only supported by Mozilla browsers (Netscape, Mozilla, Firefox,...)\n and Opera. These browsers will be autodetected.\n If you\'re not using one of these (you should) you can as well turn this off.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.refreshafter','45','45','num','If this option is set the feeds will be refreshed after x minutes of inactivity. Please respect the feed providers by not setting this value to anything lower than thirty minutes. \n\nSet this variable to 0 turn this option off.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.input.allowed','a:21:{s:1:\"a\";a:2:{s:4:\"href\";i:1;s:5:\"title\";i:1;}s:1:\"b\";a:0:{}s:10:\"blockquote\";a:0:{}s:2:\"br\";a:0:{}s:4:\"code\";a:0:{}s:3:\"del\";a:0:{}s:2:\"em\";a:0:{}s:1:\"i\";a:0:{}s:3:\"img\";a:2:{s:3:\"src\";i:1;s:3:\"alt\";i:1;}s:3:\"ins\";a:0:{}s:2:\"li\";a:0:{}s:2:\"ol\";a:0:{}s:1:\"p\";a:0:{}s:3:\"pre\";a:0:{}s:3:\"sup\";a:0:{}s:5:\"table\";a:0:{}s:2:\"td\";a:0:{}s:2:\"th\";a:0:{}s:2:\"tr\";a:0:{}s:2:\"tt\";a:0:{}s:2:\"ul\";a:0:{}}','a:21:{s:1:\"a\";a:2:{s:4:\"href\";i:1;s:5:\"title\";i:1;}s:1:\"b\";a:0:{}s:10:\"blockquote\";a:0:{}s:2:\"br\";a:0:{}s:4:\"code\";a:0:{}s:3:\"del\";a:0:{}s:2:\"em\";a:0:{}s:1:\"i\";a:0:{}s:3:\"img\";a:2:{s:3:\"src\";i:1;s:3:\"alt\";i:1;}s:3:\"ins\";a:0:{}s:2:\"li\";a:0:{}s:2:\"ol\";a:0:{}s:1:\"p\";a:0:{}s:3:\"pre\";a:0:{}s:3:\"sup\";a:0:{}s:5:\"table\";a:0:{}s:2:\"td\";a:0:{}s:2:\"th\";a:0:{}s:2:\"tr\";a:0:{}s:2:\"tt\";a:0:{}s:2:\"ul\";a:0:{}}','array','This variable controls input filtering. HTML tags and their attributes, which are not in this list, get filtered out when new RSS items are imported.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.showfeedmeta','false','false','boolean','Display meta-information (like a web- and rss/rdf/xml url) about each feed in the feed side-column.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.frontpage.mixeditems','true','true','boolean','Show read items along with unread items on the front page?',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.frontpage.numreaditems', -1 , -1, 'num','If there are no unread items then how many items to show on the front page. Set this to -1 if you want it to be the same as rss.output.frontpage.numitems',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.theme','default','default','string','The theme to use. Download more themes from the Gregarius Themes Repository.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.cachecontrol','false','false','boolean','If true, Gregarius will negotiate with the browser and check whether it should get a fresh document or not.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.plugins','a:2:{i:0;s:13:"urlfilter.php";i:1;s:18:"roundedcorners.php";}','a:2:{i:0;s:13:"urlfilter.php";i:1;s:18:"roundedcorners.php";}','array','Plugins are third-party scripts that offer extended functionalities. More plugins can be found at the Plugin Repository',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.input.allowupdates','true','true','boolean','Allow Gregarius to look for updates in existing items.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.titleunreadcnt','false','false','boolean','Display unread count in the document title.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.tzoffset','0','0','num','Timezone offset, in hours, between your local time and server time. Valid range: "-12" through "12"',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.timezone','0','0','num','Timezone offset, in hours, between your local time and server time. Valid range: "-12" through "12"',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.frontpage.numitems','100','100','num','Maximum number of items displayed on the main page. Set this variable to 0 to show no items on the main page.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.feedgrouping','false','false','boolean','When true, Gregarius groups unread items per feed and sorts the feeds according to the rss.config.absoluteordering configuration switch. When false, unread items are not grouped by feed, but are sorted by date instead.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.datedesc.unread', 'true','true','boolean','When true, Gregarius displays newer unread items first. If false, Gregarius will display older unread items first.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.datedesc.read', 'true','true','boolean','When true, Gregarius displays newer read items first. If false, Gregarius will display older read items first.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.autologout','false','false','boolean','When true, Gregarius will automatically remove the "admin cookie" when the browser window is closed, effectively logging you out.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.publictagging', 'false','false','boolean','When true, every visitor to your Gregarius site will be allowed to tag items, when false only the Administrator (you) is allowed to tag.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.rating', 'true','true','boolean','Enable the item rating system.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.title','Gregarius','Gregarius','string','Sets the title of this feedreader.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.ajaxparallelsize','3','3','num','Sets the number of feeds to update in parallel. Remember to set rss.config.serverpush to false.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.ajaxbatchsize','3','3','num','Sets the number of feeds in a batch when using the ajax updater. Remember to set rss.config.serverpush to false.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.config.defaultdashboard', '1', '1','boolean','If the first page seen when entering the admin section should be the dashboard',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.nav.unread', 'false', 'false','boolean','If the navigation hints on the feeds page should go to the next feed with unread items. If false, it simply goes to the next feed.',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.channelcollapsedefault', 'false','false','boolean','Collapse the channels on the main page by default',NULL);
INSERT INTO config (key_,value_,default_,type_,desc_,export_) VALUES ('rss.output.minimalchannellist', 'false','false','boolean','Exclude folders and channels without unread items in channel list?',NULL);
INSERT INTO dashboard (title, url, position, obj, daterefreshed, itemcount) VALUES ('Latest Gregarius News', 'http://devlog.gregarius.net/feed/?db=', '0', '', null, 3);
INSERT INTO dashboard (title, url, position, obj, daterefreshed, itemcount) VALUES ('Latest Plugins', 'http://plugins.gregarius.net/rss.php?db=', '1', '', null, 5);
INSERT INTO dashboard (title, url, position, obj, daterefreshed, itemcount) VALUES ('Latest Themes', 'http://themes.gregarius.net/rss.php?db=', '1', '', null, 5);
INSERT INTO dashboard (title, url, position, obj, daterefreshed, itemcount) VALUES ('Latest Forum posts', 'http://forums.gregarius.net/feeds/?Type=rss2&db=', '1', '', null, 5);