[sqlite] Find if an upsert operation did an insert or an update.

[Constantine Yannakopoulos]

Hello,

I would like to find whether an upsert operation actually did an insert or
an update, preferably without having to execute extra SQL statements before
or after it. I thought of using last_insert_rowid() before and after the
upsert and check if the result has changed but while this seems to work for
normal tables it will not work for WITHOUT ROWID tables. Is there another
way that works consistently both with tables with or without rowid?

Best regards,

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

Re: [sqlite] Pragma table-valued functions used in views in an attached database.

[Constantine Yannakopoulos]

On Sun, Feb 12, 2017 at 12:53 PM, Simon Slavin  wrote:
> You might have missed
>
> 
>
> which, despite its name, also works with views.

No, this returns the columns of a single table. Suggested pragma
table_list/view_list would return all tables/views in a database.

I know I can get that from sqlite_master, but having to prefix it with
the database name as part of the query syntax:

  select * from attached_db.sqlite_master;

makes it impossible to join with pragma database_list:

  select * from pragma_database_list as dblist, dblist.name.sqlite_master;

cannot work while

  select * from pragma_database_list as dblist,
pragma_table_list(dblist.name) as tbllist;

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

[sqlite] Pragma table-valued functions used in views in an attached database.

[Constantine Yannakopoulos]

Hi,

Trying to create an attached information schema using the instructions
in the additional notes of the "Pragma functions" section of
https://www.sqlite.org/pragma.html I have come across an issue. I
execute the following statements in any sqlite database:

attach :memory: as info;

drop view if exists info.table_columns;

create view info.table_columns as
select
  tbl_name as table_name,
  c.cid as column_id,
  c.name as column_name,
  c."type" as "type",
  c."notnull" as "notnull",
  c.dflt_value,
  c.pk
from sqlite_master m, pragma_table_info(m.tbl_name) c
where m.type = 'table';

Then, when I execute:

select * from table_columns;
or
select * from info.table_columns;

I get the error 'no such table: info.pragma_table_info'. But when I
execute the view's query directly I get the expected results. I get a
similar error if I try to use the example provided in the document:

CREATE VIEW info.indexed_columns AS
SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
FROM sqlite_master AS m,
pragma_index_list(m.name) AS il,
pragma_index_info(il.name) AS ii
WHERE m.type='table'
ORDER BY 1;

If I create the view in the main database it works as expected but I
would prefer to be able to create it in an attached memory database so
that it does not persist in the database file.

Am I doing something wrong/not supported?

One more note: I believe it is impossible to construct a result set
that contains all databases (main and attached) and all objects (e.g.
tables) within each database with a single SQL statement that works
without "knowledge" of the attached databases (else it is easy with a
union). If someone knows a way please let me know. If there isn't,
perhaps the addition of two more pragmas:

  pragma table_list(database-name);
  pragma view_list(database-name);

would help.

PS: I know all this is possible by creating eponymous virtual tables
but the difficulty level for this is much higher.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange behaviour of IN/NOT IN subqueries?

[Constantine Yannakopoulos]

Hello SQLite team,

I have two tables, "item" (items on sale) where a column named "itemcode"
is the PK and "tmp_salesitm" (invoice item lines) where the foreign key
column to "item" is called "itemid" and not "itemcode" for some reason. I
wanted to find all records from the first with no matching records in the
second (i.e. items that have never been sold). So I hastily typed:

  select * from item where itemcode not in (select itemcode from
tmp_salesitm)

forgetting that the column name was different. To my amazement the query
returned ALL rows from table "item" despite the fact that "tmp_salesitm"
has several matching records. After investigation I located the name
mismatch, corrected the query to:

  select * from item where itemcode not in (select itemid from tmp_salesitm)

ran it and it returned the correct results.

So I wondered, shouldn't the first query throw an error instead of
returning a result that does not make sense? Then I thought that the SQL
parser may have interpreted "itemcode" in the subquery as a reference to
the "itemcode" column of the outer query. So I changed "itemcode" to "xxx"
and I got an error I was expecting.

My question is, is this behaviour normal? Should a NOT IN subquery, that
uses a different from clause and is -to my knowledge- not correlated, be
allowed to select columns of the outer query's FROM tables? Shouldn't an
error be raised instead or am I missing something?

FYI the outcome is similar if I replace "NOT IN" with "IN". With:

  select * from item where itemcode in (select itemcode from tmp_salesitm)

I get zero records and with:

  select * from item where itemcode in (select itemid from tmp_salesitm)

I get the correct matching records. Also, the "explain query plan" command
shows a single full table scan on "item" for the first query and a more
"reasonable" plan for the second query.

I use SQLite version 3.8.11 in Windows 7.

Thanks in advance and apologies if this has been asked before.

--
Constantine

[sqlite] Using collation instead a virtual table

[Constantine Yannakopoulos]

On Wed, Sep 9, 2015 at 10:59 PM, Scott Doctor  wrote:

>
> Best practice when dealing with floating point is to normalize and Chop.


In my line of business (ledger/ERP/retail) I don't think I can normalize
but chopping is definitely performed ?on each operation that can produce
rounding errors. Luckily the number of decimal digits is fixed (almost
always 3) and one can easily detect when there is a problem with rounding.
However, there are cases like the one I described above (distribution)
where deviations are inevitable, but it is usually acceptable to fix by
slightly violating the rules, e.g. calculating the rounding error and
adding it to a single row's amount in order to balance it out.

But I feel I have hijacked the thread so I think I'd better stop.

[sqlite] Using collation instead a virtual table

[Constantine Yannakopoulos]

On Wed, Sep 9, 2015 at 9:47 PM, R.Smith  wrote:

>
> On 2015-09-09 05:19 PM, Constantine Yannakopoulos wrote:
>
>> On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik 
>> wrote:
>>
>> ?Out of curiosity, doesn't this also apply also to numeric (real number)
>> comparisons since SQLite3 uses IEEE floating point arithmetic??
>>
>
> IEEE Float comparisons do not work this way - you are more likely to find
> the opposite:  two numbers that seem to be near perfectly equal might fail
> an equality test.
>

?That is the problem. There are cases where two numbers that come out of
different calculations? (especially if a division is included) are expected
to be equal but they fail the equality test. A classic case is when you
distribute an amount (e.g. a total) among several rows using a certain
column as weight and you expect the sum of the distributed amounts to be
exactly equal to the original total but it is not.


> On Wed, Sep 9, 2015 at 7:47 PM, Igor Tandetnik  wrote:
> What aspect of IEEE floating point arithmetic makes comparisons unsafe, in
> your opinion? Given two IEEE numbers (NaNs and INFs excepted), the
> comparison would only ever declare them equal if their representations are
> bit-for-bit identical; it doesn't play "close enough" games. What again
> seems to be the problem?
>

?OK, I was under the impression that SQLite ?used an epsilon comparison to
avoid the aforementioned case. Obviously I was wrong. Sorry. As I said, no
problem, just curiosity.

[sqlite] Using collation instead a virtual table

[Constantine Yannakopoulos]

On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik  wrote:

> A comparison like this would not generally be a proper collation. The
> equivalence relation it induces is not transitive - it's possible to have A
> == B and B == C but A != C (when A is "close enough" to B and B is "close
> enough" to C, but A and C are just far enough from each other).
>

?Out of curiosity, doesn't this also apply also to numeric (real number)
comparisons since SQLite3 uses IEEE floating point arithmetic??

[sqlite] busy_timeout() not working always?

[Constantine Yannakopoulos]

Thanks for you reply Keith,

On Fri, Jul 10, 2015 at 1:30 AM, Keith Medcalf  wrote:

>
> Turn off shared-cache mode.
>
> Shared-cache is designed for use on itty-bitty-tiny computers with only a
> few KB of RAM and very paltry CPU resources.  On equipment for which shared
> cache was designed, you are unable to run "intense database actions" on
> multiple threads, and are therefore unlikely to trip over the additional
> limitations it imposes.
>

?My server is a hand-written application server, for all intents and
purposes you may consider it as a web server (e,g, Apache) serving and
managing database data for? many concurrent clients from the same database
file. I implemented shared-cache mode not? as much for memory conservation
but to facilitate table-level locking instead of database-wide locking.

I finally managed to solve the problem by incorporating a manual retry loop
with exponential back-off logic in the equivalents of blocking_step() and
blocking_prepare_v2() (see https://www.sqlite.org/unlock_notify.html). I
can disable shared-cache just by altering a server config file, but this
would mean retesting the whole application, especially in high-contention
scenarios, because the database locking model will have significantly
changed, so I would prefer to keep shared-cache and table-level locking if
I can. If anyone can think of another reason why ? shouldn't please speak
up.

--
Constantine.

[sqlite] busy_timeout() not working always?

[Constantine Yannakopoulos]

On Thu, Jul 9, 2015 at 2:47 PM, Simon Slavin  wrote:

> Temporarily turn off shared cache mode, and use a separate connection for
> each thread.  See if your SQLITE_BUSY reports go away.
>

?Thanks. It seems that the problem goes away if I turn shared cache mode
off.?

Does this means that shard cache and busy_timeout() are incompatible?
Wasn't aware of such a thing.

[sqlite] busy_timeout() not working always?

[Constantine Yannakopoulos]

Thanks for your response.

On Thu, Jul 9, 2015 at 3:20 PM, Jay Kreibich  wrote:

> The sqlite3_busy_timeout() function is just a wrapper for
> sqlite3_busy_handler().
>

?I know that, and I have no? busy_handler installed.



> As explained at http://www.sqlite.org/c3ref/busy_handler.html, the use of
> a busy handler does not avoid all SQLITE_BUSY situations:
> The presence of a busy handler does not guarantee that it will be invoked
> when there is lock contention. If SQLite determines that invoking the busy
> handler could result in a deadlock, it will go ahead and return SQLITE_BUSY
> to the application instead of invoking the busy handler. Consider a
> scenario where one process is holding a read lock that it is trying to
> promote to a reserved lock and a second process is holding a reserved lock
> that it is trying to promote to an exclusive lock. The first process cannot
> proceed because it is blocked by the second and the second process cannot
> proceed because it is blocked by the first. If both processes invoke the
> busy handlers, neither will make any progress. Therefore, SQLite returns
> SQLITE_BUSY for the first process, hoping that this will induce the first
> process to release its read lock and allow the second process to proceed.
>
> Basically, if SQLite detects a dead-lock situation, that no amount of
> waiting will resolve, the busy handlers will immediately return an
> SQLITE_BUSY.  The only way to resolve this situation is for the connection
> to rollback its current transaction, release all locks currently being
> held, and allow the other connection to continue.
>

?I am aware of that, but I'm pretty sure that is not the case. In case of a
deadlock, the unlock notification code returns SQLITE_LOCKED, not
SQLITE_BUSY and the server raises a specific exception for deadlocks.

Also, I neglected to say that all transactions are started with BEGIN
IMMEDIATE, to avoid the lock escalation problem you describe.?

[sqlite] busy_timeout() not working always?

[Constantine Yannakopoulos]

?Hello all,

I have a multithreaded server that may perform intense database actions
(both reads and writes) concurrently from different threads against two
database connection (sqlite3* objects) to the same database file. The
database connections are in shared-cache mode and I have implemented the
unlock notification mechanism (sqlite3_unlock_notify()) in order to
facilitate table-level locking according to instructions given here:
https://www.sqlite.org/unlock_notify.html. I also use sqlite_busy_timeout()
to set an internal retry mechanism with a reasonably large timeout value in
order to avoid SQLITE_BUSY errors.
??

While in rudimentary tests the whole mechanism seems to work as expected,
in normal server operation under heavy load some statements (different ones
each time) seem to return SQLITE_BUSY immediately, which seems to indicate
that busy_timeout() dos not work in some cases. And this does not change
even if I set the busy timeout to a very large value, e.g. 0x7FFF
?, and calling PRAGMA busy_timeout reports the expected value?
. Also, it doesn't seem to have a difference if the database is in WAL or
DELETE mode.

What may I be doing wrong? Or is there something regarding busy_timeout() I
have missed? A scenario where it is bypassed? Unfortunately this behaviour
appears only under heavy load in customer sites and is very hard to
reproduce and debug.

SQLite version is 3.8.10.1, OS is Win7 x64 and the server is compiled in
32-bit and SQLite is dynamically linked via separate library (sqlite3.dll),
locking_mode is NORMAL, synchronous is FULL and threading mode is
SERIALIZED.

Thank you in advance.
--
Constantine.

Re: [sqlite] Whish List for 2015

[Constantine Yannakopoulos]

On Sun, Dec 21, 2014 at 11:28 PM, Simon Slavin  wrote:

> When you're wishing for the future, however, it's best to wish big, not
> for a tiny step-wise improvement.  Don't wish for a system with a better
> journalling mode, wish for a system without 17 journalling different
> modes.  Don't wish for faster locking, wish for a system that doesn't use
> locking.  And hope most of all that Dr Hipp doesn't care what you want and
> is channelling Henry Ford:
>
> “If I had asked people what they wanted, they would have said faster
> horses.”
>
> (Note: This quote is famous but was probably never spoken by Mr Ford.)
>
>
If an Alfa Romeo owner demands that Alfa Romeo produce a 600HP hypercar,
Alfa Romeo will kindly direct them to buy a Ferrari.​ The same way Dr Hipp
will probably direct a person asking for lockless readers and row version
concurrency to use PostgreSQL instead of SQLite by posting a link to
whentouse.html.

Asking for a MERGE statement, full ALTER TABLE support or better FK
violation error reporting (my own wish list) is something that IMHO Dr Hipp
cannot dismiss that easily. They are reasonable features to ask of an SQL
database, no matter how "lite". It is like asking Alfa Romeo to improve
their cars' gearboxes and fuel consumption, they cannot get away from that.

--Constantine

PS: Whenever I see an Alfa Romeo I tip my hat --Henry Ford. :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tags / keywords support?

[Constantine Yannakopoulos]

On Sat, Dec 6, 2014 at 2:10 AM, Keith Medcalf  wrote:

>
> You probably want your "name" fields in each table to be declared name
> TEXT COLLATE NOCASE UNIQUE
>

​Probably, but my statements were pseudo-, to illustrate the model I'm
proposing. I didn't even bother to try to ​execute them.


> Your ImageTags should also have an index UNIQUE (TagID, ImageID)
>

​Why? That's the primary key reversed. Primary keys are unique by
definition, aren't they? As for "access path" by tagid first, doesn't the
skip-scan optimization (http://www.sqlite.org/optoverview.html#skipscan)
take care of this?
​


> The AUTOINCREMENT keyword in each of the Images and Tags table is
> unnecessary.
>

Probably, although it should be noted that it has a subtle difference from
INTEGER NOT NULL PRIMARY KEY in that it is guaranteed to never reuse values
from deleted records (https://www.sqlite.org/autoinc.html).
​


> You also want fields containing the same thing to have the same name
> (unless you love typing).
>

​You are probably referring to the ON clause. Actually I love explicitness​
​ and I tend to name fields according to their function, not the data they
contain. IDs are IDs, names are names etc. in all tables. I guess it's a
matter of personal taste but it can come handy when you are programming in
an object-oriented language and can have a hierarchy of "entities" and
their fields.​ You can create abstract ancestors that have these fields. Of
course you can always use aliases.


> Finding Images with various AND (intersect) and OR (UNION) of Tags is very
> efficient.
>
> select *
>   from Images
>  where ImageId in (select ImageId from ImageTags join Tags on (TagId)
> where name='summer'
>intersect
>select ImageId from ImageTags join Tags on (TagId)
> where name='house');
>

​I assume the query planner will generate identical (optimal) plans for
in-based queries and their semantically equivalent join-based ones where
UNION becomes OR, INTERSECT becomes AND and MINUS becomes AND NOT.​
 ​If this is true​
​ it is again a matter of personal taste. ​
​If this is not I would like
​you ​
to
​elaborate please.

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

Re: [sqlite] Tags / keywords support?

[Constantine Yannakopoulos]

On Fri, Dec 5, 2014 at 10:49 PM, Krzysztof  wrote:

> Hi,
>
> I need extra field which contain tags / keywords describing such
> record. Then I want to find record ID by using tags. I know that it is
> easy with TEXT field and LIKE condition but I have issue with update
> speed. Let say that you have collection of photos and you want to add
> tags like "summer", "beach" (with ignoring duplicates). But then you
> want to remove from collection tags "beach". It is quite expensive
> (find, remove using native language then update record with new
> value). I'm reading about FTS but I think that it is overloaded for my
> needs and I didn't find functions for remove keywords.
> For example PostgreSQL has special field HSTORE which is list of
> key=>value type field. It is not suitable for tags but it is just
> example for special data types. HSTORE has routines for update (with
> ignoring duplicates), removing, search, enumerates etc.
>

Why not normalize your design and store tags per image in a separate
junction table?

CREATE TABLE Images(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name STRING,
  data BLOB);

CREATE TABLE Tags(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name STRING);

CREATE TABLE ImageTags(
  ImageId INTEGER,
  TagId INTEGER,
  PRIMARY KEY (ImageId, TagId),
  FOREIGN KEY (ImageId) REFERENCES Images (ID) ON DELETE CASCADE,
  FOREIGN KEY (TagId) REFERENCES Tags (ID) ON DELETE CASCADE);

You can easily search for images that have a specific tag name with a
simple join:

SELECT
  Images.id,
  Images.name
FROM
  Images
  JOIN ImageTags ON Images.id = ImageTags.ImageId
  JOIN Tags ON Tags.Id = ImageTags.TagId
WHERE
  Tags.Name IN ('MyTag1', 'MyTag2', ...);

​To add a tag to an image, you add it into the Tags table if it doesn't
exist​ and then you add the appropriate junction record into ImageTags.
To remove a tag from an image you just delete the corresponding junction
record.
To remove a tag from all possible images you delete the corresponding tag
record and all junctions will be cascade-deleted.

​You can go half-way and merge the tables Tags and ImageTags into one​:

CREATE TABLE ImageTags(
  ImageId INTEGER,
  Tag STRING,
  PRIMARY KEY (ImageId, Tag),
  FOREIGN KEY (ImageId) REFERENCES Images (ID) ON DELETE CASCADE);

​But then ​tag strings will be duplicated if a tag is assigned to more than
one image, which is probably OK if they are relatively short.

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

Re: [sqlite] how to store latin strings with no casae

[Constantine Yannakopoulos]

On Fri, Oct 24, 2014 at 9:40 AM, dd  wrote:

>
> >>The SQLite source code includes an "ICU" extension that does these
> overloads.
>
> Will it solve for all charsets if ICU enabled?
>
>
​Probably but I cannot tell for sure because I haven't used it.​ When my
company started using SQLite we didn't know about ICU extensions so we
developed our own.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to store latin strings with no casae

[Constantine Yannakopoulos]

On Thu, Oct 23, 2014 at 2:47 PM, dd  wrote:

> Hi,
>
>   database schema defined with collate nocase. It supports only for ascii
> (upper and lower). If I want to support db for other characters with
> nocase, what steps I need to consider during schema design.
>
> for ex:
>
> *À Á Â Ã Ä Å Æ = *
>
> * à á â ã ä å æ *
>

​you can write​ your own collations
 to support custom
comparing and sorting of strings or any other data and add them to SQLite
.

​There is a pitfall though: The LIKE operator will not honour your collation
. You may choose to override it
 by using the
sqlite3_create_function() api so that it does, but if you do you will lose
the LIKE optimization .

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

Re: [sqlite] Is there equivalent to MySQL IF() function?

[Constantine Yannakopoulos]

On Tue, Oct 7, 2014 at 1:13 PM, Tony Papadimitriou  wrote:

> As you can see, the second select gives unexpected output, and according
> to the syntax diagram it's not supposed to be a valid variation of the CASE
> statement.  Is that normal?


http://www.sqlite.org/sessions/lang_expr.html

Looks perfectly valid​. "expr" can be any expression, including logical
ones such as "a < 10". Logical expressions in SQLite are just like any
other expressions and evaluate to 0 or 1. ​For example, it is perfectly
​legal to

SELECT a.a < 10 FROM a
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for concurrent transactions

[Constantine Yannakopoulos]

On Jun 26, 2014 4:06 AM, "Simon Slavin"  wrote:
>
> Of course, you do get the increased time (it takes time to open and parse
the database file) and memory overhead.

One could setup a simple connection pooling mechanism in order to minimize
_open() overhead. Standard practice in servers that provide access to the
same database to many clients at the same time and can afford to trade
connection establishment overhead with memory overhead.

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

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Wed, May 14, 2014 at 1:35 PM, Jan Slodicka <j...@resco.net> wrote:

> Simon Slavin-3 wrote
> > On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote:
> >
> >> ​This is very interesting Jan. The only way this could fail is if the
> >> collation implementation does something funny if it encounters this
> >> character​, e.g. choose to ignore it when comparing.
> >
> > That cuts out a very large number of collations.  The solution works fine
> > for any collation which orders strings according to Unicode order.  But
> > the point of creating a correlation is that you don't want that order.
> >
> > Simon.
>
> Simon, I think that the most frequent point of making a collation is to get
> the Unicode order. At the bare minimum adding LIKE optimization to the ICU
> Sqlite extension would make sense, the savings are really huge.
>

There could be a flag in sqlite3_create_collation_v2()'s TextRep argument,
much like the flag SQLITE_DETERMINISTIC of sqlite3_create_function() that
will flag the collation as a "unicode text" collation. If this flag is set,
the engine can perform the LIKE optimization for these collations using the
U+10FFFD idea to construct an upper limit for the range as it has been
described in previous posts. Since this flag is not present in existing
calls to sqlite3_create_collation_v2() the change will be
backward-compatible.

Either this or the already mentioned idea of giving the ability to manually
specify lower and upper bounds for the LIKE optimization, perhaps by means
of a callback in a hypothetical sqlite3_create_collation_v3() variant.

And by the way, "unicode text" collations include all "strange" collations
like the one of accent insensitivity and mixed codepage I described in my
original post. And I would expect these to be about 95% of all custom coded
collations.

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

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Tue, May 13, 2014 at 6:50 PM, Jan Slodicka  wrote:

> Any comments are welcome.


​This is very interesting Jan. The only way this could fail is if the
collation implementation does something funny if it encounters this
character​, e.g. choose to ignore it when comparing. Since most collations
end up calling the OS unicode API, and this handles U+10FFFD correctly,
this should be a very rare case.

This may be a reason for Dr Hipp to reject adding this to the LIKE
optimization, but anyone could choose to make the optimization manually
using BETWEEN instead of LIKE if they are sure that any custom collations
they have coded and are using handle U+10FFFD correctly. This is obviously
true in your case.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transaction in one thread and other thread also trying to write data

[Constantine Yannakopoulos]

On Tue, May 13, 2014 at 4:12 PM, d b  wrote:

>My application is multithreaded. It maintains only connection per
> application. Database accessed by single process only. ThreadA will do
> database write operations(bulk) in a transaction. ThreadB will do single
> write operation without transaction but same connection pointer.
>
>Here, application needs to synchronize the calls among threads by using
> synchronization technique(critical_section/mutex)? (OR) begin transaction
> and commit will synchronize the calls between threads?
>
>Please suggest.
>

Please read this: http://www.sqlite.org/threadsafe.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Mon, May 12, 2014 at 6:45 PM, Simon Slavin  wrote:

> They're not the same, Jan.  This optimization is for one very specific use
> of LIKE: where the match string has no wildcards except for a percent sign
> at the end


In theory a LIKE can be optimized if it has a fixed part at the beginning​
and any combination of wildcards after that. The fixed part is used to
define a range in an index (if there is one) and then the wildcard part -if
not a plain '%' taht defines 'everything'- is applied as a filter to each
record in the range to narrow down the result. Now, whether this is to be
preferred instead of a full table scan should be a job for the query
planner.

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

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Mon, May 12, 2014 at 5:41 PM, Jan Slodicka  wrote:

> I think that the answer is as before: The LIKE optimization cannot be
> performed by the DB engine, but only by the collation author.
>

​It can be implemented if the definition of a collation is extended to be
able to provide this information as Simon suggested. For example, by
defining a second callback that provides the lower and upper bounds that
enclose the results of a LIKE prefix string. If this callback is not
implemented or if it returns null strings then no optimization should be
attempted.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Mon, May 12, 2014 at 5:22 PM, Simon Slavin  wrote:

> agree: it's not possible to deduce an optimization without understanding
> the collation.  It might be possible to introduce it in a future version of
> SQLite by requiring anyone who writes an optimization to supply routines
> which work out the proper strings for comparison.
>

​Indeed. And if these routines are not implemented (e.g. for Japanese that
do not have ordering)​ then no LIKE optimization should be attempted by the
engine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Mon, May 12, 2014 at 4:46 PM, Jan Slodicka  wrote:

> Sqlite LIKE optimization is described as follows:
> A like pattern of the form "x LIKE 'abc%'" is changed into constraints
> "x>='abc' AND x<'abd' AND x LIKE 'abc%'"
>

Actually, I would do something ​like:

"x>='abc' AND x<'ab
​c​
'
​ || ​
AND x LIKE 'abc%'"

​where  is a string with a single character that is
guaranteed to be greater than -and NOT equal to- any other character. For
instance, if the encoding was single-byte ANSI​ it would be something like:

"x>='abc' AND x<'ab
​c​
'
​ || Char(255)
AND x LIKE 'abc%'"

​I understand that it is difficult to find the least greater character of a
given character if you are unaware of the inner workings of a collation,
but maybe finding a consistent upper limit for all characters in all
possible collations is not impossible?

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

Re: [sqlite] 64 bits Dll

[Constantine Yannakopoulos]

On Fri, May 9, 2014 at 5:56 PM, Carlos Ferreira wrote:

> Constantine, really useful information.
>
> Do you know if the compiled objs that may be available in Delphi are
> comparable in performance with the ones made by MS VS.
>

​No, sorry.​


I know there is a tool, IMPLIB to import dlls or libs that can be from
> Microsoft and create Libs to use in Delphi..Not sure how they did it in
> Embarcadero..
>

​I am not sure either​ but I think that the 64 bit compiler links ELF
object files. The 32 bit compiler links OMF object files and I believe that
they used their own C compiler (from CBuilder) to produce them. One also
has to reimplement some C runtime functions like malloc, free, etc. in
Delphi for the linker to find. It is not an easy task so I would recommend
linking to the dll.

See also this:
http://docwiki.embarcadero.com/RADStudio/XE6/en/Differences_Between_Clang-based_C%2B%2B_Compilers_and_Previous-Generation_C%2B%2B_Compilers

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

Re: [sqlite] 64 bits Dll

[Constantine Yannakopoulos]

On Fri, May 9, 2014 at 5:20 PM, Ralf Junker  wrote:

> FireDAC only, and outdated by 2 months at the day of release. Delphi XE5
> SQLite is still at 3.7.17, almost one year behind.
>

​In Delphi XE5 FireDAC either links statically the object files ​
​sqlite3_x86.obj
​/​sqlite3_x64.obj or loads sqlite3.dll and links to its functions via
GetProcAddress() depending to the compiler switch FireDAC_SQLITE_STATIC. So
it should be possible to recompile the units FireDAC.Phys.SQLiteXXX.pas and
either link in any later version of the sqlite3 object files or have it
load the latest dll, provided that you have $(BDS)\source\data\firedac in
your search path.

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

Re: [sqlite] 64 bits Dll

[Constantine Yannakopoulos]

On Fri, May 9, 2014 at 2:45 PM, Carlos Ferreira wrote:

> Tell me how you want to do this DLL exchange.


​I will send the compiled dlls to your personal email.​

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

Re: [sqlite] 64 bits Dll

[Constantine Yannakopoulos]

On Fri, May 9, 2014 at 1:36 PM, Carlos Ferreira wrote:

> Can anyone tell me where can I find the 64 bits version of Sqlite.dll, or
> can anyone compile it for me? It would work as a small consulting project.
>

​I can send you dlls compiled from the latest amalgamation ​(3.8.4.3). If
you need an older version
​I have a Visual Studio 2010 project ​that I use to compile the
amalgamation for Windows 32 and 64 bit. If you have VS2010 I would be happy
to send it to you. All you will need to do is to replace the amalgamation
source files (.c and .h) and definition file (.def) with that of the
previous version.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key errors

[Constantine Yannakopoulos]

On Thu, May 8, 2014 at 10:52 PM, Walter Hurry <walterhu...@gmail.com> wrote:

> On Thu, 08 May 2014 13:15:54 +0300, Constantine Yannakopoulos wrote:
>  most if not all other databases do not implement this
>
> $ psql
> psql (9.3.4)
> Type "help" for help.
> ​​
>
>
​I meant that they do not implement a way to *directly* get attributes of
the error like table name, constraint name​, record key etc. e.g. by means
of api functions. Or if some do I haven't found the way. All databases
include this information in the error message, and in such a way that it is
easily extractable by text parsing, e.g. using a regexp with groups. And as
you see, postgresql also quotes the names to make parsing easy, which is
what I've asked for. I've used such an approach with both MSSQL and Oracle
with success.

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

Re: [sqlite] Foreign Key errors

[Constantine Yannakopoulos]

On Thu, May 8, 2014 at 1:08 PM, Simon Slavin  wrote:

> Perhaps a future version of sqlite3_extended_result_codes() can return a
> string as well as the code, the string being the name of the constraint
> that was violated.
>

Yes, it would be great to be able to get the necessary info in a structured
format instead of having to parse the error message​. But I think that most
if not all other databases do not implement this, so I would settle for
parsing the message.

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

Re: [sqlite] Foreign Key errors

[Constantine Yannakopoulos]

On Thu, May 8, 2014 at 11:50 AM, Simon Slavin  wrote:

> That's your job.  You're the programmer.  SQLite does not talk to users
> and its result values should never be shown to a user.
>

​Fair enough, but SQLite needs to give me what I need to be able to
communicate something helpful to the user.​ A generic error code and a
"Foreign key constraint failed" error message without any means of getting
some context is not very helpful.


> No need.  You execute the command as expected.  Only if it returns
> SQLITE_CONSTRAINT does your software need to start doing complicated things
> to figure out what caused the problem.
>

​Yes, but what of the constraint is deferred?​
 I get the error​ at COMMIT instead of when the offending DML is executed
​and at that point, when all I have is the error code and error message, it
is impossible to find which statement has caused the violation.​



> I would argue that consideration of this should go into the design of
> SQLite4.  I don't think it's worth doing it for SQLite3.
>

​I beg to differ. having worked with a number of databases, SQLite3 is the
only one that has this defect -forgive the choice of word but I do consider
it as such, and it has caused me lots of trouble from people trying to
import data or trying to implement database replication, myself included.
The only alternatives seem to be to execute DML statements in autocommit
mode (no BEGIN/END), which as you probably guess has dismal performance, or
completely refrain from using deferred constraints, which limits database
design choices. If there is another solution I would be more than happy to
hear it.

​I would be eager to switch to SQLite4 ASAP but it seems very far from even
a beta release.​
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key errors

[Constantine Yannakopoulos]

On May 8, 2014 4:39 AM, "phaworth"  wrote:
>
> I'd vote for having this as a future enhancement under the control of a
> pragma or some other way of making it optional.  Some of my tables have
more
> than 1 foreign key and without the constraint name I have to write
> application code to pre-check for foreign key errors since I can't
translate
> the error into a user friendly message.

Same problem here. When the fk violation occurs inside a large transaction,
e.g. during a bulk data import it is essential that the user is given any
possible help to be able to locate the data that violates ref integrity and
fix it. Without this, prechecking the fks is the only option. And I would
argue in favor of a compiler switch to enable instead of a pragma.

Btw, if you decide to implement it, please format the error messages in
such a way that it will be easy to extract the name of the table/constraint
with code, e.g. with a regexp. For example quote them. Another thought is
to include the name of the last savepoint at the time of the violation in
the error message if there is one. It may be helpful to locate the
offending record if the violated constraint was deferred.

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

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Sun, Feb 9, 2014 at 8:27 PM, Simon Slavin  wrote:

> I know it's a hack.  But it's an elegant efficient hack that takes
> advantage of the things SQLite does well.  As long as that's the only way
> you were using LIKE.
>

Don't get me wrong, the solution is good. But apart from the specific
problem I also started the thread in order to prove that the implementation
of the LIKE optimization in SqLite is not all it could be. Apart from mixed
languages there are other, less extreme scenarios where a
collation-sensitive like optimization will come in handy. For instance, for
languages with accents it would be nice to be able to create a
case-insensitive accent-insensitive (CI_AI) collation and be able to use
LIKE on it, even if it doesn't use an index. And overloading the LIKE
operator globally is not a good idea because it will affect all LIKE
operations in a database, even in columns that are not CI_AI.

I was hoping to elicit a response from D. R. Hipp but he has chosen not to
respond.

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

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

Thank you all for your
replies,

@RSmith:

My question is about the _specific_ case:

SELECT * FROM ATable WHERE AColumn LIKE
'FixedSearchPart' || 'SomeWildCard'

where the right side of the LIKE operator is a constant that has a fixed
part at the beginning and a wildcard after that. The optimization is that
the index is used to produce a rowset in which AColumn starts with
'FixedSearchPart' and then rows are filtered according to 'SomeWildCard'.
For instance, in

SELECT * FROM ATable WHERE AColumn LIKE
'Constantine%Yann%'

As I understand it, the index scan will use the string 'Constantine' as
argument and then the full string 'Constantine%Yann%' will be used to
further filter the rowset. Of course any other case that has no fixed part
at the start of the right-side string will have to fall back to a full scan
and filter.

Maybe the parameter notation was a little confusing but from the
description you can deduce that it will not contain a wildcard.
Essentially, the user will be asking for the rows where AColumn BEGINS WITH
a string.

@Jean-Christophe:

Thank you for the extension. I will certainly have a look at it, but I
already have the comparison algorithm (specific to latin/greek) from
another case. So the only thing I have to do is to tailor it inside a
custom collation.

@Yuriy:

Yes, the current interface for custom functions does not provide any
information on what the arguments are, so it is impossible to deduce the
collation of arguments inside the function body. That's why this has to be
implemented by the SqLite query optimizer itself. I was thinking about
something like:

- The query optimizer understands that the left-side of the LIKE argument
is a column with an index and a collation, and the right side is a literal
with a fixed left part (after parameter substitution).
- It takes the fixed part, appends a very high-order (preferably
non-printable) character to it and use the index to materialize this clause:

  AColumn BETWEEN 'FixedPart' AND 'FixedPart' || .

If we were using ANSI I would suggest something like:

  AColumn BETWEEN 'FixedPart' AND 'FixedPart' || (char)0xFF

but for UTF-8 I am not sure what to suggest. It would have to be a UTF8
character value that is guaranteed to be greater than any other usable
character in any code page. I am not sure whether there actually is one.

-Then it applies the whole right-side argument to this rowset using LIKE
pattern matching to produce the correct result.

@Simon:

Your proposal is very clever but it has two weaknesses:
- You are assuming that 'z' is the higher order character that can appear
in a value. This is not the case; for instance greek characters have higher
order than 'z'. This can be fixed (only for latin/greek) by using the
highest order greek character 'ώ' (accented omega) instead of 'z'; but I
would prefer a very high-order non-printable one instead.
- It assumes that the column has a maximum width. Since SQLite does not
have maximum widths for columns of TEXT affinity (although a max width can
be implied and imposed by the app itself), improbable as it may be, there
could be a row with the value :SearchString||''||'a' which will
erroneously not make it into the result set. Again, the very high-order
non-printable character would solve this.
So it can be a solution for a specific case with a given max value and a
known set of code pages but it cannot be a general solution.

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

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Sun, Feb 9, 2014 at 1:25 PM, Simon Slavin  wrote:

> store two text columns in the table.  The first is the text as entered.
>  The second is your text reduced to its simplified searchable form,
> probably all LATIN characters, perhaps using some sort of soundex.  Search
> on the second column but return the text in the first.
>
> This allows you to write your conversion routine in the language you're
> using to do the rest of your programming, instead of having to express it
> as a SQLite function.
>

Thanks for your reply,


Yes. I thought of that as well. I even have the greek soundex() function
from a previous implementation. Problem is it will bloat the database
considerably, keeping in mind that the users will typically demand that ALL
searchable text columns in the application work that way, and who can blame
them? And the project manager will not be very keen on accepting both this
database size increase and the time needed to calculate the extra soundex
column for every database row. It will be much easier to convince this
person to accept the time-costly database upgrade needed in both cases
(tables need to be recreated to change collation) but not both upgrade and
bloat.

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

[sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

Hello all,

I have a case where the user needs to perform a search in a text column of
a table with many rows. Typically the user enters the first n matching
characters as a search string and the application issues a SELECT statement
that uses the LIKE operator with the search string:

SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'

According to the LIKE optimization this statement will use an index so it
will be fast.

The application is used by Greek users. The greek alphabet has some letters
that are visually identical to corresponding latin letters when in
capitals, (e.g. A, E, I, O, B, H, K, etc), some that are different but
match deterministically to a single latin letter (e.g. Σ = S, Φ = F, Ω = O
etc.) and some that don't have a deterministic match (e.g. Γ = Gh or Yi).

The table contains strings that consist of words that can be written in
either latin or greek characters; sometimes even mixed (the user changed
input locale midword before typing the first non-common letter). I have a
request that the search should match strings that are written with either
latin or greek or mixed letters, e.g. "MIS" should match "MISKO" (all
latin), "ΜΙΣΚΟ" (all greek) or "MIΣΚΟ" (first two letters latin, rest
greek). I thought of using a custom collation that does this type of
comparison, have the column use that collation and create an index on that
column to speed up the search but I discovered that the LIKE operator
either will not use collations other than BINARY and NOCASE (pragma
case_sensitive_like) or (if overloaded to perform custom matching) will not
use an index, and, worse yet, its behaviour will be the same to all string
comparisons regardless of collation. So, a full table scan seems inevitable.

I was wondering whether it is realistic to ask for the LIKE operator to use
by default the assigned collation of a column. I assume that an index on
that column is using by default the specified collation of the column for
comparisons, so a LIKE clause like the aforementioned can use the index and
perform a fast search while using the "mixed" comparison I need. This would
transparently solve my problem and make the case_sensitive_like pragma
redundant, but for backward compatibility this behaviour could be activated
by a new pragma.

Are there any details I am missing that prevent this from being implemented?

Thanks in advance.

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

Re: [sqlite] Mimic MERGE in a trigger

[Constantine Yannakopoulos]

On Fri, Feb 7, 2014 at 2:50 PM, Constantine Yannakopoulos <
alfasud...@gmail.com> wrote:

> As for scaled integers for amounts, if it was up to me I would prefer BCDs
> but this choice is out of my hands.


Oh, also the "cube" table can contain records for many accumulators (e.g.
debit, credit, turnover, quantity etc. which is a dimension) that are in
different units of measurement, and some have more than two decimal digits.
So the double is the only choice that covers all cases.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mimic MERGE in a trigger

[Constantine Yannakopoulos]

On Fri, Feb 7, 2014 at 2:10 PM, Simon Slavin  wrote:

> You're right.  Let's see if I can make it up to you.  I do think you came
> up with the right strategy, and that doing INSERT OR IGNORE is going to
> give you the best result overall.
>

Thanks. I myself couldn't come up with anything better.

Two scans on a field which is indexed won't take long.  Probably less time
> in total than those MERGE commands or some complicated triggers.
>

I would expect the implementation of the merg
e

to do a single scan and then choose which branch to execute having kept the
found record in memory, so I would expect it to be as fast as an UPDATE.
Also, while not an issue in a trigger a merge is atomic; so is the insert
or ignore strategy of course.

>
> A few notes:
>
> An alternative way to do it would simply be not to store current balances.
>  Whenever you want to know the balance as of a particular date, calculate
> it using total().  Not only does it mean smaller files and faster inputs,
> but you never have to sanity-check your totals and for a system which has
> more inputs than reports it is faster overall.  This technique is
> increasingly used in systems used by banks and in double-entry bookkeeping.
>

> I assume you have a good reason for not keeping the CustomerAccum.amount
> field in the customer table.  You did mention that your real scenario was
> more complicated than you were giving details of in your post, so that
> would explain it.
>
> Also, SQLite does not have VARCHAR fields (they're stored as text and the
> '50' will be ignored) or DATE fields.  Dates must be stored as numbers or
> text.  FLOAT will be interpreted as REAL, and will work the same way, so
> that's okay, but if I was doing something primarily for backing or
> accounting purposes I wouldn't use a REAL field, I'd use INTEGER and
> multiply by 100.
>

My example was made up to describe the case. The accumulated amounts table
actually has much more dimensions, some of them relative to time. For
instance, a fiscal year is divided into fiscal periods and the fiscal year
and period of the transaction is a dimension as well. Also the table stores
a running total as well as period totals. This allows for very fast balance
reports, even when the requested time interval extends half-way between
periods in which case I do a UNION ALL with transaction records for
"half-way" transactions.and a GROUP BY/SUM() afterwards.Transaction
processing will not be that intensive in my app's case, but balance reports
need to be as fast as possible, that's why I use this concept of a "live
cube" although its maintenance introduces an overhead in OLTP..

Column data types are named thus because the Delphi implementation of
SQLite API I am using uses names of SQLite declared datatypes to map to
dataset field types (TStringField, TIntegerField, TDateTimeField, etc.) a
DATE field is considered to be a native datetime double and I have written
an SQLite extension library that implements a bunch of manipulation
functions to deal with datetimes. I consider float datetimes smaller in
size and faster in calculations and comparisons that formatted strings,
e.g. -MM-DDThh:nn:ss:zzz.

The database needs to be able to be created in database engines of various
vendors with only minor changes to table definitions and that may be
handled by consultants, that's why you are seeing the terms VARCHAR(),
FLOAT, DATE etc; they are familiar from other database vendors.

As for scaled integers for amounts, if it was up to me I would prefer BCDs
but this choice is out of my hands.

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

Re: [sqlite] Mimic MERGE in a trigger

[Constantine Yannakopoulos]

On Fri, Feb 7, 2014 at 1:27 PM, Simon Slavin  wrote:

> Don't do it like that.
>
> Use 'INSERT OR IGNORE' to insert a record with a zero amount.
> Then update the record which, now, definitely does exist.
>

Obviously you didn't read all of my message. :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mimic MERGE in a trigger

[Constantine Yannakopoulos]

Hello everyone,

Given a master table, a table of transactions and a table of cumulative
amounts per transaction master:

CREATE TABLE Customer (
  id INTEGER PRIMARY KEY,
  firstname VARCHAR(50),
  lastname VARCHAR(50)
);

CREATE TABLE CustomerTrans (
  id INTEGER PRIMARY KEY,
  transactiondate DATE,
  customerid INTEGER,
  amount FLOAT
  FOREIGN KEY (customerid) REFERENCES Customer (id)
);

CREATE TABLE CustomerAccum (
  customerid INTEGER PRIMARY KEY,
  amount FLOAT
  FOREIGN KEY (customerid) REFERENCES Customer (id)
);

I would like to create a trigger on table CustomerTrans that performs the
accumulation of transaction amounts in CustomerAccum. This trigger should
- Insert a record with the transaction amount if a record for the
customerid does not exist
- Update an existing record, adding the transaction amount to the
cumulative amount.

Normally this would be done with a merge statement:

CREATE TRIGGER AI_CustomerTrans
AFER INSERT ON CustomerTrans FOR EACH ROW
BEGIN
  MERGE INTO CustomerAccum
  USING (SELECT new.customerid, new.amount) trans
  ON trans.customerid = CustomerAccum.customerid
  WHEN MATCHED THEN
UPDATE SET CustomerAccum.amount = CustomerAccum.amount + trans.amount
  WHEN NOT MATCHED THEN
INSERT (customerid, amount) VALUES (trans.customerid, trans.amount);
END;

but since MERGE statements are not supported by SqLite, and trigger code
does not support flow control (IF THEN ELSE constructs) I think that the
only way to do this is by using the conflict clause and always try to
insert a new record:

CREATE TRIGGER AI_CustomerTrans
AFTER INSERT ON CustomerTrans FOR EACH ROW
BEGIN
  INSERT OR IGNORE INTO CustomerAccum (customerid, amount)
  VALUES (new.customerid, 0);
  UPDATE CustomerAccum SET amount = amount + new.amount
  WHERE  CustomerAccum.customerid = new.customerid;
END;

But this will always perform two index scans on CustomerAccum, which is
suboptimal for the vast majority of trigger invokations.

Is there another way to achieve this MERGE without the double scan?
Mind you that the case I described is a simplified version of the problem I
am trying to solve, where the tables have much more dimensions than just
the customerid, and the size of data will be of the order of hundreds of
thousands of rows, thus my concern for the redundant most of the times
INSERT OR IGNORE.
Mind you also that I want to avoid the INSERT OR REPLACE construct because
on conflict it deletes and reinserts records invoking any delete triggers
and FK ON DELETE clauses which does not suit my needs.

Thanks in advance

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

Re: [sqlite] SQLite 2013 retrospective

[Constantine Yannakopoulos]

If I may:

On Thu, Jan 2, 2014 at 12:59 AM, Petite Abeille wrote:

> Wish list for the new year :))
>
> - information schema [1]
> - merge statement [2]
> - with clause [3][4]
> - analytic functions [5][6]
>

- More informative error messages for FK violations. I consider this the
only unworkaroundable flaw of SQLite.

A happy new year to all!

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

Re: [sqlite] "Common Table Expression"

[Constantine Yannakopoulos]

On Tue, Dec 31, 2013 at 8:59 PM, James K. Lowden
wrote:

> Recursive queries are a unique feature of CTEs.  They are not supported
> in SQLite.  If and when they are, CTEs will be required.
>

Not necessarily. Oracle has had the START WITH CONNECT BY syntax for
recursive queries since the eighties:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm#SQLRF52315

I think I prefer that over recursive CTES if implementing recursive queries
is the only goal. On the other hand CTEs _are_ part of the SQL:1999
standard and Oracle also supports them.

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

Re: [sqlite] sqlite does not order greek characters correctly

[Constantine Yannakopoulos]

On Sun, Dec 8, 2013 at 11:34 PM, Nikos Platis  wrote:

>
> Unfortunately, this ordering of greek characters is useless in practice, so
> the correctly behavior should be implemented.
>

You can implement your own CI-AI greek collation and use it in your columns
that contain greek text. It really isn't that hard to do. See these links:

http://www.sqlite.org/c3ref/create_collation.html
http://www.sqlite.org/c3ref/collation_needed.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers to enforce table permissions

[Constantine Yannakopoulos]

On Sat, Dec 7, 2013 at 8:45 PM, Joshua Grauman  wrote:

> Ok, so now I have a new question.
>
> Is there a way to prevent changes to the schema, while still allowing
> inserts of new data to existing tables? I'd love to just prevent any
> changes to sqlite_master for a given database connection. I tried adding
> triggers to it similar to the ones I used below, but I get an error "Error:
> cannot create trigger on system table".


You may find this of interest:
http://www.sqlite.org/c3ref/set_authorizer.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

[Constantine Yannakopoulos]

On Sun, Nov 24, 2013 at 4:30 PM, Petite Abeille wrote:

>
> On Nov 24, 2013, at 3:17 PM, Doug Currie  wrote:
>
> > There is value in compatibility, but those adjectives are awful.
>
> FWIW, DETERMINISTIC is what Oracle uses:
>
>
> http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/function.htm#LNPLS1183
>

There's also this:
http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems039.htm that
specifies various levels of "purity", i.e. if the package function reads
from or writes to the database or global state. Each level allows for more
aggressive optimization when the function takes part in a query.

It's been a while since I've coded in PL/SQL but I think that if the
RESTRICT_REFERENCES pragma is not specified for a package function the SQL
engine will not accept calls to it from an SQL statement. Also, if the
implementation of a function violates its RESTRICT_REFERENCES pragma the
PL/SQL compiler will not compile it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is access to extension objects synchronized inside sqlite3.dll in serialized mode?

[Constantine Yannakopoulos]

On Tue, Nov 12, 2013 at 8:24 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Nov 12, 2013 at 1:18 PM, Constantine Yannakopoulos <
> alfasud...@gmail.com> wrote:
>
> >
> > If I understand correctly what you say, each sqlite3* connection instance
> > serializes its calls to its extension callbacks so each instance of the
> > extension objects does not need to perform any additional serialization
> > because it is "guarded" by the serialization of the connection. Is this
> > correct?
> >
>
> Correct.
>

Thank you very much.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is access to extension objects synchronized inside sqlite3.dll in serialized mode?

[Constantine Yannakopoulos]

On Tue, Nov 12, 2013 at 7:38 PM, Richard Hipp  wrote:

>  SQLite serializes callbacks to extension objects originating from a
> single
> database connection.  But if you have multiple database connections running
> in separate threads, then the same callback might be invoked simultaneously
> from multiple threads.  You'll need to serialize access to your private
> data structures yourself.
>

Maybe I wasn't clear enough, let me elaborate a little.

>From what I understand, sqlite3_extension_init() is called once for each
new database connection. In this entry point my extension library creates
instances of the extension objects (e.g. structs) with their own data
members and destroys them using the xDestroy() callback of each
sqlite3_create_XXX() api function. Therefore each sqlite3* database
connection gets its own instance of each extension object that uses only
its own data members and no global variables to perform calculations.On the
other hand, the callback passed to create_XXX() itself is nothing more than
a trampoline that calls a method of the user data pointer argument after
typecasting it to the appropriate pointer type to the extension object, so
it does not access any "common" data.

If I understand correctly what you say, each sqlite3* connection instance
serializes its calls to its extension callbacks so each instance of the
extension objects does not need to perform any additional serialization
because it is "guarded" by the serialization of the connection. Is this
correct?

I hope I was more clear this time, please bare with me because English is
not my native language.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is access to extension objects synchronized inside sqlite3.dll in serialized mode?

[Constantine Yannakopoulos]

Hello all,

I am writing an extension library that contains some extensions, i.e.
custom functions and collations. Some of these objects use some private
data members in order to perform their calculations.

This extension library will be used by an embedded server that must handle
many database connections to the same database file in different threads
and it uses shared cache. I need to know whether SQLite synchronizes its
invocations to the callbacks of these objects or whether I have to take
care of protecting the data members these callbacks are using from
concurrent access in my own code.

>From what I understand, sqlite creates an instance of each extension object
for each active sqlite3* database connection, therefore in single-thread
and multi-thread mode I don't need to worry because sqlite3 itself may
crash if a connection is accessed simultaneously by many threads. In
serialized mode is it possibe for two threads using a single sqlite3*
database connection to invoke an extension object at the same time without
some sort of internal synchronization in sqlite3.dll?

Thanks in advance.

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

Re: [sqlite] Syntax of EXPLAIN QUERY PLAN "detail" column

[Constantine Yannakopoulos]

On Fri, Nov 1, 2013 at 3:51 PM, Richard Hipp  wrote:

>  I don't think anybody knows.  The EXPLAIN QUERY PLAN syntax is not
> formally
> designed.  It is intended for human reading, not machine parsing.  It
> changes from time to time and is not considered part of the SQLite
> interface.
>
If you want to know all possibilities of what the current code can generate
> for EXPLAIN QUERY PLAN, you'll need to look at the source code and figure
> that out for yourself.  All of the EXPLAIN QUERY PLAN generating code is
> found in the "where.c" source file.  If it were me, I'd probably start
> looking here:  http://www.sqlite.org/src/artifact/f18400f121fd?ln=3049
>

Thank you.

I was afraid that my post might be taken as a request for someone else to
do my homework. I already tried what you suggest but
I don't know C -I am using sqlite3.dll from Delphi - and it was hard for me
to find my way in unfamiliar code in a language I do not understand, but
since you have localized it for me I'll try to read the code and
find the answer myself.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Syntax of EXPLAIN QUERY PLAN "detail" column

[Constantine Yannakopoulos]

Hello all,

I am trying to write a utility that processes the results of EXPLAIN QUERY
PLAN statements and presents them in a graphical manner to developers. I
need to extract information from the "detail" column of the returned result
set (e.g. table name, index name, estimated rows etc.) and since the detail
is somewhat verbose I decided to write a small parser to parse it. After
some examination I have come up with the following EBNF:

plan_statement :=
  ( scan |
search |
aggregate |
compound |
execute |
use )

scan ::=
  "SCAN" ( "TABLE" ObjectName | "SUBQUERY" int ) ["AS" identifier] [ using
] [ cost ]

search ::=
  "SEARCH" ( "TABLE" ObjectName | "SUBQUERY" int) ["AS" identifier] [ using
] [ filter_expr ] [ cost ]

aggregate ::=
  "USE TEMP B-TREE FOR" aggregate_operation

compound ::=
  "COMPOUND SUBQUERIES" int "AND" int SET_OPERATION

execute ::=
  "EXECUTE" ("LIST" | "CORRELATED SCALAR") "SUBQUERY" int

using ::=
  "USING" (
 "AUTOMATIC" ["COVERING"] "INDEX" |
 ["COVERING"] "INDEX" ObjectName |
 "INTEGER PRIMARY KEY"
)

cost ::=
  "(~" int "rows)"

aggregate_operation ::=
  ("GROUP BY" | "ORDER BY")

filter_expr ::=
  "(" ColumnName "=" "?" ["AND" ColumnName "=" "?"]* ")"

set_operation ::= ( "UNION" ["ALL"] | "INTERSECT" | "MINUS" | "EXCEPT" )

use ::=
  "USE" ["TEMP"] "B-TREE" FOR ( "GROUP BY" | "ORDER BY" )

ColumnName ::=
  identifier

ObjectName ::=
  identifier

where sequences are separated by spaces, literals are quoted, production
names are unquoted idents, ( ... | ... ) denote choice groups and [ ... ]
denote optional sequences. Productions "identifier" and "int" are omitted
as trivial.

So far this grammar covers plans of all statements I have examined. Could
someone (probably an SQLite developer) tell me if this grammar covers all
cases of "detail" values or if there are cases I have missed? Maybe the
NGQP has introduced some changes I have missed?

Also, regarding object names, do they include database names if
tables/indexes from attached databases are used in the query? (ObjectName
::= [ identifier '.' ]  identifier instead of ObjectName ::= identifier)

Thank you in advance.

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