All,
I wanted to know the algorithm used by sqlite to generate the new
rowid. Assume there can be N distinct rowid's possible, now insert N
records, followed by random deletion of some records. Now what rowid
will be assigned to a new row that is added?
Regards,
Phanisekhar
> A database is in the EXCLUSIVE state if one of the processes has an
> EXCLUSIVE lock. Only *one* process at a time can hold an EX- CLUSIVE
> lock. The process holding the EXCLUSIVE lock is currently writing to
> the database file. Every other process must hold *no* lock. No other
>
Hi Daan,
You can make the columns (a, b) unique across (a, b), but not
separately unique; by that whenever you are trying to insert a row with
same (a, b) combination it will give an error and at that time you can
update the column values c and d. I hope this will solve your problem.
,month);
If it is not a real sample and you have string data you may concatenate
or something like this.
Hope this helps.
Regards, Dennis
Xeepe Phone Solution Team
http://en.xeepe.com
mailto:[EMAIL PROTECTED]
sip:[EMAIL PROTECTED]
-----Original Message-
From: B V, Phanisekhar [mailto:[E
-Original Message-
From: Simon Davies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 25, 2007 4:09 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] select COUNT (DISTINCT column1, column2) from
table?
On 25/09/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> Hi Simon,
.
COUNT (DISTINCT column1, column2) from
table?
On 25/09/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
.
.
> Assume you have a following data:
>
> matchNo, year, month
>
> 34 2007 9
>
> 27 2006 5
>
> 26 2006 5
>
> Now di
Assume I have a database.
Maintable (matchNo integer, year INTEGER, month INTEGER)
I have to find the count of distinct year, month combinations in which
matches were played.
I tried the query select COUNT (DISTINCT column1, column2) from table
but this gives an error.
I would like to know
Sorry for the spelling mistake in the subject.
Regards,
Phani
-Original Message-
From: B V, Phanisekhar
Sent: Thursday, September 13, 2007 3:53 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How is the Index stable in sqlite?
Database configuration:
Tables:
Create table maintable
Database configuration:
Tables:
Create table maintable(column1 INTEGER, column2 INTEGER, column3
INTEGER).
Indices:
Create index column1idx on maintable (column1);
How does the index table look like in sqlite?
c-r
1-3
3-4
3-6
3-7
5-1
5-8
6-2
Or
c-r...
1->3
f you use "select col1, col2, col3".
It can't know what you're going to do after the query is executed, so it
has to prepare for any possibility.
So, yes, there's a difference. Yes, selecting only the columns you
need is more efficient. No, I don't think you'll notice much of a
diffe
Assume I have a table with 40 columns. I would like to know the
difference between
Select * from table
Select column1, column2, column3 from table
While doing SQLITE3_PREPARE, will both take same amount of time?
While doing SQLITE3_STEP, will both take same amount of time?
to generate Unique ID?
On Thu, 30 Aug 2007 13:06:38 +0100, "Simon Davies"
<[EMAIL PROTECTED]> wrote:
> On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
>> Simon,
>>Yeah you can term the problem like that. Can't I use the
>> function
Unique ID?
Why do you have a unique primary key as an integer to hold your other
unique integer? Why not just use the unique integer as a primary key?
If you want to have a limit on the maximum unique ID you can store your
next to allocate and next to replace keys in another table.
B V
, 2007 4:35 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to generate Unique ID?
On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> Simon,
>
> > Without knowing your design, it looks like you wish to be able to
> > determine type from the id. This is cre
he answer for my original question.
Regards,
Phanisekhar
-Original Message-
From: Simon Davies [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 30, 2007 4:13 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to generate Unique ID?
On 30/08/2007, B V, Phanisekhar <[EMAIL PROTE
?
If you use the primary key as your unique identifier, sqlite will take
care of locating unused (deleted) ids when the maximum value is
reached (according to the documentation; I have not tried it)
Rgds,
Simon
On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> Sreedhar,
>
> I
n the
list,
it will solve your problem.
Best Regards,
A.Sreedhar.
-Original Message-----
From: B V, Phanisekhar [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 30, 2007 2:11 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to generate Unique ID?
Assume I have a table:
Create table YYY (i
Assume I have a table:
Create table YYY (id Interger PRIMARY KEY, puid Unique integer)
Id is the primary key.
Puid is an unsque interger, whose values needs to be assigned by the
user.
Currently my approach is get the maximum value of puid stored in the
table; add 1 to it and uses this
Suppose I have a table:
Create table "yearofbirth INTEGER, Name string"
What will be the query to identify how many people were born in
different years? The output should contain all the years that are
present in the table and the total count corresponding to each entry.
Eg:
1901
Assume I have an albumtable:
create table albumtable (albumid INTEGER PRIMARY KEY, album BLOB);
Now I do a query to return the entire albums in the albumtable table in
alphabetical order:
The instructions for the above query are given below:
explain select album from albumtable order
Thanks Igor & Dennis,
> As far as I can tell, LIMIT and OFFSET clauses are not specified in
any
> version of SQL standard. What is your belief based on that this query
is
> supported by SQL, and what precisely do you mean by the term "SQL" in
> this assertion?
I thought limit and offset are
>> Why Sqlite doesn't support just the use of offset in select
>> statements? As such SQL does support the use of offset only, without
>> limit. But in case of Sqlite it's not possible to use offset without
>> limit.
>select * from tableName limit -1 offset 5;
>-- or
>select * from tableName
I am using sqlite version 3.3.7
Also what I have noticed is "limit ALL" is not supported.
"limit_opt ::=",
"limit_opt ::= LIMIT expr",
"limit_opt ::= LIMIT expr OFFSET expr",
"limit_opt ::= LIMIT expr COMMA expr",
expr doesn't contain "ALL".
Why Sqlite doesn't support just the use of offset in select statements?
As such SQL does support the use of offset only, without limit. But in
case of Sqlite it's not possible to use offset without limit.
Also what is mentioned on website is different from what is there in
parser.c file. On
Assume a table
"create table if not exists Title (Id INTEGER PRIMARY KEY,
Titlename BLOB)"
"create unique index if not exists TitleIdx ON Title (Titlename)"
For which all queries index "TitleIdx" will be used?
* select Titlename from Title order by Titlename
*
> My question here is do I need to do sqlite3_finalize(pStmt); after
> every sqlite3_step() to free all memory allocated by
> sqlite3_step().Does calling finalize at end will free all memory
> allocated by all steps statements?
No you don't need to call sqlite3_finalize after every sqlite3_step.
<[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > <[EMAIL PROTECTED]> wrote:
> > > I completed my analysis of the SQLite database memory usage and I
was
> > > surprised to find that SQLite consumes so much memory. I ran my
test
> > > case (creates 31 tables) and found that SQLite
>> What exactly happens when I change the cache_size (both increase and
>> decrease size)?
> A variable is set.
It seems this term is a misnomer. What are we achieving by setting this
variable?
This is what is mentioned in the documentation of SQLITE:
PRAGMA cache_size;
PRAGMA cache_size =
sqlite3_release_memory(int n) internally calls
sqlite3pager_release_memory(int n)
A negative value of input n implies free as much as you can. Suppose if
the no of pages in cache = x. Will all the pages be freed when I call
sqlite3_release_memory with a negative argument? If not, then what
cache?
These are some of the questions for which I am yet to receive the
answers.
Regards,
Phani
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 12, 2007 3:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] PRAGMA cache_size = 0
&quo
: Tuesday, June 12, 2007 3:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] PRAGMA cache_size = 0
"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> I am yet to get answers for the following questions.
>
Weiyang Wang correctly answered your question at
http://www.mail-ar
?
Regards,
Phani
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 12, 2007 3:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] PRAGMA cache_size = 0
"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> I am yet to get answer
I am yet to get answers for the following questions.
> What happens if I set the cache_size to 0? Will I be able to do any of
> update/delete/insert/select operations?
>
> When I set the cache_size to 0, is there any freeing up of memory by
> sqlite?
Regards,
Phani
What happens if I set the cache_size to 0? Will I be able to do any of
update/delete/insert/select operations?
When I set the cache_size to 0, is there any freeing up of memory by
sqlite?
Regards,
Phani
for each row.
Regards,
Phani
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 07, 2007 5:28 PM
To: SQLite
Subject: [sqlite] Re: Re: Does sqlite3_step searches for a row in the
table / or some results buffer?
B V, Phanisekhar <[EMAIL PROTECTED
for a row in the table
/ or some results buffer?
B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> Assume a query
>
> "select * from table "
>
> Let there be 10 rows in the table
>
> Hence there will be 10 rows in the result corresponding to the above
> query.
Assume a query
"select * from table "
Let there be 10 rows in the table
Hence there will be 10 rows in the result corresponding to the above
query. We can get all these 10 rows by calling sqlite3_step 10 times.
Assume after 3 sqlite3_step calls, we insert a row into this table. Now
after
You can use sqlite3_get_table. This will internally step through each
row.
Regards,
Phani
-Original Message-
From: Dave Furey [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 23, 2007 4:52 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: How to retrieve results in a array?
Ok,
Let the database be:
Create table if not exists maintable (rowid INTEGER PRIMARY KEY, puid
INTEGER)
Create Unique Index if not exists puididx on maintable (puid)
Assume a query
"select rowid from maintable where puid = X OR puid = Y or puid = Z"
How will this query work internally?
1 Will it
of a query?
On Wed, 2007-05-16 at 11:39 +0530, B V, Phanisekhar wrote:
> Igor,
>
> Assume I have a database of the files/folders.
>
> Let it be
>
> Rowid puid
> 1 1
> 2 2
> 3 3
> 4 5
> 5 7
> 6 8
> 7 10
>
> A
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 15, 2007 8:13 PM
To: SQLite
Subject: [sqlite] Re: Re: Order of result of a query?
B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> Assume the values in the OR clause, be replaced by some subquery. Then
> in such scenarios how will I be able
e] Re: Order of result of a query?
B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> Assume the database given below
>
> mainTable (rowid INTEGER, puid INTEGER)
>
> Assume main table be
>
> Rowid Puid
> 1 2
> 2 3
> 3
Assume the database given below
mainTable (rowid INTEGER, puid INTEGER)
Assume main table be
Rowid Puid
1 2
2 3
3 4
4 6
5 7
6 8
"select rowid from
Hello all,
When I try to prepare the stmt for the query "PRAGMA
cache_size = ?" I am getting an SQLITE_ERROR.
Whereas it doesn't give error for queries like "select xxx from table
where rowed = ?" where xxx is some combination of columns.
Regards,
Phanisekhar
Assume I have two tables A and B in the databse.
sqlite3_last_insert_rowid will return the rowid of which table?
Regards,
Phani
Hi Trey,
Even I was looking for something like this. But I don't think SQL
allows you to do this. I didn't get what u said about INSERT OR REPLACE
looks good.
Regards,
Phani
-Original Message-
From: Trey Mack [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 24, 2007 9:11 PM
To:
DELETE FROM WHERE rowid = a AND refcount - 1 = 0
IF @@ROWCOUNT = 0
UPDATE SET refcount = refcount - 1 where rowid = a
Is conditional statements allowed in sqlite?
Regards,
Phani
?
B V, Phanisekhar wrote:
> Thanks for that Info.
>
> I have another question:
>
> Assume I have a table given below
> "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName
> String)"
> "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON Ti
l give even better
performance?
Regards,
Phanisekhar
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 26, 2007 3:42 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] An explanation?
"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote
Dennis,
How does the index table looks?
Assume the main table to be:
CREATE TABLE table1 (a INTEGER, b INTEGER)
Assume there is an index on column a:
CREATE INDEX index1 ON table1 (a);
Now let's suppose the entries in table1 be:
10, 91
Hi all,
Sqlite gives an error "Unable to close due to unfinalised
statements" if there are any active VM's while we try to close sqlite.
Will there be any error (for eg, memory leak and others) if
we don't check the condition and try to close the sqlite?
The
51 matches
Mail list logo
