Skip to content

Latest commit

 

History

History
389 lines (276 loc) · 8.83 KB

DATABASE-STRUCTURE.md

File metadata and controls

389 lines (276 loc) · 8.83 KB
Raw

Postworld Database Structure

Index of Tables

  1. Post Meta
  2. User Meta
  3. Comment Meta
  4. Post Points
  5. Comment Points
  6. Shares
  7. User Roles
  8. Favorites
  9. Feeds
  10. Cron Logs

Post Meta

Default Table Name: wp_posworld_ post_meta

Columns

post_id : integer

author_id : integer

post_class : string

  • Used by pw_query posts by class
  • Examples of classes:
    • editorial - On posts from approved authors and editors
    • community - On posts from community members

link_format : string

Default: standard

  • Used by pw_query media by format
  • Used with ng-switch on templates to change how post of various formats are displayed
  • Examples of formats:
    • standard - A regular post
    • video - On posts which have a primary link_url which is a video
    • audio - On posts which have a primary link_url which is a audio

link_url : string

  • Used to associate a specific link URL with a post
  • Used by templates for featured links, videos and audio files for embedding

post_points : integer

  • The total number of points voted to a post

post_shares : integer

  • The total number of shares to the post

rank_score : integer

  • The real-time rank-score of a post, based on calculate_rank_score() Method

User Meta

Default Table Name: wp_posworld_ user_meta

Columns

user_id : integer

  • Wordpress User ID

post_points : integer

Data : cached

  • Total points which have been voted to the user's posts

post_points_meta : integer

  • Cached data which breaks down the attribution of points by post_type
  • Data : cached
    • Generated by : pw_calculate_user_posts_points() PHP Method
    • Cached by : pw_cache_user_posts_points() PHP Method
{
	post_type : {
		post : 242,
		link : 523,
		blog : 123,
		event : 12
	}
}

comment_points : integer

Data : cached

  • Total points which have been voted to the user's comments

share_points : integer

Data : cached

  • Total number of share points of the user's shares ( incoming + outgoing )

share_points_meta : JSON

  • Cached JSON Object which breaks down the user's share points by:

    • incoming : Number of share points to my posts
    • outgoing : From posts that I have shared

  • Data : cached

    • Calculated by : calculate_user_shares($user_id)
    • Cached by : cache_user_share($user_id)
{
	outgoing : 312,
	incoming : 512
}

post_relationships : JSON

{
	viewed:[12,25,23,16,47,24,58,112,462,78,234,25,128],
	favorites:[12,16,25],
	view_later:[58,78],
}

post_votes : JSON

  • Outlines the recent 10 votes the user has cast to posts
  • Used to calculate how often a user is voting on posts
    • If the user is voting on posts too often, disable comment voting temporarily
recent : { 
	{
		post_id:242,
		points:1,
		time_voted:{{UNIX TIMESTAMP}}
	},
	{
		post_id:942,
		points:-1,
		time_voted:{{UNIX TIMESTAMP}}
	},
}

comment_votes : JSON

  • Outlines the recent 10 votes the user has cast to comments
  • Used to calculate how often a user is voting on comments
    • If the user is voting on comments too often, disable comment voting temporarily

Anatomy:

{
	has_voted:{
		recent : {
			{
				comment_id:24152,
				points:1,
				time_voted:{{UNIX TIMESTAMP}}
			},
			{
				comment_id:43532,
				points:-1,
				time_voted:{{UNIX TIMESTAMP}}
			},
		}
	}
}

location_city : string

  • Used to display and query a user by city

location_region : string

  • Used to display and query a user by region (province/state/territory)

location_country : string

  • Used to display and query a user by country

Comment Meta

Default Table Name: wp_posworld_ comment_meta

  • Used to cache data for comments

Columns

comment_id : integer

post_id : integer

comment_points : integer

Post Points

Default Table Name: wp_posworld_ post_points

  • Used to store a record of all points cast to all posts

Columns

post_id : integer

  • The post ID of the post being voted on

author_id : integer

  • The user ID of the author who created the post

user_id : integer

  • The user ID of the user who is casting a vote

post_points : integer

  • The number of points cast in the vote

time : integer

  • The time which the vote was cast

Comment Points

Default Table Name: wp_posworld_ comment_points

  • Used to store a record of all points cast to all comments

Columns

comment_post_id : integer

  • The post ID of the post that the comment is on

post_author_id : integer

  • The user ID of the author who created the post which the comment is on

comment_id : integer

  • The comment ID of the comment

comment_author_id : integer

  • The user ID of the author of the comment

user_id : integer

  • The user ID of the user who is casting the vote

comment_points : integer

  • The number of points being voted to the comment

time : integer

  • The time that the vote was cast

Shares

Default Table Name: wp_posworld_ shares

  • Used to store a record of all posts shared by users

Columns

user_id : integer

post_id : integer

  • The post ID of the post which is being shared

author_id : integer

  • The user ID of the user who authored the post

recent_ips : string

  • A list of the most recent 100 IP addesses which have loaded the share URL
  • Used to discount counting multiple hits from the same visitor

shares : integer

  • The number of shares by that user to that post

last_time : integer

  • The most recent time the share URL was loaded by a unique IP

Favorites

Default Table Name: wp_posworld_ favorites

  • Used to store a record of all favorites by user ID and post ID
  • Will be used to calculate 'reccomended pages' by favorites
    • 'People who liked this, also liked this.'
  • Will be used to reccomend friends of similar interests
    • 'People who also favorited this : {{List Users}}'

Columns

post_id : integer

user_id : integer

time : integer

  • Format : UNIX Timestamp
  • Time that the favorite was added

Feeds

Default Table Name: wp_posworld_ feeds

  • Stores registered query vars, cached feed outlines and performance data for feeds
  • Used by the load-feed JS directive to load cached feeds
  • Used by the following PHP Methods:
    • pw_register_feed()
    • pw_load_feed()
    • pw_cache_feed()

Columns

feed_id : string

  • The ID of the feed, described by pw_register_feed()

feed_query : string

  • Format : JSON
  • The registered query args for pw_query() which generates the feed_outline
  • Stored in JSON format

feed_outline : string

  • Contains a list of comma deliniated integers
  • Describes the post IDs in the order retured by pw_query()
  • 24,12,51,467,235,364,364,3453

time_start : integer

  • Format : UNIX Timestamp
  • The starting time of the last run of cache_feed()

time_end : integer

  • Format : UNIX Timestamp
  • The ending time of the last run of cache_feed()

timer : integer

  • Format : milliseconds
  • The number of miliseconds it took to generate the current feed outline

Cron Logs

Default Table Name: wp_posworld_ cron_logs

  • Stores a log of the cron tasks performed

Columns

cron_run_id : integer

  • Method : auto-increment
  • An auto-incremented ID generated by MySQL of the cron task

function_type : string

  • The name of the function which generated the cron task
    • ie. pw_cache_feed

process_id : string

  • (Optional)
  • The ID of the feed or post_type processed, if applicable

time_start : integer

  • Format : UNIX Timestamp
  • Time that the cron task started

time_end : integer

  • Format : UNIX Timestamp
  • Time that the cron task ended

timer : integer

  • Format : milliseconds
  • Time that the cron task took to process

posts : integer

  • (Optional)
  • The number of posts processed, if applicable

query_args : string

  • Format : JSON
  • (Optional)
  • If there are query args associated with the function type, store them here