Re: [sqlite] [EXTERNAL] Internal Logic Error

[Hick Gunter]

You are not showing your schema or the statement you are executing. Also, 
Sqlite3_StdCall.dll is not maintained by the Sqlite development team. Can you 
reproduce the error while executing your statement in the sqlite3 shell?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Keith Hoar
Gesendet: Dienstag, 10. April 2018 17:38
An: sqlite-users 
Betreff: [EXTERNAL] [sqlite] Internal Logic Error

While watching the value of Err.LastDllError, as soon as the single line in the 
function below is executed, the value becomes 2. According to my documentation, 
this value indicates an "Internal Logic Error".



As this is a direct call to the SQLite DLL library, I am at loss as to the 
cause of the error. Can someone point me to the source/cause of the error and a 
way to correct it?



Public Function SQLite3Step(ByVal stmtHandle As Long) As Long

SQLite3Step = sqlite3_stdcall_step(stmtHandle)

End Function



' From the SQLite StdCall Imports:

Private Declare Function sqlite3_stdcall_step Lib "SQLite3_StdCall" Alias 
"_sqlite3_stdcall_step@4" (ByVal hStmt As Long) As Long





The file - SQLIte3.dll is dated:  3/3/2016 5:39 PM

The file - SQLite3_StdCall.dll is dated:  3/12/2016 8:28 PM








Keith Hoar

kah...@zhetosoft.com

WWW.Zhetosoft.com







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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

[Hick Gunter]

Your guess is wrong.

"pragma cache_size=2000" will limit the internal page cache to 2000 PAGES (of 
whatever page size the db file has) PER OPEN FILE. To set a limit of 2000kBytes 
PER OPEN FILE, use "pragma cache_size=-2000". The page cache is process private 
memory. Whatever portion is actually loaded into main memory (the RSS) counts 
towards PSS.

"pragma mmap_size=N" will limit the number of bytes that will be mapped into 
memory PER OPEN FILE. Memory mapped files may be shared between processes. Note 
that each process gets to determine which part(s) of a file it wants to map. 
Whatever portion of the file is actually loaded into main memory (the RSS) 
counts towards the PSS of all connected processes, divided by the number of 
processes sharing that specific page (on a page per page basis).

If you are running only 1 process AND it has page faulted the whole mmap 
segment into main memory, all of that counts towards PSS.
If you are running 2 processes AND they have mmaped the same region of the same 
file AND they have page faulted the whole segment into main memory, each 
process is held accountable for HALF of that size.

Reducing PSS via mmap is only possible if you are runnning multiple copies of 
the same application against the same db file.

There is no way to directly influence the PSS of a process, since that partly 
depends on ALL of the other processes.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nick
Gesendet: Mittwoch, 11. April 2018 08:52
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Does mmap increase PSS?

Hi,
I guess that "cache_size=2000" means PSS of my process will always less than 2M.
But, when I use PRAMGA mmap_size to enable mmap, I found the PSS of my process 
will almost the same as my db.
Is that correct?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Internal Logic Error

[Keith Hoar]

Deeper research revealed that the DLL Error is raised by the Excel error 
object.  I have passed the query on to the support email at GitHub.



Thanks for your reply.



 





Keith Hoar

kah...@zhetosoft.com

WWW.Zhetosoft.com





 On Tue, 10 Apr 2018 23:03:37 -0700 Hick Gunter h...@scigames.at 
wrote 




You are not showing your schema or the statement you are executing. Also, 
Sqlite3_StdCall.dll is not maintained by the Sqlite development team. Can you 
reproduce the error while executing your statement in the sqlite3 shell? 

 

-Ursprüngliche Nachricht- 

Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Keith Hoar 

Gesendet: Dienstag, 10. April 2018 17:38 

An: sqlite-users sqlite-users@mailinglists.sqlite.org 

Betreff: [EXTERNAL] [sqlite] Internal Logic Error 

 

While watching the value of Err.LastDllError, as soon as the single line in the 
function below is executed, the value becomes 2. According to my documentation, 
this value indicates an "Internal Logic Error". 

 

 

 

As this is a direct call to the SQLite DLL library, I am at loss as to the 
cause of the error. Can someone point me to the source/cause of the error and a 
way to correct it? 

 

 

 

Public Function SQLite3Step(ByVal stmtHandle As Long) As Long 

 

 SQLite3Step = sqlite3_stdcall_step(stmtHandle) 

 

End Function 

 

 

 

' From the SQLite StdCall Imports: 

 

Private Declare Function sqlite3_stdcall_step Lib "SQLite3_StdCall" Alias 
"_sqlite3_stdcall_step@4" (ByVal hStmt As Long) As Long 

 

 

 

 

 

The file - SQLIte3.dll is dated: 3/3/2016 5:39 PM 

 

The file - SQLite3_StdCall.dll is dated: 3/12/2016 8:28 PM 

 

 

 

 

 

 

 

 

Keith Hoar 

 

kah...@zhetosoft.com 

 

WWW.Zhetosoft.com 

 

 

 

 

 

 

 

___ 

sqlite-users mailing list 

sqlite-users@mailinglists.sqlite.org 

http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

 

 

___ 

 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0 

 

May be privileged. May be confidential. Please delete if not the addressee. 

___

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] Help using 'attach database' or Why is this an error?

[Peter Da Silva]

Try this:

attach database 'test.db' as con2;

On 4/11/18, 9:51 AM, "sqlite-users on behalf of J Decker" 
 
wrote:

When I attempt to use attach database I get an error about 'no such column'


M:\sqlite3\sqlite3\build>sqlite3 test.db

SQLite version 3.23.0 2018-04-02 11:04:16es
Enter ".help" for usage hints.
sqlite> attach database test.db as con2;
Error: no such column: test.db
sqlite>


I was trying to debug this set of commands, which is actually run on two
connections within the same process... (oh I had misspelled foreign as
foriegn)




PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

#SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
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] Insert with foreign keys enabled prevented in transaction

[J Decker]

 I was trying to debug this set of commands, which is actually run on
two
connections within the same process... but that detail apparently
doesn't matter.



PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2`
UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE
CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT
`value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where
type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

--SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with foreign keys enabled prevented in transaction

[Clemens Ladisch]

J Decker wrote:
> foreign key mismatch - "option4_map" referencing "option4_name"
>
> create table `option4_name` (`name_id` char(36) NOT NULL,
> ...
> create table `option4_map` (...
> FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)

name_id in the parent table must be the primary key (or at least be unique).


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

Re: [sqlite] Help using 'attach database' or Why is this an error?

[J Decker]

On Wed, Apr 11, 2018 at 7:54 AM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Try this:
>
> attach database 'test.db' as con2;
>
ahh that makes sense.
I'll split this into a separte thread for the real issue...


>
> On 4/11/18, 9:51 AM, "sqlite-users on behalf of J Decker" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of d3c...@gmail.com>
> wrote:
>
> When I attempt to use attach database I get an error about 'no such
> column'
>
>
> M:\sqlite3\sqlite3\build>sqlite3 test.db
>
> SQLite version 3.23.0 2018-04-02 11:04:16es
> Enter ".help" for usage hints.
> sqlite> attach database test.db as con2;
> Error: no such column: test.db
> sqlite>
>
>
> I was trying to debug this set of commands, which is actually run on
> two
> connections within the same process... (oh I had misspelled foreign as
> foriegn)
>
>
>
>
> PRAGMA foreign_keys=on;
>
> create table `option4_name` (`name_id` char(36) NOT NULL,`name`
> varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);
>
> create table `option4_map` (`option_id` char(36) NOT
> NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id`
> char(36)
> NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2`
> UNIQUE
> (`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
> REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE
> CASCADE,
> FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
> CASCADE ON DELETE CASCADE);
>
> create table `option4_values` (`option_id` char(36) default
> '0',`string`
> varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT
> `value_id`
> UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
> (`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
> DELETE CASCADE);select tbl_name,sql from sqlite_master where
> type='table'
> and
>
> BEGIN TRANSACTION;
>
> insert into option4_name (name_id,name) values(
> '82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );
>
> insert into option4_map
> (option_id,parent_option_id,name_id)values('--
> --','----
> ','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
> );
>
> #SQLITE ERROR:Result of prepare failed? foreign key mismatch -
> "option4_map" referencing "option4_name" at char 185[] in [insert into
> option4_map
> (option_id,parent_option_id,name_id)values('--
> --','----
> ','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
> )]
> ___
> 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] Help using 'attach database' or Why is this an error?

[J Decker]

When I attempt to use attach database I get an error about 'no such column'


M:\sqlite3\sqlite3\build>sqlite3 test.db

SQLite version 3.23.0 2018-04-02 11:04:16es
Enter ".help" for usage hints.
sqlite> attach database test.db as con2;
Error: no such column: test.db
sqlite>


I was trying to debug this set of commands, which is actually run on two
connections within the same process... (oh I had misspelled foreign as
foriegn)




PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map
(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

#SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map
(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraints must be defined last?

[Simon Slavin]

On 11 Apr 2018, at 6:41pm, J Decker  wrote:

> Is there something about SQL that requires constraints to follow all column
> definitions?

I don't know if it applies to SQL in general, but it is in SQLite:



The table constraints come after the column definitions.

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

Re: [sqlite] Constraints must be defined last?

[Lifepillar]

On 11/04/2018 19:45, Simon Slavin wrote:

On 11 Apr 2018, at 6:41pm, J Decker  wrote:


Is there something about SQL that requires constraints to follow all column
definitions?


I don't know if it applies to SQL in general, but it is in SQLite:



The table constraints come after the column definitions.


That is a SQLite restriction. According to the SQL standard,
column definitions and table constraints can appear in any order.
In fact, PostgreSQL accepts the following:

  create table T (
constraint foo check (x > 0),
x integer primary key
  );

Life.

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

[sqlite] Constraints must be defined last?

[J Decker]

Is there something about SQL that requires constraints to follow all column
definitions?


sqlite> create table `option4_blobs` (`option_id` char(36) default '0',
CONSTRAINT `value_id` UNIQUE (`option_id`),`binary` blob, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE)
   ...> ;
Error: near "`binary`": syntax error

sqlite> create table `option4_blobs` (`option_id` char(36) default
'0',`binary` blob, CONSTRAINT `value_id` UNIQUE (`option_id`), FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE)
   ...> ;
-- works
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[sandu]

Simon Slavin-3 wrote
> https://www.sqlite.org/src/artifact?ci=trunkfilename=ext/icu/README.txt;

However the example described here is a little bit simplistic:

lower('I', 'en_us') -> 'i'
lower('I', 'tr_tr') -> 'ı' (small dotless i)

You depend on the encoding of the text editor, which has to match encoding
of the SQLite database. A more realistic example would be:

lower((BLOB CAST AS TEXT), 'tr_tr')
where the BLOB hast to be the string in one of the supported encodings:
UTF8, UTF16, UTF16le, UTF16be.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraining FTS5 results based on offsets()

[Miroslav Marangozov]

Thank you Dan!
This should be much easier to implement than a full blown virtual table, but as 
far as I can tell(as I've mentioned, I'm not an expert in this) it doesn't do 
exactly what I want. I need all the locations of the search phrases, subject to 
the attribute constraint, in a document, but this query will only return the 
spans that contain one or more instance of the phrase. Or maybe I'm missing 
something?

Provided my understanding is correct, what do you think would be most the 
pragmatic way to change the query, so that it returns the locations of the 
phrases? What I imagine could work, even though it's admittedly ugly, is to 
write another UDF, let's call it "hacky_offsets()", that has some sort of 
"private" communication channel with custom_fts5_function(). So the query will 
be something like:

   SELECT fts5tbl.rowid, hacky_offsets()
   FROM fts5tbl('SQLite+rocks'), spans
   WHERE ft5tbl.rowid=spans.doc_id
AND spans.attrib_id = 'bold'
AND custom_fts5_function(fts5tbl, spans.start_tok, spans.end_tok);

When custom_fts5_function() finds instances of the phrases that fall between 
start_tok/end_tok it records these instances into a location hacky_offsets() 
knows about, so it can read them from there and return them to the application.
Would this even work and can you think of a more idiomatic/elegant 
implementation?

Thanks!



From: sqlite-users  on behalf of 
Dan Kennedy 
Sent: Tuesday, April 10, 2018 11:49 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Constraining FTS5 results based on offsets()

On 04/08/2018 02:00 PM, miroslav.marango...@outlook.com wrote:
> Hi,
> For my use case I need to index some texts, and I also need be able to assign 
> attributes to sub-sequences/spans of tokens in the texts. I want to be able 
> to search only for keywords/phrases that occur in spans with a certain 
> attribute. As an example, imagine we have a set of rich text documents and we 
> want to find the locations of the "SQLite rocks" phrase in that set, but not 
> just any instance of it- only those that have an attribute "bold".
>
> The general idea, I'm considering at the moment is as follows:
> 1) create a table 'spans' with columns (doc_id, attrib_id, start_tok, 
> end_tok), where doc_id is equal to the corresponding rowid in the FTS table, 
> and start_tok and end_tok are the 0-based offsets that delimit a span
> 2) issue a match (sub?)query against the FTS table and obtain a list of 
> (rowid, list-of-offsets in that doc)
> 3) somehow convert the above results into (rowid, start_offset, end_offset) 
> for each entry of the list-of-offsets
> 4) join the results from 3) with the "spans" table on "rowid=spans.doc_id" 
> where "spans.attrib_id=X and start_offset >= spans.start_pos and end_offset 
> <= spans.end_pos"
>
> Do you think this general approach makes sense, and how would you approach 
> the problem if not? I only have a cursory knowledge of both SQL and SQLite at 
> this point, so it's quite possible I'm missing something obvious.
>
> On the implementation side:
> - as far as I understand FTS5 has some clear advantages over FTS3/4. 
> Apparently there isn't readily available offsets() function in FTS5 yet, but 
> the API should make writing one rather straightforward
>
> - step 3) is what I wonder about the most... What would be a good way to 
> convert a (doc_id, ) row into (doc_id, start_offset, 
> end_offset) tuples for every entry in the list? I'd guess I will have to 
> implement some sort of virtual table(a.k.a. "table-valued function")? Perhaps 
> a table-valued function that takes a FTS query as its parameter that it then 
> uses to obtain (doc_id, ), and expose each hit as (doc_id, 
> start_offset, end_offset) rows in the virtual table?
>
> Any thoughts and ideas would be greatly appreciated.

Maybe just implement an FTS5 function to do the filtering in step 4
above. So that your query looks like:

   SELECT * FROM fts5tbl('SQLite+rocks'), spans
   WHERE ft.rowid=spans.doc_id
 AND spans.attrib_id = 'bold'
 AND custom_fts5_function(fts5tbl, spans.start_tok, spans.end_tok);

where custom_fts5_function() returns true if there are any phrase hits
with token offsets between its two trailing arguments. Or whatever it is
you require.

   https://sqlite.org/fts5.html#custom_auxiliary_functions

Dan.





>
> Thank you!
> ___
> 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

[sqlite] Does mmap increase PSS?

[Nick]

Hi,
I guess that "cache_size=2000" means PSS of my process will always less than
2M.
But, when I use PRAMGA mmap_size to enable mmap, I found the PSS of my
process will almost the same as my db. 
Is that correct?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users