Re: [sqlite] Any Way to Peak at Next Row?

[jonwood]

> What you want is to fetch the data into memory first, and then process
> it later. They way you describe the goal, you'd only have to keep 1
> record in memory.

Yeah, I've been toying with that. It makes things a bit more complex than
I'd like but sounds like that can't be avoided.

> Also, if you're doing reporting and use Perl, have a look at my project,
> PDF::ReportWriter, available on CPAN and my website:
> http://entropy.homelinux.org/axis/

This particular project's in C++/MFC.

Thanks.

Jonathan

-- 
View this message in context: 
http://www.nabble.com/Any-Way-to-Peak-at-Next-Row--tp22448225p22449085.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] Any Way to Peak at Next Row?

[Daniel Kasak]

On Tue, 2009-03-10 at 20:28 -0700, jonwood wrote:

> I'm creating some reports with some SQLite data. One report groups data by
> one column and subtotals each group.
> 
> It's working well but it really doesn't look right when the last item in a
> group is at the bottom of the page and the subtotal is then orphaned on the
> start of the next page. I really need some way to determine if the next row
> starts a new group, and then take steps to keep the current row on the same
> page as my subtotals if it is.
> 
> Is there any way to peak at the next row without losing the current row? Or
> does anyone have any other suggestions for orphan control?

What you want is to fetch the data into memory first, and then process
it later. They way you describe the goal, you'd only have to keep 1
record in memory.

Also, if you're doing reporting and use Perl, have a look at my project,
PDF::ReportWriter, available on CPAN and my website:
http://entropy.homelinux.org/axis/

Dan



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

[sqlite] compilation : undefined symbols.

[Mayura S.]

Hello Sir,

I downloaded sqlite 3.6.11 code for my project in my organisation.
I'm not using the amalgamation code.  I'm building the source code files in 
unix environment.

I'm not new to sqlite, I have earlier worked on 3.2.2.
My project need is very simple - to store and read data (persistent data ).
Hence there are no complex/conditional queries or sorting of any kind.

I'm building the code with following switches, hence scaling down Sqlite as 
much as possible to cater only store and read data.

-DSQLITE_OS_OTHER=1
-DSQLITE_THREADSAFE=0
-DSQLITE_TEMP_STORE=3
-DSQLITE_OMIT_AUTHORIZATION
-DSQLITE_OMIT_AUTOINIT
-DSQLITE_OMIT_BETWEEN_OPTIMIZATION
-DSQLITE_OMIT_BLOB_LITERAL
-DSQLITE_OMIT_BUILTIN_TEST
-DSQLITE_OMIT_CHECK
-DSQLITE_OMIT_COMPLETE
-DSQLITE_OMIT_DATETIME_FUNCS
-DSQLITE_OMIT_DECLTYPE
-DSQLITE_OMIT_DEPRECATED
-DSQLITE_OMIT_FLAG_PRAGMAS
-DSQLITE_OMIT_FLOATING_POINT
-DSQLITE_OMIT_GET_TABLE
-DSQLITE_OMIT_INCRBLOB
-DSQLITE_OMIT_INTEGRITY_CHECK
-DSQLITE_OMIT_LIKE_OPTIMIZATION
-DSQLITE_OMIT_LOAD_EXTENSION
-DSQLITE_OMIT_LOCALTIME
-DSQLITE_OMIT_PAGER_PRAGMAS
-DSQLITE_OMIT_PROGRESS_CALLBACK
-DSQLITE_OMIT_SCHEMA_PRAGMAS
-DSQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
-DSQLITE_OMIT_SHARED_CACHE
-DSQLITE_OMIT_SUBQUERY
-DSQLITE_OMIT_TCL_VARIABLE
-DSQLITE_OMIT_TRACE
-DSQLITE_OMIT_TRUNCATE_OPTIMIZATION
-DSQLITE_OMIT_UTF16
-DSQLITE_OMIT_XFER_OPT
-DSQLITE_OMIT_ALTERTABLE
-DSQLITE_OMIT_ANALYZE
-DSQLITE_OMIT_ATTACH
-DSQLITE_OMIT_AUTOINCREMENT
-DSQLITE_OMIT_AUTOVACUUM
-DSQLITE_OMIT_CAST
-DSQLITE_OMIT_COMPOUND_SELECT
-DSQLITE_OMIT_CONFLICT_CLAUSE
-DSQLITE_OMIT_EXPLAIN
-DSQLITE_OMIT_FOREIGN_KEY
-DSQLITE_OMIT_PRAGMA
-DSQLITE_OMIT_REINDEX
-DSQLITE_OMIT_TEMPDB
-DSQLITE_OMIT_VACUUM
-DSQLITE_OMIT_VIEW
-DSQLITE_OMIT_VIRTUALTABLE
-DSQLITE_SYSTEM_MALLOC


... but I'm  getting the following symbols as Undefined.

Error: L6218E: Undefined symbol sqlite3CreateView (referred from sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3Vacuum (referred from sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3Pragma (referred from sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3Attach (referred from sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3Detach (referred from sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3Reindex (referred from sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3Analyze (referred from sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3AlterRenameTable (referred from 
sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3AlterFinishAddColumn (referred from 
sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3AlterBeginAddColumn (referred from 
sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3VtabFinishParse (referred from 
sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3VtabBeginParse (referred from 
sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3VtabArgInit (referred from sqlite.ptl).
Error: L6218E: Undefined symbol sqlite3VtabArgExtend (referred from sqlite.ptl)


The above symbols are not available in the '#else' code of any compiler switch. 
Most of the compiler switch do not have any '#else' code.
Please guide me on resolving these symbols.  All the above symbols are used in 
parse.c file.
I believe parse.c is a generated file.  
Should I make changes in parse.c file ? OR
Should I stub those symbols in '#else' code ?

Thanks in advance.
Mayura


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

[sqlite] Any Way to Peak at Next Row?

[jonwood]

I'm creating some reports with some SQLite data. One report groups data by
one column and subtotals each group.

It's working well but it really doesn't look right when the last item in a
group is at the bottom of the page and the subtotal is then orphaned on the
start of the next page. I really need some way to determine if the next row
starts a new group, and then take steps to keep the current row on the same
page as my subtotals if it is.

Is there any way to peak at the next row without losing the current row? Or
does anyone have any other suggestions for orphan control?

Thanks.
-- 
View this message in context: 
http://www.nabble.com/Any-Way-to-Peak-at-Next-Row--tp22448225p22448225.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] Creating a secondary table automatically

[cmartin]

On Tue, 10 Mar 2009, Erik Smith wrote:

> I am new to SQLite and am trying to automatically create a secondary table
> which my python app will query against.  The secondary table is a summary of
> the 1st table by specific types.  I have looked at stored procedures (but
> sqlite does not support these) and triggers with no success.  Any
> recommendations?  Here is a sample of what I am trying to do:
> Existing Table:
>
> 234|John|Smith|1.2|catA
> 234|John|Smith|.8|catA
> 234|John|Smith|1|catB
> 234|John|Smith|5|catC
> 234|John|Smith|.2|catD
> 234|John|Smith|.8|catD
> 567|Jim|Jones|1|catA
> 567|Jim|Jones|2|catB
> 567|Jim|Jones|3|catC
> 567|Jim|Jones|4|catD
> 890|Jane|Mickey|1|catA
> 890|Jane|Mickey|4|catA
> 890|Jane|Mickey|1|catB
> 890|Jane|Mickey|4|catC
> 890|Jane|Mickey|6|catC
>
> New table should have:
>
> 234|John|Smith|2|catA
> 234|John|Smith|1|catB
> 234|John|Smith|5|catC
> 234|John|Smith|1|catD
> 567|Jim|Jones|1|catA
> 567|Jim|Jones|2|catB
> 567|Jim|Jones|3|catC
> 567|Jim|Jones|4|catD
> 890|Jane|Mickey|5|catA
> 890|Jane|Mickey|1|catB
> 890|Jane|Mickey|10|catC

Assuming the names of the columns in the 1st table are: id, fn, ln, 
dataval, and categ, then this qry will summarize it as in your example:

select id, fn, ln, sum(dataval) as sum, categ
group by categ;

This query could serve as your 'secondary table', as you can query against 
it, e.g.,

select id, sum from
(select id, fn, ln, sum(dataval) as sum, categ group by categ);

Or, you can create a view from the first query, like a virtual table, and 
then just refer to the view as a secondary table

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

Chris


>
> Thanks.
>
> Erik
> ___
> 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] Extract error text in C API

[Andy]

I am using triggers to handle database integrity as suggested by the docs.  
When a violation occurs the 'SELECT RAISE(ROLLBACK, "Blah")' is executed.  I 
want to get extract the 'Blah' text using the C API but cannot seem to work 
out how to do it.  I feel I am missing something obvious... can anyone please 
help.

Thanks.

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

[sqlite] Creating a secondary table automatically

[Erik Smith]

I am new to SQLite and am trying to automatically create a secondary table
which my python app will query against.  The secondary table is a summary of
the 1st table by specific types.  I have looked at stored procedures (but
sqlite does not support these) and triggers with no success.  Any
recommendations?  Here is a sample of what I am trying to do:
Existing Table:

234|John|Smith|1.2|catA
234|John|Smith|.8|catA
234|John|Smith|1|catB
234|John|Smith|5|catC
234|John|Smith|.2|catD
234|John|Smith|.8|catD
567|Jim|Jones|1|catA
567|Jim|Jones|2|catB
567|Jim|Jones|3|catC
567|Jim|Jones|4|catD
890|Jane|Mickey|1|catA
890|Jane|Mickey|4|catA
890|Jane|Mickey|1|catB
890|Jane|Mickey|4|catC
890|Jane|Mickey|6|catC

New table should have:

234|John|Smith|2|catA
234|John|Smith|1|catB
234|John|Smith|5|catC
234|John|Smith|1|catD
567|Jim|Jones|1|catA
567|Jim|Jones|2|catB
567|Jim|Jones|3|catC
567|Jim|Jones|4|catD
890|Jane|Mickey|5|catA
890|Jane|Mickey|1|catB
890|Jane|Mickey|10|catC

Thanks.

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

Re: [sqlite] I need help with very complex queries

[Igor Tandetnik]

"Yuzem"  wrote in
message news:22446301.p...@talk.nabble.com
> As an example, I have:
> tables:
> movies genres keywords languages countries etc...
>
> movies columns:
> id title year director etc
>
> The others are:
> id "name of the table" (example: id genres)
>
> The id in all the tables are the same column that is the id of the
> movie so that every movie can be many times in all the tables but
> only one time in movies.
>
> What I want to do is something like:
> select ... where genres = Horror and countries = italy and keywords =
> 

select * from movies where
movies.id in (select id from genres where genres='Horror') and
movies.id in (select id from countries where countries='italy') and
movies.id in (select id from keywords where keywords='');

Igor Tandetnik 



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

Re: [sqlite] I need help with very complex queries

[Yuzem]

Jim Wilcoxson wrote:
> 
> For example, in your query you need to add something like:
> and genres.genre = 'drama'.
Yes but if I add that I neither get any result:

sqlite3 movies.db "select movies.id,title,year from
movies,genres,countries,languages,keywords,tags where movies.id = genres.id
and movies.id = countries.id and movies.id = languages.id and movies.id =
keywords.id and movies.id = tags.id and genres like 'horror'"

The same happens if I use genres.genres instead of only genres.


Jim Wilcoxson wrote:
> 
> For this query, only the movies and
> genres tables are needed because you aren't selecting any fields from
> the other tables, from what I can tell.
Yes I know but shouldn't I get all movies with that? I'm getting none...

-- 
View this message in context: 
http://www.nabble.com/I-need-help-with-very-complex-queries-tp22446301p22447156.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] I need help with very complex queries

[Jim Wilcoxson]

You have specified how the movies table relates to the other tables,
but you haven't specified any independent selection criteria for any
tables.   For example, in your query you need to add something like:
and genres.genre = 'drama'.  For this query, only the movies and
genres tables are needed because you aren't selecting any fields from
the other tables, from what I can tell.

Running through a few online SQL tutorials will help you get a better
feel for how SQL works.

Jim

On 3/10/09, Yuzem  wrote:
>
> As an example, I have:
> tables:
> movies genres keywords languages countries etc...
>
> movies columns:
> id title year director etc
>
> The others are:
> id "name of the table" (example: id genres)
>
> The id in all the tables are the same column that is the id of the movie so
> that every movie can be many times in all the tables but only one time in
> movies.
>
> What I want to do is something like:
> select ... where genres = Horror and countries = italy and keywords = 
>
> This is what I have:
> sqlite3 movies.db "select movies.id,title,year from
> movies,genres,countries,languages,keywords,tags where movies.id = genres.id
> and movies.id = countries.id and movies.id = languages.id and movies.id =
> keywords.id and movies.id = tags.id"
>
> There is no query and it is giving me nothing...
> Many thanks in advance!
> --
> View this message in context:
> http://www.nabble.com/I-need-help-with-very-complex-queries-tp22446301p22446301.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
>


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

Re: [sqlite] Formatted text with fts3

[Paul Perry]

Scott,

Thanks so much for your thoughtful response.  Things can get complicated
(especially with full text searching) when using formatted text.

What I really want to do is retain the formatting, to display in a HTML
file.  The text out of the database would be wrapped by a basic HTML outter
document, and the formatted text would mainly reference style sheets.  This
is for a desktop application, so I am not trying to target a browser.

I was thinking it is ok to limit myself to full open and close tags, and
also to not have a single word be broken up, as you illustrated nicely.

Thereby, I was thinking if I just filtered out (or removed) the basic tags I
am using, such as:

Some text. this is more text.  Followed by more.

by simply not putting the tags into the index (it technically wouldn't be
fully formed xml with a header and such) , it would still retain the text
for searching but ignore the tags.  I agree that trying to handle generic
HTML would be a bear.  The content is not coming from the web, so I have
control over that as well.  I guess there could be problems with snippets or
with the offsets of the text.

I'll have to take a closer look into the provided parsers, and maybe there
is a way that any tags, could just *not *be put into the index.

I read the documents pointed out and the readme.  It looks like most of the
info on it is the source code, so I will need to take some time to dig
deeper and understand it.  I am hoping to start with the current source code
and add a filter.

I read that some folks from Google did some of the work on the fts3 module.
They probably drop all tags when they search for text, and that allows for a
more reliable search.  I'm just guessing.

Thanks again,
Paul

On Tue, Mar 10, 2009 at 11:22 AM, Scott Hess  wrote:

> The fts module doesn't do anything "interesting" with embedded
> meta-data in the interests of simplicity.  Stripping the info out
> before inserting is probably easiest, but has the downsides of
> duplication (assuming you need to keep the raw data elsewhere), and it
> means that queries involving snippets and the like may be funky.
> Probably the best way to go about this would be to convert all tags to
> a single whitespace character.
>
> Building a custom tokenizer is certainly doable, but could be a
> frustrating goal if you intend to process generic HTML you find on the
> web, just because of the number of heuristics you'll have to layer in.
>  As a first pass, you might just treat tags as word breaks as you
> iterate over the input.  But there definitely are cases where HTML
> markup happens within words, so you might need something a bit more
> sophisticated.  There is some level of support for returning tokens
> which are not literally present in the input.  For instance, for the
> input 'this' you could return 'this' and indicate that it
> corresponds to 11 characters in the input, and everything should work.
>  I'm not sure anyone has ever exercised this aspect of things
> strongly, though, so it's possible that things don't work as intended
> when you do that.
>
> Before going either direction, you should probably sit down and figure
> out what exactly you're going to do with the results you get from the
> table.  If you want to, say, present them on a web page, then your
> problems are just beginning, because the tag nesting will open up
> layout issues and security problems.  It may be that thinking through
> that part of the system will help you figure out an appropriate
> approach for this part of the system (for instance, if you decide to
> strip tags for other reasons, then it all becomes easy!).
>
> -scott
>
>
> On Tue, Mar 10, 2009 at 6:56 AM, Paul Perry  wrote:
> > Thank you for the pointers Alexandre and Alexey.
> >
> > I spent about 30 minutes looking into the parser, and it looks like it is
> a
> > possibility.  I'll require a more in-depth understanding in order to do
> > this.  I would probably start with the simple parser, and go from there.
> >
> >> I think to prepare html before insert is more simple. You can transform
> >
> > html into "right" format for fts3 parser.
> > I would actually like to retain the tagged (html) formatting in the
> > database, thereby, when it is retrieved it can be displayed as rich text.
> >
> > Thanks,
> > Paul
> >
> >
> >
> > On Tue, Mar 10, 2009 at 4:32 AM, Alexey Pechnikov <
> pechni...@mobigroup.ru>wrote:
> >
> >> Hello!
> >>
> >> On Tuesday 10 March 2009 06:16:16 Alexandre Courbot wrote:
> >> > Never did this myself, but I think you can do what you need by writing
> >> > your own tokenizer:
> >> >
> >> >
> >>
> http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.tokenizers
> >>
> >> It's not good advice for a few documented module.
> >>
> >> I think to prepare html before insert is more simple. You can transform
> >> html
> >> into "right" format for fts3 parser.
> >>
> >> Best regards.
> >>  

[sqlite] advice about opening an encrypted database

[Dave Dyer]

using the standard sqlite encryption option:

If I open a database I expect to be encrypted, and call
sqlite_key to establish the expected key, how should I verify
that the database is now open for business? Ie that the key
was correct.

Similarly, if I open a database might or might not be encrypted,
how can I definitively determine that is the case, so I can ask
the client for a password.


It's not really satisfactory to just wait for some downstream
query to fail, or to make a dummy query and assume that if it
fails the reason is the lack of proper encryption keys.


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

[sqlite] Nested SELECTS using UNION and INTERSECT syntax problems....

[sorka]

I can't for the life of me figure this out. I'm trying to do a nested select
like this:

SELECT x FROM (( UNION ) INTERSECT ( UNION
)) WHERE X=

Each of the select a through d statements all return the same column x. If I
remove the inner parentheses, it executes just fine but of course the
results are wrong because C UNION D was not executed prior to the INTERSECT. 

Also, in each sub select case, a, b, c, and d, are all selecting on
different FTS3 tables using MATCH so I can't take advantage of FTS3's newer
nested parentheses with AND OR NOT hence the nested selects since MATCH can
only be used once per select.

Any ideas? I really don't want to have to resort to using temporary tables.
-- 
View this message in context: 
http://www.nabble.com/Nested-SELECTS-using-UNION-and-INTERSECT-syntax-problems-tp22441795p22441795.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] get_table and bind

[Igor Tandetnik]

galea...@korg.it wrote:
> is it possible to use a similar function to get_table but starting by
> a statement in order to use the bind facilities?

Anything wrong with calling sqlite3_step in a loop?

Igor Tandetnik



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

[sqlite] get_table and bind

[galeazzi]

is it possible to use a similar function to get_table but starting by  
a statement in order to use the bind facilities?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] nullable select fields

[John Elrick]

Andrea Galeazzi wrote:
> Hi All,
> I'm developing an application which relies on sqllite as  back-end. Now 
> I face to this problem: I've got a form that allows the user to fill a 
> lot of fields,  obliviously only a little part of them will actually be 
> filled, the others isn't gonna be in the search criteria. So I prepare a 
> parameterized query containing the whole possible fields like this:
> SELECT * FROM Song WHERE id = ? AND title =  ? AND album LIKE '%?%';
> How can I bind the unrequested fields? Does a trivial solution exist?
>   

If you have multiple possibilities and combinations, my own preference 
would be to have a dynamically created SQL select statement.  We do that 
for some of our more complicated object relationships.


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

Re: [sqlite] nullable select fields

[galeazzi]

Citando John Machin :

> On 10/03/2009 10:56 PM, Andrea Galeazzi wrote:
>> Hi All,
>> I'm developing an application which relies on sqllite as  back-end. Now
>> I face to this problem: I've got a form that allows the user to fill a
>> lot of fields,  obliviously only a little part of them will actually be
>> filled, the others isn't gonna be in the search criteria. So I prepare a
>> parameterized query containing the whole possible fields like this:
>> SELECT * FROM Song WHERE id = ? AND title =  ? AND album LIKE '%?%';
>> How can I bind the unrequested fields? Does a trivial solution exist?
>> Thanks
>
> I suspect that your use of the word "nullable" in the subject is causing
> some confusion.
>
> If the user fills in only the title:
>
> SELECT * FROM Song WHERE id IS NULL AND title = ? AND album LIKE NULL;
> ??? I don't think so.
>
> AFAICT you want the effect of SELECT * FROM Song WHERE title = ? ;
> without the complexity of having 7 (or 15, or 31...) prepared statements
> to cope with all possibilities.
>
> You can do this by using LIKE/GLOB/etc provided that all your columns
> are text:
>
> sql = "SELECT * FROM Song WHERE id LIKE ? AND title LIKE ? AND album
> LIKE ?;"
> *NOTE* (pax Igor) no apostrophes on the RHS of LIKE -- let your DB
> interface do the quoting for you.
> Python example:
> qid = "%" # Any id; don't care
> qtitle = "%O'Reilly%"
> # contains "O'Reilly"; the DB i/f will turn that into the equivalent of
> ... LIKE '%O''Reilly%' ...
> qalbum = "%"
> cursor.execute(sql, (qid, qtitle, qalbum))
>
> BTW, the query optimiser can ignore column1 LIKE '%' only when column1
> is declared as NOT NULL, so this way of doing it may not be the fastest :-(
>
> HTH,
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Yes, that's the problem:  I don't wanna test a field for NULL, but I'd  
like to know the
best way to ignore some field. Thanks for your advise but what about  
numeric field like
id?
I hope to write a query for each field combination isn't necessary!
By the way I'm using C Api.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Formatted text with fts3

[Paul Perry]

Thank you for the pointers Alexandre and Alexey.

I spent about 30 minutes looking into the parser, and it looks like it is a
possibility.  I'll require a more in-depth understanding in order to do
this.  I would probably start with the simple parser, and go from there.

> I think to prepare html before insert is more simple. You can transform >
html into "right" format for fts3 parser.
I would actually like to retain the tagged (html) formatting in the
database, thereby, when it is retrieved it can be displayed as rich text.

Thanks,
Paul



On Tue, Mar 10, 2009 at 4:32 AM, Alexey Pechnikov wrote:

> Hello!
>
> On Tuesday 10 March 2009 06:16:16 Alexandre Courbot wrote:
> > Never did this myself, but I think you can do what you need by writing
> > your own tokenizer:
> >
> >
> http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.tokenizers
>
> It's not good advice for a few documented module.
>
> I think to prepare html before insert is more simple. You can transform
> html
> into "right" format for fts3 parser.
>
> Best regards.
>  ___
> 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] nullable select fields

[John Machin]

On 10/03/2009 10:56 PM, Andrea Galeazzi wrote:
> Hi All,
> I'm developing an application which relies on sqllite as  back-end. Now 
> I face to this problem: I've got a form that allows the user to fill a 
> lot of fields,  obliviously only a little part of them will actually be 
> filled, the others isn't gonna be in the search criteria. So I prepare a 
> parameterized query containing the whole possible fields like this:
> SELECT * FROM Song WHERE id = ? AND title =  ? AND album LIKE '%?%';
> How can I bind the unrequested fields? Does a trivial solution exist?
> Thanks

I suspect that your use of the word "nullable" in the subject is causing 
some confusion.

If the user fills in only the title:

SELECT * FROM Song WHERE id IS NULL AND title = ? AND album LIKE NULL; 
??? I don't think so.

AFAICT you want the effect of SELECT * FROM Song WHERE title = ? ;
without the complexity of having 7 (or 15, or 31...) prepared statements 
to cope with all possibilities.

You can do this by using LIKE/GLOB/etc provided that all your columns 
are text:

sql = "SELECT * FROM Song WHERE id LIKE ? AND title LIKE ? AND album 
LIKE ?;"
*NOTE* (pax Igor) no apostrophes on the RHS of LIKE -- let your DB 
interface do the quoting for you.
Python example:
qid = "%" # Any id; don't care
qtitle = "%O'Reilly%"
# contains "O'Reilly"; the DB i/f will turn that into the equivalent of 
... LIKE '%O''Reilly%' ...
qalbum = "%"
cursor.execute(sql, (qid, qtitle, qalbum))

BTW, the query optimiser can ignore column1 LIKE '%' only when column1 
is declared as NOT NULL, so this way of doing it may not be the fastest :-(

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

[sqlite] In-memory SQLite multithreaded access

[Andrei VANCEA]

Hello,

I want to use the in-memory storage for the following scenario :

- The content is added in the beginning (multiple insert statements)
- Afterwards, all the accesses will consist only of select queries. The
problem is that I want the read accesses to be executed, in parallel, from
multiple threads.

Is it possible? It seems that the each connection has its only in-memory
database and that two connection can not share the same memory zone. Can I
use the same connection in different threads if I only have read accesses?




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

Re: [sqlite] nullable select fields

[Igor Tandetnik]

"Andrea Galeazzi"  wrote in
message news:49b6557c.3060...@korg.it
> I'm developing an application which relies on sqllite as  back-end.
> Now I face to this problem: I've got a form that allows the user to
> fill a lot of fields,  obliviously only a little part of them will
> actually be filled, the others isn't gonna be in the search criteria.
> So I prepare a parameterized query containing the whole possible
> fields like this: SELECT * FROM Song WHERE id = ? AND title =  ? AND
> album LIKE '%?%'; How can I bind the unrequested fields?

You can't. Perhaps surprisingly, NULL=NULL is always false. To test a 
field for NULL, you must write "title IS NULL". Or, if you don't need to 
distinguish between, say, NULL and an empty string, you could write 
"coalesce(title, '') = ?" and bind an empty string to the parameter.

Also, '%?%' is a string literal consisting of three characters, one of 
which is '?'. There is no parameter placeholder there. You need to write 
'%' || ? || '%'

Igor Tandetnik 



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

Re: [sqlite] nullable select fields

[Martin.Engelschalk]

Hi,

see sqlite3_bind_null:

http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob

Martin

Andrea Galeazzi schrieb:
> Hi All,
> I'm developing an application which relies on sqllite as  back-end. Now 
> I face to this problem: I've got a form that allows the user to fill a 
> lot of fields,  obliviously only a little part of them will actually be 
> filled, the others isn't gonna be in the search criteria. So I prepare a 
> parameterized query containing the whole possible fields like this:
> SELECT * FROM Song WHERE id = ? AND title =  ? AND album LIKE '%?%';
> How can I bind the unrequested fields? Does a trivial solution exist?
> Thanks
> ___
> 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] nullable select fields

[Andrea Galeazzi]

Hi All,
I'm developing an application which relies on sqllite as  back-end. Now 
I face to this problem: I've got a form that allows the user to fill a 
lot of fields,  obliviously only a little part of them will actually be 
filled, the others isn't gonna be in the search criteria. So I prepare a 
parameterized query containing the whole possible fields like this:
SELECT * FROM Song WHERE id = ? AND title =  ? AND album LIKE '%?%';
How can I bind the unrequested fields? Does a trivial solution exist?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Formatted text with fts3

[Alexey Pechnikov]

Hello!

On Tuesday 10 March 2009 06:16:16 Alexandre Courbot wrote:
> Never did this myself, but I think you can do what you need by writing
> your own tokenizer:
>
> http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.tokenizers

It's not good advice for a few documented module.

I think to prepare html before insert is more simple. You can transform html 
into "right" format for fts3 parser.

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

Re: [sqlite] Point a newbie in the right direction ;)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Monte Milanuk wrote:
> Where I need some help (or more specifically, some pointers towards books,
> tutorials and such) is on how I should organize the data in SQLite - how to
> split it up between tables, referencing them in queries, backups, dumps,
> etc.

If you use apsw then all of this applies:

  http://apsw.googlecode.com/svn/publish/tips.html

The "Customising cursors" section does not apply if you use pysqlite
instead.

As for books, you may wish to try "The Definitive Guide to SQLite".  My
review is at http://www.rogerbinns.com/sqlitereview.html and you can
find other reviews and book recommendations in the archives for this list.

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

iEYEARECAAYFAkm2GU4ACgkQmOOfHg372QR1RACgnw7ATrMG3Ug5iCg33rNqPccA
kjkAoLyhFJooqgGxqjwgF6obCZlgY3Zr
=Cld7
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users