[sqlite] Why SQLite Use Different Malloc Zone While CPU Count > 1?

Following the code in mem1.c, Why SQLite Use Different Malloc Zone While CPU Count 1? static int sqlite3MemInit(void *NotUsed){ #if defined(__APPLE__) !defined(SQLITE_WITHOUT_ZONEMALLOC) int cpuCount; size_t len; if( _sqliteZone_ ){ return SQLITE_OK; } len = sizeof(cpuCount); /* One

Re: [sqlite] Why this query plan?

I am fully aware of this and I have no problem at all with this behaviour. I just wondered why it choose the particular plan in this situation. There is no criticism, just curiosity. RBS On Thu, Jan 12, 2017 at 12:45 AM, Richard Hipp wrote: > On 1/11/17, Bart Smissaert

[sqlite] Insert into with Id...

Dear list members: I’m trying to read a .csv file and introduce in an SQLite table, using the insert into command. In the .csv file I have not used this Id field (it was defined as integer and autoincrease). I will appreciate any help about how to copy from a .csv file to an SQLite

Re: [sqlite] Why this query plan?

Since you did not specify an ORDER BY clause, SQLite is free to return rows in *any* order. The order may even change if the underlying schema changes and SQLite finds a better way to compute the results. A "covering index" is one that contains all the fields required from a certain table to

Re: [sqlite] Problem with compiled dll on Windows

On Wed, 11 Jan 2017 12:11:36 +0200 Rael Bauer wrote: > I am trying to compile the latest amalgamation (3160200) as a dll on > Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll > compiles fine, however trying to use this dll in various tools > (Delphi)

Re: [sqlite] Foreign key error...

Keith, Good point. Did not know this exists. Ken On 01/10/2017 09:48 PM, Simon Slavin wrote: On 11 Jan 2017, at 1:02am, Keith Medcalf wrote: You are correct, however, if there were a unique constraint placed on tracks.name, then a given track could only appear once

Re: [sqlite] Need help with System.Data.SQLite

I didn't assign any value directly. This is what I did: 1. On SQLite create source table. CREATE TABLE FromNum ( id INT, Num NUMERIC(5,5) NULL); INSERT INTO FromNum (id, Num) VALUES (1, .0); 2. On MS SQL Server 2014 (SP2) create empty destination table. CREATE TABLE dbo.ToNum ( id

Re: [sqlite] LIKE and the like and SIMilarity

Here's the problem as I see it (demo SQL is lame, but makes the point): SELECT trim(name) FROM names WHERE name LIKE('Ben') and name ILIKE('benjamin') ...you can't do that in SqLite using a pragma, can you? If you can, I'd sure like to learn how. If you can't, not to belabor the point, but

Re: [sqlite] LIKE and the like and SIMilarity

> On Jan 11, 2017, at 9:55 AM, Anony Mous wrote: > > Textual data has case. Sometimes that matters. Sometimes it doesn't. A > database engine should be able to cleanly deal with that without forcing > the programmer to write custom code. +1. And while we’re at it, I’d like

Re: [sqlite] Problem with compiled dll on Windows

@ Random Coder.: Thanks, that was the exact issue. Using a def file solved the problem. 1) Should the howtocompile.html webpage not include this in the "Building A Windows DLL" instructions? 2) What is the -Ox flag? I did not find what that was for. -Rael

Re: [sqlite] Problem with compiled dll on Windows

On Wed, Jan 11, 2017 at 10:28 AM, Rael Bauer wrote: > 1) Should the howtocompile.html webpage not include this in the "Building A > Windows DLL" instructions? Tough question. Normally, I'd say this is toolset knowledge, and really doesn't belong in those sort of

Re: [sqlite] LIKE and the like and SIMilarity

On 11 Jan 2017, at 9:34pm, R Smith wrote: > Doesn't this already do the trick? > > SELECT a FROM t WHERE a = b COLLATE NOCASE; Right. Better still, if you declare the columns as COLLATE NOCASE in the first place, the comparison is done ignoring case without you having to

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

Hello Richard ! It seems that sqlite query plan is having trouble with this query too: === create table if not exists municipios( id integer primary key, name varchar not null unique collate nocase_slna ); create table if not exists municipios_orgaos( id integer primary key,

Re: [sqlite] extension to run bash

I know it is not magic. It is SQLite developers' brain and effort. But it looks like magic to me. Yes, this is example I see how I could use. I am still thinking if this is what I really need. I am working with images and need, among other things, some summary measures to be placed into tables

Re: [sqlite] extension to run bash

Though it may be cleaner long-term to implement system() to pass individual arguments, rather than passing a single string which will have to be re-processed by the shell. So the API would end up like: UPDATE result SET nRows = system('wc', '-l', fileNames); The reason I suggest this is

Re: [sqlite] extension to run bash

On 1/11/17, Scott Hess wrote: > Though it may be cleaner long-term to implement system() to pass > individual arguments, rather than passing a single string which will > have to be re-processed by the shell. So the API would end up like: > UPDATE result SET nRows =

Re: [sqlite] LIKE and the like and SIMilarity

On 2017-01-11 10:11 AM, Jens Alfke wrote: And while we’re at it, I’d like to see a case-insensitive string equality operator. Yes, that shorthand can be useful. But don't make it a pragma that overrides the meaning of "=", which would be a world of hurt, it needs a different name. --

[sqlite] extension to run bash

Dear SQLites, I am using exclusively sqlite3 shell for all the processing and may need ability to run bash commands and assign result to a column. For example: UPDATE result SET nRows =` wc -l fileNames` ; Here I used `` as would be in bash for command substitution. This would run wc command

Re: [sqlite] LIKE and the like and SIMilarity

On 2017/01/11 11:19 PM, Darren Duncan wrote: On 2017-01-11 10:11 AM, Jens Alfke wrote: And while we’re at it, I’d like to see a case-insensitive string equality operator. Yes, that shorthand can be useful. But don't make it a pragma that overrides the meaning of "=", which would be a

Re: [sqlite] extension to run bash

Yes, Richard, this is exactly what I mean. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Wednesday, January 11, 2017 4:34 PM To: SQLite mailing list Subject: Re: [sqlite]

Re: [sqlite] Problem with compiled dll on Windows

On Wed, Jan 11, 2017 at 2:11 AM, Rael Bauer wrote: > I am trying to compile the latest amalgamation (3160200) as a dll on Windows > 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll compiles fine, > however trying to use this dll in various tools (Delphi)

Re: [sqlite] About ticket c92ecff2ec5f1784 LEFT JOIN problem

Ken, That went to the mailing list ... to which you are also a recipient of ;) Thanks, Chris ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with System.Data.SQLite

I try both "SELECT 1,0;" and "SELECT 1, cast(0 as real)"? The same error: Exception calling "WriteToServer" with "1" argument(s): "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column." -Original Message- From:

[sqlite] Problem with compiled dll on Windows

Hi, I am trying to compile the latest amalgamation (3160200) as a dll on Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll compiles fine, however trying to use this dll in various tools (Delphi) results in the error "sqlite3_open not found". I tried compiling with: cl

Re: [sqlite] About ticket c92ecff2ec5f1784 LEFT JOIN problem

Domingo, Thanks for the email, but I don't think I am your inteded recipient. Ken On 01/10/2017 12:11 PM, Domingo Alvarez Duarte wrote: Hello Richard ! Now that you are dealing with this ticket http://www.sqlite.org/src/info/c92ecff2ec5f1784 could be a good moment to acknowledge the

Re: [sqlite] Problem with compiled dll on Windows

On Wed, Jan 11, 2017 at 11:11 AM, Rael Bauer wrote: > I am trying to compile the latest amalgamation (3160200) as a dll on > Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll > compiles fine, however trying to use this dll in various tools (Delphi) >

[sqlite] Now memory usage for update of big tables works fine

Hello Richard ! I just tested the latest sqlite3 with this "Changes to allow some multi-row UPDATE statements to avoid the two-pass approach." and before it was blowing up my machine (swapping) and now the memory usage remains basically the same as the startup without doing nothing. Thank

Re: [sqlite] extension to run bash

On Wed, 11 Jan 2017, Jim Callahan wrote: How much doing all that is worth is a different question, since the calls made through this proposed system() SQLite function would also likely be non-portable. In this very example, there is no wc on Windows. I would suggest renaming the proposed

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

Hello again ! I did some changes and got a better plan but it takes for ever, it seems that sqlite3 can not merge the where clause before the "group by" scan the whole tables: === CREATE VIEW despesas_municipio_orgao_list_view AS SELECT a."municipio_id" rowid, a."ano_exercicio",

Re: [sqlite] extension to run bash

On Jan 11, 2017, at 3:11 PM, Richard Hipp wrote: > > On 1/11/17, Scott Hess wrote: >> UPDATE result SET nRows = system('wc', '-l', fileNames); >> >> ... >> [Though, yes, this means you'll have to use fork() and execlp() and >> waitpid() to implement, rather

Re: [sqlite] extension to run bash

> How much doing all that is worth is a different question, since the calls made through this > proposed system() SQLite function would also likely be non-portable. In this very example, > there is no wc on Windows. I would suggest renaming the proposed system() function bash() since now and in

Re: [sqlite] Need help with System.Data.SQLite

What is the result if you exchange your currently executed statement to "SELECT 1,0;" ? Or to "SELECT 1, cast(0 as real)"? The effect of declaring a NUMERIC(5,5) field in SQLite is that it's affinity becomes NUMERIC. The value .0 thus becomes the integer 0. The other legal values (.1

Re: [sqlite] LIKE and the like and SIMilarity

> On Jan 11, 2017, at 1:34 PM, R Smith wrote: > > SELECT a FROM t WHERE a = b COLLATE NOCASE; D’ohh! I overlooked the COLLATE operator. Perfect. (In my situation, these strings are not coming directly from columns, so setting collation on columns doesn’t make sense.)

[sqlite] Why this query plan?

Say I have a table created like this: create table table1( [id] integer primary key, [dob] integer) with an index (not unique) on dob and I run this SQL: select id, dob from table1 then the query plan I get is: SCAN TABLE TABLE1 USING COVERING INDEX IDX_TABLE1_DOB The result is that the

Re: [sqlite] Why this query plan?

On 1/11/17, Bart Smissaert wrote: > > The result is that the output is descending on DOB. > I expected and preferred if the output was ascending on rowid. If you omit the ORDER BY clause, then the SQL database engine (*any* engine, not just SQLite) is free to return the

Re: [sqlite] Why this query plan?

On 12 Jan 2017, at 12:45am, Richard Hipp wrote: > On 1/11/17, Bart Smissaert wrote: >> >> The result is that the output is descending on DOB. >> I expected and preferred if the output was ascending on rowid. > > If you omit the ORDER BY clause, then

Re: [sqlite] extension to run bash

On 1/11/17, Bob Friesenhahn wrote: > > For anyone thinking that it is a good idea to embed shell > functionality in the SQL interpreter, it makes the SQL interpreter > much less secure for untrusted inputs. Nobody is suggesting that this become a standard feature of

Re: [sqlite] Why this query plan?

On 1/11/17, Richard Hipp wrote: > > If you omit the ORDER BY clause, then the SQL database engine (*any* > engine, not just SQLite) is free to return the rows in whatever random > order it chooses. And it does not need to explain itself when it > does. :-) > For years I have

Re: [sqlite] Why this query plan?

On 1/11/17, Simon Slavin wrote: > > On 12 Jan 2017, at 12:49am, Richard Hipp wrote: > >> For years I have threatened to make it a feature of SQLite that it >> really does output the rows in some random order if you omit the ORDER >> BY clause > >

Re: [sqlite] LIKE and the like and SIMilarity

On 2017/01/11 7:55 PM, Anony Mous wrote: Here's the problem as I see it (demo SQL is lame, but makes the point): SELECT trim(name) FROM names WHERE name LIKE('Ben') and name ILIKE('benjamin') ...you can't do that in SqLite using a pragma, can you? If you can, I'd sure like to learn how.

Re: [sqlite] extension to run bash

> may need ability to run bash commands and assign result to a column. For example: ​> ​ UPDATE result SET nRows =` wc -l fileNames` ; ​Might be easier to run Bash commands in Bash; write the results to a file​ and then redirect the file into SQLite. See for example, this Nabble thread.

Re: [sqlite] extension to run bash

On 1/11/17, Roman Fleysher wrote: > Dear SQLites, > > I am using exclusively sqlite3 shell for all the processing and may need > ability to run bash commands and assign result to a column. For example: > > UPDATE result SET nRows =` wc -l fileNames` ; > > Here I

Re: [sqlite] extension to run bash

On 1/11/17, Roman Fleysher wrote: > Yes, Richard, this is exactly what I mean. > Then maybe use the https://www.sqlite.org/src/file/ext/misc/rot13.c extension as a prototype from which to develop yours. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] LIKE and the like and SIMilarity

It can. You declare the column to have a collation of NOCASE. In the case of the select you gave which does not d=contain any wildcards (and therefore is not needful of LIKE) you can pronounce: SELECT trim(name) FROM names WHERE name = 'Ben' and name = 'benjamin' collate nocase However

Re: [sqlite] extension to run bash

I am not even sure myself this is the right path. I have table with file names and need operations to be performed on columns (i.e. on files). Results, numeric or new file names, are to be recorded in a column. I see two ways: From bash script, make list of rows, run commands, load results

Re: [sqlite] extension to run bash

On Jan 11, 2017, at 4:01 PM, Jim Callahan wrote: > >> How much doing all that is worth is a different question, since the calls > made through this >> proposed system() SQLite function would also likely be non-portable. In > this very example, >> there is no wc