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

[GoogleCodeExporter]

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

Original issue reported on code.google.com by crazy4ch...@gmail.com on 27 Feb 2013 at 5:29

[GoogleCodeExporter]

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.

Original comment by crazy4ch...@gmail.com on 27 Feb 2013 at 5:29

[GoogleCodeExporter]

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

Original comment by crazy4ch...@gmail.com on 27 Feb 2013 at 5:33

[GoogleCodeExporter]

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.

Original comment by gba...@gmail.com on 27 Feb 2013 at 6:25

[GoogleCodeExporter]

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.

Original comment by crazy4ch...@gmail.com on 1 Mar 2013 at 12:06

• Changed state: Fixed