Re: [sqlite] selecting a random record from a table

[Joe Wilson]

(Pardon me if this email is a duplicate, but I think the last copy
was interpreted as spam due to the repetition of rows in the SQL.)

This is not strictly random, but will select a pseudo-random row 
in near constant time regardless of the number of rows in the table:

-- let's assume you know you only have 30 or fewer rows in table1
select * from table1 where rowid = random() & 31
union  all  select * from table1  where rowid = random() & 15
union all select *  from table1 where rowid  = random() & 7
union   all select  * from table1 where rowid =  random() & 3
union  all select * from table1  where  rowid = random() &  1
limit 1;

Yes, I know rowid's are not necessarily contiguous, but
you can extend the pattern to a couple of billion rows if 
you like.



 

Bored stiff? Loosen up... 
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front

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

Re: [sqlite] selecting a random record from a table

[Joe Wilson]

This is not strictly random, but will select a row in near 
constant time regardless of the number of rows in the select:

-- let's assume you know you only have 65535 rows...
  select * from table1 where rowid = random() & 65535
union all select * from table1 where rowid = random() & 32767
union all select * from table1 where rowid = random() & 16383
union all select * from table1 where rowid = random() & 8191
union all select * from table1 where rowid = random() & 4095
union all select * from table1 where rowid = random() & 2047
union all select * from table1 where rowid = random() & 1023
union all select * from table1 where rowid = random() & 511
union all select * from table1 where rowid = random() & 255
union all select * from table1 where rowid = random() & 127
union all select * from table1 where rowid = random() & 63
union all select * from table1 where rowid = random() & 31
union all select * from table1 where rowid = random() & 15
union all select * from table1 where rowid = random() & 7
union all select * from table1 where rowid = random() & 3
union all select * from table1 where rowid = random() & 1
limit 1;

Yes, I know rowid's are not necessarily contiguous, but
you can extend the pattern to a couple of billion rows if 
you like.



 

Never Miss an Email
Stay connected with Yahoo! Mail on your mobile.  Get started!
http://mobile.yahoo.com/services?promote=mail

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

Re: [sqlite] selecting a random record from a table

[Joe Wilson]

> select * from table1 order by random() limit 1

If you're performing a select on a single table (not a view, join 
or a subquery) and you are selecting many columns (say > 6) and the 
table has a very large number of rows, then this query will run faster
on recent versions of sqlite3:

 select * from table1 
 where rowid = (select rowid from table1 order by random() limit 1);

To see why:

 create table j1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p);

 explain select * from j1 order by random() limit 1;

 explain select * from j1 
  where rowid = (select rowid from j1 order by random() limit 1);



 

Food fight? Enjoy some healthy debate 
in the Yahoo! Answers Food & Drink Q
http://answers.yahoo.com/dir/?link=list=396545367

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

[sqlite] Re: selecting a random record from a table

[Igor Tandetnik]

P Kishor <[EMAIL PROTECTED]> wrote:

On 1/25/07, Artem Yankovskiy
 wrote:

select * from table1 order by random(id) limit 1



Yes, very nice, thank you. I am not familiar with the "ORDER BY
random(col)" idiom. How does this work? (It does work alright).


random(anything) produces a random number (the parameter apparently 
doesn't matter). The query works by associating a random number with 
every row, then picking whichever one happens to end up with the 
smallest number.


Igor Tandetnik 



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

Re: [sqlite] selecting a random record from a table

[P Kishor]

On 1/25/07, Artem Yankovskiy <[EMAIL PROTECTED]> wrote:

select * from table1 order by random(id) limit 1



Yes, very nice, thank you. I am not familiar with the "ORDER BY
random(col)" idiom. How does this work? (It does work alright).



--- P Kishor <[EMAIL PROTECTED]> wrote:

> 1. given a non-sequential id, select all the ids
> 2. grab a random id
> 3. select the row with that id.
>
> is there a better way of accomplishing this, one
> that requires a
> single round-trip to the db?
>

..


Best regards,
Artem Yankovskiy




--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] selecting a random record from a table

[Artem Yankovskiy]

select * from table1 order by random(id) limit 1
?

--- P Kishor <[EMAIL PROTECTED]> wrote:

> 1. given a non-sequential id, select all the ids
> 2. grab a random id
> 3. select the row with that id.
> 
> is there a better way of accomplishing this, one
> that requires a
> single round-trip to the db?
> 
> -- 
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison
> http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation
> http://www.osgeo.org/education/
>
-
> collaborate, communicate, compete
>
=
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 


Best regards,
Artem Yankovskiy







Вы уже с Yahoo!? 
Испытайте обновленную и улучшенную. Yahoo! Почту! http://ru.mail.yahoo.com

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

[sqlite] Re: selecting a random record from a table

[Igor Tandetnik]

P Kishor <[EMAIL PROTECTED]> wrote:

1. given a non-sequential id, select all the ids
2. grab a random id
3. select the row with that id.

is there a better way of accomplishing this, one that requires a
single round-trip to the db?


If you can somehow keep track of the number of rows (N) in the table, 
you could do something like


select * from table limit 1 offset ?;

and parameterize by a random number between 0 and N-1.

Igor Tandetnik 



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

[sqlite] selecting a random record from a table

[P Kishor]

1. given a non-sequential id, select all the ids
2. grab a random id
3. select the row with that id.

is there a better way of accomplishing this, one that requires a
single round-trip to the db?

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] enforcing Foreign Keys

[Martin Jenkins]

Dan McDaniel wrote:

Can someone tell me how to unsubscribe. I have sent
two messages to the link and have had no luck thank 
you.

--- Mag Gam <[EMAIL PROTECTED]> wrote:
  
Send a mail to [EMAIL PROTECTED] - it has directions on how 
to unsubscribe if the standard method fails. I just tried it and 
(assuming all the timestamps are correct) got a response back in a 
fraction over a minute.


Martin

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

Re: [sqlite] enforcing Foreign Keys

[Dan McDaniel]

Can someone tell me how to unsubscribe. I have sent
two messages to the link and have had no luck thank 
you.
--- Mag Gam <[EMAIL PROTECTED]> wrote:

> Hi All,
> 
> I am new to sqlite, and liking it a lot so far. My
> only problem is there is
> no native foreign key/data integrity. Are there
> plans to include FKs in
> future releases? I have been reading,
>
http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator
> 
> But this is very tedious. Any anyone know an easier
> way? I am thinking more
> like postgresql's FKs
> 



 

The fish are biting. 
Get more visitors on your site using Yahoo! Search Marketing.
http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php

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

Re: [sqlite] Dumb Newbie Question - Comparison if sqlite index to STL

[Jay Sprenkle]

On 1/25/07, Ben Supnik <[EMAIL PROTECTED]> wrote:


Hi Y'all,

Please forgive what I think is probably a dumb question, but...

I am porting my app's data model from C++/STL to sqlite.  My goal is
scalability - even if I implement my data model in C++ using data
structures that give me good O(N) time (e.g. use trees, hash tables,
etc.) the whole thing has to be in memory, and adding indexing means a
pretty big code churn.

My question is:

If I take all of the sqlite optimizations I should be taking (saving my
SQL query statements in compiled form to avoid recompiling over and
over, having enough pages in memory to avoid disk thrash, using
transactions to limit disk I/O)

Does anyone have sqlite and STL experience to tel me how the speed of an
indexed column of integers in sqlite would compare to a set in C++?
  (I believe my set implementation uses a red-black tree.)



I think sqlite uses btree internally for indexing, so you might compare the
time of a red black
tree to the time of a btree to get a rough idea.

A lot of people have found the operating system
will cache their database table so a disk based database will perform as
well as a memory
based database in sqlite (as long as you have enough memory for the cache).

I wouldn't worry too much about scalability until you've reviewed the limits
on sqlite.
It will handle some very large tasks right out of the box.

Good luck



--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

[sqlite] enforcing Foreign Keys

[Mag Gam]

Hi All,

I am new to sqlite, and liking it a lot so far. My only problem is there is
no native foreign key/data integrity. Are there plans to include FKs in
future releases? I have been reading,
http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator

But this is very tedious. Any anyone know an easier way? I am thinking more
like postgresql's FKs

[sqlite] OS X: "database locked" when trying to use a database on DVD-ROM

[Plissonneau Duquene, Julien]

Hi, 

I get the "Error: database is locked" when trying to do anything 
(select, pragma, getting file version etc) with a database stored on a 
DVD-ROM under OS X. 

This happens with the (fairly old) version 3.1.3 bundled with OS X, and 
also with the (less old) version 3.3.8 in a custom-built PHP 
interpreter. 

According to ktrace/kdump, just before printing the error sqlite tried a 
F_SETLK on the file, and got errno=45 Operation not supported. The DVD 
has an ISO filesystem on it (not HFS), IIRC it worked fine when we tried 
HFS (I did not try hybrid ISO/HFS). 

Suggestions for fixes: 
- do not attempt to lock database files that do not have write permission
- also check for ENOTSUP everywhere EINVAL is checked after trying F_SETLK.

Thanks, 

-- 
Julien Plissonneau Duquene, Software Architect 
www.savoirfairelinux.com

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

[sqlite] Dumb Newbie Question - Comparison if sqlite index to STL

[Ben Supnik]

Hi Y'all,

Please forgive what I think is probably a dumb question, but...

I am porting my app's data model from C++/STL to sqlite.  My goal is 
scalability - even if I implement my data model in C++ using data 
structures that give me good O(N) time (e.g. use trees, hash tables, 
etc.) the whole thing has to be in memory, and adding indexing means a 
pretty big code churn.


My question is:

If I take all of the sqlite optimizations I should be taking (saving my 
SQL query statements in compiled form to avoid recompiling over and 
over, having enough pages in memory to avoid disk thrash, using 
transactions to limit disk I/O)


Does anyone have sqlite and STL experience to tel me how the speed of an 
indexed column of integers in sqlite would compare to a set in C++? 
 (I believe my set implementation uses a red-black tree.)


Thanks!
Ben

--
Scenery Home Page: http://scenery.x-plane.com/
Scenery blog: http://xplanescenery.blogspot.com/
Plugin SDK: http://www.xsquawkbox.net/xpsdk/
Scenery mailing list: [EMAIL PROTECTED]
Developer mailing list: [EMAIL PROTECTED]

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

Re: [sqlite] How to conditionally drop a table?

[Martin Jenkins]

jose isaias cabrera wrote:
I got it right away.  And I just don't laugh for the fun of it.  :-) 
It's gotta be funny.  Was it a tad harsh?  Yeah... But it was funny.

See http://c2.com/cgi/wiki?ZenSlap

Martin

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

RE: [sqlite] Fix for sqlite3.h in version 3.3.10

[James Dennett]

> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 24, 2007 5:04 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Fix for sqlite3.h in version 3.3.10
> 
> --- James Dennett <[EMAIL PROTECTED]> wrote:
> > Joe Wilson wrote:
> > > The proposed expression ((sqlite3_destructor_type)-1) is
equivalent to
> > > ((void(*)(void *))-1). They are interchangable.
> >
> > Not in C++.  The difference being linkage; with the typedef declared
in
> > an extern "C" block, the type is something that can't be written
> > directly.
> 
> Now I understand your confusion. SQLite is a C. All its API
definitions in
> sqlite3.h and its function pointers are obviously C-based. 

They are intended, I believe, to be usable from both C and C++, and they
have code present to allow their use from C++ -- it's just not quite
complete.

> So to avoid
> these
> warnings related to using SQLITE_TRANSIENT (or SIG_ERR for that
matter)
> in your C++ code, your C++ implementation file must do the following:

That would be one workaround, but it's cleaner to make the header work
with C++ as intended.  The #ifdef __cplusplus sections in the header
file are clearly present to allow its use from C++ without needing
contortions.  That is common practice in C header files which make some
basic effort to integrate smoothly with C++.  The effort required is not
large.

> // example.cpp
> #include "sqlite3.h"
> extern "C" {
>   void example(sqlite3_context* c, const char* b, int n) {
> sqlite3_result_text(c, b, n, SQLITE_TRANSIENT);
>   }
>   // you can even put class member function implementations in here
>   // i.e., void Foo::bar() {...}
>   // without affecting their C++ linkage.
> }
> 
> You'll find that it compiles in Sun C++ 5.8 2005/10/13 without
warnings
> using the unmodified sqlite3.h file.

I expect it would, but it's suboptimal.  C++ code ought not to need such
extern "C" blocks except for 

> The C++ code does not need the proposed typedef to handle
> SQLITE_TRANSIENT,
> although if it were added to sqlite3.h it would not do any harm, and
you
> might be able to avoid the extern "C" block around your sqlite-related
> function implementations.

Correct.

> > >   #define SIG_IGN (void(*)(int))1
> > >   #define SIG_ERR (void(*)(int))-1
> > >
> > > Such C code is grandfathered in C++. If it wasn't you wouldn't be
able
> > > to do any UNIX systems programming in C++.
> >
> > It's not portable C, and it's not portable C++.  It does seem to be
> > blessed by POSIX, but POSIX is just a subset of the much larger
range of
> > platforms supported by ISO/ANSI C and C++.
> 
> In your original question you asked:
> 
>  On a related but separate note, is there any standard that guarantees
>  that casting -1 to a function pointer type is reasonably portable?
> 
> And yes, it is reasonably portable as shown by every UNIX variant that
> must deal with POSIX and every major C++ compiler vendor, as a
> consequence.

The POSIX reference you provided didn't show any such examples though,
unless I missed something.  It shows APIs that specify that certain
arguments can use given named values or function pointers, but the named
values aren't specified, and could themselves be distinguished function
pointers.

POSIX does blur the distinction between data pointers and code pointers
(e.g., with dlsym), but that's a separate issue.

> That's good enough for me. It may or may not be blessed by the C++
> standard,
> but if you can dig up a modern popular C++ compiler that does not work
> with
> such code, I'd be surprised.

It's likely that I can find C++ tools that will diagnose it as an error;
there are reasonable implementation techniques which would mean that it
is not reliable (such as ignoring the bottom bits of function pointers).
I don't know of any platform which would have problems, but why break
the rules when a standards-conforming solution is trivial?

My proposed patch didn't address the int->void(*)(void*) conversion
issue, only the C++-compatibility wart.  Is there any good argument for
not doing so?

-- James

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

[sqlite] can select but not insert. File is set rw to all (sqlite3)

[DTK]

Hello,
I am trying to get started with sqlite3. Seams there is still
something wrong with my setup.
I am stuck and was wondering if someone could give me a hand.

I can select but it wont let me insert or update the database. The
error is "unable to open database file" however it does open the file
for a select query.

I think my code is fine because it works with a memory db. However I
will include the code below.
I also tried adding the 0666 to sqlite3_open but that does not work
either. My file is saved as rw for all.
sqlite3_open('db.sqlite', 0666)  <= Does not work
-rw-rw-rw-  1 www-data www-data  2048 2007-01-25 02:31 db.sqlite

By the way I had to create the db.sqlite file from command line it
would not create it from php. So I also inserted some data from
command line. That is how I now that the select query works.

One more note, when I create the db in memory insert, update, create
table all works. But then when I commend out the create table line and
check my data it gives me the error that there is no table called
newuser in the db/memory.
Shouldn't that table be around for a little longer. I tried it with
not closing the db but it is still gone the second time I execute the
script. I am not planning on using a memory db so I could do with out
this. I just included this in case there is a relation.


Thank you

Dennis Kaplan

=

My test Code:



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

Re: [sqlite] How to conditionally drop a table?

[John Stanton]

Martin Jenkins wrote:

G. Roderick Singleton wrote:


On Thu, 2007-01-25 at 11:13 -0600, John Stanton wrote:
 

If you find a way to drop a table which dosn't exist. let us know how 
you do it.



Thanks for your insight. However what's your point? That the OP didn't
check the website, that my reference was incorrect or what?   


He's not making a point - it's British (dry, subtle) humour.

Martin

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 




Very perspicaceous old chap.  ;-)

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

[sqlite] building in fts[12] on OSX

[Jason Jobe]

Hello,

I just downloaded the latest version (3.3.11). It configures and  
build fine out-of-the-box on OSX (10.4.8) BUT I can't seem to get the  
Full Text Search extension working and / or linked into the library /  
sqlite shell.


I tried adding the suggested flags in the Makefile to no avail.

Any pointers would be most appreciated.

thanks,
Jason


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

Re: [sqlite] How to conditionally drop a table?

[P Kishor]

DROP TABLE [IF EXISTS]
DROP INDEX [IF EXISTS]
CREATE TABLE [IF NOT EXISTS]
and so on...

On 1/25/07, Nicolas Williams <[EMAIL PROTECTED]> wrote:

On Thu, Jan 25, 2007 at 10:35:23AM -0500, Anderson, James H (IT) wrote:
> I need a way to drop a table only if that table exists. How would I do
> that?

You'd think this would work:

SELECT CASE WHEN
(SELECT count(*) FROM sqlite_master WHERE
type = 'table' AND name = 'foo') > 0
THEN (DROP TABLE foo) END;

but it doesn't (it yields a syntax error).

In fact, inserts and updates inside a select case when all yield syntax
errors.

W.r.t. to dropping a table inside a select case when, well, you should
know whether the schema to begin with, so that makes sense.

Nico
--

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





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] How to conditionally drop a table?

[G. Roderick Singleton]

On Thu, 2007-01-25 at 18:40 +, Martin Jenkins wrote:
> G. Roderick Singleton wrote:
> > On Thu, 2007-01-25 at 11:13 -0600, John Stanton wrote:
> >   
> >> If you find a way to drop a table which dosn't exist. let us know how 
> >> you do it.
> >> 
> > Thanks for your insight. However what's your point? That the OP didn't
> > check the website, that my reference was incorrect or what? 
> >   
> He's not making a point - it's British (dry, subtle) humour.
> 

In bad taste if that what the attempt was. ;-(
-- 
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech



smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] How to conditionally drop a table?

[Nicolas Williams]

On Thu, Jan 25, 2007 at 10:35:23AM -0500, Anderson, James H (IT) wrote:
> I need a way to drop a table only if that table exists. How would I do
> that?

You'd think this would work:

SELECT CASE WHEN
(SELECT count(*) FROM sqlite_master WHERE
type = 'table' AND name = 'foo') > 0
THEN (DROP TABLE foo) END;

but it doesn't (it yields a syntax error).

In fact, inserts and updates inside a select case when all yield syntax
errors.

W.r.t. to dropping a table inside a select case when, well, you should
know whether the schema to begin with, so that makes sense.

Nico
-- 

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

Re: [sqlite] How to conditionally drop a table?

[jose isaias cabrera]

Fred,

I got it right away.  And I just don't laugh for the fun of it.  :-) It's 
gotta be funny.  Was it a tad harsh?  Yeah... But it was funny.


- Original Message - 
From: "Fred Williams" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, January 25, 2007 1:42 PM
Subject: RE: [sqlite] How to conditionally drop a table?



-Original Message-
From: G. Roderick Singleton [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 25, 2007 12:06 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to conditionally drop a table?


On Thu, 2007-01-25 at 11:13 -0600, John Stanton wrote:
> If you find a way to drop a table which dosn't exist. let
us know how
> you do it.
>
> G. Roderick Singleton wrote:
> > On Thu, 2007-01-25 at 10:35 -0500, Anderson, James H (IT) wrote:
> >
> >>I need a way to drop a table only if that table exists.
How would I do
> >>that?
> >>
> >>Thanks,
> >
> >
> > Are you asking if http://sqlite.org/lang_droptable.html
works or have
> > you had problems?
>
>

Thanks for your insight. However what's your point? That the OP didn't
check the website, that my reference was incorrect or what?
--
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech




Now you know why there are no software types that are stand up comedians
:-)

I think it was a (bad) attempt at a joke...

Fred


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




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

RE: [sqlite] How to conditionally drop a table?

[Fred Williams]

> -Original Message-
> From: G. Roderick Singleton [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 25, 2007 12:06 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to conditionally drop a table?
>
>
> On Thu, 2007-01-25 at 11:13 -0600, John Stanton wrote:
> > If you find a way to drop a table which dosn't exist. let
> us know how
> > you do it.
> >
> > G. Roderick Singleton wrote:
> > > On Thu, 2007-01-25 at 10:35 -0500, Anderson, James H (IT) wrote:
> > >
> > >>I need a way to drop a table only if that table exists.
> How would I do
> > >>that?
> > >>
> > >>Thanks,
> > >
> > >
> > > Are you asking if http://sqlite.org/lang_droptable.html
> works or have
> > > you had problems?
> >
> >
>
> Thanks for your insight. However what's your point? That the OP didn't
> check the website, that my reference was incorrect or what?
> --
> G. Roderick Singleton <[EMAIL PROTECTED]>
> PATH tech
>
>

Now you know why there are no software types that are stand up comedians
:-)

I think it was a (bad) attempt at a joke...

Fred


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

Re: [sqlite] How to conditionally drop a table?

[Martin Jenkins]

G. Roderick Singleton wrote:

On Thu, 2007-01-25 at 11:13 -0600, John Stanton wrote:
  
If you find a way to drop a table which dosn't exist. let us know how 
you do it.


Thanks for your insight. However what's your point? That the OP didn't
check the website, that my reference was incorrect or what? 
  

He's not making a point - it's British (dry, subtle) humour.

Martin

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

Re: [sqlite] How to conditionally drop a table?

[G. Roderick Singleton]

On Thu, 2007-01-25 at 11:13 -0600, John Stanton wrote:
> If you find a way to drop a table which dosn't exist. let us know how 
> you do it.
> 
> G. Roderick Singleton wrote:
> > On Thu, 2007-01-25 at 10:35 -0500, Anderson, James H (IT) wrote:
> > 
> >>I need a way to drop a table only if that table exists. How would I do
> >>that?
> >>
> >>Thanks,
> > 
> > 
> > Are you asking if http://sqlite.org/lang_droptable.html works or have
> > you had problems?
> 
> 

Thanks for your insight. However what's your point? That the OP didn't
check the website, that my reference was incorrect or what? 
-- 
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech



smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] How to conditionally drop a table?

[John Stanton]

If you find a way to drop a table which dosn't exist. let us know how 
you do it.


G. Roderick Singleton wrote:

On Thu, 2007-01-25 at 10:35 -0500, Anderson, James H (IT) wrote:


I need a way to drop a table only if that table exists. How would I do
that?

Thanks,



Are you asking if http://sqlite.org/lang_droptable.html works or have
you had problems?



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

RE: [sqlite] Another (possibly dumb) question

[Anderson, James H \(IT\)]

Thanks. 

-Original Message-
From: Ralf Junker [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 25, 2007 11:37 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Another (possibly dumb) question


>For select, update, insert, or delete is there a way to get the number
>of rows affected?

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

Ralf 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

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

Re: [sqlite] Another (possibly dumb) question

[Ralf Junker]

>For select, update, insert, or delete is there a way to get the number
>of rows affected?

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

Ralf 


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

[sqlite] Another (possibly dumb) question

[Anderson, James H \(IT\)]

For select, update, insert, or delete is there a way to get the number
of rows affected?

Thanks,
jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

RE: [sqlite] How to conditionally drop a table?

[Anderson, James H \(IT\)]

Sorry, I should have checked the site first. My background is sybase and
I didn't expect anything so simple.

Thanks very much,

jim 

-Original Message-
From: G. Roderick Singleton [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 25, 2007 10:49 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to conditionally drop a table?

On Thu, 2007-01-25 at 10:35 -0500, Anderson, James H (IT) wrote:
> I need a way to drop a table only if that table exists. How would I do
> that?
> 
> Thanks,

Are you asking if http://sqlite.org/lang_droptable.html works or have
you had problems?
-- 
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

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

Re: [sqlite] How to conditionally drop a table?

[G. Roderick Singleton]

On Thu, 2007-01-25 at 10:35 -0500, Anderson, James H (IT) wrote:
> I need a way to drop a table only if that table exists. How would I do
> that?
> 
> Thanks,

Are you asking if http://sqlite.org/lang_droptable.html works or have
you had problems?
-- 
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech



smime.p7s
Description: S/MIME cryptographic signature

[sqlite] How to conditionally drop a table?

[Anderson, James H \(IT\)]

I need a way to drop a table only if that table exists. How would I do
that?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

Re: [sqlite] Re: Shared Lock Transactions

[Ken]

>From os_unix.h:... After reading this, locking makes more sense! 
   Although the Lock may physically be an exclusive lock, the 
implementation is actually a logcially  "SHARED" lock.
 
 
 
 /* The following describes the implementation of the various locks and
   ** lock transitions in terms of the POSIX advisory shared and exclusive
   ** lock primitives (called read-locks and write-locks below, to avoid
   ** confusion with SQLite lock names). The algorithms are complicated
   ** slightly in order to be compatible with windows systems simultaneously
   ** accessing the same database file, in case that is ever required.
   **
   ** Symbols defined in os.h indentify the 'pending byte' and the 'reserved
   ** byte', each single bytes at well known offsets, and the 'shared byte
   ** range', a range of 510 bytes at a well known offset.
   **
   ** To obtain a SHARED lock, a read-lock is obtained on the 'pending
   ** byte'.  If this is successful, a random byte from the 'shared byte
   ** range' is read-locked and the lock on the 'pending byte' released.
   **
   ** A process may only obtain a RESERVED lock after it has a SHARED lock.
   ** A RESERVED lock is implemented by grabbing a write-lock on the
   ** 'reserved byte'.
 **
   ** A process may only obtain a PENDING lock after it has obtained a
   ** SHARED lock. A PENDING lock is implemented by obtaining a write-lock
   ** on the 'pending byte'. This ensures that no new SHARED locks can be
   ** obtained, but existing SHARED locks are allowed to persist. A process
   ** does not have to obtain a RESERVED lock on the way to a PENDING lock.
   ** This property is used by the algorithm for rolling back a journal file
   ** after a crash.
   **
   ** An EXCLUSIVE lock, obtained after a PENDING lock is held, is
   ** implemented by obtaining a write-lock on the entire 'shared byte
   ** range'. Since all other locks require a read-lock on one of the bytes
   ** within this range, this ensures that no other locks are held on the
   ** database.
   **
   ** The reason a single byte cannot be used instead of the 'shared byte
   ** range' is that some versions of windows do not support read-locks. By
   ** locking a random byte from a range, concurrent SHARED locks may exist
   ** even if the locking primitive used is always a write-lock.
   */
 

"A. Pagaltzis" <[EMAIL PROTECTED]> wrote: * Jay Sprenkle  [2007-01-22 15:20]:
> My understanding was that a "shared lock" is a metaphor, and
> IMHO, a fairly stupid one. If you lock a room, nobody else can
> get in, it's not a mechanism for sharing, it's a mechanism for
> preventing sharing.

Reasoning by analogy rarely leads to anything but a fallacy.

A shared lock prevents exclusive locks from being granted and an
exclusive lock prevents shared locks from being granted, so I’m
not sure what sort of sharing/preventing business you’re talking
about anyway.

Regards,
-- 
Aristotle Pagaltzis // 

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

Re: [sqlite] SQLite3VB.dll

[Dennis Cote]

RB Smissaert wrote:

  'these speed up inserts enormously, particulary the first one
  '
  sqlite_get_table lDBHandle, "PRAGMA synchronous=off;", strErrors
  sqlite_get_table lDBHandle, "PRAGMA encoding='UTF-8';", strErrors
 'not sure default_cache_size applies to a memory database, probably not
  If bFile Then
 sqlite_get_table lDBHandle, _
  "PRAGMA default_cache_size = 32768;", strErrors
  End If
  sqlite_get_table lDBHandle, "PRAGMA vdbe_trace = OFF;", strErrors
  sqlite_get_table lDBHandle, "PRAGMA page_size=4096;", strErrors
  
You might want to be careful with this code. The first pragma above 
turns off synchronous disk I/O so your database is subject to corruption 
by a power failure. The second sets the encoding to its default value, 
UTF-8, and shouldn't be needed. The second last (vdbe_trace) is also 
setting the value to its default. Furthermore this pragma can only be 
turned on in a special debug build of the sqlite dll. Increasing the 
cache and page size will speed up your code safely if you have the 
memory to spare.


HTH
Dennis Cote

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

Re: [sqlite] lemon issue

[Gunnar Roth]

[EMAIL PROTECTED] schrieb:
> dear all:
> i am a programmer from China, i use sqlite in my project. 
> from sqlite, i know lemon.
> i work on windows, using vc6.0,  i am trying to do something using lemon, 
> but i have some issues.
> i write  a my_calculator.y file, and generate my_calculator.c and 
> my_calculator.h file from the my_calculator.y file, i rename file name from 
> my_calculator.c to my_calculator.cpp.
> when i  add the files(.h and .cpp) to my project, i find it could not be 
> compiled.
>
> error message:
> MyCal.obj : error LNK2001: unresolved external symbol _PtCalculatorParser
> MyCal.obj : error LNK2001: unresolved external symbol _PtCalculatorParserFree
> MyCal.obj : error LNK2001: unresolved external symbol _PtCalculatorParserAlloc
> but , in my_calculator.cpp, i have define the the function 
> PtCalculatorParser
> what't wrong? who can help me?
>  thanks.
>   
learn about c++ name mangling and about the extern "C" declaration.

regards,
gunnar


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

Re: [sqlite] [Fwd: rummage bin of C API questions]

[anis chaaba]

2007/1/25, anis chaaba <[EMAIL PROTECTED]>:hello
1-I'm trying to remember and i think that all routines that execute queries
need to free the error message variable like sqlite_exec or others.
2- No transactions fail while you're writing clean code
3- While u'r allocationg and freeing all your pointers and opening/closing
connections you won't have problems
4-for the fourth question an example would be appreciated
If you having problems with code please send us error messages and code
samples


2007/1/25, Lucas (a.k.a T-Bird or bsdfan3) <[EMAIL PROTECTED]>:






Please bear with me here,
as this list may seem rather long.

1) Which of the C API routines allocate memory that my code must free?
2) Can COMMIT TRANSACTION and ROLLBACK TRANSACTION fail?
3) Which of the C API routines cannot fail?
4) C API routines with pointer return values return NULL if they cannot
allocate sufficient memory, right?


--T-Bird/bsdfan3/Lucas




-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] [Fwd: rummage bin of C API questions]

[Lucas (a.k.a T-Bird or bsdfan3)]

Please bear with me here, 
as this list may seem rather long.


1) Which of the C API routines allocate memory that my code must free?
2) Can COMMIT TRANSACTION and ROLLBACK TRANSACTION fail?
3) Which of the C API routines cannot fail?
4) C API routines with pointer return values return NULL if they cannot 
allocate sufficient memory, right?



--T-Bird/bsdfan3/Lucas



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