Module:Cargo

From Star Trek Online Wiki
Jump to: navigation, search
The following documentation is transcluded from Module:Cargo/doc (edit):

declare[edit source]

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[edit source]

#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[edit source]

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 to tables
  • join is used instead of join on
  • groupBy is used instead of group by
  • orderBy is used instead of order by
  • This function always behaves as if named args is yes
  • Very important: this module provides important special parameterization features where and having. 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[edit source]

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 simulate HOLDS and ! to simulate HOLDS 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 its defs 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 or holdsnot here is recommended for readability.

-- 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
        { 't5uhull', 'Integer' }, -- Has hull strength (T5-U)
        { '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
        { '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', 'String' },
        { 'vr_ability', 'String' },
        { 'ur_ability', 'String' },
        { '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