Re: [sqlite] Implementing LISTEN

[Mrs. Brisby]

The hard part is to do it portably.

I use a multicast socket that writes the change-event. Then I use a
select() call on the multicast socket in other clients- if they see the
change event, they incorporate the new data into their cache.

If you want it reasonably seamless, create triggers that evaluate a
dummy-function that calls your notify: (e.g. SELECT foo() FROM table
LIMIT 1)



On Sun, 2004-05-09 at 18:39, Chris Waters wrote:
> Hi,
> 
> I need the ability to tell if changes have been made in the database. Kind
> of like the LISTEN capability in Postgresql. In particular I need to know
> if:
> 
> * A row was inserted.
> * A row was deleted.
> * A row was modified.
> 
> I would like to implement this through a callback that an application could
> register. The callback would specify the operation that was performed, the
> affected table and maybe the rowid affected.
> 
> If the rowid is reported then for operations that affect a large number of
> rows there would be a performance penalty to call the callback per row. For
> my application this is not a problem, but perhaps when the callback is
> specified the application could indicate whether it wants per row, or per
> table notification.
> 
> I am looking for ideas on where I should hook into the source code to add
> this functionality. From what I have seen so far it looks like I could
> change the implementation of the Delete and MakeRecord VM instructions to do
> the callback.
> 
> I haven't thought through the timing implications yet. Ideally when a row is
> inserted, code in the callback could query the newly inserted row. I am not
> 100% sure at what point the row becomes visible for query.
> 
> I would welcome any insight since this is my first attempt (of many I have
> lots of features I want :-) to modify the sqlite code.
> 
> Thanks,
> 
> Chris.
> 
> 
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> 


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Sort by TIMESTAMP?

[Shawn Anderson]

On draw back to these is that they are pretty much non portable to other
RDBMS.  But they look good...

Shawn 

-Original Message-
From: Kurt Welgehausen [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 11, 2004 11:50 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Sort by TIMESTAMP?

> May try something like
>
> WHERE  julianday(DBTimeStamp) < julianday('2004/4/4') but I might be 
> wrong.
>
> Lloyd

Or, you could read the docs:

  www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

especially the section labeled 'Time Strings'.

Regards

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Sort by TIMESTAMP?

[Shawn Anderson]

I'll give it a try, thanks :) 

-Original Message-
From: Lloyd thomas [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 11, 2004 10:54 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Sort by TIMESTAMP?

May try something like

WHERE  julianday(DBTimeStamp) < julianday('2004/4/4') but I might be wrong.

Lloyd

- Original Message -
From: "Shawn Anderson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, May 11, 2004 3:33 PM
Subject: RE: [sqlite] Sort by TIMESTAMP?


> You right, I should have tried it :)
> 
> A couple of other things that I am noticing;
> - it seems that a col created with TIMESTAMP, is dropping all time
> information and only storing date values
> - I cannot narrow a select down using a TIMESTAMP col  for example,
> neither of the follow have any effect:
> WHERE DBTimeStamp < '4/4/2004'  or
> WHERE DBTimeStamp < '2004/4/4'  
> 
> I am trying to do something that is not possible with SQLite?
> 
> Thanks
> Shawn
> 
> -Original Message-
> From: Will Leshner [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 11, 2004 10:23 AM
> To: Forum SQLite
> Subject: Re: [sqlite] Sort by TIMESTAMP?
> 
> 
> On May 11, 2004, at 7:17 AM, Shawn Anderson wrote:
> 
> > Is it possible to do an ORDER BY on a col marked as TIMESTAMP and have 
> > the order come back sorted correctly?
> >
> 
> Sure. Try it out and see what happens.
> 
> 
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> 
> 
> 
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> 
> 



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Simultaneosly using sqlite on windows

[D. Richard Hipp]

Bronislav Klučka wrote:
I've created application using SQLite as database (Windows application). The
database is on remote server and application is accesing the database
through file system (e.g. user connect server as P:\ disc and uses the path
p:\databases\database.sdb). More users are accessing the database on the
same time. But database became corrupted nad PRAGMA integrity_check returns
some mistakes (pasted below). 
I am told that there are bugs in file locking for network files in many
version of windows.  If those reports are true and file locking under
windows does not always work correctly, then it might be possible for
two or more programs to attempt to write the database at the same time,
resulting in corruption.
You should consider moving to a client/server database engine if
you are storing a single database on a windows network.  There are
lots of good client/server databases out there.  If you really want
to use SQLite, then consider putting a thin server wrapper around
it and using it that way.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Simultaneosly using sqlite on windows

[Bronislav Klučka]

Hi,
I've created application using SQLite as database (Windows application). The
database is on remote server and application is accesing the database
through file system (e.g. user connect server as P:\ disc and uses the path
p:\databases\database.sdb). More users are accessing the database on the
same time. But database became corrupted nad PRAGMA integrity_check returns
some mistakes (pasted below). I'm using transactions for inserting records.
All inserts are done, but the datas are corruped. Does anybody dealde with
same problem? where the problem? what's the solution? How can be database
reired?


This is result of integrity check? does anybody know, what does it mean?


*** in database main ***
On page 3970 cell 0: 1 pages missing from overflow list
On page 4736 cell 1: 1 pages missing from overflow list
On page 5301 cell 1: 2nd reference to page 5196
On page 5532 cell 1: 2nd reference to page 4982
Page 2984 is never used
Page 5032 is never used
Page 5477 is never used
Page 5478 is never used
Page 5539 is never used


Brona


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] ORDER BY terms must be non-integer constants

[Christian Smith]

On Tue, 11 May 2004, Mitchell Vincent wrote:

>This is a re-post as I didn't get any comments on what that error
>message really means..
>
>My view :
>
>CREATE view product_detail as SELECT * FROM products as p,categories as
>c WHERE c.category_id = p.category_id;
>
>When I do this query (which happens at a certain point in one of my
>applications) I get an error "ORDER BY terms must be non-integer
>constants"..
>
>Offending Query :
>
>SELECT * FROM product_detail WHERE lower("p.product_name")  LIKE
>lower('%')  ORDER BY "p.product_name" ASC

At a guess, I think it is that you're trying to access the internal
workings of the view.

Views are meant to be opaque, so p.product_name means nothing as p is not
defined in this context.

Select explicit columns into the view, so you know exactly what the
columns are called, and use the view column names, not the table column
names.

As an example, I have a database with packages and files, joined on the
package name:
create table files (
  file text primary key,
  package text,
  md5 text
);
create table packages (
  package text primary key,
  version text
);

The following view:
create view files_by_package as select * from files as f, packages as p
where f.package = p.package;

gives me all the package details of each file. But the actual column names
for the view are:
sqlite> .headers on
sqlite> select * from files_by_package limit 1;
file|package|md5|package_1|version
bin/nco_run|common-scripts|d89dc9d4662f003eb1a988ad23f8258b|common-scripts|7.0

So package clashes and is resolved by sqlite by appending _1 to the second
instance of column package. No mention of f or p from the view definition.

A better view would be:
create view files_by_package as
select f.file as file, f.md5 as md5, p.package as package, p.version as version
from files as f, packages as p
where f.package = p.package;

This makes it explicit what mu columns are.

>
>It works fine and doesn't complain in 2.8.11 but in 2.8.13 it gives that
>error..

Probably an accident of implementation.

>
>Thanks!
>
>-- Mitchell Vincent
>

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Sort by TIMESTAMP?

[Kurt Welgehausen]

> May try something like
>
> WHERE  julianday(DBTimeStamp) < julianday('2004/4/4')
> but I might be wrong.
>
> Lloyd

Or, you could read the docs:

  www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

especially the section labeled 'Time Strings'.

Regards

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] testing for existence of an entity

[Paolo Vernazza]

this is likely a common and easy answer, so my  for not being 
able to figure it out.
How do I test for the existence of a table or a view so I can do 
something like...

if  exists
then DROP 
else CREATE  ()...
what I do in such situations is

SELECT 1 FROM sqlite_master WHERE type='table' AND name ='whatever';

which selects a single "1" row or nothing. A more portable way (between 
different SQL implementations) might be

SELECT 1 FROM tablename WHERE 1 == 0

If this fails the tablename table doesn't exist, otherwise it exists.

The problem of generating a potentially existing table can obviously be 
tackled by exec'ing the CREATE TABLE statement, and catching the "table 
already exists" error in case it occurs.


What about such a script? It's a bit tricky but it should work...

*
CREATE TEMP TABLE fakeTable (exists);
INSERT INTO fakeTable (exists) VALUES (2);
CREATE TEMP TRIGGER fakeTrigger INSERT ON fakeTable
BEGIN
DROP 'tablename';
DELETE FROM fakeTable WHERE exists = 2;
END;
INSERT INTO fakeTable SELECT 1 FROM sqlite_master WHERE type='table' AND 
name ='tablename';

CREATE TEMP TRIGGER fakeTrigger2 DELETE ON fakeTable
BEGIN
CREATE 'tablename' ;
END;
DELETE FROM fakeTable WHERE exists = 2 AND count(SELECT 1 FROM 
sqlite_master WHERE type='table' AND name ='tablename') = 0;

DROP trigger fakeTrigger2;
DROP trigger fakeTrigger;
DROP table fakeTable;
*
bye,
Paolo  



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]