[sqlite] Page Size with NAND FLASH

[Sugathan, Rupesh]

I am planning to use sqlite on a Linux system with JFFS2 file system on
NAND flash. NAND device that I am using has page size of 2048 bytes and
a erase sector size of 128K. I would like to take advantage of sqlite
rollback for the safety of my database files during power-fail.

 

As per http://www.sqlite.org/pragma.html#pragma_page_size, the PAGE_SIZE
should be set between 512 bytes and 64K. The page size of NAND devices,
in my understanding, is only good for 2 to 3 writes before needing to
erase the whole sector. This indicate that the PAGE_SIZE in this case
should be set to 128K (the erase size). Given that this is not a
possibility, how is the power-fail safety achieved in sqlite used on
large sectored flash devices? 

 

I would appreciate if anyone can comment on this topic and/or share
their experience with using sqlite on Flash devices with large sector
sizes. 

Thanks

--

Rupesh

 

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

[sqlite] How do I make a "singleton" using FMDB?

[Rolf Marsh]

I'm having a hard time changing my Obj-C SQLite3 code to FMDB... the 
code works (after a fashion), but I need it to be a singleton (my 
current SQLite3 code is currently a singleton)... my problem is I don't 
know what has to be changed from my existing code, since FMDB has it's 
own initialization routine.

Any help would be greatly appreciated.

Regards,
Rolf

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

Re: [sqlite] Need help understanding how to post to this list

[Mr. Puneet Kishor]

On May 3, 2011, at 6:04 PM, Rolf Marsh wrote:

> Hello.. I just joined today and can't seem to figure out how to start a 
> new thread... Can someone please enlighten me?


You just did.

Just post a question with the subject line indicating clearly what is bothering 
you and take a seat. The doctor will be with you shortly.


> 
> Regards,
> Rolf
> ___
> 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] Need help understanding how to post to this list

[Rolf Marsh]

Hello.. I just joined today and can't seem to figure out how to start a 
new thread... Can someone please enlighten me?

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

Re: [sqlite] Feature request: Fuzzy searching

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/03/2011 10:25 AM, Dotan Cohen wrote:
> I have seen this issue brought up on all types of software, from Anki
> to Kontact to Yum:

The way this is generally solved is to use a full text search engine.  The
various techniques are named things like ascii folding, stemming, synonyms
(wordnet) and double metaphone.  They'll also include support for spelling
correction, faceting, key terms, more like this and weighting.  You'll also
find lots of control over tokenization (eg if you see "d.r." what do you
turn it into?  what about "i-pad"?) and analysis.

The way most of them work is that you define a schema (a set of fields and
their types) and add "documents".  One of the fields will be an id which is
the key back into your real database.

What I suggest you do is use a full blown full text search engine and see
what features matter to you.  You can use triggers and user defined
functions to keep the FTS in sync with your content.

Then you can turn your request into one of these:

 - Adding support for FTS engine XYZ in SQLite (like how ICU is supported)

 - Please can someone write these features into SQLite's FTS.  In search XYZ
they are  lines of code.

Some FTS engines to get you started:

Xapian - GPL, library, C++ core with lots of language bindings
Lucene - Apache license, library, Java core with some language bindings
Sphinx - GPL, server?, C++ core, pipes and db connectivity

If you are a Python developer then my favourite is the pure Python Whoosh.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk3AZTYACgkQmOOfHg372QQlrQCeL5KOjgpx7Cx9OIBhmgE4zZt6
8QgAn0m03YREbaZL9aVcPCqZf8effjVR
=L/So
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: Fuzzy searching

[Cory Nelson]

On Tue, May 3, 2011 at 10:25 AM, Dotan Cohen  wrote:
> Hi all, I am interested in seeing "fuzzy searching" in SQLite, for
> lack of a better term. This type of search would return more results
> than LIKE currently does today. The search would return matches based
> on expanded criteria, each one may be considered a separate RFE for
> LIKE or for another specialized function (FLIKE, maybe).
>
> 1) Case insensitivity
> This is already implemented in SQLite for ASCII characters, but it
> would be nice to have for the rest of the UTF-8 characters (those
> defined with tolower values, of course).
>
> 2) ASCII-equivalent searching
> This would allow users to search for non-ASCII characters using
> ASCII-equivalents. For example, searching for "beisen" would return
> both "beisen" and "beißen". Another example would be a search for
> "daemon" returning both "daemon" and "dæmon".
>
> 3) Diacritic-elimination searching
> This would allow user to search for words without adding the
> diacritics. For example, searching for "Jose" would return both "Jose"
> and "José". Another example would be a search for "דותן" returning
> both "דותן" and "דוֹתָן"‎.
>
> 4) Punctuation-elimination searching
> This would allow user to search for words without adding punctuation.
> For example, searching for "Marc Anthony" would return both "Marc
> Anthony" and "Marc-Anthony". Another example would be a search for
> "Beer Sheva" returning both "Beer Sheva" and "Be'er Sheva".
>
> I have seen this issue brought up on all types of software, from Anki
> to Kontact to Yum:
> https://groups.google.com/group/ankisrs/browse_thread/thread/6fc8374b75a4bf4f/bbce3eb5e8401356
> https://bugs.kde.org/show_bug.cgi?id=158365
> http://comments.gmane.org/gmane.linux.redhat.fedora.general/389336
>

Such functionality requires a good bit of Unicode support and can be
locale-dependent.  I doubt such a large feature would ever get built
into SQLite.  However, all is not lost -- this behavior can be had
easily in SQLite as-is, using custom collations or sort keys.  I'm
currently using this approach myself and it works quite well.  On
Windows this would be done with LCMapString/LCMapStringEx, I'm sure
ICU has a similar API.

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

Re: [sqlite] Compiler Error

[Richard Hipp]

On Tue, May 3, 2011 at 3:31 PM, jeff archer  wrote:

> >I believe any compilation options that require changes in SQL parser
> >require compiling from original sources as well. They cannot be used
> >with amalgamation file which has already generated SQL parser's code.
> >
> >Pavel
>
> OK.  But this call in the parser source...
>
> sqlite3FinishTrigger
>  (pParse, yymsp[-1].minor.yy203, &all);
> Should be enclosed in...
>
> #ifdef SQLITE_OMIT_TRIGGER
> sqlite3FinishTrigger(pParse, yymsp[-1].minor.yy203, &all);
> #endif
>

You are looking at machine-generated code.  If you rerun the "lemon" parser
generator with the -DSQLITE_OMIT_TRIGGER option, then the lines of code
above will not appear in the output at all.

Rebuilding sqlite3.c is more involved that simply rerunning "lemon",
though.  You have to combine the output of lemon with the output of 4 other
machine-generated files as well as dozens of hand-written code files, in
exactly the right order.  Your best bet is to (1) get a unix box of some
kind, (2) get a copy of the canonical source code, (3) edit the Makefile to
include -DSQLITE_OMIT_TRIGGER, and (4) type "make sqlite3.c"


>
>  Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> <330>819.4615
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Compile Error

[Stephan Beal]

On Tue, May 3, 2011 at 9:15 PM, Pavel Ivanov  wrote:

> I believe any compilation options that require changes in SQL parser
> require compiling from original sources as well. They cannot be used
> with amalgamation file which has already generated SQL parser's code.
>

That's correct. See:

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

Section 1.6

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler Error

[jeff archer]

>I believe any compilation options that require changes in SQL parser
>require compiling from original sources as well. They cannot be used
>with amalgamation file which has already generated SQL parser's code.
>
>Pavel
 
OK.  But this call in the parser source...
 
sqlite3FinishTrigger
 (pParse, yymsp[-1].minor.yy203, &all);
Should be enclosed in...
 
#ifdef SQLITE_OMIT_TRIGGER
sqlite3FinishTrigger(pParse, yymsp[-1].minor.yy203, &all);
#endif

 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile Error

[Pavel Ivanov]

I believe any compilation options that require changes in SQL parser
require compiling from original sources as well. They cannot be used
with amalgamation file which has already generated SQL parser's code.


Pavel


On Tue, May 3, 2011 at 3:05 PM, jeff archer  wrote:
> I am attempting to compile SQLite on windows with SQLITE_OMIT_TRIGGER defined
> and I get the following errors.
>
> Sure enought the code in yy_...() is not exclosed in ifndef 
> SQLITE_OMIT_TRIGGER
>
> 1> Creating library ..\..\Bin\x64\Debug\SqlUtils.lib and object
> ..\..\Bin\x64\Debug\SqlUtils.exp
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3DeleteTriggerStep referenced in function yy_destructor
> 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3DropTrigger
> referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3TriggerSelectStep referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3TriggerDeleteStep referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3TriggerInsertStep referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3TriggerUpdateStep referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3BeginTrigger
> referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3FinishTrigger
> referenced in function yy_reduce
> 1>..\..\Bin\x64\Debug\SqlUtils.dll : fatal error LNK1120: 8 unresolved 
> externals
>  Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> <330>819.4615
> ___
> 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] Compile Error

[jeff archer]

I am attempting to compile SQLite on windows with SQLITE_OMIT_TRIGGER defined 
and I get the following errors.

Sure enought the code in yy_...() is not exclosed in ifndef SQLITE_OMIT_TRIGGER

1> Creating library ..\..\Bin\x64\Debug\SqlUtils.lib and object 
..\..\Bin\x64\Debug\SqlUtils.exp
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3DeleteTriggerStep referenced in function yy_destructor
1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3DropTrigger 
referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3TriggerSelectStep referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3TriggerDeleteStep referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3TriggerInsertStep referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3TriggerUpdateStep referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3BeginTrigger 
referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3FinishTrigger 
referenced in function yy_reduce
1>..\..\Bin\x64\Debug\SqlUtils.dll : fatal error LNK1120: 8 unresolved externals
 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADV: "Using SQLite" ebook, 50% off today

[Rolf Marsh]

I bought the hard copy several months ago... I use it all the time...

On 5/3/11 9:20 AM, Simon Slavin wrote:
> O'Reilly
> >Media
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADV: "Using SQLite" ebook, 50% off today

[Gerry Snyder]

I learned a lot from it, too.

Gerry Snyder

On 5/3/11, Nico Williams  wrote:
> On Tue, May 3, 2011 at 11:20 AM, Simon Slavin  wrote:
>> On 3 May 2011, at 5:09pm, Jay A. Kreibich wrote:
>>
>>>  "Using SQLite" is today's "Ebook Deal of the Day" over at O'Reilly
>>>  Media.  Today only (Tuesday, May 3rd) the ebook is 50% off, at
>>>  $15.99.
>>
>> Well I don't know, Jay.  Have you read it ?  Is it any good ?
>>
>> For those who don't know, Jay A. Kreibich is in fact the author of said
>> book.  I can't tell you if it's any good, because I'm not the target
>> audience, but you can read a /lot/ of the content from the link he
>> provided and try before you buy.
>
> I use it as a reference, along with the docs at sqlite.org.  It's quite
> good.
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice to choose an index for quad tree?

[David Garfield]

Actually, for what he wants, you don't need anything fancy.  A simple
multi-column index is enough.

The R-Tree is to allow queries of a sparse dataset, that might also
have overlaps.

So: A simple index for your background imagery.  An R-Tree index for
the features added on top of your background imagery.

--David Garfield

Enrico Thierbach writes:
> Hi,
> 
> I think an R Tree is what you are after.
> 
> http://www.sqlite.org/rtree.html
> 
> /eno
> 
> ___
> 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] Feature request: Fuzzy searching

[Simon Slavin]

On 3 May 2011, at 6:25pm, Dotan Cohen wrote:

> Hi all, I am interested in seeing "fuzzy searching" in SQLite, for
> lack of a better term. This type of search would return more results
> than LIKE currently does today. The search would return matches based
> on expanded criteria, each one may be considered a separate RFE for
> LIKE or for another specialized function (FLIKE, maybe).

Write your own collating function, which ignores all the things you consider 
fuzzy (accents, case, etc.), define it in SQLite, then use existing SQLite 
functions with it.  More details on

http://www.sqlite.org/c3ref/create_collation.html

No reason why you should restrict it to the obvious.  You could, for example, 
take care of Hebrew-English transliteration at the same time, by writing a 
function which considered 'beer' identical to 'בר', and 'len', 'לנ' and 'לן' 
all identical.

Does anyone have an example of collation function source code they can point to 
?

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

[sqlite] RE : Feature request: Fuzzy searching

[Black, Michael (IS)]

Sounds like soundex might work for you.  Not sure how it handles diacritics 
though.

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





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dotan Cohen [dotanco...@gmail.com]
Sent: Tuesday, May 03, 2011 12:25 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Feature request: Fuzzy searching

Hi all, I am interested in seeing "fuzzy searching" in SQLite, for
lack of a better term. This type of search would return more results
than LIKE currently does today. The search would return matches based
on expanded criteria, each one may be considered a separate RFE for
LIKE or for another specialized function (FLIKE, maybe).

1) Case insensitivity
This is already implemented in SQLite for ASCII characters, but it
would be nice to have for the rest of the UTF-8 characters (those
defined with tolower values, of course).

2) ASCII-equivalent searching
This would allow users to search for non-ASCII characters using
ASCII-equivalents. For example, searching for "beisen" would return
both "beisen" and "beißen". Another example would be a search for
"daemon" returning both "daemon" and "dæmon".

3) Diacritic-elimination searching
This would allow user to search for words without adding the
diacritics. For example, searching for "Jose" would return both "Jose"
and "José". Another example would be a search for "דותן" returning
both "דותן" and "דוֹתָן"‎.

4) Punctuation-elimination searching
This would allow user to search for words without adding punctuation.
For example, searching for "Marc Anthony" would return both "Marc
Anthony" and "Marc-Anthony". Another example would be a search for
"Beer Sheva" returning both "Beer Sheva" and "Be'er Sheva".

I have seen this issue brought up on all types of software, from Anki
to Kontact to Yum:
https://groups.google.com/group/ankisrs/browse_thread/thread/6fc8374b75a4bf4f/bbce3eb5e8401356
https://bugs.kde.org/show_bug.cgi?id=158365
http://comments.gmane.org/gmane.linux.redhat.fedora.general/389336

Thanks!

--
Dotan Cohen

http://gibberish.co.il
http://what-is-what.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

[sqlite] Feature request: Fuzzy searching

[Dotan Cohen]

Hi all, I am interested in seeing "fuzzy searching" in SQLite, for
lack of a better term. This type of search would return more results
than LIKE currently does today. The search would return matches based
on expanded criteria, each one may be considered a separate RFE for
LIKE or for another specialized function (FLIKE, maybe).

1) Case insensitivity
This is already implemented in SQLite for ASCII characters, but it
would be nice to have for the rest of the UTF-8 characters (those
defined with tolower values, of course).

2) ASCII-equivalent searching
This would allow users to search for non-ASCII characters using
ASCII-equivalents. For example, searching for "beisen" would return
both "beisen" and "beißen". Another example would be a search for
"daemon" returning both "daemon" and "dæmon".

3) Diacritic-elimination searching
This would allow user to search for words without adding the
diacritics. For example, searching for "Jose" would return both "Jose"
and "José". Another example would be a search for "דותן" returning
both "דותן" and "דוֹתָן"‎.

4) Punctuation-elimination searching
This would allow user to search for words without adding punctuation.
For example, searching for "Marc Anthony" would return both "Marc
Anthony" and "Marc-Anthony". Another example would be a search for
"Beer Sheva" returning both "Beer Sheva" and "Be'er Sheva".

I have seen this issue brought up on all types of software, from Anki
to Kontact to Yum:
https://groups.google.com/group/ankisrs/browse_thread/thread/6fc8374b75a4bf4f/bbce3eb5e8401356
https://bugs.kde.org/show_bug.cgi?id=158365
http://comments.gmane.org/gmane.linux.redhat.fedora.general/389336

Thanks!

-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compound primary key problem

[Simon Slavin]

On 3 May 2011, at 5:56pm, Paul Shaffer wrote:

> This is a mapping table or descriptor table or whatever you want to call
> it, between 2 tables (about 120,000 rows). I'm finding that operations are
> very slow (delete, select, etc.) even within transactions. [snip]
> 
> CREATE TABLE [Item_attribute] (
> [ItemID] integer NOT NULL,
> [AttributeID] integer NOT NULL,
> PRIMARY KEY ([ItemID], [AttributeID]),
> FOREIGN KEY ([AttributeID])
>REFERENCES [Attribute]([AttributeID]) ON DELETE CASCADE ON UPDATE CASCADE,
> FOREIGN KEY ([ItemID])
>REFERENCES [Item]([ItemID]) ON DELETE CASCADE ON UPDATE CASCADE
> )

Excuse me, I'm stating some things for beginners, and you probably know them 
already.

You absolutely need indexes on each of your FOREIGN KEY REFERENCES.  So in this 
case you need an index on the 'AttributeID' column of TABLE Attribute, and an 
index on the ItemID column of TABLE Item.  If those fields are the INTEGER 
PRIMARY KEYs, they will automatically have been indexed.

You've already tried the thing I was going to suggest: making the primary key 
an INTEGER PRIMARY KEY rather than the compound which reflects your database 
design.

Purely for testing, remove one of the FOREIGN KEY REFERENCES.  If this 
dramatically speeds up your app, it's probably something about the primary keys 
you need to pay attention to.  if not, maybe not.

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

[sqlite] Compound primary key problem

[Paul Shaffer]

This is a mapping table or descriptor table or whatever you want to call
it, between 2 tables (about 120,000 rows). I'm finding that operations are
very slow (delete, select, etc.) even within transactions. Foreign keys =
ON. I tried a different design with an integer primary key (which in this
case is a meaningless unused field), and adding UNIQUE([ItemID],
[AttributeID]). Didn't seem to help, probably because all my selects and
deletes didn't use the primary key. Is there anything I can do to speed up
sql commands?


CREATE TABLE [Item_attribute] (
[ItemID] integer NOT NULL,
[AttributeID] integer NOT NULL,
PRIMARY KEY ([ItemID], [AttributeID]),
FOREIGN KEY ([AttributeID])
REFERENCES [Attribute]([AttributeID]) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ([ItemID])
REFERENCES [Item]([ItemID]) ON DELETE CASCADE ON UPDATE CASCADE
)


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

Re: [sqlite] ADV: "Using SQLite" ebook, 50% off today

[Nico Williams]

On Tue, May 3, 2011 at 11:20 AM, Simon Slavin  wrote:
> On 3 May 2011, at 5:09pm, Jay A. Kreibich wrote:
>
>>  "Using SQLite" is today's "Ebook Deal of the Day" over at O'Reilly
>>  Media.  Today only (Tuesday, May 3rd) the ebook is 50% off, at
>>  $15.99.
>
> Well I don't know, Jay.  Have you read it ?  Is it any good ?
>
> For those who don't know, Jay A. Kreibich is in fact the author of said book. 
>  I can't tell you if it's any good, because I'm not the target audience, but 
> you can read a /lot/ of the content from the link he provided and try before 
> you buy.

I use it as a reference, along with the docs at sqlite.org.  It's quite good.

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

Re: [sqlite] ADV: "Using SQLite" ebook, 50% off today

[Simon Slavin]

On 3 May 2011, at 5:09pm, Jay A. Kreibich wrote:

>  "Using SQLite" is today's "Ebook Deal of the Day" over at O'Reilly
>  Media.  Today only (Tuesday, May 3rd) the ebook is 50% off, at
>  $15.99.

Well I don't know, Jay.  Have you read it ?  Is it any good ?

For those who don't know, Jay A. Kreibich is in fact the author of said book.  
I can't tell you if it's any good, because I'm not the target audience, but you 
can read a /lot/ of the content from the link he provided and try before you 
buy.

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

[sqlite] ADV: "Using SQLite" ebook, 50% off today

[Jay A. Kreibich]

  "Using SQLite" is today's "Ebook Deal of the Day" over at O'Reilly
  Media.  Today only (Tuesday, May 3rd) the ebook is 50% off, at
  $15.99.  Use the discount code "DDSQT".

  O'Reilly ebooks are available in several DRM-free formats including
  PDF, ePub, and Mobi. 

  http://oreilly.com/
  http://oreilly.com/catalog/9780596521189/

-j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an error 21 when doing a sqlite3_prepare_v2

[Richard Hipp]

On Tue, May 3, 2011 at 9:46 AM, Rolf Marsh wrote:

> Here is my code, written in Obj-C.  I can't figure out what I'm doing
> wrong... can someone enlighten me?  Please?
>

Set a breakpoint on sqlite3MisuseError() and see where it is being hit.


>
> NSString * errmsg = nil;
> SQLiteDB* db = [SQLiteDB sharedSQLiteDB];  //  create the d/b
>
> NSString *insertCommand = [NSString stringWithFormat:@"INSERT FAIL
> INTO CardData (CARD_ID, CARD_NAME, CODE_VAL) VALUES ('/%@', '/%@',
> '/%@')", symbol.data, @"Test Card", symbol.typeName];
>
> sqlite3_exec(db, [insertCommand UTF8String], NULL, NULL, &errmsg);
> if(errmsg != NULL)
> NSLog(@"insert error: /%@", &errmsg);  //  DEBUGGING ONLY!
>
> //  now, pull it back out of the d/b and display the data
> NSString *sqlStatement = @"SELECT card_id, card_name, code_val FROM
> CardData";
> sqlite3_stmt *compiledStatement;
> int err = sqlite3_prepare_v2(db, [sqlStatement UTF8String], -1,
> &compiledStatement, NULL); // <---  error 21 occurs here
> if(err != SQLITE_OK)
>NSLog(@"prepare error: /%@", err);
> else  {
> // Loop through the results and add them to the feeds array
> while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
>
> // Read the data from the result row
> resultText.text = [NSString stringWithFormat:@"\nDatabase:
> \n%@ \n%@ \n%@", resultText.text,
> [NSString stringWithUTF8String:(char
> *)sqlite3_column_text(compiledStatement, 0)],
> [NSString stringWithUTF8String:(char
> *)sqlite3_column_text(compiledStatement, 1)],
> [NSString stringWithUTF8String:(char
> *)sqlite3_column_text(compiledStatement, 2)]];
> }
> sqlite3_finalize(compiledStatement);  //  release it...
> sqlite3_close(db);
> }
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Getting an error 21 when doing a sqlite3_prepare_v2

[Rolf Marsh]

Here is my code, written in Obj-C.  I can't figure out what I'm doing 
wrong... can someone enlighten me?  Please?

 NSString * errmsg = nil;
 SQLiteDB* db = [SQLiteDB sharedSQLiteDB];  //  create the d/b

 NSString *insertCommand = [NSString stringWithFormat:@"INSERT FAIL 
INTO CardData (CARD_ID, CARD_NAME, CODE_VAL) VALUES ('/%@', '/%@', 
'/%@')", symbol.data, @"Test Card", symbol.typeName];

 sqlite3_exec(db, [insertCommand UTF8String], NULL, NULL, &errmsg);
 if(errmsg != NULL)
 NSLog(@"insert error: /%@", &errmsg);  //  DEBUGGING ONLY!

 //  now, pull it back out of the d/b and display the data
 NSString *sqlStatement = @"SELECT card_id, card_name, code_val FROM 
CardData";
 sqlite3_stmt *compiledStatement;
 int err = sqlite3_prepare_v2(db, [sqlStatement UTF8String], -1, 
&compiledStatement, NULL); // <---  error 21 occurs here
 if(err != SQLITE_OK)
NSLog(@"prepare error: /%@", err);
 else  {
 // Loop through the results and add them to the feeds array
 while(sqlite3_step(compiledStatement) == SQLITE_ROW) {

 // Read the data from the result row
 resultText.text = [NSString stringWithFormat:@"\nDatabase: 
\n%@ \n%@ \n%@", resultText.text,
 [NSString stringWithUTF8String:(char 
*)sqlite3_column_text(compiledStatement, 0)],
 [NSString stringWithUTF8String:(char 
*)sqlite3_column_text(compiledStatement, 1)],
 [NSString stringWithUTF8String:(char 
*)sqlite3_column_text(compiledStatement, 2)]];
 }
 sqlite3_finalize(compiledStatement);  //  release it...
 sqlite3_close(db);
 }
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] OSX Install

[Brian]

Hi Simon,

Thanks for the quick reply!

Haven't started the PyGTK step as I wanted to get the SQLite working first. Now 
I know to skip it. Thanks again!

Cheers,
===
Brian


On 03/05/2011, at 23:14 PM, Simon Slavin wrote:

> 
>> I'm Brian and very much a rookie on this topic. After several hours trying 
>> to determine how to install/compile SQLite for OS X, I'm getting no where; 
>> SQLite is required for KeepNote (http://rasm.ods.org/keepnote/) app.
> 

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

Re: [sqlite] OSX Install

[Simon Slavin]

On 3 May 2011, at 1:51pm, Brian wrote:

> I'm Brian and very much a rookie on this topic. After several hours trying to 
> determine how to install/compile SQLite for OS X, I'm getting no where; 
> SQLite is required for KeepNote (http://rasm.ods.org/keepnote/) app.

The current version of OS X already has two versions of OS X installed.  OS X 
10.6 includes version 3.6.12 of SQLite which exceeds the requirements of that 
package (according to its homepage).  There's no reason you should need to do 
anything about SQLite at all.

If you give us a specific error message we might be able to work out what's 
wrong, but I suspect that your problem is actually with PyGTK or something like 
that, not SQLite.

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

[sqlite] OSX Install

[Brian]

Hello group,

I'm Brian and very much a rookie on this topic. After several hours trying to 
determine how to install/compile SQLite for OS X, I'm getting no where; SQLite 
is required for KeepNote (http://rasm.ods.org/keepnote/) app.

I've downloaded and unzipped three sets of files, the shell, the analyser & 
amalgamation for the OSX system. I've been looking for instructions on where to 
put the files or install them as they're nothing like most OSX installs. I also 
attempted to run the command lines found on the SQLite site and in this list's 
archives using Terminal but again, I'm missing something.

Can anyone share a link to more basic, non-programmer, instructions? Starting 
with what to do with the unzipped files.

Cheers,
===
Brian








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

Re: [sqlite] Advice to choose an index for quad tree?

[Enrico Thierbach]

Hi,

I think an R Tree is what you are after.

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

/eno

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

Re: [sqlite] how to get total count of query result?

[Igor Tandetnik]

mykonica  wrote:
> If I execute a query like 'select col_1 from tbl_1', how to get the count of 
> query result before step it ?

You can't. You'll have to step through to the end while counting rows, or else 
run a statement like 'select count(*) from tbl_1'
-- 
Igor Tandetnik

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

[sqlite] how to get total count of query result?

[mykonica]

If I execute a query like 'select col_1 from tbl_1', how to get the count of 
query result before step it ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Advice to choose an index for quad tree?

[Jean-Denis Muys]

Hi,

My application displays very large images at different zoom factors. I have 
tiled my images into individual tiles of a much smaller size. Each image has 
hundreds of tiles. The tiles will be used to interactively display the image 
and the speed of the zooming and panning is very important.

That's why I am thinking storing the tiles into an SQLite database. I am 
soliciting some advice on how to setup the index.

The main column for a Tile table will be its pixel blob. I can setup the rest 
of its structure as I wish. For example:

left: integer horizontal offset of the tile
top: integer vertical pixel offset of the tile
width: integer horizontal size of the tile
height: integer vertical size of the tile
shrink ratio: a power of two. How much the resolution of the tile has been 
reduced from the image resolution

width and height are typically constant, equal, and a power of two. The 
exception is at the right and bottom edges for the right-most and bottom-most 
rows of tiles for images that have non-power of 2 dimensions.

In memory, my data structure is a quad tree. The leaves of the quad tree point 
to "elementary" tiles: tiles that holds exact pixels from the image at the same 
resolution. At he next tree level, I take 4 tiles (arranged in a square) and 
make a new tile from them. This new tile is shrunk in resolution from its 4 
source tiles by a factor of 2 in each direction. That is, each of its pixels 
represent 4 pixels from the source tiles. I iterate at the next level. 
Conceptually, this process stops when level n has only one tile, which can be 
considered as a thumbnail of the original image.

When I need a tile, I know the topleft of the tile and I know the current zoom 
factor. The zoom factor can be 1.0 (actual pixels) or smaller (zooming out). 
From the zoom factor, I can easily compute the shrink ratio to use: I want to 
use those tiles that have just enough resolution, not better, as that would be 
wasteful and I am memory constrained (I can't load the full image in memory at 
any time). To find out the shrink ratio, it's basically 1/zoom factor, rounded 
to the correct adjacent power of two.

The set of tiles needed at any given time is those that cover (at least 
partially) the displayed rectangle. The number of needed tiles is constant for 
a given window size, since tiles have a constant size (not quite constant: edge 
and zoom rounding effects can affect slightly that number. at least there is a 
known upper bound to that number). That number is small, much smaller than the 
number of elementary tiles. The required tiles are "adjacent": they form a 
connected rectangle.

Note that I can set up my Tile table differently if I want. For example, I can 
use row and column numbers rather than left and top since the pixel sizes are 
known.

My question is: is there any smart way to set up my table index? My naive idea 
is to have a composite index: shrink ratio/left/top (or ratio/row/column).

Is there any better? For example is there any reason to prefer either of 
ratio/left/top and ratio/top/left?

Is there any reason to consider "smart" encodings similar to geo hashing where 
each additional bit in an identifier encodes a smaller area?

I realize that since I have only hundreds - not millions - of tiles, this may 
sound like premature optimization. But I am not after optimization here (yet), 
only after sensible design.

Thanks,

Jean-Denis

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