Re: [sqlite] Continuous exclusive lock

> > If you _need_ exclusiveaccess all along, then start app, "begin > > exclusive", do your stuf, "commit" and exit. > > > >The 'problem' is that the application can run for the whole day. Granted. And the 'problem' is ??? > > What I don't get is you later say it's a single-user, single-app > >

Re: [sqlite] sqlite-users Digest, Vol 42, Issue 30

Pete wrote: > Thanks Igor. I assume your comment about the two queries not returning the > same results is because the LEFT JOIN query would return TableA rows with no > matching TableB rows, whereas the Scalar query would not. No - it's because the LEFT JOIN query may

Re: [sqlite] Continuous exclusive lock

2011/6/30 Jean-Christophe Deschamps > If you _need_ exclusiveaccess all along, then start app, "begin > exclusive", do your stuf, "commit" and exit. > The 'problem' is that the application can run for the whole day. > What I don't get is you later say it's a single-user,

Re: [sqlite] WHERE clause inspection

On 1 Jul 2011, at 12:06am, Simon Gornall wrote: > On 30 Jun 2011, at 14:13, Simon Slavin wrote: > >> On 30 Jun 2011, at 8:06pm, Simon Gornall wrote: >> >>> So, the goal is to maximise the number of times the 3rd-pass style can be >>> what is actually being used. At the moment, if an entity

Re: [sqlite] WHERE clause inspection

On 30 Jun 2011, at 14:13, Simon Slavin wrote: > > On 30 Jun 2011, at 8:06pm, Simon Gornall wrote: > >> So, the goal is to maximise the number of times the 3rd-pass style can be >> what is actually being used. At the moment, if an entity gets *any* write >> operation, I discard *all* entries

Re: [sqlite] WHERE clause inspection

On 30 Jun 2011, at 8:06pm, Simon Gornall wrote: > So, the goal is to maximise the number of times the 3rd-pass style can be > what is actually being used. At the moment, if an entity gets *any* write > operation, I discard *all* entries in the result-cache for that object. I think you're

Re: [sqlite] Continuous exclusive lock

Cecil, >Do I understand it correctly that after a commit the database is writeable >again for others? Yes. > In that case it is maybe better to do a: > *PRAGMA locking_mode = EXCLUSIVE; >*followed by an update of the database. As long as the application is >running, I want to be sure that

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

On Thu, Jun 30, 2011 at 9:52 PM, Tom Browder wrote: > But I think the journal file is the problem...as I understand it, it > has to be on disk, doesn't it? > Now that you mention it, that might be: http://www.sqlite.org/compile.html i interpretted SQLITE_TEMP_STORE=3 as

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

On Thu, Jun 30, 2011 at 14:44, Stephan Beal wrote: > On Thu, Jun 30, 2011 at 9:06 PM, Tom Browder wrote: ... >> Thanks, Richard (and Simon), I think I can solve my web access problem >> by giving the db file its own directory. ... > You can also try

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

On Thu, Jun 30, 2011 at 9:06 PM, Tom Browder wrote: > Thanks, Richard (and Simon), I think I can solve my web access problem > by giving the db file its own directory. > You can also try disabling the temp files (telling it to use memory instead). -- - stephan beal

Re: [sqlite] Continuous exclusive lock

2011/6/30 Simon Slavin > > On 30 Jun 2011, at 6:34pm, Cecil Westerhof wrote: > > > It is a single user application and database. > > Sorry about that, Cecil. I was remembering some of the bonehead manoeuvres > some of my former clients have pulled, then complained about. >

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

On Thu, Jun 30, 2011 at 13:42, Richard Hipp wrote: > On Thu, Jun 30, 2011 at 2:30 PM, Tom Browder wrote: ... >>  From my limited testing on my web server [temporary files] are >> written in the same directory as the db file, but I would like them to >> be

Re: [sqlite] WHERE clause inspection

On 30 Jun 2011, at 11:42, Simon Slavin wrote: > > On 30 Jun 2011, at 7:28pm, Simon Gornall wrote: > >> I don't care if an update is made to the *value* of 'otherColumnName'. I >> only care if the set-of-objects-that-would-be-returned could differ, not the >> properties of those objects. > >

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

On 30 Jun 2011, at 7:30pm, Tom Browder wrote: > I've not looked at the code yet, but is there any way, without > changing the code and recompiling, to control where the temporary > files are created? From my limited testing on my web server they are > written in the same directory as the db

Re: [sqlite] Can a sys admin control temporary file location without changing the source code?

On Thu, Jun 30, 2011 at 2:30 PM, Tom Browder wrote: > From my limited testing on my web server [temporary files] are > written in the same directory as the db file, but I would like them to > be written to a separate directory. > See http://www.sqlite.org/tempfiles.html

Re: [sqlite] WHERE clause inspection

On 30 Jun 2011, at 7:28pm, Simon Gornall wrote: > I don't care if an update is made to the *value* of 'otherColumnName'. I only > care if the set-of-objects-that-would-be-returned could differ, not the > properties of those objects. Do a SELECT group_concat(rowid) WHERE … when you do your

[sqlite] Can a sys admin control temporary file location without changing the source code?

I've seen various threads in the users' list archives about the subject of temporary file location with no definitive answer, and I've seen the use of the pragma temp_store_directory is deprecated so that solution is out. I've not looked at the code yet, but is there any way, without changing the

Re: [sqlite] WHERE clause inspection

On 30 Jun 2011, at 11:06, Igor Tandetnik wrote: > On 6/30/2011 1:43 PM, Simon Gornall wrote: >> Well, perhaps I'm missing something, but if the statement for which I'm >> caching results was something like >> >> SELECT * FROM tableName WHERE columnName = zzz; >> >> I'd only want to clear

Re: [sqlite] WHERE clause inspection

On 6/30/2011 1:43 PM, Simon Gornall wrote: > Well, perhaps I'm missing something, but if the statement for which I'm > caching results was something like > > SELECT * FROM tableName WHERE columnName = zzz; > > I'd only want to clear the results cache when statements like: > > UPDATE

Re: [sqlite] Continuous exclusive lock

On 30 Jun 2011, at 6:34pm, Cecil Westerhof wrote: > It is a single user application and database. Sorry about that, Cecil. I was remembering some of the bonehead manoeuvres some of my former clients have pulled, then complained about. Simon. ___

Re: [sqlite] WHERE clause inspection

On 30 Jun 2011, at 10:28, Igor Tandetnik wrote: > On 6/30/2011 1:15 PM, Simon Gornall wrote: >> Is it possible to get the columns operated on by a WHERE clause from the >> sqlite3_stmt once it's been prepared ? >> >> I looked at the sqlite3_set_authorizer, but it doesn't seem to have >> an

Re: [sqlite] Continuous exclusive lock

2011/6/30 Simon Slavin > > As long as the application is > > running, I want to be sure that nobody writes to the database. > > This may be sensible if the application never waits for any input. Some > sort of bulk-update application, for example, or an overnight batch

Re: [sqlite] WHERE clause inspection

On 6/30/2011 1:15 PM, Simon Gornall wrote: > Is it possible to get the columns operated on by a WHERE clause from the > sqlite3_stmt once it's been prepared ? > > I looked at the sqlite3_set_authorizer, but it doesn't seem to have > an action code for examining WHERE - the only codes that use >

Re: [sqlite] WHERE clause inspection

On Thu, Jun 30, 2011 at 1:15 PM, Simon Gornall wrote: > > I'm trying to intelligently invalidate cached results after a database > UPDATE, where the results are cached by query. Would the update_hook be helpful here? http://www.sqlite.org/c3ref/update_hook.html --

Re: [sqlite] Continuous exclusive lock

On 30 Jun 2011, at 6:16pm, Cecil Westerhof wrote: > As long as the application is > running, I want to be sure that nobody writes to the database. This may be sensible if the application never waits for any input. Some sort of bulk-update application, for example, or an overnight batch run.

[sqlite] WHERE clause inspection

Hi there, Is it possible to get the columns operated on by a WHERE clause from the sqlite3_stmt once it's been prepared ? I'm trying to intelligently invalidate cached results after a database UPDATE, where the results are cached by query. One of the ways the query can be instantiated is

Re: [sqlite] Continuous exclusive lock

2011/6/30 Jean-Christophe Deschamps > >Is good enough for me. My only problem is that between reading the > >data and > >writing the changes, I want to be sure that no one has changed the data. > >For me that is enough. > > For this, a simple "Begin immediate;" ...

Re: [sqlite] sqlite-users Digest, Vol 42, Issue 30

Thanks Igor. I assume your comment about the two queries not returning the same results is because the LEFT JOIN query would return TableA rows with no matching TableB rows, whereas the Scalar query would not. How could I change the scalar query to emulate the LEFT JOIN query? Pete

Re: [sqlite] Continuous exclusive lock

Cecil, >Is good enough for me. My only problem is that between reading the >data and >writing the changes, I want to be sure that no one has changed the data. >For me that is enough. For this, a simple "Begin immediate;" ... "Commit;" embrassing you read-modify-write block will do. Doesn't

Re: [sqlite] Continuous exclusive lock

2011/6/30 Richard Hipp > > > I am writing a desktop application in which I want to have exclusive > > rights. > > > In this way I do not need to check if the data has changed when the > user > > of > > > my program wants to change records. Is this possible? > > > > You can open

Re: [sqlite] Defining a relationship as unique

On 30/06/2011, at 11:09 PM, Black, Michael (IS) wrote: > I believe you may be right...can someone in-the-know confirm that the "create > index" below is redundant? I don't have inside knowledge, but yes, it is redundant to create an index on a primary key column. As I understand it: The

Re: [sqlite] Continuous exclusive lock

On Thu, Jun 30, 2011 at 8:59 AM, Simon Slavin wrote: > > On 30 Jun 2011, at 1:48pm, Cecil Westerhof wrote: > > > I am writing a desktop application in which I want to have exclusive > rights. > > In this way I do not need to check if the data has changed when the user > of

Re: [sqlite] Defining a relationship as unique

I believe you may be right...can someone in-the-know confirm that the "create index" below is redundant? I'm the type that likes to be explicit but perhaps that's a bad idea here. Do we end up with 2 indexes thereby slowing things down on inserts? Michael D. Black Senior Scientist NG

Re: [sqlite] Continuous exclusive lock

On 30 Jun 2011, at 1:48pm, Cecil Westerhof wrote: > I am writing a desktop application in which I want to have exclusive rights. > In this way I do not need to check if the data has changed when the user of > my program wants to change records. Is this possible? You can open a transaction as

[sqlite] Continuous exclusive lock

I am writing a desktop application in which I want to have exclusive rights. In this way I do not need to check if the data has changed when the user of my program wants to change records. Is this possible? -- Cecil Westerhof ___ sqlite-users mailing

Re: [sqlite] Defining a relationship as unique

On 06/30/11 02:31 PM, Black, Michael (IS) wrote: > sqlite> create table user(userid integer primary key autoincrement,name > varchar, login varchar); > sqlite> create unique index index1 on user(userid); Isn't userid already unique by "userid integer primary key"? /Roger

Re: [sqlite] Simple schema design help

On 30/06/2011, at 8:43 PM, Ian Hardingham wrote: > Hey Tom, many thanks for the help. You're welcome. > At times I will need to identify whether a match is a "tournament match" > or not. It seems from what you're suggesting that I should do a select > on the tournamentMembershipTable (with

Re: [sqlite] Defining a relationship as unique

Hey guys, thank you all for the help. I need to look into foreign keys. On 30/06/2011 13:31, Black, Michael (IS) wrote: > > You're getting closeif you don't use a field in a table you > don't HAVE to create it. > > Also...if you want to make your database a bit more bullet proof you > want

Re: [sqlite] Defining a relationship as unique

You're getting closeif you don't use a field in a table you don't HAVE to create it. Also...if you want to make your database a bit more bullet proof you want foreign keys to help ensure you maintain the relationship between users and tournaments (otherwise you can accidentally delete

Re: [sqlite] Defining a relationship as unique

On 30/06/2011, at 8:56 PM, Ian Hardingham wrote: > I have this table: > > tournamentParticipantTable > > id INTEGER PRIMARY KEY > user INTEGER > tournamentId INTEGER > > I'm obviously going to put an index on both user, tournamentId and > tournamentId, user Why is it obvious? It will depend

Re: [sqlite] Defining a relationship as unique

Ian Hardingham wrote: > I have this table: > > tournamentParticipantTable > > id INTEGER PRIMARY KEY > user INTEGER > tournamentId INTEGER > > I'm obviously going to put an index on both user, tournamentId and > tournamentId, user If you have one on (tournamentId, user), you

[sqlite] Defining a relationship as unique

Hey guys. I have this table: tournamentParticipantTable id INTEGER PRIMARY KEY user INTEGER tournamentId INTEGER I'm obviously going to put an index on both user, tournamentId and tournamentId, user - but as the relation is unique, I was wondering if I could in some way let SQLite know that?

Re: [sqlite] Simple schema design help

Hey Tom, many thanks for the help. At times I will need to identify whether a match is a "tournament match" or not. It seems from what you're suggesting that I should do a select on the tournamentMembershipTable (with zero results being "no") rather than having a tournamentMatch boolean in