Re: [sqlite] SQL Quick Review/Reference

[Amit Uttamchandani]

On Sat, 12 Apr 2008 17:23:55 +1000
Neville Franks <[EMAIL PROTECTED]> wrote:

> For SQLite in particular "The Definitive Guide to SQLite" by Micahel
> Owens is reasonably good. Unfortunately it has the worst index of any
> book I can recall seeing, other than some Cook Books. This makes it
> very difficult and frustrating to use as a Reference Book.
> 
> I also purchased "Beginning Database Design - from Novice to
> Professional" by Clare Churcher, but found it to much of a beginners
> book for my needs.
> 
> 

Thanks! This is exactly what I was looking for.

Amit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_LOCKED behavior

[Shawn Wilsher]

Hey all,

When using SQLite 3.5.4.1 (a special branch cut for Mozilla, based
mostly off of 3.5.4 with some OS/2 fixes), I'm getting SQLITE_LOCKED
returned unexpectedly.  The documentation seems to indicate that I
should only be getting SQLITE_LOCKED if I'm calling sqlite3_exec
recursively writing to the same table.  However, it seems to me that
I'm having that happen when two different threads are trying to write
to the same table.  I would expect to get SQLITE_BUSY at this point,
but perhaps I'm misusing the API or have the wrong expectations.

This is happening by using a different sqlite3 database pointers, one
for each thread.

Cheers,

Shawn Wilsher
Mozilla Developer
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] extension don't return value

[Igor Tandetnik]

"dark0s dark0s" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> bash-3.1# gcc -shared half.c -o half.so
> bash-3.1# sqlite3
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> select load_extension('/root/half.so')

Which part of "terminate each statement with a semicolon" do you find 
difficult to understand?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] extension don't return value

[dark0s dark0s]

I modified row in function:

sqlite3_result_text(ctx,(const char*)ret, 4, SQLITE_TRANSIENT);

I know that dimension is 4, but my shell don't return values for extension 
function also with half.c example copied from wiki:

bash-3.1# gcc -shared half.c -o half.so  
bash-3.1# sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> select load_extension('/root/half.so')
   ...> select half(7)
   ...> select half(7);
SQL error: near "select": syntax error
sqlite> select half(7) 
   ...> 

half.c is below:

#include 
SQLITE_EXTENSION_INIT1

/*
** The half() SQL function returns half of its input value.
*/
static void halfFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}

/* SQLite invokes this routine once when it loads the extension.
** Create new functions, collating sequences, and virtual table
** modules here.  This is usually the only exported symbol in
** the shared library.
*/
int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
  SQLITE_EXTENSION_INIT2(pApi)
  sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
  return 0;
}




   
-
Inviato da Yahoo! Mail.
La casella di posta intelligente.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to disable journaling & rollback?

[Phil Sherrod]

Thank you for pointing out the omitJournal argument to sqlite3BtreeFactory!
That's just what I was looking for.

By forcing omitJournal to 1 in all cases (thereby always turning off
journaling), I was able to increase the total speed of my application by a
factor of 2.5 over the speed I got by just removing the call to
FlushFileBuffers (which by itself provided a speed-up factor of 20).

So by removing journaling, my application runs 50 times as fast as it does
with journaling enabled!

I don't know how much write performance matters to others, but increasing
the speed of an application by a factor of 50 is worth looking into.

Aladdin Lamp wrote:

In order to disable totally journaling, I think (to be confirmed by real
sqlite experts though) that you could patch each 4 calls to the function:
int sqlite3BtreeFactory(const sqlite3 *db, const char *zFilename, int
omitJournal, int nCache, int flags, Btree **ppBtree); using always 1 as the
third parameter (omitJournal) instead of 0 in the following files:
- attach.c (line 136)
- build.c (line 3212)
- main.c (line 1025)
(- and in vdbe.c (line 2611) but omitJournal argument already equals 1 in
this file)

Keep us informed if this works.
Aladdin


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] List Columns

[Fred J. Stephens]

Dennis Cote wrote:
> Fred J. Stephens wrote:
>> Is there a way to list the column names in a table from the SQLite 
>> command line interface? I know .schema  will show them, but the 
>> output seems like it would be difficult to parse so that only the column 
>> names are shown.
>>
> 
> See pragma table_info(table-name) at 
> http://www.sqlite.org/pragma.html#schema
Thanks Denis,
Thats just what I needed. I wasn't familiar with the pragma statements.
Fred Stephens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Quick Review/Reference

[Amit Uttamchandani]

On Sat, 12 Apr 2008 10:40:43 -0400
"P Kishor" <[EMAIL PROTECTED]> wrote:

> On 4/12/08, Amit Uttamchandani <[EMAIL PROTECTED]> wrote:
> > Hey everyone,
> >
> >  Just got back into the world of SQL after being away for 5 years. This 
> > time I decided to jump in with SQLite. It really has me excited.
> >
> >  Anyways, I have done a lot database work but I seem to have forgotten a 
> > lot of the subtleties of database designs/schema.
> >
> >  I am wondering if any of you have been in a similar situation? Is there a 
> > set of documents/notes that you refer to? Something that covers SQL and 
> > database design in general?
> >
> >  For example, for Python I refer to "A Byte of Python" and for LaTex there 
> > is "The Not So Short Introduction to LaTeX." Something similar for SQL is 
> > what I am looking for.
> >
> >  I've googled around but I thought why not ask the SQL veterans here?
> >
> 
> 
> nothing against the other suggestions, but have you really Googled?
> Lookee what I found
> 
> http://www.google.com/search?q=sql+tutorial
> 
> Honestly, there must be a bazillion SQL tutorials out there many of
> which provide quite a good intro to the basic concepts. Try a few of
> them.

Thanks for the reply.

Yes, I googled using similar search terms that you gave and found a lot of 
resources. I read up on them but I just wanted an opinion from some of the 
users here. I never owned a SQL reference material before...so some of the 
recommendations definitely helped a lot.

Amit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Quick Review/Reference

[Rich Shepard]

On Sat, 12 Apr 2008, Jay A. Kreibich wrote:

>  I purchased the hardback of this book some months ago only to discover
>  the PDF version some weeks later.  In the end, I ended up buying them
>  both.

Jay,

   I have both, too. Yes, using the find function within xpdf does compensate
for the lack of index and I keep the digital version on my notebook for use
when I'm traveling. I still refer to the paper edition frequently here in
the office.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Quick Review/Reference

[P Kishor]

On 4/12/08, Amit Uttamchandani <[EMAIL PROTECTED]> wrote:
> Hey everyone,
>
>  Just got back into the world of SQL after being away for 5 years. This time 
> I decided to jump in with SQLite. It really has me excited.
>
>  Anyways, I have done a lot database work but I seem to have forgotten a lot 
> of the subtleties of database designs/schema.
>
>  I am wondering if any of you have been in a similar situation? Is there a 
> set of documents/notes that you refer to? Something that covers SQL and 
> database design in general?
>
>  For example, for Python I refer to "A Byte of Python" and for LaTex there is 
> "The Not So Short Introduction to LaTeX." Something similar for SQL is what I 
> am looking for.
>
>  I've googled around but I thought why not ask the SQL veterans here?
>


nothing against the other suggestions, but have you really Googled?
Lookee what I found

http://www.google.com/search?q=sql+tutorial

Honestly, there must be a bazillion SQL tutorials out there many of
which provide quite a good intro to the basic concepts. Try a few of
them.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Quick Review/Reference

[Jay A. Kreibich]

On Sat, Apr 12, 2008 at 05:23:55PM +1000, Neville Franks scratched on the wall:
> For SQLite in particular "The Definitive Guide to SQLite" by Micahel
> Owens is reasonably good. Unfortunately it has the worst index of any
> book I can recall seeing, other than some Cook Books. This makes it
> very difficult and frustrating to use as a Reference Book.

  It is, however, available on the Apress website in PDF format for 30%
  off the cover price.  A good PDF viewer with listed search results is
  almost better than an index.

  I purchased the hardback of this book some months ago only to
  discover the PDF version some weeks later.  In the end, I ended up
  buying them both.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Quick Review/Reference

[Rich Shepard]

On Fri, 11 Apr 2008, Amit Uttamchandani wrote:

> Is there a set of documents/notes that you refer to? Something that covers
> SQL and database design in general?

Amit,

   I second the recommendation of Mike Owens' "The Definitive Guide to
SQLite" for detailed insight on this system. And it does have the worst
index of any technical book. (I wrote to the publisher about it but never
received any reply.)

   Now, for general information, nothing beats Rick F. van der Lans'
"Introduction to SQL, 4th Ed." He includes coverage of time-based database
applications (which applies to most business needs) which is not covered in
other books.

   Also, anything written by Joe Celko is worth reading and owning. The
latest edition of "SQL for Smarties" is very useful, as is his "SQL
Programming Style." He has a couple of other titles out, but they're not
applicable to my needs so I don't have them. Joe's been writing and teaching
about dbms for at least 25 years; I remember reading his columns in the mid-
to late-1980s.

HTH,

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] extension don't return value

[Igor Tandetnik]

"dark0s dark0s" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> The soundex function don't return results of your processing.
> My output is below:
> bash-3.1# gcc -shared labsinf.c -o inf.so
> bash-3.1# sqlite3
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> select load_extension('/root/inf.so')

You need to end the statement with a semicolon.

>   ...> select inf(savio)

This one too. "...>" is a continuation prompt - it means you are 
continuing the previous statement on the new line. This allows you to 
enter a large statement on multiple lines, but you have to eventually 
teminate the query with a semicolon, to let SQLite know you are done 
typing it in.

Also, your extension creates a function named "soundex", not "inf". Why 
do you expect inf() to work?

In addition, "savio" is an unknown identifier. You probably meant to 
pass a string literal, as in

select soundex('savio');

Note apostrophes around the literal, and the terminating semicolon.

> void soundex(sqlite3_context* ctx, int nargs, sqlite3_value** values)
> {
>  int i,j;
>  char c,r;
>  int d;
>  int count;
>  char* str2;
>  char* result;
>  int dim;
>  const char* str;
>  char ret[4];
>
>  str = sqlite3_value_text(values[0]);
>  dim = strlen(str);
>  for (i=0;i  for (i=0;i<=dim;i++) str2[i] = toupper(str2[i]);
>  for (i=0;i<=dim;i++)
>switch (str[i]) {

Curious: you have just capitalized the text in str2, but here you are 
still looking at the original text in str. Is this intentional?

>  result = malloc(count);
>  j=0;
>  for (i=0;iif (str2[i] != str2[i+1]) {
>  result[j]=str2[i];
>  j++;
>}
> //  for (i=0;i<4;i++) printf("%c", result[i]);
>  for (i=0;i<4;i++) ret[i] = result[i];

You copy the data into ret, but never use it afterwards.

>  sqlite3_result_text(ctx,(const char*)result, strlen(result),
> SQLITE_TRANSIENT);

You have not added terminating NUL character to result: strlen will 
either crash or return garbage. On the other hand, you already know the 
length of string in result - it's in the variable "count". Just use 
that.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] extension don't return value

[dark0s dark0s]

The soundex function don't return results of your processing.
My output is below:
bash-3.1# gcc -shared labsinf.c -o inf.so
bash-3.1# sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> select load_extension('/root/inf.so')
   ...> select inf(savio)
   ...> 

I don't understand where is the problem? in sqlite3_result_text function or in 
sqlite3_create_function

#include 
#include 
#include 
#include 
SQLITE_EXTENSION_INIT1

void soundex(sqlite3_context* ctx, int nargs, sqlite3_value** values) {

  int i,j;
  char c,r;
  int d;
  int count;
  char* str2;
  char* result;
  int dim;
  const char* str;
  char ret[4];
  
  str = sqlite3_value_text(values[0]);
  dim = strlen(str);
  for (i=0;i

Re: [sqlite] SQL Quick Review/Reference

[Neville Franks]

For SQLite in particular "The Definitive Guide to SQLite" by Micahel
Owens is reasonably good. Unfortunately it has the worst index of any
book I can recall seeing, other than some Cook Books. This makes it
very difficult and frustrating to use as a Reference Book.

I also purchased "Beginning Database Design - from Novice to
Professional" by Clare Churcher, but found it to much of a beginners
book for my needs.


Saturday, April 12, 2008, 3:23:10 PM, you wrote:

AU> Hey everyone,

AU> Just got back into the world of SQL after being away for 5
AU> years. This time I decided to jump in with SQLite. It really has
AU> me excited.

AU> Anyways, I have done a lot database work but I seem to have
AU> forgotten a lot of the subtleties of database designs/schema.

AU> I am wondering if any of you have been in a similar
AU> situation? Is there a set of documents/notes that you refer to?
AU> Something that covers SQL and database design in general?

AU> For example, for Python I refer to "A Byte of Python" and for
AU> LaTex there is "The Not So Short Introduction to LaTeX." Something
AU> similar for SQL is what I am looking for.

AU> I've googled around but I thought why not ask the SQL veterans here?

AU> Thanks,
AU> Amit
AU> ___
AU> sqlite-users mailing list
AU> sqlite-users@sqlite.org
AU> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema design question

[Jay A. Kreibich]

On Fri, Apr 11, 2008 at 01:54:43PM -0700, Richard Klein scratched on the wall:
> Jay A. Kreibich wrote:
> >On Thu, Apr 10, 2008 at 05:58:59PM -0700, Richard Klein scratched on the 
> >wall:
> >
> >>My advice would be to try it and see.  If table creation takes too long,
> >>you can always remove the UNIQUE constraint, and then write a routine to
> >>check the table for uniqueness after it's created.
> >
> >  That "routine" could simply be creating an explicit unique index on
> >  the column after all the data is loaded.  If the index can be
> >  created, you're good to go.
> >
> >  This is still a slow process, but I know the creation of indexes on
> >  existing tables is one area the development team hopes to speed up.
> >  I'm not sure what the priority of that is, however.
> 
> Wouldn't this take just as long as creating the index immediately after
> creating the table, and then letting each INSERT update the index?

  There are savings to be found when you have the whole set of indexes
  available to you, just as you get some savings from batching up
  INSERTs.  If you have a large enough page cache, you might be able to
  do a substantial part of the sort in one disk pass.  If the sorter has
  the whole set avaliable, there's also the issue of how you deal
  with disk management and what sorting algorithm is used.  Current
  thought is that there is room for improvement:

  http://www.mail-archive.com/sqlite-users@sqlite.org/msg31481.html

  Although that obviously doesn't help in the here and now.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users