Extension talk:Cargo

From MediaWiki.org
Jump to: navigation, search

Changes to Cargo content via XML Import won't update tables?[edit]

We used the MediaWiki Import/Export special pages to allow mass changes to some pages holding Cargo content, but related tables didn't update afterwards unless we recreate data (or save individual pages) manually. Is this expected behavior, or have we done something wrong? Thanks!

--198.49.180.254 14:26, 4 May 2017 (UTC)

That sounds like a bug in Cargo - my guess is that Special:Import doesn't call the hook that Cargo relies on to know when a page has been re-saved. So maybe it's really a bug in MediaWiki; I don't know. Yaron Koren (talk) 14:47, 4 May 2017 (UTC)

Hiding the "where" column(s) (SOLVED)[edit]

Hi there. I'm trying to run a query to show a list of events - a personal timeline - on each character sheet, based on the person's name showing up in the event.

{{#cargo_query:
tables=EVENT
|fields=EVENT._pageName=Date, EVENT.EVENT_Description=Description, EVENT.EVENT_Characters
|where=EVENT.EVENT_Characters HOLDS "{{PAGENAME}}"
|format=dynamic table}}

The events show up fine, but the last column is redundant, and I'd like to hide it. Is there a way to have the output table not show a column that is needed only for the where clause? --Brilligtove (talk) 17:44, 12 March 2017 (UTC)

Just don't include it in the "fields" parameter - you can have fields in "where" that aren't in "fields". Yaron Koren (talk) 21:40, 12 March 2017 (UTC)
Blimey, that was easy. I definitely tried that at one point but had other syntax errors to deal with and didn't go back to try it again. Thanks!--Brilligtove (talk) 17:31, 13 March 2017 (UTC)

Creation of tables fails on last column[edit]

There is an SQL-error apparently when trying to create the last column defined:

Software:

Cargo 1.3, MW 1.27.1, PHP 5.6.30, MariaDB 10.0.29

Template:

{{#cargo_declare:_table=Knowledge_Bar_Foo
|Knowledge_HasInfoa = Wikitext
|Knowledge_HasInfob = Boolean
|Knowledge_HasInfoc = String
|Knowledge_HasInfod = Date
|Knowledge_HasInfoe = Date
|Knowledge_HasInfof = Boolean
|Knowledge_HasInfog = Boolean
|Knowledge_HasInfoh = Boolean
|Knowledge_HasInfol = Boolean
|Knowledge_HasInfom = Wikitext
|Knowledge_HasInfon = List (,) of Page
|Knowledge_HasInfoo = List (,) of Page
|Knowledge_HasInfop = List (,) of Page
|Knowledge_HasInfoq = List (,) of String
|Knowledge_HasInfor = List (,) of String
|Knowledge_HasInfos = List (,) of Page
}}

Error:

php cargoRecreateData.php --table Knowledge_Bar_Foo
Recreating data for Cargo table Knowledge_Bar_Foo in 5 seconds... hit [Ctrl]-C to escape.
Deleting and recreating table...
A database query error has occurred.
Query: CREATE TABLE `MyPrefix_cargo__Knowledge_Bar_Foo__Knowledge_HasInfos` ( `_rowID` Int, `_value` Varchar(300) )
Function: DatabaseBase::query
Error: 1103 Incorrect table name 'MyPrefix_cargo__Knowledge_Bar_Foo__Knowledge_HasInfos' (localhost)

This operation also fails via the GUI. Help appreciated. --91.65.247.224 16:26, 17 March 2017 (UTC)

That's odd... I have no idea. Especially not why it fails on that table/column, and not on the previous ones. Could you try removing that "HasInfos" line from #cargo_declare, and see if it works without it? Yaron Koren (talk) 17:13, 17 March 2017 (UTC)
Turns out that we ran into multiple issues here.
Underscores cannot be used to define a field so the template must be like this and things roll:
{{#cargo_declare:_table=Knowledge_Bar_Foo
|KnowledgeHasInfoa = Wikitext
|KnowledgeHasInfob = Boolean
|KnowledgeHasInfoc = String
|KnowledgeHasInfod = Date
|KnowledgeHasInfoe = Date
|KnowledgeHasInfof = Boolean
|KnowledgeHasInfog = Boolean
|KnowledgeHasInfoh = Boolean
|KnowledgeHasInfol = Boolean
|KnowledgeHasInfom = Wikitext
|KnowledgeHasInfon = List (,) of Page
|KnowledgeHasInfoo = List (,) of Page
|KnowledgeHasInfop = List (,) of Page
|KnowledgeHasInfoq = List (,) of String
|KnowledgeHasInfor = List (,) of String
|KnowledgeHasInfos = List (,) of Page
}}
So the script probably already failed on the lines before but silently because of the second issue we got a visible error:
The second issue was that on the same template #cargo_store did not have a line for the KnowledgeHasInfos field so I got
PHP Notice:  Undefined index: KnowledgeHasInfos in /extensions/Cargo/parserfunctions/CargoStore.php on line 293
Notice: Undefined index: KnowledgeHasInfos in /extensions/Cargo/parserfunctions/CargoStore.php on line 293
Same for line 315 of that file.
It seems to be working now. Thanks for the suggestion which helped track down the issue. --91.65.247.224 09:09, 19 March 2017 (UTC)
Still out of luck. Now I get
[7450cee4a6402af82ba10421] /wiki/Special:CargoTables/Knowledge_Bar_Foo MWException from line 526 of /extensions/Cargo/CargoSQLQuery.php: Error: no field named "" found for any of the specified database tables.

Backtrace:

#0 /extensions/Cargo/specials/CargoTables.php(101): CargoSQLQuery->setDescriptionsForFields()
#1 /includes/specialpage/SpecialPage.php(479): CargoTables->execute(string)
#2 /includes/specialpage/SpecialPageFactory.php(576): SpecialPage->run(string)
#3 /includes/MediaWiki.php(282): SpecialPageFactory::executePath(Title, RequestContext)
#4 /includes/MediaWiki.php(735): MediaWiki->performRequest()
#5 /includes/MediaWiki.php(509): MediaWiki->main()
#6 /index.php(43): MediaWiki->run()
#7 {main}
--91.65.247.224 09:16, 19 March 2017 (UTC)
Removing all the blancs from #cargo_declare and #cargo_store does not help either. I also created a second template KnowledgeFooBar but this special page still fails. --91.65.247.224 09:24, 19 March 2017 (UTC)
Actually, field names can contain underscores. Can you run regular #cargo_query calls on this table, or do those fail as well? Yaron Koren (talk) 22:52, 19 March 2017 (UTC)
Turns out I told only half of the fun regarding "#cargo_declare" and "#cargo_store". Both functions contained invalid characters like umlauts when declaring fields. Now after only using characters MySQL can handle in names things seem to work. Three wishes if possible from what I have learned now: 1. The user or at least the sysop should get a warning that invalid characters were used, 2. the user/sysop should get a warning that the fields specified with both functions should match in name and number and 3. cargoRecreateData.php or a new script should have an option to delete tables if no longer used on the wiki. --91.65.247.224 15:38, 27 March 2017 (UTC)
Ah - that makes sense. #1 is a very good idea - I should have added that check in a long time ago. #2 I'm not sure is possible, unfortunately. For #3 - you should be able to delete any table at the page Special:CargoTables. Is this table not showing up there? Or would you prefer to do this from the command line in any case? Yaron Koren (talk) 15:54, 27 March 2017 (UTC)
I figured that 2 might be pretty tricky. For 3 yes, I prefer to have a command line option. If there is a listing option for cargo tables this will be nice too. --91.65.247.224 16:04, 27 March 2017 (UTC)

Okay, I looked into this, and it turns out that MySQL - and thus Cargo - can handle accented characters in table and field names. There was just a bug in the code that caused the error message you saw. I just checked in a fix for it, so if you get the latest code, you may be able to go back to the field names you used to have. Yaron Koren (talk) 02:17, 28 March 2017 (UTC)

Sorry that it took a while. No the issue caused by e.g. umlauts is still there using current master (d54c207) of Cargo. --91.65.247.224 17:30, 7 April 2017 (UTC)
That's not good. What's the exact error message you see? Yaron Koren (talk) 18:38, 7 April 2017 (UTC)
It is basically the same error but from another line:
[203dd3ab9e2c92b6eab6cd24] /wiki/Special:CargoTables/Knowledge_Bar_Foo MWException from line 565 of /.../w/extensions/Cargo/CargoSQLQuery.php: Error: no field named "" found for any of the specified database tables.

Backtrace:

#0 /.../w/extensions/Cargo/specials/CargoTables.php(115): CargoSQLQuery->setDescriptionsForFields()
#1 /.../w/includes/specialpage/SpecialPage.php(479): CargoTables->execute(string)
#2 /.../w/includes/specialpage/SpecialPageFactory.php(577): SpecialPage->run(string)
#3 /.../w/includes/MediaWiki.php(282): SpecialPageFactory::executePath(Title, RequestContext)
#4 /.../w/includes/MediaWiki.php(735): MediaWiki->performRequest()
#5 /.../w/includes/MediaWiki.php(509): MediaWiki->main()
#6 /.../w/index.php(43): MediaWiki->run()
#7 {main}
--91.65.247.224 16:52, 25 April 2017 (UTC)
That's too bad. This is actually the same line, unfortunately - it's just that other parts of the code have moved around. What is the name of the field that is causing the problem, do you know? Yaron Koren (talk) 19:33, 25 April 2017 (UTC)
A bit sad. I just run cargoRecreateData.php after upgrading to 1.3.1 and it ends without issues. The issue when trying to access the special page on wiki remains the same. Withing the template I do not really see what could cause the issue. No special chars etc. --91.65.247.224 14:15, 17 May 2017 (UTC)
Could you email me the template definition - or pastebin it and link to it here? Yaron Koren (talk) 16:06, 17 May 2017 (UTC)
Here we are. --91.65.247.224 16:33, 17 May 2017 (UTC)

Aha! That's very helpful. My fix for the previous problem with non-ASCII characters ended up causing a problem with fields with numbers in their name. I just checked in a fix, so hopefully now everything works - ASCII and non-ASCII characters alike. At least, I hope so. Yaron Koren (talk) 17:40, 17 May 2017 (UTC)

Great that this was of help. Sometimes the small things can be troubling. Affirmative, current master of Cargo makes the issue go away. Thank you for the fix! --91.65.247.224 15:43, 18 May 2017 (UTC)

Pre- and post-nominal letters for surnames in List of String[edit]

I have several pages, relating to court cases, each of which was decided by one or more judges.

The judges have pre-nominal (e.g. DJ Bloggs or HHJ Bloggs) or post-nominal (e.g. Bloggs J or Bloggs LJ) letters.

I would like to display the full title for the judge on each case page, but use Special:Drilldown on the surname.

The reason for this is that, although the surname is unique, the letters may change over time (e.g. in a 2000 case it may be Bloggs J, but the same judge in 2010 may be Bloggs LJ).

What would be the best way to implement this?

I had thought of using three fields, all Lists of String, for pre-nominal, surname, post-nominal, but I don't think there is any way to manipulate the individual results of Lists (in order to display pre-nominal + surname + post-nominal in a template).

Alternatively I could maybe use a list of wikitext like "[[Bloggs]] J, [[Jones]] J" and in the judge surname pages have a template query using HOLDS LIKE on the surname, but this wouldn't help with the Special:Drilldown interface.

Any ideas would be very welcome :-) Jonathan3 (talk) 22:58, 20 April 2017 (UTC)

Are there going to be pages for the judges? Or would you have that only if it's necessary for filtering? And why filter on, say, "Jones", instead of "Jones, J"? What if there's more than one Jones? Yaron Koren (talk)
Third attempt at replying. The mobile site crashed on my iPad...
I don't plan to have a page per judge. Ideally I'll write that code for you to have Miga-like links as an option for lists of strings etc. But more limited functionality could be provided by a judge page which lists each relevant case page. Perhaps automatically-created?
A judge's pre- or post-nominal letters may change over time so it would be much better (easier, less prone to error) to be able to filter on name only. Or have a way of linking each one so that filtering on one automatically includes the others. For example, HHJ Jones, Jones J and Jones LJ may be the same person.
At each level a more junior judge uses his Christian name (in addition to surname) as disambiguation if there is another judge with the same surname. There may be ambiguities between levels - I am not sure - but I have never seen this, and would be happy to proceed on the basis that it's unlikely and I could add Christian names for disambiguation on my website if it became necessary.
Perhaps the simplest solution/workaround would be to use name only in the list of string (for filtering etc) but have an extra text field for the [name with] correct pre- or post-nominal letters for the judge(s) on each individual case page. That has its advantages - mainly flexibility, e.g. strictly "Jones and Bloggs JJ" is correct whereas "Jones J, Bloggs J" is wrong - but it's still not my preferred option. Jonathan3 (talk) 10:18, 21 April 2017 (UTC)
Alright. It seems to me that the right answer is a variant on your idea at the end - have a Text field for the exact wording of the authors list, and have a separate field, holding a list of Strings, for however you want to store the information as data, whether it's last names only or anything else. It's redundant, but it seems like the easiest approach. Yaron Koren (talk) 12:28, 21 April 2017 (UTC)
Thanks. I'll give that a go. Jonathan3 (talk) 21:04, 23 April 2017 (UTC)

Hierarchies and categories[edit]

I have some plain wiki pages which I would like to convert to using Cargo. I am using four sets of categories per page. Three of these can easily be changed to Cargo fields (e.g. year). But the fourth category, in relation to the page subject matter, is part of a hierarchy which can be browsed by Extension:CategoryTree. I see from Extension:Cargo/Known bugs and planned features that a hierarchical structure for fields is not currently possible, but what could I do in the meantime?

Would it be possible to have a field containing a List of String, corresponding to the existing subject matter categories, which could be used in a template to put pages into categories? If so, it would have the twin benefits of allowing the categories to be browsed by CategoryTree, and allowing for them (via the field) to be browsed using Cargo's drilldown page. What would be the best way of implementing this? Thanks in advance. Jonathan3 (talk) 21:03, 23 April 2017 (UTC)

Yes, that's possible - use the tree input type to select the values within the form, and then use #arraymap in the template to create a category tag for each value (see the "For category names" subsection in that link). Yaron Koren (talk) 23:48, 23 April 2017 (UTC)
Thanks. The tree input type example on DiscourseDB briefly flashes up on my iPad Safari normally (with a category structure) but then ends up showing empty check/radio box inputs. Otherwise it looks like what I need and I guess it works on a "real computer". Jonathan3 (talk) 21:19, 24 April 2017 (UTC)
Oh, I had no idea that "tree" failed on the iPad - I'll have to look into that. Yaron Koren (talk) 01:43, 25 April 2017 (UTC)
The same thing happens when I try on various browsers on the PC. Maybe I am selecting the wrong option. The URL is: http://discoursedb.org/wiki/Special:FormEdit/Category_input_test/Testing Jonathan3 (talk) 00:01, 27 April 2017 (UTC)
Oops! I made a change to the code last week that clearly broke the "tree" input - sorry about that. I just checked in what I think is a fix. Yaron Koren (talk) 02:41, 27 April 2017 (UTC)

Cargo query on Drilldown page[edit]

It would be good if it were possible to add a Cargo query (or template containing one) to the Drilldown page. For instance, for one table I'd like it to show a map, and for another table I'd like it to show the "summary" field from the relevant pages. Is this something that would be possible in future? Jonathan3 (talk) 21:55, 26 April 2017 (UTC)

It would be great to show a map if the table has a Coordinates field - it's actually one of the planned features to have that done automatically. Allowing additional display fields is an interesting idea - Semantic Drilldown has that feature, so it's not inconceivable. Yaron Koren (talk) 02:57, 27 April 2017 (UTC)
I have added a proposed patch for this on Phabricator at task T166536. I hope you find it useful! :-) Jonathan3 (talk) 01:03, 30 May 2017 (UTC)

timeline display format displays blank[edit]

I'm using a Referata wiki with Cargo. The timeline display format used to work, but now displays blank. (The calendar display format does work, but is not useful for my purposes, since I want to be able to scroll quickly over a span of at least 60 years.) Please confirm that timeline should work (it does work on the example given in the Extension:Cargo/Display formats page), and if so, suggest what I might try to get it to work again. I tried each Skin option and each Date format option, with the same blank result in each case.--Egnatoff (talk) 16:32, 29 April 2017 (UTC)

What's the URL with the problem? If you don't want to share the URL, what's the query? Yaron Koren (talk) 14:10, 30 April 2017 (UTC)
Here's the URL of the query page: Even Timeline and here's the query:
==Concerts by Date==
{{#cargo_query:
tables=Concerts|fields=_pageName,ConcertDate|order by=ConcertDate|order by=ConcertDate
|format=timeline|height=200! 
}}

--Egnatoff (talk) 01:35, 2 May 2017 (UTC)

Oops! It looks like "timeline" has been broken for a while. I just fixed it, I think, on Referata (that's the nice thing about having access to the server where the problem is). I also plan to check in this fix. Yaron Koren (talk) 03:09, 2 May 2017 (UTC)
Hurray! Thank you very much. I will have several thousand events so the timeline display will be a very important browsing mechanism.--Egnatoff (talk) 02:32, 3 May 2017 (UTC)

Cargo table list coordination problem[edit]

Example: Music concert database

Problem: Coordinating two lists from different tables—ensuring they match on data entry, and displaying them nicely. In particular, instruments are listed in the Pieces table and performers are listed in the PiecePerformances table. They need to match.

Tables in question: Field1 (type), Field 2 (type), . . .

Pieces: Page, Composer (page), Title, . . . (where Page is calculated: Title—Composer)

PiecePerformances: Page, Piece (page), Concert (page), Performers (list of page) (where Page is called: Piece performed at Concert)

Concerts: Page, SeriesTitle (page), Title, ConcertDate, ConcertTime, etc.(where Page is calculated: SeriesTitle—Title—ConcertDate)

A concert page displays all the PiecePerformances for that concert.

Here are the two things I haven’t figured out how to do:

1. A pieceperformance page should display the performers along with the instrument(s) that each performer plays, something like this:

performer1 (instrument1) - performer2 (instrument2) - . . .

2. I would like the data entry form for a piece performance to take the entered name of the piece and then get and display for that piece each instrument, with a box for entering the corresponding performer. That would seem to be a two-step process.--Egnatoff (talk) 03:24, 30 April 2017 (UTC)

If I understand the problem, you want to define some piece as a duet for flute and bassoon, and then, every time someone fills out a form for some performance of that piece, you want to have pre-set fields for "Flute:" and "Bassoon:". If that's correct, unfortunately there's no easy way to do that. The only approach I can think of involves the Scribunto extension, and dynamically-generated forms. I would think it's better, though, to just have editors enter the instruments and musicians every time themselves (using a multiple-instance template). It's more work for editors, but then again it's a more foolproof system. What if someone decides to perform that piece on glockenspiel instead? It's still the same piece, isn't it? Yaron Koren (talk) 14:28, 30 April 2017 (UTC)
That's certainly a more direct solution to the main problem, and would accommodate cases where instruments are changed, and whether or not there is a conductor. Point taken, thanks.--Egnatoff (talk) 18:48, 30 April 2017 (UTC)

Hidden parameter[edit]

The documentation at Extension:Cargo/Storing_data states, in relation to the "hidden" parameter:

"hidden - takes no value. If set, the field is not listed in either Special:ViewTable or Special:Drilldown, although it is still queriable."

I added "(hidden)" to some fields in an existing table and recreated the data. This prevented those fields from appearing in Special:Drilldown, but they still appear in Special:CargoTables. To be honest, this is what I would like to happen, but it does not match the documentation. Maybe this is just something which applies only when an existing table is recreated. Incidentally, I think "Special:ViewTable" should be changed to "Special:CargoTables". Jonathan3 (talk) 23:05, 1 May 2017 (UTC)

Those were indeed some bugs in the documentation. I think I just fixed them. Thanks! Yaron Koren (talk) 02:06, 2 May 2017 (UTC)

Duplicates Every Time[edit]

I updated to the latest version via git this morning to get rid of an error with getNamespace, and now every thing seems to make two entries.

Templates defined on the page multiple times, as well as templates that are just defined once per page. Example

Clearing the table and then re-populating does seem to clear up the issue.--Cody3647 (talk) 20:41, 2 May 2017 (UTC)

I don't know what's causing this; it could be some difference in storage between your old version and the current one. I would suggest just recreating all the tables; if that doesn't fully solve the issue, let me know. Yaron Koren (talk) 20:57, 2 May 2017 (UTC)
I have a similar problem using Cargo to store external data. Any new entry added to the database is fine and gets stored once. As soon as I re-save an existing data record by editing the page, the database starts to hold multiple copies of the entry (sometimes 2, sometimes 3). As if the old saved versions are not deleted. Using 'recreate data' through a browser cleans up the multiple entries of any records and it's fine again. However, I don't want to 'recreate data' every time a entry get's modified. Any idea what might go wrong? Used PageForm to get external data: http://csdms.colorado.edu/wiki/Form:Reference-auto1. --Albert Ke (talk) 16:48, 8 May 2017 (UTC)
(Cargo version: latest (installed May 1st); MW 1.28.1; PHP 5.6.30(fpm-fcgi); mysql 5.6.23-log)
Does this happen for every form, or just this one? And does it happen on every page save? Yaron Koren (talk) 19:54, 8 May 2017 (UTC)
I only made 1 pf using cargo, so not sure. Will do a test on that. Happens for every entry after I re-save that entry. --Albert Ke (talk) 21:42, 8 May 2017 (UTC)
Sorry this took a while. I created a very basic Page Form example using cargo to define & store just 1 field and I'm having the same issue: I can create a new entry for the database and it will be stored once. However, editing this page again using 'Edit with form' and this entry results in 3 data rows to the same page. I did test to see if it matters using instead of 'Edit with form' the wiki 'edit', and it does, now cargo will keep it to 1 data row. The used form: http://csdms.colorado.edu/wiki/Form:TestCargo The used template to define, store and show cargo data: http://csdms.colorado.edu/wiki/Template:HoldName. Any idea what I'm doing wrong? --Albert Ke (talk) 22:18, 12 May 2017 (UTC)
SOLVED (at least for me). Thank you Yaron for solving this problem. I had included a 'namespace' and 'query' statement in the '#formlink' definition and both shouldn't be there. Now data just get stored once, also after editing using the 'Edit with form' option. Many thanks Yaron!!! --Albert Ke (talk) 15:07, 14 May 2017 (UTC)
It's great that that fixed it! Although my guess is that it was actually the removal of both namespace settings in the form's page name formula, in this edit, that solved the problem. That's just a hunch, though. It's worth looking into this further, at some point. Yaron Koren (talk) 01:01, 15 May 2017 (UTC)


I have just installed Cargo and Page Forms on a new offline wiki and seem to be getting random duplicate table rows. This is happening across two of the three Cargo tables I've defined so far. The first time I create the Cargo table from the template page every record has a duplicate. I have then tried deleting the table and creating it again. Subsequent table creations result in no duplicates (for a while), then randomly a duplicate will occur. Any suggestions on how to troubleshoot? --Nha4601 (talk) 00:20, 8 June 2017 (UTC)

I don't know. I wish I knew why it was happening - or why it seems to happen a lot more for some people than others. If anyone having this problem could give me access to their system, it might help a lot. Yaron Koren (talk) 15:08, 8 June 2017 (UTC)

I also have the problem… When I'm almost done with modifications, I launch the recreate data script and modify ASAP the indexes to the table so that the _pageID is unique. I haven't tested yet whether it writes all recordings or misses some, but that's the only way I found (and it works only because there is one and only one template call in each page). But I'm thinking that if the bug is hard to correct, letting the user manually specify a "unique" attribute to the appropriate columns could be a failsafe (the question remains about the update of entries, if I have time to check that aspect, I'll post about it). --Loizbec (talk) 14:54, 19 June 2017 (UTC)

Importing data (e.g. from Access)[edit]

I have been sent a database which would be too big to enter into Cargo manually. Is there any way to import automatically? I guess I would need an extension/bot to create the necessary wiki pages. Jonathan3 (talk) 22:30, 3 May 2017 (UTC)

Yes - use the Data Transfer extension. Yaron Koren (talk) 00:49, 4 May 2017 (UTC)

Storage of lists: how to store and query for the order of values[edit]

I later need to sort and query by the order in which the template instances are appearing in a wiki page. In other words, the order of appearance represents the priority, and it is easy to implement drag-and-drop with Extension:Page_Forms.

Now I wonder how to get the ordering information into Cargo's database for later query.

The templates are hopefully stored in the database in the same order in which they appear on the page, so you may be able to accomplish this by just sorting on the "_ID" field. If that doesn't work, you may need to install the NumerAlpha extension, and add a new field to that multiple-instance template, setting the field to the value of #counter within #cargo_store. (Let me know if that doesn't make sense.) Yaron Koren (talk) 01:33, 8 May 2017 (UTC)

"Illegal mix of collations" error [RESOLVED][edit]

Here is an error from Cargo:

A database error has occurred. Did you forget to run maintenance/update.php after upgrading? See: https://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script
Query: SELECT `Description` AS `Description` FROM `wm_cargo__Event` WHERE _pageName="‎<event name>" ORDER BY `Description` LIMIT 100 Function: CargoSQLQuery::run Error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' (<MySQL host URL>)

Here's what happened next:

  • I changed the title of the event page, to something purely alphabetical, which made the error go away.
  • I then changed it back to the original title, and the error came back.
  • Finally, I changed it to a slightly different version of the original (just by changing a small c to a capital C) and the error went away again.
  • Even now, having deleted the page with the original title, a Cargo query on the original title returns the same error, which I guess this because MediaWiki has saved the page title as a deleted page.

I can't think what caused this problem, which has not happened before. I wonder whether it may be has to do with the two changes necessary to the the auto complete work (which you suggested above). In that case I suppose the answer would be to update Page Forms properly.

My Cargo database tables are all latin1_swedish_ci but my MediaWiki database tables have ended up being a mixture of latin1_swedish_ci and utf8_general_ci. I asked about this here before and the answer was, sensibly enough, along the lines of "if it ain't broke don't fix it"... But now I wonder whether I should perhaps look into this properly.

Do you think this might be a Cargo thing, or a particular problem with my installation? Thanks in advance. Jonathan3 (talk)

It's not surprising that the query fails regardless of whether the page exists or not - this query is just accessing a single Cargo table, and doesn't look at the core MediaWiki data at all. So everything has to do with the page name, which I don't know. Though it could well be that replacing the double quotes in the query's "where" clause with single quotes would fix the issue also - I would definitely try that. Yaron Koren (talk) 14:30, 9 May 2017 (UTC)
The name was alphabetic except for a colon and a hyphen (never caused a problem before and other pages are similar without errors). When I do the same query with a page which never existed it says No results but gives no error. I'll try to look into the problem today or tomorrow. Jonathan3 (talk) 17:36, 9 May 2017 (UTC)
I am completely stumped! I can't do anything to make the error appear today. Jonathan3 (talk) 12:50, 10 May 2017 (UTC)
I think have found the source of the error :-) When I cut and paste the page title from the page itself, or from Special:Recentchanges the query works fine. The error only appears when I cut and paste the page title from Special:Newpages - when I put a page title from this source into an online Charset Decoder it looks fine as UTF-8 but there is an extra ‎ before the page title in ANSI. Jonathan3 (talk) 01:03, 23 May 2017 (UTC)
Ah, an invisible character... very annoying, and very hard to debug. I'm glad you were able to find this one. Yaron Koren (talk) 02:06, 23 May 2017 (UTC)
Cheers. It happens every time a page name is cut and pasted from Newpages, including from Wikipedia itself. Jonathan3 (talk) 18:40, 23 May 2017 (UTC)

Wikitable syntax exported from Page Schemas generated templates[edit]

I'm using Page Schemas to setup the schema for a new site and generate the starter templates. I noticed that when using the option "display if not empty" the resulting wikitable syntax in the form doesn't work quite right. The syntax itself seems to be correct but due to some odd line breaks it's not showing the data in each cell and is displaying a "-" instead and not breaking into rows. A partial example of code is below

{{#if:{{{Access|}}}|
{{!}}-
! Access
{{!}} {{{Access|}}}
 }}{{#if:{{{Visitor Information|}}}|
{{!}}-
! Visitor Information
{{!}} {{{Visitor Information|}}}
 }}{{#if:{{{Time Required|}}}|
{{!}}-
! Time Required
{{!}} {{{Time Required|}}}
 }}

Removing the extra space from the end of the If statement and making a new row with the next one seems to solve the issue.

{{#if:{{{Access|}}}|
{{!}}-
! Access
{{!}} {{{Access|}}}}}
{{#if:{{{Visitor Information|}}}|
{{!}}-
! Visitor Information
{{!}} {{{Visitor Information|}}}}}
{{#if:{{{Time Required|}}}|
{{!}}-
! Time Required
{{!}} {{{Time Required|}}}}}

A second issue is that the option for "Hide" is not actually hiding the data in the resulting wikitable. The label is hidden but the value is displayed in the previous cell.

add map type variable for googlemaps[edit]

Is there a way to add the maptype variable or other common variables that manage display types for Google Maps such as whether to show the zoom controls, map switcher, etc? I want to set the default map to Terrain type. In the SMW Maps extension I can add type=terrain, but that doesn't seem to work under Cargo.

Yes, I haven't added many parameters for the "googlemaps" format because I wasn't sure which ones people actually use. The SMW/Maps equivalent has about 50 parameters - which of these would you want? Yaron Koren (talk) 13:35, 4 June 2017 (UTC)
I'd be interested in experimenting with the resizable and enablefullscreen parameters. Jonathan3 (talk) 07:55, 8 July 2017 (UTC)
What about zoom controls, etc.? Yaron Koren (talk) 13:42, 9 July 2017 (UTC)
They're there by default and I can't see why anyone wouldn't want them :-) Jonathan3 (talk) 23:17, 9 July 2017 (UTC)
P.S. What would be helpful, but I don't think is even one of the options for (Semantic) Maps, would be to have numerals on the markers which could correlate to a list of locations displayed beneath the map. Sometimes you find this on "store locator" websites etc. Jonathan3 (talk) 23:20, 9 July 2017 (UTC)
What was the original question about, then? And for the last thing - there has long been a desire for some kind of interface that makes it easier to find a map location given its name - when I was still working on Semantic Maps, we wanted to add something like that too. I agree that it's a great idea. Ideally it could also involve highlighting or otherwise isolating a point on the map when its name is hovered over, or clicked, in the list. Yaron Koren (talk) 14:42, 10 July 2017 (UTC)
The original question wasn't mine :-) It sounds like he is interested in using map type etc and I'm interested in trying out the full screen/resize parameters. It would be fantastic if that multiple-marker functionality were to be added. Jonathan3 (talk) 18:03, 10 July 2017 (UTC)
Ah! That explains it. Yaron Koren (talk) 19:04, 10 July 2017 (UTC)
I've submitted a patch to Phabricator (task T170216) about a fullscreen parameter, as I would find this useful. I would be grateful if you could consider it. Jonathan3 (talk) 23:12, 10 July 2017 (UTC)

Do you definitely want this feature, or do you just want to experiment with it? And if you do want it, can you explain how it's useful? Yaron Koren (talk) 02:57, 11 July 2017 (UTC)

Yes I'd definitely like it (and have experimented with it now). When you have lots of markers it looks great to view on full screen. Jonathan3 (talk) 09:22, 11 July 2017 (UTC)
Can't you just set "width=100%"? I thought the big advantage of "fullscreen" is that it lets users toggle between the two modes, but I don't know how useful that is. Yaron Koren (talk) 11:56, 11 July 2017 (UTC)
Width=100% would make it the width of the current div. Yes, with full screen there's a button to press to enable it and you press escape or click a button or click on a marker's wiki link to exit it. I just think it looks great which I hope does make it "useful" :-) Jonathan3 (talk) 13:48, 11 July 2017 (UTC)
Interesting. What about enabling it all the time, then? Yaron Koren (talk) 14:05, 11 July 2017 (UTC)
That would suit me fine. One line of code would do it. It's quite an unobtrusive icon (top right of the map) so I don't think anyone would mind. Jonathan3 (talk) 14:24, 11 July 2017 (UTC)
PS I wonder whether it would answer the first question to have a general parameter for the googlemaps format which takes comma separated values within round brackets, which could contain any of the standard Google maps controls - so it would be possible to disable or enable anything, and you'd only need to write code to cover one extra parameter? Jonathan3 (talk) 14:30, 11 July 2017 (UTC)
That's an option, and I've thought about having something like that, although a general "other options" parameter may be too much of a hack. Another possibility is to have a "controls" parameter which lets people specify which controls they want. SMW has such a thing, although for some reason "fullscreen" isn't one of the allowed values - it gets a separate parameter. But "controls" would enable a lot of functionality without cluttering things too much. What do you think? Yaron Koren (talk) 03:53, 12 July 2017 (UTC)
Sounds like a plan. That would answer part of the original question and would suit me. Of the various controls, I'd probably enable "scale" too, as well as full screen. Jonathan3 (talk) 22:09, 12 July 2017 (UTC)
Alright, cool. I think it makes sense to enable all of them, if that parameter is getting added. Yaron Koren (talk) 15:44, 13 July 2017 (UTC)
I agree they all should be enabled in that sense (I meant I'd choose to enable scale etc in my maps) Jonathan3 (talk) 17:21, 13 July 2017 (UTC)
Oh... now I get it. Yaron Koren (talk) 18:44, 13 July 2017 (UTC)

External data not always saved[edit]

Hi Yaron,

Looks like external retrieved data not always gets stored by Cargo. I'm getting external reference data through rest API from [1] : (using the following template: [2]). Parts of data gets stored using cargo in [3]. Below 2 examples: a) data get stored; b) data doesn't get stored:

GETS STORED:
a) http://csdms.colorado.edu/wiki/Reference:Reference-000779 Data is retrieved from [1] using DOI (so [5]) and displayed on the page and stored in Cargo [4] (close to the bottom of the table).

DOESN'T GET STORED
b) http://csdms.colorado.edu/wiki/Reference:Reference-000780 Data is retrieved from [1] using DOI (so [6]) and displayed on page but not stored in Cargo [4] (Reference:Reference-000780 is missing from the table)


Turns out that data from a specific publisher (Wiley) doesn't get stored by Cargo, although I couldn't find any difference in the external data json file. Any idea why some data from 1 publisher doesn't get stored?

Thanks, and sorry for the many web links, --Albert Ke (talk) 16:44, 5 June 2017 (UTC)

[1]: https://en.wikipedia.org/api/rest_v1/data/citation/zotero/
[2]: http://csdms.colorado.edu/mediawiki/index.php?title=Template:CSDMS_reference_template_cargo&action=edit
[3]: http://csdms.colorado.edu/wiki/Template:RefsExt2
[4]: http://csdms.colorado.edu/mediawiki/index.php?title=Special:ViewData&limit=500&offset=0&tables=RefsExt2&fields=_pageName%3DPage%2CDOIentry%3DDOIentry%2CItemType%3DItemType%2CTitle%3DTitle%2CPublicationTitle%3DPublicationTitle%2CBooktitle%3DBooktitle%2CJournalAbbreviation%3DJournalAbbreviation%2CVolume%3DVolume%2CIssue%3DIssue%2CUrl%3DUrl%2CDOI%3DDOI%2CPagenumbers%3DPagenumbers%2CPublicationdate%3DPublicationdate%2CAccessDate%3DAccessDate&order_by=%60_pageName%60
[5] https://en.wikipedia.org/api/rest_v1/data/citation/zotero/10.1080%2F02626667.2017.1307571
[6] https://en.wikipedia.org/api/rest_v1/data/citation/zotero/10.1002%2F2016GL069262

Is it definitely all of the Wiley pages, and only the Wiley pages, whose data is not stored correctly? Yaron Koren (talk) 17:52, 5 June 2017 (UTC)
Yes, all the Wiley published papers I have tried so far (~100 references) don't get stored. Unsure why as their json file from [1] is identical with other journals references ([5] vs [6]). Total mystery to me. --Albert Ke (talk) 18:15, 5 June 2017 (UTC)
Yaron, I should mention that references from Wiley publisher were for some time not available through [1]; see also: https://phabricator.wikimedia.org/T165105. It seemed like [1] was blocked by the publisher although this changed recently, as you see by e.g. [6]. Could this be related, although external data is able to get data? --Albert Ke (talk) 18:33, 5 June 2017 (UTC)
That's odd indeed. The previous Wiley issue doesn't seem connected, but who knows. Looking through the templates, I notice some settings that should probably be changed in the Cargo declarations... again, I don't know if any of these are connected, but they seem like a good idea to change regardless. Here, I think that "Firstname" and "Lastname" should just be a String, not a list of Strings; and here, I think that "Url" should be of type "URL", and "Publicationdate" should be of type "Date". I would recommend changing them all - it wouldn't hurt, and who knows, maybe it'll fix this issue. Yaron Koren (talk) 18:48, 5 June 2017 (UTC)
Thank you Yaron. I made the 4 suggested changes ("Firstname", "Lastname", "Url" and "Publicationdate"), updated the cargo tables (php ./cargoRecreateData.php) but no luck; Wiley references won't show up unfortunately.... --Albert Ke (talk) 21:11, 5 June 2017 (UTC)
Alright - well, that was probably an improvement regardless. I tried to replicate this myself, and saw the same problem, and I may have found the issue: I just checked in a small change to External Data that seems to solve the problem, at least for me, though I'm not sure why. But please get the latest ED code, and let me know if that fixes anything. Yaron Koren (talk) 17:31, 6 June 2017 (UTC)
The issue is fixed but not sure if your change was part of it. Actually, now that I'm think about it, I cloned the latest Cargo, not ED (will still do tomorrow). The problem was the following; references from Wiley publisher were not available for some time. So when pages tried to collect data an error message would show. This was fixed recently by citoid. I repopulated the Wiley pages initially using cargoRecreateData.php, (so not editing each reference that contained data from Wiley). Individual pages were again showing external Wiley reference data (so error message was gone) but this data wasn't actually saved in Cargo. To get data in Cargo tables, each page that contains Wiley reference data needed to be edit for that. This is now done and all looks good after running cargoRecreateData.php. It does bring back an older problem of when saving external data page after it's first creation, it will save it multiple times. See for example Cargo table: http://csdms.colorado.edu/wiki/Special:CargoTables/RefsExt2 and than search for "Reference:Reference-000168". It is saved 3 times. You have to run cargoRecreateData.php to remove the 2 extra edits. --Albert Ke (talk) 01:43, 7 June 2017 (UTC)

order by integer…[edit]

Hi,

I have carefully created a cargo table with integer columns in order to be the sort key later on, without performing any test… And now my table is rich with over 100 entries I realize that cargo sorts integers as strings…

Below is

my table declaration[edit]

{{#cargo_declare:
_table = sons_LUCIOLE
|type = String (allowed values=mot,consigne,dialogue,feedback,stimulus,réponse,aide,comptine,bruitage)
|persos = List (,) of String
|mission = Integer
|etape = Integer
|fonction = String (allowed values=intro,conclu,auto,général,spécifique,N.A)
|id = String
|langue = String (allowed values=en,fr,en+fr,Ø)
|accent = String
|synopsis = String
|condition = String (size=256)
|transcript = Wikitext (size=10000)
|fichier = File
|validation = Wikitext (size=1024)
}}

my cargo query[edit]

{{#cargo_query:
tables=sons_LUCIOLE
|fields=id, _pageName, persos,  transcript
|where=sons_LUCIOLE.mission=1
|order by=sons_LUCIOLE.mission,sons_LUCIOLE.etape ASC, sons_LUCIOLE.fonction DESC
|format=dynamic table}}

my cargo result[edit]

Cargo sort error cargo output.png

the SQL query to confirm that the problem is in cargo[edit]

SELECT * FROM `wiki_cargo__sons_LUCIOLE` WHERE 1 ORDER BY mission, etape ASC, fonction DESC

the SQL query output[edit]

Cargo sort error SQL output.png

I'm not sure there's anything to do, but I'm welcoming any advice to sort this out ;) --Loizbec (talk) 12:43, 19 June 2017 (UTC)

Yes, that's an issue with the "dynamic table" format - it does its own custom sorting, and doesn't use the sorting from the database, because of the way the JavaScript is written. I'll have to look into that. Yaron Koren (talk) 15:31, 19 June 2017 (UTC)
Sorry, I'm only looking at this now, and now I realize that I don't know what the problem is. What should the order of those three rows be? Yaron Koren (talk) 03:49, 17 July 2017 (UTC)

Maybe a problem with HOLDS[edit]

I have a query with

|fields=... About ... |where=About HOLDS 'Example'

In the output, the "About" field only has "Example" listed as values, which is wrong as I know that "Example" is only one of the values in each list.

What do you think is up? Thanks Jonathan3 (talk) 22:20, 3 July 2017 (UTC)

A field that holds a list of values, like "About" in your case, is a "virtual field" - there's no DB field directly called "About", but rather two different fields that together represent its contents. The code has to figure out which one to use in any circumstances; in this case, it's clearly making the wrong decision. You can get around this bug by replacing "About" in the "fields" parameter with "About__full", or "About__full=About", etc. Yaron Koren (talk) 00:32, 6 July 2017 (UTC)
Using About__full=About worked fine, thanks. Jonathan3 (talk) 19:29, 6 July 2017 (UTC)

Maybe a problem with List of Integer[edit]

When I declare a table with List (,) of Integer only the first item in the list is displayed on Special:CargoTables. The $fieldValue variable at that stage of things is the full list delimited by spaces (e.g. "123 1234 12 2 3" (rather than one of the list items) so I guess the list was stored to the database with spaces instead of commas. What can I do to fix this? Thanks. Jonathan3 (talk) 22:56, 5 July 2017 (UTC)

Try using a delimiter other than a comma, like maybe ";". The commas may be confusing the code, which thinks that they are thousands separators. Perhaps commas (and periods) should simply not be allowed as delimiters for integers and floats... Yaron Koren (talk) 00:34, 6 July 2017 (UTC)
Good thinking. Changing to List (;) of Integer fixed the problem. The normal use of commas as thousand separators works fine too. I guess you're right that commas shouldn't be allowed as delimiters - or save the numbers without thousand separators and only recreate them when displaying the numbers. Can't see any easy way round the problem that full stops as delimiters would cause. Jonathan3 (talk) 19:39, 6 July 2017 (UTC)

Maybe a problem with List of Integer on Special:Drilldown page[edit]

I get the following error when I click on any value relating to a List of Integer:

Error: 1066 Not unique table/alias: 'cargo__Tablename__Fieldname'

I was just creating the List of Integer field to test something else, so this isn't a real problem for me, just something I wanted to point out in case there is something wrong. Jonathan3 (talk) 19:52, 6 July 2017 (UTC)

Correct way to alter structure of existing table[edit]

I always seem to have difficulties with this. What exactly is the correct procedure? From memory, I think this should work, all from the web interface:

  1. Edit table definition (and the query if necessary) in template page
  2. Change form page if necessary.
  3. Delete existing table
  4. Create new table
  5. Recreate data

Is there anything else?

Does it matter at what stage the existing table is deleted?

Are there any points at which it is necessary to use runJobs.php? Thank you. Jonathan3 (talk) 19:20, 7 July 2017 (UTC)

Well, steps 3-5 are all just one action, from the web interface... but you probably knew that. It's not necessary to run runJobs.php, but it would probably make the new table get filled up more quickly. Yaron Koren (talk) 21:07, 7 July 2017 (UTC)
Thanks. Yes I think recreate data drops and recreates the table. I've had such trouble with it all (no doubt something I don't understand about how my databases are set up) that I wondered if there was a belt-and-braces approach! Jonathan3 (talk) 07:46, 8 July 2017 (UTC)
What kind of trouble have you had? Yaron Koren (talk) 13:42, 9 July 2017 (UTC)
I've tried to describe it here before (unsuccessfully) but will try again at some point in future if it crops up again. Jonathan3 (talk) 23:16, 9 July 2017 (UTC)

I'm also having the same issue, I've added a column to an existing table in the template, deleted the original cargo table, Clicked on recreate data and the new table is empty. Am I missing something? Cheers.

Have you tried using runJobs.php? Assuming the new table has the correct columns. Jonathan3 (talk) 14:49, 18 July 2017 (UTC)

Cargo and Elastic Search[edit]

Yaron, We have set up our wiki and are running 1.27.3, for the long term support, we are using Cargo ver 1.3 for all of our data. Now we want to set up search and have installed elastic search with Cirrus search extension. Our question is how do we get elastic to include the data in our Cargo tables? We have been told by two different people that elastic and Cargo cannot work together see link: https://www.mediawiki.org/wiki/Help_talk:CirrusSearch, we thought we would contact you to see if that is indeed true, that we cannot use Cargo and Elastic search. If it is true, do you know of any other search option that will work with the Cargo extensions.

They're right, unfortunately - there's no integration of the two extensions at the moment. What you can do instead is have Cargo store the full text of every page - see here for how to do that. Then, within Special:Drilldown, you'll see a text search input for every table; and you can also run queries that include a full-text search, using MATCHES. Yaron Koren (talk) 19:55, 19 July 2017 (UTC)

Treatment of blank date fields[edit]

When I don't enter a date for a date field it defaults to the current date when it is displayed in the template page, but there is blank when it is displayed in a dynamic table query. This is only a minor issue but something I thought I would mention for you. I'm not really sure whether it's a Page Forms or Cargo thing. Jonathan3 (talk) 00:08, 5 August 2017 (UTC)

I'm not sure it's either. What's the template page? Yaron Koren (talk) 01:38, 7 August 2017 (UTC)
You're right. It's neither. The template uses {{#time: j/n/y | {{{Date field|}}} }} and {{#time: j/n/y | }} defaults to today's date (e.g. now it displays 7/8/17). I guess I need to check for an empty date field, or just not leave it blank... Jonathan3 (talk) 20:00, 7 August 2017 (UTC)