Re: [sqlite] Accessing sqlite using javascript

[Saurabh Pawar]

This is the best link i have found out till now which will give a very clear 
view.

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

It helped me a lot.Hope it helps you too.



- Original Message 
From: Saurabh Pawar 
To: sql...@surfulater.com; General Discussion of SQLite Database 

Sent: Tuesday, 19 May, 2009 8:53:52 AM
Subject: Re: [sqlite] Accessing sqlite using javascript


I tried both the options.EXTJS lib is for firefox 1.5+.So it wont work.

The snippet is giving an error --
Permission denied to get property XPCComponents.classes

Are you aware of this error?.

Thank you.



- Original Message 
From: Neville Franks 
To: sqlite-users@sqlite.org
Sent: Monday, 18 May, 2009 2:16:52 PM
Subject: Re: [sqlite] Accessing sqlite using javascript

Monday, May 18, 2009, 3:16:45 PM, you wrote:

SP> I had asked the same question a few days back,but need a some more help.
SP> I am having my whole SQLite database on client's machine.I want a
SP> way to access that db using javscript.I heard about gears,but the
SP> problem is my target PC has firefox 1.08 and gears is for 1.5+.So
SP> is there any other way around?...Also is there any tutorial or
SP> guide which i can have online for referring when i am writing the code?.

SP> Thank you.

There are several ways to do this. From Firefox see:
http://codesnippets.joyent..com/posts/show/1030

The ExtJS Library also provides access to SQLite, but I have not used
that part of ExtJS yet.

Finally Google: "sqlite from javascript".

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



  Explore and discover exciting holidays and getaways with Yahoo! India 
Travel http://in.travel.yahoo.com/

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



  Cricket on your mind? Visit the ultimate cricket website. Enter 
http://beta.cricket.yahoo.com

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

Re: [sqlite] Sqlite as a FIFO buffer?

[Dennis Cote]

Allen Fowler wrote:
> I have several CGI and cron scripts and that I would like coordinate via a 
> "First In
> / First Out" style buffer.That is, some processes are adding work
> units, and some take the oldest and start work on them.
>
> Could SQLite be used for this?  
>
> It would seem very complex to use SQL for just a FIFO, but then again, SQLite 
> would take acre of all ACID / concurrency issues.
>
> Has this been done before?
>
>   
You can use simple triggers to manage a fifo in a table.

See http://www.mail-archive.com/sqlite-users@sqlite.org/msg12121.html 
for an example.

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

Re: [sqlite] most efficient way to get 1st row

[Sam Carleton]

Kees Nuyt wrote:

Imagine a SELECT with an ORDER BY which makes SQLite sort
the resultset before it can return the first row in the
resultset. Need I say more?

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

Thank you for the link, it is a VERY useful read, VERY useful!

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

Re: [sqlite] most efficient way to get 1st row

[Kees Nuyt]

On Tue, 19 May 2009 11:26:31 -0400, Sam Carleton
 wrote:

>Marco Bambini wrote:
>> SELECT ... LIMIT 1;
>>   
>Marco,  Is this to say that adding the LIMIT 1 does make it more efficient?

Not necessarily.

Imagine a SELECT with an ORDER BY which makes SQLite sort
the resultset before it can return the first row in the
resultset. Need I say more?

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] most efficient way to get 1st row

[Simon Slavin]

On 19 May 2009, at 4:26pm, Sam Carleton wrote:

> Marco Bambini wrote:
>> SELECT ... LIMIT 1;
>>
> Marco,  Is this to say that adding the LIMIT 1 does make it more  
> efficient?

LIMIT does not make the query (much) more efficient: the software  
still needs to do the same filtering and sorting, it just returns a  
shorter table.  However, LIMIT does reduce the amount of memory (or  
possibly disk space) that the result table takes up, until you dispose  
of it, since the results are limited to one row.

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

Re: [sqlite] Sqlite as a FIFO buffer?

[Christopher Taylor]

--
From: "Douglas E. Fajardo" 
Sent: Tuesday, May 19, 2009 11:57 AM
To: "'General Discussion of SQLite Database'" 
Subject: Re: [sqlite] Sqlite as a FIFO buffer?

> One-per-second sounds *very* slow - I think I was getting around 10 per 
> second in my application, although usage patterns may account for the 
> difference.
>
> The main cause of the slowdown (IMHO) is inherent in ensuring that changes 
> are written to physical disk. There are some pragmas that change the 
> behavior of SQLITE in this respect, but performance improvements in this 
> area are at some (finite) expense in data integrity. The only other 
> solution for this might be fast disk drives or a solid-state disk.
>
> There is also the issue of attempting to synchronize writes to two (or 
> more) different tasks. My own implementation was pretty primitive - it 
> simply used the transaction controls in SQLITE. This approach worked, but 
> I believe that a semaphore or other (external to the SQL language) 
> signaling mechanism would be a more efficient means of synchronizing 
> database access between tasks.
>
> *** Doug F.
>
>

The slowdown on my end is due to this being an embedded application with 
little RAM, a slow processor, and very slow flash.

Chris 

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

Re: [sqlite] error in documentation of SELECT?

[Doug Currie]

On May 19, 2009, at 10:05 AM, Jean-Denis Muys wrote:

> On 5/19/09 2:44 PM, "Igor Tandetnik"  wrote:
>>
>> Wikipedia gives a definition different from yours, for what it's  
>> worth:
>>
>> http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers
>
> Also to support my version, the same article says a bit later:
>
> " Usually, in number theory, we always choose the positive remainder".
>
> While programming languages seems to make rather different choices:
>
> "C99 and Pascal choose the remainder with the same sign as the  
> dividend a.
> (Before C99, the C language allowed either choice.) Perl and Python  
> choose
> the remainder with the same sign as the divisor d."

For those with ACM digital library access, the three options are  
defined and analyzed in some detail in the paper "The Euclidean  
definition of the functions div and mod" by Raymond T. Boute,  
University of Nijmegen, ACM Transactions on Programming Languages and  
Systems (TOPLAS) Volume 14, Issue 2  (April 1992), Pages: 127 - 144,
Year of Publication: 1992, ISSN:0164-0925

http://portal.acm.org/citation.cfm?doid=128861.128862

e

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

Re: [sqlite] Sqlite as a FIFO buffer?

[Douglas E. Fajardo]

One-per-second sounds *very* slow - I think I was getting around 10 per second 
in my application, although usage patterns may account for the difference.

The main cause of the slowdown (IMHO) is inherent in ensuring that changes are 
written to physical disk. There are some pragmas that change the behavior of 
SQLITE in this respect, but performance improvements in this area are at some 
(finite) expense in data integrity. The only other solution for this might be 
fast disk drives or a solid-state disk.
 
There is also the issue of attempting to synchronize writes to two (or more) 
different tasks. My own implementation was pretty primitive - it simply used 
the transaction controls in SQLITE. This approach worked, but I believe that a 
semaphore or other (external to the SQL language) signaling mechanism would be 
a more efficient means of synchronizing database access between tasks.

*** Doug F.


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Christopher Taylor
Sent: Monday, May 18, 2009 9:36 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite as a FIFO buffer?

--
From: "Douglas E. Fajardo" 
Sent: Monday, May 18, 2009 12:25 PM
To: "General Discussion of SQLite Database" ; 
"AllenFowler" 
Subject: Re: [sqlite] Sqlite as a FIFO buffer?

> One thing to watch out for - using SQLITE for a FIFO will have limited 
> throughput, because commits will have to be done after inserting or 
> removing each entry. This might not be an issue in some situations - I 
> have actually implemented a FIFO to communicate between two tasks where 
> the work-per-entry was significant, the transaction rate was low, and the 
> protection against accidental loss was paramount.
>
> *** Doug F.
>
>
I have seen this.  I have two other tables where the inserts can take up to 
1 second to do.  For my application this is acceptable.  The events 
themselves are queued and there is enough idle time between jobs to catch up 
if needed.  Is there a better way to do this?

Chris 

___
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] most efficient way to get 1st row

[Sam Carleton]

Marco Bambini wrote:

SELECT ... LIMIT 1;
  

Marco,  Is this to say that adding the LIMIT 1 does make it more efficient?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] most efficient way to get 1st row

[Marco Bambini]

SELECT ... LIMIT 1;

--  
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





On May 19, 2009, at 5:03 PM, Sam Carleton wrote:

> I am far from a SQL expert, but I am 99.9% sure there is SQL syntax  
> to limit
> the number of results, I have not looked it up but I will in a little
> while...
>
> I only need one result.  Since I am working with the C/C++ API, I  
> plan to
> simply call sqlite3_step() only once.
>
> Is there any point in using the SQL syntax to limit it to one?   
> Would the
> syntax slow the process down because it simply isn't needed or will  
> it speed
> it up because of all the wonderful internal things going on?
> ___
> 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

[sqlite] most efficient way to get 1st row

[Sam Carleton]

I am far from a SQL expert, but I am 99.9% sure there is SQL syntax to limit
the number of results, I have not looked it up but I will in a little
while...

I only need one result.  Since I am working with the C/C++ API, I plan to
simply call sqlite3_step() only once.

Is there any point in using the SQL syntax to limit it to one?  Would the
syntax slow the process down because it simply isn't needed or will it speed
it up because of all the wonderful internal things going on?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in documentation of SELECT?

[Jean-Denis Muys]

On 5/19/09 2:44 PM, "Igor Tandetnik"  wrote:
> 
> Wikipedia gives a definition different from yours, for what it's worth:
> 
> http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers

Also to support my version, the same article says a bit later:

" Usually, in number theory, we always choose the positive remainder".

While programming languages seems to make rather different choices:

"C99 and Pascal choose the remainder with the same sign as the dividend a.
(Before C99, the C language allowed either choice.) Perl and Python choose
the remainder with the same sign as the divisor d."

Rejoice !

Jean-Denis

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

Re: [sqlite] error in documentation of SELECT?

[Jean-Denis Muys]

On 5/19/09 2:44 PM, "Igor Tandetnik"  wrote:
> 
> Well then, for the equality to hold, (-1)/7 should be -1. Would you be
> happy with such an outcome?
> 

Yep

> Wikipedia gives a definition different from yours, for what it's worth:
> 
> http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers
> http://en.wikipedia.org/wiki/Modulo_operation

Well, I contend it doesn't. It says:

"−42 = 9×(−5) + 3 as is usual for mathematicians"

Which was my point.

But as was pointed out, there are two choices, both equally valid.

> 
> Once you decide that division truncates towards zero (as is the
> long-standing tradition in C and many other languages, for better or
> worse), you have no choice but to let the remainder take the sign of the
> dividend.

True enough.  It seems Computer Science has most of the time made the
opposite choice than mathematicians. I can live with that too.

> 
> Igor Tandetnik
> 

Jean-Denis Muys

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

[sqlite] sqlite3_exec(): errmsg parameter

[chandan]

Hi,
if sqlite3_exec() returns an error code (i.e other than SQLITE_OK), 
is it guaranteed that the "errmsg" argument always contains the error 
message string. Or should i compare the value of errmsg with NULL before 
printing the value?

E.g:
ret = sqlite3_exec(db, sql, NULL, NULL, _msg);
if (ret != SQLITE_OK) {
 if (err_msg != NULL) {  /* is this checking necessary? */
   /* print err_msg */
  sqlite3_free(err_msg);
 }
}

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

Re: [sqlite] error in documentation of SELECT?

[John Machin]

On 19/05/2009 9:57 PM, Igor Tandetnik wrote:
> "John Machin"  wrote
> in message news:4a129cb4.2090...@lexicon.net
>> It's handy for checking how things work e.g.
>>
>> sqlite> select (-1) % 7;
>> -1
>> sqlite> -- it's not a real modulo operator :-(
> 
> What do you feel is wrong with this result? What should a "real" modulo 
> operator return, in your opinion? Before you answer, note that you very 
> likely want this equality to hold for all a, b!=0 :
> 
> a = q*b + r
> where q = a/b, r = a%b

Sure do. No problem for Python:

 >>> for a in (1, -1):
... for b in (7, -7):
... q = a / b
... r = a % b
... x = b * q + r
... print a, b, q, r, x
...
1 7 0 1 1
1 -7 -1 -6 1
-1 7 -1 6 -1
-1 -7 0 -1 -1
 >>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in documentation of SELECT?

[Emil Obermayr]

On Tue, May 19, 2009 at 02:06:37PM +0200, Jean-Denis Muys wrote:
> 
> There exists unique natural numbers q and r such as:
> 
> a = b*q+r
> 0 <= r < b
> 
> q is defined as the quotient, r is defined as the remainder.
> 
> So if the % operator wants to match that math definition, its results should
> never be negative. In the example given, (-1)%7 should therefore be 6.

It depends wether you focus on a arithmetic meaning of the remainder or
on what you call "mathematics" (what would be "Restklasse" in german, I
don't know the english term).

In terms of "Restklasse" you want a positive modulo and get a negative
division result.

In terms of arithmetics you want a positive division result and get a
negative modulo.

Both are valid solutions. At the end you have to live with the
definition of the given implementation.

Just check the sign and correct the sign of the result according to your
needs.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in documentation of SELECT?

[Igor Tandetnik]

"Jean-Denis Muys" 
wrote in message news:c6386d6d.45a7%jdm...@kleegroup.com
> On 5/19/09 1:57 PM, "Igor Tandetnik"
>  wrote:
>
>> "John Machin" 
>> wrote in message
>> news:4a129cb4.2090...@lexicon.net
>>> It's handy for checking how things work e.g.
>>>
>>> sqlite> select (-1) % 7;
>>> -1
>>> sqlite> -- it's not a real modulo operator :-(
>>
>> What do you feel is wrong with this result? What should a "real"
>> modulo operator return, in your opinion? Before you answer, note
>> that you very likely want this equality to hold for all a, b!=0 :
>>
>> a = q*b + r
>> where q = a/b, r = a%b
>>
>> Igor Tanetnik
>>
>
> My math courses taught me a long time ago, that the remainder r in
> Euclidian division of a by b is defined by:
>
> There exists unique natural numbers q and r such as:
>
> a = b*q+r
> 0 <= r < b
>
> q is defined as the quotient, r is defined as the remainder.
>
> So if the % operator wants to match that math definition, its results
> should
> never be negative. In the example given, (-1)%7 should therefore be 6.

Well then, for the equality to hold, (-1)/7 should be -1. Would you be 
happy with such an outcome?

Wikipedia gives a definition different from yours, for what it's worth:

http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers
http://en.wikipedia.org/wiki/Modulo_operation

Once you decide that division truncates towards zero (as is the 
long-standing tradition in C and many other languages, for better or 
worse), you have no choice but to let the remainder take the sign of the 
dividend.

Igor Tandetnik



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

Re: [sqlite] error in documentation of SELECT?

[Nuno Lucas]

On Tue, May 19, 2009 at 12:49 PM, John Machin  wrote:
> It's handy for checking how things work e.g.
>
> sqlite> select (-1) % 7;
> -1
> sqlite> -- it's not a real modulo operator :-(

I also used it as:

sqlite> .mode col
sqlite> .h 1
sqlite> select "€", length("€"), length(cast("€" as blob)), hex("€");
"€"   length("€")  length(cast("€" as blob))  hex("€")
--  -  ---  --
€ 1  3E282AC

To make sure the sqlite shell was inserting UTF-8 as it should.


Regards,
~Nuno Lucas

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

Re: [sqlite] error in documentation of SELECT?

[Jean-Denis Muys]

My memory failed me on a detail if I want to be rigorous. In the definition
of q and r, r is a natural number, but q is a relative number, not a
natural.


On 5/19/09 2:06 PM, "Jean-Denis Muys"  wrote:

> On 5/19/09 1:57 PM, "Igor Tandetnik"  wrote:
> 
>> "John Machin"  wrote
>> in message news:4a129cb4.2090...@lexicon.net
>>> It's handy for checking how things work e.g.
>>> 
>>> sqlite> select (-1) % 7;
>>> -1
>>> sqlite> -- it's not a real modulo operator :-(
>> 
>> What do you feel is wrong with this result? What should a "real" modulo
>> operator return, in your opinion? Before you answer, note that you very
>> likely want this equality to hold for all a, b!=0 :
>> 
>> a = q*b + r
>> where q = a/b, r = a%b
>> 
>> Igor Tanetnik 
>> 
> 
> My math courses taught me a long time ago, that the remainder r in Euclidian
> division of a by b is defined by:
> 
> There exists unique natural numbers q and r such as:
> 
> a = b*q+r
> 0 <= r < b
> 
> q is defined as the quotient, r is defined as the remainder.
> 
> So if the % operator wants to match that math definition, its results should
> never be negative. In the example given, (-1)%7 should therefore be 6.
> 
> But I will not argue that the % operator needs to match the math definition,
> though I would probably prefer it to.
> 
> Jean-Denis
> 
> ___
> 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] error in documentation of SELECT?

[Jean-Denis Muys]

On 5/19/09 1:57 PM, "Igor Tandetnik"  wrote:

> "John Machin"  wrote
> in message news:4a129cb4.2090...@lexicon.net
>> It's handy for checking how things work e.g.
>> 
>> sqlite> select (-1) % 7;
>> -1
>> sqlite> -- it's not a real modulo operator :-(
> 
> What do you feel is wrong with this result? What should a "real" modulo
> operator return, in your opinion? Before you answer, note that you very
> likely want this equality to hold for all a, b!=0 :
> 
> a = q*b + r
> where q = a/b, r = a%b
> 
> Igor Tanetnik 
> 

My math courses taught me a long time ago, that the remainder r in Euclidian
division of a by b is defined by:

There exists unique natural numbers q and r such as:

a = b*q+r
0 <= r < b

q is defined as the quotient, r is defined as the remainder.

So if the % operator wants to match that math definition, its results should
never be negative. In the example given, (-1)%7 should therefore be 6.

But I will not argue that the % operator needs to match the math definition,
though I would probably prefer it to.

Jean-Denis

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

Re: [sqlite] error in documentation of SELECT?

[Igor Tandetnik]

"John Machin"  wrote
in message news:4a129cb4.2090...@lexicon.net
> It's handy for checking how things work e.g.
>
> sqlite> select (-1) % 7;
> -1
> sqlite> -- it's not a real modulo operator :-(

What do you feel is wrong with this result? What should a "real" modulo 
operator return, in your opinion? Before you answer, note that you very 
likely want this equality to hold for all a, b!=0 :

a = q*b + r
where q = a/b, r = a%b

Igor Tanetnik 



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

Re: [sqlite] error in documentation of SELECT?

[John Machin]

On 19/05/2009 9:37 PM, Nuno Lucas wrote:
> On Mon, May 18, 2009 at 5:03 PM, Mitchell L Model  wrote:
>> I may be misreading the select-core diagram on 
>> http://www.sqlite.org/lang_select.html  but it appears that the down-arrow 
>> that would allow a query without a FROM clause should not be there. Is it 
>> really possible to have a SELECT with no FROM? If so, could someone provide 
>> an example; if not, would someone maintaining the documentation make a note 
>> of this? Thank you.
> 
> Yes.
> 
> An use-case could be when you need to pass results to a function
> expecting table result data, but you actually have const data. Instead
> of having a select from some table (that must exist for the SQL to be
> valid), you can just issue a select with no "FROM" clause.
> 
> sqlite> select 1,"Some Data" UNION select 2, "Some More Data";
> 1|Some Data
> 2|Some More Data
> 
> Note that although this is not standard SQL, some other engines also
> have similar features, for example, Oracle let's you do "SELECT 1 FROM
> DUAL", where "DUAL" means "not really from any table". In Oracle you
> could use it, for example, to get the server date/time so you could
> sync your client application clock. As SQLite is not a server, most
> pratical examples are for running user defined functions.


It's handy for checking how things work e.g.

sqlite> select (-1) % 7;
-1
sqlite> -- it's not a real modulo operator :-(

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

Re: [sqlite] column names

[John Machin]

On 19/05/2009 6:47 PM, Chanas, Olivier wrote:
> Hi all,
> 
> I would like to know what are the allowed characters to build a column name.
> Is there some limitation ?

OTTOMH (because it's not all in one convienient place in the docs):

This applies to names of columns, tables, triggers, indexes, 
constraints, etc etc.

Basic: [A-Za-z_][A-Za-z_0-9]* (using regular expression notation)
AFAIK the rules for non-ASCII alphabetics and digits are not spelled out.

Must not be a keyword aka reserved word.

To use a reserved word, or some other character, quote with "" 
(preferred, SQL standard). If you are desperate to include a " in the 
name, double it. E.g. "fu bar", "fu""bar"

Alternatives:
(1) mySQL compatibility: quote with ' (similar desperation clause)
E.g. 'fu bar', 'fu''bar'

(2) MS SQL Server compatibility: quote by bracketing. If desperate to 
include a [, DON'T double it. If desperate to include a ], use " or ' 
quoting. E.g. [fu bar], [fu[bar], "fu]bar"

Note that equivalence of names is determined after stripping out the 
quoting and then lower-casing. So the following are all effectively the 
same:

"O'Brien"
'O''Brien'
[O'Brien]
"o'brien"

What lower-casing means exactly in the non-ASCII realm, I know not. 
Include a German eszett or a Turkish dotless i or dotted I at your peril 
;-)

HTH

John




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

Re: [sqlite] error in documentation of SELECT?

[Nuno Lucas]

On Mon, May 18, 2009 at 5:03 PM, Mitchell L Model  wrote:
> I may be misreading the select-core diagram on 
> http://www.sqlite.org/lang_select.html  but it appears that the down-arrow 
> that would allow a query without a FROM clause should not be there. Is it 
> really possible to have a SELECT with no FROM? If so, could someone provide 
> an example; if not, would someone maintaining the documentation make a note 
> of this? Thank you.

Yes.

An use-case could be when you need to pass results to a function
expecting table result data, but you actually have const data. Instead
of having a select from some table (that must exist for the SQL to be
valid), you can just issue a select with no "FROM" clause.

sqlite> select 1,"Some Data" UNION select 2, "Some More Data";
1|Some Data
2|Some More Data

Note that although this is not standard SQL, some other engines also
have similar features, for example, Oracle let's you do "SELECT 1 FROM
DUAL", where "DUAL" means "not really from any table". In Oracle you
could use it, for example, to get the server date/time so you could
sync your client application clock. As SQLite is not a server, most
pratical examples are for running user defined functions.

The documentation could be ommiting this, but it's an esoteric enough feature.


Regards,
~Nuno Lucas


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

[sqlite] column names

[Chanas, Olivier]

Hi all,

I would like to know what are the allowed characters to build a column name.
Is there some limitation ?

Thanks in advance

? +33 476 14 6656 > olivier.cha...@hp.com ? GMT +1
Hewlett-Packard - 5 av R Chanas, Eybens - 38053 Grenoble Cedex 9 - France

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

[sqlite] error in documentation of SELECT?

[Mitchell L Model]

I may be misreading the select-core diagram on 
http://www.sqlite.org/lang_select.html  but it appears that the down-arrow that 
would allow a query without a FROM clause should not be there. Is it really 
possible to have a SELECT with no FROM? If so, could someone provide an 
example; if not, would someone maintaining the documentation make a note of 
this? Thank you.
-- 

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

Re: [sqlite] memory occupied by columns with NULL value

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

chandan wrote:
> I would like to know how much memory is used for a record field 
> whose value is NULL.

The file format is documented at http://www.sqlite.org/fileformat.html
with the specific information in section 2.3.2 - a null is stored as one
byte.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkoSdHkACgkQmOOfHg372QSIwwCeMrmB6qfYKPVntB2H8UnUpCkZ
t4gAn2mjJHchwKxoRlFKQ+LC7TqJRkry
=FtGs
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory occupied by columns with NULL value

[John Machin]

On 19/05/2009 6:25 PM, chandan wrote:
> Hi,
> I would like to know how much memory is used for a record field 
> whose value is NULL.

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

[sqlite] memory occupied by columns with NULL value

[chandan]

Hi,
I would like to know how much memory is used for a record field 
whose value is NULL.
Regards,
chandan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users