Extension talk:Cargo/Archive January to March 2016

Order by with format category produces unexpected results

When using the category format and having a list of names, I ordered by the surname (which is set as a separate field). The pages all end up ordered by surname but letter headers are created for the first letter of the page name not based on order by surname. So it ends up with something like this.

K

Katya Alkaev

R

Raine Almasy

N

Nicholas Bevans

M

Maiko Biladeau-Yukawa

A

Alexandra Carstairs

V

Virgil Carstairs

A

Aisling Cooper

If the category format was able to use the default sort key that would at least help but it doesnt seem like it should be this way either.--Cody3647 (talk) 02:38, 1 January 2016 (UTC)

Ah, that's true. I just added a new parameter for the "category" format, called "header field", that takes in the name of an alternate field to get the header letter from. If you try it out, please let me know how it works for you. Yaron Koren (talk) 02:42, 4 January 2016 (UTC)

Still having the problem, http://absitomen.com/lexicon/Portal:Characters --Cody3647 (talk) 01:27, 9 January 2016 (UTC)

It looks like it's doing the right thing - you just need to also order by that field. Yaron Koren (talk) 05:09, 9 January 2016 (UTC)

Ah, yep that fixed it. Thanks --Cody3647 (talk) 06:21, 9 January 2016 (UTC)

Gallery Format Options

The gallery format with SMW/SRF had the ability to set the image caption based on a property. Is there a way to do similar with a cargo field used for the caption? And also to set the heights and widths, and turn off the file names and file size.--Cody3647 (talk) 02:23, 2 January 2016 (UTC)

Forgot that I asked about this here, but I uploaded a patch with the gerrit patch uploader to add the gallery parameters to the query format. --Cody3647 (talk) 19:01, 15 January 2016 (UTC)

Yes, I saw - sorry; it's on my to-do list to look into this. Yaron Koren (talk) 19:24, 15 January 2016 (UTC)

No problem, just saw this when writing up another question.--Cody3647 (talk) 19:29, 15 January 2016 (UTC)

$wgCargoAllowedSQLFunctions in LocalSettings Not Working

I have RAND, GROUP_CONCAT,IN added to $wgCargoAllowedSQLFunctions in LocalSettings and I'm getting the error "the SQL function "RAND()" is not allowed." I just pulled the latest version of Cargo from git and Im running MW 1.25.5. --Cody3647 (talk) 01:14, 9 January 2016 (UTC)

What does the #cargo_query call look like? Yaron Koren (talk) 05:09, 9 January 2016 (UTC)

{{#cargo_query:tables=Characters|fields=_pageName|where= status="npc" AND npc_permission<>"closed"|order by=RAND(19112024)|limit=1|more results text=}}, I was able to fix it by adding the functions straight into the array in Cargo.hooks.php.--Cody3647 (talk) 06:10, 9 January 2016 (UTC)

Aha - that was due to a bug in your MediaWiki version's handling of extension.json. I just added what I think is a workaround to the Cargo code. Yaron Koren (talk) 16:58, 10 January 2016 (UTC)

Just started the process of testing the upgrade to mediawiki 1.26 and $wgCargoAllowedSQLFunctions is not working again on my test wiki. --Cody3647 (talk) 17:33, 29 January 2016 (UTC)

That's not good. What exact MW version are you using? Yaron Koren (talk) 19:18, 29 January 2016 (UTC)

It was 1.26.2 but I was having a lot of weird issues with the install/upgrade of the test wiki so I have removed it all until I have time to deal with it. --Cody3647 (talk) 06:07, 2 February 2016 (UTC)

Yes, okay - I see the same problem. I just set it so that extension.json is only used for MW 1.27 and higher - hopefully the problem has been fixed in 1.27. Yaron Koren (talk) 19:26, 3 February 2016 (UTC)

A big table — a big problem

I can not save the full amount of data. Action "recreatedata" and cargoRecreateData.php don't help. The problem is clearly in time-outs, as the number of rows after each regeneration turns different. Pages are big, some contain about a thousand queries. The table is simple, it is less than ten fields. My wiki includes about 800 pages with the table queries to create 17 thousand rows. It created about 13 thousand. What should I do? --StasR (talk) 09:50, 13 January 2016 (UTC)

By "queries", I assume you mean "template calls". I can see the problem: when it does its batching, the web interface assumes that each page contains an average of one or two calls to #cargo_store - not hundreds or thousands. The batching definitely needs to take that into account, if that's possible. In the meantime, have you tried calling the command-line script? That seems like it should still work. Yaron Koren (talk) 13:45, 13 January 2016 (UTC)

Yes, I mean "template calls". I wrote: Action "recreatedata" and cargoRecreateData.php don't help; script created ~13,000 rows vs 10-11K on "recreatedata". --StasR (talk) 14:32, 13 January 2016 (UTC)

Oh, right. Did the script crash? Have you tried running it more than once? Yaron Koren (talk) 15:23, 13 January 2016 (UTC)

When the interactive performance, all the job are seen in the job queu? I made a long pause and run the script again. I did it twice, and the results are identical :-) The number of entries is still different from the expected. Do you allow one page to create two identical rows in the table? --StasR (talk) 15:39, 13 January 2016 (UTC)

I didn't understand the first question, but the script doesn't use jobs - only the web interface does. The fact that the results are identical is actually a good sign - it means that the problem is not with the size of the data, but with something else. Yes, you can have identical rows from one page (they won't be fully identical, since they'll have different _rowID values). Is there any way you can run some analytical queries on the data set that was generated, using "group by" or something, to try to figure out if there's a pattern to the data that's missing? Did certain pages not get stored at all, or maybe the issue is rows with certain values? Yaron Koren (talk) 16:11, 13 January 2016 (UTC)

First question was: can be invisible remnants of the interactive 'recreatedata'? (The job queu was empty, but I ran the script immediately after 'recreatedata'.) I'm looking for errors, and is found only one, with two identical calls on the same page. --StasR (talk) 16:37, 13 January 2016 (UTC)

Identical calls are wiki Scribunto problem, not Cargo. Direct template calls work properly. --StasR (talk) 16:50, 13 January 2016 (UTC)

Oh, I see. Yes, it's certainly possible that the jobs created by the web interface could be interfering with the script. I would just delete everything from the job queue, if you want to "cancel" the recreatedata action. Yaron Koren (talk) 17:36, 13 January 2016 (UTC)

It seems to be on the theme: (MediaWiki_1.24/wmf8) git #d18ba4e9 - Add PPFrame::isVolatile and PPFrame::setVolatile (bug 46815, bug 31834) --StasR (talk) 21:04, 13 January 2016 (UTC)

Sorry, I don't understand the connection to those MW issues. Yaron Koren (talk) 15:25, 14 January 2016 (UTC)

Combination Outline and Template Formats

So I'm trying to get the function of the outline format to display a series of events and articles with more information and with formatting that is beyond just a simple list. I could use just the template format but that would end up listing the dates, since events and articles often have the same date, over and over again, hence wanting to use the outline. I tried using concat but that quickly got weird and confusing.

I also tried creating a new format but my php skill level especially with classes has made it difficult without repeating a lot of code. Would it be possible to create a new format that combines the two formats? Or other suggestions for getting the same result?--Cody3647 (talk) 19:28, 15 January 2016 (UTC)

Would adding a 'template=' parameter to the 'outline' format solve this problem? Yaron Koren (talk) 21:51, 15 January 2016 (UTC)

Probably yes. To replace the list/parenthetical list output of the remaining fields. --Cody3647 (talk) 21:58, 15 January 2016 (UTC)

I did figure out a way to do it with one level using the template format and the variables extension. --Cody3647 (talk) 22:07, 15 January 2016 (UTC)

Alright - it does seem like adding a "template" parameter would allow for an easier solution. Yaron Koren (talk) 04:37, 17 January 2016 (UTC)

Line breaks in wikitext format

I have a field that is setup as Wikitext. I have a block of text that has a line break in it (empty line line break not a hardcoded line break) and when getting the field via a query the line break is gone. I double checked in the database itself and the line break is still in the saved version. --Cody3647 (talk) 03:36, 16 January 2016 (UTC)

Is it a single line break, or a double line break? Because single line breaks are ignored by the MediaWiki parser. Yaron Koren (talk) 04:38, 17 January 2016 (UTC)

Double line break, but I think I figured it out. I had it in a div, but moved it into a blockquote and now the line break shows up. --Cody3647 (talk) 17:23, 17 January 2016 (UTC)

Dashes in field value in WHERE clause

| where = name="--Тестов, Иосиф Виссарионович" gives Error in "where" parameter: the string "--" cannot be used within #cargo_query. Why? --StasR (talk) 15:08, 17 January 2016 (UTC)

It's because "--" is used for comments, and comments can be used for SQL injection. Though I think "--" within quotes doesn't pose a threat, so that should be fixed. On the other hand - why do you have that format for the value? I didn't expect many values to contain this string. Yaron Koren (talk) 03:43, 18 January 2016 (UTC)

Random Question Marks appended to File Name

I have a SF field that takes a file name {{{field|image|values from category=Avatar|remote autocompletion|image preview|placeholder=FirstnameLastnameAvatar.extension|class=fullwidth|uploadable|cargo table=Characters|cargo field=avatar}}}, it is saved to a cargo field that is defined as for a File.

13 of the 780 pages that have saved a file name in that field, have had random ? added to the end of the file name in the database. Some have had multiple ? (up to three in a row at the moment) added. Some are png, jpg, jpeg. The question marks are not showing up in the field/or wikitext (the image does display in the image preview).--Cody3647 (talk) 17:33, 17 January 2016 (UTC)

Very strange. Is there any pattern to when they show up? Are some of the characters in the file names non-ASCII? Yaron Koren (talk) 03:46, 18 January 2016 (UTC)

None that I can tell. These are the names of the files as they show up in the database: Casey O'Doherty.png?, Anadotti Squiggs.png?, Dingy.png?, John Flickwick.png???, Obderedria Pienas.png??, Ligeia Canterbury.png?, Philo Falkin.png?, Mace Steinbeck.png??, Emily Flickwick.png???, Theta Pepper.jpeg?, Ruby Flickwick.png?, Johann Storm.jpg?, Wolfgang Storm.png? The other 750+ are just fine and are in the same format, entered in the same way. I tried editing the question marks out in the database, but its back on the next save.--Cody3647 (talk) 04:21, 18 January 2016 (UTC)

Not sure how but on closer inspection of the source when on the edit source page, somehow the html entity for left to right marks were added, sometimes multiple times in the cases of the multiple question marks.--Cody3647 (talk) 04:30, 18 January 2016 (UTC)

Oh, interesting. Could it be these left-to-right marks also exist in the page source, but are not visible in the browser? Yaron Koren (talk) 04:59, 18 January 2016 (UTC)

They were, Ive removed them all now, no idea how they got there. --Cody3647 (talk) 13:14, 20 January 2016 (UTC)

Time Format

This is partly SF and partly Cargo. But basically I need to store time independent of the date. I know i can split them out in the query, but SF when doing datetime automatically makes it midnight for the date entered instead of leaving it blank on saving with just the date and no time. I thought about making it with text fields but I want to be able to sort on time, and Im guessing that a mysql text field is sorted different from a time field and there's no way to set the mysql field as time that I can find.--Cody3647 (talk) 20:06, 23 January 2016 (UTC)

I didn't think a "Time" type would be that useful - maybe that was a mistake. But I think you could do a hack to enable sorting, even if you implement it as text fields - have another template/Cargo field that holds the number of minutes (or seconds) in the time, by doing a mathematical calculation with the values of the text fields, probably using #expr. Then sort on that field. Yaron Koren (talk) 03:33, 24 January 2016 (UTC)

Storing Extension:External_Data content with Cargo

I am trying to move data from a CSV into Cargo via Extension:External_Data but I'm only able to grab the first row by using the #external_value function within #cargo_store. I tried putting #cargo_store inside #for_external_table so it would iterate and store all rows but it seems to evaluate #cargo_store first and inserts a single row with my param text (instead of the actual value as assigned by #for_external_table). In a nutshell: Is there a way to store full CSV files into Cargo? --Patrick

I found the solution, it is documented on the External Data page. Make sure to copy the examples, I believe there is a bug in #display_external_table. The documentation says to use a "data" parameter but the example uses a "date" parameter and "date" is the one that worked for me. --Patrick

Oops - that was a typo in the example page; thanks for catching it. Actually, I think it works either way - if you don't specify a "data" parameter, #display_external_table just displays (and if you use Cargo, also stores) every field from the CSV. So having a "date" parameter instead is like having no parameter at all. But I just changed the example to use "data=", and it seems like it still works. It didn't work for you? In any case, either way is fine. Yaron Koren (talk) 21:48, 27 January 2016 (UTC)

Haha your error caused me to find my error. I was calling #display_external_table with the external CSV values instead of the internal variable names. Since it defaults to everything the "date" parameter was doing nothing and when I tried assigning "data" it was getting invalid variable names with spaces in it, etc. causing it not to work. The definition for the date parameter led me to believe I should populate it just like I did for #get_web_data

data - the data mappings between external variable and local template parameter; much like the data parameters for the other functions

It's actually a mapping of internal variables in this template to variables in the template being called. Also I didn't realize the data parameter was optional, that is awesome because I always plan on using all columns so that is a lot less leg work.

Okay, great. I don't understand the confusion, though - there's only one template, no? Yaron Koren (talk) 01:32, 28 January 2016 (UTC)

I made a wrapper template so that I can define the columns of the file in one place. The template accepts the URL of the file so different pages can easily show different files that are in the same format. --Patrick

Missing Gallery Module for Packed-hover gallery

After updating mediawiki to 1.26.2, the packed-hover gallery option of the gallery format was displaying as a list instead of a gallery. After looking around, it seemed that the javascript for galleries was not loading. Adding $this->mOutput->addModules( 'mediawiki.page.gallery' ); to the display method of CargoGalleryFormat got things looking correct again. --Cody3647 (talk) 01:19, 10 February 2016 (UTC)

Strangely, I'm not seeing this problem - I'm running MW 1.26.2, and "packed-hover" shows up for me (more or less). Are you using the latest version of Cargo, 0.11? By the way, I say "more or less" because the display of "packed-hover" shows up for me the same as that of "packed-overlay" - nothing happens when I hover over the image. But the same thing happens when I view the examples at Help:Images - "packed-hover" does nothing on hover. And I've tried it on multiple browsers. Are you seeing the same thing? Yaron Koren (talk) 02:55, 12 February 2016 (UTC)

Yep, have Cargo 0.11 installed. And packed-hover works for me. The overlay only shows when hovering. This is what it looks like with the line I added removed http://prnt.sc/a2r8w8 --Cody3647 (talk) 15:56, 13 February 2016 (UTC)

Concepts

Hi I wondered if Cargo has an equivalent to Concepts in SMW?

Many thanks

Duncan

No, unfortunately - out of curiosity, what would you use them for? Yaron Koren (talk) 00:38, 14 February 2016 (UTC)

Hi Yaron. I wanted to have reusable query snipets. Eg I could define queries that could be used within another query. Having said that, I guess I could probably do that with templates?

Many thanks - Duncan, 14 Feb 2016

I suppose you could, with "mini-templates" for each parameter, but it might be easier to just re-do the query every time. Yaron Koren (talk) 12:18, 14 February 2016 (UTC)

SF and Cargo produce "Error: operator for the virtual field 'file_extension' must be 'HOLDS', 'HOLDS NOT', 'HOLDS LIKE' or 'HOLDS NOT LIKE'."

Hello folks

I have a form with the following field defined:

 {{{field|file_extension|cargo field=file_extension|placeholder=Allowed file extensions|size=80|input type=tokens|cargo table=instance|list|max values=42}}}

of which the data is stored in table instance and field file_extension. The field itself is defined as

 file_extension=List (,) of Text

When I try to access the form, I get the error message Error: operator for the virtual field 'file_extension' must be 'HOLDS', 'HOLDS NOT', 'HOLDS LIKE' or 'HOLDS NOT LIKE'.

What I was expecting was an input field where already given values for table instance field file_extension would appear in the suggestion list.

Can somebody please tell me what I did wrong?

---

Tobias (talk) 12:51, 23 February 2016 (UTC)

OK, just upgraded to SF 3.4.3-alpha. Works as intended. :) Sry about all the fuss :/

---- Tobias (talk) 13:52, 23 February 2016 (UTC)

Big page

There is a page with a large number of requests (write data in the two tables, approximately 1000 lines each). When saving, no error is shown. The page is displayed completely. But the data in the tables are recorded only about 700-900 records each (unstable). It's like a timeout, but why are there no warnings? --StasR (talk) 19:41, 28 February 2016 (UTC)

The debug log contains nothing interesting, just recorded few lines. --StasR (talk) 17:34, 29 February 2016 (UTC)

I added an extra wfDebugLog() calls in CargoStore.php. It turns out that for an arbitrary number of the last rows run() is not called. The template that contains #cargo_store, is performed for all rows. --StasR (talk) 21:19, 29 February 2016 (UTC)

I had a similar problem awhile back with lots of fields and Yaron pointed me here: http://php.net/manual/en/info.configuration.php#ini.max-input-vars --Kc5vcx (talk) 03:14, 1 March 2016 (UTC)

Thanks. But I have not lost the last fields. Lost the last rows. Since the number of lost rows is variable, and the page text are always presented completely, I assume that the problem is in asynchronous write table rows. --StasR (talk) 16:48, 1 March 2016 (UTC)

I don't know why there's no warning message, but clearly there appears to be some sort of timeout. 2,000 calls to #cargo_store in one page (if I understand the numbers correctly) is a lot! Why so many, if I may ask? Why not split up the data across multiple pages? Yaron Koren (talk) 19:55, 1 March 2016 (UTC)

It is clear that 2000 is very much. This wiki is similar to wikisource, and the page is a document of about 1000 people. And, most importantly, how can I find out that so many turned into too many, if there is no warning? --StasR (talk) 20:47, 1 March 2016 (UTC)

Issue with Boolean Fields and Semantic Forms

Boolean Fields are not updated with semantic forms. Semantic Forms insert a Sí/no (yes/no in spanish) word in the template [1] but Cargo inserts Sí (yes) as false (0) in the table [2].

[1] http://innovacion.educa.aragon.es/wiki/Certamen_de_Lectura_en_Voz_Alta

[2] http://innovacion.educa.aragon.es/wiki/Especial:CargoTables/RecursosDBN

--Lmorillas (talk) 15:54, 16 March 2016 (UTC)

You're using a rather old version of Cargo - could that be the issue? Yaron Koren (talk) 19:41, 16 March 2016 (UTC)

Yes, I'm using the 1_25 branch. I see it's outdated. But now some SQL functions are not allowed now (LEFT, GROUP_CONCAT, ...) I'm going to try with master. Why can't I use now these functions? --Lmorillas (talk) 21:32, 16 March 2016 (UTC)

Updated with $wgCargoAllowedSQLFunctions[] --Lmorillas (talk) 21:43, 16 March 2016 (UTC)

Solved the issue with Boolean fields. Thanks. --Lmorillas (talk) 21:52, 16 March 2016 (UTC)

MWException from line 157 of CargoUtils.php

Hello,

I just installed Cargo in a LAMPP developement environment utilizing MediaWiki 1.25.3, PHP 5.6.12 and MySQL 5.6.26 for testing purposes with a separated database "wiki_cargo". The user is the same as for the wiki itself, except it has additional rights to create and drop tables in this database.

After creating the template holding the cargo_declare tag and successful creation of the new table, I get an error following the link to the special page showing the cargo tables:

[ddc1fe5b] /index.php/Spezial:CargoTables/TableName MWException from line 157 of /opt/lampp/htdocs/spielewiki/extensions/Cargo/CargoUtils.php: Error: table "TableName" not found.

When I look up the databases, I find the table is created with the fields as expected, but the wiki tables cargo_pages and cargo_tables contain no data.

Also, when using the template to store data, nothing will be stored inside "TableName". I tried several different table names, due to the thought, it might be a problem with the name containing special characters, but with a simple name like "test" it was the same.

May there be an issue with the database name "wiki_cargo", does cargo itself rely on another extension I didn't install or do I miss something else?

With best regards, --~o)sabine(o~ (talk) 07:02, 20 March 2016 (UTC)

Sorry about the problems - and thanks for the detective work. This might be related to the fact that you're using an outside database - I haven't done enough testing with that setup. I doubt the DB name is the issue, though; though I could be wrong. And no, Cargo doesn't require any other extensions.

Are you using a very recent version of the Cargo code? Because on March 17, a few days ago, I introduced a bunch of changes which unfortunately included some DB-related bugs. I just checked in fixes for those bugs. If you have a recent version of the code, I would suggest getting the latest code, and trying it again. If that's not the issue - could you try running the script /maintenance/cargoRecreateData.php, and see if that either works or produces a more helpful error message? Yaron Koren (talk) 19:10, 20 March 2016 (UTC)

Wow, fast reply. I'm not very used to such a fast response :)

It's v0.11 of Cargo I've done the testing with. Changed the settings to use the wiki database for Cargo tables, but the result is the same, even when running cargoRecreateData.php:

Recreating data for Cargo table test in 5 seconds... hit [Ctrl]-C to escape.
Deleting and recreating table...
A database query error has occurred.
Query: CREATE TABLE `cargo__test` ( _ID Int NOT NULL UNIQUE, _pageName Varchar(300) NOT NULL, _pageTitle Varchar(300) NOT NULL, _pageNamespace Int NOT NULL, _pageID Int NOT NULL, `name` Varchar(15), `amiga` Varchar(15), `class` Varchar(15), `exp` Int, `minlp` Int, `maxlp` Int, `ac` Varchar(2), `notes` Text, `special` Varchar(6), `counter` Int )
Function: DatabaseBase::query
Error: 1050 Table 'cargo__test' already exists (localhost)

Changed the user to root, just to exclude missing rights - no change.

I'm still wondering a little about the first error message. It mentions the missing table "test" and not "cargo__test". Do you think this could be a hint or do I just see things? --~o)sabine(o~ (talk) 10:07, 21 March 2016 (UTC)

Hey, and this reply is even faster! That's actually a different error than the one before - it is happening because of the conflicting data, in that the "cargo__test" DB table exists, but there is no entry for it in the "cargo_tables" table - and that missing entry is the real issue. Please delete the "cargo__test" table, and try running the script again. And the naming thing is how it's supposed to work: "test" is the on-wiki name for the table, but "cargo__test" is the actual DB table name. Yaron Koren (talk) 13:39, 21 March 2016 (UTC)

After deleting the database and running the cargoRecreateData.php script again, I found out, the user must have index rights and the creation and the insertion of the data works correctly. Thanks for all the incredible fast help :) --~o)sabine(o~ (talk) 16:55, 21 March 2016 (UTC)

That's great! The documentation should probably list somewhere all the rights that DB users need. Yaron Koren (talk) 17:57, 21 March 2016 (UTC)

I read the "Download and installation" and "Quick start guide" parts of the basic documentation and most parts of "Using Cargo" . If I didn't miss it, only the rights to create and to drop tables were mentioned.

Well, and one problem was a special character in the table name. I substitute ' with ´ to keep names as close to the original as possible and either Cargo or MySQL do not like ´s in the table name.

Unfortunately I encountered another strange behaviour - see next section --~o)sabine(o~ (talk) 08:49, 22 March 2016 (UTC)

You mean, you tried to use ´ in the name of the table itself? That doesn't seem like it would work - that character is used within MYSQL (and other SQL systems) as a quote mark around table and field names. Yaron Koren (talk) 13:29, 22 March 2016 (UTC)

Yes is the short answer xD

The explanation is a bit longer. I'm migrating data from an older wiki installation which utilized DataTable to store and retrieve data on different sites. DataTable uses one big table and the "table" name you supply will be used as the first field name. Whenever you query a "table" it just combines one where clause for the table field with your clause.

So after I decided to upgrade to a more recent version of mediawiki, I had to recognize, DataTable isn't longer working. I tried DataTable2, but it's to restricted in its use if you don't want to program some workarounds in lua by yourself.

To make things shorter, now I give Cargo a try and the wrong table name was a relict from the DataTable times...

As far as I can see, Cargo does everything I need. I tested only counting the records, but suppose sum or max queries will work, too. And Cargo does two things I was barely missing with DataTable:

1. The pages show the actual data - at least with caching disabled. When using DataTable I had to save the querying page to get the actual data.
2. Cargo handles template args. DataTable used its own format for storing the data. Template args were saved as the names of the template args not as the values.

At least I would say, Cargo has another fan :) --~o)sabine(o~ (talk) 15:27, 22 March 2016 (UTC)

Ah, DataTable - that's a name I haven't heard in a long time. I wasn't even aware that there was a DataTable2. I'm glad you like Cargo! It certainly has taken a lot of effort. By the way, you don't need to disable caching to see the results of queries, though that's the easiest way to do it - see here. Yaron Koren (talk) 16:18, 22 March 2016 (UTC)

Thank you for the hint. The MagicNoCache extension sounds interesting. I will give it a try when I find the time. --~o)sabine(o~ (talk) 12:37, 24 March 2016 (UTC)

some lines are saved twice

When filling up the database, I encountered a strange behaviour. Though every page that uses Cargo for storing data just contains one record, some lines are displayed twice when using the special page CargoTables or when querying the data. The doubled lines seem to vary when I add another record.

I added 74 records, as the category counter says, and CargoTables count 79. When I use the maintenance script, this is corrected, but after adding some more records, twin lines appear again. Any idea? --~o)sabine(o~ (talk) 09:03, 22 March 2016 (UTC)

cargo_pages and cargo_tables contain the right number of entries. In this case the number of pages is 98 and the number of lines in the defined table is 106. Every page defines one record, remember?

As you can see, the number of doubled lines is increasing when adding more records. --~o)sabine(o~ (talk) 10:23, 22 March 2016 (UTC)

Duplicate records are definitely still a problem in Cargo - but as far as I know, it only happens after you call "Recreate data" from the wiki - especially if you call it multiple times in quick succession (i.e., you call "recreate data" before the last one completed). I could be wrong, though. Did that happen in this case? Yaron Koren (talk) 13:36, 22 March 2016 (UTC)

No, I just added some records.

I'm about creating the next table. This time I will keep an eye on the lines when inserting new records, maybe a scheme shows up. --~o)sabine(o~ (talk) 15:34, 22 March 2016 (UTC)

Okay, that's not good. But yes, if you can offer any more insight, that would definitely be welcome. Yaron Koren (talk) 16:26, 22 March 2016 (UTC)

I could work on a second and third table without any issues - at least I thought so until I looked at table 1. While adding records to table 2 and 3 new doubled lines appeared in table 1.

For testing purposes I declared a table named "aaaaaaaaaa", but double lines furthermore appeared in table 1.

Table 1 has the lowest page number I declare tables within so far and I suppose, it has something to do with it, since the table name seems not to have any effect.

That's very interesting. Does every page still call one template, and belong to one table?

I only migrated 2 new pages so far. Both hold data for table 2 and 3. You can compare them to pages for music albums, which store the main data like release date, publisher etc. and the song part, which contains title, composers, length etc.

To stay within this example, there's one template call for saving the main data, but showing them and the song data, which it fetches through comparation with the key field - in this case the name of the album - and displaying them in rows.

The song data are saved by using a template multiple times, which just stores them and displays nothing.

As I noticed, the lines in the first table are doubled when adding records and when retrieving a page. I just have to hit F5 some times to reload the page and can watch the number of rows in table 1 increasing.

So the "fowl" part has to be in a more global function, which deals with both storing and retrieving data. --~o)sabine(o~ (talk) 17:47, 26 March 2016 (UTC)

I'm pretty sure it relates to the job queue. I had to adjust some things in an often used template and got a quite huge job queue. So I set a page to auto reload ever 15 secs and while the job queue slowly decreases, lines in all three tables appear. --~o)sabine(o~ (talk) 18:47, 26 March 2016 (UTC)

The job queue is used when you call "Recreate data" from the wiki. Are you sure you haven't done that? Yaron Koren (talk) 14:43, 27 March 2016 (UTC)

In the evening I will have time to do some more investigation. I plan to run the script to get rid of the doublettes and have a clean base.

Let's see what happens, when the job queue is empty, the tables are cleaned and data are only queried. --~o)sabine(o~ (talk) 09:49, 29 March 2016 (UTC)

Well, I ran the script for a clean start and defined 3 new tables, of which one holds 2 list fields. All tables were successfully created and right now, after adding some data, everything looks fine. --~o)sabine(o~ (talk) 15:40, 1 April 2016 (UTC)

That's great to hear. Yaron Koren (talk) 17:33, 8 April 2016 (UTC)

row number for results

(separated from "some lines are saved twice") --~o)sabine(o~ (talk) 16:02, 8 April 2016 (UTC)

One new question came up: Does Cargo use a way to number the rows in a result record set which I can query? A kind of rowID? --~o)sabine(o~ (talk) 15:40, 1 April 2016 (UTC)

Yes - the field "_ID" for main tables, "_rowID" for auxiliary tables. Yaron Koren (talk) 19:32, 1 April 2016 (UTC)

Sorry, but I didn't express myself clear enough. Do you have a counter for rows in the result of a query? I'm looking for a way to hint a template which number the row has it is actually going to display. --~o)sabine(o~ (talk) 22:46, 1 April 2016 (UTC)

I've found an answer to this on this site. I failed in getting the first solution working with mysql 5.5.26, but the second worked.

Building a query like this:

SELECT DISTINCT @num := @num + 1 AS row_number, *

FROM clients

JOIN (SELECT @num := 0) AS n ON 1=1;

would give the desired row_number.

Now I am wondering how to translate this into a cargo_query?

Maybe the new section should title "how do I query the results of a query"? --~o)sabine(o~ (talk) 16:02, 8 April 2016 (UTC)

Sorry for not responding before. I don't know if that solution would work with #cargo_query, unfortunately - I'd guess no, but I could be wrong. Maybe the best solution is for Cargo itself to add in an index field, like "_resultNum" or something. Another option might be to use the NumerAlpha extension - I don't know if you've heard of it. Yaron Koren (talk) 13:35, 10 April 2016 (UTC)

Thanks again. NumerAlpha helps me out with that. I tested colouring every second line and some other little things that make reading tables a bit easier. --~o)sabine(o~ (talk) 17:30, 10 April 2016 (UTC)

Neither form of doing this will work in a cargo query. There is no way to add a "SET" command to the cargo query, and the cargo_query refuses to allow a "SELECT" in the FROM clause for security purposes. I'll try the NumerAlpha counter. 70.20.34.99 13:50, 10 April 2016 (UTC)

The NumerAlph parser function does not work in the cargo query, as it gives the error "Error: the string "#" cannot be used within #cargo_query.". I would suggest adding an enhancement request for this in the Cargo Query module. Every underlying database has a different and incompatible way of doing this. 70.20.34.99 14:06, 10 April 2016 (UTC)

It does seem like a reasonable addition. Just to be sure, though: are you using a template for the display? If so, have you tried putting the #counter function into the template itself? Yaron Koren (talk) 15:05, 10 April 2016 (UTC)

MySQL syntax for "limit"

[Split off from the discussion above. -Yaron]

Cargo allows to limit the results. Does it use the MySQL syntax? I tried to get the 3rd maximum out of 10, but "limit = 3,1" doesn't work. I tried adding "LIMIT = 3,1" to the where clause as a workaround, but you use a default limitation to 100 and the result was an error, because LIMIT was used twice. Is there a way to disable the default limitation or do you plan to extend the limit function to support the nth position? --~o)sabine(o~ (talk) 12:31, 24 March 2016 (UTC)

The best approach would be to add an "offset" parameter, which is actually supported already - you can use it at Special:ViewData. But why would you want to use it in a regular query? Yaron Koren (talk) 13:59, 24 March 2016 (UTC)

In this case, I used it to find the four maximum values of a field and sum them up. --~o)sabine(o~ (talk) 17:47, 26 March 2016 (UTC)

I don't see how an offset would help with that. What was the query you wanted to run? Yaron Koren (talk) 14:44, 27 March 2016 (UTC)

I didn't find a sql query to do this in one rush, so I used to retrieve the 1st, 2nd, 3rd and 4th maximum with separate queries and add them with #expr as a workaround.

In the meanwhile I found a way to do this with a template, so do not waste more brain capacity on this. ;) --~o)sabine(o~ (talk) 09:42, 29 March 2016 (UTC)

The "offset" parameter is very necessary! --StasR (talk) 08:21, 7 April 2016 (UTC)

Could you elaborate on that? Yaron Koren (talk) 11:40, 7 April 2016 (UTC)

The following portions of the records may require the same special handling (formatting) that first. --StasR (talk) 18:25, 7 April 2016 (UTC)

The "view more" stuff is handled by Special:ViewData, which allows for an "offset" parameter. The question is whether to also have an "offset" parameter for other querying methods, most notably #cargo_query. Yaron Koren (talk) 23:54, 7 April 2016 (UTC)

Yes, I was talking about #cargo_query. --StasR (talk) 10:35, 8 April 2016 (UTC)

In that case, I don't understand the use case you're talking about. Yaron Koren (talk) 12:28, 8 April 2016 (UTC)

Postgres Issues

I can get Cargo working with a MySQL database, no problems, right out of the gate. But I get quite a few errors trying with a Postgres DB hosted in AWS. Nothing strange happens when I create a table and click the create data table button. But trying to view the tables of a template gives "A database query error has occurred. This may indicate a bug in the software." Trying to create a page using the Author template, as in the quick start example, gives the same thing; the page itself displays "A database error has occurred. Did you forget to run maintenance/update.php after upgrading?" Turns out I get the same result on a locally installed Postgres DB as well, with any superuser. Is there some additional setup I'm overlooking? Thanks. Wmvolckmann (talk) 22:14, 30 March 2016 (UTC)

Could you add "$wgShowSQLErrors = true;" to LocalSettings.php? That will hopefully lead to a more informative error message. Yaron Koren (talk) 02:08, 31 March 2016 (UTC)

Alright, added it to LocalSettings. When making a page for an author, it says

Query:

INSERT INTO "cargo__Authors" ("Country","_pageName","_pageTitle","_pageNamespace","_pageID","_ID") VALUES ('Midgard','Fake Author','Fake Author','0','25','1')

Function: DatabasePostgres::insert

Error: 42703 ERROR: column "_pageName" of relation "cargo__Authors" does not exist LINE 1: ...t Wmvolckmann */ INTO "cargo__Authors" ("Country","_pageName..

And when making a page for a book, it says

Query:

INSERT INTO "cargo__Books"

("Year_of_publication","Number_of_pages","Year_of_publication__precision","_pageName","_pageTitle","_pageNamespace","_pageID","_ID","Authors__full","Genres__full") VALUES ::('2044-01-01','4','3','Fake Book','Fake Book','0','24','1',NULL,'Steampunk')

Function: DatabasePostgres::insert

Error: 42703 ERROR: column "Year_of_publication__precision" of relation "cargo__Books" does not exist LINE 1: ...__Books" ::("Year_of_publication","Number_of_pages","Year_of_p... ^

And finally, when trying to view the tables of Books, it says

Query:

SELECT _pageName AS "Page",Authors__full AS "Authors",Genres__full AS "Genres",Year_of_publication AS "Year of publication",Number_of_pages AS "Number of pages",Year_of_publication__precision AS "Year of publication__precision" FROM "cargo__Books" ORDER BY _pageName LIMIT 100

Function: CargoSQLQuery::run

Error: 42703 ERROR: column "year_of_publication" does not exist LINE 1: ...thors__full AS "Authors",Genres__full AS "Genres",Year_of_pu... ^

So the common thread appears to be a 42703 error. Wmvolckmann (talk) 05:41, 31 March 2016 (UTC)

Could you look into the cargo__Authors and cargo__Books tables and let me know what fields each one has? Yaron Koren (talk) 13:27, 31 March 2016 (UTC)

In cargo__Authors, I see columns: _id, _pagename, _pagetitle, _pagenamespace, _pageid, Country. In cargo__Books, I see columns: _id, _pagename, _pagetitle, _pagenamespace, _pageid, authors__full, genres__full, Year_of_publication, year_of_publication__precision, Number_of_pages. Wmvolckmann (talk) 17:49, 31 March 2016 (UTC)

Ah, that's very helpful. The issue is casing - for some unknown reason, PostgreSQL lowercases the names of fields that aren't put in quotes. I think I just fixed this in the code - if you get the latest Cargo code, and recreate the tables, the problems should hopefully go away. Yaron Koren (talk) 02:43, 1 April 2016 (UTC)

Alright, I grabbed the latest code, but I get some different errors now. I started with a new database just for good measure. When trying to create a table, it says (via the command line php update),

  Deleting and recreating table...

  A database query error has occurred.

  Query: CREATE INDEX page_id_Authors ON "cargo__Authors" (_pageID)

  Function: DatabaseBase::query

  Error: 42703 ERROR: column "_pageid" does not exist

When trying a second time, it says

  Deleting and recreating table...

  A database query error has occurred.

  Query: CREATE TABLE "cargo__Authors" ( "_ID" Int NOT NULL UNIQUE, "_pageName" Varchar(300) NOT NULL, "_pageTitle" Varchar(300) NOT NULL, "_pageNamespace" Int NOT NULL, "_pageID" Int NOT NULL, "Country" Varchar(300) )

  Function: DatabaseBase::query

  Error: 42P07 ERROR: relation "cargo__Authors" already exists

Furthermore, when I try to view the table for the Author template, it gives the error

   Query:

   SELECT "_pageName" AS "Page","Country" FROM "cargo__Authors" ORDER BY _pageName LIMIT 100

   Function: CargoSQLQuery::run

   Error: 42703 ERROR: column "_pagename" does not exist LINE 1: ...age","Country" FROM "cargo__Authors" ORDER BY _pageName ... ^

The columns in cargo__Authors appear to be properly cased this time: _ID, _pageName, _pageTitle, _pageNamespace, _pageID, Country.

When I try to create the books table data via MediaWiki, no error is given but the page itself still says "This table has not been created yet." Indeed, the table does not appear when I access the database myself. Wmvolckmann (talk) 09:07, 1 April 2016 (UTC)

Sorry about that! More lowercasing problems. I think these are fixed now as well. Yaron Koren (talk) 19:27, 1 April 2016 (UTC)

Now when I tried to create tables via the php cargoRecreateData.php, with an empty database, I initially get the error

  Deleting and recreating table...

  A database query error has occurred.

  Query: INSERT INTO "cargo_tables" (template_id,main_table,field_tables,table_schema) VALUES ('30','Authors','a:0:{}','a:1:{s:7:"Country";a:1:{s:4:"type";s:6:"String";}}')

  Function: DatabasePostgres::insert

  Error: 23505 ERROR: duplicate key value violates unique constraint "cargo_tables_main_table_key"

  DETAIL: Key (main_table)=(Authors) already exists.

Immediately running it a second time (and more), I get

  Deleting and recreating table...

  A database query error has occurred.

  Query: CREATE TABLE "cargo__Authors" ( "_ID" Int NOT NULL UNIQUE, "_pageName" Varchar(300) NOT NULL, "_pageTitle" Varchar(300) NOT NULL, "_pageNamespace" Int NOT NULL, "_pageID" Int NOT NULL, "Country" Varchar(300) )

  Function: DatabaseBase::query

  Error: 42P07 ERROR: relation "cargo__Authors" already exists

At this point there is no error when I click to view the Template:Author table, but I do get an error when trying to use the template for an author page:

   Query:

   SELECT MAX(_ID) AS "ID" FROM "cargo__Authors"

   Function: DatabaseBase::select

   Error: 42703 ERROR: column "_id" does not exist LINE 1: ...ELECT /* DatabaseBase::select Wmvolckmann */ MAX(_ID) AS "I... ^

Furthermore, the table for Template:Book is not created. Perhaps the script is aborting before it gets to Books because of the error with Authors? Wmvolckmann (talk) 01:10, 2 April 2016 (UTC)

Yet more casing issues... sorry about that. I think I just fixed the two additional bugs you found. Yaron Koren (talk) 02:26, 3 April 2016 (UTC)

Seems to have improved things quite a bit, but I still cannot create the table for Template:Book. There aren't any error messages or anything. I assume it's something with the way the template is written? I've copied it right from the quick start page. (I do have Semantic Forms installed.) When I remove the List (,) of parts in the declaration, it actually creates the page, but the list capability is pretty desirable. Wmvolckmann (talk) 05:29, 3 April 2016 (UTC)

I'm guessing that the problem is database inconsistency - some of the Cargo tables exist but not others, or there's a record in the table "cargo_tables" but the actual tables don't exist. I need to fix the scripts to handle those kinds of cases fully, but for now, if you remove all the Cargo tables, and empty out the "cargo_tables" and "cargo_pages" tables, and run the script again, it should hopefully work. Yaron Koren (talk) 13:30, 3 April 2016 (UTC)

Hmm, alright, I've dropped "cargo_tables" and "cargo_pages" from my MediaWiki database (which is also Postgres), and started with a fresh and separate Postgres database for Cargo. Using the "Create data table" button gives "The data is being recreated.", but there is no table created in the database, and there is no link to view table. No error shown. In contrast, the Author template creates the table for "cargo__Authors" and provides a working link to view table just fine. Wmvolckmann (talk) 08:22, 6 April 2016 (UTC)

Sorry about the problems. This may now be fixed. If you can, please try it again. Yaron Koren (talk) 17:55, 8 April 2016 (UTC)

Alright great, looks like the Books tables are all created now. But when I try to create a book page, I get the error

   Query:

   INSERT INTO "cargo__Books__Authors" (_rowID,_value) VALUES ('1','Fake Author')

   Function: DatabasePostgres::insert

   Error: 42703 ERROR: column "_rowid" of relation "cargo__Books__Authors" does not exist LINE 1: ...sert Wmvolckmann */ INTO "cargo__Books__Authors" (_rowID,_va... ^

Another casing issue with _rowid vs _rowID, perhaps? Wmvolckmann (talk) 10:39, 9 April 2016 (UTC)

Sorry once again! I should have just tested this myself right from the beginning. Yes, this was another Postgres casing issue, and I believe it's been solved now. Yaron Koren (talk) 02:47, 11 April 2016 (UTC)

Great, I can use the Book template without issue and I can view the tables, which have the right values. However, when I try to save a page that uses the Author template, I get

   A database query error has occurred. This may indicate a bug in the software.

   Query:    DELETE FROM "cargo__Authors" WHERE _pageID = '25'

   Function: DatabaseBase::delete

   Error: 42703 ERROR: column "_pageid" does not exist LINE 1: ...delete Wmvolckmann */ FROM "cargo__Authors" WHERE _pageID = ... ^

The page is created, but it shows the error

   Country of origin Midgard

   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

   Books Query: SELECT "_pageName" FROM "cargo__Books" LEFT OUTER JOIN "cargo__Books__Authors" ON (("cargo__Books"."_ID"="cargo__Books__Authors"."_rowID")) WHERE "cargo__Books__Authors"."_value"="Fake Author" ORDER BY "_pageName" LIMIT 100 Function: CargoSQLQuery::run Error: 42703 ERROR: column "Fake Author" does not exist LINE 1: ..._rowID")) WHERE "cargo__Books__Authors"."_value"="Fake Auth...

Just found out that I get an error when trying to make a change to a page that uses the Book template,

   A database query error has occurred. This may indicate a bug in the software.

   Query:

   DELETE FROM "cargo__Books__Authors" WHERE _rowID IN (SELECT _ID FROM "cargo__Books" WHERE _pageID = '77')

   Function: DatabaseBase::deleteJoin

   Error: 42703 ERROR: column "_id" does not exist LINE 1: ...M "cargo__Books__Authors" WHERE _rowID IN (SELECT _ID FROM "... ^

Wmvolckmann (talk) 10:49, 11 April 2016 (UTC)

Sorry again... and thanks for your continued patience. This problem with deletion has now been fixed, I think. Yaron Koren (talk) 13:52, 11 April 2016 (UTC)

Alright cool, the deletion problem is gone. But a page that uses the Author template still gives an error instead of a link to the author's books:

   Query: SELECT "_pageName" FROM "cargo__Books" LEFT OUTER JOIN "cargo__Books__Authors" ON (("cargo__Books"."_ID"="cargo__Books__Authors"."_rowID")) WHERE "cargo__Books__Authors"."_value"="Fake Author" ORDER BY "_pageName" LIMIT 100 Function: CargoSQLQuery::run Error: 42703 ERROR: column "Fake Author" does not exist LINE 1: ..._rowID")) WHERE "cargo__Books__Authors"."_value"="Fake Auth...

Doing a query myself, I get

   cargo=> SELECT * FROM "cargo__Authors";

   _ID | _pageName | _pageTitle | _pageNamespace | _pageID | Country

   -----+-------------+-------------+----------------+---------+---------

   1 | Fake Author | Fake Author | 0 | 76 | Midgard

Fake Author also appears when I do SELECT * FROM "cargo__Books__Authors"; with _rowID of 1. (Am I being obnoxious yet?) Wmvolckmann (talk) 16:22, 11 April 2016 (UTC)

Definitely not - this is important. Does your #cargo_query call contain the string "Fake Author"? If so, it might work if you call it with 'Fake Author' instead - in SQL you're supposed to use single quotes for string literals, though people often don't follow that. Yaron Koren (talk) 16:38, 11 April 2016 (UTC)

I'm not sure I follow. The only instance of cargo_query I see is in the Author template:

   cargo_query:tables=Books|where=Authors HOLDS "PAGENAME"

(Brackets omitted.) Are you saying I should change the double quotes to single quotes and see if that works instead? Wmvolckmann (talk) 12:31, 12 April 2016 (UTC)

Ah. Yes. Yaron Koren (talk) 13:03, 12 April 2016 (UTC)

Sorry, I'm still rather noobish with SQL. But changing the double quotes to single quotes seems to have fixed it, and I'm not getting any errors at this point. If anything else pops up, I'll let you know. But thanks Yaron, I appreciate the help! Wmvolckmann (talk) 12:49, 13 April 2016 (UTC)