Module:SchemaDiagram

Usage

{{#invoke:SchemaDiagram|render|
|layout= [
  { "title": "User",
    "color": "#C5E8B4",
    "tables": [
      [ "user" ],
      [ "user_properties", "user_newtalk" ]
    ]
  }
]
|sql_page=SchemaDiagram/sql/mediawiki-1.34.0.txt
}}

Parameters

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_page or sql

(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

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

• Manual:Database layout/diagram
• Manual:Database layout/diagram instructions
• Template:SchemaDiagram.css
• Special:PrefixIndex/Template:SchemaDiagram/sql/

---

--[[
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 and #table_v.pk == 1 and table_v.pk[1] == field_name then
				field_attribs.primary = true
			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 ) )
	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