Module:SchemaDiagram
Usage
[edit]User
|
|
{{#invoke:SchemaDiagram|render|
|layout= [
{ "title": "User",
"color": "#C5E8B4",
"tables": [
[ "user" ],
[ "user_properties", "user_newtalk" ]
]
}
]
|sql_page=SchemaDiagram/sql/mediawiki-1.34.0.txt
}}
 | This module uses TemplateStyles: |
Parameters
[edit]caption- (Optional) Some wikitext to render above the diagram in a yellow caption.
layout- (Required) JSON structure that describes the database table groups (see below).
sql_pageorsql- (Required) SQL "CREATE TABLE" syntax. Can either be the title of a page that contains it (e.g. Template:SchemaDiagram/sql/…), or a literal string.
Layout
[edit]The layout JSON structure is an array of objects, where each object describes a group of one or more database tables.
Each group object requires the following properties:
title- Name of the group to display.
color- CSS color value, to use as background color (using light pastel colors is the convention).
tables- An array of arrays of table names. The first level are columns.
See also
[edit]
--[[
Example for debug console:
mw.logObject( p._parse_sql( [===[
CREATE TABLE /*_*/user (
user_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_name varchar(255) binary NOT NULL default '',
user_real_name varchar(255) binary NOT NULL default '',
user_password tinyblob NOT NULL,
user_newpassword tinyblob NOT NULL,
user_newpass_time binary(14),
user_email tinytext NOT NULL,
user_touched binary(14) NOT NULL default '',
user_token binary(32) NOT NULL default '',
user_email_authenticated binary(14),
user_email_token binary(32),
user_email_token_expires binary(14),
user_registration binary(14),
user_editcount int,
user_password_expires varbinary(14) DEFAULT NULL
) /*$wgDBTableOptions*/;
]===] ) )
]]
-- For MediaWiki 1.37 and later
function parse_json(tables_json)
local parsed_tables = {}
local map_field_types = {
integer = "INT",
mwtinyint = "TINYINT"
}
function format_field_type(column_v)
type = map_field_types[column_v.type] or string.upper( column_v.type )
if type == "MWENUM" then
return "ENUM(…)"
end
if type == "MWTIMESTAMP" then
return type
end
if column_v.options and column_v.options.length and column_v.options.length < 9999 then
type = type .. '(' .. column_v.options.length .. ')'
end
return type
end
for table_i, table_v in ipairs( tables_json ) do
local current = {
table_name = table_v.name,
-- For each field, the object can have keys:
-- * Required 'type'
-- * Optional: 'primary', 'nullable'
table_fields = {}
}
for column_i, column_v in ipairs( table_v.columns ) do
local field_name = column_v.name
local field_attribs = {}
field_attribs.type = format_field_type( column_v )
if column_v.options and column_v.options.notnull then
field_attribs.nullable = false
end
if table_v.pk then
for i, pk in ipairs( table_v.pk ) do
if pk == field_name then
field_attribs.primary = true
end
end
end
table.insert( current.table_fields, {
name = field_name,
attribs = field_attribs,
} )
end
if ( current.table_name ) then
-- mw.log( "completed table: " .. current.table_name )
-- mw.logObject( current.table_fields )
parsed_tables[current.table_name] = current.table_fields
end
end
return parsed_tables
end
-- For MediaWiki 1.36 and earlier,
-- where tables where not yet completely generated via JSON.
function parse_sql(sql)
local legal_states = {
root = {
create = true,
root = true,
},
create = {
create = true,
create_table = true,
root = true,
},
create_table = {
create_table = true,
table_field_start = true,
},
table_field_start = {
table_field_type = true,
table_field_start = true,
create = true,
create_table_goto_semi = true,
},
table_field_type = {
table_field_attrib = true,
table_field_goto_closeparen = true,
},
table_field_goto_closeparen = {
table_field_goto_closeparen = true,
table_field_attrib = true,
},
table_field_attrib = {
table_field_attrib_not = true,
table_field_attrib = true,
table_field_start = true,
create = true,
},
table_field_attrib_not = {
table_field_attrib = true,
},
create_table_goto_semi = {
create_table_goto_semi = true,
root = true,
},
}
local state = "root"
local parsed_tables = {}
local parsed_current = {
table_name = nil,
table_fields = nil,
field_name = nil,
-- Required keys: 'type'
-- Optional keys: 'primary', 'nullable'.
field_attribs = nil
}
-- Inspired by http://lua-users.org/wiki/SwitchStatement
local parser = {
-- outer-most state
["root"] = function (token)
if token == "CREATE" then
return "create"
end
-- ignore anything else
return "root"
end,
-- seen "CREATE"
["create"] = function (token)
-- expect "TABLE" for "CREATE TABLE"
if token == "TABLE" then
return "create_table"
end
-- expect ";" (end of CREATE statement)
if token == ";" then
record_table_commit_maybe()
return "root"
end
-- expect top-level "INDEX" for "CREATE INDEX" (ignored)
-- expect top-level "UNIQUE" for "CREATE UNIQUE INDEX" (ignored)
-- expect "/*$wgDBTableOptions*/" (ignored)
return "create"
end,
-- seen "CREATE TABLE"
["create_table"] = function (token)
-- expect "/*_*/<table name>"
if mw.ustring.sub( token, 1, 5 ) == "/*_*/" then
record_table_begin( mw.ustring.sub( token, 6 ) )
return "create_table"
end
-- expect "<table name>"
if mw.ustring.match( token, "^%w+$" ) then
record_table_begin( token )
return "create_table"
end
-- expect "("
if token == "(" then
if parsed_current.table_name == nil then
error( "CREATE TABLE requires a name" )
end
return "table_field_start"
end
error( "unexpected token after create_table: '" .. token .. "'" )
end,
-- seen "CREATE TABLE mytable ("
["table_field_start"] = function (token)
-- expect "PRIMARY" for "CREATE TABLE mytable ( fields …, PRIMARY KEY (foo,bar)"
if token == "PRIMARY" then
-- this is too complex to parse (due to nested commas and parenthesis)
-- and we don't have a way to display it anyway.
-- Until MediaWiki 1.34, convention was to indicate PRIMARY direclty on the field (see table_field_attrib),
-- and to only use "PRIMARY KEY" statements for irregular multi-field primary keys.
-- Ignore everything until the semicolon for the end of the create_table statement.
record_table_commit_maybe()
return "create_table_goto_semi"
end
-- expect "UNIQUE" or "INDEX" for "CREATE TABLE mytable ( fields …, [UNIQUE] INDEX idx_name ( column_name,…)"
if token == "UNIQUE" or token == "INDEX" then
record_table_commit_maybe()
return "create_table_goto_semi"
end
-- expect "<field name>"
if mw.ustring.match( token, "^[%w_]+$" ) then
record_field_begin( token )
return "table_field_type"
end
-- expect "," (trailing comma after table field)
if token == "," then
return "table_field_start"
end
-- expect ")"
-- seen "CREATE TABLE … ( … )"
if token == ")" then
return "create"
end
error("unexpected token at table_field_start: '" .. token .. "'")
end,
-- seen "myfield"
["table_field_type"] = function (token)
-- expect "ENUM('<field name>'"
if mw.ustring.sub( token, 1, 5 ) == "ENUM(" then
record_field_attrib( "type", "ENUM(…)" )
return "table_field_goto_closeparen"
end
-- expect "<type>" where <type> contains at least 1 alphanumeric char,
-- accept "int" or "varbinary(123)",
-- reject ",", "()" or ";"
if mw.ustring.match( token, "%w" ) then
-- normalise type to uppercase for display, e.g. "INT" and "VARBINARY(123)
record_field_attrib( "type", string.upper( token ) )
return "table_field_attrib"
end
error("field '" .. parsed_current.table_name .. "." .. parsed_current.field_name .. "'" .. " must have a type")
end,
-- seen "myfield ENUM( …"
["table_field_goto_closeparen"] = function (token)
if mw.ustring.sub( token, -1 ) == ")" then
return "table_field_attrib"
else
-- ignore everything else
return "table_field_goto_closeparen"
end
end,
-- seen "myfield type"
["table_field_attrib"] = function (token)
if token == "NOT" then
return "table_field_attrib_not"
end
-- seen "myfield type … ,"
if token == "," then
record_field_commit()
return "table_field_start"
end
-- expect ")"
-- seen "CREATE TABLE … ( … fields … )"
if token == ")" then
record_field_commit()
return "create"
end
-- expect "<attrib>"
if mw.ustring.match( token, "^[%w_]+$" ) then
if token == "PRIMARY" then
record_field_attrib( "primary", true )
end
-- allow multiple attributes
return "table_field_attrib"
end
-- expect "default" (ignored)
-- expect "0" after "default" (ignored)
-- expect " '' " after "default" (ignored)
return "table_field_attrib"
end,
-- seen "myfield type … NOT"
["table_field_attrib_not"] = function (token)
-- expect "<attrib>"
if mw.ustring.match( token, "^%w+$" ) then
if token == "NULL" then
-- seen "mytype type … NOT NULL"
record_field_attrib( "nullable", false )
end
return "table_field_attrib"
end
error("unexpected token after table_field_attrib_not: '" .. token .. "'")
end,
-- seen "CREATE TABLE mytable ( …"
["create_table_goto_semi"] = function (token)
if token == ";" then
return "root"
else
-- ignore everything else
return "create_table_goto_semi"
end
end
}
function record_table_begin(name)
parsed_current.table_name = name
parsed_current.table_fields = {}
parsed_current.field_name = nil
parsed_current.field_attribs = nil
end
function record_table_commit_maybe()
if ( parsed_current.table_name and parsed_current.table_fields ) then
-- mw.log( "completed table: " .. parsed_current.table_name )
-- mw.logObject( parsed_current.table_fields )
parsed_tables[parsed_current.table_name] = parsed_current.table_fields
end
parsed_current.table_name = nil
parsed_current.table_fields = nil
parsed_current.field_name = nil
parsed_current.field_attribs = nil
end
function record_field_begin(name)
parsed_current.field_name = name
parsed_current.field_attribs = {}
end
function record_field_attrib(key, value)
if parsed_current.field_attribs == nil then
error("cannot set attribute before field (called with " .. key .. ", " .. value .. ")" )
end
parsed_current.field_attribs[key] = value
end
function record_field_commit()
if not ( parsed_current.field_name and parsed_current.field_attribs ) then
error("cannot commit invalid field")
end
table.insert( parsed_current.table_fields, {
name = parsed_current.field_name,
attribs = parsed_current.field_attribs,
} )
parsed_current.field_name = nil
parsed_current.field_attribs = nil
end
function parse_token(token)
local nextcase = parser[state] or error("undefined case '" .. state .. "'")
local nextstate = nextcase( token )
-- mw.log( "[" .. state .. "] '" .. token .. "' -> " .. nextstate )
if nextstate and legal_states[state][nextstate] == nil then
error("illegal state change from '" .. state .. "' to '" .. nextstate .. "'")
end
state = nextstate
end
-- remove SQL comments
sql = string.gsub( sql, "%-%-[^\n]*\n", "" )
-- split into word-ish tokens
local chunks = mw.text.split( sql, '%s+' )
for i, chunk in ipairs( chunks ) do
if mw.ustring.sub( chunk, -1 ) == "," then
parse_token( mw.ustring.sub( chunk, 1, -2 ) )
parse_token( "," )
elseif mw.ustring.sub( chunk, -1 ) == ";" then
parse_token( mw.ustring.sub( chunk, 1, -2 ) )
parse_token( ";" )
else
parse_token( chunk )
end
end
return parsed_tables
end
function normalize_group_class(value)
value = string.lower( value )
-- Legacy colors as used by [[Manual:Database layout]] before 2021
local legacyMap = {
["#c5e8b4"] = "mw-tpl-schemadiagram-group--green",
["#d8fffc"] = "mw-tpl-schemadiagram-group--blue",
["#fff1c2"] = "mw-tpl-schemadiagram-group--yellow",
["#f7ccff"] = "mw-tpl-schemadiagram-group--red",
["#e8d0c9"] = "mw-tpl-schemadiagram-group--grey"
}
return legacyMap[value] or "mw-tpl-schemadiagram-group--green"
end
local p = {}
function p.render(frame)
-- Process arguments
local input = ( frame.args.sql_page and mw.title.new( frame.args.sql_page, 'Template' ):getContent() )
or frame.args.sql
or ''
local parsed_tables = nil
if ( frame.args.sql_page and string.match( frame.args.sql_page, '.json$' ) ) then
parsed_tables = parse_json( mw.text.jsonDecode( input ) )
elseif frame.args.sql_json then
parsed_tables = parse_json( mw.text.jsonDecode( frame.args.sql_json ) )
else
parsed_tables = parse_sql( input )
end
input = nil
local layout = mw.text.jsonDecode( frame.args.layout )
local show_render_link = not ( frame.args.show_render_link == nil )
local warn_incomplete_layout = not ( frame.args.warn_incomplete_layout == 'no' )
local credit_line = frame.args.credit
-- Prepare header (if any)
local header_html = ''
if frame.args.header_title then
header_html = header_html .. '<h1 class="mw-tpl-schemadiagram-title">' .. frame.args.header_title .. '</h1>'
end
if frame.args.caption then
header_html = header_html .. '<div class="mw-tpl-schemadiagram-caption">'
if not (frame.args.show_render_link == nil) then
local render_link = mw.title.getCurrentTitle():canonicalUrl( { action = 'render' } )
header_html = header_html
.. '<p class="mw-tpl-schemadiagram-fullscreen">[' .. render_link .. ' <span class="mw-ui-button mw-ui-progressive">Full screen</span>]</p>'
end
header_html = header_html .. '<p>' .. frame.args.caption .. '</p>'
header_html = header_html .. '</div>'
end
-- Start output HTML
local stylesheet = ( frame.args.wmui and "SchemaDiagramWmui.css" ) or "SchemaDiagram.css"
local result_html = ''
.. frame:extensionTag{ name = 'templatestyles', args = { src = stylesheet } }
.. header_html
.. '<div class="mw-tpl-schemadiagram">'
-- Build table groups
local shown_table_names = {}
for i, group in ipairs( layout ) do
local group_class = normalize_group_class( group.color )
local group_html = '<div class="mw-tpl-schemadiagram-group ' .. group_class .. '" style="background: ' .. group.color .. ';">'
.. '<h2>' .. mw.text.nowiki( group.title ) .. '</h2>'
.. '<table><tr>'
for i, table_names in ipairs( group.tables ) do
local tables_column_html = '<td>'
for i, table_name in ipairs( table_names ) do
shown_table_names[table_name] = true
local table_html = '<div class="mw-tpl-schemadiagram-table">'
.. '<h3>[[Special:MyLanguage/Manual:' .. table_name .. ' table|' .. table_name .. ']]</h3>'
.. '<ul>'
local table_fields = parsed_tables[table_name] or error( "Unknown database table '" .. table_name .. "'" )
for i, field in ipairs( table_fields ) do
local field_html_attribs = {}
if field.attribs.primary then
field_html_attribs.class = 'mw-tpl-schemadiagram-field mw-tpl-schemadiagram-field--primary'
field_html_attribs.title = 'Primary key'
elseif field.attribs.nullable == false then
field_html_attribs.class = 'mw-tpl-schemadiagram-field mw-tpl-schemadiagram-field--notnull'
field_html_attribs.title = 'Required (Not nullable)'
else
field_html_attribs.class = 'mw-tpl-schemadiagram-field mw-tpl-schemadiagram-field--regular'
field_html_attribs.title = 'Optional (May be null)'
end
local field_html = mw.html.create( 'span' )
field_html:attr( field_html_attribs )
table_html = table_html
.. '<li>'
.. tostring( field_html )
.. ' '
.. field.name
.. ' '
.. field.attribs.type
.. '</li>'
end
tables_column_html = tables_column_html .. table_html
.. '</ul>'
.. '</div>'
end
group_html = group_html .. tables_column_html .. '</td>'
end
result_html = result_html
.. group_html
.. '</tr></table>'
.. '</div>'
end
if credit_line then
result_html = result_html
.. '<p class="mw-tpl-schemadiagram-credit">'
.. frame:preprocess( 'Credit: ' .. credit_line .. '<br/><br/>From {{canonicalurl:{{FULLPAGENAME}}}}' )
.. '</p>'
end
-- Close the diagram element (flexbox)
result_html = result_html .. '</div>'
if warn_incomplete_layout then
for table_name, table_fields in pairs( parsed_tables ) do
if not shown_table_names[table_name] then
mw.log( "table not in layout: " .. table_name )
mw.addWarning( "Database table '" .. table_name .. "' is not in the current layout." )
end
end
end
return result_html
end
p._parse_sql = parse_sql
return p