[sqlite] update of view via trigger

[Igor Tandetnik]

On 3/5/2015 9:56 PM, Scott Robison wrote:
> Finally, my question: Is there some sort of syntax that I'm missing that
> would "simplify" my schema with a single update trigger, or is this the
> proper way to "update" individual columns of a view?

Imagine that the "clean" table has null in column X of row R, and the 
"dirty" table has the value 42 there. You run this statement:

update CombinedView SET X=42 where rowid=R;

Is it important to you that Clean.X be updated to 42, or is it OK if it 
remains null?

If the latter is OK, then you can have a single trigger doing something 
like this:

insert or replace into Clean(X, Y, Z)
select
case when new.X = ifnull(c.X, d.X) then c.X else new.X end,
case when new.Y = ifnull(c.Y, d.Y) then c.Y else new.Y end,
case when new.Z = ifnull(c.Z, d.Z) then c.Z else new.Z end
from Clean c join Dirty d on (c.rowid=d.rowid and c.rowid=new.rowid);

If you do need to translate a "no-op" update into setting a value in 
Clean, then I don't see a way around one trigger per column. I can't 
think of a way for a whole-table trigger to distinguish between a column 
not touched, and a column explicitly set to the value it already had.
-- 
Igor Tandetnik

[sqlite] db File Remains Open After Connection is Closed

[Simon Slavin]

On 5 Mar 2015, at 9:23pm, RNACS - Info  wrote:

> Does not help.  I put a breakpoint at the "SQLconn = Nothing" statement &
> checked the value of SQLconn.State. It was "Closed", so I continued one &
> still got the error.

Okay.  Worth checking.  I'm afraid I don't know your API well enough to suggest 
anything else.

Simon.

[sqlite] db File Remains Open After Connection is Closed

[Simon Slavin]

> On 5 Mar 2015, at 8:34pm, RNACS - Info  wrote:
> 
> The code I am using is:
> 
>' Close license database connection 
>If Not IsNothing(SQLconn) Then
>If SQLconn.State <> ConnectionState.Closed Then
>SQLconn.Close()
>End If
>SQLconn = Nothing
>End If

After "SQLconn.Close()", check again the state of the connection to see if it 
was successfully closed.  If not, display an error message.

Simon.

[sqlite] update of view via trigger

[Scott Robison]

I have a use case to update a view, and wanted to make sure I'm not missing
anything obvious.

The basics of my schema are that I have a table from a third party that
might be updated at any time. A lot of the data in that table is garbage
though, and I want cleaner data, so I have a parallel table that has the
exact same set of columns, but they all start off null (except for the
rowid primary key).

I have a view that generates a hybrid view of the data, joining the
parallel table with the original table, using ifnull to flatten the
information [ifnull(parallel.columnname, original.columnname) as
columnname]. This view does exact what I want.

Then I realized how handy it would be if I could update the view when I
notice things amiss. So I created a single instead of trigger that updated
all columns in the parallel table when the view was updated. And it updated
all columns, even those I didn't specify in my update statement. This is
completely rational, it just wasn't what I was expecting (having not it
through, apparently, and having never used triggers before).

Next I dropped that trigger and created 20 separate instead of triggers,
one per column. This behaves as I'd hoped the original trigger would behave.

Finally, my question: Is there some sort of syntax that I'm missing that
would "simplify" my schema with a single update trigger, or is this the
proper way to "update" individual columns of a view?

-- 
Scott Robison

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Mike Nicolino]

Hmm, yes this will work.  Though re-educating users writing their own queries 
to avoid like in this case will be an ongoing challenge. :)  But I do 
understand that generic 'like' support for Virtual Tables given the ability to 
override would be very challenging to implement generically.

MikeN


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jay Kreibich
Sent: Thursday, March 05, 2015 10:55 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite 
with 'like'


On Mar 5, 2015, at 12:30 PM, Mike Nicolino  
wrote:

> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a 
> bug with Virtual Tables.  Queries using 'like' in the where clause are not 
> getting the like clause passed to BestIndex as a query constraint.  
> Specifically:
> 


"LIKE" is not a supported virtual table index constraint.  See: 
http://www.sqlite.org/c3ref/c_index_constraint_eq.html   As such, it cannot be 
passed to a virtual table implementation, and the constraint is handled by the 
SQLite engine above the virtual table implementation.

Given that the LIKE expression is translated into an SQL function, which the 
application can override, it would be difficult for a virtual table to 
correctly implement a LIKE operation internally, while matching the exact 
functionality of the current LIKE function.

Consider a statement like this:

SELECT * FROM myVirtualTable AS vt WHERE returnThisRow_CustomFunction( 
vt.col1 );

If returnThisRow_CustomFunction() is a function returns a true or false based 
on... well, who knows what... there is no way for a virtual table 
implementation to understand the inter-workings of that function and pre-filter 
the rows.  LIKE is no different.



It should be noted that MATCH is a supported virtual table index constraint 
supported.  Along with the virtual table xFindFunction() function allows a 
virtual table to implement a table specific filter function.  This is how the 
FTS extensions implement searches.

Consider providing a virtual table specific MATCH function, over-ride use on 
your table with xFindFunction(), and rewrite statements using MATCH rather than 
LIKE.

See the FTS modules as examples.  You might want to start here: 
https://www.sqlite.org/fts3.html


 -j



--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





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

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Mike Nicolino]

> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to 
> be a bug with Virtual Tables.  Queries using 'like' in the where 
> clause are not getting the like clause passed to BestIndex as a query 
> constraint.
> Specifically:
>
>
> -  Simple query: select * from foo where name like 'a%'

The LIKE operator can be overridden by the application to mean anything the 
application wants - it is not compelled to follow standard SQL semantics.  For 
that reason, virtual tables are unable to optimize using LIKE since they have 
no way of knowing what it will do.

Works as designed.


Not communicating the like constraint to virtual tables make it impossible for 
a virtual table to do query optimization to be done in that case.  I realize 
like behavior can be overridden, but the resulting 'query' still needs to 
filter down to virtual table in some way to avoid full table scans.  Otherwise 
any queries using 'like' against a virtual table of a substantial size become 
potentially unusable depending on 'time' required for a full table scan.

Incidentally, this used to 'work' in a much older version of SQLite, though the 
semantics may not have been correct in all cases.  Version 3.7.7.1, ended up 
transforming like to of pair of constraints in the "like 'a%'" case which were 
passed to BestIndex.

Thanks,
MikeN



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Thursday, March 05, 2015 10:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite 
with 'like'

On 3/5/15, Mike Nicolino  wrote:
> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to 
> be a bug with Virtual Tables.  Queries using 'like' in the where 
> clause are not getting the like clause passed to BestIndex as a query 
> constraint.
> Specifically:
>
>
> -  Simple query: select * from foo where name like 'a%'

The LIKE operator can be overridden by the application to mean anything the 
application wants - it is not compelled to follow standard SQL semantics.  For 
that reason, virtual tables are unable to optimize using LIKE since they have 
no way of knowing what it will do.

Works as designed.

>
> -  Break inside module BestIndex
>
> -  SQLiteIndex.Inputs.Constraints has 0 length (no constraints)
>
> The above causes a full table scan of the virtual table for queries 
> using 'like', which is very bad for any virtual table of a substantial size.
> Virtual tables need to be able to use 'like' clauses to restrict 
> result set size.
>
> Before I bug this issue, is anyone aware of it and have any workaround?
> Currently, the only workaround I've got, is telling users don't use 'like'
> in their queries (obviously not a good thing).
>
> Thanks,
> MikeN
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
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] Next version of System.Data.Sqlite bug fixes only or new features?

[Mike Nicolino]

1.0.96.0 is already released and its bug fix only per the release notes:

System.Data.SQLite version 1.0.96.0 (with SQLite 3.8.8.3) is now available on 
the System.Data.SQLite website:

 https://system.data.sqlite.org/

Further information about this release can be seen at

 https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Andy (KU7T)
Sent: Thursday, March 05, 2015 10:34 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] Next version of System.Data.Sqlite bug fixes only or new 
features?

I have been reading there are a few issues with v 95, so I am waiting for the 
next. Will there be a version that has only bug fixes or new features as well? 
As many of you know, new features usually also create regressions.



Thanks

Andy



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

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Mike Nicolino]

I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a bug 
with Virtual Tables.  Queries using 'like' in the where clause are not getting 
the like clause passed to BestIndex as a query constraint.  Specifically:


-  Simple query: select * from foo where name like 'a%'

-  Break inside module BestIndex

-  SQLiteIndex.Inputs.Constraints has 0 length (no constraints)

The above causes a full table scan of the virtual table for queries using 
'like', which is very bad for any virtual table of a substantial size.  Virtual 
tables need to be able to use 'like' clauses to restrict result set size.

Before I bug this issue, is anyone aware of it and have any workaround?  
Currently, the only workaround I've got, is telling users don't use 'like' in 
their queries (obviously not a good thing).

Thanks,
MikeN

[sqlite] Bitfield in Sqlite3-Table

[R.Smith]

On 2015-03-05 04:29 PM, Oskar Schneider wrote:
> Yes i already wonder why this table creating looks so much different from the 
> other table, because i needed many loops.This is the new 
> version:db.execute("""CREATE TABLE IF NOT EXISTS TRAVELDAYS
> (TaskUID INTEGER PRIMARY KEY  AUTOINCREMENT,
> Day INT  NOT NULL,
> Value   INT  NOT NULL);""")Is it enough that TaskUID 
> is primary key or should (TaskUID, Day) be primary key for faster look up?

Depends... how will you look up the values? If searches and lookups will 
mostly be done by TaskUID+Day, then definitely create the PK over both. 
The only consideration is that tables with few columns and millions of 
rows usually carry heavy loads with larger Indices. Another option is to 
create the PK over both and declare the table WITHOUT ROWID which should 
render it less fat.

Example:

CREATE TABLE IF NOT EXISTS TRAVELDAYS (
   TaskUID INT,
   Day INT  NOT NULL,
   Value INT  NOT NULL,
   PRIMARY  KEY (TaskUID, Day)
) WITHOUT ROWID;


You don't need an autoincrement, it will be always unique. Unless you 
plan to delete entries and wishes their old TaskUIDs never to be used 
again. But if this is the case, you should consider using a foreign key 
reference to whatever table you have that keeps a list of the tasks 
(assuming there is such a thing) - or maintain your own UID fountain.

[sqlite] db File Remains Open After Connection is Closed

[RNACS - Info]

Simon,

Does not help.  I put a breakpoint at the "SQLconn = Nothing" statement &
checked the value of SQLconn.State. It was "Closed", so I continued one &
still got the error.

Ray

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Thursday, March 05, 2015 3:58 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] db File Remains Open After Connection is Closed


> On 5 Mar 2015, at 8:34pm, RNACS - Info  wrote:
> 
> The code I am using is:
> 
>' Close license database connection 
>If Not IsNothing(SQLconn) Then
>If SQLconn.State <> ConnectionState.Closed Then
>SQLconn.Close()
>End If
>SQLconn = Nothing
>End If

After "SQLconn.Close()", check again the state of the connection to see if
it was successfully closed.  If not, display an error message.

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

[sqlite] db File Remains Open After Connection is Closed

[RNACS - Info]

No.  Any commands will have finished before the user can get to the screen
that allows them to move the data files.

Ray Andrews

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of J Decker
Sent: Thursday, March 05, 2015 3:45 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] db File Remains Open After Connection is Closed

do you have any ... SqliteCommand's laying around that aren't finished?

On Thu, Mar 5, 2015 at 12:34 PM, RNACS - Info  wrote:

> I have a program written in Visual Basic .NET.
>
> One of the functions of the program is to move all of the program's 
> data, including a SQLite database to a different folder.
>
> The program is a 32-bit application using System.Data.SQLite.dll.
>
> I am having a problem that after I copy the data to the new folder, 
> the program fails when I try to delete the old folder.  The error 
> message says that the folder cannot be deleted because the SQLite 
> database file is in use by another process.
>
> The code I am using is:
>
> ' Close license database connection
> If Not IsNothing(SQLconn) Then
> If SQLconn.State <> ConnectionState.Closed Then
> SQLconn.Close()
> End If
> SQLconn = Nothing
> End If
>
> ' Move data files
>  '\ code to move files goes here
>
> ' Delete old directory
>  Directory.Delete(strOldLocation,
> FileIO.DeleteDirectoryOption.DeleteAllContents)
>
> The error occurs when I try to execute the "Directory.Delete" statement.
>
> "SQLconn" is a SQLiteConnection to my database file.  Once the 
> connection is closed & disposed of, what still has the .db file still 
> open?  How do I close the file so I can delete it?
>
> Thank you for any assistance.
>
> Ray Andrews
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] db File Remains Open After Connection is Closed

[RNACS - Info]

I have a program written in Visual Basic .NET.

One of the functions of the program is to move all of the program's data,
including a SQLite database to a different folder.

The program is a 32-bit application using System.Data.SQLite.dll.

I am having a problem that after I copy the data to the new folder, the
program fails when I try to delete the old folder.  The error message says
that the folder cannot be deleted because the SQLite database file is in use
by another process.

The code I am using is:

' Close license database connection 
If Not IsNothing(SQLconn) Then
If SQLconn.State <> ConnectionState.Closed Then
SQLconn.Close()
End If
SQLconn = Nothing
End If

' Move data files
 '\ code to move files goes here

' Delete old directory
 Directory.Delete(strOldLocation,
FileIO.DeleteDirectoryOption.DeleteAllContents)

The error occurs when I try to execute the "Directory.Delete" statement.  

"SQLconn" is a SQLiteConnection to my database file.  Once the connection is
closed & disposed of, what still has the .db file still open?  How do I
close the file so I can delete it?

Thank you for any assistance.

Ray Andrews

[sqlite] Bitfield in Sqlite3-Table

[R.Smith]

On 2015-03-05 03:00 PM, Oskar Schneider wrote:
> I just created for each day a seperate column is this worse than your 
> approach?
>   
>
>   Hick Gunter  schrieb am 8:01 Donnerstag, 5.M?rz 
> 2015:
> 
>
>   

That's around 1000 columns, right?  Beware that SQLite has a standard 
upper limit in SQL column list length of 999 columns in queries, you can 
amend this with the API.

See:
https://www.sqlite.org/limits.html

[sqlite] Bitfield in Sqlite3-Table

[Oskar Schneider]

Yes i already wonder why this table creating looks so much different from the 
other table, because i needed many loops.This is the new 
version:db.execute("""CREATE TABLE IF NOT EXISTS TRAVELDAYS
   (TaskUID INTEGER PRIMARY KEY  AUTOINCREMENT,
   Day INT  NOT NULL,
   Value   INT  NOT NULL);""")Is it enough that TaskUID is 
primary key or should (TaskUID, Day) be primary key for faster look up? 

 Hick Gunter  schrieb am 14:56 Donnerstag, 5.M?rz 2015:


 With the query the OP described he is interested only in one day at a time so 
this won't hurt.

I am anticipating a question regarding variable column names in a query though.

-Urspr?ngliche Nachricht-
Von: R.Smith [mailto:rsmith at rsweb.co.za]
Gesendet: Donnerstag, 05. M?rz 2015 14:33
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] Bitfield in Sqlite3-Table



On 2015-03-05 03:00 PM, Oskar Schneider wrote:
> I just created for each day a seperate column is this worse than your 
> approach?
>
>
>? ? ? Hick Gunter  schrieb am 8:01 Donnerstag, 5.M?rz 
>2015:
>
>
>

That's around 1000 columns, right?? Beware that SQLite has a standard upper 
limit in SQL column list length of 999 columns in queries, you can amend this 
with the API.

See:
https://www.sqlite.org/limits.html

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Richard Hipp]

On 3/5/15, Mike Nicolino  wrote:
> Incidentally, this used to 'work' in a much older version of SQLite, though
> the semantics may not have been correct in all cases.  Version 3.7.7.1,
> ended up transforming like to of pair of constraints in the "like 'a%'" case
> which were passed to BestIndex.
>

That was a bug - it could lead to incorrect answers depending on the
virtual table and its content.  The bug was fixed on 2012-03-29.
https://www.sqlite.org/src/timeline?c=2012-03-29+14:29:07

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Bitfield in Sqlite3-Table

[Hick Gunter]

With the query the OP described he is interested only in one day at a time so 
this won't hurt.

I am anticipating a question regarding variable column names in a query though.

-Urspr?ngliche Nachricht-
Von: R.Smith [mailto:rsmith at rsweb.co.za]
Gesendet: Donnerstag, 05. M?rz 2015 14:33
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] Bitfield in Sqlite3-Table



On 2015-03-05 03:00 PM, Oskar Schneider wrote:
> I just created for each day a seperate column is this worse than your 
> approach?
>
>
>   Hick Gunter  schrieb am 8:01 Donnerstag, 5.M?rz 
> 2015:
>
>
>

That's around 1000 columns, right?  Beware that SQLite has a standard upper 
limit in SQL column list length of 999 columns in queries, you can amend this 
with the API.

See:
https://www.sqlite.org/limits.html

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Bitfield in Sqlite3-Table

[Hick Gunter]

I strongly suggest you read up on database theory. An array should be 
implemented as a separate table (unless perhaps it contains only a small number 
of repeats and fields, where it may be faster). This is clearly not the case.

I assume your ?day array? is located at the end of the table definition 
(anywhere else is up to twice as bad).

I also assume there is no index on the day fields (that would be 1000 indices, 
which is a lot even if they are all partial indices).

Lets assume you want to check which tasks are planned for day 500.

SQLite will need to do a full table scan (i.e. read every record) and 
uncompress all the regular fields plus 499 day array fields before it can check 
the value of ?day500?.

With a separate table and the proposed indices, SQLite will retrieve exactly 
those entries of index todo_day which have day_no=500 and use the task_id to 
retrieve exactly the required rows from table task.

With N the number of rows, D the number of days and K the average number of 
tasks per day, your method will incur a cost of O(N) * O(M) (examine all rows 
and all day columns), whereas the table method will incur a cost of O(log N) * 
(K+1) (locate first index entry + retrieve K rows).

Which do you think will scale better? What happens when you reach the ?end of 
days? (i.e. less than 3 years after the first task is scheduled)?

Von: Oskar Schneider [mailto:oskars93 at yahoo.com]
Gesendet: Donnerstag, 05. M?rz 2015 14:00
An: Hick Gunter
Cc: 'General Discussion of SQLite Database'
Betreff: Re: AW: AW: [sqlite] Bitfield in Sqlite3-Table

I just created for each day a seperate column is this worse than your approach?

Hick Gunter mailto:hick at scigames.at>> schrieb am 8:01 
Donnerstag, 5.M?rz 2015:

Since you give no indication of the schema you are using:

Create table task (id integer primary key, ?.);
Create table todo ( task_id integer, day_no integer, foreign key (task_id) 
references task (id ) on delete cascade on update cascade  );
Create unique index todo_day on todo (day_no, task_id);
Create unique index todo_task on todo(task_id, day_no);

-- what to do on day x
Select task.* from todo join task on task.id = todo.task_id where todo.dayno = 
? [ORDER BY ?];

-- when to do task x
Select dayno from todo where task_id = ? [ORDER BY ?];

-- when to do which tasks that match
Select todo.dayno,task.* from task join todo on task.id = todo.task_id where ? 
[ORDER BY];


Von: Oskar Schneider [mailto:oskars93 at yahoo.com]
Gesendet: Mittwoch, 04. M?rz 2015 18:57
An: Hick Gunter
Betreff: Re: AW: [sqlite] Bitfield in Sqlite3-Table

With normalize you mean i should create a column for each integer-column for 
each bit?
What is the fastest approach for queries? (omit 1. since i don't want to use 
external software)

Hick Gunter mailto:hick at scigames.at>> schrieb am 17:19 
Mittwoch, 4.M?rz 2015:

In order of preference

a) use FastBit software
b) normalize your database design to eliminate the array
c) use a BLOB of 125 bytes and user defined functions to operate on them
d) use a string of 1000 characters ('0' or '1') and the SUBSTR() function

-Urspr?ngliche Nachricht-
Von: Oskar Schneider [mailto:oskars93 at yahoo.com]
Gesendet: Mittwoch, 04. M?rz 2015 16:42
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Bitfield in Sqlite3-Table

Hello,
what is the best way to implement a Bitfield of size 1000 as a column in a 
Table. I need to make queries to select all rows which have the nth bit set?The 
bitfield describes for each day in about three years if a specific task needs 
to be done.

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


___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.



___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna,Austria
Tel: +43 1 80100 - 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain 

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Richard Hipp]

On 3/5/15, Mike Nicolino  wrote:
> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a
> bug with Virtual Tables.  Queries using 'like' in the where clause are not
> getting the like clause passed to BestIndex as a query constraint.
> Specifically:
>
>
> -  Simple query: select * from foo where name like 'a%'

The LIKE operator can be overridden by the application to mean
anything the application wants - it is not compelled to follow
standard SQL semantics.  For that reason, virtual tables are unable to
optimize using LIKE since they have no way of knowing what it will do.

Works as designed.

>
> -  Break inside module BestIndex
>
> -  SQLiteIndex.Inputs.Constraints has 0 length (no constraints)
>
> The above causes a full table scan of the virtual table for queries using
> 'like', which is very bad for any virtual table of a substantial size.
> Virtual tables need to be able to use 'like' clauses to restrict result set
> size.
>
> Before I bug this issue, is anyone aware of it and have any workaround?
> Currently, the only workaround I've got, is telling users don't use 'like'
> in their queries (obviously not a good thing).
>
> Thanks,
> MikeN
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Bitfield in Sqlite3-Table

[Oskar Schneider]

I just created for each day a seperate column is this worse than your approach?


 Hick Gunter  schrieb am 8:01 Donnerstag, 5.M?rz 2015:


 #yiv6530489154 P.yiv6530489154ImprintUniqueID {MARGIN:0cm 0cm 
0pt;}#yiv6530489154 LI.yiv6530489154ImprintUniqueID {MARGIN:0cm 0cm 
0pt;}#yiv6530489154 DIV.yiv6530489154ImprintUniqueID {MARGIN:0cm 0cm 
0pt;}#yiv6530489154 TABLE.yiv6530489154ImprintUniqueIDTable {MARGIN:0cm 0cm 
0pt;}#yiv6530489154 DIV.yiv6530489154Section1 {}#yiv6530489154 #yiv6530489154 
-- _filtered #yiv6530489154 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 
4;} _filtered #yiv6530489154 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 
4;} _filtered #yiv6530489154 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 
4;} _filtered #yiv6530489154 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 
4;}#yiv6530489154 #yiv6530489154 p.yiv6530489154MsoNormal, #yiv6530489154 
li.yiv6530489154MsoNormal, #yiv6530489154 div.yiv6530489154MsoNormal 
{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv6530489154 a:link, 
#yiv6530489154 span.yiv6530489154MsoHyperlink 
{color:blue;text-decoration:underline;}#yiv6530489154 a:visited, #yiv6530489154 
span.yiv6530489154MsoHyperlinkFollowed 
{color:purple;text-decoration:underline;}#yiv6530489154 
p.yiv6530489154MsoAcetate, #yiv6530489154 li.yiv6530489154MsoAcetate, 
#yiv6530489154 div.yiv6530489154MsoAcetate 
{margin:0cm;margin-bottom:.0001pt;font-size:8.0pt;}#yiv6530489154 
span.yiv6530489154SprechblasentextZchn {}#yiv6530489154 
span.yiv6530489154E-MailFormatvorlage19 {color:#1F497D;}#yiv6530489154 
.yiv6530489154MsoChpDefault {font-size:10.0pt;} _filtered #yiv6530489154 
{margin:70.85pt 70.85pt 2.0cm 70.85pt;}#yiv6530489154 
div.yiv6530489154WordSection1 {}#yiv6530489154 Since you give no indication of 
the schema you are using:  ? Create table task (id integer primary key, ?.); 
Create table todo ( task_id integer, day_no integer, foreign key (task_id) 
references task (id ) on delete cascade on update cascade ?); Create unique 
index todo_day on todo (day_no, task_id); Create unique index todo_task on 
todo(task_id, day_no);  ? -- what to do on day x Select task.* from todo join 
task on task.id = todo.task_id where todo.dayno = ? [ORDER BY ?];  ? -- when to 
do task x Select dayno from todo where task_id = ? [ORDER BY ?];  ? -- when to 
do which tasks that match Select todo.dayno,task.* from task join todo on 
task.id = todo.task_id where ? [ORDER BY];  ?  ? Von: Oskar Schneider 
[mailto:oskars93 at yahoo.com]
Gesendet: Mittwoch, 04. M?rz 2015 18:57
An: Hick Gunter
Betreff: Re: AW: [sqlite] Bitfield in Sqlite3-Table  ? With normalize you mean 
i should create a column for each integer-column for each bit? What is the 
fastest approach for queries? (omit 1. since i don't want to use external 
software)  ? Hick Gunter  schrieb am 17:19 Mittwoch, 
4.M?rz 2015:  ? In order of preference

a) use FastBit software
b) normalize your database design to eliminate the array
c) use a BLOB of 125 bytes and user defined functions to operate on them
d) use a string of 1000 characters ('0' or '1') and the SUBSTR() function 
-Urspr?ngliche Nachricht-
Von: Oskar Schneider [mailto:oskars93 at yahoo.com]
Gesendet: Mittwoch, 04. M?rz 2015 16:42
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Bitfield in Sqlite3-Table

Hello,
what is the best way to implement a Bitfield of size 1000 as a column in a 
Table. I need to make queries to select all rows which have the nth bit set?The 
bitfield describes for each day in about three years if a specific task needs 
to be done. 
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your 
cooperation. 

  ? ?___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna,Austria
Tel: +43 1 80100 - 0
E-Mail:?hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received 

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Jay Kreibich]

On Mar 5, 2015, at 12:30 PM, Mike Nicolino  
wrote:

> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a 
> bug with Virtual Tables.  Queries using 'like' in the where clause are not 
> getting the like clause passed to BestIndex as a query constraint.  
> Specifically:
> 


?LIKE? is not a supported virtual table index constraint.  See: 
http://www.sqlite.org/c3ref/c_index_constraint_eq.html   As such, it cannot be 
passed to a virtual table implementation, and the constraint is handled by the 
SQLite engine above the virtual table implementation.

Given that the LIKE expression is translated into an SQL function, which the 
application can override, it would be difficult for a virtual table to 
correctly implement a LIKE operation internally, while matching the exact 
functionality of the current LIKE function.

Consider a statement like this:

SELECT * FROM myVirtualTable AS vt WHERE returnThisRow_CustomFunction( 
vt.col1 );

If returnThisRow_CustomFunction() is a function returns a true or false based 
on? well, who knows what? there is no way for a virtual table implementation to 
understand the inter-workings of that function and pre-filter the rows.  LIKE 
is no different.



It should be noted that MATCH is a supported virtual table index constraint 
supported.  Along with the virtual table xFindFunction() function allows a 
virtual table to implement a table specific filter function.  This is how the 
FTS extensions implement searches.

Consider providing a virtual table specific MATCH function, over-ride use on 
your table with xFindFunction(), and rewrite statements using MATCH rather than 
LIKE.

See the FTS modules as examples.  You might want to start here: 
https://www.sqlite.org/fts3.html


 -j



--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] db File Remains Open After Connection is Closed

[J Decker]

do you have any ... SqliteCommand's laying around that aren't finished?

On Thu, Mar 5, 2015 at 12:34 PM, RNACS - Info  wrote:

> I have a program written in Visual Basic .NET.
>
> One of the functions of the program is to move all of the program's data,
> including a SQLite database to a different folder.
>
> The program is a 32-bit application using System.Data.SQLite.dll.
>
> I am having a problem that after I copy the data to the new folder, the
> program fails when I try to delete the old folder.  The error message says
> that the folder cannot be deleted because the SQLite database file is in
> use
> by another process.
>
> The code I am using is:
>
> ' Close license database connection
> If Not IsNothing(SQLconn) Then
> If SQLconn.State <> ConnectionState.Closed Then
> SQLconn.Close()
> End If
> SQLconn = Nothing
> End If
>
> ' Move data files
>  '\ code to move files goes here
>
> ' Delete old directory
>  Directory.Delete(strOldLocation,
> FileIO.DeleteDirectoryOption.DeleteAllContents)
>
> The error occurs when I try to execute the "Directory.Delete" statement.
>
> "SQLconn" is a SQLiteConnection to my database file.  Once the connection
> is
> closed & disposed of, what still has the .db file still open?  How do I
> close the file so I can delete it?
>
> Thank you for any assistance.
>
> Ray Andrews
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Corrupted database

[Simon Slavin]

> On 5 Mar 2015, at 7:28am, Alexandr N?mec  wrote:
> 
> Hi Simon,
>  
>> Does SQLite automatically detect and uncorrupt these problems the next time 
>> it opens the database,
>>  or has something been done to the hardware to break in-order writing ?
>  
> No. The database can be opened successfully, but a simple "select" ends up 
> with "database disk image is malformed" in the command line shell. I can, 
> however, ".dump" the database. Using the SQLite API, the "sqlite3_prepare_v2" 
> function ends up with error code 11 (SQLITE_CORRUPT).

Okay.  A few things, all of them agreeing with your description.  The _open() 
call doesn't actually access the database files.  It just makes a note of where 
they are for later use.  So to have SQLite automatically detect and recover 
from unfinished changes it's necessary to issue at least one SQL command that 
reads something from the database file.  For example, "SELECT rowid FROM atable 
LIMIT 1".

The '.dump' command reads the table without using searching or sorting, so it 
would not spot a corrupt index.

So from all the above it's likely that the data in your tables is okay, and 
only the indexes in that file are corrupt.  So dropping and recreating your 
indexes should fix the file perfectly unless the violation of the UNIQUE 
requirement is in the primary index for a table.

>  I don't understand exactly, what you mean by "breaking in-order writing". 
> The database has been running on a Windows 2012 R2 server and was placed 
> (with a lot of other data) on a RAID-5 disk array made to one logical disk 
> for the server. AFAICT it was a pretty standard Windows 2012 configured 
> server.

That does sound like a common setup and it is subject to corruption.  'in-order 
writing' is the assurance from the computer to the programmer that if they 
issue a number of write commands to disk, they will be executed in the order 
they were given rather than something like 1, 2, 5, 4, 3.  Without in-order 
writing, it is impossible to maintain a database in a trustworthy manner since 
whatever locking or corruption-checking you do can be easily defeated.  For 
more on this see





Unfortunately, most disk subsystems do not enforce in-order writing because it 
is far faster to write changes to disk in an order given by the fastest path 
which covers all parts of the disk which need to be written.  For a normal 
computer used mainly for Web/Word/Email/Games this doesn't matter.  For a 
computer used for databases it does matter.  Many Server-class hard disks or 
hard disk drivers come with settings which enforce correct behaviour (at the 
costs of slowing down writing).  Some RAID software has settings which do the 
same.  You might find these settings near, or part of, the ones to do with 
caching.

Unfortunately I don't know enough about Windows Server to advise you.

Simon.

[sqlite] Next version of System.Data.Sqlite bug fixes only or new features?

[Andy (KU7T)]

I have been reading there are a few issues with v 95, so I am waiting for
the next. Will there be a version that has only bug fixes or new features as
well? As many of you know, new features usually also create regressions.



Thanks

Andy

[sqlite] Corrupted database

[Dave Dyer]

If you can .dump it, can you also use .read to restore it? And if so, how 
damaged does it appear to be?

My databases, with a corrupt index, couldn't be restored directly, but
the duplicate entries could be seen, cleaned up, and then the restore
succeeded.  Or (more conveniently) remove the index creation from the
.dump, restore, use queries to find and remove duplicates, then reinstate
the index.

My thinking is that the kind of corruption I've had should at least
be a different error code, and that a pragma to drop the index could
allow repair without the extreme of dumping and editing the dump file.

[sqlite] Corrupted database

[Alexandr Němec]

Hi Dave,
?
thanks for yoyr?reply.
?
>I'd be interested if you could characterize the corruption.
?
As I already replied to Simon, I can ".dump" the database but I can't run any 
''select" queries. The SQLite shell displays "database disk image is malformed" 
and using C?API, error code 11?(SQLITE_CORRUPT)?is returned.
?
Alex
?

[sqlite] Corrupted database

[Alexandr Němec]

Hi Simon,
?
> Does SQLite automatically detect and uncorrupt these problems the next time 
> it opens the database,
>?or has something been done to the hardware to break in-order writing ?
?
No. The database can be opened successfully, but a simple "select" ends up with 
"database disk image is malformed" in the command line shell. I can, however, 
".dump" the database. Using the SQLite API, the "sqlite3_prepare_v2" function 
ends up with error code 11 (SQLITE_CORRUPT).
?
I don't understand exactly, what you mean by "breaking in-order writing". The 
database has been running on a Windows 2012 R2 server and was placed (with a 
lot of other data) on a RAID-5 disk array made to one logical disk for the 
server. AFAICT it was a pretty standard Windows 2012 configured server.

Alex

[sqlite] Bitfield in Sqlite3-Table

[Hick Gunter]

Since you give no indication of the schema you are using:

Create table task (id integer primary key, ?.);
Create table todo ( task_id integer, day_no integer, foreign key (task_id) 
references task (id ) on delete cascade on update cascade  );
Create unique index todo_day on todo (day_no, task_id);
Create unique index todo_task on todo(task_id, day_no);

-- what to do on day x
Select task.* from todo join task on task.id = todo.task_id where todo.dayno = 
? [ORDER BY ?];

-- when to do task x
Select dayno from todo where task_id = ? [ORDER BY ?];

-- when to do which tasks that match
Select todo.dayno,task.* from task join todo on task.id = todo.task_id where ? 
[ORDER BY];


Von: Oskar Schneider [mailto:oskars93 at yahoo.com]
Gesendet: Mittwoch, 04. M?rz 2015 18:57
An: Hick Gunter
Betreff: Re: AW: [sqlite] Bitfield in Sqlite3-Table

With normalize you mean i should create a column for each integer-column for 
each bit?
What is the fastest approach for queries? (omit 1. since i don't want to use 
external software)

Hick Gunter mailto:hick at scigames.at>> schrieb am 17:19 
Mittwoch, 4.M?rz 2015:

In order of preference

a) use FastBit software
b) normalize your database design to eliminate the array
c) use a BLOB of 125 bytes and user defined functions to operate on them
d) use a string of 1000 characters ('0' or '1') and the SUBSTR() function

-Urspr?ngliche Nachricht-
Von: Oskar Schneider [mailto:oskars93 at yahoo.com]
Gesendet: Mittwoch, 04. M?rz 2015 16:42
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Bitfield in Sqlite3-Table

Hello,
what is the best way to implement a Bitfield of size 1000 as a column in a 
Table. I need to make queries to select all rows which have the nth bit set?The 
bitfield describes for each day in about three years if a specific task needs 
to be done.

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


___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.






___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna,Austria
Tel: +43 1 80100 - 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.