schemas.pm

Insipid 是一款基于Web书签仓库。很方面的记录下各种输入输出信息。

#!/usr/bin/perl -w
#
# Copyright (C) 2005 Luke Reeves
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307
# USA
#

package Insipid::Schemas;

use strict;
use vars qw(@ISA @EXPORT @EXPORT_OK);
require Exporter;

@ISA = qw(Exporter);

@EXPORT = qw(
$createMySQL
$createPostgres
);

@EXPORT_OK = qw();

our $createPostgres = <<CPOSTGRES;
CREATE TABLE sessions (
	id CHAR(32) NOT NULL UNIQUE,
	a_session TEXT NOT NULL
);

CREATE TABLE bookmarks (
	id SERIAL,
	url TEXT NOT NULL,
	md5 CHAR(32) NOT NULL UNIQUE,
	date INT NOT NULL DEFAULT 0,
	title VARCHAR(255) NOT NULL,
	description TEXT NOT NULL,
	access_level INT NOT NULL DEFAULT 0,
	PRIMARY KEY(id)
);


CREATE TABLE tags (
	id SERIAL,
	name VARCHAR(255) NOT NULL UNIQUE,
	PRIMARY KEY(id),
	UNIQUE(name)
);

CREATE TABLE bookmark_tags (
	bookmark_id SERIAL,
	tag_id INT NOT NULL,
	PRIMARY KEY(bookmark_id, tag_id)
);

CREATE TABLE options (
        name VARCHAR(255) NOT NULL UNIQUE,
        description TEXT NOT NULL,
        value TEXT NOT NULL,
        PRIMARY KEY(name)
);

CREATE TABLE pagecache (
        md5 CHAR(32) NOT NULL DEFAULT '',
        url TEXT NOT NULL DEFAULT '',
        content_type VARCHAR(50),
        content_length INT NOT NULL DEFAULT 0,
        content bytea,
        date INT NOT NULL DEFAULT 0,
        PRIMARY KEY(md5)
);

CREATE TABLE pagecache_references (
        md5_parent CHAR(32) NOT NULL DEFAULT '',
        md5_child CHAR(32) NOT NULL DEFAULT '',
        PRIMARY KEY(md5_parent, md5_child)
);

INSERT INTO options VALUES (
  'feed_name',
  'The title of your feed (e.g. My Bookmarks)',
  'Bookmarks'
);

INSERT INTO options VALUES (
  'site_name',
  'The title of the main page (e.g. My Bookmarks)',
  'My Bookmarks'
);


INSERT INTO options VALUES (
  'public_searches',
  'Allow public searches - when set to \'yes\', any visitor can search your bookmarks.',
  'no'
);

INSERT INTO options VALUES(
  'version',
  'Internal Insipid version number',
  '0.9.15'
);
CPOSTGRES



our $createMySQL = <<CMYSQL;
CREATE TABLE IF NOT EXISTS sessions (
	id CHAR(32) NOT NULL UNIQUE,
	a_session TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS bookmarks (
	id INT AUTO_INCREMENT NOT NULL,
	url TEXT NOT NULL DEFAULT '',
	md5 CHAR(32) NOT NULL DEFAULT '' UNIQUE,
	date INT NOT NULL DEFAULT 0,
	title VARCHAR(255) NOT NULL DEFAULT '',
	description TEXT NOT NULL DEFAULT '',
	access_level INT NOT NULL DEFAULT 0,
	PRIMARY KEY(id)
);


CREATE TABLE IF NOT EXISTS tags (
	id INT AUTO_INCREMENT NOT NULL,
	name VARCHAR(255) NOT NULL DEFAULT '' UNIQUE,
	PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS bookmark_tags (
	bookmark_id INT NOT NULL,
	tag_id INT NOT NULL,
	PRIMARY KEY(bookmark_id, tag_id),
	INDEX(bookmark_id),
	INDEX(tag_id)
);


CREATE TABLE IF NOT EXISTS options (
        name VARCHAR(255) NOT NULL UNIQUE,
        description TEXT NOT NULL DEFAULT '',
        value TEXT NOT NULL DEFAULT '',
        PRIMARY KEY(name)
);

CREATE TABLE IF NOT EXISTS pagecache (
        md5 CHAR(32) NOT NULL DEFAULT '',
        url TEXT NOT NULL DEFAULT '',
        content_type VARCHAR(50),
        content_length INT NOT NULL DEFAULT 0,
        content LONGBLOB,
        date INT NOT NULL DEFAULT 0,
        PRIMARY KEY(md5)
);

CREATE TABLE IF NOT EXISTS pagecache_references (
        md5_parent CHAR(32) NOT NULL DEFAULT '',
        md5_child CHAR(32) NOT NULL DEFAULT '',
        PRIMARY KEY(md5_parent, md5_child)
);

INSERT IGNORE INTO options VALUES(
  'feed_name',
  'The title of your feed (e.g. My Bookmarks)',
  'Bookmarks'
);

INSERT IGNORE INTO options VALUES(
  'site_name',
  'The title of the main page (e.g. My Bookmarks)',
  'My Bookmarks'
);

INSERT IGNORE INTO options VALUES(
  'public_searches',
  'Allow public searches - when set to yes, any visitor can search your bookmarks.',
  'no'
);

INSERT INTO options VALUES(
  'proxy_host',
  'The proxy server (if any) to use when making page snapshots.',
  ''
);


INSERT INTO options VALUES(
  'proxy_port',
  'Your proxy port number.',
  '3128'
);

INSERT INTO options VALUES(
  'version',
  'Internal Insipid version number',
  '0.9.15'
);
CMYSQL

1;
__END__