Re: [sqlite] [sqlite-dev] Implementing per-value custom types

[Sohail Somani]

On 2014-11-26, 7:53 AM, RSmith wrote:
> The fact that inside of SQLite an Integer can be stored in different
> ways is simply a code/data/space optimisation for SQLite, it is
> transparent to the user and transparent to the SQL - it is in no way
> intended as a data-feature or extension of the SQL Language. If you try
> to build on top of that (or maybe not on top of it, but in the same way)
> more arbitrary sub-types, yes of course it is possible, but it may leave
> you in a World of hurt in terms of using the DB as a relational database
> system for other normal activities.

This is a documented feature of SQLite and shouldn't be considered an
optimisation or a transparent feature. I've depended on this feature to
implement something very similar to OP where two columns together
described a value: 1 column represented the type, the other a BLOB
representing the value.

SQLite's ultimately untyped storage allowed me to index these types as
best as I could expect it to be done in a superb, efficient manner.

I'd include this feature of SQLite as a killer feature. For sure, if you
needed DB portability, you'd have to have a single type in a column
which could mean one column for each type you'd want to have. Yuck.

Here is an example of how I consumed this variant in C++:

  template
  static
  T
  getAttribute( Wt::Dbo::Session & session,
dbo::id_type   repositoryId,
String const & attrName )
  {
return boost::get(
  findAttribute(session,repositoryId,attrName)
  ->value
  );
  }

The C++ magic deserialized the two column value into value. It has
turned out to be immensely useful.

My point again is: it's a documented feature of SQLite, not an optimisation.

Sohail

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

Re: [sqlite] Serializing an object's vector or array using sqlite3 in c++

[Sohail Somani]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 2014-11-21, 12:52 AM, Thane Michael wrote:
> I've been searching for a way to serialize an object's vector using
> sqlite3 but are yet find a working solution. How do I go about
> making it happen, an example would be of great help.

If your goal is to store random objects in SQLite, I think it's a bad
idea unless you deal with them as plain BLOBs. However, if your goal
is to easily access tables in SQLite, and manage the relations between
them in a reasonably easy way, take a look at Wt::Dbo:

  http://www.webtoolkit.eu/wt/doc/tutorial/dbo/tutorial.html

It works remarkably well.

Sohail
-BEGIN PGP SIGNATURE-
Version: GnuPG/MacGPG2 v2.0.22 (Darwin)

iQEcBAEBAgAGBQJUcBzxAAoJEEuBkU/kdV4d4akIAJnB09hJbWEbn73hyuV5YyKg
9MjqGctOx2vCl4O6fyI1OmRJ6Zv/Ozs43Viwt+2ciw/y6erDH1BesdUuSl3IsU7r
d7gFfPEvfAjVm9HNcvn0zcBt38nL9PKigV4XhyWMTTWfB0cajE6sI7oOwhtEVXL6
bxTTGjX10UOE0RBLqziKXdHd6iKDaGV0eBrvXgYCVQjFokGGPO282FI3KVP1tXgJ
w97sgcpnEcspnv1m3xEe8kF2IovdDshSHf08IoxMaMVn0+nqrRlUvlxYFjYrMEWA
TzbGxiQimksFMlcfkyxQNItIabpImE6YohU23Mt8zaaqoffA0pE4rKqGx/rDqR0=
=a5mS
-END PGP SIGNATURE-

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

Re: [sqlite] Database is locked exceptions

[Sohail Somani]

On 2014-10-29, 12:13 PM, Mike McWhinney wrote:

System.Windows.Forms.Control.ControlNativeWindow.OnThreadException(Exception
  e)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
  IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&
  msg)
at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
  dwComponentID, Int32 reason, Int32 pvLoopData)
at


I'm not very knowledgeable about Windows forms but if you are writing 
the SQLite database from multiple threads, that could be the reason. The 
main reasons I've seen this happen are when something is trying to write 
to the database when something else has it open for a read.


If you are using threads, then ensure that reader threads exhaust their 
result sets. Unfortunately some third party libraries do lazy loading 
which doesn't work well with SQLite. Pseudo-code:


# guithread.pseudo
def fetchResults(query):
  while(query.hasMore())
query.fetchMore() # without this, the sqlite result is active...

# writethread.pseudo
def execQuery(query):
  query.exec() # ...which would lock this

If you are not using threads, then it is possible that having the DB 
itself on a network share is causing the problem. I seem to recall some 
issues along these lines in the docs but I don't have any personal 
experience.


Sohail

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

Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

[Sohail Somani]

On 2014-10-09, 11:09 AM, Dan Kennedy wrote:

On 10/09/2014 07:23 PM, Sohail Somani wrote:

On 2014-10-09, 7:32 AM, Dan Kennedy wrote:

Got it, thanks for the explanation. Just to make sure that I
understand you correctly, is the clause MATCH '*l0l* *h4x*' getting
translated to MATCH 'l0l* h4x*'?


Yes, that's right.

Dan.


In that case, shouldn't the test in the original post have returned
the same results for both cases?


Fair point. Fixed here: http://www.sqlite.org/src/info/49dfee7cd1c9

Dan.


Thank you kind sir (or madam, as the case may be).

Sohail

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

Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

[Sohail Somani]

On 2014-10-09, 7:32 AM, Dan Kennedy wrote:

Got it, thanks for the explanation. Just to make sure that I
understand you correctly, is the clause MATCH '*l0l* *h4x*' getting
translated to MATCH 'l0l* h4x*'?


Yes, that's right.

Dan.


In that case, shouldn't the test in the original post have returned the 
same results for both cases?


Maybe I'm misunderstanding something.

Sohail

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

Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

[Sohail Somani]

On 2014-10-07, 4:04 PM, Dan Kennedy wrote:

On 10/08/2014 01:52 AM, Sohail Somani wrote:

Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*",
though it did work as expected with the older version. I'd suggest
keeping the old behaviour unless there is a performance-based reason
not to.

On 2014-10-07, 2:49 PM, Sohail Somani wrote:

SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';

COMMIT;

BEGIN TRANSACTION;

INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');

COMMIT;

SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';


Unfortunately, this was a break with backwards compatibility in response
to this issue:

https://www.mail-archive.com/sqlite-users@sqlite.org/msg83345.html


Before:

   http://www.sqlite.org/src/info/e21bf7a2ade6373e

(version 3.8.6), it was up to the specific tokenizer being used whether
or not the special characters *, ", ( and ) were available to the query
parser. After that commit, they are stripped out first. So with the new
version, your query is now equivalent to "MATCH '101* h4x*'".

This should only affect FTS tables that use custom tokenizers (not the
default simple or porter tokenizers).


Got it, thanks for the explanation. Just to make sure that I understand 
you correctly, is the clause MATCH '*l0l* *h4x*' getting translated to 
MATCH 'l0l* h4x*'?


Sohail

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

Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

[Sohail Somani]

Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*", 
though it did work as expected with the older version. I'd suggest 
keeping the old behaviour unless there is a performance-based reason not to.


On 2014-10-07, 2:49 PM, Sohail Somani wrote:

SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';

COMMIT;

BEGIN TRANSACTION;

INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');

COMMIT;

SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';



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

[sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

[Sohail Somani]

$ ./bin/sqlite3 --version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668

$ ./bin/sqlite3 -batch < /tmp/test.sql
--- 1 ---
0
--- 2 ---
1

$ ./bin/sqlite3 --version
3.8.7 2014-09-30 19:04:41 5ce05757aac80b99c3b2141cd301809f8e28e661

/bin/sqlite3 -batch < /tmp/test.sql
--- 1 ---
0
--- 2 ---
0

The SQL is below. Perhaps someone can see if I'm doing something wrong?

BEGIN TRANSACTION;

CREATE TABLE t(
   id INTEGER PRIMARY KEY AUTOINCREMENT
   ,key TEXT NOT NULL
   ,value0 DEFAULT ''
   ,value1 DEFAULT ''
);

CREATE VIRTUAL TABLE t_fts USING FTS4 (
   content="t",tokenize=unicode61 "tokenchars=-_"
   ,key
   ,value0
   ,value1
);

CREATE TRIGGER t_bu BEFORE UPDATE ON t BEGIN
DELETE FROM t_fts WHERE docid = old.id;
END;

CREATE TRIGGER t_bd BEFORE DELETE ON t BEGIN
DELETE FROM t_fts WHERE docid = old.id;
END;

CREATE TRIGGER t_au AFTER UPDATE ON t BEGIN
   INSERT INTO t_fts(
  docid
  ,key
  ,value0
  ,value1
)
VALUES (
   new.rowid
   ,new.key
   ,new.value0
   ,new.value1
);
END;

CREATE TRIGGER t_ai AFTER INSERT ON t BEGIN
   INSERT INTO t_fts(
  docid
  ,key
  ,value0
  ,value1
)
VALUES (
   new.rowid
   ,new.key
   ,new.value0
   ,new.value1
);

END;

COMMIT;

BEGIN TRANSACTION;

SELECT '--- 1 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';

COMMIT;

BEGIN TRANSACTION;

INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');

COMMIT;

SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';

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

Re: [sqlite] Can't figure out how to report a bug

[Sohail Somani]

On 2014-09-07, 8:26 AM, Joe Mucchiello wrote:

   PmaReader *pReadr;
   SortSubtask *pLast = >aTask[pSorter->nTask-1];
   rc = vdbeSortAllocUnpacked(pLast);
   if( rc==SQLITE_OK ){
 pReadr = (PmaReader*)sqlite3DbMallocZero(db, sizeof(PmaReader));
 pSorter->pReader = pReadr;
 if (pReadr == 0) rc = SQLITE_NOMEM;
   }
   if (rc == SQLITE_OK){
 rc = vdbeIncrMergerNew(pLast, pMain, >pIncr); -- << Error line


Why isn't vdbeIncrMergerNew inside the initial if(rc == SQLITE_OK)? That 
seems more like the right thing to do.


Sohail

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

Re: [sqlite] Handling Timezones

[Sohail Somani]

On 2014-07-29, 8:23 PM, Will Fong wrote:

Hi,

On Wed, Jul 30, 2014 at 8:16 AM, Igor Tandetnik  wrote:

'localtime' and 'utc' modifiers.


Ah! I have not explained my issue properly :)  I'm very sorry about that.

I'm using SQLite as a backend to a small website and I have users in
multiple timezones. When users login, their timezone is retrieved from
the user table.

Really sorry for the confusion. Late night.


I'd suggest a view on the data that does the conversion. You can also 
use an INSTEAD OF trigger for insertion:


CREATE VIEW user_data_view AS
  SELECT *,to_user_tz(gmt_time,user_tz)
  FROM user_data_view;

When I need to care about timezones, I always store them as GMT and only 
convert them when I need to present them to the user. This makes math 
and comparisons on dates easy.


Fortunately, my applications are usually designed in a manner that there 
are very few lines of code needed to support these to/from conversions.


Sohail


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

Re: [sqlite] Data visibility problem

[Sohail Somani]

On 22/06/2014 6:33 PM, João Ramos wrote:

The issue appeared with the following scenario: - using SQLite v3.8.4.3 -
the DB is in WAL mode and running with multi-thread mode - every thread has
a read-only DB connection (using thread-local-storage) - a single DB
connection is shared between threads to write


I had a similar issue and while I haven't yet resolved it, the culprit 
is a SQL statement not being finalized somewhere, not sure where. Make 
sure you have no open SQL statements.


Sohail

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

Re: [sqlite] Any way to debug which query/connection is holding a share lock?

[Sohail Somani]

On 13/06/2014 4:38 PM, RSmith wrote:

If none of these finds you a culprit, it means your program is the only
thing left to blame. Here we need to start with logging every time you
create a connection and start a query or creating a prepared statement.
Log the handles or object IDs you get back from any call too. Compare
these to the logs when you reset or close the statements and when you
free the statements and the DB connection. Go through these with a
fine-tooth comb, make sure every resource made is linked to a release in
the correct order.


I think I'm pretty certain that my program *is* the culprit. I'd like to 
add the tracing to SQLite itself. Which functions do you suggest? I know 
you mentioned opening/closing so that would be sqlite3_open* and 
sqlite3_finalize?


Just to clarify, this is all happening within one process:

Write thread: write
Read thread: read something
Write thread: write
Write thread: write
Read thread: read something <-- this is a "special" query but I can't 
see anything special about it except that it uses FTS

Write thread: hang until app exits

Also, I tried using sqlite3_config to add logging but that didn't seem 
to do much. Time for some printfs...


Sohail

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

Re: [sqlite] Any way to debug which query/connection is holding a share lock?

[Sohail Somani]

On 13/06/2014 11:04 AM, RSmith wrote:


On 2014/06/13 15:02, Sohail Somani wrote:


My application's architecture looks roughly like this:

- A main thread which does only database reads
- A dedicated database write thread

I needed to create this because while reads are fast, there are
occasions where writes take a significant portion of time and that can
cause the main thread to lock up, which results in a sub-par user
experience.


Let me pause right here. It doesn't matter which  thread is writing to
the DB in which Journal mode, the lock is because you cannot change the
state of Data in the DB "while other things are reading it". So whether
it is the same thread or another thread doesn't matter, the moment you
start writing to a DB, the readers have to wait.

There are Pragma settings able to change this behaviour, if you use
proper journal mode and cache-sharing settings in combination with
allowing uncomitted reads, BUT before we even point you to that
literature, let me ask whether it actually matters? In other words,
let's say you have a read operation that is scanning through the table,
would it matter if some of the records are more up-to-date than others?

If this doesn't matter then you might use the said Pragmas to achieve
it. If it /DOES/ matter, then you need to rethink the update concurrency
strategy - such as writing little bits over time in stead of big chunks
etc. (This is a recurring theme on here, so lots of people here, other
than me, are quite knowledgeable on that subject, but a lot more detail
about your strategy or problem is needed).

As to the literature on the said Pragmas, kindly see these links:

http://www.sqlite.org/pragma.html#pragma_read_uncommitted
http://www.sqlite.org/isolation.html
http://www.sqlite.org/sharedcache.html

Have a great day!


Thanks for your response. I had been trying to avoid the "writing little 
bits" over time to avoid an inconsistent state in the case of a crash or 
user exit, but the inconsistency is not that big of a deal as the data 
integrity is still there and eventually, it becomes consistent. So this 
solution is not the end of the world.


I actually don't have a problem with readers or writers waiting, but the 
problem that I am having is something holds on to the read lock well 
beyond where it should. I cannot figure out who (I suspect it's a 
third-party framework) and what I'd like to do is figure out what piece 
of code is refusing to relinquish the lock regardless of the eventual 
strategy I take. Even if I ended up doing everything in the same thread, 
with piecemeal updates, this means that there is still something, 
somewhere doing the wrong thing and I think that will probably end up 
biting me in an unwelcome place.


So rather than actually solve the problem, I'm looking to find the 
source of the problem. Does that make sense?


For what it's worth, I did rewire the code to do everything in one 
thread, with piecemeal writes and it works fine, but is not a good 
solution as far as I am concerned because it is just papering over the 
actual problem.


Sohail


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

[sqlite] Any way to debug which query/connection is holding a share lock?

[Sohail Somani]

Hi,

My application's architecture looks roughly like this:

- A main thread which does only database reads
- A dedicated database write thread

I needed to create this because while reads are fast, there are 
occasions where writes take a significant portion of time and that can 
cause the main thread to lock up, which results in a sub-par user 
experience.


The problem I am having is that somewhere in the main thread, something 
is grabbing a shared lock on the SQLite DB and not letting go. the 
effect of this is that the dedicated write thread ends up waiting until 
the application exits(!) in order to do some writes that occur after this.


I've tried debugging manually, disabling various features but I haven't 
been able to pinpoint which bit actually grabs the lock. Are there any 
debugging strategies I can employ to get to the bottom of it?


My current journal_mode is DELETE but WAL has similar problems, in that 
the database writes are not seen, presumably because some reader has an 
open transaction somewhere.


Thanks for your help.

Sohail

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