DROP TABLE affix;
DROP TABLE spell;
DROP table "url";
DROP table "dict";
DROP table "robots";
DROP table "stopword";
DROP SEQUENCE next_url_id;


CREATE SEQUENCE next_url_id;

CREATE TABLE "url" (
	"rec_id" int4 DEFAULT nextval('next_url_id') PRIMARY KEY,
	"status" int4 NOT NULL DEFAULT 0,
	"url" character varying(128) NOT NULL,
	"content_type" character varying(32) NOT NULL DEFAULT '',
	"last_modified" character varying(32) NOT NULL DEFAULT '',
	"title" character varying(128) NOT NULL DEFAULT '',
	"text" character varying(255) NOT NULL DEFAULT '',
	"size" int4 NOT NULL DEFAULT 0,
	"indexed" int4 NOT NULL DEFAULT 0,
	"last_index_time" datetime NOT NULL DEFAULT 'Thu Dec 31 20:00:00 1970 GMT',
	"next_index_time" datetime NOT NULL DEFAULT 'Thu Dec 31 20:00:00 1970 GMT',
	"referrer" int4 NOT NULL DEFAULT 0,
	"tag" int4 NOT NULL DEFAULT 0,
	"hops" int4 NOT NULL DEFAULT 0,
	"keywords" character varying(255) NOT NULL DEFAULT '',
	"description" character varying(100) NOT NULL DEFAULT '',
	"crc" character varying(33) NOT NULL DEFAULT '');

CREATE TABLE "dict" (
	"url_id" int4 NOT NULL,
	"word" character varying(32) NOT NULL,
	"intag" int4 NOT NULL);

CREATE TABLE "robots" (
	"hostinfo" character varying(127) NOT NULL,
	"path" character varying(127) NOT NULL);

CREATE TABLE affix (
  flag char(1) DEFAULT '' NOT NULL,
  type char(1) DEFAULT '' NOT NULL,
  lang char(3) DEFAULT '' NOT NULL,
  mask char(32) DEFAULT '' NOT NULL,
  find char(32) DEFAULT '' NOT NULL,
  repl char(32) DEFAULT '' NOT NULL
);


CREATE TABLE spell (
  word varchar(64) DEFAULT '' NOT NULL,
  flag varchar(32) DEFAULT '' NOT NULL,
  lang char(3) DEFAULT '' NOT NULL
);

create table stopword (
 word text not null primary key
);


CREATE  INDEX "word_url" on "dict" using btree ( "word" "varchar_ops" );
CREATE UNIQUE INDEX "url_url" on "url" using btree ( "url" "varchar_ops" );
CREATE  INDEX "url_crc" on "url" using btree ( "crc" "varchar_ops" );

CREATE UNIQUE INDEX aff_idx ON affix (flag,lang,type,mask,find,repl);
CREATE UNIQUE INDEX spell_word ON spell (word,flag,lang);
