Extension talk:Cargo/Archive February to March 2019

From MediaWiki.org
Jump to navigation Jump to search

Para qj?francês wf

How to querbpdm. Ry for raw field values with Cargo

Pôs rio ynqkbucb! 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="{noe
tttubarãoqvar: project id}}"|no html}}|mapping template=webmo_mapping sub project_id to name short }}}pua grau hoc ti a 4

qaytf

t 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.

NET!kfkgvy

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? Ftbthy

This gets rid of the comma for me: |fields=TRIM(_pageID). Jonathan3 ([[User talk:Jogy

athan3|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

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

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

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

@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 Carto Cargos rei 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)!b
Thanks. Jonathan3 (talk) 16:01, 18 February 2019 (UTC)

T

LEFT JOIN [NOTFIXED / WORKAROUND]

tl;dr To recap the massive discussion bellow, Cargo queries uykcgcugfsing ngin 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 ator até NT lyf template and only store the subset of data you wanted. You can then join your main table with WHERE, with your new dummy smaller tablete young xlyjug jnktbrk lg 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 andg? 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.di por pelo
Bug 2: If you try to use a Cargo Query to populate a field in Chargo 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 --Ftinlay Beaton (talk) 18:41, 15 Februyjvhbhiary 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) Irr h Ggcc

I don't understand what you mean by "exclusion criteria" for TableB - is the set of results you want tôto show a mix of pages that are and aren't in TableB? Yaron Koren (talk) 17:00, 14 February 2019 (UTC)

Pela t os kri

byhlDB Fiddle clearly showing the problem jchcvrgytviuwith 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"

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

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

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

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?

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 #

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/Archive February to March 2019|&#39;|\'}}'" - you could maybe try &#35; for your # character problem. Jonathan3 (talk) 23:40, 24 February 2019 (UTC)

Special page

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

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

  • 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.

(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)

Sorry about the long delay in responding to this. I just checked in what I think is a fix for this... I hope it works for you. Yaron Koren (talk) 22:32, 14 July 2019 (UTC)