Re: [sqlite] Is it possible to transpose a table using SQL?

[Jake Thaw]

Hi Shane,

You might be interested in trying out my pivot virtual table implementation.

https://github.com/jakethaw/pivot_vtab

This will cater for changing values, but like a VIEW implementation,
it does not satisfy your criteria of dynamically changing
rows/columns. Changes to rows/columns can only be propagated by
dropping and re-creating the virtual table.

I have provided an example below using your data structure, however it
works nicer with normalized data.

e.g.

.load ./pivot_vtab
.headers on
.mode column

CREATE TABLE t(
  "Product/Region",
  Belgium,
  France,
  USA
);
INSERT INTO t VALUES
('Oil_filter', 1, 2, 3),
('Spark_plug', 4, 5, 6),
('Coolent', 7, 8, 9);

CREATE VIRTUAL TABLE pivot USING pivot_vtab(
 (SELECT name "Product/Region" -- pivot table key
FROM pragma_table_info('t')
   WHERE name <> 'Product/Region'),

 (SELECT "Product/Region", -- pivot column key - can be referenced in
pivot query as ?2,
 "Product/Region"  -- pivot column name
FROM t),

 (SELECT CASE ?1
   WHEN 'Belgium' THEN Belgium
   WHEN 'France' THEN France
   WHEN 'USA' THEN USA
 END
FROM t
   WHERE "Product/Region" = ?2)
);

SELECT *
  FROM pivot;

Product/Region  Oil_filter  Spark_plug  Coolent
--  --  --  --
Belgium 1   4   7
France  2   5   8
USA 3   6   9

-Jake

On Mon, Apr 1, 2019 at 7:07 AM Shane Dev  wrote:
>
> Hello,
>
> Is it possible to create a view which switches rows and columns of a
> dynamically changing table?
>
> For example, imagine we have table t1 where both columns and rows could
> change after the view has been created
>
> sqlite> select * from t1;
> Product/Region|Belgium|France|USA
> Oil_filter|1|2|3
> Spark_plug|4|5|6
> Coolent|7|8|9
>
> Could view v1 be created such that
>
> sqlite> select * from v1;
> Product/Region|Oil_filter|Spark_plug|Coolent
> Belgium|1|4|7
> France|2|5|8
> USA|3|6|9
> ___
> 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] Need advice: Some queries show performance regression

[Jinho Jung]

Thanks for the quick response.

1) It seems our bisecting is wrong. We manually moved commit by commit by
using "fossil checkout" command. Next time, we will use "fossil bisect"
command.
2) Sorry for the misleading. Our first report contains three queries. We
will submit another cases with correct bisect later.

Jinho Jung

On Mon, Apr 1, 2019 at 1:32 PM Richard Hipp  wrote:

> On 4/1/19, Jinho Jung  wrote:
> > Hello,
> >
> > We are developing a tool called sqlfuzz for automatically finding
> > performance regressions in SQLite. sqlfuzz performs mutational fuzzing to
> > generate SQL queries that take more time to execute on the latest version
> > of SQLite compared to prior versions. We hope that these queries would
> help
> > further increase the utility of the regression test suite.
>
> Thanks for the report.
>
> Since there are already a bazillion fuzzers for SQLite, may I suggest
> that you choose a more a more specific and descriptive name for your
> fuzzer?  Perhaps "sql-perf-fuzz" or something similar - so that we
> know that your fuzzer is targeting performance regressions?
>
> >
> > We are sharing four SQL queries that exhibit regressions in this report.
> > Here’s an illustrative query:
>
> I only got 3 SQL queries.  What am I missing?
>
> Also, I got bisect results for all three problem that are different
> from the results you report.  When I run bisect, I get the same result
> for all three test cases:
>
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__sqlite.org_src_timeline-3Fbid-3Dy736b53f57fnbd49a8271dycb1511065dy6c6fb1c6eay30f08d5888y507c43537fy2c8769c69fn4371a0c46en4d0a949fd9n79c073878dy93386a7c97nd840e9bb02=DwIFaQ=clK7kQUTWtAVEOVIgvi0NU5BOUHhpN0H8p7CSfnc_gI=Eb5d36HrPu-wBrtI5PLOoA=bTwdIvNq94lBWxZ0Jw0Hony2eN54rEUuJrZ2CNiSxYg=W8KSOERSMGlpnn6BItMu8zuy4oCZlGBlFUDrZGVwBzw=
>
> So it was apparently a bug-fix that caused the performance decrease.
> I have not looked into the details yet.  Perhaps there is an
> alternative fix for the bug that does not cause unnecessary
> performance loss.
>
> --
> 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] Need advice: Some queries show performance regression

[Richard Hipp]

On 4/1/19, Jinho Jung  wrote:
> Hello,
>
> We are developing a tool called sqlfuzz for automatically finding
> performance regressions in SQLite. sqlfuzz performs mutational fuzzing to
> generate SQL queries that take more time to execute on the latest version
> of SQLite compared to prior versions. We hope that these queries would help
> further increase the utility of the regression test suite.

Thanks for the report.

Since there are already a bazillion fuzzers for SQLite, may I suggest
that you choose a more a more specific and descriptive name for your
fuzzer?  Perhaps "sql-perf-fuzz" or something similar - so that we
know that your fuzzer is targeting performance regressions?

>
> We are sharing four SQL queries that exhibit regressions in this report.
> Here’s an illustrative query:

I only got 3 SQL queries.  What am I missing?

Also, I got bisect results for all three problem that are different
from the results you report.  When I run bisect, I get the same result
for all three test cases:

   
https://sqlite.org/src/timeline?bid=y736b53f57fnbd49a8271dycb1511065dy6c6fb1c6eay30f08d5888y507c43537fy2c8769c69fn4371a0c46en4d0a949fd9n79c073878dy93386a7c97nd840e9bb02

So it was apparently a bug-fix that caused the performance decrease.
I have not looked into the details yet.  Perhaps there is an
alternative fix for the bug that does not cause unnecessary
performance loss.

-- 
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] Need advice: Some queries show performance regression

[Jinho Jung]

Hello,

We are developing a tool called sqlfuzz for automatically finding
performance regressions in SQLite. sqlfuzz performs mutational fuzzing to
generate SQL queries that take more time to execute on the latest version
of SQLite compared to prior versions. We hope that these queries would help
further increase the utility of the regression test suite.

We are sharing four SQL queries that exhibit regressions in this report.
Here’s an illustrative query:


EXAMPLE:

select ref_0.NO_O_ID
from
  main.NEW_ORDER as ref_0
where ref_0.NO_W_ID is not NULL
  and (EXISTS (
select 1
from (
  select 1
  from main.STOCK as ref_2
left join main.CUSTOMER as ref_3 on (ref_2.S_YTD = ref_3.C_ID)
  where ref_3.C_ZIP is not NULL) as subq_0
where (ref_0.NO_W_ID is not NULL)
  )
);

Time taken on SQLite v3.23.0:   73 (milliseconds)
Time taken on SQLite v3.27.2: 4955 (milliseconds)


Here are the steps for reproducing our observations:

[Our test environment]
* Ubuntu 16.04 machine "Linux sludge 4.4.0-116-generic #140-Ubuntu SMP Mon
Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux"
* Database: TPC-C benchmark

[Setup Test Environment]

1. build SQLite 3.27.2 (verion of Feb 2019)
  $ wget https://www.sqlite.org/2019/sqlite-src-3270200.zip
  $ unzip sqlite-src-3270200.zip
  $ mv sqlite-src-3270200 sqlite327
  $ cd sqlite327
  $ ./configure
  $ make
  $ cd ..

2. build SQLite 3.23.0 (verion of Apr 2018)
  $ wget https://www.sqlite.org/2018/sqlite-src-323.zip
  $ unzip sqlite-src-323.zip
  $ mv sqlite-src-323 sqlite323
  $ cd sqlite323
  $ ./configure
  $ make
  $ cd ..

3. download tpc-c for sqlite3 (scale-factor of 1)
  $ mkdir testcase
  $ cd testcase

  $ wget https://gts3.org/~/jjung/sqlite/tpcc_sqlite.tar.gz
  $ tar xzvf tpcc_sqlite.tar.gz

; download regression queries
  $ wget https://gts3.org/~/jjung/sqlite/report1.tar.gz
  $ tar xzvf report1.tar.gz
  $ cd ..

4. launch two SQLites
  - start
$ sqlite327/sqlite3 testcase/test.db
$ sqlite323/sqlite3 testcase/test.db

  - for each DB, set up timer
sqlite> .timer on

 - copy and paste extracted queries


Here’s the time taken to execute four SQL queries on old (v3.23) and newer
version (v3.27.2) of SQLite (in milliseconds). We also try bisecting to
know which commit activate the regression.

+--++
| Query|   Time |
+--++
| 1.sql (v3.23)|148 |
| 1.sql (v3.27.2)  |314 |
+--++
| 1-1.sql (v3.23)  |  1 |
| 1-1.sql (v3.27.2)| > 5min |
+--++
| 338.sql (v3.23)  | 73 |
| 338.sql (v3.27.2)|   4955 |
+--++

1) 1.sql shows x2.12 slow query execution.
 - bisect fossil commit: 4978ee8b54

2) 1-1.sql is slight modification from 1.sql. However, we observed much
slower execution speed.
 - bisect fossil commit: X (we couldn't bisect due to the long execution
time)

3) 338.sql shows x67 show query execution.
 - bisect fossil commit: 4978ee8b54

We would greatly appreciate feedback from the community regarding these
queries and are looking forward to improving the tool based on the
community’s feedback.

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

Re: [sqlite] Database Encryption (System.Data.SQLite)

[Paul]

As I was using the unchanged nuget package I assumed it would be a “default” 
encryption as it isn’t something I compiled or changed?

Tithras

Sent from my iPhone

> On 1 Apr 2019, at 15:33, Simon Slavin  wrote:
> 
>> On 1 Apr 2019, at 3:30pm, Mattock Paul  wrote:
>> 
>> Thanks, do we have any idea on what Algorithm is used?
> 
> Since more than one algorithm is available, this would be something selected 
> by the software you were using.  So I can only suggest you read the source 
> code for the bit of the software which created the database.
> 
> It might be possible for someone clever with encryption to work it out, given 
> a copy of your database, but that's not something I've ever tried.
> ___
> 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] importing a large TSV file

[David Raymond]

I believe it's basically saying that the way it's implemented, vacuum can't be 
rolled back, and therefore can't be done in the middle of a transaction, it has 
to be on its own.



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Monday, April 01, 2019 9:47 AM
To: SQLite mailing list
Subject: Re: [sqlite] importing a large TSV file

On 1 Apr 2019, at 2:41pm, Simon Slavin  wrote:

> BEGIN;
>... CREATE all your INDEXes ...
>VACUUM; -- optional
> END;

Apologies.  The documentation says

"A VACUUM will fail if there is an open transaction"

I'm not sure whether this means that VACUUM must be outside the transaction, 
like the particular PRAGMA I used.  So it would better to move the VACUUM after 
the END.
___
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] UPSERT with multiple constraints

[David Raymond]

Adding on to the other comments, I think the reason you can't do this is that 
each constraint could be violated by different rows.

So with...

create table foo (a int unique, b int unique);
insert into foo values (1, 3), (2, 2), (3, 1);

...then when trying...

insert into foo values (1, 2) on conflict (a) or on conflict (b) do update ...;

...there are conflicts on both a and b, and each is from a different row. Do we 
update the (1, 3) row because of the (a) conflict, or update the (2, 2) row 
because of the (b) conflict? Both? None? The first based on the order they're 
written in the statement?

So I think that ambiguity is why you get to pick one and only one constraint 
for an upsert.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Kurz
Sent: Wednesday, March 27, 2019 10:41 AM
To: SQLite mailing list
Subject: [sqlite] UPSERT with multiple constraints

Dear all,

I have a table with multiple (in this case 2) UNIQUE constraints:

UNIQUE (col1, col2)
UNIQUE (col1, col3, col4, col5)

Is it possible to use UPSERT twice? I have already tried some statements, but 
neither of these were successful. This is what I want to achieve:

INSERT INTO ... ON CONFLICT DO UPDATE SET ...

So the update should occur no matter which UNIQUE-constraint would be violated 
by the insert.

I've also tried ON CONFLICT (col1, col2, col3, col4, col5) which is rejected 
("does not match any UNIQUE constraint"). The error message is perfectly 
correct, but doesn't solve my problem ;-)

So what I'm looking for is some kind of "ON CONFLICT (col1, col2) OR CONFLICT 
(col1, col3, col4, col5) DO UPDATE".

Any hints for me?

Kind regards,
Thomas

___
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] Database Encryption (System.Data.SQLite)

[Simon Slavin]

On 1 Apr 2019, at 3:30pm, Mattock Paul  wrote:

> Thanks, do we have any idea on what Algorithm is used?

Since more than one algorithm is available, this would be something selected by 
the software you were using.  So I can only suggest you read the source code 
for the bit of the software which created the database.

It might be possible for someone clever with encryption to work it out, given a 
copy of your database, but that's not something I've ever tried.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Encryption (System.Data.SQLite)

[Mattock Paul]

Simon,
Thanks, do we have any idea on what Algorithm is used?

Tithras

> On 01 April 2019 at 14:28 Simon Slavin  wrote:
> 
> 
> On 1 Apr 2019, at 1:15pm, Mattock Paul  wrote:
> 
> > Just to confirm I am using the nuget package (System.Data.SQLite v1.0.109.2)
> 
> This uses PCL Crypto, which in turn accesses crypto implemented in PCL itself 
> rather than implmenting its own.  A list of crypto methods it supports, 
> tabled against OS, can be found here:
> 
> 
> 
> Also see the 'Legal Key Sizes' link on that page.
> 
> So the bad part is that your cryto is done entirely outside SQLite so we 
> don't know much about it.  But the good part is that there is a source that 
> does.
> ___
> 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] Documentation enhancement request: VACUUM

[Simon Slavin]

The documentation for VACUUM says

"A VACUUM will fail if there is an open transaction"

Could this statement be changed to make it clear whether this refers to other 
connections having an open transaction, and whether it means that VACUUM fill 
fail if it itself is inside a transaction ?

I seem to remember that discussion on this list revealed that this matter has 
changed since the VACUUM command was first implemented.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing a large TSV file

[Simon Slavin]

On 1 Apr 2019, at 2:41pm, Simon Slavin  wrote:

> BEGIN;
>... CREATE all your INDEXes ...
>VACUUM; -- optional
> END;

Apologies.  The documentation says

"A VACUUM will fail if there is an open transaction"

I'm not sure whether this means that VACUUM must be outside the transaction, 
like the particular PRAGMA I used.  So it would better to move the VACUUM after 
the END.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing a large TSV file

[Simon Slavin]

On 1 Apr 2019, at 12:14pm, Gert Van Assche  wrote:

> I need to create an SQLite db from a large TSV file. (30 GB)
> Are there any setting I can give to the db so I can speed up the import?

If you're doing it using the SQLite CLI tool, then just rely on the tool to do 
it in the most convenient manner.

If you're doing it in your own code, here is the pattern which this list thinks 
is fastest:

PRAGMA foreign_keys = OFF;
BEGIN;
DROP TABLE IF EXISTS ...
CREATE TABLE ...
END;
BEGIN;
... import all your data into the table ...
END;
BEGIN;
... CREATE all your INDEXes ...
VACUUM; -- optional
END;
PRAGMA foreign_keys = ON;

The VACUUM step isn't really needed.  It might speed up future access or reduce 
the filespace used, especially for big databases.  In case you wonder later, 
SQLite is easily able to handle a table with 30GB of data.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Encryption (System.Data.SQLite)

[Simon Slavin]

On 1 Apr 2019, at 1:15pm, Mattock Paul  wrote:

> Just to confirm I am using the nuget package (System.Data.SQLite v1.0.109.2)

This uses PCL Crypto, which in turn accesses crypto implemented in PCL itself 
rather than implmenting its own.  A list of crypto methods it supports, tabled 
against OS, can be found here:



Also see the 'Legal Key Sizes' link on that page.

So the bad part is that your cryto is done entirely outside SQLite so we don't 
know much about it.  But the good part is that there is a source that does.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to transpose a table using SQL?

[Simon Slavin]

On 1 Apr 2019, at 10:18am, Dominique Devienne  wrote:

> I can't seem to find that one, but I found another here:
> https://metacpan.org/pod/SQLite::VirtualTable::Pivot

I looked at that one, but it doesn't do what OP wants, which is to swap rows 
and columns without the programmer having to specify anything.

It would be possible to implement the transform pivot as a virtual table in any 
language.  But you do have to do the work: the feature doesn't come with 
SQLite.  And if you're writing code you might as well do it in your program.

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

Re: [sqlite] importing a large TSV file

[Donald Griggs]

I believe it's also helpful to avoid creating indexes (and enforcing
foreign keys) until after the import.

On Mon, Apr 1, 2019 at 7:15 AM Gert Van Assche  wrote:

> Hi all,
>
> I need to create an SQLite db from a large TSV file. (30 GB)
> Are there any setting I can give to the db so I can speed up the import?
>
> thank you
>
> Gert
> ___
> 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] Database Encryption (System.Data.SQLite)

[Mattock Paul]

Jim,
Thanks for the information.

Just to confirm I am using the nuget package (System.Data.SQLite v1.0.109.2) 
and my sqlite database is encrypted without any additional modules associated 
with my release. A code snippet of the section which handles the initial 
database encryption is as follows:

using (SQLiteConnection localDBConnection = new SQLiteConnection(connection))
{
 localDBConnection.SetPassword("password");
 localDBConnection.Open();

 using (SQLiteCommand command = new SQLiteCommand(localDBConnection))
 {
  command.CommandText = createTableSNBuild;
  command.ExecuteNonQuery();
 }
}

Regards,
Tithras

> On 01 April 2019 at 12:52 Jim Borden  wrote:
> 
> 
> From looking at the System.Data.SQLite source (someone please correct me if I 
> am wrong)
> 
> It would use whatever cipher was provided to it via the native library that 
> it was deployed with.  It's designed with sqlite encryption extension in mind 
> but I suppose in theory it would work with any implementation that properly 
> implements the sqlite3_key APIs / PRAGMAs.  As far as I can tell it is not a 
> foregone conclusion in the C# as to what algorithm is used.
> 
> The one on Nuget just ships with the vanilla sqlite which has no encryption 
> support.  Decompiling and searching for the sqlite3_key binding shows that it 
> is not present in the library (which makes sense since it is guarded by an 
> #if in the source base)
> 
> On 2019/04/01 18:27, "sqlite-users on behalf of Mattock Paul" 
>  pmatt...@ntlworld.com> wrote:
> 
> All,
> 
> Would anyone be able to confirm what cipher is used for encrypting an 
> SQLite database when password="" is used?
> 
> 
> I have seen old posts online which state its 128bit but assume this is 
> now wrong and am after completing a design document which requires I state 
> the encryption level.
> 
> 
> Regards,
> 
> Tithras
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> Privacy Policy
> Marketing 
> Preferences
> ___
> 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] Database Encryption (System.Data.SQLite)

[Jim Borden]

From looking at the System.Data.SQLite source (someone please correct me if I 
am wrong)

It would use whatever cipher was provided to it via the native library that it 
was deployed with.  It's designed with sqlite encryption extension in mind but 
I suppose in theory it would work with any implementation that properly 
implements the sqlite3_key APIs / PRAGMAs.  As far as I can tell it is not a 
foregone conclusion in the C# as to what algorithm is used.

The one on Nuget just ships with the vanilla sqlite which has no encryption 
support.  Decompiling and searching for the sqlite3_key binding shows that it 
is not present in the library (which makes sense since it is guarded by an #if 
in the source base)

On 2019/04/01 18:27, "sqlite-users on behalf of Mattock Paul" 
 wrote:

All,

Would anyone be able to confirm what cipher is used for encrypting an 
SQLite database when password="" is used?


I have seen old posts online which state its 128bit but assume this is now 
wrong and am after completing a design document which requires I state the 
encryption level.


Regards,

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



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

Re: [sqlite] importing a large TSV file

[Domingo Alvarez Duarte]

Hello Gert !

I normally do this (be aware that if there is a power outage the 
database is screwed):


===

PRAGMA synchronous = OFF;

begin;

--processing here

commit;

PRAGMA synchronous = ON;

===

Cheers !

On 1/4/19 13:14, Gert Van Assche wrote:

Hi all,

I need to create an SQLite db from a large TSV file. (30 GB)
Are there any setting I can give to the db so I can speed up the import?

thank you

Gert
___
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] importing a large TSV file

[Gert Van Assche]

Hi all,

I need to create an SQLite db from a large TSV file. (30 GB)
Are there any setting I can give to the db so I can speed up the import?

thank you

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

Re: [sqlite] Drop table error with existing views

[Domingo Alvarez Duarte]

Hello Graham !

Thank you so much !

With this pragma I can continue to solve my problem as before.

Cheers !

On 1/4/19 12:42, Graham Holden wrote:

PRAGMA legacy_alter_table=ON

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

Re: [sqlite] Drop table error with existing views

[Graham Holden]

See https://www.sqlite.org/lang_altertable.html ... the ALTER TABLE
command, by default, now alters references to it in triggers/views.
To get the old behaviour, which I believe you need, use:

PRAGMA legacy_alter_table=ON

Graham

Monday, April 01, 2019, 11:23:45 AM, Domingo Alvarez Duarte 
 wrote:

> Hello !

> I'm using sqlite3 for a long time and I used to be able to drop tables 
> that have views without error before but now it seems that it is not 
> possible anymore.

> I mainly use it to restructure tables (add/move/remove fields) with 
> something like this:

> 

> PRAGMA foreign_keys=OFF;

> BEGIN;

> CREATE TABLE "banks___new"(
>      id integer primary key,
>      account_id integer references ledger_accounts(id),
>      name varchar not null collate nocase unique,
>      new_field integer, -- for example here I'm adding a new 
> field
>      notes text collate nocase
> );

> INSERT INTO "banks___new"(
>      "id",
>      "account_id",
>      "name",
>      "notes"
>      )
> SELECT
>      "id",
>      "account_id",
>      "name",
>      "notes"
> FROM "banks";

> DROP TABLE "banks";

> ALTER TABLE "banks___new" RENAME TO "banks";

> PRAGMA foreign_key_check;

> COMMIT;

> PRAGMA foreign_keys=ON;



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

[sqlite] Drop table error with existing views

[Domingo Alvarez Duarte]

Hello !

I'm using sqlite3 for a long time and I used to be able to drop tables 
that have views without error before but now it seems that it is not 
possible anymore.


I mainly use it to restructure tables (add/move/remove fields) with 
something like this:




PRAGMA foreign_keys=OFF;

BEGIN;

CREATE TABLE "banks___new"(
    id integer primary key,
    account_id integer references ledger_accounts(id),
    name varchar not null collate nocase unique,
    new_field integer, -- for example here I'm adding a new 
field

    notes text collate nocase
);

INSERT INTO "banks___new"(
    "id",
    "account_id",
    "name",
    "notes"
    )
SELECT
    "id",
    "account_id",
    "name",
    "notes"
FROM "banks";

DROP TABLE "banks";

ALTER TABLE "banks___new" RENAME TO "banks";

PRAGMA foreign_key_check;

COMMIT;

PRAGMA foreign_keys=ON;



As I said before it use to work in previous versions of sqlite3 but now 
it seems to not allow to drop tables that has views on then.


How can something like the above be achieved now without need to drop 
and recreate the views (can be several views), any new pragma that I'm 
not aware off ?


Cheers !

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

[sqlite] Database Encryption (System.Data.SQLite)

[Mattock Paul]

All,

Would anyone be able to confirm what cipher is used for encrypting an SQLite 
database when password="" is used?


I have seen old posts online which state its 128bit but assume this is now 
wrong and am after completing a design document which requires I state the 
encryption level.


Regards,

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

Re: [sqlite] Is it possible to transpose a table using SQL?

[Dominique Devienne]

On Mon, Apr 1, 2019 at 7:15 AM Shane Dev  wrote:

> [...]. By "dynamically changing table", I meant the number of columns and
> rows could could change
> after the dependant view was created. it appears this is impossible using
> only SQL
>

It's possible using a virtual table, which years ago a colleague used in
our app.
I can't seem to find that one, but I found another here:
https://metacpan.org/pod/SQLite::VirtualTable::Pivot

I don't see anything related to pivot/transpose in either usual locations
below:
https://www.sqlite.org/src/dir?ci=6cf8b18ec20f11c2=ext/misc
https://www.sqlite.org/contrib
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users