aboutsummaryrefslogtreecommitdiffstats
path: root/install/schema_postgres.sql
diff options
context:
space:
mode:
Diffstat (limited to 'install/schema_postgres.sql')
-rw-r--r--install/schema_postgres.sql164
1 files changed, 161 insertions, 3 deletions
diff --git a/install/schema_postgres.sql b/install/schema_postgres.sql
index 1f9ce753f..70a7a576a 100644
--- a/install/schema_postgres.sql
+++ b/install/schema_postgres.sql
@@ -6,13 +6,19 @@ CREATE TABLE "abook" (
"abook_my_perms" bigint NOT NULL DEFAULT '0',
"abook_their_perms" bigint NOT NULL DEFAULT '0',
"abook_closeness" numeric(3) NOT NULL DEFAULT '99',
- "abook_rating" bigint NOT NULL DEFAULT '0',
- "abook_rating_text" TEXT NOT NULL DEFAULT '',
"abook_created" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
"abook_updated" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
"abook_connected" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
"abook_dob" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
"abook_flags" bigint NOT NULL DEFAULT '0',
+ "abook_blocked" smallint NOT NULL DEFAULT '0',
+ "abook_ignored" smallint NOT NULL DEFAULT '0',
+ "abook_hidden" smallint NOT NULL DEFAULT '0',
+ "abook_archived" smallint NOT NULL DEFAULT '0',
+ "abook_pending" smallint NOT NULL DEFAULT '0',
+ "abook_unconnected" smallint NOT NULL DEFAULT '0',
+ "abook_self" smallint NOT NULL DEFAULT '0',
+ "abook_feed" smallint NOT NULL DEFAULT '0',
"abook_profile" char(64) NOT NULL DEFAULT '',
"abook_incl" TEXT NOT NULL DEFAULT '',
"abook_excl" TEXT NOT NULL DEFAULT '',
@@ -27,6 +33,14 @@ CREATE TABLE "abook" (
create index "abook_created" on abook ("abook_created");
create index "abook_updated" on abook ("abook_updated");
create index "abook_flags" on abook ("abook_flags");
+ create index "abook_blocked" on abook ("abook_blocked");
+ create index "abook_ignored" on abook ("abook_ignored");
+ create index "abook_hidden" on abook ("abook_hidden");
+ create index "abook_archived" on abook ("abook_archived");
+ create index "abook_pending" on abook ("abook_pending");
+ create index "abook_unconnected" on abook ("abook_unconnected");
+ create index "abook_self" on abook ("abook_self");
+ create index "abook_feed" on abook ("abook_feed");
create index "abook_profile" on abook ("abook_profile");
create index "abook_dob" on abook ("abook_dob");
create index "abook_connected" on abook ("abook_connected");
@@ -93,6 +107,8 @@ CREATE TABLE "app" (
"app_price" text NOT NULL DEFAULT '',
"app_page" text NOT NULL DEFAULT '',
"app_requires" text NOT NULL DEFAULT '',
+ "app_created" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
+ "app_edited" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
PRIMARY KEY ("id")
);
create index "app_id" on app ("app_id");
@@ -102,6 +118,8 @@ create index "app_photo" on app ("app_photo");
create index "app_version" on app ("app_version");
create index "app_channel" on app ("app_channel");
create index "app_price" on app ("app_price");
+create index "app_created" on app ("app_created");
+create index "app_edited" on app ("app_edited");
CREATE TABLE "attach" (
"id" serial NOT NULL,
"aid" bigint NOT NULL DEFAULT '0',
@@ -114,6 +132,11 @@ CREATE TABLE "attach" (
"revision" bigint NOT NULL DEFAULT '0',
"folder" varchar(64) NOT NULL DEFAULT '',
"flags" bigint NOT NULL DEFAULT '0',
+ "is_dir" smallint NOT NULL DEFAULT '0',
+ "is_photo" smallint NOT NULL DEFAULT '0',
+ "os_storage" smallint NOT NULL DEFAULT '0',
+ "os_path" text NOT NULL,
+ "display_path" text NOT NULL,
"data" bytea NOT NULL,
"created" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
"edited" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
@@ -135,6 +158,9 @@ create index "attach_edited_idx" on attach ("edited");
create index "attach_revision_idx" on attach ("revision");
create index "attach_folder_idx" on attach ("folder");
create index "attach_flags_idx" on attach ("flags");
+create index "attach_is_dir_idx" on attach ("is_dir");
+create index "attach_is_photo_idx" on attach ("is_photo");
+create index "attach_os_storage_idx" on attach ("os_storage");
create index "attach_creator_idx" on attach ("creator");
CREATE TABLE "auth_codes" (
"id" varchar(40) NOT NULL,
@@ -197,6 +223,8 @@ CREATE TABLE "channel" (
"channel_w_pages" bigint NOT NULL DEFAULT '128',
"channel_a_republish" bigint NOT NULL DEFAULT '128',
"channel_w_like" bigint NOT NULL DEFAULT '128',
+ "channel_removed" smallint NOT NULL DEFAULT '0',
+ "channel_system" smallint NOT NULL DEFAULT '0',
PRIMARY KEY ("channel_id"),
UNIQUE ("channel_address")
);
@@ -235,6 +263,8 @@ create index "channel_a_republish" on channel ("channel_a_republish");
create index "channel_w_like" on channel ("channel_w_like");
create index "channel_dirdate" on channel ("channel_dirdate");
create index "channel_lastpost" on channel ("channel_lastpost");
+create index "channel_removed" on channel ("channel_removed");
+create index "channel_system" on channel ("channel_system");
CREATE TABLE "chat" (
"chat_id" serial NOT NULL,
"chat_room" bigint NOT NULL DEFAULT '0',
@@ -311,6 +341,30 @@ CREATE TABLE "conv" (
create index "conv_created_idx" on conv ("created");
create index "conv_updated_idx" on conv ("updated");
+CREATE TABLE IF NOT EXISTS "dreport" (
+ "dreport_id" int(11) NOT NULL,
+ "dreport_channel" int(11) NOT NULL DEFAULT '0',
+ "dreport_mid" char(255) NOT NULL DEFAULT '',
+ "dreport_site" char(255) NOT NULL DEFAULT '',
+ "dreport_recip" char(255) NOT NULL DEFAULT '',
+ "dreport_result" char(255) NOT NULL DEFAULT '',
+ "dreport_time" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
+ "dreport_xchan" char(255) NOT NULL DEFAULT '',
+ "dreport_queue" char(255) NOT NULL DEFAULT '',
+ PRIMARY KEY ("dreport_id")
+);
+
+create index "dreport_mid" on dreport ("dreport_mid");
+create index "dreport_site" on dreport ("dreport_site");
+create index "dreport_time" on dreport ("dreport_time");
+create index "dreport_xchan" on dreport ("dreport_xchan");
+create index "dreport_queue" on dreport ("dreport_queue");
+create index "dreport_channel" on dreport ("dreport_channel");
+
+
+
+
+
CREATE TABLE "event" (
"id" serial NOT NULL,
"aid" bigint NOT NULL DEFAULT '0',
@@ -338,6 +392,7 @@ CREATE TABLE "event" (
"event_repeat" text NOT NULL,
"event_sequence" smallint NOT NULL DEFAULT '0',
"event_priority" smallint NOT NULL DEFAULT '0',
+ "event_vdata" text NOT NULL,
PRIMARY KEY ("id")
);
create index "event_uid_idx" on event ("uid");
@@ -463,6 +518,10 @@ CREATE TABLE "hubloc" (
"hubloc_sitekey" text NOT NULL DEFAULT '',
"hubloc_updated" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
"hubloc_connected" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
+ "hubloc_primary" smallint NOT NULL DEFAULT '0',
+ "hubloc_orphancheck" smallint NOT NULL DEFAULT '0',
+ "hubloc_error" smallint NOT NULL DEFAULT '0',
+ "hubloc_deleted" smallint NOT NULL DEFAULT '0',
PRIMARY KEY ("hubloc_id")
);
create index "hubloc_url" on hubloc ("hubloc_url");
@@ -475,6 +534,10 @@ create index "hubloc_network" on hubloc ("hubloc_network");
create index "hubloc_updated" on hubloc ("hubloc_updated");
create index "hubloc_connected" on hubloc ("hubloc_connected");
create index "hubloc_status" on hubloc ("hubloc_status");
+create index "hubloc_primary" on hubloc ("hubloc_primary");
+create index "hubloc_orphancheck" on hubloc ("hubloc_orphancheck");
+create index "hubloc_error" on hubloc ("hubloc_error");
+create index "hubloc_deleted" on hubloc ("hubloc_deleted");
CREATE TABLE "issue" (
"issue_id" serial NOT NULL,
"issue_created" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
@@ -513,6 +576,7 @@ CREATE TABLE "item" (
"mimetype" text NOT NULL DEFAULT '',
"title" text NOT NULL,
"body" text NOT NULL,
+ "html" text NOT NULL,
"app" text NOT NULL DEFAULT '',
"lang" varchar(64) NOT NULL DEFAULT '',
"revision" bigint NOT NULL DEFAULT '0',
@@ -543,6 +607,28 @@ CREATE TABLE "item" (
"item_flags" bigint NOT NULL DEFAULT '0',
"item_private" numeric(4) NOT NULL DEFAULT '0',
"item_unseen" smallint NOT NULL DEFAULT '0',
+ "item_wall" smallint NOT NULL DEFAULT '0',
+ "item_origin" smallint NOT NULL DEFAULT '0',
+ "item_starred" smallint NOT NULL DEFAULT '0',
+ "item_uplink" smallint NOT NULL DEFAULT '0',
+ "item_consensus" smallint NOT NULL DEFAULT '0',
+ "item_thread_top" smallint NOT NULL DEFAULT '0',
+ "item_notshown" smallint NOT NULL DEFAULT '0',
+ "item_nsfw" smallint NOT NULL DEFAULT '0',
+ "item_relay" smallint NOT NULL DEFAULT '0',
+ "item_mentionsme" smallint NOT NULL DEFAULT '0',
+ "item_nocomment" smallint NOT NULL DEFAULT '0',
+ "item_obscured" smallint NOT NULL DEFAULT '0',
+ "item_verified" smallint NOT NULL DEFAULT '0',
+ "item_retained" smallint NOT NULL DEFAULT '0',
+ "item_rss" smallint NOT NULL DEFAULT '0',
+ "item_deleted" smallint NOT NULL DEFAULT '0',
+ "item_type" int(11) NOT NULL DEFAULT '0',
+ "item_hidden" smallint NOT NULL DEFAULT '0',
+ "item_unpublished" smallint NOT NULL DEFAULT '0',
+ "item_delayed" smallint NOT NULL DEFAULT '0',
+ "item_pending_remove" smallint NOT NULL DEFAULT '0',
+ "item_blocked" smallint NOT NULL DEFAULT '0',
"item_search_vector" tsvector,
PRIMARY KEY ("id")
);
@@ -575,7 +661,29 @@ create index "item_public_policy" on item ("public_policy");
create index "item_comment_policy" on item ("comment_policy");
create index "item_layout_mid" on item ("layout_mid");
create index "item_unseen" on item ("item_unseen");
+create index "item_wall" on item ("item_wall");
+create index "item_origin" on item ("item_origin");
+create index "item_starred" on item ("item_starred");
+create index "item_uplink" on item ("item_uplink");
+create index "item_consensus" on item ("item_consensus");
+create index "item_thread_top" on item ("item_thread_top");
+create index "item_notshown" on item ("item_notshown");
+create index "item_nsfw" on item ("item_nsfw");
+create index "item_relay" on item ("item_relay");
+create index "item_mentionsme" on item ("item_mentionsme");
+create index "item_nocomment" on item ("item_nocomment");
+create index "item_obscured" on item ("item_obscured");
+create index "item_verified" on item ("item_verified");
+create index "item_retained" on item ("item_retained");
+create index "item_rss" on item ("item_rss");
+create index "item_deleted" on item ("item_deleted");
+create index "item_type" on item ("item_type");
+create index "item_hidden" on item ("item_hidden");
+create index "item_unpublished" on item ("item_unpublished");
+create index "item_delayed" on item ("item_delayed");
+create index "item_pending_remove" on item ("item_pending_remove");
+create index "item_blocked" on item ("item_blocked");
-- fulltext indexes
create index "item_search_idx" on item USING gist("item_search_vector");
create index "item_allow_cid" on item ("allow_cid");
@@ -620,21 +728,30 @@ create index "likes_target_id" on likes ("target_id");
CREATE TABLE "mail" (
"id" serial NOT NULL,
"convid" bigint NOT NULL DEFAULT '0',
+ "conv_guid" text NOT NULL,
"mail_flags" bigint NOT NULL DEFAULT '0',
"from_xchan" text NOT NULL DEFAULT '',
"to_xchan" text NOT NULL DEFAULT '',
"account_id" bigint NOT NULL DEFAULT '0',
- "channel_id" bigint NOT NULL,
+ "channel_id" bigint NOT NULL DEFAULT '0',
"title" text NOT NULL,
"body" text NOT NULL,
+ "sig" text NOT NULL,
"attach" text NOT NULL DEFAULT '',
"mid" text NOT NULL,
"parent_mid" text NOT NULL,
+ "mail_deleted" smallint NOT NULL DEFAULT '0',
+ "mail_replied" smallint NOT NULL DEFAULT '0',
+ "mail_isreply" smallint NOT NULL DEFAULT '0',
+ "mail_seen" smallint NOT NULL DEFAULT '0',
+ "mail_recalled" smallint NOT NULL DEFAULT '0',
+ "mail_obscured" smallint NOT NULL DEFAULT '0',
"created" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
"expires" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
PRIMARY KEY ("id")
);
create index "mail_convid" on mail ("convid");
+create index "mail_conv_guid" on mail ("conv_guid");
create index "mail_created" on mail ("created");
create index "mail_flags" on mail ("mail_flags");
create index "mail_account_id" on mail ("account_id");
@@ -644,6 +761,12 @@ create index "mail_to_xchan" on mail ("to_xchan");
create index "mail_mid" on mail ("mid");
create index "mail_parent_mid" on mail ("parent_mid");
create index "mail_expires" on mail ("expires");
+create index "mail_deleted" on mail ("mail_deleted");
+create index "mail_replied" on mail ("mail_replied");
+create index "mail_isreply" on mail ("mail_isreply");
+create index "mail_seen" on mail ("mail_seen");
+create index "mail_recalled" on mail ("mail_recalled");
+create index "mail_obscured" on mail ("mail_obscured");
CREATE TABLE "manage" (
"id" serial NOT NULL,
"uid" bigint NOT NULL,
@@ -720,6 +843,11 @@ CREATE TABLE "obj" (
"obj_type" bigint NOT NULL DEFAULT '0',
"obj_obj" text NOT NULL DEFAULT '',
"obj_channel" bigint NOT NULL DEFAULT '0',
+ "obj_term" char(255) NOT NULL DEFAULT '',
+ "obj_url" char(255) NOT NULL DEFAULT '',
+ "obj_imgurl" char(255) NOT NULL DEFAULT '',
+ "obj_created" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
+ "obj_edited" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
"allow_cid" text NOT NULL,
"allow_gid" text NOT NULL,
"deny_cid" text NOT NULL,
@@ -732,6 +860,11 @@ create index "obj_page" on obj ("obj_page");
create index "obj_type" on obj ("obj_type");
create index "obj_channel" on obj ("obj_channel");
create index "obj_obj" on obj ("obj_obj");
+create index "obj_term" on obj ("obj_term");
+create index "obj_url" on obj ("obj_url");
+create index "obj_imgurl" on obj ("obj_imgurl");
+create index "obj_created" on obj ("obj_created");
+create index "obj_edited" on obj ("obj_edited");
CREATE TABLE "outq" (
"outq_hash" text NOT NULL,
@@ -785,6 +918,11 @@ CREATE TABLE "photo" (
"data" bytea NOT NULL,
"scale" numeric(3) NOT NULL,
"profile" numeric(1) NOT NULL DEFAULT '0',
+ "photo_usage" smallint(6) NOT NULL DEFAULT '0',
+ "is_nsfw" smallint NOT NULL DEFAULT '0',
+ "os_storage" smallint NOT NULL DEFAULT '0',
+ "os_path" mediumtext NOT NULL,
+ "display_path" mediumtext NOT NULL,
"photo_flags" bigint NOT NULL DEFAULT '0',
"allow_cid" text NOT NULL,
"allow_gid" text NOT NULL,
@@ -802,6 +940,9 @@ create index "photo_aid" on photo ("aid");
create index "photo_xchan" on photo ("xchan");
create index "photo_size" on photo ("size");
create index "photo_resource_id" on photo ("resource_id");
+create index "photo_usage" on photo ("photo_usage");
+create index "photo_is_nsfw" on photo ("is_nsfw");
+create index "photo_os_storage" on photo ("os_storage");
CREATE TABLE "poll" (
"poll_id" serial NOT NULL,
@@ -983,6 +1124,7 @@ CREATE TABLE "site" (
"site_realm" text NOT NULL DEFAULT '',
"site_valid" smallint NOT NULL DEFAULT '0',
"site_dead" smallint NOT NULL DEFAULT '0',
+ "site_type" smallint NOT NULL DEFAULT '0',
"site_project" text NOT NULL DEFAULT '',
PRIMARY KEY ("site_url")
);
@@ -995,6 +1137,7 @@ create index "site_sellpage" on site ("site_sellpage");
create index "site_realm" on site ("site_realm");
create index "site_valid" on site ("site_valid");
create index "site_dead" on site ("site_dead");
+create index "site_type" on site ("site_type");
create index "site_project" on site ("site_project");
CREATE TABLE "source" (
@@ -1126,6 +1269,13 @@ CREATE TABLE "xchan" (
"xchan_flags" bigint NOT NULL DEFAULT '0',
"xchan_photo_date" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
"xchan_name_date" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
+ "xchan_hidden" smallint NOT NULL DEFAULT '0',
+ "xchan_orphan" smallint NOT NULL DEFAULT '0',
+ "xchan_censored" smallint NOT NULL DEFAULT '0',
+ "xchan_selfcensored" smallint NOT NULL DEFAULT '0',
+ "xchan_system" smallint NOT NULL DEFAULT '0',
+ "xchan_pubforum" smallint NOT NULL DEFAULT '0',
+ "xchan_deleted" smallint NOT NULL DEFAULT '0',
PRIMARY KEY ("xchan_hash")
);
create index "xchan_guid" on xchan ("xchan_guid");
@@ -1137,6 +1287,14 @@ create index "xchan_flags" on xchan ("xchan_flags");
create index "xchan_connurl" on xchan ("xchan_connurl");
create index "xchan_instance_url" on xchan ("xchan_instance_url");
create index "xchan_follow" on xchan ("xchan_follow");
+create index "xchan_hidden" on xchan ("xchan_hidden");
+create index "xchan_orphan" on xchan ("xchan_orphan");
+create index "xchan_censored" on xchan ("xchan_censored");
+create index "xchan_selfcensored" on xchan ("xchan_selfcensored");
+create index "xchan_system" on xchan ("xchan_system");
+create index "xchan_pubforum" on xchan ("xchan_pubforum");
+create index "xchan_deleted" on xchan ("xchan_deleted");
+
CREATE TABLE "xchat" (
"xchat_id" serial NOT NULL,
"xchat_url" text NOT NULL DEFAULT '',