An sqlite3 database browsing problem ...

[R.Wieser]

Hello all,

I'm trying to browse a random sqlite3 database and would like to sort on an
also random collumn. I've got two problems:

1) the text collumns contents may be longer than I care to handle. Meaning
that I want to do a string compare only upto a certain number of characters.

2) the selected "select on" column may be numeric.

Some googeling gave me SUBSTRING. Alas, that doesn't go well if the target
column is numeric (lots of entries get skipped).

Some more googeling gave me LIKE. But for some reason I think that adding a
"%" to the end of a value (in string form) will also turn the value into an
actual string, causing the same problem.

So, the question: How do I do a WHERE and SORT BY which will work with
limited length provided strings as well as values. Both retrieved as
strings and provided parameterized.

Example of a query :

SELECT * FROM %s
WHERE (SUBSTR(%s,1,63), %s) >= (?1, ?9)
ORDER BY %s , %s LIMIT %d;

And yes, that is a "sprintf" string :-)

"?1" is the "sort on" collumn, which may be string or nmeric, and "?9" the
first, key collumn - as a fallback if the "sort on" column contains (more
than) a screenfull of the same data.

Regards,
Rudy Wieser

[JJ]

Post by R.Wieser
Hello all,
I'm trying to browse a random sqlite3 database and would like to sort on an
1) the text collumns contents may be longer than I care to handle. Meaning
that I want to do a string compare only upto a certain number of characters.
2) the selected "select on" column may be numeric.
Some googeling gave me SUBSTRING. Alas, that doesn't go well if the target
column is numeric (lots of entries get skipped).
Some more googeling gave me LIKE. But for some reason I think that adding a
"%" to the end of a value (in string form) will also turn the value into an
actual string, causing the same problem.
So, the question: How do I do a WHERE and SORT BY which will work with
limited length provided strings as well as values. Both retrieved as
strings and provided parameterized.
SELECT * FROM %s
WHERE (SUBSTR(%s,1,63), %s) >= (?1, ?9)
ORDER BY %s , %s LIMIT %d;
And yes, that is a "sprintf" string :-)
"?1" is the "sort on" collumn, which may be string or nmeric, and "?9" the
first, key collumn - as a fallback if the "sort on" column contains (more
than) a screenfull of the same data.
Regards,
Rudy Wieser

If the column value is numeric, why not just use math calculation?

[R.Wieser]

JJ,

Post by JJ
If the column value is numeric, why not just use math calculation?

Thats the whole problem : *I don't know* what the (random, user-selectable)
"sort on" column will contain. If I would know for a fact that that column
contains just numbers I would (not need to) bother with the
string-comparision length at all ...

IOW: Fine-tuning the WHERE for numeric use will (probably?) clash with a
columns string contents, and fine-tuning it for string comparision will (as
experienced) clash with the columns numeric contents. :-|

One possibile solution would be if I could tell the DB that the comparision
should be done in accordance with the columns type specification, instead of
letting the WHERE clause dictate it (like the use of SUBSTRING forcing a
string comparision). The problem is that I simply do not know enough about
sqlite3 to even be aware of possible roads to investigate.

Regards,
Rudy Wieser

[R.Wieser]

I /think/ I've solved my previous problem by modifying the query depending
on the type of the targetted column (in the DB) (wrapping a SUBSTRING around
requests towards non-numeric colums - not shown in the below), but now I've
got another problem :

When a SELECTed column contains many of the same entries (in my case: empty
string fields) those rows are, when doing a "give me the next batch"
request, skipped.

I /thought/ had solved that one by appending the ID column to each query,
but as it appears it doesn't seem to.

The used query (to get the next screen-full of results) is this one :

SELECT * FROM moz_places WHERE (title, id) >= (?1, ?9) ORDER BY title, id
LIMIT 34;

"?1" and "?9" contain the last "title" and "id" colum results. In the case
of "?1" that is an empty string.

My question is : what am I missing here ? Why are those entries skipped
and what can I do against it ?

Regards,
Rudy Wieser

[R.Wieser]

Post by R.Wieser
When a SELECTed column contains many of the same entries
(in my case: empty string fields) those rows are, when doing a "give me
the next batch" request, skipped.

I was a bit too quick - the above described skipping behaviour seems to
*only* happen on empty (string?) fields (I've not yet found empty numeric
fields). The moment there is /anything/ in there, even just a single
dot, all works well again.

Did I just stumble over a bug ?? If not, what is happening / the reason
for it ?

Regards,
Rudy Wieser