Re: [sqlite] Merge two sqlar files

[Keith Medcalf]

Be aware however that the resulting sqlar table is incomplete.  It does not 
have a "create unique index sqlar_autoindex_1 on sqlar(name)", and you may not 
be able to create one because there may be duplicate name entries 
notwithstanding that the rows are unique.

To do this properly and include the last "name" duplicate as the unique "name" 
entry you would do for an arbitrary number of sqlar files:

import sqlite3
cn=sqlite3.connect(sys.argv[1], isolation_level=None) # open database and turn 
off the broken magic
cn.execute('''CREATE TABLE IF NOT EXISTS sqlar(
name TEXT PRIMARY KEY,  -- name of the file
mode INT,   -- access permissions
mtime INT,  -- last modification time
sz INT, -- original file size
data BLOB   -- compressed content
  );''')
for infile in sys.argv[2:]:
cn.execute('ATTACH DATABASE ? AS d1', [infile])
cn.execute('''insert into main.sqlar select * from d1.sqlar where true 
  on conflict (name) do 
  update set (mode, mtime, sz, data) = (excluded.mode, 
excluded.mtime, excluded.sz, excluded.data);''')
cn.execute('DETACH d1')
cn.close()

*you need to be using a version of sqlite that understands the insert on 
conflict (upsert) and true/false constants.

where the first param is the name of the database (which may already be an 
sqlar database containing data) to merge into and the rest of the params are 
the sqlar databases to merge from.

-- 
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: Friday, 6 September, 2019 18:09
>To: SQLite mailing list 
>Subject: Re: [sqlite] Merge two sqlar files
>
>OK. It works. But it seems that `.ar` is not necessary.
>
>import sqlite3
>infile1, infile2, outfile = sys.argv[1:]
>conn=sqlite3.connect(outfile)
>c=conn.cursor()
>c.execute('ATTACH DATABASE ? AS d1', [infile1])
>c.execute('ATTACH DATABASE ? AS d2', [infile2])
>c.execute('CREATE TABLE sqlar AS SELECT * FROM d1.sqlar UNION SELECT *
>FROM d2.sqlar')
>conn.commit()
>
>> UNION is for "unioning" the output of two select statements (with
>duplicate rows removed).  It does not matter if the tables are in the same
>database or even on the same planet, as long as they can be accessed on the
>same connection.  Sqlar creates nothing more than a standard sqlite
>database file with a table called sqlar that contains the data.
>>
>> >sqlite
>> SQLite version 3.30.0 2019-09-04 07:55:38
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .ar -cf test.db appi.py
>> sqlite> .ar -cf test1.db bs.py
>> sqlite> attach 'test.db' as test;
>> sqlite> attach 'test1.db' as test1;
>> sqlite> create table sqlar as select * from test.sqlar union select *
>from test1.sqlar;
>> sqlite> .ar -t
>> appi.py
>> bs.py
>
>--
>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] Merge two sqlar files

[Peng Yu]

OK. I made a mistake. UNION of multiple tables should work.

On Fri, Sep 6, 2019 at 7:28 PM Peng Yu  wrote:
>
> Hi, I think that there is no way to UNION 3 tables in one command is
> it. What is the best way to merge 3 and more sqlar files? Thanks.
>
> On Fri, Sep 6, 2019 at 6:30 PM Keith Medcalf  wrote:
> >
> >
> > UNION is for "unioning" the output of two select statements (with duplicate 
> > rows removed).  It does not matter if the tables are in the same database 
> > or even on the same planet, as long as they can be accessed on the same 
> > connection.  Sqlar creates nothing more than a standard sqlite database 
> > file with a table called sqlar that contains the data.
> >
> > >sqlite
> > SQLite version 3.30.0 2019-09-04 07:55:38
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> .ar -cf test.db appi.py
> > sqlite> .ar -cf test1.db bs.py
> > sqlite> attach 'test.db' as test;
> > sqlite> attach 'test1.db' as test1;
> > sqlite> create table sqlar as select * from test.sqlar union select * from 
> > test1.sqlar;
> > sqlite> .ar -t
> > appi.py
> > bs.py
> >
> > --
> > 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: Friday, 6 September, 2019 17:04
> > >To: SQLite mailing list 
> > >Subject: [sqlite] Merge two sqlar files
> > >
> > >Hi,
> > >
> > >UNION is for tables in the same db file. Is there an easy command to
> > >combine two sqlar files? Thanks.
> > >
> > >https://www.sqlitetutorial.net/sqlite-union/
> > >
> > >--
> > >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
>
>
>
> --
> Regards,
> Peng



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

Re: [sqlite] Merge two sqlar files

[Peng Yu]

Hi, I think that there is no way to UNION 3 tables in one command is
it. What is the best way to merge 3 and more sqlar files? Thanks.

On Fri, Sep 6, 2019 at 6:30 PM Keith Medcalf  wrote:
>
>
> UNION is for "unioning" the output of two select statements (with duplicate 
> rows removed).  It does not matter if the tables are in the same database or 
> even on the same planet, as long as they can be accessed on the same 
> connection.  Sqlar creates nothing more than a standard sqlite database file 
> with a table called sqlar that contains the data.
>
> >sqlite
> SQLite version 3.30.0 2019-09-04 07:55:38
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .ar -cf test.db appi.py
> sqlite> .ar -cf test1.db bs.py
> sqlite> attach 'test.db' as test;
> sqlite> attach 'test1.db' as test1;
> sqlite> create table sqlar as select * from test.sqlar union select * from 
> test1.sqlar;
> sqlite> .ar -t
> appi.py
> bs.py
>
> --
> 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: Friday, 6 September, 2019 17:04
> >To: SQLite mailing list 
> >Subject: [sqlite] Merge two sqlar files
> >
> >Hi,
> >
> >UNION is for tables in the same db file. Is there an easy command to
> >combine two sqlar files? Thanks.
> >
> >https://www.sqlitetutorial.net/sqlite-union/
> >
> >--
> >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



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

Re: [sqlite] Merge two sqlar files

[Peng Yu]

OK. It works. But it seems that `.ar` is not necessary.

import sqlite3
infile1, infile2, outfile = sys.argv[1:]
conn=sqlite3.connect(outfile)
c=conn.cursor()
c.execute('ATTACH DATABASE ? AS d1', [infile1])
c.execute('ATTACH DATABASE ? AS d2', [infile2])
c.execute('CREATE TABLE sqlar AS SELECT * FROM d1.sqlar UNION SELECT *
FROM d2.sqlar')
conn.commit()

> UNION is for "unioning" the output of two select statements (with duplicate 
> rows removed).  It does not matter if the tables are in the same database or 
> even on the same planet, as long as they can be accessed on the same 
> connection.  Sqlar creates nothing more than a standard sqlite database file 
> with a table called sqlar that contains the data.
>
> >sqlite
> SQLite version 3.30.0 2019-09-04 07:55:38
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .ar -cf test.db appi.py
> sqlite> .ar -cf test1.db bs.py
> sqlite> attach 'test.db' as test;
> sqlite> attach 'test1.db' as test1;
> sqlite> create table sqlar as select * from test.sqlar union select * from 
> test1.sqlar;
> sqlite> .ar -t
> appi.py
> bs.py

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

Re: [sqlite] Merge two sqlar files

[Keith Medcalf]

UNION is for "unioning" the output of two select statements (with duplicate 
rows removed).  It does not matter if the tables are in the same database or 
even on the same planet, as long as they can be accessed on the same 
connection.  Sqlar creates nothing more than a standard sqlite database file 
with a table called sqlar that contains the data.

>sqlite
SQLite version 3.30.0 2019-09-04 07:55:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .ar -cf test.db appi.py
sqlite> .ar -cf test1.db bs.py
sqlite> attach 'test.db' as test;
sqlite> attach 'test1.db' as test1;
sqlite> create table sqlar as select * from test.sqlar union select * from 
test1.sqlar;
sqlite> .ar -t
appi.py
bs.py

-- 
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: Friday, 6 September, 2019 17:04
>To: SQLite mailing list 
>Subject: [sqlite] Merge two sqlar files
>
>Hi,
>
>UNION is for tables in the same db file. Is there an easy command to
>combine two sqlar files? Thanks.
>
>https://www.sqlitetutorial.net/sqlite-union/
>
>--
>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

[sqlite] Merge two sqlar files

[Peng Yu]

Hi,

UNION is for tables in the same db file. Is there an easy command to
combine two sqlar files? Thanks.

https://www.sqlitetutorial.net/sqlite-union/

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

Re: [sqlite] Why is a multiple MATCH clause not allowed in an FTS query?

[Jens Alfke]

> On Aug 30, 2019, at 12:43 AM, mailing lists  wrote:
> 
> SELECT * FROM myData WHERE (content MATCH 'one') AND (body MATCH 'two');
> 
> What is the reason that the above query is not allowed 

I'm curious too. This limitation is documented somewhere, but not the reason 
why.
For programs that generate queries automatically or by translating a different 
input form, it's annoying to work around.

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

Re: [sqlite] non-aggregate columns in aggregate queries

[Simon Slavin]

On 6 Sep 2019, at 11:40pm, Jeff Rogers  wrote:

> Could a pragma or build-time flag be added to enforce "standard" behavior, 
> i.e., raising an error rather than handling it as currently documented?

I think this is one of the things mentioned in



which, of course, discusses a feature which doesn't exist.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] non-aggregate columns in aggregate queries

[Jeff Rogers]

Hi all,

It's a documented quirk that sqlite allows the inclusion of 
non-aggregate, non-group by columns in an agregate query:


https://sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause

The benefits aside, it's not "standard sql", or at a minimum most other 
databases do not allow it.  Could a pragma or build-time flag be added 
to enforce "standard" behavior, i.e., raising an error rather than 
handling it as currently documented?



-J

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

Re: [sqlite] Is pcre available on homebrew?

[Keith Medcalf]

There is a standard extension for the regexp function.  You have to compile it 
yourself, however.

ext/misc/regexp.c in the source distribution.

Then load the module.

-- 
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: Friday, 6 September, 2019 10:44
>To: SQLite mailing list 
>Subject: [sqlite] Is pcre available on homebrew?
>
>I'd like to use regex.
>
>https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-
>query
>
>But I don't find pcre.
>
>https://formulae.brew.sh/formula/sqlite
>
>Does anybody know how to make regex available for slqite3 installed by
>homebrew? Thanks.
>
>--
>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

[sqlite] Is pcre available on homebrew?

[Peng Yu]

I'd like to use regex.

https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query

But I don't find pcre.

https://formulae.brew.sh/formula/sqlite

Does anybody know how to make regex available for slqite3 installed by
homebrew? Thanks.

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

Re: [sqlite] Query for Many to Many

[Simon Slavin]

On 6 Sep 2019, at 7:36am, Rowan Worth  wrote:

> I was surprised when this behaved differently in other SQL engines. eg. in
> SQLite you can write:
> 
> SELECT col1, col2 FROM table1, table2 USING 

But please don't, for the reason you gave.  Not only is it ambiguous but 
different SQL engines interpret it differently.  SQLite uses PostgreSQL as a 
model for many things but here they diverge.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Endless loop in update recursive query with UNION ALL

[Hick Gunter]

Does your "parent" relationship contain (at least one) loop(s)? UNION will 
break the loop by eliminating already visited rows, whereas UNION ALL will run 
faster precisely because it does not keep track of the visited rows.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Slava G
Gesendet: Freitag, 06. September 2019 10:56
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Endless loop in update recursive query with UNION 
ALL

I have query that enters into endless loop in update recursive query with UNION 
ALL, but when I remove ALL from the UNION it's works fine :
WITH recursive parentitems(itemid) AS (
VALUES("58f6fb3e-40a0-4b32-90a1-37945c44a649_c476ed54-217a-432a-9857-4fbb1eb5bc7a")

*UNION ALL *
SELECT snapshotdata.itemid
FROM   snapshotdata,
   parentitems
WHERE  folder = parentitems.itemid
ANDifnull(deleteddate ,99) = 99 )
UPDATE snapshotdata
SETdeleteddate = 20190903142833
WHERE  itemid IN parentitems
ANDbackupdate < 20190903142833
ANDifnull(deleteddate,99) = 99

As far as I understand ALL in UNION should provide better performance, but 
somehow it enters into endless loop and eat all computer resources.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Endless loop in update recursive query with UNION ALL

[Slava G]

I have query that enters into endless loop in update recursive query with
UNION ALL, but when I remove ALL from the UNION it's works fine :
WITH recursive parentitems(itemid) AS (
VALUES("58f6fb3e-40a0-4b32-90a1-37945c44a649_c476ed54-217a-432a-9857-4fbb1eb5bc7a")

*UNION ALL *
SELECT snapshotdata.itemid
FROM   snapshotdata,
   parentitems
WHERE  folder = parentitems.itemid
ANDifnull(deleteddate ,99) = 99 )
UPDATE snapshotdata
SETdeleteddate = 20190903142833
WHERE  itemid IN parentitems
ANDbackupdate < 20190903142833
ANDifnull(deleteddate,99) = 99

As far as I understand ALL in UNION should provide better performance, but
somehow it enters into endless loop and eat all computer resources.

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

[sqlite] Bug with json_group_array() as a window function

[Shawn Wagner]

Using 3.29 and a 3.30 snapshot:

When using json_group_array() as a window function, it loses the tagging of
its argument as being JSON, treating JSON objects etc. as strings instead
when creating arrays.

Sample table:

CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT);
INSERT INTO testjson VALUES(1, '{"a":1}');
INSERT INTO testjson VALUES(2, '{"b":2}');

This query

SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;

produces

["{\"a\":1}"]
["{\"a\":1}","{\"b\":2}"]

instead of

[{"a":1}]
[{"a":1},{"b":2}]

while the plain aggregate

SELECT json_group_array(json(j)) FROM testjson;

produces the expected

[{"a":1},{"b":2}]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query for Many to Many

[Rowan Worth]

On Tue, 3 Sep 2019 at 22:17, Keith Medcalf  wrote:

> And the "," in the list of tables may be replaced by the word JOIN.  It is
> merely an alternate spelling.
>

I was surprised when this behaved differently in other SQL engines. eg. in
SQLite you can write:

SELECT col1, col2 FROM table1, table2 USING (commonId)

But in eg. postgres it must be written using "table1 JOIN table2" rather
than the comma, because postgres treats "table1, table2" as "table1 JOIN
tabel2 ON TRUE" resulting in a conflict with the USING clause.

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