WackoWiki: Database

https://wackowiki.org/doc     Version: 10.02.2020 10:48
Dev[link7]/Database

Table Structure

  1. acl 
  2. auth_token
  3. cache
  4. category
  5. category_assignment
  6. config
  7. file
  8. file_link
  9. log 
  10. menu
  11. page[link4]
    1. the latest versions of the pages and also stored comments, distinct from ordinary pages of comments that, comments that filled the fields and comment_on_id
  12. page_link
    1. a reference to internal Wiki page
  13. rating
  14. referrer
  15. revision
  16. poll
  17. user
  18. user_setting
  19. usergroup
  20. usergroup_member
  21. watch

Access MySQL via command line

 mysql -h 127.0.0.1 -P 3306 -u root -p <database>

 mysql> DESCRIBE table_name;

Database Diagram

There are no attached files to display.

0 of 0 Files accessible from this page :


-> MySQL Workbench[link5]

Database Script


Performance

Indexes

Naming

Use singular for table names, e.g. “page” not “pages”
A table structure describes a specific entity

Proposed Database Schema Changes

-> R5.5[link6]

Table Details

Field Type Null Key Default Extra

Schema summary

DESCRIBE page;

+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| page_id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| version_id       | int(10) unsigned    | NO   |     | 1       |                |
| owner_id         | int(10) unsigned    | NO   | MUL | 0       |                |
| user_id          | int(10) unsigned    | NO   | MUL | 0       |                |
| title            | varchar(250)        | NO   | MUL |         |                |
| tag              | varchar(250)        | NO   | UNI |         |                |
| supertag         | varchar(250)        | NO   | MUL |         |                |
| menu_tag         | varchar(250)        | NO   |     |         |                |
| depth            | int(10) unsigned    | NO   | MUL | 0       |                |
| parent_id        | int(10) unsigned    | NO   |     | 0       |                |
| created          | datetime            | YES  | MUL | NULL    |                |
| modified         | datetime            | YES  | MUL | NULL    |                |
| body             | mediumtext          | NO   | MUL | NULL    |                |
| body_r           | mediumtext          | NO   |     | NULL    |                |
| body_toc         | text                | NO   |     | NULL    |                |
| formatting       | varchar(20)         | NO   |     | wacko   |                |
| edit_note        | varchar(200)        | NO   |     |         |                |
| minor_edit       | tinyint(1) unsigned | YES  | MUL | 0       |                |
| page_size        | int(10) unsigned    | NO   |     | 0       |                |
| reviewed         | tinyint(1) unsigned | NO   | MUL | 0       |                |
| reviewed_time    | datetime            | YES  |     | NULL    |                |
| reviewer_id      | int(10) unsigned    | NO   |     | 0       |                |
| ip               | varchar(15)         | NO   |     |         |                |
| latest           | tinyint(1) unsigned | YES  |     | 1       |                |
| handler          | varchar(30)         | NO   |     | page    |                |
| comment_on_id    | int(10) unsigned    | NO   | MUL | 0       |                |
| comments         | int(4) unsigned     | NO   |     | 0       |                |
| hits             | int(10) unsigned    | NO   |     | 0       |                |
| theme            | varchar(20)         | YES  |     | NULL    |                |
| page_lang        | varchar(2)          | NO   |     |         |                |
| commented        | datetime            | YES  | MUL | NULL    |                |
| description      | varchar(250)        | NO   |     |         |                |
| keywords         | varchar(250)        | NO   |     |         |                |
| footer_comments  | tinyint(1) unsigned | YES  |     | NULL    |                |
| footer_files     | tinyint(1) unsigned | YES  |     | NULL    |                |
| footer_rating    | tinyint(1) unsigned | YES  |     | NULL    |                |
| hide_toc         | tinyint(1) unsigned | YES  |     | NULL    |                |
| hide_index       | tinyint(1) unsigned | YES  |     | NULL    |                |
| tree_level       | tinyint(1) unsigned | NO   |     | 0       |                |
| show_menu_tag    | tinyint(1) unsigned | NO   |     | 0       |                |
| allow_rawhtml    | tinyint(1) unsigned | YES  |     | NULL    |                |
| disable_safehtml | tinyint(1) unsigned | YES  |     | NULL    |                |
| noindex          | tinyint(1) unsigned | YES  |     | 0       |                |
| deleted          | tinyint(1) unsigned | YES  | MUL | 0       |                |
+------------------+---------------------+------+-----+---------+----------------+

Indices

SHOW INDEX IN page;

+----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dev_page |          0 | PRIMARY           |            1 | page_id       | A         |         151 |     NULL | NULL   |      | BTREE      |         |               |
| dev_page |          0 | idx_tag           |            1 | tag           | A         |         151 |     NULL | NULL   |      | BTREE      |         |               |
| dev_page |          1 | idx_user_id       |            1 | user_id       | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
| dev_page |          1 | idx_owner_id      |            1 | owner_id      | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
| dev_page |          1 | idx_supertag      |            1 | supertag      | A         |         151 |     NULL | NULL   |      | BTREE      |         |               |
| dev_page |          1 | idx_depth         |            1 | depth         | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
| dev_page |          1 | idx_created       |            1 | created       | A         |         151 |     NULL | NULL   | YES  | BTREE      |         |               |
| dev_page |          1 | idx_modified      |            1 | modified      | A         |         151 |     NULL | NULL   | YES  | BTREE      |         |               |
| dev_page |          1 | idx_minor_edit    |            1 | minor_edit    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| dev_page |          1 | idx_deleted       |            1 | deleted       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| dev_page |          1 | idx_reviewed      |            1 | reviewed      | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| dev_page |          1 | idx_comment_on_id |            1 | comment_on_id | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| dev_page |          1 | idx_commented     |            1 | commented     | A         |         151 |     NULL | NULL   | YES  | BTREE      |         |               |
| dev_page |          1 | idx_title         |            1 | title         | A         |         151 |     NULL | NULL   |      | BTREE      |         |               |
| dev_page |          1 | body              |            1 | body          | NULL      |         151 |     NULL | NULL   |      | FULLTEXT   |         |               |
+----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+