Re: [sqlite] FTS problem with 'NOT term' queries
The explanation right above that table of examples contains these important (I believe) phrases: ...BINARY SET operators... ...TWO operands to an operator... (emphasis mine) On Tue, Jan 29, 2013 at 11:30 PM, Alexey Pechnikov wrote: > SELECT * FROM docs WHERE docs MATCH 'NOT -- -- -- --Ô¿Ô-- K e V i N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS problem with 'NOT term' queries
On 1/29/2013 11:30 PM, Alexey Pechnikov wrote: From http://www.sqlite.org/fts3.html#section_3_1 we can see the query SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite'; But the equal query doesn't works: SELECT * FROM docs WHERE docs MATCH 'NOT sqlite AND database'; Error: malformed MATCH expression: [NOT sqlite AND database] And as result it's impossible to search docs in some situations: SELECT * FROM docs WHERE docs MATCH 'NOT sqlite'; Error: malformed MATCH expression: [NOT sqlite] As far as I can tell, in MATCH syntax NOT is a binary operator, denoting set difference. You are trying to use it as a unary operator. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS problem with 'NOT term' queries
Hello! From http://www.sqlite.org/fts3.html#section_3_1 we can see the query SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite'; But the equal query doesn't works: SELECT * FROM docs WHERE docs MATCH 'NOT sqlite AND database'; Error: malformed MATCH expression: [NOT sqlite AND database] And as result it's impossible to search docs in some situations: SELECT * FROM docs WHERE docs MATCH 'NOT sqlite'; Error: malformed MATCH expression: [NOT sqlite] -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] secure_delete versus journal_mode persist.
On Tue, Jan 29, 2013 at 7:52 AM, Yuriy Kaminskiy wrote: > User-space database encryption and all "safe data erase" tools only > provide you > with warm feeling of safety without any real security. I agree. I am merely pointing out that I think that this result may be surprising in the case where you decided to use secure_delete, without regard to how you came to that decision. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] secure_delete versus journal_mode persist.
Simon Slavin wrote:
> On 29 Jan 2013, at 8:19am, Scott Hess wrote:
>
>> insert into x values ('SQLite is a software library that implements
>> a self-contained, serverless, zero-configuration, transactional SQL
>> database engine. SQLite is the most widely deployed SQL database engine in
>> the world. The source code for SQLite is in the public domain.');
>> -- Repeat that four or five times.
>>
>> Now, somewhere else, running:
>> strings trial.db-journal
>> gives me various repetitions of the inserted string present in the journal
>> file. This applies even after running 'delete from x;', at which point the
>> main database itself will not contain these strings.
>
> Verified here with version 3.7.12, native with OS X 10.8 installation.
>
> Can't be helped at all up to closing the connection to the database.
>
> Once the database connection is closed, still being able to see that text is
> a consequence of 'journal_mode = persist' or WAL. I think that this also
> can't be helped -- it's inherent in the journal file not being wiped -- but
> this is more debatable than the state before the connection is closed.
>
> The only way to prevent the data being available from a disk-read with
> sufficient privileges would be to use "journal_mode = MEMORY". Might be
> worth adding a comment to the documentation for the secure_delete PRAGMA.
IMO, both secure_delete and any user-space disk encryption is pointless and
wasteful security theatre.
What you can do with traces in *filesystem* journal (e.g. data=journal)? What
about defragmentation/volume resize/other block relocation?
If you use SSD, what you can do with block relocation always used in normal
work? (If you use HDD - that can also happen; much more rarely, and usually just
before drive dies, but still possibility).
If you want to defend against other users, setting proper permissions is
sufficient; to improve protection - run all potentially vulnerable services on
dedicated accounts, use jail/chroot/containers/VM/etc.
If you want real encryption, you use full-disk encryption (like TrueCrypt, LUKS,
and their ilk), and encrypt everything at once including any temporary files,
swap, hibernation images, etc.
If you want defend against other processes running under same user or against
root, you lost anyway. They can read anything they need right from memory (e.g.
ptrace()), and completely hijack control over your process (keylogger/etc).
User-space database encryption and all "safe data erase" tools only provide you
with warm feeling of safety without any real security.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [System.Data.SQLite] Extension datatypes (i.e. Spatialite geometry)
Hi,
thanks for your answer again (again I was unclear in my question, sorry
for that - I am using "ST_GeomFromText('LINESTRING(APPROPRIATE_VALUES)',
APPROPRIATE_SRID)" ).
I think I found the problem:
My parametrized command text looks like:
"INSERT INTO RoadSegments (Id, Geometry) VALUES (@IdParam, @GeometryParam)"
An example for the command parameters would then be:
IdParam - "1"
GeometryParam - "ST_GeomFromText('LINESTRING(APPROPRIATE_VALUES)',
APPROPRIATE_SRID)"
Where it should be:
"INSERT INTO RoadSegments (Id, Geometry) VALUES (@IdParam,
ST_GeomFromText(@GeometryWKTParam, APPROPRIATE_SRID))"
and the command parameter values:
IdParam - "1"
GeometryWKTParam - "'LINESTRING(APPROPRIATE_VALUES)'"
I will try this and if it works I will post the answer on the
spatialite-us...@googlegroups.com mailing list.
Thanks for your time and helping me, even if this is not the correct
place to ask :)
bye Markus
Am 29.01.2013 15:21, schrieb a.furi...@lqt.it:
Hi Mark,
Currently I am converting my C# polygons to a string of the form
"LINESTRING(x1 y1, ..., xN yN)" which would be the WKT (Well-Known
Text) format.
If I understand your answer correct I could simply convert my C#
polygons to the WKB (Well-Known Binary) format and use BLOB as the
DbType in my SQLiteParameter.
Is that correct?
no, this is not correct.
be very careful: both WKT and WKB are intended as standardized notations
representing Geometries, but they are not the same of the "binary
internal
BLOB format" supported by SpatiaLite.
anyway you can use the following standard Spatial SQL functions in order
to correctly serialize / deserialize the internal Geometries format:
to/from WKT notation: ST_AsText() / ST_GeomFromText()
to/from WKB notation: ST_AsBinary() / ST_GeomFromWKB()
bye Sandro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [System.Data.SQLite] Extension datatypes (i.e. Spatialite geometry)
Hi Mark, Currently I am converting my C# polygons to a string of the form "LINESTRING(x1 y1, ..., xN yN)" which would be the WKT (Well-Known Text) format. If I understand your answer correct I could simply convert my C# polygons to the WKB (Well-Known Binary) format and use BLOB as the DbType in my SQLiteParameter. Is that correct? no, this is not correct. be very careful: both WKT and WKB are intended as standardized notations representing Geometries, but they are not the same of the "binary internal BLOB format" supported by SpatiaLite. anyway you can use the following standard Spatial SQL functions in order to correctly serialize / deserialize the internal Geometries format: to/from WKT notation: ST_AsText() / ST_GeomFromText() to/from WKB notation: ST_AsBinary() / ST_GeomFromWKB() bye Sandro -- Il messaggio e' stato analizzato alla ricerca di virus o contenuti pericolosi da MailScanner, ed e' risultato non infetto. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [System.Data.SQLite] Extension datatypes (i.e. Spatialite geometry)
Hi Sandro,
thanks for your answer - I will post the question (possibly including
the solution) on the appropriate mailing list.
Actually, my columns are defined the way you suggested - I will make it
more clear in my post to the spatialite mailing-list.
But your answer sounds interesting - so I hope you could answer one
further question:
Currently I am converting my C# polygons to a string of the form
"LINESTRING(x1 y1, ..., xN yN)" which would be the WKT (Well-Known Text)
format.
If I understand your answer correct I could simply convert my C#
polygons to the WKB (Well-Known Binary) format and use BLOB as the
DbType in my SQLiteParameter.
Is that correct?
Am 29.01.2013 14:48, schrieb a.furi...@lqt.it:
On Tue, 29 Jan 2013 14:22:07 +0100, Markus Dibo wrote:
Hello dear (System.Data.)SQLite users,
I am using System.Data.SQLite in combination with Spatialite which
works very well.
One problem I currently have ...
Hi Markus,
I suppose that posting such questions into the SpatiaLite own mailing
list [1] would probably be much more appropriate.
[1] https://groups.google.com/forum/?fromgroups#!forum/spatialite-users
just a very quick answer:
trying to insert several milions of records (namely road segments with
just two columns: Id INTEGER, Geometry LINESTRING)
sorry, but this is not the expected way to declare a Geometry column;
the syntax expected by SpatiaLite is:
CREATE xxx (Id INTEGER);
SELECT AddGeometryColumn('xxx', 'Geometry', 4321, 'LINESTRING', 'XY');
Now, the problem is that I don't have a fitting DbType for LINESTRING
(or other GEOMETRY types).
from the SQLite own perspective all Geometries simply are generic BLOBs;
in order to activate any kind of specific support for Geometry,
Linestring,
Polygon or Point you absolutely have to explicitly call the "special"
Spatial
SQL functions supported by the SpatiaLite extension.
bye Sandro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [System.Data.SQLite] Extension datatypes (i.e. Spatialite geometry)
On Tue, 29 Jan 2013 14:22:07 +0100, Markus Dibo wrote:
Hello dear (System.Data.)SQLite users,
I am using System.Data.SQLite in combination with Spatialite which
works very well.
One problem I currently have ...
Hi Markus,
I suppose that posting such questions into the SpatiaLite own mailing
list [1] would probably be much more appropriate.
[1] https://groups.google.com/forum/?fromgroups#!forum/spatialite-users
just a very quick answer:
trying to insert several milions of records (namely road segments
with
just two columns: Id INTEGER, Geometry LINESTRING)
sorry, but this is not the expected way to declare a Geometry column;
the syntax expected by SpatiaLite is:
CREATE xxx (Id INTEGER);
SELECT AddGeometryColumn('xxx', 'Geometry', 4321, 'LINESTRING', 'XY');
Now, the problem is that I don't have a fitting DbType for LINESTRING
(or other GEOMETRY types).
from the SQLite own perspective all Geometries simply are generic
BLOBs;
in order to activate any kind of specific support for Geometry,
Linestring,
Polygon or Point you absolutely have to explicitly call the "special"
Spatial
SQL functions supported by the SpatiaLite extension.
bye Sandro
--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [System.Data.SQLite] Extension datatypes (i.e. Spatialite geometry)
Hello dear (System.Data.)SQLite users, I am using System.Data.SQLite in combination with Spatialite which works very well. One problem I currently have is that when I'm trying to insert several milions of records (namely road segments with just two columns: Id INTEGER, Geometry LINESTRING) it takes a very long time (about an hour or more). The index on the Geometry column is created after all records are inserted and the whole insertion process is encapsulated in one big transaction. I was then trying to reuse the same insertion-command (SQLiteCommand) with two parameters for the Id and the Geometry (doing this for non-spatial tables resulted in a significant performance boost). Now, the problem is that I don't have a fitting DbType for LINESTRING (or other GEOMETRY types). Using a parameter without specifying the DbType then yields an exception. I also tryed using DbType.String which also caused an exception. So my question is: Is there any possibility to use commands with command parameters to insert spatial data with System.Data.SQLite? PS: This is my first time writing to a mailing list so I hope the form is right. Best regards Markus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug report: out of memory error for most operations on a large database
As per your questions:
1. I do not use sqlite from C or some other language. The calls are
from the command line using sqlite3 command line utility. Hence no
sqlite3_memory_used calls. Memory used by sqlite3 process was obtained by
eyeballing the sqlite3 process in Windows task manager.
2. I have appended at the bottom of this mail the previous report,
with the table schema and the query which results in memory problem (as well
as sqlite analyzer results for the database).
3. [Over the weekend I have also tried to run with different settings
for journal mode and synchronous - no change in results. As well as running
on a windows server 2012 in the cloud, with no other software installed - no
change in results]
4. Thus, I believe, we have only three elements involved: the database
file containing one table, the query and SQLite 3 command line utility.
5. I do not understand the internals of sqlite3. What baffles me is
different memory use profile when ran with cache_size 2,000 pages and
cache_size 10,000 pages - constant in the former and linearly growing with
the latter.
From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard
Hipp
Sent: Monday, January 28, 2013 6:18 AM
To: ammon_lympha...@hotmail.com; General Discussion of SQLite Database
Cc: Michael Black
Subject: Re: [sqlite] bug report: out of memory error for most operations on
a large database
On Sun, Jan 27, 2013 at 2:57 PM, ammon_lymphater
mailto:ammon_lympha...@hotmail.com> > wrote:
I think I localized the bug - potential query memory use with larger cache
sizes
Details
- created a database and started in a loop 10 times { import 1m
records; run query}
- for each iteration observe the memory use by SQLITE3 process using
task manager
- the mimimum use was during the load, the maximum during the query
Below you will find the observations of memory size for three different
cache sizes (Page size 32768)
Comments:
- for 2k pages the maximum levels off at d (4m rows) at 220mb, so
the query mem use is 220-60 = 160mb
- for 10k pages the maximum grows by 50mb for each million rows,
query mem is 160 mb initially, but 500mb for 10m rows
- for 30k pages cache the picture is similar to that of 10k pages
cache
Summary: it looks like for large cache sizes the query has some sort of
memory mis-management.
Run on the full table with 500m records but cache size limited to 2k pages
the same query was fine.
Have you measured SQLite's memory utilization directly using the
sqlite3_memory_used() routine described at
http://www.sqlite.org/c3ref/memory_highwater.html
Do you have any other evidence to suggest that the problem is in SQLite and
not in your application?
The coding is 'f 66 220' means that during loading of the 6-th million of
rows (f) the minimum memory reported
Was 66mb, maximum 220mb)
cache 2k pages
a 90 b 64 166 c 64 ... d 64 219 e 64 220 f 66 220 g 65 220 h 65 220
i 65 220 j 65 220
cache 10k pages
a 135 187 b 202 372 c 307 469 d 320 521 e 320 571 f 318 622 g 319 672 h 322
722 i 318 772 j 323 824
cache 30k pages
a 187 270 b .. 373 c 363 559 d 421 746 e 562 932 f 711 1175 g 820 1302 h 974
1354 i 960 1403 j 961 1455
--
D. Richard Hipp
d...@sqlite.org
-Original Message-
From: ammon_lymphater [mailto:ammon_lympha...@hotmail.com]
Sent: Friday, January 25, 2013 1:40 PM
To: 'Michael Black'; 'General Discussion of SQLite Database'
Subject: RE: [sqlite] bug report: out of memory error for most operations on
a large database
To answer your question:
- Disk space is not a problem - there is over 500gb free space on that disk
(with database size 80gb).
- Memory - 16gb with at least 8 free at all times during testing
- Platform - I moved the platform to Windows Server 2012
I did more testing. I have a large csv file (100gb/500m lines) Originally I
have imported full file into database (using sqlite3 .import) - and then
ran the select query which resulted in out-of-memory error;
Over the last 24 hours it tried the following
- split the csv file into smaller pieces (10m lines each)
- import smaller pieces one-by-one (either in one sqlite3 session or 1
session per piece - same result)
- after each import run the query to check whether we have out-of-memory
condition
Out of memory condition:
1. does not affect import - I can still load the rows and count shows that
they are loaded 2. once it occurs all subsequent select...group by queries
show out of memory - adding new rows does not change that 3. is _random_ I
got it after three piece import yesterday, after two piece import today
(on the same machine, creating new db each time).
So this is _not_ deterministic but over larger timescale (like few hours -
not minutes or a couple of hours)
- It does not depend on the database size - 20m lines database is only 3gb.
- In in the f
Re: [sqlite] secure_delete versus journal_mode persist.
On 29 Jan 2013, at 8:19am, Scott Hess wrote:
> insert into x values ('SQLite is a software library that implements
> a self-contained, serverless, zero-configuration, transactional SQL
> database engine. SQLite is the most widely deployed SQL database engine in
> the world. The source code for SQLite is in the public domain.');
> -- Repeat that four or five times.
>
> Now, somewhere else, running:
> strings trial.db-journal
> gives me various repetitions of the inserted string present in the journal
> file. This applies even after running 'delete from x;', at which point the
> main database itself will not contain these strings.
Verified here with version 3.7.12, native with OS X 10.8 installation.
Can't be helped at all up to closing the connection to the database.
Once the database connection is closed, still being able to see that text is a
consequence of 'journal_mode = persist' or WAL. I think that this also can't
be helped -- it's inherent in the journal file not being wiped -- but this is
more debatable than the state before the connection is closed.
The only way to prevent the data being available from a disk-read with
sufficient privileges would be to use "journal_mode = MEMORY". Might be worth
adding a comment to the documentation for the secure_delete PRAGMA.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] secure_delete versus journal_mode persist.
Given a 3.7.15.2 OSX binary fresh from the website, the following scenario
seems suspect:
> ./sqlite3 trial.db
sqlite> pragma secure_delete = 1;
1
sqlite> pragma journal_mode = persist;
persist
sqlite> pragma journal_size_limit = 4096;
4096
sqlite> pragma page_size;
1024
sqlite> create table x (x);
sqlite> insert into x values ('SQLite is a software library that implements
a self-contained, serverless, zero-configuration, transactional SQL
database engine. SQLite is the most widely deployed SQL database engine in
the world. The source code for SQLite is in the public domain.');
-- Repeat that four or five times.
Now, somewhere else, running:
strings trial.db-journal
gives me various repetitions of the inserted string present in the journal
file. This applies even after running 'delete from x;', at which point the
main database itself will not contain these strings.
---
Background of this is that awhile back I enabled journal_mode settings
similar to the above to reduce the amount of filesystem create/delete
churn. So converting to journal_mode = truncate would be a viable
workaround. But having the data remain visible when secure_delete was
turned on surprised me. Admittedly, writing zeros would reduce
performance, but that seems like the bargain one signs up for when enabling
secure_delete. WDYT?
AFAICT, actually running with this combination of settings is probably not
an improvement over journal_mode = truncate, since the OS has the advantage
of being able to release those pages without overwriting until they are
reallocated to new data (*). Which might suggest that when running with
secure_delete, journal_mode = persist should only persist a single page at
most, or even just operate exactly like journal_mode = truncate.
Thanks,
scott
(*) If your filesystem does not enforce strict ordering, a poorly-timed
crash could result in the block being allocated to a file without being
overwritten. I'm not overly concerned about that.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
