Re: [sqlite] Confusing FAQ(26) wording

[Tobias Hoffmann]

>
> http://www.sqlite.org/nulls.html
>
> seems to clarify things for me on this topic.
>   
That definitely helps.
Perhaps someone could add this link in the FAQ(26)?

Thanks Pavel and Adam.

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

Re: [sqlite] Bug

[Wiktor Adamski]

> Already fixed.  Seehttp://www.sqlite.org/src/info/f74beaabde

I'm might be wrong because I didn't check with the newest binary, but
if I remember correctly this fix will compare t3.a to t1.a (instead of
t2.a). So it will produce different results (first select will be 1|2
and second will be empty) but still incorrect because error should be
returned.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG Report -- schema.test does not check for authorization in build

[Shane Harrelson]

Thanks for the reports on the TCL test cases.  I think
http://www.sqlite.org/src/vinfo/97f8a886b6 should correct everything you
reported.

On Tue, Dec 29, 2009 at 1:02 PM, Noah Hart  wrote:

> Test schema-13.1 fails with
> Error: {authorization not available in this build}
>
> Test needs to be bracket with
> ifcapable auth {
>
> do_test schema-13.1 {
>  set S [sqlite3_prepare_v2 db "SELECT * FROM sqlite_master" -1 dummy]
>  db function hello hello
>  db function hello {}
>  db auth auth
>
>
> db auth fails because tclsqlite.c has
>
> #ifdef SQLITE_OMIT_AUTHORIZATION
>Tcl_AppendResult(interp, "authorization not available in this
> build", 0);
>return TCL_ERROR;
> #else
>
>
>
> Regards,
>
> Noah Hart
>
>
>
> CONFIDENTIALITY NOTICE:
> This message may contain confidential and/or privileged information. If you
> are not the addressee or authorized to receive this for the addressee, you
> must not use, copy, disclose, or take any action based on this message or
> any information herein. If you have received this message in error, please
> advise the sender immediately by reply e-mail and delete this message. Thank
> you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selective result columns

[Roger Andersson]

> -Ursprungligt meddelande-
> Från: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] För nomorecaddy
> Skickat: den 31 december 2009 17:44
> Till: sqlite-users@sqlite.org
> Ämne: Re: [sqlite] selective result columns
> 
> 
> I'm operating at the user level in a java application.  The 
> application allows me to run an SQL query, then it renders 
> the output into HTML.  I want to avoid showing empty colums 
> in the HTML output, so I wanted the include/exclude column 
> logic in my actual SQL statement.  
> 
It might not be possible for you but still.
Create the "actual SQL" with something like below, assuming that f1 never is
NULL
select distinct 'select f1'|| case when f2 is null then '' else ',f2' end||
case when f3 is null then '' else ',f3'|| ' from t1;' end from t1;
And then use the result to query.

If there are some records where f2 and/or f3 is NULL and others where one or
both isn't you will get 2 or maybe even 3 lines from the above SQL.

/Roger

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

Re: [sqlite] Mozilla's method

[Bert Nelsen]

Hello Olaf!
Thank you for your reply. I am using your wrapperlib (and I love it) but I
thought it was a more general question and had nothing directly to do with
your wrapperlib. But I am not sure anymore what is yours and what is common
Sqlite.
Your .Sychronous = False property does everything as fast as I want, and I
am not afraid of losing some user data (it's not a critical application) but
I am very much afraid of having a corrupted db. Can anybody please confirm
that there is no chance of getting my db corrupted?

On Thu, Dec 31, 2009 at 6:11 PM, Olaf Schmidt  wrote:

>
> "Bert Nelsen"  schrieb
> im Newsbeitrag
> news:a5ffd530912310853t7024d908tefbf1ef40df47...@mail.gmail.com...
>
> > I would like to make writes to my SQLite db faster.
> In what regard (under which circumstances) is it slow
> currently?
>
> What's your typical recordcount per transaction?
> Do you use transactions at all?
> How many indexes are defined on the "slow tables"?
> Are you using Command-Objects (the sqlite-bind API)?
> In what language is the App-Host written (regarding
> easy "thread-control")?
> Do you work through a wrapper yet, or with the SQLite-lib
> directly?
>
> > I was thinking about the Async method, but I think I
> > remember reading somewhere that it may cause database
> > corruption.
> IMO the async-feature was not risky with regards to data-
> corruption, only with regards to durability - an (uncorrupted)
> DB could contain "lesser data" (not the last "version", which
> your successfully reported transactions suggested earlier) -
> in case of e.g. a powerfailure.
> But your "mozilla-comment-snippet" already mentioned that too.
>
> I remember, that at some point in time you were using my
> COM-wrapper, to work with SQLite. And I was thinking
> about "handing out" the async-functionality over an appropriate
> method, as the feature came up - but then stayed away from
> it, for the sake of higher stability. The COM-wrapper is
> mostly used in conjunction with VB5/6 - and there you have
> not that much control over the teardown-process of an App
> like in other, more "bare to the metal" languages - imagine
> the async SQLite-WriterThread, having "yet some stuff
> in the pipe" - and a closing VB-App, which only "knows"
> the COM-Object - and not the SQLite-lib behind it, which
> "owns" the async thread.
>
> So, just in case you're using the dhRichClient3-wrapper-lib,
> there's currently no plan (and no time), to make that feature
> available in a reliable and stable working way (playing well
> with VB5/6) over the COM-interface.
>
> Olaf Schmidt
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] mode is not changed

[Simon Slavin]

On 31 Dec 2009, at 4:56pm, Kees Nuyt wrote:

> On Thu, 31 Dec 2009 04:24:51 +, Simon Slavin
>  wrote:
> 
>> On 31 Dec 2009, at 2:13am, Wensui Liu wrote:
>> 
>>> i am trying to change the output by 'sqlite3 mydb ".mode csv"'.
>>> however, when i type 'sqlite3 mydb .show', I couldn't see the change
>>> of mode at all.
>> 
>> Each new 'sqlite3' command starts a new instance of the application, and the 
>> new instance starts with all settings set to the defaults.
> 
> Correct.
> 
> Additionally, if Wensui Liu wants to have his own defaults,
> he can put a   .sqliterc   file in his home directory

How would I string two commands together in the command-line ?  Is there a way 
to do something like

sqlite3 mydb ".mode csv.show"

?

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

Re: [sqlite] Mozilla's method

[Shawn Wilsher]

On Thu, Dec 31, 2009 at 9:06 AM, Bert Nelsen wrote:

> It's here:
>
> https://developer.mozilla.org/en/Storage:Performance
>
Thanks.  I've removed the outdated information.

Cheers,

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

Re: [sqlite] selective result columns

[Pavel Ivanov]

> I'm operating at the user level in a java application.  The application
> allows me to run an SQL query, then it renders the output into HTML.

OK. Some third-party java application allows you to execute SQL query
and show you result as HTML. Then you take this HTML and show it to
somebody else? If not then it's not a big deal to just skip empty
columns with your eyes. If yes then you have to edit HTML as you wish.
I bet simple deleting of empty cells in the result table inside HTML
will not provide you the result you want...

We still don't see the whole picture of what you want to achieve. But
anyway there's no way you can include/exclude columns on SQL level so
you have to look for a way to get what you want at some higher level.

Pavel

On Thu, Dec 31, 2009 at 11:43 AM, nomorecaddy  wrote:
>
> I'm operating at the user level in a java application.  The application
> allows me to run an SQL query, then it renders the output into HTML.  I want
> to avoid showing empty colums in the HTML output, so I wanted the
> include/exclude column logic in my actual SQL statement.
>
>
>
>
> Pavel Ivanov-2 wrote:
>>
>>> I don't have access to that level of software, so that's the problem.
>>
>> You mean your experience with SQLite is based on using sqlite3 command
>> line utility only? If so you can just redirect its output to sed which
>> will do something like s/|\+/|/g.
>> If your experience with SQLite is based on using some other kind of
>> software then you cannot do anything with it - it will inevitably rely
>> on the fact that each row has the same number of columns and it will
>> show you all of these columns...
>>
>> In any case as you probably have understood already you better to tell
>> us what you're trying to achieve in general and what software you're
>> working with so that we could provide a better general advice that
>> doesn't involves such action as "removing columns without useful
>> information from result set"...
>>
>>
>> Pavel
>>
>> On Tue, Dec 29, 2009 at 4:34 PM, nomorecaddy 
>> wrote:
>>>
>>> I don't have access to that level of software, so that's the problem.
>>>  Thanks
>>> for your response - I like the power of select case, and was hoping that
>>> case could be applied in other areas as well.
>>>
>>>
>>>
>>> Simon Slavin-3 wrote:


 On 29 Dec 2009, at 8:34pm, nomorecaddy wrote:

> I'm looking for a SQL query that returns a variable number of
> columns.  Many of my columns contain NULL data, and I want to avoid
> showing
> the column altogether in that case.

 That is something that must be handled by your software.  There is no
 way
 in SQL to return an answer to a SELECT which has a different number of
 columns in different records.  You could make SQL return columns with
 NULL
 in and your software could automatically recognise them and know not to
 print them.

 Remember that SQL is a database engine.  It's job is to supply data.
  It's
 your software's job to understand what needs to be done with it.

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


>>>
>>> --
>>> View this message in context:
>>> http://old.nabble.com/selective-result-columns-tp26958131p26960765.html
>>> Sent from the SQLite mailing list archive at Nabble.com.
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: 
> http://old.nabble.com/selective-result-columns-tp26958131p26980075.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Olaf Schmidt]

"Bert Nelsen"  schrieb
im Newsbeitrag
news:a5ffd530912310853t7024d908tefbf1ef40df47...@mail.gmail.com...

> I would like to make writes to my SQLite db faster.
In what regard (under which circumstances) is it slow
currently?

What's your typical recordcount per transaction?
Do you use transactions at all?
How many indexes are defined on the "slow tables"?
Are you using Command-Objects (the sqlite-bind API)?
In what language is the App-Host written (regarding
easy "thread-control")?
Do you work through a wrapper yet, or with the SQLite-lib
directly?

> I was thinking about the Async method, but I think I
> remember reading somewhere that it may cause database
> corruption.
IMO the async-feature was not risky with regards to data-
corruption, only with regards to durability - an (uncorrupted)
DB could contain "lesser data" (not the last "version", which
your successfully reported transactions suggested earlier) -
in case of e.g. a powerfailure.
But your "mozilla-comment-snippet" already mentioned that too.

I remember, that at some point in time you were using my
COM-wrapper, to work with SQLite. And I was thinking
about "handing out" the async-functionality over an appropriate
method, as the feature came up - but then stayed away from
it, for the sake of higher stability. The COM-wrapper is
mostly used in conjunction with VB5/6 - and there you have
not that much control over the teardown-process of an App
like in other, more "bare to the metal" languages - imagine
the async SQLite-WriterThread, having "yet some stuff
in the pipe" - and a closing VB-App, which only "knows"
the COM-Object - and not the SQLite-lib behind it, which
"owns" the async thread.

So, just in case you're using the dhRichClient3-wrapper-lib,
there's currently no plan (and no time), to make that feature
available in a reliable and stable working way (playing well
with VB5/6) over the COM-interface.

Olaf Schmidt



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

Re: [sqlite] [sqlite-dev] Sqlite Query Escaping

[Kees Nuyt]

On Thu, 31 Dec 2009 10:19:24 -0500, Tim wrote:

> I have the need to prepare some strings for including
> into sqlite, I have not found an escape function, but I
> was wondering if anyone had any comments about the
> following code snippet. I had read on the site
> sqlite.org web site that ' should pretty much be the
> only problem character and that \ escaping doesn't
> "work" as might be expected. This function is basically
> just to make sure that any quotes in text fields still
> appear correctly. Is there anything else I might need?

> Thanks,
> Tim

Your question is more on topic in the sqlite-users mailing
list, sqlite-dev focusses on development of sqlite itself.
I forwarded your mail to sqlite-users.

In short: if you use the sqlite3_prepare*() and
sqlite3_bind*() interface you don't have to escape single
quotes. Only if you compose a complete SQL statement for the
deprecated sqlite3_exec() call and using the sqlite3 command
line tool, where single quotes are used for text literals,
you'd have to worry about escaping single quotes, which, by
the way can be done by just doubling it:
INSERT INTO person (name) VALUES ('O''Reilly');


> #include 
> #include 
> #include 
> static char * sqlitedrv_escape_string(const char *instr) ;
> static char * sqlitedrv_unescape_string(const char * instr) ;
> int main ( ){
>   char * sqlite = "this is a test' this is after''\n" ;
>   char * out = sqlitedrv_escape_string ( sqlite ) ;
>   char * back = NULL ;
>   printf ( sqlite ) ;
>   if ( out ){
>   printf ( out ) ;
>   back = sqlitedrv_unescape_string ( out ) ;
>   if ( back ){
>   printf ( back ) ;
>   if ( strcmp ( sqlite, back ) == 0 ){
>   printf ( "They are the same\n" );
>   }else{
>   printf ( "They are NOT the same\n" );
>   }
>   free ( back ) ;
>   }
>   free ( out ) ;
>   }else{
>   printf ( "Failed to escape\n" ) ;
>   }
>   return 0 ;
> }
> static char * sqlitedrv_unescape_string(const char * instr){
>   char *outstr;
>   char * ptr ;
>   unsigned int len;
>   unsigned int tlen = 0 ;
>   unsigned int atlen = 0 ;
>   unsigned int i = 0 ;
>   if (instr == NULL)
>   return NULL;
> 
>   len = strlen(instr);
>   atlen = (len * sizeof(char)) + 1 ;
>   outstr = ( char * ) malloc(atlen);
>   if (outstr == NULL)
>   return NULL;
>   ptr = outstr ;
>   for ( i = 0 ; i < len ; i++ ){
>   if ( tlen >= atlen ){
>   break ;
>   }
>   if ( instr[i] == '\'' && instr[i+1] == '\'' ){
>   i++;
>   tlen ++ ;
>   }
>   *ptr = instr[i] ;
>   ptr++ ;
>   tlen ++ ;
>   if ( instr=='\0' ){
>   break ;
>   }
>   }
>   *ptr = '\0' ;
>   return outstr;
> }
> 
> static char * sqlitedrv_escape_string(const char *instr) {
>   char *outstr;
>   char * ptr ;
>   unsigned int len;
>   unsigned int tlen = 0 ;
>   unsigned int atlen = 0 ;
>   unsigned int i = 0 ;
>   if (instr == NULL)
>   return NULL;
>   len = strlen(instr);
>   atlen = (2 * len * sizeof(char)) + 1 ;
>   outstr = ( char * ) malloc(atlen);
>   if (outstr == NULL)
>   return NULL;
>   ptr = outstr ;
>   for ( i = 0 ; i < len ; i++ ){
>   if ( tlen >= atlen ){
>   break ;
>   }
>   if ( instr[i] == '\'' ){
>   *ptr = '\'' ;
>   ptr++ ;
>   tlen ++ ;
>   }
>   *ptr = instr[i] ;
>   ptr++ ;
>   tlen ++ ;
>   if ( instr=='\0' ){
>   break ;
>   }
>   }
>   *ptr = '\0' ;
>   return outstr;
> }
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Bert Nelsen]

It's here:

https://developer.mozilla.org/en/Storage:Performance

On Thu, Dec 31, 2009 at 6:04 PM, Shawn Wilsher  wrote:

> Where did you see this?  It's inaccurate, and if I can, I'll remove it.  If
> you want to use a proper asynchronous statement execution which wraps your
> statement[s] in a transaction, you want to use this:
> https://developer.mozilla.org/En/Storage#Asynchronously
>
> Cheers,
>
> Shawn Wilsher
> Mozilla Developer
>
> On Thu, Dec 31, 2009 at 8:53 AM, Bert Nelsen  >wrote:
>
> > Hello,
> >
> > I would like to make writes to my SQLite db faster.
> >
> > I was thinking about the Async method, but I think I remember reading
> > somewhere that it may cause database corruption.
> >
> > Now I read something on the Mozilla pages, and I don't understand what
> > exactely they are doing.
> >
> > Do they bundle everything in transactions only or do they in fact use the
> > Async method?
> > It is not clear to me by reading through their articles.
> >
> > It would be nice if somebody could clear me up on this issue.
> >
> > Here is the article:
> >
> > Lazy writing
> >
> > Mozilla has relaxed the ACID requirements in order to speed up commits.
> In
> > particular, we have dropped durability. This means that when a commit
> > returns, you are not guaranteed that the commit has gone through. If the
> > power goes out right away, that commit may (or may not) be lost. However,
> > we
> > still support the other (ACI) requirements. This means that the database
> > will not get corrupted. If the power goes out immediately after a commit,
> > the transaction will be like it was rolled back: the database will still
> be
> > in a consistent state.
> >
> > Higher commit performance is achieved by writing to the database from a
> > separate thread (see
> > storage/src/mozStorageAsyncIO.cpp<
> >
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageAsyncIO.cpp
> > >
> > which
> > is associated with the storage service in
> > storage/src/mozStorageService.cpp<
> >
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageService.cpp
> > >
> > ).
> > The main database thread does everything exactly as it did before.
> However,
> > we have overridden the file operations and everything comes through the
> > AsnycIO module. This file is based on
> > test_async.c<
> http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/test_async.c
> > >from
> > the sqlite distribution.
> >
> > The AsyncIO module packages writes up in messages and puts them on the
> > write
> > thread's message queue. This write thread waits for messages and
> processes
> > them as fast as it can. This means that writes, locking, and most
> > importantly, disk syncs, only block the AsyncIO thread. Reads are done
> > synchronously, taking into account unwritten data still in the buffer.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Mozilla's method

[Shawn Wilsher]

Where did you see this?  It's inaccurate, and if I can, I'll remove it.  If
you want to use a proper asynchronous statement execution which wraps your
statement[s] in a transaction, you want to use this:
https://developer.mozilla.org/En/Storage#Asynchronously

Cheers,

Shawn Wilsher
Mozilla Developer

On Thu, Dec 31, 2009 at 8:53 AM, Bert Nelsen wrote:

> Hello,
>
> I would like to make writes to my SQLite db faster.
>
> I was thinking about the Async method, but I think I remember reading
> somewhere that it may cause database corruption.
>
> Now I read something on the Mozilla pages, and I don't understand what
> exactely they are doing.
>
> Do they bundle everything in transactions only or do they in fact use the
> Async method?
> It is not clear to me by reading through their articles.
>
> It would be nice if somebody could clear me up on this issue.
>
> Here is the article:
>
> Lazy writing
>
> Mozilla has relaxed the ACID requirements in order to speed up commits. In
> particular, we have dropped durability. This means that when a commit
> returns, you are not guaranteed that the commit has gone through. If the
> power goes out right away, that commit may (or may not) be lost. However,
> we
> still support the other (ACI) requirements. This means that the database
> will not get corrupted. If the power goes out immediately after a commit,
> the transaction will be like it was rolled back: the database will still be
> in a consistent state.
>
> Higher commit performance is achieved by writing to the database from a
> separate thread (see
> storage/src/mozStorageAsyncIO.cpp<
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageAsyncIO.cpp
> >
> which
> is associated with the storage service in
> storage/src/mozStorageService.cpp<
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageService.cpp
> >
> ).
> The main database thread does everything exactly as it did before. However,
> we have overridden the file operations and everything comes through the
> AsnycIO module. This file is based on
> test_async.c >from
> the sqlite distribution.
>
> The AsyncIO module packages writes up in messages and puts them on the
> write
> thread's message queue. This write thread waits for messages and processes
> them as fast as it can. This means that writes, locking, and most
> importantly, disk syncs, only block the AsyncIO thread. Reads are done
> synchronously, taking into account unwritten data still in the buffer.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] mode is not changed

[Kees Nuyt]

On Thu, 31 Dec 2009 04:24:51 +, Simon Slavin
 wrote:

>
>On 31 Dec 2009, at 2:13am, Wensui Liu wrote:
>
>> i am trying to change the output by 'sqlite3 mydb ".mode csv"'.
>> however, when i type 'sqlite3 mydb .show', I couldn't see the change
>> of mode at all.
>
>Each new 'sqlite3' command starts a new instance of the application, and the 
>new instance starts with all settings set to the defaults.

Correct.

Additionally, if Wensui Liu wants to have his own defaults,
he can put a   .sqliterc   file in his home directory with
the commands he wants to have executed every time he starts
the sqlite3 command line tool. This works in Unix, Linux and
Windows.

Unix and Linux use ~/.sqliterc, Windows tries to find the
location of .sqliterc using the environment variables
USERPROFILE HOME HOMEDRIVE and HOMEPATH, on modern versions
USERPROFILE is the most probable to be used.

>Simon.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mozilla's method

[Bert Nelsen]

Hello,

I would like to make writes to my SQLite db faster.

I was thinking about the Async method, but I think I remember reading
somewhere that it may cause database corruption.

Now I read something on the Mozilla pages, and I don't understand what
exactely they are doing.

Do they bundle everything in transactions only or do they in fact use the
Async method?
It is not clear to me by reading through their articles.

It would be nice if somebody could clear me up on this issue.

Here is the article:

Lazy writing

Mozilla has relaxed the ACID requirements in order to speed up commits. In
particular, we have dropped durability. This means that when a commit
returns, you are not guaranteed that the commit has gone through. If the
power goes out right away, that commit may (or may not) be lost. However, we
still support the other (ACI) requirements. This means that the database
will not get corrupted. If the power goes out immediately after a commit,
the transaction will be like it was rolled back: the database will still be
in a consistent state.

Higher commit performance is achieved by writing to the database from a
separate thread (see
storage/src/mozStorageAsyncIO.cpp
which
is associated with the storage service in
storage/src/mozStorageService.cpp
).
The main database thread does everything exactly as it did before. However,
we have overridden the file operations and everything comes through the
AsnycIO module. This file is based on
test_async.cfrom
the sqlite distribution.

The AsyncIO module packages writes up in messages and puts them on the write
thread's message queue. This write thread waits for messages and processes
them as fast as it can. This means that writes, locking, and most
importantly, disk syncs, only block the AsyncIO thread. Reads are done
synchronously, taking into account unwritten data still in the buffer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selective result columns

[nomorecaddy]

I'm operating at the user level in a java application.  The application
allows me to run an SQL query, then it renders the output into HTML.  I want
to avoid showing empty colums in the HTML output, so I wanted the
include/exclude column logic in my actual SQL statement.  




Pavel Ivanov-2 wrote:
> 
>> I don't have access to that level of software, so that's the problem.
> 
> You mean your experience with SQLite is based on using sqlite3 command
> line utility only? If so you can just redirect its output to sed which
> will do something like s/|\+/|/g.
> If your experience with SQLite is based on using some other kind of
> software then you cannot do anything with it - it will inevitably rely
> on the fact that each row has the same number of columns and it will
> show you all of these columns...
> 
> In any case as you probably have understood already you better to tell
> us what you're trying to achieve in general and what software you're
> working with so that we could provide a better general advice that
> doesn't involves such action as "removing columns without useful
> information from result set"...
> 
> 
> Pavel
> 
> On Tue, Dec 29, 2009 at 4:34 PM, nomorecaddy 
> wrote:
>>
>> I don't have access to that level of software, so that's the problem.
>>  Thanks
>> for your response - I like the power of select case, and was hoping that
>> case could be applied in other areas as well.
>>
>>
>>
>> Simon Slavin-3 wrote:
>>>
>>>
>>> On 29 Dec 2009, at 8:34pm, nomorecaddy wrote:
>>>
 I'm looking for a SQL query that returns a variable number of
 columns.  Many of my columns contain NULL data, and I want to avoid
 showing
 the column altogether in that case.
>>>
>>> That is something that must be handled by your software.  There is no
>>> way
>>> in SQL to return an answer to a SELECT which has a different number of
>>> columns in different records.  You could make SQL return columns with
>>> NULL
>>> in and your software could automatically recognise them and know not to
>>> print them.
>>>
>>> Remember that SQL is a database engine.  It's job is to supply data.
>>>  It's
>>> your software's job to understand what needs to be done with it.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/selective-result-columns-tp26958131p26960765.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/selective-result-columns-tp26958131p26980075.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Bug

[D. Richard Hipp]

On Dec 31, 2009, at 6:27 AM, Wiktor Adamski wrote:

> SQLite version 3.6.21
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t1(a int);
> sqlite> create table t2(a int);
> sqlite> create table t3(a int);
> sqlite> insert into t1 values(1);
> sqlite> insert into t2 values(2);
> sqlite> insert into t3 values(1);
> sqlite> select * from t1 join t2 on t1.a < t2.a join t3 using(a);
> sqlite> update t3 set a = 2;
> sqlite> select * from t1 join t2 on t1.a < t2.a join t3 using(a);
> 1|2
>
> First join produces 2 columns named a so result of the query should by
> similar to:
> MySQL: Column 'a' in from clause is ambiguous
> PostgreSQL: ERROR: common column name "a" appears more than once in
> left table

Already fixed.  See http://www.sqlite.org/src/info/f74beaabde

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

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Bug

[Wiktor Adamski]

SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t1(a int);
sqlite> create table t2(a int);
sqlite> create table t3(a int);
sqlite> insert into t1 values(1);
sqlite> insert into t2 values(2);
sqlite> insert into t3 values(1);
sqlite> select * from t1 join t2 on t1.a < t2.a join t3 using(a);
sqlite> update t3 set a = 2;
sqlite> select * from t1 join t2 on t1.a < t2.a join t3 using(a);
1|2

First join produces 2 columns named a so result of the query should by
similar to:
MySQL: Column 'a' in from clause is ambiguous
PostgreSQL: ERROR: common column name "a" appears more than once in
left table
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined reference to `readline'

[Angelo]

Hi,

you put me on the right direction and I solved. Thank you veru much !!!

Hoping to help other sqlite users with the same problem, I give here a list of 
what I did to solve.

=> I checked if readline was installed and answer was not
=> downloaded readline.tar.gz (vers 6) and installed
=> re-run configure and still problems complaining it was not able to find
tgetnum, tgoto togetflags and so on
=> downloaded ncurses.tar.gz (vers 5.7) and installed
=> re-run configure and no complaint,
=> make and make install and now history works quite fine



Happy new year to everybody
Angelo




On 29/12/2009 19:41, Lutz Horn wrote:
> Hi,
> 
> Am 29.12.09 19:28, schrieb Angelo:
>> sqlite-3.6.16/conftest.c:37: undefined reference to `readline'
>> collect2: ld returned 1 exit status.
>>
>> So it's clear, the linker does not find the readline function , but I don't 
>> understand why.
> 
> Do you have the readline library including headers installed?
> 
> Lutz
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users