database schema documentation for item table

1 files changed, 96 insertions, 49 deletions

diff --git a/doc/database/db_item.bb b/doc/database/db_item.bb
index b31823d6c..e4e594196 100644
--- a/doc/database/db_item.bb
+++ b/doc/database/db_item.bb
@@ -1,104 +1,151 @@
 [table]
 [tr][th]Field[/th][th]Description[/th][th]Type[/th][th]Null[/th][th]Key[/th][th]Default[/th][th]Extra
 [/th][/tr]
-[tr][td]id[/td][td][/td][td]int(10) unsigned[/td][td]NO[/td][td]PRI[/td][td]NULL[/td][td]auto_increment
+[tr][td]id[/td][td]Sequential ID[/td][td]int(10) unsigned[/td][td]NO[/td][td]PRI[/td][td]NULL[/td][td]auto_increment
 [/td][/tr]
-[tr][td]mid[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]mid[/td][td]Message-id - globally unique, there can be several items with the same message-ID in the table as they may have different uid owners[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]aid[/td][td][/td][td]int(10) unsigned[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[tr][td]aid[/td][td]channel_account_id of the channel_id (uid) which owns this copy of the item[/td][td]int(10) unsigned[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
 [/td][/tr]
-[tr][td]uid[/td][td][/td][td]int(10) unsigned[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[tr][td]uid[/td][td]channel_id (uid) which owns this copy of the item[/td][td]int(10) unsigned[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
 [/td][/tr]
-[tr][td]parent[/td][td][/td][td]int(10) unsigned[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[tr][td]parent[/td][td]item.id of the parent to this item if it is a reply of some form; otherwise this must be set to the id of this item[/td][td]int(10) unsigned[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
 [/td][/tr]
-[tr][td]parent_mid[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]parent_mid[/td][td]Globally unique message-id of the parent to this item[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]thr_parent[/td][td][/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]thr_parent[/td][td]If the parent of this item is not the top-level item in the conversation, the message-id of the immediate parent; otherwise set to parent_mid[/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]created[/td][td][/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
+[tr][td]created[/td][td]Creation timestamp. If creation is more than ten minutes into the future, set item_delayed to 1; it will automatically be delivered by the poller once the created time has passed[/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
 [/td][/tr]
-[tr][td]edited[/td][td][/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
+[tr][td]edited[/td][td]Date of  last edit (default is created)[/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
 [/td][/tr]
-[tr][td]expires[/td][td][/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
+[tr][td]expires[/td][td]Date this item expires and will be removed[/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
 [/td][/tr]
-[tr][td]commented[/td][td][/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
+[tr][td]commented[/td][td]Date of last comment/reply to this item[/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
 [/td][/tr]
-[tr][td]received[/td][td][/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
+[tr][td]received[/td][Date the item was received at this sitetd][/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
 [/td][/tr]
-[tr][td]changed[/td][td][/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
+[tr][td]changed[/td][td]Date that something in the conversation changed, indicating clients should fetch the conversation again[/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
 [/td][/tr]
-[tr][td]comments_closed[/td][td][/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
+[tr][td]comments_closed[/td][td]Date after which no more comments will be accepted[/td][td]datetime[/td][td]NO[/td][td]MUL[/td][td]0000-00-00 00:00:00[/td][td]
 [/td][/tr]
-[tr][td]owner_xchan[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]owner_xchan[/td][td]xchan_hash of the owner of this conversation (this is who replies are sent to)[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]author_xchan[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]author_xchan[/td][td]xchan_hash of the author of this item[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]source_xchan[/td][td][/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]source_xchan[/td][td]xchan_hash of the external source of this item belongs to multiple delivery chains and comments need to be uplinked[/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]mimetype[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]mimetype[/td][td]mime type of the content body[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]title[/td][td][/td][td]text[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
+[tr][td]title[/td][td]item title[/td][td]text[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]body[/td][td][/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
+[tr][td]body[/td][td]item body content[/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]app[/td][td][/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]app[/td][td]application which generated this item[/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]lang[/td][td][/td][td]char(64)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]lang[/td][td]auto-detected language[/td][td]char(64)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]revision[/td][td][/td][td]int(10) unsigned[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[tr][td]revision[/td][td]future use, version control[/td][td]int(10) unsigned[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
 [/td][/tr]
-[tr][td]verb[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]verb[/td][td]ActivityStreams verb (old style URI)[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]obj_type[/td][td][/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]obj_type[/td][td]ActivityStreams object type (old style URI)[/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]object[/td][td][/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
+[tr][td]object[/td][td]JSON encoded object structure unless it is an implied object (normal post)[/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]tgt_type[/td][td][/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]tgt_type[/td][td]ActivityStreams target type if applicable (URI)[/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]target[/td][td][/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
+[tr][td]target[/td][td]JSON encoded target structure if used[/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]layout_mid[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]layout_mid[/td][td]For webpages, which layout (mid or message_id) to use when displaying this page[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]postopts[/td][td][/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
+[tr][td]postopts[/td][td]External post connectors add their network name to this comma-separated string to identify that they should be delivered to these networks during delivery[/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]route[/td][td][/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
+[tr][td]route[/td][td]comma separated xchan list of xchans where this message was routed on its way to this destination, used for route loop discovery and rejection of comments which arrived by alternate routes and may have different permissions[/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]llink[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]llink[/td][td]URL of a displayable copy of this post/conversation on this site[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]plink[/td][td][/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]plink[/td][td]permalink or URL toa displayable copy  of the message at its source[/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]resource_id[/td][td][/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]resource_id[/td][td]Used to link other tables to items, it identifies the linked resource and if set must also set resource_type[/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]resource_type[/td][td][/td][td]char(16)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]resource_type[/td][td]default none, if a linked resource this should be the name of the resource type such as "photo" or "event"[/td][td]char(16)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]attach[/td][td][/td][td]mediumtext[/td][td]NO[/td][td][/td][td]NULL[/td][td]
+[tr][td]attach[/td][td]JSON structure representing attachments to this item[/td][td]mediumtext[/td][td]NO[/td][td][/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]sig[/td][td][/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
+[tr][td]sig[/td][td]RSA signature of the item body by the original author if the private key is available[/td][td]text[/td][td]NO[/td][td][/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]diaspora_meta[/td][td][/td][td]mediumtext[/td][td]NO[/td][td][/td][td]NULL[/td][td]
+[tr][td]diaspora_meta[/td][td]Used to store Diaspora comment signatures with their weird requirements[/td][td]mediumtext[/td][td]NO[/td][td][/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]location[/td][td][/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]location[/td][td]text location where this item originated[/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]coord[/td][td][/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
+[tr][td]coord[/td][td] longitude/latitude pair representing location where this item originated[/td][td]char(255)[/td][td]NO[/td][td][/td][td][/td][td]
 [/td][/tr]
-[tr][td]public_policy[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]public_policy[/td][td]If the author has specified restrictions (this network, this site) etc. for distribution, the corresponding policy text is present here and item_private = 1[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]comment_policy[/td][td][/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
+[tr][td]comment_policy[/td][td]If the author has specified comment restrictions (thei network, this site, etc.) the corresponding policy text is present here[/td][td]char(255)[/td][td]NO[/td][td]MUL[/td][td][/td][td]
 [/td][/tr]
-[tr][td]allow_cid[/td][td][/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
+[tr][td]allow_cid[/td][td]Access Control - list of allowed xchans '<xchan1><xchan2>...'[/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]allow_gid[/td][td][/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
+[tr][td]allow_gid[/td]Access Control - list of allowed group hashes, see allow_cid[td][/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]deny_cid[/td][td][/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
+[tr][td]deny_cid[/td][td]Access Control - list of denied xchans[/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]deny_gid[/td][td][/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
+[tr][td]deny_gid[/td][td]Access Control - list of denied groups[/td][td]mediumtext[/td][td]NO[/td][td]MUL[/td][td]NULL[/td][td]
 [/td][/tr]
-[tr][td]item_restrict[/td][td][/td][td]int(11)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[tr][td]item_restrict[/td]no longer used[td][/td][td]int(11)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
 [/td][/tr]
-[tr][td]item_flags[/td][td][/td][td]int(11)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[tr][td]item_flags[/td][td]no longer used[/td][td]int(11)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
 [/td][/tr]
-[tr][td]item_private[/td][td][/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[tr][td]item_private[/td][td]distribution is restricted[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
 [/td][/tr]
+[tr][td]item_origin[/td][td]item originated at this site[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_unseen[/td][td]item has not been seen[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_starred[/td][td]item has been favourited[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_uplink[/td][td]This item is part of a multiple delivery chain and must be uplinked to the original sender (source_xchan)[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_consensus[/td][td]This item allows voting tools[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_wall[/td][td]This item was posted to the wall of uid[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_thread_top[/td][td]parent = id, this is the top post in a conversation [/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_notshown[/td][td][/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_nsfw[/td][td][/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_relay[/td][td][/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_mentionsme[/td][td]The owner of this item was mentioned in it[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_nocomment[/td][td]if 1, no comments are allowed[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_obscured[/td][td]no longer used[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_verified[/td][td]the signature has been verified on this site[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_retained[/td][td][/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_rss[/td][td]item originated in a feed[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_deleted[/td][td]item has been deleted[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_type[/td][td]used to identify webpage and design element types, 0 is a normal conversation item[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_hidden[/td][td]0 or 1 if item is not to be displayed[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_unpublished[/td][td][/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_delayed[/td][td]item is posted in the future[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_pending_remove[/td][td]item is in the process of being removed[/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+[tr][td]item_blocked[/td][td][/td][td]tinyint(4)[/td][td]NO[/td][td]MUL[/td][td]0[/td][td]
+[/td][/tr]
+
 [/table]
 
 Return to [zrl=[baseurl]/help/database]database documentation[/zrl]
\ No newline at end of file