Hi @jow,
I usually import the toh_dump_tab_separated.zip file for an own system for choosing new Routers before buy.
But today I noted that the field version is incomplete.
I have 3 DIR-505 V1 and I noticed that when consulting the TOH (https://openwrt.org/toh/views/toh_extended_all?dataflt[Model*~]=505), in the Version field is exhibited "A1, A2 ", but when importing, in file ToH_dump_tab_separated.csv, this field only contains "A2". There must be some error in the export routine because of the comma.
Best,
carliedu
1 Like
After comparing the csv dump and the ToH, I can confirm that there is an issue with multiple versions separated by comma: The dump does not contain any multiple versions. If multiple versions are present in the dataentry, only the last one will be shown in the dump.
ToH shows multiple versions:
CSV dump shows only the last version:
Exactly.
Can it be corrected?
I just noticed that the issue is not only with the version, but each and every field that contains multiple comma separated values (e.g. flash mb, usb ports, installation and recovery methods and others, havn't checked them all)
After checking the csv-dump script I think I know where the problem is coming from, but I'm not skilled in mysql, therefore being unable to solve this issue.
My preliminary guesstimated rootcause: the "max" in the following mysql statement
Max(CASE WHEN td.key = 'version' THEN td.value END) as version,
As long as @bobafetthotmail does not reply, I'd appreciate if someone with mysql knowledge could jump in to help fixing this issue.
1 Like
@tmomas, seeing what you have posted, I can say that this was done intentionally to show only the highest value (funtion MAX).
I would only output the just the value itself.
just replace Max(CASE WHEN td.key = 'version' THEN td.value END) as version, with version.
That is all.
I soon imagined that this must be happening in other fields.
just use the table column name, separated with commas.
If you don't use max then you'd get multiple rows for each record.
MySQL has GROUP_CONCAT, this might help
Is it possible to post the full SQL pls?
mysql -h<host> -D<database> -u<user> -p<pass> <<'EndOfQuery' > ToH_dump_tab_separated.csv
SELECT td.pid,
Max(CASE WHEN td.key = 'device type' THEN td.value END) as devicetype,
Max(CASE WHEN td.key = 'brand' THEN td.value END) as brand,
Max(CASE WHEN td.key = 'model' THEN td.value END) as model,
Max(CASE WHEN td.key = 'version' THEN td.value END) as version,
Max(CASE WHEN td.key = 'fccid' THEN td.value END) as fccid,
Max(CASE WHEN td.key = 'availability' THEN td.value END) as availability,
Max(CASE WHEN td.key = 'where available' THEN td.value END) as whereavailable,
Max(CASE WHEN td.key = 'supported since commit' THEN td.value END) as supportedsincecommit,
Max(CASE WHEN td.key = 'supported since rel' THEN td.value END) as supportedsincerel,
Max(CASE WHEN td.key = 'supported current rel' THEN td.value END) as supportedcurrentrel,
Max(CASE WHEN td.key = 'unsupported functions' THEN td.value END) as unsupported_functions,
Max(CASE WHEN td.key = 'target' THEN td.value END) as target,
Max(CASE WHEN td.key = 'subtarget' THEN td.value END) as subtarget,
Max(CASE WHEN td.key = 'package architecture' THEN td.value END) as packagearchitecture,
Max(CASE WHEN td.key = 'bootloader' THEN td.value END) as bootloader,
Max(CASE WHEN td.key = 'cpu' THEN td.value END) as cpu,
Max(CASE WHEN td.key = 'cpu cores' THEN td.value END) as cpucores,
Max(CASE WHEN td.key = 'cpu mhz' THEN td.value END) as cpumhz,
Max(CASE WHEN td.key = 'flash mb' THEN td.value END) as flashmb,
Max(CASE WHEN td.key = 'ram mb' THEN td.value END) as rammb,
Max(CASE WHEN td.key = 'ethernet 100m ports' THEN td.value END) as ethernet100mports,
Max(CASE WHEN td.key = 'ethernet gbit ports' THEN td.value END) as ethernetgbitports,
Max(CASE WHEN td.key = 'switch' THEN td.value END) as switch,
Max(CASE WHEN td.key = 'vlan' THEN td.value END) as vlan,
Max(CASE WHEN td.key = 'modem' THEN td.value END) as modem,
Max(CASE WHEN td.key = 'comments network ports' THEN td.value END) as commentsnetworkports,
Max(CASE WHEN td.key = 'wlan hardware' THEN td.value END) as wlanhardware,
Max(CASE WHEN td.key = 'wlan 2.4ghz' THEN td.value END) as wlan24ghz,
Max(CASE WHEN td.key = 'wlan 5.0ghz' THEN td.value END) as wlan50ghz,
Max(CASE WHEN td.key = 'wlan comments' THEN td.value END) as wlancomments,
Max(CASE WHEN td.key = 'wlan driver' THEN td.value END) as wlandriver,
Max(CASE WHEN td.key = 'detachable antennas' THEN td.value END) as detachableantennas,
Max(CASE WHEN td.key = 'bluetooth' THEN td.value END) as bluetooth,
Max(CASE WHEN td.key = 'usb ports' THEN td.value END) as usbports,
Max(CASE WHEN td.key = 'sata ports' THEN td.value END) as sataports,
Max(CASE WHEN td.key = 'comments usb sata ports' THEN td.value END) as commentsusbsataports,
Max(CASE WHEN td.key = 'video ports' THEN td.value END) as videoports,
Max(CASE WHEN td.key = 'audio ports' THEN td.value END) as audioports,
Max(CASE WHEN td.key = 'phone ports' THEN td.value END) as phoneports,
Max(CASE WHEN td.key = 'comments av ports' THEN td.value END) as commentsavports,
Max(CASE WHEN td.key = 'serial' THEN td.value END) as serial,
Max(CASE WHEN td.key = 'serial connection parameters' THEN td.value END) as serialconnectionparameters,
Max(CASE WHEN td.key = 'jtag' THEN td.value END) as jtag,
Max(CASE WHEN td.key = 'led count' THEN td.value END) as ledcount,
Max(CASE WHEN td.key = 'button count' THEN td.value END) as buttoncount,
Max(CASE WHEN td.key = 'gpios' THEN td.value END) as gpios,
Max(CASE WHEN td.key = 'power supply' THEN td.value END) as powersupply,
Max(CASE WHEN td.key = 'device page' THEN td.value END) as devicepage,
Max(CASE WHEN td.key = 'device techdata' THEN td.value END) as device_techdata,
Max(CASE WHEN td.key = 'owrt forum topic url' THEN td.value END) as owrt_forum_topic_url,
Max(CASE WHEN td.key = 'lede forum topic url' THEN td.value END) as lede_forum_topic_url,
Max(CASE WHEN td.key = 'forum search' THEN td.value END) as forumsearch,
Max(CASE WHEN td.key = 'git search' THEN td.value END) as gitsearch,
Max(CASE WHEN td.key = 'wikidevi url' THEN td.value END) as wikideviurl,
Max(CASE WHEN td.key = 'oem device homepage url' THEN td.value END) as oemdevicehomepageurl,
Max(CASE WHEN td.key = 'firmware oem stock url' THEN td.value END) as firmwareoemstockurl,
Max(CASE WHEN td.key = 'firmware openwrt install url' THEN td.value END) as firmwareopenwrtinstallurl,
Max(CASE WHEN td.key = 'firmware openwrt upgrade url' THEN td.value END) as firmwareopenwrtupgradeurl,
Max(CASE WHEN td.key = 'firmware openwrt snapshot install url' THEN td.value END) as firmwareopenwrtsnapshotinstallurl,
Max(CASE WHEN td.key = 'firmware openwrt snapshot upgrade url' THEN td.value END) as firmwareopenwrtsnapshotupgradeurl,
Max(CASE WHEN td.key = 'installation method(s)' THEN td.value END) as installationmethods,
Max(CASE WHEN td.key = 'comment installation' THEN td.value END) as commentinstallation,
Max(CASE WHEN td.key = 'recovery method(s)' THEN td.value END) as recoverymethods,
Max(CASE WHEN td.key = 'comment recovery' THEN td.value END) as commentrecovery,
Max(CASE WHEN td.key = 'picture' THEN td.value END) as picture,
Max(CASE WHEN td.key = 'comments' THEN td.value END) as comments
FROM data td
WHERE EXISTS (SELECT * FROM data td2 WHERE td2.key = 'device type' AND td.pid = td2.pid AND td.pid != 1 )
GROUP BY pid
EndOfQuery
Could you replace
Max(CASE WHEN td.key = 'version' THEN td.value END) as version,
With
GROUP_CONCAT(CASE WHEN td.key = 'version' THEN td.value END) as version,
And see if that works please?
2 Likes
I have done the modification as suggested. On first look, seemed to have worked, resulting csv contains now also values separated by commas.
Please check yourself in detail and report back any issues found.
Might want to replace the max function on all fields with multiple values.
Happy to help!
Will keep my eye out for more ways I can contribute.
This topic was automatically closed 10 days after the last reply. New replies are no longer allowed.