Re: [sqlite] Path Length Limit on Windows

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 22/08/13 07:38, Markus Schaber wrote:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg18707.html
> seems to indicate that SQLite is not using those APIs. On the other
> hand, that post is of 2006.

There has been an open ticket about this for several years.

  http://www.sqlite.org/src/tktview?name=c060923a54

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIXpJkACgkQmOOfHg372QQDFQCgn56WYKUg3oxbiZ0Kp886flWa
H+sAoLMWH6Q462K1Lb5pTs7WwlZyxlnf
=Uj9e
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BETWEEN and explicit collation assignment

[Roman Fleysher]

You are correct, James, comparison depends on types. However, lets say we have 
suitcase and we want to test if it is bigger than the allowed limit, i.e. we 
have a biggest allowed suitcase to compare against. How do we answer: is this 
one bigger than the standard? Well, if this is a check-in bag, it has to be 
lighter than 50 lb, below a weight limit. If the suitcase is a carry-on it has 
to pass both weight limit and dimension limit. It is possible to cast the 
suitcase into carry-on subtype and conduct test there and to cast it to 
check-in type and test there too. But comparisons can be more complicated in 
general. If we have vectors, we may want to compare them by length (one 
sub-type of a vector), by one of its elements (one sub-type of vector per each 
element), by projection to a given vector (another sub-type), by angle that it 
makes with a vector (you guessed, another sub-type).

Thus, it is indeed possible to attach comparisons to types. This leads to 
enormous growth in number of types. Another solution is to realize that the 
comparator (the thing that makes comparisons) is actually an object of its own 
and it can be configured to apply different algorithms. Then, one does not need 
many  subtypes: one suitcase and one vector. This is, as I understand, the 
approach implemented in SQLite: algorithm is passed as an argument to the 
comparator along with the objects.

All to say something simple but not obvious: the rules of equality -- of any 
comparison -- are governed by the attribute(s) of the object relevant for 
comparison. What is relevant is not determined by the type of the object, but 
by the task at hand, in other words by the comparator.

Each column in a table is supposed to hold an attribute of an object (of the 
record -- the row). But strings, stored as attributes of the record, are 
objects of their own. Composite objects, composed of other objects -- 
characters. The characters  have upper and lower case, they can be printable 
and and non-printable, white spaces or not, bold and italic(?).  Therefore, 
comparing strings requires specification which of the attributes of those 
underlying objects are relevant. Collation rule is such a specification, an 
algorithm that tells comparator how to compare two strings -- objects of the 
same type. Type alone is not enough, if we want all types to fit human heads.


Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of James K. Lowden [jklow...@schemamania.org]
Sent: Thursday, August 22, 2013 9:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] BETWEEN and explicit collation assignment

On Thu, 22 Aug 2013 13:36:00 +0100
Simon Slavin  wrote:

> > 1.  where COLLATE( x AS NOCASE )
> > between COLLATE( 'a' AS NOCASE )
> > and COLLATE( 'b' AS NOCASE )
>
> Again, you are trying to apply a collation to a value.  And because
> BETWEEN takes three operands you are being given the opportunity to
> apply three collations for one condition.  Which is nonsense.  So the
> syntax shouldn't allow it or even suggest it would work.  You should
> be casting the 'BETWEEN', not the operands you're supplying to it.

There are two issues:

1.  Is collation a property of the value, its type, or the operation?
2.  Should the implied collation as currently implemented be retained,
or should it be replaced?

Before we can consider what syntax to use, we have to agree on what the
semantics are, on what is means to compare two things.  So let's
dispense with item #2 until we agree on #1.

Collation is not a property of the operation.  There is no other kind
of '=' than equality.  Equal is absolute; there is no modifier for it.

I understand your temptation.  I've used strcasecmp(3) a few times
myself.  But when you realize there's no such thing in SQLite or in the
relational model as an "operator modifier", you'll see the light.

Collation is not a property of the value.  Strings are just strings.
They have an *encoding*, necessarily, but not a collation.

Collation therefore must, by process of elimination, be a property of
the type.  And a good thing, too, because it's a property of the
column, and a column is a type.

Forget indexes; they're a feature of the implementation.  Think
logically, *semantically*: when are two things equal?  Two values in a
column either are or are not equal.  What determines what "equal"
*means*?  The type!

The question of whether

A = B

is true or false rests *not* on "how they are compared" but on *what*
they are.  The same is true for

'abc' = NAME

To make that obvious, let's take a little mathematical excursion.

Ask yourself how this is evaluated:

where 1 = '1'

SQL is logical (or so we like to think), and we're being asked to
compare a number to a string, an apple to an orange.  What to do?

There are two ways to treat that: report a type error, or perform a
type 

Re: [sqlite] Path Length Limit on Windows

[Markus Schaber]

Hi, Richard,

Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
> On Thu, Aug 22, 2013 at 10:38 AM, Markus Schaber wrote:
> > During our internal tests, I recently stumbled across a problem when
> > using SVN, which uses SQLite to manage the "wc.db" metadata database
> > in the working copy.
> >
> > SVN itself uses the Apache APR as PAL, which internally uses the
> > Windows Unicode APIs with the \\?\ path prefix, allowing path lengths
> > of up to 32k characters, so it can deal fine with deep directory
> > hierarchies.
> >
> > Except when using SVN externals within those deep hierarchies.
> > Externals are managed via nested working copies coming with their own
> > "wc.db", so it's easy to run into the MAX_PATH limit of 260
> > characters.
> >
> > http://www.mail-archive.com/sqlite-users@sqlite.org/msg18707.html
> > seems to indicate that SQLite is not using those APIs. On the other
> > hand, that post is of 2006.
> >
> > So my questions are:
> >
> > Is there any way to successfully open a database at such a long path
> > location?
> 
> (1) Move the database file to a new location with a shorter pathname.

This won't work in my case, as the path name is dictated by the working 
copy layout and location - svn expects the wc.db to sit within the ".svn"
directory of the working copy.
 
> (2) SQLite version 3.8.0 allows for longer windows pathnames up to 3*MAX_PATH
> bytes, which is 3x more space that was allowed before.  This is still not 32K
> but might be sufficient for your needs.  The limit can be extended at
> compile-time.
>[]

> SQLite version 3.8.0 allows you to recompile with -
> DSQLITE_WIN32_MAX_PATH= with however large of filename length limit you
> desire.

This sounds like a viable solution. SVN embeds the SQLite amalgamation, IIRC,
so it should be possible to raise the limit.

I'll relay your suggestion to the SVN developers.

Best regards

Markus Schaber

-- 
CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

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

Re: [sqlite] Path Length Limit on Windows

[Richard Hipp]

On Thu, Aug 22, 2013 at 10:38 AM, Markus Schaber wrote:

> Hi,
>
> During our internal tests, I recently stumbled across a problem when
> using SVN, which uses SQLite to manage the "wc.db" metadata database
> in the working copy.
>
> SVN itself uses the Apache APR as PAL, which internally uses the
> Windows Unicode APIs with the \\?\ path prefix, allowing path lengths
> of up to 32k characters, so it can deal fine with deep directory
> hierarchies.
>
> Except when using SVN externals within those deep hierarchies.
> Externals are managed via nested working copies coming with their
> own "wc.db", so it's easy to run into the MAX_PATH limit of 260
> characters.
>
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg18707.html
> seems to indicate that SQLite is not using those APIs. On the other
> hand, that post is of 2006.
>
> So my questions are:
>
> Is there any way to successfully open a database at such a long path
> location?
>

(1) Move the database file to a new location with a shorter pathname.

(2) SQLite version 3.8.0 allows for longer windows pathnames up to
3*MAX_PATH bytes, which is 3x more space that was allowed before.  This is
still not 32K but might be sufficient for your needs.  The limit can be
extended at compile-time.



>
> Is the SQLite project interested in a fix of this problem?
>

That depends on how dirty the fix is.



>
> What is the correct way to get such a fix rolled (bug tracker,
> patches, etc.?)
>

Post to this mailing list.


>
> Is it possible to implement a workaround, e. G. via a custom
> VFS layer (which might be able to delegate everything except file
> opening to the default one)?
>

SQLite version 3.8.0 allows you to recompile with
-DSQLITE_WIN32_MAX_PATH= with however large of filename length limit
you desire.


>
>
> PS: I'm already on contact with the SVN Developers, but I think the
> issue is best fixed in SQLite itself:
> http://svn.haxx.se/dev/archive-2013-08/0344.shtml
>
>
> Best regards
>
> Markus Schaber
>
> CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH
>
> Inspiring Automation Solutions
>
> 3S-Smart Software Solutions GmbH
> Dipl.-Inf. Markus Schaber | Product Development Core Technology
> Memminger Str. 151 | 87439 Kempten | Germany
> Tel. +49-831-54031-979 | Fax +49-831-54031-50
>
> E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS
> store: http://store.codesys.com
> CODESYS forum: http://forum.codesys.com
>
> Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner |
> Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915
>
> ___
> 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] Python -> Sqlite3 import, can't retrieve values in the last column

[m.duering]

Thank you for the tip with the numbered rows, Simon (everything worked fine in 
the shell)
-- But I just discovered that my problem was caused by a \n character that my 
script failed to strip


--
Dr. Marten Düring
Digital Humanities Researcher
Centre Virtuel de la Connaissance sur l’Europe (CVCE) 
www.cvce.eu / www.cubrikproject.eu

Personal website, Historical Network Research


 On Fri, 23 Aug 2013 14:59:06 +0200 Simon Slavin 
slav...@bigfraud.org wrote  



On 23 Aug 2013, at 1:48pm, m.duering m.duer...@zoho.com wrote: 
 
 However I only get one result if I run the query 
 SELECT * FROM nodes WHERE Netzwerk="Kaufmann" 
 
SQLite uses single quotes to surround strings. Should be: 
 
SELECT * FROM nodes WHERE Netzwerk='Kaufmann' 
 
 
 This works ok and I am able to import everything into the Firefox SQlite 
Manager and Gephi. 
 
If you're not sure your data is stored properly, please download and use the 
SQLite Shell Tool to examine your database. Make sure that the results of 
SELECT commands are what you think they should be. Other tools may be useful 
but only the SQLite shell tool was written by the SQLite team and is completely 
trustworthy. 
 
http://www.sqlite.org/sqlite.html; 
http://www.sqlite.org/download.html; 
 
I also suspect you need to redesign your data structure for long-term use. 
Anything that leads to numbered columns (e.g. edges 0 to 17) is usually a sign 
that you really need to be storing things in numbered rows instead. But your 
existing code should work fine. 
 
Simon. 
___ 
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] Python -> Sqlite3 import, can't retrieve values in the last column

[Simon Slavin]

On 23 Aug 2013, at 1:48pm, m.duering  wrote:

> However I only get one result if I run the query
> SELECT * FROM nodes WHERE Netzwerk="Kaufmann"

SQLite uses single quotes to surround strings.  Should be:

SELECT * FROM nodes WHERE Netzwerk='Kaufmann'


> This works ok and I am able to import everything into the Firefox SQlite 
> Manager and Gephi.

If you're not sure your data is stored properly, please download and use the 
SQLite Shell Tool to examine your database.  Make sure that the results of 
SELECT commands are what you think they should be.  Other tools may be useful 
but only the SQLite shell tool was written by the SQLite team and is completely 
trustworthy.




I also suspect you need to redesign your data structure for long-term use.  
Anything that leads to numbered columns (e.g. edges 0 to 17) is usually a sign 
that you really need to be storing things in numbered rows instead.  But your 
existing code should work fine.

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

[sqlite] Python -> Sqlite3 import, can't retrieve values in the last column

[m.duering]

Hi all,

this is my first project using Sqlite3 so I might have done something silly 
along the way. 
I can not run queries for the last columns of my tables. I either get only the 
very last row or nothing where there should be hundreds of entries which match 
the query. 

Here is what I did:

I read network data from a csv file into Sqlite using the following code in 
Python and transformed them into unicode.
Where edges and nodes are represented as a list which contains a list of the 
node and its attributes.
One sample node entry: 
[(u'1480', u'Z_Avi', u'2', u'3', u'1', u'4', u'99', u'3', u'99', u'11', 
u'Kaufmann')]


to enter edges:


conn = sqlite3.connect("test.db")
c = conn.cursor()

for i in range(len(edges)):

edge1 = [(
edges[i][0],
edges[i][1],
edges[i][2],
edges[i][3],
edges[i][4],
edges[i][5],
edges[i][6],
edges[i][7],
edges[i][8],
edges[i][9],
edges[i][10],
edges[i][11],
edges[i][12],
edges[i][13],
edges[i][14],
edges[i][15],
edges[i][16],
edges[i][17],
)]

i = 0

c.executemany("INSERT INTO edges 
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", edge1)
conn.commit()
print "inserted records: edges"

conn.close()




and then to enter nodes:




conn = sqlite3.connect("test.db")
c = conn.cursor()
for i in range(len(nodes)):

node1 = [(
nodes[i][0],
nodes[i][1],
nodes[i][2],
nodes[i][3],
nodes[i][4],
nodes[i][5],
nodes[i][6],
nodes[i][7],
nodes[i][8],
nodes[i][9],
nodes[i][10],
)]
i = 0

c.executemany("INSERT INTO nodes VALUES(?,?,?,?,?,?,?,?,?,?,?)", node1)
conn.commit()

conn.close()


This works ok and I am able to import everything into the Firefox SQlite 
Manager and Gephi. However I only get one result if I run the query
SELECT * FROM nodes WHERE Netzwerk="Kaufmann"

Any thoughts and tips on this are much appreciated.

Best, Marten





--
Dr. Marten Düring
Digital Humanities Researcher
Centre Virtuel de la Connaissance sur l’Europe (CVCE) 
www.cvce.eu / www.cubrikproject.eu

Personal website, Historical Network Research

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

[sqlite] Path Length Limit on Windows

[Markus Schaber]

Hi,

During our internal tests, I recently stumbled across a problem when 
using SVN, which uses SQLite to manage the "wc.db" metadata database 
in the working copy.

SVN itself uses the Apache APR as PAL, which internally uses the 
Windows Unicode APIs with the \\?\ path prefix, allowing path lengths
of up to 32k characters, so it can deal fine with deep directory 
hierarchies.

Except when using SVN externals within those deep hierarchies. 
Externals are managed via nested working copies coming with their
own "wc.db", so it's easy to run into the MAX_PATH limit of 260 
characters.

http://www.mail-archive.com/sqlite-users@sqlite.org/msg18707.html 
seems to indicate that SQLite is not using those APIs. On the other 
hand, that post is of 2006.

So my questions are:

Is there any way to successfully open a database at such a long path 
location?

Is the SQLite project interested in a fix of this problem?

What is the correct way to get such a fix rolled (bug tracker, 
patches, etc.?)

Is it possible to implement a workaround, e. G. via a custom 
VFS layer (which might be able to delegate everything except file 
opening to the default one)?


PS: I'm already on contact with the SVN Developers, but I think the 
issue is best fixed in SQLite itself:
http://svn.haxx.se/dev/archive-2013-08/0344.shtml


Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

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

[sqlite] Memory use and release on statement prepare

[Herfian Rothman]

Hi,

Encountered what seemed to be somewhat excessive memory consumption for a
relatively simple statement prepare. The following was carried out in
sequence. Platform/environment is Windows CE.

1. System and SQLite memory statistics obtained and recorded
2. Preparation of SQLite statement. The query is : "select docid,
upper(words) from Homonyms;" on table of schema : 'CREATE VIRTUAL TABLE
Homonyms using fts4(words , tokenize=simpleplus "&/"); ' (simpleplus refers
to a custom FTS tokenizer.) There are about 80 records/rows in the table.
3. Statement is finalized.
4. System and SQLite memory statistics obtained and recorded again for
comparison

So comparing memory statistics changes at 4 over 1, the following was noted
(figures are in bytes)
-> dwAvailPhys (system physical memory) decrease by : 1,286,144
-> dwAvailVirtual (system virtual memory) decrease by : 1,376,256
-> SQLITE_STATUS_PAGECACHE_OVERFLOW status increase by : 304,704 (I
understand it here to be page cache use, please correct/enlighten me if I'm
mistaken)
-> SQLITE_STATUS_MEMORY_USED increase by : 801,440 (other general memory
use by SQLite)
-> SQLITE_DBSTATUS_CACHE_USED increase by : 304,704 (memory use for page
cache by database, perhaps understandably identical to the overflow figure
above)
-> SQLITE_DBSTATUS_SCHEMA_USED increase by : 495,224 (memory used to store
database schema - why the sudden increase at this point?)

It was a little strange that such memory consumption could not be observed
when a similar query/statement prepare was carried out on other tables. It
is of concern as we are trying to find ways to reduce or eliminate precious
run-time memory usage by the application where we can.

The following numbers may be superfluous, but hope it will help in better
shedding light on the behaviour. The sections are as reported by the
GlobalMemoryStatus(), sqlite3_status() and sqlite_db_status() functions
respectively.

Many thanks - Herfian

Memory statistics before statement prepare (at 1):

dwLength : 32
dwMemoryLoad : 35
dwTotalPhys : 112959488
dwAvailPhys : 73674752
dwTotalPageFile : 0
dwAvailPageFile : 0
dwTotalVirtual : 1073741824
dwAvailVirtual : 824377344

SQLITE_STATUS_MEMORY_USED   : 1299608
SQLITE_STATUS_MALLOC_SIZE   : 48000
SQLITE_STATUS_MALLOC_COUNT  : 13785
SQLITE_STATUS_PAGECACHE_USED: 0
SQLITE_STATUS_PAGECACHE_OVERFL  : 342792
SQLITE_STATUS_PAGECACHE_SIZE: 4232
SQLITE_STATUS_SCRATCH_USED  : 0
SQLITE_STATUS_SCRATCH_OVERFLOW  : 0
SQLITE_STATUS_SCRATCH_SIZE  : 0

SQLITE_DBSTATUS_LOOKASIDE_USED   : 8
SQLITE_DBSTATUS_LOOKASIDE_HIT: 0
SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE  : 0
SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL  : 0
SQLITE_DBSTATUS_CACHE_USED   : 4808
SQLITE_DBSTATUS_SCHEMA_USED  : 0
SQLITE_DBSTATUS_STMT_USED: 0

Memory statistics after statement finalize (at 4):

dwLength : 32
dwMemoryLoad : 36
dwTotalPhys : 112959488
dwAvailPhys : 72388608
dwTotalPageFile : 0
dwAvailPageFile : 0
dwTotalVirtual : 1073741824
dwAvailVirtual : 823001088

SQLITE_STATUS_MEMORY_USED   : 2101048
SQLITE_STATUS_MALLOC_SIZE   : 48000
SQLITE_STATUS_MALLOC_COUNT  : 26878
SQLITE_STATUS_PAGECACHE_USED: 0
SQLITE_STATUS_PAGECACHE_OVERFL  : 647496
SQLITE_STATUS_PAGECACHE_SIZE: 4232
SQLITE_STATUS_SCRATCH_USED  : 0
SQLITE_STATUS_SCRATCH_OVERFLOW  : 0
SQLITE_STATUS_SCRATCH_SIZE  : 0

SQLITE_DBSTATUS_LOOKASIDE_USED   : 9
SQLITE_DBSTATUS_LOOKASIDE_HIT: 0
SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE  : 1447
SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL  : 0
SQLITE_DBSTATUS_CACHE_USED   : 309512
SQLITE_DBSTATUS_SCHEMA_USED  : 495224
SQLITE_DBSTATUS_STMT_USED: 0
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users