Extension talk:Cargo

From MediaWiki.org
Jump to navigation Jump to search

Querying data in cargo with the "HOLDS" command combining with "AND" clause[edit]

Hi, How can I modify the "HOLDS" command in this example to use an "AND" clause? (Version I'm using now: MediaWiki 1.32.1 Cargo 2.1.1)

Example what I'm trying to achieve (lets pretend the Authors wrote among others one book together and Authors is decleared as a List of Strings):

{{#cargo_query: tables=Books |fields=_pageName=Book |where=Authors HOLDS 'Leo Tolstoy' AND Authors HOLDS 'Dostoevsky' }}

It works fine with 'OR' but sadly no results for 'AND'.

I asked a similar question in January - Use HOLDS with multiple values - and the suggestion then was to query the actual Cargo tables. I didn't pursue it so can't give you a full answer. Jonathan3 (talk) 13:38, 14 May 2019 (UTC)
Just tried to query the actual Cargo tables but with the same result. It works just fine with 'OR' but no sign of result with 'AND'.
{{#cargo_query:tables=Books,Books__Authors|join on=Books._ID=Books__Authors._rowID|fields=_pageName=Book,Books.Authors__full=Authors|where=Books__Authors._value = 'Leo Tolstoy' AND Books__Authors._value = 'Dostoevsky'}}
Should I assume there is still no any solution for this issue? Grombol
Update: The issue is solved! Here is the explanetion Grombol

How to query for raw field values with Cargo[edit]

Does anybody know whether there is a way to query for raw field values of integer fields (e.g. _pageID).

In a project database I try to connect sub projects to that project by this field

{{{field|belongs to subproject|input type=dropdown|values={{#cargo_query:tables=webmo_sub_project|fields=_pageID|where=belongs_to_project="{{#var: project id}}"|no html}}|mapping template=webmo_mapping sub project_id to name short }}}

If now the _pageID is e.g 1234, the thousand separator of the _pageID (1,234) causes the attached mapping template to search for _pageIDs 1 and 234 instead of 1234.

In a SMW environment I just used # after the correspondig property, like

{{#ask: [[Category:My Database]]
        |?My number property#
}}

Is there something similar possible in Cargo queries?

This gets rid of the comma for me: |fields=TRIM(_pageID). Jonathan3 (talk) 14:35, 1 February 2019 (UTC)

Thanks a lot. Solved my problem.--Shuitavsshente (talk) 16:36, 1 February 2019 (UTC)

Note that this solution requires Cargo 2.1 or higher, else you will receive the message Error: The SQL function "TRIM()" is not allowed.. I learned this the hard way. EliteMasterEric (talk) 21:00, 11 April 2019 (UTC)

Error: 1267 Illegal mix of collations on cargoRecreateData.php[edit]

This is probably a problem with my database setup but I wanted to record it here anyway in case it affected anyone else. If I'd not been using the command line cargoRecreateData.php I'd not so easily have known why the table wasn't recreating properly. It turned out to be a character that I'd copied and pasted from a Wordpress site into the Description field: U+200E or &# 8206;

Query: SELECT  COUNT(*)  FROM `..._cargo__...`    WHERE `_pageID` = '8980' AND `Description` = '...'

Function: Wikimedia\Rdbms\Database::select

Error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' (...)

Jonathan3 (talk) 14:25, 1 February 2019 (UTC)

P.S. I raised it on the Support desk and got some advice there. Jonathan3 (talk) 22:28, 19 March 2019 (UTC)

Start date/End date and Timeline display format[edit]

This works fine for pages with both set, but a page with an empty end date doesn't appear at all in the timeline format. In contrast, the calendar format shows it as normal (i.e. being one day long). I wonder if this could be changed, so if the end date is not specified then timeline format assumed it to be the same as the start date. Thanks. Jonathan3 (talk) 14:58, 2 February 2019 (UTC)

That's a good idea - I just made this change in the Cargo code. Yaron Koren (talk) 03:28, 5 February 2019 (UTC)
I've just got the latest Cargo code but Special:Drilldown's timeline tab doesn't show the item with the Start date and no End date on my test wiki. Jonathan3 (talk) 13:38, 6 February 2019 (UTC)
It should appear as just a thin vertical bar. Yaron Koren (talk) 14:09, 6 February 2019 (UTC)

Cargo equivalent to PF restricted parameter[edit]

In Page Forms there's the parameter:

"restricted - Specifies that this field will be editable only by admins/sysops and disabled for all other users."

But I guess anyone could still edit the page and add |Field=Value. Is there anything in Cargo that would prevent the field being saved to the database in this situation? I guess something like "allowed values" but in relation to the type of user saving the page.

Or should I go the whole hog and start using Extension:Approved_Revs? Thanks. Jonathan3 (talk) 09:03, 6 February 2019 (UTC)

No, Cargo doesn't do any checks on the user, or comparisons between the old and new value. Yaron Koren (talk) 14:10, 6 February 2019 (UTC)

Cargo and $wgUseFileCache[edit]

@Yaron Koren: I've added a query on the Mediawiki Support desk (here, and in italics below) and wonder if there is anything Cargo-specific I need to know about this setting.

I've recently set $wgUseFileCache = true. Would it be possible to have pages cached for a maximum of 24 hours by setting $wgCacheEpoch = date('YmdHis', strtotime('-24 hours'))?

I have Cargo query pages which I would probably be happy to have cached for that limited period. I know alternatives are to $wgUseFileCache = false again, or use MagicNoCache's __NOCACHE__</nowiki> on the Cargo templates. Are there other options? Thanks.

Thanks. Jonathan3 (talk) 14:37, 11 February 2019 (UTC)

I don't know of anything. Yaron Koren (talk) 21:21, 11 February 2019 (UTC)
Thanks. Jonathan3 (talk) 16:01, 18 February 2019 (UTC)

LEFT JOIN [NOTFIXED / WORKAROUND][edit]

tl;dr To recap the massive discussion bellow, Cargo queries using join on cannot do full conditionals, which makes LEFT OUTER JOINS less useful.

Workaround: Create a dummy template to declare a new table that only holds the subset of rows you wanted in the join. cargo attach it from the main storage template and only store the subset of data you wanted. You can then join your main table with WHERE, with your new dummy smaller table that is pre-reduced. This only works if you only need full conditionals on one table. If you need it one more than one joined table, a bug with cargo_attach currently stops you from doing this. By the time you read this it may be fixed.
Bug 1: You cannot use more than 1 cargo_declare and 1 cargo_attach in a template, only the last one is respected when it comes to populating rows in the tables. You can get around this for cargo_declare by putting it in another template and attaching, but this only works for a maximum of 2 tables per template.
Bug 2: If you try to use a Cargo Query to populate a field in Cargo store, you may get garbage UNIQ--item data. There may be some bigger chicken and egg problems here even if this was fixed.
Thanks for all the help Joanthan3 and Yaron --Finlay Beaton (talk) 18:41, 15 February 2019 (UTC)

I have two tables where I want to display all items in TableA that are not in TableB... except I want to specify exclusion criteria for rows in both tables. In SQL SELECT tableA.col1 FROM tableA LEFT OUTER JOIN tableB ON tableA.col1 = TableB.col2 AND tableA.col2 = 'somevalue' AND tableB.col2 = 'anothervalue' WHERE tableB.col1 IS NULL but in Cargo the |join on= gives a Table and field name must be specified in somevalue error. I can't put it in the WHERE because then the tables would match every row on col1 and I would never have any nulls in tableB. So they need to be in the ON which is valid MySQL. I think this check might be a DDoS protection to ensure all tables have matching columns, but clearly I am doing that. I just want additional ON. I know if I installed Lua I could do the processing in Lua, but that would be inefficient and I would much rather my DB to do the work. I could use your ExternalData extension to query the cargo db directly, but I would lose the formatting niceness of Cargo. Any other ideas or recommendations? --Finlay Beaton (talk) 19:36, 13 February 2019 (UTC)

I don't understand what you mean by "exclusion criteria" for TableB - is the set of results you want to show a mix of pages that are and aren't in TableB? Yaron Koren (talk) 17:00, 14 February 2019 (UTC)
DB Fiddle clearly showing the problem with example tables. I don't want to come across as patronizing, just trying to clear up misunderstanding of the problem. About my example above: AND tableA.col2 = 'somevalue' is an inclusion/exclusion criteria. It specifies which rows in TableA to include for the join. Unlike with INNER JOIN, with LEFT OUTER JOIN you get different results if you put this in the WHERE or the ON. This is because if you put it in the WHERE you won't get NULLs if there is a row match BEFORE the exclusion criteria, it will just remove the whole matched rows. If instead you do it in the ON (while valid SQL, Cargo doesn't allow it with the error above) then you get NULL rows from the left outer join because those rows were removed before being joined (ie the rows never existed). The fiddle really does demonstrate it well IMHO --Finlay Beaton (talk) 19:00, 14 February 2019 (UTC)
Right, okay. Maybe the simplest solution is to store the name of the "captain" in the "groups" table, instead of storing that information in the "members" table. That would simplify queries, and it would also allow you to guarantee that a group has no more than one captain defined for it. Would that work? Yaron Koren (talk) 19:43, 14 February 2019 (UTC)
Yup that would work too, however since all I need for the groups is the _pageName in reality all I have is the members table. Since people populate the group page with all the members, I don't want them to have to mark the captain twice, so I'd have to make a groups table, and use a Cargo query to then populate its captain field based on the members table. It seems a bit hackish given there is a perfectly proper way to do the query in SQL, just Cargo joins don't support it... I could also maybe turn on the "table of all pages" and join against that to reintroduce the nulls, but again, why do that if there is perfectly valid SQL? If there's no intention in Cargo to support full conditionals in the |join on clause (anything you can put in |where= should be valid here), then I'll certainly look at doing one of those other workarounds. It might be nice for the docs to mention |join on doesn't support full conditionals. Thank you for taking the time to look at the problem and consider the feature. --Finlay Beaton (talk) 22:05, 14 February 2019 (UTC)
Sorry, I still don't understand. Is there a Cargo table for groups? If not, what tables are you joining? Yaron Koren (talk) 02:27, 15 February 2019 (UTC)
DB Fiddle I'm sorry, I guess the reality is more complicated then my example. My example showed how Cargo doesn't support full conditionals in the join and how that could be useful, but not my particular use case to the fullest. You can always restructure things into more or different table viewpoints, but I was trying to talk more about functionality and less about my particular use case. Restructuring would have different tradeoffs that are hard to express, like making other reports or queries more complicated, maintenance, etc. We have projects that have contacts listed on the page, with a mix of users and team contacts. One team is the "Owner Team", and one user is the "Owner User". I want a report that tells me which projects owned by a given team are missing an owner user. The fiddle I've added shows our problem more accurately, but introduces a lot of complexity I had hoped to avoid in my previous examples. Full conditionals in the join make this query trivial, hence my feature request.--Finlay Beaton (talk) 03:00, 15 February 2019 (UTC)
Alright. It's true that there will always be use cases for which special handling is needed, but still, since you're asking about a specific need, I feel compelled to drill down. Does this example more closely reflect your situation, with a table being joined to itself? If so, it still seems to me like there's a simpler option, which is to replace the "contacts" table with a "projects" table, which has fields like "owner team", "member teams" (a list), "owner user" and "member users" (another list). Or, if there's already a "projects" table, add those four fields to it. I don't see the need for this (I'm guessing) multiple-instance template setup you have. Unless, again, I don't know the full story. Yaron Koren (talk) 03:34, 15 February 2019 (UTC)
I do in fact have a projects table (generated by the projects infobox) for all the project pages. Then I have a ==Contacts== section where people can edit it one or more Template:Contact rows and it display a nice table of all the contacts for that project, their roles (fully customisable with a few preset like owner and owner team) and any special notes about them. It also cargo query displays the phone number / email / cell information from the relevant user/team page fields (user table, team table). So I could absolutely add a owner user and owner team to the project table. Since I don't want people to have to edit the same information in two places, I could use a Cargo query on the contacts table to autofill this entry in the projects table. Because of the extra fields in the contact table, I don't think adding all the members to the project table is realistic. I mentioned this workaround solution as possible in a previous reply too. I will probably do this. This did feel like a hack (using Cargo queries to populate cargo columns), and adding redundant fields just to satisfy a specific report seems like a bad pattern, but I could be wrong here. Especially since valid simple SQL exists to generate the report, just Cargo SQL doesn't support it. Thanks for taking so much time to understand my use case, and suggest possible solutions (even if they aren't the one I want), it's above and beyond, so thank you. User support is thankless work and despite this thread I'm very happy with Cargo and the transition from SMW. --Finlay Beaton (talk) 03:50, 15 February 2019 (UTC)
Set out to add the workaround this morning. Realised we have contacts on many different types of pages (not just projects), and really on any wiki page, not just ones with infoboxes. So adding the fields to the infobox not doable. Figured it was ugly but I would just add a ContactsActiveTeamOwners and ContactsActiveUserOwners tables to my contact template, and store each contact in 3 places using parserfunctions ifs based on their role. Then I discovered a template can only define a single cargo table (it uses the last one you define)... so that's out too. Tried to make sub-templates to define the other tables and call it from the first template, but of course Cargo doesn't work like that. Still searching for a workaround for cargo's limitations to join syntax. I'll update if I figure anything out.--Finlay Beaton (talk) 15:03, 15 February 2019 (UTC)
What a rabbit hole this has been. So because my contact lists are tables (and not say bulleted lists), I realised that I have another template on all pages that have contacts, a Template:Contacts head template. So I can make it store a new table called PagesWithContacts and add a ActiveOwnerTeam and ActiveOwnerUser fields to do the workaround you suggested. All good so far, and the table is created. To populate it, I would like to use a carge_query on the contacts table. Still good, and I get values in the columns where I should. The problem is that the value it stores is not the result of the query (a format=list, with default=0, which works perfectly when called outside the template), but text like ?'"`UNIQ--item-198--QINU`"'?. This seems like a bug, unless you're not supposed to use cargo queries to populate fields in cargo store. I could potentially see a chicken and egg situation when re-creating all tables here, depending on creation order (say if my PageWithContacts was populated before my Contacts table was). I could potentialy use Page Forms to make the user fill in the owners manually, but they already did using the Contacts template so this seems like forcing them to repeat work and would likely result in people forgetting to update it. So I'm a bit stumped again and looking for other options. I may just move the cargo tables to a separate db, then enable ExternalData extension to get proper sql join syntax on it. That will limit the security risk of exposing all wiki data via SQL. I lose the formatting and other query protections from cargo though. --Finlay Beaton (talk) 16:06, 15 February 2019 (UTC)

I’ve not read everything but noticed this: “Figured it was ugly but I would just add a ContactsActiveTeamOwners and ContactsActiveUserOwners tables to my contact template, and store each contact in 3 places using parserfunctions ifs based on their role. Then I discovered a template can only define a single cargo table (it uses the last one you define)... so that's out too.” Have you tried defining those two extra tables in separate templates that just never get called, and saving to those extra tables using your main template? You’d also need to use cargo_attach so that the data can be recreated again properly in future if needed. Adding fuller SQL functionality might be a good GSoC project, as students would be all over that sort of thing... Jonathan3 (talk) 17:33, 15 February 2019 (UTC)

Hey thanks for chiming in! I'm super new at Cargo and still learning a ton. You were right I did need to add a cargo_attach to make my contact template store the info. Then I used the dummy ContactsActiveUserOwners template to declare a new cargo_declare. In doing so with ContactsActiveTeamOwners I noticed a bug in Cargo though... my contact template only attaches to the LAST declared attach. So if I have 2 attaches, it only uses one. I can have a declare, and 1 attach, and that works great. But as soon as I add a 2nd attach only the last attach declared works for creating/storing data. Maybe I'm going crazy as I didn't make a full PoC bug reproduction. In my case I was able to eliminate the ContactsActiveTeamOwners table completely, and just join my Contacts table (with a WHERE to only display active team owners) with the new ContactsActiveUserOwners table. This all seems like a huge hack to get around the lack of full conditionals in joins... but at least my reports work now. Thanks for all the help Joanthan3 and Yaron. --Finlay Beaton (talk) 18:41, 15 February 2019 (UTC)
You’re welcome. I hope the only-one-attach problem is something Yaron can change relatively easily. It probably hasn’t occurred to anyone before. How about your main template storing to (and attached to) extra table A, and template A storing some of those fields to (and being attached to) extra table B? Though, re-reading your message on the phone here it seems you don’t need to try that. Jonathan3 (talk) 21:00, 15 February 2019 (UTC)
Thinking through it again, to have one template save to three tables, you'd maybe need actually to call the extra template with all the extra fields as parameters, and that extra template could either save to its own table or one attached to it... Jonathan3 (talk) 15:03, 18 February 2019 (UTC)
huh! I wouldn't have thought this would work. But it seems to? Thanks for the further workaround! I also learned something new about Cargo! I'd still rather have full conditionals as it doesn't require a ton of extra templates on a per-query basis, but I'll be using this until then. --Finlay Beaton (talk) 17:46, 18 February 2019 (UTC)

Fatal exception of type "Wikimedia\Rdbms\DBUnexpectedError"[edit]

I'm trying to recreate a table after adding two new fields to the Page Schema, Form, and Template.

Is this possible or do I need to recreate everything?

I go to "recreate table" from the Template:Nameofcategory and it gives me the new table to choose from, but when I hit "keep," I get this error:

A database query error has occurred. This may indicate a bug in the software.
[XGi-KUrQOn4AAJJi6CcAAAAb] 2019-02-17 01:55:53: Fatal exception of type "Wikimedia\Rdbms\DBQueryError"

I also got this error, too, separately. I think I was trying to delete the table (and start over)

Fatal exception of type "Wikimedia\Rdbms\DBUnexpectedError"
I usually get the 'Wikimedia\Rdbms\DBQueryError' error when I didn't run the update script. Maybe it would solve your issue?
php maintenance/update.php
--Extarys (talk) 07:30, 17 February 2019 (UTC)

Using the data provided in the template inside a page[edit]

I'm currently adding basic data using Cargo but I would like to avoid using the template to display the data inside a page because the values are needed in different places and would make editing of articles harder.

I call the template at the top of the article. Is there an efficient way of accessing those values inside the same article?

Extarys (talk) 07:31, 17 February 2019 (UTC)

Would this work? Create a template for each value you want to use in the page, e.g. Template:Show_field1, and in that template have a Cargo query returning just that field |where=_pageName={{PAGENAME}}. Jonathan3 (talk) 15:54, 18 February 2019 (UTC) ...
Just checked - this works fine... you could do a generic template which also takes the field as a parameter e.g. {{show|fieldname}}, or even also the table if you need to do this for more than one, e.g. {{show|tablename|fieldname}}... Jonathan3 (talk) 22:05, 18 February 2019 (UTC)
Thanks Jonathan3 I'll try that, it's a good idea. Extarys (talk) 04:19, 19 February 2019 (UTC)

Importing csv[edit]

I'm currently using WikiDB to store large tables, and Cargo obviously seems like a possible improvement. But in order to use it, I must be able to do a batch import of a few thousand records. Given that I have a lot of content already, I dont want to go back and re-create tons of content using elaborate forms. But Cargo still seems like a nice upgrade from WikiDB. What's the easiest way to do a batch import, so that the records are later easily editable? thanks Osishkin (talk) 13:00, 17 February 2019 (UTC)

I've had importing data from elsewhere on my to do list for a while, and have been looking at using Extension:External Data to do this. Jonathan3 (talk) 16:00, 18 February 2019 (UTC)
You should probably use the Data Transfer extension, and specifically the page Special:ImportCSV, for this. Yaron Koren (talk) 22:58, 18 February 2019 (UTC)
I’m sorry, that’s the one - I should have checked before typing. Jonathan3 (talk) 08:55, 19 February 2019 (UTC)
Excuse my newbie ignorance but how exactly would that work? would you be willing to provide, say, an example csv file for the Books table which shows how the csv would be formatted in order to be automatically parsed into Cargo table data upon loading? also, can I later update records if I upload an updated version of records in a later csv, or will that only create duplicates? thank you Osishkin (talk) 14:56, 19 February 2019 (UTC)
The documentation gives one example - does it make sense? Yes, you can later modify the CSV and re-import it, overwriting the previous pages. Yaron Koren (talk) 20:11, 19 February 2019 (UTC)
Perhaps it does, but not to me. I dont understand how mediawiki knows to parse the imported records as ones that pertain to a specific Cargo table. I'm obviously missing something very basic here. This may be entirely my own ignorant fault, but if you're willing to shed a light on it, I'd appreciate itOsishkin (talk) 20:39, 19 February 2019 (UTC)
Data Transfer - and Page Forms - just deal in wikitext: they create pages that include template calls. It's the templates being called that handle the Cargo stuff, through their use of #cargo_declare and #cargo_store. Does that make sense? Yaron Koren (talk) 04:49, 20 February 2019 (UTC)
Sounds like I just need to toy around with it (once I get my tables working), and come back with more informed questions if I still dont get it. Thanks Osishkin (talk) 06:46, 20 February 2019 (UTC)
So after toying around with Data Transfer using the basic example a it requires that I create pages using the CSV, with every line representing a new record. What if I just want data imported to a cargo table, without creating pages? how do I go about doing that using a batch import?Osishkin (talk) 12:11, 5 March 2019 (UTC)
Without pages you’ll have no cargo_stores so won’t be able easily to recreate the table. How about putting the pages into a separate namespace, out of the way? Or if a page can have multiple cargo_stores to the same table, maybe use php or even mail merge to create a single wiki page? Jonathan3 (talk) 14:51, 5 March 2019 (UTC)
If you don't want to store the data in wikitext, you could use the External Data extension to do it. There are two potential ways: create some kind of outside API to access the data, or put the whole CSV set in one wiki page, and then use Special:GetData as the API for it. See here for a little more on using ED together with Cargo. Yaron Koren (talk) 16:42, 5 March 2019 (UTC)

Tables not created[edit]

My tables are not being created, getting an error "table X not found". (from CargoUtils.php line 231);". I've seen people getting this error before. I'm using the latest version, and I tried the Books table example (so there's no issue with probelmatic field names). Any idea what might be the problem? MW 1.30.1, php 5.5.9, MySQL 5.7 Osishkin (talk) 14:56, 19 February 2019 (UTC)

You have to create each table yourself, either from each template page or all at once via the command-line script cargoRecreateData.php. Could that be the issue? Yaron Koren (talk) 04:49, 20 February 2019 (UTC)
I tried both. The above error is displayed after trying to view the table data after creating it from the template page. The php script then runs without any messages being displayed (perhaps because the table was already created?)
UPDATE - the php script does nothing even if run first after anew template declaring a table is created. Anyway, looking at the the relevant mysql DB shows that all cargo tables ARE created, but cargo_tables and cargo_pages have no records (i.e., "select * from cargo_tables" comes up empty)Osishkin (talk) 11:26, 20 February 2019 (UTC)
I’d something similar a while back. I just deleted everything from the database using SQL and started from scratch, which solved it. Jonathan3 (talk) 17:54, 20 February 2019 (UTC)
Tried it. Dropped all tables, restarted apache, ran update.php again, recreated table. Same error when trying to see table Osishkin (talk) 18:40, 20 February 2019 (UTC)
Maybe the database user hasn’t got the right permissions? Jonathan3 (talk) 20:14, 20 February 2019 (UTC)
It's the root user. And the tables do get created, they just cant be viewed. And I'm guessing that has to do with the fact that cargo_pages and cargo_tables remain empty Osishkin (talk) 21:14, 20 February 2019 (UTC)

I have no idea why that would be happening. What version of Cargo are you running? Yaron Koren (talk) 17:49, 21 February 2019 (UTC)

2.1.1 Osishkin (talk) 18:43, 21 February 2019 (UTC)
What happens if you create a template with nothing but <noinclude>{{#cargo_declare:_table=Test|Fieldname=String}}</noinclude> in it? Jonathan3 (talk) 23:35, 21 February 2019 (UTC)
Same error message Osishkin (talk) 05:42, 22 February 2019 (UTC)
BTW, I cant think how exactly this matters, but my wiki is an upgrade from MW 1.18. Could this somehow be the issue? notably, after some debugging I realized that the problem is indeed that indeed inserting an entry to cargo_tables in includes/CargoUtils:createCargoTableOrTables() doesnt work. I tried to create the cargo_tables entry manually from the command line, and that made the error go away.Osishkin (talk) 07:48, 22 February 2019 (UTC)
Okay, that's good to know. I don't think the former version is the issue. There's clearly some problem with the API call. Is this a public wiki, by any chance? Yaron Koren (talk) 15:02, 22 February 2019 (UTC)
It's not public, sorry Osishkin (talk) 17:59, 22 February 2019 (UTC)
Alright. Can you access the API on your wiki? It should look like the URLs that have index.php in them, but with index.php replaced with api.php. If you can - and it looks correct - please go to "api.php?action=cargorecreatedata&template=TemplateName&table=CargoTableName" on your wiki. An example would be "api.php?action=cargorecreatedata&template=Book&table=Books". What do you see when you go to that URL? Yaron Koren (talk) 21:14, 22 February 2019 (UTC)
{"success": ""} Osishkin (talk) 06:37, 23 February 2019 (UTC)
Any thoughts?

Do Cargo tables get indexes in the database or is every query a table scan?[edit]

I've been running the `slow log` on our mysql instance and 8 of the top 10 slow queries are all Cargo related, and they all show 100% table scan which would explain their slowness. So is there a way to create indexes with Cargo?

As of version 2.0, most Cargo DB fields are supposed to get indexed automatically - everything except "Text", "Wikitext" and "Searchtext" fields, I think. Do you know what types the fields are that are slowing down the queries on your site? Yaron Koren (talk) 18:10, 21 February 2019 (UTC)

where clause with special character #[edit]

I just ran into this problem from Extension_talk:Cargo/Archive_November_to_December_2015#where_clause_on_page_names_with_special_characters Dec 2015. Error in "where" parameter: the string "#" cannot be used within #cargo_query.. I tried to look in common problems and tried using the same trick as for quotes with a \# but that didn't work. Is there another workaround? --Finlay Beaton (talk) 16:15, 22 February 2019 (UTC)

I don't think there's any workaround at the moment. Why do you need to use '#'? Yaron Koren (talk) 16:28, 22 February 2019 (UTC)
The user specifies a page link to store in a cargo table. That link can be a dedicated page about the subject, or enterprising users started putting in links to a section on another page, ie Page#Section. Said value is sometimes searched from another template in a cargo query. Given the template that inputs the cargo data is using numbered parameters, if I added a new dedicated field just for subsections I'd have to go and edit every single use of the template. --Finlay Beaton (talk) 21:29, 22 February 2019 (UTC)
I know it's a different problem, but do you think the solution in Extension_talk:Cargo/Archive_October_to_December_2018#Database_error_when_page_name_contains_both_single_and_double_quotation_marks could help you? Jonathan3 (talk) 23:35, 24 February 2019 (UTC)
Just spotted another way (in relation to quotation marks, anyway) in Extension:Cargo/Common_problems: _pageName='{{#replace:Cargo|&#39;|\'}}'" - you could maybe try &#35; for your # character problem. Jonathan3 (talk) 23:40, 24 February 2019 (UTC)

Special page[edit]

This extension use a special page, but what is the name of this special page? It's not mentioned on the article page.

Cargo defines the special pages Special:CargoTables, Special:Drilldown and Special:ViewData. Yaron Koren (talk) 15:28, 24 February 2019 (UTC)

Special:Drilldown and PostgreSQL[edit]

I'm running this in a virutal machine (testing various things before setting up our wiki). Prior to the PostgreSQL database I tested all with MariaDB and I didn't get these errors.

Just a test template with a table and single field. Everything works (define, create, store, querry) exept the Special:Drilldown where I get following error:

[dc0306367072deeed4558910] /wiki/Spezial:Drilldown/TestB?_single Wikimedia\Rdbms\DBQueryError from line 1457 of /var/www/REL1_31/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: SELECT "TestB_alias"."_pageName" AS "title","TestB_alias"."_pageName" AS "value","TestB_alias"."_pageNamespace" AS "namespace","TestB_alias"."_pageID" AS "ID" FROM "cargo__TestB" "TestB_alias" GROUP BY "TestB_alias"."_pageName","TestB_alias"."_pageName","TestB_alias"."_pageNamespace","TestB_alias"."_pageID" ORDER BY "TestB_alias"."_pageTitle" LIMIT 251 OFFSET 0
Function: CargoDrilldownPage::reallyDoQuery
Error: 42803 FEHLER: Spalte »TestB_alias._pageTitle« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
LINE 1: ..."_pageNamespace","TestB_alias"."_pageID" ORDER BY "TestB_ali...
^

Backtrace:

#0 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1427): Wikimedia\Rdbms\Database->makeQueryException(string, string, string, string)
#1 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1200): Wikimedia\Rdbms\Database->reportQueryError(string, string, string, string, boolean)
#2 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1653): Wikimedia\Rdbms\Database->query(string, string)
#3 /var/www/REL1_31/includes/specialpage/QueryPage.php(425): Wikimedia\Rdbms\Database->select(array, array, array, string, array, array)
#4 /var/www/REL1_31/includes/specialpage/QueryPage.php(599): QueryPage->reallyDoQuery(integer, integer)
#5 /var/www/REL1_31/extensions/Cargo/drilldown/CargoSpecialDrilldown.php(257): QueryPage->execute(string)
#6 /var/www/REL1_31/includes/specialpage/SpecialPage.php(522): CargoDrilldown->execute(string)
#7 /var/www/REL1_31/includes/specialpage/SpecialPageFactory.php(568): SpecialPage->run(string)
#8 /var/www/REL1_31/includes/MediaWiki.php(288): SpecialPageFactory::executePath(Title, RequestContext)
#9 /var/www/REL1_31/includes/MediaWiki.php(861): MediaWiki->performRequest()
#10 /var/www/REL1_31/includes/MediaWiki.php(524): MediaWiki->main()
#11 /var/www/REL1_31/index.php(42): MediaWiki->run()
#12 {main}

The versions I use:

MediaWiki 1.31.1 (0e052c8), PHP 7.0.33-0+deb9u3, PostgreSQL 9.6.12, Cargo 2.1.2 (e424804)

If you need any more informations just ask. Any ideas on this ?


Don't know if this is somehow connected to that error. If I run the php cargoRecreateData.php I get in addition this (also everything seems to be recreated):

Saving data for pages 1 to 1 that call this template...
PHP Notice:  Undefined index: COUNT(*) in /var/www/REL1_31/extensions/Cargo/includes/parserfunctions/CargoStore.php on line 538
It looks like you found two different bugs! Sorry about that. I just checked in what I think are fixes for both of them. Yaron Koren (talk) 18:37, 13 March 2019 (UTC)
Hey thanks for the fast fix. It worked for the php cargoRecreateData.php but the first error is now a different one (the test field is called 'Muh'):
[9ed93d2065dc32c0a5f0920f] /wiki/Spezial:Drilldown/TestB?_single Wikimedia\Rdbms\DBQueryError from line 1457 of /var/www/REL1_31/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: SELECT "TestB_alias"."Muh" AS value,COUNT(DISTINCT "TestB_alias"._pageID) AS total FROM "cargo__TestB" "TestB_alias" GROUP BY "TestB_alias"."Muh"
Function:
Error: 42703 FEHLER: Spalte TestB_alias._pageid existiert nicht
LINE 1: ... */ "TestB_alias"."Muh" AS value,COUNT(DISTINCT "TestB_ali...
^
HINT: Vielleicht wurde beabsichtigt, auf die Spalte »TestB_alias._pageID« zu verweisen.

Backtrace:
...

Brabi81 (talk) 06:51, 14 March 2019 (UTC)

This was a third bug... sorry about that. I guess Cargo hasn't been run with PostgreSQL in a while. I just checked in what I think is a fix for this bug too. Yaron Koren (talk) 15:22, 15 March 2019 (UTC)
Thanks again for the fast fix. Now everything seems to work as expected! But if I filter now in the Drilldown for values that are defined as List (,) of another error comes up. Other filters work. 'Authors' is the field defined as list. And a second if I come up with a date or datetime field. 'Tach' is the date field. Btw is here the right place to post errors ?
[897695dedd3e56493d4a1955] /wiki/Spezial:Drilldown/TestC?_single&Kuh=ajslh&Authors=s4a65d4 MWException from line 335 of /var/www/REL1_31/extensions/Cargo/includes/CargoSQLQuery.php: Error: table ""TestC_alias"" is not in list of table names.

Backtrace:

#0 /var/www/REL1_31/extensions/Cargo/includes/CargoSQLQuery.php(66): CargoSQLQuery->setCargoJoinConds(string)
#1 /var/www/REL1_31/extensions/Cargo/drilldown/CargoSpecialDrilldown.php(2220): CargoSQLQuery::newFromValues(string, string, string, string, string, NULL, string, integer, integer)
#2 /var/www/REL1_31/includes/specialpage/QueryPage.php(396): CargoDrilldownPage->getQueryInfo()
#3 /var/www/REL1_31/includes/specialpage/QueryPage.php(599): QueryPage->reallyDoQuery(integer, integer)
#4 /var/www/REL1_31/extensions/Cargo/drilldown/CargoSpecialDrilldown.php(257): QueryPage->execute(string)
#5 /var/www/REL1_31/includes/specialpage/SpecialPage.php(522): CargoDrilldown->execute(string)
#6 /var/www/REL1_31/includes/specialpage/SpecialPageFactory.php(568): SpecialPage->run(string)
#7 /var/www/REL1_31/includes/MediaWiki.php(288): SpecialPageFactory::executePath(Title, RequestContext)
#8 /var/www/REL1_31/includes/MediaWiki.php(861): MediaWiki->performRequest()
#9 /var/www/REL1_31/includes/MediaWiki.php(524): MediaWiki->main()
#10 /var/www/REL1_31/index.php(42): MediaWiki->run()
#11 {main}
[4136c2e5ceb98e30c5da8505] /wiki/Spezial:Drilldown/TestC?_single Wikimedia\Rdbms\DBQueryError from line 1457 of /var/www/REL1_31/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: SELECT DATEDIFF(MAX(Tach), MIN(Tach))/ COUNT(*) as avgDaysPerEvent FROM "cargo__TestC"
Function: Wikimedia\Rdbms\Database::select
Error: 42703 FEHLER: Spalte »tach« existiert nicht
LINE 1: ...ikimedia\Rdbms\Database::select */ DATEDIFF(MAX(Tach), MIN...
^
HINT: Vielleicht wurde beabsichtigt, auf die Spalte »cargo__TestC.Tach« zu verweisen.

Backtrace:

#0 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1427): Wikimedia\Rdbms\Database->makeQueryException(string, string, string, string)
#1 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1200): Wikimedia\Rdbms\Database->reportQueryError(string, string, string, string, boolean)
#2 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1653): Wikimedia\Rdbms\Database->query(string, string)
#3 /var/www/REL1_31/extensions/Cargo/drilldown/CargoSpecialDrilldown.php(165): Wikimedia\Rdbms\Database->select(string, string)
#4 /var/www/REL1_31/includes/specialpage/SpecialPage.php(522): CargoDrilldown->execute(string)
#5 /var/www/REL1_31/includes/specialpage/SpecialPageFactory.php(568): SpecialPage->run(string)
#6 /var/www/REL1_31/includes/MediaWiki.php(288): SpecialPageFactory::executePath(Title, RequestContext)
#7 /var/www/REL1_31/includes/MediaWiki.php(861): MediaWiki->performRequest()
#8 /var/www/REL1_31/includes/MediaWiki.php(524): MediaWiki->main()
#9 /var/www/REL1_31/index.php(42): MediaWiki->run()
#10 {main}

Brabi81 (talk) 06:11, 18 March 2019 (UTC)

This is a fine place to post bug reports (Phabricator is the other main option). I think I fixed the second of these bugs. The first, though, I don't understand why is happening. Do you have anything unusual in the Cargo declaration for that table, like a "parent table" or custom drilldown tabs? Yaron Koren (talk) 20:53, 18 March 2019 (UTC)
The first one has nothing special about it. The declare looks like {{#cargo_declare:_table=TestA|Authors=List (,) of Page|Genres=List (,) of String|Number_of_pages=Integer}} and that error appears for 'Authors' and for 'Genres'. And for the second error: now it finds the field but now tells me no function ...
5febdf247dccf1541fcca986] /wiki/Spezial:Drilldown/TestC?_single Wikimedia\Rdbms\DBQueryError from line 1457 of /var/www/REL1_31/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: SELECT DATEDIFF(MAX("Tach"), MIN("Tach"))/ COUNT(*) as avgDaysPerEvent FROM "cargo__TestC"
Function: Wikimedia\Rdbms\Database::select
Error: 42883 FEHLER: Funktion datediff(date, date) existiert nicht
LINE 1: SELECT /* Wikimedia\Rdbms\Database::select */ DATEDIFF(MAX...
^
HINT: Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.

Backtrace:

#0 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1427): Wikimedia\Rdbms\Database->makeQueryException(string, string, string, string)
#1 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1200): Wikimedia\Rdbms\Database->reportQueryError(string, string, string, string, boolean)
#2 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1653): Wikimedia\Rdbms\Database->query(string, string)
#3 /var/www/REL1_31/extensions/Cargo/drilldown/CargoSpecialDrilldown.php(165): Wikimedia\Rdbms\Database->select(string, string)
#4 /var/www/REL1_31/includes/specialpage/SpecialPage.php(522): CargoDrilldown->execute(string)
#5 /var/www/REL1_31/includes/specialpage/SpecialPageFactory.php(568): SpecialPage->run(string)
#6 /var/www/REL1_31/includes/MediaWiki.php(288): SpecialPageFactory::executePath(Title, RequestContext)
#7 /var/www/REL1_31/includes/MediaWiki.php(861): MediaWiki->performRequest()
#8 /var/www/REL1_31/includes/MediaWiki.php(524): MediaWiki->main()
#9 /var/www/REL1_31/index.php(42): MediaWiki->run()
#10 {main}

Brabi81 (talk) 06:21, 19 March 2019 (UTC)

Sorry about that again - I really should have tested this on Postgres a while ago, instead of leaving it to users to do the testing. Anyway, I just checked in what I think is a fix for this latest bug. Yaron Koren (talk) 00:05, 20 March 2019 (UTC)

Maybe yes, but I have also no problem in reporting and testing here :) And I'm amazed by the fast fixing. To recap now, the Drilldown page is viewable again (even with dates) only filtering the values doesn't work for Lists. And now also for dates (seems different from lists):
[5a4b91be1904937953e70fef] /wiki/Spezial:Drilldown/TestC?Tach=1991 MWException from line 154 of /var/www/REL1_31/extensions/Cargo/includes/CargoSQLQuery.php: Error: the string "FROM" cannot be used within #cargo_query.

Backtrace:

#0 /var/www/REL1_31/extensions/Cargo/includes/CargoSQLQuery.php(53): CargoSQLQuery::validateValues(string, string, string, string, string, NULL, string, integer, integer)
#1 /var/www/REL1_31/extensions/Cargo/drilldown/CargoSpecialDrilldown.php(2228): CargoSQLQuery::newFromValues(string, string, string, string, string, NULL, string, integer, integer)
#2 /var/www/REL1_31/includes/specialpage/QueryPage.php(396): CargoDrilldownPage->getQueryInfo()
#3 /var/www/REL1_31/includes/specialpage/QueryPage.php(599): QueryPage->reallyDoQuery(integer, integer)
#4 /var/www/REL1_31/extensions/Cargo/drilldown/CargoSpecialDrilldown.php(265): QueryPage->execute(string)
#5 /var/www/REL1_31/includes/specialpage/SpecialPage.php(522): CargoDrilldown->execute(string)
#6 /var/www/REL1_31/includes/specialpage/SpecialPageFactory.php(568): SpecialPage->run(string)
#7 /var/www/REL1_31/includes/MediaWiki.php(288): SpecialPageFactory::executePath(Title, RequestContext)
#8 /var/www/REL1_31/includes/MediaWiki.php(861): MediaWiki->performRequest()
#9 /var/www/REL1_31/includes/MediaWiki.php(524): MediaWiki->main()
#10 /var/www/REL1_31/index.php(42): MediaWiki->run()
#11 {main}
If I try the calendar, the error seems related to that but a little different:
[926b9b7fe90fbc004083ad95] /wiki/Spezial:Drilldown/TestC?format=calendar&formatBy=Tach Wikimedia\Rdbms\DBQueryError from line 1457 of /var/www/REL1_31/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: SELECT MAX( TestC_alias.Tach ) as start_date FROM "cargo__TestC" "TestC_alias"
Function:
Error: 42P01 FEHLER: fehlender Eintrag in FROM-Klausel für Tabelle »testc_alias«
LINE 1: SELECT /* */ MAX( TestC_alias.Tach ) as start_date FROM ...
^

Backtrace:

#0 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1427): Wikimedia\Rdbms\Database->makeQueryException(string, string, string, NULL)
#1 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1200): Wikimedia\Rdbms\Database->reportQueryError(string, string, string, NULL, boolean)
#2 /var/www/REL1_31/includes/libs/rdbms/database/Database.php(1653): Wikimedia\Rdbms\Database->query(string, NULL)
#3 /var/www/REL1_31/extensions/Cargo/drilldown/CargoSpecialDrilldown.php(2128): Wikimedia\Rdbms\Database->select(array, string, array, NULL, array, array)
#4 /var/www/REL1_31/includes/specialpage/QueryPage.php(396): CargoDrilldownPage->getQueryInfo()
#5 /var/www/REL1_31/includes/specialpage/QueryPage.php(599): QueryPage->reallyDoQuery(integer, integer)
#6 /var/www/REL1_31/extensions/Cargo/drilldown/CargoSpecialDrilldown.php(265): QueryPage->execute(string)
#7 /var/www/REL1_31/includes/specialpage/SpecialPage.php(522): CargoDrilldown->execute(string)
#8 /var/www/REL1_31/includes/specialpage/SpecialPageFactory.php(568): SpecialPage->run(string)
#9 /var/www/REL1_31/includes/MediaWiki.php(288): SpecialPageFactory::executePath(Title, RequestContext)
#10 /var/www/REL1_31/includes/MediaWiki.php(861): MediaWiki->performRequest()
#11 /var/www/REL1_31/includes/MediaWiki.php(524): MediaWiki->main()
#12 /var/www/REL1_31/index.php(42): MediaWiki->run()
#13 {main}
And to complete it, the timeline shows:
Failed to load json data from http://phpc093.gsi.de/w/index.php?title=Spezial:CargoExport&tables=TestC%3DTestC_alias&&fields=TestC_alias._pageName%2CTestC_alias.Tach&&group+by="TestC_alias"."_pageName"%2C"TestC_alias"."_pageName"%2C"TestC_alias"."_pageNamespace"%2C"TestC_alias"."_pageID"&order+by="TestC_alias"."_pageName"%2C"TestC_alias"."_pageName"%2C"TestC_alias"."_pageNamespace"%2C"TestC_alias"."_pageID"&limit=250&format=timeline
Internal Server Error
Brabi81 (talk) 06:25, 20 March 2019 (UTC)

Field type mismatch[edit]

  • Cargo: 2.0-rc1 (dac1f35) 06:52, 16 October 2018
  • MediaWiki: 1.31.1 (6962c2c) 16:18, 9 March 2019

Declaration:

{{#cargo_declare: _table = unitprages
  | unit       = string    <!--  -->
  | class      = string   <!--  -->
  | first      = date
  | last       = date
  | special    = string (size = 1)
  | guard      = Boolean
  | standalone = Boolean
  | number     = integer <!--  -->
}}

Special:CargoTables/unitprages

Структура таблицы:
    unit - string
    class - string
    first - date
    last - date
    special - string
    guard - Boolean
    standalone - Boolean
    number - integer
Эта таблица имеет в целом 0 строк.
Статья 	unit 	class 	first 	last 	special 	guard 	standalone 	number

But in reality, the field 'number' in the database is of type 'varchar(300)'. --StasR (talk) 17:22, 24 March 2019 (UTC)

I think the issue is that you have "integer", and not "Integer", as the type - the type parsing used to be case-sensitive. I think this has been fixed in more recent versions of Cargo, so now you can have any casing on the types. Yaron Koren (talk) 01:24, 25 March 2019 (UTC)
Thanks. Sadly, when it turns out after making 200,000 entries :-) Maybe it makes sense to add more stringent syntax checks for the cargo_declare? This is a rare request, and performance is not important there. --StasR (talk) 06:36, 25 March 2019 (UTC)
Ooh - 200,000 entries is a big table. I assume you know about the "replacement table" feature? I would recommend recreating the table, and using a replacement table to avoid messing up the current operations. My guess is that those two date fields are being stored as strings as well, given that "date" is lowercase, so all the more reason to redo the table. The syntax stuff has already been solved in Cargo, as far as I know - I would recommend upgrading to the latest version. If you do that, you can also make use of the new "Start date" and "End date" types, which may match your data structure. Yaron Koren (talk) 12:50, 25 March 2019 (UTC)

Drilldown cannot distinguish between NULL and 0 for Integer fields.[edit]

(using Cargo v2.1.1 on Gamepedia) Drilldown is displaying MediaWiki:Powersearch-togglenone for Integer values 0 or NULL (might also apply for Boolean fields). This makes it difficult to find undefined fields using Drilldown (view table and cargo_query are working as expected), unless we mark those fields as mandatory (not sure if our database should be doing that). --BryghtShadow (talk) 11:26, 29 March 2019 (UTC)

Importing from XML Pagename delimited by ~[edit]

Got a problem. Creating a Cargo table "Sources" (and category/form/template) that has a field "Repertoire" that has multiple values (list ~ of Page) where each page is from Category=Repertoire. Each entry in the Source.Repertoire is a pageName from category Repertoire. I've uploaded a scad of new "Source" pages via XML upload that has the correct Pagenames in the Repertoire field delimited correctly. After the XML upload process, all the pages were recognized, and I can click the link in the newly created Source Page/Cargo table, and follow those links to the correct already exiting page (in category "Repertoire"). Looks like the upload worked great.

But, in the individual repertoire pages, I have a cargo query that returns a list of all sources that HOLD the pagename in Sources.Repertoire.

Here's the query in the Repertoire Template

{{#cargo_query:
tables=Source
|fields=_pageName
|where=Repertoire HOLDS '{{PAGENAME}}'}}

None of the pages uploaded by XML show correct query results. However, if I go into Form for Sources, and add individual pages in the Repertoire field. , it works. The Cargo Source table shows an entry (from the XML upload) and another entry (from the FORM). They are the same page. If I follow the XML generated link, edit it, and open the Form generated link, it's definitely the same page.

Stumped.May214 (talk) 21:03, 2 April 2019 (UTC)

Also tried
'{{FULLPAGENAME}}'
May214 (talk) 21:04, 2 April 2019 (UTC)
Just to be clear - is the problem in the data storage, or the data querying, as far as you know? Yaron Koren (talk) 23:55, 2 April 2019 (UTC)
My assumption is storage. If I add an individual entry using the Form (rather than a mass upload by XML) then the query works. THe XML loaded entries work functionally - I can click and follow links to existing pages. May214 (talk) 12:37, 3 April 2019 (UTC)
If you resave one of the imported, non-working pages (making a minor change first), does it start to show up in queries? Yaron Koren (talk) 02:24, 11 April 2019 (UTC)
No. If I go to Cargo Tables, and view the table for each entry, all the correct pages show up on the table viewer. I can click and it will open the correct page. In order for the query to work, I have to enter the page in the Form. The field in the Form is set up to autocomplete on category. When I add using the Form the query will work. But I'm adding the same page that is already showing up in the Cargo table. The exact same page. I can open the page from the cargo table, make a minor edit and save it. The query doesn't pick it up. Then if I add the same page in the Form (now I have duplicate entries of the same page in the table) and open the Form added page, the minor edit shows up (proving I don't have duplicate pages) and then the query works. Also, If I add the exact text of the page title without clicking on a page that shows up from the autocomplete options, the query doesn't work. There's something about selecting the page from the autocomplete options that makes the query work. But it shouldn't matter,right? I mean the page title is currently held in the Cargo Table and functions. May214 (talk) 19:07, 18 April 2019 (UTC)
Update. I can't copy paste text into the form either. I have to start typing the title, and then select the autocomplete option that works. May214 (talk) 19:16, 18 April 2019 (UTC)
Don't you have to include the Repertoire field in your field= line? Jeremi Plazas (talk) 19:28, 19 April 2019 (UTC)
Well, I want the query to return the pageNames in the source tables that hold a certain item in Repertoire. And it works, when I add each page by autocomplete using the Source Form. But I'll try a couple variations to see if including Repertoire in the fields work. If I just replace _pageName with Repertoire, it spits out the name of the repertoire, and not the source that holds it in repertoire. 204.124.167.5 15:51, 23 April 2019 (UTC)
So, no other suggestions? Any idea why it will work with an autocompleted entry, but not with a straight text entry? May214 (talk) 20:28, 1 May 2019 (UTC)
Sorry - I read through your explanation from April 18 about five times, but I still don't understand it. Each time you say "form" and "table", I don't know if you're talking about Source or Repertoire. This might be easier for me to understand with an example (even a fake one). Yaron Koren (talk) 00:53, 2 May 2019 (UTC)

Date and "Start date"[edit]

I'm planning to merge two tables. Table A has a Date field. Table B has Start date and End date fields. I am considering converting Table A's Date field to Start date (with no End date), and having Start date and End date for Table B's data. Would this work? Is Start date, when it's there on its own, treated as if it were Date? Thanks. Jonathan3 (talk) 23:05, 10 April 2019 (UTC)

I think having a "Start date" field without an "End date" field is simply not allowed. Yaron Koren (talk) 02:18, 11 April 2019 (UTC)
I’ll check it out but what about this? “Start date, End date - similar to Date, but are meant to hold the beginning and end of some duration. A table can hold either no Start date and no End date field, or exactly one of both.” Jonathan3 (talk) 08:08, 11 April 2019 (UTC)
That's right. Yaron Koren (talk) 15:50, 11 April 2019 (UTC)
I see what you meant now! I would end up with both a start date and end date field but usually with no value for end date. I was wondering if a start date would be as good as having a date for those entries with no end date value. Jonathan3 (talk) 16:04, 11 April 2019 (UTC)
Oh, I see. Well, either or both values can be null. Out of curiosity, why would the end date usually be null - is it because the end date is unknown, or because the concept of an end date is invalid for those pages? If the latter, it may be worth restructuring the data. Yaron Koren (talk) 17:12, 11 April 2019 (UTC)
Table A relates to documents (publication date) and table B relates to consultation documents (publication=start date, and end date). I want both together so that, for example, it’s easy to drill down on author. It seems there are two choices:
  1. Use a Start Date for every document and leave End Date empty unless required.
  2. Retain “publication date” as a Date, and when necessary ALSO use both Start Date (same value as the publication date Date field) and End Date.
Which seems preferable? Thank you. Jonathan3 (talk) 21:23, 11 April 2019 (UTC)
Okay, now I think I fully get it. Merging these two seems awkward... I know that it would make drill-down easier, but it still seems strange. If you're planning to do it, though, the first approach seems better. Yaron Koren (talk) 22:22, 11 April 2019 (UTC)
Thanks. I'll probably adopt a third option - keep publication date as an ordinary Date field, and add an End/Closure date also as an ordinary Date field. If I decide I need any of the special Start Date & End Date stuff (e.g. lines instead of dots on timeline format) then I can change the table definition later. Jonathan3 (talk) 13:56, 12 April 2019 (UTC)
That approach makes sense too. Yaron Koren (talk) 14:20, 12 April 2019 (UTC)

How does one exclude something in a Cargo query? [RESOLVED][edit]

Forgive my noobiness...

How does one exclude something in Cargo? I've got a query of the type:

{{#cargo_query:
	tables=LtrhfFiles
	|fields=_pageName,Caption,AssetPage
	|where=AssetPage HOLDS '{{PAGENAME}}' AND _pageName NOT '{{{FeaturedImagePage| }}}'
	|format=template
	|template=TinyGallery
}}

Obviously i get an error, but would like to accomplish the following: find all pages in that table where the AssetPage field contains {{PAGENAME}}, but excluding the pagename {{{FeaturedImagePage| }}}.

--Jeremi Plazas (talk) 18:46, 19 April 2019 (UTC)

I can give you half an answer. For most fields, change your NOT to <> and it'll work. I can't get it to work with _pageName fields though. Jonathan3 (talk) 21:03, 19 April 2019 (UTC)
P.S. It works fine. I just forgot that the pages in my test query had a namespace (it works when adding the namespace, or instead using _pageTitle). Jonathan3 (talk) 21:11, 19 April 2019 (UTC)
Fantastic! That did it thank you. Jeremi Plazas (talk) 04:02, 20 April 2019 (UTC)

ORDER BY CASE is broken with PostgreSQL and general Pg issues[edit]

Cargo seems to quote the ORDER BY clause, making it a column name:

Apr 20 20:13:25 db postgres[62078]: [41-1] ERROR:  column "CASE WHEN "Rarity" = 'Normal' THEN '1' WHEN "Rarity" = 'Rare' T" does not exist at character 183
Apr 20 20:13:25 db postgres[62078]: [41-2] STATEMENT:  SELECT /* CargoSQLQuery::run  */  "Name" AS "Name","Rarity" AS "Rarity","5-4note" AS "5-4note"  FROM "cargo__shipDrops"    WHERE "5-4" IS NOT NULL OR "5-4note" IS NOT NULL  ORDER BY "CASE WHEN ""Rarity"" = 'Normal' THEN '1' WHEN ""Rarity"" = 'Rare' THEN '2' WHEN ""Rarity"" = 'Elite' THEN '3' WHEN ""Rarity"" = 'Priority' THEN '4' WHEN ""Rarity"" = 'Super Rare' THEN '5' WHEN ""Rarity"" = 'Ultra Rare' THEN '6'  ELSE ""Rarity"" END" DESC,"ID" LIMIT 100

Does it pass the quotation marks when used via the API?

Another note: GROUP BY clause parsing:

ERROR:  column "artist" does not exist at character 90
HINT:  Perhaps you meant to reference the column "cargo__ships.Artist".
STATEMENT:  SELECT /* CargoSQLQuery::run  */  "Artist" AS "Artist"  FROM "cargo__ships"     GROUP BY Artist ORDER BY "Artist" LIMIT 500

If "Artist" is put in double quotes for groupBy, it escapes that which results in

ERROR:  column ""Artist"" does not exist at character 90
HINT:  Perhaps you meant to reference the column "cargo__ships.Artist".
STATEMENT:  SELECT /* CargoSQLQuery::run  */  "Artist" AS "Artist"  FROM "cargo__ships"     GROUP BY """Artist""" ORDER BY "Artist" LIMIT 500

--IijimaYun (talk) 11:30, 20 April 2019 (UTC)

Storing Cargo in Scribunto pages[edit]

I'm not sure if this is a Cargo thing or a Scribunto thing but it seems like data won't store on any page with Scribunto content model - if that's a cargo thing could it be changed or added to a config setting? I want to store some cargo data about a module on its documentation, and I'd prefer to have that be an in includeonly and executed on the page itself instead of on the doc page for consistency/clarity. --RheingoldRiver (talk) 08:42, 5 May 2019 (UTC)

(This is the data I'm showing, I'd like to have it immediately update when the module page is edited rather than needing some other location blank edited as well) --RheingoldRiver (talk) 10:16, 5 May 2019 (UTC)

Well I guess I could do this without Cargo at all & instead do a DPL query to get category members and then pull data by directly transcluding everything here with mw.loadData. But it would still be nice to have Cargo able to store from Scribunto pages --RheingoldRiver (talk) 10:47, 5 May 2019 (UTC)

Are you saying that pages in the Module: namespace aren't storing Cargo data? If so, that's not too surprising, since modules can't call templates... or can they? Yaron Koren (talk) 14:42, 5 May 2019 (UTC)
Yeah, but the documentation on module pages does transclude templates (that's how you add categories to Lua modules etc), so I would expect Cargo transcluded from the documentation subpage to store properly even in that content model --RheingoldRiver (talk) 01:25, 6 May 2019 (UTC)
What's an example of such a module? Yaron Koren (talk) 03:08, 6 May 2019 (UTC)
Everything in the category I linked above, and here's a direct link to one in particular --RheingoldRiver (talk) 09:47, 6 May 2019 (UTC)
Okay, so you linked to the module, but the documentation page for it, which is a standard wikitext page, is here. But how is the category getting set for the actual module? Somehow the /doc page is doing it, but I don't see how. Is that some Scribunto feature? Yaron Koren (talk) 14:29, 6 May 2019 (UTC)
The doc page is just transcluded at the top of the module. I did weird things with titleparts on that particular one to make the display on the doc subpage completely different from the display on the module page. Also I just realized that I deleted all Cargo from this within an hour of setting it up because I did change to DPL, whoops. Maybe this is a better example of how doc pages are transcluded to module pages. --RheingoldRiver (talk) 09:37, 7 May 2019 (UTC)

Calendar display that lists different text than page name but still works as a link[edit]

I am trying to set up a calendar that displays something other than a page name in the event, but that can still link to the event page. I tried generating a link with something like

CONCAT('[[', workdays._pageName, '|', person, ' - ', Location, ']]') = name

but that just shows the []s, it doesn't use them to designate a link. I could not find an alias to use for this in the calendar documentation, and the "link field" available for the gallery etc don't seem to work. Is there a way to do this? Thanks! Tenbergen (talk) 05:52, 8 May 2019 (UTC)

I think the following will work, though I'm not sure: "fields=workdays._pageName,CONCAT(person, ' - ', Location)=name". The documentation could be better, I admit... I'm not sure I understand it myself. :( Yaron Koren (talk) 03:23, 10 May 2019 (UTC)
Thanks Yaron, that does the trick; so does that mean the first field listed will always be taken as the "link" value, and there is no named alias this, and the name as "what yous see"? I am trying to think how I would update that in Extension:Cargo/Display_formats#calendar. Tenbergen (talk) 22:45, 22 May 2019 (UTC)

Group or Order by YEARWEEK (or by anything...)[edit]

I can get Cargo to display the following: |fields=YEARWEEK(_pageData._modificationDate) - but can't get it to work with |group by= or |order by=. Is this possible somehow? Thanks. Jonathan3 (talk) 13:13, 16 May 2019 (UTC)

The SQL produced has an ORDER BY but only based on the order the fields are listed in the |fields= line, and there is no GROUP BY in the SQL at all. Just tried a really simple query and the same applies, i.e. |group by= and |order by= are being ignored. Jonathan3 (talk) 13:20, 16 May 2019 (UTC)

"Group by" works elsewhere on my website so it must be something stupid I'm doing... ignore the question for now :-) Jonathan3 (talk) 22:13, 16 May 2019 (UTC)

Correct storage of values of multiple instance templates only after Recreate Data on main cargo table[edit]

Hello everybody,

may I ask you for some time and expertise on an issue related to cargo storage of values of a multiple instance template (MIT).

Here some details:

In the form of an indicator database I use the functionality of multiple instance templates to enter current measurementvalues of the corresponding indicator. It is implemented in this way within the form of the indicator database:

{{{field|current|holds template}}}

{{{for template|assessment|multiple|add button text={{int:add-assessment}}|embed in field=indicator[current]}}}
;{{int:date}}:{{{field|assessment date|input type=datepicker|disable input field|week start=1|highlight days of week=0|date format={{int:date-format-form}}|size=8}}}
;{{int:current}}:{{{field|assessment current|size=10|default=0}}}
{{{end template}}}

All values of the MIT are stored in an additional cargo table called "assessment".

Based on the most recent currentvalue of that table, I try to calculate the current progress of the indicator and store it to the cargo-table "indicator". The formular of the progress calculation is like that:

(current - baseline) / (target - baseline) * 100

Baseline and target can be entered in the form of the indicator database, while the current value(s) are entered via MIT as stated above

The corresponding store command in the indicator template looks as follows:

{{#cargo_store: 
_table=indicator
|progress={{#expr: ({{#cargo_query:
                      tables=assessment
                     |fields=TRIM(current)
                     |where=_pageID="{{PAGEID}}"
                     |limit=1
                     |order by=date DESC
                     |more results text=
                     |no html
                    }} - {{{baseline|}}}) / ({{{target|}}} - {{{baseline|}}}) *100}}
}}

The problem is now, that every time I add a new instance with a recent current value, cargo stores "0" to the progress column. First of all I was assuming that it is maybe an data update problem and a null edit on the indicator would solve the problem. But I was mistaken. The only way to fix the problem is to recreate the indicator cargo table. After recreating the table the progress column is being calculated correctly. But as soon as I add a new instance or edit an existing instance of the MIT progress is again "0" and will only be calculated correctly after recreating the (indicator) table once again.

Anybody an idea, what could cause the problem of false storage of the current progress and how it can be resolved?

Thanks in advance. Axel

It's not surprising that this is not working - the page is querying itself, and it's storing the computed data at the same time that the original data is being stored. So everything depends on the timing - which templates get called first. I don't know what the best solution for this is. Would it be possible to not store the "progress" at all, but instead query it when needed? Or would that lead to queries that are too complex for #cargo_query? Yaron Koren (talk) 16:58, 21 May 2019 (UTC)

Text-file output display format[edit]

The T223886: Add a bibtex format page on Phabricator reminded me of something...

Would it be possible to add a text-file Export display format, with the text being formatted using a template in the same way as with the normal Template display format?

I'd like to be able to offer people a link to a RIS file for import into Endnote (reference management software). And maybe other types of file too.

Thanks. Jonathan3 (talk) 22:06, 23 May 2019 (UTC)

I wonder if it'd be cool to make a way of exporting to any other format in that fashion, e.g. a filename=foo.txt parameter would give text, and filename=foo.gpx would give XML (both coming from a template, as you say, via the |format=template |template=foo parameters). Maybe there would be weirdnesses that would arise with that though; it's not always wise to generalize things too much. Sam Wilson 12:24, 26 May 2019 (UTC)
Or maybe, in an ideal world, have all output formats default to displaying on screen (including csv etc), and have a new “destination” parameter to override that eg destination=txt, destination=csv to save as file, or destination=link to display link to display in new tab/window.
This could be in conjunction with format=template and template= (eg for RIS format) or without that (eg for CSV or other formats that Cargo itself can create).
It would also allow new formats to be added to Cargo in future (without then needing format=template any more).
I haven’t fully thought this through and maybe (even if it makes sense) it would require too much of a change to Cargo code to be worth it. Jonathan3 (talk) 14:39, 26 May 2019 (UTC)