Re: [sqlite] Is there any memory leak in the normal routine?

[liubin liu]

Thank you very much!

I test my code enough time, and the memory using indeed stop increases when
the process's memory using is 3524 RES.

And I try to "PRAGMA default_cache_size=100". Then the memory is just 1324
and stop increase.





Black, Michael (IS) wrote:
> 
> I confirmed your "memory leak".  What you're seeing is the page cache
> growing.  Not really a memory leak.  Default page cache size is 2000 and
> indeed if I just let it run it topped out at 5404 RES in top.
>  
>  
> I added dmalloc to sqlite3 and found that if you let your program loop
> several times and politely exit there is no memory leak.
>  
> So I added some debug statements in sqlite3.c to the alloc and free
> sections.  It produced an output like this:
> alloc 664 0x2ac150a61408
> alloc 3224 0x2aab9008
> alloc 360 0x2aaab008
> alloc 1016 0x2aac7808
> free 0x2aab9008
> free 0x2ac150a61408
> free 0x2aac7808
> free 0x2aaab008
> 
> I ran just two iterations of your program and edited the log to remove
> everything except the iteration between 1 & 2.
>  
> "grep alloc log | wc -l" and "grep free log" show that the 1st loop
> through your program shows 811 allocs and 809 frees.  So there are two
> places where memory is not getting freed.
>  
> I then wrote a quick log analysis that showed
> free not found for 0x2ac150a63808
> free not found for 0x2aac7008
> 
> grep for those two addresses showed
>  
> alloc 1280 0x2ac150a63808
> alloc 1280 0x2aac7008
>  
> There are only two 1280 alloc's in the log do I added a debug statement in
> sqlite3MemMalloc to trigger on 1280 bytes.
> Stepping through the debug then showed me at this point (line#'s are
> approximate as I've added statements) for both alloc's that occur:
> Breakpoint 1, sqlite3MemMalloc (nByte=1272) at sqlite3.c:12977
> #0  sqlite3MemMalloc (nByte=1272) at sqlite3.c:12977
> #1  0x00405b17 in mallocWithAlarm (n=1272, pp=0x7fff655c1248) at
> sqlite3.c:16343
> #2  0x00405bbc in sqlite3Malloc (n=1272) at sqlite3.c:16371
> #3  0x0040fc5c in pcache1Alloc (nByte=1272) at sqlite3.c:31163
> #4  0x0040fd39 in pcache1AllocPage (pCache=0x2acd74d1eb90) at
> sqlite3.c:31197
> #5  0x004105ca in pcache1Fetch (p=0x2acd74d1eb90, iKey=1,
> createFlag=2) at sqlite3.c:31566
> #6  0x0040f2f3 in sqlite3PcacheFetch (pCache=0x2acd74d288f0,
> pgno=1, createFlag=1,
> ppPage=0x7fff655c1400) at sqlite3.c:30645
> #7  0x0041470b in sqlite3PagerAcquire (pPager=0x2acd74d28810,
> pgno=1, ppPage=0x7fff655c1400,
> noContent=0) at sqlite3.c:36020
> #8  0x00418492 in btreeGetPage (pBt=0x2acd74d29c10, pgno=1,
> ppPage=0x7fff655c1450, noContent=0)
> at sqlite3.c:40101
> #9  0x004193bc in lockBtree (pBt=0x2acd74d29c10) at
> sqlite3.c:40811
> #10 0x004199a2 in sqlite3BtreeBeginTrans (p=0x2acd74d1ee90,
> wrflag=0) at sqlite3.c:41061
> #11 0x00455de7 in sqlite3InitOne (db=0x2acd74d28c10, iDb=0,
> pzErrMsg=0x2acd74d28420)
> at sqlite3.c:79614
> #12 0x0045629d in sqlite3Init (db=0x2acd74d28c10,
> pzErrMsg=0x2acd74d28420) at sqlite3.c:79784
> #13 0x00456384 in sqlite3ReadSchema (pParse=0x2acd74d28410) at
> sqlite3.c:79821
> #14 0x00442d4a in sqlite3StartTable (pParse=0x2acd74d28410,
> pName1=0x2aab90e8,
> pName2=0x2aab9108, isTemp=0, isView=0, isVirtual=0, noErr=0) at
> sqlite3.c:68162
> #15 0x0046bf2e in yy_reduce (yypParser=0x2aab9010,
> yyruleno=26) at sqlite3.c:94009
> #16 0x0046f67e in sqlite3Parser (yyp=0x2aab9010, yymajor=22,
> yyminor=
>   {z = 0x47b36e "(id INTEGER PRIMARY KEY, d1 CHAR(16))", n = 1},
> pParse=0x2acd74d28410)
> at sqlite3.c:95191
> #17 0x0047037b in sqlite3RunParser (pParse=0x2acd74d28410,
> zSql=0x47b358 "CREATE TABLE data_his (id INTEGER PRIMARY KEY, d1
> CHAR(16))", pzErrMsg=0x7fff655c1980)
> at sqlite3.c:96017
> #18 0x004566fd in sqlite3Prepare (db=0x2acd74d28c10,
> zSql=0x47b358 "CREATE TABLE data_his (id INTEGER PRIMARY KEY, d1
> CHAR(16))", nBytes=-1,
> saveSqlFlag=0, pReprepare=0x0, ppStmt=0x7fff655c1ac8,
> pzTail=0x7fff655c1ad0) at sqlite3.c:79995
> #19 0x00456a2b in sqlite3LockAndPrepare (db=0x2acd74d28c10,
> zSql=0x47b358 "CREATE TABLE data_his (id INTEGER PRIMARY KEY, d1
> CHAR(16))", nBytes=-1,
> saveSqlFlag=0, pOld=0x0, ppStmt=0x7fff655c1ac8, pzTail=0x7fff655c1ad0)
> at sqlite3.c:80090
> #20 0x00456b97 in sqlite3_prepare (db=0x2acd74d28c10,
> zSql=0x47b358 "CREATE TABLE data_his (id INTEGER PRIMARY KEY, d1
> CHAR(16))", nBytes=-1,
> ppStmt=0x7fff655c1ac8, pzTail=0x7fff655c1ad0) at sqlite3.c:80153
> #21 0x00451bc7 in sqlite3_exec (db=0x2acd74d28c10,
> zSql=0x47b358 "CREATE TABLE data_his (id INTEGER PRIMARY KEY, d1
> CHAR(16))", xCallback=0, pArg=0x0,
> pzErrMsg=0x0) at sqlite3.c:76835
> #22 0x00401d8a in main () at thread.c:16
> 
> 
>  
> Michael D. Black
> Senior Scientist
> Northrop Grumman Missi

[sqlite] Search multiple columns AND extra coparator

[flakpit]

Hello folks, sorry to bother you. You must admit I take 6 months or more to
ask questions:):)

Using the first part of this query works fine to return data by searching
the multiple concatenated columns, very happy with that.

SELECT * FROM sitelist
 WHERE
sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
LIKE '%wik%' 7

But I also want to AND the following columns to apply to the results and I
am not getting it (So what's new, I hear you say??)

 AND category='Bundled Software'
 AND owner='Gary'

 ORDER BY sitename

So I want the columns to be searched for a match (working) but ONLY
DISPLAYED if the category and owner are matching as well (Not working). 
Have tried all manner of ANDs and ORs and WHEREes in various ways and am not
getting it.

Would anyone be able to elucidate?
-- 
View this message in context: 
http://old.nabble.com/Search-multiple-columns-AND-extra-coparator-tp28356196p28356196.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] Search multiple columns AND extra coparator

[Simon Slavin]

On 25 Apr 2010, at 2:44pm, flakpit wrote:

> Using the first part of this query works fine to return data by searching
> the multiple concatenated columns, very happy with that.
> 
> SELECT * FROM sitelist
> WHERE
> sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
> LIKE '%wik%' 7
> 
> But I also want to AND the following columns to apply to the results and I
> am not getting it (So what's new, I hear you say??)
> 
> AND category='Bundled Software'
> AND owner='Gary'
> 
> ORDER BY sitename

Just add your AND and ORDER by clauses on after the '%wik%'.  But I don't know 
why there's a '7' at the end of your original line.  I don't think it does 
anything.  Perhaps a typo when you made the post ?

SELECT * FROM sitelist
WHERE 
sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
 LIKE '%wik%'
AND category='Bundled Software'
AND owner='Gary'
ORDER BY sitename

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

Re: [sqlite] Search multiple columns AND extra coparator

[flakpit]

>Just add your AND and ORDER by clauses on after the '%wik%'.  But I don't
know why there's a '7' at >the end of your original line.  I don't think it
does anything.  Perhaps a typo when you made the post ?

>SELECT * FROM sitelist
>WHERE
sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
LIKE >'%wik%'
>AND category='Bundled Software'
>AND owner='Gary'
>ORDER BY sitename

Hello Simon, that's what I tried first (yes, the '7' was a typo, sorry) and
it didn't work. I know there is a limit to field concatenation this way but
I don't know what it is so will have to play as it is not giving me any
results at the moment.

Oh well, thanks for the idea...I don't really need any sleep tonight..:):)
-- 
View this message in context: 
http://old.nabble.com/Search-multiple-columns-AND-extra-comparator-tp28356196p28356587.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] [csv extension] feedback

[Kyle McKay]

On Apr 19, 2010, at 10:32:13 PDT, Shane Harrelson wrote:
> Thanks for the report.  The extension is still very a much a
> work-in-progress and any feedback is greatly appreciated.
>
> -Shane

Just in case you haven't already seen this, there is a published CSV  
spec for the text/csv MIME type:

http://tools.ietf.org/html/rfc4180

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

Re: [sqlite] Search multiple columns AND extra coparator

[Igor Tandetnik]

flakpit wrote:
>> SELECT * FROM sitelist
>> WHERE
> sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
> LIKE >'%wik%'
>> AND category='Bundled Software'
>> AND owner='Gary'
>> ORDER BY sitename
> 
> Hello Simon, that's what I tried first (yes, the '7' was a typo, sorry) and
> it didn't work.

Define "didn't work". What results did this query return, and how do these 
results differ from your expectations?

> it is not giving me any results at the moment.

Well, do you actually have any records that match all the conditions? Show a 
sample of your data; in particular, show the record that you believe should be 
returned by the query.
-- 
Igor Tandetnik

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

Re: [sqlite] Search multiple columns AND extra coparator

[flakpit]

Igor Tandetnik wrote:
> 
> 
> Define "didn't work". What results did this query return, and how do these
> results differ from your expectations?
> 
>> it is not giving me any results at the moment.
> 
> Well, do you actually have any records that match all the conditions? Show
> a sample of your data; in particular, show the record that you believe
> should be returned by the query.
> -- 
> Igor Tandetnik
> 

Thanks Igor but as I indicated to Simon, I finally figured it out and pasted
the resulting query string to Simon. Now I may fall out of this chair and
die quietly.

P.s. Your help in the past has also been invaluable, thank you.
-- 
View this message in context: 
http://old.nabble.com/Search-multiple-columns-AND-extra-comparator-tp28356196p28356875.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] Searching with like for a specific start letter

[Alberto Simões]

Hello

I am running on the fly a query to count the number of words starting
with one of the 26 letters.

I am doing the usual SELECT COUNT(term) from dictionary WHERE normword
LIKE "a%"  (for the 26 letters)

normword is the term normalized without accents and the like


Is there any way to make this query faster? It is taking about 10
second for 140K entries.

One idea is to add a column named 'letter' and SELECT COUNT(letter)
from dictionary WHERE letter = 'a'.
But are there other solutions?

Thanks
-- 
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching with like for a specific start letter

[Igor Tandetnik]

Alberto Simões wrote:
> I am running on the fly a query to count the number of words starting
> with one of the 26 letters.
> 
> I am doing the usual SELECT COUNT(term) from dictionary WHERE normword
> LIKE "a%"  (for the 26 letters)
> 
> normword is the term normalized without accents and the like

See if this condition works better:

where normword >= 'a' and normword < 'b'

See also http://sqlite.org/optoverview.html#like_opt
-- 
Igor Tandetnik

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

Re: [sqlite] Searching with like for a specific start letter

[Simon Slavin]

On 25 Apr 2010, at 9:39pm, Alberto Simões wrote:

> One idea is to add a column named 'letter' and SELECT COUNT(letter)
> from dictionary WHERE letter = 'a'.

That will be the simplest way to make a fast lookup, though it will slow down 
your INSERT function.  You could speed it up a tiny bit more by making the 
search field an INTEGER field, and storing, for example, 65 in there for 'a', 
66 for 'b', etc..

Two ways to do it: either put the right letter in the column when you create 
the record, or leave the column at a dummy default value when you create the 
record and have an 'update index' which updates all records which have the 
dummy default value.  Which one you choose depends on whether your INSERT 
function has to run faster than it does naturally.

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

Re: [sqlite] Searching with like for a specific start letter

[Kees Nuyt]

On Sun, 25 Apr 2010 21:39:43 +0100, Alberto Simões
 wrote:

>Hello
>
> I am running on the fly a query to count the number of 
> words starting with one of the 26 letters.
>
> I am doing the usual SELECT COUNT(term) from dictionary WHERE normword
> LIKE "a%"  (for the 26 letters)
>
>normword is the term normalized without accents and the like

Would your application allow to return all 26 in one query?

SELECT COUNT(term) FROM dictionary 
WHERE normword >= 'a'
  AND normword <= 'zz'
GROUP BY substr(normword,1,1);

(untested, but certainly faster than 26 separate queries)

>Is there any way to make this query faster? It is taking about 10
>second for 140K entries.
>
>One idea is to add a column named 'letter' and SELECT COUNT(letter)
>from dictionary WHERE letter = 'a'.
>But are there other solutions?
>
>Thanks
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] values containing dash - not evaluated

[jason d]

Hi there everyone,
its very hard to find sqlite users so I am glad I joined this mailing list.
Recently it seems a lot of people are using sqlite which I dont get but
anyways.

I am still using sqlite2 as the libraries I use do not support sqlite3
(yet). I am using it for web development.

I ran into a strange problem recently. seems like a bug but i am no guru on
sql internals. so  here goes.

Lets say I have a colum "names" and have values "bob", "jones" and
"diana-rogers"
when I use

Select * from names where "name" = "bob";

it works fine. but whenever I use "name" = "diana-rogers"
or anything with a dash in it, it wont work. I have tried with PHP and with
command line (linux)
but there is no error and i get return values as if there really does not
exist this filed.

the column name is TEXT type (if that means anything is sqlite)
I have tried with VARCHAR but I get the same result.

I have hit a brick wall with this problem and have been at it for a couple
of weeks. trying everything I know and can see on gazillionth google search.

If anyone has any info on this and can please share it will be deeply
appreciated and given virtual cookies :D

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

Re: [sqlite] values containing dash - not evaluated

[Simon Slavin]

On 26 Apr 2010, at 3:17am, jason d wrote:

> when I use
> 
> Select * from names where "name" = "bob";
> 
> it works fine. but whenever I use "name" = "diana-rogers"

SQLite uses single quotes for strings, not double quotes.  And the thing 'name' 
is meant to be the name of a column, not a fixed string.  So try something like

SELECT * FROM names WHERE name = 'bob'

and see if that works any better.

> the column name is TEXT type (if that means anything is sqlite)
> I have tried with VARCHAR but I get the same result.

SQLite doesn't have a VARCHAR type.  It interprets it as identical to TEXT.  
Won't do any harm to called it VARCHAR, I'm just telling you there's no point 
in trying it.

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

Hello Simon,
First , thank you for responding.

Yes maybe in the email i used double quotes, but I have actually tried every
quote/ quoteless combination.
In fact initially the SQL was in single quotes. It does not work as
expected, no results are returned an no error is thrown.


Jason



On Mon, Apr 26, 2010 at 10:32 AM, Simon Slavin  wrote:

>
> On 26 Apr 2010, at 3:17am, jason d wrote:
>
> > when I use
> >
> > Select * from names where "name" = "bob";
> >
> > it works fine. but whenever I use "name" = "diana-rogers"
>
> SQLite uses single quotes for strings, not double quotes.  And the thing
> 'name' is meant to be the name of a column, not a fixed string.  So try
> something like
>
> SELECT * FROM names WHERE name = 'bob'
>
> and see if that works any better.
>
> > the column name is TEXT type (if that means anything is sqlite)
> > I have tried with VARCHAR but I get the same result.
>
> SQLite doesn't have a VARCHAR type.  It interprets it as identical to TEXT.
>  Won't do any harm to called it VARCHAR, I'm just telling you there's no
> point in trying it.
>
> Simon.
> ___
> 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] values containing dash - not evaluated

[Simon Slavin]

On Mon, Apr 26, 2010 at 10:32 AM, Simon Slavin  wrote:
> 
>> SELECT * FROM names WHERE name = 'bob'

On 26 Apr 2010, at 3:54am, jason d wrote:

> Hello Simon,
> First , thank you for responding.

You're welcome.  New text below the text you're quoting, please.  English is 
read top to bottom.

> Yes maybe in the email i used double quotes, but I have actually tried every
> quote/ quoteless combination.
> In fact initially the SQL was in single quotes.

You do not want quotes of any kind around 'name'.  Try it exactly as I wrote it 
below and see if that works.

> It does not work as
> expected, no results are returned an no error is thrown.

Then you have no records in your table that match your search criterion.  Are 
you sure you really do have a record for 'bob' ?  How do you prove it ?

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

On Mon, Apr 26, 2010 at 11:01 AM, Simon Slavin  wrote:

> On Mon, Apr 26, 2010 at 10:32 AM, Simon Slavin 
> wrote:
> >
> >> SELECT * FROM names WHERE name = 'bob'
>
> On 26 Apr 2010, at 3:54am, jason d wrote:
>
> > Hello Simon,
> > First , thank you for responding.
>
> You're welcome.  New text below the text you're quoting, please.  English
> is read top to bottom.
>
> > Yes maybe in the email i used double quotes, but I have actually tried
> every
> > quote/ quoteless combination.
> > In fact initially the SQL was in single quotes.
>
> You do not want quotes of any kind around 'name'.  Try it exactly as I
> wrote it below and see if that works.
>
> > It does not work as
> > expected, no results are returned an no error is thrown.
>
> Then you have no records in your table that match your search criterion.
>  Are you sure you really do have a record for 'bob' ?  How do you prove it ?
>
> Simon.
>


sorry about the top posting.

I believe you misunderstood my problem. Its not that records dont exist. and
select statement for Bob does work. a select * does display all the data.
 its the names with dashes that dont shows up. and i have 40,000 records.
any with dashes do not give any result on a pure select statement. but if I
select on any other column and then work on the resultset it is ok. for
example I may choose column projectname since it does not have a dash (-) in
it. The information is clearly there, just its as if it does not equate to
anything at all.

SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

returns zero results and yes it is in database.

sorry but i just tried this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Igor Tandetnik]

jason d wrote:
> I believe you misunderstood my problem. Its not that records dont exist. and
> select statement for Bob does work. a select * does display all the data.
> its the names with dashes that dont shows up. and i have 40,000 records.
> any with dashes do not give any result on a pure select statement. but if I
> select on any other column and then work on the resultset it is ok. for
> example I may choose column projectname since it does not have a dash (-) in
> it. The information is clearly there, just its as if it does not equate to
> anything at all.
> 
> SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

What does this statement return:

select name, hex(name) from Groups
where name like '%jean%';

My guess is, you either have leading and/or trailing whitespace around the 
value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other 
Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex dump 
would tell.
-- 
Igor Tandetnik

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

On Mon, Apr 26, 2010 at 11:28 AM, Igor Tandetnik wrote:

> jason d wrote:
> > I believe you misunderstood my problem. Its not that records dont exist.
> and
> > select statement for Bob does work. a select * does display all the data.
> > its the names with dashes that dont shows up. and i have 40,000 records.
> > any with dashes do not give any result on a pure select statement. but if
> I
> > select on any other column and then work on the resultset it is ok. for
> > example I may choose column projectname since it does not have a dash (-)
> in
> > it. The information is clearly there, just its as if it does not equate
> to
> > anything at all.
> >
> > SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.
>
> What does this statement return:
>
> select name, hex(name) from Groups
> where name like '%jean%';
>
> My guess is, you either have leading and/or trailing whitespace around the
> value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other
> Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex
> dump would tell.
> --
> Igor Tandetnik
>
> this is what I got. I am using SQLite version 2.8.17

SQL error: no such function: hex
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Igor Tandetnik]

jason d wrote:
> On Mon, Apr 26, 2010 at 11:28 AM, Igor Tandetnik wrote:
>> What does this statement return:
>> 
>> select name, hex(name) from Groups
>> where name like '%jean%';
>> 
>> My guess is, you either have leading and/or trailing whitespace around the
>> value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other
>> Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex
>> dump would tell.
>> 
> this is what I got. I am using SQLite version 2.8.17
> 
> SQL error: no such function: hex

Well, I don't have the reference for SQLite2, so you'll have to do some 
investigative work here. Isn't there some way to inspect actual character codes 
comprising a string? Perhaps you can write a test application to do that.
-- 
Igor Tandetnik

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

On Mon, Apr 26, 2010 at 11:50 AM, Igor Tandetnik wrote:

> jason d wrote:
> > On Mon, Apr 26, 2010 at 11:28 AM, Igor Tandetnik  >wrote:
> >> What does this statement return:
> >>
> >> select name, hex(name) from Groups
> >> where name like '%jean%';
> >>
> >> My guess is, you either have leading and/or trailing whitespace around
> the
> >> value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some
> other
> >> Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex
> >> dump would tell.
> >>
> > this is what I got. I am using SQLite version 2.8.17
> >
> > SQL error: no such function: hex
>
> Well, I don't have the reference for SQLite2, so you'll have to do some
> investigative work here. Isn't there some way to inspect actual character
> codes comprising a string? Perhaps you can write a test application to do
> that.
>
> Even though I could not run your test, I believe you may be on to something
here. I suspected that encoding is a problem but I cannot seem to get
anything that explains this behaviour until you mention this. Does Sqlite2
have anyway of specifying character encoding during INSERT or UPDATE? like
they do in MySQL etc etc. In which case I could test different character
encoding to see what result I am getting in my tests. I have already tried
getting output in HTML with different encodings. however it does not give
away anything visually. For example if I use PHP to generate pretty URLs
with the dashes values i get the URL correctly with the dashes in the
browser address bar. Same results in command line. The dash is always
visible. So I am theorizing here, that if I could instead insert in
different encodings and then run a test to match against the values it might
give me which encoding is correct. Thanks for helping me brain storm on this
one.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size

[Tom Broadbent]

thanks for the replies.

understood.  i was informed that our underlying (EMMC??) FS will do this w/ FS 
pages, i.e. read the entire 256k FS page, modify some small portion of it, and 
write it out again.  i'm higher in the stack so i don't understand the 
underlying FS mechanisms; i've simply asked the lower-level devs for advice 
about maximizing FS IO.  

on this particular FS i'm under the impression that writing a 1k SQLite page 
requires reading a 256k FS page, modifying the 1k portion and then writing the 
whole 256k FS page out. i'll verify w/ the lower-level devs next week.

yes, this sounds _very_ inefficient, but perhaps this is why our commit times 
are so poor (i.e. writing each DB page requires reading and writing a much 
larger FS page).

yes - i'm aware that each table and index are in a separate DB page; i saw 
evidence of this when bumping the page size from 1k to 32k.  

this DB has only one table w/ a single pkey index.  the DB will be relatively 
large (10MB) compared to the page size and will continue to grow over time, so 
i'm not concerned.

basically i want to know if SQLite will have internal problems running w/ a 
page_size greater than the recommended (required?) max of 32k.

thanks
tom


On Apr 21, 2010, at 1:39 PM, D. Richard Hipp wrote:

> 
> On Apr 21, 2010, at 4:37 PM, Pavel Ivanov wrote:
> 
>> I don't know anything about internal support of pages bigger than 32k.
>> But I want to warn you: each table and each index in SQLite occupy at
>> least 1 database page. So let's say you have 4 tables with 1
>> additional index each (besides 'integer primary key' one). With 256k
>> pages this schema will result in a database of more than 2 Mb without
>> any data stored. Is your embedded FS okay with this storage amount?
> 
> Furthermore, SQLite changes whole pages at a time.  So in a database  
> with 256kB pages, if you change a single byte, you still have to write  
> 256kB both to the rollback journal and to the database file.
> 
>> 
>> 
>> Pavel
>> 
>> On Tue, Apr 20, 2010 at 4:51 PM, Tom Broadbent
>>  wrote:
>>> i've read in the docs that SQLITE_MAX_PAGE_SIZE can't be > 32k  
>>> (below).  is this limitation still valid?
>>> 
>>> we have an embedded FS that is _very_ slow and performs best w/ a  
>>> write page size of 256k.  will bad things happen if i configure  
>>> SQLite w/ 256k pages?
>>> 
>>> thanks
>>> tom
>>> 
>>> Maximum Database Page Size
>>> 
>>> An SQLite database file is organized as pages. The size of each  
>>> page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The  
>>> default value for SQLITE_MAX_PAGE_SIZE is 32768. The current  
>>> implementation will not support a larger value.
>>> 
>>> It used to be the case that SQLite would allocate some stack  
>>> structures whose size was proportional to the maximum page size.  
>>> For this reason, SQLite would sometimes be compiled with a smaller  
>>> maximum page size on embedded devices with limited stack memory.  
>>> But more recent versions of SQLite put these large structures on  
>>> the heap, not on the stack, so reducing the maximum page size is no  
>>> longer necessary on embedded devices. There is no longer any real  
>>> reason to lower the maximum page size.
>>> 
>>> __
>>> This email has been scanned by the MessageLabs Email Security System.
>>> For more information please visit http://www.messagelabs.com/email
>>> __
>>> ___
>>> 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
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email 
> __


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Igor Tandetnik]

jason d wrote:
>> Even though I could not run your test, I believe you may be on to something
> here. I suspected that encoding is a problem but I cannot seem to get
> anything that explains this behaviour until you mention this. Does Sqlite2
> have anyway of specifying character encoding during INSERT or UPDATE?

I don't know about SQLite2, but SQLite3 always expects strings in UTF-8 or 
UTF-16 (depending on what API function you use: those that want UTF-16 usually 
have "16" somewhere in their names). If you have a string in some other 
encoding, you need to convert it to UTF-{8,16} before passing it to SQLite.

However, if all your strings are pure 7-bit ASCII (and all your examples so far 
were such), then encoding shouldn't matter.

> they do in MySQL etc etc. In which case I could test different character
> encoding to see what result I am getting in my tests.

Can't you just retrieve the string exactly as SQLite reports it, and dump 
numeric values of each individual byte (which is what built-in hex() function 
does in SQLite3). Post the dump here, and we'll try to figure out the encoding.
-- 
Igor Tandetnik

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

> I don't know about SQLite2, but SQLite3 always expects strings in UTF-8 or
> UTF-16 (depending on what API function you use: those that want UTF-16
> usually have "16" somewhere in their names). If you have a string in some
> other encoding, you need to convert it to UTF-{8,16} before passing it to
> SQLite.
>
> However, if all your strings are pure 7-bit ASCII (and all your examples so
> far were such), then encoding shouldn't matter.
>
I believe so but I used PHP built in functions to do INSERTs. I did specify
any encoding explicitly  but it's assumed to be UTF-8 by default.

>
> > they do in MySQL etc etc. In which case I could test different character
> > encoding to see what result I am getting in my tests.
>
> Can't you just retrieve the string exactly as SQLite reports it, and dump
> numeric values of each individual byte (which is what built-in hex()
> function does in SQLite3). Post the dump here, and we'll try to figure out
> the encoding.
> --
>
All right. I get it. I need to write something up to get the hex values of
strings in database. Get back to you soon with the dump. thanks.
Jason
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Licensing of SQLIte

[Navaneeth Sen B]

Hello All,

I just have some doubts on the licensing issues of SQLite.

   1. Being open-source, is my company liable to post back
  changes/modifications to
  SQLite? What are the licensing terms & conditions?
   2. As our present development is on a Linux variant, are there any
  present
  feature/functionality that need to be posted back?


Thanks & Regards,
Sen

*
*


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

Re: [sqlite] Licensing of SQLIte

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/25/2010 09:43 PM, Navaneeth Sen B wrote:
> I just have some doubts on the licensing issues of SQLite.

SQLite doesn't have a license - it is public domain - copyright has been
waived.  (Some countries claim you can't do this - allegedly Germany is one
place.)  In any event the details are all on the web site (and source):

  http://www.sqlite.org/copyright.html

>1. Being open-source, is my company liable to post back
>   changes/modifications to SQLite?

No.

>   What are the licensing terms & conditions?

No license as there is no copyright.

>2. As our present development is on a Linux variant, are there any
>   present
>   feature/functionality that need to be posted back?

Your question is not clear.  The source is the same for all platforms.

Also see http://www.sqlite.org/support.html

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

iEYEARECAAYFAkvVHuEACgkQmOOfHg372QQrVACfX/udub4al6bmq1zfrOYvh3TC
PxkAn1a0ODwHOLb4li15H+JftXnra8ap
=0/wQ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Licensing of SQLIte

[Simon Slavin]

On 26 Apr 2010, at 5:43am, Navaneeth Sen B wrote:

> I just have some doubts on the licensing issues of SQLite.
> 
>   1. Being open-source, is my company liable to post back
>  changes/modifications to
>  SQLite? What are the licensing terms & conditions?
>   2. As our present development is on a Linux variant, are there any
>  present
>  feature/functionality that need to be posted back?

The operative page for licensing terms and conditions is



Have someone read and understand that page.  SQLite is Public Domain.  That 
basically means you can do anything you like with SQLite apart from pretend you 
invented it or stop other people from using it.  However, I am not a lawyer in 
India and if you are unsure about your legal situation you should consult a 
qualified lawyer.

Your company can modify SQLite however it likes for its own purposes.  Doing 
this does not mean that your company 'owns' SQLite in any way: the licensing 
terms remain the same.  Your company is not required to reveal what it has 
changed or how it has done so.  It can if it wants, but there's no requirement 
to do so.  If it does want to contribute changes back to SQLite, these changes 
must be released as Public Domain, or they will not be incorporated into future 
versions of SQLite.

There is nothing special about compiling SQLite for use with any version of 
Linux.  The facilities for all operating systems are equivalent, or as close as 
they reasonably can be given how each operating system works.

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

Re: [sqlite] Licensing of SQLIte

[Navaneeth Sen B]

Hello All,
>
>
> Obtaining An Explicit License To Use SQLite
>
> Even though SQLite is in the public domain and does not require a 
> license, some users want to obtain a license anyway. Some reasons for 
> obtaining a license include:
>
> * You are using SQLite in a jurisdiction that does not recognize
>   the public domain.
> * You are using SQLite in a jurisdiction that does not recognize
>   the right of an author to dedicate their work to the public domain.
> * You want to hold a tangible legal document as evidence that you
>   have the legal right to use and distribute SQLite.
> * Your legal department tells you that you have to purchase a
>   license.
>
> If you feel like you really have to purchase a license for SQLite, 
> Hwaci , the company that employs the architect 
> and principal developers of SQLite, will sell you one 
> .
>
Could somebody explain to me this paragraph?

I also need clarification on the below quote :
> using SQLite in a jurisdiction that does not recognize the public domain. 
Thanks & Regards,
Sen



On 4/26/2010 10:39 AM, Simon Slavin wrote:
> On 26 Apr 2010, at 5:43am, Navaneeth Sen B wrote:
>
>
>> I just have some doubts on the licensing issues of SQLite.
>>
>>1. Being open-source, is my company liable to post back
>>   changes/modifications to
>>   SQLite? What are the licensing terms&  conditions?
>>2. As our present development is on a Linux variant, are there any
>>   present
>>   feature/functionality that need to be posted back?
>>  
> The operative page for licensing terms and conditions is
>
> 
>
> Have someone read and understand that page.  SQLite is Public Domain.  That 
> basically means you can do anything you like with SQLite apart from pretend 
> you invented it or stop other people from using it.  However, I am not a 
> lawyer in India and if you are unsure about your legal situation you should 
> consult a qualified lawyer.
>
> Your company can modify SQLite however it likes for its own purposes.  Doing 
> this does not mean that your company 'owns' SQLite in any way: the licensing 
> terms remain the same.  Your company is not required to reveal what it has 
> changed or how it has done so.  It can if it wants, but there's no 
> requirement to do so.  If it does want to contribute changes back to SQLite, 
> these changes must be released as Public Domain, or they will not be 
> incorporated into future versions of SQLite.
>
> There is nothing special about compiling SQLite for use with any version of 
> Linux.  The facilities for all operating systems are equivalent, or as close 
> as they reasonably can be given how each operating system works.
>
> Simon.
> ___
> 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] page_size

[Dan Kennedy]

On Apr 26, 2010, at 11:09 AM, Tom Broadbent wrote:

>
> thanks for the replies.
>
> understood.  i was informed that our underlying (EMMC??) FS will do  
> this w/ FS pages, i.e. read the entire 256k FS page, modify some  
> small portion of it, and write it out again.  i'm higher in the  
> stack so i don't understand the underlying FS mechanisms; i've  
> simply asked the lower-level devs for advice about maximizing FS IO.
>
> on this particular FS i'm under the impression that writing a 1k  
> SQLite page requires reading a 256k FS page, modifying the 1k  
> portion and then writing the whole 256k FS page out. i'll verify w/  
> the lower-level devs next week.
>
> yes, this sounds _very_ inefficient, but perhaps this is why our  
> commit times are so poor (i.e. writing each DB page requires reading  
> and writing a much larger FS page).
>
> yes - i'm aware that each table and index are in a separate DB page;  
> i saw evidence of this when bumping the page size from 1k to 32k.
>
> this DB has only one table w/ a single pkey index.  the DB will be  
> relatively large (10MB) compared to the page size and will continue  
> to grow over time, so i'm not concerned.
>
> basically i want to know if SQLite will have internal problems  
> running w/ a page_size greater than the recommended (required?) max  
> of 32k.

The page formats use 16-bit unsigned integers to store various offsets
(in bytes) to cells and free-blocks within a page. So it definitely
won't work with greater than 64KB pages.

Not sure if 64KB would work or not. Since it hasn't been tested, the
answer is probably "No.".

Dan.



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

Re: [sqlite] Licensing of SQLIte

[Simon Slavin]

On 26 Apr 2010, at 6:19am, Navaneeth Sen B wrote:

>> If you feel like you really have to purchase a license for SQLite, 
>> Hwaci , the company that employs the architect 
>> and principal developers of SQLite, will sell you one 
>> .
>> 
> 
> Could somebody explain to me this paragraph?

Under US law you are allowed to use SQLite without making any special 
arrangements to obtain a license.  However, some companies find it impossible 
to do business in this way (usually for one of the reasons listed on that 
page), so if your company wants to purchase a license, arrangements have been 
made to sell one to it.

> I also need clarification on the below quote :
>> using SQLite in a jurisdiction that does not recognize the public domain. 

The legal systems in some countries do not accept the idea of public domain.  
In those countries all intellectual property is owned by someone (a company or 
a person).  To find out if India is one of them, consult a qualified Indian 
lawyer.

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

Re: [sqlite] page_size

[Tom Broadbent]

ah - good to know. that is what i was looking for.

thanks
tom

On Apr 25, 2010, at 10:21 PM, Dan Kennedy wrote:

> The page formats use 16-bit unsigned integers to store various offsets
> (in bytes) to cells and free-blocks within a page. So it definitely
> won't work with greater than 64KB pages.
> 
> Not sure if 64KB would work or not. Since it hasn't been tested, the
> answer is probably "No.".


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Licensing of SQLIte

[Navaneeth Sen B]

Thank you all
Great thanks for the numerous suggestions and explanations.

Thanks and Regards,
Sen

On 4/26/2010 11:01 AM, Simon Slavin wrote:
> On 26 Apr 2010, at 6:19am, Navaneeth Sen B wrote:
>
>
>>> If you feel like you really have to purchase a license for SQLite,
>>> Hwaci, the company that employs the architect
>>> and principal developers of SQLite, will sell you one
>>> .
>>>
>>>
>> Could somebody explain to me this paragraph?
>>  
> Under US law you are allowed to use SQLite without making any special 
> arrangements to obtain a license.  However, some companies find it impossible 
> to do business in this way (usually for one of the reasons listed on that 
> page), so if your company wants to purchase a license, arrangements have been 
> made to sell one to it.
>
>
>> I also need clarification on the below quote :
>>  
>>> using SQLite in a jurisdiction that does not recognize the public domain.
>>>
> The legal systems in some countries do not accept the idea of public domain.  
> In those countries all intellectual property is owned by someone (a company 
> or a person).  To find out if India is one of them, consult a qualified 
> Indian lawyer.
>
> Simon.
> ___
> 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