Cargo extension general discussion

[CXuesong]

Forked from #78

Let's use this thread for discussing the general nature of MediaWiki Cargo extension.

[CXuesong]

I'm not sure if there is a consistent approach to represent DateTime in Cargo query. For now I can see there are following valid expression

# ODBC
WHERE ReleaseDate < {d'2020-1-1'}
WHERE ReleaseDate < {dt'2020-01-01T00:00:00.000Z'}
# string literal
WHERE ReleaseDate < '2020-1-1'
WHERE ReleaseDate < '2020-01-01T00:00:00.000Z'
[yet more to discover]

Do you have any suggestion on this? For now I'd prefer this one: {dt'2020-01-01T00:00:00.000Z'}. This looks "correct" to me but frankly speaking I haven't used this kind of literal before.

c.f. https://stackoverflow.com/questions/33228765/how-do-i-specify-date-literal-when-writing-sql-query-from-sql-server-that-is-lin

[BrunoBlanes]

I think @RheingoldRiver will have a proper answer to this, but I've been using the standard ISO 8601 string literal and haven't had any problems.

[CXuesong]

Yeah. So the problem is whether {dt'...'} is actually widely supported (I've tried in Cargo and it works.). I'd like to distinguish between DateTime and String literal explicitly, if possible.

[RheingoldRiver]

Cargo DateTime values like 2020-01-01 or even 2020 should be accepted since Cargo DateTime objects are of arbitrary precision...this makes it kind of hard to do a data type other than String literal, unless you wanted to implement something custom I think.

FWIW I do have several use cases on my wiki of DateTime values of e.g. 2021 where we know an event is taking place next year but so far don't yet know the exact date., for example the field DateStartFuzzy here https://lol.gamepedia.com/Special:CargoTables/Tournaments, but they are for internal use only for the most part so they wouldn't really be used by API users.

[CXuesong]

Is there concept of "Required field" in Cargo? If not, I assume all the fields are optional (i.e. can be null).

[RheingoldRiver]

Just tables and fields are required (and join_on if you have more than 2 tables)

[CXuesong]

I mean, can a field has NULL value? Can we configure it otherwise (so NULL is not allowed)?

In relational DB you can configure schema so an INT field can have NULL value (or not).

NULL | NOT NULL Determine whether null values are allowed in the column. NULL is not strictly a constraint but can be specified just like NOT NULL. [1]

[RheingoldRiver]

Oh, yeah, sorry for my misunderstanding. Cargo supports mandatory fields. It also supports unique fields, and you can set a list of allowed values. There's some docs on it here: https://www.mediawiki.org/wiki/Extension:Cargo/Storing_data#Declaring_a_table

[CXuesong]

Some questions on Cargo lists (e.g. List (,) of String)

Q1: Are string-based operations supported for Cargo lists? I've tried one (CONCAT) but it doesn't work:
https://lol.gamepedia.com/Special:ApiSandbox#action=cargoquery&format=json&tables=Skins&fields=Name%2CArtists%2CCONCAT(Artists%2C%20'%2Ctest')%3DArtists1

More generally, what operations are supported for Cargo lists, asides from HOLDS and HOLDS LIKE? For example, can I retrieve the list element count of a Cargo list?

Q2: Should whitespaces around list delimiter in effect get ignored?
Check out the query result: https://lol.gamepedia.com/Special:ApiSandbox#action=cargoquery&format=json&tables=Skins&fields=Name%2CArtists&where=Name%20%3D%20'Arcanist%20Kog''Maw'

[RheingoldRiver]

Q1: So the actual string field here is Artists__FULL, the field Artists doesn't exist except to serve in the syntax sugar for HOLDS. The list-type "field" is actually an "alias" for two things: first, a string field Fieldname__FULL that holds the exact string value (I think? actually it may be text, and unindexed). Second, there's actually a second table, which gets joined to the first when you do HOLDS. You may also manually join this table: You can see me doing that explicitly here - https://lol.gamepedia.com/Module:TeamPLHQuery - LeagueGroups.Leagues is a list-type table that I join to its parent LeagueGroups, and I then join its value to Tournaments.League. The value is available via LeagueGroups__Leagues._value.

Q2: I believe so, however not in the actual string field I mentioned above, only when doing HOLDS/HOLDS LIKE.

[CXuesong]

What is the timezone of the Datetime data type? UTC, server local, or unspecified?

[RheingoldRiver]

Unspecified

[CXuesong]

It seems that HOLDS and HOLDS LIKE does not support conjunction predicates (AND). For example, where=(Artists HOLDS 'David Villegas') AND (Artists HOLDS LIKE '% Studio') does not yield any result: https://lol.gamepedia.com/Special:ApiSandbox#action=cargoquery&format=json&tables=Skins&fields=Name%2CArtists&where=(Artists%20HOLDS%20'David%20Villegas')%20AND%20(Artists%20HOLDS%20'West%20Studio')

But there is actually a record containing both of them

On the other hand, disjunction (OR) works: https://lol.gamepedia.com/Special:ApiSandbox#action=cargoquery&format=json&tables=Skins&fields=Name%2CArtists&where=(Artists%20HOLDS%20'David%20Villegas')%20OR%20(Artists%20HOLDS%20'West%20Studio')

[CXuesong]

Okay it seems that the issue has been resolved in a later Cargo build: https://www.mediawiki.org/wiki/Extension_talk:Cargo/Archive_April_to_July_2019#Querying_data_in_cargo_with_the_%22HOLDS%22_command_combining_with_%22AND%22_clause_[RESOLVED]

[RheingoldRiver]

Even if Cargo says it works, I don't trust any WHERE clause with HOLDS and an AND - I'd raise a warning that this is risky if you have a compound WHERE clause using HOLDS. In this case it's much much much safer to do the explicit join instead of using the syntax sugar (unfortunately the explicit join is kind of messy and not well documented).