[sqlite] Generating new rowid algo
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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] sqlite 3.x lock states
> 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 > process can acquire a lock of any kind. Does this apply to hot journal? http://www.sqlite.org/lockingv3.html In the above link the following is mentioned as the last step in dealing with hot journals. Drop the EXCLUSIVE and PENDING locks but retain the SHARED lock. Does the above sentence means it can hold EXCLUSIVE, PENDING and SHARED lock together? Regards, Phani -Original Message- From: Richard Klein [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 17, 2007 4:59 AM To: sqlite-users@sqlite.org Subject: [sqlite] sqlite 3.x lock states Hello all, I'm upgrading from sqlite 2 to 3, and am trying to get a handle on the new lock states. Here is my current understanding (by "process" I mean a process *or* thread that has opened the database): A database is in the UNLOCKED state if every process holds *no* lock of any kind on the database. A database is in the SHARED state if every process holds either no lock or a SHARED lock. Processes holding a SHARED lock may read, but not write, the database. A database is in the RESERVED state if one of the processes has a RESERVED lock. Only *one* process at a time can hold a RESERVED lock. The process holding the RESERVED lock intends to write to the database file by the time its current transaction ends. Every other process must hold either no lock or a SHARED lock. A process holding no lock can acquire a SHARED lock, but nothing stronger than that. Processes holding SHARED locks can continue to read. A database is in the PENDING state if one of the processes has a PENDING lock. Only *one* process at a time can hold a PENDING lock. The process holding the PENDING lock intends to write to the database file as soon as all other processes drop their SHARED locks. Every other process must hold either no lock or a SHARED lock. A process holding no lock cannot acquire a lock of any kind. Processes holding SHARED locks can continue to read. 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 process can acquire a lock of any kind. Do I have this right? Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Merge two rows/records
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. Regards, Phani -Original Message- From: Daan van der Sanden [mailto:[EMAIL PROTECTED] Sent: Monday, October 08, 2007 3:06 PM To: sqlite-users@sqlite.org Subject: [sqlite] Re: Merge two rows/records Thanks for the quick reply! Simon Davies writes: > > sqlite> select a,b,sum(c),sum(d) from foo group by a,b; > > > > gives you the data you are after. This could be used to populate > > another table via > > > > sqlite> insert into newFoo select a,b,sum(c),sum(d) from foo group by > > a,b; > > > > Of course, if you can get the data you want from your existing table > > using a simple query, you may not actually need a new table. At the moment I've got a database with values gathered from multiple inputs that generated "duplicate entries" for the "what should be unique" a,b combination. So I was wondering if they could be "easily" merged without creating a new table. Now I'm going to first copy all unique samples to a new database and then insert the summed values using the given query. But this solution seems a bit awkward, since I'm copying 6 million unique records to a new database and adding a small 22.000 records that are summed. So that's why I was wondering if it could be done in the same table. I hope my problem is a bit clearer now. Kind regards Daan - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] select COUNT (DISTINCT column1, column2) from table?
Thanks Dennis, Your query seems really good. Why SQL doesn't allow "select COUNT (DISTINCT column1, column2) from table"? When it allows: "select DISTINCT column1, column2 from table" and "select COUNT (DISTINCT column1) from table". Regards, Phani -Original Message- From: Dennis Povshedny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 25, 2007 4:40 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] select COUNT (DISTINCT column1, column2) from table? Hi Phani! For your sample the following query will fit: select COUNT (DISTINCT year*12+month) FROM m If you take a look at EXPLAIN select COUNT (DISTINCT year*12+month) FROM m you will see that effectiveness is almost the same than in case of EXPLAIN select COUNT (DISTINCT year) FROM m and significantly better than in SELECT COUNT(*) FROM ( SELECT COUNT(*) FROM m group by year,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:[EMAIL PROTECTED] Sent: Tuesday, September 25, 2007 2:46 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] select COUNT (DISTINCT column1, column2) from table? Hi Simon, Yeah, I thought of the query which u mentioned. But the problem is overhead is too much. I was wondering why SQL doesn't support something like: Select COUNT (DISTINCT year, month) FROM table when it supports: select COUNT (DISTINCT year) FROM table Regards, Phani No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.488 / Virus Database: 269.13.30/1029 - Release Date: 24.09.2007 19:09 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] select COUNT (DISTINCT column1, column2) from table?
Hi Simon, Yeah, I thought of the query which u mentioned. But the problem is overhead is too much. I was wondering why SQL doesn't support something like: Select COUNT (DISTINCT year, month) FROM table when it supports: select COUNT (DISTINCT year) FROM table Regards, Phani -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, . . > Here I > need to find the number of distinct combinations of year, month not the > count for a particular year, month. > > Regards, > Phani > SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> create table m( mNo integer, year integer, month integer ); sqlite> sqlite> insert into m values (1, 2006, 11 ); sqlite> insert into m values (2, 2007, 5 ); sqlite> insert into m values (3, 2007, 5 ); sqlite> insert into m values (4, 2007, 6 ); sqlite> sqlite> select count(*) from ( select * from m group by year, month ); 3 sqlite> Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] select COUNT (DISTINCT column1, column2) from table?
Hi Simon,
Assume you have a following data:
matchNo, year, month
34 2007 9
27 2006 5
26 2006 5
24 2005 4
For the above data my answer should be 3, since there are three unique
combination of year, month {(2007, 9), (2006, 5), (2005, 4)}. Here I
need to find the number of distinct combinations of year, month not the
count for a particular year, month.
Regards,
Phani
-Original Message-
From: Simon Davies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 25, 2007 3:14 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:
.
.
> Assume you have a following data:
>
> matchNo, year, month
>
> 34 2007 9
>
> 27 2006 5
>
> 26 2006 5
>
> Now distinct year, month will return
>
> 2007, 9
>
> 2006, 5
>
> Is there a way by which I can count (distinct year, month)
combinations?
> For this example answer should be 2.
>
> Regards,
>
> Phani
Hi Phani,
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite> create table m( mNo integer, year integer, month integer );
sqlite>
sqlite> insert into m values (1, 2006, 11 );
sqlite> insert into m values (2, 2007, 5 );
sqlite> insert into m values (3, 2007, 5 );
sqlite>
sqlite> select count(*), year, month from m group by year,month;
1|2006|11
2|2007|5
sqlite>
Rgds,
Simon
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] select COUNT (DISTINCT column1, column2) from table?
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 is there a simple query by which one can figure out this. Assume you have a following data: matchNo, year, month 34 2007 9 27 2006 5 26 2006 5 Now distinct year, month will return 2007, 9 2006, 5 Is there a way by which I can count (distinct year, month) combinations? For this example answer should be 2. Regards, Phani
RE: [sqlite] How is the Index table in sqlite?
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(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 3->4-6-7 5->1-8 6-2 I believe it looks like the first one. And if I am right, I would like to know the problem with the second (which seems memory efficient)? Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How is the Index stable in sqlite?
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 3->4-6-7 5->1-8 6-2 I believe it looks like the first one. And if I am right, I would like to know the problem with the second (which seems memory efficient)? Regards, Phani
RE: [sqlite] (select *) VS (select column1, column2 ...)
Thanks Tom,
I wanted to know how SQLITE works internally. Assume I am doing
"select *" and "select column1, column2" for some row. In both the cases
it will have to go through the B-Tree to reach that row. I believe this
is done when sqlite3_step is called.
Now assume I have reached the node. Now I have to retrieve column1 and
column2 of this node. I believe this is done when I call
sqlite3_column_int and its variants. In case of "select *", I can input
the column number of column1, and column2 directly to get the results
without concerning about other columns. And if I retrieve these two
columns using "select column1, column2" then I have to input column no's
1 and 2 to get theses values. I believe the time to get any particular
column (column1 or column2 ... column40) will be same. If that's the
case then as far as sqlite3_step and Sqlite3_column_int functions are
concerned both "select *" (extracting only column1 and column2) and
"select column1, column2" will take the same time to retrieve the two
columns (column1 and column2). So I shouldn't see any difference (very
minor difference) between "select *" and "select column1, column2", when
I try to extract just the two columns.
But if SQLITE uses some other buffer then this might cause problem as
copying the whole data will certainly take more time than copying the
two columns.
Please let me know if I am wrong and let me understand what exactly
happens.
Regards,
Phani
-Original Message-
From: Tom Briggs [mailto:[EMAIL PROTECTED]
Sent: Friday, August 31, 2007 6:41 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] (select *) VS (select column1, column2 ...)
In general, it's best to only include the columns you need in the
SELECT clause. And not just with SQLite - that's the best approach when
dealing with any database. SQLite is a bit more forgiving because
there's no network between the client and the database to slow things
down, but that's still a good rule to follow.
In the particular example you cited, I think that the difference
would be so minimal as to be unnoticeable. But there will definitely be
a difference - the sqlite3_prepare call will make it possible to
retrieve any of the 40 columns if you do "select *", while it will only
make available the three you name if 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
difference in terms of performance.
-T
> -Original Message-
> From: B V, Phanisekhar [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 31, 2007 7:33 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] (select *) VS (select column1, column2 ...)
>
> 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?
>
> --
> --
> ---
>
> sqlite3_prepare("Select * from table");
>
> while(1)
>
> {
>
> iRet = sqlite3_step(pStmt);
>
> if(iRet != SQLITE_ROW)
>
> {
>
> iRet = sqlite3_finalize(pStmt);
>
> break;
>
> }
>
> Sqlite3_column_int(pStmt, column1);
>
> Sqlite3_column_int(pStmt, column2);
>
> Sqlite3_column_int(pStmt, column3);
>
> }
>
> --
> --
> ---
>
> sqlite3_prepare("Select column1, column2, column3 from table");
>
> while(1)
>
> {
>
> iRet = sqlite3_step(pStmt);
>
> if(iRet != SQLITE_ROW)
>
> {
>
> iRet = sqlite3_finalize(pStmt);
>
> break;
>
> }
>
> Sqlite3_column_int(pStmt, column1);
>
> Sqlite3_column_int(pStmt, column2);
>
> Sqlite3_column_int(pStmt, column3);
>
> }
>
> --
> --
> ---
>
>
>
> If I want to extract just the 3 columns (column1, column2,
> column3), and
> use select* from table as sql query, how much impact it will have?
>
>
>
> Why I want to do this is because in some cases I need some particular
> combination in another any other combination of columns to be
> extracted?
> (It's possible for me to do this using "select * from
[sqlite] (select *) VS (select column1, column2 ...)
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?
---
sqlite3_prepare("Select * from table");
while(1)
{
iRet = sqlite3_step(pStmt);
if(iRet != SQLITE_ROW)
{
iRet = sqlite3_finalize(pStmt);
break;
}
Sqlite3_column_int(pStmt, column1);
Sqlite3_column_int(pStmt, column2);
Sqlite3_column_int(pStmt, column3);
}
---
sqlite3_prepare("Select column1, column2, column3 from table");
while(1)
{
iRet = sqlite3_step(pStmt);
if(iRet != SQLITE_ROW)
{
iRet = sqlite3_finalize(pStmt);
break;
}
Sqlite3_column_int(pStmt, column1);
Sqlite3_column_int(pStmt, column2);
Sqlite3_column_int(pStmt, column3);
}
---
If I want to extract just the 3 columns (column1, column2, column3), and
use select* from table as sql query, how much impact it will have?
Why I want to do this is because in some cases I need some particular
combination in another any other combination of columns to be extracted?
(It's possible for me to do this using "select * from table" but it's
not possible if I used "select column1, column2, column3 from table" as
I will have to frame another query)
NOTE: Please don't look at the syntax of sqlite3_prepare I just wrote
the code to show what I want to do.
Regards,
Phani
RE: [sqlite] How to generate Unique ID?
Hi Gerhard,
I am finding your code really tough to understand. Can you
please provide some comments?
Regards,
Phani
-Original Message-
From: Gerhard Haering [mailto:[EMAIL PROTECTED]
Sent: Friday, August 31, 2007 12:31 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How 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 which is assigning a unique id for INTEGER PRIMARY KEY
column
>> inside sql? If yes, how to use it?
>>
>> Regards,
>> Phani
>>
>
> Phani,
>
> With the whole of the sqlite codebase available you are free to use
> any of it as you wish ;-)
> But what you are suggesting above is not an approach that I would
> choose to get involved with. (I don't know how sqlite assigns its
> unique ids for INTEGER PRIMARY KEY columns, but I would be surprised
> if it caters for specific subranges).
I recently produced sample code that gets id ranges. I once did
something similar with Oracle SEQUENCEs, and my sample code emulates
sequences as good as it can. It's a rough sketch, and could most
probably be improved upon:
http://initd.org/tracker/pysqlite/wiki/IdRange
import sqlite3 as sqlite
import os
def init_tables(con):
for row in con.execute("select name from sqlite_master where
type='table' and name not like 'sqlite%'"):
column = None
for r in con.execute("pragma table_info (%s)" % row[0]):
if r[-1] == 0:
column = r[1]
break
con.execute("insert into %s(%s) values ('xx')" % (row[0],
column))
con.execute("delete from %s" % row[0])
def get_id_range(con, table, n):
isolation_level = con.isolation_level
start, end = None, None
try:
con.isolation_level = None
con.execute("BEGIN EXCLUSIVE")
start = con.execute("SELECT SEQ FROM SQLITE_SEQUENCE WHERE
NAME=?", (table,)).fetchone()[0]
end = start + n - 1
con.execute("UPDATE SQLITE_SEQUENCE SET SEQ=? WHERE NAME=?",
(end, table))
con.execute("COMMIT")
finally:
con.isolation_level = isolation_level
return start, end
con = sqlite.connect(":memory:")
con.execute("create table test(id integer primary key autoincrement,
name text)")
init_tables(con)
print get_id_range(con, "test", 1000)
print get_id_range(con, "test", 1000)
print get_id_range(con, "test", 1000)
con.execute("insert into test(name) values ('foo')")
con.execute("insert into test(name) values ('foo')")
con.execute("insert into test(name) values ('foo')")
print con.execute("select * from test").fetchall()
-- Gerhard
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] How to generate Unique ID?
Assume I have a table Create table T1 (id INTEGER PRIMARY KEY not null, puid UNIQUE INTEGER not null, format INTEGER not null); Now some values given below Id puidformat 1 8000123 2 9000169 3 8001178 4 8002165 5 9001180 6 8003123 What I wanted was categorize the format values. Format 123, 178, 165, 190, 118, 623, 789, and 234 likewise other values to be categorized into one group. Similarly another category of another set of different formats. Likewise many categories. Now if I want to retrieve all objects of category 1, I can't do where format = 123 or format = 178, or format = 190 ... Hence I wanted to categorize them using puid, all those that belong to category 1 will have puid's from 8000-9000, Likewise others. That's why I wanted to use some generator which will produce a unique puid. Since after reaching the max value 9000; I don't have a method to generate puid that have been deleted. Regards, Phani -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 9:00 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to generate 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, Phanisekhar wrote: > 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 value as puid for any new row that > needs to be added. The problem occurs when I reach the max value. > Meanwhile, some rows might have been deleted. In case, when I reach the > maximum value I want to reuse the puids of the deleted rows for new rows > that are to be added. Currently SQLite uses some algorithm to generate a > unique rowid (even when it reaches the limit). I want to use the same > algorithm here also. I tried to understand the algorithm but couldn't. I > need a simple way by which I can generate a unique puid without writing > the algorithm. > > > > > > Regards, > > Phani > > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Simon, Yeah you can term the problem like that. Can't I use the function which is assigning a unique id for INTEGER PRIMARY KEY column inside sql? If yes, how to use it? Regards, Phani -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 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 creating a problem. Can you not > > store the type separately from the id? > > Why is this creating a problem? > No it's not possible to store the type separately. > > I just need the answer for my original question. > > Regards, > Phanisekhar > Phani, Without the requirement to have unique ids within fixed ranges dependant on type, you could just declare a column as 'INTEGER PRIMARY KEY' and let sqlite take care of assigning your ids. As it is you have to come up with a way to generate these unique ids yourself, hence your question. This is what I have termed a problem. Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Simon, > Without knowing your design, it looks like you wish to be able to > determine type from the id. This is creating a problem. Can you not > store the type separately from the id? Why is this creating a problem? No it's not possible to store the type separately. I just need the 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 PROTECTED]> wrote: > Hi Simon, >The problem is I have rowid which is the PRIMARY Key, and > another column puid which should be unique. Now when I do vacuum rowid > changes, but puid doesn't change and I don't want puid to change. Now it > is possible to make puid itself as PRIMARY KEY, but the problem is I > assign the puid's to the objects (row entries) based upon their type. > For some type of objects I need values from x to x + 1000, for other x + > 1000 to x + 2000 and likewise. Hence I need a generator which can > generate a number from the given range (x, x + 1000) which is not > assigned. > > Regards, > Phani > Hi Phani, Implicit rowid's might be modified by VACUUM, but values in a column declared 'INTGER PRIMARY KEY' will not. Without knowing your design, it looks like you wish to be able to determine type from the id. This is creating a problem. Can you not store the type separately from the id? Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Hi Simon, The problem is I have rowid which is the PRIMARY Key, and another column puid which should be unique. Now when I do vacuum rowid changes, but puid doesn't change and I don't want puid to change. Now it is possible to make puid itself as PRIMARY KEY, but the problem is I assign the puid's to the objects (row entries) based upon their type. For some type of objects I need values from x to x + 1000, for other x + 1000 to x + 2000 and likewise. Hence I need a generator which can generate a number from the given range (x, x + 1000) which is not assigned. Regards, Phani -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 3:21 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to generate Unique ID? Hi Phani, What is the point of generating a unique integer separate from the (unique) integer primary key? 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 think u didn't read my mail properly. I want to generate unique id for > puid not for id (which is the PRIMARY KEY). Anyone knows how to generate > a undique ID for a column which is not PRIMARY KEY? Also, whenever I > reach the limit (max value), I should be able to use the values of > deleted rows. > > Regards, > Phani > > -Original Message- > From: Sreedhar.a [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 30, 2007 2:22 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] How to generate Unique ID? > > Phani, > > Read the "auto increment" in Sqlite FAQ s it is the first one in 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 (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 value as puid for any new row that needs to be > added. The problem occurs when I reach the max value. > Meanwhile, some rows might have been deleted. In case, when I reach the > maximum value I want to reuse the puids of the deleted rows for new rows > that are to be added. Currently SQLite uses some algorithm to generate a > unique rowid (even when it reaches the limit). I want to use the same > algorithm here also. I tried to understand the algorithm but couldn't. I > need a simple way by which I can generate a unique puid without writing > the > algorithm. > > > > > > Regards, > > Phani > > > > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Sreedhar, I think u didn't read my mail properly. I want to generate unique id for puid not for id (which is the PRIMARY KEY). Anyone knows how to generate a undique ID for a column which is not PRIMARY KEY? Also, whenever I reach the limit (max value), I should be able to use the values of deleted rows. Regards, Phani -Original Message- From: Sreedhar.a [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 2:22 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] How to generate Unique ID? Phani, Read the "auto increment" in Sqlite FAQ s it is the first one in 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 (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 value as puid for any new row that needs to be added. The problem occurs when I reach the max value. Meanwhile, some rows might have been deleted. In case, when I reach the maximum value I want to reuse the puids of the deleted rows for new rows that are to be added. Currently SQLite uses some algorithm to generate a unique rowid (even when it reaches the limit). I want to use the same algorithm here also. I tried to understand the algorithm but couldn't. I need a simple way by which I can generate a unique puid without writing the algorithm. Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to generate Unique ID?
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 value as puid for any new row that needs to be added. The problem occurs when I reach the max value. Meanwhile, some rows might have been deleted. In case, when I reach the maximum value I want to reuse the puids of the deleted rows for new rows that are to be added. Currently SQLite uses some algorithm to generate a unique rowid (even when it reaches the limit). I want to use the same algorithm here also. I tried to understand the algorithm but couldn't. I need a simple way by which I can generate a unique puid without writing the algorithm. Regards, Phani
[sqlite] sql query required
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 rahul 1902 deepak 1901 joy 1945 deep 1953 preeti 1945 saum The output should be 1901 2 1902 1 1945 2 1953 1 One can use GROUP BY. Regards, Phanisekhar
[sqlite] sorting of blobs
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 by album; addr opcode p1 p2 p3 -- -- -- - 0 OpenEphemeral 1 3 keyinfo(1,BINARY) 1 Goto0 26 2 Integer 0 0 3 OpenRead0 2 4 SetNumColumns 0 2 5 Rewind 0 14 6 Column 0 1 7 MakeRecord 1 0 8 Column 0 1 9 Sequence1 0 10Pull2 0 11MakeRecord 3 0 12IdxInsert 1 0 13Next0 6 14Close 0 0 15OpenPseudo 2 0 16SetNumColumns 2 1 17Sort1 24 18Integer 1 0 19Column 1 2 20Insert 2 0 21Column 2 0 22Callback1 0 23Next1 18 24Close 2 0 25Halt0 0 26Transaction 0 0 27VerifyCookie0 1 28Goto0 2 29Noop0 0 I would like to know which portion of the above code does the sorting of the blobs "album". Basically would like to know where exactly the comparison "album A > album B" is done. Regards, Phanisekhar
RE: [sqlite] Re: Re: Why "Offset" only not supported?
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 part of SQL standard language; which actually is not correct. > select * from tableName order by someField DESC > limit ROUND( (select count(*) from tableName) / 10); "limit_opt ::=", "limit_opt ::= LIMIT expr", "limit_opt ::= LIMIT expr OFFSET expr", "limit_opt ::= LIMIT expr COMMA expr", Which one of the above four will allow the above statement to pass through? I didn't find "ROUND" in parser.c file. Regards, Phanisekhar - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Why "Offset" only not supported?
>> 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 limit 5, -1 Can we have the following statement? Select * from tableName offset 3 I believe the above query is supported by SQL; but it's not supported by Sqlite. >> Also what is mentioned on website is different from what is there in >> parser.c file. On website Limit and offset is followed by integer >> whereas in parser.c it's followed by an expression. > An expression is allowed. I use a query that has a subselect in the > LIMIT clause (I need to return, say, top 10% of all the records). Can we have subselect in Limit Clause? On the website it says: "The limit is applied to the entire query not to the individual SELECT statement to which it is attached." What does the above statement mean? How will it work for the below given query? Select * from mainTable where xxx in (select yyy from table1) limit 10 Will the limit work for the subselect query also? Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Why "Offset" only not supported?
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 website Limit and offset is followed by integer whereas in parser.c it's followed by an expression. How does the "Limit 10, 30" works? Is this same as "limit 10, offset 30"? Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Why "Offset" only not supported?
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 website Limit and offset is followed by integer whereas in parser.c it's followed by an expression. How does the "Limit 10, 30" works? Is this same as "limit 10, offset 30"? Regards, Phani
[sqlite] where all indexing is used?
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 * select Titlename from Title where id in (...) order by Titlename * select Titlename from Title order by Titlename limit = 3 offset = 10 Regards, Phani
RE: [sqlite] Step Query
> 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. > *pzBlob = (unsigned char *)malloc(*pnBlob); This portion of the memory needs to be freed up by you. Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Can the memory usage of SQLite be adjusted?
<[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 consumed 545,231 bytes > > > of malloced memory before it started giving it back. > > > > > > > 100K? yes. 20K? no. > > > What is the set of commands to do this? > Ray > > There is no "command" to make SQLite use less memory. > Rather you have to use careful engineering to keep > the memory usage to a minimum. Start with this: > > PRAGMA default_cache_size=10; > > Then close your connection and reopen it and do this: > > PRAGMA cache_size; > > Verify that you got a "10" back so that the change took. > > When you compile, you will want to leave out as much stuff > as you do not need using SQLITE_OMIT_* flags. You will > want to take care in your use of SQL so that you do not > specify SQL statements that inherently require a lot of > memory. You'll probably want to make use of > sqlite3_soft_heap_limit() and sqlite3_release_memory(). > Stay far, far away from sqlite3_get_table(). And so > forth. Do we need to close the database connection and reopen it for the setting of cache_size to be effective? How do we reduce the memory by setting the cache_size? Will there be any freeing up of memory in case where cache_size is reduced? Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] PRAGMA cache_size = 0
>> 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 = Number-of-pages; Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses about 1.5K of memory. The default cache size is 2000. If you are doing UPDATEs or DELETEs that change many rows of a database and you do not mind if SQLite uses more memory, you can increase the cache size for a possible speed improvement. So without allocation extra memory how can I increase the size of cache_size? >> What happens to the data that's there in the result cache at the time >> when the instruction PRAGMA cache_size = 0 is executed? > Nothing. The aforementioned variable is set to 10 instead of the > specified value. >> Will there be any memory that will be freed up when I reduce the size of >> result cache? > No. So whats there in this cache? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_release_memory
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 makes it prevent from freeing up all the pages? What is the difference between calling sqlite3_release_memory with negative value as argument and executing the PRAGMA cache_size = 0? Regards, Phani
[sqlite] PRAGMA cache_size = 0
What exactly happens when I change the cache_size (both increase and decrease size)? What happens to the data that's there in the result cache at the time when the instruction PRAGMA cache_size = 0 is executed? Will there be any memory that will be freed up when I reduce the size of result 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 "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-archive.com/sqlite-users%40sqlite.org/msg25290.html -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] PRAGMA cache_size = 0
I tried to set the cache size to 0 (after sqlite3_open0, and then query for pragma cache_size which returns 2000 (default cache size). Why its not returning 10 (according to Weiyang Wang)? 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 answers for the following questions. > Weiyang Wang correctly answered your question at http://www.mail-archive.com/sqlite-users%40sqlite.org/msg25290.html -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] PRAGMA cache_size = 0
What exactly happens when I change the cache_size(both increase and decrease size)? What happens to the data that's there in the result cache at the time when the instruction PRAGMA cache_size = 0 is executed? Will there be any memory that will be freed up when I reduce the size of result cache? 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 answers for the following questions. > Weiyang Wang correctly answered your question at http://www.mail-archive.com/sqlite-users%40sqlite.org/msg25290.html -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] PRAGMA cache_size = 0
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
[sqlite] PRAGMA cache_size = 0
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
RE: [sqlite] Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?
Thanks Igor, >> Why it's unpredictable? Why can't the unpredictable be made predictable? Assume I update the column of a row that meets the criteria of some select stmt and I am yet to encounter that row in sqlite3_step. So the sqlite3_step on that row will return the old column value or new column value for that row? > E.g. a query with ORDER BY on a table that > doesn't have a suitable index has no choice but to retrieve all records In case of complex queries for e.g. ORDER BY (as mentioned by you), I assume I will not be able to see the updated column value. Doesn't u think there is an inconsistency? Also, is there a better function to retrieve all (rows) of the results in one call? Sqlite3_get_table does that, but it uses internally sqlite3_step, which takes lot of time. So is there a way that I can get all the result rows in one step rather then stepping 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]> wrote: > Why it's unpredictable? You are enumerating rows in some order. A new row may be inserted before or after your current position in this order. If it is inserted before, you will not see it in this enumeration. If it is inserted after, you will eventually reach it. With simple enough queries, you may guess (or explicitly specify wit ORDER BY) the order in which rows are enumerated, and can predict whether a newly inserted order will be seen. With complex queries, it may be difficult to make such a prediction. > In sqlite 3.3.8, since it allows INSERT while SELECT statement is > running, I assume that it will return an 11th row. Can you explain how > step operation works interiorly on a table? It walks a B-Tree, moving from current node to the next. > (Does it gets all the > results at one time Not most of the time, but some queries require such a temporary intermediate resultset. E.g. a query with ORDER BY on a table that doesn't have a suitable index has no choice but to retrieve all records, sort them in a temporary resultset, then enumerate that resultset. In this case, by the way, a row inserted into the table in the middle of enumeration will not be seen. > or it searches for the rows matching the criteria one after the other SQLite tries hard to do it this way, but for some queries it is not possible. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Does sqlite3_step searches for a row in the table / or some results buffer?
Igor, Why it's unpredictable? It's because of different sqlite versions or even in the same sqlite version it's unpredictable? If the latter is the case, I would like to know the internal of sqlite which makes it unpredictable. Richard, In sqlite 3.3.8, since it allows INSERT while SELECT statement is running, I assume that it will return an 11th row. Can you explain how step operation works interiorly on a table? (Does it gets all the results at one time and returns a single result during each sqlite3_step or it searches for the rows matching the criteria one after the other (i.e. in each sqlite3_step call it searches for the row from the current row onwards till the end of the table) ? Regards, Phani -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 06, 2007 5:41 PM To: SQLite Subject: [sqlite] Re: Does sqlite3_step searches 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. 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 insertion we call sqlite3_step for the instruction "select > * from table". Will this newly added row also get returned in this > case? It may or may not. In general, it's unpredictable. In this particular case, it will most likely appear at the end of enumeration, as an 11th row. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Does sqlite3_step searches for a row in the table / or some results buffer?
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 insertion we call sqlite3_step for the instruction "select * from table". Will this newly added row also get returned in this case? Sqlite3_step is supposed to execute the statement pStmt, either until a row of data is ready, the statement is completely executed or an error occurs. Regards, Phani
RE: [sqlite] Re: How to retrieve results in a array?
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, thanks for that quick response! ppcinfo -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 22, 2007 4:18 PM To: SQLite Subject: [sqlite] Re: How to retrieve results in a array? Dave Furey <[EMAIL PROTECTED]> wrote: > I've got a C routine that queries for integer data, and I'm currently > stepping through each row to fill an integer array. Is there a more > convenient way of doing this? For example, is there a special sqlite3 > call I can make that will fill up an integer array directly with me > having to step through all the returned rows? No. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] working of OR operation
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 first check the index table for puid = X and get the rowid, then followed by checking the index table for puid = Y, then puid =Z. 2 Will it first check each row puid and match it with X then Y then Z and then move to the second row in maintable? (Without referring to the index table) 3 Will it first check for puid = X in the maintable then check for puid = Y in the maintable and then for puid =z? (Without referring to the index table) 4 Any other way Regards, Phani
RE: [sqlite] Re: Re: Order of result of a query?
Kennedy,
You are using Join that's going to take time. I need the most
efficient query.
Regards,
Phanisekhar
-Original Message-
From: Dan Kennedy [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 16, 2007 12:54 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: Re: Order of result 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
>
> Assume I have a relation table showing which file is in which folder
>
> AbsPuid Puid
> 710
> 72
> 78
> 75
>
> The above relation table tells that the object 10, 2, 8, 5 is inside a
> folder object 7.
>
> Now assume the entries are added to this relation ship table in the
> sequence in which the files are created inside the folder along with
the
> sequence by which they were moved inside the folder. So the above
table
> says file 10 was first added to the sequence then 2, then 8, and at
last
> 5. Now assume I want to retrieve the rowid information in the order in
> which the objects have been added to the folder. I am using the
> following SQL query:
>
> Select rowid from maintable where puid in (select puid from
> relationtable where AbsPuid =7)
>
> The subquery will return the values {10, 2, 8, 5} but the result of
the
> main query is {2, 4, 6, 7} rather than {7, 2, 6, 4}. So what should be
> the query so that my end result is {7, 2, 6, 4}
SELECT maintable.rowid
FROM maintable, relationtable
WHERE AbsPuid=7 AND maintable.puid=relationtable.puid
ORDER BY relationtable.rowid
Or something like that anyhow.
Dan.
> I hope this will clear the question.
>
> Another doubt which I have is will the SQLite search the entire table
> with the first entry in the result set then followed by second entry
in
> the result set, and so on or will it first check the first row against
> all the values in the result test, then second row with all the values
> in the result set and so on. If the first case the result will be {7,
2,
> 6, 4} and in the latter the result will be {2, 4, 6, 7}. Hence I feel
> that sqlite follows the second case. So is that true?
>
>
> Chris,
> I don't have any specific precise display sequence to mention it
using
> another column.
>
>
> Regards,
> Phanisekhar
>
>
>
>
> -----Original Message-
> From: Igor Tandetnik [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 to maintain the order?
> > I want the order of the subquery to be preserved in the main query.
>
> Show an example. I'd like to look at the ORDER BY clause of that
> subquery (it does have one, right)? Then I'll essentially move the
ORDER
>
> BY out of the subquery and into the main query.
>
> > For e.g.: for the sub query returned values in order (2, 8, 7, 3)
>
> How does the subquery impose this particular order?
>
> Igor Tandetnik
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Re: Re: Order of result of a query?
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
Assume I have a relation table showing which file is in which folder
AbsPuid Puid
7 10
7 2
7 8
7 5
The above relation table tells that the object 10, 2, 8, 5 is inside a
folder object 7.
Now assume the entries are added to this relation ship table in the
sequence in which the files are created inside the folder along with the
sequence by which they were moved inside the folder. So the above table
says file 10 was first added to the sequence then 2, then 8, and at last
5. Now assume I want to retrieve the rowid information in the order in
which the objects have been added to the folder. I am using the
following SQL query:
Select rowid from maintable where puid in (select puid from
relationtable where AbsPuid =7)
The subquery will return the values {10, 2, 8, 5} but the result of the
main query is {2, 4, 6, 7} rather than {7, 2, 6, 4}. So what should be
the query so that my end result is {7, 2, 6, 4}
I hope this will clear the question.
Another doubt which I have is will the SQLite search the entire table
with the first entry in the result set then followed by second entry in
the result set, and so on or will it first check the first row against
all the values in the result test, then second row with all the values
in the result set and so on. If the first case the result will be {7, 2,
6, 4} and in the latter the result will be {2, 4, 6, 7}. Hence I feel
that sqlite follows the second case. So is that true?
Chris,
I don't have any specific precise display sequence to mention it using
another column.
Regards,
Phanisekhar
-Original Message-
From: Igor Tandetnik [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 to maintain the order?
> I want the order of the subquery to be preserved in the main query.
Show an example. I'd like to look at the ORDER BY clause of that
subquery (it does have one, right)? Then I'll essentially move the ORDER
BY out of the subquery and into the main query.
> For e.g.: for the sub query returned values in order (2, 8, 7, 3)
How does the subquery impose this particular order?
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Re: Order of result of a query?
>> Will the result of the above query be (1, 6, 5, 2) or (1, 2, 5, 6)? Actually I was getting the result (1, 2, 5, 6), but I wanted the result as per the given order in the OR clause "2 or 8 or 7 or 3". I wanted to know how sqlite works internally. >> Using which query we can get the result (1, 6, 5, 2)? Assume the values in the OR clause, be replaced by some subquery. Then in such scenarios how will I be able to maintain the order? I want the order of the subquery to be preserved in the main query. For e.g.: for the sub query returned values in order (2, 8, 7, 3), I need the main query should to return (1, 6, 5, 2) not (1, 2, 5, 6). Here (2, 8, 7, 3) is not fixed, which u have assumed in your reply. Regards, Phani -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 15, 2007 5:27 PM To: SQLite Subject: [sqlite] 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 4 > 4 6 > 5 7 > 6 8 > > "select rowid from mainTable where Puid = 2 OR puid = 8 OR puid = 7 > OR puid = 3" > > Will the result of the above query be (1, 6, 5, 2) or (1, 2, 5, 6)? Why don't you try it and see for yourself? Note that, without ORDER BY clause, the order of records is an implementation detail you should not rely on. It may be (1, 6, 5, 2), or (1, 2, 5, 6), or something else. If you want a particular order, specify it explicitly. > Using which query we can get the result (1, 6, 5, 2)? select rowid from mainTable where Puid in (2, 8, 7, 3) order by (case Puid when 2 then 1 when 8 then 2 when 7 then 3 when 3 then 4 end); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Order of result of a query?
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 mainTable where Puid = 2 OR puid = 8 OR puid = 7 OR puid = 3" Will the result of the above query be (1, 6, 5, 2) or (1, 2, 5, 6)? Using which query we can get the result (1, 6, 5, 2)? Regards, Phani
[sqlite] SQLITE_ERROR in sqlite3_prepare
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
[sqlite] sqlite3_last_insert_rowid : what will it return if we have more than one table?
Assume I have two tables A and B in the databse. sqlite3_last_insert_rowid will return the rowid of which table? Regards, Phani
RE: [sqlite] INSERT OR REPLACE without new rowid
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: sqlite-users@sqlite.org Subject: [sqlite] INSERT OR REPLACE without new rowid Hello all, I'd like to perform an update to a row if it exists (uniquely identified by 3 text columns), otherwise insert a new row with the right data. INSERT OR REPLACE looks good, but it generates a new primary key each time there is a conflict. If the row exists, I need to keep the original primary key (rowid). Any way to do this short of SELECT.. if (exists) UPDATE else INSERT ? Thanks, Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is this valid sqlite stmt?
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
RE: [sqlite] An explanation?
Thanks Dennis, Is it that when a Integer column of a table is defined as primary key, the it will be part of every index table (rather than rowid) defined on that table? How does it work when we define a non integer as primary key. Assuming in the example given below if we make the Title column as primary key and create index on Id, how does it affect the performance of the two queries? Regards, Phanisekahr From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thu 4/26/2007 7:52 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] An explanation? 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 TitleName" > > Now since Id is an integer and a primary key, this will work as rowid > internally. > > I have two queries that needs to be optimized: > > 1 Select TitleName from Title where Id = ? > 2 Select Id from Title where TitleName = ? > > In order to make the previous two queries optimized, how should I > declare my Table and Index? > > Should it be: > > 1 > "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName > String)" > "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON (TitleName, Id)" > > 2 > The one which I assumed > > Which one of these will give the better performance for the two queries? > Or is there any other alternative that will give even better > performance? > > Regards, > Phanisekhar > Phanisekhar, Your original index definition is all that is needed. The index already contains the rowid for the table record, which happens to be the column id because of the integer primary key optimization. There is nothing to be gained by adding it to the index again. Your first query will be satisfied by a binary search in the title table looking for the id. It won't use the index. Your second query will be satisfied by a binary search in the TitleIdx index looking for a matching title. It won't use the Title table. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] An explanation?
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 TitleName" Now since Id is an integer and a primary key, this will work as rowid internally. I have two queries that needs to be optimized: 1 Select TitleName from Title where Id = ? 2 Select Id from Title where TitleName = ? In order to make the previous two queries optimized, how should I declare my Table and Index? Should it be: 1 "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName String)" "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON (TitleName, Id)" 2 The one which I assumed Which one of these will give the better performance for the two queries? Or is there any other alternative that will 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: > 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 >9, 56 > 89, 78 > 34, 12 > 99, 26 > 19, 77 > 44, 62 > 59, 55 Each table entry also has a hidden ROWID. Let's assume that the rowids are sequential. Then your data is really this: 1, 10, 91 2, 9, 56 3, 89, 78 4, 34, 12 5, 99, 26 6, 19, 77 7, 44, 62 8, 59, 55 Here the rowids are sequential. That do not have to be. But they do have to be unique and in increasing order. Because the rowids are ordered, we can do a binary search to quickly find an entry with a particular rowid. > > Corresponding to this table1 how will index table be? > The index on table1(a) consists of all table1.a values followed by their corresponding rowid, in increasing order: 9, 2 10, 1 19, 6 34, 4 44, 7 59, 8 89, 3 99, 5 > If each data value was unique, then one index lookup would find the > matching record. Can you explain how this is? Doesn't it will do binary > search on index table? > When you do: SELECT b FROM table1 WHERE a=34; SQLite first does a binary search on the index to find the entry where a==34. From this entry it discovers the rowid. rowid=4. Then it does a binary search on the table using rowid=4 to find the corresponding entry in the table. From that entry it sees that b=12. So in this case, SQLite has to do two separate binary searches, one on the index and another on the table. If, however, you declare your index like this: CREATE INDEX index1 ON table1(a, b); Then the index will look like this: 9, 56, 2 10, 91, 1 19, 77, 6 34, 12, 4 44, 62, 7 59, 55, 8 89, 78, 3 99, 26, 5 With this two-column index, if you repeat the same query SELECT b FROM table1 WHERE a=34 Then SQLite begins as it did before by doing a binary search on the index to find the row of the index where a==34. But having found that index row, it can read out the value of b=12 directly, without having to do a second binary search on the table. The original table is never consulted and the query runs twice as fast. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] An explanation?
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 9, 56 89, 78 34, 12 99, 26 19, 77 44, 62 59, 55 Corresponding to this table1 how will index table be? If each data value was unique, then one index lookup would find the matching record. Can you explain how this is? Doesn't it will do binary search on index table? Regards, Phani -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 24, 2007 4:06 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] An explanation? Marco Bambini wrote: > > Database is uniformly distributed, I created it ad hoc just for my > test (sqlite 3.3.12): Marco, Another way to think of this is that if your database contained random numbers in the range 1-100 for both a and b, then an index on either of those values would allow sqlite to ignore all but the requested value, or 99% of the entries. It would only have to examine 1% of the records and would run in perhaps 2% of the time of a full table scan. If your data had even more distinct values, things would be even faster. Ultimately, if each data value was unique, then one index lookup would find the matching record, and the lookup time would only be about 2/300,000 or 0.0007% of the time for a full table scan. Indexes are not a magical cure all, they only speed up lookups if you enough different values to let them to reduce the search space to a small enough portion of the entire database to pay for their overhead. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL Close issues
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 following code checks for that in sqlite3_close in main.c
if( db->pVdbe ){
sqlite3Error(db, SQLITE_BUSY,
"Unable to close due to unfinalised statements");
return SQLITE_BUSY;
}
Regards,
Phani
