Re: [sqlite] Connection open to nonexistent database succeeds! WTF?

[Barry]

It looks like you're using C# and System.Data.SQLite.

If that's the case, use the connection string parameter FailIfMissing=True
to throw an exception if the file does not exist.

On 1 April 2018 at 02:34, Jens Alfke  wrote:

>
> > On Mar 31, 2018, at 8:17 AM, Mike Clark 
> wrote:
> >
> > Is this expected behavior?
>
> Yes. If the database file doesn’t exist, opening it will create it.
> (That’s how you create new databases.) There is a flag to sqlite3_open (in
> the C API) that prevents creating a file.
>
> —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] Select query does not give new added records

[Simon Slavin]

On 31 Mar 2018, at 2:04pm, Koen Amant  wrote:

> there is a service running in the background who adds records
> to the database (POS system) I can't stop this service and all the new
> records that are added I can't see in my query result. It's like the
> database is locked for other users and so it gives an old snapshot of the
> database... Very Strange.

Are the 'missing' records always the most recently added ones ?

First, make sure the database isn't corrupt by executing



If the database passes this, then the problem is that the POS system is not 
committing its changes immediately.  This speeds things up for the POS since 
most of the time taken to make changes to a database is taken up by the COMMIT 
statement, not the INSERT statements.  It also reduces wear and tear on the 
storage device, which is especially important if it's solid state, e.g. Flash 
storage.  It is not at all surprising that the POS does this: it's don't in 
many systems which generate a continuous log because it makes such a difference.

You should be able to cause the POS system to flush all changes to the database 
by quitting and restarting it, but you have said that you can't do this.

An alternative might be to use



However, you may need to not only do this in the connection you're using the 
read the database but also do it in the connection the POS is using.  And it 
may be that the only way to do that is to make a change to the POS.

If you can't do this, my only suggestion is to figure out how often the POS 
system commits changes and trigger it somehow.  So if it does one COMMIT every 
ten minutes, wait ten minutes.  Or if it does a COMMIT every ten transactions, 
generate ten dummy transactions and reverse them.

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

[sqlite] Select query does not give new added records

[Koen Amant]

I have a table with 9 records. When I run 'SELECT * FROM table;' in my
VB.Net I only get 6 from the 9 records. When I run the query in 'DB Browser'
an SQLite viewer I get them all. 

The thing is there is a service running in the background who adds records
to the database (POS system) I can't stop this service and all the new
records that are added I can't see in my query result. It's like the
database is locked for other users and so it gives an old snapshot of the
database... Very Strange. 

How can I prevent this?

 

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

[sqlite] Before Insert/Update Trigger

[Thomas Kurz]

Other DBMS support the following construct in a trigger:

CREATE TRIGGER name BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
  SET NEW.column = anyvalue
END;

In SQLite, the NEW record appearently is read-only.

Support for changeable NEW records would however be graceful as it 
automatically prevents an infinite loop (recursive trigger) that can will more 
or less automatically occur otherwise when working with additional 
UPDATE-statements.

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

Re: [sqlite] Fixes to the transliteration table in ext/misc/spellfix.c

[Will Parsons]

On Friday, 30 Mar 2018 11:38 AM -0400, Ian Zimmerman wrote:
> On 2018-03-30 11:28, Marcin Ciura wrote:
>
>>   { 0x0426,  0x54, 0x63, 0x00, 0x00 },  /* Ц to Tc */
>>   { 0x0446,  0x74, 0x63, 0x00, 0x00 },  /* ц to tc */
>> 
>> This Cyrillic letter is usually transliterated as Ts.  Unless "Tc" is
>> a hack meant to cover both "Ts" and the rarer transliteration "C", it
>> looks like a typo: https://en.wikipedia.org/wiki/Tse_(Cyrillic)
>
> Also often as "Cz".  Unfortunately :-P

"Often"?  I don't think so.  "Ts" is normal for most uses; "c" is
typically used in scientific/linguistic works.

-- 
Will

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

Re: [sqlite] MIN() and MAX() of set of row values

[Wout Mertens]

As a sidenote, I believe row values were added because of keyset pagination
https://use-the-index-luke.com/no-offset. I found them to not be actually
useful, so I thought I'd explain here. (copied from my comments on that
page (now no longer visible), slightly edited)


I ended up implementing this approach for sqlite since it has the
(a,b)<(x,y) operation now, but while doing so I realized that that
comparison is not a magical operation that uses the given fields to compare
them to the sort order of the query, but instead logically equivalent to
`(a < x OR (a = x AND b < y)`.

So if you are querying with `ORDER BY a ASC, b DESC`, you have to use `(a >
x OR (a = x AND b < y))`, since there is no way to express that with the
combined operator.

Just wanted to point that out since it surprised me, and since I (wrongly)
gleaned from your explanation that the combined operator is necessary to
implement keyset pagination.

I did some EXPLAINing of "cursor where clauses" and the shortest plans were
with clauses of the form:

a >= x AND (a != x OR (b <= y AND (b != y OR c > z))

(with ORDER BY a ASC, b DESC, c ASC and x, y, z the last-seen values)

A bit annoying to write but the idea is that the DB can scan a in index
order, then b if needed, then c if needed.

In fact, writing it that way gets me less instructions than when I use the
row values. Not sure if that translates in faster queries though.


On Fri, Mar 30, 2018 at 7:09 PM David Raymond 
wrote:

> https://www.sqlite.org/rowvalue.html section 2.1 for ordering.
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Simon Slavin
> Sent: Friday, March 30, 2018 1:06 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] MIN() and MAX() of set of row values
>
> On 30 Mar 2018, at 6:04pm, Peter Da Silva 
> wrote:
>
> > On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> slav...@bigfraud.org> wrote:
> >> can think of to do it is to devise a metric to turn a pair (a, b) into
> one number.
> >
> > Problem is you can't uniquely order pairs of points. Is (1, 2) greater
> or lesser than (2, 1)?
>
> That's up to you.  That's why you are devising the metric.  Maybe the
> metric is just score == a + b .
>
> Simon.
> ___
> 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] Connection open to nonexistent database succeeds! WTF?

[Jens Alfke]

> On Mar 31, 2018, at 8:17 AM, Mike Clark  wrote:
> 
> Is this expected behavior?

Yes. If the database file doesn’t exist, opening it will create it. (That’s how 
you create new databases.) There is a flag to sqlite3_open (in the C API) that 
prevents creating a file.

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

[sqlite] Connection open to nonexistent database succeeds! WTF?

[Mike Clark]

I've written the following code for the ADO.NET client for SQLite, and the
odd thing I've noticed is that even when the connection string passed to it
does not refer to a file that exists, the code returns true!  Does SQLite
not actually check for a valid connection until you try to perform a data
operation?  The result I get when trying to insert a row to a table in a
nonexistent database is "Sql logic error" "table doesn't exist" -- no
kidding, because there's no such database file!

Is this expected behavior?

public bool ConnectionOK(string ConnString)
{
bool result = true;
SQLiteConnection sqlConnection;
if (DbPath.Length > 0)
{
try
{
sqlConnection = new SQLiteConnection(ConnString);
sqlConnection.Open();
if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
}
catch (Exception ex)
{
result = false;
}
}
return result;
}


-- 
Mike Clark 
Twitter: @Cyberherbalist
Blog: Cyberherbalist's Blog 
-
"Free will, though it makes evil possible, is also the only thing that
makes possible any love or goodness or joy worth having."
*- C. S. Lewis*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Change name part of a JSON object

[Bernardo Sulzbach]

On 03/31/2018 09:58 AM, Robert M. Münch wrote:


A hack would be to search & replace “Untitled“: with the new name… but might be 
dangerous… any better idea?



Unless you are certain that the text you are replacing cannot occur 
anywhere else, this is asking for problems.

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

[sqlite] Change name part of a JSON object

[Robert M. Münch]

Hi, we use one JSON column to store column data that was added after tables 
were created. If such a column is now renamed, we need to rename the name part 
as well.

Example: {"Untitled":"bla"} How to change “Untitled“ ?

A hack would be to search & replace “Untitled“: with the new name… but might be 
dangerous… any better idea?

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users