Re: [sqlite] An issue with System.Data.SQLite

[Joe Mistachkin]

Aleksey Lulchenko wrote:
>
> Please, give me the clues what is going wrong, if you can. I think it
> is a typical bug in many situations. 
> 

Could you please show us the schema of the table (or tables) involved?

--
Joe Mistachkin 

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

Re: [sqlite] Suspected bug: parse error depending on platform

[Yannick Duchêne]

Ok, I see.

So finally using `sqlite3.sqlite_version`, it says version 3.8.11 for the
Windows version and 3.11.0 for the Ubuntu version.

May be I could solve it changing the sqlite3.dll which is in the isolated
Python environment. I will try to build it in the virtual machine.

Thanks for having pointed out my error …



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

[Paul Wise]

On Fri, 2017-12-22 at 20:09 -0500, Richard Hipp wrote:

> Nevertheless, we will investigate from the SQLite side,
> just in case.

If you need any information from the core dump, please let me know
which gdb commands to run. I still have one available from the latest
crash and have marked it immutable to prevent it from being removed
until your investigation is complete.

-- 
bye,
pabs

http://bonedaddy.net/pabs3/

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] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

[Paul Wise]

On Fri, 2017-12-22 at 20:09 -0500, Richard Hipp wrote:

> There are no known issues like this with any recent version of SQLite.
> Usually these kinds of things end up being heap corruption in the
> application.  Nevertheless, we will investigate from the SQLite side,
> just in case.

I see, thanks for the information and investigation.

> "SQLite 3.21.0-1" is not an official SQLite product.  It must be a
> version of SQLite that has been customized by Debian.  Where can we
> get a copy of Debian's customized code?

You can browse the Debian source code here:

https://sources.debian.org/src/sqlite3/3.21.0-1/

You can browse the Debian patches here:

https://sources.debian.org/patches/sqlite3/3.21.0-1/

You can download the Debian source code here (debian.tar has patches):

http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0-1.dsc
http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0-1.debian.tar.xz
http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0.orig.tar.xz
http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0.orig-www.tar.xz

You can view the build logs for the package here, in the Status column:

https://buildd.debian.org/status/package.php?p=sqlite3

Note that the crash was on amd64 and logs are not available for that.

-- 
bye,
pabs

http://bonedaddy.net/pabs3/

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] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

[Richard Hipp]

On 12/22/17, Paul Wise  wrote:
> Hi all,
>
> I got a couple of random crashes GNOME's tracker-store daemon that
> appear to be related to sqlite3. I use 3.21.0-1 from Debian buster.
>
> Is this a bug in sqlite3 or is it caused by data corruption?
>

There are no known issues like this with any recent version of SQLite.
Usually these kinds of things end up being heap corruption in the
application.  Nevertheless, we will investigate from the SQLite side,
just in case.

"SQLite 3.21.0-1" is not an official SQLite product.  It must be a
version of SQLite that has been customized by Debian.  Where can we
get a copy of Debian's customized code?
-- 
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] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

[Paul Wise]

Hi all,

I got a couple of random crashes GNOME's tracker-store daemon that
appear to be related to sqlite3. I use 3.21.0-1 from Debian buster.

https://bugzilla.gnome.org/show_bug.cgi?id=791243

The short backtraces are available here:

https://bugzilla.gnome.org/page.cgi?id=traceparser/trace.html_id=238220
https://bugzilla.gnome.org/page.cgi?id=traceparser/trace.html_id=238281

The full backtraces are available here:

https://bugzilla.gnome.org/attachment.cgi?id=365000
https://bugzilla.gnome.org/attachment.cgi?id=365891

Is this a bug in sqlite3 or is it caused by data corruption?

PS: please either CC me in response or post on the GNOME bug.

-- 
bye,
pabs

http://bonedaddy.net/pabs3/

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] An issue with System.Data.SQLite

[Barry]

Do you allow the entity framework to create your database, or do you
do it yourself?

You need text affinity on the columns in question. Then any numbers
will be stored as text, not numbers.

See:
https://sqlite.org/datatype3.html

Particularly:

3.1. Determination Of Column Affinity

The affinity of a column is determined by the declared type of the
column, according to the following rules in the order shown:

1) If the declared type contains the string "INT" then it is assigned
INTEGER affinity.

2) If the declared type of the column contains any of the strings
"CHAR", "CLOB", or "TEXT" then that column has TEXT affinity.

On 21 December 2017 at 23:06, Алексей Люльченко
 wrote:
> Dear Sir!
>
> I would like to ask you to help me with an issue which appears while I use
> System.Data.SQLite database engine.
>
> I use .net framework 4.0.0, EntityFramework 6.2.0 and SQLite 1.0.106. I
> have an entity with properties of string type. This entity is mapped
> to my database table which has the same structure (columns names and data
> types). When I write and read back data to and from the database, all is OK
> while these columns have strings like "abcdef" or "123abc". But when I try
> to write a string like "1234" it is written to the database table like an
> int data type value. I know that it is a normal behavior and it is not an
> issue (https://sqlite.org/faq.html#q3). But I get the
> "System.InvalidCastException" when I try to read back the row from the
> database to my entity because the datatypes are different (the entity has
> the property of string type but the database table now has the cell of int
> datatype).
>
> Please, give me the clues what is going wrong, if you can. I think it is a
> typical bug in many situations.
>
> Thank you in advance!
>
> Yours Faithfully,
>
> Aleksey Lulchenko, .Net developer at "TDabbat".
>  Saratov, Russia
> ___
> 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] Suspected bug: parse error depending on platform

[Keith Medcalf]

>The simple example below works as expected on Ubuntu, but fails on
>Windows 7. Both using Python3.5 and Sqlite 2.6.0, so that’s not a 
>version issue.

2.6.0 is not the version of SQLite3, it is the version of the pysqlite2 wrapper 
module.  When that third party package was incorporated into the Python 
distribution its name was changed to the package sqlite3.

The "package (wrapper)" version is sqlite3.version or sqlite3.version_info
The "SQLite3 (database engine)" versions is sqlite3.sqlite_version or 
sqlite3.sqlite_version_info

So probably you have an "old as the hills" version of SQLite3 (the database 
engine -- the thing that actually does anything) on Windows 7.

---
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Thank you Peter!

I like sqlite so much and I think we all benefit if
errors are fixed. I see I was not very clear with my
first post. Will do better next time. Yes would
be nice if people would try to understand first
not just think on title text.

Talking about sqlite I use it for web development,
desktop apps. I also created tool or gui sqlite manager
and will try to sell it in near future. I needed very
good tool because I work with sqlite every day. It
is just for windows platform. I support different
datetime formats, blobs, compress etc. See this page:

http://www.arsistemi.si/izdelki/sqlite-4-all.html

Every report is created with sqlite tables. I read
data from different RDBMS to sqlite and then work
with data as needed.

petern je 22.12.2017 ob 23:19 napisal:

Radovan.  Thank you for sticking to your guns.  Your appeal to expected
behavior under other DB engines was also a very good post.  I see the usual
suspects of the echo chamber uselessly piled on against you to clog the
forum nevertheless.  That happens too frequently.

BTW, here is a simpler test suite which pinpoints the problem:

SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
d
1

CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
c
1

CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c);
SELECT * FROM test;
d
1

And still one more that illustrates the contradictory and unstable
behavior. Use '*' instead of 'd' and it works as expected.

CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
d
1

Obviously the most immediate well formed alias 'd' should be carried
through irrespective of nesting.

FYI, there was a checkin just now with some changes to track intermediate
aliases:

http://www.sqlite.org/src/info/5efd854fe2147033

Peter

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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[petern]

Radovan.  Thank you for sticking to your guns.  Your appeal to expected
behavior under other DB engines was also a very good post.  I see the usual
suspects of the echo chamber uselessly piled on against you to clog the
forum nevertheless.  That happens too frequently.

BTW, here is a simpler test suite which pinpoints the problem:

SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
d
1

CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
c
1

CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c);
SELECT * FROM test;
d
1

And still one more that illustrates the contradictory and unstable
behavior. Use '*' instead of 'd' and it works as expected.

CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
d
1

Obviously the most immediate well formed alias 'd' should be carried
through irrespective of nesting.

FYI, there was a checkin just now with some changes to track intermediate
aliases:

http://www.sqlite.org/src/info/5efd854fe2147033

Peter


On Fri, Dec 22, 2017 at 11:34 AM, Radovan Antloga 
wrote:

> Thank you Klaus!
>
> Klaus Maas je 22.12.2017 ob 20:30 napisal:
>
>> Radovan is correct.
>> Executing the same command sequence in version 3.11.0 and 3.21.0 results
>> in different column names for table test2.
>> (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0)
>> I marked the results with '<='
>> Klaus
>>
>>
>> SQLite version 3.11.0 2016-02-15 17:29:24
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .header on
>> sqlite>
>> sqlite> create table test(a int, b int);
>> sqlite> insert into test values (1,1);
>> sqlite>
>> sqlite> select d from (select c as d from (select a as c from test));
>> d
>> 1
>> sqlite>
>> sqlite> create table test2 as
>>...> select d from (select c as d from (select a as c from test));
>> sqlite> SELECT * FROM test2;
>> d  <=
>> 1
>> sqlite>
>>
>>
>>
>> SQLite version 3.21.0 2017-10-24 18:55:49
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .header on
>> sqlite> create table test(a int, b int);
>> sqlite> insert into test values (1,1);
>> sqlite>
>> sqlite> select d from (select c as d from (select a as c from test));
>> d
>> 1
>> sqlite> create table test2 as
>>...> select d from (select c as d from (select a as c from test));
>> sqlite> SELECT * FROM test2;
>> a   <=
>> 1
>> sqlite>
>>
>> email signature Klaus Maas
>> 
>> On 2017-12-22 20:11, Radovan Antloga wrote:
>>
>>> Just try this sql-s:
>>>
>>> create table test(a int, b int);
>>> insert into test values (1,1);
>>>
>>> select d from (select c as d from (select a as c from test));
>>>
>>> will return name d.
>>>
>>> create table test2 as
>>> select d from (select c as d from (select a as c from test));
>>>
>>> will create table test2 with column name a.
>>>
>>> with PostgreSQL I get table test2 with name d.
>>>
>>> Thank you very much for your time!
>>>
>>>
>>> Richard Hipp je 22.12.2017 ob 20:06 napisal:
>>>
 On 12/22/17, Radovan Antloga  wrote:

> My point is you do not have to change anything regards
> to how select works. SELECT statement is working just
> great or OK or fine.
>
> CREATE TABLE AS
> SELECT 
>
> does not give same name as SELECT does.
>
> SELECT give name d
>
> CREATE TABLE AS SELECT
> create table with name a instead of d.
>
 Can you provide a simple test case for this behavior?


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

[sqlite] Suspected bug: parse error depending on platform

[Yannick Duchêne]

Hello,

I’em facing an issue developing a Python+SQlite3 application on Ubuntu, to
be shipped on Windows 7.

The simple example below works as expected on Ubuntu, but fails on Windows
7. Both using Python3.5 and Sqlite 2.6.0, so that’s not a version issue.

import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("create view v(c) as values(1);")

No error on Ubuntu but fails on Windows 7 with this message:

sqlite3.OperationalError: near "(": syntax error

If that matters for anything, I’m testing the application on Windows 7 32
bits running in Virtualbox, using the image provided by Microsoft. The
Python3.5 I use on Ubuntu is in a vitualenv and the one for Windows is from
a bundle archive provided by the Python community website. This archive
contains an isolated Python environment.

Someone else encountered a similar issue one year ago, in the context of a
NodeJS application, here: https://github.com/brianc/node-sql/issues/314

For the personal story, the real (above is a sample for testing the issue)
view table SQLite don’t want to create is to be used by a trigger running
some checks. So I have to drop it. It does not prevent the application from
running, but it will be less reliably without this trigger.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Thank you Klaus!

Klaus Maas je 22.12.2017 ob 20:30 napisal:

Radovan is correct.
Executing the same command sequence in version 3.11.0 and 3.21.0 
results in different column names for table test2.

(Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0)
I marked the results with '<='
Klaus


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite>
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite>
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
d  <=
1
sqlite>



SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
a   <=
1
sqlite>

email signature Klaus Maas

On 2017-12-22 20:11, Radovan Antloga wrote:

Just try this sql-s:

create table test(a int, b int);
insert into test values (1,1);

select d from (select c as d from (select a as c from test));

will return name d.

create table test2 as
select d from (select c as d from (select a as c from test));

will create table test2 with column name a.

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

On 12/22/17, Radovan Antloga  wrote:

My point is you do not have to change anything regards
to how select works. SELECT statement is working just
great or OK or fine.

CREATE TABLE AS
SELECT 

does not give same name as SELECT does.

SELECT give name d

CREATE TABLE AS SELECT
create table with name a instead of d.

Can you provide a simple test case for this behavior?



___
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

THANK YOU!

Richard Hipp je 22.12.2017 ob 20:29 napisal:

Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511



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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Klaus Maas]

Radovan is correct.
Executing the same command sequence in version 3.11.0 and 3.21.0 results 
in different column names for table test2.

(Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0)
I marked the results with '<='
Klaus


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite>
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite>
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
d  <=
1
sqlite>



SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
a   <=
1
sqlite>

email signature Klaus Maas

On 2017-12-22 20:11, Radovan Antloga wrote:

Just try this sql-s:

create table test(a int, b int);
insert into test values (1,1);

select d from (select c as d from (select a as c from test));

will return name d.

create table test2 as
select d from (select c as d from (select a as c from test));

will create table test2 with column name a.

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

On 12/22/17, Radovan Antloga  wrote:

My point is you do not have to change anything regards
to how select works. SELECT statement is working just
great or OK or fine.

CREATE TABLE AS
SELECT 

does not give same name as SELECT does.

SELECT give name d

CREATE TABLE AS SELECT
create table with name a instead of d.

Can you provide a simple test case for this behavior?



___
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] Changed behaviour or bug using field alias in 3.21.0

[Richard Hipp]

Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511

-- 
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Just try this sql-s:

create table test(a int, b int);
insert into test values (1,1);

select d from (select c as d from (select a as c from test));

will return name d.

create table test2 as
select d from (select c as d from (select a as c from test));

will create table test2 with column name a.

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

On 12/22/17, Radovan Antloga  wrote:

My point is you do not have to change anything regards
to how select works. SELECT statement is working just
great or OK or fine.

CREATE TABLE AS
SELECT 

does not give same name as SELECT does.

SELECT give name d

CREATE TABLE AS SELECT
create table with name a instead of d.

Can you provide a simple test case for this behavior?



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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

What is correct?

Do you understand what I write?
What is my point? Tell me please.

David Raymond je 22.12.2017 ob 20:04 napisal:

Correct.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Friday, December 22, 2017 1:45 PM
To: SQLite mailing list
Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

On 12/22/17, Radovan Antloga  wrote:

In my example I have AS clause so rule 1.

You did not understand my question.

I think what you are asking for is that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.


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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Richard Hipp]

On 12/22/17, Radovan Antloga  wrote:
> My point is you do not have to change anything regards
> to how select works. SELECT statement is working just
> great or OK or fine.
>
> CREATE TABLE AS
> SELECT 
>
> does not give same name as SELECT does.
>
> SELECT give name d
>
> CREATE TABLE AS SELECT
> create table with name a instead of d.

Can you provide a simple test case for this behavior?

-- 
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] Changed behaviour or bug using field alias in 3.21.0

[David Raymond]

Correct.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Friday, December 22, 2017 1:45 PM
To: SQLite mailing list
Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

On 12/22/17, Radovan Antloga  wrote:
> In my example I have AS clause so rule 1.

You did not understand my question.

I think what you are asking for is that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.
-- 
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] An issue with System.Data.SQLite

[Алексей Люльченко]

Dear Sir!

I would like to ask you to help me with an issue which appears while I use
System.Data.SQLite database engine.

I use .net framework 4.0.0, EntityFramework 6.2.0 and SQLite 1.0.106. I
have an entity with properties of string type. This entity is mapped
to my database table which has the same structure (columns names and data
types). When I write and read back data to and from the database, all is OK
while these columns have strings like "abcdef" or "123abc". But when I try
to write a string like "1234" it is written to the database table like an
int data type value. I know that it is a normal behavior and it is not an
issue (https://sqlite.org/faq.html#q3). But I get the
"System.InvalidCastException" when I try to read back the row from the
database to my entity because the datatypes are different (the entity has
the property of string type but the database table now has the cell of int
datatype).

Please, give me the clues what is going wrong, if you can. I think it is a
typical bug in many situations.

Thank you in advance!

Yours Faithfully,

Aleksey Lulchenko, .Net developer at "TDabbat".
 Saratov, Russia
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

My point is you do not have to change anything regards
to how select works. SELECT statement is working just
great or OK or fine.

CREATE TABLE AS
SELECT 

does not give same name as SELECT does.

SELECT give name d

CREATE TABLE AS SELECT
create table with name a instead of d.

Richard Hipp je 22.12.2017 ob 19:45 napisal:

On 12/22/17, Radovan Antloga  wrote:

In my example I have AS clause so rule 1.

You did not understand my question.

I think what you are asking for is that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.


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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Richard Hipp]

On 12/22/17, Radovan Antloga  wrote:
> In my example I have AS clause so rule 1.

You did not understand my question.

I think what you are asking for is that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.
-- 
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

In my example I have AS clause so rule 1.

sqlite select statement is correct or name
is correct.

sqlite create table as statement create table
with different name that select statement return
Problem is different result or name.


Richard Hipp je 22.12.2017 ob 19:27 napisal:

These are the rules that SQLite uses to name a column in the result set:

(1) If there is an AS clause, use it.

(2) If the result-set value comes from a table column (even
indirectly, such as through a subquery, but not if the value is
altered by an expression) then use the name of the column as it
appears in the original CREATE TABLE statement for the table.

(3) Otherwise, name the result-set column using the SQL text in the
SELECT statement.

What set of rules are you proposing that we should use instead?



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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Richard Hipp]

These are the rules that SQLite uses to name a column in the result set:

(1) If there is an AS clause, use it.

(2) If the result-set value comes from a table column (even
indirectly, such as through a subquery, but not if the value is
altered by an expression) then use the name of the column as it
appears in the original CREATE TABLE statement for the table.

(3) Otherwise, name the result-set column using the SQL text in the
SELECT statement.

What set of rules are you proposing that we should use instead?

-- 
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Just tried my example with PostgreSQL that have
create table as statement.

It work as expected. It creates table test2 with
column name d. sqlite3 creates table with column
name a.

sqlite3 try to mimic postgresql but in this example
is not.

Simon Slavin je 22.12.2017 ob 17:58 napisal:


On 22 Dec 2017, at 4:50pm, Radovan Antloga  wrote:


select d from (select c AS d from (select a AS c from test));

I get d as column name. If I create table with
create table as I get a as column name.

As I wrote, you did not specify AS for d, so you cannot depend on a column name.

If you want to know what the column will be called, specify AS:

select d AS myColumnNameHere from (select c AS d from (select a AS c from 
test));

Now you know what the column will be called.

Simon.
___
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

What?

I have to write select d as d. You are not
serious. You dont understand what I wrote.

I get select correct but create table as
does not have same name as select has. Why
different result?

If select gives some name I don't care what
I expect create table as give me same name.
Is this so hard to understand.

Simon Slavin je 22.12.2017 ob 17:58 napisal:


On 22 Dec 2017, at 4:50pm, Radovan Antloga  wrote:


select d from (select c AS d from (select a AS c from test));

I get d as column name. If I create table with
create table as I get a as column name.

As I wrote, you did not specify AS for d, so you cannot depend on a column name.

If you want to know what the column will be called, specify AS:

select d AS myColumnNameHere from (select c AS d from (select a AS c from 
test));

Now you know what the column will be called.

Simon.
___
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Thank you very much to understand my point!
This is exactly what I think. I have name
and select is working ok. It gives me my
name but create table as not.

David Raymond je 22.12.2017 ob 17:59 napisal:

I think the underlying feeling here is that if you're not doing anything 
tricky, and just straight up referencing a column name, that it should be fair 
to assume that the result will have that column name.

I.e. with
create table foo (bar);
if I run
select bar from foo;
I should be able to assume the result column is named "bar".

So...
select a as c from test
is certain to have a field named "c",

select c as d from (something with a field named "c")
is certain to have a field named "d",

but select d from (something with a field named "d")
doesn't give a field named "d"?

I'd say it's fair to be confused at that.
I definitely don't want to have to do queries in the form of "select foo as foo, bar 
as bar, baz as baz..."

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, December 22, 2017 11:34 AM
To: SQLite mailing list
Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0



On 22 Dec 2017, at 3:04pm, Tim Streater  wrote:


2) I don't see this issue mentioned when I read the PHP documentation about 
their SQLite interface, nor do I see it in the Xojo docs about *their* 
interface either. I assume their interfaces are not rewriting SELECT statements 
to include AS for every column selected, so should they be warning their users 
about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

Simon.
___
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] Changed behaviour or bug using field alias in 3.21.0

[David Raymond]

I think the underlying feeling here is that if you're not doing anything 
tricky, and just straight up referencing a column name, that it should be fair 
to assume that the result will have that column name.

I.e. with
create table foo (bar);
if I run
select bar from foo;
I should be able to assume the result column is named "bar".

So...
select a as c from test
is certain to have a field named "c",

select c as d from (something with a field named "c")
is certain to have a field named "d",

but select d from (something with a field named "d")
doesn't give a field named "d"?

I'd say it's fair to be confused at that.
I definitely don't want to have to do queries in the form of "select foo as 
foo, bar as bar, baz as baz..."

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, December 22, 2017 11:34 AM
To: SQLite mailing list
Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0



On 22 Dec 2017, at 3:04pm, Tim Streater  wrote:

> 2) I don't see this issue mentioned when I read the PHP documentation about 
> their SQLite interface, nor do I see it in the Xojo docs about *their* 
> interface either. I assume their interfaces are not rewriting SELECT 
> statements to include AS for every column selected, so should they be warning 
> their users about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

Simon.
___
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] Changed behaviour or bug using field alias in 3.21.0

[Simon Slavin]

On 22 Dec 2017, at 4:50pm, Radovan Antloga  wrote:

> select d from (select c AS d from (select a AS c from test));
> 
> I get d as column name. If I create table with
> create table as I get a as column name.

As I wrote, you did not specify AS for d, so you cannot depend on a column name.

If you want to know what the column will be called, specify AS:

select d AS myColumnNameHere from (select c AS d from (select a AS c from 
test));

Now you know what the column will be called.

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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Sorry but this is not related to my example.
I have AS in inner select. My select return
name as specified.

Problem I have is with create table as where
name is changed.

I give example like this:

select d from (select c AS d from (select a AS c from test));

I get d as column name. If I create table with
create table as I get a as column name.


Simon Slavin je 22.12.2017 ob 17:33 napisal:


On 22 Dec 2017, at 3:04pm, Tim Streater  wrote:


2) I don't see this issue mentioned when I read the PHP documentation about 
their SQLite interface, nor do I see it in the Xojo docs about *their* 
interface either. I assume their interfaces are not rewriting SELECT statements 
to include AS for every column selected, so should they be warning their users 
about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

Simon.
___
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] journal mode and transactions

[David Raymond]

In memory databases can have rollbacks if they're using journal mode memory. 
The last sentence there is a little confusing...

"Note that the journal_mode for an in-memory database is either MEMORY or OFF 
and can not be changed to a different value. An attempt to change the 
journal_mode of an in-memory database to any setting other than MEMORY or OFF 
is ignored. Note also that the journal_mode cannot be changed while a 
transaction is active"

...it seems to say you can't change it, but then says if you do change it...

For the transaction benefits you might be right, or it might be the case where 
having them in a transaction saves some disk flushes while it's going on. I.e. 
in both autocommit and journal_mode off you'll be making changes directly to 
the main file with no rollback journals. But with autocommit it'll be syncing 
the file after each statement whereas with the transaction it might wait until 
the commit to sync. Not sure on that though.

An in memory database with journal_mode off I'm pretty sure will see no 
benefits from transactions.

These are my edumacated guesses anyway. Please trust the actual experts more 
than me.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Friday, December 22, 2017 11:10 AM
To: SQLite mailing list
Subject: [sqlite] journal mode and transactions

The transactions documentation (https://sqlite.org/lang_transaction.html) states

   >If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal 
file) then the behavior of the ROLLBACK command is undefined.

Does that correspondingly mean there's no performance benefit to wrapping 
operations in a transaction?  Is that true for in-memory databases as well?

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
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] Changed behaviour or bug using field alias in 3.21.0

[Simon Slavin]

On 22 Dec 2017, at 3:04pm, Tim Streater  wrote:

> 2) I don't see this issue mentioned when I read the PHP documentation about 
> their SQLite interface, nor do I see it in the Xojo docs about *their* 
> interface either. I assume their interfaces are not rewriting SELECT 
> statements to include AS for every column selected, so should they be warning 
> their users about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

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

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

>You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5 
and then starting the "sqlite3.exe" command-line shell with the 
"--heap" argument to tell it how much memory to use.  Give it a few 
megabytes.  Then start up your in-memory database and fill it up to 
see what happens. 

Richard suggested this earlier. Can this be done in C rather than the shell?

Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling
with a minus sign before that directive I get a compile error "macro names
must be identifiers ". Compiles OK if I leave out the minus
sign.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] journal mode and transactions

[Nelson, Erik - 2]

The transactions documentation (https://sqlite.org/lang_transaction.html) states

   >If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal 
file) then the behavior of the ROLLBACK command is undefined.

Does that correspondingly mean there's no performance benefit to wrapping 
operations in a transaction?  Is that true for in-memory databases as well?

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

On Fri, Dec 22, 2017 at 10:26 AM, Warren Young  wrote:

> On Dec 22, 2017, at 7:07 AM, Michael Tiernan 
> wrote:
> >
> > "Working as advertised" Okay, that's just funny. That it doesn't build is
> > correct?
>
> It does build.


Okay, it does build the binaries. Valid point.

  It just doesn’t install to a directory it can’t write to, because you
> told it to install system-level things.



Not going to hash it out here but I didn't tell it to install system-level
things, I told it to compile and install everything locally. Just like I do
with lots of other source packages especially when I'm not very familiar
with the software and wish to make sure of what I'm doing before committing
it to the system.



> > Just as an FYI, it builds correctly on MacOSX and doesn't complain about
> > things it can't control.
> I’m guessing you’re using Homebrew,


Nope, not at all. It doesn't seem to try and touch the things it has no
right to touch.

-- 
<< MCT >>   Michael C Tiernan.http://www.linkedin.com/in/mtiernan

Non Impediti Ratione Cogatationis
Women and cats will do as they please, and
men and dogs should relax and get used to the idea. -Robert A. Heinlein
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRIM Function in Python3's sqlite3 import

[David Raymond]

http://www.sqlite.org/changes.html

Looks like trim was added in 10 years ago in 3.4.0 (2007-06-18)

Can't help you with the Linux side of things, sorry.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Ed Lipson
Sent: Friday, December 22, 2017 10:07 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] TRIM Function in Python3's sqlite3 import

I shifting some Python code to Linux from Windows. I find that TRIN i
smissing from the verison on Linux.
Python reports this version information
sqlite.version 2.6.0 for both Windows and Linux
sqlite.sqlite_version 3.3.6 for Linux, 3.15.2 for Windows.

Two questions:
1. When was TRIM added as a function?
2. How do I update the RHEL Python to a version which has the TRIM
function, or is there a replacement function available in 3.3.6 to which I
can change the code?


Thanks,
Ed
___
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] Minor bug reports during build.

[Warren Young]

On Dec 22, 2017, at 7:07 AM, Michael Tiernan  wrote:
> 
> "Working as advertised" Okay, that's just funny. That it doesn't build is
> correct?

It does build.  It just doesn’t install to a directory it can’t write to, 
because you told it to install system-level things.  Rowan also gave you the 
solution, forcing installation of Tcl extension to other than the default you 
implicitly selected by not overriding the default.

> Just as an FYI, it builds correctly on MacOSX and doesn't complain about
> things it can't control.

I’m guessing you’re using Homebrew, which gives your normal user all rights to 
/usr/local, which is atypical for POSIX machines.  In fact on macOS’s closest 
cousin, FreeBSD, giving that level of access to /usr/local would be a major 
security problem.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing Text to Create a Table

[Peter Da Silva]

I suspect you would be best advised to do more processing of the data to 
extract just the email addresses rather than treating it like an unstructured 
text blob.
 

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

[sqlite] TRIM Function in Python3's sqlite3 import

[Ed Lipson]

I shifting some Python code to Linux from Windows. I find that TRIN i
smissing from the verison on Linux.
Python reports this version information
sqlite.version 2.6.0 for both Windows and Linux
sqlite.sqlite_version 3.3.6 for Linux, 3.15.2 for Windows.

Two questions:
1. When was TRIM added as a function?
2. How do I update the RHEL Python to a version which has the TRIM
function, or is there a replacement function available in 3.3.6 to which I
can change the code?


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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Tim Streater]

On 22 Dec 2017, at 09:57, Hick Gunter  wrote:

> The problem lies in your reliance on unspecified behaviour. Unspecified
> behaviour is allowed to change.
>
> I am sure you have read (and ignored) the following guarantee taken from
> http://sqlite.org/c3ref/column_name.html :

My questions are these:

1) That the name without AS is documented as being unspecified, is that the 
case with all/most SQL systems or is it SQLite specific?

2) I don't see this issue mentioned when I read the PHP documentation about 
their SQLite interface, nor do I see it in the Xojo docs about *their* 
interface either. I assume their interfaces are not rewriting SELECT statements 
to include AS for every column selected, so should they be warning their users 
about it?


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

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

"Working as advertised" Okay, that's just funny. That it doesn't build is
correct?

Also, despite the "disable-tcl" flag, the tests all fail because of
something involving tcl. Don't think I read that in the advertisement.

Just as an FYI, it builds correctly on MacOSX and doesn't complain about
things it can't control.
-- 
<< MCT >>   Michael C Tiernan.http://www.linkedin.com/in/mtiernan

Non Impediti Ratione Cogatationis
Women and cats will do as they please, and
men and dogs should relax and get used to the idea. -Robert A. Heinlein


On Dec 21, 2017 10:36 PM, "Rowan Worth"  wrote:

Seems to be working as advertised.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

>I would think that a temp file database (created with an empty string) is no
different from a regular disk file resident database EXCEPT that the file is
generated with an random tmpfile name and automatically unlinked when
closed, and that "memory pressure" equates to "page cache is full".  I don't
know if it would use the temp page cache size or the database page cache
size. 


I think it has something to do with persistent versus temp tables. You can't
qualify a temp table e.g. "create temp table mem.Tbl" isn't allowed so it's
either "create temp table Tbl" (standard temp table) or "create table
mem.Tbl" (persistent table within a temp db).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[Keith Medcalf]

>I thought I had posted this earlier but I don't see it.

>Earlier I said the ideal solution would be something that uses memory
>and defaults to disc if it runs out of memory. In response Richard's
>suggested using a temp database with a blank name as that would use 
>memory but parts of it would be flushed to disc if sqlite came under 
>memory pressure (https://sqlite.org/inmemorydb.html). I tried the 
>large insert in such a db but the performance was only on a par 
>with a temp table with temp_store set as FILE.

I would think that a temp file database (created with an empty string) is no 
different from a regular disk file resident database EXCEPT that the file is 
generated with an random tmpfile name and automatically unlinked when closed, 
and that "memory pressure" equates to "page cache is full".  I don't know if it 
would use the temp page cache size or the database page cache size.

---
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] Atomic DELETE index optimisation?

[Dinu]

Thank you all for the replies,
I will hack this problem one way or another after the hoildays and let you
know how it went.
In the mean time, I wish you all happy peaceful holidays, and a great New
Year!

Dinu



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

How do you test your sql-s? I write sql
from step to step from very simple to complex.
So I have select and finally I need table for
that. It was simple until now. Just put
create table as before select and you get
same result (same names) as in select.

Your example is not the same. If using expression
name can be different and can change. This is
true for every datatase I know. In Firebird If
I write select 1+2 from rdb$database I get
column name ADD. If I specify with AS clause
I get name I want.

This works in sqlite and is not a problem. If
I do not specify name it can change from version
to version and everbody knows it must be specified.

But in my example name is explicitly defined in
inner select and for select I get correct name
but for create table as I do not.

Please dont tell me again what is documented because
at first very little was and not as one would like but
I understand that. It is not the point in this example.

The point is different behavior or result or names
when you have select or create table as from that select.
It should give same result or is better just not using
create table as at all because you will never know
what you can expect. Maybe as you eplained some day
create table as will create name like 1, 2, 3, 4

Long names sometimes are used for excel export or some
simple reports with just sql behind.


R Smith je 22.12.2017 ob 12:34 napisal:


On 2017/12/22 11:06 AM, Radovan Antloga wrote:

I'm using sqlite from version 2. I have 1000+
sql-s written already. I understand that
outermost statement must have AS clause as I
have explained below (select d as d ...).
Sqlite authors always say that new version can
not break sql-s for milions of users using
sqlite. But this change does.


It didn't break for millions of users, it only broke for the few who 
relied on behaviour that is documented to not be reliable and that 
might change in future.


Usually the Dev team is not in the habit of going around messing with 
features just because it is documented as unspecified, but in this 
case, it had to change to fix another real bug that had real-world 
problematic implications.




Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


The first "d" tells SQLite where to find or how to evaluate the value, 
the second "d" tells SQLite how to name the output of that SELECT. It 
isn't any more ridiculous than SELECT (P+3) AS 'P+3' would be ridiculous.
This is not English101, this is Programming. Rules are rules, even 
silly ones.



.../and later/...
>If I have long name then I would have
>select some_long_name_to_understand_column as 
some_long_name_to_understand_column

>and if I have 20 columns like that. Just ridiculous or what.
>So once again I use AS clause but why I must use AS clause in
>outer statement if name is already defined in inner select.

Why would you use such a long silly name in the inner query KNOWING 
that it is ignored (wrt. to output naming anyway)?  That doesn't make 
a sound argument.


The CREATE TABLE Evaluates the select with some special considerations 
(as documented), nothing prescribes the resulting column name except 
the final outer statement's AS clause (as documented). Internally 
sqlite doesn't even see Aliases, it has numbers for columns. The outer 
select is however the boss, when it says "this-column" AS 'this_name' 
then sqlite (and any other DB engine) /has/ to abide by that (as 
documented).  The aliasing in the inner queries matter none other than 
to allow reliable reference pointing between query levels (as 
documented).


If it were undocumented behaviour you'd still not have a case but some 
anger would be understandable, however, you've explicitly relied on 
/documented-to-be-non-reliable/ behaviour, I still feel your pain, but 
it still isn't a bug and calling it "ridiculous" still won't change that.
(Btw. - the devs might change the behaviour again, maybe even to your 
liking if they feel merit and it doesn't break another behaviour, the 
point remains that it must not be trusted until it becomes the 
documented behaviour.)



I sincerely hope not too many of those 1000+ sql-s need fixing...
Good luck!
Ryan

___
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] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

[R Smith]

On 2017/12/22 11:06 AM, Radovan Antloga wrote:

I'm using sqlite from version 2. I have 1000+
sql-s written already. I understand that
outermost statement must have AS clause as I
have explained below (select d as d ...).
Sqlite authors always say that new version can
not break sql-s for milions of users using
sqlite. But this change does.


It didn't break for millions of users, it only broke for the few who 
relied on behaviour that is documented to not be reliable and that might 
change in future.


Usually the Dev team is not in the habit of going around messing with 
features just because it is documented as unspecified, but in this case, 
it had to change to fix another real bug that had real-world problematic 
implications.




Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


The first "d" tells SQLite where to find or how to evaluate the value, 
the second "d" tells SQLite how to name the output of that SELECT. It 
isn't any more ridiculous than SELECT (P+3) AS 'P+3' would be ridiculous.
This is not English101, this is Programming. Rules are rules, even silly 
ones.



.../and later/...
>If I have long name then I would have
>select some_long_name_to_understand_column as 
some_long_name_to_understand_column

>and if I have 20 columns like that. Just ridiculous or what.
>So once again I use AS clause but why I must use AS clause in
>outer statement if name is already defined in inner select.

Why would you use such a long silly name in the inner query KNOWING that 
it is ignored (wrt. to output naming anyway)?  That doesn't make a sound 
argument.


The CREATE TABLE Evaluates the select with some special considerations 
(as documented), nothing prescribes the resulting column name except the 
final outer statement's AS clause (as documented). Internally sqlite 
doesn't even see Aliases, it has numbers for columns. The outer select 
is however the boss, when it says "this-column" AS 'this_name' then 
sqlite (and any other DB engine) /has/ to abide by that (as 
documented).  The aliasing in the inner queries matter none other than 
to allow reliable reference pointing between query levels (as documented).


If it were undocumented behaviour you'd still not have a case but some 
anger would be understandable, however, you've explicitly relied on 
/documented-to-be-non-reliable/ behaviour, I still feel your pain, but 
it still isn't a bug and calling it "ridiculous" still won't change that.
(Btw. - the devs might change the behaviour again, maybe even to your 
liking if they feel merit and it doesn't break another behaviour, the 
point remains that it must not be trusted until it becomes the 
documented behaviour.)



I sincerely hope not too many of those 1000+ sql-s need fixing...
Good luck!
Ryan

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

Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

But column name is specified in inner select with AS clause.
It can be used in outer select and work as expected.

select d from (select c AS d from (select a AS c from test));

I hope you did read my example. You can see explicitly defined

name in select a AS c from test

then again explicitly defined new name

select c AS d from 

and finally outer select can select only name d or what you think?

select statement gives correct answer or column name

but when you put CREATE TABLE AS in front of this statement you
get a as column name not d in created table. Do you understand that?

So outer select must have select d AS d so column must be renamed
into it self to get proper name?!

If I have long name then I would have

select some_long_name_to_understand_column as 
some_long_name_to_understand_column


and if I have 20 columns like that. Just ridiculous or what.

So once again I use AS clause but why I must use AS clause in
outer statement if name is already defined in inner select.




Hick Gunter je 22.12.2017 ob 10:57 napisal:

The problem lies in your reliance on unspecified behaviour. Unspecified 
behaviour is allowed to change.

I am sure you have read (and ignored) the following guarantee taken from 
http://sqlite.org/c3ref/column_name.html:

"Column Names In A Result Set
...
The name of a result column is the value of the "AS" clause for that column, if 
there is an AS clause. If there is no AS clause then the name of the column is unspecified and 
may change from one release of SQLite to the next."


Here is another common trap to fall into, taken from 
http://sqlite.org/lang_select.html :

" The ORDER BY clause

If a SELECT statement that returns more than one row does not have an ORDER BY 
clause, the order in which the rows are returned is undefined."

The order in which rows are returned is determined by the selected query plan. 
This may change after running ANALYZE, adding or deleting indices, or when 
changes are made to the Query Planner itself.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Radovan Antloga
Gesendet: Freitag, 22. Dezember 2017 10:06
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias 
in 3.21.0

I'm using sqlite from version 2. I have 1000+ sql-s written already. I 
understand that outermost statement must have AS clause as I have explained 
below (select d as d ...).
Sqlite authors always say that new version can not break sql-s for milions of 
users using sqlite. But this change does.

Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


Hick Gunter je 22.12.2017 ob 7:55 napisal:

The behaviour does not need to match what you think of as consistent.

The only way to force a certain column name is with the AS clause *on the 
outermost statement*. Otherwise, the column name is implementation defined and 
may change between releases. You should not be relying on column names other 
than those you explicitly set using the AS clause. This is a common mistake.

If you really need the column names, then just CREATE TABLE first (this gives 
you defined column names AND declared data types) and the INSERT INTO ... 
SELECT later (which ignores the generated column names from the select 
statement).

-Ursprüngliche Nachricht-
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Radovan Antloga
Gesendet: Donnerstag, 21. Dezember 2017 16:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field
alias in 3.21.0

Behaviour is not consistent when using create table as or just select
statement.
Try this simple test.

create table test(a int, b int);
insert into test values (1, 1);

select d from (select c as d from (select a as c from test));

you get column name d as expected
but when you have create table as statement

create table test2 as
select d from (select c as d from (select a as c from test));

you get table test2 with column name a.

If you change to this

create table test2 as
select d as d from (select c as d from (select a as c from test));

you will get name correct. I think it should be the same as when just using 
select statement.

Best Regards
Radovan


select a from (select b from (select c from test)))

Richard Hipp je 21.12.2017 ob 14:52 napisal:

The behavior change is a bug fix.  See
http://sqlite.org/src/info/de3403bf5ae for details.

On 12/21/17, Radovan Antloga  wrote:

I have table (create statement):

CREATE TABLE SOPP1 (
  STAT  varchar(1) collate systemnocase,
  RID  varchar(2) collate systemnocase,
  VP  integer,
  BLANK  varchar(6) collate systemnocase,
  NAZIV  varchar(24) collate systemnocase,
  KN  varchar(12) collate 

Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

[Hick Gunter]

The problem lies in your reliance on unspecified behaviour. Unspecified 
behaviour is allowed to change.

I am sure you have read (and ignored) the following guarantee taken from 
http://sqlite.org/c3ref/column_name.html:

"Column Names In A Result Set
...
The name of a result column is the value of the "AS" clause for that column, if 
there is an AS clause. If there is no AS clause then the name of the column is 
unspecified and may change from one release of SQLite to the next."


Here is another common trap to fall into, taken from 
http://sqlite.org/lang_select.html :

" The ORDER BY clause

If a SELECT statement that returns more than one row does not have an ORDER BY 
clause, the order in which the rows are returned is undefined."

The order in which rows are returned is determined by the selected query plan. 
This may change after running ANALYZE, adding or deleting indices, or when 
changes are made to the Query Planner itself.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Radovan Antloga
Gesendet: Freitag, 22. Dezember 2017 10:06
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias 
in 3.21.0

I'm using sqlite from version 2. I have 1000+ sql-s written already. I 
understand that outermost statement must have AS clause as I have explained 
below (select d as d ...).
Sqlite authors always say that new version can not break sql-s for milions of 
users using sqlite. But this change does.

Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


Hick Gunter je 22.12.2017 ob 7:55 napisal:
> The behaviour does not need to match what you think of as consistent.
>
> The only way to force a certain column name is with the AS clause *on the 
> outermost statement*. Otherwise, the column name is implementation defined 
> and may change between releases. You should not be relying on column names 
> other than those you explicitly set using the AS clause. This is a common 
> mistake.
>
> If you really need the column names, then just CREATE TABLE first (this gives 
> you defined column names AND declared data types) and the INSERT INTO ... 
> SELECT later (which ignores the generated column names from the select 
> statement).
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Radovan Antloga
> Gesendet: Donnerstag, 21. Dezember 2017 16:35
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field
> alias in 3.21.0
>
> Behaviour is not consistent when using create table as or just select
> statement.
> Try this simple test.
>
> create table test(a int, b int);
> insert into test values (1, 1);
>
> select d from (select c as d from (select a as c from test));
>
> you get column name d as expected
> but when you have create table as statement
>
> create table test2 as
> select d from (select c as d from (select a as c from test));
>
> you get table test2 with column name a.
>
> If you change to this
>
> create table test2 as
> select d as d from (select c as d from (select a as c from test));
>
> you will get name correct. I think it should be the same as when just using 
> select statement.
>
> Best Regards
> Radovan
>
>
> select a from (select b from (select c from test)))
>
> Richard Hipp je 21.12.2017 ob 14:52 napisal:
>> The behavior change is a bug fix.  See
>> http://sqlite.org/src/info/de3403bf5ae for details.
>>
>> On 12/21/17, Radovan Antloga  wrote:
>>> I have table (create statement):
>>>
>>> CREATE TABLE SOPP1 (
>>>  STAT  varchar(1) collate systemnocase,
>>>  RID  varchar(2) collate systemnocase,
>>>  VP  integer,
>>>  BLANK  varchar(6) collate systemnocase,
>>>  NAZIV  varchar(24) collate systemnocase,
>>>  KN  varchar(12) collate systemnocase,
>>>  A  varchar(1) collate systemnocase,
>>>  B  varchar(1) collate systemnocase,
>>>  RACUN  varchar(1) collate systemnocase,
>>>  URE  varchar(1) collate systemnocase,
>>>  ZN  varchar(1) collate systemnocase,
>>>  TOCKE  varchar(1) collate systemnocase,
>>>  PRC  varchar(1) collate systemnocase,
>>>  UP  varchar(1) collate systemnocase,
>>>  IZPIS  varchar(1) collate systemnocase,
>>>  D  varchar(1) collate systemnocase,
>>>  F2U  varchar(1) collate systemnocase,
>>>  F2O  varchar(1) collate systemnocase,
>>>  F2T  varchar(1) collate systemnocase,
>>>  F2Z  varchar(1) collate systemnocase,
>>>  F2P_1  integer,
>>>  F2P_2  integer,
>>>  F2P_3  integer,
>>>  F5  varchar(1) collate systemnocase,
>>>  AJPES  varchar(1) collate systemnocase,
>>>  ZZ  integer,
>>>  VD  integer,
>>>  NS  integer,
>>>  MES  

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

I thought I had posted this earlier but I don't see it.

Earlier I said the ideal solution would be something that uses memory and
defaults to disc if it runs out of memory. In response Richard's suggested
using a temp database with a blank name as that would use memory but parts
of it would be flushed to disc if sqlite came under memory pressure
(https://sqlite.org/inmemorydb.html). I tried the large insert in such a db
but the performance was only on a par with a temp table with temp_store set
as FILE. 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

I'm using sqlite from version 2. I have 1000+
sql-s written already. I understand that
outermost statement must have AS clause as I
have explained below (select d as d ...).
Sqlite authors always say that new version can
not break sql-s for milions of users using
sqlite. But this change does.

Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


Hick Gunter je 22.12.2017 ob 7:55 napisal:

The behaviour does not need to match what you think of as consistent.

The only way to force a certain column name is with the AS clause *on the 
outermost statement*. Otherwise, the column name is implementation defined and 
may change between releases. You should not be relying on column names other 
than those you explicitly set using the AS clause. This is a common mistake.

If you really need the column names, then just CREATE TABLE first (this gives 
you defined column names AND declared data types) and the INSERT INTO ... 
SELECT later (which ignores the generated column names from the select 
statement).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Radovan Antloga
Gesendet: Donnerstag, 21. Dezember 2017 16:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in 
3.21.0

Behaviour is not consistent when using
create table as
or just select statement.
Try this simple test.

create table test(a int, b int);
insert into test values (1, 1);

select d from (select c as d from (select a as c from test));

you get column name d as expected
but when you have create table as statement

create table test2 as
select d from (select c as d from (select a as c from test));

you get table test2 with column name a.

If you change to this

create table test2 as
select d as d from (select c as d from (select a as c from test));

you will get name correct. I think it should be the same as when just using 
select statement.

Best Regards
Radovan


select a from (select b from (select c from test)))

Richard Hipp je 21.12.2017 ob 14:52 napisal:

The behavior change is a bug fix.  See
http://sqlite.org/src/info/de3403bf5ae for details.

On 12/21/17, Radovan Antloga  wrote:

I have table (create statement):

CREATE TABLE SOPP1 (
 STAT  varchar(1) collate systemnocase,
 RID  varchar(2) collate systemnocase,
 VP  integer,
 BLANK  varchar(6) collate systemnocase,
 NAZIV  varchar(24) collate systemnocase,
 KN  varchar(12) collate systemnocase,
 A  varchar(1) collate systemnocase,
 B  varchar(1) collate systemnocase,
 RACUN  varchar(1) collate systemnocase,
 URE  varchar(1) collate systemnocase,
 ZN  varchar(1) collate systemnocase,
 TOCKE  varchar(1) collate systemnocase,
 PRC  varchar(1) collate systemnocase,
 UP  varchar(1) collate systemnocase,
 IZPIS  varchar(1) collate systemnocase,
 D  varchar(1) collate systemnocase,
 F2U  varchar(1) collate systemnocase,
 F2O  varchar(1) collate systemnocase,
 F2T  varchar(1) collate systemnocase,
 F2Z  varchar(1) collate systemnocase,
 F2P_1  integer,
 F2P_2  integer,
 F2P_3  integer,
 F5  varchar(1) collate systemnocase,
 AJPES  varchar(1) collate systemnocase,
 ZZ  integer,
 VD  integer,
 NS  integer,
 MES  integer,
 NORURE  varchar(1) collate systemnocase,
 G  varchar(1) collate systemnocase,
 E  varchar(1) collate systemnocase,
 H  varchar(1) collate systemnocase,
 I  varchar(1) collate systemnocase,
 J  varchar(1) collate systemnocase,
 SM  varchar(1) collate systemnocase,
 NO  varchar(1) collate systemnocase,
 PRIO  varchar(1) collate systemnocase,
 V_1  varchar(1) collate systemnocase,
 V_2  varchar(1) collate systemnocase,
 V_3  varchar(1) collate systemnocase,
 V_4  varchar(1) collate systemnocase,
 V_5  varchar(1) collate systemnocase,
 V_6  varchar(1) collate systemnocase,
 V_7  varchar(1) collate systemnocase,
 V_8  varchar(1) collate systemnocase,
 V_9  varchar(1) collate systemnocase,
 V_10  varchar(1) collate systemnocase,
 V_11  varchar(1) collate systemnocase,
 V_12  varchar(1) collate systemnocase,
 FOR  integer,
 P_1  integer,
 P_2  integer,
 P_3  integer,
 P_4  integer,
 P_5  integer,
 P_6  integer,
 primary key (RID, VP, BLANK));

When I create new table using this sql:

drop table if exists WM4P;
create table WM4P as
select
 P, A, B, AB, U, H, ZZ,
 case
   when AB in ('7') then 99
   when AB in ('57', '58', '59', '5M') then null
   when AB = '56' and ZZ = 12 then 01
   when AB = '56' then 02
   when A = '3' then 03
   when AB in ('1M') then 08
   when AB in ('10') then 07
   when AB in ('12') then null
  

Re: [sqlite] Importing Text to Create a Table

[Chris Locke]

What operating system are you using, and what software are you using to do
the import?  What specificially are you trying to import?
If you perform a sequence of 'insert' statements, then that can be time
consuming - its better to incorporate them into one 'transaction' - sqlite
bundles the operations into one big operation.  This is better IO wise - it
performs far less writes to the disk, etc.  But understanding the methods
you're using so far would be helpful.

On Fri, Dec 22, 2017 at 12:22 AM, Lawrence Murphy  wrote:

> I am supporting a website which aims to protect a forest from development.
> The website produces an email for supporters to mail out. A copy of the
> email is sent to our Gmail address and we wish to capture the supporters
> return email address. Google provides an archive of our Gmail account which
> is 458Mbs in size and contains a lot of superfluous data.
>
> I have tried making a table with one column, text 255c in size and doing
> the import but it takes more than overnight and is still running. Is there
> a quicker way to import the data?
>
> Warm Regards,
> Lawrence
>
> Mb: 0408 403 324
> PO Box 263 Cherrybrook NSW 2126
>
> Lose Weight, Gain Health & Prevent Disease
> Find out how here
> http://lwghpd.blogspot.com.au/
> ___
> 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 Text to Create a Table

[Luuk]

Can you give a (short) example of some lines,
and how you import them?

Creating a table with 1 field text 255 seems not the right way to do
this if you are only interested in emailaddresses.



On 22-12-17 01:22, Lawrence Murphy wrote:
> I am supporting a website which aims to protect a forest from development.
> The website produces an email for supporters to mail out. A copy of the
> email is sent to our Gmail address and we wish to capture the supporters
> return email address. Google provides an archive of our Gmail account which
> is 458Mbs in size and contains a lot of superfluous data.
>
> I have tried making a table with one column, text 255c in size and doing
> the import but it takes more than overnight and is still running. Is there
> a quicker way to import the data?
>
> Warm Regards,
> Lawrence
>
>

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