Re: [sqlite] Optimizing searches across several indexes

[Clemens Ladisch]

Wout Mertens wrote:
> I have a table with a bunch of data (in json). I want to search on several
> values, each one is indexed. However, if I search for COND1 AND COND2, the
> query plan is simply
>
> SEARCH TABLE data USING INDEX cond1Index (cond1=?)
>
> Is this normal?

Yes.  A query can use only a single index per table.

> Should I be creating indexes differently to cover two dimensions at once?

Create an index on both colums:

  CREATE INDEX cond1and2Index ON data(cond1, cond2);

If you are not using equality comparisons on all but the last columns in
the index, you might need an expression index.  (With JSON, I guess you
are already doing this.)

> Perhaps with r*tree?

That would be useful mainly for multidimensional, numeric range queries
(e.g., "xColumn BETWEEN ? AND ? AND yColumn BETWEEN ? AND ?").


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

[sqlite] Optimizing searches across several indexes

[Wout Mertens]

Hi,

I have a table with a bunch of data (in json). I want to search on several
values, each one is indexed. However, if I search for COND1 AND COND2, the
query plan is simply

SEARCH TABLE data USING INDEX cond1Index (cond1=?)

Is this normal? I was hoping it could use the indexes somehow for both
conditions.

Should I be creating indexes differently to cover two dimensions at once?
Perhaps with r*tree?

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

Re: [sqlite] Error Message "database table is locked"

[Jens Alfke]

> On Aug 8, 2017, at 9:38 PM, Manoj Sengottuvel  wrote:
> 
> In our IOS app - we are getting the error "*database table is locked*"
> while executing the table drop script (e.g. drop table tbl1;).

You may still have a compiled statement (sqlite3_stmt*) for a query that 
references this table. That will prevent the table from being deleted.
If you’ve got a reference to such a statement, free it first by calling 
sqlite3_finalize().
If you don’t, it may have been leaked, i.e. you may have created the statement 
but forgotten to finalize it.

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

[sqlite] Error Message "database table is locked"

[Manoj Sengottuvel]

Hi Richard,

In our IOS app - we are getting the error "*database table is locked*"
while executing the table drop script (e.g. drop table tbl1;).

The same error we are getting for index drop also.

Pl let me know how to fix this issue.


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

Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0

[Simon Slavin]

On 9 Aug 2017, at 3:31am, Jacky Lam  wrote:

> 1. Can I call sqlite3_open more than one times before calling sqlite3_close
> and sqlite3_free?

Call sqlite3_open() for each database you want to open.  You can have any 
number of databases open at the same time.  Call sqlite3_close() for each 
database you have open when you no longer need it.  After closing the last 
connection call sqlite3_shutdown() as described in



(The above ignores use of SQL's ATTACH command.)

You are not expected to ever call sqlite3_free() unless you are using SQLite to 
do other memory-handing tasks for you.  Most people who use SQLite never call 
sqlite3_free().

> 2. If the above mentioned devices change to mutli-thread setting but no
> thread safe functions such as mutex, is this setting still fine?

You have explicitly declared SQLITE_THREADSAFE=0 .  That means you will arrange 
that only one thread will be doing SQLite calls at once.  As long as you can 
ensure this, SQLite will function correctly.

> If not, how can I make it thread safe with lack of mutex support in
> the system?

Do any of the following:

A) Implement your own mutex system.

B) Use SQLite’s mutex system (  )

C) Supply the value SQLITE_OPEN_FULLMUTEX when you open connections using 
sqlite3_open_v2(), as described in  .

Please note that the above is a top-of-the-head answer and I have not 
personally tries each of the options to make sure it works.

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

[sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0

[Jacky Lam]

Hi All,
I am currently doing amalgamation compilation of SQLite and target for low
computational power, portable, single thread devices.
The OS version used in these devices are the very simplified version of
Linux OS. More specifically, it does not have pthread, mutex and
mutli-threading related functions. it has its own specific file system.

Hence to let the SQLite compiles successfully, I set the following MACRO
during compilation:
SQLITE_OS_OTHER=1
SQLITE_THREADSAFE=0
SQLITE_OMIT_LOAD_EXTENSION=1
SQLITE_OMIT_MEMORYDB=1

I would like to know
1. Can I call sqlite3_open more than one times before calling sqlite3_close
and sqlite3_free?
2. If the above mentioned devices change to mutli-thread setting but no
thread safe functions such as mutex, is this setting still fine?
 If not, how can I make it thread safe with lack of mutex support in
the system?

Thanks.

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

Re: [sqlite] hex and char functions

[Rowan Worth]

On 8 August 2017 at 18:32, x  wrote:

> Why does it always turn out to be my own fault ☹
>

Welcome to programming ;)

It's very rare in practice to find an actual bug in a library or language,
especially one as widely deployed and tested as sqlite. Glad you're sorted,
I just wanted to have a quick go at clearing this up:

but finding out that the UTF8 code for the UTF16 code \u0085 is in fact
> \uc285 has tipped me over the edge
>

1. \u0085 is a unicode code-point, 'NEXT LINE (NEL)'
2. \uc285 is a unicode code-point, 'HANGUL SYLLABLE SWIT (솅)'
3. Neither of these is a UTF8 or UTF8 "code" -- UTF8 and UTF16 are
different ways of enconding/decoding unicode code-points
4. In UTF8, \u0085 is encoded as two bytes -- 0xc2 0x85
5. In UTF8, \uc285 is encoded as three bytes -- 0xec 0x8a 0x85

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

Re: [sqlite] Error whilst trying to compile csv.c

[Richard Hipp]

A fix is now on trunk.  https://www.sqlite.org/src/info/795eede331b832a5

On 8/8/17, Richard Hipp  wrote:
> On 8/8/17, Gavin Rayner  wrote:
>> Hi,
>>
>> I’m new to sqlite and C. I get this error whilst trying to compile csv.c
>> on
>> macOS 10.12.6. See http://sqlite.org/csv.html
>> 
>
> It appears that recent versions of XCode have created a macro
> "vsnprintf" in the .  I'm not sure that is allowed.  But
> allowed or not, it does cause a symbol conflict within SQLite, which
> also uses that label.
>
> I do not have a solution just yet.
>
>>
>> gavmacbook:code Gavin$ gcc -g -fPIC -dynamiclib csv.c -o csv.dylib
>> csv.c:115:3: error: no member named '__builtin___vsnprintf_chk' in
>> 'struct
>> sqlite3_api_routines'
>>   sqlite3_vsnprintf(CSV_MXERR, p->zErr, zFormat, ap);
>>   ^
>> /usr/include/sqlite3ext.h:435:53: note: expanded from macro
>> 'sqlite3_vsnprintf'
>> #define sqlite3_vsnprintf  sqlite3_api->vsnprintf
>>~~~  ^
>> /usr/include/secure/_stdio.h:75:3: note: expanded from macro 'vsnprintf'
>>   __builtin___vsnprintf_chk (str, len, 0, __darwin_obsz(str), format, ap)
>>   ^
>> csv.c:115:21: warning: incompatible integer to pointer conversion passing
>> 'int' to parameter of type 'const void *' [-Wint-conversion]
>>   sqlite3_vsnprintf(CSV_MXERR, p->zErr, zFormat, ap);
>> ^
>> csv.c:67:19: note: expanded from macro 'CSV_MXERR'
>> #define CSV_MXERR 200
>>   ^~~
>> /usr/include/secure/_stdio.h:75:57: note: expanded from macro 'vsnprintf'
>>   __builtin___vsnprintf_chk (str, len, 0, __darwin_obsz(str), format, ap)
>> ^~~
>> /usr/include/secure/_common.h:39:54: note: expanded from macro
>> '__darwin_obsz'
>> #define __darwin_obsz(object) __builtin_object_size (object,
>> _USE_FORTIFY_LEVEL > 1 ? 1 : 0)
>>  ^~
>> 1 warning and 1 error generated.
>>
>>
>> Any assistance would be greatly appreciated.
>>
>> Regards,
>>
>>
>> Gavin Rayner
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>


-- 
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] Error whilst trying to compile csv.c

[Richard Hipp]

On 8/8/17, Gavin Rayner  wrote:
> Hi,
>
> I’m new to sqlite and C. I get this error whilst trying to compile csv.c on
> macOS 10.12.6. See http://sqlite.org/csv.html 

It appears that recent versions of XCode have created a macro
"vsnprintf" in the .  I'm not sure that is allowed.  But
allowed or not, it does cause a symbol conflict within SQLite, which
also uses that label.

I do not have a solution just yet.

>
> gavmacbook:code Gavin$ gcc -g -fPIC -dynamiclib csv.c -o csv.dylib
> csv.c:115:3: error: no member named '__builtin___vsnprintf_chk' in 'struct
> sqlite3_api_routines'
>   sqlite3_vsnprintf(CSV_MXERR, p->zErr, zFormat, ap);
>   ^
> /usr/include/sqlite3ext.h:435:53: note: expanded from macro
> 'sqlite3_vsnprintf'
> #define sqlite3_vsnprintf  sqlite3_api->vsnprintf
>~~~  ^
> /usr/include/secure/_stdio.h:75:3: note: expanded from macro 'vsnprintf'
>   __builtin___vsnprintf_chk (str, len, 0, __darwin_obsz(str), format, ap)
>   ^
> csv.c:115:21: warning: incompatible integer to pointer conversion passing
> 'int' to parameter of type 'const void *' [-Wint-conversion]
>   sqlite3_vsnprintf(CSV_MXERR, p->zErr, zFormat, ap);
> ^
> csv.c:67:19: note: expanded from macro 'CSV_MXERR'
> #define CSV_MXERR 200
>   ^~~
> /usr/include/secure/_stdio.h:75:57: note: expanded from macro 'vsnprintf'
>   __builtin___vsnprintf_chk (str, len, 0, __darwin_obsz(str), format, ap)
> ^~~
> /usr/include/secure/_common.h:39:54: note: expanded from macro
> '__darwin_obsz'
> #define __darwin_obsz(object) __builtin_object_size (object,
> _USE_FORTIFY_LEVEL > 1 ? 1 : 0)
>  ^~
> 1 warning and 1 error generated.
>
>
> Any assistance would be greatly appreciated.
>
> Regards,
>
>
> Gavin Rayner
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Error whilst trying to compile csv.c

[Gavin Rayner]

Hi,

I’m new to sqlite and C. I get this error whilst trying to compile csv.c on 
macOS 10.12.6. See http://sqlite.org/csv.html 

gavmacbook:code Gavin$ gcc -g -fPIC -dynamiclib csv.c -o csv.dylib
csv.c:115:3: error: no member named '__builtin___vsnprintf_chk' in 'struct 
sqlite3_api_routines'
  sqlite3_vsnprintf(CSV_MXERR, p->zErr, zFormat, ap);
  ^
/usr/include/sqlite3ext.h:435:53: note: expanded from macro 'sqlite3_vsnprintf'
#define sqlite3_vsnprintf  sqlite3_api->vsnprintf
   ~~~  ^
/usr/include/secure/_stdio.h:75:3: note: expanded from macro 'vsnprintf'
  __builtin___vsnprintf_chk (str, len, 0, __darwin_obsz(str), format, ap)
  ^
csv.c:115:21: warning: incompatible integer to pointer conversion passing 'int' 
to parameter of type 'const void *' [-Wint-conversion]
  sqlite3_vsnprintf(CSV_MXERR, p->zErr, zFormat, ap);
^
csv.c:67:19: note: expanded from macro 'CSV_MXERR'
#define CSV_MXERR 200
  ^~~
/usr/include/secure/_stdio.h:75:57: note: expanded from macro 'vsnprintf'
  __builtin___vsnprintf_chk (str, len, 0, __darwin_obsz(str), format, ap)
^~~
/usr/include/secure/_common.h:39:54: note: expanded from macro '__darwin_obsz'
#define __darwin_obsz(object) __builtin_object_size (object, _USE_FORTIFY_LEVEL 
> 1 ? 1 : 0)
 ^~
1 warning and 1 error generated.


Any assistance would be greatly appreciated.

Regards,


Gavin Rayner


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

Re: [sqlite] LSM1 extension

[Charles Leifer]

There's some information that may be of interest on the sqlite4 wiki:

* Design overview: https://sqlite.org/src4/doc/trunk/www/lsm.wiki
* User guide: https://sqlite.org/src4/doc/trunk/www/lsmusr.wiki
* API: https://sqlite.org/src4/doc/trunk/www/lsmapi.wiki

My interest in this particular feature stems from my having written a set
of python bindings to the sqlite4 implementation (to be used as a sort of
embedded ordered key/value storage engine). I've wondered about the
possibility of exposing these APIs as a virtual table, and so seeing this
extension really excited me. So obviously my interest is much greater than
that of the average SQLite user :)

On Tue, Aug 8, 2017 at 12:54 PM, x  wrote:

> Thanks Richard.
>
> From: Richard Hipp
> Sent: 08 August 2017 18:47
> To: SQLite mailing list
> Subject: Re: [sqlite] LSM1 extension
>
> On 8/8/17, x  wrote:
> > I keep reading about this LSM1 but can’t find anything about it beyond
> > tickets. Any links?
>
> Baseline technology: https://en.wikipedia.org/wiki/
> Log-structured_merge-tree
>
> No documentation yet on the LSM1 implementation in SQLite.
>
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE PHP syntax issues - INSERT INTO db

[Stephen Chrzanowski]

Awesome find Warren.  Thanks.

On Tue, Aug 8, 2017 at 4:08 PM, Warren Young  wrote:

> On Aug 8, 2017, at 11:06 AM, Jens Alfke  wrote:
> >
> > On Aug 8, 2017, at 10:03 AM, Igor Korot  wrote:
> >>
> >> Just google "Jonny Drop All Tables". ;-)
> >
> > “Little Bobby Tables” to be precise; here’s a direct link:
>
> Little Bobby has a web site now: http://bobby-tables.com/
>
> ‘E’s all growed up!  :)
>
> ___
> 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] SQLITE PHP syntax issues - INSERT INTO db

[Stephen Chrzanowski]

To add on to what Jens mentions, with PHP, you can at least sanitize the
$NewID by using (integer)$NewID.  Any string or non integer that is
assigned to $NewID will get converted to the integer value zero.  From my
experience, typically IDs aren't stored as zero, but I've not looked at
EVERY database out there, so there could still be consequences.  Just know
your data.

With strings, you can use mysql_real_escape (Or something of the sort --
Been a few months since I've dealt with PHP/MySQL directly) which will
convert any string into a SQL safe string.  There are additional things you
can do to sanitize what you're inputting, but it'd depend on what codepage
you're putting the data in as, and where you're getting the data from.  I
only have ever dealt with ASCII, sanitize as ASCII, and strip out any other
character that doesn't fit between #01 and #FF.  Unicode is not in my
vocabulary, since I don't write in any other spoken language that doesn't
fit within 8-bits.

There is also binding you can do, as Jens mentions.  I've infrequently done
this as I've written routines that do the sanitizing for me, so I don't
need to think about it, and its a PDO thing.

On Tue, Aug 8, 2017 at 12:58 PM, Jens Alfke  wrote:

>
> > On Aug 5, 2017, at 6:48 AM, Edmondo Borasio 
> wrote:
> >
> > *$query1="INSERT INTO Table"."(ID,name,surname)"."VALUES(\' ' . $NewID .
> > '\','newName','newSurname');"; *
>
> It’s a very, very bad idea to insert variable strings directly into a SQL
> query like this. If the content of those strings is unknown or untrusted
> data (as it usually is), it leaves you wide open to SQL Injection Attacks,
> which give an attacker full access to your database. This is probably the
> single most common form of attack against web applications.
>
> Your PHP SQLite API includes facilities for safely plugging variables into
> the query, similar to printf. You put a placeholder like “?” into the SQL
> string and then pass the actual value as a separate parameter to the PHP
> function. That’s the right way to do it. (As a bonus, it lets you
> precompile the query and reuse it, which speeds up your code.)
>
> —Jens
> ___
> 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] SQLITE PHP syntax issues - INSERT INTO db

[Warren Young]

On Aug 8, 2017, at 11:06 AM, Jens Alfke  wrote:
> 
> On Aug 8, 2017, at 10:03 AM, Igor Korot  wrote:
>> 
>> Just google "Jonny Drop All Tables". ;-)
> 
> “Little Bobby Tables” to be precise; here’s a direct link:

Little Bobby has a web site now: http://bobby-tables.com/

‘E’s all growed up!  :)

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Nico Williams]

On Tue, Aug 08, 2017 at 10:30:33AM -0700, Jens Alfke wrote:
> > On Aug 4, 2017, at 11:28 AM, Nico Williams  wrote:
> > Imagine a mode where there is only a WAL, and to checkpoint is to write
> > a new WAL with only live contents and... rename(2) into place.
> 
> What you’re describing is exactly how CouchDB’s storage engine works,
> as well as descendants like Couchbase Server’s CouchStore and
> ForestDB. (Note: I work for Couchbase.)

Also how ZFS works.

> Efficient lookups in a file like this require the existence of a bunch
> of extraneous metadata like interior B-tree nodes. This metadata
> changes all the time as records are written*, so a lot of it has to be
> written out too along with every transaction, resulting in substantial
> write amplification.

Yes.  It's called write magnification.  ZFS deals with this by first
committing to an intent log (ZIL) without writing all those interior
nodes, then it eventually writes a proper transaction.

One could do the same sort of thing for a single-file DB: write a number
of transactions as intents, then once in a while "checkpoint" them by
writing a b-tree transaction that includes all those updates.  For
readers this means always processing all intent log entries since the
last b-tree-updating transaction.

LSMs are... a lot lik this, IIUC.  Are they not?

> The other big drawback is that compaction (the checkpoint step you
> describe) is very expensive in terms of I/O. I’ve known of CouchDB
> systems that took many hours to compact their databases, and since
> every write that occurs during a compaction has to be replayed onto
> the new file after the copy before compaction completes, one can get
> into a state where a busy database either never actually finishes
> compacting, or has to temporarily block all writers just so it can get
> the damn job done without interruption. (It’s a similar problem to GC
> thrash.)

There's definitely trade-offs.  This is already the case for SQLite3's
WAL.  You have to checkpoint often, but when you do you lose read
concurrency.  When you value read concurrency highly you might tune the
WAL max size up to reduce checkpoint frequence, and now you slow down
checkpointing.

Another thing that can be done is to write to the "next" DB as soon as
possible, possibly synchronously with writes to the "current" DB.  Then
the checkpoint process is very simple: write the "close" TX to the
"current" DB, rename the "next" into place, create the next "next".

> We’ve also seen that, on low-end hardware like mobile devices, I/O
> bandwidth is limited enough that a running compaction can really harm
> the responsiveness of the _entire OS_, as well as cause significant
> battery drain.

Yes.  But there you don't really need read concurrency, so SQLite3
without a WAL (or with a WALL but frequent checkpointing) will suffice.

> * Modifying/rewriting a single record requires rewriting the leaf node
> that points to it, which requires rewriting the parent node that
> points to the leaf, and this ripples all the way up to the root node.

I'm well aware :)  Others on the list might not, naturally.

One of the very nice features of an append-only single-file DB file
format is that you can just "tail -f" (well, a binary tail -f) to
replicate.  If you add in the intent log concept, it's not so bad in
terms of I/O, but you slow down readers somewhat.

Another very nice feature of an append-only single-file DB file is high
read concurrency.  And yet another is fast recovery (throw away a
truncated transaction and "checkpoint").

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

Re: [sqlite] LSM1 extension

[x]

Thanks Richard.

From: Richard Hipp
Sent: 08 August 2017 18:47
To: SQLite mailing list
Subject: Re: [sqlite] LSM1 extension

On 8/8/17, x  wrote:
> I keep reading about this LSM1 but can’t find anything about it beyond
> tickets. Any links?

Baseline technology: https://en.wikipedia.org/wiki/Log-structured_merge-tree

No documentation yet on the LSM1 implementation in SQLite.

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

Re: [sqlite] LSM1 extension

[Richard Hipp]

On 8/8/17, x  wrote:
> I keep reading about this LSM1 but can’t find anything about it beyond
> tickets. Any links?

Baseline technology: https://en.wikipedia.org/wiki/Log-structured_merge-tree

No documentation yet on the LSM1 implementation in SQLite.

-- 
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] LSM1 extension

[x]

I keep reading about this LSM1 but can’t find anything about it beyond tickets. 
Any links?

From: Jens Alfke
Sent: 08 August 2017 18:04
To: SQLite mailing list
Subject: Re: [sqlite] LSM1 extension


> On Aug 4, 2017, at 9:12 PM, Charles Leifer  wrote:
>
> Right -- my question is still unanswered as to why the code was merged in.

It’s not uncommon in software engineering to put unfinished long-running 
feature work on the main development branch but disable it with a flag. (It was 
standard policy in one of the projects I worked on at Google.) This helps keep 
the mainline code and unfinished code from diverging, and it simplifies testing 
of the unfinished code.

I’m eager for LSM1 too, but I’m waiting for it to show up in the release notes 
:)

—Jens
___
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] Thinking about a way to extend the number of writers in WAL mode

[Simon Slavin]

On 8 Aug 2017, at 6:30pm, Jens Alfke  wrote:

> We’ve also seen that, on low-end hardware like mobile devices, I/O bandwidth 
> is limited enough that a running compaction can really harm the 
> responsiveness of the _entire OS_, as well as cause significant battery drain.

Yes.  Cannot stress enough that you don’t need VACUUM for efficient storage.  
It locks the entire database, it monopolises access to storage, and it does 
many writes to storage which means it’ll wear through Flash storage cycles.  
What used to be the great advantage of VACUUM — defragmentation — was useful 
for hard disks but does not give any advantage for Flash storage.

The only advantages are to save filespace immediately after deleting data or 
indexes.  You might want VACUUM if you delete data, then take a copy of your 
database for backup.  But most SQLite databases are small enough that this 
doesn’t matter.

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Jens Alfke]

> On Aug 4, 2017, at 11:28 AM, Nico Williams  wrote:
> 
> Imagine a mode where there is only a WAL, and to checkpoint is to write
> a new WAL with only live contents and... rename(2) into place.

What you’re describing is exactly how CouchDB’s storage engine works, as well 
as descendants like Couchbase Server’s CouchStore and ForestDB. (Note: I work 
for Couchbase.)

Efficient lookups in a file like this require the existence of a bunch of 
extraneous metadata like interior B-tree nodes. This metadata changes all the 
time as records are written*, so a lot of it has to be written out too along 
with every transaction, resulting in substantial write amplification.

The other big drawback is that compaction (the checkpoint step you describe) is 
very expensive in terms of I/O. I’ve known of CouchDB systems that took many 
hours to compact their databases, and since every write that occurs during a 
compaction has to be replayed onto the new file after the copy before 
compaction completes, one can get into a state where a busy database either 
never actually finishes compacting, or has to temporarily block all writers 
just so it can get the damn job done without interruption. (It’s a similar 
problem to GC thrash.)

We’ve also seen that, on low-end hardware like mobile devices, I/O bandwidth is 
limited enough that a running compaction can really harm the responsiveness of 
the _entire OS_, as well as cause significant battery drain.

—Jens

* Modifying/rewriting a single record requires rewriting the leaf node that 
points to it, which requires rewriting the parent node that points to the leaf, 
and this ripples all the way up to the root node.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: Nested function call to replace() throws parser stack overflow exception

[petern]

Hey Brian.  I had some time this morning to look at it.  Try whacking this
new concat_replace into your SQLite build in either func.c or sqlite3.c
amalgam.  Also remember to hook it up with a new entry after say,
group_concat, as follows.

AGGREGATE(group_replace, 3, 0, 0,
groupReplaceStep,groupReplaceFinalize),

[This is a first pass so there could be an edge case with NULL/empty that I
missed... pls test it!  Honestly, the original replace() that I borrowed
from might also be a bit shaky with the NULL/empty edge cases.]

Here's a tester snippet you can play with:

sqlite> WITH T(pattern,replacement) AS (VALUES
('a','This'),('b','is'),('c','a'),('d','test')) SELECT group_replace('a b c
d',pattern,replacement) FROM T;
This is a test

---all C code below this point---

struct StrRepl {
  const unsigned char* zStr;
  u32 nStr;
  u8 replError; /* STRREPL_NOMEM or STRREPL_TOOBIG */
};
typedef struct StrRepl StrRepl;
#define STRREPL_NOMEM   1
#define STRREPL_TOOBIG  2

/*
 ** Aggregate group_replace(A,B,C) result string is derived from A by
replacing
 ** every exact match occurrence of B with C.  A is presumed constant over
the group
 ** while B and C may vary at each step.  Collating sequences are not used.
 */
static void groupReplaceStep(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
  ) {

  const unsigned char *zStr; /* The input string A */
  const unsigned char *zPattern; /* The pattern string B */
  const unsigned char *zRepl; /* The replacement string C */
  unsigned char *zOut; /* The output */
  int nStr; /* Size of zStr */
  int nPattern; /* Size of zPattern */
  int nRepl; /* Size of zRep */
  i64 nOut; /* Maximum size of zOut */
  int loopLimit; /* Last zStr[] that might match zPattern[] */
  int i, j; /* Loop counters */

  assert(argc == 3);
  UNUSED_PARAMETER(argc);
  zStr = sqlite3_value_text(argv[0]);
  if (zStr == 0) return;
  nStr = sqlite3_value_bytes(argv[0]);
  assert(zStr == sqlite3_value_text(argv[0])); /* No encoding change */
  zPattern = sqlite3_value_text(argv[1]);
  if (zPattern == 0) {
assert(sqlite3_value_type(argv[1]) == SQLITE_NULL
  || sqlite3_context_db_handle(context)->mallocFailed);

return;
  }
  if (zPattern[0] == 0) {
assert(sqlite3_value_type(argv[1]) != SQLITE_NULL);
sqlite3_result_value(context, argv[0]);
return;
  }
  nPattern = sqlite3_value_bytes(argv[1]);
  assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change */
  zRepl = sqlite3_value_text(argv[2]);
  if (zRepl == 0) return;
  nRepl = sqlite3_value_bytes(argv[2]);
  assert(zRepl == sqlite3_value_text(argv[2]));

  StrRepl* pStrRepl = (StrRepl*) sqlite3_aggregate_context(context, sizeof
(StrRepl));
  if (pStrRepl) {
if (pStrRepl->replError) return;
if (!pStrRepl->zStr) {
  if (sqlite3_value_type(argv[0]) == SQLITE_NULL
|| sqlite3_value_type(argv[1]) == SQLITE_NULL) return;
  pStrRepl->nStr = sqlite3_value_bytes(argv[0]);
  pStrRepl->zStr = contextMalloc(context,pStrRepl->nStr);

memcpy((void*)pStrRepl->zStr,sqlite3_value_text(argv[0]),pStrRepl->nStr);
/* No encoding change */
}
zPattern = sqlite3_value_text(argv[1]);
nPattern = sqlite3_value_bytes(argv[1]);
assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change
*/
if (0 == pStrRepl->nStr || 0 == nPattern) return;
zRepl = sqlite3_value_text(argv[2]);
nRepl = sqlite3_value_bytes(argv[2]);
if (0 == zRepl) return;
nOut = pStrRepl->nStr + 1;
assert(nOut < SQLITE_MAX_LENGTH);
zOut = contextMalloc(context, (i64) nOut);
if (0 == zOut) return;
loopLimit = pStrRepl->nStr - nPattern;
for (i = j = 0; i <= loopLimit; i++) {
  if (pStrRepl->zStr[i] != zPattern[0] || memcmp(&(pStrRepl->zStr)[i],
zPattern, nPattern)) {
zOut[j++] = pStrRepl->zStr[i];
  } else {
u8 *zOld;
sqlite3 *db = sqlite3_context_db_handle(context);
nOut += nRepl - nPattern;
testcase(nOut - 1 == db->aLimit[SQLITE_LIMIT_LENGTH]);
testcase(nOut - 2 == db->aLimit[SQLITE_LIMIT_LENGTH]);
if (nOut - 1 > db->aLimit[SQLITE_LIMIT_LENGTH]) {
  pStrRepl->replError = STRREPL_TOOBIG;
  sqlite3_free(zOut);
  return;
}
zOld = zOut;
zOut = sqlite3_realloc64(zOut, (int) nOut);
if (zOut == 0) {
  pStrRepl->replError = STRREPL_NOMEM;
  sqlite3_free(zOld);
  return;
}
memcpy(&zOut[j], zRepl, nRepl);
j += nRepl;
i += nPattern - 1;
  }
}
assert(j + pStrRepl->nStr - i + 1 == nOut);
memcpy(&zOut[j], &pStrRepl->zStr[i], pStrRepl->nStr - i);
j += pStrRepl->nStr - i;
assert(j <= nOut);
zOut[j] = 0;
void* pFree = (void*)pStrRepl->zStr;
pStrRepl->zStr = zOut;
pStrRepl->nStr = nOut;
sqlite3_free(pFree);
  }
}

static void groupReplaceFinalize(sqlite3_context *context) {
  const char **pzVal;
  StrRepl* pStrRepl = sqlite3_aggregate_context(context, 0);
  if (p

Re: [sqlite] SQLITE PHP syntax issues - INSERT INTO db

[Jens Alfke]

> On Aug 8, 2017, at 10:03 AM, Igor Korot  wrote:
> 
> Just google "Jonny Drop All Tables". ;-)

“Little Bobby Tables” to be precise; here’s a direct link:
https://xkcd.com/327/ 

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

Re: [sqlite] LSM1 extension

[Jens Alfke]

> On Aug 4, 2017, at 9:12 PM, Charles Leifer  wrote:
> 
> Right -- my question is still unanswered as to why the code was merged in.

It’s not uncommon in software engineering to put unfinished long-running 
feature work on the main development branch but disable it with a flag. (It was 
standard policy in one of the projects I worked on at Google.) This helps keep 
the mainline code and unfinished code from diverging, and it simplifies testing 
of the unfinished code.

I’m eager for LSM1 too, but I’m waiting for it to show up in the release notes 
:)

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

Re: [sqlite] SQLITE PHP syntax issues - INSERT INTO db

[Igor Korot]

Hi,

On Tue, Aug 8, 2017 at 12:58 PM, Jens Alfke  wrote:
>
>> On Aug 5, 2017, at 6:48 AM, Edmondo Borasio  wrote:
>>
>> *$query1="INSERT INTO Table"."(ID,name,surname)"."VALUES(\' ' . $NewID .
>> '\','newName','newSurname');"; *
>
> It’s a very, very bad idea to insert variable strings directly into a SQL 
> query like this. If the content of those strings is unknown or untrusted data 
> (as it usually is), it leaves you wide open to SQL Injection Attacks, which 
> give an attacker full access to your database. This is probably the single 
> most common form of attack against web applications.

Yup.
Just google "Jonny Drop All Tables". ;-)

Thank you.

>
> Your PHP SQLite API includes facilities for safely plugging variables into 
> the query, similar to printf. You put a placeholder like “?” into the SQL 
> string and then pass the actual value as a separate parameter to the PHP 
> function. That’s the right way to do it. (As a bonus, it lets you precompile 
> the query and reuse it, which speeds up your code.)
>
> —Jens
> ___
> 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] SQLITE PHP syntax issues - INSERT INTO db

[Jens Alfke]

> On Aug 5, 2017, at 6:48 AM, Edmondo Borasio  wrote:
> 
> *$query1="INSERT INTO Table"."(ID,name,surname)"."VALUES(\' ' . $NewID .
> '\','newName','newSurname');"; *

It’s a very, very bad idea to insert variable strings directly into a SQL query 
like this. If the content of those strings is unknown or untrusted data (as it 
usually is), it leaves you wide open to SQL Injection Attacks, which give an 
attacker full access to your database. This is probably the single most common 
form of attack against web applications.

Your PHP SQLite API includes facilities for safely plugging variables into the 
query, similar to printf. You put a placeholder like “?” into the SQL string 
and then pass the actual value as a separate parameter to the PHP function. 
That’s the right way to do it. (As a bonus, it lets you precompile the query 
and reuse it, which speeds up your code.)

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

Re: [sqlite] hex and char functions

[x]

Just received this on Embarcadero forum.

You are assigning an Ansi literal string to a Unicode string so a conversion 
from the current Ansi locale is occurring.
Try assigning a unicode literal:

UnicodeString S= L"\u0085";

That L in front of the string makes Code==133 and it’s also 133 inside the udf 
after retrieval using sqlite3_value_text16.

Why does it always turn out to be my own fault ☹

Many thanks to all who contributed.




From: x
Sent: 08 August 2017 09:24
To: SQLite mailing list
Subject: Re: [sqlite] hex and char functions

I accidentally deleted the latest 3 replies to this thread in my mail so 
apologies for not including any content from those mails.

Ryan, the error happens immediately in c++ builder. E.g.

UnicodeString S=”\u0085”;
Int Code=S[1];

Code returns 63 (x3F) which is code for ‘?’ and the question mark is also what 
shows when I hover the cursor over S during debugging.
As u+0085 is a control character, and so has no associated graphic, I can 
forgive the ‘?’ being printed in its place but why does the underlying Code 
change? I have posted this question on the c++ builder website and will post 
any answer here.


Simon’s timing with his comment about debugging is spooky. Last night I found 
that when I try to ‘Evaluate/Modify’ S[1] during debugging I get the following 
error message

E2015 Ambiguity between 'System::UnicodeString::System::UnicodeString::operator 
[](const int)' and 'System::UnicodeString::System::UnicodeString::operator 
[](const int)'

Ambiguity between X and X?? I had no such problems earlier yesterday but, as it 
was working fine when I first encountered the ‘\u0085’ problem, I doubt it’s 
the debugger.


I conducted the following experiment in SQLite Expert Pro by creating a couple 
of new empty databases.
select hex(char(65,133,66))
returned
'41C28542' when the current db was utf-8
'410085004200' when the current db was utf-16le
'004100850042' when the current db was utf-16be

The bottom one of those is what I expected when I first ran the select yet I 
think I'm right in saying my system (windows 10) is little endian. This is a 
further confusion thrown into the mix for me. As is usually the case. I've 
little doubt sqlite is coming up with the right answer in each case.

Tom
___
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] hex and char functions

[x]

I accidentally deleted the latest 3 replies to this thread in my mail so 
apologies for not including any content from those mails.

Ryan, the error happens immediately in c++ builder. E.g.

UnicodeString S=”\u0085”;
Int Code=S[1];

Code returns 63 (x3F) which is code for ‘?’ and the question mark is also what 
shows when I hover the cursor over S during debugging.
As u+0085 is a control character, and so has no associated graphic, I can 
forgive the ‘?’ being printed in its place but why does the underlying Code 
change? I have posted this question on the c++ builder website and will post 
any answer here.


Simon’s timing with his comment about debugging is spooky. Last night I found 
that when I try to ‘Evaluate/Modify’ S[1] during debugging I get the following 
error message

E2015 Ambiguity between 'System::UnicodeString::System::UnicodeString::operator 
[](const int)' and 'System::UnicodeString::System::UnicodeString::operator 
[](const int)'

Ambiguity between X and X?? I had no such problems earlier yesterday but, as it 
was working fine when I first encountered the ‘\u0085’ problem, I doubt it’s 
the debugger.


I conducted the following experiment in SQLite Expert Pro by creating a couple 
of new empty databases.
select hex(char(65,133,66))
returned
'41C28542' when the current db was utf-8
'410085004200' when the current db was utf-16le
'004100850042' when the current db was utf-16be

The bottom one of those is what I expected when I first ran the select yet I 
think I'm right in saying my system (windows 10) is little endian. This is a 
further confusion thrown into the mix for me. As is usually the case. I've 
little doubt sqlite is coming up with the right answer in each case.

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