July 25, 2020

👭 Knight Challenge #11 👬

Want to try your hand at these challenges? There's a couple of things you can do!
From writing, to research, to images, find your preferred way to contribute with our eleventh theme: Couples!

Latest Announcements

Module:UtilsCargo

From Zelda Wiki, the Zelda encyclopedia
Jump to: navigation, search

This module is a wrapper over mw.ext.cargo. It also provides utilities for constructing queries.

Parser functions

query

query(tables, fields, [args])

Parameters
Returns
  • An array of the query results. Throws an error when query syntax is invalid.
Examples
InputOutputStatus
query(
  "Games",
  "code, shortName",
  {
    orderBy = "releaseDate",
    where = "type='main' AND releaseDate !=''",
    limit = 3,
  }
)
{
  {
    shortName = "The Legend of Zelda",
    code = "TLoZ",
  },
  {
    shortName = "The Adventure of Link",
    code = "TAoL",
  },
  {
    shortName = "A Link to the Past",
    code = "ALttP",
  },
}
Green check.svg


Query builders

allOf

allOf(...)

Parameters
Returns
  • A WHERE clause with ANDed conditions and escaped quotation marks.
Examples
InputOutputStatus
allOf(
  {
    remakeNum = 2,
    game = "Link's Awakening",
  },
  "foo HOLDS 'bar'",
  "baz LIKE '%quux%'"
)
"remakeNum='2' AND game='Link\\'s Awakening' AND foo HOLDS 'bar' AND baz LIKE '%quux%'"
Green check.svg

holdsAny

holdsAny(field, values)

Parameters
Returns
  • A query string of or'ed HOLDS clauses.
Examples
InputOutputStatus
holdsAny("game", {"Link's Awakening"})
"game HOLDS 'Link\\'s Awakening'"
Green check.svg
holdsAny("game", {"OoT", "TP"})
"game HOLDS 'OoT' OR game HOLDS 'TP'"
Green check.svg

IN

IN(field, values)

Parameters
Returns
  • A where clause using the SQL IN keyword.
Examples
InputOutputStatus
IN("_pageName", {"Link's Shadow", "Zelda", "Ganon"})
"_pageName IN ('Link\\'s Shadow', 'Zelda', 'Ganon')"
Green check.svg
IN("_pageName", {})
"_pageName IN ('')"
Green check.svg

local p = {}
local h = {}

local frame = mw.getCurrentFrame()

function p.query(tables, fields, args)
	return mw.ext.cargo.query(tables, fields, args)
end

local function escape(str)
	return string.gsub(str, "'", "\\'")
end

function p.allOf(...)
	local query
	for i, v in ipairs({...}) do
		if type(v) == "table" then
			for k, v in pairs(v) do
				v = escape(v)
				query = h.andClause(query, string.format("%s='%s'", k, v))
			end
		else
			query = h.andClause(query, v)
		end
	end
	return query
end

function p.holdsAny(field, values)
	local query
	for i, value in ipairs(values) do
		value = escape(value)
		query = h.orClause(query, string.format("%s HOLDS '%s'", field, value))
	end
	return query
end

function p.IN(field, values)
	local inValues = {}
	for i, value in ipairs(values) do
		value = escape(value)
		value = string.format("'%s'", value)
		inValues[i] = value
	end
	inValues = table.concat(inValues, ", ")
	if inValues == "" then
		inValues = "''"
	end
	return string.format("%s IN (%s)", field, inValues)
end

function h.andClause(query, clause)
	return h.addClause("AND", query, clause)
end

function h.orClause(query, clause)
	return h.addClause("OR", query, clause)
end

function h.addClause(operator, query, clause)
	if not query or query == "" then
		return clause
	end
	return table.concat({query, operator, clause}, " ")
end


p.Schemas = {
	query = {
		tables = {
			type = "string",
			required = true,
		},
		fields = {
			type = "string",
			required = true,
		},
		args = {
			type = "record",
			properties = {
				{
					name = "where",
					type = "string",
				},	
				{
					name = "join",
					type = "string",
				},
				{
					name = "groupBy",
					type = "string",
				},
				{
					name = "having",
					type = "string",
				},
				{
					name = "orderBy",
					type = "string",
				},
				{
					name = "limit",
					type = "number",
					default = 100,
				},
				{
					name = "offset",
					type = "number",
					default = 0,
				},
			}
		},
	},
	allOf = {
		["..."] = {
			type = "array",
			required = true,
			items = {
				oneOf = {
					{
						type = "string",
					},
					{
						type = "map",
						keys = { type = "string" },
						values = { type = "string" },
					},
				},
			}
		},
	},
	holdsAny = {
		field = {
			type = "string",
			required = true,
		},
		values = {
			type = "array",
			required = true,
			items = { type = "string" },
		}
	},
	IN = {
		field = {
			type = "string",
			required = true,
		},
		values = {
			type = "array",
			required = true,
			items = { type = "string" },
		},
	}
}

p.Documentation = {
	sections = {
		{
			heading = "Parser functions",
			section = {
				declare = {
					params = {"tableName", "fields"},
				},
				store = {
					params = {"tableName", "fields"},
				},
				query = {
					params = {"tables", "fields", "args"},
					returns = "An array of the query results. Throws an error when query syntax is invalid.",
					cases = {
						{
							args = {
								"Games",
								"code, shortName",
								{
									where = "type='main' AND releaseDate !=''",
									orderBy = "releaseDate",
									limit = 3,
								},
							},
							expect = {
								{ code = "TLoZ", shortName = "The Legend of Zelda"},
								{ code = "TAoL", shortName = "The Adventure of Link"},
								{ code = "ALttP", shortName = "A Link to the Past"},
							},
						},
					}
				},
			}
		},
		{
			heading = "Query builders",
			section = {
				allOf = {
					params = {"..."},
					returns = "A WHERE clause with ANDed conditions and escaped quotation marks.",
					cases = {
						outputOnly = true,
						{
							args = {
								{
									game = "Link's Awakening",
									remakeNum = 2,
								},
								"foo HOLDS 'bar'",
								"baz LIKE '%quux%'",
							},
							expect = [[remakeNum='2' AND game='Link\'s Awakening' AND foo HOLDS 'bar' AND baz LIKE '%quux%']]
						}
					},
				},
				holdsAny = {
					params = {"field", "values"},
					returns = "A query string of or'ed HOLDS clauses.",
					cases = {
						outputOnly = true,
						{
							args = {"game", {"Link's Awakening"}},
							expect = "game HOLDS 'Link\\'s Awakening'",
						},
						{
							args = {"game", {"OoT", "TP"}},
							expect = "game HOLDS 'OoT' OR game HOLDS 'TP'",
						}
					}
				},
				IN = {
					params = {"field", "values"},
					returns = "A where clause using the SQL IN keyword.",
					cases = {
						outputOnly = true,
						{
							args = {"_pageName", {"Link's Shadow", "Zelda", "Ganon"}},
							expect = [[_pageName IN ('Link\'s Shadow', 'Zelda', 'Ganon')]],
						},
						{
							args = {"_pageName", {}},
							expect = [[_pageName IN ('')]],
						}
					}
				},
			}
		}
	}
}

return p