Re: [sqlite] Any Way to Peak at Next Row?
> 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?
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.
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?
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
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
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
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
"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
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
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, Yuzemwrote: > > 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
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 Hesswrote: > 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
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....
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
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
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
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
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
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 Pechnikovwrote: > 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
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
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
"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
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
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
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 ;)
-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