Extension talk:Cargo

From MediaWiki.org
Jump to navigation Jump to search

Data in cargo table not updating after I renamed a column title[edit]

I changed the name of a column header and changed the data type from boolean to text, now when I try to add text to the new field it will not save. The new header name also disappeared from the drilldown menu... I renamed all the references to the old header in the template, am I missing somewhere else where I have to rename the old column header?

Did you recreate the table? And does the listing for that table look correct in its Special:CargoTables page? Yaron Koren (talk) 21:21, 13 May 2020 (UTC)

Cargo equivalent to SMW's #show?[edit]

I'm sorry if this is a stupid question or if I missed something obvious. I've been trying to make it where infoboxes on my wiki will display data from another specified page so that information that changes there will be changed on other pages automatically, something I know how to do using Semantic MediaWiki's #show function. However, I can't seem to find any information on how to do anything like that with Cargo, and I don't know if it's because it doesn't exist or because I don't know how to look for it. Does an equivalent function exist in Cargo? Where can I learn how to use it? (P.S. In case you're wondering why I would want to do this over just updating the relevant pages, it's for ranked taxonomy in an ongoing evolution game. Basically, that means information potentially relevant to hundreds of pages is expected to change semi-regularly.) Disgustedorite (talk) 00:55, 14 May 2020 (UTC)

There's no equivalent for #show, although #show itself is not really necessary - it's just slightly different syntax for #ask. Similarly, you can just call #cargo_query to get a specific field value for a specific page. If that seems like too complex a call to make every time, you could always create a "show" template that wraps around #cargo_query and takes in the necessary values: table name, field name and page name, or some subset of those. Yaron Koren (talk) 12:10, 14 May 2020 (UTC)

Cargo equivalent to SQL arithmetic operations between 2 or more columns?[edit]

Greetings.

Considering the following SQL query:

SELECT column1 + column2 AS "add", column3 * column4 AS "multiply" FROM table1;

Is it possible to achieve such behavior in Cargo?

-- N tonio36 (talk) 03:34, 15 May 2020 (UTC)

I don't think so, unfortunately. If there are specific sums, products, etc. you want to display, the only way to do it may be to have the template store those as separate fields - calculating them using #expr from the ParserFunctions extension. Then it's just a matter of querying those fields. Yaron Koren (talk) 04:16, 15 May 2020 (UTC)

Title disambig text[edit]

For Cargo. I'm using this template with the following code |history={{Title disambig text|{{PAGENAME}} }}. But when you go to special:CargoTables/Articles, the "history" column appears empty. How can I fix that? The reason is that in my personal wiki I develop fictional content and to differentiate one from another that have the same name, I add a title in brackets. --Hispano76 (talk) 19:26, 16 May 2020 (UTC)

Does {{Title disambig text|{{PAGENAME}} }} display what you expect (i.e. the part of the page title which is within round brackets) when it's just on its own on the page? Does plain text save to the Cargo table? Is history correctly declared in the template? Jonathan3 (talk) 20:50, 16 May 2020 (UTC)
when it's just on its own on the page? yes
Does plain text save to the Cargo table? no, is blank
Is history correctly declared in the template? yes
Greetings. --Hispano76 (talk) 21:41, 16 May 2020 (UTC)
Could you cut and paste here your template for the Articles table? Jonathan3 (talk) 21:47, 16 May 2020 (UTC)
<includeonly>
{{Metaplantilla de avisos
|type   = aviso
|imagen = 
|texto  = Este artículo perteneciente a la historia alternativa '''{{Title disambig text|{{PAGENAME}} }}''', ha sido clasificado como de importancia '''{{{1|Desconocida}}}''' y de calidad '''{{{2|Desconocida}}}''' en la evaluación de artículos.
}}
{{#cargo_store:_table=Artículos|Historia={{Title disambig text|{{PAGENAME}}}}|Calidad={{{1|Desconocida}}}|Importancia={{{2|Desconocida}}}|Estado={{{3|Sin información}}} }}</includeonly>
<noinclude>{{#cargo_declare:_table=Artículos|Historia=String|Calidad=String|Importancia=String|Estado=String}}</noinclude>

For example (in spanish). Grettings. --Hispano76 (talk) 22:01, 16 May 2020 (UTC)

Multiple Cargo wikis from one database[edit]

Is it possible to set up one stand-alone database to store the cargo tables from multiple wikis? I was thinking of using separate prefixes per database, and it looks like it currently uses $wgDBprefix, but I have that overlapping in some wikis. Would it be possible to get $wgCargoDBprefix, or is there another option? -- Prod (talk) 22:09, 18 May 2020 (UTC)

Sure, it sounds possible to have multiple wikis use the same database as their Cargo database - and if each one had a different DB prefix, then you would prevent collisions. You couldn't have wikis then access each others' data, but you could query all the tables together outside of MediaWiki. Yaron Koren (talk) 13:51, 19 May 2020 (UTC)

How to query out a Cargo "Datetime" column in local time[edit]

Hi everyone, hope you're well.

I'd like to use #cargo_query to pull out page creation/modification times from the fields in the _pageData table (which I've switched on with $wgCargoPageDataColumns) in the user's local timezone.

What I've managed to do is whitelist CONVERT_TZ using the $wgCargoAllowedSQLFunctions array, and then use something like CONVERT_TZ(pd._creationDate, 'UTC', 'America/New_York')=Created in the Cargo query. This isn't a great solution because now I've hard-coded a time zone in the query, and my users might have a different local time zone in their preferences. What I was hoping for was a way to just default to showing DATETIME fields in the user's local time.

I see that another option might be to use {{#cargo_query: […] format=template}}, then, in the template, wrap the returned DATETIME field with the #timel parser function, and that may be the route I end up going in the end. I just miss the convenience of the "table" output format, because it saves a lot of work (thanks Yaron!).

What I'm wondering is whether there isn't a more Cargo-y way to do this, like something built in that I just missed.

It looks like what's missing is a variable like "{{CURRENTUSERTIMEZONE}}", which would return a value like 'America/New_York' for each user. If such a thing existed, you could pass that in to CONVERT_TZ() within #cargo_query, and I think you'd get exactly the output that you're looking for. I don't believe any such variable exists. I don't know what the best approach is to create it - maybe to use the MyVariables extension, and add this additional variable to it. It'll require some PHP programming... which hopefully you can do. Yaron Koren (talk) 00:38, 25 May 2020 (UTC)
OK, I was initially confused whether magic words or parser functions would be parsed in the field= parameter of #cargo_query, but now it seems obvious that they would be. I'm already using Extension:WikiUserInfo with the "dangerous functions" enabled on a non-public wiki, which provides a parser function version of what you describe, so maybe that's my solution.
As far as Cargo is concerned, do you think there's any merit in defining a DateLocal / DatetimeLocal field type, the results of which always come back formatted using the user's timezone preference, or maybe a tz= field parameter? Or a $wgCargoDateTimeUsesServerTZ to default to displaying Date / Datetime fields using the server's $wgLocaltimezone? I'm not pestering you with a feature request, just wondering if you think that would be a worthwhile addition, or out of the scope of what you believe Cargo's job should be with respect to DATETIME fields. --Ernstkm (talk) 05:00, 27 May 2020 (UTC)
That's an interesting question, of when (if ever) it makes sense for times to be displayed in the user's time zone, rather than the wiki's, if there's a difference between the two. You mentioned a few options: per field, with some new types; per wiki, with a global variable; and of course, per query field, with that CONVERT_TZ() approach you discovered (I'm glad that works, by the way, even if it's "dangerous"). I can think of two more: per user, with a new preference like "Display Cargo times in my timezone"; and per query, with a new parameter to #cargo_query.
I guess the question is, what's the logic to determine whether any specific time should be shown in the user's time zone or not? Does it depend mostly on whether this is an event that someone in a different time zone could participate in? And is there the possibility that having too "clever" a solution could lead to user confusion about which times are in their time zone and which are not? Yaron Koren (talk) 13:46, 27 May 2020 (UTC)

Write to Cargo tables in Lua[edit]

Cargo is amazing. Thank you! Just one issue -- Is it possible to write to Cargo tables in Lua? My module is used in the same template that defines the table. It iterates over an unlimited number of infobox-like numbered params (entry1=foo, entry2=bar, etc.) so it would be tedious to do the storage via wikitext. I think I could get away with the Lua module returning the {{#cargo_store}} code along with the generated wikitext... but that seems hacky. MusikAnimal talk 05:39, 24 May 2020 (UTC)

I'm glad you're enjoying Cargo! I don't know that much about Lua programming, but I believe that, instead of passing in that wikitext (which would indeed be hacky), you should use the callParserFunction method to call #cargo_store. Eventually, I hope there will be native Lua methods for #cargo_store and #cargo_declare, like there is for #cargo_query. Yaron Koren (talk) 00:40, 25 May 2020 (UTC)
I do not know Lua very well either, but that's exactly what I was looking for! It's essentially the same as a native call, from a programmer standpoint. What I wanted to avoid was all that string concatentation in building the wikitext. This allows me to pass in key/value pairs, so consider it solved. I have made note of this in docs with Special:Diff/3875998. Thanks! MusikAnimal talk 04:47, 25 May 2020 (UTC)

database error after renaming (moving) a template[edit]

I've created a template, called TEMPLATE_foo_bar, which has the following lines in it:

  • {{#cargo_store: _table = TABLE_foo_bar |PROPERTY_foo_bar={{{bar}}} }}

to store data and

  • {{#cargo_declare: _table = TABLE_foo_bar |PROPERTY_foo_bar=String (mandatory;unique) }}

to declare the respective table. This used to work well. Now I moved the template to something called TEMPLATE_foo_xyz and also replaced any occurrence of "bar" with "xyz". Nothing else was changed. After that I recreated the cargo table which also worked fine. But now if I try to use this template, it keeps throwing error messages at me, namely

A database query error has occurred. Did you forget to run your application's database schema updater after upgrading? Query: SELECT COUNT(*) FROM "cargo__TABLE_foo_xyz" WHERE PROPERTY_foo_xyz = 'TestEntry' Function: Wikimedia\Rdbms\Database::select Error: 42703 ERROR: column "property_foo_xyz" does not exist

Now the funny part is that the column in all lower case does not exist indeed. But it exists exactly as I spelled it in the template. If I enclose PROPERTY_foo_xyz with quotationmarks in the template, it works again. I checked the table and column names in the backend. They look exactly as they should. Again, the only thing I did was to move the template and replace 3 characters in the code with another 3 characters. What do you make of this? How to get this to work again? Thanks in advance.

--Mwarge (talk) 15:26, 26 May 2020 (UTC)

That's strange. What version of Cargo are you running? Yaron Koren (talk) 17:48, 26 May 2020 (UTC)
As of today it is the latest 2.5-release. Downloaded and installed after the error occured. I am not 100% sure which version I used before, but I guess I can figure that out if needed. Also I ran update.php before and after setting up 2.5. --Mwarge (talk) 19:43, 26 May 2020 (UTC)
What about the display of the table in Special:CargoTables, and in the Special:CargoTables subpage for that table - does it show up correctly in both? Yaron Koren (talk) 20:09, 26 May 2020 (UTC)
I am quite sure it did. In the meantime I renamed the template (and table/column) to yet something else and it did not work. Then I restored to the version I originally wanted to work, recreated the tables once again and voila - now it works. Special:CargoTables shows everything correctly. Also the subpage for that table looked correct as it always did after (re)creating the tables, eventhough there is a notice about some jobque (got a localized message and im not sure what the english version is). This notice was there before today and I am not quite sure what kind of job this might be. php runJobs.php does nothing. Anyhow, for the moment my problem got magically resolved. I'll keep an eye on that and report back if something happens. I moved/renamed quite some templates and tables along with their columns, so the issue might show up again somewhere else. Let me know if you'd like more info on something and thanks so far.
I have no idea why any of that happened, but I'm glad it's working now! Yaron Koren (talk) 21:48, 26 May 2020 (UTC)

Subquery expands right in other formats but not calendar[edit]

I have the following query with subqueries. When I run it in default format without specifying anything, the subquery expands as I would expect. When I add format=calendar the calendar days just contain the not-yet-executed query code. Here is the query:

{{#cargo_query:
tables= allDates
|fields= dt,
CONCAT( "Days: ", "{","{#cargo_query:
tables = SE
{{!}}fields= sName
{{!}}where = SE.sDay = DATE('", dt, "') AND SE._pageName = 'Gastroenterology Days Staff'
{{!}}default = ''}", "}", "<br>", "Nights: ", "{","{#cargo_query:
tables = SE
{{!}}fields= sName
{{!}}where = SE.sDay = DATE('", dt, "') AND SE._pageName = 'Gastroenterology Nights Staff'
{{!}}default = ''}", "}") = name, 
|where = dt >= DATE("2020-05-01") AND dt <= DATE("2020-05-31")
|limit=50
}}

Here is what shows in a calendar day box when I run it with |format=calendar, so here is a typed version but it will likely have

<pre> Days: {{#cargo_query:tables = SE|fields= sName|where = SE.sDay = DATE('2020-05-27') AND SE._pageName = 'Gastroenterology Days Staff'|default = ''}}&lt;br&gt;Nights: {{#cargo_query:tables = SE|fields= sName|where = SE.sDay = DATE('2020-05-27') AND SE._pageName = 'Gastroenterology Nights Staff'|default = ''}}

As I re-read this I realize there might be an easier way to write the query for calendar view (I am re-using this from a table view where I had to us a table with all dates to force blank boxes), but it still seems like a bug that the code that expands fine in other formats doesn't in calendar format. Thanks! Tenbergen (talk) 20:02, 27 May 2020 (UTC)

Wow, that's... impressive. :) I've never heard of constructing a #cargo_query call as the result of another #cargo_query call, although I can see how it would work. Yes, the "calendar" format doesn't parse the contents of its results, and it probably should. Still, I hope there's a less hacky solution than this... Yaron Koren (talk) 14:08, 28 May 2020 (UTC)
You mean others are not doing this? The ability to stick all sorts of things into a CONCAT statement is one of the features that have pulled me from SMW to Cargo, it adds a lot of flexibility in aggregating information. I love that I can do that on one line in Cargo, where if I can do it at all in SMW I would have to do it through a template. Tenbergen (talk) 21:42, 28 May 2020 (UTC)
Right, I've never heard of anyone else doing this. Now I'm curious: could you describe what it is that you're trying to display? I want to know if there's a real use case for this kind of approach. Yaron Koren (talk) 00:12, 29 May 2020 (UTC)
I have pages for shifts that contain multiple entries that encode the date and the name of the person who is to work the shift. In this case, one shift is a night shift and one a day shift. The people who use the data would like a calendar that lists both the day and night person. Of course it should still list the day even if the night is not covered. It seems like I should be able to pull this into one query and concatenate this, e.g. by calling the table twice and filtering one for day shifts and one for night shifts, but that is not working as expected, so also see my question below re joins. To work around that, I did it with the query inside a query.
Are you wondering whether there is a use case for query-in-query in calendars or in general? Tenbergen (talk) 20:46, 1 June 2020 (UTC)
In general. Do you know about the GROUP_CONCAT() function? It works in conjunction with "group by". It could potentially be a simpler approach to displaying what you're trying to display... Yaron Koren (talk) 20:51, 1 June 2020 (UTC)
I did not know about that function, thanks for the tip! It allows further functions inside it, so something like GROUP_CONCAT(if(_pageName = "Gastroenterology Days Staff", "Day: ", "<br>Night: "), sName) gives me the text I need. However, when I turn format=calendar, the <br> doesn't turn into the expected newline, but instead displays as "&ltbr&gt". Tenbergen (talk) 21:23, 1 June 2020 (UTC)
That's looking better already. :) Instead of the <br> tag, how about just putting two actual newlines there? I don't know if that will have any effect, but it's worth trying. Yaron Koren (talk) 21:59, 1 June 2020 (UTC)
You got it: the \n in the following adds the newline: GROUP_CONCAT(if(_pageName = "Gastroenterology Days Staff", "Day: ", "\nNight: "), sName). Thanks Yaron! Tenbergen (talk) 02:10, 2 June 2020 (UTC)
Great! I meant an actual newline, but if "\n" works, that's even better. It probably still makes sense to get the calendar format's parsing to match those of the other formats, but I'm relieved to hear that it's not necessary for this. And even more relieved that that original monstrosity can be avoided... Yaron Koren (talk) 02:28, 2 June 2020 (UTC)

Are those joins really left outers?[edit]

I ended up doing #Subquery expands right in other formats but not calendar because I am not sure that the join on actually works as a left outer join as described in here. I have two pages "day shift" and "night shift" that each hold multiple template calls that populate the sDays and sNames for those shifts. Some dates in that list only have either a "day" or "night" entry. If both are not present, the line doesn't show up. For example, I tried to re-write the query above to not use the query-in-query. When I write it as the following I don't get an line for a date where only the "day" has a record:

{{#cargo_query:
tables= allDates, SE = Days, SE = Nights
|join on=allDates.dt = Days.sDay, allDates.dt = Nights.sDay
|fields= dt, Days.sName = D, Nights.sName = N
|where = dt >= DATE("2020-05-01") AND dt < DATE("2020-06-02")
         AND (Days._pageName = "Gastroenterology Days Staff" or ISNULL(Days._pageName))
         AND (Nights._pageName = "Gastroenterology Nights Staff" or ISNULL(Nights._pageName))
|limit=50
|default=nothing here
}}

When I instead only pull in the Day part of the table I get the line. EG Still testing

{{#cargo_query:
tables= allDates, SE = Days
|join on=allDates.dt = Days.sDay
|fields= dt, Days.sName = D
|where = dt >= DATE("2020-05-01") AND dt < DATE("2020-06-02")
         AND (Days._pageName = "Gastroenterology Days Staff" or ISNULL(Days._pageName))
|limit=50
|default=nothing here
}}

If it was a left outer join I would have expected the line to show in the first query, just with the night column empty. I was able to overcome this problem with the Subquery expands right in other formats but not calendar|above]] for non-calendar layout, but it doesn't work in calendar layout. So how would I need to do the query to also get lines that only have an entry for one column (resp one joined table)? Tenbergen (talk) 22:14, 28 May 2020 (UTC)

Error in HOLDS when search value has apostrophe[edit]

Hello! After Cargo was updated to version 2.5, the Wiki I'm working on is getting the following error for HOLDS queries that previously worked.

Version: 2.5

Tested Queries

{{#cargo_query:tables=Stages
|fields=_pageName
|where=Drops HOLDS "Lucien's Microphone"
|format=ul
}}
{{#cargo_query:tables=Stages
|fields=_pageName
|where=Drops HOLDS 'Lucien\'s Microphone'
|format=ul
}}

Errors

A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: SELECT `_pageName` AS `_pageName` FROM `cargo__Stages` WHERE `cargo__Stages`.`_ID` IN (SELECT _rowID FROM `cargo__Stages__Drops` WHERE _value = "Lucien') s Microphone" ORDER BY `_pageName` LIMIT 100 Function: CargoSQLQuery::run Error: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near at line 1 (dbt1.miraheze.org)
A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: SELECT `_pageName` AS `_pageName` FROM `cargo__Stages` WHERE `cargo__Stages`.`_ID` IN (SELECT _rowID FROM `cargo__Stages__Drops` WHERE _value = 'Lucien\') s Microphone' ORDER BY `_pageName` LIMIT 100 Function: CargoSQLQuery::run Error: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near at line 1 (dbt1.miraheze.org)

Looking at the repo, seems like it's related to the changes in CargoSqlQuery::handleVirtualFields() and CargoSqlQuery::substVirtualFieldName(), which looks like it's picking up the apostrophe in the pattern match.

For anyone else encountering the issue, this can currently be worked around by directly using the helper tables:

Workaround

{{#cargo_query:tables=Stages,Stages__Drops
|join on=Stages._ID=Stages__Drops._rowID
|fields=_pageName
|where=_value = 'Lucien\'s Microphone'
|format=ul
}}

--Elaeagnifolia (talk) 16:02, 29 May 2020 (UTC)

Another user on the same Wiki host also reported issues with HOLDS as well. Just using where=CompetitorInfo.PreviousAliases HOLDS TournamentCompetitorScores.Player works, but anything after will error out the query (eg. where=CompetitorInfo.PreviousAliases HOLDS TournamentCompetitorScores.Player AND CompetitorInfo.Player = 'Todd'). I've shortened the error message, but you can see that there's a closing parentheses being appended to ') Todd'.
...(SELECT _rowID FROM cargo__CompetitorInfo__PreviousAliases WHERE _value = cargo__TournamentCompetitorScores.Player AND cargo__CompetitorInfo.Player = ') Todd' GROUP BY cargo__TournamentCompetitorScores.Tournament ORDER BY cargo__TournamentInfo.StartDate LIMIT 100 Function: CargoSQLQuery::run Error: 1064...
--Elaeagnifolia (talk) 01:42, 30 May 2020 (UTC)
Sorry about that, and thanks for letting me know. There was just a change checked in to Cargo that I believe fixes both issues. Yaron Koren (talk) 15:07, 2 June 2020 (UTC)

DISPLAYTITLE[edit]

Any chance on setting a the DISPLAYTITLE to the string from a cargo field? a la {{DISPLAYTITLE:{{ {{#cargo_query:tables=testTable |fields=Name|where=id="123"}} }} Whenever i do this the title defaults back to its PAGENAME, whereas the query works elsewhere on the page, and setting the title to something else also works.

I've searched this talk page but didn't find a definite answer if this is possible or not. For the Extension:DisplayTitle there is an existing thread Topic:Thao0y9d7e9y52bk from which i take it it's possible. But from that I'm left confused whether to use the deprecated Semantic Title, the built in magicword, or the extension and I cannot get it to work with either of these. Keep up the great work! --Fehlerx (talk) 23:39, 2 June 2020 (UTC)

Try adding "|no html" to the #cargo_query call - that might help. Yaron Koren (talk) 23:40, 2 June 2020 (UTC)
Thank you!!--Fehlerx (talk) 01:05, 3 June 2020 (UTC)