[sqlite] Lua inside SQLite

[Darko Volaric]

Wow, what a great feature. This saves me so much hacking!

I planned on using virtual tables but per-query instance creation and
parameter passing was going to be such a mess.

On Sun, Aug 23, 2015 at 1:55 PM, Richard Hipp  wrote:

> On 8/23/15, Abilio Marques  wrote:
> >
> > 1. sqlite3 CLI doesn't seem to "load" the .so easily... The generated so
> is
> > called "lua.ext". If I run "*.load lua.ext*" it claims that there is no
> .so
> > available. I went into renaming the file to lua.so, and ran *.load
> lua.so*
> > ... Got: *Error: Shared object "lua.so.so " not found,
> > required by "sqlite3"* ... then tried with *.load lua* ... I guessed that
> > would work... but got *Error: Shared object "lua.so" not found, required
> by
> > "sqlite3*", even when the file was present at that directory... But then
> I
> > ran *.load ./lua.so* , and even *.load ./lua* ... both loaded without
> > problems... Is that the correct behavior?
>
> Yes. You each have to specify a pathname or else the library must be
> on LD_LIBRARY_PATH.  I think this is a security restriction.  Maybe
> somebody else can shed more light on the matter.
>
> >
> > 2. Which is the convention for the file extension of a loadable module?
> .so
> > and .dll?, or is there any other name used regularly among sqlite3 users?
>
> .so for linux.  .dylib for mac.  .dll for windows.
>
> You can just do ".load ./lua" without the suffix and SQLite will
> supply the correct suffix for your platform.
>
>
> >
> > 3. Lua can return arrays. Also, Lua can return multiple values. Tried to
> > take advantage of those facts, but while reading the SQLite API
> > documentation, found no function where I could map multiple values as the
> > return of a function. Am I wrong? For example:
> >
> > select * from table t where fieldA in (lua('return 1,2,3'));
> >
> > Could be a useful feat in real scripts with real code.
> >
>
> Regular SQL functions always return scalars in SQLite.
>
> See https://www.sqlite.org/src/artifact/b8fb7befd85b3a9b for an
> example of how to implement table-valued functions.  This is a new
> feature so there is no documentation on it yet.  But the example is
> well-commented.  This capability will be in the next release, so
> you'll have to compile from trunk if you want to use it right away -
> it is not found in 3.8.11.1.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] ATTACH DATABASE statement speed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/23/2015 06:48 PM, Simon Slavin wrote:
> Paolo is using Linux which does not do read-ahead optimization like
> some versions of Windows.  Therefore if he really is using an SSD
> then fragmentation is not an issue.

You are confusing things.  The data structures used in a copy on write
filesystem (as both btrfs and zfs are) are what gets fragmented.  This
is not like a traditional filesystem that will update existing data
structures.  It is more analoguous to garbage collection.

I promise you that it really does become an issue, even with an SSD.
When a file is across ten thousand fragments, latency and throughput
suffer.

> I have seen fragmentation make something take twice as long.

You are talking about traditional fragmentation, not a degenerate
state for copy on write based filesystems.

> My suspicion here is that there's a design fault in ZFS.

Unless zfs does automatic defragging, it will have exactly the same
problems as btrfs.  This is inherent in how the data structures are
laid out, and that nothing is modified in place.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXakBgACgkQmOOfHg372QTepACffpEZ/tozxJKv0bKgZQ0D0wIL
HqUAn3ES+b+xr/c8h7I/lqJs1zhQRVrg
=+S02
-END PGP SIGNATURE-

[sqlite] DbFunctions.TruncateTime

[Joe Mistachkin]

The LINQ provider for System.Data.SQLite does not provide this function
directly; however, it may be possible to use one of the core date-time
related SQL functions to do it?

https://www.sqlite.org/lang_datefunc.html

--
Joe Mistachkin

[sqlite] ATTACH DATABASE statement speed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/19/2015 05:56 PM, Paolo Bolzoni wrote:
> I left running the pragma quick check during the night and finished
> in 2 hours and 46 minutes, so it is about 8 times slower than in
> ext4. Zfs is an advanced filesystem plenty of features, but this
> speed difference is too much I think.

I use btrfs which like zfs is also a copy on write filesystem.  It is
possible for the files to get very fragmented which can result in
dismal performance, even on an SSD even for reads.  Random small
writes especially aggravate this.  btrfs has an autodefrag option that
addresses this in the background, and SQLite is specifically mentioned
as all the browsers use it behind the scenes as do many email clients.

  https://btrfs.wiki.kernel.org/index.php/Gotchas  (2nd last section)

The filefrag command may be helpful if implemented for zfs and will
tell you if fragmentation is a problem.

Virtual machine images are another problematic file type with similar
read/write patterns to SQLite.

Copy on write filesystems don't modify existing (meta)data, but rather
write new versions that point to the existing data for bits that
aren't changed.  Repeat this many times and the chains of pointers get
very long, which is the fragmentation.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXadDsACgkQmOOfHg372QRdpACfRUmHD4hXfAx6+il0q/7lINxd
9lwAoNAyYV1oa5cYcn1O00JxW4nsI/Sp
=VrTq
-END PGP SIGNATURE-

[sqlite] ESC_BAD_ACCESS when using a background thread

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/23/2015 03:31 AM, Jeff M wrote:
> sqlite3_step();   // occasionally crashes here (showing
> ESC_BAD_ACCESS on main thread)

That has three very likely causes.  The first is that your internal
state gets messed up, and the statement has actually been
finalized/freed.  ie it is junk memory.  You can add assertions that
check it is still a known statement pointer by checking this returns it:

  http://sqlite.org/c3ref/next_stmt.html

The second is that the memory where you stored the statement pointer
is what is trashed.

The final cause is that some other code has memory bugs, causing
damage to SQLite's data structures.

> It's not a zombie object issue (tested with NSZombieEnabled).

Sadly that only checks Objective C objects, and not all memory.

> Any ideas on how to debug this?

I used valgrind running the app in the simulator.  (I also configure
valgrind to never actually reuse memory.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXaXpEACgkQmOOfHg372QRKZACfWyT6pEyNQ9sEKPbhFQ4pI/5G
Nh0AniO5ESx9CIbB484/gYqjtfCsGUrM
=Op+8
-END PGP SIGNATURE-

[sqlite] Lua inside SQLite

[Richard Hipp]

On 8/23/15, Abilio Marques  wrote:
>
> 1. sqlite3 CLI doesn't seem to "load" the .so easily... The generated so is
> called "lua.ext". If I run "*.load lua.ext*" it claims that there is no .so
> available. I went into renaming the file to lua.so, and ran *.load lua.so*
> ... Got: *Error: Shared object "lua.so.so " not found,
> required by "sqlite3"* ... then tried with *.load lua* ... I guessed that
> would work... but got *Error: Shared object "lua.so" not found, required by
> "sqlite3*", even when the file was present at that directory... But then I
> ran *.load ./lua.so* , and even *.load ./lua* ... both loaded without
> problems... Is that the correct behavior?

Yes. You each have to specify a pathname or else the library must be
on LD_LIBRARY_PATH.  I think this is a security restriction.  Maybe
somebody else can shed more light on the matter.

>
> 2. Which is the convention for the file extension of a loadable module? .so
> and .dll?, or is there any other name used regularly among sqlite3 users?

.so for linux.  .dylib for mac.  .dll for windows.

You can just do ".load ./lua" without the suffix and SQLite will
supply the correct suffix for your platform.


>
> 3. Lua can return arrays. Also, Lua can return multiple values. Tried to
> take advantage of those facts, but while reading the SQLite API
> documentation, found no function where I could map multiple values as the
> return of a function. Am I wrong? For example:
>
> select * from table t where fieldA in (lua('return 1,2,3'));
>
> Could be a useful feat in real scripts with real code.
>

Regular SQL functions always return scalars in SQLite.

See https://www.sqlite.org/src/artifact/b8fb7befd85b3a9b for an
example of how to implement table-valued functions.  This is a new
feature so there is no documentation on it yet.  But the example is
well-commented.  This capability will be in the next release, so
you'll have to compile from trunk if you want to use it right away -
it is not found in 3.8.11.1.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Compile warnings

[Scott Robison]

On Sat, Aug 22, 2015 at 8:07 PM, David Bennett  wrote:

> Of course that is the aim, as always.
>
>
>
> In this particular case, maximally portable code (that will compile and
> execute correctly on all conforming compilers) must (a) ensure that the
> pointer argument is valid (b) ensure that the length is valid, or zero.
> Where reasonably possible both should be done statically. If conditional
> code is introduced then it must be tested with all branches covered.
>

Agreed, and in C89 NULL was a valid pointer argument in this context as
long as the length was zero. That has nothing to do with this particular
thread, but I referenced it just as a point of "C99 changed the semantics
of what is valid, invalidating previously valid code". Projects that took
advantage of that can either modify their code to accommodate the newer
standard or leave it along claiming it conforms to the intended / desired
standard. In that case, it was easy to change to code to be compatible with
both standards and it was done. In this case (gcc warning about possible
argument order error due to a constant expression) can be equally
accommodated, but it's not a matter of standards compliance. It *is* a
matter of one of the (or perhaps *the*) most used compilers bellyaching
about something that is not wrong or invalid in any way. So is the code
modified to suppress the warning, is the warning disabled globally,
locally, or just ignored? I can see the benefits to making the change and
to not making the change.

> As always, it may be the case that one or more compilers may issue
> warnings for code that is fully compliant with the standard and fully
> tested. Sadly there may even be compilers that compile the code incorrectly
> (a compiler bug). The question of how to handle undesired warnings or
> compiler bugs on code that is known to be correct and compliant is always a
> judgement call. In my opinion the solution chosen should always be as
> fine-grained as possible (such as a compiler-specific conditional), but the
> downside is that the code can become littered with references to specific
> problems in specific compilers and thus become harder to work with.
>

I agree completely. Most of us don't have to worry about this too much
because we target one platform (most code is not written with portability
in mind). SQLite is not most projects.


> In my opinion changes that are visible to other compilers should be
> avoided unless the changed code is an equally valid solution to the problem
> at hand and/or the problem affects multiple compilers. In this light adding
> an if-test would be an incorrect choice (and would require an additional
> set of tests). Suppressing the warning specifically for this compiler would
> be a preferable solution.
>

Agreed for the most part. Just to be clear: I never said "don't make this
change" (I don't think). I just wanted to bring up the thought that an if
statement *might* degrade performance in a code base that has been
carefully tuned to maximize efficiency.  I wasn't thinking of test cases or
such, just efficiency. That being said, if gcc is generating the warning
because a constant expression has a value of zero, an if statement might
actually increase performance by providing the compiler with the knowledge
that it can completely remove the corresponding memset because the
expression will always be false. In that case, add the if might be the
exact right thing to do.

Again we're to the point of "there is no one universal right solution to
this issue". The only thing I can say is: not all warnings are equally bad,
and I will review warnings that are generated from third party code (such
as SQLite) but I rarely will do anything to try to suppress them. Making my
code right is a hard enough task. I don't need to "fix" third party code
(as long as it passes testing).

-- 
Scott Robison

[sqlite] Lua inside SQLite

[Abilio Marques]

Hi,

I'm a regular user of SQLite, but a first timer in this list. A few months
ago, while doing some intensive but manual data processing, I created
(hacked) a loadable module that allowed me to run short Lua scripts
directly inside the SQL queries. That eased a lot of the work that day.

I'm making it public today, at: https://github.com/abiliojr/sqlite-lua
(under BSD license)

Yet, I have 3 questions:

1. sqlite3 CLI doesn't seem to "load" the .so easily... The generated so is
called "lua.ext". If I run "*.load lua.ext*" it claims that there is no .so
available. I went into renaming the file to lua.so, and ran *.load lua.so*
... Got: *Error: Shared object "lua.so.so " not found,
required by "sqlite3"* ... then tried with *.load lua* ... I guessed that
would work... but got *Error: Shared object "lua.so" not found, required by
"sqlite3*", even when the file was present at that directory... But then I
ran *.load ./lua.so* , and even *.load ./lua* ... both loaded without
problems... Is that the correct behavior?

2. Which is the convention for the file extension of a loadable module? .so
and .dll?, or is there any other name used regularly among sqlite3 users?

3. Lua can return arrays. Also, Lua can return multiple values. Tried to
take advantage of those facts, but while reading the SQLite API
documentation, found no function where I could map multiple values as the
return of a function. Am I wrong? For example:

select * from table t where fieldA in (lua('return 1,2,3'));

Could be a useful feat in real scripts with real code.


Anyways, I hope some of you can find the spirit of this plugin useful for
some applications, and of course, any ideas, suggestions, bug reports and
improvements are welcomed!

[sqlite] ESC_BAD_ACCESS when using a background thread

[Simon Slavin]

On 23 Aug 2015, at 11:31am, Jeff M  wrote:

> Any ideas on how to debug this?

Are you checking the values returned by sqlite3_prepare, sqlite3_bind, and 
sqlite3_step, to make sure they return SQLITE_OK ?

I'm not quite sure what you mean by 'done once' -- whether it's once for the 
whole application or once per loop -- but I think either the 
sqlite3_prepare_v2() should be moved outside the load logic, or you should be 
using _finalize() instead of _reset().  But I don't think this could cause the 
problem you reported.

Simon.

[sqlite] Compile warnings

[David Bennett]

Of course that is the aim, as always.



In this particular case, maximally portable code (that will compile and execute 
correctly on all conforming compilers) must (a) ensure that the pointer 
argument is valid (b) ensure that the length is valid, or zero. Where 
reasonably possible both should be done statically. If conditional code is 
introduced then it must be tested with all branches covered.



As always, it may be the case that one or more compilers may issue warnings for 
code that is fully compliant with the standard and fully tested. Sadly there 
may even be compilers that compile the code incorrectly (a compiler bug). The 
question of how to handle undesired warnings or compiler bugs on code that is 
known to be correct and compliant is always a judgement call. In my opinion the 
solution chosen should always be as fine-grained as possible (such as a 
compiler-specific conditional), but the downside is that the code can become 
littered with references to specific problems in specific compilers and thus 
become harder to work with.



In my opinion changes that are visible to other compilers should be avoided 
unless the changed code is an equally valid solution to the problem at hand 
and/or the problem affects multiple compilers. In this light adding an if-test 
would be an incorrect choice (and would require an additional set of tests). 
Suppressing the warning specifically for this compiler would be a preferable 
solution.



[Disclosure: I have just over 30 years of trying to write portable C code 
across 30 or so compilers. That doesn?t mean I?m right, but it does mean I?ve 
thought a lot about the problem and made most of the usual mistakes.]



Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org





From: Scott Robison [mailto:sc...@casaderobison.com] 
Sent: Sunday, 23 August 2015 2:22 AM
To: General Discussion of SQLite Database ; davidb at pfxcorp.com
Subject: Re: [sqlite] Compile warnings



Unless of course your objective is to write maximally portable code. It's not 
perfect, and certainly things have been done to accommodate more recent 
standards, but C89 compilers (including compilers that claim to support it 
through a switch) are more common than even full C99 implementations.

Don't get me wrong, I don't object to a change to accommodate C99 null pointer 
requirements or even (necessarily) a change to suppress warnings, even if both 
requirements are needlessly strict in some situations. I just don't think that 
C99 or some compilers warning setup should mandate a change.

On Aug 22, 2015 8:36 AM, "David Bennett" mailto:davidb 
at pfxcorp.com> > wrote:

True. The C89/90 standard has of course been withdrawn and I don't have a
copy. However, based on the drafts I have available the entirety of the text
I quoted was added after the last correction to C89/90 and first appears in
C99. In my opinion it would be unwise to rely on the omission of material
from the earlier version of the standard to justify code that would be
non-compliant with C99 and all later standards.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org  

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org 
 ] On Behalf Of Scott
Robison
Sent: Saturday, 22 August 2015 2:05 AM
To: General Discussion of SQLite Database
mailto:sqlite-users at 
mailinglists.sqlite.org> >; davidb at pfxcorp.com  
Subject: Re: [sqlite] Compile warnings

And C89 doesn't have the valid pointer requirement On Aug 21, 2015 7:03 AM,
"David Bennett" mailto:davidb at pfxcorp.com> > wrote:

> Addressing only standards compliance, the C99 (n1256) standard says as
> follows.
>
> 7.21.1 /2
> Where an argument declared as size_t n specifies the length of the
> array for a function, n can have the value zero on a call to that
> function. Unless explicitly stated otherwise in the description of a
> particular function in this subclause, pointer arguments on such a
> call shall still have valid values, as described in 7.1.4. On such a
> call, a function that locates a character finds no occurrence, a
> function that compares two character sequences returns zero, and a
> function that copies characters copies zero characters.
>
> Later versions of the standard contain similar wording.
>
> A zero value for the third argument of memset() is standards
> compliant. Any warning by any compiler is for the convenience of
> developers and may be safely disabled or ignored while remaining standards
compliant.
>
> In my opinion, disabling or simply ignoring the warning are both
> legitimate choices. Modifying the code to suppress the warning is NOT.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org  
>
> -Original 

[sqlite] design problem involving trigger

[Barry Smith]

Could this not be achieved by two indexes: one partial and one complete?

CREATE UNIQUE INDEX idx_books1 ON Books(title, author);

CREATE UNIQUE INDEX idx_books2 ON Books(title) WHERE author ISNULL;

To save space and (maybe) time, you could put a 'WHERE author NOTNULL' on the 
first index.

Of course, I'm just talking about how to code it, the issues mentioned by R 
Smith is a different kettle of fish.

Cheers,

Barry


> On 23 Aug 2015, at 3:05 am, "R.Smith"  wrote:
> 
> 
> 
>> On 2015-08-21 11:23 PM, Will Parsons wrote:
>>> On 21 Aug 2015, R.Smith wrote:
>>> 
 On 2015-08-21 04:47 AM, Will Parsons wrote:
 I'm working on a program that involves a catalogue of books.  Part of
 the database schema looks like this:
 
 create table Books(id integer primary key,
 title text collate nocase not null,
 author references Authors(id),
 ...
 unique(title, author));
 
 create table Authors(id integer primary key,
   name text unique not null check(name <> ''));
 
 The idea here is that the title+author of a book must be unique, but a
 book may not necessarily have an author associated with it.  But, the
 schema fragment as I have presented it does not disallow entering the
 same title with a null author multiple times, which is clearly
 undesirable.
 
 In thinking about how to avoid this, one possibility that has occurred
 to me is to add an entry to the Authors table with id=0, name=NULL, and
 to modify the Books table to be:
 
 create table Books(id integer primary key,
 title text collate nocase not null,
 author references Authors(id) not null,
 ...
 unique(title, author));
 
 With this, entries in the Books table that presently have the author
 field set to NUll would instead have author=0.
 
 What I would like to have is a trigger that when an attempt is made to
 enter a new record into the Books table with a NULL author field, is
 to force the author field to 0 instead.  I can't see how to do this
 with a "before insert" trigger, though.  Perhaps I'm approaching this
 the wrong way; in any case I'd appreciate advice.
>>> Nothing wrong with your approach, simply declare the Author to be NOT
>>> NULL and if it is possible to have non-Authors you could do what you
>>> suggest, add a 0-Author to the parent table, or, you could use a partial
>>> Index (available after SQLite 3.7 only I think) which would allow you to
>>> have NULLS but still maintain a strict relation, something like this:
>>> 
>>> create table Books(id integer primary key,
>>> title text collate nocase not null,
>>> author int not null references Authors(id),
>>> ...
>>> );
>>> 
>>> create unique index uBookAuth on Books(title,author) where author is not 
>>> null;
>>> 
>>> create table Authors(id integer primary key,
>>>   name text unique not null check(name <> ''));
>> I guess I don't understand how having a partial index where author is
>> not null prevents adding two entries with the same title and a null
>> author.  How is this different from what I have now with the
>> "unique(title, author)" specification?
> 
> I think I may have misinterpreted slightly...
> 
> To clarify: are the book titles unique or are they not?
> 
> If they are Unique, i.e if no book title can ever appear twice, regardless 
> who authored it, then simply declare a unique index on title.
> If they are NOT Unique, i.e. if a title can appear more than once, then why 
> is it not working for you?
> 
> Essentially, if I interpret correctly, you are asking the DB to NOT limit the 
> number of same-titled books, except when you have supplied an author and the 
> same author has already such a titled book, but then when you don't supply an 
> author, it should know to now also limit the copies of  no-author books?
> 
> I assumed before that you only added NULL for author if you don't know the 
> author yet (which would make sense and can later be updated) but then you 
> can't force the unique constraint, there may be many books with coinciding 
> titles and not-yet-known authors.
> 
> Reading again, I am now thinking that's not the case, you might add NULL 
> authors to books which simply don't have authors (well, all books have 
> authors, but the author might be unknown and accepted to be of unknown status 
> for time to come), in which case, there might be many same-titled 
> unknown-author books.
> 
> If this is the case and you still want to limit unknown author books to just 
> 1 instance, I would suggest to use an explicit author name, maybe something 
> like "(by Unknown)" which would be happily subdued by the Unique constraint 
> if violated. 

[sqlite] ESC_BAD_ACCESS when using a background thread

[Jeff M]

My iOS app displays a gallery of thumbnails in a tableView (four to a row).  To 
allow smooth scrolling, I lazy-load the image data on a background thread using 
dispatch_async().

sqlite3_threadsafe() returns true.
sqlite3_open_v2() uses SQLITE_OPEN_FULLMUTEX.
queue is a serial queue from dispatch_queue_create(...).
There is only one connection to the database.

lazy load summary logic:
   dispatch_async(queue, ^{
done once:  sqlite3_prepare_v2( "SELECT ..." ... );

sqlite3_bind_int(...);
sqlite3_bind_int(...);
sqlite3_step();   // occasionally crashes here (showing ESC_BAD_ACCESS 
on main thread)
... sqlite3_column_blob(...);
... sqlite3_column_bytes(...);
// ...
// occasionally crashes here (showing ESC_BAD_ACCESS on main thread) 
not inside SQLite
// ...
sqlite3_reset();
   });

The main thread is not doing anything with the DB when the crash occurs (it has 
previously loaded an array with all the id values needed for the lazy-load's 
SELECT statement).  It doesn't crash very often (might take 10, 20 or 30 
trials), but when it happens, it crashes while loading one of the first few 
thumbnails.

It's not a zombie object issue (tested with NSZombieEnabled).

Any ideas on how to debug this?

Jeff

[sqlite] design problem involving trigger

[R.Smith]

On 2015-08-23 03:32 AM, Barry Smith wrote:
> Could this not be achieved by two indexes: one partial and one complete?
>
> CREATE UNIQUE INDEX idx_books1 ON Books(title, author);
>
> CREATE UNIQUE INDEX idx_books2 ON Books(title) WHERE author ISNULL;
>
> To save space and (maybe) time, you could put a 'WHERE author NOTNULL' on the 
> first index.
>
> Of course, I'm just talking about how to code it, the issues mentioned by R 
> Smith is a different kettle of fish.
>
> Cheers,
>
> Barry

Yes this will work for what the OP wanted, I think.  Great suggestion, 
to prove the concept, consider:

   -- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
   -- Script Items: 10 Parameter Count: 0 SQLitespeed v2.0.1

   -- 2015-08-23 04:50:41.253  |  [Info]   Script Initialized,
Started executing...
   --



CREATE TABLE tA(c1 TEXT, c2 TEXT, c3 TEXT);

CREATE UNIQUE INDEX tAI1 ON tA(c1, c2);

CREATE UNIQUE INDEX tAI2 ON tA(c1) WHERE c2 IS NULL;

INSERT INTO tA VALUES ('ABC', 'Joe',  '1');

INSERT INTO tA VALUES ('ABC', 'John', '2');

INSERT INTO tA VALUES ('ABC', 'Jim',  '3');

INSERT INTO tA VALUES ('ABC', NULL,   '4');

INSERT INTO tA VALUES ('ABC', NULL,   '5');

   -- 2015-08-23 04:50:41.263  |  [ERROR]  UNIQUE constraint
failed: tA.c1
   -- 2015-08-23 04:50:41.264  |  [Info]   Script failed -
Rolling back...
   -- 2015-08-23 04:50:41.264  |  [Success]Transaction Rolled back.
   -- 2015-08-23 04:50:41.264  |  [ERROR]  Failed to complete:
Script Failed in Item 7: UNIQUE constraint failed: tA.c1
   -- ---  DB-Engine Logs (Contains logged information from all
DB connections during run)  --
   -- [2015-08-23 04:50:41.226] APPLICATION : Script
D:\Documents\SQLiteAutoScript.sql started at 04:50:41.226 on 23 August.
   -- [2015-08-23 04:50:41.263] ERROR (2067) : abort at 15 in
[INSERT INTO tA VALUES ('ABC', NULL,   '5');]: UNIQUE constraint
failed: tA.c1
   --

[sqlite] DbFunctions.TruncateTime

[R.Smith]

On 2015-08-23 12:16 AM, Steffen Mangold wrote:
> Hi Ryan,
>
> I get your point. :)
> It seems the I was misunderstanding this help mailing list. I thought it's 
> also support for 'System.Data.SQLite'.
>
> In the way 'System.Data.SQLite' is an ADO.NET provider for SQLite and also 
> give support for entity framework.
> that because I was asking if it support 'DbFunctions' like 'TruncateTime'.
> I'm asking because I want to know if I simpley miss the a way or a SQLite 
> ADO.Net class to do this.
>
> I hope I make it a little more clear why I'm asking in this mailing list.

Hang on, this be definitely the correct list for System.data.SQLite, and 
the dev is called Joe Mistachkin who do see this usually (though he just 
published a release within the week, so might be a bit swamped).

Also, many people here know that system well... I am still not convinced 
the code you've shown resembles the usual ADO.NET provider calls, but I 
do now think (and hope for you) someone might pipe up about it.



Cheers,
Ryan

PS: To be clear, I wasn't suggesting you posted on the wrong list, or 
are in any way unwelcome to post that question, I just tried to explain 
why the answers are not streaming in fast and furious. :)


>
> Regards Steffen
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] : it seems to be only orber by and group

[R.Smith]

This will work great - just a correction, there should be a comma after 
"columnB" in the order by clause, else it might not parse, so the 
revised is:

SELECT * FROM table
  ORDER BY columnA, columnB,
  CASE WHEN columnC = 1
  THEN 0 ELSE 1 END;



On 2015-08-22 10:47 PM, Kevin Benson wrote:
> On Sat, Aug 22, 2015
>
>> afriendandmore wrote:
>>> The table shall be ordered according to A. (Order by A)
>>> But if C ist true and to the value B1 in this Record, there exists
>> another Record with B2 and B2 = B1, then B1 should be ordered in the row
>> above B2.
>
> If the B1 and B2, to which you refer, are just two equal values in
> different rows of column B, then it sounds like you want:
>
> SELECT * FROM table
>   ORDER BY columnA, columnB
>   CASE WHEN columnC = 1
>   THEN 0 ELSE 1 END;
>
> If neither ASC or DESC are specified, rows are sorted in ascending (smaller
> values first) order by default.
> --
> --
>--
>   --???--
>  K e V i N
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DbFunctions.TruncateTime

[R.Smith]

On 2015-08-22 10:57 PM, Steffen Mangold wrote:
>> how can I trunc time in EntityFramework?
>>
>> I tried it this way:
>>
>>  model.Datas
>> .GroupBy(d => 
>> DbFunctions.TruncateTime(d.TimeStamp))
>> .Select(d => d.Key.Value)
>> .ToArray();
>>
>> But get this error:
>>  "SQLite error (1): no such function: TruncateTime"
>>
>> How else can I use the "date(timestring) function in EntityFramework?
>>
> No solution for this? :(
>
> Regards Steffen

Hi Steffen,
The little sadface prompted me to respond... :)

it's not that we don't want to answer, but that question is about a 
framework, not about SQLite, and very few people here might know how to 
do what you want, and those who do might not read it every day.

I do not know what ".GroupBy(d => DbFunction.TruncateTime(d.TimeStamp))" 
does at all...

I mean we've been around long enough to deduce the gist of what you are 
doing via some model-view system, but can't speak with any authority 
about it.
What I can tell for sure, is that those are not SQLite functions, nor 
are they SQL. Whatever you use to translate that code into something 
that spits out data is not a thing any of us have any authority on 
(well, some might).

It's a bit like buying a surfboard and tying it to the roof of your car 
and then asking the surf-board manufacturer if he can tell you how fast 
you can go like that... (He might suggest not to go too fast as a 
standard precaution, but can't speak definitively or with any authority 
on the subject).

I can tell you how to do what you want in SQL and in SQLite, though I am 
sure it won't help your case.

But, on the off chance it does, here goes:
The SQL to achieve what you want (as understood by SQLite) /might/ be 
like this:

SELECT date(d.TimeStamp) FROM sometable AS d WHERE d.Key='somevalue' 
GROUP BY date(d.TimeStamp);


And if you feed that into the C function in SQLite interface called 
sqlite3_prepareV2() as defined in the DLL you are linking against (I 
assume) then it will start a cursor and return a pointer to a prepared 
statement you can use to retrieve successive rows.

You might even be able to peek into your model and influence or change 
the actual SQL that reaches the engine directly, which might help, 
though the idea with those frameworks is probably to stay removed from 
the nuts and bolts.

Ok, that's a really convoluted way of saying, sorry, I am stupid, have 
no idea what you are talking about. I assume some Entity framework 
groupies might be able to help more or maybe someone here with knowledge 
of that framework might spot the request in the coming week and assist, 
but don't hold your breath.

Best of luck,
Ryan