Re: [sqlite] extension-functions.tgz for sqlite3 3.4.1 ?

[Joe Wilson]

--- Liam Healy <[EMAIL PROTECTED]> wrote:
> Thanks.  I have  changed the use of sqlite3CreateFunc to
> sqlite3_create_function.  I did not need to include the source code for
> sqlite3utf8CharLen because there's a sqlite3Utf8CharLen (note different
> capitalization) in the library.  However, the definition

How you intend to integrate your new SQL extension functions into sqlite?
As a loadable module or as an sqlite3 source code patch?

You can't use sqlite3 internal functions such as sqlite3Utf8CharLen 
if you're making an external loadable module, which is why it was suggested
to copy the function into your code statically. You can only use the
published sqlite3 external API in this case. But your library will survive
without modifications over new sqlite3 releases.

If you're not making an external loable module and are making an sqlite3 
source patch, just use the script provided earlier in this thread to change 
the old extension sources to be compatible with the 3.4.x sqlite3.c 
amalgamation. Mind you, if you're doing the patch approach you may have to 
keep updating it with every new sqlite release.

> of sqlite3ReadUtf8 and needed definitions READ_UTF8, xtra_utf8_bytes,
> xtra_utf8_bits, utf_mask are not in 3.4.2, so I needed to
> salvage from 3.3.13 source.  This compiles and loads OK, but I'm wondering
> if there is a 3.4 way of doing what sqlite3ReadUtf8 did
> so that I don't have to carry the definitions.   If anyone has a suggestion
> I'd appreciate hearing about it.
> 
> Liam
> 
> 
> On 8/31/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> >
> > --- Liam Healy <[EMAIL PROTECTED]> wrote:
> > > I was the one who packaged up extension-functions.tgz and posted on
> > > contrib.  I didn't author the original code but I'd like to fix this up.
> > > I'm not clear on what needs to be changed.  I gather that
> > sqlite3utf8CharLen
> > > and sqlite3CreateFunc shouldn't be used.  I'm not sure how to convert to
> > use
> > > the external API.  Anyone have a pointer?
> >
> > See:
> >
> >   http://www.sqlite.org/capi3ref.html#sqlite3_create_function
> >
> >   http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
> >
> > And include this statically in your code, if you require it:
> >
> > #define SQLITE_SKIP_UTF8(zIn) {\
> >   if( (*(zIn++))>=0xc0 ){  \
> > while( (*zIn & 0xc0)==0x80 ){ zIn++; } \
> >   }\
> > }
> >
> > int sqlite3Utf8CharLen(const char *zIn, int nByte){
> >   int r = 0;
> >   const u8 *z = (const u8*)zIn;
> >   const u8 *zTerm;
> >   if( nByte>=0 ){
> > zTerm = [nByte];
> >   }else{
> > zTerm = (const u8*)(-1);
> >   }
> >   assert( z<=zTerm );
> >   while( *z!=0 && z > SQLITE_SKIP_UTF8(z);
> > r++;
> >   }
> >   return r;
> > }



   

Yahoo! oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]

[Dan Kennedy]

On Sat, 2007-09-01 at 11:58 +0800, RaghavendraK 70574 wrote:
> Hi,
> 
> In one of the slides "http://www.sqlite.org/php2004/page-052.html;
> it is stated "OR will make DBEngine not to use Indexes" and IN will 
> make DBEngine use
> indexes"
> 
> I could not understand the rationale abt this. Can u explain?

The point is (was) that if you have a table like this:

  CREATE TABLE abc(a PRIMARY KEY, b, c);

Then the first query uses the index on a, the second does (did) not:

  SELECT * FROM abc WHERE a IN ('hello', 'world');
  SELECT * FROM abc WHERE a = 'hello' OR a = 'world';

However, since those slides were written SQLite has been updated so that
queries of the second form are internally transformed to the first, and
therefore may use an index.

Dan.

> 
> regards
> ragha
> 
> **
>  This email and its attachments contain confidential information from HUAWEI, 
> which is intended only for the person or entity whose address is listed 
> above. Any use of the information contained herein in any way (including, but 
> not limited to, total or partial disclosure, reproduction, or dissemination) 
> by persons other than the intended recipient(s) is prohibited. If you receive 
> this e-mail in error, please notify the sender by phone or email immediately 
> and delete it!
>  
> *
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]

[RaghavendraK 70574]

Hi,

In one of the slides "http://www.sqlite.org/php2004/page-052.html;
it is stated "OR will make DBEngine not to use Indexes" and IN will 
make DBEngine use
indexes"

I could not understand the rationale abt this. Can u explain?

regards
ragha

**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] extension-functions.tgz for sqlite3 3.4.1 ?

[Liam Healy]

Thanks.  I have  changed the use of sqlite3CreateFunc to
sqlite3_create_function.  I did not need to include the source code for
sqlite3utf8CharLen because there's a sqlite3Utf8CharLen (note different
capitalization) in the library.  However, the definition
of sqlite3ReadUtf8 and needed definitions READ_UTF8, xtra_utf8_bytes,
xtra_utf8_bits, utf_mask are not in 3.4.2, so I needed to
salvage from 3.3.13 source.  This compiles and loads OK, but I'm wondering
if there is a 3.4 way of doing what sqlite3ReadUtf8 did
so that I don't have to carry the definitions.   If anyone has a suggestion
I'd appreciate hearing about it.

Liam


On 8/31/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
>
> --- Liam Healy <[EMAIL PROTECTED]> wrote:
> > I was the one who packaged up extension-functions.tgz and posted on
> > contrib.  I didn't author the original code but I'd like to fix this up.
> > I'm not clear on what needs to be changed.  I gather that
> sqlite3utf8CharLen
> > and sqlite3CreateFunc shouldn't be used.  I'm not sure how to convert to
> use
> > the external API.  Anyone have a pointer?
>
> See:
>
>   http://www.sqlite.org/capi3ref.html#sqlite3_create_function
>
>   http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
>
> And include this statically in your code, if you require it:
>
> #define SQLITE_SKIP_UTF8(zIn) {\
>   if( (*(zIn++))>=0xc0 ){  \
> while( (*zIn & 0xc0)==0x80 ){ zIn++; } \
>   }\
> }
>
> int sqlite3Utf8CharLen(const char *zIn, int nByte){
>   int r = 0;
>   const u8 *z = (const u8*)zIn;
>   const u8 *zTerm;
>   if( nByte>=0 ){
> zTerm = [nByte];
>   }else{
> zTerm = (const u8*)(-1);
>   }
>   assert( z<=zTerm );
>   while( *z!=0 && z SQLITE_SKIP_UTF8(z);
> r++;
>   }
>   return r;
> }
>
>

RE: [sqlite] Problem inserting blob

[Prakash Reddy Bande]

OK,

I figured it out X should be followed by only hex characters 0-9, a-f.

Regards,

Prakash Reddy Bande
Altair Engg. Inc,
Troy, MI

-Original Message-
From: Prakash Reddy Bande [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 31, 2007 4:00 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Problem inserting blob

Hi,

I am trying to insert blob in a sqlite database using sqlite.exe .

create table t (x blob);

insert into t values (x'ccaaffee');

insert into t values (x'ccaa-ffee'); // This line give the error SQL
error: unrecognized token: "x'ccaa"

 

Where am I going wrong? 

 

Regards,

 

Prakash Reddy Bande

Altair Engg. Inc,

Troy, MI

 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Problem inserting blob

[Cesar D. Rodas]

insert into t values ("x'ccaa-ffee'");
or
insert into t values ('x''ccaa-ffee''');
What you wanna insert must be between " or ' (''[double ' ] is the scape of
')

The cleanest way to do is compiling the query


On 31/08/2007, Prakash Reddy Bande <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I am trying to insert blob in a sqlite database using sqlite.exe .
>
> create table t (x blob);
>
> insert into t values (x'ccaaffee');
>
> insert into t values (x'ccaa-ffee'); // This line give the error SQL
> error: unrecognized token: "x'ccaa"
>
>
>
> Where am I going wrong?
>
>
>
> Regards,
>
>
>
> Prakash Reddy Bande
>
> Altair Engg. Inc,
>
> Troy, MI
>
>
>
>


-- 
Cesar D. Rodas
http://www.cesarodas.com/
Mobile Phone: 595 961 974165
Phone: 595 21 645590
[EMAIL PROTECTED]
[EMAIL PROTECTED]

[sqlite] Problem inserting blob

[Prakash Reddy Bande]

Hi,

I am trying to insert blob in a sqlite database using sqlite.exe .

create table t (x blob);

insert into t values (x'ccaaffee');

insert into t values (x'ccaa-ffee'); // This line give the error SQL
error: unrecognized token: "x'ccaa"

 

Where am I going wrong? 

 

Regards,

 

Prakash Reddy Bande

Altair Engg. Inc,

Troy, MI

 

Re: [sqlite] version 3.5.0 - Segv

[Ken]

I did do an update this morning to pick up the latest code base. 
I guess the prior version that I had check out earlier in the week was not the 
most current.



[EMAIL PROTECTED] wrote: Ken  wrote:
> The segv seems to be resolved. At least no more issues 
> with this version of the code. 
> 

That's funny, because I haven't changed anything to 
address your problem.  I never could reproduce it.

Perhaps you were using a version of the code that was
two or three revs behind what I was using.  There were
many fixes to the multithreading logic earlier in the
week.

--
D. Richard Hipp 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] extension-functions.tgz for sqlite3 3.4.1 ?

[Joe Wilson]

--- Liam Healy <[EMAIL PROTECTED]> wrote:
> I was the one who packaged up extension-functions.tgz and posted on
> contrib.  I didn't author the original code but I'd like to fix this up.
> I'm not clear on what needs to be changed.  I gather that sqlite3utf8CharLen
> and sqlite3CreateFunc shouldn't be used.  I'm not sure how to convert to use
> the external API.  Anyone have a pointer?

See:

  http://www.sqlite.org/capi3ref.html#sqlite3_create_function

  http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

And include this statically in your code, if you require it:

#define SQLITE_SKIP_UTF8(zIn) {\
  if( (*(zIn++))>=0xc0 ){  \
while( (*zIn & 0xc0)==0x80 ){ zIn++; } \
  }\
}

int sqlite3Utf8CharLen(const char *zIn, int nByte){
  int r = 0;
  const u8 *z = (const u8*)zIn;
  const u8 *zTerm;
  if( nByte>=0 ){
zTerm = [nByte];
  }else{
zTerm = (const u8*)(-1);
  }
  assert( z<=zTerm );
  while( *z!=0 && zhttp://sims.yahoo.com/  

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] rowid versus docid for fts3.

[Scott Hess]

Unfortunately, the reason fts2 couldn't be "fixed" was because you
can't perform the necessary ALTER TABLE if the column you're adding is
a primary key.  Since the only alternative would be to build a new
table and copy everything over, it seemed more reasonable to just let
the app developer do that, rather than forcing it on them under the
covers.

-scott


On 8/31/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
> This one just came to my mind:
>
>   CREATE TABLE (rowid INTEGER PRIMARY KEY, t TEXT);
>
> This promotes "rowid" to a visible column "rowid" which does not change 
> during a VACUUM. "rowid" is already a reserved word in SQLite. Maybe this 
> option is even compatible to FTS2?
>
> Ralf
>
> >ext/fts3.c in the current code fixes the fts2-vs-vacuum problem by
> >adding "docid INTEGER PRIMARY KEY" to the %_content table.  This
> >becomes an alias for rowid, and thus causes vacuum to not renumber
> >rowids.  It is safe to add that column because the other columns in
> >%_content are constructed such that even the following:
> >
> >CREATE VIRTUAL TABLE t USING fts3(docid);
> >
> >will work fine.
> >
> >I'm considering whether I should take it one step further, and make
> >docid a reserved column name for fts3 tables.  My rational is that
> >fts3 rowids are not quite the same as the rowids of regular tables -
> >in fact, some use-cases would encourage users of fts3 to use rowids in
> >exactly the way that fts2 was inappropriately using them!
> >
> >docid would be a hidden column, like rowid.  That means that you'll
> >only see the column in SELECT and INSERT statements if you explicitly
> >reference it.  It would operate WRT rowid exactly as an INTEGER
> >PRIMARY KEY column would.
> >
> >Opinions?
> >
> >-scott
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] extension-functions.tgz for sqlite3 3.4.1 ?

[Liam Healy]

I was the one who packaged up extension-functions.tgz and posted on
contrib.  I didn't author the original code but I'd like to fix this up.
I'm not clear on what needs to be changed.  I gather that sqlite3utf8CharLen
and sqlite3CreateFunc shouldn't be used.  I'm not sure how to convert to use
the external API.  Anyone have a pointer?

Thanks.
Liam


On 8/2/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
>
> --- Paul Harris <[EMAIL PROTECTED]> wrote:
> > I wanted to get a LOG10() function in sqlite3, and I found the
> > extension-functions.tgz file in http://sqlite.org/contrib
> >
> > I am using the all-in-one sqlite3.h/c version of sqlite3, and the
> > extension-functions files don't seem to fit at all.
> >
> > For example, it wants to call a function called sqlite3CreateFunc(),
> > which seems to have been renamed to sqlite3_create_function()
> >
> > Should I even bother trying to hack these files into shape, or has
> > sqlite3 changed so much that these files will only serve to introduce
> > bugs?
>
> The extension is somewhat out of date, but still usable.
>
> sqlite3CreateFunc is an internal sqlite function to register
> the extension functions, making it incompatible with a seperately
> compiled sqlite3.c. sqlite3utf8CharLen is another internal
> function used by the extension.
>
> You could convert all the registration functions to use the external
> API, or drop this file into sqlite/src and update the standard makefile.
>
> If you want to live on the edge, put map.h, map.c, func_ext.c,
> sqlite3.c and sqlite3.h in the same directory and run this:
>
> sed 's/sqlite3RegisterBuiltinFunctions(db);/& {extern void
> sqlite3RegisterExtraFunctions(sqlite3
> *db); sqlite3RegisterExtraFunctions(db);};/' sqlite3.c > sqlite3f.c
> echo "" >> sqlite3f.c
> echo "#include " >> sqlite3f.c
> echo "" >> sqlite3f.c
> sed 's/#include.*//' map.h map.c func_ext.c | \
>   sed 's/sqlite3utf8CharLen/sqlite3Utf8CharLen/' >> sqlite3f.c
>
> Then use sqlite3f.c instead of sqlite3.c to build your program.
>
> If you're on Windows, download and install Cygwin or MSYS to get these
> UNIX commands to build sqlite3f.c.
>
> # optional: build sqlite3 command-line shell.
> # shell.c must be in current directory.
> gcc sqlite3f.c shell.c -o sqlite3f
>
>
>
>
>
> 
> Pinpoint customers who are looking for what you sell.
> http://searchmarketing.yahoo.com/
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] Performance tuning for Insert and select operations

[Joe Wilson]

--- venkata ramana <[EMAIL PROTECTED]> wrote:
> I am using SQLite 3_3_17with the default configuration of SQLite.
> Using Windows XP C++ API.
> 50,000 inserts into Table (with primary key and no other index as single
> transaction commit) is taking 4.609000 sec.
> 50,000 selects from Table ( having 1,00,000 rec with primary key and no
> other index) , query using primary key is taking 8.751000 sec.
> I have to achieve insertion bench mark of nearly 50,000 insertions in 1.5 to
> 2 sec.
> I have to achieve selection bench mark of nearly 50,000 selections in 1.5 to
> 2 sec.
> Please tell me if it is possible to tune Sqlite to achieve this performance.

With the right hardware you can achieve this performance.


   

Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] (select *) VS (select column1, column2 ...)

[Joe Wilson]

Using SELECT * will be slower. How much slower depends on your table.
Why don't you test it and see?

--- "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> 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?



   

Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] rowid versus docid for fts3.

[Joe Wilson]

--- Ralf Junker <[EMAIL PROTECTED]> wrote:
> This one just came to my mind:
> 
>   CREATE TABLE (rowid INTEGER PRIMARY KEY, t TEXT);
> 
> This promotes "rowid" to a visible column "rowid" which does not change 
> during a VACUUM. "rowid"
> is already a reserved word in SQLite. Maybe this option is even compatible to 
> FTS2?

Making rowid public changes the default INSERT behavior of a table.

sqlite> create table t(a);
sqlite> insert into t values(1);
sqlite> drop table t;
sqlite> create table t(rowid INTEGER PRIMARY KEY, a);
sqlite> insert into t values(1);
SQL error: table t has 2 columns but 1 values were supplied

But if you always use named columns for INSERT, you would be okay.


   

Sick sense of humor? Visit Yahoo! TV's 
Comedy with an Edge to see what's on, when. 
http://tv.yahoo.com/collections/222

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] version 3.5.0 - Segv

[Joe Wilson]

--- Dan Kennedy <[EMAIL PROTECTED]> wrote:
> On Thu, 2007-08-30 at 19:13 -0700, Joe Wilson wrote:
> > I see what's going on now. I incorrectly assumed that both configure 
> > builds and the amalgamation were both threadsafe by default in the 
> > 3.4.x sources. 
> > 
> > It appears that a default ./configure without options for both 3.4.x 
> > and the new 3.5 sources will result in a non-threadsafe build. This 
> > has always been the case, since configure explicitly defines 
> > -DTHREADSAFE=0 in the Makefile.
> 
> It's an ongoing problem that the configure script is a bit of
> a second-class citizen. It's not convenient to develop with,
> so people working on the sqlite code don't use it.

Here's a patch to make configure default to a thread-safe build
to match the default behavior of the amalgamation.

http://www.sqlite.org/cvstrac/tktview?tn=2606



   

Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Is there any book (books??) to help us creating a bidirectional Sync tool?

[jfbaro]

Hi guys,

We have been looking for books which cover the DB synchronisation subject.
We have started creating a plugin to our socket server but we quickly
realised how much of a challenge it would be (We are creating it "from
scratch"). The conflict problems, the order to tables (rows) be synchronised
(Child - Parent) and how to delete rows were some of the problems we found.
The main goal is to synchronise devices in the field (Compact framework) to
the server using less bandwich as possible.
We are trying to be "agnostic" about the DB in both Server and Client side.

We are pretty sure there are books out there which cover this subject, we
just couldn't find them! :confused:

We know this will be a challenge but we also know it will be a great feature
to add to the server.

Does anyone could point us out to any book?

Cheers

Jean Baro
Dynamic Devices Ltd
-- 
View this message in context: 
http://www.nabble.com/Is-there-any-book-%28books--%29-to-help-us-creating-a-bidirectional-Sync-tool--tf4361009.html#a12429220
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] 3.5.0 error

[Ken]

Running the same code (sqlitetest_thrd35.c) using shared_cache, occasionally 
generates the following output:

Where did the table go?

0 => Executing: COMMIT
3 => Executing: INSERT INTO test_table VALUES(3, 0, 'test3_0')
0 => Executing: select count(*) from test_Table
3 => Executing: INSERT INTO test_table VALUES(3, 1, 'test3_1')

3 => query failed: [1] no such table: test_table

3 => Executing: select count(*) from test_Table
0 => finished.
1 => Executing: BEGIN
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY

Re: [sqlite] version 3.5.0 - Segv

[drh]

Ken <[EMAIL PROTECTED]> wrote:
> The segv seems to be resolved. At least no more issues 
> with this version of the code. 
> 

That's funny, because I haven't changed anything to 
address your problem.  I never could reproduce it.

Perhaps you were using a version of the code that was
two or three revs behind what I was using.  There were
many fixes to the multithreading logic earlier in the
week.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] select round(98926650.50001, 1) ?

[Serena Lien]

Hello,

With SQLite 3.3.13, this returns 98926650.501
Can you explain how I can get the expected rounding/truncation?

thanks.

Re: [sqlite] version 3.5.0 - Segv

[Ken]

The segv seems to be resolved. At least no more issues with this version of the 
code. 

$Id: sqliteInt.h,v 1.606 2007/08/30 14:10:30 drh Exp $

Thanks for a great product.
Ken

[sqlite] SQL logic error or missing

[Jiri Hajek]

Hi,

Some time ago I asked here about a strange and very rare SQLite DB
problem that puzzles me, but unfortunatelly I got no answer. I'll try
to describe it again and hopefully someone will be able to comment
it...

Very rarely (I have just 5 debug logs from all our beta testers) executing
'COMMIT' statement returns SQLITE_ERROR + 'SQL logic error or missing
database'. Analysis of the debug logs and source codes doesn't show
any problem, there simply begins a transaction, some SQL statements
are executed and COMMIT should finish it - but it doesn't.

Unfortunatelly SQLITE_ERROR is quite a generic term that doesn't tell
much. I tried to go through the places SQLite returns this message (I
don't have any deeper understanding of SQLite sources) and one place
that seems to be related to my problem is in
sqlite3PagerCommitPhaseTwo(), namely:

  if( pPager->state

Re: [sqlite] Regenerating ROWID...?

[Babu, Lokesh]

Thanks Simon, Time being I was looking for the same. Thanks again for the help.

On 8/31/07, Simon Davies <[EMAIL PROTECTED]> wrote:
> On 31/08/2007, Babu, Lokesh <[EMAIL PROTECTED]> wrote:
> > I was trying the following piece of code (see below),
> >
> > The requirement here is very simple, I want the t_id field or ROWID
> > field to be regenerated sequentially even after delete has been
> > performed.
> > 
>
> Hi Lokesh,
>
> You can achieve this with a trigger as follows:
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> create table testTbl( t_id integer, t_name text, t_desc text );
> sqlite>
> sqlite> insert into testTbl values( 1, '111', 'd' );
> sqlite> insert into testTbl values( 2, '222', 'd2' );
> sqlite> insert into testTbl values( 3, '3', 'd3' );
> sqlite> insert into testTbl values( 4, '4', 'd4' );
> sqlite> insert into testTbl values( 5, '5', 'd5' );
> sqlite> create trigger testTblTrigger after DELETE on testTbl begin
>   ...> update testTbl set t_id=t_id-1 where t_id>old.t_id;
>   ...> end;
> sqlite>
> sqlite>
> sqlite> select * from testTbl;
> 1|111|d
> 2|222|d2
> 3|3|d3
> 4|4|d4
> 5|5|d5
> sqlite> delete from testTbl where t_id=2;
> sqlite> select * from testTbl;
> 1|111|d
> 2|3|d3
> 3|4|d4
> 4|5|d5
> sqlite> delete from testTbl where t_id>1 and t_id<4;
> sqlite> select * from testTbl;
> 1|111|d
> 2|5|d5
> sqlite>
>
> Rgds,
> Simon
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] (select *) VS (select column1, column2 ...)

[Tom Briggs]

   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 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
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Regenerating ROWID...?

[Simon Davies]

On 31/08/2007, Babu, Lokesh <[EMAIL PROTECTED]> wrote:
> I was trying the following piece of code (see below),
>
> The requirement here is very simple, I want the t_id field or ROWID
> field to be regenerated sequentially even after delete has been
> performed.
> 

Hi Lokesh,

You can achieve this with a trigger as follows:

SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite> create table testTbl( t_id integer, t_name text, t_desc text );
sqlite>
sqlite> insert into testTbl values( 1, '111', 'd' );
sqlite> insert into testTbl values( 2, '222', 'd2' );
sqlite> insert into testTbl values( 3, '3', 'd3' );
sqlite> insert into testTbl values( 4, '4', 'd4' );
sqlite> insert into testTbl values( 5, '5', 'd5' );
sqlite> create trigger testTblTrigger after DELETE on testTbl begin
   ...> update testTbl set t_id=t_id-1 where t_id>old.t_id;
   ...> end;
sqlite>
sqlite>
sqlite> select * from testTbl;
1|111|d
2|222|d2
3|3|d3
4|4|d4
5|5|d5
sqlite> delete from testTbl where t_id=2;
sqlite> select * from testTbl;
1|111|d
2|3|d3
3|4|d4
4|5|d5
sqlite> delete from testTbl where t_id>1 and t_id<4;
sqlite> select * from testTbl;
1|111|d
2|5|d5
sqlite>

Rgds,
Simon

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Regenerating ROWID...?

[Babu, Lokesh]

I was trying the following piece of code (see below),

The requirement here is very simple, I want the t_id field or ROWID
field to be regenerated sequentially even after delete has been
performed.

In the below code, I have created a table with 3 fields, Inserted 1000
records, Deleted some middle records,
After deletion, I want the ROWID to be regenrated or I want to UPDATE
the t_id to regenerate the numbers from 0-N.

Please Note: I don't want to create a new table, I don't want to do
VACUUM (I'm working on In-Memory database).

Anyone knows...? Please reply...

Thanks in advance...


static const char *TestSqlStats [] = {

"CREATE TABLE testTbl (t_id INTEGER, t_name TEXT, t_desc TEXT);",



"INSERT INTO testTbl (t_id,t_name,t_desc) VALUES (%d,'%s','%s');",



"SELECT * FROM testTbl WHERE t_name LIKE '%%%s%%' LIMIT 100;",

};



int main(int argc, char *argv[])

{

char *zErrMsg = NULL;

int status , i = 0;



status = sqlite3_open (database_name, & db_handle);

 if ( status)

{

printf("%d", status );

return 0;

}

/* Create Table */

status = sqlite3_exec (db_handle, TestSqlStats[0], NULL , 0, );

if (SQLITE_OK == status)

{

char name [30];

char desc [50];



for ( i = 1; i < 1000 && status == SQLITE_OK; i ++)

{

sprintf(name ,"TableName""%d", i);

sprintf(desc ,"Moves the selected control or dialog down""%d", i );

sprintf(queryString ,

TestSqlStats[1], i, name , desc);

status = sqlite3_exec (db_handle, queryString, NULL , 0, );
}
 sprintf( queryString,


"DELETE FROM testTbl WHERE t_name LIKE '%%0%%' AND ROWID
BETWEEN 100 AND 200;");

status = sqlite3_exec (db_handle, queryString, callback, 0,  );



sprintf(queryString ,

"SELECT ROWID,* FROM testTbl WHERE ROWID BETWEEN 100 AND 200;");

status = sqlite3_exec (db_handle, queryString, callback, 0,  );
}
}

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] rowid versus docid for fts3.

[Ralf Junker]

This one just came to my mind:

  CREATE TABLE (rowid INTEGER PRIMARY KEY, t TEXT);

This promotes "rowid" to a visible column "rowid" which does not change during 
a VACUUM. "rowid" is already a reserved word in SQLite. Maybe this option is 
even compatible to FTS2?

Ralf

>ext/fts3.c in the current code fixes the fts2-vs-vacuum problem by
>adding "docid INTEGER PRIMARY KEY" to the %_content table.  This
>becomes an alias for rowid, and thus causes vacuum to not renumber
>rowids.  It is safe to add that column because the other columns in
>%_content are constructed such that even the following:
>
>CREATE VIRTUAL TABLE t USING fts3(docid);
>
>will work fine.
>
>I'm considering whether I should take it one step further, and make
>docid a reserved column name for fts3 tables.  My rational is that
>fts3 rowids are not quite the same as the rowids of regular tables -
>in fact, some use-cases would encourage users of fts3 to use rowids in
>exactly the way that fts2 was inappropriately using them!
>
>docid would be a hidden column, like rowid.  That means that you'll
>only see the column in SELECT and INSERT statements if you explicitly
>reference it.  It would operate WRT rowid exactly as an INTEGER
>PRIMARY KEY column would.
>
>Opinions?
>
>-scott


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] compiling

[John Stanton]

nishit sharma wrote:

hi all,
i have made a sampe which is opening a database file but i m
unable to compile that source code and getting error that
undefined reference to sqlite3_open().
i m compiling as
gcc test.c

can anybody tell that these is the command to compile
sqlite3 application or we have any other command

waiting for reply

regards
Nishit


You need to link with the sqlite3 library.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] running code

[nishit sharma]

i m able to run the code.
how can i read some specific stuff from that database entry

regards
Nishit

On 8/31/07, kirrthana M <[EMAIL PROTECTED]> wrote:
>
> Your test1.o with 2 arguments
> first will be the name of the database and the second will be some sql
> command to be executed.
>
> -Original Message-
> From: nishit sharma [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 31, 2007 5:13 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] running code
>
>
> here i am attaching a code
> can anyone tell me how to run this code.
>
> waiting for reply
>
> The information contained in this electronic message and any attachments
> to this message are intended for the exclusive use of the addressee(s) and
> may contain proprietary, confidential or privileged information. If you are
> not the intended recipient, you should not disseminate, distribute or copy
> this e-mail. Please notify the sender immediately and destroy all copies of
> this message and any attachments contained in it.
>
> Contact your Administrator for further information.
>

RE: [sqlite] running code

[kirrthana M]

Your test1.o with 2 arguments
first will be the name of the database and the second will be some sql
command to be executed.

  -Original Message-
  From: nishit sharma [mailto:[EMAIL PROTECTED]
  Sent: Friday, August 31, 2007 5:13 PM
  To: sqlite-users@sqlite.org
  Subject: [sqlite] running code


  here i am attaching a code
  can anyone tell me how to run this code.

  waiting for reply

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments contained in it.

Contact your Administrator for further information.

[sqlite] running code

[nishit sharma]

here i am attaching a code
can anyone tell me how to run this code.

waiting for reply
#include 
#include 

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i

[sqlite] (select *) VS (select column1, column2 ...)

[B V, Phanisekhar]

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] compiling

[nishit sharma]

i have downloaded
sqlite-3.4.2.tar.gz from
the site and i have compiled this but i didn't
find any libraries regarding this.
can u help me regarding libraries.

regards


On 8/31/07, nishit sharma <[EMAIL PROTECTED]> wrote:
>
> it is included. the program which i m compiling is attached and thats the
> sample program available on sqlite3.org but again its giving me error.
>
> regards
>
>
>  On 8/31/07, kirrthana M <[EMAIL PROTECTED]> wrote:
> >
> > I think you have not included header file "sqlite3.h" in your code,if u
> > have
> > not included include and compile again.
> >
> > -Original Message-
> > From: nishit sharma [mailto:[EMAIL PROTECTED]
> > Sent: Friday, August 31, 2007 12:45 PM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] compiling
> >
> >
> > hi all,
> > i have made a sampe which is opening a database file but i m
> > unable to compile that source code and getting error that
> > undefined reference to sqlite3_open().
> > i m compiling as
> > gcc test.c
> >
> > can anybody tell that these is the command to compile
> > sqlite3 application or we have any other command
> >
> > waiting for reply
> >
> > regards
> > Nishit
> >
> >
> > The information contained in this electronic message and any attachments
> > to this message are intended for the exclusive use of the addressee(s) and
> > may contain proprietary, confidential or privileged information. If you are
> > not the intended recipient, you should not disseminate, distribute or copy
> > this e-mail. Please notify the sender immediately and destroy all copies of
> > this message and any attachments contained in it.
> >
> > Contact your Administrator for further information.
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
>
>

Re: [sqlite] compiling

[nishit sharma]

it is included. the program which i m compiling is attached and thats the
sample program available on sqlite3.org but again its giving me error.

regards


On 8/31/07, kirrthana M <[EMAIL PROTECTED]> wrote:
>
> I think you have not included header file "sqlite3.h" in your code,if u
> have
> not included include and compile again.
>
> -Original Message-
> From: nishit sharma [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 31, 2007 12:45 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] compiling
>
>
> hi all,
> i have made a sampe which is opening a database file but i m
> unable to compile that source code and getting error that
> undefined reference to sqlite3_open().
> i m compiling as
> gcc test.c
>
> can anybody tell that these is the command to compile
> sqlite3 application or we have any other command
>
> waiting for reply
>
> regards
> Nishit
>
>
> The information contained in this electronic message and any attachments
> to this message are intended for the exclusive use of the addressee(s) and
> may contain proprietary, confidential or privileged information. If you are
> not the intended recipient, you should not disseminate, distribute or copy
> this e-mail. Please notify the sender immediately and destroy all copies of
> this message and any attachments contained in it.
>
> Contact your Administrator for further information.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
#include 
#include 

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i

RE: [sqlite] How to generate Unique ID?

[Andre du Plessis]

Hi how about the following:


CREATE TABLE puids (ID INTEGER PRIMARY KEY AUTOINCREMENT)


In python:
Def GetUniquePUID():
   #OPTIONAL, if you already have a transaction
   _Conn.cursor().execute("BEGIN EXCLUSIVE")
   Try:
 _Conn.cursor().execute("INSERT INTO PUIDS (id) values(null)");
 Return _Conn.cursor().execute("select
last_insert_rowid()").fetchone()[0]

 _Conn.cursor().execute("COMMIT")
   Except:
_Conn.cursor().execute("ROLLBACK")
   raise

-Original Message-
From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] 
Sent: 31 August 2007 08:54 AM
To: sqlite-users@sqlite.org
Subject: 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]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] beginner

[nishit sharma]

hey buddy can u tell me how to compile the C source code in which i have
used
sqlite3_open() like calls.
i m doing gcc test.c but it is giving me error of undefined reference.
i think i am missing some thing in compiling.
waiting for reply

regards
Nishit


On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
>
> thanks for telling the link.
>
> regards
>
>
>  On 8/30/07, Pavan <[EMAIL PROTECTED]> wrote:
> >
> > Hi Nishit,
> >
> > http://www.sqlite.org/quickstart.html
> >
> > This is a good link to start with.
> >
> > Thanks,
> > Pavan.
> >
> >
> > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> > >
> > > Hi,
> > > i m beginner to sqlite
> > > can anybody send me a link which can help me
> > > in building and maintining databse
> > >
> >
> >
> >
> > --
> > '
> > Always finish stronger than you start
> > *
> >
>
>

Re: [sqlite] Table locked - why?

[Yves Goergen]

On 31.08.2007 10:45 CE(S)T, Yves Goergen wrote:
> Maybe I should retry it with a plain SQLite console and figure out
> whether the bug is in the .NET wrapper (just as the previous one I've
> found...). Stay tuned...

When I do that from an SQLite console, it works as expected. So I'll
head over to the SQLite.NET forum. Sorry for bothering you.

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] compiling

[kirrthana M]

I think you have not included header file "sqlite3.h" in your code,if u have
not included include and compile again.

-Original Message-
From: nishit sharma [mailto:[EMAIL PROTECTED]
Sent: Friday, August 31, 2007 12:45 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] compiling


hi all,
i have made a sampe which is opening a database file but i m
unable to compile that source code and getting error that
undefined reference to sqlite3_open().
i m compiling as
gcc test.c

can anybody tell that these is the command to compile
sqlite3 application or we have any other command

waiting for reply

regards
Nishit


The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments contained in it.

Contact your Administrator for further information.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Table locked - why?

[Yves Goergen]

On 31.08.2007 06:03 CE(S)T, Dan Kennedy wrote:
> On Fri, 2007-08-31 at 00:09 +0200, Yves Goergen wrote:
>> CREATE TEMPORARY TABLE attached_db.temp_table 
> 
> I'm not sure where that table is created - in the temporary
> namespace or as part of attached_db. Checking...
> 
>   SQLite version 3.4.2
>   Enter ".help" for instructions
>   sqlite> attach 'def' as def;
>   sqlite> create temporary table def.t1(a, b, c);
>   SQL error: temporary table name must be unqualified
> 
> Huh. Maybe that's the root of your problem there.

Sorry, no. I've now created the temp table without the database name
prefix but it doesn't help.

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Table locked - why?

[Yves Goergen]

On 31.08.2007 00:50 CE(S)T, Virgilio Alexandre Fornazin wrote:
> Did you closed the cursor opened at 'select *...' ?
> Thats probably the reason you have getting a 'table is locked' error.

I'm using the .NET wrapper to SQLite that should handle all API
internals for me. Even disposing the previous SQLiteCommand object from
"INSERT INTO ... SELECT * FROM ..." doesn't help here.

Maybe I should retry it with a plain SQLite console and figure out
whether the bug is in the .NET wrapper (just as the previous one I've
found...). Stay tuned...

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Table locked - why?

[Yves Goergen]

On 31.08.2007 00:23 CE(S)T, RaghavendraK 70574 wrote:
> Pls see if u hv an open sqlite3 terminal.sometimes this can also
> cause a  prob with begin tx and just kept it open.

Really, I don't. Trust me. :)

-- 
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How to generate Unique ID?

[Gerhard Haering]

On Fri, 31 Aug 2007 12:28:49 +0530, "B V, Phanisekhar" <[EMAIL PROTECTED]> 
wrote:

> Hi Gerhard,

> 

>   I am finding your code really tough to understand. Can you

> please provide some comments?



try:

from pysqlite2 import dbapi2 as sqlite

except ImportError:

import sqlite3 as sqlite



def init_tables(con):

"""

This function has to be run immediately after schema creation. It fills the

internal SQLite table sqlite_sequence. This is necessary because SQLite

creates entries for the sequences only on first use of the sequence, but we

don't want to use the sequence via autoincrement fields, but using our own

function that gets explicit id ranges.



con: connection object

"""

con.execute("""

insert into sqlite_sequence(name, seq)

select name, 1 from sqlite_master where type='table' and name not like 
'sqlite%'

""")



def get_id_range(con, table, n):

"""

Retrieves a tuple with an id range that can be used for the primary key of

the table `table`.



con: connection object

table: name of the table to get the id range for

n: number of usable ids to be allocated

"""

isolation_level = con.isolation_level

start, end = None, None

try:

con.isolation_level = None  # autocommit mode

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



if __name__ == "__main__":

# Test code, manually look wether the output makes sense ;-)

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')")




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] compiling

[nishit sharma]

hi all,
i have made a sampe which is opening a database file but i m
unable to compile that source code and getting error that
undefined reference to sqlite3_open().
i m compiling as
gcc test.c

can anybody tell that these is the command to compile
sqlite3 application or we have any other command

waiting for reply

regards
Nishit

RE: [sqlite] How to generate Unique ID?

[B V, Phanisekhar]

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?

[B V, Phanisekhar]

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]
-