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 the
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?
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 don't also need on
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 o
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 somet
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
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 ze
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
__
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 li
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
BE
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 Inf
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
> > my program wants to
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 built
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 a transaction as
>
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
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
-
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;" ... "Commit;" embrassing you
> r
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 with
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. B
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
--
D. Richard Hipp
d...@s
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
> co
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 run.
> But if the applica
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 acti
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.
___
sql
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 ta
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
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
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 or
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 for a list of the variou
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 file
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.
>
>
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 written to a separate directory.
...
> Se
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.
>
No problem. Better a
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
http://wanderinghorse.ne
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 disabling the temp files (telling it to use mem
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 using memory for the jo
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
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 impl
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
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 ge
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, single-app
> use. Yo
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 return more than one row f
> > 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
> >
43 matches
Mail list logo