[sqlite] Fix for non unix times DATETIME

[Simon Slavin]

On 10 Jul 2015, at 6:12pm, Lee Reiber  wrote:

> Searching for many days on a fix to the dreaded DATETIME mapping to .NET.

My guess is that this is a reference to





Simon.

[sqlite] Suggestions for Fast Set Logic?

[Simon Slavin]

On 10 Jul 2015, at 5:58pm, James K. Lowden  wrote:

> Simon Slavin  wrote:
> 
>> If you're going to do it properly you have a keywords column which
>> contains strings like
>> 
>> ,
>> ,animal,
>> ,animal,predator,
>> ,animal,predator,bird,
> 
> Why do it that way?  I would recommend a schema like Keith's, with one
> keyword per row in a separate table. 

I was trying not to change the ideas of the OP too much by introducing another 
table.  Keith's suggestion of normalization is, of course, a superior solution 
if the schema can be changed that much.

Simon.

[sqlite] Fix for non unix times DATETIME

[Lee Reiber]

More to the first post than the second yes

On Fri, Jul 10, 2015, 11:32 AM Simon Slavin  wrote:

>
> On 10 Jul 2015, at 6:12pm, Lee Reiber  wrote:
>
> > Searching for many days on a fix to the dreaded DATETIME mapping to .NET.
>
> My guess is that this is a reference to
>
> 
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Fix for non unix times DATETIME

[Lee Reiber]

Searching for many days on a fix to the dreaded DATETIME mapping to .NET.
Of course SQLite technically does not have a datetime per-say as I clearly
understand but the problem remains with dates such as Mac absolute and also
Java datetimes.  The fun "string cannot be converted to string" is what I
receive in return from .NET mapping on FILL to a datatable or dataset.

I have tried:

Connection string including FLAG: 256 - GetAlllAsText but this throws error
on System.Byte[] columns obviously.

Connection string including DateTimeFormat=UnixEpoch which works (does not
throw the exception) but of course Mac Absolute and Java times are
incorrectly displayed to the user in the datagridview.

Connection string including DateTimeFormat=Ticks which also works (no
exception) on most but all dates are 01/01/0001 which is expected.

This is the problem I have.  The databases my application is dealing with
are not created by my application so I cannot control the format (e.g.,
unix times, mac absolute, java, webkit) so a connection string specific
DateTimeFormat would not work for each situation.

Is there a way on connection to use a flag to GetAllAsTextIfDateTime ?
That would most certainly work because I can then use my other methods to
convert the string date time to its correct format after reading from the
database.  As it is now, it cannot be extracted from the db without
exception or incorrect formatting.

Thank you

[sqlite] System.Data.Sqlite3.dll for OSX

[Ben Clewett]

Sorry if this has been asked many times before.

I have having great difficulty finding a copy of the .NET (mono) assembly 
System.Data.Sqlite3.dll for OS X.  None I have tried work, and the Xamarin 
Studio can?t compile the C++ needed to build my own.

Tried many libs, none of seems to work.

After getting to the end of Google, I fall on the advise of this group.  If 
anybody can recommend a place where this can be located, it would be very 
welcome.

Regards,

Ben.

[sqlite] System.Data.Sqlite3.dll for OSX

[Joe Mistachkin]

Ben Clewett wrote:
>
> I have having great difficulty finding a copy of the .NET (mono) assembly 
> System.Data.Sqlite3.dll for OS X.  None I have tried work, and the Xamarin

> Studio can't compile the C++ needed to build my own.
> 

Are you able to compile managed binaries on Windows?

https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q6

--
Joe Mistachkin

[sqlite] Suggestions for Fast Set Logic?

[Simon Slavin]

On 10 Jul 2015, at 4:14pm, Andy Rahn  wrote:

> I don't think
> there is a way I can just concatenate an extensive list of WHERE clauses
> into one monolithic SQLite query and also guarantee that SQLite will be
> able to SEARCH via indexes instead of falling back to slow SCANS.

You are engaged in premature optimization of your code.  The optimizer built 
into SQLite is extremely clever.  To take as an example

SELECT id FROM documents WHERE keyword LIKE '%,animal,%' AND rating > 3

If you have an index on the 'rating' column, then SQLite knows that it can 
satisfy the second condition very easily.  So it will use the index and make up 
its own list of rows which satisfy 'rating > 3'.  Then it will search only 
those rows for rows which satisfy the other part of the clause.

In other words, SQLite does internally what you were expecting to do yourself.

This extends to longer and more complicated combinations of ANDs and ORs in 
your WHERE clause.  Equipped with the results of ANALYZE the optimizer can 
explore each subclause, figure out how much it will narrow the search path, and 
choose the order of doing things which should yield the fastest path to the 
smallest number of rows to scan.  In a few milliseconds.

So there's no need to build the cleverness into your own code.  Instead

1) Make up a database with a convincing set of data in.  It doesn't have to be 
final, just get a lots of rows of plausible data into the tables.
2) Create some indexes which would facilitate most-used searches.
3) Run the SQL command "ANALYZE".  The result is stored in the database and 
unless the nature of the data in the tables changes, there's no need to rerun 
ANALYZE each time you add/update rows.
4) Make up your SQL commands using a standard SQL WHERE clause.

Simon.

[sqlite] Fix for non unix times DATETIME

[Joe Mistachkin]

Lee Reiber wrote:
>
> Is there a way on connection to use a flag to GetAllAsTextIfDateTime ?
>

The following might be able to help:

SQLiteConnection connection = new SQLiteConnection(
"Data Source=C:\\some\\path\\test.db;Flags=UseConnectionTypes;");

connection.AddTypeMapping("DATETIME", DbType.String, true);

Please note that you may need to add more mappings if the DateTime type
names you need are not always "DATETIME".

Also, please let us know if this works for you.

--
Joe Mistachkin

[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] Suggestions for Fast Set Logic?

[James K. Lowden]

On Thu, 9 Jul 2015 22:28:04 +0100
Simon Slavin  wrote:

> If you're going to do it properly you have a keywords column which
> contains strings like
> 
> ,
> ,animal,
> ,animal,predator,
> ,animal,predator,bird,

Why do it that way?  I would recommend a schema like Keith's, with one
keyword per row in a separate table.  

The OP mentioned that searching 250,000 rows took 2 seconds.  Because a
LIKE clause can't use an index, every query of a column such as you
suggest will require a linear scan, plus pattern-matching overhead.  A
normalized plan like Keith's permits indexing by keyword, which should
be blazing fast when testing for equality to string constants.  

--jkl

[sqlite] Regarding Result Set

[Sairam Gaddam]

Sir,
  I created a custom Result set in Resultrow case in sqlite3VdbeExec
function as below

custom_pResultSet = (Mem*) malloc(sizeof (Mem) * (custom_numElements));

and I initialized it using sqlite3VdbeMemInit function.

  for( i = 0 ; i < custom_numElements ; i++ )
  {
  sqlite3VdbeMemInit(&custom_pResultSet[i], p->db,
MEM_Null);
  }

Then I copied the first element of custom_pResultSet i.e custom_pResultSet[0]
to

custom_pResultSet[0].z = zColumnSelector;
custom_pResultSet[0].flags = MEM_Str;
custom_pResultSet[0].n = strlen(zColumnSelector);
custom_pResultSet[0].zMalloc = zColumnSelector;
custom_pResultSet[0].enc = SQLITE_UTF8;

I modified the result Custom Result set for select statements and then made
pMem to point to it.

pMem = p->pResultSet = custom_pResultSet;

and then NULL terminated pMem.


I included the code in paste bin * http://pastebin.com/vZuUwXzR
*


The program executed fine when executing less number of queries(less than
60) but when large number of queries(more than 60) are executed then I
found some memory leak form sqlite3VdbeMemNulTerminate function but the
result is found to be correct.

==16096== 160 bytes in 2 blocks are definitely lost in loss record 1 of 1
==16096==at 0x4C2AB80: malloc (in
/usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==16096==by 0x46DACE: sqlite3MemMalloc (sqlite3.c:17131)
==16096==by 0x46E52B: mallocWithAlarm (sqlite3.c:20794)
==16096==by 0x46E5C5: sqlite3Malloc (sqlite3.c:20825)
==16096==by 0x46EE7E: sqlite3DbMallocRaw (sqlite3.c:21191)
==16096==by 0x497721: sqlite3VdbeMemGrow (sqlite3.c:63167)
==16096==by 0x497A50: vdbeMemAddTerminator (sqlite3.c:63278)
==16096==by 0x497ADD: sqlite3VdbeMemNulTerminate (sqlite3.c:63298)
==16096==by 0x4A756D: sqlite3VdbeExec (sqlite3.c:73113)
==16096==by 0x4A00CA: sqlite3Step (sqlite3.c:69462)
==16096==by 0x4A02EC: sqlite3_step (sqlite3.c:69528)

The memory leak is increased while increasing the number of rows in the
tables.

Can anyone kindly explain why there is memory leak when executing large
number of queries ???

Or what should be done to correctly create and NULL terminate Custom result
set ??

[sqlite] Suggestions for Fast Set Logic?

[Andy Rahn]

Thanks for the suggestions.  Keith, Igor and Simon, I will definitely look
at your SQL examples and compare them to my implementation!

But, I admit I feel guilty for not adequately explaining the problem I'm
trying to solve in its entirety.

For one thing, I'm in a very performance sensitive situation.  So although
there are a lot of ways to write queries that give the right result, it's
absolutely essential that the results be gotten quickly: in other words, I
can't afford to do any full table scans.

Also, the example scenario (searching by keyword) is just an example.  What
I'm really doing is converting a user's query in our own domain specific
language (DSL -- actually some JSON) into a SQLite query (or queries).  The
user may search on keywords, or date ranges, or "likes", and any logical
combination of them (this AND this but NOT this, etc...).  I don't think
there is a way I can just concatenate an extensive list of WHERE clauses
into one monolithic SQLite query and also guarantee that SQLite will be
able to SEARCH via indexes instead of falling back to slow SCANS.

This is why I was considering building out a bunch of temporary tables of
IDs.  I know I can build each of those efficiently, and tailor the
individual "sub" queries so that they run very quickly.  The keyword
queries you provided above would be an example of one of the "subqueries".

All that's left to do is to combine those result sets.  Based on other
messages I've seen here, I am becoming convinced that a custom
implementation of result set might be the answer.

 - Andy




On Thu, Jul 9, 2015 at 5:44 PM, Keith Medcalf  wrote:

>
> create table data
> (
>  data_id integer primary key,
>  ...
> );
>
> create table keywords
> (
>  keyword_id integer primary key,
>  keyword text collate nocase unique
> );
>
>
> create table n2mKeywords
> (
>  data_id integer references data,
>  keyword_id integer references keywords,
>  primary key (data_id, keyword_id),
>  unique (keyword_id, data_id)
> ) WITHOUT ROWID;
>
>
> select * form data where id in
> (select data_id from keywords natural join n2mkeywords where keyword =
> 'onca' or keyword = 'bonca'
> INTERSECT
> select data_id from keywords natural join n2mkeyworks where keyword =
> 'chocolate');
>
> will return all the data tuples associated with the keyword ('once' or
> 'bonca') and 'chocolate'
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Andy Rahn
> > Sent: Thursday, 9 July, 2015 11:26
> > To: General
> > Subject: [sqlite] Suggestions for Fast Set Logic?
> >
> > I want to build an application that can search for specific documents
> > based
> > on a lot of criteria from a user (e.g. matching keywords, text, etc...).
> > And then to combine these results using boolean logic ... For example,
> > keyword 'animal' AND rating > 3
> >
> > Each document has an integer id.
> >
> > My strategy so far is to gather the id for each subquery into a temporary
> > table.  Then I can combine the tables using UNION or INTERSECT keywords.
> >
> > However, when I try a sample that does this on 250,000 rows, I find the
> > performance is rather slow: 2 seconds.  (I've built sqlite3 myself on
> > MacOS)
> >
> > sqlite3 --version
> > > 3.8.10.2 2015-05-20 18:17:19 2ef4f3a5b1d1d0c4338f8243d40a2452cc1f7fe4
> >
> >
> >
> > > cat config.sql intersection.sql | sqlite3
> > > 250001
> > > Run Time: real 2.000 user 1.956734 sys 0.044040
> > >
> >
> > (samples files included below.)
> >
> > I wonder if anyone has suggestions for how to improve this approach.  For
> > example, I've considered trying to build a monolithic query which does
> the
> > subqueries and UNION/INTERSECTION logic all at once, but my previous
> > experience has shown that the resulting queries are very complex and hard
> > for me to reason about if/when they get slow.  This approach lets me
> > profile each subquery easily.  It also lets me tackle sorting the results
> > as a separate step.
> >
> > Another idea I've toyed with is building a custom implementation of UNION
> > /
> > INTERSECTION for result sets that are just sets of integers.  I could do
> > this as a virtual table in sqlite.
> >
> > Thanks for your thoughts on this problem.
> >
> > Andy
> >
> >
> > config.sql contains:
> >
> > CREATE TABLE config( numAssets );
> >
> > INSERT INTO config VALUES( 25 );
> >
> > intersection.sql contains:
> >
> > CREATE TEMPORARY TABLE idset1 ( id PRIMARY KEY );
> > CREATE TEMPORARY TABLE idset2 ( id PRIMARY KEY );
> >
> > CREATE INDEX idset1_id ON idset1 ( id );
> > CREATE INDEX idset2_id ON idset2 ( id );
> >
> > BEGIN TRANSACTION;
> >
> > INSERT INTO idset1
> > SELECT id FROM ( WITH RECURSIVE
> >   cnt( id ) AS (
> >   VALUES( 0 ) UNION ALL
> >   SELECT id+1 FROM cnt WHERE id < ( SELECT MAX( numAssets ) FROM
> > config
> > ))
> > select * from cnt ), config;
> >
> > COMMIT 

[sqlite] Suggestions for Fast Set Logic?

[Scott Robison]

While I am opposed to premature optimization as well, this is not clearly a
case of that. As described, the user will be able to create arbitrarily
complex queries. Since it is impossible to know in advance what indexes
might be required, breaking it down to individual sub queries with simple
where clauses that can be virtually guaranteed to use a simple index can
provide fast enough queries for the general case, since the SQL will be
generated dynamically from complicated user input.
On Jul 10, 2015 9:36 AM, "Simon Slavin"  wrote:

>
> On 10 Jul 2015, at 4:14pm, Andy Rahn  wrote:
>
> > I don't think
> > there is a way I can just concatenate an extensive list of WHERE clauses
> > into one monolithic SQLite query and also guarantee that SQLite will be
> > able to SEARCH via indexes instead of falling back to slow SCANS.
>
> You are engaged in premature optimization of your code.  The optimizer
> built into SQLite is extremely clever.  To take as an example
>
> SELECT id FROM documents WHERE keyword LIKE '%,animal,%' AND rating > 3
>
> If you have an index on the 'rating' column, then SQLite knows that it can
> satisfy the second condition very easily.  So it will use the index and
> make up its own list of rows which satisfy 'rating > 3'.  Then it will
> search only those rows for rows which satisfy the other part of the clause.
>
> In other words, SQLite does internally what you were expecting to do
> yourself.
>
> This extends to longer and more complicated combinations of ANDs and ORs
> in your WHERE clause.  Equipped with the results of ANALYZE the optimizer
> can explore each subclause, figure out how much it will narrow the search
> path, and choose the order of doing things which should yield the fastest
> path to the smallest number of rows to scan.  In a few milliseconds.
>
> So there's no need to build the cleverness into your own code.  Instead
>
> 1) Make up a database with a convincing set of data in.  It doesn't have
> to be final, just get a lots of rows of plausible data into the tables.
> 2) Create some indexes which would facilitate most-used searches.
> 3) Run the SQL command "ANALYZE".  The result is stored in the database
> and unless the nature of the data in the tables changes, there's no need to
> rerun ANALYZE each time you add/update rows.
> 4) Make up your SQL commands using a standard SQL WHERE clause.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>