Please remember to make use of the DML Wiki Manual of Style and Code of Conduct during your stay.

 Actions

Help

Help:Extension - Cargo

From Dragon Mania Legends (DML) Wiki

link=Category:{{{base}}} (Element)
The following page or feature is intended for use by Dragon Mania Legends (DML) Wiki Staff (Bureaucrats) who work with advanced MediaWiki Extension functionality.
Sticker - Blush.png
The following article is in development.
The information may be incorrect or incomplete, and may change without warning.

Helpful Resources

Important notes

  • Where possible, avoid LIKE with % at the start of the pattern, which does a full table scan.
  • Avoid the use of LIKE in HOLDS.

Creating Tables & Storing Data

See the documentation above for fulsome details on how to use Cargo, this just gives a super brief reminder!

Using Cargo

Declaring/Creating DB table

The declaration to create the Cargo table, must go between <noinclude></noinclude> tags in the template that is used on pages you desire to query.

The following tells Cargo to create a table in the database named "Events," with the Events template parameters as column names (note these names can be whatever is desired, in later steps Cargo is told what actual parameter names get stored in them).

Note: SQL terms like "update" cannot be used as parameters/column names, so instead the first letter of the table name was used; e.g., for the "DragonInfos" table, "dupdate" was used instead of "update". 

{{#cargo_declare:
_table = EventInfos
|started = Wikitext
|duration = Wikitext
|type = Wikitext
|prize = Wikitext
|vip = Wikitext
|dragon = Wikitext
|reward = Wikitext
|ended = Wikitext
|ticket = Wikitext
|ticketType = Wikitext
}}

Storing Data in Cargo tables

Now we tell Cargo what to store in each column by assigning template parameters. This must go between <includeonly></includeonly> tags. And, the lines must the lines above to prevent errors, (as of v2.8 leaving one on/off causes a bug (duplicate lines in tables). 

{{#cargo_store:
_table = EventInfos
|started = {{{started|}}}
|duration = {{{duration|}}}
|type = {{{type|}}}
|prize = {{{prize|}}}
|vip = {{{vip|}}}
|dragon = {{{dragon|}}}
|reward = {{{reward|}}}
|ended = {{{ended|}}}
}}


Creating and Populating the Table

Cargo table creation/re-creation is not automatic, rather after adding the code to the relevant template to create a Cargo table for, revisit the template page (viewing) and use page actions to select "Create/Recreate table/data". Some tables cannot be created/recreated from the interface (such as _pageData or _fileData tables) if the number of pages to create for is too large (in the thousands), in which case, there are command line scripts in the Cargo/maintenance directory. Table population takes place via the job queue and may take a while to fully populate.

Cargo Tables on this Wiki

Cargo tables on this wiki are found at Special:CargoTables. At this time, only the cargo-admin user group can create, recreate, delete, or switch-in Cargo tables.

Query Reference

Once a Cargo table is made and populated, it can be queried. Note: Unlike SQL, when using Cargo, table selection comes first, followed by column(s) (referred to as "field(s)") selection.

Allowed functions

Math functions COUNT, FLOOR, CEIL, ROUND, MAX, MIN, AVG, SUM, POWER, LN, LOG
String functions CONCAT, IF, LOWER, LCASE, UPPER, UCASE, SUBSTRING, TRIM, FORMAT
Date functions NOW, DATE, YEAR, MONTH, DAYOFMONTH, DATE_FORMAT, DATE_ADD, DATE_SUB, DATEDIFF


Auto-created Table Columns

Field Description
_pageName The page name for this row of values.
_pageTitle The page title (without namespace) for this row of values.
_pageNamespace The numerical ID for the page's namespace (for the row of values).
_pageID The MediaWiki ID for the page (for the row of values).
_ID The unique ID for the row.

SELECT & FROM

  • SELECT - Selects the columns by column name to include in (or make available for) the output.
    • The Cargo name for this is tables
  • FROM - Specifies which Cargo table to select the items from.
    • The Cargo name for this is fields

Note LIMIT is being used here (though explained below) to limit the result count to prevent excessive result numbers.

SQL: 

SELECT _pageName, type
FROM DragonInfos
LIMIT 2;

Cargo: 

{{#cargo_query:
tables=DragonInfos
|fields=_pageName, type
|limit=2
}}


DISTINCT/group by

DISTINCT does not work in Cargo (except within a function like COUNT()), use "group by" to eliminate duplicates instead.

AS/Alias

  • AS - Sets a column label alias (or table header alias) on the result set only.
  • Important: when format=template is used, parameter values are retried by the alias set. So if type=Type then {{{Type|}}} is used to retrieve the value. This also allows compound query values with the same name to be set as different parameter names for template use.

SQL: 

SELECT _pageName AS 'Dragon', type AS 'Type'
FROM DragonInfos
LIMIT = 2;

Cargo: 

{{#cargo_query:
tables=DragonInfos
|fields=_pageName=Dragon, type=Type
|limit=2
}}


Conditions

WHERE

Where syntax is displayed directly below, and the sections following that also gives examples for:

  • WHERE + AND, to look for rows where ALL conditions are met.
  • WHERE + OR, to look for rows where ANY conditions are met.
  • WHERE + IN, is a list-based shorthand for OR, to look for rows where ANY conditions are met (when there are multiple conditions).
  • WHERE + NOT IN, to look for rows that DON'T meet ANY of a list of multiple conditions.
  • WHERE + NOT, to look for rows NOT matching a condition.


WHERE using numbers

  • WHERE - Sets conditions/criteria for selection

SQL: 

SELECT _pageName, cost
FROM ObtainingInfos
WHERE cost > 100
LIMIT 2;

Cargo: 

{{#cargo_query: tables = ObtainingInfos
|fields = _pageName, cost
|where = cost > 100
|limit = 2
}}


WHERE using text

SQL: 

SELECT _pageName AS Dragon, type AS Type
FROM DragonInfos
WHERE type = 'Epic';

Cargo: 

{{#cargo_query: tables= DragonInfos
|fields = _pageName=Dragon, type=Type
|where = type = 'Epic'
|limit = 2
}}


WHERE + AND
  • WHERE + AND, looks for rows where ALL conditions are met.

SQL: 

SELECT _pageName=Dragon, type=Type, base=Base
FROM DragonInfos 
WHERE type = 'Epic' 
  AND base = 'Fire'
LIMIT 2;

Cargo: 

{{#cargo_query: tables = DragonInfos
|fields = _pageName=Dragon, type=Type, base=Base
|where = type = 'Epic'
  AND base ='Fire'
|limit = 2
}}


WHERE + OR
  • WHERE + OR, looks for rows where ANY conditions are met.

SQL: 

SELECT pageName=Dragon, type=Type, base=Base
FROM DragonInfos
WHERE type = 'Tyrant'
   OR type = 'Ancient'

Cargo: 

{{#cargo_query: tables = DragonInfos
|fields =_pageName=Dragon, type=Type, base=Base
|where = type = 'Tyrant'
 OR type = 'Ancient'
|limit = 2
}}


WHERE + IN
  • WHERE + IN - Is a list-based shorthand for OR, to look for rows where ANY conditions are met (when there are multiple conditions).
    • Data can be filtered based on a list instead of chaining together multiple unwieldy OR statements.
{{#cargo_query: tables = DragonInfos
|fields = _pageName, type, base
|where = type IN ('Ancient', 'Tyrant', 'Divine', 'Primal')
|limit = 10
}}
WHERE + NOT IN
  • WHERE + NOT IN, to look for rows that DON'T meet ANY of a list of conditions.
{{#cargo_query: tables = DragonInfos
|fields = _pageName, type, base
|where = type NOT IN ('Ancient', 'Tyrant', 'Divine', 'Primal')
|limit = 10
}}


WHERE + NOT
  • WHERE + NOT, to look for rows NOT matching a condition.
{{#cargo_query: tables = DragonInfos
|fields = _pageName, type, base
|where = NOT type = 'Ancient'
|limit = 10
}}


LIKE/Wildcards

  • _ - Underscore is wildcard for single character
  • % - Percent is wildcard for any number of characters.

LIKE example using _ (underscore)

SQL: 

SELECT _pageName, type, base
FROM DragonInfos
WHERE _pageName LIKE 'Di_e Dragon'
LIMIT 2;

Cargo: The following returns both Dice and Dile Dragon's name, rarity/type, and base element. 

{{#cargo_query: tables = DragonInfos
|fields = _pageName, type, base
|where = _pageName LIKE 'Di_e Dragon'
|limit = 2
}}

LIKE example using % (percent)

SQL: 

SELECT _pageName, type, base
FROM DragonInfos
WHERE _pageName LIKE 'Fairy%'
LIMIT 2;

Cargo: The following returns both Fairy and Fairy Dust Dragon's name, rarity/type, and base element. 

{{#cargo_query: tables = DragonInfos
|fields = _pageName, type, base
|where = _pageName LIKE 'Fairy%'
|limit = 2
}}

Between

Between accepts two values that are either numbers, text, or dates.

BETWEEN example using numbers

SQL: 

SELECT _pageName, cost
FROM ObtainingInfos
WHERE cost BETWEEN 700 AND 900

Cargo: 

{{#cargo_query: tables = ObtainingInfos
|fields = _pageName, cost
|where = cost BETWEEN 700 AND 900
|limit = 2
}}


BETWEEN example using letters

SQL: 

SELECT _pageName, cost
FROM ObtainingInfos
WHERE _pageName BETWEEN 'M' AND 'N';

Cargo: 

{{#cargo_query: tables = ObtainingInfos
|fields = _pageName, cost
|where = _pageName BETWEEN 'M' AND 'N'
|limit = 2
}}


IS/IS NOT - NULL

Unknown values are indicated by NULL. It's not possible to test for NULL values using comparison operators, (e.g., = and !=) which is why IS/NOT NULL is useful.


IS NOT NULL example

SQL: 

SELECT _pageName, base
FROM DragonInfos
WHERE base IS NOT NULL
LIMIT 2;

Cargo: This example returns Dragons that are not Clan Dragons, whose 'base' is NULL. 

{{#cargo_query: tables = DragonInfos
|fields = _pageName, base
|where = base IS NOT NULL
|limit = 2
}}


IS NULL example

SQL: 

SELECT _pageName, base
FROM DragonInfos
WHERE base IS NULL
LIMIT 2;

Cargo: This example returns only Clan Dragons, whose 'base' is NULL. 

{{#cargo_query: tables = DragonInfos
|fields = _pageName, base
|where = base IS NULL
|limit = 2
}}


Order & Volume

Limit

SQL: 

SELECT name
FROM dragons
LIMIT 2;

Cargo: 

{{#cargo_query: tables = DragonInfos
|fields = _pageName, type
|limit = 2
}}


ORDER BY

  • ASC - Sort results in ascending order (default, needs not be specified).
  • DESC - Sort results in descending order.

SQL: 

SELECT _pageName, type
FROM DragonInfos
LIMIT 2
ORDER BY _pageName DESC;

Cargo: The following sorts from Z to A: 

{{#cargo_query: tables = DragonInfos
|fields = _pageName, type
|limit = 2
|order by = _pageName DESC
}}


Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value:


Column References

SQL: 

SELECT COUNT(*) AS 'Total Dragons', type AS 'Type'
FROM DragonInfos
GROUP BY 2
ORDER BY 1
LIMIT 2;

Cargo: This example creates a new column (not in the DragonInfos Cargo database table) named "Total Dragons" and populates it with the grouped (by type) count of each type/rarity. The table is then ordered by Note: Unnamed, and only numbered, columns return a Cargo error and must always be specified by name per the error message received. 

{{#cargo_query: tables = DragonInfos
|fields = COUNT(*)=Total Dragons, type=Type
|group by = type
|order by = Total Dragons
|limit = 10
}}

COUNT

SELECT type, 
   COUNT(*) 
FROM DragonInfos 
WHERE type = 'Epic'

Cargo: 

{{#cargo_query: tables = DragonInfos
|fields = COUNT(*)
|where = type = 'Epic'
}}


SUM

SQL: 

SELECT SUM(cost)
FROM ObtainingInfos;

Cargo: The name of a column is used as an argument in which SUM returns the sum of all values in that column of the results set (in this case, the total cost of all dragons, which is a bit misleading as they represent multiple currency types). 

{{#cargo_query: tables = ObtainingInfos
|fields = SUM(cost)
}}


MAX

Note: Will not work on mixed data fields, such as cost, which contains wikitext. SQL: 

SELECT MAX(health3) 
FROM DragonInfos;

Cargo: The name of a column is used as an argument in which MAX returns the the largest value in that column of the results set (in this case, the total cost of all dragons, which is a bit misleading as they represent multiple currency types). 

{{#cargo_query: tables = DragonInfos
|fields = MAX(health3)
}}


GROUP BY

Groups records in a result set by identical values in one (or more) columns and is often used with aggregate functions to query the information in similar records.

SQL: 

SELECT type, 
   COUNT(*) 
FROM DragonInfos
GROUP BY type
LIMIT 2;

Cargo: 

{{#cargo_query: tables = DragonInfos
|fields = type, COUNT(*)
|group by = type
|limit = 2
}}

MIN

Note: Will not work on mixed data fields, such as cost, which contains wikitext. SQL: 

SELECT MIN(health3) 
FROM DragonInfos;

Cargo: 

{{#cargo_query: tables = DragonInfos
|fields = MIN(health3)
}}


AVG

Note: Will not work on mixed data fields, such as cost, which contains wikitext. SQL: 

SELECT AVG(health3)
FROM DragonInfos;

Cargo: 

{{#cargo_query: tables = DragonInfos
|fields = AVG(health3)
}}


HAVING

Used to with GROUP BY to further filter the result groups provided. Must be declared after a GROUP BY clause but before ORDER BY or LIMIT clauses.


SQL: 

SELECT type, 
   COUNT(*) 
FROM dragons 
GROUP BY type
HAVING COUNT(*) > 50;

Cargo: Shows only the rarities/types of dragons that have greater than 50 results. 

{{#cargo_query: tables = DragonInfos
|fields = type, COUNT(*)
|group by = type
|having = COUNT(*) > 50
}}


ROUND

SQL: 

SELECT health3
   ROUND(AVG(health3), 2)
FROM DragonInfos;

Cargo: 

{{#cargo_query: tables = DragonInfos
|fields = ROUND(AVG(health3),2)
}}

CONCAT

{{#cargo_query:
tables = DragonInfos
|fields =_pageName, CONCAT(base, " ",second, " ", third) = Elements
|where=type = 'Epic'
|limit = 5
}}

Note: If any concatenated fields are empty, none display. Better to send the raw elements to a template, and in that template use something like {{forEach|{{{base|}}},{{{second|}}},{{{third|}}}|ei}}.

Date concatenation example
CONCAT(DATE_FORMAT(startDate,'%d.%m.%Y'), DATE_FORMAT(startDate, ' %H:%i:%s'))

Multiple Tables

Sticker - Blush.png
The following article is in development.
The information may be incorrect or incomplete, and may change without warning.

Outer Join (join on)

Specifies how to connect a/each pair of tables and corresponds to LEFT OUTER JOIN...ON clauses in SQL, though pairs of tables are separated by commas rather than "AND".

SQL: 

SELECT EventInfos._pageName, EventInfos.prize, _pageData._numRevisions
FROM EventInfos
LEFT JOIN _pageData
  ON EventInfos._pageName = pageData._pageName;
LIMIT 5;

Cargo: 

{{#cargo_query: tables = EventInfos, _pageData
|join on = EventInfos._pageID = _pageData._pageID
|fields = EventInfos._pageName, EventInfos.prize, _pageData._numRevisions
|limit = 5
}}

Note: Always best to join on _pageID which is the MediaWiki ID for a page, since there is occasionally a duplicate row issue in Cargo, though they get different IDs. Joining this way prevents any duplicates from interfering with the query.

Format using CONCAT: 

{{#cargo_query: tables = EventInfos, _pageData
|join on = EventInfos._pageID = _pageData._pageID
|fields = CONCAT( '[[', EventInfos._pageName, ']]' )=Event, CONCAT( '{{di|', EventInfos.prize, '|2}}' )=Dragon Prize, _numRevisions
|limit = 5
}}


Cargo-Specific Commands

HOLDS variations

Sticker - Blush.png
The following article is in development.
The information may be incorrect or incomplete, and may change without warning.

All HOLDS-related commands are exclusively used for fields that contain a list of values (includes HOLDS, HOLDS NOT, HOLDS LIKE). This is essentially a shortcut for simple queries to find a value against a list.

The HOLDS command allows AND but does not allow OR. So, If you need anything more complex, like OR, IN, or NOT IN you should query the full (child) table of the "list-type" field instead. Also, avoid using LIKE with HOLDS (thanks to the river.me blog for some details paraphrased here).

Important Notes:

  • List-type fields are not really lists, they are actually a field like cargocats__FULL of type String with all values concatenated together by its declaration delimiter: |cargocats = List (,) of Wikitext string.
  • SQL does not support a list type, instead Cargo actually stores these in the field noted above and creates a child table to store its values.
  • So if the DragonInfos table has a "list-type" field called cargocats, Cargo creates a child table called: DragonInfos__cargocats. To query this table, it must be included in tables, then have its parent cell ID joined to the matching child table row ID.

Joining parent to child: 

{{#cargo_query: tables = DragonInfos, DragonInfos__cargocats
|join on=DragonInfos._ID = DragonInfos__cargocats._rowID
}}
  • _ID is the parent table's primary key.
  • _rowID is the child table's foreign key, the matching parent row ID.

List tables (that are no coordinates) have 3 columns:

  • _rowID
  • _value (the value to query)
  • _position (1-indexed)

To query on a child table value: 

{{#cargo_query:
tables = DragonInfos, DragonInfos__cargocats
|join on = DragonInfos._ID = DragonInfos__cargocats._rowID
|fields = _pageName, DragonInfos.cargocats
|where = DragonInfos__cargocats._value="Category:Event Dragons"
|format = table
|limit = 5
}}

Notes:

  • In the fields list, omit __FULL (the extension adds it, unless you're using HOLDS in the where clause, where it gives the ._value of the child table).
  • Query on the child table name (e.g., DragonInfos__cargocats), against its value ._value fields.
  • It's possible to join on a _value.

HOLDS

TBD

  • Remember, you cannot use OR with HOLDS, should not use LIKE, though can use AND. For this reason, it is probably better to query the __FULL field as noted above.

HOLDS LIKE

TBD

  • Using this is not the best idea, see above.

HOLDS NOT

TBD

HOLDS NOT LIKE

TBD

  • Using this is not the best idea, see above.

WITHIN

Applies to hierarchy fields that hold a single value, allowing matching against not only a value, but all of its children's values as defined in that hierarchy. TBD


HOLDS WITHIN

Works on hierarchy fields that hold not a single value but a list of values. TBD


NEAR

Used for coordinates.


MATCHES (fulltext search)

Used on columns that have the "Searchtext" type, where a standard text search can be done on the column's contents (e.g., similar to MATCH ... AGAINST in MySQL, which is why it only works on MySQL). By default this displays result output similar to MediaWiki's standard search functionality.

Generally, the only "Searchtext" type field is the _pageData._fullText field (see Storing page data).

Note: The following also works: |where=_pageData._fullText LIKE "%searchterm%"

Query: 

{{#cargo_query:
tables = _pageData
|fields = _pageName=Page, _fullText=Search results
|where = _categories HOLDS 'Base Dragons' AND _fullText MATCHES 'underbelly'
|limit = 5
}}


Note: It's important to know that MediaWiki does not update categories right away, so this method of category selection is unreliable. Instead, to force category update on page save, a "cargocats" column has been added to cause pages to those categories to be logged on page save.

Multiple matches and special characters: 

{{#cargo_query: tables = EventInfos, _pageData
|join on = EventInfos._pageID = _pageData._pageID
|fields = EventInfos.type=Type, EventInfos._pageName=Event Name, EventInfos.started=Started, EventInfos.duration=Duration, EventInfos.ended=Ended
|where = _pageData._fullText MATCHES '"dragon{{=}}Sunrise" "prize{{=}}Sunrise" "dungeonoffer{{=}}Sunrise" "di{{!}}Sunrise{{!}}2"'
|order by = EventInfos.started DESC
|limit = 5
}}


Notes:

  • See w3Schools Joins.
  • Example of viewing the full text content of a page via the API, after pressing make request.
  • For MATCHES, which functions like SQL MATCH AGAINST, see Boolean Full-Text Searches.
    • LEFT JOIN (equivalent to Cargo join on): Returns all results/records from left table, and matched records from right table.

Other Examples

Example: 

{{#cargo_query: tables = EventInfos
|fields = _pageTitle=Title, type, started, duration, prize, vip, reward, ended
|where = _pageTitle LIKE '%(21/%'
|limit = 5
|offset = 0
|named args = yes
|default = No Events to display
|more results text = <!-- no more results text -->
}}

Cargo Issues/Bugs

There are a few bugs with Cargo on version 3.2. To create tables, headers must be set in |intro=, and footers set in |outro=. However, Cargo's "Modify Query" function (that appears automatically on paginated result sets) doesn't respect |intro= or |outro=, and unchecks namedargs=yes (which is equivalent to the checkbox) by default, meaning that formatting and values are dropped if a user modifies the query using this utility. For this reason, it has been disabled on this wiki to prevent issues.

Other Bugs

See the following for other potential bugs:

Retrieved from "https://www.dragon-mania-legends.wiki/w/index.php?title=Help:Extension_-_Cargo&oldid=134790"
This Wiki is created by players for other players and is not maintained by, or affiliated with, the game maker (Gameloft).
Cookies help us deliver our services. By using our services, you agree to our use of cookies.
More information