date:20130201

Re: [sqlite] I suspect not - but is the database separator configurable?

2013-02-01 Thread Mohit Sindhwani


Hi Ryan,

On 2/2/2013 1:55 AM, Ryan Johnson wrote:
That would break sybase, though: the quotes would also tell it to 
treat the db name and periods as part of the table name, too:


sqlite3> create table foo(x,y);
sqlite3> .tables
foo
sqlite3> select * from "main.foo";
Error: no such table: main.foo


Since the OP (you aren't the OP, right?)  seems to have control over the 
code, I wonder if a compile-time macro could be used to attach the 
quotes around the database name only in the test environment. But, 
otherwise, I think we're at the end of what can be done with SQLite.


Best Regards,
Mohit.



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

[sqlite] fts4 contentless tables, multiple inserts to same doc id?

2013-02-01 Thread James Berry

Is the following intended to be legal and possible for a contentless fts table: 
to do multiple inserts with the same docid, but to different columns?

It seems to work, and I like it, but it wasn't entirely expected.

sqlite> CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b);

sqlite> INSERT INTO t1(docid, a) VALUES (1, "avery");
sqlite> INSERT INTO t1(docid, b) VALUES (1, "billy");

sqlite> SELECT docid FROM t1 WHERE a MATCH 'avery';
1
sqlite> SELECT docid FROM t1 WHERE b MATCH 'avery';
sqlite> SELECT docid FROM t1 WHERE b MATCH 'billy';
1
sqlite> SELECT docid FROM t1 WHERE t1 MATCH 'billy';
1
sqlite> SELECT docid FROM t1 WHERE t1 MATCH 'avery';
1

Thanks,

James

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

Re: [sqlite] I suspect not - but is the database separator configurable?

2013-02-01 Thread Ryan Johnson


On 01/02/2013 12:28 PM, Mohit Sindhwani wrote:

Hi Marc,

On 1/2/2013 10:42 PM, message adams wrote:

My applications actually run against sybase, but I'd love to use a
connection to an in-memory sqlite to carry out my testing.
As part of the unit-test, I'd pass the sqlite conenction into my source
code hoping it would be none the wiser.

The only real problem I see, is sybase uses a double period separator
between the database and table. e.g.
select * from database..table

... whereas sqlite
select * from database.table


Can you use double quotes in the query?

For example, this works in SQLite3

sqlite> create table "db..abc"(p INTEGER, q INTEGER);
sqlite> .tables
db..abc
sqlite> select * from "db..abc";
sqlite>
That would break sybase, though: the quotes would also tell it to treat 
the db name and periods as part of the table name, too:


sqlite3> create table foo(x,y);
sqlite3> .tables
foo
sqlite3> select * from "main.foo";
Error: no such table: main.foo


Ryan

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

Re: [sqlite] I suspect not - but is the database separator configurable?

2013-02-01 Thread Mohit Sindhwani


Hi Marc,

On 1/2/2013 10:42 PM, message adams wrote:

My applications actually run against sybase, but I'd love to use a
connection to an in-memory sqlite to carry out my testing.
As part of the unit-test, I'd pass the sqlite conenction into my source
code hoping it would be none the wiser.

The only real problem I see, is sybase uses a double period separator
between the database and table. e.g.
select * from database..table

... whereas sqlite
select * from database.table


Can you use double quotes in the query?

For example, this works in SQLite3

sqlite> create table "db..abc"(p INTEGER, q INTEGER);
sqlite> .tables
db..abc
sqlite> select * from "db..abc";
sqlite>

Best Regards,
Mohit.


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

Re: [sqlite] Tracking changes with sha1 hashes

2013-02-01 Thread Igor Tandetnik


On 2/1/2013 10:21 AM, Amit Chaudhuri wrote:

If I only issue select queries on my two input databases, can I expect the
sha1 hash to stay the same over time?


Yes. You can even mark the database file as read-only and use 
sqlite3_open_v2 with SQLITE_OPEN_READONLY flag.

--
Igor Tandetnik

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

Re: [sqlite] I suspect not - but is the database separator configurable?

2013-02-01 Thread Pavel Ivanov

On Fri, Feb 1, 2013 at 6:42 AM, message adams  wrote:
> Greetings;
>
> I've recently started using sqlite within Python, to help unit-test my
> applications.
>
> My applications actually run against sybase, but I'd love to use a
> connection to an in-memory sqlite to carry out my testing.
> As part of the unit-test, I'd pass the sqlite conenction into my source
> code hoping it would be none the wiser.
>
> The only real problem I see, is sybase uses a double period separator
> between the database and table. e.g.
> select * from database..table

No, it's not double period separator. It's actually
database.table_owner.table, but table_owner can be omitted, in that
case it's defaulted to 'dbo'.

> ... whereas sqlite
> select * from database.table
>
>
> I assume the sqlite database separator is not configurable, but was
> wondering if anybody's resolved a similar issue?

SQLite doesn't have a notion of users, thus it doesn't and won't
support the "double period" separator. SQLite even has a different
notion of databases that can go before table name. So to make queries
work both in SQLite and in Sybase don't use database name at all, make
it "select * from table".

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

[sqlite] I suspect not - but is the database separator configurable?

2013-02-01 Thread message adams

Greetings;

I've recently started using sqlite within Python, to help unit-test my
applications.

My applications actually run against sybase, but I'd love to use a
connection to an in-memory sqlite to carry out my testing.
As part of the unit-test, I'd pass the sqlite conenction into my source
code hoping it would be none the wiser.

The only real problem I see, is sybase uses a double period separator
between the database and table. e.g.
select * from database..table

... whereas sqlite
select * from database.table


I assume the sqlite database separator is not configurable, but was
wondering if anybody's resolved a similar issue?


Many thanks for any help,
Marc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible optimization in FTS SQLite? (abort early when unpacking docids of inverted index list)

2013-02-01 Thread Dan Kennedy


On 02/01/2013 08:31 PM, Dominique Pellé wrote:

Hi

I was trying to optimize this FTS query which can be a bottleneck
in my application:


SELECT docId,matchinfo(ftsStreets,'pcx')
FROM ftsStreets
WHERE ftsStreets MATCH '...' AND docId BETWEEN docid1 AND docid2;


Can't SQLite optimize the query to use the fact that docid cannot be more
than the specified upper limit (docid2) given in the BETWEEN clause here?

Looking that the SQLite code (3.7.14),  I see that it unpacks docid lists (in
function fts3SegReaderNextDocid.c) of the FTS inverted index. Those
docid lists are stored in ascending order and stored by delta. Each delta
is stored in variable size (1 bytes to 10 bytes depending on size of delta).

When running above query, I see that SQLite unpacks sequentially all
the docids of the lists, even after it reaches beyond upper bound docid2,
which seems useless at least in above query.


It probably is possible to include an optimization like this.

One problem will be the 'x' passed to the matchinfo() function. One of
the values returned in this case is the number of rows in the entire
table that contain at least one instance of a given query phrase. To
obtain that information we have to iterate through the entire set of
docids anyhow.

Dan.





I made a temporary change to sqlite3.c (function fts3SegReaderNextDocid.c)
just to prove that there is a potential optimization here:

$ p4 diff -d-c sqlite3.c

***
*** 126024,126029 
--- 126024,126030 
   }else{
 rc = fts3SegReaderRequire(pReader, p, FTS3_VARINT_MAX);
 if( rc==SQLITE_OK ){
+ extern sqlite3_int64 docidMax;
   sqlite3_int64 iDelta;
   pReader->pOffsetList = p + sqlite3Fts3GetVarint(p,);
   if( pTab->bDescIdx ){
***
*** 126031,126036 
--- 126032,126042 
   }else{
 pReader->iDocid += iDelta;
   }
+ if ((unsigned long long)pReader->iDocid>  (unsigned long
long)docidMax)
+ {
+   fprintf(stdout, "*** MAX DOCID (0x%llx) REACHED! (abort
unpack of docid list)\n", docidMax);
+   pReader->pOffsetList = 0;
+ }
 }
   }
 }

The docidMax is normally initialized to 0xUL (-1), so my
optimization normally do not do anything. But I set doidMax to docid2
(upper bound of the BETWEEN clause) temporarily when I run my query
and then put it back to 0xUL.

Needless to say that this is an ugly hack which should definitely not be
checked-in as-is.

But it clearly helps to speed up my query and it seems to work (without
being 100% sure it's correct).  I get the same results, faster.
And I hope it's good enough at least to prove that there is a potential
optimization in FTS SQLite. I don't know the SQLite code well enough
to come up with a clean patch for the optimization.

I measured timing several times and it consistently speeds up. For
example, I see some queries that used to take 203 ms which now
take 68 ms. Of course speed up depends on the upper bound
value docid2.

Regards
Dominique
___
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] FTS4 languageid : not sure I understand this correctly

2013-02-01 Thread Richard Hipp

On Thu, Jan 31, 2013 at 3:18 PM, Gert Van Assche  wrote:

> All,
>
> I have the feeling this is the most stupid question ever, but...
> If I create a FTS4 table, put text in it, could I use the languageid to
> figure out what Language that text actually is?
> Is that how langID works?
>

No.  The languageID simply allows the same FTS4 table to hold separate and
independent content for multiple languages.  It is the same as having a
separate table for each languange, except that with languageID you can
choose which language in between sqlite3_prepare() and sqlite3_step()
whereas with separate tables, you must make the choice prior to
sqlite3_prepare().  That difference, though subtle, is very important for
some applications.

> I did some tests, but the LangID seems to be 0 all the time, so or I'm
> doing something wrong, or I misunderstand the process.
>
> thanks
>
> gert
> ___
> 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] Sqlite query doesn't run correctly on different databases

2013-02-01 Thread Paul Sanderson

Thanks Richard that worked


On 1 February 2013 11:23, Richard Hipp  wrote:

> The expression "x NOT IN (something-that-contains-NULL)" is always false.
>  I suggest you add an additional term to the WHERE clause of the subquery:
>  "... AND md5 NOT NULL".
>
> On Fri, Feb 1, 2013 at 6:20 AM, Paul Sanderson <
> sandersonforens...@gmail.com
> > wrote:
>
> > I have a query
> >
> > SELECT * FROM rtable WHERE md5 NOT IN (SELECT md5 FROM rtable WHERE vsc =
> > 0)
> >
> > I have a test data set
> >
> > sqlite> select * from rtable;
> > $RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
> > $RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
> > $RmMetadata|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
> > $RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
> > $RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62
> > $RmMetadata|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62
> > $RmMetadata|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
> > $RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
> > $RmMetadata|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
> >
> > When I execute the above query on my test dataset all works OK. These
> rows
> > are a subset (some of the columns and some of the rows) of a real
> dataset.
> >
> > However I also have a real dataset where the query returns no rows (and
> it
> > should). The real dataset has indexes on all of the relevant columns. The
> > only differences I can think of with the real dataset are that there are
> > rows where MD5 is null, and that I have added a custom collation
> > (SYSTEMNOCASE) for some text columns (although MD5 is a text column the
> > data in it will only ever by capitals so the collation is not used on
> this
> > column).
> >
> > Any ideas what the problem could be?
> > ___
> > 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
>



-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determine if an index has been created

2013-02-01 Thread Simon Slavin

On 1 Feb 2013, at 10:12am, Paul Sanderson  wrote:

> I will know the name of the index - I just need to check that it has been
> created.

Oh, in that case just submit the 'CREATE' command.  If the index already exists 
you'll get an error result and nothing will be done.  If the index doesn't 
exist, it'll be created.

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

Re: [sqlite] Sqlite query doesn't run correctly on different databases

2013-02-01 Thread Richard Hipp

The expression "x NOT IN (something-that-contains-NULL)" is always false.
 I suggest you add an additional term to the WHERE clause of the subquery:
 "... AND md5 NOT NULL".

On Fri, Feb 1, 2013 at 6:20 AM, Paul Sanderson  wrote:

> I have a query
>
> SELECT * FROM rtable WHERE md5 NOT IN (SELECT md5 FROM rtable WHERE vsc =
> 0)
>
> I have a test data set
>
> sqlite> select * from rtable;
> $RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
> $RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
> $RmMetadata|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
> $RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
> $RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62
> $RmMetadata|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62
> $RmMetadata|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
> $RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
> $RmMetadata|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
>
> When I execute the above query on my test dataset all works OK. These rows
> are a subset (some of the columns and some of the rows) of a real dataset.
>
> However I also have a real dataset where the query returns no rows (and it
> should). The real dataset has indexes on all of the relevant columns. The
> only differences I can think of with the real dataset are that there are
> rows where MD5 is null, and that I have added a custom collation
> (SYSTEMNOCASE) for some text columns (although MD5 is a text column the
> data in it will only ever by capitals so the collation is not used on this
> column).
>
> Any ideas what the problem could be?
> ___
> 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] Determine if an index has been created

2013-02-01 Thread Paul Sanderson

Thank You



On 1 February 2013 10:38, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 01/02/13 02:12, Paul Sanderson wrote:
> > I will know the name of the index - I just need to check that it has
> > been created.
>
> Just use pragma index_info on the index name.  If it doesn't exist then
> you get no rows returned and if it does exist then you a row per column in
> the index.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlELmyUACgkQmOOfHg372QSkCwCgvIzw59DcPMytdRssRs37YPRB
> IUMAoKmyDMkKhdubaMqdmp/H9WLrkYAW
> =WCNM
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determine if an index has been created

2013-02-01 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/02/13 02:12, Paul Sanderson wrote:
> I will know the name of the index - I just need to check that it has
> been created.

Just use pragma index_info on the index name.  If it doesn't exist then
you get no rows returned and if it does exist then you a row per column in
the index.

Roger

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

iEYEARECAAYFAlELmyUACgkQmOOfHg372QSkCwCgvIzw59DcPMytdRssRs37YPRB
IUMAoKmyDMkKhdubaMqdmp/H9WLrkYAW
=WCNM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determine if an index has been created

2013-02-01 Thread Paul Sanderson

I will know the name of the index - I just need to check that it has been
created.


On 1 February 2013 00:09, Simon Slavin  wrote:

>
> On 31 Jan 2013, at 10:57pm, Igor Tandetnik  wrote:
>
> > On 1/31/2013 5:45 PM, Paul Sanderson wrote:
> >> Is it possible to ascertain if an index on a particular column has
> already
> >> been created.
> >
> > PRAGMA index_list(YourTable), then for each index, PRAGMA
> index_info(IndexName)
>
> But note that one index can be on any number of columns.  In fact if you
> have long SELECT statements you probably have long indexes too.  So do you
> want to know if you have an index on just that one column, or if you have
> any indexes which include that column ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Peter Aronson

2013-02-01 Thread Peter Aronson

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

Re: [sqlite] SELECT and UPDATE in single query

2013-02-01 Thread Richard Baron Penman

> If you want to know which rows were updated regardless of the key, what
> you need is a column to hold a unique value for each update transaction,
> and set it as part of the UPDATE.  You could add a datetime column,
> for example, if the time resolution is fine enough.

Good idea. I found setting the status column to epoch was considerably faster:

epoch = int(datetime.datetime.now().strftime('%s%f'))
conn.execute('UPDATE queue SET status=? WHERE key in (SELECT key FROM
queue WHERE status=? LIMIT ?);', (epoch, 0, limit))
rows = conn.execute('SELECT key FROM queue WHERE status=?', (epoch,))
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I suspect not - but is the database separator configurable?

[sqlite] fts4 contentless tables, multiple inserts to same doc id?

Re: [sqlite] I suspect not - but is the database separator configurable?

Re: [sqlite] I suspect not - but is the database separator configurable?

Re: [sqlite] Tracking changes with sha1 hashes

Re: [sqlite] I suspect not - but is the database separator configurable?

[sqlite] I suspect not - but is the database separator configurable?

Re: [sqlite] Possible optimization in FTS SQLite? (abort early when unpacking docids of inverted index list)

Re: [sqlite] FTS4 languageid : not sure I understand this correctly

Re: [sqlite] Sqlite query doesn't run correctly on different databases

Re: [sqlite] Determine if an index has been created

Re: [sqlite] Sqlite query doesn't run correctly on different databases

Re: [sqlite] Determine if an index has been created

Re: [sqlite] Determine if an index has been created

Re: [sqlite] Determine if an index has been created

[sqlite] Peter Aronson

Re: [sqlite] SELECT and UPDATE in single query

17 matches

Site Navigation

Mail list logo

Footer information