[sqlite] I have a database working directly from

[Matt Young]

http://www.w3.org/TR/2010/NOTE-webdatabase-20101118/
//I can open a database
//  write to it, read from it.
// But I would like access to the hidden database it creates.
//  I look everywhere, cannot find it!

var databaseOptions = {
fileName: "file://localhost/C:/R/work/test.db",
version: "1.0",
displayName: "SQLite Hello World",
maxSize: 8024};
var datebase;

try {
if (openDatabase) {
database =
window.openDatabase(databaseOptions.fileName,databaseOptions.version,
databaseOptions.displayName,databaseOptions.maxSize );
if (!database)
alert("Failed to open the database on disk. This is probably because the
version was bad or there is not enough space left in this domain's quota");
} else
alert("Couldn't open the database. Please try with a WebKit nightly with
this feature enabled");
} catch(err) { }
I
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] jquery sqlite3

[Simon Slavin]

On 7 Mar 2011, at 2:49am, Matt Young wrote:

> Trying to understand it.  How do I specifically open sqlite from a jquery
> widget.
> Or best simple example.

jquery is just a JavaScript library.  JavaScript can't open a file on your hard 
disk.  Because if JavaScript could open a file on your hard disk, anyone who 
wrote a web site you visited could grab a copy of one of your files.

However, HTML5 does have facilities in for using SQL:

http://www.w3.org/TR/webdatabase/

and as it turns out, everyone who implemented this implemented it by building 
SQLite into their JavaScript engine.  It just uses private databases rather 
than allowing you to specify a directory path.

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

[sqlite] jquery sqlite3

[Matt Young]

Trying to understand it.  How do I specifically open sqlite from a jquery
widget.
Or best simple example.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG: test script e_vacuum.test needs has_codec

[Noah Hart]

According to vacuum.c

 /* A VACUUM cannot change the pagesize of an encrypted database. */

So the tests in e_vacuum.test which attempt to change page size need to be
bracked with a test for codec

something like the following

# Determine if there is a codec available on this test.
#
if {[catch {sqlite3 -has-codec} r] || $r} {
  set has_codec 1
} else {
  set has_codec 0
}

if {!$has_codec} {
do_test e_vacuum-1.3.2.1 {
...
}

if {!$has_codec} {
do_test e_vacuum-1.3.3.2 {
...
}

~Noah Hart 
-- 
View this message in context: 
http://old.nabble.com/BUG%3A-test-script-e_vacuum.test-needs-has_codec-tp31084122p31084122.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Attaching vfs

[Max Vlasov]

On Mon, Mar 7, 2011 at 2:56 AM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 03/06/2011 03:04 PM, Max Vlasov wrote:
> > I don't think I need a solution that complex.
>
> You need to be very careful with your terminology :-)
>
> As for getting other data into your database, just do a .dump of a
> different
> SQLite database and run it.
>
> If you are looking for something to exercise ...
>
>
Roger, proably dumping is my current way to go and also thanks for tests
links

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

Re: [sqlite] Attaching vfs

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/06/2011 03:04 PM, Max Vlasov wrote:
> I don't think I need a solution that complex. 

You need to be very careful with your terminology :-)

As for getting other data into your database, just do a .dump of a different
SQLite database and run it.

If you are looking for something to exercise your database then I suggest
running this which will spew out SQL you can run.

  http://www.sqlite.org/src/finfo?name=tool/mkspeedsql.tcl

I have an enhanced version in my project:

  http://code.google.com/p/apsw/source/browse/tools/speedtest.py

You can give it a scaling factor (roughly how many seconds it should take),
include Unicode in addition to plain ascii, use larger data items etc.
Sample command line:

  speedtest.pt --unicode 5 --scale 10 --dump-sql /tmp/test.sql

In the big picture you are right about multiple connected databases not
being from different VFS, but that can only be changed by additions to the
syntax and/or API so it won't help you now.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk10Hy0ACgkQmOOfHg372QTjcgCgsMtIeZEP+fgUzTrTvolidvLj
+kcAnjR/PoN/Fm5AKT1uO7zzpCq93A6F
=+H9P
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS3/FTS4+ICU segfault on NULLs

[Alexey Pechnikov]

This code produce segfault on current trunk SQLite and some previous
releases (3.7.0.1 and 3.7.3):

CREATE VIRTUAL TABLE fts USING fts3(name,TOKENIZE icu ru_RU);
insert into fts (name) values (NULL);
insert into fts (name) values (NULL);
delete from fts;


Or:

CREATE VIRTUAL TABLE fts USING fts3(name,TOKENIZE icu en_US);
insert into fts (name) values (NULL);
insert into fts (name) values (NULL);
delete from fts;

...

-- 
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] FTS4 bug: last_insert_rowid() fail

[Alexey Pechnikov]

This is regression from FTS3 to FTS4 and may crash some applications.

2011/3/7 Roger Binns :
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> See also http://www.sqlite.org/src/tktview?name=13137dccf3
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk10DMwACgkQmOOfHg372QQX2QCgoEe5w0hwY8LFEEaNrtos+Vxh
> z4oAnRQIQgs/ewTNdvEIRamLmL+Qm9Jc
> =nbXW
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Attaching vfs

[Max Vlasov]

On Sun, Mar 6, 2011 at 9:00 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 03/06/2011 06:31 AM, Max Vlasov wrote:
> > For example, currently it's not
> > possible for some exotic vfs to selectively import data from a
> conventional
> > format db (CMIIW)
>
> A VFS is only used for reading and writing SQLite's btree pages.
>
> If you want to access data in other formats then use virtual tables.  There
> are no issues with attach and virtual tables.  In general you can make the
> first parameter to your virtual table be the name of the file it should
> use.
>
>
I don't think I need a solution that complex. I'll explain. Actually it's a
vfs supporting compressing databases dynamically and I wanted to test it
with some external data. But it looks like I either can take some other
general db and convert it to this vfs format as a whole (without sqlite api,
just by file converting) or populate it with manual queries. This is because
attaching general db in this case won't work right from the box. Although it
is still possible for my vfs to recognize that the newly attached db has a
different format and bypass some calls, this will require extra work and
testing.

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

Re: [sqlite] FTS4 bug: last_insert_rowid() fail

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

See also http://www.sqlite.org/src/tktview?name=13137dccf3

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk10DMwACgkQmOOfHg372QQX2QCgoEe5w0hwY8LFEEaNrtos+Vxh
z4oAnRQIQgs/ewTNdvEIRamLmL+Qm9Jc
=nbXW
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS4 bug: last_insert_rowid() fail

[Alexey Pechnikov]

Test:
###
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db :memory:
db function elog elog
proc elog {args} {
puts [join $args]
}

db eval {
CREATE TABLE test (id INTEGER PRIMARY KEY);
CREATE VIRTUAL TABLE fts3 USING fts3(name);
CREATE VIRTUAL TABLE fts4 USING fts4(name);

select elog('regular table - OK');
insert into test (rowid) values (1);
select elog('last_insert_rowid = ',last_insert_rowid());
delete from test;
select elog('last_insert_rowid = ',last_insert_rowid());

select elog('FTS3 table - OK');
insert into fts3 (rowid) values (1);
select elog('last_insert_rowid = ',last_insert_rowid());
delete from fts3;
select elog('last_insert_rowid = ',last_insert_rowid());

select elog('FTS4 table - FAIL');
insert into fts4 (rowid) values (1);
select elog('last_insert_rowid = ',last_insert_rowid());
delete from fts4;
select elog('last_insert_rowid = ',last_insert_rowid());
}
###


Result:
###
regular table - OK
last_insert_rowid =  1
last_insert_rowid =  1
FTS3 table - OK
last_insert_rowid =  1
last_insert_rowid =  1
FTS4 table - FAIL
last_insert_rowid =  1
last_insert_rowid =  0



-- 
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] Feature request: hash function for fast indices on text and blob fields

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/06/2011 05:22 AM, Alexey Pechnikov wrote:
> I think one or more hash functions may be
> included into core SQLite.

(Argggh.  You posted the same message to multiple mailing lists.  That is
annoying.)

Why can't you use the SQLite API in order to register the functions for your
databases?

Every now and then people request that some favourite function special to
them be part of the core.  Rarely does anyone show that it would be broadly
useful to the however many billion instances of SQLite around the world.
And even rarer still does anyone show how the existing SQLite mechanisms to
add functions are problematic.

For something to be added to the core it means the code has to be compatible
with the SQLite license (public domain, on file contributor agreement), has
to be implemented by the SQLite team (fit in with the existing coding style,
error handling, test infrastructure), has to be included in the testing
(remember there is 100% MCDC coverage) and has to be maintained in SQLite 3
until it is end of lifed.  This is a lot of work to inflict on the team.

The murmur hash is good example of why it would be a bad idea to include the
SQLite core.  There are many different versions (1, 2, 2A, 2-32, 2-64, 3)
and it currently works on binary blobs.  To use it for strings would require
defining encoding and byte ordering.

You can create an extension that includes a whole bunch of hash functions.
Make it available to the world by getting it listed at the end of
http://sqlite.org/contrib where you can see a good 'extension-functions.c'
to follow.  If the team see the extension being downloaded a lot then that
is a good case for migrating it into the core.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk1z0UUACgkQmOOfHg372QRTcwCguCl0ZZCDnYHSdK9hyRucdEE2
aiEAoMRGLPH8RCuD/mOnW2levNbr/QyF
=D5kz
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attaching vfs

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/06/2011 06:31 AM, Max Vlasov wrote:
> For example, currently it's not
> possible for some exotic vfs to selectively import data from a conventional
> format db (CMIIW)

A VFS is only used for reading and writing SQLite's btree pages.

If you want to access data in other formats then use virtual tables.  There
are no issues with attach and virtual tables.  In general you can make the
first parameter to your virtual table be the name of the file it should use.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk1zy8UACgkQmOOfHg372QTHNgCdEKaPqedyJ57lOuffe47+Ejkg
6FQAmgNG9YDjcmNmArYVXkJKpCbopmBn
=wU3/
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VB.net error on adding SQLite3.dll to ProjectReferences

[J Trahair]

Thanks everyone, this is sorted now.

J Trahair
  - Original Message - 
  From: jose simas 
  To: General Discussion of SQLite Database 
  Sent: Sunday, March 06, 2011 11:54 AM
  Subject: Re: [sqlite] VB.net error on adding SQLite3.dll to ProjectReferences


  Hi,

  Use System.Data.Sqlite instead: http://sqlite.phxsoftware.com/

  Regards,
  Jose

  On 3 March 2011 09:23, J Trahair  wrote:
  > Hi everyone.
  >
  > VB.Net 2005, I am trying to add sqlite3.dll to the Project References. When 
I try to Add the dll I get an error message:
  >
  > 'A reference to {path to sqlite3.dll} could not be added. Please make sure 
that the file is accessible, and that it is a valid assembly or COM component.'
  >
  > The version of sqlite3.dll is unknown, size 546 KB modified 31 January 2011.
  >
  > Thanks in advance.
  >
  > Jonathan Trahair
  >
  > ___
  > 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: hash function for fast indices on text and blob fields

[Alexey Pechnikov]

Yes, I have my extension for this
http://sqlite.mobigroup.ru/dir?name=ext/murmurhash

But I think the feature will be very helpful as core function.

2011/3/6 Enrico Thierbach :
>
> On 06.03.2011, at 14:22, Alexey Pechnikov wrote:
>
>> Sqlite does not has ability to compress indices. There is huge
>> performance problem with populating a database with indexed
>> text or blob fields. But we can emulate indices compression
>> by using index on hashed field (and populate the hash field by
>> trigger as example). I think one or more hash functions may be
>> included into core SQLite.
>>
>> Note: I use the public domain licensed murmur hash algorithm
>> (as extension) but this need custom build of SQLite or external
>> extension.
>>
>> Does anybody has the same problem?
>>
>
> Hey Alexey,
>
> couldn't you just define any hash function you like using SQLite's interface 
> as outlined here?
>
> http://www.sqlite.org/c3ref/create_function.html
>
> /eno
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Feature request: hash function for fast indices on text and blob fields

[Enrico Thierbach]

On 06.03.2011, at 14:22, Alexey Pechnikov wrote:

> Sqlite does not has ability to compress indices. There is huge
> performance problem with populating a database with indexed
> text or blob fields. But we can emulate indices compression
> by using index on hashed field (and populate the hash field by
> trigger as example). I think one or more hash functions may be
> included into core SQLite.
> 
> Note: I use the public domain licensed murmur hash algorithm
> (as extension) but this need custom build of SQLite or external
> extension.
> 
> Does anybody has the same problem?
> 

Hey Alexey,

couldn't you just define any hash function you like using SQLite's interface as 
outlined here?

http://www.sqlite.org/c3ref/create_function.html

/eno

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

[sqlite] Documentation bug?

[Black, Michael (IS)]

I was trying to follow the documentation on FTS4 and found what I guess is a 
typo.

In several places there is a reference to "document" where I believe it should 
be "documents" as the first term doesn't exist in any create statement.

There are several of these in Appendix A
http://www.sqlite.org/fts3.html#matchinfo



CREATE VIRTUAL TABLE documents USING fts3(title, content);
-- Assuming the application has supplied an SQLite user function named 
"countintegers"
-- that returns the number of space-separated integers contained in its only 
argument,
-- the following query could be used to return the titles of the 10 documents 
that contain
-- the greatest number of instances of the users query terms. Hopefully, these 
10
-- documents will be those that the users considers more or less the most 
"relevant".
SELECT title FROM documents
  WHERE documents MATCH 
  ORDER BY countintegers(offsets(document)) DESC
  LIMIT 10 OFFSET 0



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attaching vfs

[Max Vlasov]

Hi,
as long I understand for Attach command sqlite assumes the same vfs as the
one used the the connection. Can this be extended to allow more
communication possibilities between vfs'es? For example, currently it's not
possible for some exotic vfs to selectively import data from a conventional
format db (CMIIW)

There was a suggestion in the list to extend the Attach syntax. I think it's
easier and safer to do this with a pragma, that temporally points the the
name of vfs to be used for attach command. This pragma can empty by default
for any newly opened db (the same vfs assumed)

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

[sqlite] Feature request: hash function for fast indices on text and blob fields

[Alexey Pechnikov]

Sqlite does not has ability to compress indices. There is huge
performance problem with populating a database with indexed
text or blob fields. But we can emulate indices compression
by using index on hashed field (and populate the hash field by
trigger as example). I think one or more hash functions may be
included into core SQLite.

Note: I use the public domain licensed murmur hash algorithm
(as extension) but this need custom build of SQLite or external
extension.

Does anybody has the same problem?

-- 
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] VB.net error on adding SQLite3.dll to Project References

[jose simas]

Hi,

Use System.Data.Sqlite instead: http://sqlite.phxsoftware.com/

Regards,
Jose

On 3 March 2011 09:23, J Trahair  wrote:
> Hi everyone.
>
> VB.Net 2005, I am trying to add sqlite3.dll to the Project References. When I 
> try to Add the dll  I get an error message:
>
> 'A reference to {path to sqlite3.dll} could not be added. Please make sure 
> that the file is accessible, and that it is a valid assembly or COM 
> component.'
>
> The version of sqlite3.dll is unknown, size 546 KB modified 31 January 2011.
>
> Thanks in advance.
>
> Jonathan Trahair
>
> ___
> 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