Re: [sqlite] DB change notification hooks across process barriers

[Simon Slavin]

On 27 Nov 2012, at 5:05am, Tristan Van Berkom  wrote:

> Now what I'd like to do additionally is notify clients
> of changes without the IPC, relying only on SQLite's
> presumably lightweight IPC change notifications
> (notifications which, I'm now discovering don't
> exist...)

Right.

> If SQLite could at least notify us which row was
> added/removed/changed, then we could potentially
> propagate that change to the client directly
> at least without serializing/deserializing the
> contact data and pushing the whole thing through
> the IPC.

If it helps, the only way I've ever done this successfully is to add a log 
table to the database file and instead of calling the SQLite API, call my own 
API which adds an entry to the log table and then calls the normal SQLite API.  
To detect whether anything has changed you do

SELECT id FROM changeLog ORDER BY id DESC LIMIT 1

and see if it's the same as it was last time.  If it has you can read the new 
entries to see what was done.  Of course, if you are accessing SQLite using a 
general database driver and cannot change your code you can't do this.

The implementation I was doing this in had no access to sqlite3_update_hook() 
so I never considered it and didn't know about the thread/process problem.

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

Re: [sqlite] DB change notification hooks across process barriers

[Tristan Van Berkom]

On 11/27/2012 01:40 PM, Simon Slavin wrote:


On 27 Nov 2012, at 3:58am, Yongil Jang  wrote:


What I'd done to solve this problem is reading change counter in db file
header.


Or watch the modification date of the database file.



Right, except what I need is to know exactly which row was
added, removed or updated (and polling is of course completely
out of the question).

Actually, to give some context, currently I have SQLite
underneath an IPC already (Evolution Data Server contacts
database).

When writes are performed, they go over the IPC and are written
by the addressbook server to the underlying SQLite.

Likewise, this same IPC notifies connected clients whenever
a contact is added/removed/changed (in add/change notifications,
all contact data is propagated over the IPC).

We've proven that fetching the data directly, without going
through the IPC, improves performance dramatically
(i.e. the serialization of contact data and pushing that
data over the IPC is completely avoided if the SQLite
is accessed directly at least for read operations).

Now what I'd like to do additionally is notify clients
of changes without the IPC, relying only on SQLite's
presumably lightweight IPC change notifications
(notifications which, I'm now discovering don't
exist...)

If SQLite could at least notify us which row was
added/removed/changed, then we could potentially
propagate that change to the client directly
at least without serializing/deserializing the
contact data and pushing the whole thing through
the IPC.

At any rate, I figure I'll need to go with some
hybrid solution where clients can receive minimal
data in notifications and then fetch the data directly.

Cheers,
   -Tristan


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

Re: [sqlite] DB change notification hooks across process barriers

[Simon Slavin]

On 27 Nov 2012, at 3:58am, Yongil Jang  wrote:

> What I'd done to solve this problem is reading change counter in db file
> header.

Or watch the modification date of the database file.

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

Re: [sqlite] DB change notification hooks across process barriers

[Yongil Jang]

I also have same problem.

What I'd done to solve this problem is reading change counter in db file
header.
I just implemented a simple code that reads file header.
When WAL mode is enabled, iChange variable in WAL header is used.

But... this code cannot be used for update notify...


2012/11/27 Tristan Van Berkom 

> Hi all,
> I just wanted to verify this detail.
>
> As I read here, 
> http://www.sqlite.org/c3ref/**update_hook.html
> ,
> the function sqlite3_update_hook() can be used to watch
> for row insert/update/delete changes to a DB.
>
> Now, the documentation does not mention anything about this
> function not working for modifications which were made by
> a separate process, however grepping the web shows a few
> warnings about this function.
>
> This one is over 2 years old but very detailed:
> 
> http://www.mail-archive.com/**sqlite-users@sqlite.org/**msg53058.html
>
> Is this still true ? Or has this been fixed in the past 2 years ?
>
> What is the preferred way of receiving change notifications from
> the SQLite connection ?
>
> If it's really the case that sqlite3_update_hook() does not
> work across separate connections/processes, could that please
> be noted in the documentation ? A simple note there could save
> people valuable time in experimentation.
>
> Best Regards,
>   -Tristan
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DB change notification hooks across process barriers

[Tristan Van Berkom]

Hi all,
I just wanted to verify this detail.

As I read here, http://www.sqlite.org/c3ref/update_hook.html,
the function sqlite3_update_hook() can be used to watch
for row insert/update/delete changes to a DB.

Now, the documentation does not mention anything about this
function not working for modifications which were made by
a separate process, however grepping the web shows a few
warnings about this function.

This one is over 2 years old but very detailed:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg53058.html

Is this still true ? Or has this been fixed in the past 2 years ?

What is the preferred way of receiving change notifications from
the SQLite connection ?

If it's really the case that sqlite3_update_hook() does not
work across separate connections/processes, could that please
be noted in the documentation ? A simple note there could save
people valuable time in experimentation.

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

Re: [sqlite] light weight write barriers

[Nico Williams]

On Mon, Nov 26, 2012 at 6:05 PM, Larry Brasfield
 wrote:
> Nico Williams emitted:
>
>> You keep saying that programmers don't understand "barriers".  You've
>> provided no evidence of this.  Meanwhile memory barriers are generally
>> well understood, and every programmer I know understands that a
>> "barrier" is a synchronization primitive that says that all operations
>> of a certain type will have completed prior to the barrier returning
>> control to its caller.
>
>
> Well, since you don't know me, this does not contradict you, but ...
>
> My understanding of a memory barrier, formed from close study of the need
> for them and some implementations, is that they cause a partial ordering of
> memory operations, such that all accesses instigated before the barrier is
> created occur before all accesses instigated after the barrier is created.
> This does not mean that the caller of a barrier-creating function (or the
> executor of a barrier-creating instruction) does not get control until all
> prior accesses have been "completed".  The "caller" may well continue
> executing instructions from cache, and other execution units may not be held
> up at all unless they instigate an "after" access.
>
> I will be happy to become differently educated on this subject. (perhaps via
> some evidence ;-)

That's fair, but the effect is still indistinguishable from from what
I wrote.  (Well, I suppose one has to be careful about the possibility
of a CPU with I/O ports writes to which are not included in the
concept of a memory barrier, but we have to simplify somewhere, and
the point is that barriers are a simple enough concept that we can
program with it, and this is all the more so in filesystems, where we
don't have to concern ourselves with the nuances of many different
CPUs.  There are nuances in filesystem barriers, particularly writes
to MAP_SHARED mmap()ed regions, but barriers don't create new problems
there.)

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

Re: [sqlite] light weight write barriers

[Larry Brasfield]

Nico Williams emitted:

You keep saying that programmers don't understand "barriers".  You've
provided no evidence of this.  Meanwhile memory barriers are generally
well understood, and every programmer I know understands that a
"barrier" is a synchronization primitive that says that all operations
of a certain type will have completed prior to the barrier returning
control to its caller.


Well, since you don't know me, this does not contradict you, but ...

My understanding of a memory barrier, formed from close study of the 
need for them and some implementations, is that they cause a partial 
ordering of memory operations, such that all accesses instigated before 
the barrier is created occur before all accesses instigated after the 
barrier is created.  This does not mean that the caller of a 
barrier-creating function (or the executor of a barrier-creating 
instruction) does not get control until all prior accesses have been 
"completed".  The "caller" may well continue executing instructions from 
cache, and other execution units may not be held up at all unless they 
instigate an "after" access.


I will be happy to become differently educated on this subject. 
(perhaps via some evidence ;-)


Cheers,
--
Larry Brasfield

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

Re: [sqlite] light weight write barriers

[Nico Williams]

Vlad,

You keep saying that programmers don't understand "barriers".  You've
provided no evidence of this.  Meanwhile memory barriers are generally
well understood, and every programmer I know understands that a
"barrier" is a synchronization primitive that says that all operations
of a certain type will have completed prior to the barrier returning
control to its caller.

For some filesystems it is possible to configure fsync() to act as a
barrier: for example, ZFS can be told to perform no synchronous
operations for a given dataset, in which case fsync() devolves into a
simple barrier.  (Cue Simon to tell us that some hardware and some
OSes, and some filesystems simply cannot implement fsync(), with or
without synchronicity.)

So just give us a barrier.  Yes, I know, it's tricky to implement, but
it'd be OK to return EOPNOSUPP, and let the app do something else
(e.g., call fsync() instead, tell the user to expect instability, tell
the user to get a better system, ...).

As for implementation, it helps to have a journalled or log-structured
filesystem.  It also helps to have hardware synchronization primitives
that don't suck, but these aren't entirely necessary: ZFS, for
example, can recover [*] from N incomplete transactions[**], and still
provides fsync() as a barrier given its on-disk structure and the ZIL.
 Note that ZFS recovery from incomplete transactions should never be
necessary where the HW has proper cache flush support, but the
recovery functionality was added precisely because of lousy hardware.

[*]   At volume import time, such as at boot-time.
[**] Granted, this requires user input, but if the user didn't care it
could be made automatic.

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

Re: [sqlite] SQLite driver for Java

[Duquette, William H (318K)]

Thanks, Julian; we'll see.

I'm not married to JDBC; an SQLite API wrapper might suit me down to the
ground.  I'm not doing general SQL database stuff, but we've done quite a
lot with SQLite in the past, and I'd like to retain it in my toolkit.  In
any event I'm using Java 1.7; it's a little troubling that the Xerial
driver hasn't been kept up to date.

Will


--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."





On 11/24/12 9:37 AM, "Julian"  wrote:

>Dear William
>
>> What driver are people using to access SQLite databases from Java
>>applications?
>
>Personally I'm using the Xerial/Zentus driver, which seems available in
>several places on the net, such as
>http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC, as this uses JDBC
>rather than a SQLite api wrapper. One problem is that it hasn't been
>updated for Java 1.6, so needs extra dummy functions added - if you like
>I can send you my version. I've also got it working for Android (on
>version for pre-2.3 and one 2.3+).
>
>(My apologies that this isn't posted to the SQLite list, but I've joined
>after your message).
>
>- Julian
>
>
>
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] SQLite on x86 CE device

[Scott Nutter]

I was wondering if anyone has compiled the source code for an x86 based
CE PC?

 

Thanks,

Scott Nutter



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

Re: [sqlite] creating indexes on empty columns

[Paul Sanderson]

Sorry - generally the sorts will be on one column - but they may choose at
a later time to sort by another column. They will (but rarely - sort by two
or more columns at the same time).


On 26 November 2012 14:20, Clemens Ladisch  wrote:

> Paul Sanderson wrote:
> > My software creates a large table containing anything between about 250K
> > and Millions of rows when first run, the indexes are created immediately
> > after the table is populated and the tables do not change afterwards.
> >
> > The reason for the indexes is that the data is then reviewed by a user
> and
> > each user will have a different end result in mind and so will be looking
> > at (lots of) different subsets of the data but will want pretty much
> > instant results, i.e. a few seconds not minutes. I have no idea which
> > columns, other than a few of the main columns, that they will need to do
> > selects and sorts on.
>
> If they can doing sorts on more than one column, you need indexes for
> all combinations of those columns.  (For n columns, that's "only" n!
> combinations.  Database size might become an issue ...)
>
> > The size of the database is never an issue. Memory is unlikely to be an
> > issue. The initial processing time (data loading and indexing) is not an
> > issue.
>
> Then why not creating covering indexes for every combination of sort
> columns?  (This is essentially the same as creating lots of sorted
> copies of the table.)
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite for WinCE

[Brian Wheatley]

I have successfully compiled the source code, created a simple test 
application, and deployed to my device. When I attempt to execute the line 
below I receive an error message:

System.Data.SQLite.SQLiteConnection connection= new 
System.Data.SQLite.SQLiteConnection();

The error is as follows:

System.TypeLoadException was unhandled
  Message="Could not load type 'System.Data.SQLite.SQLiteConnection' from 
assembly 'System.Data.SQLite, Version=1.0.82.0, Culture=neutral, 
PublicKeyToken=1FDB50B1B62B4C84'."
  StackTrace:
    at System.Windows.Forms.Control.OnClick()
    at System.Windows.Forms.Button.OnClick()
    at System.Windows.Forms.ButtonBase.WnProc()
    at System.Windows.Forms.Control._InternalWnProc()
    at Microsoft.AGL.Forms.EVL.EnterMainLoop()
    at System.Windows.Forms.Application.Run()
    at ManagedFramework.Windows.Forms.Manager.Run()
    at ManagedFramework.Program.runGUI()
    at ManagedFramework.Program.Main()
  InnerException: 


I do have the System.Data.SQLite.dll assembly referenced.

Also, how can I check to if this is being compiled for .NET Compact Framework 
2.0 ?

And I've noticed that there's a "System.Transactions" reference in the 
System.Data.SQLite.Compact.2008 project but the project compiles without error. 
Should I remove the reference?

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

Re: [sqlite] creating indexes on empty columns

[Clemens Ladisch]

Paul Sanderson wrote:
> My software creates a large table containing anything between about 250K
> and Millions of rows when first run, the indexes are created immediately
> after the table is populated and the tables do not change afterwards.
>
> The reason for the indexes is that the data is then reviewed by a user and
> each user will have a different end result in mind and so will be looking
> at (lots of) different subsets of the data but will want pretty much
> instant results, i.e. a few seconds not minutes. I have no idea which
> columns, other than a few of the main columns, that they will need to do
> selects and sorts on.

If they can doing sorts on more than one column, you need indexes for
all combinations of those columns.  (For n columns, that's "only" n!
combinations.  Database size might become an issue ...)

> The size of the database is never an issue. Memory is unlikely to be an
> issue. The initial processing time (data loading and indexing) is not an
> issue.

Then why not creating covering indexes for every combination of sort
columns?  (This is essentially the same as creating lots of sorted
copies of the table.)


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

Re: [sqlite] creating indexes on empty columns

[Paul Sanderson]

Thanks for the replies - I'll try and read through them all thoroughly a
bit later.

But for now a bit of background.

My software creates a large table containing anything between about 250K
and Millions of rows when first run, the indexes are created immediately
after the table is populated and the tables do not change afterwards. This
process generally takes 10 minutes to an hour or so.

The reason for the indexes is that the data is then reviewed by a user and
each user will have a different end result in mind and so will be looking
at (lots of) different subsets of the data but will want pretty much
instant results, i.e. a few seconds not minutes. I have no idea which
columns, other than a few of the main columns, that they will need to do
selects and sorts on. It's unlikely to be all of them for a specific user
but all users are very likely to use all columns but wont really know which
until they start looking at the data and even then the desired end reult is
sometimes fluid so requirements change.

The size of the database is never an issue. Memory is unlikely to be an
issue. The initial processing time (data loading and indexing) is not an
issue.

The whole target here is to drive the end user experience and reduce any
wait time when they are at the keyboard to a minimum.

I have considered just indexing the main columns and indexing other columns
on demand (first use if you like) but can't see the benefit of that other
than saving time and disk space at stage 1, but there is no need for this




On 26 November 2012 01:52, Simon Slavin  wrote:

>
> On 25 Nov 2012, at 11:30pm, "Jay A. Kreibich"  wrote:
>
> > If you view an index as an optimization, then the idea
> >  is usually to increase overall performance, so that there is net win.
> [snip]
> >
> >  I disagree with this idea, as it implies there is a 1:1 exchange in
> >  read performance gains and write performance loss.
>
> To enlarge upon Jay's monotribe, I'll put these two together.
>  Optimization of a system sometimes doesn't mean optimizing /net/
> performance, it means speeding things up when time matters.  I once got an
> ovation from a roomfull of staff by deleting a couple of indexes, not
> adding them.  Because they spent most of their time entering data, but
> reports were generated by other people they never met and didn't care
> about. My changes reduced a two-second pause after hitting 'send' to half a
> second, massively reducing false keystrokes.
>
> The report-needing people, on the other hand didn't care.  It already took
> the system 30 minutes to generate the reports: they pressed the button and
> came back an hour later.  Changing the 30 minutes to 50 minutes didn't
> impact their workflow badly.  Out of courtesy I explained why the
> end-of-day reports were taking longer and nobody complained.  (By the way
> this sort of thing is why banks used to close to customers at 3:30pm even
> though the staff still worked until 5pm.)
>
> This was decades ago on a pre-SQL system.  One thing I like about SQL is
> that the language used to change and consult the database doesn't say
> anything about indexes (except in some non-standard extensions).  So you
> can add or delete indexes when you want without needing to change a line of
> code in any of your software and having to deploy new versions of your
> software.  [insert dramatic evil genius music here]
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] creating indexes on empty columns

[Petite Abeille]

On Nov 26, 2012, at 12:30 AM, Jay A. Kreibich  wrote:

>  "Using SQLite" (http://shop.oreilly.com/product/9780596521196.do)
>  has a very lengthy discussion of indexes and how they work,
>  specifically because it is difficult to generalize the use of
>  indexes.  One must really look at each application and each database
>  (and the data in that database!) with a solid knowledge of what an
>  index can-- or cannot-- provide in order to find places where an
>  index will make a positive difference.

All true, and well articulated. 

But, at the end of the day, SQLite only provides b-tree indexes and that is 
that.

And b-trees are not that useful and/or detrimental for a wide range of use case.

What about providing bitmap indexes?

http://en.wikipedia.org/wiki/Bitmap_index

Bitmap Index vs. B-tree Index: Which and When?

http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html

Now, that would be a breakthrough :)



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