View source for Module:SchemaDiagram

--[[
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