diff options
Diffstat (limited to 'install/schema_postgres.sql')
| -rw-r--r-- | install/schema_postgres.sql | 164 |
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 '', |