Re: [sqlite] Ideas or Guide on SQLite Search Engine for a relational database?

[Domingo Alvarez Duarte]

Hello Scoott !

I have done some work with a prototyping tool for data applications 
using sqlite https://github.com/mingodad/db-api-server and use for 
navigate several opendata sources like (sorry it is in Spainsh) 
https://meimporta.eu look here 
https://borme.w.meimporta.eu/bormeMalaga-lm.app .


With this tool we can create links that make easy navigate/filter/search 
non trivial amounts of relational data.


I used this repository https://github.com/mingodad/db-api-server for a 
talk about prototyping database applications, I hope it can be helpful 
for you or other people with similar needs.


Cheers !

On 20/01/2019 18:02, Andy Goth wrote:

You can try reading the Fossil source code to see how it handles full-text
searching across multiple tables.

https://fossil-scm.org/index.html/artifact?fn=src/search.c&ci=trunk

On Sat, Jan 19, 2019, 06:10 Scott 
I apologize, I sent this from a different email than I registered
accidentally.
Hi Everyone!
The Ask and what I’ve done:
I'm building a research database with an embedded SQLite DB using Intellij
for development. I've created the data entry point for the users; however,
I'm looking for anything anyone might want to share or suggest as a code,
link, or design, whatever it may be, on developing a search engine for a
relational database. I’ve been reading through tutorials on
SQLiteTutorials.net to learn a little more about indexing and searching.
I’ve come across the FTS5 (“Full Text Search”) tutorial, but I’m having
difficulty finding anything that may assist with a multi-table search. I
guess it would nice to have an example to follow. I wouldn’t think this
would be the first-time for this, but I’m a little naïve too.

I'm not asking for someone to solve the problem but just provide a
direction that may save a lot of research time. I don’t know if my images
will post or be available through this email, but I've provided some images
below of the GUI and the SQLite DB which has 8 tables all linking back to
the primary table called "Source". This is a new task for me and my
development skills in Java.

The Goal:
This Research DB users want to be able to search the database either by
Source, Topic, Question, Comments, Quotes, Authors, which most are a one to
many with one a many to many relationship. A single source, for example a
book entered in the database, could be linked to multiple topics,
questions, Comments, Quotes, etc. So the users may want to search each of
these separately on a particular subject, or possibly all the tables for a
particular subject. Topic will help the mostly, but a topic may be
addressed in a comment or quote, etc, that may not be associated directly
with a particular topic. So there is a need to search a number of fields
over 8 tables. My thoughts are that the search criteria should probably
return into a table list, then when the user selects a particular row, this
data would populate into corresponding fields for readability similar to
the "Entry" tab (see below).

The only thing I suspect may make this easier than expected is that no
matter whether the user searches by Topic, or Question, or Comment, etc,
the search will always join all the tables and return all the fields for
anything linked back to a source. However, the source will be associated to
many of each of those.

Thanks,

Scott





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


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

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

Re: [sqlite] Ideas or Guide on SQLite Search Engine for a relational database?

[Andy Goth]

You can try reading the Fossil source code to see how it handles full-text
searching across multiple tables.

https://fossil-scm.org/index.html/artifact?fn=src/search.c&ci=trunk

On Sat, Jan 19, 2019, 06:10 Scott 
> I apologize, I sent this from a different email than I registered
> accidentally.
> Hi Everyone!
> The Ask and what I’ve done:
> I'm building a research database with an embedded SQLite DB using Intellij
> for development. I've created the data entry point for the users; however,
> I'm looking for anything anyone might want to share or suggest as a code,
> link, or design, whatever it may be, on developing a search engine for a
> relational database. I’ve been reading through tutorials on
> SQLiteTutorials.net to learn a little more about indexing and searching.
> I’ve come across the FTS5 (“Full Text Search”) tutorial, but I’m having
> difficulty finding anything that may assist with a multi-table search. I
> guess it would nice to have an example to follow. I wouldn’t think this
> would be the first-time for this, but I’m a little naïve too.
>
> I'm not asking for someone to solve the problem but just provide a
> direction that may save a lot of research time. I don’t know if my images
> will post or be available through this email, but I've provided some images
> below of the GUI and the SQLite DB which has 8 tables all linking back to
> the primary table called "Source". This is a new task for me and my
> development skills in Java.
>
> The Goal:
> This Research DB users want to be able to search the database either by
> Source, Topic, Question, Comments, Quotes, Authors, which most are a one to
> many with one a many to many relationship. A single source, for example a
> book entered in the database, could be linked to multiple topics,
> questions, Comments, Quotes, etc. So the users may want to search each of
> these separately on a particular subject, or possibly all the tables for a
> particular subject. Topic will help the mostly, but a topic may be
> addressed in a comment or quote, etc, that may not be associated directly
> with a particular topic. So there is a need to search a number of fields
> over 8 tables. My thoughts are that the search criteria should probably
> return into a table list, then when the user selects a particular row, this
> data would populate into corresponding fields for readability similar to
> the "Entry" tab (see below).
>
> The only thing I suspect may make this easier than expected is that no
> matter whether the user searches by Topic, or Question, or Comment, etc,
> the search will always join all the tables and return all the fields for
> anything linked back to a source. However, the source will be associated to
> many of each of those.
>
> Thanks,
>
> Scott
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ideas or Guide on SQLite Search Engine for a relational database?

[Scott]

I apologize, I sent this from a different email than I registered accidentally.
Hi Everyone!
The Ask and what I’ve done:
I'm building a research database with an embedded SQLite DB using Intellij for 
development. I've created the data entry point for the users; however, I'm 
looking for anything anyone might want to share or suggest as a code, link, or 
design, whatever it may be, on developing a search engine for a relational 
database. I’ve been reading through tutorials on SQLiteTutorials.net to learn a 
little more about indexing and searching. I’ve come across the FTS5 (“Full Text 
Search”) tutorial, but I’m having difficulty finding anything that may assist 
with a multi-table search. I guess it would nice to have an example to follow. 
I wouldn’t think this would be the first-time for this, but I’m a little naïve 
too. 
 
I'm not asking for someone to solve the problem but just provide a direction 
that may save a lot of research time. I don’t know if my images will post or be 
available through this email, but I've provided some images below of the GUI 
and the SQLite DB which has 8 tables all linking back to the primary table 
called "Source". This is a new task for me and my development skills in Java.

The Goal:
This Research DB users want to be able to search the database either by Source, 
Topic, Question, Comments, Quotes, Authors, which most are a one to many with 
one a many to many relationship. A single source, for example a book entered in 
the database, could be linked to multiple topics, questions, Comments, Quotes, 
etc. So the users may want to search each of these separately on a particular 
subject, or possibly all the tables for a particular subject. Topic will help 
the mostly, but a topic may be addressed in a comment or quote, etc, that may 
not be associated directly with a particular topic. So there is a need to 
search a number of fields over 8 tables. My thoughts are that the search 
criteria should probably return into a table list, then when the user selects a 
particular row, this data would populate into corresponding fields for 
readability similar to the "Entry" tab (see below).

The only thing I suspect may make this easier than expected is that no matter 
whether the user searches by Topic, or Question, or Comment, etc, the search 
will always join all the tables and return all the fields for anything linked 
back to a source. However, the source will be associated to many of each of 
those.
 
Thanks,
 
Scott
 
  
 
   
   
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search % using sqlite

[Hegde, Deepakakumar (D.)]

Hi,


Thanks a lot for the support.


We are actually using sqlite3_snprintf() with the proper buffer size to make 
sure that overflow will not happen. and also bind operations.


INSTR() seems to be the best option. It seems to be fine. Thanks a lot.


Thanks and Regards

Deepak



From: sqlite-users  on behalf of 
R Smith 
Sent: Friday, January 5, 2018 3:41:35 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Search % using sqlite


On 2018/01/05 10:14 AM, Rowan Worth wrote:
> For this simple search it's easier to replace "NAME LIKE ?" with
> "instr(NAME, ?) > 0", unless you need case insensitive matching.

And in case you do wish for Case-Insensitive matching while using
Rowan's method...

"instr(UPPER(NAME), UPPER(?)) > 0"

(Or LOWER(), if you so wish).


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

Re: [sqlite] Search % using sqlite

[R Smith]

On 2018/01/05 10:14 AM, Rowan Worth wrote:

For this simple search it's easier to replace "NAME LIKE ?" with
"instr(NAME, ?) > 0", unless you need case insensitive matching.


And in case you do wish for Case-Insensitive matching while using 
Rowan's method...


"instr(UPPER(NAME), UPPER(?)) > 0"

(Or LOWER(), if you so wish).


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

Re: [sqlite] Search % using sqlite

[Rowan Worth]

Firstly this code is extremely dangerous. What would happen if
acInputString contained this string?

';DROP TABLE AUDIO; SELECT '

It's best practice to use bound parameters to prevent this kind of problem,
ie.

sqlite3_prepare(db, "SELECT NAME FROM AUDIO WHERE NAME LIKE ?", -1, &stmt,
NULL);
sprintf(acGlobString, "%%%s%%", acInputString);
sqlite3_bind_string(stmt, 1, acGlobString);
sqlite3_step(stmt);

Which is still dangerous if acInputString is larger than can fit in
acGlobString -- snprintf is advised to avoid buffer overflow. And all
sqlite3 return codes should be checked to see if an error occurred, of
course.


Ok now to the actual problem -- you can modify your query to read:

SELECT NAME FROM AUDIO WHERE NAME LIKE ? ESCAPE '!'

The ESCAPE clause defines a character which can be used to match a literal
% instead of % being treated as a wildcard. I've chosen ! as the escape
character, which means you'll have to prefix all !, %, and _ characters
with an ! to get a literal match.


For this simple search it's easier to replace "NAME LIKE ?" with
"instr(NAME, ?) > 0", unless you need case insensitive matching.

-Rowan


On 5 January 2018 at 15:49, Hegde, Deepakakumar (D.) <
deep...@allgosystems.com> wrote:

> Hi All,
>
>
> We are implementing a wild card search feature.  our query is as below:
>
>
> sprintf (acQstring,
>
> "SELECT NAME FROM AUDIO WHERE NAME LIKE '%%%s%%'", acInputString);
>
>
> In the input string is '%' then we are getting t all the entry in the
> column.
>
>
> ex: name column have following:
>
> %a
>
> a
>
> a%a
>
> aa%
>
>
> we are expecting entry 2 which don't have % in it should not get as
> output. But it seems not the case, it is giving all the 4 entry as output.
>
> Please can we know is there any way of searching this? Thanks.
>
>
> Thanks and Regards
>
> Deepak
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Search % using sqlite

[Hegde, Deepakakumar (D.)]

Hi All,


We are implementing a wild card search feature.  our query is as below:


sprintf (acQstring,

"SELECT NAME FROM AUDIO WHERE NAME LIKE '%%%s%%'", acInputString);


In the input string is '%' then we are getting t all the entry in the column.


ex: name column have following:

%a

a

a%a

aa%


we are expecting entry 2 which don't have % in it should not get as output. But 
it seems not the case, it is giving all the 4 entry as output.

Please can we know is there any way of searching this? Thanks.


Thanks and Regards

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

Re: [sqlite] Search semantics with a virtual table?

[Hick Gunter]

The SQLITE_INDEX_SCAN_UNIQUE flag is a hint for the query planner. It does not 
affect query execution mechanics. You should be returning TRUE from xEOF after 
the first call to your xNext function (provided indeed that there is only 1 row 
that matches the value). Or you need to add a LIMIT 1 clause, so that SQLIte 
will not attempt to read more than 1 record.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Deon Brewis
Gesendet: Montag, 03. April 2017 15:52
An: SQLite mailing list 
Betreff: [sqlite] Search semantics with a virtual table?

How do I convince SQLITE to give me SEARCH semantics over a virtual table 
instead of SCAN semantics?

e.g. I have:

explain query plan SELECT * FROM vtable WHERE value = 12345

0 | 0 | 0 | SCAN TABLE vtable VIRTUAL TABLE INDEX 1:

And I'm returning in xBestIndex:
   pIdxInfo->idxNum = 1;
   pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
   pIdxInfo->aConstraintUsage[0].argvIndex = 1;
   pIdxInfo->aConstraintUsage[0].omit = true;
   pIdxInfo->estimatedCost = 1;
   pIdxInfo->estimatedRows = 1;


So obviously the explain might just be iffy, but I'm actually getting SCAN 
semantics. Well, somewhere between SEARCH & SCAN.

I can perform a true search for the '12345' item between xBestIndex & xFilter, 
but after I returned the first item, SQLITE keeps calling back my xNext/xEof to 
ask for more and more items. However... I'm specifying SQLITE_INDEX_SCAN_UNIQUE 
so why doesn't it stop after I returned the first one?

Lacking convincing it to call me more than once... I then tried to tell it that 
the results I return are ordered and maybe it will notice once it runs beyond 
the range. Since there's no way to return an unsolicited order in xBestIndex, 
I've tried:

SELECT * FROM vtable WHERE value = 12345 ORDER BY value

And then responded:
  pIdxInfo->orderByConsumed = 1;


to the request. But nada - it still keeps calling me over and over.

I know I can hack around that by mucking around in my xEof call, but is that 
the only way? It really does seem like SQLITE_INDEX_SCAN_UNIQUE (or something 
similar) should achieve SEARCH semantics directly?

- Deon

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] Search semantics with a virtual table?

[Deon Brewis]

How do I convince SQLITE to give me SEARCH semantics over a virtual table 
instead of SCAN semantics?

e.g. I have:

explain query plan SELECT * FROM vtable WHERE value = 12345

0 | 0 | 0 | SCAN TABLE vtable VIRTUAL TABLE INDEX 1:

And I'm returning in xBestIndex:
   pIdxInfo->idxNum = 1;
   pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
   pIdxInfo->aConstraintUsage[0].argvIndex = 1;
   pIdxInfo->aConstraintUsage[0].omit = true;
   pIdxInfo->estimatedCost = 1;
   pIdxInfo->estimatedRows = 1;


So obviously the explain might just be iffy, but I'm actually getting SCAN 
semantics. Well, somewhere between SEARCH & SCAN.

I can perform a true search for the '12345' item between xBestIndex & xFilter, 
but after I returned the first item, SQLITE keeps calling back my xNext/xEof to 
ask for more and more items. However... I'm specifying SQLITE_INDEX_SCAN_UNIQUE 
so why doesn't it stop after I returned the first one?

Lacking convincing it to call me more than once... I then tried to tell it that 
the results I return are ordered and maybe it will notice once it runs beyond 
the range. Since there's no way to return an unsolicited order in xBestIndex, 
I've tried:

SELECT * FROM vtable WHERE value = 12345 ORDER BY value

And then responded:
  pIdxInfo->orderByConsumed = 1;


to the request. But nada - it still keeps calling me over and over.

I know I can hack around that by mucking around in my xEof call, but is that 
the only way? It really does seem like SQLITE_INDEX_SCAN_UNIQUE (or something 
similar) should achieve SEARCH semantics directly?

- Deon

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

Re: [sqlite] Search for text in all tables

[Max Vlasov]

>> I once implemented a virtual table "allvalues" that outputs all
>> database values with (hope self-explaining) fields
>>
>> TableName, TableRowId, FieldName, Value
>
> Could you expand on how you coped with the underlying database
> changing, and how you mapped virtual table rowids to the actual
> database records?
>

This particular implementation was intended to be used as a
Select-only wrapper so it just iterates through every sqlite_master
table and every row of each table. I didn't support update and insert.
Rowid of this virtual table is compound bit mask starting with table
bits (able to fit the number of tables of this db), field bits
(maximum possible number of fields in a table) and the rest is rowid
of the particular table. So in theory this method won't work for
tables containing large 64-bit ids where there are not enough bits to
be used for table number and field number.

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

Re: [sqlite] Search for text in all tables

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/05/2014 01:24 AM, Max Vlasov wrote:
> I once implemented a virtual table "allvalues" that outputs all 
> database values with (hope self-explaining) fields
> 
> TableName, TableRowId, FieldName, Value

Could you expand on how you coped with the underlying database
changing, and how you mapped virtual table rowids to the actual
database records?

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSB+fcACgkQmOOfHg372QRZ0QCdHbaDwwE0mrE8SaITJhn5lB7K
KugAoJaBjpLVj4zemq9kqS1UsCAyvjuc
=1Jet
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Dominique Devienne]

On Fri, Dec 5, 2014 at 10:24 AM, Max Vlasov  wrote:

> Not particularity the answer to your question, but rather a method you
> or others might use.
> I once implemented a virtual table "allvalues" that outputs all
> database values with (hope self-explaining) fields
>
>   TableName, TableRowId, FieldName, Value
>
> that allows also exploring unknown complex databases.


Thanks for sharing Max! That's a great idea. I'd never would have thought
of that.

I agree with you, Sqlite's virtual tables are great. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Max Vlasov]

On Thu, Dec 4, 2014 at 10:44 AM, Baruch Burstein  wrote:
>
> Is it possible to somehow search for/replace a string in all columns of all
> tables?
>

Not particularity the answer to your question, but rather a method you
or others might use.
I once implemented a virtual table "allvalues" that outputs all
database values with (hope self-explaining) fields

  TableName, TableRowId, FieldName, Value

that allows also exploring unknown complex databases. Take for example
places.sqlite of Mozilla Firefox. If you want to explore where it
saves your visited site, you can use the query

  SELECT Distinct TableName, FieldName FROM allvalues where Value like
"%http://%";

Sqlite's virtual tables are a great tool since with a little effort on
the developer side the newly created entity starts working as a first
class citizen of the sqlite engine. Compare this to a quick hack that
outputs all raw data from a specific database to a specific media.

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

Re: [sqlite] Search for text in all tables

[Petite Abeille]

> On Dec 4, 2014, at 10:26 PM, Roger Binns  wrote:
> 
> That will only work under the simplest of cases. 

Simplicity first and foremost. 

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

Re: [sqlite] Search for text in all tables

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/04/2014 11:59 AM, Petite Abeille wrote:
>> On Dec 4, 2014, at 8:44 AM, Baruch Burstein
>>  wrote:
>> 
>> Is it possible to somehow search for/replace a string in all
>> columns of all tables?
> 
> .dump | sed ’s/old/new/g' | .read ?

That will only work under the simplest of cases.  For example if "old"
occurs anywhere outside a value (eg table name, text of a trigger,
index, column) then the database won't be right.  Baruch also didn't
say if "old" should be an entire value match or a substring match -
the former won't work with sed reliably.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSA0Y4ACgkQmOOfHg372QTCDgCfbsJR9uJ/tVlYVnnn0clU1Egr
x/YAoOUuleJXlh3XEADeAm9CO/DH47qZ
=/ItF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Petite Abeille]

> On Dec 4, 2014, at 8:44 AM, Baruch Burstein  wrote:
> 
> Is it possible to somehow search for/replace a string in all columns of all
> tables?

.dump | sed ’s/old/new/g' | .read ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/03/2014 11:44 PM, Baruch Burstein wrote:
> Is it possible to somehow search for/replace a string in all
> columns of all tables?

(Disclosure: I am the APSW author)

The APSW shell includes a .find command that does the searching bit.
You also get coloured output which is nice.  It was implemented to
help find things in a database where you are unfamiliar with the
schema and wondering where the heck things are referenced amongst lots
of tables and columns.

http://rogerbinns.github.io/apsw/shell.html

The code that implements it is here:

https://github.com/rogerbinns/apsw/blob/master/tools/shell.py#L1384

It essentially has to loop over all tables, and then uses an OR
statement to check for the value in each column.  From the code you
can see it does additional work based on the value so that it may do
string, integer and LIKE comparisons simultaneously as appropriate.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSAsisACgkQmOOfHg372QQC+QCgt4YAbvrxt1luvsnhK/r2R/0Q
l4kAoI3PvnQRvmObQqqGMAGJC1cEvehf
=X77t
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Paul Sanderson]

Had 5 mins waiting for a conference call so knocked this bit of Python
up. No debugging, comments or testing (other than one run on a test
db)

import sqlite3
import tkinter
import tkinter.filedialog

root = tkinter.Tk()
root.withdraw()

fileName = tkinter.filedialog.askopenfilename(parent=root,
title='Select the SQLite database')
DB = sqlite3.connect(fileName)

print ('Processing ' + fileName)

tables = DB.execute("select * from sqlite_master where type like 'table'")

for tablerow in tables:
tableName = tablerow[2]
print ("tableName = " + tableName)
columns = DB.execute("pragma table_info (" + tableName + ")")
for colrow in columns:
colName = colrow[1]
colType = colrow[2]
print ("colName = " + colName)
DB.execute("UPDATE " + tableName + " SET " + colName + " =
REPLACE(" + colName + ", 'paul', 'sandy')")

DB.commit()
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 4 December 2014 at 12:31, Paul Sanderson
 wrote:
> As above I use
>
> pragma table_info tablename
>
> then you can loop through each row and check the type column to see if
> it is text
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
>
>
>
> On 4 December 2014 at 11:57, Dominique Devienne  wrote:
>> On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein 
>> wrote:
>>
>>> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
>>> > If you are a coder then it is a relatively straight forward process
>>> > Loop through each table
>>> >Loop through each column
>>>
>>> This is the part I am having trouble with. I can loop through tables using
>>> sqlite3_master, but how do I loop through columns? Parse the schema?
>>
>>
>> http://www.sqlite.org/pragma.html#pragma_table_info  --DD
>> ___
>> 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] Search for text in all tables

[J T]

Dominique,

Why not get a column count (datareader.fieldcount (C#) or 
cursor.getColumnCount() (Java/Android))?

>From there you should be able to simply do a 
try { 
str = getString(columnIndex); 
checkValue(str); 
} 
catch(Exception e) {  
// wasn't a string or the check (and replace) failed
// you may want to catch different exceptions
}





 

 

 

-Original Message-
From: Dominique Devienne 
To: General Discussion of SQLite Database 
Sent: Thu, Dec 4, 2014 6:57 am
Subject: Re: [sqlite] Search for text in all tables


On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein 
wrote:

> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
> > If you are a coder then it is a relatively straight forward process
> > Loop through each table
> >Loop through each column
>
> This is the part I am having trouble with. I can loop through tables using
> sqlite3_master, but how do I loop through columns? Parse the schema?


http://www.sqlite.org/pragma.html#pragma_table_info  --DD
___
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] Search for text in all tables

[Paul Sanderson]

As above I use

pragma table_info tablename

then you can loop through each row and check the type column to see if
it is text
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 4 December 2014 at 11:57, Dominique Devienne  wrote:
> On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein 
> wrote:
>
>> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
>> > If you are a coder then it is a relatively straight forward process
>> > Loop through each table
>> >Loop through each column
>>
>> This is the part I am having trouble with. I can loop through tables using
>> sqlite3_master, but how do I loop through columns? Parse the schema?
>
>
> http://www.sqlite.org/pragma.html#pragma_table_info  --DD
> ___
> 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] Search for text in all tables

[Dominique Devienne]

On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein 
wrote:

> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
> > If you are a coder then it is a relatively straight forward process
> > Loop through each table
> >Loop through each column
>
> This is the part I am having trouble with. I can loop through tables using
> sqlite3_master, but how do I loop through columns? Parse the schema?


http://www.sqlite.org/pragma.html#pragma_table_info  --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Martin Engelschalk]

Hi Baruch,

in such cases I do a sqlite3_prepare_v2 on "select * from mytable LIMIT 
1" and get the column names using sqlite3_column_count and 
sqlite3_column_name.


Martin
Am 04.12.2014 11:45, schrieb Baruch Burstein:

On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:


If you are a coder then it is a relatively straight forward process
along the lines of

Loop through each table
Loop through each column


This is the part I am having trouble with. I can loop through tables using
sqlite3_master, but how do I loop through columns? Parse the schema?




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

Re: [sqlite] Search for text in all tables

[Baruch Burstein]

On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> If you are a coder then it is a relatively straight forward process
> along the lines of
>
> Loop through each table
>Loop through each column
>

This is the part I am having trouble with. I can loop through tables using
sqlite3_master, but how do I loop through columns? Parse the schema?


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Paul Sanderson]

Hi Baruch

I have a commercial tool that can do the search all tables bit but not
the replacing :) (not usually required by my clients - although I may
add it)

If you are a coder then it is a relatively straight forward process
along the lines of

Loop through each table
   Loop through each column
   UPDATE tablename SET colname = REPLACE(colname, 'oldtext', 'newtext')

The gotchas are how you choose to deal with strings stored in non text
fields and how unique your terms are/what you do with substrings



Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 4 December 2014 at 08:10, Martin Engelschalk
 wrote:
> Hi Baruch,
>
> no, not with SQL as I know it.
>
> Perhaps you can .dump and .output the database to a text file using sqlite3
> command shell, replace your text and then create the database from the SQL
> using .read.
>
> HTH
> Martin
>
> Am 04.12.2014 08:44, schrieb Baruch Burstein:
>>
>> Hi,
>>
>> Is it possible to somehow search for/replace a string in all columns of
>> all
>> tables?
>>
>> Thanks
>>
>
> ___
> 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] Search for text in all tables

[Martin Engelschalk]

Hi Baruch,

no, not with SQL as I know it.

Perhaps you can .dump and .output the database to a text file using 
sqlite3 command shell, replace your text and then create the database 
from the SQL using .read.


HTH
Martin

Am 04.12.2014 08:44, schrieb Baruch Burstein:

Hi,

Is it possible to somehow search for/replace a string in all columns of all
tables?

Thanks



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

[sqlite] Search for text in all tables

[Baruch Burstein]

Hi,

Is it possible to somehow search for/replace a string in all columns of all
tables?

Thanks

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search query alternatives.

[Michael Falconer]

I am glad I posted the question.

Yes James, there is little I can disagree with in your excellent summary.
Even the critique of my perhaps poorly framed question is indeed valid. I
take your point regarding spec vs implementation, and in my experience
across different rdbms's I have frequently seen evidence which supports
your assertions regarding db/os system influence on implementation
approaches.

Application code too has it's part to play. How do we plan to access the
data? There's a strong case too, IMHO, to have flexibility in the design,
perhaps leading to differing approaches with the variable types of data we
may be storing.

The original design decisions were made by someone who was, and still is,
essentially a hobby programmer. I don't think he'd ever heard of of Chris
Date or Mr Codd at that time and like all novice application programmers,
he had little understanding about the effect db design could have on his
application and it's source code. His design choices were initially made on
the basis of what he could easily understand and what was (as it appeared
to him then) easy to program with. It is some time ago, and we who have
lived with rdbms's for years get to say, 'that is a horrible design!'. I
think R. Smith hit on a point above, regarding code overhead. Yep, plenty
of that. And so the lesson is learned the hard way for someone who until
recently had viewed normalisation as a way to make coding harder and to
slow down the execution of queries.

So with that perspective you can perhaps come some way to understanding the
why component. On analysis, I agree with suggested design changes at the
higher level. i.e. Dynamic tables are at the root of issues going forward.
They are requiring tedious application code gymnastics, more difficult
query analysis and poorer query performance.  I suppose the upside is that
it will be a challenge to see what improvements can be made, and that is
always fun and games. I kind of like Mr Smith's other suggestion about an
SQLITE testbed or prototype. So easy to work with SQLITE, and probably
perfect for this task. Thanks all for your contributions.

Just FYI James, the application is coded in php and connects to a mysql
database. It can be installed either as a browser based, stand alone or
client server app. It's common implementation is on low end shared hosts,
even free hosting services. So this limits us somewhat to what is commonly
allowed on such platforms. Things like Stored Procedures are unfortunately
outside our scope when it come to design considerations.

Thanks all.


On 18 October 2014 02:24, James K. Lowden  wrote:

> On Thu, 16 Oct 2014 09:05:51 +1100
> Michael Falconer  wrote:
>
> > we just wonder if there is a better way to perform this search in
> > SQL. Is there a general technique which is superior either in speed,
> > efficiency or load bearing contexts?
>
> The simple answer is No, because SQL is a specification, not an
> implementation.  Different systems implement it differently and
> therefore perform differently.  Any "general technique" affecting
> performance belongs to the implementation per se, not the SQL, which is
> a logical construction. SQLite itself has changed its performance
> characteristics over the course of its development.
>
> For that reason, any question of performance has to be answered in
> terms of a particular implementation, even its specific version, and
> the OS and hardware it's running on.
>
> That said, there is reason to suppose that a single-table design would
> be more efficient.  If the queries can be expressed with recursion and
> the indexes lead to efficient searches, the query optimizer has less
> work to do.  It has fewer permutations to consider, and the search is
> apt to touch fewer pages.  The analysis tools of the system you're
> using should be able to confirm or deny that supposition.
>
> I would remind your fellows, though, that efficiency is not all.  The
> utility of a model (that is, the database design) is measured by how
> well, to its purpose, it describes the real world.  Any model that must
> be changed as that reality changes in predictable ways isn't really
> much of a model; it turns the designer into a component of the model.
> By recognizing all trees as one, you generalize your model and make it
> do work you are now doing yourself (manually, or in application
> logic).  By any measure, that makes it a better model.
>
> HTH.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search query alternatives.

[James K. Lowden]

On Thu, 16 Oct 2014 09:05:51 +1100
Michael Falconer  wrote:

> we just wonder if there is a better way to perform this search in
> SQL. Is there a general technique which is superior either in speed,
> efficiency or load bearing contexts?

The simple answer is No, because SQL is a specification, not an
implementation.  Different systems implement it differently and
therefore perform differently.  Any "general technique" affecting
performance belongs to the implementation per se, not the SQL, which is
a logical construction. SQLite itself has changed its performance
characteristics over the course of its development.  

For that reason, any question of performance has to be answered in
terms of a particular implementation, even its specific version, and
the OS and hardware it's running on.  

That said, there is reason to suppose that a single-table design would
be more efficient.  If the queries can be expressed with recursion and
the indexes lead to efficient searches, the query optimizer has less
work to do.  It has fewer permutations to consider, and the search is
apt to touch fewer pages.  The analysis tools of the system you're
using should be able to confirm or deny that supposition.  

I would remind your fellows, though, that efficiency is not all.  The
utility of a model (that is, the database design) is measured by how
well, to its purpose, it describes the real world.  Any model that must
be changed as that reality changes in predictable ways isn't really
much of a model; it turns the designer into a component of the model.
By recognizing all trees as one, you generalize your model and make it
do work you are now doing yourself (manually, or in application
logic).  By any measure, that makes it a better model.  

HTH.  

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

Re: [sqlite] Search query alternatives.

[GB]

Michael,

a Guy named Joe Celko elaborated about trees and hierarchies in SQL a 
few years ago. Have a look here: 
http://www.amazon.com/Hierarchies-Smarties-Edition-Kaufmann-Management/dp/0123877334/ref=dp_ob_title_bk



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

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Thanks Eduardo,

a most interesting link.

On 17 October 2014 05:41, Eduardo Morras  wrote:

> On Thu, 16 Oct 2014 09:05:51 +1100
> Michael Falconer  wrote:
>
> > Hi all,
> >
> > first off I must start with an apology. I know I'm sort of doing the
> > wrong thing here as this question is NOT related to sqlite. It is a
> > general SQL question but I ask it here because I have great respect
> > for the answers and discussions I have seen on this forum over many
> > years. I rarely post myself as there are always several contributors
> > who beat me to the answer and often their response is far better than
> > mine would have been. I'm not a code leper, I don't need the actual
> > SQL just the method really, though a short code example would be well
> > received for illustration.
> >
> > Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
> > completely off the grid? I do use sqlite quite a bit, but not on this
> > particular project.
>
> I point you to sqlite closure extension. It may shows you some ideas for
> tree implementation and parent/child relations under sql/sqlite.
>
> http://www.sqlite.org/src/artifact/636024302cde41b2bf0c542f81c40c624cfb7012
> http://www.sqlite.org/src/finfo?name=ext/misc/closure.c
>
> HTH
>
> >
> > --
> > Regards,
> >  Michael.j.Falconer.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ---   ---
> Eduardo Morras 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search query alternatives.

[Eduardo Morras]

On Thu, 16 Oct 2014 09:05:51 +1100
Michael Falconer  wrote:

> Hi all,
> 
> first off I must start with an apology. I know I'm sort of doing the
> wrong thing here as this question is NOT related to sqlite. It is a
> general SQL question but I ask it here because I have great respect
> for the answers and discussions I have seen on this forum over many
> years. I rarely post myself as there are always several contributors
> who beat me to the answer and often their response is far better than
> mine would have been. I'm not a code leper, I don't need the actual
> SQL just the method really, though a short code example would be well
> received for illustration.
>
> Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
> completely off the grid? I do use sqlite quite a bit, but not on this
> particular project.

I point you to sqlite closure extension. It may shows you some ideas for tree 
implementation and parent/child relations under sql/sqlite.

http://www.sqlite.org/src/artifact/636024302cde41b2bf0c542f81c40c624cfb7012
http://www.sqlite.org/src/finfo?name=ext/misc/closure.c

HTH
 
> 
> -- 
> Regards,
>  Michael.j.Falconer.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Thanks Stephen,

good point, I was just after some general results, I do take your point
about caching etc. However it is logical to apply UNION ALL if appropriate
in preference to UNION which in this context is a bit lazy. I'm a bit
annoyed I didn't pick up on it myself, but thankful to Igor for reminding
me of the difference in the two statements.


On 16 October 2014 11:14, Stephen Chrzanowski  wrote:

> Careful with the timing.  You may be looking at OS memory caching the
> result set instead of pulling from the drive.  For best bets, either re-run
> both queries several times, ditch the longest and shortest times, then take
> the mean or average times and do the comparison that way.
>
> On Wed, Oct 15, 2014 at 7:54 PM, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
>
> > Igor,
> >
> > Nice one. A quick test using 10 lookup trees and the same search
> criteria:
> > *Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*
> >
> > Now with UNION ALL replacing UNION:
> > *Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*
> >
> > You weren't kidding about cheaper were you? LOL
> >
> >
> > On 16 October 2014 10:30, Michael Falconer  >
> > wrote:
> >
> > > Yes, I agree with the general sentiment. It is not exactly the design I
> > > would have chosen either, but it is what exists. Design change is
> > occurring
> > > but for the moment we are stuck with the current schema.
> > >
> > > If you cannot change the schemata to be more suitable, then your
> > >> demonstrated queries are very near as good as it gets. I can think of
> > other
> > >> ways to achieve the same, but nothing that would be more efficient to
> a
> > >> tangible level. Maybe someone else here can come up with something a
> bit
> > >> more tight.
> > >>
> > > You are preaching to the converted I'm afraid, but I appreciate the
> > points
> > > you made. The quoted paragraph probably answers most of my questions
> > > really, except for this from Igor:
> > >
> > > Unless you do expect duplicates and need to eliminate them, use UNION
> ALL
> > >> - it's much cheaper (this is assuming you insist on keeping multiple
> > >> tables).
> > >>
> > > Cheaper because it drops the operation to remove the duplicate records
> > > from the result set? I'm assuming. I'll give that a try.
> > >
> > > On 16 October 2014 10:05, RSmith  wrote:
> > >
> > >>
> > >> On 2014/10/16 00:05, Michael Falconer wrote:
> > >>
> > >>> Hi all,
> > >>>
> > >>> first off I must start with an apology. I know I'm sort of doing the
> > >>> wrong//...
> > >>>
> > >>
> > >> No need to apologise, this flies quite close to the central theme.
> > >> Whether you are using SQLite or any other SQL RDBMS, this is horrible
> DB
> > >> design and it is so for precisely the reasons you are asking the
> > question.
> > >> Tables should not be dynamic: The simple rule of thumb being - if you
> > >> cannot tell (or at least accurately predict) before-hand exactly how
> > many
> > >> tables will be in the final system, then you are doing it wrong.  Why
> > not
> > >> have one set of tables and in stead of preceding each of them with a
> > >> , simply add a column that can host the  as a simple
> > value,
> > >> which will immediately make your life very much easier and get the SQL
> > >> engine to do the work you are now trying to compensate for in a
> > lop-sided
> > >> manual kind of way.
> > >>
> > >> I can only imagine the amount of code you invested into your system to
> > >> track and deal with these dynamic sets of tables, so I know changing
> it
> > >> will seem like a rather large undertaking, but really it will mostly
> > >> involve removing loads of code to end up with a small set of simple
> > queries
> > >> that does all the work for you.
> > >>
> > >> The only reason I can imagine this sort of breakdown useful is if your
> > >> tree tables are all really really huge, like Gigabytes, and so there
> > might
> > >> be some efficiency to be gained from splitting it up, but I doubt this
> > is
> > >> the case.
> > >>
> > >> Just imagine the ease of those search queries you demonstrated... one
> > >> query, no UNIONs, a single set of joins and an extra where-clause
> > check...
> > >> Add to that increased efficiency at finding results (run one query in
> > stead
> > >> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one
> > set
> > >> of tables as per your list, add column(s) for prefixing as needed,
> > import
> > >> all the tables from some large set of them (specifying additionally
> the
> > >> column for the prefix) and ten run search queries on them. I am
> willing
> > to
> > >> bet on the fact it will be much quicker in addition to being much
> > simpler.
> > >>
> > >> If you cannot change the schemata to be more suitable, then your
> > >> demonstrated queries are very near as good as it gets. I can think of
> > other
> > >> ways to achieve the same, but nothing that would be more efficient to
> a
> > >> tangible level. Maybe someone else here can c

Re: [sqlite] Search query alternatives.

[Stephen Chrzanowski]

Careful with the timing.  You may be looking at OS memory caching the
result set instead of pulling from the drive.  For best bets, either re-run
both queries several times, ditch the longest and shortest times, then take
the mean or average times and do the comparison that way.

On Wed, Oct 15, 2014 at 7:54 PM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> Igor,
>
> Nice one. A quick test using 10 lookup trees and the same search criteria:
> *Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*
>
> Now with UNION ALL replacing UNION:
> *Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*
>
> You weren't kidding about cheaper were you? LOL
>
>
> On 16 October 2014 10:30, Michael Falconer 
> wrote:
>
> > Yes, I agree with the general sentiment. It is not exactly the design I
> > would have chosen either, but it is what exists. Design change is
> occurring
> > but for the moment we are stuck with the current schema.
> >
> > If you cannot change the schemata to be more suitable, then your
> >> demonstrated queries are very near as good as it gets. I can think of
> other
> >> ways to achieve the same, but nothing that would be more efficient to a
> >> tangible level. Maybe someone else here can come up with something a bit
> >> more tight.
> >>
> > You are preaching to the converted I'm afraid, but I appreciate the
> points
> > you made. The quoted paragraph probably answers most of my questions
> > really, except for this from Igor:
> >
> > Unless you do expect duplicates and need to eliminate them, use UNION ALL
> >> - it's much cheaper (this is assuming you insist on keeping multiple
> >> tables).
> >>
> > Cheaper because it drops the operation to remove the duplicate records
> > from the result set? I'm assuming. I'll give that a try.
> >
> > On 16 October 2014 10:05, RSmith  wrote:
> >
> >>
> >> On 2014/10/16 00:05, Michael Falconer wrote:
> >>
> >>> Hi all,
> >>>
> >>> first off I must start with an apology. I know I'm sort of doing the
> >>> wrong//...
> >>>
> >>
> >> No need to apologise, this flies quite close to the central theme.
> >> Whether you are using SQLite or any other SQL RDBMS, this is horrible DB
> >> design and it is so for precisely the reasons you are asking the
> question.
> >> Tables should not be dynamic: The simple rule of thumb being - if you
> >> cannot tell (or at least accurately predict) before-hand exactly how
> many
> >> tables will be in the final system, then you are doing it wrong.  Why
> not
> >> have one set of tables and in stead of preceding each of them with a
> >> , simply add a column that can host the  as a simple
> value,
> >> which will immediately make your life very much easier and get the SQL
> >> engine to do the work you are now trying to compensate for in a
> lop-sided
> >> manual kind of way.
> >>
> >> I can only imagine the amount of code you invested into your system to
> >> track and deal with these dynamic sets of tables, so I know changing it
> >> will seem like a rather large undertaking, but really it will mostly
> >> involve removing loads of code to end up with a small set of simple
> queries
> >> that does all the work for you.
> >>
> >> The only reason I can imagine this sort of breakdown useful is if your
> >> tree tables are all really really huge, like Gigabytes, and so there
> might
> >> be some efficiency to be gained from splitting it up, but I doubt this
> is
> >> the case.
> >>
> >> Just imagine the ease of those search queries you demonstrated... one
> >> query, no UNIONs, a single set of joins and an extra where-clause
> check...
> >> Add to that increased efficiency at finding results (run one query in
> stead
> >> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one
> set
> >> of tables as per your list, add column(s) for prefixing as needed,
> import
> >> all the tables from some large set of them (specifying additionally the
> >> column for the prefix) and ten run search queries on them. I am willing
> to
> >> bet on the fact it will be much quicker in addition to being much
> simpler.
> >>
> >> If you cannot change the schemata to be more suitable, then your
> >> demonstrated queries are very near as good as it gets. I can think of
> other
> >> ways to achieve the same, but nothing that would be more efficient to a
> >> tangible level. Maybe someone else here can come up with something a bit
> >> more tight.
> >>
> >>
> >>  thing here as this question is NOT related to sqlite. It is a general
> SQL
> >>> question but I ask it here because I have great respect for the answers
> >>> and
> >>> discussions I have seen on this forum over many years. I rarely post
> >>> myself
> >>> as there are always several contributors who beat me to the answer and
> >>> often their response is far better than mine would have been. I'm not a
> >>> code leper, I don't need the actual SQL just the method really, though
> a
> >>> short code example would be well received for illustration.
> >>>
> >>> It's about a sea

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Igor,

Nice one. A quick test using 10 lookup trees and the same search criteria:
*Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*

Now with UNION ALL replacing UNION:
*Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*

You weren't kidding about cheaper were you? LOL


On 16 October 2014 10:30, Michael Falconer 
wrote:

> Yes, I agree with the general sentiment. It is not exactly the design I
> would have chosen either, but it is what exists. Design change is occurring
> but for the moment we are stuck with the current schema.
>
> If you cannot change the schemata to be more suitable, then your
>> demonstrated queries are very near as good as it gets. I can think of other
>> ways to achieve the same, but nothing that would be more efficient to a
>> tangible level. Maybe someone else here can come up with something a bit
>> more tight.
>>
> You are preaching to the converted I'm afraid, but I appreciate the points
> you made. The quoted paragraph probably answers most of my questions
> really, except for this from Igor:
>
> Unless you do expect duplicates and need to eliminate them, use UNION ALL
>> - it's much cheaper (this is assuming you insist on keeping multiple
>> tables).
>>
> Cheaper because it drops the operation to remove the duplicate records
> from the result set? I'm assuming. I'll give that a try.
>
> On 16 October 2014 10:05, RSmith  wrote:
>
>>
>> On 2014/10/16 00:05, Michael Falconer wrote:
>>
>>> Hi all,
>>>
>>> first off I must start with an apology. I know I'm sort of doing the
>>> wrong//...
>>>
>>
>> No need to apologise, this flies quite close to the central theme.
>> Whether you are using SQLite or any other SQL RDBMS, this is horrible DB
>> design and it is so for precisely the reasons you are asking the question.
>> Tables should not be dynamic: The simple rule of thumb being - if you
>> cannot tell (or at least accurately predict) before-hand exactly how many
>> tables will be in the final system, then you are doing it wrong.  Why not
>> have one set of tables and in stead of preceding each of them with a
>> , simply add a column that can host the  as a simple value,
>> which will immediately make your life very much easier and get the SQL
>> engine to do the work you are now trying to compensate for in a lop-sided
>> manual kind of way.
>>
>> I can only imagine the amount of code you invested into your system to
>> track and deal with these dynamic sets of tables, so I know changing it
>> will seem like a rather large undertaking, but really it will mostly
>> involve removing loads of code to end up with a small set of simple queries
>> that does all the work for you.
>>
>> The only reason I can imagine this sort of breakdown useful is if your
>> tree tables are all really really huge, like Gigabytes, and so there might
>> be some efficiency to be gained from splitting it up, but I doubt this is
>> the case.
>>
>> Just imagine the ease of those search queries you demonstrated... one
>> query, no UNIONs, a single set of joins and an extra where-clause check...
>> Add to that increased efficiency at finding results (run one query in stead
>> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one set
>> of tables as per your list, add column(s) for prefixing as needed, import
>> all the tables from some large set of them (specifying additionally the
>> column for the prefix) and ten run search queries on them. I am willing to
>> bet on the fact it will be much quicker in addition to being much simpler.
>>
>> If you cannot change the schemata to be more suitable, then your
>> demonstrated queries are very near as good as it gets. I can think of other
>> ways to achieve the same, but nothing that would be more efficient to a
>> tangible level. Maybe someone else here can come up with something a bit
>> more tight.
>>
>>
>>  thing here as this question is NOT related to sqlite. It is a general SQL
>>> question but I ask it here because I have great respect for the answers
>>> and
>>> discussions I have seen on this forum over many years. I rarely post
>>> myself
>>> as there are always several contributors who beat me to the answer and
>>> often their response is far better than mine would have been. I'm not a
>>> code leper, I don't need the actual SQL just the method really, though a
>>> short code example would be well received for illustration.
>>>
>>> It's about a search performed on multiple tables. However the structure
>>> of
>>> this database is somewhat unorthodox. It contains genealogical data and
>>> this is clustered into trees. Each tree has 8 tables, there can be as
>>> many
>>> as 100 trees but most installations of the associated application
>>> software
>>> contain between 5 - 50 trees. These 8 tables contain a family trees data
>>> and are named:
>>> addresses
>>> connections
>>> events
>>> family
>>> person
>>> repositories
>>> sources
>>> texts
>>>
>>> The  changes and is unique for each tree.
>>>
>>> There are extensive search options o

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Yes, I agree with the general sentiment. It is not exactly the design I
would have chosen either, but it is what exists. Design change is occurring
but for the moment we are stuck with the current schema.

If you cannot change the schemata to be more suitable, then your
> demonstrated queries are very near as good as it gets. I can think of other
> ways to achieve the same, but nothing that would be more efficient to a
> tangible level. Maybe someone else here can come up with something a bit
> more tight.
>
You are preaching to the converted I'm afraid, but I appreciate the points
you made. The quoted paragraph probably answers most of my questions
really, except for this from Igor:

Unless you do expect duplicates and need to eliminate them, use UNION ALL -
> it's much cheaper (this is assuming you insist on keeping multiple tables).
>
Cheaper because it drops the operation to remove the duplicate records from
the result set? I'm assuming. I'll give that a try.

On 16 October 2014 10:05, RSmith  wrote:

>
> On 2014/10/16 00:05, Michael Falconer wrote:
>
>> Hi all,
>>
>> first off I must start with an apology. I know I'm sort of doing the
>> wrong//...
>>
>
> No need to apologise, this flies quite close to the central theme. Whether
> you are using SQLite or any other SQL RDBMS, this is horrible DB design and
> it is so for precisely the reasons you are asking the question.  Tables
> should not be dynamic: The simple rule of thumb being - if you cannot tell
> (or at least accurately predict) before-hand exactly how many tables will
> be in the final system, then you are doing it wrong.  Why not have one set
> of tables and in stead of preceding each of them with a , simply
> add a column that can host the  as a simple value, which will
> immediately make your life very much easier and get the SQL engine to do
> the work you are now trying to compensate for in a lop-sided manual kind of
> way.
>
> I can only imagine the amount of code you invested into your system to
> track and deal with these dynamic sets of tables, so I know changing it
> will seem like a rather large undertaking, but really it will mostly
> involve removing loads of code to end up with a small set of simple queries
> that does all the work for you.
>
> The only reason I can imagine this sort of breakdown useful is if your
> tree tables are all really really huge, like Gigabytes, and so there might
> be some efficiency to be gained from splitting it up, but I doubt this is
> the case.
>
> Just imagine the ease of those search queries you demonstrated... one
> query, no UNIONs, a single set of joins and an extra where-clause check...
> Add to that increased efficiency at finding results (run one query in stead
> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one set
> of tables as per your list, add column(s) for prefixing as needed, import
> all the tables from some large set of them (specifying additionally the
> column for the prefix) and ten run search queries on them. I am willing to
> bet on the fact it will be much quicker in addition to being much simpler.
>
> If you cannot change the schemata to be more suitable, then your
> demonstrated queries are very near as good as it gets. I can think of other
> ways to achieve the same, but nothing that would be more efficient to a
> tangible level. Maybe someone else here can come up with something a bit
> more tight.
>
>
>  thing here as this question is NOT related to sqlite. It is a general SQL
>> question but I ask it here because I have great respect for the answers
>> and
>> discussions I have seen on this forum over many years. I rarely post
>> myself
>> as there are always several contributors who beat me to the answer and
>> often their response is far better than mine would have been. I'm not a
>> code leper, I don't need the actual SQL just the method really, though a
>> short code example would be well received for illustration.
>>
>> It's about a search performed on multiple tables. However the structure of
>> this database is somewhat unorthodox. It contains genealogical data and
>> this is clustered into trees. Each tree has 8 tables, there can be as many
>> as 100 trees but most installations of the associated application software
>> contain between 5 - 50 trees. These 8 tables contain a family trees data
>> and are named:
>> addresses
>> connections
>> events
>> family
>> person
>> repositories
>> sources
>> texts
>>
>> The  changes and is unique for each tree.
>>
>> There are extensive search options offered to users as well as simple name
>> searching and it is one of the best features of the app. It works pretty
>> well, so it ain't broke in any sense, we just wonder if there is a better
>> way to perform this search in SQL. Is there a general technique which is
>> superior either in speed, efficiency or load bearing contexts?
>>
>> I am sure you can see one of the pitfalls here is the exponential growth
>> of
>> such a search query as both total n

Re: [sqlite] Search query alternatives.

[RSmith]

On 2014/10/16 00:05, Michael Falconer wrote:

Hi all,

first off I must start with an apology. I know I'm sort of doing the wrong//...


No need to apologise, this flies quite close to the central theme. Whether you are using SQLite or any other SQL RDBMS, this is 
horrible DB design and it is so for precisely the reasons you are asking the question.  Tables should not be dynamic: The simple 
rule of thumb being - if you cannot tell (or at least accurately predict) before-hand exactly how many tables will be in the final 
system, then you are doing it wrong.  Why not have one set of tables and in stead of preceding each of them with a , simply 
add a column that can host the  as a simple value, which will immediately make your life very much easier and get the SQL 
engine to do the work you are now trying to compensate for in a lop-sided manual kind of way.


I can only imagine the amount of code you invested into your system to track and deal with these dynamic sets of tables, so I know 
changing it will seem like a rather large undertaking, but really it will mostly involve removing loads of code to end up with a 
small set of simple queries that does all the work for you.


The only reason I can imagine this sort of breakdown useful is if your tree tables are all really really huge, like Gigabytes, and 
so there might be some efficiency to be gained from splitting it up, but I doubt this is the case.


Just imagine the ease of those search queries you demonstrated... one query, no UNIONs, a single set of joins and an extra 
where-clause check... Add to that increased efficiency at finding results (run one query in stead of 50).  Easy-mode. As a 
proof-of-concept, just make an SQLite DB, one set of tables as per your list, add column(s) for prefixing as needed, import all the 
tables from some large set of them (specifying additionally the column for the prefix) and ten run search queries on them. I am 
willing to bet on the fact it will be much quicker in addition to being much simpler.


If you cannot change the schemata to be more suitable, then your demonstrated queries are very near as good as it gets. I can think 
of other ways to achieve the same, but nothing that would be more efficient to a tangible level. Maybe someone else here can come up 
with something a bit more tight.



thing here as this question is NOT related to sqlite. It is a general SQL
question but I ask it here because I have great respect for the answers and
discussions I have seen on this forum over many years. I rarely post myself
as there are always several contributors who beat me to the answer and
often their response is far better than mine would have been. I'm not a
code leper, I don't need the actual SQL just the method really, though a
short code example would be well received for illustration.

It's about a search performed on multiple tables. However the structure of
this database is somewhat unorthodox. It contains genealogical data and
this is clustered into trees. Each tree has 8 tables, there can be as many
as 100 trees but most installations of the associated application software
contain between 5 - 50 trees. These 8 tables contain a family trees data
and are named:
addresses
connections
events
family
person
repositories
sources
texts

The  changes and is unique for each tree.

There are extensive search options offered to users as well as simple name
searching and it is one of the best features of the app. It works pretty
well, so it ain't broke in any sense, we just wonder if there is a better
way to perform this search in SQL. Is there a general technique which is
superior either in speed, efficiency or load bearing contexts?

I am sure you can see one of the pitfalls here is the exponential growth of
such a search query as both total number of trees and indeed user search
criteria increase. For each criteria component, and there are quite a few,
the appropriate tables must be queried and results joined.

Searches return records of individuals meeting the entered search criteria
so the query focuses on the person table as it's anchor, performs searches
on required other tables in the tree and joins the results to the person
data. The results from each tree search are then UNION'ed to provide the
result set. Here is a contrived example of the SQL query code which should
make things clearer.

The user enters simple search criteria - any person with a last name
containing 'mac' and a first name containing the character 'a'. This is the
resulting query (generated by php code), which searches a small 4 family
tree installation.

(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
 FROM humo1_person
 LEFT JOIN humo1_events ON pers_gedcomnumber=event_person_id
 LEFT JOIN humo1_addresses ON pers_gedcomnumber=address_person_id
 WHERE pers_lastname LIKE '%mac%'
   AND (pers_firstname LIKE '%a%'
   OR (event_kind='name' AND event_event LIKE '%a%')

Re: [sqlite] Search query alternatives.

[Igor Tandetnik]

On 10/15/2014 6:05 PM, Michael Falconer wrote:

addresses
connections
events
family
person
repositories
sources
texts


Personally, I'd have one set of tables, each with an extra column 
containing . Why do you need a separate set of tables, only to 
UNION them on every request? Instead of sharding by table, shard by row, 
using "prefix" column as a discriminator.



(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
UNION
 (SELECT humo2_person.*, event_kind, event_event, address_place,


Unless you do expect duplicates and need to eliminate them, use UNION 
ALL - it's much cheaper (this is assuming you insist on keeping multiple 
tables).

--
Igor Tandetnik

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

[sqlite] Search query alternatives.

[Michael Falconer]

Hi all,

first off I must start with an apology. I know I'm sort of doing the wrong
thing here as this question is NOT related to sqlite. It is a general SQL
question but I ask it here because I have great respect for the answers and
discussions I have seen on this forum over many years. I rarely post myself
as there are always several contributors who beat me to the answer and
often their response is far better than mine would have been. I'm not a
code leper, I don't need the actual SQL just the method really, though a
short code example would be well received for illustration.

It's about a search performed on multiple tables. However the structure of
this database is somewhat unorthodox. It contains genealogical data and
this is clustered into trees. Each tree has 8 tables, there can be as many
as 100 trees but most installations of the associated application software
contain between 5 - 50 trees. These 8 tables contain a family trees data
and are named:
addresses
connections
events
family
person
repositories
sources
texts

The  changes and is unique for each tree.

There are extensive search options offered to users as well as simple name
searching and it is one of the best features of the app. It works pretty
well, so it ain't broke in any sense, we just wonder if there is a better
way to perform this search in SQL. Is there a general technique which is
superior either in speed, efficiency or load bearing contexts?

I am sure you can see one of the pitfalls here is the exponential growth of
such a search query as both total number of trees and indeed user search
criteria increase. For each criteria component, and there are quite a few,
the appropriate tables must be queried and results joined.

Searches return records of individuals meeting the entered search criteria
so the query focuses on the person table as it's anchor, performs searches
on required other tables in the tree and joins the results to the person
data. The results from each tree search are then UNION'ed to provide the
result set. Here is a contrived example of the SQL query code which should
make things clearer.

The user enters simple search criteria - any person with a last name
containing 'mac' and a first name containing the character 'a'. This is the
resulting query (generated by php code), which searches a small 4 family
tree installation.

(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
FROM humo1_person
LEFT JOIN humo1_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo1_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo2_person.*, event_kind, event_event, address_place,
address_zip
FROM humo2_person LEFT JOIN humo2_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo2_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%') )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo3_person.*, event_kind, event_event, address_place,
address_zip
FROM humo3_person LEFT JOIN humo3_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo3_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo4_person.*, event_kind, event_event, address_place,
address_zip
FROM humo4_person
LEFT JOIN humo4_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo4_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
ORDER BY pers_lastname ASC , pers_firstname ASC LIMIT 0,30

Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
completely off the grid? I do use sqlite quite a bit, but not on this
particular project.


-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search string in the db question

[Yongil Jang]

Select * from mytable where c5 like '9806067880' OR c6 like '9806067880' OR
c7 like '9806067880';

AFAIK, like function should called for each column.

Regards,
Yongil Jang.


2013/2/8 YAN HONG YE 

> my table has a table like this:
> c5  c6  c7
> 9806062380  9806062380  9806062380
> 9806062480  9806062680  9806062680
> 9806063280  9806068980  9806068980
> 9806062980  9806067980  9806067980
> 9806062280  9806067880  9806067880
> 9806734080  9806068280  9806068380
> 9806069180  9806068380  9806068680
> 9806063180  9806068680  9806635280
>
> I wanna to find in the column c5,c6,c7 include 9806067880 like this:
>
> select * from mytable where * like '9806067880';
>
> it's right?
> ___
> 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] search string in the db question

[Igor Tandetnik]

On 2/8/2013 12:08 AM, YAN HONG YE wrote:

my table has a table like this:
c5  c6  c7
9806062380  9806062380  9806062380
9806062480  9806062680  9806062680
9806063280  9806068980  9806068980
9806062980  9806067980  9806067980
9806062280  9806067880  9806067880
9806734080  9806068280  9806068380
9806069180  9806068380  9806068680
9806063180  9806068680  9806635280

I wanna to find in the column c5,c6,c7 include 9806067880 like this:

select * from mytable where * like '9806067880';

it's right?


No it's not. You can't use star like this. You have to spell out your 
condition in full:


select * from mytable where c5='9806067880' or c6='9806067880' or 
c7='9806067880';


Or a bit more compactly:

select * from mytable where '9806067880' in (c5, c6, c7);

--
Igor Tandetnik

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

[sqlite] search string in the db question

[YAN HONG YE]

my table has a table like this:
c5  c6  c7
9806062380  9806062380  9806062380
9806062480  9806062680  9806062680
9806063280  9806068980  9806068980
9806062980  9806067980  9806067980
9806062280  9806067880  9806067880
9806734080  9806068280  9806068380
9806069180  9806068380  9806068680
9806063180  9806068680  9806635280

I wanna to find in the column c5,c6,c7 include 9806067880 like this:

select * from mytable where * like '9806067880';

it's right?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search

[Alek Paunov]

BTW, in my eyes, sqlite has the full potential to become most used DB 
engine for hierarchical data too (besides relational), once someone 
clever hacker manage to port something like Pathfinder [1,2,3] (which 
is, let say, optimizing XQuery/SQL compiler) to generate VDBE bytecode 
(like the build-in frontend for SQL)


[1] http://dev.monetdb.org/hg/MonetDB/file/f7d6c302cc9c/pathfinder
[2] http://hackage.haskell.org/package/Pathfinder
[3] 
http://www.lug-erding.de/vortrag/Purely%20Relational%20XQuery%20LUG%20Erding.pdf


On 30.12.2011 16:35, Aris Setyawan wrote:

Hi Durga,

Another alternative, you can use an xml database. It will fix your
problem easily using xquery, like this:
 doc('region')//country/title/text() ->  it will show all region you have
 doc('region')//village/title/text() ->  it will show all village you have

You also can use selection too (where condition).
http://en.wikibooks.org/wiki/XQuery/XPath_examples
http://sedna.org

SQLite with fts is my favorite, but for tree like data structure I
will use xml database.

-aris

On 12/28/11, Durga D  wrote:

Dear Michael.Black.

 It's correct.

I need to design database to store file paths and their info like
size. I have an idea
   item(file or folder), level0(imm. parent), level1(grand parent)
to level160(ancestor), type(file type or folder type).

  primary key: (item, level0 to level160)

  Is it correct approach? This is from server side. Need to store
millions of records.

 Need optimum relationship between folders and files uniquely.

 for ex: c:/mydocs/home/a.doc
 c:/mydocs/office/agreement.doc

   insertion of filepaths,deltion of file paths are enough. should be able
to search by folder wise also.

any ideas?

Thanks in advance.

On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS)
wrote:



I don't know if FTS or a normal table will matter here but just normalize
the whole thing.



CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);

Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch
faster.



INSERT INTO virfts4 VALUES(1,'CO','country1');

INSERT INTO virfts4 VALUES(1,'ST','state1');

INSERT INTO virfts4 VALUES(1,'CI','city1');

INSERT INTO virfts4 VALUES(1,'VI','village1');

SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';



You can store as many levels as you want.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Durga D [durga.d...@gmail.com]
Sent: Tuesday, December 27, 2011 4:27 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] search

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?

in case, if I have 250 levels like this ex: file system. how to do this.
any idea?

Thanks in advance.

On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:


2011/12/27 Durga D:

select * from virfts4 where residence match '/*'; -- dint work
how to get counties names from this db by using query?


Normalize database to 1NF, e.g.
CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
arrivtime, duration, imagelocation);
INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
0730, 1500,'C');

then use select:
SELECT DISTINCT country FROM virfts4;
--
Kit
___
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


___
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] search

[Aris Setyawan]

Durga,

> I think, it may not support for search with wild chars. like vil*
> Can I get wild char search in leaf node or parent node with XML?

About wildchar, sedna will support it, but I don't think so about
prefixed wildchar. You can join the forum and ask it there. I'm sorry,
I'm also new in this xml dbms, and just want to share the idea behind
that.

Another alternative you can use postgresql ltree, and I think it will
support your all requirement. You can read the documentation here
http://www.postgresql.org/docs/9.1/static/ltree.html (full of features
and operators) and this is the live project example
http://www.dmoz.org/

regards,
-aris

On 1/2/12, Durga D  wrote:
> Dear Aris,
>
>  Wish you a happy new year.
>
>  I agree.
>
> I think, it may not support for search with wild chars. like vil*
>
> Can I get wild char search in leaf node or parent node with XML?
>
> Thanks,
> Durga.
>
> On Fri, Dec 30, 2011 at 8:05 PM, Aris Setyawan  wrote:
>
>> Hi Durga,
>>
>> Another alternative, you can use an xml database. It will fix your
>> problem easily using xquery, like this:
>>doc('region')//country/title/text() -> it will show all region you have
>>doc('region')//village/title/text() -> it will show all village you
>> have
>>
>> You also can use selection too (where condition).
>> http://en.wikibooks.org/wiki/XQuery/XPath_examples
>> http://sedna.org
>>
>> SQLite with fts is my favorite, but for tree like data structure I
>> will use xml database.
>>
>> -aris
>>
>> On 12/28/11, Durga D  wrote:
>> > Dear Michael.Black.
>> >
>> > It's correct.
>> >
>> >I need to design database to store file paths and their info like
>> > size. I have an idea
>> >   item(file or folder), level0(imm. parent), level1(grand
>> > parent)
>> > to level160(ancestor), type(file type or folder type).
>> >
>> >  primary key: (item, level0 to level160)
>> >
>> >  Is it correct approach? This is from server side. Need to store
>> > millions of records.
>> >
>> > Need optimum relationship between folders and files uniquely.
>> >
>> > for ex: c:/mydocs/home/a.doc
>> > c:/mydocs/office/agreement.doc
>> >
>> >   insertion of filepaths,deltion of file paths are enough. should be
>> > able
>> > to search by folder wise also.
>> >
>> >any ideas?
>> >
>> > Thanks in advance.
>> >
>> > On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS) <
>> michael.bla...@ngc.com
>> >> wrote:
>> >
>> >> I don't know if FTS or a normal table will matter here but just
>> normalize
>> >> the whole thing.
>> >>
>> >>
>> >>
>> >> CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
>> >>
>> >> Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a
>> >> touch
>> >> faster.
>> >>
>> >>
>> >>
>> >> INSERT INTO virfts4 VALUES(1,'CO','country1');
>> >>
>> >> INSERT INTO virfts4 VALUES(1,'ST','state1');
>> >>
>> >> INSERT INTO virfts4 VALUES(1,'CI','city1');
>> >>
>> >> INSERT INTO virfts4 VALUES(1,'VI','village1');
>> >>
>> >> SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
>> >>
>> >>
>> >>
>> >> You can store as many levels as you want.
>> >>
>> >>
>> >>
>> >> Michael D. Black
>> >>
>> >> Senior Scientist
>> >>
>> >> Advanced Analytics Directorate
>> >>
>> >> Advanced GEOINT Solutions Operating Unit
>> >>
>> >> Northrop Grumman Information Systems
>> >>
>> >> 
>> >> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> >> on behalf of Durga D [durga.d...@gmail.com]
>> >> Sent: Tuesday, December 27, 2011 4:27 AM
>> >> To: General Discussion of SQLite Database
>> >> Subject: EXT :Re: [sqlite] search
>> >>
>> >> Thank you. I agree. It's correct.
>> >>
>> >> I already have data base with /country/state/city/village format. Is it
>&g

Re: [sqlite] search

[Durga D]

Dear Aris,

 Wish you a happy new year.

 I agree.

I think, it may not support for search with wild chars. like vil*

Can I get wild char search in leaf node or parent node with XML?

Thanks,
Durga.

On Fri, Dec 30, 2011 at 8:05 PM, Aris Setyawan  wrote:

> Hi Durga,
>
> Another alternative, you can use an xml database. It will fix your
> problem easily using xquery, like this:
>doc('region')//country/title/text() -> it will show all region you have
>doc('region')//village/title/text() -> it will show all village you have
>
> You also can use selection too (where condition).
> http://en.wikibooks.org/wiki/XQuery/XPath_examples
> http://sedna.org
>
> SQLite with fts is my favorite, but for tree like data structure I
> will use xml database.
>
> -aris
>
> On 12/28/11, Durga D  wrote:
> > Dear Michael.Black.
> >
> > It's correct.
> >
> >I need to design database to store file paths and their info like
> > size. I have an idea
> >   item(file or folder), level0(imm. parent), level1(grand parent)
> > to level160(ancestor), type(file type or folder type).
> >
> >  primary key: (item, level0 to level160)
> >
> >  Is it correct approach? This is from server side. Need to store
> > millions of records.
> >
> > Need optimum relationship between folders and files uniquely.
> >
> > for ex: c:/mydocs/home/a.doc
> > c:/mydocs/office/agreement.doc
> >
> >   insertion of filepaths,deltion of file paths are enough. should be able
> > to search by folder wise also.
> >
> >any ideas?
> >
> > Thanks in advance.
> >
> > On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS) <
> michael.bla...@ngc.com
> >> wrote:
> >
> >> I don't know if FTS or a normal table will matter here but just
> normalize
> >> the whole thing.
> >>
> >>
> >>
> >> CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
> >>
> >> Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch
> >> faster.
> >>
> >>
> >>
> >> INSERT INTO virfts4 VALUES(1,'CO','country1');
> >>
> >> INSERT INTO virfts4 VALUES(1,'ST','state1');
> >>
> >> INSERT INTO virfts4 VALUES(1,'CI','city1');
> >>
> >> INSERT INTO virfts4 VALUES(1,'VI','village1');
> >>
> >> SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
> >>
> >>
> >>
> >> You can store as many levels as you want.
> >>
> >>
> >>
> >> Michael D. Black
> >>
> >> Senior Scientist
> >>
> >> Advanced Analytics Directorate
> >>
> >> Advanced GEOINT Solutions Operating Unit
> >>
> >> Northrop Grumman Information Systems
> >>
> >> 
> >> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> >> on behalf of Durga D [durga.d...@gmail.com]
> >> Sent: Tuesday, December 27, 2011 4:27 AM
> >> To: General Discussion of SQLite Database
> >> Subject: EXT :Re: [sqlite] search
> >>
> >> Thank you. I agree. It's correct.
> >>
> >> I already have data base with /country/state/city/village format. Is it
> >> possible to do that while virtual table creation time?
> >>
> >> if yes, how?
> >>
> >> in case, if I have 250 levels like this ex: file system. how to do this.
> >> any idea?
> >>
> >> Thanks in advance.
> >>
> >> On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:
> >>
> >> > 2011/12/27 Durga D :
> >> > > select * from virfts4 where residence match '/*'; -- dint work
> >> > > how to get counties names from this db by using query?
> >> >
> >> > Normalize database to 1NF, e.g.
> >> > CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> >> > arrivtime, duration, imagelocation);
> >> > INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> >> > 0730, 1500,'C');
> >> >
> >> > then use select:
> >> > SELECT DISTINCT country FROM virfts4;
> >> > --
> >> > Kit
> >> > ___
> >> > 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
> >>
> > ___
> > 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] search

[Aris Setyawan]

Hi Durga,

Another alternative, you can use an xml database. It will fix your
problem easily using xquery, like this:
doc('region')//country/title/text() -> it will show all region you have
doc('region')//village/title/text() -> it will show all village you have

You also can use selection too (where condition).
http://en.wikibooks.org/wiki/XQuery/XPath_examples
http://sedna.org

SQLite with fts is my favorite, but for tree like data structure I
will use xml database.

-aris

On 12/28/11, Durga D  wrote:
> Dear Michael.Black.
>
> It's correct.
>
>I need to design database to store file paths and their info like
> size. I have an idea
>   item(file or folder), level0(imm. parent), level1(grand parent)
> to level160(ancestor), type(file type or folder type).
>
>  primary key: (item, level0 to level160)
>
>  Is it correct approach? This is from server side. Need to store
> millions of records.
>
> Need optimum relationship between folders and files uniquely.
>
> for ex: c:/mydocs/home/a.doc
> c:/mydocs/office/agreement.doc
>
>   insertion of filepaths,deltion of file paths are enough. should be able
> to search by folder wise also.
>
>any ideas?
>
> Thanks in advance.
>
> On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS) > wrote:
>
>> I don't know if FTS or a normal table will matter here but just normalize
>> the whole thing.
>>
>>
>>
>> CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
>>
>> Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch
>> faster.
>>
>>
>>
>> INSERT INTO virfts4 VALUES(1,'CO','country1');
>>
>> INSERT INTO virfts4 VALUES(1,'ST','state1');
>>
>> INSERT INTO virfts4 VALUES(1,'CI','city1');
>>
>> INSERT INTO virfts4 VALUES(1,'VI','village1');
>>
>> SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
>>
>>
>>
>> You can store as many levels as you want.
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> Advanced Analytics Directorate
>>
>> Advanced GEOINT Solutions Operating Unit
>>
>> Northrop Grumman Information Systems
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on behalf of Durga D [durga.d...@gmail.com]
>> Sent: Tuesday, December 27, 2011 4:27 AM
>> To: General Discussion of SQLite Database
>> Subject: EXT :Re: [sqlite] search
>>
>> Thank you. I agree. It's correct.
>>
>> I already have data base with /country/state/city/village format. Is it
>> possible to do that while virtual table creation time?
>>
>> if yes, how?
>>
>> in case, if I have 250 levels like this ex: file system. how to do this.
>> any idea?
>>
>> Thanks in advance.
>>
>> On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:
>>
>> > 2011/12/27 Durga D :
>> > > select * from virfts4 where residence match '/*'; -- dint work
>> > > how to get counties names from this db by using query?
>> >
>> > Normalize database to 1NF, e.g.
>> > CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
>> > arrivtime, duration, imagelocation);
>> > INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
>> > 0730, 1500,'C');
>> >
>> > then use select:
>> > SELECT DISTINCT country FROM virfts4;
>> > --
>> > Kit
>> > ___
>> > 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
>>
> ___
> 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] search

[Durga D]

Dear Michael.Black.

It's correct.

   I need to design database to store file paths and their info like
size. I have an idea
  item(file or folder), level0(imm. parent), level1(grand parent)
to level160(ancestor), type(file type or folder type).

 primary key: (item, level0 to level160)

 Is it correct approach? This is from server side. Need to store
millions of records.

Need optimum relationship between folders and files uniquely.

for ex: c:/mydocs/home/a.doc
c:/mydocs/office/agreement.doc

  insertion of filepaths,deltion of file paths are enough. should be able
to search by folder wise also.

   any ideas?

Thanks in advance.

On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS)  wrote:

> I don't know if FTS or a normal table will matter here but just normalize
> the whole thing.
>
>
>
> CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
>
> Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch
> faster.
>
>
>
> INSERT INTO virfts4 VALUES(1,'CO','country1');
>
> INSERT INTO virfts4 VALUES(1,'ST','state1');
>
> INSERT INTO virfts4 VALUES(1,'CI','city1');
>
> INSERT INTO virfts4 VALUES(1,'VI','village1');
>
> SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
>
>
>
> You can store as many levels as you want.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Durga D [durga.d...@gmail.com]
> Sent: Tuesday, December 27, 2011 4:27 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] search
>
> Thank you. I agree. It's correct.
>
> I already have data base with /country/state/city/village format. Is it
> possible to do that while virtual table creation time?
>
> if yes, how?
>
> in case, if I have 250 levels like this ex: file system. how to do this.
> any idea?
>
> Thanks in advance.
>
> On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:
>
> > 2011/12/27 Durga D :
> > > select * from virfts4 where residence match '/*'; -- dint work
> > > how to get counties names from this db by using query?
> >
> > Normalize database to 1NF, e.g.
> > CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> > arrivtime, duration, imagelocation);
> > INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> > 0730, 1500,'C');
> >
> > then use select:
> > SELECT DISTINCT country FROM virfts4;
> > --
> > Kit
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search

[Durga D]

Mohit,

  sqlite> .separator /
  sqlite> create virtual table virfts4 using fts4 (residence);
  sqlite> insert into  virfts4  select * from source
  sqlite> select count(*) from  virfts4  where residence match '/*'; --
result is 0.

  Please correct it. I think, i am doing something wrong.

Note: there is no spelling mistakes. all sql stmts compiled. but dint get
result. result should be greater than 0. it has 4 records.

Thanks in advance.

On Tue, Dec 27, 2011 at 7:40 PM, Mohit Sindhwani  wrote:

> On 27/12/2011 9:25 PM, Eduardo Morras wrote:
>
>> At 11:27 27/12/2011, you wrote:
>>
>>> Thank you. I agree. It's correct.
>>>
>>> I already have data base with /country/state/city/village format. Is it
>>> possible to do that while virtual table creation time?
>>>
>>> if yes, how?
>>>
>>
>> Change '/' to ','. This way you get a csv file which you can import
>> directly. Perhaps you need to add a line with the table column description.
>>
>>
> or at the SQLite3 shell do
> sqlite>separator /
> before doing a .import
>
> no?
>
> Cheers,
> Mohit.
> 27/12/2011 | 10:10 PM.
>
>
> __**_
> 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] search

[Black, Michael (IS)]

I don't know if FTS or a normal table will matter here but just normalize the 
whole thing.



CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);

Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch faster.



INSERT INTO virfts4 VALUES(1,'CO','country1');

INSERT INTO virfts4 VALUES(1,'ST','state1');

INSERT INTO virfts4 VALUES(1,'CI','city1');

INSERT INTO virfts4 VALUES(1,'VI','village1');

SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';



You can store as many levels as you want.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Durga D [durga.d...@gmail.com]
Sent: Tuesday, December 27, 2011 4:27 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] search

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?

in case, if I have 250 levels like this ex: file system. how to do this.
any idea?

Thanks in advance.

On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:

> 2011/12/27 Durga D :
> > select * from virfts4 where residence match '/*'; -- dint work
> > how to get counties names from this db by using query?
>
> Normalize database to 1NF, e.g.
> CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> arrivtime, duration, imagelocation);
> INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> 0730, 1500,'C');
>
> then use select:
> SELECT DISTINCT country FROM virfts4;
> --
> Kit
> ___
> 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] search

[Eduardo Morras]

At 15:10 27/12/2011, Mohit Sindhwani wrote:

On 27/12/2011 9:25 PM, Eduardo Morras wrote:

At 11:27 27/12/2011, you wrote:

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?


Change '/' to ','. This way you get a csv file which you can import 
directly. Perhaps you need to add a line with the table column description.


or at the SQLite3 shell do
sqlite>separator /
before doing a .import

no?


Yes, didn't remember.


Cheers,
Mohit.



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

Re: [sqlite] search

[Mohit Sindhwani]

On 27/12/2011 9:25 PM, Eduardo Morras wrote:

At 11:27 27/12/2011, you wrote:

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?


Change '/' to ','. This way you get a csv file which you can import 
directly. Perhaps you need to add a line with the table column 
description.




or at the SQLite3 shell do
sqlite>separator /
before doing a .import

no?

Cheers,
Mohit.
27/12/2011 | 10:10 PM.

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

Re: [sqlite] search

[Eduardo Morras]

At 11:27 27/12/2011, you wrote:

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?


Change '/' to ','. This way you get a csv file which you can import 
directly. Perhaps you need to add a line with the table column description.




in case, if I have 250 levels like this ex: file system. how to do this.
any idea?


You can use a shell script to do so. I leave it as exercise to you ;)


Thanks in advance.


HTH 



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

Re: [sqlite] search

[Durga D]

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?

in case, if I have 250 levels like this ex: file system. how to do this.
any idea?

Thanks in advance.

On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:

> 2011/12/27 Durga D :
> > select * from virfts4 where residence match '/*'; -- dint work
> > how to get counties names from this db by using query?
>
> Normalize database to 1NF, e.g.
> CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> arrivtime, duration, imagelocation);
> INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> 0730, 1500,'C');
>
> then use select:
> SELECT DISTINCT country FROM virfts4;
> --
> Kit
> ___
> 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] search

[Kit]

2011/12/27 Durga D :
> select * from virfts4 where residence match '/*'; -- dint work
> how to get counties names from this db by using query?

Normalize database to 1NF, e.g.
CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
arrivtime, duration, imagelocation);
INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
0730, 1500,'C');

then use select:
SELECT DISTINCT country FROM virfts4;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search

[Durga D]

Hi kit,

   Thanks for your response.

CREATE VIRTUAL TABLE virfts4 using fts4(residence, arrivtime, duration,
imagelocation);

insert into virfts4 values('/country1/state1/city1/village1', 0730, 1500,
'C');
insert into virfts4 values('/country1/state1/city1/village2', 0731, 1500,
'C');
insert into virfts4 values('/countr1/state1/village1/', 1800, 1000, 'D');

select * from virfts4 where residence match '/*'; -- dint work
how to get counties names from this db by using query?

select * from virfts4 where residence match '/c*'; -- it's worked and very
fast.

Thanks in advance.

On Tue, Dec 27, 2011 at 2:01 PM, Kit  wrote:

> 2011/12/27 Durga D :
> > HI all,
> >   I have sqlite database with more than 1000 records. Here,
> > residencearea is the primary key.
> > /country/state/city/village
> >   I tried with fts3 and fts4 virtual tables. Not much performance.
> >   like query on direct table, dead slow.
>
> Send your SQL query and table structure.
>
> Maybe you used LIKE instead of MATCH.
> --
> Kit
> ___
> 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] search

[Kit]

2011/12/27 Durga D :
> HI all,
>   I have sqlite database with more than 1000 records. Here,
> residencearea is the primary key.
> /country/state/city/village
>   I tried with fts3 and fts4 virtual tables. Not much performance.
>   like query on direct table, dead slow.

Send your SQL query and table structure.

Maybe you used LIKE instead of MATCH.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] search

[Durga D]

HI all,

   I have sqlite database with more than 1000 records. Here,
residencearea is the primary key.

   for ex:

/country/state/city/village
/country/city
/country/state/city
/country/

country: USA, UK, CHINA


   Here I want to search based on country and sometimes search based on
village and sometimes search based on /country/state/.

   I tried with fts3 and fts4 virtual tables. Not much performance.

   like query on direct table, dead slow.

   Any alternate solution for this?

Thanks in advance.

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

Re: [sqlite] Search optimization questions

[Igor Tandetnik]

Igor Tandetnik  wrote:
> But at one point, Mr. Hipp chimed in

Sorry, it's Dr. Hipp. Shame on me.
-- 
Igor Tandetnik

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

Re: [sqlite] Search optimization questions

[Igor Tandetnik]

Simon Slavin  wrote:
> 1) My index does not specifically involve ordering the entries in 'id' order. 
>  But entries in the table are always entered
> chronologically.  Am I right that SQLite implicitly adds the rowid into every 
> index to keep entries unique ?

Not so much to keep them unique, but to be able to refer back to the row this 
index entry came from (an index would be pretty useless otherwise). But yes, an 
index always implicitly has rowid as the last column.

> In other words, if I do  
> 
> SELECT id FROM changes WHERE theTable='customers' AND theRowID=123 ORDER BY 
> id DESC LIMIT 1
> 
> will SQLite figure out that it can do this directly from the index ?

I believe so, but you can confirm this with EXPLAIN QUERY PLAN.

> 2) Can SQLite optimize searches across 'OR' ?  In other words if I do
> 
> SELECT id FROM changes WHERE theTable='customers' AND (theRowID=123 OR 
> theRowID='*') ORDER BY id DESC LIMIT 1
> 
> will it use the index once for 123 and again for '*' ?

I used to advise rewriting such a query with UNION ALL, because once upon a 
time, SQLite reverted to a table scan as soon as it saw an OR clause. But at 
one point, Mr. Hipp chimed in and said this was no longer true, that recent 
versions of SQLite could perform such a transformation automatically. I don't 
remember which version is the first that does it, but the conversation took 
place at least a year ago.

Confirm with EXPLAIN QUERY PLAN how your SQLite version executes this query.

Personally, I'd write the check as theRowID IN (123, '*'). Not that it should 
make any difference, apart from making the statement a bit more compact and 
perhaps easier to read.
-- 
Igor Tandetnik

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

[sqlite] Search optimization questions

[Simon Slavin]

This is simplified from what I'm really doing and I'm having to type this from 
memory, but I hope I've preserved enough that you can  give the right answer.

I keep a log of changes made.  It looks like this:

CREATE TABLE changes (
id INTEGER PRIMARY KEY,
dateTime TEXT,
theTable TEXT,
theRowID INTEGER,
theColumn TEXT,
theCommand TEXT)

CREATE INDEX changesTR ON changes (theTable,theRowID)

In 'theRowID' I have either a specific rowid, or '*' indicating that a change 
effected more than one row.  Rows in the 'changes' table are entered as changes 
are made, in chronological order.  No tricks.

I need to be able to search for /the most recent change/ which effected either 
a specific row of a specific table.  Which means I need the last record which 
has either a specific number (123 for the sake of argument) or a '*' in.

1) My index does not specifically involve ordering the entries in 'id' order.  
But entries in the table are always entered chronologically.  Am I right that 
SQLite implicitly adds the rowid into every index to keep entries unique ?  In 
other words, if I do

SELECT id FROM changes WHERE theTable='customers' AND theRowID=123 ORDER BY id 
DESC LIMIT 1

will SQLite figure out that it can do this directly from the index ?  Or should 
I add the id column into the index ?  If I added the id column in would it make 
the index take up more space, or does SQLite know not to add the rowid if it 
has already been specifically mentioned in the index ?

2) Can SQLite optimize searches across 'OR' ?  In other words if I do

SELECT id FROM changes WHERE theTable='customers' AND (theRowID=123 OR 
theRowID='*') ORDER BY id DESC LIMIT 1

will it use the index once for 123 and again for '*' ?

Thanks for any help or advice about the above, including people telling me 
better and completely different ways to do it.  None of this is fixed right now 
and I can completely rewrite it if someone comes up with something better.

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

[sqlite] SQLite search using IN clause

[Trevor Borgmeier]

I have a database where a field's value is a comma separated list 
indicating the basic categories the item belongs to.

so if the "categories" field has a value of "1,8,15"

I want to do a query like this:

SELECT categories FROM myTable WHERE "8" IN (categories);

but it only finds records where "8" is the only category...

Is there anyway for it to evaluate the contents fo the categories field 
first rather than compare it as a whole?

The describe query works in MySQL, but the port doesn't... So far the 
hack is to do something like this...

SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%";

but I'm guessing LIKE isn't as efficient, and the query is more 
awkward.  Any advise would be appreciated.  Thanks!

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

Re: [sqlite] Search multiple columns AND extra coparator

[flakpit]

Igor Tandetnik wrote:
> 
> 
> Define "didn't work". What results did this query return, and how do these
> results differ from your expectations?
> 
>> it is not giving me any results at the moment.
> 
> Well, do you actually have any records that match all the conditions? Show
> a sample of your data; in particular, show the record that you believe
> should be returned by the query.
> -- 
> Igor Tandetnik
> 

Thanks Igor but as I indicated to Simon, I finally figured it out and pasted
the resulting query string to Simon. Now I may fall out of this chair and
die quietly.

P.s. Your help in the past has also been invaluable, thank you.
-- 
View this message in context: 
http://old.nabble.com/Search-multiple-columns-AND-extra-comparator-tp28356196p28356875.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] Search multiple columns AND extra coparator

[Igor Tandetnik]

flakpit wrote:
>> SELECT * FROM sitelist
>> WHERE
> sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
> LIKE >'%wik%'
>> AND category='Bundled Software'
>> AND owner='Gary'
>> ORDER BY sitename
> 
> Hello Simon, that's what I tried first (yes, the '7' was a typo, sorry) and
> it didn't work.

Define "didn't work". What results did this query return, and how do these 
results differ from your expectations?

> it is not giving me any results at the moment.

Well, do you actually have any records that match all the conditions? Show a 
sample of your data; in particular, show the record that you believe should be 
returned by the query.
-- 
Igor Tandetnik

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

Re: [sqlite] Search multiple columns AND extra coparator

[flakpit]

>Just add your AND and ORDER by clauses on after the '%wik%'.  But I don't
know why there's a '7' at >the end of your original line.  I don't think it
does anything.  Perhaps a typo when you made the post ?

>SELECT * FROM sitelist
>WHERE
sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
LIKE >'%wik%'
>AND category='Bundled Software'
>AND owner='Gary'
>ORDER BY sitename

Hello Simon, that's what I tried first (yes, the '7' was a typo, sorry) and
it didn't work. I know there is a limit to field concatenation this way but
I don't know what it is so will have to play as it is not giving me any
results at the moment.

Oh well, thanks for the idea...I don't really need any sleep tonight..:):)
-- 
View this message in context: 
http://old.nabble.com/Search-multiple-columns-AND-extra-comparator-tp28356196p28356587.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] Search multiple columns AND extra coparator

[Simon Slavin]

On 25 Apr 2010, at 2:44pm, flakpit wrote:

> Using the first part of this query works fine to return data by searching
> the multiple concatenated columns, very happy with that.
> 
> SELECT * FROM sitelist
> WHERE
> sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
> LIKE '%wik%' 7
> 
> But I also want to AND the following columns to apply to the results and I
> am not getting it (So what's new, I hear you say??)
> 
> AND category='Bundled Software'
> AND owner='Gary'
> 
> ORDER BY sitename

Just add your AND and ORDER by clauses on after the '%wik%'.  But I don't know 
why there's a '7' at the end of your original line.  I don't think it does 
anything.  Perhaps a typo when you made the post ?

SELECT * FROM sitelist
WHERE 
sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
 LIKE '%wik%'
AND category='Bundled Software'
AND owner='Gary'
ORDER BY sitename

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

[sqlite] Search multiple columns AND extra coparator

[flakpit]

Hello folks, sorry to bother you. You must admit I take 6 months or more to
ask questions:):)

Using the first part of this query works fine to return data by searching
the multiple concatenated columns, very happy with that.

SELECT * FROM sitelist
 WHERE
sitename||username||password||serialnum||misc1||misc2||misc3||misc4||misc5||notes
LIKE '%wik%' 7

But I also want to AND the following columns to apply to the results and I
am not getting it (So what's new, I hear you say??)

 AND category='Bundled Software'
 AND owner='Gary'

 ORDER BY sitename

So I want the columns to be searched for a match (working) but ONLY
DISPLAYED if the category and owner are matching as well (Not working). 
Have tried all manner of ANDs and ORs and WHEREes in various ways and am not
getting it.

Would anyone be able to elucidate?
-- 
View this message in context: 
http://old.nabble.com/Search-multiple-columns-AND-extra-coparator-tp28356196p28356196.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] Search a Select in another Select

[Terence Lorenzo]

yes I am aware of that. word and word1 are variables in my case. I didnt wish
to create confusion so I appreciate the comment

cheers


Igor Tandetnik wrote:
> 
> Terence Lorenzo wrote:
>> select K1.keyword
>> from emaildata as E1
>>  INNER JOIN keylocations AS L1 on L1.id_unique = E1.id_unique
>>  INNER JOIN keywords as K1 on K1.id = L1.id_keyword
>>   WHERE K1.keyword LIKE '%word%' or K1.keyword LIKE '%word2%'
> 
> The second test is redundant. Everything that matches '%word2%' would 
> also match '%word%'
> 
>> This query finds all matching keywords
>>
>> select
>> E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset
>> from emaildata as E1
>> left join subjects on id_subject = subjects.id
>> left join recipients as R1 on E1.id_from = R1.id
>> left join recipients as R2 on E1.id_to = R2.id
>> left join chunkinfo as C1 on E1.id_chunk = C1.id;
>>
>> this query gets all the info about an email
>>
>>
>> Now I'd like to mix the 2 queries; I want to limit the 2nd query
>> results with the 1st query, but I cant figure it out
> 
> select
> E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset
> from emaildata as E1
>   left join subjects on id_subject = subjects.id
>   left join recipients as R1 on E1.id_from = R1.id
>   left join recipients as R2 on E1.id_to = R2.id
>   left join chunkinfo as C1 on E1.id_chunk = C1.id
> where E1.id_unique in (
>   select L1.id_unique from keylocations AS L1
>   INNER JOIN keywords as K1 on K1.id = L1.id_keyword
>   WHERE K1.keyword LIKE '%word%'
> );
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Search-a-Select-in-another-Select-tp25234534p25239570.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] Search a Select in another Select

[Igor Tandetnik]

Terence Lorenzo wrote:
> select K1.keyword
> from emaildata as E1
>  INNER JOIN keylocations AS L1 on L1.id_unique = E1.id_unique
>  INNER JOIN keywords as K1 on K1.id = L1.id_keyword
>   WHERE K1.keyword LIKE '%word%' or K1.keyword LIKE '%word2%'

The second test is redundant. Everything that matches '%word2%' would 
also match '%word%'

> This query finds all matching keywords
>
> select
> E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset
> from emaildata as E1
> left join subjects on id_subject = subjects.id
> left join recipients as R1 on E1.id_from = R1.id
> left join recipients as R2 on E1.id_to = R2.id
> left join chunkinfo as C1 on E1.id_chunk = C1.id;
>
> this query gets all the info about an email
>
>
> Now I'd like to mix the 2 queries; I want to limit the 2nd query
> results with the 1st query, but I cant figure it out

select
E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset
from emaildata as E1
  left join subjects on id_subject = subjects.id
  left join recipients as R1 on E1.id_from = R1.id
  left join recipients as R2 on E1.id_to = R2.id
  left join chunkinfo as C1 on E1.id_chunk = C1.id
where E1.id_unique in (
  select L1.id_unique from keylocations AS L1
  INNER JOIN keywords as K1 on K1.id = L1.id_keyword
  WHERE K1.keyword LIKE '%word%'
);

Igor Tandetnik 



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

Re: [sqlite] Search a Select in another Select

[Terence Lorenzo]

Thanks for the help.
I suspected I had to use a secondary select, though I want sure how to use
it
For others, here is the completed sql

select 
E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset
from 
( select distinct E2.id 
 from emaildata as E2
  INNER JOIN keylocations AS L1 on L1.id_unique = E2.id_unique  
  LEFT JOIN keywords as K1 on K1.id = L1.id_keyword  
  where K1.keyword LIKE '%word1%' or  where K1.keyword LIKE '%word2%'  ) as
filt  
  inner join emaildata as E1 on E1.id = filt.id
  left join subjects on id_subject = subjects.id 
  left join recipients as R1 on E1.id_from = R1.id 
  left join recipients as R2 on E1.id_to = R2.id 
  left join chunkinfo as C1 on E1.id_chunk = C1.id;


cheers Darren


-- 
View this message in context: 
http://www.nabble.com/Search-a-Select-in-another-Select-tp25234534p25235045.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] Search a Select in another Select

[Darren Duncan]

Terence Lorenzo wrote:
> I have these 2 queries
> 
> select K1.keyword 
>  from emaildata as E1 
>   INNER JOIN keylocations AS L1 on L1.id_unique = E1.id_unique  
>   INNER JOIN keywords as K1 on K1.id = L1.id_keyword  
>WHERE K1.keyword LIKE '%word%' or K1.keyword LIKE '%word2%' 
> 
> This query finds all matching keywords
> 
> select
> E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset
>  
> from emaildata as E1 
> left join subjects on id_subject = subjects.id 
> left join recipients as R1 on E1.id_from = R1.id 
> left join recipients as R2 on E1.id_to = R2.id 
> left join chunkinfo as C1 on E1.id_chunk = C1.id;
> 
> this query gets all the info about an email
> 
> Now I'd like to mix the 2 queries; I want to limit the 2nd query results
> with the 1st query, but I cant figure it out

I do that sort of thing all the time, using a subquery in the FROM clause.

Basically what you want, for example, is to make a filtering query that just 
returns a single-column rowset whose values are the unique primary keys for the 
table your main query is selecting on.

For example:

   select ...
   from (
   select idcol
   from footbl
   
 ) as footbl_filt
 inner join footbl on footbl.idcol = footbl_filt.idcol
   

Or I suppose that subquery could go in the main WHERE instead, but I always did 
it in FROM.

The point is that this approach cleanly separates the complexity for the search 
filter from the complexity for gathering the details you want to return.

Complexity management is just one of several reasons why subquery support is 
immensely valuable in a DBMS, and I'm glad SQLite supports it.

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

[sqlite] Search a Select in another Select

[Terence Lorenzo]

I have these 2 queries

select K1.keyword 
 from emaildata as E1 
  INNER JOIN keylocations AS L1 on L1.id_unique = E1.id_unique  
  INNER JOIN keywords as K1 on K1.id = L1.id_keyword  
   WHERE K1.keyword LIKE '%word%' or K1.keyword LIKE '%word2%' 

This query finds all matching keywords

select
E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset
 
from emaildata as E1 
left join subjects on id_subject = subjects.id 
left join recipients as R1 on E1.id_from = R1.id 
left join recipients as R2 on E1.id_to = R2.id 
left join chunkinfo as C1 on E1.id_chunk = C1.id;

this query gets all the info about an email


Now I'd like to mix the 2 queries; I want to limit the 2nd query results
with the 1st query, but I cant figure it out
-- 
View this message in context: 
http://www.nabble.com/Search-a-Select-in-another-Select-tp25234534p25234534.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] search time is non-determinate in multi-thread enviroment

[Lawrence Gold]

On Jul 28, 2009, at 8:09 PM, pierr wrote:

> http://www.sqlite.org/threadsafe.html
> "Multi-thread. In this mode, SQLite can be safely used by multiple  
> threads
> provided that no single database connection is used simulataneously  
> in two
> or more threads."
>
> If I have one(different) connection for each thread , it seems no  
> need to
> synchronize these threads' accessing to the database because they are
> through different connection. Did I read it wrongly?

I believe you are correct.

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

Re: [sqlite] search time is non-determinate in multi-thread enviroment

[pierr]

Jay A. Kreibich-2 wrote:
> 
> 
>   THREADSAFE=1 is "Serialize mode" and THREADSAFE=2 is "Multithread"
>   mode.  THREADSAFE=2 (basic Multithread) actually offers less protection,
>   requiring the application to provide its own locks to prevent multiple
>   threads from accessing the DB at the same time, while THREADSAFE=1
>   (Serialize) handles all that for you.  Generally, unless you're doing
>   a lot of thread and lock management yourself, you want to use =1.
> 

http://www.sqlite.org/threadsafe.html
"Multi-thread. In this mode, SQLite can be safely used by multiple threads 
provided that no single database connection is used simulataneously in two
or more threads." 

If I have one(different) connection for each thread , it seems no need to
synchronize these threads' accessing to the database because they are
through different connection. Did I read it wrongly?
-- 
View this message in context: 
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24711210.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] search time is non-determinate in multi-thread enviroment

[pierr]

Igor Tandetnik wrote:
> 
> pierr wrote:
>> Hi,
>>   My sqlite is configure as serialized (THREADSAFE=1). My application
>> has only one connection but it is shared by two thread. One thread
>> will do insert/update/delete in the background , another thread will
>> do the select upon request of the user from gui.
>>  Typically, we will call following function 12 times (with different
>> parameters ,of course)before showing the pages to user. In the
>> standalone test (single  thread), a call to following function will
>> take less than 40ms, so 12 times will take less than 500 ms and it is
>> acceptable. However, in the real application, sometimes this function
>> took 1000ms to return the result which make the gui slow.
> 
> When the second thread makes SQLite calls, it takes a lock on the 
> connection. If the UI thread runs a SELECT at this moment, it also tries 
> to take that lock, and so has to wait until the second thread releases 
> it.
> 
1. I expected the second thread would impact the SELECT thread but not that
much. The serach time increase as much as 6 times on average. But in my
experiment, when i add a usleep(1) (10ms) in the INSERT thread, the
search time will back to normal.I am not sure if this is the POINT. I would
try this in my real application to see what happened.

2. I also suspect the increase of search time is caused by the SELECT thread
can not be scheduled timely to run because of INTERRUPT that happend very
frequently in my system , or because of other threads cost to much CPU time
like the other_thead in my expriment ,which has nothing but while(1).
(Actually ,I don't quite understand why this would impact the SELECT thread
(that much). Although other_thread has a while(1), I thought kernel (linux
2.6) should use time_sharing scheduler policy as both other_thread and
select_thread are nomal thread , i.e, not real time thread and no priority
set explicitly. So priority base preemptive should not be applied here. Any
idea? )

3.I think the way I use the sqlite (one thread for write , another for read)
is not uncommon. Do we have any best practice for the question I am asking
here?


Igor Tandetnik wrote:
> 
> Further, you cannot open two connections to the same in-memory database.
> 

This would be the thing that prevent me from going THREADSAFE=2.

-- 
View this message in context: 
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24711093.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] search time is non-determinate in multi-thread enviroment

[Jay A. Kreibich]

On Tue, Jul 28, 2009 at 07:59:23AM -0400, Igor Tandetnik scratched on the wall:
> pierr wrote:
> >   Any insight? Should I go with THREADSAFE=2 and two connections: one
> > for read ,another for write? Thanks.
> 
> THREADSAFE is not a number of allowed concurrent threads. It's a boolean 
> switch - 0 means non-threadsafe, non-zero means thread-safe. 
> THREADSAFE=2 won't make any difference.

  THREADSAFE=1 is "Serialize mode" and THREADSAFE=2 is "Multithread"
  mode.  THREADSAFE=2 (basic Multithread) actually offers less protection,
  requiring the application to provide its own locks to prevent multiple
  threads from accessing the DB at the same time, while THREADSAFE=1
  (Serialize) handles all that for you.  Generally, unless you're doing
  a lot of thread and lock management yourself, you want to use =1.

  http://sqlite.org/compile.html#threadsafe
  http://sqlite.org/threadsafe.html

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search time is non-determinate in multi-thread enviroment

[Igor Tandetnik]

pierr wrote:
> Hi,
>   My sqlite is configure as serialized (THREADSAFE=1). My application
> has only one connection but it is shared by two thread. One thread
> will do insert/update/delete in the background , another thread will
> do the select upon request of the user from gui.
>  Typically, we will call following function 12 times (with different
> parameters ,of course)before showing the pages to user. In the
> standalone test (single  thread), a call to following function will
> take less than 40ms, so 12 times will take less than 500 ms and it is
> acceptable. However, in the real application, sometimes this function
> took 1000ms to return the result which make the gui slow.

When the second thread makes SQLite calls, it takes a lock on the 
connection. If the UI thread runs a SELECT at this moment, it also tries 
to take that lock, and so has to wait until the second thread releases 
it.

>   Any insight? Should I go with THREADSAFE=2 and two connections: one
> for read ,another for write? Thanks.

THREADSAFE is not a number of allowed concurrent threads. It's a boolean 
switch - 0 means non-threadsafe, non-zero means thread-safe. 
THREADSAFE=2 won't make any difference.

Further, you cannot open two connections to the same in-memory database.

Igor Tandetnik 



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

Re: [sqlite] search time is non-determinate in multi-thread enviroment

[pierr]

pierr wrote:
> 
> Hi,
>My sqlite is configure as serialized (THREADSAFE=1). My application has
> only one connection but it is shared by two thread. One thread will do
> insert/update/delete in the background , another thread will do the select
> upon request of the user from gui. 
>   Typically, we will call following function 12 times (with different
> parameters ,of course)before showing the pages to user. In the standalone
> test (single  thread), a call to following function will take less than
> 40ms, so 12 times will take less than 500 ms and it is acceptable.
> However, in the real application, sometimes this function took 1000ms to
> return the result which make the gui slow.
> 
>  int search_eit_event_in_time_inteval(...)
>  86 {
>  93 
>  94 sql = (char *)" SELECT *,rowid FROM tblEvent_basic "
>  95   " WHERE "
>  96   " service_id = ? AND "
>  97   " start_time < ? AND end_time > ? "
>  98   " ORDER by start_time ASC";
>  }
> 
>  I did some experiment try to understand why this happend? Here is the
> code I used :
>  http://pastebin.ca/1509723
> 
>  I found :
>  1. When  i create a write_thread
> a. with usleep(1) in while(1) 
> search time always < 50ms
> b. without usleep(1) in while(1) 
>  17.7 % search time > 50ms , usually 200ms
>   
>  2. When  i create another_thread (no database access at all)
> a. with usleep(1) in while(1) 
> search time always < 50ms
> b. without usleep(1) in while(1) 
>  21.7 % search time > 50ms , usually 200ms.
> 
>Any insight? Should I go with THREADSAFE=2 and two connections: one for
> read ,another for write? Thanks. 
> 
I was using memory database. This should be impormant background information
:)
-- 
View this message in context: 
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24693647.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

[sqlite] search time is non-determinate in multi-thread enviroment

[pierr]

Hi,
   My sqlite is configure as serialized (THREADSAFE=1). My application has
only one connection but it is shared by two thread. One thread will do
insert/update/delete in the background , another thread will do the select
upon request of the user from gui. 
  Typically, we will call following function 12 times (with different
parameters ,of course)before showing the pages to user. In the standalone
test (single  thread), a call to following function will take less than
40ms, so 12 times will take less than 500 ms and it is acceptable. However,
in the real application, sometimes this function took 1000ms to return the
result which make the gui slow.

 int search_eit_event_in_time_inteval(...)
 86 {
 93 
 94 sql = (char *)" SELECT *,rowid FROM tblEvent_basic "
 95   " WHERE "
 96   " service_id = ? AND "
 97   " start_time < ? AND end_time > ? "
 98   " ORDER by start_time ASC";
 }

 I did some experiment try to understand why this happend? Here is the code
I used :
 http://pastebin.ca/1509723

 I found :
 1. When  i create a write_thread
a. with usleep(1) in while(1) 
search time always < 50ms
b. without usleep(1) in while(1) 
 17.7 % search time > 50ms , usually 200ms
  
 2. When  i create another_thread (no database access at all)
a. with usleep(1) in while(1) 
search time always < 50ms
b. without usleep(1) in while(1) 
 21.7 % search time > 50ms , usually 200ms.

   Any insight? Should I go with THREADSAFE=2 and two connections: one for
read ,another for write? Thanks. 
-- 
View this message in context: 
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24693604.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] search in archive

[John Machin]

On 20/06/2009 3:56 AM, Rizzuto, Raymond wrote:
> Is it possible to have a search feature for the archive?  I.e. rather than 
> having to do a linear search through 18 archives for an answer to a question, 
> have a google-like search across all of the archives?

http://search.gmane.org/

In the box called "group", type in: comp.db.sqlite.general

Based on a reverse-date search for "Richard Hipp", it appears to go back 
to 2002 at least.

HTH,
John

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

Re: [sqlite] search in archive

[Rizzuto, Raymond]

My apologies for being unclear.  I meant the archive of all the sqlite-users 
messages.  I prefer not to ask a question that has already been answered.

The link http://www.mail-archive.com/sqlite-users@sqlite.org/ suggested by Adam 
DeVita does has such a function.  I was accessing the archives via 
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/, which doesn't 
have that functionality.

-Original Message-
From: Swithun Crowe [mailto:swit...@swithun.servebeer.com]
Sent: Friday, June 19, 2009 3:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] search in archive

Hello

KN On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
KN  wrote:
KN
KN > Is it possible to have a search feature for the
KN > archive?
KN
KN Which archive?

I think Raymond means the sqlite-users archive.

You could download all the txt.gz files, cat them together and then grep
for what you want to find. You wouldn't have the threads, but it might be
easier than checking all the threads via the web site.

Perhaps it is possible to import the files into an email client which can
recreate the messages and threads.

Swithun.


IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search in archive

[Adam DeVita]

The link I posted (http://www.mail-archive.com/sqlite-users%40sqlite.org/)
only goes back to March 10, 2009  It searches well but is currently
incomplete. Will it be expanded to include older posts?

http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/

goes back much further, but searching is as Raymond described.  Painful,
compared to the nice enter search text here at the mail-archive.com.

http://marc.info/?l=sqlite-users&r=1&w=2 also has nice search
capabilities.   Perhaps the http://www.sqlite.org/support.html should be
updated to advertise that the off site archives have search features? A lot
of questions that have already been answered and keep coming back could be
avoided.






On Fri, Jun 19, 2009 at 3:16 PM, Swithun Crowe <
swit...@swithun.servebeer.com> wrote:

> Hello
>
> KN On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
> KN  wrote:
> KN
> KN > Is it possible to have a search feature for the
> KN > archive?
> KN
> KN Which archive?
>
> I think Raymond means the sqlite-users archive.
>
> You could download all the txt.gz files, cat them together and then grep
> for what you want to find. You wouldn't have the threads, but it might be
> easier than checking all the threads via the web site.
>
> Perhaps it is possible to import the files into an email client which can
> recreate the messages and threads.
>
> Swithun.
> ___
> 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] search in archive

[Swithun Crowe]

Hello

KN On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
KN  wrote:
KN 
KN > Is it possible to have a search feature for the
KN > archive? 
KN 
KN Which archive?

I think Raymond means the sqlite-users archive.

You could download all the txt.gz files, cat them together and then grep 
for what you want to find. You wouldn't have the threads, but it might be 
easier than checking all the threads via the web site.

Perhaps it is possible to import the files into an email client which can 
recreate the messages and threads.

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

Re: [sqlite] search in archive

[Adam DeVita]

There is a search of archives at
http://www.mail-archive.com/sqlite-users%40sqlite.org/



On Fri, Jun 19, 2009 at 2:43 PM, Kees Nuyt  wrote:

> On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
>  wrote:
>
> > Is it possible to have a search feature for the
> > archive?
>
> Which archive?
>
> I'll assume you have 18 different databases and you want to
> search them in parallel.
>
> > I.e. rather than having to do a linear
> > search through 18 archives for an answer
> > to a question, have a google-like search
> > across all of the archives?
>
> Yes, make your application multithreaded, one thread for the
> user interface and 18 for databases. Every dbthread would
> open a different database.
>
> It will only really help if your system has multiple
> processor cores, and if the databases are each on a
> different disk.
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> 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] search in archive

[Kees Nuyt]

On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
 wrote:

> Is it possible to have a search feature for the
> archive? 

Which archive?

I'll assume you have 18 different databases and you want to
search them in parallel.

> I.e. rather than having to do a linear
> search through 18 archives for an answer
> to a question, have a google-like search
> across all of the archives?

Yes, make your application multithreaded, one thread for the
user interface and 18 for databases. Every dbthread would
open a different database.

It will only really help if your system has multiple
processor cores, and if the databases are each on a
different disk.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] search in archive

[Rizzuto, Raymond]

Is it possible to have a search feature for the archive?  I.e. rather than 
having to do a linear search through 18 archives for an answer to a question, 
have a google-like search across all of the archives?



Ray Rizzuto
raymond.rizz...@sig.com
Susquehanna International Group
(610)747-2336 (W)
(215)776-3780 (C)




IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search in multiple fields.

[Jay A. Kreibich]

On Fri, Jun 05, 2009 at 10:53:58AM -0400, German Escallon scratched on the wall:

> Say I have the following record in my DB:
> 
> Path: /home/media/mymusic
> filename: rock_my_world
> extension: mp3
> 
> I want to be able to find this file by typing any of the following in
> the user interface that I'll provide.

> ?> rock
> ?> rock_my_world
> ?> mp3
> ?> mymusic
> ?> rock_my_world.mp3
> ?> /home/media/mymusic/rock_my_world.mp3

> The above returns the right results for the first 4 cases, but not the
> last two. Any suggestions?? Thank you in advance.

  Build the full path and search against that:

  WHERE (path||filename||'.'||extension LIKE '%%');

  In fact, that's the only WHERE clause you'd need.


  BTW, string constants in SQL are single quotes ('), not double (").

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search in multiple fields.

[Alexandre Courbot]

Hi,

Not sure if that answers your question, but I think you seriously want
to use FTS3. It will be both a trillion times faster than your current
query - you may have trouble with your last example though. Maybe you
can still go through by escaping the search terms.

http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.syntax&v=1.2

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

[sqlite] Search in multiple fields.

[German Escallon]

Hello all,

I have a table with file information (path, filename, extension, size,
etc), and I would like to enable search on this table based on different
fields with a single input, and/or concatenation of the same. For example..

Say I have the following record in my DB:

Path: /home/media/mymusic
filename: rock_my_world
extension: mp3

I want to be able to find this file by typing any of the following in
the user interface that I'll provide.


?> rock
?> rock_my_world
?> mp3
?> mymusic
?> rock_my_world.mp3
?> /home/media/mymusic/rock_my_world.mp3

I am doing something along the lines of:

SELECT basename, extension FROM tb_file WHERE (basename LIKE
"%%") OR (extension LIKE "%%") OR (path LIKE
"%%");

The above returns the right results for the first 4 cases, but not the
last two. Any suggestions?? Thank you in advance.

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search time in FTS3 tables sometimes very long

[Scott Hess]

BTW, http://www.sqlite.org/cvstrac/chngview?cn=4599 is the final fix
to this.  It's different from the patch I posted.  The patch did the
job, but felt wrong to me.  This has the same performance
characteristics, but feels ... well, less wrong.

This change should apply cleanly to fts2.c, if anyone is still using fts2.

-scott


On Dec 5, 2007 2:18 AM, Ingo Godau-Gellert <[EMAIL PROTECTED]> wrote:
> Hi Scott!
>
> You're great! I checked the attached modification and found no search
> taking longer than 20s now! It's a great improvement. I didn't find any
> other problems, so I will leave the modification in my FTS3 compilation.
>
> Many thanks!
>
> Ingo
>
>
> Scott Hess schrieb:
>
> > 2007/12/4 Scott Hess <[EMAIL PROTECTED]>:
> >
> >> This seems a little excessive, though.  I do see that there's an
> >> O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to
> >> docListUnion()).  I can reasonably make that O(logN), which might help
> >> a great deal, if you're hitting it.  Not really sure how to tell if
> >> you're hitting it, but I'll experiment at my end and see whether I can
> >> improve things there.
> >>
> >
> > With the attached patch, the time to match against 't*' with the rfc
> > dataset goes from 1m16s to 5s.
> >
> > It passes the tests, but I'll not guarantee that this is what I'll
> > check in.  I want to think on it.  But let me know if this doesn't
> > help.
> >
> > -scott
> >
> > 
>
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] search time in FTS3 tables sometimes very long

[Ingo Godau-Gellert]

Hi Scott!

You're great! I checked the attached modification and found no search 
taking longer than 20s now! It's a great improvement. I didn't find any 
other problems, so I will leave the modification in my FTS3 compilation.


Many thanks!

Ingo


Scott Hess schrieb:

2007/12/4 Scott Hess <[EMAIL PROTECTED]>:
  

This seems a little excessive, though.  I do see that there's an
O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to
docListUnion()).  I can reasonably make that O(logN), which might help
a great deal, if you're hitting it.  Not really sure how to tell if
you're hitting it, but I'll experiment at my end and see whether I can
improve things there.



With the attached patch, the time to match against 't*' with the rfc
dataset goes from 1m16s to 5s.

It passes the tests, but I'll not guarantee that this is what I'll
check in.  I want to think on it.  But let me know if this doesn't
help.

-scott
  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] search time in FTS3 tables sometimes very long

[Scott Hess]

2007/12/4 Scott Hess <[EMAIL PROTECTED]>:
> This seems a little excessive, though.  I do see that there's an
> O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to
> docListUnion()).  I can reasonably make that O(logN), which might help
> a great deal, if you're hitting it.  Not really sure how to tell if
> you're hitting it, but I'll experiment at my end and see whether I can
> improve things there.

With the attached patch, the time to match against 't*' with the rfc
dataset goes from 1m16s to 5s.

It passes the tests, but I'll not guarantee that this is what I'll
check in.  I want to think on it.  But let me know if this doesn't
help.

-scott
Index: ext/fts3/fts3.c
===
RCS file: /sqlite/sqlite/ext/fts3/fts3.c,v
retrieving revision 1.12
diff -u -r1.12 fts3.c
--- ext/fts3/fts3.c 24 Nov 2007 00:41:52 -  1.12
+++ ext/fts3/fts3.c 5 Dec 2007 01:03:32 -
@@ -,6 +,21 @@
   return rc;
 }
 
+/* Call docListUnion() to merge *left and *right into *out, destroying
+** *left and *right.  Handles left or right in the same location as
+** out (in-place merge).
+*/
+static void docListUnionWithDestroy(DataBuffer *left, DataBuffer *right,
+DataBuffer *out) {
+  DataBuffer result;
+  dataBufferInit(&result, left->nData+right->nData);
+  docListUnion(left->pData, left->nData, right->pData, right->nData,
+   &result);
+  dataBufferDestroy(left);
+  dataBufferDestroy(right);
+  *out = result;
+}
+
 /* Scan pReader for pTerm/nTerm, and merge the term's doclist over
 ** *out (any doclists with duplicate docids overwrite those in *out).
 ** Internal function for loadSegmentLeaf().
@@ -5562,6 +5577,15 @@
 static int loadSegmentLeavesInt(fulltext_vtab *v, LeavesReader *pReader,
 const char *pTerm, int nTerm, int isPrefix,
 DataBuffer *out){
+  /* To keep merging O(logN), keep a list of merged buffers.  After
+  ** each doclist is added, buffers are merged for the number of 0
+  ** low-order bits in nDoclists.  So this many doclists are merged at
+  ** each point: 0, 1, 0, 2, 0, 1, 0, 4, 0, 1, 0, 2, ... (like a
+  ** tree).
+  */
+  DataBuffer *pBuffers = NULL;
+  int nBuffers = 0, nMaxBuffers = 0, nDoclists = 0;
+
   assert( nTerm>0 );
 
   /* Process while the prefix matches. */
@@ -5575,24 +5599,54 @@
 int c = leafReaderTermCmp(&pReader->leafReader, pTerm, nTerm, isPrefix);
 if( c==0 ){
   const char *pData = leavesReaderData(pReader);
-  int nData = leavesReaderDataBytes(pReader);
-  if( out->nData==0 ){
-dataBufferReplace(out, pData, nData);
-  }else{
-DataBuffer result;
-dataBufferInit(&result, out->nData+nData);
-docListUnion(out->pData, out->nData, pData, nData, &result);
-dataBufferDestroy(out);
-*out = result;
-/* TODO(shess) Rather than destroy out, we could retain it for
-** later reuse.
-*/
+  int n, nData = leavesReaderDataBytes(pReader);
+  if( nBuffers==nMaxBuffers ){
+++nMaxBuffers;
+pBuffers = sqlite3_realloc(pBuffers, nMaxBuffers*sizeof(*pBuffers));
+  }
+  dataBufferInit(&(pBuffers[nBuffers]), nData);
+  dataBufferReplace(&(pBuffers[nBuffers]), pData, nData);
+  nBuffers++;
+  assert(nBuffers<=nMaxBuffers);
+
+  ++nDoclists;
+  for( n=nDoclists; (n%2)==0; n>>=1) {
+assert(n);  /* Can't happen if nDoclists!=0. */
+docListUnionWithDestroy(&(pBuffers[nBuffers-2]),
+&(pBuffers[nBuffers-1]),
+&(pBuffers[nBuffers-2]));
+nBuffers--;
   }
 }
 if( c>0 ) break;  /* Past any possible matches. */
 
 rc = leavesReaderStep(v, pReader);
-if( rc!=SQLITE_OK ) return rc;
+if( rc!=SQLITE_OK ){
+  while( nBuffers>0 ){
+nBuffers--;
+dataBufferDestroy(&(pBuffers[nBuffers]));
+  }
+  sqlite3_free(pBuffers);
+  return rc;
+}
+  }
+  while( nBuffers>1 ){
+docListUnionWithDestroy(&(pBuffers[nBuffers-2]),
+&(pBuffers[nBuffers-1]),
+&(pBuffers[nBuffers-2]));
+nBuffers--;
+  }
+  if( nBuffers>0 ){
+assert(1==nBuffers);
+if( out->nData==0 ){
+  dataBufferDestroy(out);
+  *out = pBuffers[0];
+}else{
+  docListUnionWithDestroy(out, &(pBuffers[0]), out);
+}
+sqlite3_free(pBuffers);
+  } else {
+assert(NULL==pBuffers);
   }
   return SQLITE_OK;
 }
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] search time in FTS3 tables sometimes very long

[Scott Hess]

2007/12/4 Ingo Godau-Gellert <[EMAIL PROTECTED]>:
> What is really strange is that FTS3 search phrases like
> SELECT referenzcode FROM volltext where volltext match ('installation
> manual') are performed really fast within some milliseconds, independent
> to the search phrase.
> But in general I allow the user to enter a search word in a dedicated
> form field (Windows computers), the search starts after entering of each
> additional character.
>
> That means:
> Entering the word "installation" the search starts after entering the
> character "i". Then, after entering the second character "n" the search
> field is "in" and starts again. Then the user enters "s" and the search
> is interrupted and starts again with search word "ins".
>
> To be able to find not only table entries containing "i", "in", "ins",
> "inst", ... there is automatically added the character "*" - in fact the
> search phrase is "i*", "in*", "ins"*, "inst*", ...
>
> Now it's very interesting that a search phrase containing at least 4
> characters causes a search time of max. some seconds.

In general, the more specific the term is, the faster the search will
be.  Very unlikely terms will be faster than common terms.

If you do a search for 'installation', then fts can dig directly down
to the information for 'installation' and serve it up.  But if you
search for 'i*', it's as if you're searching for all terms starting
with 'i', so fts has to find all those terms and merge their
information together.  This is always going to be slower than a query
for a specific term within that range of terms.

The specific reasons why you're seeing slow performance, and the
specific things you see slow performance on, are very dependent on the
data in your system.  In general, more is slower than less.  So in
cases where you have a large number of results, it will generally be
slower.

> The search of "E5*" f.e takes 2,7 seconds and is finding 24419 entries.
> But if the search f.e. is "F1*" the search takes around 1128,5 seconds
> to find 77652 entries.

This seems a little excessive, though.  I do see that there's an
O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to
docListUnion()).  I can reasonably make that O(logN), which might help
a great deal, if you're hitting it.  Not really sure how to tell if
you're hitting it, but I'll experiment at my end and see whether I can
improve things there.

> It's clear to me that the search time in some cases takes longer as in
> other cases. Especially I would expect that the search takes as longer
> as the amount of found entries is bigger.
>
> But is there anyone who could explain me, why "F1*" takes 1128,5 seconds
> search time? Or "F3*" takes 202,8s? What is the reason for such a long
> duration?

If there are a lot of hits distributed across a few terms, that might
be much faster than fewer hits distributed across a large number of
terms.

> In my opinion a short search phrase with "*" should be very fast.

:-).  Prefix searches will likely always be slower than specific
searches, because they're essentially implemented as a set of specific
searches OR'ed together.  Better than that, of course, but still
strictly slower than a single specific search.

> Is there any possibility to solve this behaviour? Today I tried every
> possible search phrase combination with 2 characters only, noticed the
> search time and decided to use FTS search only in case the search will
> likely take less than 30 seconds. As soon as the search phrase will take
> longer than 30 seconds I use the standard SQLITE3 search algorithm.
> That's a workaround for today, but I consider someone being here who
> could improve the algorithm behaviour of FTS3?

SQLite has a function sqlite3_interrupt() which you can use to
interrupt statements.  Since fts3 is implemented in terms of SQLite,
you _should_ be able to call sqlite3_interrupt() to cause it to stop
executing.  If your code is asynchronous, then you could start the
query in the background, and call sqlite3_interrupt() after awhile to
stop execution.

There are a couple different things I've considered for fts to
implement to help out with this case.  One thing would be a "limit"
feature, so you could say something like:

   SELECT rowid FROM t WHERE t MATCH 'a* limit:10';

Why this is helpful is that it tells fts that it only needs to
generate 10 hits, which it might be able to optimize to be very fast.

Another thing I've considered is to add some sort of prefix-specific
index to the system.  I'm not even clear what this would mean, so I'm
not going to describe it :-).  But it would probably be something in
the CREATE statement which indicated that you expected to do prefix
queries and wanted fts to keep additional information to make those
fast.  OR, it might be a completely distinct table which only keeps an
index to make prefix-searching fast.

One reason you might find full-table-scans to sometimes be faster than
fts is if fts ends up essentially looking at

[sqlite] search time in FTS3 tables sometimes very long

[Ingo Godau-Gellert]

For our stock management I created a SQLITE 3.5.3 table, containing 
around 1,5M entries.


The FTS3 table is created with statement:
CREATE VIRTUAL TABLE volltext using FTS3(referenzcode, code, deut, engl, 
ital, sppm, rep, info)


"referenzcode" and "code" are containing part numbers with 10 or 11 
digits or letters, f.e. "1401326732D"
"deut", "engl" and "ital" are containing descriptions in different 
languages of the same part, max. 80 characters.
"sppm" and "rep" are internal references with up to 6 characters, 
describing the part usage ("mech", "elect", ...)
"info" is the biggest field, containing up to some 1000 characters but 
also sometimes empty. "info" may contain f.e. a digitalized product 
manual or installation manual text.


What is really strange is that FTS3 search phrases like
SELECT referenzcode FROM volltext where volltext match ('installation 
manual') are performed really fast within some milliseconds, independent 
to the search phrase.
But in general I allow the user to enter a search word in a dedicated 
form field (Windows computers), the search starts after entering of each 
additional character.


That means:
Entering the word "installation" the search starts after entering the 
character "i". Then, after entering the second character "n" the search 
field is "in" and starts again. Then the user enters "s" and the search 
is interrupted and starts again with search word "ins".


To be able to find not only table entries containing "i", "in", "ins", 
"inst", ... there is automatically added the character "*" - in fact the 
search phrase is "i*", "in*", "ins"*, "inst*", ...


Now it's very interesting that a search phrase containing at least 4 
characters causes a search time of max. some seconds.
But as some parts in our stock are having short names like "P7" or "E5", 
sometimes the search phrase is only 2 characters long plus additional "*".


I found out that some character combinations are causing longer search 
durations than others.


The search of "E5*" f.e takes 2,7 seconds and is finding 24419 entries.
But if the search f.e. is "F1*" the search takes around 1128,5 seconds 
to find 77652 entries.
Other search phrases "vi*" are taking 3,7s for 14803 entries or "ta*" 
takes 42,8s for 102189 entries.

A "very good" example is "tm*" with 0,2s for  entries.

It's clear to me that the search time in some cases takes longer as in 
other cases. Especially I would expect that the search takes as longer 
as the amount of found entries is bigger.


But is there anyone who could explain me, why "F1*" takes 1128,5 seconds 
search time? Or "F3*" takes 202,8s? What is the reason for such a long 
duration?


In my opinion a short search phrase with "*" should be very fast.

By the way, an other example: The same table with same entries is 
existing in a standard SQLITE database, without using FTS3.


The search of every phrase, independent to length of the phrase, takes 
max. 55 seconds only (after first call; every further search is 
performed in max. 10 seconds as the table seems to be in cache then). 
Unfortunately this standard search is finding every entry containing the 
search phrase, not only the rows where the word is beginning with the 
search phrase.



The goal - and reason for my question - is: I tried to use fulltext 
search as it should be faster than the standard SQLITE search method, 
and in fact it is faster if the search phrase is long enough. But with 
short search phrases the FTS3 extension seems to run in troubles.


Is there any possibility to solve this behaviour? Today I tried every 
possible search phrase combination with 2 characters only, noticed the 
search time and decided to use FTS search only in case the search will 
likely take less than 30 seconds. As soon as the search phrase will take 
longer than 30 seconds I use the standard SQLITE3 search algorithm.
That's a workaround for today, but I consider someone being here who 
could improve the algorithm behaviour of FTS3?


By the way, as I wrote above, the "referenzcode" entry contains mostly 
numbers with only some other characters. FTS3 search with digits only 
like "13*" or "76*" takes always more than 120 seconds, so the use of 
FTS 3 is never possible in that case. It makes only sense to use FTS3 if 
the search phrase with digits is at least 4 characters long (f.e. "1403*").


Would be interesting to read your comments about that...

Many thanks!


Best regards
Ingo



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] search multiple files

[gecko]

Hi,

I need to write a script that searches multiple db files (made with SQLite)
for a text string. How can I do this?
-- 
View this message in context: 
http://www.nabble.com/search-multiple-files-tf3971138.html#a11272075
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Search on Age, from DOB column

[Allan, Mark]

Thanks to everybody that contirbuted to this thread. I have now implemented 
this functionality and it works well.


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Search on Age, from DOB column

[Samuel R. Neff]

You'll be better off converting the target age back to a date and then
search for the date.  That way SQLite can use an index in your query (it
can't use an index when the filter is on an expression).

HTH,

Sam 

---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Allan, Mark [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 03, 2007 6:57 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Search on Age, from DOB column

Hi,

I need to be able offer the user the ability to search for patients in the
database based on age. i.e. age > 17 or age = 45 etc etc...

I only store the patient DOB in the database however, what is the SQL to
achive this? Can I subract todays date from the DOB and get the number of
years within an SQL string?

The patient table is similar to:-

Patients
{
INTEGER PrimaryKey;
TEXT Surname;
TEXT FirstName;
TIMESTAMP DOB;
...
...
...
}


Thanks in advance for your help.

Mark


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Search on Age, from DOB column

[Pix]

Allan, Mark wrote:

Hi,

I need to be able offer the user the ability to search for patients in the 
database based on age. i.e. age > 17 or age = 45 etc etc...

I only store the patient DOB in the database however, what is the SQL to achive 
this? Can I subract todays date from the DOB and get the number of years within 
an SQL string?

The patient table is similar to:-

Patients
{
INTEGER PrimaryKey;
TEXT Surname;
TEXT FirstName;
TIMESTAMP DOB;
...
...
...
}


Thanks in advance for your help.

Mark
  

SELECT * FROM Patients WHERE date('now', '-17 years') > date(DOB);
SELECT * FROM Patients WHERE date('now', '-45 years') > date(DOB) && 
date('now', '-46 years') < date(DOB);


Here you have other time/date functions:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions


Paolo


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Search on Age, from DOB column

[RB Smissaert]

I am also working with a clinical application, using SQLite and VBA.
I use this function to produce the SQL to convert dates in the  ISO8601
format to an integer age. 

Function ISO8601Date2Age(strField, Optional strAlias As String) As String

   Dim strAS As String

   If Len(strAlias) > 0 Then
  strAS = " AS "
   End If

   ISO8601Date2Age = "case when date(" & strField & ", '+' || " & _
 "(strftime('%Y', 'now') - strftime('%Y', " & strField &
")) || " & _
 "' years') <= date('now') then " & _
 "strftime('%Y', 'now') - strftime('%Y', " & strField &
") " & _
 "else " & _
 "strftime('%Y', 'now') - strftime('%Y', " & strField &
") -1  End" & _
 strAS & strAlias

End Function


You may not be coding in VB, but you will get the idea.

RBS


-Original Message-
From: Allan, Mark [mailto:[EMAIL PROTECTED] 
Sent: 03 May 2007 11:57
To: sqlite-users@sqlite.org
Subject: [sqlite] Search on Age, from DOB column

Hi,

I need to be able offer the user the ability to search for patients in the
database based on age. i.e. age > 17 or age = 45 etc etc...

I only store the patient DOB in the database however, what is the SQL to
achive this? Can I subract todays date from the DOB and get the number of
years within an SQL string?

The patient table is similar to:-

Patients
{
INTEGER PrimaryKey;
TEXT Surname;
TEXT FirstName;
TIMESTAMP DOB;
...
...
...
}


Thanks in advance for your help.

Mark



DISCLAIMER:
This information and any attachments contained in this email message is
intended only for the use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential, and
exempt from disclosure under applicable law.  If the reader of this message
is not the intended recipient, or the employee or agent responsible for
delivering the message to the intended recipient, you are hereby notified
that any dissemination, distribution, forwarding, or copying of this
communication is strictly prohibited.  If you have received this
communication in error, please notify the sender immediately by return
email, and delete the original message immediately.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Search on Age, from DOB column

[Allan, Mark]

Hi,

I need to be able offer the user the ability to search for patients in the 
database based on age. i.e. age > 17 or age = 45 etc etc...

I only store the patient DOB in the database however, what is the SQL to achive 
this? Can I subract todays date from the DOB and get the number of years within 
an SQL string?

The patient table is similar to:-

Patients
{
INTEGER PrimaryKey;
TEXT Surname;
TEXT FirstName;
TIMESTAMP DOB;
...
...
...
}


Thanks in advance for your help.

Mark



DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Search engines and the Sqlite.Org website

[Joe Wilson]

I noticed that one of the sqlite contrib files had 1500 downloads in a single 
day this past week, which is around 100X normal. I'm guessing it was a robot.

I see that Google and many other websites' robots.txt prefer this form:

  Disallow: /contrib/download/

over this form:

  Disallow: /contrib/download

for directories.

Is the trailing slash required to disallow directories for (some) robots?

Also, what does the trailing question mark mean in robots.txt?

  Disallow: /foo/bar?

Is it a wildcard or does it literally mean the '?' character?


 

The fish are biting. 
Get more visitors on your site using Yahoo! Search Marketing.
http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Search engines and the Sqlite.Org website

[John Stanton]

Why are you surprised?  No doubt they are trying to do the job with 
Microsoft quality control and software.


[EMAIL PROTECTED] wrote:

I was looking into the server logs and I discovered a
curious difference between MSN and Google.  Over the past
7 days, the MSN bot has visited www.sqlite.org 114550 times.
The Google bot, on the other hand, has only visited 4070
times.  


In spite of this, when I type "Embedded SQL database" into
Google, www.sqlite.org is the #1 hit.  But www.sqlite.org
appears nowhere on the first 2 pages of results from MSN.

Of the 114550 visits that the MSN bot made to www.sqlite.org,
42320 were to a single URL:

http://www.sqlite.org/cvstrac/captcha

You would think that after a few hundred visits, the bot would
figure out what this page contains and move on

Another 59115 hits from the MSN bot contain the name "luggle.com"
in the URL.  No such page exists on the SQLite website, so I
am puzzled about why MSN finds this such an attractive place
to visit.  Does anybody know what "luggle.com" is?  Here is
a typical luggle.com URL:

   http://www.sqlite.org/cvstrac/wiki/luggle.com/attach_get/241/wiki

In this URL, everything from the "luggle.com" onward is bogus.
So the wiki server ignores it all and serves up the wiki home
page.  So MSN has apparently indexed the SQLite wiki homepage
some 59115 times over the past 7 days.  Any ideas why?  Where
is MSN getting this "luggle.com" stuff?

I consider the behavior of the MSN bot to be abusive.  I'm
sorely tempted to ban the MSN bot from the entire sqlite.org
website.  If SQLite ceased to be indexed by MSN, would that
seriously inconvenience any users?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-