{"id":2435,"date":"2015-02-04T11:53:55","date_gmt":"2015-02-04T11:53:55","guid":{"rendered":"http:\/\/wiki.davelevy.info\/?p=2435"},"modified":"2015-02-04T14:11:52","modified_gmt":"2015-02-04T14:11:52","slug":"wordpress-database-schema","status":"publish","type":"post","link":"https:\/\/davelevy.info\/wiki\/wordpress-database-schema\/","title":{"rendered":"WordPress Database Schema"},"content":{"rendered":"

While working on Nulis and while attempting to solve some other problems I began to need to document the contents of wordpress databases. The first problem I sought to solve was to build a visual map of the site. This needs the schema to be understood and the table contents to be extracted. This needs the login parameters and a connection route. The second problem involves creating a new template and will need it’s SQL designed and encapsulated in PHP.<\/p>\n

This article<\/a> documents the relationships between tags and posts<\/strong>, which can be categories and pages.<\/p>\n

In order to extract the schema logic, I was pointed at this page at Stack Over flow<\/a>.<\/p>\n

From these I examined,<\/p>\n

    \n
  1. http:\/\/schemaspy.sourceforge.net\/<\/a> &<\/li>\n
  2. https:\/\/code.google.com\/p\/database-diagram\/<\/a><\/li>\n<\/ol>\n

    the first requires java which is not always available in hosting environments, the second doesn’t seem to do foreign keys, at least not with My SQL, it does however work from the SQL\/DDL. It might be best to extract the schema using mysqldump, parse it using a scripting language and generate the graph using dot. (This of course could be used to generate an html page).<\/p>\n

    While looking for an image to decorate this page, using google image search with the keywords “wordpress database”\u00a0 I came across a bunch of schema diagrams, I wonder if these are useful and usable. It would seem they are, here is Database Descriptions at the WordPress Codex<\/a>. It’s documented as V3.3, so I will check if it’s changed since.<\/p>\n

    Here is the schema diagram.<\/p>\n

    \"wp<\/a><\/p>\n

    ooOOOoo<\/p>\n

    The featured image taken from http:\/\/www.metserve.com\/blog\/optimise-wordpress-database<\/a><\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    While working on Nulis and while attempting to solve some other problems I began to need to document the contents of wordpress databases. The first problem I sought to solve was to build a visual map of the site. This needs the schema to be understood and the table contents to be extracted. This needs…<\/p>\n","protected":false},"author":1,"featured_media":2436,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_share_on_mastodon":"0"},"categories":[3,584],"tags":[496,812,911,28],"class_list":["post-2435","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology","category-wordpress-technology","tag-database","tag-schema","tag-technology","tag-wordpress"],"share_on_mastodon":{"url":"","error":""},"jetpack_featured_media_url":"https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2015\/02\/Optimize-WordPress.jpg","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts\/2435","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/comments?post=2435"}],"version-history":[{"count":1,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts\/2435\/revisions"}],"predecessor-version":[{"id":2437,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts\/2435\/revisions\/2437"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/media\/2436"}],"wp:attachment":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/media?parent=2435"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/categories?post=2435"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/tags?post=2435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}