[sqlite] database design question
Hi All, I am working on sample database application. I want to store book names and authors. Tables: 1. Authors: columns(AuthorId_primarykey, Name, SSN) 2. Books : columns(BookId_primarykey, Title)//Title is unique 3. Author_Books: columns(AuthorId_primarykey, BookId_primarykey) Here, I am able to avoid rendandent data (author names). I am facing an issue with deletion of records from Books table. One author may belongs to morethan one book. How to handle this scenario? Is there technique like shared_ptr. Thanks in advance. Regards, d ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 + unicode61 tokenizer performance issue
Ok, I'm working on a simple project, which will reproduce a problem. Valentine. 11.06.2013, в 17:30, Dan Kennedy написал(а): > On 06/11/2013 08:20 PM, Valentine Silvansky wrote: >> Hi all! >> >> I have a custom-built sqlite with FTS4 and unicode61 tokenizer for it. I've >> got the only one table "messages": >> >> CREATE VIRTUAL TABLE messages USING FTS4(body TEXT, tokenize=unicode61); >> >> So, I populate this table with ~100k entries, each one is a sentence (or a >> short message) in english or russian. So when I execute SELECT query with >> english text in MATCH, I got result less than in a second, but when query is >> in russian, it takes minute and more to get result. >> >> I need a case-insensitive search in any language, so I used unicode61 >> tokenizer. >> >> My code is written for OS X 10.8, SQLite version is 3.7.16, custom defines >> are SQLITE_ENABLE_FTS3 and SQLITE_ENABLE_FTS4_UNICODE61. >> >> Is there a way to boost up the search? > > Are you able to make the database file and a couple of > example queries available? > > Thanks, > Dan. > > ___ > 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] Obtain limit-less count of previous select
On Tue, Jun 11, 2013 at 9:08 AM, Igor Tandetnik wrote: > On 6/11/2013 9:01 AM, Simon Slavin wrote: > >> The only time that _prepare() will use a lot of memory is when it has to >> generate a temporary index because you have not created a table index >> suitable for the WHERE and ORDER BY clauses. >> > > I'm pretty sure this would happen on the first _step(), not on _prepare(). > Prepare is always nearly instantaneous. > Correct. Think of sqlite3_prepare() as the compiler that converts your SQL program into machine language (really byte-code in the case of SQLite). sqlite3_step() runs your program up to the next breakpoint, which is the spot in which the first row of output is computed, or until the program exits. SQLITE_ROW is returned if it hits a breakpoint and SQLITE_DONE is returned on exit. Creating of temporary indices happen when the program is run, not when it is compiled. That means it happens during sqlite3_step(). sqlite3_prepare() == running the compiler sqlite3_step() == running the compiled binary NB: There are some PRAGMA statements that cheat and take effect during the compilation step. But all "regular" SQL statements obey the rules above. -- 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] Obtain limit-less count of previous select
On Tue, Jun 11, 2013 at 09:08:42AM -0400, Igor Tandetnik scratched on the wall: > On 6/11/2013 9:01 AM, Simon Slavin wrote: > > The only time that _prepare() will use a lot of memory is when it has to > > generate a temporary index because you have not created a table index > > suitable for the WHERE and ORDER BY clauses. > > I'm pretty sure this would happen on the first _step(), not on > _prepare(). Prepare is always nearly instantaneous. Yes. And it isn't always a temporary index. Many times, with an ORDER BY, SQLite will compute (and hold in memory) the full result set. -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] sqlite3Fts3ExprParse and MAX_EXPR_DEPTH = 12
On Tue, Jun 11, 2013 at 9:10 AM, wrote: > Hello, > > we just updated the SQLite sources/DLL in our project from version > 3.7.15.2 to version 3.7.17. > After executing the update we got a failing unit test concerning a string > based database search > with a very long string (but this string is present in our current SQLite > DB). In fact the reason of > the failing test/problem is the limitation of constant MAX_EXPR_DEPTH to > 12 in call "sqlite3Fts3ExprParse". > After changing this constant to 60 and a recompile the unit test didn't > fail anymore. > > The question is why is this constant set to such a small value and what is > the drawback when > we set this constant to a (much) higher value? > There are test cases that require 1< > best regards, > > Thomas > > > PANalytical > The Analytical X-ray Company > > The information contained in this message is confidential and may be > legally privileged. The message is intended solely for the addressee(s). > If you are not the intended recipient, you are hereby notified that any > use, dissemination, or reproduction is strictly prohibited and may be > unlawful. If you are not the intended recipient, please contact the sender > by return e-mail and destroy all copies of the original message. > ___ > 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] FTS4 + unicode61 tokenizer performance issue
On 06/11/2013 08:20 PM, Valentine Silvansky wrote: Hi all! I have a custom-built sqlite with FTS4 and unicode61 tokenizer for it. I've got the only one table "messages": CREATE VIRTUAL TABLE messages USING FTS4(body TEXT, tokenize=unicode61); So, I populate this table with ~100k entries, each one is a sentence (or a short message) in english or russian. So when I execute SELECT query with english text in MATCH, I got result less than in a second, but when query is in russian, it takes minute and more to get result. I need a case-insensitive search in any language, so I used unicode61 tokenizer. My code is written for OS X 10.8, SQLite version is 3.7.16, custom defines are SQLITE_ENABLE_FTS3 and SQLITE_ENABLE_FTS4_UNICODE61. Is there a way to boost up the search? Are you able to make the database file and a couple of example queries available? Thanks, Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS4 + unicode61 tokenizer performance issue
Hi all! I have a custom-built sqlite with FTS4 and unicode61 tokenizer for it. I've got the only one table "messages": CREATE VIRTUAL TABLE messages USING FTS4(body TEXT, tokenize=unicode61); So, I populate this table with ~100k entries, each one is a sentence (or a short message) in english or russian. So when I execute SELECT query with english text in MATCH, I got result less than in a second, but when query is in russian, it takes minute and more to get result. I need a case-insensitive search in any language, so I used unicode61 tokenizer. My code is written for OS X 10.8, SQLite version is 3.7.16, custom defines are SQLITE_ENABLE_FTS3 and SQLITE_ENABLE_FTS4_UNICODE61. Is there a way to boost up the search? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3Fts3ExprParse and MAX_EXPR_DEPTH = 12
Hello, we just updated the SQLite sources/DLL in our project from version 3.7.15.2 to version 3.7.17. After executing the update we got a failing unit test concerning a string based database search with a very long string (but this string is present in our current SQLite DB). In fact the reason of the failing test/problem is the limitation of constant MAX_EXPR_DEPTH to 12 in call "sqlite3Fts3ExprParse". After changing this constant to 60 and a recompile the unit test didn't fail anymore. The question is why is this constant set to such a small value and what is the drawback when we set this constant to a (much) higher value? best regards, Thomas PANalytical The Analytical X-ray Company The information contained in this message is confidential and may be legally privileged. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, dissemination, or reproduction is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Obtain limit-less count of previous select
On 6/11/2013 9:01 AM, Simon Slavin wrote: The only time that _prepare() will use a lot of memory is when it has to generate a temporary index because you have not created a table index suitable for the WHERE and ORDER BY clauses. I'm pretty sure this would happen on the first _step(), not on _prepare(). Prepare is always nearly instantaneous. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Obtain limit-less count of previous select
On 10 Jun 2013, at 1:56pm, Daniel Hofmann wrote: > Background is, that I want to implement my paging entirely in sql in order to > save memory, because the complete result-data consumes a lot of memory. If you use _prepare(), _step(), _finalize() instead of using _exec() then you get the behaviour you want. SQLite does not read all rows into memory when you execute _prepare(). SQLite reads just one row each time you call _step(), reading the data from your database file. The only time that _prepare() will use a lot of memory is when it has to generate a temporary index because you have not created a table index suitable for the WHERE and ORDER BY clauses. > Question is: Why isn't there a way like the FOUND_ROWS()-function of MySQL (I > didn't find any reading the docs and googling), to get the amount of rows a > query would have returned if it had been executed without specifying a > limit-clause? It may be that you are trying to combine the SELECT you are using to get your data with the SELECT needed to find how many rows will be returned. This isn’t the most efficient way to do things in SQLite. The fastest way to learn how many rows a SELECT would return is to do BEGIN SELECT count(*) FROM WHERE ... any number of SELECT commands you need to retrieve your data ... END The SELECT instruction I gave does the fastest minimum database-retrieval necessary to return your row count. This tells you your row count first and you can use this number to decide how your other SELECT commands should work. The count is valid until you execute the END, at which point other processes or users can again change the data in the file which might, of course, change the number of rows you’d find with your SELECT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Download SQLlite
Jeffrey Hartman wrote: > I tried downloading the Zip files to install SQLLite but my system > does not recognize the file format. Windows has built-in .zip support. What happens when you double-click on such a file? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
Hi all, I'm starting to use SQLITE in GIS use cases. I'm facing this view typing column issue, which prevent my favourite client from interpreting correctly numeric data types. I'm using QGIS, so I won't be able to map numeric values (they fall back as text values). Is there anything new since 2010? If not, should I suggest Qgis Devs to hack the field type detection for views? Régis -- View this message in context: http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-tp56769p69350.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Download SQLlite
I tried downloading the Zip files to install SQLLite but my system does not recognize the file format. I'm using Windows 7 Express and it is a 64-Bit machine. Thanks. Jeff Hartman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Obtain limit-less count of previous select
Hi, Background is, that I want to implement my paging entirely in sql in order to save memory, because the complete result-data consumes a lot of memory. Question is: Why isn't there a way like the FOUND_ROWS()-function of MySQL (I didn't find any reading the docs and googling), to get the amount of rows a query would have returned if it had been executed without specifying a limit-clause? Doing the same query again (using count() and without sorting) is a lot faster than the original, limited query, but still I have queries where this takes about 1 second to execute and thus affects user-experience. To be honest it is 4 seconds for the normal query + 1 seconds for the count-query so it is not a show-stopper, however it seems to me, that this 1 second could be avoided easily in some cases.. I understand, that the total-count-value may not be internally available in all cases without doing some calculations that might affect performance when the value is not needed, but I'm pretty sure, that it should be easily available in many cases, e.g. when the query has an order-by-clause before the limit-clause.. As all my queries for the paging do an order-by, I'm pretty sure a "total_rows()"-functions that only yields a value when it can be calculated easily or a pragma to turn the feature on and off would serve me well enough.. Cheers, Daniel -- Dipl.-Inf. Daniel Hofmann Axivion GmbH Nobelstr. 15 70569 Stuttgart Germany Tel: +49 711 6204378-44 Fax: +49 711 6204378-99 Geschaeftsfuehrung: Stefan Bellon, Thomas Eisenbarth, Sebastian Rummler Sitz der Gesellschaft: Stuttgart Registergericht: Amtsgericht Stuttgart, HRB 720590 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
