Chart mode only accepts some hard-coded type-names, problem with (group) functions

[Christopher Kramer]

Originally reported on Google Code with ID 182

This issue was initially posted in the discussion group by Gerry Barksdale:
https://groups.google.com/forum/?fromgroups=#!topic/phpliteadmin/KFbpcMf4zUM

What steps will reproduce the problem?
1. I have a "portfolio" table that lists share cost, number of shares, and investment
type (bonds, oil, finance, etc.).
2. Using the following view, I get valid results in the TABLE mode:
VIEW:  SELECT type, sum(cost*shares) AS tcost FROM portfolio GROUP BY type;
3. Switch to CHART mode and I have both "type" and "tcost" as labels and NOTHING as
a value field (thus making CHART mode useless).

What is the expected output? What do you see instead?
CHART mode works for defined column names, but I cannot seem to make it work for a
calculated name (e.g., AS or just the sum(cost*shares)).

Reported by crazy4chrissi on 2013-02-27 17:29:03

[Christopher Kramer]

The issue seems to be the following:
Chart mode checks for number-column-types such as integer, float or real.
If we do PRAGMA table_info(viewname) on a view, the type of the SUM()-column is unknown
(empty), so phpLiteAdmin does not propose it as a valid value-column.

I think checking for some hard-coded column-types is not such a good idea anyhow, because
SQLite is very weakly typed and people might use "smallint" as a column-type (although
it does not have the usual semantics as normally in SQL, SQLIte will accept it). Maybe
we should drop the check completely and rely on the user to choose columns that work?
I mean, the user could enter strings in an integer column anyhow (in SQLite), so the
check does not prevent things from breaking anyhow.

Reported by crazy4chrissi on 2013-02-27 17:29:56

[Christopher Kramer]

There are 2 almost-the-same-lines that look like this:
if(strtolower($result[$i][2])=="integer" ||
strtolower($result[$i][2])=="float" || strtolower($result[$i][2])=="real") 

The first picks the initial value-column and the second one filters the list of possible
value-columns.
I think we should remove the 2nd one and leave it up to the user to choose reasonable
columns.

We might leave the first there as it makes sense to pick a numeric column as default.
But if there is no numeric column, we should probably choose the next "unused" column
(not the same column as the label-column).

Reported by crazy4chrissi on 2013-02-27 17:33:43

[Former user]

Agree with #2.  Choosing a nonsense (non-numeric) column as a value results in an error
message, so the bad choice is noted & can easily be corrected by the user.

Reported by gbark2 on 2013-02-27 18:25:39

[Christopher Kramer]

I just fixed this issue with r344.

It is now possible to select any column as value-column.
However, a column of a numeric type is chosen as default, if such a column exists.
If none exists, the first column is chosen that is not the label-column (if there is
more than 1 col).

In contrast to previous versions, the default now picks the first column of a suitable
type and not the last one.

Reported by crazy4chrissi on 2013-03-01 00:06:19 - Status changed: Fixed