Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Keith Medcalf]

On Monday, 9 March, 2020 18:18, Peng Yu  wrote:

>But I never experience the problem in my original email when I used
>python3's default sqlite3 module (WAL was not used). What is the
>difference between the default sqlite3 module and apsw? Thanks.

THe relevant difference is that the sqlite3 wrapper sets a default busy timeout 
of 5000 milliseconds when opening a connection, which you can change with the 
timeout parameter on the sqlite3.connect connection constructor.

APSW does not do anything behind your back, so if you want a non-zero 
busy_timeout you have to set one.

cn = apsw.Connection( ... )

cn.setbusytimeout(5000)
or
cn.cursor().execute('pragma busy_timeout=5000;')

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Peng Yu]

On 3/8/20, Keith Medcalf  wrote:
>
> On Sunday, 8 March, 2020 21:24, Peng Yu  wrote:
>
>>When I open an sqlite3 db using the following python code,
>
>>conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY)
>
>>, I got the following error.
>
>>Traceback (most recent call last):
>>  File "/xxx.py", line 21, in 
>>for x in c.execute('SELECT (data) FROM sqlar'):
>>  File "src/cursor.c", line 236, in resetcursor
>>apsw.BusyError: BusyError: database is locked
>
>>The db file is currently processed by a python script which opens the
>>db file for writing in the following way.
>
>>conn = apsw.Connection(filepath)
>
>>Since the first process just reads, I'd like it not be blocked. What
>>is the correct way to do so? Thanks.
>
> Opening a connection with the SQLITE_OPEN_READONLY only means that the
> connection cannot write to the database using that connection.  It does not
> affect the locking and transaction system in any way.  Merely that if you
> try to "write" using the readonly connection that you will get an error to
> the effect that the connection is read-only.
>
> Perhaps you want to set a busy timeout or use WAL journal mode.

But I never experience the problem in my original email when I used
python3's default sqlite3 module (WAL was not used). What is the
difference between the default sqlite3 module and apsw? Thanks.


-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Keith Medcalf]

On Monday, 9 March, 2020 08:33, Simon Slavin  wrote:

>If your .shm and .wal files still exist when no apps are accessing the
>database, the most likely cause is that at least one of the apps is not
>closing its connection correctly.

or you are opening the database connection with SQLITE_OPEN_READNLY because a 
READONLY connection cannot delete the shm and wal files as that would require 
writing, and that readonly connection happens to be the last one to close.

In other words where there are multiple connections to a WAL database, the 
associated WAL and SHM files will be deleted when the last connection to that 
database closes cleanly *unless* that connection is not permitted to write (was 
opened with SQLITE_OPEN_READONLY) or was otherwise configured not to delete the 
WAL and SHM files.

This is, of course, documented.

The original unreferenced contextless fragment of text was this:

There is an additional quasi-persistent "-wal" file and "-shm" shared
memory file associated with each database, which can make SQLite less
appealing for use as an application file-format.

This is because now you have three things which may in certain circumstances 
constitute 1 thing.  If journal_mode=DELETE you only have one thing be one 
thing -- the database file -- and if there is a -journal file then you know 
something "is broked".  However in journal_mode=WAL it is not so simple.  More 
watts (not merely milliwatts) much be expended to understand why there may be 
three things consitituting one thing, and that it does not necessarily indicate 
any "brokeness" but may rather be a necessary and normal state of affairs.  Or 
not.  But an external observed will not be able to tell.  Hence a version of 
Word that stores its documents in an SQLite database in WAL mode may not be 
suitable for use by someone who expects that "one document is one file".

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Simon Slavin]

On 9 Mar 2020, at 2:16pm, David Raymond  wrote:

> In general I believe the last connection tries to do a complete checkpoint 
> when it closes, and if it succeeds then it'll delete the -wal and -shm files. 
> If you have automatic checkpointing turned off (maybe you're doing regular 
> checkpoints from a separate dedicated process) then it's possible for all 
> connections to finish and close the database without error, but still have 
> those files sitting around because they haven't been checkpointed yet.

If your .shm and .wal files still exist when no apps are accessing the 
database, the most likely cause is that at least one of the apps is not closing 
its connection correctly.

If your app runs code to close connections, and the connections are still not 
closing properly, then the most likely cause is that you have a statement 
active when you close the connection.

To make sure your statements are not active, make sure you call 
sqlite3_finalize() on each statement when you no longer need it.  If you are 
not using the C library to execute SQLite calls, look for some equivalent in 
your SQLite library.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[David Raymond]

I see this. What does it mean? Does it mean even when the sqlite3
session is closed there is still -wal and -shm left on the disk?

"""
There is an additional quasi-persistent "-wal" file and "-shm" shared
memory file associated with each database, which can make SQLite less
appealing for use as an application file-format.
"""


In rollback journal mode the -journal file only shows up when you're making 
changes. In wal mode the -wal and -shm files are there for the entire life of 
the connection, as long as there's anything even reading the file, they're 
there.

In general I believe the last connection tries to do a complete checkpoint when 
it closes, and if it succeeds then it'll delete the -wal and -shm files. If you 
have automatic checkpointing turned off (maybe you're doing regular checkpoints 
from a separate dedicated process) then it's possible for all connections to 
finish and close the database without error, but still have those files sitting 
around because they haven't been checkpointed yet.

Some combination of those two is what is being referred to there I believe.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Peng Yu]

I see this. What does it mean? Does it mean even when the sqlite3
session is closed there is still -wal and -shm left on the disk?

"""
There is an additional quasi-persistent "-wal" file and "-shm" shared
memory file associated with each database, which can make SQLite less
appealing for use as an application file-format.
"""

On 3/8/20, Simon Slavin  wrote:
> [This explanation is much simplified for clarity.  Before filling in missing
> details please consider what the OP wants.  Don't just show off your
> exhaustive knowledge of SQLite.]
>
> A database is normally in delete journal mode, as if you'd executed
>
> PRAGMA journal_mode=DELETE
>
> In this journal mode you can't read from a database which is being changed.
> If one process is changing the database it has the database locked.  Another
> process cannot read it until it is unlocked.  Because the reading process
> might read some of the row before it is changed, and the rest of the row
> after it is changed.
>
> To fix this, change the journal mode to WAL:
>
> PRAGMA journal_mode=WAL
>
> In this mode one process can read a database while another process is
> changing it.  The process that reads the database gets the data as it was
> before the change.
>
> For further details see
>
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Simon Slavin]

[This explanation is much simplified for clarity.  Before filling in missing 
details please consider what the OP wants.  Don't just show off your exhaustive 
knowledge of SQLite.]

A database is normally in delete journal mode, as if you'd executed

PRAGMA journal_mode=DELETE

In this journal mode you can't read from a database which is being changed.  If 
one process is changing the database it has the database locked.  Another 
process cannot read it until it is unlocked.  Because the reading process might 
read some of the row before it is changed, and the rest of the row after it is 
changed.

To fix this, change the journal mode to WAL:

PRAGMA journal_mode=WAL

In this mode one process can read a database while another process is changing 
it.  The process that reads the database gets the data as it was before the 
change.

For further details see


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Keith Medcalf]

On Sunday, 8 March, 2020 21:24, Peng Yu  wrote:

>When I open an sqlite3 db using the following python code,

>conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY)

>, I got the following error.

>Traceback (most recent call last):
>  File "/xxx.py", line 21, in 
>for x in c.execute('SELECT (data) FROM sqlar'):
>  File "src/cursor.c", line 236, in resetcursor
>apsw.BusyError: BusyError: database is locked

>The db file is currently processed by a python script which opens the
>db file for writing in the following way.

>conn = apsw.Connection(filepath)

>Since the first process just reads, I'd like it not be blocked. What
>is the correct way to do so? Thanks.

Opening a connection with the SQLITE_OPEN_READONLY only means that the 
connection cannot write to the database using that connection.  It does not 
affect the locking and transaction system in any way.  Merely that if you try 
to "write" using the readonly connection that you will get an error to the 
effect that the connection is read-only.

Perhaps you want to set a busy timeout or use WAL journal mode.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Peng Yu]

When I open an sqlite3 db using the following python code,

conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY)

, I got the following error.

Traceback (most recent call last):
  File "/xxx.py", line 21, in 
for x in c.execute('SELECT (data) FROM sqlar'):
  File "src/cursor.c", line 236, in resetcursor
apsw.BusyError: BusyError: database is locked

The db file is currently processed by a python script which opens the
db file for writing in the following way.

conn = apsw.Connection(filepath)

Since the first process just reads, I'd like it not be blocked. What
is the correct way to do so? Thanks.

-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

[nomad]

On Tue Mar 03, 2020 at 05:12:17PM +0800, suanzi wrote:
> Thank you,you let me know what happened.
> 
> About your answer,I try it,but it can't work,maybe because can't have two
> zlib.
> 
> apt-get could not find zlib:i386.

Did you specifically try "zlib1g"? I don't think the "zlib" package exists.

mark@b:~$ uname -a
Linux b 4.19.0-8-amd64 #1 SMP Debian 4.19.98-1 (2020-01-26) x86_64
GNU/Linux

mark@b:~$ dpkg -S libz.so.1
zlib1g:amd64: /lib/x86_64-linux-gnu/libz.so.1
zlib1g:amd64: /lib/x86_64-linux-gnu/libz.so.1.2.11

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to enforce a specific order of group_concat?

[Dominique Devienne]

On Mon, Mar 2, 2020 at 6:35 PM Keith Medcalf  wrote:
> Well, in theory an order by in a nested select means that the result of the 
> operation is an ordered projection and not merely a set of rows.
> For this particular case (a nested select with an order by and the outer 
> query with an aggregate) the query will not be flattened (#16)

OK. I was more trying to find out whether such nested "ordered"
projections were a standard-SQL thing or not.

> select x,y from (select x, y from t order by y) order by x;
> will do two order-by sorts to obtain the result even though the query could 
> be (in this particular case) re-written as "select x, y from t order by x, y"

That's assuming the sort is "stable" :)  Stable-sort is typically
slower than non-stable-sort, that's why the STL has std::sort and
std::stable_sort.

> This is why putting an "order by" in a view will usually preclude query 
> flattening because the view is not merely producing a "set of rows" it is 
> producing an "ordered projection" and the ordering must be significant else 
> it would not be there.

I would actually prefer these nested order-by to be ignored, and the
"set of rows" being assumed, forcing the outer query to do its own
ordering.
The very notion of "ordered projection" for nested query sounds more
like an implementation detail, to word-around the lack of window
functions,
than something "official" from the SQL standard or relational theory.

I'm not disputing how SQLite implements things, for historical or
practical reasons, I just want to understand whether such "ordered
projection"
is an official concept from SQL or just an SQLite thing. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

[suanzi]

Thank you,you let me know what happened.

About your answer,I try it,but it can't work,maybe because can't have 
two zlib.


apt-get could not find zlib:i386.

And there have not sqlite3 tool for linux x64 in sqlite.org

Anyway,thank you,I plan download the sqlite source and compile.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

[Rowan Worth]

This means you're missing a dependency - in this case zlib. It's hard to
believe you don't have zlib on your system at all; probably this is
happening because your system is amd64 but the sqlite binary you've
downloaded is x86. I'm not a debian user but this should get you going:

apt-get install zlib1g:i368

(alternately, you can download the sqlite source and compile your own
binary which will be amd64 compatible)
-Rowan

On Tue, 3 Mar 2020 at 16:42, suanzi  wrote:

> My OS is Debian10 amd64,desktop is xfce4
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

[suanzi]

My OS is Debian10 amd64,desktop is xfce4

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How should I use the sqlite3 tool?(for linux)

[suanzi]

After I download the tool,I cd to the path,then,I run "./sqlite3"

But It can't work,here are error msgs:

./sqlite3: error while loading shared libraries: libz.so.1: cannot open 
shared object file: No such file or directory


So,What should I do for it?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to enforce a specific order of group_concat?

[Keith Medcalf]

On Monday, 2 March, 2020 09:20, Dominique Devienne  wrote:

>On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf  wrote:

>> select group_concat(value) from (select distinct value from test order by 
>> value);

>But is that guaranteed to be ordered correctly "forever" instead of by
>"happenstance" from current implementation details? 

>My point was that the Window Function version is ordered "by design", 
>and not an implementation detail (as I think
>the simpler version is).

>Your subquery returns rows in a given order too, but "who" says
>they'll be processed in that order?

>Tables are just "sets of rows" after all, and the relational model is
>about set-theory, no? order by in subquery therefore make little to no 
>sense in nested SQL (in theory...). --DD

Well, in theory an order by in a nested select means that the result of the 
operation is an ordered projection and not merely a set of rows.  For this 
particular case (a nested select with an order by and the outer query with an 
aggregate) the query will not be flattened (#16)

https://sqlite.org/optoverview.html#flattening

Consider that

create table t (x,y);
insert into t values (1,1),(2,2),(3,3),(4,1),(4,2),(4,3),(4,4);
select x,y from (select x, y from t order by y) order by x;

will do two order-by sorts to obtain the result even though the query could be 
(in this particular case) re-written as "select x, y from t order by x, y" 
because a query with an order-by in both the outer and nested query cannot be 
flattened (#11).

This is why putting an "order by" in a view will usually preclude query 
flattening because the view is not merely producing a "set of rows" it is 
producing an "ordered projection" and the ordering must be significant else it 
would not be there.

Of course in the case of the original statement:

select group_concat(distinct value) from test order by value;

The "order by value" might in fact be used to select the use of an index on 
value to scan, rather than the table test, if that results in less I/O that 
scanning the table.  However, the Query Planner doesn't believe aggregate 
functions (including group_concat) results are non-commutative so determines 
that the "order by value" clause is superfluous (perhaps this is an error, 
since other functions such as sum(), total(), avg() can also be non-commutative 
in certain pathological cases and have varying results depending on the 
ordering of the data sent to them, especially avg() since it merely returns 
sum()/count() rather than a successive approximation to the mean, though 
successive approximation still has pathological cases for non-sorted input, 
they are fewer than the sum()/count() method).

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to enforce a specific order of group_concat?

[Dominique Devienne]

On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf  wrote:
> select group_concat(value) from (select distinct value from test order by 
> value);

But is that guaranteed to be ordered correctly "forever" instead of by
"happenstance"
from current implementation details? My point was that the Window
Function version
is ordered "by design", and not an implementation detail (as I think
the simpler version is).

Your subquery returns rows in a given order too, but "who" says
they'll be processed in that order?
Tables are just "sets of rows" after all, and the relational model is
about set-theory, no? order by
in subquery therefore make little to no sense in nested SQL (in theory...). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to enforce a specific order of group_concat?

[Keith Medcalf]

You mean like:

select group_concat(value) over (order by value rows between unbounded 
preceding and unbounded following) from (select distinct value from test) limit 
1;
and
select group_concat(value) over (order by value desc rows between unbounded 
preceding and unbounded following) from (select distinct value from test) limit 
1;

which seems far more convoluted than just:

select group_concat(value) from (select distinct value from test order by 
value);
and
select group_concat(value) from (select distinct value from test order by value 
desc);


-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Dominique Devienne
>Sent: Monday, 2 March, 2020 08:02
>To: SQLite mailing list 
>Subject: Re: [sqlite] How to enforce a specific order of group_concat?
>
>On Sun, Mar 1, 2020 at 10:58 PM mailing lists 
>wrote:
>> Are there any other solutions / possibilities?
>
>I thought someone more knowledgeable than I about Window Functions [1]
>would answer,
>but since nobody mentioned them so far, I'll do it, as I believe this
>is the "SQL native" way
>to achieve what you want (modulo DISTINCT perhaps). Notably (from the
>doc):
>
>Every aggregate window function can also work as a ordinary aggregate
>function,
>simply by omitting the OVER and FILTER clauses. Furthermore, all of
>the built-in aggregate
>functions of SQLite can be used as an aggregate window function by
>adding an appropriate OVER clause
>
>[2] has an example with group_concat() and OVER (ORDER BY ...). I
>assume that's what you need,
>someone better at Window Functions then me (not difficult!) can
>confirm or not that. --DD
>
>[1] https://www.sqlite.org/windowfunctions.html
>[2] https://www.sqlite.org/windowfunctions.html#aggwinfunc
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to enforce a specific order of group_concat?

[Dominique Devienne]

On Sun, Mar 1, 2020 at 10:58 PM mailing lists  wrote:
> Are there any other solutions / possibilities?

I thought someone more knowledgeable than I about Window Functions [1]
would answer,
but since nobody mentioned them so far, I'll do it, as I believe this
is the "SQL native" way
to achieve what you want (modulo DISTINCT perhaps). Notably (from the doc):

Every aggregate window function can also work as a ordinary aggregate function,
simply by omitting the OVER and FILTER clauses. Furthermore, all of
the built-in aggregate
functions of SQLite can be used as an aggregate window function by
adding an appropriate OVER clause

[2] has an example with group_concat() and OVER (ORDER BY ...). I
assume that's what you need,
someone better at Window Functions then me (not difficult!) can
confirm or not that. --DD

[1] https://www.sqlite.org/windowfunctions.html
[2] https://www.sqlite.org/windowfunctions.html#aggwinfunc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to enforce a specific order of group_concat?

[Jean-Luc Hainaut]

On 1/03/2020 22:57, mailing lists wrote:

Assume I create the following table:

CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
INSERT INTO Test (Value) VALUES('Alpha');
INSERT INTO Test (Value) VALUES('Beta');
INSERT INTO Test (Value) VALUES('Beta');
INSERT INTO Test (Value) VALUES('Alpha');

According to the documentation of group_concat the order is undefined, indeed:

SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC;
SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC;

Both queries result in Alpha,Beta.

Changing the queries to

WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC) SELECT 
group_concat(x) FROM Result;
WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC) SELECT 
group_concat(x) FROM Result;

leads to the results Alpha,Beta, respectively Beta,Alpha.

Is this a coincidence or is this guaranteed to work?

Are there any other solutions / possibilities?


I also sometimes need a deterministic version of group_concat(). For 
such cases, I have written the short UDF below (in Python, but I guess 
the algorithm can easily be translated in other languages):


JLH

class group_concat2:
# Rewriting of "group_concat" of SQLite to simulate that of MySQL.
# Implements "distinct", "order by", "descending" and "separator".
# Interprets "null" values "intuitively"
#
# Format: group_concat2(value,distinct,sortkey,direction,separator)
#   value: char or numeric SQL expression; if numeric, 
converted into char;

#  the next value to concatenate;
#  discarded if None (Python translation of SQL null).
#   distinct:  numeric or char SQL expression; if char, converted 
into integer;

#  uniqueness indicator;
#  if 1, duplicates ignored; if 0, duplicates allowed.
#   sortkey:   char or numeric SQL expression (no conversion);
#  the order key value for the current "value" instance;
#  If None or u'', the current "value" instance is used 
instead.
#   direction: numeric or char SQL expression; if char, converted 
into integer;

#  ordering direction (1 = asc; 2 = desc).
#   sep:   char or numeric SQL expression; if numeric, 
converted into char;

#  value separator;
#  If None, = default u','.
# Example:
#   select City,group_concat2(lower(CustID),1,Account,'2','; ') as 
Customers

#   from   CUSTOMER group by City;

def __init__(self):
# Initialize
self.number  = 0# number of values added
self.valList = []   # List of values to concatenate
self.orderby = []   # list of values of the order key
self.distinct = 0   # whether "valList" values must be unique 
(0 = no; 1 = yes)

self.direction = 1  # ordering direction (1 = asc; 2 = desc)
self.sep = u',' # separator

def step(self,value,distinct,sortkey,direction,sep):
# Adding a new value to concatenate.
# Each call of this method may specify different values of
#  (distinct,sortkey,direction,sep) parameters.
# However, only those specified by the call of the first 
"value" instance

# will be considered, the others being ignored.

import numbers

self.number += 1

# Initialize user values of "distinct", "direction" and "sep"
if self.number == 1:
if distinct in [1,u'1']:
self.distinct = 1
if direction in [1,2,u'1',u'2']:
self.direction = int(direction)
if sep is not None:
if isinstance(sep,numbers.Number):
self.sep = unicode(sep)
else:
self.sep = sep

if sortkey is None:
sortkey = value if value is not None else 1
elif sortkey == u'':
sortkey = value if value is not None else 1

if value is not None:
if isinstance(value,numbers.Number):
value = unicode(value)

if self.distinct:
if value not in self.valList:
self.valList.append(value)
self.orderby.append(sortkey)
else:
self.valList.append(value)
self.orderby.append(sortkey)
else:
# value discarded
pass

def finalize(self):
if self.direction == 1:
self.valList = [y for x,y in 
sorted(zip(self.orderby,self.valList),reverse=False)]

else:
self.valList = [y for x,y in 
sorted(zip(self.orderby,self.valList),reverse=True)]

return self.sep.join(self.valList)








___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to enforce a specific order of group_concat?

[mailing lists]

Hi Keith,

thanks for the explanation.

PS: I used a CTE because official examples (e.g. Mandelbrot) also used CTEs in 
combination with group_concat. Although the incorporation of group_concat was 
not the primary reason to use CTEs.
PPS: Is it possible to rephrase the documentation for group_concat in the sense 
that it does not mention that the order is (always) arbitrary? 

Regards,
Hardy

> Am 2020-03-02 um 04:46 schrieb Keith Medcalf  >:
> 
> 
> On Sunday, 1 March, 2020 14:58, mailing lists  > wrote:
> 
>> Assume I create the following table:
> 
>> CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
>> INSERT INTO Test (Value) VALUES('Alpha');
>> INSERT INTO Test (Value) VALUES('Beta');
>> INSERT INTO Test (Value) VALUES('Beta');
>> INSERT INTO Test (Value) VALUES('Alpha');
> 
>> According to the documentation of group_concat the order is undefined,
>> indeed:
> 
>> SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC;
>> SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC;
> 
>> Both queries result in Alpha,Beta.
> 
>> Changing the queries to
> 
>> WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC)
>> SELECT group_concat(x) FROM Result;
>> WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC)
>> SELECT group_concat(x) FROM Result;
> 
>> leads to the results Alpha,Beta, respectively Beta,Alpha.
> 
>> Is this a coincidence or is this guaranteed to work?
> 
>> Are there any other solutions / possibilities?
> 
> group_concat builds a "group" by concatenating the values sent to it in the 
> order they are sent.  If you do not know this order then for all intents and 
> purposes the order is "undefined" because it is defined as the order in which 
> the query planner decides to visit the rows forming the group.  SQLite3 
> believes that all aggregate functions are commutative and that the order in 
> which rows are fed into them is immaterial to the result and there 
> (presently) is no way to specify that this is not the case.
> 
> So in the rare case where the aggregate is not commutative and you depend on 
> the presentation order, then you must specify it.  The only built-in 
> aggregate that is not commutative is the group_concat function.  If you were 
> to write another non-commutative aggregate function, lets say SHA1(...), that 
> computed the SHA1 hash of the values fed into it, you would also have to 
> control the presentation order or the result would be "undefined".
> 
> select group_concat(value) from (select distinct value from test order by 
> value desc); will do that.  (rephrasing as a CTE makes no difference)
> 
> This works because the query as phrased cannot be flattened since the outer 
> query contains an aggregate and the inner query contains an order by.
> 
> Moving the distinct does not alter the fact that the query cannot be 
> flattened.
> 
> select group_concat(distinct value) from (select value from test order by 
> value desc);
> 
> Whether the query planner will always not flatten a query where the outer 
> query contains an aggregate and the inner query contains an order by is 
> something on which I cannot comment other than to say that is does not 
> flatten such a query up to now.
> 
> -- 
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org 
> 
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to enforce a specific order of group_concat?

[Keith Medcalf]

On Sunday, 1 March, 2020 14:58, mailing lists  wrote:

>Assume I create the following table:

>CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
>INSERT INTO Test (Value) VALUES('Alpha');
>INSERT INTO Test (Value) VALUES('Beta');
>INSERT INTO Test (Value) VALUES('Beta');
>INSERT INTO Test (Value) VALUES('Alpha');

>According to the documentation of group_concat the order is undefined,
>indeed:

>SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC;
>SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC;

>Both queries result in Alpha,Beta.

>Changing the queries to

>WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC)
>SELECT group_concat(x) FROM Result;
>WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC)
>SELECT group_concat(x) FROM Result;

>leads to the results Alpha,Beta, respectively Beta,Alpha.

>Is this a coincidence or is this guaranteed to work?

>Are there any other solutions / possibilities?

group_concat builds a "group" by concatenating the values sent to it in the 
order they are sent.  If you do not know this order then for all intents and 
purposes the order is "undefined" because it is defined as the order in which 
the query planner decides to visit the rows forming the group.  SQLite3 
believes that all aggregate functions are commutative and that the order in 
which rows are fed into them is immaterial to the result and there (presently) 
is no way to specify that this is not the case.

So in the rare case where the aggregate is not commutative and you depend on 
the presentation order, then you must specify it.  The only built-in aggregate 
that is not commutative is the group_concat function.  If you were to write 
another non-commutative aggregate function, lets say SHA1(...), that computed 
the SHA1 hash of the values fed into it, you would also have to control the 
presentation order or the result would be "undefined".

select group_concat(value) from (select distinct value from test order by value 
desc); will do that.  (rephrasing as a CTE makes no difference)

This works because the query as phrased cannot be flattened since the outer 
query contains an aggregate and the inner query contains an order by.

Moving the distinct does not alter the fact that the query cannot be flattened.

select group_concat(distinct value) from (select value from test order by value 
desc);

Whether the query planner will always not flatten a query where the outer query 
contains an aggregate and the inner query contains an order by is something on 
which I cannot comment other than to say that is does not flatten such a query 
up to now.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to enforce a specific order of group_concat?

[mailing lists]

Assume I create the following table:

CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT);
INSERT INTO Test (Value) VALUES('Alpha');
INSERT INTO Test (Value) VALUES('Beta');
INSERT INTO Test (Value) VALUES('Beta');
INSERT INTO Test (Value) VALUES('Alpha');

According to the documentation of group_concat the order is undefined, indeed:

SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC;
SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC;

Both queries result in Alpha,Beta.

Changing the queries to

WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC) SELECT 
group_concat(x) FROM Result;
WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC) SELECT 
group_concat(x) FROM Result;

leads to the results Alpha,Beta, respectively Beta,Alpha.

Is this a coincidence or is this guaranteed to work?

Are there any other solutions / possibilities?

Regards,
Hardy
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to prevent sqlite_reset reporting an already known error

[Simon Slavin]

On 26 Feb 2020, at 11:58pm, mailing lists  wrote:

> The issue is that sqlite_reset() reports the same error that already 
> sqlite3_step() reported. How can I prevent that sqlite_reset() reports the 
> same error.

You can't.  It's working as designed.

One would normally test the result that sqlite_reset() returns, making sure it 
is either SQLITE_OK or the error you got from sqlite3_step(). However I see 
you're using a wrapper that makes it difficult.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to prevent sqlite_reset reporting an already known error

[mailing lists]

I am executing a prepared statement S with a couple of different bindings. The 
execution sequence is similar to this

while (moreBindings)
{
 bind_parameters_to_prepared_statement;
sqlite3_step();

if (error)
{
…
}
sqlite_reset();
if (error)
{
}

}

The issue is that sqlite_reset() reports the same error that already 
sqlite3_step() reported. How can I prevent that sqlite_reset() reports the same 
error. I also tried to clear the bindings before the reset statement but this 
did not help either.

PS: Actually, I am using not directly the SQLite statement but a wrapper around 
it that makes it a bit more complicated to filter out the multiple reported 
same error.

Regards,
Hardy

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to compile sqlite with gcov support?

[Xingwei Lin]

When I deleted the --enable-debug flag, the .gcno file can be generated.

Not sure why the --enable-debug flag will influence gcov compilation.

On Tue, Feb 18, 2020 at 3:16 PM Xingwei Lin  wrote:

> Hi,
>
> I decompressed the sqlite-autoconf-3310100.tar.gz and compiled it with the
> following commands:
>
> ```
> cd sqlite-autoconf-3310100
> CFLAGS="-g -ftest-coverage -fprofile-arcs" ./configure --enable-fts3
> --enable-debug --enable-session
> make -j$(nproc)
> ```
>
> But when I completed the building process, I didn't find the .gcno file.
> And when I execute sqlite3, it also didn't occur the  .gcda files.
>
> --
> Best regards，
> Xingwei Lin
>


-- 
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to compile sqlite with gcov support?

[Xingwei Lin]

Hi,

I decompressed the sqlite-autoconf-3310100.tar.gz and compiled it with the
following commands:

```
cd sqlite-autoconf-3310100
CFLAGS="-g -ftest-coverage -fprofile-arcs" ./configure --enable-fts3
--enable-debug --enable-session
make -j$(nproc)
```

But when I completed the building process, I didn't find the .gcno file.
And when I execute sqlite3, it also didn't occur the  .gcda files.

-- 
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Bart Smissaert]

Solved this now, nil to do with SQL, but just running a different search
(other value code and then you can ask for a secondary value and no need
anymore to find the matching pair).

RBS

On Mon, Feb 10, 2020 at 8:22 AM Bart Smissaert 
wrote:

> I fully agree with you, but I sofar I have no control over this data, I
> have it like I showed.
> As far as I can see there always will be a secondary value, but as you say
> I can't be sure.
> All this has to do with changing our clinical coding system from Read
> codes to Snomed.
> In the old setup there was the concept of a secondary value (systolic >>
> diastolic), but it
> seems in this particular case that is missing.
> I get the data by running searches (not SQL) on a clinical database and I
> have no control
> over this database.
> I will see if I can get better data with a different search, to do with
> blood pressure values.
>
> RBS
>
> On Mon, Feb 10, 2020 at 3:12 AM Richard Damon 
> wrote:
>
>> On 2/9/20 7:24 PM, Bart Smissaert wrote:
>> > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID
>> > 
>> > 1308 15/Mar/2013 Systolic 127 701559
>> > 1308 15/Mar/2013 Diastolic 81 701568
>> > 1308 27/Jun/2013 Systolic 132 701562
>> > 1308 27/Jun/2013 Systolic 141 701563
>> > 1308 27/Jun/2013 Systolic 143 701564
>> > 1308 27/Jun/2013 Diastolic 82 701571
>> > 1308 27/Jun/2013 Diastolic 85 701572
>> > 1308 27/Jun/2013 Diastolic 94 701573
>> > 278975701 08/Mar/2018 Systolic 136 1583551
>> > 278975701 08/Mar/2018 Diastolic 99 1583591
>> > 278975701 04/Apr/2018 Systolic 119 1583552
>> > 278975701 04/Apr/2018 Systolic 124 1583553
>> > 278975701 04/Apr/2018 Systolic 130 1583554
>> > 278975701 04/Apr/2018 Diastolic 74 1583592
>> > 278975701 04/Apr/2018 Diastolic 75 1583593
>> > 278975701 04/Apr/2018 Diastolic 85 1583594
>> >
>> > These are systolic and diastolic blood pressures for 2 people with the
>> ID's
>> > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
>> > Systolic and diastolic values are a pair and should be grouped in one
>> row.
>> > This is no problem if there is only one pair for one date, but sometimes
>> > there multiple pairs per date.
>> > The pairing should be based on the rowed if there are multiple pairs by
>> > date, so for ID 1308
>> > I should get:
>> >
>> > 127/81
>> > 132/82
>> > 141/85
>> > 143/94
>> >
>> > What should be the SQL to group like this?
>> >
>> > RBS
>>
>> To be honest, I think the problem is fundamentally badly designed. You
>> say pair the two readings by ROWID, but they of course don't have the
>> same ROWID, but you seem to be saying to pair them sorted by ROWID (1st
>> to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't
>> the same number of each? You may say that you know that there will
>> always be the same number, but there is no constraint that forces this,
>> so any general program is going to have to deal with the possibility
>> (and at least throw out an error when it sees that).
>>
>>
>> --
>> Richard Damon
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Jean-Luc Hainaut]

On 11/02/2020 01:35, Simon Slavin wrote:

I don't think that creating an index on a view actually works, does it?
You're right.  What was I thinking ?  Maybe I've used another implementation of 
SQL that it does work on.  Thanks for picking me up on it.


You are right, SQL Server allows you to create an index on a view.

J-L Hainaut


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Simon Slavin]

On 10 Feb 2020, at 10:41pm, Wolfgang Enzinger  wrote:

> Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin:
> 
>> On 10 Feb 2020, at 1:25am, no...@null.net wrote:
>> 
>> create two VIEWs, [...].  Index both VIEWs on (id, date),
> 
> I don't think that creating an index on a view actually works, does it?

You're right.  What was I thinking ?  Maybe I've used another implementation of 
SQL that it does work on.  Thanks for picking me up on it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Wolfgang Enzinger]

Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin:

> On 10 Feb 2020, at 1:25am, no...@null.net wrote:
>
> create two VIEWs, [...].  Index both VIEWs on (id, date),

I don't think that creating an index on a view actually works, does it?

Wolfgang

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Jen Pollock]

Sorry, I made a typo. The windows should be ORDER BY ROWID, not ORDER BY
ID.

Jen

On Mon, Feb 10, 2020 at 09:19:59AM -0700, Jen Pollock wrote:
> I think the following works:
> 
>  SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic
>  FROM
>  (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic, row_number() OVER 
> id_date r
>   FROM pressure
>   WHERE TERM = 'Systolic'
>   WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID)
>  ) s
>  JOIN
>  (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Diastolic, row_number() OVER 
> id_date r
>   FROM pressure
>   WHERE TERM = 'Diastolic'
>   WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID)
>  ) d
>  ON s.ID = d.ID AND s.Date = d.Date AND s.r = d.r
>  ORDER BY s.ID, s.Date;
> 
> You will need a fairly recent version of SQLite, older versions don't
> have window functions. And I have assumed that there is a Diastolic
> value for every Systolic value and vice versa.
> 
> Jen
> 
> On Mon, Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote:
> > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID
> > 
> > 1308 15/Mar/2013 Systolic 127 701559
> > 1308 15/Mar/2013 Diastolic 81 701568
> > 1308 27/Jun/2013 Systolic 132 701562
> > 1308 27/Jun/2013 Systolic 141 701563
> > 1308 27/Jun/2013 Systolic 143 701564
> > 1308 27/Jun/2013 Diastolic 82 701571
> > 1308 27/Jun/2013 Diastolic 85 701572
> > 1308 27/Jun/2013 Diastolic 94 701573
> > 278975701 08/Mar/2018 Systolic 136 1583551
> > 278975701 08/Mar/2018 Diastolic 99 1583591
> > 278975701 04/Apr/2018 Systolic 119 1583552
> > 278975701 04/Apr/2018 Systolic 124 1583553
> > 278975701 04/Apr/2018 Systolic 130 1583554
> > 278975701 04/Apr/2018 Diastolic 74 1583592
> > 278975701 04/Apr/2018 Diastolic 75 1583593
> > 278975701 04/Apr/2018 Diastolic 85 1583594
> > 
> > These are systolic and diastolic blood pressures for 2 people with the ID's
> > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
> > Systolic and diastolic values are a pair and should be grouped in one row.
> > This is no problem if there is only one pair for one date, but sometimes
> > there multiple pairs per date.
> > The pairing should be based on the rowed if there are multiple pairs by
> > date, so for ID 1308
> > I should get:
> > 
> > 127/81
> > 132/82
> > 141/85
> > 143/94
> > 
> > What should be the SQL to group like this?
> > 
> > RBS
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Jen Pollock]

I think the following works:

 SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic
 FROM
 (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic, row_number() OVER id_date 
r
  FROM pressure
  WHERE TERM = 'Systolic'
  WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID)
 ) s
 JOIN
 (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Diastolic, row_number() OVER 
id_date r
  FROM pressure
  WHERE TERM = 'Diastolic'
  WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID)
 ) d
 ON s.ID = d.ID AND s.Date = d.Date AND s.r = d.r
 ORDER BY s.ID, s.Date;

You will need a fairly recent version of SQLite, older versions don't
have window functions. And I have assumed that there is a Diastolic
value for every Systolic value and vice versa.

Jen

On Mon, Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote:
> ID ENTRY_DATE TERM NUMERIC_VALUE ROWID
> 
> 1308 15/Mar/2013 Systolic 127 701559
> 1308 15/Mar/2013 Diastolic 81 701568
> 1308 27/Jun/2013 Systolic 132 701562
> 1308 27/Jun/2013 Systolic 141 701563
> 1308 27/Jun/2013 Systolic 143 701564
> 1308 27/Jun/2013 Diastolic 82 701571
> 1308 27/Jun/2013 Diastolic 85 701572
> 1308 27/Jun/2013 Diastolic 94 701573
> 278975701 08/Mar/2018 Systolic 136 1583551
> 278975701 08/Mar/2018 Diastolic 99 1583591
> 278975701 04/Apr/2018 Systolic 119 1583552
> 278975701 04/Apr/2018 Systolic 124 1583553
> 278975701 04/Apr/2018 Systolic 130 1583554
> 278975701 04/Apr/2018 Diastolic 74 1583592
> 278975701 04/Apr/2018 Diastolic 75 1583593
> 278975701 04/Apr/2018 Diastolic 85 1583594
> 
> These are systolic and diastolic blood pressures for 2 people with the ID's
> 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
> Systolic and diastolic values are a pair and should be grouped in one row.
> This is no problem if there is only one pair for one date, but sometimes
> there multiple pairs per date.
> The pairing should be based on the rowed if there are multiple pairs by
> date, so for ID 1308
> I should get:
> 
> 127/81
> 132/82
> 141/85
> 143/94
> 
> What should be the SQL to group like this?
> 
> RBS
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Richard Damon]

On 2/9/20 11:44 PM, Rowan Worth wrote:

On Mon, 10 Feb 2020 at 11:12, Richard Damon 
wrote:


On 2/9/20 7:24 PM, Bart Smissaert wrote:

ID ENTRY_DATE TERM NUMERIC_VALUE ROWID

1308 15/Mar/2013 Systolic 127 701559
1308 15/Mar/2013 Diastolic 81 701568
1308 27/Jun/2013 Systolic 132 701562
1308 27/Jun/2013 Systolic 141 701563
1308 27/Jun/2013 Systolic 143 701564
1308 27/Jun/2013 Diastolic 82 701571
1308 27/Jun/2013 Diastolic 85 701572
1308 27/Jun/2013 Diastolic 94 701573
278975701 08/Mar/2018 Systolic 136 1583551
278975701 08/Mar/2018 Diastolic 99 1583591
278975701 04/Apr/2018 Systolic 119 1583552
278975701 04/Apr/2018 Systolic 124 1583553
278975701 04/Apr/2018 Systolic 130 1583554
278975701 04/Apr/2018 Diastolic 74 1583592
278975701 04/Apr/2018 Diastolic 75 1583593
278975701 04/Apr/2018 Diastolic 85 1583594

These are systolic and diastolic blood pressures for 2 people with the

ID's

1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
Systolic and diastolic values are a pair and should be grouped in one

row.

This is no problem if there is only one pair for one date, but sometimes
there multiple pairs per date.
The pairing should be based on the rowed if there are multiple pairs by
date, so for ID 1308
I should get:

127/81
132/82
141/85
143/94

What should be the SQL to group like this?

RBS

To be honest, I think the problem is fundamentally badly designed. You
say pair the two readings by ROWID, but they of course don't have the
same ROWID, but you seem to be saying to pair them sorted by ROWID (1st
to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't
the same number of each? You may say that you know that there will
always be the same number, but there is no constraint that forces this,
so any general program is going to have to deal with the possibility
(and at least throw out an error when it sees that).


Yeah, it would have been easier to group the readings at write time - eg.
via another column storing the time of day or the "nth reading of the day".
You could still add the latter, post-hoc.

Note that ROWID is not persistent -- see quirk #6 here:
https://www.sqlite.org/rowidtable.html

I would expect that VACUUM's renumbering happens to maintain the row order,
but I doubt it's guaranteed. If you do have an INTEGER PRIMARY KEY it's
better to refer to that directly.

-Rowan


I think Vacuum maintains Row order but not RowID, unless it has been 
aliased to a user defined column.


Yes, one solution would be to add another column that can be used either 
alone or paired with other columns to allow a join to find the pair of 
readings. So modified, the writing application could be updated to write 
the same value into that column for the two readings, and/or a fix-it 
app could be run to add it to existing data.


SQL itself is unlikely to have a simple way to do this, as the problem 
isn't solvable in general, which is what the SQL solution would be 
attempting, unless the SQL solution was basically doing the fixup 
operation, finding the duplicate ID/Date rows and adding the pairing value.


--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Bart Smissaert]

I fully agree with you, but I sofar I have no control over this data, I
have it like I showed.
As far as I can see there always will be a secondary value, but as you say
I can't be sure.
All this has to do with changing our clinical coding system from Read codes
to Snomed.
In the old setup there was the concept of a secondary value (systolic >>
diastolic), but it
seems in this particular case that is missing.
I get the data by running searches (not SQL) on a clinical database and I
have no control
over this database.
I will see if I can get better data with a different search, to do with
blood pressure values.

RBS

On Mon, Feb 10, 2020 at 3:12 AM Richard Damon 
wrote:

> On 2/9/20 7:24 PM, Bart Smissaert wrote:
> > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID
> > 
> > 1308 15/Mar/2013 Systolic 127 701559
> > 1308 15/Mar/2013 Diastolic 81 701568
> > 1308 27/Jun/2013 Systolic 132 701562
> > 1308 27/Jun/2013 Systolic 141 701563
> > 1308 27/Jun/2013 Systolic 143 701564
> > 1308 27/Jun/2013 Diastolic 82 701571
> > 1308 27/Jun/2013 Diastolic 85 701572
> > 1308 27/Jun/2013 Diastolic 94 701573
> > 278975701 08/Mar/2018 Systolic 136 1583551
> > 278975701 08/Mar/2018 Diastolic 99 1583591
> > 278975701 04/Apr/2018 Systolic 119 1583552
> > 278975701 04/Apr/2018 Systolic 124 1583553
> > 278975701 04/Apr/2018 Systolic 130 1583554
> > 278975701 04/Apr/2018 Diastolic 74 1583592
> > 278975701 04/Apr/2018 Diastolic 75 1583593
> > 278975701 04/Apr/2018 Diastolic 85 1583594
> >
> > These are systolic and diastolic blood pressures for 2 people with the
> ID's
> > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
> > Systolic and diastolic values are a pair and should be grouped in one
> row.
> > This is no problem if there is only one pair for one date, but sometimes
> > there multiple pairs per date.
> > The pairing should be based on the rowed if there are multiple pairs by
> > date, so for ID 1308
> > I should get:
> >
> > 127/81
> > 132/82
> > 141/85
> > 143/94
> >
> > What should be the SQL to group like this?
> >
> > RBS
>
> To be honest, I think the problem is fundamentally badly designed. You
> say pair the two readings by ROWID, but they of course don't have the
> same ROWID, but you seem to be saying to pair them sorted by ROWID (1st
> to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't
> the same number of each? You may say that you know that there will
> always be the same number, but there is no constraint that forces this,
> so any general program is going to have to deal with the possibility
> (and at least throw out an error when it sees that).
>
>
> --
> Richard Damon
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Rowan Worth]

On Mon, 10 Feb 2020 at 11:12, Richard Damon 
wrote:

> On 2/9/20 7:24 PM, Bart Smissaert wrote:
> > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID
> > 
> > 1308 15/Mar/2013 Systolic 127 701559
> > 1308 15/Mar/2013 Diastolic 81 701568
> > 1308 27/Jun/2013 Systolic 132 701562
> > 1308 27/Jun/2013 Systolic 141 701563
> > 1308 27/Jun/2013 Systolic 143 701564
> > 1308 27/Jun/2013 Diastolic 82 701571
> > 1308 27/Jun/2013 Diastolic 85 701572
> > 1308 27/Jun/2013 Diastolic 94 701573
> > 278975701 08/Mar/2018 Systolic 136 1583551
> > 278975701 08/Mar/2018 Diastolic 99 1583591
> > 278975701 04/Apr/2018 Systolic 119 1583552
> > 278975701 04/Apr/2018 Systolic 124 1583553
> > 278975701 04/Apr/2018 Systolic 130 1583554
> > 278975701 04/Apr/2018 Diastolic 74 1583592
> > 278975701 04/Apr/2018 Diastolic 75 1583593
> > 278975701 04/Apr/2018 Diastolic 85 1583594
> >
> > These are systolic and diastolic blood pressures for 2 people with the
> ID's
> > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
> > Systolic and diastolic values are a pair and should be grouped in one
> row.
> > This is no problem if there is only one pair for one date, but sometimes
> > there multiple pairs per date.
> > The pairing should be based on the rowed if there are multiple pairs by
> > date, so for ID 1308
> > I should get:
> >
> > 127/81
> > 132/82
> > 141/85
> > 143/94
> >
> > What should be the SQL to group like this?
> >
> > RBS
>
> To be honest, I think the problem is fundamentally badly designed. You
> say pair the two readings by ROWID, but they of course don't have the
> same ROWID, but you seem to be saying to pair them sorted by ROWID (1st
> to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't
> the same number of each? You may say that you know that there will
> always be the same number, but there is no constraint that forces this,
> so any general program is going to have to deal with the possibility
> (and at least throw out an error when it sees that).
>

Yeah, it would have been easier to group the readings at write time - eg.
via another column storing the time of day or the "nth reading of the day".
You could still add the latter, post-hoc.

Note that ROWID is not persistent -- see quirk #6 here:
https://www.sqlite.org/rowidtable.html

I would expect that VACUUM's renumbering happens to maintain the row order,
but I doubt it's guaranteed. If you do have an INTEGER PRIMARY KEY it's
better to refer to that directly.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Richard Damon]

On 2/9/20 7:24 PM, Bart Smissaert wrote:

ID ENTRY_DATE TERM NUMERIC_VALUE ROWID

1308 15/Mar/2013 Systolic 127 701559
1308 15/Mar/2013 Diastolic 81 701568
1308 27/Jun/2013 Systolic 132 701562
1308 27/Jun/2013 Systolic 141 701563
1308 27/Jun/2013 Systolic 143 701564
1308 27/Jun/2013 Diastolic 82 701571
1308 27/Jun/2013 Diastolic 85 701572
1308 27/Jun/2013 Diastolic 94 701573
278975701 08/Mar/2018 Systolic 136 1583551
278975701 08/Mar/2018 Diastolic 99 1583591
278975701 04/Apr/2018 Systolic 119 1583552
278975701 04/Apr/2018 Systolic 124 1583553
278975701 04/Apr/2018 Systolic 130 1583554
278975701 04/Apr/2018 Diastolic 74 1583592
278975701 04/Apr/2018 Diastolic 75 1583593
278975701 04/Apr/2018 Diastolic 85 1583594

These are systolic and diastolic blood pressures for 2 people with the ID's
1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
Systolic and diastolic values are a pair and should be grouped in one row.
This is no problem if there is only one pair for one date, but sometimes
there multiple pairs per date.
The pairing should be based on the rowed if there are multiple pairs by
date, so for ID 1308
I should get:

127/81
132/82
141/85
143/94

What should be the SQL to group like this?

RBS


To be honest, I think the problem is fundamentally badly designed. You 
say pair the two readings by ROWID, but they of course don't have the 
same ROWID, but you seem to be saying to pair them sorted by ROWID (1st 
to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't 
the same number of each? You may say that you know that there will 
always be the same number, but there is no constraint that forces this, 
so any general program is going to have to deal with the possibility 
(and at least throw out an error when it sees that).



--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Keith Medcalf]

That's good, but this not screw up later userid/date if an entry is AWOL.

WITH systolic 
  AS (
  select userid,
 date,
 rank() over (partition by userid, date order by id) as rank, 
 reading 
from pressure 
   where term == 'Systolic'
 ),
 diastolic 
  AS (
  select userid,
 date,
 rank() over (partition by userid, date order by id) as rank, 
 reading 
from pressure 
   where term == 'Diastolic'
 )
SELECT d.userid,
   d.date,
   s.reading || '/' || d.reading AS Reading
  FROM systolic s
  JOIN diastolic d
ON d.userid == s.userid
   AND d.date == s.date
   AND d.rank == s.rank
;



-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of no...@null.net
>Sent: Sunday, 9 February, 2020 18:26
>To: SQLite mailing list 
>Subject: Re: [sqlite] How to group this?
>
>On Mon Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote:
>> I should get:
>>
>> 127/81
>> 132/82
>> 141/85
>> 143/94
>>
>> What should be the SQL to group like this?
>
>Here is one way that appears to generate the correct result.
>
>CREATE TABLE pressure(
>id INTEGER PRIMARY KEY,
>userid INTEGER NOT NULL,
>date TEXT NOT NULL,
>term text NOT NULL,
>reading INTEGER NOT NULL
>);
>
>INSERT INTO
>pressure(userid,date,term,reading,id)
>VALUES
>(1308,'15/Mar/2013','Systolic',127,701559),
>(1308,'15/Mar/2013','Diastolic',81,701568),
>(1308,'27/Jun/2013','Systolic',132,701562),
>(1308,'27/Jun/2013','Systolic',141,701563),
>(1308,'27/Jun/2013','Systolic',143,701564),
>(1308,'27/Jun/2013','Diastolic',82,701571),
>(1308,'27/Jun/2013','Diastolic',85,701572),
>(1308,'27/Jun/2013','Diastolic',94,701573),
>(278975701,'08/Mar/2018','Systolic',136,1583551),
>(278975701,'08/Mar/2018','Diastolic',99,1583591),
>(278975701,'04/Apr/2018','Systolic',119,1583552),
>(278975701,'04/Apr/2018','Systolic',124,1583553),
>(278975701,'04/Apr/2018','Systolic',130,1583554),
>(278975701,'04/Apr/2018','Diastolic',74,1583592),
>(278975701,'04/Apr/2018','Diastolic',75,1583593),
>(278975701,'04/Apr/2018','Diastolic',85,1583594);
>
>WITH
>systolic AS (
>SELECT
>row_number() OVER (order by id) AS row,
>*
>FROM
>pressure
>WHERE
>term='Systolic'
>),
>diastolic AS (
>SELECT
>row_number() OVER (order by id) AS row,
>*
>FROM
>pressure
>WHERE
>term='Diastolic'
>)
>SELECT
>d.userid AS User,
>d.date AS Date,
>s.reading || '/' || d.reading AS Reading
>FROM
>systolic s
>LEFT JOIN
>diastolic d
>ON
>d.row=s.row
>ORDER BY
>d.userid,
>d.date,
>d.id
>;
>
>I'm sure others will produce more elegant solutions - I was just using
>your question as an exercise to learn about window functions. The query
>plan of the above looks a little scary though:
>
> id   parent  notused  detail
> 20   0CO-ROUTINE 1
> 92   0CO-ROUTINE 4
> 12   9   0SCAN TABLE pressure
> 26   2   0SCAN SUBQUERY 4
> 72   0   0MATERIALIZE 2
> 79   72  0CO-ROUTINE 5
> 82   79  0SCAN TABLE pressure
> 97   72  0SCAN SUBQUERY 5
> 144  0   0SCAN SUBQUERY 1 AS s
> 159  0   0SEARCH SUBQUERY 2 AS d USING AUTOMATIC
>COVERING INDEX
>   (row=?)
> 180  0   0USE TEMP B-TREE FOR ORDER BY
>
>Presumably if you index the date/userid and filter the CTE tables it
>gets a little better.
>
>--
>Mark Lawrence
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Keith Medcalf]

  select id, 
 entry_date, 
 max(case when term == 'Systolic' then reading end) as Systolic, 
 max(case when term == 'Diastolic' then reading end) as Diastolic 
from the_table
group by id, entry_date
;

should be

  select id, 
 entry_date, 
 max(case when term == 'Systolic' then reading end)
 || '/' ||
 max(case when term == 'Diastolic' then reading end) as Pressure
from the_table
group by id, entry_date
;

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Keith Medcalf
>Sent: Sunday, 9 February, 2020 19:17
>To: SQLite mailing list 
>Subject: Re: [sqlite] How to group this?
>
>  select id,
> entry_date,
> max(case when term == 'Systolic' then reading end) as Systolic,
> max(case when term == 'Diastolic' then reading end) as Diastolic
>from the_table
>group by id, entry_date
>;
>
>If you want to make sure you have both terms for a given id/entry_date
>then you need to make it a subquery so you can check for null.
>
>select id,
>   entry_date,
>   systolic || '/' || diastolic as pressure
>  from (
>  select id,
> entry_date,
> max(case when term == 'Systolic' then reading end) as
>Systolic,
> max(case when term == 'Diastolic' then reading end) as
>Diastolic
>from the_table
>group by id, entry_date
>   )
>  where systolic is not null
>and diastolic is not null
>;
>
>Note that (id, entry_date, term) needs to be unique ... or you will just
>get the combination of the max(systolic)/max(diastolic) for the results
>for a given id/entry_date.
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users  On
>>Behalf Of Bart Smissaert
>>Sent: Sunday, 9 February, 2020 17:25
>>To: General Discussion of SQLite Database >us...@mailinglists.sqlite.org>
>>Subject: [sqlite] How to group this?
>>
>>ID ENTRY_DATE TERM NUMERIC_VALUE ROWID
>>
>>1308 15/Mar/2013 Systolic 127 701559
>>1308 15/Mar/2013 Diastolic 81 701568
>>1308 27/Jun/2013 Systolic 132 701562
>>1308 27/Jun/2013 Systolic 141 701563
>>1308 27/Jun/2013 Systolic 143 701564
>>1308 27/Jun/2013 Diastolic 82 701571
>>1308 27/Jun/2013 Diastolic 85 701572
>>1308 27/Jun/2013 Diastolic 94 701573
>>278975701 08/Mar/2018 Systolic 136 1583551
>>278975701 08/Mar/2018 Diastolic 99 1583591
>>278975701 04/Apr/2018 Systolic 119 1583552
>>278975701 04/Apr/2018 Systolic 124 1583553
>>278975701 04/Apr/2018 Systolic 130 1583554
>>278975701 04/Apr/2018 Diastolic 74 1583592
>>278975701 04/Apr/2018 Diastolic 75 1583593
>>278975701 04/Apr/2018 Diastolic 85 1583594
>>
>>These are systolic and diastolic blood pressures for 2 people with the
>>ID's
>>1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
>>Systolic and diastolic values are a pair and should be grouped in one
>>row.
>>This is no problem if there is only one pair for one date, but sometimes
>>there multiple pairs per date.
>>The pairing should be based on the rowed if there are multiple pairs by
>>date, so for ID 1308
>>I should get:
>>
>>127/81
>>132/82
>>141/85
>>143/94
>>
>>What should be the SQL to group like this?
>>
>>RBS
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Keith Medcalf]

  select id, 
 entry_date, 
 max(case when term == 'Systolic' then reading end) as Systolic, 
 max(case when term == 'Diastolic' then reading end) as Diastolic 
from the_table
group by id, entry_date
;

If you want to make sure you have both terms for a given id/entry_date then you 
need to make it a subquery so you can check for null.

select id, 
   entry_date, 
   systolic || '/' || diastolic as pressure
  from (
  select id, 
 entry_date, 
 max(case when term == 'Systolic' then reading end) as 
Systolic, 
 max(case when term == 'Diastolic' then reading end) as 
Diastolic 
from the_table
group by id, entry_date
   )
  where systolic is not null
and diastolic is not null
;

Note that (id, entry_date, term) needs to be unique ... or you will just get 
the combination of the max(systolic)/max(diastolic) for the results for a given 
id/entry_date.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Bart Smissaert
>Sent: Sunday, 9 February, 2020 17:25
>To: General Discussion of SQLite Database us...@mailinglists.sqlite.org>
>Subject: [sqlite] How to group this?
>
>ID ENTRY_DATE TERM NUMERIC_VALUE ROWID
>
>1308 15/Mar/2013 Systolic 127 701559
>1308 15/Mar/2013 Diastolic 81 701568
>1308 27/Jun/2013 Systolic 132 701562
>1308 27/Jun/2013 Systolic 141 701563
>1308 27/Jun/2013 Systolic 143 701564
>1308 27/Jun/2013 Diastolic 82 701571
>1308 27/Jun/2013 Diastolic 85 701572
>1308 27/Jun/2013 Diastolic 94 701573
>278975701 08/Mar/2018 Systolic 136 1583551
>278975701 08/Mar/2018 Diastolic 99 1583591
>278975701 04/Apr/2018 Systolic 119 1583552
>278975701 04/Apr/2018 Systolic 124 1583553
>278975701 04/Apr/2018 Systolic 130 1583554
>278975701 04/Apr/2018 Diastolic 74 1583592
>278975701 04/Apr/2018 Diastolic 75 1583593
>278975701 04/Apr/2018 Diastolic 85 1583594
>
>These are systolic and diastolic blood pressures for 2 people with the
>ID's
>1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
>Systolic and diastolic values are a pair and should be grouped in one
>row.
>This is no problem if there is only one pair for one date, but sometimes
>there multiple pairs per date.
>The pairing should be based on the rowed if there are multiple pairs by
>date, so for ID 1308
>I should get:
>
>127/81
>132/82
>141/85
>143/94
>
>What should be the SQL to group like this?
>
>RBS
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Simon Slavin]

On 10 Feb 2020, at 1:25am, no...@null.net wrote:

> Here is one way that appears to generate the correct result.

Another way: create two VIEWs, one for systolic, one for diasystolic.  Index 
both VIEWs on (id, date), then JOIN ON id AND date.  If you want to, you could 
use this to make a third VIEW which would have the systolic and diasystolic 
figures on the same row.

If you are going to do more than just this on that data convert the dates to 
MMDD form or to an integer day number to make searching and sorting easier. 
 If you're not confident with programming you could do this in SQLite using 
CASE for the month name.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[nomad]

On Mon Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote:
> I should get:
> 
> 127/81
> 132/82
> 141/85
> 143/94
> 
> What should be the SQL to group like this?

Here is one way that appears to generate the correct result.

CREATE TABLE pressure(
id INTEGER PRIMARY KEY,
userid INTEGER NOT NULL,
date TEXT NOT NULL,
term text NOT NULL,
reading INTEGER NOT NULL
);

INSERT INTO
pressure(userid,date,term,reading,id)
VALUES
(1308,'15/Mar/2013','Systolic',127,701559),
(1308,'15/Mar/2013','Diastolic',81,701568),
(1308,'27/Jun/2013','Systolic',132,701562),
(1308,'27/Jun/2013','Systolic',141,701563),
(1308,'27/Jun/2013','Systolic',143,701564),
(1308,'27/Jun/2013','Diastolic',82,701571),
(1308,'27/Jun/2013','Diastolic',85,701572),
(1308,'27/Jun/2013','Diastolic',94,701573),
(278975701,'08/Mar/2018','Systolic',136,1583551),
(278975701,'08/Mar/2018','Diastolic',99,1583591),
(278975701,'04/Apr/2018','Systolic',119,1583552),
(278975701,'04/Apr/2018','Systolic',124,1583553),
(278975701,'04/Apr/2018','Systolic',130,1583554),
(278975701,'04/Apr/2018','Diastolic',74,1583592),
(278975701,'04/Apr/2018','Diastolic',75,1583593),
(278975701,'04/Apr/2018','Diastolic',85,1583594);

WITH
systolic AS (
SELECT
row_number() OVER (order by id) AS row,
*
FROM
pressure
WHERE
term='Systolic'
),
diastolic AS (
SELECT
row_number() OVER (order by id) AS row,
*
FROM
pressure
WHERE
term='Diastolic'
)
SELECT
d.userid AS User,
d.date AS Date,
s.reading || '/' || d.reading AS Reading
FROM
systolic s
LEFT JOIN
diastolic d
ON
d.row=s.row
ORDER BY
d.userid,
d.date,
d.id
;

I'm sure others will produce more elegant solutions - I was just using
your question as an exercise to learn about window functions. The query
plan of the above looks a little scary though:

 id   parent  notused  detail
 20   0CO-ROUTINE 1
 92   0CO-ROUTINE 4
 12   9   0SCAN TABLE pressure
 26   2   0SCAN SUBQUERY 4
 72   0   0MATERIALIZE 2
 79   72  0CO-ROUTINE 5
 82   79  0SCAN TABLE pressure
 97   72  0SCAN SUBQUERY 5
 144  0   0SCAN SUBQUERY 1 AS s
 159  0   0SEARCH SUBQUERY 2 AS d USING AUTOMATIC COVERING INDEX
   (row=?)
 180  0   0USE TEMP B-TREE FOR ORDER BY

Presumably if you index the date/userid and filter the CTE tables it
gets a little better.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to group this?

[Bart Smissaert]

ID ENTRY_DATE TERM NUMERIC_VALUE ROWID

1308 15/Mar/2013 Systolic 127 701559
1308 15/Mar/2013 Diastolic 81 701568
1308 27/Jun/2013 Systolic 132 701562
1308 27/Jun/2013 Systolic 141 701563
1308 27/Jun/2013 Systolic 143 701564
1308 27/Jun/2013 Diastolic 82 701571
1308 27/Jun/2013 Diastolic 85 701572
1308 27/Jun/2013 Diastolic 94 701573
278975701 08/Mar/2018 Systolic 136 1583551
278975701 08/Mar/2018 Diastolic 99 1583591
278975701 04/Apr/2018 Systolic 119 1583552
278975701 04/Apr/2018 Systolic 124 1583553
278975701 04/Apr/2018 Systolic 130 1583554
278975701 04/Apr/2018 Diastolic 74 1583592
278975701 04/Apr/2018 Diastolic 75 1583593
278975701 04/Apr/2018 Diastolic 85 1583594

These are systolic and diastolic blood pressures for 2 people with the ID's
1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc.
Systolic and diastolic values are a pair and should be grouped in one row.
This is no problem if there is only one pair for one date, but sometimes
there multiple pairs per date.
The pairing should be based on the rowed if there are multiple pairs by
date, so for ID 1308
I should get:

127/81
132/82
141/85
143/94

What should be the SQL to group like this?

RBS
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I detect rows with non-ASCII values?

[Richard Damon]

On 1/18/20 3:21 AM, Rocky Ji wrote:

Hi,

I am asked to highlight rows containing strange characters. All data were
ingested by a proprietary crawler.

By strange, I mean, question marks, boxes, little Christmas Trees,  solid
arrows, etc. kind of symbols; these appear suddenly in flow of normal ASCII
English letters.

How do I approach this?

Thanks.


The first thing that you are going to need to find to do this is how the 
data has actually been stored. The strange characters you are describing 
sound like somewhere some data obtained in one character encoding, but 
then interpreted as another.  This is a very old problem, without a 
solution in general except to always know the encoding of your data. It 
goes back to the development of the ASCII character set which is the 
base for most character sets in use today (another major branch is 
EBCDIC, but that has its own issues and you tend to know when you need 
to deal with that).


The A in ASCII is for American, the ASCII character set was designed for 
American (i.e. english) data, and when it when it was developed, memory 
and bandwidth were limited and expensive, so you didn't waste them. 
ASCII was a compact set, using only 7 bits per character, and was fine 
for english information, with the basic alphabet. It became the base 
standard because America was a core part in the initial computer 
development, and had a lot of influence in the initial standards.


While it worked well for American data, it didn't work so well for many 
other countries, so most other countries adopted their own character set 
for use in their country, normally based on ASCII as a base, but adding 
codes to extend the coding to an 8 bit code, keep (at least most of) 
ASCII as the first 128 values.


To exchange data between character machines, you needed to include what 
character set the data was in (or you see some funny words due to the 
mis-match).


Operating systems adopted the concept of Code Pages, which basically 
defined which of the many standard character sets was to be used, and 
some transfer formats actually included as part of the header 
information what character set the data that follows was in. One of 
these was the web pages that were on the Internet.


Later, to try and get out of this mess, a new character encoding was 
invented called Unicode, Unicode initially intended to provide a single 
universal encoding that would let any of the many standard encodings be 
converted to this universal encoding. It was first thought that this 
could be done with a 16 bit character set, but it later needed to be 
enlarged in size as they found out how many different characters there 
really were. While memory isn't quite as precious as it was when ASCII 
was designed, it isn't so abundant that we could just increase the size 
of out data by a factor, so a compact encoding was developed called 
UTF-8, which represents the ASCII characters exactly as the ASCII 
character set, and all the extra characters with multiple bytes.


Because it did make files with the extra characters longer, and it was 
somewhat complicated to work with, and most people only worked with 
documents that could all be encoded in a single character set, its 
adoption was slow, but it now is becoming a norm, but still many things 
are in the old legacy encodings.


If you try to interpret a file that is in one of the legacy encodings as 
Unicode UTF-8, then (if it uses extended characters) it almost certainly 
will create decoding errors (UTF-8 was intentionally designed with 
redundancy in the encoding to easy processing, so many 'random' 
sequences become invalid). If you interpret a file that is in UTF-8 and 
a legacy encoding, you will tend to get some strange out of place 
extended characters.


My first guess is that your proprietary crawler either didn't properly 
detect the page encoding and handle it, ideally it would have converted 
it to UTF-8, but in might also save the data in the original encoding 
and saved what that encoding was, or your read out program isn't 
detecting the character set the data was stored as and processing it 
right. I believe SQLite assumes that 'TEXT' data is UTF-8 encoded, but 
other encodings can be declared or data stored as BLOBs.


What likely should happen is someone (maybe you) needs to read out 
samples of the funny data as a blob, and figure out how the data is 
actually encoded, ideally comparing it to the original page crawled, and 
once you know what the problem was, you can perhaps work on fixing it 
and detecting the records with problems.


One possible issue is that some conversion routines take characters they 
don't know how to handle and replace them with the ASCII Question Mark, 
and if that is what has been stored in the database, it may be very hard 
to distinguish that from an actual question mark in the data.


--
Richard Damon

___
sqlite-users mailing list

Re: [sqlite] How can I detect rows with non-ASCII values?

[Keith Medcalf]

If we are talking the later case, and the 'text' field contains text in Windows 
MBCS then you can use, for example:

for row in db.execute('select cast(mbcsfield as blob) from table'):
   textfield = row[0].decode('mbcs')

to recover proper unicode text.  If the encoding is not 'mbcs' substitute the 
actual encoding.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Keith Medcalf
>Sent: Saturday, 18 January, 2020 05:38
>To: SQLite mailing list 
>Subject: Re: [sqlite] How can I detect rows with non-ASCII values?
>
>
>On Saturday, 18 January, 2020 05:21, Rocky Ji 
>wrote:
>
>>>  > GLOB supports character classes
>
>>thanks for teaching new keyword and its use.
>
>>My first attempt was very similar to what you suggest, except I used
>>sqlite3 and re from inside Python.
>
>>But as you see, I can't reliably seprate 'interrogative' question marks
>>from question marks that get displayed due to 'encoding faults'.
>
>>Any suggestions?
>
>Ah.  So the real problem is that you stored non-text (text defined a UTF-
>8 encoded sequence of unicode codepoints with no zero/null byte except at
>the end) in a database text field, and now you are trying to access those
>text fields with something that expects them to contain properly
>formatted text strings?  Or do you mean that they *are* valid UTF-8
>encoded strings any you are trying to encode them as something else?
>
>If the former you can retrieve the raw bytes in python by retrieving the
>field as cast(x as blob) and then .decode the result from whatever
>encoding it is in into proper unicode.
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I detect rows with non-ASCII values?

[Simon Slavin]

On 18 Jan 2020, at 12:12pm, Rocky Ji  wrote:

> By question marks, I meant- that some text, like Dutch programmers names, and 
> address in Nordic locations, have accents and umaults and other such 
> modifications done to English-alphabets. These get displayed as ? or box

SQLite doesn't display anything.  It's a database.  It stores things in a file 
and allows you to recall them.  It provides these facilities to programs other 
people write.

So you must be using a program which isn't SQLite to display those characters.  
That program will be choosing whether to display them as alphabet characters 
with accents, or strange things like boxes and question-marks.

What program are you using to display those characters ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I detect rows with non-ASCII values?

[Keith Medcalf]

On Saturday, 18 January, 2020 05:21, Rocky Ji  wrote:

>>  > GLOB supports character classes

>thanks for teaching new keyword and its use.

>My first attempt was very similar to what you suggest, except I used
>sqlite3 and re from inside Python.

>But as you see, I can't reliably seprate 'interrogative' question marks
>from question marks that get displayed due to 'encoding faults'.

>Any suggestions?

Ah.  So the real problem is that you stored non-text (text defined a UTF-8 
encoded sequence of unicode codepoints with no zero/null byte except at the 
end) in a database text field, and now you are trying to access those text 
fields with something that expects them to contain properly formatted text 
strings?  Or do you mean that they *are* valid UTF-8 encoded strings any you 
are trying to encode them as something else?

If the former you can retrieve the raw bytes in python by retrieving the field 
as cast(x as blob) and then .decode the result from whatever encoding it is in 
into proper unicode.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I detect rows with non-ASCII values?

[Keith Medcalf]

On Saturday, 18 January, 2020 05:13, Rocky Ji  wrote:

>Sorry for lack of clarity.

>By question marks, I meant- that some text, like Dutch programmers names,
>and address in Nordic locations, have accents and umaults and other such
>modifications done to English-alphabets. These get displayed as ? or box

x GLOB '*[^ -~]*' will return true (1) if x contains any character that is not 
printable 7-bit ASCII character

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I detect rows with non-ASCII values?

[Rocky Ji]

>
>  > GLOB supports character classes


thanks for teaching new keyword and its use.

My first attempt was very similar to what you suggest, except I used
sqlite3 and re from inside Python.

But as you see, I can't reliably seprate 'interrogative' question marks
from question marks that get displayed due to 'encoding faults'.

Any suggestions?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I detect rows with non-ASCII values?

[Rocky Ji]

Sorry for lack of clarity.

By question marks, I meant- that some text, like Dutch programmers names,
and address in Nordic locations, have accents and umaults and other such
modifications done to English-alphabets. These get displayed as ? or box

On Sat, Jan 18, 2020, 16:34 Clemens Ladisch  wrote:

> Rocky Ji wrote:
> > I am asked to highlight rows containing strange characters. All data were
> > ingested by a proprietary crawler.
> >
> > By strange, I mean, question marks, boxes, little Christmas Trees,  solid
> > arrows, etc. kind of symbols; these appear suddenly in flow of normal
> ASCII
> > English letters.
>
> GLOB supports character classes:
>
>   SELECT *
>   FROM MyTable
>   WHERE DataField GLOB '*[^ -~]*';
>
> Question marks _are_ ASCII characters.  If you want to allow fewer
> characters,
> list them:  [^ A-Za-z0-9,.-]
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I detect rows with non-ASCII values?

[Clemens Ladisch]

Rocky Ji wrote:
> I am asked to highlight rows containing strange characters. All data were
> ingested by a proprietary crawler.
>
> By strange, I mean, question marks, boxes, little Christmas Trees,  solid
> arrows, etc. kind of symbols; these appear suddenly in flow of normal ASCII
> English letters.

GLOB supports character classes:

  SELECT *
  FROM MyTable
  WHERE DataField GLOB '*[^ -~]*';

Question marks _are_ ASCII characters.  If you want to allow fewer characters,
list them:  [^ A-Za-z0-9,.-]


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How can I detect rows with non-ASCII values?

[Rocky Ji]

Hi,

I am asked to highlight rows containing strange characters. All data were
ingested by a proprietary crawler.

By strange, I mean, question marks, boxes, little Christmas Trees,  solid
arrows, etc. kind of symbols; these appear suddenly in flow of normal ASCII
English letters.

How do I approach this?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to disable dot commands?

[Xingwei Lin]

I like this answer!! I think I think it's the easiest way.

On Mon, Jan 13, 2020 at 10:22 AM Keith Medcalf  wrote:

>
> On Sunday, 12 January, 2020 18:44, Xingwei Lin 
> wrote:
>
> >Is there any way can we disable the dot commands feature in sqlite?
>
> SQLite does not process dot commands, they are commands to the shell.c
> SQLite Application program.
>
> The current shell.c application currently does not have a way to omit the
> meta commands.
>
> However, if you build your own you can simply search shell.c (or
> shell.c.in) for the function do_meta_command function definition and have
> it execute a "return 0;" at the top of the function after the variable
> declarations rather that process the meta command.  This will not remove
> the code that handles the meta commands but merely bypass the processing of
> them.
>
> That is make it look thusly by inserting the return 0; line:
>
> /*
> ** If an input line begins with "." then invoke this routine to
> ** process that line.
> **
> ** Return 1 on error, 2 to exit, and 0 otherwise.
> */
> static int do_meta_command(char *zLine, ShellState *p){
>   int h = 1;
>   int nArg = 0;
>   int n, c;
>   int rc = 0;
>   char *azArg[52];
>
>   return 0;
>
> #ifndef SQLITE_OMIT_VIRTUALTABLE
>   if( p->expert.pExpert ){
> expertFinish(p, 1, 0);
>   }
> #endif
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to disable dot commands?

[Keith Medcalf]

On Sunday, 12 January, 2020 18:44, Xingwei Lin  wrote:

>Is there any way can we disable the dot commands feature in sqlite?

SQLite does not process dot commands, they are commands to the shell.c SQLite 
Application program.

The current shell.c application currently does not have a way to omit the meta 
commands.

However, if you build your own you can simply search shell.c (or shell.c.in) 
for the function do_meta_command function definition and have it execute a 
"return 0;" at the top of the function after the variable declarations rather 
that process the meta command.  This will not remove the code that handles the 
meta commands but merely bypass the processing of them.

That is make it look thusly by inserting the return 0; line:

/*
** If an input line begins with "." then invoke this routine to
** process that line.
**
** Return 1 on error, 2 to exit, and 0 otherwise.
*/
static int do_meta_command(char *zLine, ShellState *p){
  int h = 1;
  int nArg = 0;
  int n, c;
  int rc = 0;
  char *azArg[52];

  return 0;

#ifndef SQLITE_OMIT_VIRTUALTABLE
  if( p->expert.pExpert ){
expertFinish(p, 1, 0);
  }
#endif

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to disable dot commands?

[Igor Korot]

Hi,

On Sun, Jan 12, 2020 at 7:44 PM Xingwei Lin  wrote:
>
> Hi,
>
> Is there any way can we disable the dot commands feature in sqlite?

Are you talking about the SQLite shell?
Why do you want to disable them? What is your specific scenario?

Thank you.

>
> --
> Best regards，
> Xingwei Lin
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to disable dot commands?

[Simon Slavin]

On 13 Jan 2020, at 1:43am, Xingwei Lin  wrote:

> Is there any way can we disable the dot commands feature in sqlite?

SQLite – the library you call from C and other programming languages – does not 
support the dot commands.  It doesn't recognise them.  If you try to use them 
you will get a complaint about bad syntax.

The dot commands are part of the SQLite command line shell program.  Only this 
one program understands the dot commands.  The source code for this program is 
part of the SQLite download package.

You could make your own copy of that program which did not support the dot 
commands.  But that would not stop someone else from using their copy on your 
own database files.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to disable dot commands?

[Xingwei Lin]

Hi,

Is there any way can we disable the dot commands feature in sqlite?

-- 
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get rowid for select query?

[Andy]

previous:
  rescells.Clear;
  rc := sqlite3_get_table(db, PChar(selectquery), @selres, @nRow, @nColumn,
@zErrmsg);
  Result := rc = SQLITE_OK;
  for i := 0 to nRow * nColumn - 1 do
  begin
rescells.Add(selres[i]);
  end;
  if selres <> nil then
sqlite3_free_table(selres);

current, working
  rescells.Clear;
  rc := sqlite3_get_table(db, PChar(selectquery), @selres, @nRow, @nColumn,
@zErrmsg);
  Result := rc = SQLITE_OK;
  for i := 0 to (nRow+1) * nColumn - 1 do <-- nRow+1  - it is works
  begin
rescells.Add(selres[i]);
  end;
  if selres <> nil then
sqlite3_free_table(selres);



pt., 10 sty 2020 o 14:01 Clemens Ladisch  napisał(a):

> Andy wrote:
> > I try "select rowid, field1,field2 from table" but first value was not
> > number rowid but literary string "rowid".
>
> Please show the actual code (not SQL, but your program) that you're
> executing.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get rowid for select query?

[Andy]

OK, first row is always header, next rows are values of query,

pt., 10 sty 2020 o 13:56 Andy  napisał(a):

> I don't know why sqlite3_get_table
> for 'SELECT rowid, id, Bytes, BytesCompr, flags FROM Articles where id=5
> limit 1'
> give me column names:
> rowid id Bytes BytesCompr flags
> instead od values of this columns
>
> pt., 10 sty 2020 o 13:36 Andy  napisał(a):
>
>> I successfully insert blob. I use sqlite3_last_insert_rowid afer calling
>> sqlite3_step.
>> But how to retrieve blob?
>> I try "select rowid, field1,field2 from table" but first value was not
>> number rowid but literary string "rowid".
>> I can get blob if I know rowid.
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get rowid for select query?

[Clemens Ladisch]

Andy wrote:
> I try "select rowid, field1,field2 from table" but first value was not
> number rowid but literary string "rowid".

Please show the actual code (not SQL, but your program) that you're executing.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get rowid for select query?

[Andy]

I don't know why sqlite3_get_table
for 'SELECT rowid, id, Bytes, BytesCompr, flags FROM Articles where id=5
limit 1'
give me column names:
rowid id Bytes BytesCompr flags
instead od values of this columns

pt., 10 sty 2020 o 13:36 Andy  napisał(a):

> I successfully insert blob. I use sqlite3_last_insert_rowid afer calling
> sqlite3_step.
> But how to retrieve blob?
> I try "select rowid, field1,field2 from table" but first value was not
> number rowid but literary string "rowid".
> I can get blob if I know rowid.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to get rowid for select query?

[Andy]

I successfully insert blob. I use sqlite3_last_insert_rowid afer calling
sqlite3_step.
But how to retrieve blob?
I try "select rowid, field1,field2 from table" but first value was not
number rowid but literary string "rowid".
I can get blob if I know rowid.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Xingwei Lin]

Thanks all the nice guys' reply!!

On Thu, Jan 9, 2020 at 10:59 PM Gary R. Schmidt  wrote:

> On 09/01/2020 17:47, Xingwei Lin wrote:
> > Hi,
> >
> > How can I pass -Dxxx compile option when I build sqlite? Such as, -
> > DSQLITE_ENABLE_INTERNAL_FUNCTIONS.
> >
> ./configure --help will tell you that CFLAGS is how you do that, so:
>
>  ./configure CFLAGS=-Dwhatever
>
> If you have many options:
>
>  ./configure CFLAGS="-Dwhatever -Dthis -Dthat -mwhoops"
>
> Cheers,
> GaryB-)
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Stephan Buchert]

I have an executable file myconfigure with

./configure CFLAGS="-O2 -DSQLITE_MAX_COLUMN=4096 -DHAVE_FDATASYNC
-DHAVE_STRCHRNUL -DHAVE_LOCALTIME_R -DHAVE_GMTIME_R -DHAVE_NAN
-DHAVE_USLEEP -DSQLITE_DEFAULT_WORKER_THREADS=4 -DSQLITE_TEMP_STORE=2
-DSQLITE_USE_URI -DSQLITE_ENABLE_API_ARMOR
-DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_DBSTAT_VTAB
-DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_FTS5
-DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_PREUPDATE_HOOK
-DSQLITE_ENABLE_PREUPDATE_HOOK -DSQLITE_ENABLE_MEMORY_MANAGEMENT
-DSQLITE_MAX_MMAP_SIZE=4393751543808
-DSQLITE_DEFAULT_MMAP_SIZE=2194728288256
-DSQLITE_DEFAULT_CACHE_SIZE=-8000 -DSQLITE_DEFAULT_WORKER_THREADS=4
-DSQLITE_ENABLE_MEMSYS5 -DSQLITE_ENABLE_STMTVTAB -DSQLITE_ENABLE_RTREE
-DSQLITE_ENABLE_GEOPOLY -DSQLITE_ENABLE_STAT4
-DSQLITE_ENABLE_UNLOCK_NOTIFY -DSQLITE_ENABLE_NULL_TRIM
-DSQLITE_ENABLE_OFFSET_SQL_FUNC" LIBS="-lm"

(everything on one line) and execute it instead of "configure" whenever
there is an update of Sqlite. Then "make" and "sudo make install".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Gary R. Schmidt]

On 09/01/2020 17:47, Xingwei Lin wrote:

Hi,

How can I pass -Dxxx compile option when I build sqlite? Such as, -
DSQLITE_ENABLE_INTERNAL_FUNCTIONS.


./configure --help will tell you that CFLAGS is how you do that, so:

./configure CFLAGS=-Dwhatever

If you have many options:

./configure CFLAGS="-Dwhatever -Dthis -Dthat -mwhoops"

Cheers,
GaryB-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Warren Young]

On Jan 9, 2020, at 6:51 AM, Warren Young  wrote:
> 
>./configure CFLAGS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS'
> 
> The reasons for the recommendation have to do with complications that result 
> from multiple variables, nested Makefile.am, etc.

It also allows autoreconf to work properly in the case of Autoconf and 
@AUTOREMAKE@ in the case of Autosetup.

To see it, try this in the Fossil source tree to produce a debuggable build:

./configure CFLAGS='-O0 -g'

Then look at the rule generated at the end for re-generating the Makefile:

Makefile: ./Makefile.in …noise noise noise…
/path/to/configure CFLAGS=-O0
touch /path/to/Makefile

You don’t get that by exporting CFLAGS as an environment variable, for example.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Warren Young]

On Jan 9, 2020, at 6:37 AM, Richard Hipp  wrote:
> 
> On 1/9/20, Xingwei Lin  wrote:
>> 
>> How can I pass -Dxxx compile option when I build sqlite? Such as, -
>> DSQLITE_ENABLE_INTERNAL_FUNCTIONS.
> 
> Option 1:
> 
> CFLAGS='-O2 -DSQLITE_ENABLE_INTERNAL_FUNCTIONS' ./configure && make
> 
> Option 2:
> 
> ./configure && OPTS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS' make -e
> 
> Option 3:
> 
> ./configure && make OPTS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS’

…or the one actually recommended by the Autoconf developers:

./configure CFLAGS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS'

The reasons for the recommendation have to do with complications that result 
from multiple variables, nested Makefile.am, etc.:


https://www.gnu.org/software/automake/manual/html_node/Flag-Variables-Ordering.html

I assume SQLite’s Autoconf usage isn’t complicated enough that all of that 
applies, but you want to get into the habit of doing it the way that works 
across the broadest set of use cases.

Incidentally, this method of overriding CFLAGS and such also works with 
Autosetup.  See the second example here:

https://msteveb.github.io/autosetup/user/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Richard Hipp]

On 1/9/20, Xingwei Lin  wrote:
> Hi,
>
> How can I pass -Dxxx compile option when I build sqlite? Such as, -
> DSQLITE_ENABLE_INTERNAL_FUNCTIONS.
>

Option 1:

CFLAGS='-O2 -DSQLITE_ENABLE_INTERNAL_FUNCTIONS' ./configure && make

Option 2:

./configure && OPTS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS' make -e

Option 3:

./configure && make OPTS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS'

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Kees Nuyt]

On Thu, 9 Jan 2020 14:47:44 +0800, you wrote:

> Hi,
>
> How can I pass -Dxxx compile option when I build sqlite? Such as, -
> DSQLITE_ENABLE_INTERNAL_FUNCTIONS.

I don't consider myself an expoert, but the script
below works for me on a Raspberry Pi, Raspbian Jessie.
Note: instead of readline I use the linenoise lib 
that is shipped with fossil. Also, I built a few
of the tools at the same time.
You may have to tweak this a little for your preferences and your platform.

cd ~/src/sqlite
test ! -z "$opt_p" && fossil pull --verbose
test ! -z "$opt_u" && fossil update trunk

OPTS=-DHAVE_LINENOISE
export CPPFLAGS="-DSQLITE_ENABLE_API_ARMOR \
-DSQLITE_ENABLE_COLUMN_METADATA \
-DSQLITE_ENABLE_DBPAGE_VTAB \
-DSQLITE_ENABLE_DBSTAT_VTAB \
-DSQLITE_ENABLE_DESERIALIZE \
-DSQLITE_ENABLE_EXPLAIN_COMMENTS \
-DSQLITE_ENABLE_FTS5 \
-DSQLITE_ENABLE_HIDDEN_COLUMNS \
-DSQLITE_ENABLE_JSON1 \
-DSQLITE_ENABLE_MEMSYS5 \
-DSQLITE_ENABLE_NORMALIZE \
-DSQLITE_ENABLE_OFFSET_SQL_FUNC \
-DSQLITE_ENABLE_PREUPDATE_HOOK \
-DSQLITE_ENABLE_RBU \
-DSQLITE_ENABLE_RTREE \
-DSQLITE_ENABLE_GEOPOLY \
-DSQLITE_ENABLE_STMT_SCANSTATUS \
-DSQLITE_ENABLE_STMTVTAB \
-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION \
-DSQLITE_ENABLE_UNLOCK_NOTIFY \
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
-DSQLITE_INTROSPECTION_PRAGMAS \
-DSQLITE_SOUNDEX \
-DSQLITE_USE_URI \
-DSQLITE_SECURE_DELETE \
-DSQLITE_DQS=0 \
-DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1"
./configure \
--prefix=/usr/local \
--libdir=/usr/lib/arm-linux-gnueabihf \
--enable-load-extension \
--enable-threadsafe \
--with-readline-lib=auto \
--with-pic \
--with-gnu-ld \
--enable-json1 \
--enable-fts5 \
--enable-rtree \
--enable-session \
--enable-update-limit \
--enable-geopoly \
--enable-tcl \
--disable-debug \
--disable-static \
&& make clean
&& make sqlite3.c \
&& make shell.c \
&& gcc $OPTS $CPPFLAGS \
-I ../fossil/src \
-L /usr/lib/arm-linux-gnueabihf -ltcl8.6 -lm -ldl -lz -lpthread \
shell.c ../fossil/src/linenoise.c sqlite3.c -o sqlite3 \
&& make sqlite3_analyzer \
&& make sqldiff \
&& make scrub \
&& make showdb \
&& make showwal \
&& make showshm \
&& make wordcount


-- 
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Niall O'Reilly]

On 9 Jan 2020, at 9:41, Xingwei Lin wrote:

> I always use ./configure && make to compile sqlite.
>
> ./configure can add some compile options, but I don't know how to add -Dxxx
> option in this compilation process.

I don't know (since I've never needed to build SQLite),
but can offer a hint which may help you discover for yourself
while waiting for real expert advice.

Just before sending, I did check my incoming mail, and didn't see
anything from a real expert yet.

After running ./configure (without && make), inspect the Makefile
and try to find how it runs the compiler, and how it passes options
to the compiler.  The man page for make is worth reading, but can
be confusing.

The Makefiles for other software packages that I have to build
from time to time are written following the convention that the
compiler is invoked using a macro CC, and that options are passed
to it using a macro CFLAGS.

If you find lines like
  $(CC) ... $(CFLAGS) ...
then you can set a value for CFLAGS by giving an argument to make:
  make CFLAGS=-Dxxx
If you see lines like
  CFLAGS = $(SOMEFLAGS) $(MOREFLAGS) $(EXTRAFLAGS)
then you'll need to be more careful, and work out which of the
macros on the right-hand side should be over-ridden by the
argument to make.

I hope this helps.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Xingwei Lin]

Thanks for reply.

I always use ./configure && make to compile sqlite.

./configure can add some compile options, but I don't know how to add -Dxxx
option in this compilation process.

On Thu, Jan 9, 2020 at 5:29 PM Simon Slavin  wrote:

> On 9 Jan 2020, at 6:47am, Xingwei Lin  wrote:
>
> > How can I pass -Dxxx compile option when I build sqlite? Such as, -
> > DSQLITE_ENABLE_INTERNAL_FUNCTIONS.
>
> Depends on which compiler you're using.  See examples for gcc on this page:
>
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Simon Slavin]

On 9 Jan 2020, at 6:47am, Xingwei Lin  wrote:

> How can I pass -Dxxx compile option when I build sqlite? Such as, -
> DSQLITE_ENABLE_INTERNAL_FUNCTIONS.

Depends on which compiler you're using.  See examples for gcc on this page:


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to pass -Dxxx compile option

[Xingwei Lin]

Hi,

How can I pass -Dxxx compile option when I build sqlite? Such as, -
DSQLITE_ENABLE_INTERNAL_FUNCTIONS.

-- 
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How create database, tables,indices...

[David Raymond]

To create a new database you just open the file name you want, and if it 
doesn't exist it will be created.

The actual new file creation is slightly delayed until it has to write 
something to the disk. There are a couple of options that can only be set at 
file creation time like page size, text encoding, file format, etc. So if you 
wanted any non-default settings for those you would set those first thing after 
opening it and before doing anything else, so that they are set _before_ the 
actual file itself is created.


-Original Message-
From: sqlite-users  On Behalf Of 
Andy
Sent: Tuesday, January 7, 2020 9:23 AM
To: SQLite mailing list 
Subject: Re: [sqlite] How create database, tables,indices...

I see: http://zetcode.com/db/sqlitec/
New database is simply open not existing file in path?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How create database, tables,indices...

[Simon Slavin]

On 7 Jan 2020, at 2:11pm, Andy  wrote:

> Which function are for crating new database, tables, indices, columns, fill 
> tables.

You do this with SQL commands, just like the commands you use to read data out 
of the database.



On 7 Jan 2020, at 2:23pm, Andy  wrote:

> New database is simply open not existing file in path?

Your path is for files you execute.  You do not execute a database file.  So if 
the database is not in your current directory you must specify the directory it 
is in.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How create database, tables,indices...

[Andy]

I see: http://zetcode.com/db/sqlitec/
New database is simply open not existing file in path?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How create database, tables,indices...

[Andy]

I am beginner in Sqlite programming. I already open and read tables.
I compiled sqlite3.c (8 MB) amalgamation file do DLL and call functions
from FreePascal.
Which function are for crating new database, tables, indices, columns, fill
tables.
I see
#define SQLITE_CREATE_INDEX  1   /* Index Name  Table Name
 */
#define SQLITE_CREATE_TABLE  2   /* Table Name  NULL
 */
but how function I must call?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type? – virtual table?

[Richard Damon]

On 12/14/19 1:55 PM, František Kučera wrote:
> Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
>> What he wants is different. He takes a basically arbitrary database
>> (user provided) and an arbitrary SQL statement (also user provided) and
>> he wants to determine what type a given column will present.
> Yes, I am looking for something that is usually called ResultSetMetaData.
>
> Currently I have solved it by adding a new command line option, so if the 
> user wants integer in e.g. "size" column, he must say it explicitly by: 
> --type-cast "size" integer.
>
> I can imagine a module, that will introduce optional static typing to SQLite. 
> It could provide a virtual table that will parse the query and return 
> expected result set metadata. So before executing the query, I could do 
> SELECT order, column_name, column_type FROM result_set_metadata WHERE sql = 
> 'SELECT mount_point, pass+1000 AS pass FROM fstab'; and it would return:
>
> result_set_metadata:
>  ╭─┬──┬──╮
>  │ order (integer) │ column_name (string) │ column_type (string) │
>  ├─┼──┼──┤
>  │   1 │ mount_point  │ string   │
>  │   2 │ pass │ integer  │
>  ╰─┴──┴──╯
> Record count: 2
>
> to do this, it would have to:
>
>  - parse the SQL (I am not sure whether internal SQLite parser could be used 
> for it or if I had to do it myself)
>
>  - check whether requested tables and columns exist and check the declared 
> types
>
>  - analyze the operations done in the SELECT clause (operators, function 
> calls or CASE) and derive resulting type
>
>  - return more generic type if types varies e.g. CASE that returns integer or 
> decimal numbers will result in a decimal type
>
>  - return an error if the CASE mixes e.g. integers and strings which does not 
> make much sense and is probably a mistake
>
> expectations and limitations:
>
>  - the table contains only values of declared types
>
>  - null is not perceived as a type itself but rather as a value of another 
> type (missing integer, missing string etc.)
>
> This is IMHO feasible, but would mean probably a lot of work. However it 
> might be beneficial for those who come from other relational databases and 
> prefer static typing rather than dynamic.
>
> i.e. something like optional type hints and checks in dynamic programming 
> languages.
>
> Franta

I think the biggest part of the issue is that you are thinking in
'Standard SQL' and then giving the user free reign in what SQL they are
going to use (so they COULD use some of the relaxation of limitations
provided by SQLite).

One big issue with your proposed module is that it (or at least programs
using it) are going to want to assume its assumptions, but there is not
an easy way to enforce them, as a program that doesn't use the module
could access the data base and break them.

-- 
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type? – virtual table?

[Simon Slavin]

On 14 Dec 2019, at 6:55pm, František Kučera  wrote:

> This is IMHO feasible, but would mean probably a lot of work. However it 
> might be beneficial for those who come from other relational databases and 
> prefer static typing rather than dynamic.

I don't think it's practical.  For instance, did you know that SQLite does not 
enforce string lengths ?  If you define a column as CHAR (100) SQLite 
completely ignores the length.  It'll store and return any string, no matter 
how long.

Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type? – virtual table?

[J Decker]

I just don't see the trouble

https://github.com/d3x0r/SACK/blob/master/src/SQLlib/sqlstub.c#L3613-L3680
for each row for each column
= sqlite3_column_type( collection->stmt, idx - 1 )
and then get the data according to the type... sqlite3_column_double  (for
instance)
 It's not very expensive to get the data type; sqlite3 will have already
prepared its internal variant structure...
if you don't know the type, then you don't know what type go get (yes, you
can, get everything as a string, but then why do you care about the type
anyway? :)  )



On Sat, Dec 14, 2019 at 10:55 AM František Kučera 
wrote:

> Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
> > What he wants is different. He takes a basically arbitrary database
> > (user provided) and an arbitrary SQL statement (also user provided) and
> > he wants to determine what type a given column will present.
>
> Yes, I am looking for something that is usually called ResultSetMetaData.
>
> Currently I have solved it by adding a new command line option, so if the
> user wants integer in e.g. "size" column, he must say it explicitly by:
> --type-cast "size" integer.
>
> I can imagine a module, that will introduce optional static typing to
> SQLite. It could provide a virtual table that will parse the query and
> return expected result set metadata. So before executing the query, I could
> do SELECT order, column_name, column_type FROM result_set_metadata WHERE
> sql = 'SELECT mount_point, pass+1000 AS pass FROM fstab'; and it would
> return:
>
> result_set_metadata:
>  ╭─┬──┬──╮
>  │ order (integer) │ column_name (string) │ column_type (string) │
>  ├─┼──┼──┤
>  │   1 │ mount_point  │ string   │
>  │   2 │ pass │ integer  │
>  ╰─┴──┴──╯
> Record count: 2
>
> to do this, it would have to:
>
>  - parse the SQL (I am not sure whether internal SQLite parser could be
> used for it or if I had to do it myself)
>
>  - check whether requested tables and columns exist and check the declared
> types
>
>  - analyze the operations done in the SELECT clause (operators, function
> calls or CASE) and derive resulting type
>
>  - return more generic type if types varies e.g. CASE that returns integer
> or decimal numbers will result in a decimal type
>
>  - return an error if the CASE mixes e.g. integers and strings which does
> not make much sense and is probably a mistake
>
> expectations and limitations:
>
>  - the table contains only values of declared types
>
>  - null is not perceived as a type itself but rather as a value of another
> type (missing integer, missing string etc.)
>
> This is IMHO feasible, but would mean probably a lot of work. However it
> might be beneficial for those who come from other relational databases and
> prefer static typing rather than dynamic.
>
> i.e. something like optional type hints and checks in dynamic programming
> languages.
>
> Franta
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type? – virtual table?

[František Kučera]

Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
> What he wants is different. He takes a basically arbitrary database
> (user provided) and an arbitrary SQL statement (also user provided) and
> he wants to determine what type a given column will present.

Yes, I am looking for something that is usually called ResultSetMetaData.

Currently I have solved it by adding a new command line option, so if the user 
wants integer in e.g. "size" column, he must say it explicitly by: --type-cast 
"size" integer.

I can imagine a module, that will introduce optional static typing to SQLite. 
It could provide a virtual table that will parse the query and return expected 
result set metadata. So before executing the query, I could do SELECT order, 
column_name, column_type FROM result_set_metadata WHERE sql = 'SELECT 
mount_point, pass+1000 AS pass FROM fstab'; and it would return:

result_set_metadata:
 ╭─┬──┬──╮
 │ order (integer) │ column_name (string) │ column_type (string) │
 ├─┼──┼──┤
 │   1 │ mount_point  │ string   │
 │   2 │ pass │ integer  │
 ╰─┴──┴──╯
Record count: 2

to do this, it would have to:

 - parse the SQL (I am not sure whether internal SQLite parser could be used 
for it or if I had to do it myself)

 - check whether requested tables and columns exist and check the declared types

 - analyze the operations done in the SELECT clause (operators, function calls 
or CASE) and derive resulting type

 - return more generic type if types varies e.g. CASE that returns integer or 
decimal numbers will result in a decimal type

 - return an error if the CASE mixes e.g. integers and strings which does not 
make much sense and is probably a mistake

expectations and limitations:

 - the table contains only values of declared types

 - null is not perceived as a type itself but rather as a value of another type 
(missing integer, missing string etc.)

This is IMHO feasible, but would mean probably a lot of work. However it might 
be beneficial for those who come from other relational databases and prefer 
static typing rather than dynamic.

i.e. something like optional type hints and checks in dynamic programming 
languages.

Franta

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type? – explicit cast

[Richard Damon]

On 12/14/19 11:12 AM, Simon Slavin wrote:
> On 14 Dec 2019, at 10:46am, František Kučera  wrote:
>
>> SELECT cast(dump+100 AS integer) FROM fstab;
>>
>> the sqlite3_column_decltype() still does not return the integer type.
>>
>> Would it be possible to modify this function or add a new one, to tell the 
>> correct type at least if there is an explicit cast like this in given query?
> It works fine for me:
>
> SQLite version 3.28.0 2019-04-15 14:49:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE t (a TEXT, b INTEGER, c REAL);
> sqlite> INSERT INTO t VALUES ('123', 123, 123.4);
> sqlite> INSERT INTO t VALUES (CAST ('456' AS INTEGER), CAST (456 AS INTEGER), 
> CAST (456.7 AS INTEGER));
> sqlite> SELECT a,typeof(a),b,typeof(b),c,typeof(c) FROM t;
> 123|text|123|integer|123.4|real
> 456|text|456|integer|456.0|real
> sqlite> SELECT cast(a AS INTEGER),typeof(cast(a AS INTEGER)),cast(b AS 
> INTEGER),typeof(cast(b AS INTEGER)),cast(c AS INTEGER),typeof(cast(c AS 
> INTEGER)) FROM t;
> 123|integer|123|integer|123|integer
> 456|integer|456|integer|456|integer
>
> When you do your CAST when you store, and the column type is compatible with 
> the input value, the column type is what you declared the column type to be.
>
> But whether you do your cast() when you recall, the column type is always 
> INTEGER.  Which is what you want.

What he wants is different. He takes a basically arbitrary database
(user provided) and an arbitrary SQL statement (also user provided) and
he wants to determine what type a given column will present.

He is willing to assume that columns hold their declared data type
(otherwise the problem is truly impossible), and if the results goes
back to just a column, the answer is simple, the declared type of the
column (even though in many cases, the answer could also be NULL). The
problem is that there is no built in algebra to deduce what type an
expression will produce (assuming you know the types of the inputs), in
part because in SQLite you can't 'know' the type that the input would
be, and in part because sometimes result type will depend on the values
received.

The only answers I can think of are

1) retrieve all the results, taking whatever type SQLite says that value
is, and then process all the results for a give column to figure out
what (and if) that column produces (if because it might be inconsistent,
so you need to respond 'mixed' and maybe handle mixed INTEGER and REAL
some way).

2) Parse the expression yourself and determine the types (and know ahead
of time if there are problem columns). There might be ways to use some
internals of SQLite to help, but SQLite isn't going to do the job
itself, as it has no need for that answer (as it is based on assumptions
that SQLite doesn't make on the data).

-- 
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type? – explicit cast

[Simon Slavin]

On 14 Dec 2019, at 10:46am, František Kučera  wrote:

> SELECT cast(dump+100 AS integer) FROM fstab;
> 
> the sqlite3_column_decltype() still does not return the integer type.
> 
> Would it be possible to modify this function or add a new one, to tell the 
> correct type at least if there is an explicit cast like this in given query?

It works fine for me:

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t (a TEXT, b INTEGER, c REAL);
sqlite> INSERT INTO t VALUES ('123', 123, 123.4);
sqlite> INSERT INTO t VALUES (CAST ('456' AS INTEGER), CAST (456 AS INTEGER), 
CAST (456.7 AS INTEGER));
sqlite> SELECT a,typeof(a),b,typeof(b),c,typeof(c) FROM t;
123|text|123|integer|123.4|real
456|text|456|integer|456.0|real
sqlite> SELECT cast(a AS INTEGER),typeof(cast(a AS INTEGER)),cast(b AS 
INTEGER),typeof(cast(b AS INTEGER)),cast(c AS INTEGER),typeof(cast(c AS 
INTEGER)) FROM t;
123|integer|123|integer|123|integer
456|integer|456|integer|456|integer

When you do your CAST when you store, and the column type is compatible with 
the input value, the column type is what you declared the column type to be.

But whether you do your cast() when you recall, the column type is always 
INTEGER.  Which is what you want.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[Richard Damon]

On 12/14/19 5:22 AM, František Kučera wrote:
> Dne 14. 12. 19 v 9:36 Darren Duncan napsal(a):
>> With respect to SQLite, every column is the union of: Null, every Integer, 
>> every Float, every Text, every Blob.
> OK, we can close this thread with that it is currently impossible to track 
> the declared column types through relational operations.
>
It is currently impossible because it is actually impossible to know the
result type of some expressions except by knowing the values and
computing it. Some arithmetic operations have thier result type varied
based on the value of the result (overflow goes to REAL), a CASE
expression can return different types based on the value of an
expression, and then you have the fact that NULL has a unique type.

A common issue with computer systems is that when you have something
that looks easy to do for a lot of simple cases, but there actually are
some cases that are hard or impossible to determine, then those hard
cases make it hard to handle the general problem.

Thus even without using SQLite's flexibility it types stored in columns,
you can't predetermine the type of some expressions.

-- 

Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[Jean-Christophe Deschamps]

dump+100 (string), pass+1000 (string)

the type information disappears and former integers becomes mere strings


There must be something else going on here:

Z:> sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table T (N integer);
sqlite> insert into T values (1), (2), (3);
sqlite> select N, N+100, typeof(N+100), N+1000, typeof(N+1000) from T;
1|101|integer|1001|integer
2|102|integer|1002|integer
3|103|integer|1003|integer
sqlite> .q

JcD 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type? – explicit cast

[František Kučera]

P.S. Even if I do:

SELECT cast(dump+100 AS integer) FROM fstab;

the sqlite3_column_decltype() still does not return the integer type.

Would it be possible to modify this function or add a new one, to tell the 
correct type at least if there is an explicit cast like this in given query?

If I do: SELECT cast("xxx" AS integer); it returns 0. So I hope that it is not 
too immodest to expect that all values of such column will have the same type 
(or be null).

Franta


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[František Kučera]

Dne 14. 12. 19 v 9:36 Darren Duncan napsal(a):
> With respect to SQLite, every column is the union of: Null, every Integer, 
> every Float, every Text, every Blob.

OK, we can close this thread with that it is currently impossible to track the 
declared column types through relational operations.

Just to explain what my generic program does: it is typically used as a filter 
and works on the fly. The user feeds a stream of relations (tables) into it, 
the program does some transformations (execute SELECTs in this case) and sends 
a stream of another relations on the standard output.

The input relations have declared attribute types (currently boolean, integer 
and string) and it does not allow mixing various types in the same column.

For example the user can do this:

relpipe-in-fstab | relpipe-tr-sql --relation "x" "SELECT * FROM fstab WHERE 
type = ?" --parameter "ext4" | relpipe-out-tabular
x:
 
╭─┬──┬──┬───┬───┬┬╮
 │ scheme (string) │ device  (string) │ mount_point 
(string) │ type (string) │ options  (string) │ dump 
(integer) │ pass (integer) │
 
├─┼──┼──┼───┼───┼┼┤
 │ UUID    │ 29758270-fd25-4a6c-a7bb-9a18302816af │ /   
 │ ext4  │ relatime,user_xattr,errors=remount-ro │  0 │ 
 1 │
 │ │ /dev/sde │ /mnt/data   
 │ ext4  │ relatime,user_xattr,errors=remount-ro │  0 │ 
 2 │
 
╰─┴──┴──┴───┴───┴┴╯
Record count: 2

If you look at the header, the integer types of the "dump" and "pass" columns 
are preserved (I get the type from the sqlite3_column_decltype() function).

But if the user just slightly modifies the query:

relpipe-in-fstab | relpipe-tr-sql --relation x "SELECT mount_point, dump+100, 
pass+1000 FROM fstab WHERE type = ?" --parameter "ext4" | relpipe-out-tabular
x:
 ╭──┬───┬╮
 │ mount_point (string) │ dump+100 (string) │ pass+1000 (string) │
 ├──┼───┼┤
 │ /    │ 100   │ 1001   │
 │ /mnt/data    │ 100   │ 1002   │
 ╰──┴───┴╯
Record count: 2

the type information disappears and former integers becomes mere strings (a 
fallback generic type here). From the point of view of a developer who knows 
SQLite internals, this is obvious. But from the user's point of view, this is 
quite weird and unexpected behavior. Users expect that a mathematical operation 
will return the original (integer) type or at least some other numeric type – 
but not a text string. I would have to add something like --type-cast "dump" 
"integer" option to explicitly specify the types and convert the strings back 
to the integers. But I was wondering whether there is a more user-friendly 
automatic way.

The source codes and more information are available here: 
.

>
> With respect to a generic application programming language, every column is 
> of the most generic type, such as "Object" or "Any" for examples.
>
> Now, if you want to be more precise, you can say that the type of every 
> column is the union of all values currently in it.  This means that the type 
> of an empty column is the empty type consisting of zero values, which is a 
> subset of all other types just as the universal type is the superset of all 
> other types.
>
> Generally speaking, you want to support union types.

In my software, the most generic type is the text string. e.g. 123 will become 
"123", true will become "true" or byte array (when implemented) will become a 
hexadecimal text string. And it is currently also used as a fallback – if I can 
not get a better type from SQLite, the attribute is returned as a string. So 
the text string serves as that union type for me.

Franta

relpipe-in-fstab | relpipe-tr-sql --relation x "SELECT * FROM fstab WHERE type 
= ?" --parameter "ext4" | relpipe-out-tabular 
x:
 
╭─┬──┬──┬───┬───┬┬╮
 │ scheme (string) │ device  (string) │ mount_point 
(string) │ type (string) │ options  (string) │ dump 
(integer) │ pass (integer) │
 

Re: [sqlite] How to determine the column type?

[Darren Duncan]

František,

Having done this myself, I will tell you that:

The only effective design for your "generic software that should work with any 
database model" is that every column is the universal type, the union of all 
other types; the type is the set of all representable values.


With respect to SQLite, every column is the union of: Null, every Integer, every 
Float, every Text, every Blob.


With respect to a generic application programming language, every column is of 
the most generic type, such as "Object" or "Any" for examples.


Now, if you want to be more precise, you can say that the type of every column 
is the union of all values currently in it.  This means that the type of an 
empty column is the empty type consisting of zero values, which is a subset of 
all other types just as the universal type is the superset of all other types.


Generally speaking, you want to support union types.

Do you have any questions to help you understand this?

-- Darren Duncan

On 2019-12-13 2:49 p.m., František Kučera wrote:

I know that SQLite uses dynamic types, so it is not easy… But what is the best 
way to determine the column type of a result set?

The sqlite3_column_decltype() works only if I select directly a column, but not 
when I do some other operations (call function, increment etc.).

The sqlite3_column_type() works for while iterating over particular rows. I can 
fetch the first row and get type here (expecting that all values in that column 
will have same type), but the problem is a) if the first value is NULL or b) if 
the result set is empty.

If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the x will be 
numeric? Or if I have "SELECT 1+1 AS x"?

I am writing a generic software that should work with any database model 
(tables are defined by the user) and I need to know the types, so I can 
properly present the results. Currently I use sqlite3_column_decltype() and 
will add options so the user could explicitly specify types of particular 
columns, but I am looking for a better way…


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[Keith Medcalf]

On Friday, 13 December, 2019 18:35, Richard Damon  
wrote:

>One big thing to watch out is that columns of NUMERIC type can easily
>return values of either INTEGER or REAL type. Your single type
>expectation is easily broken here. I also don't know if
>9223372036854775807 (the biggest integer value) from an INTEGER field +
>1 gives a REAL result, or some incorrect INTEGER value.

The result is 9223372036854775807.999 which is the closest representable 
double precision floating point number.  Of course, you get the same answer 
unless you add 1025 instead of 1, in which case you get the next representable 
floating point number which is 9223372036854777855.999.  The arithmetic 
functions carry out the operations using the "type" of their arguments -- and 
if one of them is a float, then the other is converted to a float.  If both are 
integers and an overflow (or underflow) occurs, then both arguments are 
converted to floating point and the operation is carried out in floating point.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[Richard Damon]

On 12/13/19 7:16 PM, František Kučera wrote:
> Dne 14. 12. 19 v 0:09 Keith Medcalf napsal(a):
>> On Friday, 13 December, 2019 15:49, František Kučera 
>>  wrote:
>>
>>> I know that SQLite uses dynamic types, so it is not easy… But what is the
>>> best way to determine the column type of a result set?
>> Result sets do not have "column types".  Each result value (the intersection 
>> of row and column) has a type associated with it.
> I know that SQLite is that flexible, but I am using just a subset of its 
> features and looking for a way how to propagate the types through the queries 
> and result sets.
>
>> Your expectation would be incorrect. You have to call sqlite3_column_type 
>> for each column of each row in order to determine the type of data contained 
>> there. Every intersection of row and column can contain data of any type. 
>> Think of an Excel (or VisiCalc) spreadsheet. Just because you labeled a 
>> column as containing only integers does not mean that someone did not put 
>> something else there.
> Yes, I can do:
>
> sqlite> create table t (i integer, f float, s text);
> sqlite> insert into t values (1, 1.0, 'abc');
> sqlite> insert into t values (1.5, 1.0, 'abc');
> sqlite> insert into t values (1.5, 'xxx', 'abc');
> sqlite> select * from t;
> 1|1.0|abc
> 1.5|1.0|abc
> 1.5|xxx|abc
> sqlite> select typeof(i), typeof(f), typeof(s) from t;
> integer|real|text
> real|real|text
> real|text|text
>
> but this would be unsupported scenario and the user will expect, that if he 
> declares an integer column, he could put only integers into it. If the types 
> become into such inconsistent state, then my software could crash, throw 
> exception, refuse working… it is OK (user can recover his data directly 
> through SQLite, but it is not expected that this would happen).
>
> In case of my software I can really expect that all values in a column will 
> have the same type or be NULL (and everything else means error).
>
> So if I have a table with an integer column and I do e.g. a +1 operation on 
> it, the database has theoretically everything it needs to say that the 
> resulting type will be also integer. Or can the "+" operation result anything 
> than number (or null or error)?
>
> Would not it be useful to have optional function to determine the types 
> before executing the query? (yes, it would require that the columns contains 
> only values of declared type… but it is quite obvious and who call such 
> function will expect such constraint… and it might also require adding some 
> optional metadata to existing functions – hint what types they return…).
>
> Franta

As I said in my reply, you can keep asking for the type of columns that
have returned NULL previously to get the type.

Part of the issue is that you ARE allowing multiple types (since NULL is
its own type), so you need to be prepared for differing types.

One big thing to watch out is that columns of NUMERIC type can easily
return values of either INTEGER or REAL type. Your single type
expectation is easily broken here. I also don't know if
9223372036854775807 (the biggest integer value) from an INTEGER field +
1 gives a REAL result, or some incorrect INTEGER value.

-- 
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[Simon Slavin]

On 14 Dec 2019, at 12:16am, František Kučera  wrote:

> In case of my software I can really expect that all values in a column will 
> have the same type or be NULL (and everything else means error).

In that case, execute your query and use sqlite3_column_type() on each column 
of the first row returned.

Alternatively, execute your query with " LIMIT 1" added to the end, use the 
column types that one gives, then execute your real query.

> Would not it be useful to have optional function to determine the types 
> before executing the query? (yes, it would require that the columns contains 
> only values of declared type…

The problem with that "optional function" is that the requirement you listed is 
not a requirement of SQLite.

You can read the types of every column in a table using a PRAGMA:



Of course, this does not help when some columns returned by your query are not 
table columns.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[František Kučera]

Dne 14. 12. 19 v 0:09 Keith Medcalf napsal(a):
> On Friday, 13 December, 2019 15:49, František Kučera  
> wrote:
>
>> I know that SQLite uses dynamic types, so it is not easy… But what is the
>> best way to determine the column type of a result set?
> Result sets do not have "column types".  Each result value (the intersection 
> of row and column) has a type associated with it.

I know that SQLite is that flexible, but I am using just a subset of its 
features and looking for a way how to propagate the types through the queries 
and result sets.

> Your expectation would be incorrect. You have to call sqlite3_column_type for 
> each column of each row in order to determine the type of data contained 
> there. Every intersection of row and column can contain data of any type. 
> Think of an Excel (or VisiCalc) spreadsheet. Just because you labeled a 
> column as containing only integers does not mean that someone did not put 
> something else there.

Yes, I can do:

sqlite> create table t (i integer, f float, s text);
sqlite> insert into t values (1, 1.0, 'abc');
sqlite> insert into t values (1.5, 1.0, 'abc');
sqlite> insert into t values (1.5, 'xxx', 'abc');
sqlite> select * from t;
1|1.0|abc
1.5|1.0|abc
1.5|xxx|abc
sqlite> select typeof(i), typeof(f), typeof(s) from t;
integer|real|text
real|real|text
real|text|text

but this would be unsupported scenario and the user will expect, that if he 
declares an integer column, he could put only integers into it. If the types 
become into such inconsistent state, then my software could crash, throw 
exception, refuse working… it is OK (user can recover his data directly through 
SQLite, but it is not expected that this would happen).

In case of my software I can really expect that all values in a column will 
have the same type or be NULL (and everything else means error).

So if I have a table with an integer column and I do e.g. a +1 operation on it, 
the database has theoretically everything it needs to say that the resulting 
type will be also integer. Or can the "+" operation result anything than number 
(or null or error)?

Would not it be useful to have optional function to determine the types before 
executing the query? (yes, it would require that the columns contains only 
values of declared type… but it is quite obvious and who call such function 
will expect such constraint… and it might also require adding some optional 
metadata to existing functions – hint what types they return…).

Franta


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[Keith Medcalf]

On Friday, 13 December, 2019 15:49, František Kučera  
wrote:

>I know that SQLite uses dynamic types, so it is not easy… But what is the
>best way to determine the column type of a result set?

Result sets do not have "column types".  Each result value (the intersection of 
row and column) has a type associated with it.

>The sqlite3_column_decltype() works only if I select directly a column,
>but not when I do some other operations (call function, increment etc.).

See above comment.  The declared type of a column has no bearing on what type 
of data is stored in the column.

>The sqlite3_column_type() works for while iterating over particular rows.
>I can fetch the first row and get type here (expecting that all values in
>that column will have same type), but the problem is a) if the first
>value is NULL or b) if the result set is empty.

Your expectation would be incorrect.  You have to call sqlite3_column_type for 
each column of each row in order to determine the type of data contained there. 
 Every intersection of row and column can contain data of any type.  Think of 
an Excel (or VisiCalc) spreadsheet.  Just because you labeled a column as 
containing only integers does not mean that someone did not put something else 
there.  You only know what type of data is contained in a cell by looking and 
asking using the sqlite3_column_type interface.  Every time.

Although I suppose you could write a check constraint for each column to ensure 
that only "approved" datatypes are stored in a column:

sqlite> create table x(x integer check (typeof(x) in ('integer', 'null')));
sqlite> insert into x values (null);
sqlite> insert into x values (1);
sqlite> insert into x values (1.0);
Error: CHECK constraint failed: x
sqlite> insert into x values ('1');
Error: CHECK constraint failed: x
sqlite> insert into x values (X'41');
Error: CHECK constraint failed: x

but most databases/tables will not have done that, so you need to check for 
EACH value.

>If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the
>x will be numeric? Or if I have "SELECT 1+1 AS x"?

>I am writing a generic software that should work with any database model
>(tables are defined by the user) and I need to know the types, so I can
>properly present the results. Currently I use sqlite3_column_decltype()
>and will add options so the user could explicitly specify types of
>particular columns, but I am looking for a better way…

You need to call sqlite3_column_type on each column of each row to determine 
what type of data is contained therein.

>Or will SQLite4 work differently (for me better) with types?

There is no SQLite4, it was cancelled.  SQLite3 is strongly typed, just those 
types are dynamic.

https://sqlite.org/datatype3.html

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[Richard Damon]

On 12/13/19 5:49 PM, František Kučera wrote:
> Hello,
>
> I know that SQLite uses dynamic types, so it is not easy… But what is the 
> best way to determine the column type of a result set?
>
> The sqlite3_column_decltype() works only if I select directly a column, but 
> not when I do some other operations (call function, increment etc.).
>
> The sqlite3_column_type() works for while iterating over particular rows. I 
> can fetch the first row and get type here (expecting that all values in that 
> column will have same type), but the problem is a) if the first value is NULL 
> or b) if the result set is empty.
>
> If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the x 
> will be numeric? Or if I have "SELECT 1+1 AS x"?
>
> I am writing a generic software that should work with any database model 
> (tables are defined by the user) and I need to know the types, so I can 
> properly present the results. Currently I use sqlite3_column_decltype() and 
> will add options so the user could explicitly specify types of particular 
> columns, but I am looking for a better way…
>
> Or will SQLite4 work differently (for me better) with types?
>
> Thanks,
>
> Franta
>
My guess is that your statement '(expecting that all values in that
column will have same type)' is where your problems arise. The SQLite
model doesn't assume that. A column in a result set doesn't necessarily
have *A* type. What do you want you software to do if the data in the
database has differing types in a given column?

If you are going to enforce a uniform type (excepting allowing NULL as a
value), then you could check your recorded column type for each column
for each row, and if you have it currently recorded as NULL, check the
type in this row and update if needed, otherwise use the recorded type.
Note that you need to be prepared for different queries of the same set
of columns (or the same query at different times) may give you changing
types for a given column at different times.

-- 
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to determine the column type?

[František Kučera]

Hello,

I know that SQLite uses dynamic types, so it is not easy… But what is the best 
way to determine the column type of a result set?

The sqlite3_column_decltype() works only if I select directly a column, but not 
when I do some other operations (call function, increment etc.).

The sqlite3_column_type() works for while iterating over particular rows. I can 
fetch the first row and get type here (expecting that all values in that column 
will have same type), but the problem is a) if the first value is NULL or b) if 
the result set is empty.

If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the x 
will be numeric? Or if I have "SELECT 1+1 AS x"?

I am writing a generic software that should work with any database model 
(tables are defined by the user) and I need to know the types, so I can 
properly present the results. Currently I use sqlite3_column_decltype() and 
will add options so the user could explicitly specify types of particular 
columns, but I am looking for a better way…

Or will SQLite4 work differently (for me better) with types?

Thanks,

Franta


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to update plural rows from one table to another.

[Keith Medcalf]

On Friday, 15 November, 2019 15:22, Gan Uesli Starling  wrote:

>In the following update query, I had expected for the integer values
>"rowid" from the table "info" to project copies of themselves singly and
>separately into the integer cells "info_id" of table "qso", row-by-row,
>where the timestamps "t_from" of each are matching.

>UPDATE qso SET info_id = (
> SELECT info.rowid FROM info, qso WHERE info.t_from = qso.t_from
>)
>WHERE qso.t_from = info.t_from;

That statement is in error and cannot possibly compile.  Column "info.t_from" 
in the outer update does not exist.  The outer update may only reference 
columns contained in the table being updated, qso.  Furthermore, the 
"subselect" returns multiple rows and is not correlated with the data you are 
updating.

>They do not. Instead what happens, is that the integer value "rowid"
>from table "info" of first row in which the timestamps "t_from" of table
>"qso" and table "info" are matching goes multiply into all cells
>"info_id" of table "qso".

As I said, I think you are mistaken.  The above statement does nothing at all 
since it is an error and cannot execute.

>How can I instead get what I want?

UPDATE qso
   SET info_id = (SELECT rowid
FROM info
   WHERE t_from == qso.t_from)
 WHERE EXISTS (SELECT 1
 FROM info
WHERE t_from == qso.t_from);

The where exists prevents setting info_id to null where no match is found in 
the correlated subquery.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to update plural rows from one table to another.

[Gan Uesli Starling]

In the following update query, I had expected for the integer values 
"rowid" from the table "info" to project copies of themselves singly and 
separately into the integer cells "info_id" of table "qso", row-by-row, 
where the timestamps "t_from" of each are matching.


UPDATE qso SET info_id = (
    SELECT info.rowid FROM info, qso WHERE info.t_from = qso.t_from
)
WHERE qso.t_from = info.t_from;

They do not. Instead what happens, is that the integer value "rowid" 
from table "info" of first row in which the timestamps "t_from" of table 
"qso" and table "info" are matching goes multiply into all cells 
"info_id" of table "qso".


How can I instead get what I want?

TIA,

Gan Starling, KY8D

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use CASE statement to SUM() some numbers

[Jim Morris]

Maybe something like:

CREATE VIEW "Sum of Expenses Between two Dates" AS 
SELECT Date,
sum( CASE
  WHEN Date BETWEEN date('now', '-1 months') AND date('2019-11-04', 
'-1 days') THEN Expense
  ELSE 0
  END) as 'Sum of Expenses:'
  FROM Expenses;

On 11/15/2019 12:22 PM, David Raymond wrote:
> So why do you need a case? What will not work with the simple:
>
> select sum(Expense)
> from Expenses
> where Date between date('now', '-1 months') and date('2019-11-04', '-1 days');
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use CASE statement to SUM() some numbers

[David Raymond]

So why do you need a case? What will not work with the simple:

select sum(Expense)
from Expenses
where Date between date('now', '-1 months') and date('2019-11-04', '-1 days');

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use CASE statement to SUM() some numbers

[Csanyi Pal]

Hi,

I have a simple database, the 'Incomes_Expenses.db' on my system.

It's SQL is here:

--
-- File generated with SQLiteStudio v3.2.1 on P nov. 15 20:39:18 2019
--
-- Text encoding used: UTF-8
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: Expenses
CREATE TABLE Expenses (Id INTEGER PRIMARY KEY, Date DATE, Expense REAL, 
Currency TEXT);
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (1, 
'2019-10-15', 421.35, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (2, 
'2019-10-18', 560.5, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (3, 
'2019-10-19', 37.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (4, 
'2019-10-20', 632.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (5, 
'2019-10-21', 124.5, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (6, 
'2019-10-23', 1200.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (7, 
'2019-10-31', 278.43, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (8, 
'2019-11-01', 250.3, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (9, 
'2019-11-02', 429.72, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (10, 
'2019-11-03', 310.11, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (11, 
'2019-11-04', 197.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (12, 
'2019-11-05', 257.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (13, 
'2019-11-06', 4230.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (14, 
'2019-11-08', 452.38, '$');


-- Table: Incomes
CREATE TABLE Incomes (Id INTEGER PRIMARY KEY, Date DATE, Income REAL, 
Currency TEXT);
INSERT INTO Incomes (Id, Date, Income, Currency) VALUES (1, 
'2019-11-04', 1573.0, 'USD');


-- View: Sum of Expenses Beteen two Dates
CREATE VIEW "Sum of Expenses Beteen two Dates" AS SELECT Date,
   CASE
 WHEN Date BETWEEN date('now', '-1 months') AND date('2019-11-04', 
'-1 days') THEN SUM(Expense)

 ELSE 'Sorry, it is not between the two dates.'
 END as 'Sum of Expenses:'
 FROM Expenses;

COMMIT TRANSACTION;
PRAGMA foreign_keys = on;



I wish to SUM some Expenses which meets some condition.
The condition follows.
The Date of an Expense must be between two dates.

The CASE statement should add to the SUM the value of an Expense if and 
only if the Date of an Expense is BETWEEN two dates.


The two dates are like this:
the 1. Date is in the previous month, and
the 2. Date is in the Incomes Table and is in this month minus 1 day.

Eg. 1. Date is say date('now', '-1 months')
and 2. Date is say date('2019-11-04').

So I tried with this Query to get the SUM of Expenses between 1. and 2. 
Date like this:



SELECT Date,
   CASE
 WHEN Date BETWEEN date('now', '-1 months') AND date('2019-11-04', 
'-1 days') THEN 'It is between the two dates.'

 ELSE 'Sorry, it is not between the two dates.'
 END as 'Information:'
 FROM Expenses


This shows only wether is a Date between two dates or is not.

The folloing code shows some results, but the resulted Sum is not valid:


sqlite3 Incomes_Expenses.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> SELECT * FROM 'Sum of Expenses Beteen two Dates';
2019-10-14|Sorry, it is not between the two dates.
2019-10-15|10132.7
2019-10-18|10132.7
2019-10-19|10132.7
2019-10-20|10132.7
2019-10-21|10132.7
2019-10-23|10132.7
2019-10-31|10132.7
2019-11-01|10132.7
2019-11-02|10132.7
2019-11-03|10132.7
2019-11-04|Sorry, it is not between the two dates.
2019-11-05|Sorry, it is not between the two dates.
2019-11-06|Sorry, it is not between the two dates.
2019-11-08|Sorry, it is not between the two dates.
sqlite>


It should gives this result: 4440,91 $ but not the 10132.7 $.


Can one uses the CASE statement to get a SUM of Expenses which Dates are 
between two given dates?


--
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users