Difference between revisions of "User:Woozle/PostFerret/sql/post"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(adapted from InstaGov on htyp)
 
(decided that this should point to other Atoms rather than directly to Text)
 
(9 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
==About==
 
==About==
−
* '''Purpose''': a trackable piece of text (can be modified, has edit history, etc.)
+
* '''Purpose''': content type -- has a title and content; optionally, an owner and summary
 +
* '''Fields''':
 +
** '''ID_Owner''': ID of user who owns this post (NULL means ownership is not relevant)
 +
*** Some post types will not have owners, but it's a common enough thing that I thought it should go in at a low level.
 +
** '''ID_Title''': ID of {{l/same|atom}} for title for page headers and narrower listings
 +
** '''ID_Summary''': ID of {{l/same|atom}} for one-line description for wide listings, intro paragraph for blog posts, etc.
 +
** '''ID_Body''': ID of {{l/same|atom}} for main content
 
* '''History''':
 
* '''History''':
 
** '''2013-03-12''' Created, but not yet in use.
 
** '''2013-03-12''' Created, but not yet in use.
−
* '''Fields''':
+
** '''2013-03-15''' Most fields moved from {{l/same|post}} to {{l/same|atom}}; added ID_Owner.
−
** '''URI''': I haven't yet decided if this really belongs here, but it could be used for either:
+
** '''2013-06-28'''
−
*** - a page on the site which is set aside for more information about the given post
+
*** Decided to remove '''URI''' until there's a clear purpose and need for it.
−
*** - the post's official URI on the site
+
*** Removed owner/summary uniqueness requirement; can't see need for this either (e.g. multiple posts might be called "status report", for example).
 +
*** Added '''Body''' field to make this independent of the containing [[content management system|CMS]].
 +
** '''2013-07-21''' ID_Owner needs to be DEFAULT NULL (not NOT NULL) if it's going to be optional. Duh.
 +
** '''2015-06-29''' Decided that editable text content should be in a separate table, to make it easier to track revisions
 
==SQL==
 
==SQL==
 
<mysql>
 
<mysql>
−
CREATE TABLE `post` (
+
CREATE TABLE `pf_post` (
−
   `ID`         INT         NOT NULL AUTO_INCREMENT,
+
   `ID_Atom`     INT             NOT NULL COMMENT "ID of atom this post is using",
−
  `Summary`    VARCHAR(255)    NOT NULL COMMENT "short description of post, for listings",
+
   `ID_Owner`   INT          DEFAULT NULL COMMENT "ID of user who owns this post (optional)",
−
   `URI`         VARCHAR(255) DEFAULT NULL COMMENT "site-relative URI for more information",
+
   `ID_Title`    INT              NOT NULL COMMENT "ID of atom for title",
−
   `WhenCreated` DATETIME        NOT NULL COMMENT "when this question was entered",
+
   `ID_Summary`  INT          DEFAULT NULL COMMENT "ID of atom for summary (optional)",
−
   `WhoCreated`  INT              NOT NULL COMMENT "ID (site-defined) of user who added the question",
+
   `ID_Body`     INT             NOT NULL COMMENT "ID of atom for body of post",
−
   `WhenEdited`  DATETIME    DEFAULT NULL COMMENT "when this question was last edited",
+
   PRIMARY KEY(`ID_Atom`)
−
  `WhoEdited`  INT          DEFAULT NULL COMMENT "ID of user who last edited the question",
 
−
   `QtyEdits`   INT         DEFAULT NULL COMMENT "number of times this question has been edited",
 
−
   PRIMARY KEY(`ID`),
 
−
  UNIQUE KEY `content` (`Summary`)
 
 
  )
 
  )
 
  ENGINE = MYISAM;
 
  ENGINE = MYISAM;
 
</mysql>
 
</mysql>
 +
==Obsolete==
 +
* '''URI''': I haven't yet decided if this really belongs here or how to formally define it, but it could be used for either:
 +
** a page on the site which is set aside for more information about the given post
 +
** the post's official URI on the site
 +
<mysql>  `URI`        VARCHAR(255) DEFAULT NULL COMMENT "optional site-relative URI for more information",</mysql>

Latest revision as of 01:59, 30 June 2015

About

  • Purpose: content type -- has a title and content; optionally, an owner and summary
  • Fields:
    • ID_Owner: ID of user who owns this post (NULL means ownership is not relevant)
      • Some post types will not have owners, but it's a common enough thing that I thought it should go in at a low level.
    • ID_Title: ID of atom for title for page headers and narrower listings
    • ID_Summary: ID of atom for one-line description for wide listings, intro paragraph for blog posts, etc.
    • ID_Body: ID of atom for main content
  • History:
    • 2013-03-12 Created, but not yet in use.
    • 2013-03-15 Most fields moved from post to atom; added ID_Owner.
    • 2013-06-28
      • Decided to remove URI until there's a clear purpose and need for it.
      • Removed owner/summary uniqueness requirement; can't see need for this either (e.g. multiple posts might be called "status report", for example).
      • Added Body field to make this independent of the containing CMS.
    • 2013-07-21 ID_Owner needs to be DEFAULT NULL (not NOT NULL) if it's going to be optional. Duh.
    • 2015-06-29 Decided that editable text content should be in a separate table, to make it easier to track revisions

SQL

<mysql> CREATE TABLE `pf_post` ( 

  `ID_Atom`     INT              NOT NULL COMMENT "ID of atom this post is using",
  `ID_Owner`    INT          DEFAULT NULL COMMENT "ID of user who owns this post (optional)",
  `ID_Title`    INT              NOT NULL COMMENT "ID of atom for title",
  `ID_Summary`  INT          DEFAULT NULL COMMENT "ID of atom for summary (optional)",
  `ID_Body`     INT              NOT NULL COMMENT "ID of atom for body of post",
  PRIMARY KEY(`ID_Atom`)
)
ENGINE = MYISAM;

</mysql>

Obsolete

  • URI: I haven't yet decided if this really belongs here or how to formally define it, but it could be used for either:
    • a page on the site which is set aside for more information about the given post
    • the post's official URI on the site

<mysql> `URI` VARCHAR(255) DEFAULT NULL COMMENT "optional site-relative URI for more information",</mysql>

Retrieved from "https://htyp.org/mw/index.php?title=User:Woozle/PostFerret/sql/post&oldid=21059"