Re: [sqlite] Is it necessary to encode() for file names in sqlar format?

[Keith Medcalf]

sys.argv is a list of unicode text strings.  There is no need to specifically 
encode or decode it so long as sys.getdefaultencoding() returns 'utf-8'.  If 
your version of Python is so old that it returns something else then you need 
to modify site.py and have it set the default encoding to 'utf-8' otherwise you 
may end up with MBCS or some other invalid text encoding in your database text 
fields.

-- 
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 Peng Yu
>Sent: Wednesday, 29 January, 2020 22:57
>To: SQLite mailing list 
>Subject: [sqlite] Is it necessary to encode() for file names in sqlar
>format?
>
>I use the following python3 code to create sqlar file. Is it necessary
>to sys.argv[2].encode('utf-8') in the line of execute()? In other
>word, does the native sqlar tools inteprete the name column as an
>encoded value or a non-encode value? Thanks.
>
>import sqlite3
>conn=sqlite3.connect(sys.argv[1])
>c=conn.cursor()
>c.execute('''
>CREATE TABLE IF NOT EXISTS sqlar(
>name TEXT PRIMARY KEY
>, mode INT
>, mtime INT
>, sz INT
>, data BLOB)
>''')
>
>import zlib
>data = sys.stdin.buffer.read()
>c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', [sys.argv[2], 0,
>0, len(data), zlib.compress(data)])
>conn.commit()
>
>--
>Regards,
>Peng
>___
>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] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

[Keith Medcalf]

On Wednesday, 29 January, 2020 22:45, Peng Yu  wrote:

>In python sqlite3 program, if I call .execute() multiple times then
>call .commit(). Does it ensure that all the sqlite3 commands specified
>by execute()'s either all take effect or none effect?

Mayhaps yes, mayhaps no.  .commit() is merely syntactic sugar for 
.execute('COMMIT')

Basically, the method does the following:

if (sqlite3_get_autocommit(connection) == 0) {
   sqlite3_stmt* stmt = 0;
   sqlite3_prepare(connection, "COMMIT", -1, &stmt, NULL);
   if (stmt) {
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);
   }
}

That is, if there is a transaction in progress on the connection, it does a 
commit.  Mutatis mutandis for .rollback() doing a 'ROLLBACK' rather than commit.

It makes sure that all changes made in the current transaction are committed to 
the database.  If you are using "magical mode" then hopefully a transaction was 
started when you updated something however this is not guaranteed (such is the 
nature of magic).  The "magical mode" of the sqlite3 wrapper decides when to 
BEGIN and COMMIT transactions on its own.  Sometimes the magician is a little 
daffy though and gets it wrong so it may begin/commit/rollback whenever the 
magician feels like it, which may or may not be when you want.  

If you want to ensure that transactions BEGIN and COMMIT/ROLLBACK when *you* 
want them to then use explicit .execute('BEGIN') (or .execute('BEGIN 
IMMEDIATE') to start an immediate transaction) command to start a transaction 
on a connection opened with isolation_level=None.  You can still use .commit() 
for .execute('COMMIT') and .rollback() for .execute('ROLLBAC') if you want.  
The connection has a property in_transaction that lets you test whether a 
transaction is in progress (True) or the database is in autocommit (False) mode.

>In other words, if any error occurs while running the sqlite3 commands
>specified in execute(), what happens?

That depends on the nature of the error.  If you put in a bad SQL statement 
(got a syntax error) then that statement did nothing.  Otherwise it depends on 
the conflict resolution method in effect for the statement causing the error, 
which is usually ABORT (by default) and it just ABORTs the current statement.

>Also, is .executemany() the same as calling .execute() many times?

Yes.  x.executemany(sql, stuff) is syntactic sugar for

for e in stuff:
x.execute(sql, e):

-- 
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] Is it necessary to encode() for file names in sqlar format?

[Peng Yu]

I use the following python3 code to create sqlar file. Is it necessary
to sys.argv[2].encode('utf-8') in the line of execute()? In other
word, does the native sqlar tools inteprete the name column as an
encoded value or a non-encode value? Thanks.

import sqlite3
conn=sqlite3.connect(sys.argv[1])
c=conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS sqlar(
name TEXT PRIMARY KEY
, mode INT
, mtime INT
, sz INT
, data BLOB)
''')

import zlib
data = sys.stdin.buffer.read()
c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', [sys.argv[2], 0,
0, len(data), zlib.compress(data)])
conn.commit()

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

[sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

[Peng Yu]

In python sqlite3 program, if I call .execute() multiple times then
call .commit(). Does it ensure that all the sqlite3 commands specified
by execute()'s either all take effect or none effect?

In other words, if any error occurs while running the sqlite3 commands
specified in execute(), what happens?

Also, is .executemany() the same as calling .execute() many times?

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

Re: [sqlite] Possible caching issue between connections with specific inserts

[Joe Mistachkin]

Alexey Podogov wrote:
>
> Also, the issue can't be reproduced if:
> - Cache size is set to 0; or
> - Journal mode is WAL; or
> - Password for database is too short (please see examples in the
>   demo code).
> 

Thanks for the report.  I'm looking into it now.

--
Joe Mistachkin

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

Re: [sqlite] New word to replace "serverless"

[Brian Curley]

"Serverless" has worked flawlessly since inception. Why change now...?

The marketing buzzword usage will disappear...long before Dr Hipp convinces
the list that email is dead even. 😏

Regards.

Brian P Curley



On Wed, Jan 29, 2020, 5:39 PM Jim Dodgen  wrote:

> I vote for ignoring the marketing types and stick with "serverless"
>
> Jim "Jed" Dodgen
> j...@dodgen.us
>
>
> On Wed, Jan 29, 2020 at 10:20 AM Thomas Kurz 
> wrote:
>
> > I would not choose a new wording. "Serverless" is correct, and just
> > because others start using "serverless" in a wrong manner, I don't see
> any
> > need for a change.
> >
> > Just my 2 cts.
> >
> >
> > - Original Message -
> > From: Richard Hipp 
> > To: General Discussion of SQLite Database <
> > sqlite-users@mailinglists.sqlite.org>
> > Sent: Monday, January 27, 2020, 23:18:45
> > Subject: [sqlite] New word to replace "serverless"
> >
> > For many years I have described SQLite as being "serverless", as a way
> > to distinguish it from the more traditional client/server design of
> > RDBMSes.  "Serverless" seemed like the natural term to use, as it
> > seems to mean "without a server".
> >
> > But more recently, "serverless" has become a popular buzz-word that
> > means "managed by my hosting provider rather than by me."  Many
> > readers have internalized this new marketing-driven meaning for
> > "serverless" and are hence confused when they see my claim that
> > "SQLite is serverless".
> >
> > How can I fix this?  What alternative word can I use in place of
> > "serverless" to mean "without a server"?
> >
> > Note that "in-process" and "embedded" are not adequate substitutes for
> > "serverless".  An RDBMS might be in-process or embedded but still be
> > running a server in a separate thread. In fact, that is how most
> > embedded RDBMSes other than SQLite work, if I am not much mistaken.
> >
> > When I say "serverless" I mean that the application invokes a
> > function, that function performs some task on behalf of the
> > application, then the function returns, *and that is all*.  No threads
> > are left over, running in the background to do housekeeping.  The
> > function does send messages to some other thread or process.  The
> > function does not have an event loop.  The function does not have its
> > own stack. The function (with its subfunctions) does all the work
> > itself, using the callers stack, then returns control to the caller.
> >
> > So what do I call this, if I can no longer use the word "serverless"
> > without confusing people?
> >
> > "no-server"?
> > "sans-server"?
> > "stackless"?
> > "non-client/server"?
> >
> >
> > --
> > 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
> >
> > ___
> > 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] New word to replace "serverless"

[Jim Dodgen]

I vote for ignoring the marketing types and stick with "serverless"

Jim "Jed" Dodgen
j...@dodgen.us


On Wed, Jan 29, 2020 at 10:20 AM Thomas Kurz  wrote:

> I would not choose a new wording. "Serverless" is correct, and just
> because others start using "serverless" in a wrong manner, I don't see any
> need for a change.
>
> Just my 2 cts.
>
>
> - Original Message -
> From: Richard Hipp 
> To: General Discussion of SQLite Database <
> sqlite-users@mailinglists.sqlite.org>
> Sent: Monday, January 27, 2020, 23:18:45
> Subject: [sqlite] New word to replace "serverless"
>
> For many years I have described SQLite as being "serverless", as a way
> to distinguish it from the more traditional client/server design of
> RDBMSes.  "Serverless" seemed like the natural term to use, as it
> seems to mean "without a server".
>
> But more recently, "serverless" has become a popular buzz-word that
> means "managed by my hosting provider rather than by me."  Many
> readers have internalized this new marketing-driven meaning for
> "serverless" and are hence confused when they see my claim that
> "SQLite is serverless".
>
> How can I fix this?  What alternative word can I use in place of
> "serverless" to mean "without a server"?
>
> Note that "in-process" and "embedded" are not adequate substitutes for
> "serverless".  An RDBMS might be in-process or embedded but still be
> running a server in a separate thread. In fact, that is how most
> embedded RDBMSes other than SQLite work, if I am not much mistaken.
>
> When I say "serverless" I mean that the application invokes a
> function, that function performs some task on behalf of the
> application, then the function returns, *and that is all*.  No threads
> are left over, running in the background to do housekeeping.  The
> function does send messages to some other thread or process.  The
> function does not have an event loop.  The function does not have its
> own stack. The function (with its subfunctions) does all the work
> itself, using the callers stack, then returns control to the caller.
>
> So what do I call this, if I can no longer use the word "serverless"
> without confusing people?
>
> "no-server"?
> "sans-server"?
> "stackless"?
> "non-client/server"?
>
>
> --
> 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
>
> ___
> 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] Possible caching issue between connections with specific inserts

[Alexey Podogov]

Hi SQLite development team.

I think that I found a bug in SQLite or in System.Data.SQLite and isolated the 
case when it happens. In short, the newly added row can be missed if it is 
requested from another connection.

The bug is quite stable and can be reproduced on my demo application each time, 
but it is very fragile: if I change or remove any query or some preference in 
connection string, it will disappear.

Short steps (full demo application with comments are referenced below):
0. Create two password-protected connections. Let's call them #0 and #1.
data source=DatabaseFileName;password=00
1. Use connection #1 to select something.
SELECT id FROM [MainTable]
2. Use connection #0 to insert new row to the same table.
INSERT INTO MainTable (id) VALUES (@id)
3. Use connection #0 to insert something absolutely not related to 
another table.
INSERT INTO DummyTable (Id) VALUES (@id)
4. Use connection #1 to read the row that was inserted first.
SELECT id FROM [MainTable]
Actual: The query doesn't return newly inserted row.

Each of these steps is obligatory. For me the step #3 looks the most confusing: 
issue happens if application inserts something to another table. In reality, if 
application inserts something to the same table, the issue will happen too. I 
left example with another table because it shows strangeness of the issue. But 
if application doesn't insert anything on this step, then you won't see the 
issue: on the step #4 the newly inserted row will be read.

Also, the issue can't be reproduced if:
- Cache size is set to 0; or
- Journal mode is WAL; or
- Password for database is too short (please see examples in the demo code).

Link to the demo application: 
https://github.com/apodogov/SQLiteCachingIssue/blob/master/IsolatedCacheIssue/Program.cs

Regards,
Alexey Podogov,
Akvelon - Russia (UTC+3),
Email: alexey.podo...@akvelon.com
Skype: alexey.podogov

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

Re: [sqlite] single table data collapse with constraints

[Keith Medcalf]

Excuse the top posting.  This perhaps:

create table srcdata
(
CLS1text not null,
CLS2integer not null,
START   integer not null,
END integer not null
);
insert into srcdata values ('ABC1',100,0,1);
insert into srcdata values ('ABC1',100,1,1);
insert into srcdata values ('ABC1',100,1,3);
insert into srcdata values ('ABC1',100,1,3);
insert into srcdata values ('ABC1',100,3,4);
insert into srcdata values ('ABC1',100,4,3);
insert into srcdata values ('ABC1',100,5,6);
insert into srcdata values ('ABC1',100,6,20);
insert into srcdata values ('ABC1',100,6,20);
insert into srcdata values ('ABC1',500,4,19);
insert into srcdata values ('ABC1',500,19,4);
insert into srcdata values ('ABC2',300,4,4);

sqlite> select * from srcdata;
CLS1CLS2START   END
--  --  --  --
ABC1100 0   1
ABC1100 1   1
ABC1100 1   3
ABC1100 1   3
ABC1100 3   4
ABC1100 4   3
ABC1100 5   6
ABC1100 6   20
ABC1100 6   20
ABC1500 4   19
ABC1500 19  4
ABC2300 4   4

with src (cls1, cls2, start, end)
  as (
  select distinct cls1, cls2, min(start, end), max(start, end)
from srcdata
 ),
 c (cls1, cls2, start, end)
  as (
 select cls1, cls2, start, end
   from src
  union
 select c.cls1, c.cls2, s.start, c.end
   from src as s, c
  where c.cls1 == s.cls1
and c.cls2 == s.cls2
and c.start == s.end
and s.start != c.start
 ),
 u (cls1, cls2, start, end)
  as (
  select cls1, cls2, start, end
from c as o
   where not exists (
 select *
   from c
  where cls1 == o.cls1
and cls2 == o.cls2
and (   (
 o.start between start + 1 and end
 and o.start between start and end - 1
)
 or (
 o.end between start + 1 and end
 and o.end between start and end - 1
)
)
)
 )
select *
  from u
order by cls1, cls2, start, end
;

cls1cls2start   end
--  --  --  --
ABC1100 0   4
ABC1100 5   20
ABC1500 4   19
ABC2300 4   4

So in the CTE 
 "src" makes sure that start < end and removes duplicate rows
 "c" finds and adds coalesced range rows
 "u" finds the rows in c where there is no containing row

If you have any significant amount of data the following procedure is probably 
many many times faster since the various bits of the single CTE version are not 
materialized (though if you can figure out how to force that, it will do the 
same thing):

drop table if exists temp.src;
drop table if exists temp.coal;

create temporary table src
(
cls1text not null,
cls2text not null,
start   integer not null,
end integer not null
);
insert into temp.src
select distinct cls1, cls2, min(start, end) as start, max(start, end) as end
  from srcdata
;
create index temp.idx_src on src (cls1, cls2, end);

create temporary table coal
(
cls1text not null,
cls2text not null,
start   integer not null,
end integer not null
);
insert into temp.coal
with c
  as (
 select cls1, cls2, start, end
   from temp.src
  union
 select c.cls1, c.cls2, s.start, c.end
   from temp.src as s, c
  where c.cls1 == s.cls1
and c.cls2 == s.cls2
and c.start == s.end
and s.start != c.start
 )
select cls1, cls2, start, end
  from c
;
create index temp.idx_coal on coal (cls1, cls2);

  select cls1, cls2, start, end
from temp.coal as o
   where not exists (
 select 1
   from temp.coal as c
  where cls1 == o.cls1
and cls2 == o.cls2
and (   (
 o.start between start + 1 and end
 and o.start between start and end - 1
)
 or (
 o.end between start + 1 and end
 and o.end between start and end - 1
)
)

Re: [sqlite] New word to replace "serverless"

[Thomas Kurz]

I would not choose a new wording. "Serverless" is correct, and just because 
others start using "serverless" in a wrong manner, I don't see any need for a 
change.

Just my 2 cts.


- Original Message - 
From: Richard Hipp 
To: General Discussion of SQLite Database 
Sent: Monday, January 27, 2020, 23:18:45
Subject: [sqlite] New word to replace "serverless"

For many years I have described SQLite as being "serverless", as a way
to distinguish it from the more traditional client/server design of
RDBMSes.  "Serverless" seemed like the natural term to use, as it
seems to mean "without a server".

But more recently, "serverless" has become a popular buzz-word that
means "managed by my hosting provider rather than by me."  Many
readers have internalized this new marketing-driven meaning for
"serverless" and are hence confused when they see my claim that
"SQLite is serverless".

How can I fix this?  What alternative word can I use in place of
"serverless" to mean "without a server"?

Note that "in-process" and "embedded" are not adequate substitutes for
"serverless".  An RDBMS might be in-process or embedded but still be
running a server in a separate thread. In fact, that is how most
embedded RDBMSes other than SQLite work, if I am not much mistaken.

When I say "serverless" I mean that the application invokes a
function, that function performs some task on behalf of the
application, then the function returns, *and that is all*.  No threads
are left over, running in the background to do housekeeping.  The
function does send messages to some other thread or process.  The
function does not have an event loop.  The function does not have its
own stack. The function (with its subfunctions) does all the work
itself, using the callers stack, then returns control to the caller.

So what do I call this, if I can no longer use the word "serverless"
without confusing people?

"no-server"?
"sans-server"?
"stackless"?
"non-client/server"?


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

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

Re: [sqlite] Virtual table OR constraint bug

[Lalit Maganti]

Thank you for the fast fix!

Best,
Lalit

On Wed, 29 Jan 2020 at 15:07, Dan Kennedy  wrote:

>
> On 29/1/63 20:09, Lalit Maganti wrote:
> > Hi folks,
> >
> > Just wanted to check up on this to see if this issue is something which
> is
> > being tracked and if there was a potential fix in the works?
>
> Thanks for the excellent bug report and minimal reproduction case. Now
> fixed here:
>
>https://www.sqlite.org/src/info/dcb4838757ca49cf
>
> None of us saw your post last month. We think it must have been filtered
> as spam by gmail. Sorry about that.
>
> Dan.
>
>
>
>
>
> >
> > Thanks,
> > Lalit
> >
> > On Thu, 2 Jan 2020 at 15:13, Lalit Maganti  wrote:
> >
> >> Hi all,
> >>
> >> I believe that I have found a bug in the virtual table bytecode
> generation
> >> when OR constraints are present and argvIndex is set in xBestIndex but
> the
> >> application does not actually filter fully.
> >>
> >> The problem seems to be in not setting/unsetting the non-null flag
> >> correctly (SQLITE_JUMPIFNULL) on the instruction which does the
> comparison;
> >> this leads to NULLs slipping through the filter.
> >>
> >> To fully repro the problem, please find attached a small C file which
> >> shows the unexpected behaviour. By adding an EXPLAIN in-front of the
> SELECT
> >> query, you should be able to see the incorrect bytecode on the NE
> opcode.
> >>
> >> If the attachment doesn't come through for some reason, I've also put
> the
> >> same file as a GitHub gist
> >> .
> >>
> >> Thank you!
> >>
> >> Regards,
> >> Lalit
> >>
> > ___
> > 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] Virtual table OR constraint bug

[Dan Kennedy]

On 29/1/63 20:09, Lalit Maganti wrote:

Hi folks,

Just wanted to check up on this to see if this issue is something which is
being tracked and if there was a potential fix in the works?


Thanks for the excellent bug report and minimal reproduction case. Now 
fixed here:


  https://www.sqlite.org/src/info/dcb4838757ca49cf

None of us saw your post last month. We think it must have been filtered 
as spam by gmail. Sorry about that.


Dan.







Thanks,
Lalit

On Thu, 2 Jan 2020 at 15:13, Lalit Maganti  wrote:


Hi all,

I believe that I have found a bug in the virtual table bytecode generation
when OR constraints are present and argvIndex is set in xBestIndex but the
application does not actually filter fully.

The problem seems to be in not setting/unsetting the non-null flag
correctly (SQLITE_JUMPIFNULL) on the instruction which does the comparison;
this leads to NULLs slipping through the filter.

To fully repro the problem, please find attached a small C file which
shows the unexpected behaviour. By adding an EXPLAIN in-front of the SELECT
query, you should be able to see the incorrect bytecode on the NE opcode.

If the attachment doesn't come through for some reason, I've also put the
same file as a GitHub gist
.

Thank you!

Regards,
Lalit


___
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] single table data collapse with constraints

[Golding Robert]

Hi,

I am emailing to ask if someone could advise me how to create a query or 
queries which will collapse some data based on a limited number of constraints.
I am currently attempting to complete this task using DB Browser for SQLite. I  
have tried to write a WITH RECLUSIVE statement as I think this is the 
requirement but am struggling with both the abstraction and syntax.


I have data contained with a single table of structure:
CLS1 field (text)
CLS2 field (integer)
START field (integer)
END field (integer

I need to collapse the data based on the matching of values in fields CLS1 and 
CLS2; the final constraint is that if END and START values are continuous of 
the another record then they can be collapsed. Therefore records should only be 
collapsed if gaps do not exist. The results then need to written to a new 
table, leaving the original data as is.

Input data: assumptions

  1.  Data may or may not be ordered
  2.  Duplicates may or may not exist
  3.  Start and end values could be the same
  4.  Start values are normally lower that the end value, however the high 
value could be in the start field
  5.  Assume that there is no overlap in terms of start and end values (namely 
if they can be joined then one will stop where the next starts)

Input data:  example
CLS1,CLS2,START,END
ABC1,100,0,1
ABC1,100,1,1 (start and end values could be the same, in the first instance 
assume that they may be dissolved if possible, if they cannot the record need 
to be retained)
ABC1,100,1,3
ABC1,100,1,3 (duplicates may or may not be present, if present then they can be 
dissolved into a single instance)
ABC1,100,3,4
ABC1,100,4,3
ABC1,100,5,6
ABC1,100,6,20
ABC1,100,6,20(duplicates may or may not be present, if present then they can be 
dissolved into a single instance)
ABC1,500,4,19
ABC1,500,19,4 (start and end values could be inverted where Start is high and 
End id low (this is not the norm but it is legitimate) in this case start and 
end values may be inverted, and in this case dissolved into a single instance)
ABC2,300,4,4 (start and end values could be the same, in the first instance 
assume that they may be dissolved if possible, if they cannot the record need 
to be retained)



Output data: collapsed/merged expected output
CLS1,CLS2,START,END
ABC1,100,0,4
ABC1,100,5,20
ABC1,500,4,19
ABC2,300,4,4


I would be extremely grateful if anybody could help me with this issue.


Regards,

Rob




 

The content of this email (and any attachment) is confidential. It may also be 
legally privileged or otherwise protected from disclosure. 
This email should not be used by anyone who is not an original intended 
recipient, nor may it be copied or disclosed to anyone who is not an original 
intended recipient. 

If you have received this email by mistake please notify us by emailing the 
sender, and then delete the email and any copies from your system. 

Liability cannot be accepted for statements made which are clearly the sender's 
own and not made on behalf of Network Rail. 
Network Rail Infrastructure Limited registered in England and Wales No. 
2904587, registered office Network Rail, 2nd Floor, One Eversholt Street, 
London, NW1 2DN 


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

Re: [sqlite] Is sqlite3 smart enough to remove redundant command?

[David Raymond]

It's going to run both since you asked it to. Even if it was in a compiled 
language where the compiler could look ahead and was looking to implement that 
sort of optimization, then for example there still might be triggers on the 
table which would need to be run, or other constraints on the fields which 
might get triggered by the second statement and not the first. There's no way 
it's going to know that without actually running them both.


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Wednesday, January 29, 2020 12:04 AM
To: SQLite mailing list 
Subject: [sqlite] Is sqlite3 smart enough to remove redundant command?

Suppose that I have the following command, which writes two entries
with the same key. So the 1st entry will be overwritten by the 2nd
entry. Therefore, there is no need to write the 1st entry. Is sqlite3
smart enough to not to write the 1st entry? Or it will write both the
1st entry and the 2nd entry? Thanks.

conn=sqlite3.connect("my.db")
c=conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS sqlar(
name TEXT PRIMARY KEY
, mode INT
, mtime INT
, sz INT
, data BLOB)
''')
c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', ["a", 0, 0, 1, "1"])
c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', ["a", 0, 0, 1, "2"])
conn.commit()

-- 
Regards,
Peng
___
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] Generated columns and COLLATE in the AS parens

[Richard Hipp]

On 1/29/20, Markus Winand  wrote:
> Hi!
>
> I think there might be a glitch in the way SQLite 3.31.x derives the
> collation information from the expression of a generated column.

I think the current behavior is correct.

If you want a column to have a non-standard collating sequence, you
should add a COLLATE constraint to that column definition.  The fact
that there is a COLLATE operator on the expression that determines the
value of that column seems irrelevant.

Consider this:

CREATE TABLE t1(a TEXT DEFAULT('xyzzy' COLLATE nocase));

Would you expect the COLLATE operator in the DEFAULT clause to change
the collating sequence associated with column a?  Why should a
GENERATED ALWAYS AS constraint work differently from a DEFAULT
constraint?

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

[sqlite] Generated columns and COLLATE in the AS parens

[Markus Winand]

Hi!

I think there might be a glitch in the way SQLite 3.31.x derives the collation 
information from the expression of a generated column.

In particular, COLLATE inside the AS parens seems to be ignored, but it is 
honoured after the parens:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE tmp (str VARCHAR(255), str_nc1 GENERATED ALWAYS AS (str 
COLLATE NOCASE), str_nc2 GENERATED ALWAYS AS (str) COLLATE NOCASE);
sqlite> INSERT INTO tmp VALUES ('a'), ('A'), ('b'), ('B');
sqlite> SELECT * FROM tmp ORDER BY str;
A|A|A
B|B|B
a|a|a
b|b|b
sqlite> SELECT * FROM tmp ORDER BY str_nc1;
A|A|A
B|B|B
a|a|a
b|b|b
sqlite> SELECT * FROM tmp ORDER BY str_nc2;
a|a|a
A|A|A
b|b|b
B|B|B
sqlite> SELECT * FROM tmp ORDER BY (str COLLATE NOCASE);
a|a|a
A|A|A
b|b|b
B|B|B
sqlite>

I believe the "ORDER BY str_nc1” should yield the same row order as the last 
two queries.

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

Re: [sqlite] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Stefan Brüns]

On Dienstag, 28. Januar 2020 18:26:05 CET Brüns, Stefan wrote:
> 
> On armv7l, there is another failure in the fuzztests, with and without the
> patch:
> sessionfuzz-data1.db: sessionfuzz: ./sqlite3.c:57249: pager_open_journal:
> Assertion `rc!=SQLITE_OK || isOpen(pPager->jfd)' failed.

I had previously overlooked this, but the fuzz check also fails on ppc32be, 
but passes on i586 (and all tried 64bit archs).

Kind regards,

Stefan

-- 
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034 mobile: +49 151 50412019

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Stefan Brüns]

On Mittwoch, 29. Januar 2020 13:40:44 CET Richard Hipp wrote:
> Please retry using this check-in:
> https://www.sqlite.org/src/info/b20503aaf5b6595a

The failings test now pass on all architectures:
- ix86/x86_64
- armv7hl, aarch64
- ppc32be, ppc64be, ppc64le
- s390x

Kind regards,

Stefan

> On 1/28/20, Brüns, Stefan  wrote:
> > On Dienstag, 28. Januar 2020 18:26:05 CET Brüns, Stefan wrote:
> >> On Dienstag, 28. Januar 2020 16:16:01 CET Richard Hipp wrote:
> >> > On 1/27/20, Ondrej Dubaj  wrote:
> >> > > Hi,
> >> > > 
> >> > > I came across a problem during mate test, where fuzzcheck ends with
> >> > > segfault.
> >> > > The problem appears to be only on [s390x]. Other architectures are
> >> > > working fine.
> >> > 
> >> > Fixed by check-in https://www.sqlite.org/src/info/04885763c4cd00cb
> >> > 
> >> > Thanks for the temporary SSH login!
> >> 
> >> We were seeing the problem also on other ppc64BE:
> >> 
> >> ppc64 (big endian):
> >> fuzzdata1.db: 0% 10% 20% 30% 40% 50% 60% 70%./fuzzcheck
> >> /home/abuild/rpmbuild/ BUILD/sqlite-src-3310100/test/fuzzdata1.db
> >> (sqlid=7726,dbid=1): segfault
> >> 
> >> The issue is cured with the fix, but we still see 3 failing tests with
> >> fts4/
> >> fts5:
> >> 
> >> ! fts5matchinfo-15.1 expected: [X'0200']
> >> ! fts5matchinfo-15.1 got:  [X'0002']
> >> ! fts5matchinfo-15.2 expected: [X'0200']
> >> ! fts5matchinfo-15.2 got:  [X'0002']
> >> ! fts4aa-6.10 expected:
> >> [X'02000E000E0001000100010001
> >> 00' ] ! fts4aa-6.10 got:
> >> [X'0002000E000E00010001000100
> >> 01' ]
[...]
> > 
> > This is 3.31.1, btw.
> > 

-- 
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034 mobile: +49 151 50412019

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table OR constraint bug

[Lalit Maganti]

Hi folks,

Just wanted to check up on this to see if this issue is something which is
being tracked and if there was a potential fix in the works?

Thanks,
Lalit

On Thu, 2 Jan 2020 at 15:13, Lalit Maganti  wrote:

> Hi all,
>
> I believe that I have found a bug in the virtual table bytecode generation
> when OR constraints are present and argvIndex is set in xBestIndex but the
> application does not actually filter fully.
>
> The problem seems to be in not setting/unsetting the non-null flag
> correctly (SQLITE_JUMPIFNULL) on the instruction which does the comparison;
> this leads to NULLs slipping through the filter.
>
> To fully repro the problem, please find attached a small C file which
> shows the unexpected behaviour. By adding an EXPLAIN in-front of the SELECT
> query, you should be able to see the incorrect bytecode on the NE opcode.
>
> If the attachment doesn't come through for some reason, I've also put the
> same file as a GitHub gist
> .
>
> Thank you!
>
> Regards,
> Lalit
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IO Short Read Error when inserting a TEXT column

[Richard Hipp]

On 1/28/20, rgarnett  wrote:
> the step function fails
> with an IO Error Short Read.The VFS I am using I developed myself from the
> demo on the sqLite website.  I suspect there may be problems with this code

I suspect you are correct.

The SQLITE_IOERR_SHORT_READ is an error code that is only generated by
the VFS.  That means it is your code that is likely generating the
error.  I suggest you set a breakpoint on the place in your custom VFS
where the SQLITE_IOERR_SHORT_READ is being generated and try to figure
out what is going on.

-- 
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] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures

[Richard Hipp]

Please retry using this check-in:
https://www.sqlite.org/src/info/b20503aaf5b6595a

On 1/28/20, Brüns, Stefan  wrote:
> On Dienstag, 28. Januar 2020 18:26:05 CET Brüns, Stefan wrote:
>> On Dienstag, 28. Januar 2020 16:16:01 CET Richard Hipp wrote:
>> > On 1/27/20, Ondrej Dubaj  wrote:
>> > > Hi,
>> > >
>> > > I came across a problem during mate test, where fuzzcheck ends with
>> > > segfault.
>> > > The problem appears to be only on [s390x]. Other architectures are
>> > > working fine.
>> >
>> > Fixed by check-in https://www.sqlite.org/src/info/04885763c4cd00cb
>> >
>> > Thanks for the temporary SSH login!
>>
>> We were seeing the problem also on other ppc64BE:
>>
>> ppc64 (big endian):
>> fuzzdata1.db: 0% 10% 20% 30% 40% 50% 60% 70%./fuzzcheck
>> /home/abuild/rpmbuild/ BUILD/sqlite-src-3310100/test/fuzzdata1.db
>> (sqlid=7726,dbid=1): segfault
>>
>> The issue is cured with the fix, but we still see 3 failing tests with
>> fts4/
>> fts5:
>>
>> ! fts5matchinfo-15.1 expected: [X'0200']
>> ! fts5matchinfo-15.1 got:  [X'0002']
>> ! fts5matchinfo-15.2 expected: [X'0200']
>> ! fts5matchinfo-15.2 got:  [X'0002']
>> ! fts4aa-6.10 expected:
>> [X'02000E000E000100010001000100'
>> ] ! fts4aa-6.10 got:
>> [X'0002000E000E0001000100010001'
>> ]
>
> Seems to be an endianess problem, all three affected archs are big endian
> (ppc32, ppc64be, s390x):
>
>> ! fts4aa-6.10 expected:
>> [X'0200   0E00 0E00 0100 0100 0100
>>
> 0100'
>> ] ! fts4aa-6.10 got:
>> [X'0002   000E 000E 0001 0001 0001
>>
> 0001'
>> ]
>
> This is 3.31.1, btw.
>
> Kind regards, Stefan
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Is mutliple-execute-one-commit slower than multiple single-execute-single-commit?

[Richard Damon]

On 1/29/20 1:42 AM, Peng Yu wrote:

Hi,

I have two python programs using sqlite3. They function the same,
except the following.

In the first, execute() is called in batches and then commit() is
called following them. In the second, commit() is called after each
execute(). It seems that the second case is faster (I can not separate
my code in a self-contained test case to show here).

This is counterintuitive. I thought the first should be faster.

Is it expected that the 2nd case should be slightly faster?

One thought is that if the execute creates a lot of data for the 
database, then multiple executes could exceed the memory cache, causing 
it to spill to the database, and then the commit needs to read that back 
and put it into the right place, while a commit after each execute keeps 
everything in memory until the commit writes it to the database.


--
Richard Damon

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

Re: [sqlite] Is mutliple-execute-one-commit slower than multiple single-execute-single-commit?

[Keith Medcalf]

On Tuesday, 28 January, 2020 23:42, Peng Yu  wrote:

>I have two python programs using sqlite3. They function the same,
>except the following.

I presume this means you are using the standard (as in included with the 
standard Python distribution) sqlite3 module?  There are other ways to use 
SQLite3 (the database) from Python, the sqlite3 (pysqlite2) wrapper being only 
one of them.  What are the statements being executed?  Are you using "magical" 
mode for the wrapper (what is the isolation_level set to on the sqlite3.connect 
call).

>In the first, execute() is called in batches and then commit() is
>called following them. In the second, commit() is called after each
>execute(). It seems that the second case is faster (I can not separate
>my code in a self-contained test case to show here).

>This is counterintuitive. I thought the first should be faster.

>Is it expected that the 2nd case should be slightly faster?

The first case (batching multiple inserts in a single transaction) should be 
faster.  On the below test disk I/O rate exceeded 100 MB/s for wal mode.

-//- insertspeed.py -//-
import sqlite3
import sys
import time

db = sqlite3.connect('test.db')

db.executescript('create table if not exists x(x)')

records = 100

print(sys.version)
print('sqlite3 wrapper version', sqlite3.version, 'using library version', 
sqlite3.sqlite_version)
print()
for mode in ['wal', 'delete']:
print('Database mode', mode)
print('  ')
print(' RecordsBatch  Seconds')
print('  ')
for batchsize in [100, 10, 1, 1000, 100, 10, 1]:
db.executescript('pragma journal_mode=delete')
db.executescript('delete from x')
db.executescript('vacuum')
db.execute('pragma journal_mode=%s' % mode)
t = time.time()
for i in range(records):
db.execute('insert into x values (?)', (i,))
if i % batchsize == 0:
db.commit()
db.commit()
print('%8d %8d %8.3f' % (records, batchsize, time.time() - t))
print('  ')

-//--//-

3.8.1 (tags/v3.8.1:1b293b6, Dec 18 2019, 23:11:46) [MSC v.1916 64 bit (AMD64)]
sqlite3 wrapper version 2.6.0 using library version 3.31.1

Database mode wal
  
 RecordsBatch  Seconds
  
 100  1002.255
 100   102.348
 10012.344
 100 10002.453
 100  1003.355
 100   108.856
 1001   64.167
  
Database mode delete
  
 RecordsBatch  Seconds
  
 100  1002.215
 100   102.460
 10013.320
 100 1000   12.099
 100  100   96.896

I couldn't wait for the smaller batches to complete in journal_mode delete.  I 
would expect them to take a looong time ...

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