- The following documentation is transcluded from Module:Cargo/doc (edit):
declare
This assembles a #cargo_declare
call for the given Cargo table, provided its definition is in the defs
at the top of the module. This is used in a <noinclude
section of a template to permit creating the table. The template in question is almost always going to be the same one making #cargo_store
calls in a <includeonly>
section to insert values into the table.
Usage is simple:
{{#invoke:cargo|declare|_table=}}
store
#cargo_store
has to be used directly due to technical issues of framing and when Lua processing happens in the page save, so there is no store function in the module.
query
The query
function of this module is an alternative to #cargo_query
with format=template
. It should not be used as an alternative to #cargo_query
for any other format
.
The latter is subject to an intermittent bug that causes output to appear as unrendered HTML requiring a purge to correct. The query
function avoids the bug by processing the template transclusion in Lua after running the query through Cargo, rather than having Cargo perform both steps.
Usage is similar to a #cargo_query
call:
{{#invoke:Cargo|query |tables= |fields= |where= |join= |groupBy= |having= |orderBy= |template= |intro= |outro= |default= |limit= |offset= }}
See Extension:Cargo/Querying data for more information, as the parameters here have the same usage as in #cargo_query
with the following exceptions:
table
cannot be used as an alternative totables
join
is used instead ofjoin on
groupBy
is used instead ofgroup by
orderBy
is used instead oforder by
- This function always behaves as if
named args
isyes
- Very important: this module provides important special parameterization features
where
andhaving
. Using these features allow a number of workarounds for certain Cargo bugs and limitations to be used automatically. In addition, it also handles certain escaping automatically. It is strongly recommended that the parameterization features be used on every query to prevent a number of avoidable errors and complications.
where
and having
parameterization
The parameterization features are similar to Parameterized queries used in many programming languages to avoid the security risks of SQL injection. They have different purposes here, but the way they are used in this module is similar in some ways.
This involves using "placeholders" in the where
and having
clauses where numeric or quoted values would usually go, and then supplying values for those placeholders in special arguments in the #invoke call. In the clauses, the placeholders are identified by a name in between two question marks, such as ?$value?
or ?$value?
.
- The name should begin with
#
for placeholders used where numeric values should go. - The name should begin with
$
for placeholders used where quoted values should go.
As an example, suppose you wanted to get all escorts with over 5000 hull strength. Without using parameterization. Using a basic #cargo_query
call, it'd look something like this:
{{#cargo_query: tables=Ships |fields=_pageName |where=Ships.type HOLDS "Escort" AND Ships.hull > 5000 |format=template |template=SomeTemplate }}
Using the query
function of this module, the recommended way to write this would instead be something like this:
{{#invoke:Cargo|query |tables=Ships |fields=_pageName |where=Ships.type HOLDS ?$type? AND Ships.hull > ?#hull? |$type=Escort |#hull=5000 |template=SomeTemplate }}
It's true that this simple case doesn't gain anything from it, but it would come in useful if:
- Any of the values happened to contain text that matched the field names of one of the tables, as this triggers a known Cargo bug. This module automatically detects that as long as the
defs
table at the top of this module is up to date and applies a workaround. - If any quoted value has quotes that are part of the value. This automatically escapes those quotes so they are handled correctly. Otherwise, if you were building a template to use arbitrary values, you'd have to use a
#replace
to escape them yourself. - This often comes in most useful in templates where the values aren't known in advance.
There is a more advanced parameterization feature used to work around limitations with using HOLDS
or HOLDS NOT
on list fields in Cargo tables. As a general rule, you can only get away with using HOLDS
or HOLDS NOT
once in a query, though it may work if you don't have to use them on different fields.
For example, say you were looking for all Escorts with Pilot Maneuvers. The way to write this would seem to be:
{{#invoke:Cargo|query |tables=Ships |fields=_pageName |where=Ships.type HOLDS ?$type? AND Ships.abilities HOLDS ?$ability? |$type=Escort |$ability=Pilot Maneuvers }}
But this will probably fail, as Cargo will probably not be able to construct a valid SQL query out of this.
This module has a workaround, and this workaround is recommended instead of using HOLDS
or HOLDS NOT
at all. Instead, you'd write it something like this:
{{#invoke:Cargo|query |tables=Ships |fields=_pageName |where=?%Ships.type.holds? AND ?%Ships.abilities.holds? |%Ships.type.holds=Escort |%Ships.abilities.holds=Pilot Maneuvers }}
The module will construct a comparison for each that doesn't actually use HOLDS
or HOLDS NOT
, though usually give the same result, and can be used multiple times in a single query.
In more detail, the HOLDS
or HOLDS NOT
workarounds are used like this:
- Start the name with
%
to simulateHOLDS
and!
to simulateHOLDS NOT
- Follow with the table name the field to check belongs to and follow that with a
.
- Add the field name and then another
.
(this with the table name allows the module to look up the correct delimiter from itsdefs
table), which it needs to know. - After the last dot, it isn't actually necessary to add anything. Anything more just makes it a unique name, which is useful if you need to do multiple
HOLDS
checks on the same field. For example?%Ships.abilities.holds1? AND ?%Ships.abilities.holds2?
would let you check for ships that have two specific abilities by passing those abilities through the arguments%Ships.abilities.holds1
and%Ships.abilities.holds2
.- Using
holds
orholdsnot
here is recommended for readability.
- Using
-- TABLE DEFINITIONS
-- declared through the module so it has access to the field names
-- which allows the module to work around an issue with query values
-- that happen to include words matching one of the field names
-- In each table, the row has two to three values:
-- 1. field name (matches the appropriate template parameter where possible)
-- 2. field type (see https://www.mediawiki.org/wiki/Special:MyLanguage/Extension:Cargo/Storing_data)
-- 3. a delimiter if a list of values is needed in the field (omitting this makes the field single-value)
-- UNDER CONSTRUCTION
local defs = {
Costs = {
{ 'id', 'String' }, -- For the "group by" workaround, will just be {{FULLPAGENAME}}#costunit for a unique row value
{ 'cost', 'Integer' }, -- Has cost
{ 'costunit', 'String' }, -- Has cost unit
},
Rarity = {
{ 'rarity', 'String' }, -- Has rarity
},
Ships = {
{ 'name', 'String' }, -- Has name
{ 'image', 'Page' }, -- Has image
{ 'fc', 'Integer' }, -- Is fleet version
{ 'faction', 'String', ',' }, -- Is of faction, Is of faction/code
{ 'facsort', 'String' }, -- new
{ 'rank', 'String' }, -- Is available at rank
{ 'ranklevel', 'Integer' }, -- Is available at level
{ 'tier', 'String' }, -- Is available at tier
{ 'upgradecost', 'String' }, -- Has T5-U upgrade cost
{ 'type', 'String', '/' }, -- Has ship type
{ 'hull', 'Integer' }, -- Has hull strength
{ 'hullmod', 'Float' }, -- Has hull modifier
{ 'shieldmod', 'Float' }, -- Has shield modifier
{ 'turnrate', 'Float' }, -- Has turn rate
{ 'impulse', 'Float' }, -- Has impulse modifier
{ 'inertia', 'Integer' }, -- Has inertia rating
{ 'powerall', 'Integer' }, -- Has bonus power to all
{ 'powerweapons', 'Integer' }, -- Has bonus power to weapons
{ 'powershields', 'Integer' }, -- Has bonus power to shields
{ 'powerengines', 'Integer' }, -- Has bonus power to engines
{ 'powerauxiliary', 'Integer' }, -- Has bonus power to auxiliary
{ 'powerboost', 'Integer' }, -- Sum of previous 5 power entries
{ 'boffs', 'String', ',' }, -- Has boff stations
{ 'fore', 'Integer' }, -- Has fore weapons
{ 'aft', 'Integer' }, -- Has aft weapons
{ 'equipcannons', 'Integer' }, -- Can equip dual cannons
{ 'devices', 'Integer' }, -- Has device slots
{ 'consolestac', 'Integer' }, -- Has tactical consoles
{ 'consoleseng', 'Integer' }, -- Has engineering consoles
{ 'consolessci', 'Integer' }, -- Has science consoles
{ 'uniconsole', 'String' }, -- Has uni console
{ 't5uconsole', 'String' }, -- Gains the following console on T5-U upgrade
{ 'hangars', 'Integer' }, -- Has hangar bays
{ 'cost', 'String', '/' }, -- Has cost in units
{ 'abilities', 'String', ',' }, -- Has ability
{ 'admiraltyeng', 'String', ',' }, -- Has engineering admiralty capability
{ 'admiraltytac', 'String', ',' }, -- Has tactical admiralty capability
{ 'admiraltysci', 'String', ',' }, -- Has science admiralty capability
{ 'displayprefix', 'String' }, -- Has name prefix
{ 'displayclass', 'String' }, -- Is ship class
{ 'factionlede', 'String' }, -- Is of primary faction
},
Mastery = {
{ 'masterytype', 'String' }, -- Has mastery type
{ 'shiptype', 'String' }, -- Has ship profession
{ 'shipfaction', 'String' }, -- Has faction
{ 'masterypackage', 'String' }, -- Has mastery package
},
Traits = {
{ 'name', 'String' }, -- Has name
{ 'chartype', 'String' }, -- Is for character type
{ 'environment', 'String' }, -- is for environment
{ 'type', 'String' }, -- Trait type
{ 'isunique', 'Boolean' }, -- Has unique effect
{ 'master', 'Boolean' }, -- Is master trait
{ 'description', 'Text' }, -- Has game description
{ 'required', 'String', ',' }, -- Trait is required for
{ 'possible', 'String', ',' }, -- Trait is optional for, Trait is possible for
},
BoffTraits = {
{ 'name', 'String' }, -- Has name
{ 'level', 'String' }, -- "Basic", "Base", or "Superior"
{ 'levelname', 'String' }, -- Basic trait has name, Base trait has name, Superior trait has name
{ 'required', 'String', ',' }, -- Basic trait is required for, Base trait is required for, Superior trait is required for
{ 'possible', 'String', ',' }, -- Basic trait is possible for, Base trait is possible for, Superior trait is possible for
},
Doffs = {
{ 'name', 'String' }, -- Has name
{ 'image', 'Page' }, -- Has image
{ 'faction', 'String' }, -- Is of faction
{ 'species', 'String' }, -- Is of species
{ 'gender', 'String' }, -- is of gender
{ 'shipdutytype', 'String' }, -- Has ship duty type
{ 'spec', 'String' }, -- Has specialization
{ 'quality', 'String' }, -- Is of quality
{ 'qualityindex', 'Integer' }, -- Is of quality/order
{ 'rank', 'String' }, -- Is of rank
{ 'traits', 'String', ',' }, -- Has traits
{ 'rnd', 'String' }, -- Has r&d school
{ 'powertype', 'Integer' }, -- Has power type
{ 'uniquepower', 'Boolean' }, -- is a unique power type?
{ 'powerdesc1', 'Text' }, -- Has game description
{ 'powerdesc2', 'Text' }, -- Has ? quality effect
},
Specializations = {
{ 'name', 'String' }, -- Has name
{ 'department', 'Page' }, -- Is in department
{ 'powertype', 'Integer' }, -- Is of faction
{ 'powerid', 'String' }, -- to allow use of group by to workaround duplication issue
{ 'shipdutytype', 'String' }, -- Has ship duty type
{ 'description', 'Text' }, -- Has game description
{ 'white', 'Text' }, -- Has white quality effect
{ 'green', 'Text' }, -- Has green quality effect
{ 'blue', 'Text' }, -- Has blue quality effect
{ 'purple', 'Text' }, -- Has purple quality effect
{ 'violet', 'Text' }, -- Has violet quality effect
{ 'gold', 'Text' }, -- Has gold quality effect
},
DoffIcons = {
{ 'species', 'String' }, -- Is of species
{ 'gender', 'String' }, -- is of gender
},
Assignments = {
{ 'name', 'String' }, -- Has name
{ 'faction', 'String' }, -- Is of faction
{ 'chain', 'String' }, -- Is part of chain
{ 'chainpos', 'Integer' }, -- is position in chain
{ 'chainrepeat', 'String', ',' }, -- Unlocks repeatable assignment
{ 'loctype', 'String' }, -- Has location type
{ 'commendation', 'String', ',' }, -- Is for commendation
{ 'tier', 'Integer' }, -- Requires tier
{ 'rarity', 'String' }, -- Is of rarity
{ 'rarityindex', 'Integer' }, -- Is of rarity
{ 'duration', 'String', ',' }, -- Has duration
{ 'cooldown', 'String' }, -- Has cooldown
{ 'level', 'Integer', ',' }, -- Requires level
{ 'doffs', 'Wikitext', ',' }, -- Requires duty officer
{ 'risk', 'String' }, -- Has casualty risk
{ 'showcost', 'String', ';' },
{ 'cost', 'String', ';' },
{ 'showreward', 'String', ';' },
{ 'plus', 'String', ';' },
{ 'crit', 'String', ';' },
},
AssignReqs = {
{ 'id', 'String' },
{ 'count', 'Integer' },
{ 'reqtype', 'String' },
{ 'requirement', 'String' },
},
AssignSlots = {
{ 'id', 'String' },
{ 'effect', 'String' }, -- good or bad
{ 'effects', 'String' }, -- cs, s, f, or cf (critical or noncritical success and failure)
{ 'effectfrom', 'String' }, -- dept, spec, or trait that provides the effect
},
Admiralty = {
{ 'name', 'String' },
{ 'faction', 'String' },
{ 'rarity', 'String' },
{ 'type', 'String' },
{ 'eng', 'Integer' },
{ 'tac', 'Integer' },
{ 'sci', 'Integer' },
{ 'bonus', 'String' },
{ 'cost', 'String', '/' },
{ 'tier', 'Integer' },
{ 'rarindex', 'Integer' },
},
GwNav = {
{ 'flavor', 'String' },
},
SwObtain = {
{ 'cat', 'String' },
{ 'type', 'String' },
{ 'flavor', 'String' },
{ 'box', 'String' },
{ 'lb', 'String' },
{ 'ships', 'String' },
{ 'rep', 'String' },
},
GwObtain = {
{ 'cat', 'String' },
{ 'type', 'String' },
{ 'flavor', 'String' },
{ 'box', 'String' },
{ 'lb', 'String' },
{ 'rep', 'String' },
},
Omni = {
{ 'type1', 'String' },
{ 'weapon1', 'String' },
{ 'type2', 'String' },
{ 'weapon2', 'String' },
},
Wide = {
{ 'type2', 'String' },
{ 'weapon2', 'String' },
},
Infobox= {
{ 'name', 'String' },
{ 'rarity', 'String' },
{ 'type', 'String' },
{ 'boundto', 'String' },
{ 'boundwhen', 'String' },
{ 'who', 'String' },
{ 'head1', 'Text' },
{ 'head2', 'Text' },
{ 'head3', 'Text' },
{ 'head4', 'Text' },
{ 'head5', 'Text' },
{ 'head6', 'Text' },
{ 'head7', 'Text' },
{ 'head8', 'Text' },
{ 'head9', 'Text' },
{ 'subhead1', 'Text' },
{ 'subhead2', 'Text' },
{ 'subhead3', 'Text' },
{ 'subhead4', 'Text' },
{ 'subhead5', 'Text' },
{ 'subhead6', 'Text' },
{ 'subhead7', 'Text' },
{ 'subhead8', 'Text' },
{ 'subhead9', 'Text' },
{ 'text1', 'Text' },
{ 'text2', 'Text' },
{ 'text3', 'Text' },
{ 'text4', 'Text' },
{ 'text5', 'Text' },
{ 'text6', 'Text' },
{ 'text7', 'Text' },
{ 'text8', 'Text' },
{ 'text9', 'Text' },
},
TraySkill= {
{ 'name', 'String' },
{ 'system', 'String' },
{ 'description', 'Text' },
{ 'targets', 'String' },
{ 'affects', 'String' },
{ 'activation', 'String' },
{ 'recharge_base', 'Integer' },
{ 'recharge_global', 'Integer' },
{ 'type', 'String' },
{ 'region', 'String' },
{ 'rank1rank', 'Text' },
},
Hangar= {
{ 'hangar', 'String' },
{ 'ship', 'String' },
{ 'faction', 'String' },
},
Hangartable= {
{ 'hangar', 'String' },
{ 'role', 'String' },
{ 'squad', 'String' },
{ 'r_energy', 'String' },
{ 'vr_energy', 'String' },
{ 'ur_energy', 'String' },
{ 'r_torp', 'String' },
{ 'vr_torp', 'String' },
{ 'ur_torp', 'String' },
{ 'r_ability', 'Text' },
{ 'vr_ability', 'Text' },
{ 'ur_ability', 'Text' },
{ 'faction', 'String' },
{ 'unlocked', 'String' },
},
SetBonus= {
{ 'Name', 'String' },
{ 'ReqItem', 'Integer' },
{ 'Passives', 'String' },
{ 'TraySkills', 'Page' },
{ 'Procs', 'Text' },
{ 'Abilities', 'Text' },
},
}
-- An ugly hack to prevent Cargo from puking when a string value happens to have
-- a field name in it. Breaks the value up into individual characters and builds
-- an SQL CONCAT around it so it doesn't get put back together until it actually
-- hits the database.
local function splode( pre, value, post )
local tokens = {}
if pre and pre ~= '' then table.insert( tokens, '"' .. pre .. '"' ) end
for chr in string.gmatch( value, '.' ) do
if chr == '"' then
table.insert( tokens, '"\\\""' )
else
table.insert( tokens, '"' .. chr .. '"' )
end
end
if post and post ~= '' then table.insert( tokens, '"' .. post .. '"' ) end
return "CONCAT(" .. table.concat( tokens, "," ) .. ")"
end
-- Determines if the ugly "splode" hack is going to be needed and uses it if so.
-- Otherwise, simply quotes the string and escapes quotes inside.
local function quoteOrSplode( frame, pre, value, post )
pre = pre or ''
post = post or ''
local tables = mw.text.split( frame.args.tables, ",", true )
for _, tableName in ipairs( tables ) do
local def = defs[tableName]
if def then
for _, field in ipairs( def ) do
local fieldName = string.lower( field[1] )
if string.find( string.lower( value ), fieldName ) then
return splode( pre, value, post )
end
end
end
end
return '"' .. pre .. string.gsub( value, '"', '\\"' ) .. post .. '"'
end
-- Finds the delimiter of a list field, provided the defs table above was used
-- in declaring the table or the defs correctly match the table declaration.
local function delimiter( tableName, fieldName )
local def = defs[tableName]
if def then
for _, field in ipairs( def ) do
if ( field[1] == fieldName ) then return field[3] end
end
end
return nil
end
-- Provides a workaround for difficulties in using multiple HOLDS or HOLDS NOT
-- in a single query. This uses an equivalent regex that will (in most cases)
-- give the same result as a HOLDS, but can be used multiple times in the same
-- query.
local function holds( frame, name, arg )
local mode = string.sub( name, 1, 1 )
local op = " RLIKE "
if mode == '!' then op = " NOT" .. op end
local tokens = mw.text.split( string.sub( name, 2, -1 ), ".", true )
local sep = delimiter( tokens[1], tokens[2] )
if sep == '\\' then
sep = '\\\\'
elseif sep == '|' then
sep = '\\\\\\|'
end
local pre = '(^|' .. sep .. ')'
local post = '(' .. sep .. '|$)'
arg = quoteOrSplode( frame, pre, arg, post )
return tokens[1] .. '.' .. tokens[2] .. '__full' .. op .. arg
end
-- Retrieves the value for a placeholder and correctly formats it, applying any
-- workarounds as needed.
local function value( frame, token )
local name = string.sub( token, 2, -2 )
local arg = frame.args[ name ] or ''
local mode = string.sub( name, 1, 1 )
if mode == '#' then
return tostring( tonumber( arg ) or '' )
elseif mode == '$' then
return quoteOrSplode( frame, pre, arg, post )
elseif mode == '%' or mode == '!' then
return holds( frame, name, string.gsub( arg, '"', '\\"' ) )
else
error( 'Placeholder names must start with "#", "$", "%", or "!": "' ..
name .. '" does not' )
end
end
-- Tokenizes a clause to allow placeholders to be correctly identified only
-- outside of quoted text.
local function tokenize( text )
local tokens = {}
local start = nil
local token = ''
local escaped = false
text = text or ''
for chr in string.gmatch( text, "." ) do
if start then
token = token .. chr
if chr == start and not escaped then
table.insert( tokens, { start = chr, value = token } )
token = ''
start = nil
end
escaped = ( chr == '\\' and not escaped )
else
if string.match( chr, '[%?"\']' ) then
table.insert( tokens, { start = nil, value = token } )
token = chr
start = chr
else
token = token .. chr
end
end
end
if token ~= '' then
table.insert( tokens, { start = start, value = token } )
end
return tokens
end
-- Processes placeholders in a clause using syntax somewhat similar to
-- parameterized queries, formatting values supplied in the matching #invoke
-- args and swapping those values with their placeholders
local function parameterize( frame, arg )
local tokens = tokenize( frame.args[arg] )
local clause = ''
for _, token in ipairs( tokens ) do
if ( token.start == '?' ) then
clause = clause .. value( frame, token.value )
else
clause = clause .. token.value
end
end
return clause
end
local p = {}
-- invokes #cargo_declare for the given table if there is a matching
-- table name in defs above
function p.declare( frame )
local def = defs[frame.args['_table']]
if def then
local args = { '_table=' .. frame.args['_table'] }
for _, field in ipairs( def ) do
if field[3] then
table.insert( args, '|' .. field[1] .. '=List (' .. field[3] .. ') of ' .. field[2] )
else
table.insert( args, '|' .. field[1] .. '=' .. field[2])
end
end
return frame:preprocess( '{{#cargo_declare:' .. table.concat( args ) .. '}}' )
end
end
-- performs a query in a manner similar to #cargo_query with
-- format=template specified
function p.query( frame )
local results = mw.ext.cargo.query( frame.args.tables, frame.args.fields, {
where = parameterize( frame, 'where' ),
join = frame.args.join,
groupBy = frame.args.groupBy,
having = parameterize( frame, 'having' ),
orderBy = frame.args.orderBy,
limit = frame.args.limit,
offset = frame.args.offset,
} )
local out
if #results > 0 then
out = frame.args.intro or ''
for index = 1, #results, 1 do
out = out .. '\n' .. frame:expandTemplate{ title = frame.args.template, args = results[index] }
end
out = out .. '\n' .. ( frame.args.outro or '' )
else
out = frame.args.default or ''
end
if frame.args.debug == "yes" then
out = out .. "\n----\n" .. frame.args.where .. "\n----\nResults: " .. #results
end
return out
end
function p.debug( frame )
local out = '<pre>\n'
out = out ..'tables = ' .. frame.args.tables .. "\n"
out = out ..'fields = ' .. frame.args.fields .. "\n"
out = out ..'where = ' .. parameterize( frame, 'where' ) .. "\n"
out = out ..'join = ' .. frame.args.join .. "\n"
out = out ..'groupBy = ' .. frame.args.groupBy .. "\n"
out = out ..'having = ' .. parameterize( frame, 'having' ) .. "\n"
out = out ..'orderBy = ' .. frame.args.orderBy .. "\n"
out = out ..'limit = ' .. frame.args.limit .. "\n"
out = out ..'offset = ' .. frame.args.offset .. "\n"
out = out .. '</pre>\n'
return out
end
return p