date:20171019

Re: [sqlite] [BUG] shell: .import: no special characters in table name

2017-10-19 Thread Simon Slavin

On 19 Oct 2017, at 7:38pm, Clemens Ladisch  wrote:

> the .import command does not work (and can show misleading error messages)
> if the table name contains special characters:
> 
>  sqlite> .import test.csv temp.t

What format is the file "temp.t" in ?  UTF-8 ?  ASCII ?  Some sort of code page 
?

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

[sqlite] [BUG] shell: .import: no special characters in table name

2017-10-19 Thread Clemens Ladisch

Hi,

the .import command does not work (and can show misleading error messages)
if the table name contains special characters:

  sqlite> .import test.csv temp.t
  Error: no such table: temp.t
  sqlite> select * from temp.t;
  sqlite>

This appears to be caused by inconsistent quoting of the table name:

zSql = sqlite3_mprintf("SELECT * FROM %s", zTable);
  char *zCreate = sqlite3_mprintf("CREATE TABLE %s", zTable);
sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable);


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

[sqlite] virtual tables, and theTrue meaning of pIdxInfo->estimatedCost, pIdxInfo->estimatedRows, and pIdxInfo->idxFlags...

2017-10-19 Thread dave

Hi folks,
 
I am trying to fully understand the impact and correct use of a few subtle
features related to virtual tables' the xBestIndex mechanism, and their
correct use.  Here are my current beliefs:
 
*  pIdxInfo->estimatedCost
  obviously the cost of the proposed plan; a metric of the 'viscosity' of
the table when traversing through xNext relative to other tables and
especially to filesystem access
*  pIdxInfo->estimatedRows
  obviously the approximate number of rows that a proposed plan will return.
But less obvious to me is how this materially affects the query plan,
especially relative to pIdxInfo->estimatedCost
  and a little bit with respect to:
* pIdxInfo->idxFlags
  when the SQLITE_INDEX_SCAN_UNIQUE is set.  Isn't setting
pIdxInfo->estimatedRows to 0 or 1 enough to communicate this same
information?
 
Anyway, I am dutifully setting both estimatedRows and idxFlags in cases
where I have a 0-or-1-result table (I have several of these), and I am also
estimatedRows to LLONG_MAX along with estimatedCost to DBL_MAX in cases
where a plan can never be executed (btw I would respectfully suggest perhaps
using a bit in idxFlags to communicate 'never use this plan, it will never
work').
 
I haven't had any ill effects doing the above, but wonder if that is
'correct'.  Also, it would be interesting just to know what the material
effect of estimatedRows and idxFlags is, so that I can maybe use them more
effectively.
 
Any thoughts or corrections to my thinking?  Thanks in advance; cheers!
-dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Inconsistency between sha1 and sha3 extensions

2017-10-19 Thread Domingo Alvarez Duarte


Hello !

I've tried to use both ext/misc/sha1.c and ext/misc/shathree.c static 
linked with sqlite3 and noticed that instead of using an unique prefix 
for hash_step_vformat like (sha1_step_vformat / sha3_step_vformat) 
giving an error "duplicated symbol" at link time.


Also the return of sha1 is converted to hexadecimal but the sha3 is not.

Another problem with sha3 is that it clashes when compiled for ARM 
Android whit a CPU register B0 and I did a dirty patch to allow it 
compile see bellow:


 /*
 ** A single step of the Keccak mixing function for a 1600-bit state
 */
 static void KeccakF1600Step(SHA3Context *p){
   int i;
+#ifdef B0
+#undef B0
+#endif
   u64 B0, B1, B2, B3, B4;
   u64 C0, C1, C2, C3, C4;
   u64 D0, D1, D2, D3, D4;
   static const u64 RC[] = {
 0x0001ULL,  0x8082ULL,

Cheers !

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

Re: [sqlite] Problems compiling FTS5 extension

2017-10-19 Thread Eugene Mirotin

Thanks for the tips!

Don't know about the segfault too but can ignore it for now — this same
file works well with the GUI app and with my Node.js program.

On Thu, Oct 19, 2017 at 6:38 PM Dan Kennedy  wrote:

> On 10/19/2017 08:40 PM, Eugene Mirotin wrote:
> > Hm, I think that's because the extension is already preloaded with my
> > version if sqlite. Which means I didn't even have to build it :)
> > But now is the question how would I reliably load / not load it in
> > production env if the target user may or may not have sqlite precompiled
> > with it?
>
> Right, it's an error to try to register a new version of a module -
> "fts5" in this case - that has already been registered.
>
> You could just assume that if the attempt to load fts5 dynamically fails
> it is already registered. Or you could try preparing the statement
> "SELECT fts5_source_id()" before loading the extension. If preparing the
> statement succeeds, fts5 is already present and you don't need to load it.
>
> Don't know why you might be getting the segfault.
>
> Dan.
>
>
> >
> > On Thu, Oct 19, 2017 at 3:30 PM Eugene Mirotin 
> wrote:
> >
> >> Well, now I have troubles loading this extension.
> >>
> >> I've built another one before, fts5stemmer.
> >>
> >> When using the CLI sqlite3 (version 3.20.1 from MacPorts):
> >>
> >> ❯ sqlite3
> >> SQLite version 3.20.1 2017-08-24 16:21:36
> >> Enter ".help" for usage hints.
> >> Connected to a transient in-memory database.
> >> Use ".open FILENAME" to reopen on a persistent database.
> >> sqlite> .load './fts5.dylib'
> >> Error: error during initialization:
> >> sqlite> .load './fts5stemmer.dylib'
> >> [1]35952 segmentation fault  sqlite3
> >>
> >> When using the GUI DB Browser for SQLite:
> >> fts5 extension reports the same error "Error: error during
> initialization:"
> >> fst5stemmer loads fine (or at least reports to)
> >>
> >> On Thu, Oct 19, 2017 at 3:09 PM Eugene Mirotin 
> wrote:
> >>
> >>> Thanks a lot Dan, that worked!
> >>> I'm fine with the trunk version for now but hope to see this in stable
> >>> eventually as I'll have to later build this extension for various
> platforms
> >>> for the release of my app.
> >>> Thanks again for the quick fix
> >>>
> >>> On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy 
> >>> wrote:
> >>>
>  On 10/18/2017 06:32 PM, Eugene Mirotin wrote:
> > In short the error I get is
> > fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member
> named
> > '__builtin___snprintf_chk'
> >
> > More details in SO question here:
> >
> 
> https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension
>  ,
> > please let me know if I should paste everything in my email.
> >
> > I've seen a similar problem reported before and somehow related to
>  XCode,
> > but that issue was reported to be fixed.
> >
> > Would be thankful for any tips, I haven't used C for years and have
> no
>  idea
> > where to start.
>  Please try with the latest trunk checkin:
> 
>  http://www.sqlite.org/src/info/cd0471ca9f75e7c8
> 
>  (click the "ZIP archive" link to download if you're not using fossil)
> 
>  To generate the sqlite3ext.h and sqlite3.h files required when
> compiling
>  fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something
>  like:
> 
>  ./configure
>  make fts5.c sqlite3.h sqlite3ext.h
>  gcc -O2 -fPIC -shared fts5.c -o fts5.dylib
> 
>  Or, if you want to use a released version, after [make sqlite3ext.h]
>  replace the two instances of "snprintf" in the sqlite3ext.h with
>  "xsnprintf".
> 
>  Dan.
> 
> 
> 
> 
>  ___
>  sqlite-users mailing list
>  sqlite-users@mailinglists.sqlite.org
>  http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems compiling FTS5 extension

2017-10-19 Thread Dan Kennedy

On 10/19/2017 08:40 PM, Eugene Mirotin wrote:

Hm, I think that's because the extension is already preloaded with my
version if sqlite. Which means I didn't even have to build it :)
But now is the question how would I reliably load / not load it in
production env if the target user may or may not have sqlite precompiled
with it?

Right, it's an error to try to register a new version of a module -
"fts5" in this case - that has already been registered.

You could just assume that if the attempt to load fts5 dynamically fails
it is already registered. Or you could try preparing the statement
"SELECT fts5_source_id()" before loading the extension. If preparing the
statement succeeds, fts5 is already present and you don't need to load it.

Don't know why you might be getting the segfault.

Dan.

On Thu, Oct 19, 2017 at 3:30 PM Eugene Mirotin wrote:

Well, now I have troubles loading this extension.

I've built another one before, fts5stemmer.

When using the CLI sqlite3 (version 3.20.1 from MacPorts):

❯ sqlite3
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load './fts5.dylib'
Error: error during initialization:
sqlite> .load './fts5stemmer.dylib'
[1]35952 segmentation fault sqlite3

When using the GUI DB Browser for SQLite:
fts5 extension reports the same error "Error: error during initialization:"
fst5stemmer loads fine (or at least reports to)

On Thu, Oct 19, 2017 at 3:09 PM Eugene Mirotin wrote:

Thanks a lot Dan, that worked!
I'm fine with the trunk version for now but hope to see this in stable
eventually as I'll have to later build this extension for various platforms
for the release of my app.
Thanks again for the quick fix

On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy
wrote:

On 10/18/2017 06:32 PM, Eugene Mirotin wrote:

In short the error I get is
fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named
'__builtin___snprintf_chk'

Re: [sqlite] Problems compiling FTS5 extension

2017-10-19 Thread Eugene Mirotin

Hm, I think that's because the extension is already preloaded with my
version if sqlite. Which means I didn't even have to build it :)
But now is the question how would I reliably load / not load it in
production env if the target user may or may not have sqlite precompiled
with it?

On Thu, Oct 19, 2017 at 3:30 PM Eugene Mirotin  wrote:

> Well, now I have troubles loading this extension.
>
> I've built another one before, fts5stemmer.
>
> When using the CLI sqlite3 (version 3.20.1 from MacPorts):
>
> ❯ sqlite3
> SQLite version 3.20.1 2017-08-24 16:21:36
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .load './fts5.dylib'
> Error: error during initialization:
> sqlite> .load './fts5stemmer.dylib'
> [1]35952 segmentation fault  sqlite3
>
> When using the GUI DB Browser for SQLite:
> fts5 extension reports the same error "Error: error during initialization:"
> fst5stemmer loads fine (or at least reports to)
>
> On Thu, Oct 19, 2017 at 3:09 PM Eugene Mirotin  wrote:
>
>> Thanks a lot Dan, that worked!
>> I'm fine with the trunk version for now but hope to see this in stable
>> eventually as I'll have to later build this extension for various platforms
>> for the release of my app.
>> Thanks again for the quick fix
>>
>> On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy 
>> wrote:
>>
>>> On 10/18/2017 06:32 PM, Eugene Mirotin wrote:
>>> > In short the error I get is
>>> > fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named
>>> > '__builtin___snprintf_chk'
>>> >
>>> > More details in SO question here:
>>> >
>>> https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension
>>> ,
>>> > please let me know if I should paste everything in my email.
>>> >
>>> > I've seen a similar problem reported before and somehow related to
>>> XCode,
>>> > but that issue was reported to be fixed.
>>> >
>>> > Would be thankful for any tips, I haven't used C for years and have no
>>> idea
>>> > where to start.
>>>
>>> Please try with the latest trunk checkin:
>>>
>>>http://www.sqlite.org/src/info/cd0471ca9f75e7c8
>>>
>>> (click the "ZIP archive" link to download if you're not using fossil)
>>>
>>> To generate the sqlite3ext.h and sqlite3.h files required when compiling
>>> fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something
>>> like:
>>>
>>>./configure
>>>make fts5.c sqlite3.h sqlite3ext.h
>>>gcc -O2 -fPIC -shared fts5.c -o fts5.dylib
>>>
>>> Or, if you want to use a released version, after [make sqlite3ext.h]
>>> replace the two instances of "snprintf" in the sqlite3ext.h with
>>> "xsnprintf".
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>> ___
>>> 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] Problems compiling FTS5 extension

2017-10-19 Thread Eugene Mirotin

Well, now I have troubles loading this extension.

I've built another one before, fts5stemmer.

When using the CLI sqlite3 (version 3.20.1 from MacPorts):

❯ sqlite3
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load './fts5.dylib'
Error: error during initialization:
sqlite> .load './fts5stemmer.dylib'
[1]35952 segmentation fault  sqlite3

When using the GUI DB Browser for SQLite:
fts5 extension reports the same error "Error: error during initialization:"
fst5stemmer loads fine (or at least reports to)

On Thu, Oct 19, 2017 at 3:09 PM Eugene Mirotin  wrote:

> Thanks a lot Dan, that worked!
> I'm fine with the trunk version for now but hope to see this in stable
> eventually as I'll have to later build this extension for various platforms
> for the release of my app.
> Thanks again for the quick fix
>
> On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy  wrote:
>
>> On 10/18/2017 06:32 PM, Eugene Mirotin wrote:
>> > In short the error I get is
>> > fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named
>> > '__builtin___snprintf_chk'
>> >
>> > More details in SO question here:
>> >
>> https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension
>> ,
>> > please let me know if I should paste everything in my email.
>> >
>> > I've seen a similar problem reported before and somehow related to
>> XCode,
>> > but that issue was reported to be fixed.
>> >
>> > Would be thankful for any tips, I haven't used C for years and have no
>> idea
>> > where to start.
>>
>> Please try with the latest trunk checkin:
>>
>>http://www.sqlite.org/src/info/cd0471ca9f75e7c8
>>
>> (click the "ZIP archive" link to download if you're not using fossil)
>>
>> To generate the sqlite3ext.h and sqlite3.h files required when compiling
>> fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something like:
>>
>>./configure
>>make fts5.c sqlite3.h sqlite3ext.h
>>gcc -O2 -fPIC -shared fts5.c -o fts5.dylib
>>
>> Or, if you want to use a released version, after [make sqlite3ext.h]
>> replace the two instances of "snprintf" in the sqlite3ext.h with
>> "xsnprintf".
>>
>> Dan.
>>
>>
>>
>>
>> ___
>> 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] Problems compiling FTS5 extension

2017-10-19 Thread Eugene Mirotin

Thanks a lot Dan, that worked!
I'm fine with the trunk version for now but hope to see this in stable
eventually as I'll have to later build this extension for various platforms
for the release of my app.
Thanks again for the quick fix

On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy  wrote:

> On 10/18/2017 06:32 PM, Eugene Mirotin wrote:
> > In short the error I get is
> > fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named
> > '__builtin___snprintf_chk'
> >
> > More details in SO question here:
> >
> https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension
> ,
> > please let me know if I should paste everything in my email.
> >
> > I've seen a similar problem reported before and somehow related to XCode,
> > but that issue was reported to be fixed.
> >
> > Would be thankful for any tips, I haven't used C for years and have no
> idea
> > where to start.
>
> Please try with the latest trunk checkin:
>
>http://www.sqlite.org/src/info/cd0471ca9f75e7c8
>
> (click the "ZIP archive" link to download if you're not using fossil)
>
> To generate the sqlite3ext.h and sqlite3.h files required when compiling
> fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something like:
>
>./configure
>make fts5.c sqlite3.h sqlite3ext.h
>gcc -O2 -fPIC -shared fts5.c -o fts5.dylib
>
> Or, if you want to use a released version, after [make sqlite3ext.h]
> replace the two instances of "snprintf" in the sqlite3ext.h with
> "xsnprintf".
>
> Dan.
>
>
>
>
> ___
> 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] Wrong Column Name in "create table as select"

2017-10-19 Thread Thomas Rohwer


Hello,

with SQLite 3.20.1 2017-08-24 16:21:36 
8d3a7ea6c5690d6b7c3767558f4f01b511c55463e3f9e64506801fe9b74dce34
I am observing the following:

sqlite> create table q as select service from (select v as service from (select 
1 as v));
sqlite> .schema
CREATE TABLE q(v);

I would expect the column name to be "service", not "v", and I think the 
behaviour changed recently.

For comparison, with SQLite version 3.11.0 2016-02-15 17:29:24 I get:

sqlite> create table q as select service from (select v as service from (select 
1 as v));
sqlite> .schema
CREATE TABLE q(service);

This is a simplified example of a similar more complex case, that exhibits the 
same phenomenon.

Sincerely,

Thomas Rohwer

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

Re: [sqlite] Grouping and grabbing one item

2017-10-19 Thread Clemens Ladisch

no...@null.net wrote:
> On Wed Oct 18, 2017 at 07:57:24PM +0200, Darko Volaric wrote:
>> select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1),
>> max(ed),target, sum(amt) from Tasks where Pid=1 group by target
>> HAVING amt > 0;
>
> When I first read that query I wondered if putting a query inside a
> select expression would execute it for each row.

Only correlated subqueries are executed for each row.  All other
subqueries are executed only once (when they are first needed).


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

Re: [sqlite] [BUG] shell: .import: no special characters in table name

[sqlite] [BUG] shell: .import: no special characters in table name

[sqlite] virtual tables, and theTrue meaning of pIdxInfo->estimatedCost, pIdxInfo->estimatedRows, and pIdxInfo->idxFlags...

[sqlite] Inconsistency between sha1 and sha3 extensions

Re: [sqlite] Problems compiling FTS5 extension

Re: [sqlite] Problems compiling FTS5 extension

Re: [sqlite] Problems compiling FTS5 extension

Re: [sqlite] Problems compiling FTS5 extension

Re: [sqlite] Problems compiling FTS5 extension

[sqlite] Wrong Column Name in "create table as select"

Re: [sqlite] Grouping and grabbing one item

11 matches

Site Navigation

Mail list logo

Footer information