Re: [sqlite] Tracking item history using SQLite
On Fri, Aug 30, 2019 at 3:35 PM Keith Medcalf wrote: > Seems fine, other than that event.first_seen and event.last_seen can be NULL, > in which case that field will never be updated. So while you may claim that > you never store NULL in those fields, doing so will cause non-workage due to > integrity failure, Good point. There are no code paths that could put NULL in there now, but as you say, I should make sure that's the case in the design, in case the insert logic changes in the future. Thanks for catching this. > and the purpose of a DBMS is to enforce integrity. And thanks a ton for this comment, I need to get in this mentality, clearly. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tracking item history using SQLite
On Friday, 30 August, 2019 14:45, Random Coder wrote: >CREATE TABLE IF NOT EXISTS >event( >event_id TEXT PRIMARY KEY, >first_seen INTEGER, >last_seen INTEGER >) WITHOUT ROWID; So first_seen and last_seen are permitted to be NULL? >INSERT INTO event(event_id, first_seen, last_seen) VALUES(?, ?, ?) >ON CONFLICT(event_id) DO UPDATE SET >first_seen = MIN(excluded.first_seen, hashes.first_seen), >last_seen = MAX(excluded.last_seen, hashes.last_seen); MAX(NULL, ) -> NULL MIN(NULL, ) -> NULL > ATTACH ':memory:' AS mem_db; > CREATE TABLE mem_db.valid(event_id TEXT PRIMARY KEY); >And populate that table with the events the user is interested in. >I'm doing this since I won't get the list of items in one list, it's >built up over some minutes. If the system running dies in the middle >of a request, it's OK to start over. Then I run: >SELECT >mem_db.valid.event_id, >event.first_seen, >event.last_seen >FROM >mem_db.valid >LEFT JOIN event ON >event.event_id = mem_db.valid.event_id; >And gather up the results and pretty them up for the user. >Does all of this seem valid? It works, so I'm OK with it, but I'm >far from a SQLite expert, and I want to know if I'm going to be >backing myself into a corner or otherwise torturing things that >should be done differently. Seems fine, other than that event.first_seen and event.last_seen can be NULL, in which case that field will never be updated. So while you may claim that you never store NULL in those fields, doing so will cause non-workage due to integrity failure, and the purpose of a DBMS is to enforce integrity. >Thanks for any feedback. Just my 2 cents. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tracking item history using SQLite
First off, if this sort of "code review" style question is inappropriate for this list, please feel free to reply to me directly and tell me to stop, and I'll know to not do this again. That said, I have a question on the use of SQLite. At the risk of falling into the XY problem, I'll give some details on what i'm doing, and then how I'm doing it. My basic question is if what I'm doing is valid, and if I'm doing anything needlessly wasteful. I have a system monitoring events (and I know I'm being vague on the exact nature of these events, I can't provide too many details, sorry. Please try to accept what I say as given about them). The events have IDs that are generated externally, they're 30-40 ascii characters long, appear random, and known to be unique by external means for a given event. For the purposes of this particular problem, the only thing I care about tracking is when I first saw an event, and the last time I saw it. For better or worse, this ecosystem already thinks about timestamps as the number of minutes since a specific epoch, and is used to treating all time values as an integer in that space, I'm doing the same here. So, I have a RESTful server written in Python, using APSW to create a simple SQLite database: CREATE TABLE IF NOT EXISTS event( event_id TEXT PRIMARY KEY, first_seen INTEGER, last_seen INTEGER ) WITHOUT ROWID; Every time a new event comes in (they might be very out of order), I do a: INSERT INTO event(event_id, first_seen, last_seen) VALUES(?, ?, ?) ON CONFLICT(event_id) DO UPDATE SET first_seen = MIN(excluded.first_seen, hashes.first_seen), last_seen = MAX(excluded.last_seen, hashes.last_seen); To create the record for the event if it's new, or possibly update an existing one with new values. To give a sense of scale, I have around 5 billion events stored right now for the past couple of years in a 250gb database, and I see around 20 million per day, some small percentage of those are new. The important thing I can do for users is pull up reports. The report is roughly a summary of how old events are (when they were first seen, and how long they've been seen for). Outliers are highlighted, as are events that haven't been seen at all. The user will provide around ten thousand event IDs, the majority of them, approaching 99%, will exist in my database. When the user requests a report, I create an in memory database: ATTACH ':memory:' AS mem_db; CREATE TABLE mem_db.valid(event_id TEXT PRIMARY KEY); And populate that table with the events the user is interested in. I'm doing this since I won't get the list of items in one list, it's built up over some minutes. If the system running dies in the middle of a request, it's OK to start over. Then I run: SELECT mem_db.valid.event_id, event.first_seen, event.last_seen FROM mem_db.valid LEFT JOIN event ON event.event_id = mem_db.valid.event_id; And gather up the results and pretty them up for the user. Does all of this seem valid? It works, so I'm OK with it, but I'm far from a SQLite expert, and I want to know if I'm going to be backing myself into a corner or otherwise torturing things that should be done differently. Or, if the answer is: "Don't use SQLite for that", that's fine too, I'll start looking at other options. Thanks for any feedback. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error: unable to read database header
When you run ".open test.db" if the database doesn't already exist, then the file is created. However *no data is written yet* as there are a few permanent things you could change at the very start (like page size, encoding, etc), so it holds off on writing the first page with the header until you issue some sort of statement that isn't setting those "new database options" So since the file is still 0 size at this point, there is no header for .dbinfo to read from. If you create a table first for example, then it will populate the header and actually write to the file, at which point there will be a header for .dbinfo to read. -Original Message- From: sqlite-users On Behalf Of Alexander Gabriel Sent: Friday, August 30, 2019 10:54 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] error: unable to read database header Hi I downloaded the files for v3.29.0 into a folder. Then double clicked sqlite3.exe to open a command shell. Then typed `.open test.db` Then typed `.dbinfo` And got: `unable to read database header` What am I doing wrong? Have I created a broken database? Alex ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .save always errors: database is locked
Running sqlite3 will open up an in-memory database to start with. ".open test" will either open the file "test" if it exists, or create the file if it doesn't. You will then be working on that file called "test" which is your database. The help line for .save is: .save FILE Write in-memory database into FILE ".save test" tries to save the current database to the file "test". Since you already have the file "test" open in the CLI tool, when it tries to open it a second time to run the .save command it's getting the "database is locked" error. In fact it seems weird it lets you try at all since at that point you don't have an in-memory database open, but have an actual file open. Once you do the ".open test" anything you do is done on the "test" file/database. There is no need to save it with .save. "saving" is all through normal database transactions at that point. -Original Message- From: sqlite-users On Behalf Of Alexander Gabriel Sent: Friday, August 30, 2019 12:11 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] .save always errors: database is locked Hi I do this: - restart windows10 or macOS and log in (to guarantee no other process is using sqlite3) - cd to the folder where sqlite3 v3.29.0 is contained - type `sqlite3` (windows 10) or `./sqlite3` (macOS) to start sqlite3 - type `.open test`, followed by `.save test` Result: `Error: database is locked` What am I doing wrong? How can I change configuration if I can never save changes? The only time I can save without an error is when I `.save test2` without having opened it before. Alex ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .save always errors: database is locked
On 8/30/19 9:11 AM, Alexander Gabriel wrote: > Hi > > I do this: > >- restart windows10 or macOS and log in (to guarantee no other process >is using sqlite3) >- cd to the folder where sqlite3 v3.29.0 is contained >- type `sqlite3` (windows 10) or `./sqlite3` (macOS) to start sqlite3 >- type `.open test`, followed by `.save test` > > Result: `Error: database is locked` > > What am I doing wrong? > How can I change configuration if I can never save changes? > > The only time I can save without an error is when I `.save test2` without > having opened it before. > > Alex Databases are different than things like Word Documents. When you issue command that modify the database, the database on disk is immediately changed (subject to being in a transaction that can be rolled back), so you don't need to 'save' a database after working with it. The 'Save' command is basically similar to the 'Save As' command for a document. Since the database is current open, trying to save back over the original copy can't be done, as it is open for reading, and also doesn't need to be done. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .save always errors: database is locked
Hi I do this: - restart windows10 or macOS and log in (to guarantee no other process is using sqlite3) - cd to the folder where sqlite3 v3.29.0 is contained - type `sqlite3` (windows 10) or `./sqlite3` (macOS) to start sqlite3 - type `.open test`, followed by `.save test` Result: `Error: database is locked` What am I doing wrong? How can I change configuration if I can never save changes? The only time I can save without an error is when I `.save test2` without having opened it before. Alex ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] error: unable to read database header. Also: My mails are being held back due to subscription to list not having worked
Hi *error: unable to read database header* Sorry for spamming this list but as it is an email list I cannot add to my original issue as for instance in github. As mentioned in my previous email I have donwloaded binaries for windows 10, run `.open test` followed by `.dbinfo` and got this error: `unable to read database header`. There is an old issue from v3.20.1 that reported the same error which seemed to be a build issue which later was corrected if I understand correctly: https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106549.html I have tried: - installing sqlite3 in ubuntu on windows by copying the binaries and running `sqlite3` as per your documentation. Result: sqlite3 not installed - installing sqlite3 in ubuntu on windows by copying the binaries and running `./sqlite3`. Result: `bash: ./sqlite3: cannot execute binary file: Exec format error` - installing sqlite3 in ubuntu on windows by using `sudo apt-get update` then `sudo apt-get install sqlite3`. Result: v2.8.17 installed. I do not want to use such an old version - installing sqlite3 on macOS by copying the binaries and running `sqlite3` as per your documentation. Result: sqlite3 not installed - installing sqlite3 on macOS by copying the binaries and running `./sqlite3`. Works. But same error as in Windows 10 when running `.open test` followed by `.dbinfo`: `unable to read database header` I have also tried to access binaries for older versions like 3.28.0. Maybe this error does not occur there? After all it did not occur last year when I worked with v3.27.2. But it seems binaries for older versions do not exist. I have also tried a few more things like searching google. Unfortunately it seems that it is MUCH harder to find help for issues concerning sqlite3 than for any other library I have used so far. So after a few hours I am left with a db file created that cannot show .dbinfo and makes me feel real bad. Can I trust it and use it? *Subscription to List not working* I posted the following email to sqlite-us...@sqlite.org (this email was explicitly shown somewhere on the sqlite.org site): --- I signed in to http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users. I got the confirmation email for sqlite-users-requ...@mailinglists.sqlite.org and confirmed. But every time I try to log in I get this error: > *sqlite-users roster authentication failed.* --- but got this answer: --- This is the mail system at host relay.mailchannels.net. I'm sorry to have to inform you that your message could not be delivered to one or more recipients. It's attached below. For further assistance, please send mail to postmaster. If you do so, please include this problem report. You can delete your own text from the attached returned message. --- I also sent a first error message to sqlite-users-ow...@mailinglists.sqlite.org and got this answer: --- Your mail to 'sqlite-users' with the subject error: unable to read database header Is being held until the list moderator can review it for approval. The reason it is being held: Post by non-member to a members-only list Either the message will get posted to the list, or you will receive notification of the moderator's decision. If you would like to cancel --- It seems that the fact that signing up to the list seems not to work (in my case?) also may prevent me from mailing to the list :-( I feel sqlite3 is making it REALLY hard to get a working version of sqlite3 if a published version happens to be faulty. I realize this is open source and I have no right to complain. But maybe it could be made easier to report issues in a timely fashion? Alex ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] error: unable to read database header
Hi I downloaded the files for v3.29.0 into a folder. Then double clicked sqlite3.exe to open a command shell. Then typed `.open test.db` Then typed `.dbinfo` And got: `unable to read database header` What am I doing wrong? Have I created a broken database? Alex ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected REINDEX behavior.
On 8/30/19 3:42 PM, David Raymond wrote:
> Sorry if my mind is working slowly today, but why are those showing up as a
> difference when they're exactly the same line?
>
>
> The only difference between both runs:
>> $ diff run1 run2
>> 1260d1259
>> < INSERT INTO space_used
>> VALUES('idx_words1','words',1,0,551407,550067,3,3826907,0,0,7,6,1341,0,5816,14208,0,1282,5517312);
>> 1270a1270
>>> INSERT INTO space_used
>>> VALUES('idx_words1','words',1,0,551407,550067,3,3826907,0,0,7,6,1341,0,5816,14208,0,1282,5517312);
Because the line has been moved ;)
--
Best regards
dirdi
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected REINDEX behavior.
Sorry if my mind is working slowly today, but why are those showing up as a
difference when they're exactly the same line?
The only difference between both runs:
> $ diff run1 run2
> 1260d1259
> < INSERT INTO space_used
> VALUES('idx_words1','words',1,0,551407,550067,3,3826907,0,0,7,6,1341,0,5816,14208,0,1282,5517312);
> 1270a1270
>> INSERT INTO space_used
>> VALUES('idx_words1','words',1,0,551407,550067,3,3826907,0,0,7,6,1341,0,5816,14208,0,1282,5517312);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dqlite 1.0.0 - SQLite replication and failover library
> From: Free Ekanayaka, on Thursday, August 29, 2019 10:21 AM, wrote... > > Hello Jose, > > Jose Isaias Cabrera, on > > which lets me know that it linux/unix based. But, is Windows an > > option also? Thanks. > > At the moment Windows is not an option, mainly because under the hood > dqlite uses a Linux-specifc asynchronous file system API (io_submit) not > available on Windows. However, such code lives behind a pluggable > interface that could be implemented on other OSs. How about cygwin? :-) Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation addition request for FTS3 / FTS4 phrase search
Hi, please add to the documentation that column limited phrase search is not supported by FTS3 and FTS4. Example: CREATE VIRTUAL TABLE FTSTable USING FTS3 (content,body); SELECT * FROM FTSTable WHERE FTSTable MATCH 'content:"one two"'; Does not result in an error but also returns no results. Regards, Hardy ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?
Hi, yes, but unfortunately I have still a project using FTS3 tables to which I have to add new features.. Regards, Hardy > Am 2019-08-30 um 13:00 schrieb Dan Kennedy : > > > On 30/8/62 17:39, mailing lists wrote: >> Hi, >> >> but there is no token '^beta'! Even if in FTS3 '^' is not regarded as a >> special character. Is the '^' removed by the tokenizer? > > Right. It's removed by the tokenizer. > > FTS3/4 is focused on backwards compatibility. New work should really use FTS5 > - it's faster, has more features, is better tested and the query syntax is > properly defined. > > https://sqlite.org/fts5.html > > Dan. > > > >> Because it is regarded as a diacritical character? >> >> PS: I have to admit that I have overlooked the comment that '^' works only >> for FTS4 tables. >> >> Regards, >> Hardy >> >>> Am 2019-08-30 um 12:24 schrieb Dan Kennedy : >>> >>> >>> >>> The fts3/4 documentation says: >>> >>> "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed >>> with a "^" character. In this case, in order to match the token must appear >>> as the very first token in any column of the matching row." >>> >>> So change "FTS3" to "FTS4" and it will likely work. >>> >>> Dan. >>> >>> >>> On 30/8/62 16:31, mailing lists wrote: Hi, I could not find an example showing a result in the documentation, therefore I created one by myself: CREATE VIRTUAL TABLE myData USING FTS3(content); INSERT INTO myData 'alpha beta'; 1) SELECT * FROM myData WHERE myData MATCH 'beta'; Result: content alpha beta This is what I expected. 2) SELECT * FROM myData WHERE myData MATCH '^beta'; Result: content alpha beta This is what I did not expect. According to the documentation I expected to get no rows because the column begins with alpha and not with beta. What's the issue here? I tested these examples using version 3.24.0. Regards, Hardy ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> ___ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?
On 30/8/62 17:39, mailing lists wrote: Hi, but there is no token '^beta'! Even if in FTS3 '^' is not regarded as a special character. Is the '^' removed by the tokenizer? Right. It's removed by the tokenizer. FTS3/4 is focused on backwards compatibility. New work should really use FTS5 - it's faster, has more features, is better tested and the query syntax is properly defined. https://sqlite.org/fts5.html Dan. Because it is regarded as a diacritical character? PS: I have to admit that I have overlooked the comment that '^' works only for FTS4 tables. Regards, Hardy Am 2019-08-30 um 12:24 schrieb Dan Kennedy : The fts3/4 documentation says: "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed with a "^" character. In this case, in order to match the token must appear as the very first token in any column of the matching row." So change "FTS3" to "FTS4" and it will likely work. Dan. On 30/8/62 16:31, mailing lists wrote: Hi, I could not find an example showing a result in the documentation, therefore I created one by myself: CREATE VIRTUAL TABLE myData USING FTS3(content); INSERT INTO myData 'alpha beta'; 1) SELECT * FROM myData WHERE myData MATCH 'beta'; Result: content alpha beta This is what I expected. 2) SELECT * FROM myData WHERE myData MATCH '^beta'; Result: content alpha beta This is what I did not expect. According to the documentation I expected to get no rows because the column begins with alpha and not with beta. What's the issue here? I tested these examples using version 3.24.0. Regards, Hardy ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why is a multiple MATCH clause not allowed in an FTS query?
Hi,
I think that I have to be more specific here. Only under certain configurations
this MATCH-clause does not work (example):
CREATE VIRTUAL TABLE myDATA USING FTS3 (one,two,three,four);
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' AND
m2.myData MATCH 'two: alpha';
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' OR
m2.myData MATCH 'two: alpha';
Everything is OK so far. Now:
INSERT INTO myData VALUES('alpha','beta',NULL,NULL);
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' AND
m2.myData MATCH 'two: alpha';
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' OR
m2.myData MATCH 'two: alpha';
Error: unable to use function MATCH in the requested context
To solve the issue again:
DELETE FROM myData;
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' AND
m2.myData MATCH 'two: alpha';
SELECT * FROM myData m1, myData m2 WHERE m1.myData MATCH 'one: 42*' OR
m2.myData MATCH 'two: alpha';
Strange…
Hardy
PS: sqlite3 version 3.24.0
> Am 2019-08-30 um 09:43 schrieb mailing lists :
>
> Because of my (obviously wrong) automatic query generator I came across this
> issue:
>
> CREATE VIRTUAL TABLE myData USING FTS3 (content, body);
>
> SELECT * FROM myData WHERE (content MATCH 'one') AND (body MATCH 'two');
>
>
> What is the reason that the above query is not allowed and that
>
> SELECT * FROM myData WHERE myData MATCH 'content: one body: two';
>
> has to be used? Performance issues do not matter in this case.
>
> Regards,
> Hardy
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?
Hi, but there is no token '^beta'! Even if in FTS3 '^' is not regarded as a special character. Is the '^' removed by the tokenizer? Because it is regarded as a diacritical character? PS: I have to admit that I have overlooked the comment that '^' works only for FTS4 tables. Regards, Hardy > Am 2019-08-30 um 12:24 schrieb Dan Kennedy : > > > > The fts3/4 documentation says: > > "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed > with a "^" character. In this case, in order to match the token must appear > as the very first token in any column of the matching row." > > So change "FTS3" to "FTS4" and it will likely work. > > Dan. > > > On 30/8/62 16:31, mailing lists wrote: >> Hi, >> >> I could not find an example showing a result in the documentation, therefore >> I created one by myself: >> >> CREATE VIRTUAL TABLE myData USING FTS3(content); >> INSERT INTO myData 'alpha beta'; >> >> 1) >> SELECT * FROM myData WHERE myData MATCH 'beta'; >> >> Result: >> >> content >> alpha beta >> >> This is what I expected. >> >> 2) >> SELECT * FROM myData WHERE myData MATCH '^beta'; >> >> Result: >> >> content >> alpha beta >> >> This is what I did not expect. According to the documentation I expected to >> get no rows because the column begins with alpha and not with beta. >> >> What's the issue here? I tested these examples using version 3.24.0. >> >> Regards, >> Hardy >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?
The fts3/4 documentation says: "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed with a "^" character. In this case, in order to match the token must appear as the very first token in any column of the matching row." So change "FTS3" to "FTS4" and it will likely work. Dan. On 30/8/62 16:31, mailing lists wrote: Hi, I could not find an example showing a result in the documentation, therefore I created one by myself: CREATE VIRTUAL TABLE myData USING FTS3(content); INSERT INTO myData 'alpha beta'; 1) SELECT * FROM myData WHERE myData MATCH 'beta'; Result: content alpha beta This is what I expected. 2) SELECT * FROM myData WHERE myData MATCH '^beta'; Result: content alpha beta This is what I did not expect. According to the documentation I expected to get no rows because the column begins with alpha and not with beta. What's the issue here? I tested these examples using version 3.24.0. Regards, Hardy ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?
Hi, I could not find an example showing a result in the documentation, therefore I created one by myself: CREATE VIRTUAL TABLE myData USING FTS3(content); INSERT INTO myData 'alpha beta'; 1) SELECT * FROM myData WHERE myData MATCH 'beta'; Result: content alpha beta This is what I expected. 2) SELECT * FROM myData WHERE myData MATCH '^beta'; Result: content alpha beta This is what I did not expect. According to the documentation I expected to get no rows because the column begins with alpha and not with beta. What's the issue here? I tested these examples using version 3.24.0. Regards, Hardy ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why is a multiple MATCH clause not allowed in an FTS query?
Because of my (obviously wrong) automatic query generator I came across this issue: CREATE VIRTUAL TABLE myData USING FTS3 (content, body); SELECT * FROM myData WHERE (content MATCH 'one') AND (body MATCH 'two'); What is the reason that the above query is not allowed and that SELECT * FROM myData WHERE myData MATCH 'content: one body: two'; has to be used? Performance issues do not matter in this case. Regards, Hardy ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
