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

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?

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

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

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

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

Re: [sqlite] Tags / keywords support?

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

Re: [sqlite] Tags / keywords support?

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

Re: [sqlite] Whish List for 2015

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

[sqlite] busy_timeout() not working always?

?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

[sqlite] busy_timeout() not working always?

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

[sqlite] busy_timeout() not working always?

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

[sqlite] busy_timeout() not working always?

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

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

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

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

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 >

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

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

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

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

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

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

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

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

Re: [sqlite] Triggers to enforce table permissions

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

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

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

Re: [sqlite] "Common Table Expression"

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

Re: [sqlite] SQLite 2013 retrospective

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

[sqlite] Mimic MERGE in a trigger

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

Re: [sqlite] Mimic MERGE in a trigger

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. :)

Re: [sqlite] Mimic MERGE in a trigger

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

Re: [sqlite] Mimic MERGE in a trigger

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 accumulato

[sqlite] LIKE operator and collations

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 *

Re: [sqlite] LIKE operator and collations

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 >

Re: [sqlite] LIKE operator and collations

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

Re: [sqlite] LIKE operator and collations

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

Re: [sqlite] Foreign Key errors

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

Re: [sqlite] Foreign Key errors

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

Re: [sqlite] Foreign Key errors

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

Re: [sqlite] Foreign Key errors

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 he

Re: [sqlite] 64 bits Dll

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

Re: [sqlite] 64 bits Dll

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

Re: [sqlite] 64 bits Dll

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

Re: [sqlite] 64 bits Dll

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,

Re: [sqlite] LIKE operator and collations

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

Re: [sqlite] LIKE operator and collations

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

Re: [sqlite] LIKE operator and collations

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

Re: [sqlite] LIKE operator and collations

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

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

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

Re: [sqlite] LIKE operator and collations

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

Re: [sqlite] LIKE operator and collations

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

Re: [sqlite] Support for concurrent transactions

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

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

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

[sqlite] Using collation instead a virtual table

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,

[sqlite] Using collation instead a virtual table

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

[sqlite] Using collation instead a virtual table

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.

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

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

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

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

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

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