Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

2014-10-10 Thread James K. Lowden 
On Thu, 9 Oct 2014 11:16:25 -0400
Stephen Chrzanowski  wrote:
> On Wed, Oct 8, 2014 at 8:38 PM, James K. Lowden
>  wrote:
> 
> >
> > The problem I see with your suggestion is that I can't think of
> > another situation, with or without NULL, with or without defaults,
> > where
> >
> > insert into T (t) value (X)
> >
> > results in T.t = Y.  You should get what you asked for, or an error,
> > not a magical transformation.

> I wouldn't call it 'magical' if the definition is right on the field
> declaration

Perhaps "magical" wasn't the best term.  My simple point is that in no
other case does inserting a value X into a column result in a
different value Y appearing there.  

The "value (DEFAULT)" syntax does not suffer from that problem.  

I hear someone saying "triggers".  Sure, you can abuse the system.
Anyone burned by triggers that do anything other than ensure
referential integrity quickly learns to be twice shy.  The system is
not improved when it subverts stated intentions.  

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

Re: [sqlite] Curious datetime/strftime BUG?

2014-10-10 Thread John 



On 6/10/2014 18:35, Clemens Ladisch wrote:

John wrote:

On 5/10/2014 19:59, Clemens Ladisch wrote:

The documentation  says:
| These functions only work for dates between -01-01 00:00:00 and
| -12-31 23:59:59. For dates outside that range, the results of
| these functions are undefined.


All equivalent functions should return consistent results.


Why do you assume that "undefined" should imply consistency?  datetime()
could return "Cthulhu fhtagn" at the Ides of any month in such a year,
and there would be nothing wrong with it.  Undefined allows _anything_.



Ok Clemens, I concede, undefined does allow _anything_. God, it's got to 
be 40 years since I read Lovecraft.


Regards,
John

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

Re: [sqlite] Will someone be able to explain this weird outcome...

2014-10-10 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/10/2014 01:18 PM, jose isaias cabrera wrote:
> I was able to figure out that comma's are more important than just
> a 1000 number delemeter, so I received the right answer by taking
> the commas out:

To help avoid this in the future, be aware that how developers deal
with numbers and how users experience them are very different.

People using your apps expect to see numbers in the normal way for
them.  For example thousands separators are useful, but note that some
locales group differently (eg around ten thousands).  Some use dots
not commas, and others the other way around.  Some use dots for the
decimal point and others use a comma.  Some don't use Arabic numerals
(0, 1, 2, 3 etc)

  https://en.wikipedia.org/wiki/Decimal_mark#Digit_grouping

Fortunately the operating system and programming environment provide
ways to output numbers (and dates, currency etc) in the most
appropriate way for the user.

Widespread programming languages wouldn't work very well if numbers
weren't consistently formatted (eg what happens if a developer in a
different locale runs the code).  Reflecting their origins, they
almost always only accept the anglo-centric integer notation of no
grouping and a dot as the decimal point.  SQLite uses SQL which does
the same.

For you that means separating out text that you are using with SQLite,
versus text that is shown/accepted from the user.  If you mix them
together you'll end up with unexpected behaviour, crashes, wrong
results etc.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlQ4dEsACgkQmOOfHg372QS17gCdGr31RcjBKe7ncvHbR8yAyoCW
dkAAoMZyiAzNIsVkirunvVWCh5ADspPq
=fCjG
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if file exists in SQL syntax?

2014-10-10 Thread Keith Medcalf 

You do not say what operating system, but for example, on Windows, the 
following function works:

SQLITE_PRIVATE void _GetFileAttributes(sqlite3_context *context, int argc, 
sqlite3_value **argv)
{
sqlite3_result_int(context, 
GetFileAttributesW(sqlite3_value_text16(argv[0])));
}

which is then declared to SQLite function interface (for connection db) thus:

nErr += sqlite3_create_function(db, "GetFileAttributes", 1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  0, _GetFileAttributes,0, 0);

Returning the file attributes for the given pathspec, or -1 if it does not 
exist.


SQLite version 3.8.7 2014-10-09 15:08:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select getfileattributes('D:\');
22
sqlite> select getfileattributes('D:\momo');
-1
sqlite> select getfileattributes('D:\source\sqlite\sqlite3s.exe');
32
sqlite> select getfileattributes('D:\source\sqlite');
16
sqlite>

You do similar on Unices just substitute the appropriate call to get the file 
attributes ...

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Krzysztof
>Sent: Friday, 10 October, 2014 12:21
>To: General Discussion of SQLite Database
>Subject: [sqlite] Check if file exists in SQL syntax?
>
>Hi,
>
>I'm collecting file names (full paths) in sqlite table. Each day I
>need to remove non existing files from this table (thousands records).
>For now I'm selecting all records and then checking if file exists
>using C++ routine and if not then deleting it. I'm wondering if SQLite
>has such function (could not find it in core functions). For example I
>would like to call something like:
>
>CREATE TABLE my_table {
>  id INTEGER PRIMARY KEY,
>  filename TEXT,
>  tags TEXT
>}
>
>DELETE FROM my_table WHERE NOT FileExists(filename)
>
>Regards
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Will someone be able to explain this weird outcome...

2014-10-10 Thread Simon Slavin 

On 10 Oct 2014, at 9:27pm, to...@acm.org wrote:

> sqlite> select "7,915" - "5,021";
> 2
> 
> But, would someone explain the result of 2?  Sorry for this child-like
> question, but I can't find the how the result of 2 came to be displayed.

7 - 5 = 2

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

Re: [sqlite] maintaining order state for custom functions in sqlite: is it possible to insure that callback gets records in "ORDER BY" order?

2014-10-10 Thread Clemens Ladisch 
john soprych wrote:
> Is there a good way to create functions in sqlite where you can insure that
> your function callback will be called in order?

No.

Your best bet is writing the query in such a way that the function is
applied to an already ordered sequence:

SELECT myfunc(x) FROM (SELECT x FROM t ORDER BY x);


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

[sqlite] maintaining order state for custom functions in sqlite: is it possible to insure that callback gets records in "ORDER BY" order?

2014-10-10 Thread john soprych 
Is there a good way to create functions in sqlite where you can insure that 
your function callback will be called in order? 

The api currently has support for purely functional functions and result 
set aggregates, but neither require that their respective callbacks are 
called in sort order (ORDER BY).  

The built in aggregate avg() function works on entire result set and so it 
doesn't matter if it receives rows 'out of order' (say in respect to time), 
but a moving average function would need to maintain a window period, e.g. 
ordered set of previous rows -- to work properly.

Is there a way of insuring such an order with minimal impact on query 
optimization that is not a total hack that completely circumvents the 
sqlite execution pipeline? 

For example: as a compromise, I could live with a robust, single-threaded 
build that would insure the ordered calls to my custom function callback. 

I suspect there is no palatable fix, but I'd appreciate getting 
confirmation from someone with a better grasp of the internals :)

Thanks in advance,

john
 


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

Re: [sqlite] how set the threading mode with ado.net sqlite provider

2014-10-10 Thread Joe Mistachkin 

korkless wrote:
> 
> hi, how can i set the threading mode at Start-time/Run-time as descrived
> here https://www.sqlite.org/threadsafe.html?
> i have searched also in the source code but i cannot find any api or
> connectionstring option to do it 
> 

The threading mode cannot currently be set via System.Data.SQLite.

--
Joe Mistachkin

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

Re: [sqlite] Will someone be able to explain this weird outcome...

2014-10-10 Thread Jean-Christophe Deschamps 

select 7,915 - 5,021
is:
select 7,  915 - 5,  021
giving
7  91021
just like
select 'a', 915 - 5, 'b'

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

Re: [sqlite] Will someone be able to explain this weird outcome...

2014-10-10 Thread tonyp 
Apparently, in trying to make numbers out of strings, it is interpreted as 
7 - 5 = 2 and the part after the comma is truncated.


-Original Message- 
From: jose isaias cabrera


sqlite> select "7,915" - "5,021";
2

But, would someone explain the result of 2?  Sorry for this child-like
question, but I can't find the how the result of 2 came to be displayed.

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

[sqlite] Will someone be able to explain this weird outcome...

2014-10-10 Thread jose isaias cabrera 


Greetings!

select 7,915 - 5,021;

displays this result:

7|910|21

I was really looking to have 2,894 returned, but instead I received the 
above.  Then, I added quotes,


sqlite> select "7,915" - "5,021";
2
sqlite> select '7,915' - '5,021';
2

I was able to figure out that comma's are more important than just a 1000 
number delemeter, so I received the right answer by taking the commas out:


sqlite> select 7915 - 5021;
2894

But, would someone explain the result of 2?  Sorry for this child-like 
question, but I can't find the how the result of 2 came to be displayed. 
Thanks.


josé
Any thoughts? 


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

Re: [sqlite] sqlite data source not available

2014-10-10 Thread Kevin Benson 
On Fri, Oct 10, 2014 at 11:55 AM, Ben Lam  wrote:

> Hi,
>
> Really appreciate any help, spent a day and a half trying to figure this
> out without success. I can't get 'System.Data.SQLite Database File' to show
> up as an option I the 'choose data source' window.
>
> My environment: VS2010 SP1, WIN 8.1 PRO
>
> 1. Installed sqlite-netFx40-setup-bundle-x86-2010-1.0.94.0.exe, ran as
> Administrator, checkmarked 'Install the designer components for Visual
> Studio 2010', avast! antivirus disabled
> 2. Created new WPF project
> 3. Used Nuget package manager console: Install-Package System.Data.SQLite
> 4. Right click on project, Add -> Data -> ADO.NET 
> Entity Data Model; Generate from database; New Connection, in the 'Choose
> Data Source' window I don't see 'System.Data.Sqlite Database File' as
> expected
>
> Snippet from app.config:
>
> < DbProviderFactories>
>description=".NET Framework Data Provider for SQLite"
> type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
>   
>   
>invariant="System.Data.SQLite.EF6" description=".NET Framework Data
> Provider for SQLite (Entity Framework 6)"
> type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6"
> />
> 
>   
>   
>  type="System.Data.Entity.Infrastructure.SqlConnectionFactory,
> EntityFramework" />
> 
>type="System.Data.Entity.SqlServer.SqlProviderServices,
> EntityFramework.SqlServer" />
>type="System.Data.SQLite.EF6.SQLiteProviderServices,
> System.Data.SQLite.EF6" />
> 
>   
>
>
>

Did you already do as Joe asked ?

http://sqlite.1065341.n5.nabble.com/sqlite-data-source-not-available-td78521.html

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if file exists in SQL syntax?

2014-10-10 Thread Igor Tandetnik 

On 10/10/2014 2:20 PM, Krzysztof wrote:

I'm collecting file names (full paths) in sqlite table. Each day I
need to remove non existing files from this table (thousands records).
I'm wondering if SQLite
has such function (could not find it in core functions).


SQLite doesn't have such a function - but it provides a way for you to 
create your own custom functions. So you can write one.

--
Igor Tandetnik

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

[sqlite] How to use multiple connections with locking_mode=exclusive

2014-10-10 Thread Deon Brewis 
Richard implied in this bug that you can use locking_mode=exclusive when you
have a single process using that database, but that process has multiple
threads & connections:

https://bugzilla.mozilla.org/show_bug.cgi?id=993556


However, I've tried this and can't get it to work - the second open call
that comes around will block.

Am I misunderstanding the post, or just not getting the configuration right? 

I'm running:
WAL / NORMAL.

And my 2 sqlite3_open_v2 calls uses:
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX |
SQLITE_OPEN_PRIVATECACHE





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-use-multiple-connections-with-locking-mode-exclusive-tp78528.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Check if file exists in SQL syntax?

2014-10-10 Thread Krzysztof 
Hi,

I'm collecting file names (full paths) in sqlite table. Each day I
need to remove non existing files from this table (thousands records).
For now I'm selecting all records and then checking if file exists
using C++ routine and if not then deleting it. I'm wondering if SQLite
has such function (could not find it in core functions). For example I
would like to call something like:

CREATE TABLE my_table {
  id INTEGER PRIMARY KEY,
  filename TEXT,
  tags TEXT
}

DELETE FROM my_table WHERE NOT FileExists(filename)

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

Re: [sqlite] Stored Procedures

2014-10-10 Thread Nico Williams 
You can't change the NEW "row" in trigger bodies.

Since you can't make "SELECT"s (or virtual tables) this way, all your
"stored procedure" can do is INSERT/UPDATE/DELETE anyways.  Using
coalesce(NEW.foo, "default value") works fine (and it's how you'd
default "SP arguments").

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

[sqlite] sqlite data source not available

2014-10-10 Thread Ben Lam 
Hi,

Really appreciate any help, spent a day and a half trying to figure this out 
without success. I can't get 'System.Data.SQLite Database File' to show up as 
an option I the 'choose data source' window.

My environment: VS2010 SP1, WIN 8.1 PRO

1. Installed sqlite-netFx40-setup-bundle-x86-2010-1.0.94.0.exe, ran as 
Administrator, checkmarked 'Install the designer components for Visual Studio 
2010', avast! antivirus disabled
2. Created new WPF project
3. Used Nuget package manager console: Install-Package System.Data.SQLite
4. Right click on project, Add -> Data -> ADO.NET Entity Data Model; Generate 
from database; New Connection, in the 'Choose Data Source' window I don't see 
'System.Data.Sqlite Database File' as expected

Snippet from app.config:


  
  
  
  

  
  


  
  

  



I am using the Free version of SPAMfighter.
SPAMfighter has removed 689 of my spam emails to date.

Do you have a slow PC? Try 
a free scan!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how set the threading mode with ado.net sqlite provider

2014-10-10 Thread korkless 
hi, how can i set the threading mode at Start-time/Run-time as descrived here 
https://www.sqlite.org/threadsafe.html?
i have searched also in the source code but i cannot find any api or 
connectionstring option to do it 

note: i'm using the last release for .net 4 


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

[sqlite] Shell not returning 1 on error

2014-10-10 Thread John Taylor 

This error occurs in 3.8.6:

$ echo 'foo' > /tmp/foo.sql
$ sqlite3 /tmp/foo.db < /tmp/foo.sql
Error: incomplete SQL: foo
$ echo $?
0

0 is the return code from sqlite3 and this should instead return 1 since
an error occured.

shell.c:3644

if( nSql ){
  if( !_all_whitespace(zSql) ){
  fprintf(stderr, "Error: incomplete SQL: %s\n", zSql);
}
free(zSql);
}
free(zLine);
return errCnt>0;
==

I think there should be a errCnt++; after the fprintf statement.
I recompiled with this statement. It now returns 1 back to the OS.

See also:
http://stackoverflow.com/questions/26286104/sqlite3-command-line-inconsistent-return-codes

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

Re: [sqlite] Stored Procedures

2014-10-10 Thread Mark Lawrence 
On Thu Oct 09, 2014 at 05:38:57PM -0500, Nico Williams wrote:
> I use triggers, particularly INSTEAD OF INSERT triggers on VIEWs.
> The values of the columns of the rows to be "inserted" are the
> "stored procedure's" arguments.

I would like to able to do this too, but INSTEAD OF INSERT on a view
does not support default values for arguments the same way that BEFORE
INSERT on a regular table does.

What would really be nice is if one could run the following inside a
BEFORE or INSTEAD OF trigger:

UPDATE
NEW
SET
NEW.name = COALESCE(NEW.name, new_value)
;

Could the SQLite team perhaps comment on how difficult this would be to
implement?

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