Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

2010-08-06 Thread Scott Hess 
On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts  wrote:
> On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess  wrote:
>> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts  wrote:
>>> FTS3 only searches full terms/words by default, but I think if I built a 
>>> custom
>>> tokenizer that returned all the suffix trees for a name:
>>
>> FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support
>
> Prefix searches don't allow matching in the middle of words. For
> example, I want  "bert"
> to match my name, "roberts".

Darn.  Sorry, was only thinking with half my brain, and that half
connected your problem up with some past idea.  You're right, you'd
need the tidbits to get at the interior substrings.

That said, you should be able to pretty easily copy the current
tokenizer and modify it to return multiple tokens at a single
location.

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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

2010-08-06 Thread Mohd Radzi Ibrahim 
Have you not consider loading the whole rows into memory array and use simple 
string search or regexp? I'm sure 10,000 records could be search a blink.

best regards,
Radzi.
On 6-Aug-2010, at 3:42 AM, Sam Roberts wrote:

> I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or
> sqlite expert, so I might be thinking about it all wrong.
> 
> I have something like a (read-only) address book/rolodex, with interactive
> searching. As users type into the search box, I need to first know for each
> section how many rows match the substring typed so far.  I only display the
> rows that are visible on screen.
> 
> I have two queries:
> 
> (A) I count the rows in a letter group.
> 
> If they typed "e":
> 
> select substr(name,1,1), count(*) from my_table where name like '%e%'
> group by substr(name,1,1);
> A|94
> B|118
> C|131
> ...
> 
> This is too slow, ~3sec, with 2500 rows, and we want to have 1 rows.
> 
> Worse, when they type "es", the search is as slow after they type "s" as when
> they typed "e", even though the "es" rows are a sub-set of the rows that
> matched "e".
> 
> FTS3 only searches full terms/words by default, but I think if I built a 
> custom
> tokenizer that returned all the suffix trees for a name:
> 
> "fu bar" => [ "r", "ar", "bar", " bar", "u bar", "fu bar"]
> 
> That I could do rewrite query (A) like this:
> 
> select substr(name,1,1), count(*) from my_table where name match 'e*'
> group by substr(name,1,1);
> 
> Is this a reasonable approach? Is there a better way? Has somebody
> else done this?
> 
> 
> 
> (B) I access specific rows within a letter group.
> 
> For visible rows, I fetch them by offset into a letter group, so row 4 in the
> "g" section of names containing "e" would be:
> 
> select * from my_table where name like "g%" and name like "%e%" order
> by name limit 1 offset 4;
> 
> The performance for this is OK, right now, I think it's because the first LIKE
> can use the index, so the linear scan is over only a few hundred rows. Or it
> could be that the on-screen display of each row is slower than the DB search. 
> I
> think it might become a problem, though.
> 
> I'm not sure how I would rewrite this to use FTS3 if it turns out to be to 
> slow
> for a larger DB, maybe a tokenizer that puts the first letter of  the name as
> the first letter of every suffix?
> ___
> 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] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

2010-08-06 Thread Sam Roberts 
On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess  wrote:
> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts  wrote:
>> FTS3 only searches full terms/words by default, but I think if I built a 
>> custom
>> tokenizer that returned all the suffix trees for a name:
>
> FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support

Prefix searches don't allow matching in the middle of words. For
example, I want  "bert"
to match my name, "roberts".

So, I think I'd need to tokenize roberts as "s", "ts", ..., "berts",
"oberts", ... etc.

Then do a prefix match for "bert*" in order to see that "roberts" matches.

Lucky, I don't need or care about any of the snippeting stuff, because
I'm matching short strings (names).

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

Re: [sqlite] Multi table select

2010-08-06 Thread taftech 



Igor Tandetnik wrote:
> 
> 
> select table1.id, table2.date
> from table1 left join table2 on (table1.id = table2.id);
> 
> 


Thanks, You beat me to it.  

And for those who don't have a clue what a "left join" is (this was me 2
days ago)

It includes all of the entries from the "left" table (even if there are no
entries on the "right table), matched with the entries that are on the
"right" table.
-- 
View this message in context: 
http://old.nabble.com/Multi-table-select-tp29356401p29363582.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] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

2010-08-06 Thread Scott Hess 
Yes.  Pawel is wondering if he could patch fts1 and fts2.  I don't
think Chromium cares about fts1 (our version was patched for
completeness), but I believe there are still places where fts2 is
present because older databases might be using it.

-scott


On Fri, Aug 6, 2010 at 12:10 PM, Richard Hipp  wrote:
> FTS3 updated here:  http://www.sqlite.org/src/ci/b8b465ed2c
>
> On Fri, Aug 6, 2010 at 2:24 PM, Scott Hess  wrote:
>
>> This bug comment describes the problem:
>>   http://code.google.com/p/chromium/issues/detail?id=15261#c20
>>
>> excerpt:
>> > Apparently the problem is caused by tolower(), whose behavior is affected
>> by current
>> > locale. Under locale tr_TR.UTF-8, tolower('I') returns 'I' rather than
>> 'i', because
>> > lower case of 'I' defined in tr_TR is 'ı' (U+0131).
>>
>> I think at the time the bug was being diagnosed, sqlite3_strnicmp()
>> wasn't being exposed.  I think that does the right thing because it
>> uses the internal UpperToLower table.
>>
>> -scott
>>
>>
>> On Fri, Aug 6, 2010 at 11:11 AM, Richard Hipp  wrote:
>> > If "ch" is an unsigned char then how is the following unsafe:
>> >
>> >     ch = (ch<0x80) ? tolower(ch) : ch
>> >
>> > And why does it need to be changed to
>> >
>> >    ch = (ch>='A' && ch<='Z') ? ch - 'A' + 'a' : ch;
>> >
>> > There is only one such instance of code remaining in FTS3 (at
>> > fts3_tokenizer1.c:196) but I want to understand what the issue is before
>> I
>> > change it.
>> >
>> > On Fri, Aug 6, 2010 at 1:30 PM, Paweł Hajdan, Jr.
>> > wrote:
>> >
>> >> On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. <
>> phajdan...@chromium.org
>> >> >wrote:
>> >>
>> >> > I'm attaching a suggested patch to fix locale-unsafe usage of tolower
>> in
>> >> > FTS code. The goal is to make Chromium closer to the upstream, so if
>> you
>> >> > have a better solution, that's great.
>> >>
>> >>
>> >> Oh, I have just noticed that the mailing list removes all attachments.
>> What
>> >> is the best way to send patches then?
>> >>
>> >> By the way, any suggestions about the Chromium patch I linked to (
>> >>
>> >>
>> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup
>> >> )?
>> >> It seems that it has somehow been fixed in fts3 code. I'm not yet very
>> >> familiar with the SQLite codebase though, so could you point me to the
>> >> fixes?
>> >> ___
>> >> 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
>> >
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

2010-08-06 Thread Nicolas Williams 
On Fri, Aug 06, 2010 at 02:11:33PM -0400, Richard Hipp wrote:
> If "ch" is an unsigned char then how is the following unsafe:
> 
>  ch = (ch<0x80) ? tolower(ch) : ch
> 
> And why does it need to be changed to
> 
> ch = (ch>='A' && ch<='Z') ? ch - 'A' + 'a' : ch;
> 
> There is only one such instance of code remaining in FTS3 (at
> fts3_tokenizer1.c:196) but I want to understand what the issue is before I
> change it.

The problem is that libc's tolower() is locale-aware.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

2010-08-06 Thread Richard Hipp 
FTS3 updated here:  http://www.sqlite.org/src/ci/b8b465ed2c

On Fri, Aug 6, 2010 at 2:24 PM, Scott Hess  wrote:

> This bug comment describes the problem:
>   http://code.google.com/p/chromium/issues/detail?id=15261#c20
>
> excerpt:
> > Apparently the problem is caused by tolower(), whose behavior is affected
> by current
> > locale. Under locale tr_TR.UTF-8, tolower('I') returns 'I' rather than
> 'i', because
> > lower case of 'I' defined in tr_TR is 'ı' (U+0131).
>
> I think at the time the bug was being diagnosed, sqlite3_strnicmp()
> wasn't being exposed.  I think that does the right thing because it
> uses the internal UpperToLower table.
>
> -scott
>
>
> On Fri, Aug 6, 2010 at 11:11 AM, Richard Hipp  wrote:
> > If "ch" is an unsigned char then how is the following unsafe:
> >
> > ch = (ch<0x80) ? tolower(ch) : ch
> >
> > And why does it need to be changed to
> >
> >ch = (ch>='A' && ch<='Z') ? ch - 'A' + 'a' : ch;
> >
> > There is only one such instance of code remaining in FTS3 (at
> > fts3_tokenizer1.c:196) but I want to understand what the issue is before
> I
> > change it.
> >
> > On Fri, Aug 6, 2010 at 1:30 PM, Paweł Hajdan, Jr.
> > wrote:
> >
> >> On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. <
> phajdan...@chromium.org
> >> >wrote:
> >>
> >> > I'm attaching a suggested patch to fix locale-unsafe usage of tolower
> in
> >> > FTS code. The goal is to make Chromium closer to the upstream, so if
> you
> >> > have a better solution, that's great.
> >>
> >>
> >> Oh, I have just noticed that the mailing list removes all attachments.
> What
> >> is the best way to send patches then?
> >>
> >> By the way, any suggestions about the Chromium patch I linked to (
> >>
> >>
> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup
> >> )?
> >> It seems that it has somehow been fixed in fts3 code. I'm not yet very
> >> familiar with the SQLite codebase though, so could you point me to the
> >> fixes?
> >> ___
> >> 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
> >
> ___
> 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] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

2010-08-06 Thread Scott Hess 
On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts  wrote:
> FTS3 only searches full terms/words by default, but I think if I built a 
> custom
> tokenizer that returned all the suffix trees for a name:

FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support
multiple hits at the same position, for stemming purposes.  So you
might be able to get away with making a copy of fts3_tokenizer1.c, and
modifying it to keep an additional flag in the cursor to let you
return each token twice (once reversed).

I can't offhand think of how to distinguish the resulting prefix
matches from suffix matches.  Maybe you can work that out yourself by
using the rows returned to figure it out.  Also note that this will
possibly interact poorly with the snippeting and offset functions.

As a short-term proof-of-concept hack, you could just have two tables.
 Insert your originals into one table, then take last_insert_rowid()
and insert the document reversed into the other table.

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

Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

2010-08-06 Thread Scott Hess 
This bug comment describes the problem:
  http://code.google.com/p/chromium/issues/detail?id=15261#c20

excerpt:
> Apparently the problem is caused by tolower(), whose behavior is affected by 
> current
> locale. Under locale tr_TR.UTF-8, tolower('I') returns 'I' rather than 'i', 
> because
> lower case of 'I' defined in tr_TR is 'ı' (U+0131).

I think at the time the bug was being diagnosed, sqlite3_strnicmp()
wasn't being exposed.  I think that does the right thing because it
uses the internal UpperToLower table.

-scott


On Fri, Aug 6, 2010 at 11:11 AM, Richard Hipp  wrote:
> If "ch" is an unsigned char then how is the following unsafe:
>
>     ch = (ch<0x80) ? tolower(ch) : ch
>
> And why does it need to be changed to
>
>    ch = (ch>='A' && ch<='Z') ? ch - 'A' + 'a' : ch;
>
> There is only one such instance of code remaining in FTS3 (at
> fts3_tokenizer1.c:196) but I want to understand what the issue is before I
> change it.
>
> On Fri, Aug 6, 2010 at 1:30 PM, Paweł Hajdan, Jr.
> wrote:
>
>> On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. > >wrote:
>>
>> > I'm attaching a suggested patch to fix locale-unsafe usage of tolower in
>> > FTS code. The goal is to make Chromium closer to the upstream, so if you
>> > have a better solution, that's great.
>>
>>
>> Oh, I have just noticed that the mailing list removes all attachments. What
>> is the best way to send patches then?
>>
>> By the way, any suggestions about the Chromium patch I linked to (
>>
>> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup
>> )?
>> It seems that it has somehow been fixed in fts3 code. I'm not yet very
>> familiar with the SQLite codebase though, so could you point me to the
>> fixes?
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

2010-08-06 Thread Richard Hipp 
If "ch" is an unsigned char then how is the following unsafe:

 ch = (ch<0x80) ? tolower(ch) : ch

And why does it need to be changed to

ch = (ch>='A' && ch<='Z') ? ch - 'A' + 'a' : ch;

There is only one such instance of code remaining in FTS3 (at
fts3_tokenizer1.c:196) but I want to understand what the issue is before I
change it.

On Fri, Aug 6, 2010 at 1:30 PM, Paweł Hajdan, Jr.
wrote:

> On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr.  >wrote:
>
> > I'm attaching a suggested patch to fix locale-unsafe usage of tolower in
> > FTS code. The goal is to make Chromium closer to the upstream, so if you
> > have a better solution, that's great.
>
>
> Oh, I have just noticed that the mailing list removes all attachments. What
> is the best way to send patches then?
>
> By the way, any suggestions about the Chromium patch I linked to (
>
> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup
> )?
> It seems that it has somehow been fixed in fts3 code. I'm not yet very
> familiar with the SQLite codebase though, so could you point me to the
> fixes?
> ___
> 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] copying only new rows from one table to another

2010-08-06 Thread Jay A. Kreibich 
On Fri, Aug 06, 2010 at 04:30:49PM +0100, Paul Sanderson scratched on the wall:
> Newbie question
> 
> I have two tables and I want to copy (occasionally) a few thousand
> columns that have been added to table1 into table2, both tables have
> the same unique key but otherwise the columns are different.
> 
> table2 will be initially populated from table1 using
> 
> INSERT into table table2 SELECT refno from table1 WHERE flag > 0
> 
> I could drop table2 and then do the above but will then obviously lose
> any data that has been modified in table2. How can I just append the
> new rows from table1 into table2

  In addition to Igor's suggestion, you may also be able to do an LEFT
  OUTER JOIN, and look for NULLs in the right-hand side (assuming your
  unique keys are NOT NULL):

  INSERT INTO table2
SELECT table1.refno, ... 
  FROM table1 LEFT OUTER JOIN table2
 ON table1.key = table2.key
  WHERE table2.key IS NULL;

  I have no idea which would be faster.  You'll need to try and see.

  You could also try to do a sub-query that does a compound EXCEPT,
  although I would guess that's slower for this case.  I don't actually
  know, however.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

2010-08-06 Thread Paweł Hajdan , Jr . 
On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. wrote:

> I'm attaching a suggested patch to fix locale-unsafe usage of tolower in
> FTS code. The goal is to make Chromium closer to the upstream, so if you
> have a better solution, that's great.


Oh, I have just noticed that the mailing list removes all attachments. What
is the best way to send patches then?

By the way, any suggestions about the Chromium patch I linked to (
http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup)?
It seems that it has somehow been fixed in fts3 code. I'm not yet very
familiar with the SQLite codebase though, so could you point me to the
fixes?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

2010-08-06 Thread Sam Roberts 
On Fri, Aug 6, 2010 at 6:11 AM, Adam DeVita  wrote:
> A variant on Simon's plan.
> Are the 10,000 rows static, slowly changing, or frequently changing?

Never change, it's read-only.

>  Does
> it make sense to pre-calculate some counts at the time data is loaded?
>  Is
> this memory constrained so much that you can't afford 1 or 2 MB to let you
> look up based on ints? (I'm assuming that one letter is all you are after,
> either 'starts with' or 'contains' and not in order combinations.)

No, substrings, it's just that I then need a count of matching
substrings by first char.

Good idea, there are a number of other queries where pre-calculating
is linear in the space cost, but here the the usage is interactive
search, where as they type more of the name, it narrows down the
search results as people type in more.

Pre-calculating would be about 40 factorial in space, there are about
64000 3-character strings, and then once  they typed the 4th char in
it would be slow again. Of course, not all of those exist. Hm. Maybe
I'll try to precalculate the suffix tree, and see how many results
there really are, I don't need to store zero results.

The fastest I've found so far is using FTS3. Its a little slow, but
not unusably so. There are only 2500 rows now, I hope that it will
scale well as the DB increases in size. I'm still considering other
approaches, maybe a custom b-tree.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database is locked

2010-08-06 Thread Paul Sanderson 
I expect that this has been asked before but I cant find anything
useful via google.

I am updating about 20 rows in a table and setting a particular value
to a particular value. The first time I do this all is OK, if I try
again a few seconds later I get a database is locked error.

The code is (using UniDAC), x is passed as a value to the function

try
{
UniConnection1->StartTransaction();
for(int i=0; iRecNo = i;
Tab->Edit();
Tab->FieldByName("cat")->AsInteger = x;
ThumbTab->Post();
}
}
__finally
{
UniConnection1->Commit();
Beep(1000,200);
}

I have added the Beep message to make sure that commit is returning
quickly, which it is doing

I am updating the same set of records, if I move on within the dataset
by count records and then update a new set of rows all works OK, if I
then move back to the previous set and update all is OK. The error
just happens when I trya nd update the same set of records on two
consecutive occasions

What can cause the dataabse to be locked in this way, are there any
sqliteisms I should be looking for.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copying only new rows from one table to another

2010-08-06 Thread Paul Sanderson 
Thanks igor - ill give that a go.

On 6 August 2010 16:36, Igor Tandetnik  wrote:
> Paul Sanderson  wrote:
>> I have two tables and I want to copy (occasionally) a few thousand
>> columns that have been added to table1 into table2, both tables have
>> the same unique key but otherwise the columns are different.
>>
>> table2 will be initially populated from table1 using
>>
>> INSERT into table table2 SELECT refno from table1 WHERE flag > 0
>>
>> I could drop table2 and then do the above but will then obviously lose
>> any data that has been modified in table2. How can I just append the
>> new rows from table1 into table2
>
> If refno is a primary key in table2, or has a unique constraint on it, you 
> can do
>
> insert or ignore into table2(refno)
> select refno from table1;
>
> "or ingore" part instructs SQLite to skip over all rows that would cause 
> uniqueness conflict if inserted.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul Sanderson
Sanderson Forensics
+44 (0)1869 325667
www.sandersonforensics.com
http://www.twitter.com/sandersonforens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copying only new rows from one table to another

2010-08-06 Thread Igor Tandetnik 
Paul Sanderson  wrote:
> I have two tables and I want to copy (occasionally) a few thousand
> columns that have been added to table1 into table2, both tables have
> the same unique key but otherwise the columns are different.
> 
> table2 will be initially populated from table1 using
> 
> INSERT into table table2 SELECT refno from table1 WHERE flag > 0
> 
> I could drop table2 and then do the above but will then obviously lose
> any data that has been modified in table2. How can I just append the
> new rows from table1 into table2

If refno is a primary key in table2, or has a unique constraint on it, you can 
do

insert or ignore into table2(refno)
select refno from table1;

"or ingore" part instructs SQLite to skip over all rows that would cause 
uniqueness conflict if inserted.
-- 
Igor Tandetnik


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

[sqlite] copying only new rows from one table to another

2010-08-06 Thread Paul Sanderson 
Newbie question

I have two tables and I want to copy (occasionally) a few thousand
columns that have been added to table1 into table2, both tables have
the same unique key but otherwise the columns are different.

table2 will be initially populated from table1 using

INSERT into table table2 SELECT refno from table1 WHERE flag > 0

I could drop table2 and then do the above but will then obviously lose
any data that has been modified in table2. How can I just append the
new rows from table1 into table2

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

Re: [sqlite] sqlite3-dbf - converter of XBase / FoxPro tables to SQLite

2010-08-06 Thread Alexey Pechnikov 
Sources for MinGW and compiled binary.
Note: MEMO files does not supported because I don't know how to
emulate mmap interface for MinGW

http://mobigroup.ru/files/sqlite3-dbf/


2010/8/6 Oliver Peters :
> I'm only a heavy user (!= programmer) so if you have an extension for directly
> importing dbf-files into sqlite you make me very happy; BTW: I've to work with
> M$ and can't use standard unix libraries.

-- 
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] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

2010-08-06 Thread Adam DeVita 
A variant on Simon's plan.
Are the 10,000 rows static, slowly changing, or frequently changing?   Does
it make sense to pre-calculate some counts at the time data is loaded?  Is
this memory constrained so much that you can't afford 1 or 2 MB to let you
look up based on ints? (I'm assuming that one letter is all you are after,
either 'starts with' or 'contains' and not in order combinations.)

Adam

On Thu, Aug 5, 2010 at 5:40 PM, Simon Slavin  wrote:

>
> On 5 Aug 2010, at 10:03pm, Sam Roberts wrote:
>
> > But do you think the section would make the counting faster? I think
> > I'd have to get the row counts like this, which would still do the
> > slow full table scan:
> >
> >  select section, count(*) from my_table where name like '%e%' group by
> section;
>
> But 'group by section' can profit from the index on the section column so
> it should be faster.
>
> As with all these things, the suggestion is to try it and see.  You should
> try six or seven different solutions including shuffling columns and indexes
> before you settle on the one that will be in your final code.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slowdown when adding terms to query

2010-08-06 Thread Igor Tandetnik 
Edward Hawke  wrote:
> I have a query that is working at an acceptable speed, and I need to add
> something else to it. As soon as I add the extra terms it grinds to a halt
> (taking well over 5 minutes to perform the query on a relatively small
> dataset c.100,000 records).

But because you repeat the same table three-four times in the FROM clause, thus 
building a cross-product, you are effectively working with a dataset of some 
10^15 records. Anything that suppress the use of indexes by SQLite will cause 
performance to tank.

> My acceptably fast query is fairly complicated as it pulls information from
> a number of different tables, linking them all using WHERE clauses (I've
> read somewhere this is the fastest way to do this in SQLite). It is shown
> below (apologies for it's length) though I have cut out the information
> about which columns it is selecting as it makes it too long to comfortably
> read.
> 
> *SELECT*  *FROM* Delivery, Match, Tour,
> Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam
> PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam,
> Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID =
> Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID =
> BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID =
> BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND
> BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
> Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
> Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
> AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
> AND Batsman.ID = 1234567890);
> 
> There are often multiple IDs specified at the end of the WHERE clause, as
> these are added depending upon selections a user has made from a GUI.
> 
> In a very specific case I need to select the above plus one unrelated row.
> Therefore my query changes to (for example):
> 
> *SELECT*  *FROM* Delivery, Match, Tour,
> Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam
> PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam,
> Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID =
> Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID =
> BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID =
> BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND
> BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
> Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
> Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
> AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
> AND Batsman.ID = 1234567890) *OR* (Delivery.MatchID = Match.ID AND
> Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND
> BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND
> Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID =
> Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
> Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
> Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
> AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
> AND Delivery.ID = 1987654321);

Using OR pretty much kills optimization in SQLite. Run this as two separate 
queries, or as a UNION query with two subqueries, each of which only uses AND.
-- 
Igor Tandetnik

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

Re: [sqlite] Slowdown when adding terms to query

2010-08-06 Thread Richard Hipp 
On Fri, Aug 6, 2010 at 5:23 AM, Edward Hawke wrote:

>
> I have done an EXPLAIN QUERY PLAN on the query, which gave me the
> following:
>
> "0","2","TABLE Tour"
> "1","4","TABLE Country"
> "2","1","TABLE Match"
> "3","11","TABLE Team AS BowlingTeam"
> "4","12","TABLE Team AS BattingTeam"
> "5","13","TABLE Team AS HomeTeam"
> "6","14","TABLE Team AS AwayTeam"
> "7","3","TABLE Ground"
> "8","8","TABLE Player AS Bowler"
> "9","9","TABLE Player AS Batsman"
> "10","10","TABLE Player AS Partner"
> "11","5","TABLE PlayerTeam AS BowlerPT"
> "12","6","TABLE PlayerTeam AS BatsmanPT"
> "13","0","TABLE Delivery VIA MULTI-INDEX UNION"
> "14","7","TABLE PlayerTeam AS PartnerPT"
> "0","0","TABLE Delivery WITH INDEX Delivery_BatsmanPTIdx"
> "0","0","TABLE Delivery WITH INDEX sqlite_autoindex_Delivery_1"
>
> But I am unsure on how to read this, and what information it is giving me
> about how to better select my indices.
>

What this shows us is that, except for the subqueries on the Delivery table,
you are not using any indices anywhere.  As far as this query is concerned,
you might as well not have any indices on any of your tables other than
Delivery.

You can start by adding indices on terms that appear on either side of an =
in your WHERE clause.  The whole story is rather more complex, but that will
be a good start.


-- 
-
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] EXTERNAL: Slowdown when adding terms to query

2010-08-06 Thread Black, Michael (IS) 
Try putting Delivery.ID=1987654321 at the front of your query.  I suspect 
that's a pretty small set.
 
And I also assume you have a Delivery index for ID.  Would help if you would 
show your indexes.  Also, show the explain with and without the added column.  
That should show what it's doing different.
 
Also...how big is the database?  Can you increase the cache to squeeze it all 
in memory?  PRAGMA cache_size=? -- default is 2000.
 
I believe the general rule is to do the table joins from least-to-most matches. 
 If you have one part of your query that returns a very small set do that first.
 
I'm sure somebody will have a much better solution than what I'm saying 
here...I'm always having to re-learn SQL as I don't use it very often.
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Edward Hawke
Sent: Fri 8/6/2010 4:23 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Slowdown when adding terms to query




 Hi all,

I have a query that is working at an acceptable speed, and I need to add
something else to it. As soon as I add the extra terms it grinds to a halt
(taking well over 5 minutes to perform the query on a relatively small
dataset c.100,000 records).

I've looked into the speed optimisations suggested on the site and tried a
few but can't seem to speed it up. I think setting better indices will help
me, but I'm fairly green when it comes to choosing which will be best for a
query.

My acceptably fast query is fairly complicated as it pulls information from
a number of different tables, linking them all using WHERE clauses (I've
read somewhere this is the fastest way to do this in SQLite). It is shown
below (apologies for it's length) though I have cut out the information
about which columns it is selecting as it makes it too long to comfortably
read.

*SELECT*  *FROM* Delivery, Match, Tour,
Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam
PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam,
Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID =
Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID =
BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID =
BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND
BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
AND Batsman.ID = 1234567890);

There are often multiple IDs specified at the end of the WHERE clause, as
these are added depending upon selections a user has made from a GUI.

In a very specific case I need to select the above plus one unrelated row.
Therefore my query changes to (for example):

*SELECT*  *FROM* Delivery, Match, Tour,
Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam
PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam,
Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID =
Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID =
BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID =
BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND
BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
AND Batsman.ID = 1234567890) *OR* (Delivery.MatchID = Match.ID AND
Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND
BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND
Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID =
Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
AND Delivery.ID = 1987654321);

This is the query that takes an unbelievable amount of time to process
compared to the approximately 10 seconds the first query does. Does anyone
have any suggestions for what indices to use or how to change this query to
speed it up?

I have done an EXPLAIN QUERY PLAN on the query, which gave me the following:

"0","2","TABLE Tour"
"1","4","TABLE Country"
"2","1","TABLE Match"
"3","11","TABLE Team AS BowlingTeam"
"4","12","TABLE Team AS BattingTeam"
"5","13","TABLE Team AS HomeTeam"
"6","14","TABLE Team AS AwayTeam"
"7","3","TABLE Ground"
"8","8","TABLE Player AS Bowler"
"9","9","TABLE Player AS Batsman"
"10","10","TABLE Player AS Partner"
"11","5","TABLE

[sqlite] Slowdown when adding terms to query

2010-08-06 Thread Edward Hawke 
 Hi all,

I have a query that is working at an acceptable speed, and I need to add
something else to it. As soon as I add the extra terms it grinds to a halt
(taking well over 5 minutes to perform the query on a relatively small
dataset c.100,000 records).

I've looked into the speed optimisations suggested on the site and tried a
few but can't seem to speed it up. I think setting better indices will help
me, but I'm fairly green when it comes to choosing which will be best for a
query.

My acceptably fast query is fairly complicated as it pulls information from
a number of different tables, linking them all using WHERE clauses (I've
read somewhere this is the fastest way to do this in SQLite). It is shown
below (apologies for it's length) though I have cut out the information
about which columns it is selecting as it makes it too long to comfortably
read.

*SELECT*  *FROM* Delivery, Match, Tour,
Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam
PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam,
Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID =
Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID =
BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID =
BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND
BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
AND Batsman.ID = 1234567890);

There are often multiple IDs specified at the end of the WHERE clause, as
these are added depending upon selections a user has made from a GUI.

In a very specific case I need to select the above plus one unrelated row.
Therefore my query changes to (for example):

*SELECT*  *FROM* Delivery, Match, Tour,
Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam
PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam,
Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID =
Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID =
BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID =
BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND
BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
AND Batsman.ID = 1234567890) *OR* (Delivery.MatchID = Match.ID AND
Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND
BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND
Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID =
Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
AND Delivery.ID = 1987654321);

This is the query that takes an unbelievable amount of time to process
compared to the approximately 10 seconds the first query does. Does anyone
have any suggestions for what indices to use or how to change this query to
speed it up?

I have done an EXPLAIN QUERY PLAN on the query, which gave me the following:

"0","2","TABLE Tour"
"1","4","TABLE Country"
"2","1","TABLE Match"
"3","11","TABLE Team AS BowlingTeam"
"4","12","TABLE Team AS BattingTeam"
"5","13","TABLE Team AS HomeTeam"
"6","14","TABLE Team AS AwayTeam"
"7","3","TABLE Ground"
"8","8","TABLE Player AS Bowler"
"9","9","TABLE Player AS Batsman"
"10","10","TABLE Player AS Partner"
"11","5","TABLE PlayerTeam AS BowlerPT"
"12","6","TABLE PlayerTeam AS BatsmanPT"
"13","0","TABLE Delivery VIA MULTI-INDEX UNION"
"14","7","TABLE PlayerTeam AS PartnerPT"
"0","0","TABLE Delivery WITH INDEX Delivery_BatsmanPTIdx"
"0","0","TABLE Delivery WITH INDEX sqlite_autoindex_Delivery_1"

But I am unsure on how to read this, and what information it is giving me
about how to better select my indices.

Thanks in advance for all your help,

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

Re: [sqlite] Repairing a Database.

2010-08-06 Thread Kirk Clemons 
Thank you,
I believe this will help. Since my last email I took Rogers suggestion of 
looking over the structure of a database file and decided that if there is 
anything recovered from the .dump that it will need to be inserted into a 
database "template" in order to be functional.

The result was successful. I created a new database by importing the schema of 
a backup database that had not been corrupted like this;

.output Schema.txt
.schema

I then created a new database and set all of my user_version, journal_mode, 
etc. to match my original pre-corrupt specifications. Then I used .read to read 
in Schema.txt. This created an empty template database. Then I read in my dump 
file. The only errors were that the tables already exist but the insert into 
statements went through and my new database will launch in my application and I 
am able to recover the 3D symbols that were stored there and back them up.

It is not a 100% of course but it shows promise for future corrupt databases. I 
will test it on a few more of my client's databases and send an update of my 
findings.

Thank you all for your help,
~Kirk

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Thursday, August 05, 2010 4:40 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Repairing a Database.


On 4 Aug 2010, at 10:15pm, Kirk Clemons wrote:

> Also, does this mean that I could take a backup copy of my database and 
> import the data that is retrieved from the .dump command on the corrupt 
> database? 

We cannot tell what .dump will get from your old database because it is 
corrupt.  It might miss out lots of records.  It might appear to be doing all 
the records but actually put the same values in each one.  It might dump the 
entire database perfectly.

> If so how would I do this and get past the PRIMARY KEY/existing table errors?

You can edit the file to replace 'INSERT' with 'INSERT OR IGNORE' or some 
variation on that.  See



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

[sqlite] SQlite 3.7.0 fix for sys/mman.h inclusion

2010-08-06 Thread Alan Hourihane 
When building sqlite 3.7.0 on a system that doesn't have mmap, I use the
SQLITE_OMIT_WAL option, but this check didn't make it around the
 inclusion.

Patch attached to fix this problem.

Thanks,

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

Re: [sqlite] sqlite3-dbf - converter of XBase / FoxPro tables to SQLite

2010-08-06 Thread Oliver Peters 
Alexey Pechnikov  writes:

> 
> This is not tested enough but it's work for me.  May be it's
> interesting for somebody.
> 
> http://sqlite.mobigroup.ru/wiki?name=sqlite3-dbf
> 

I'm only a heavy user (!= programmer) so if you have an extension for directly
importing dbf-files into sqlite you make me very happy; BTW: I've to work with
M$ and can't use standard unix libraries.


thx in advance ;-)

Oliver

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

[sqlite] sqlite3-dbf - converter of XBase / FoxPro tables to SQLite

2010-08-06 Thread Alexey Pechnikov 
This is not tested enough but it's work for me.  May be it's
interesting for somebody.

http://sqlite.mobigroup.ru/wiki?name=sqlite3-dbf

-- 
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