Re: [sqlite] Using SQLite for GIS Application

[Stephen Woodbridge]

Itzchak Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?

You might want to look at SpatiaLite which is a GIS addon to SQLite and 
has most of the capabilities of PostGIS.

http://www.gaia-gis.it/spatialite/

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

Re: [sqlite] Db design question (so. like a tree)

[Stephen Woodbridge]

Here is a relational model that I use for my genealogy. It is in 
postgresql, but it should work fine in SQLite:

All people are stored in the indi table:

CREATE TABLE woodbridge.indi
(
   indi character varying(10) NOT NULL,
   lname character varying(30),
   fname character varying(60),
   title character varying(20),
   lname_sndx character varying(4),
   famc character varying(10),
   sex character(1),
   birt_date character varying(20),
   birt_plac character varying(60),
   chr_date character varying(20),
   chr_plac character varying(60),
   deat_date character varying(20),
   deat_plac character varying(60),
   buri_date character varying(20),
   buri_plac character varying(60),
   refn character varying(20),
   note character varying(10),
   sour character varying(10),
   CONSTRAINT indi_pkey PRIMARY KEY (indi)
)
WITHOUT OIDS;

This is the relationship and indi is placed in a family unit as a child 
of the unit:

CREATE TABLE woodbridge.child
(
   fami character varying(10) NOT NULL,
   seq integer NOT NULL DEFAULT 0,
   indi character varying(10) NOT NULL,
   CONSTRAINT child_pkey PRIMARY KEY (fami, seq)
)
WITHOUT OIDS;

fams is the relationship where two indi's have a spousal relationship

CREATE TABLE woodbridge.fams
(
   indi character varying(10) NOT NULL,
   seq integer NOT NULL DEFAULT 0,
   fami character varying(10) NOT NULL,
   CONSTRAINT fams_pkey PRIMARY KEY (indi, seq)
)
WITHOUT OIDS;

fami is the description of a family unit, ie a set of parents and 
resulting children:

CREATE TABLE woodbridge.fami
(
   fami character varying(10) NOT NULL,
   husb character varying(10),
   wife character varying(10),
   marr_date character varying(20),
   marr_plac character varying(60),
   div character(1),
   div_date character varying(20),
   div_plac character varying(60),
   sour character varying(10),
   CONSTRAINT fami_pkey PRIMARY KEY (fami)
)
WITHOUT OIDS;

There are additional tables for things like notes and photos.

You can see it running here:

http://swoodbridge.com/family/Woodbridge/

It should be pretty easy to convert this to tables for livestock breeding.

-Steve


Francis GAYREL wrote:
> To build a consistent oriented tree we need to associate to the nodes a 
> ranking property such as the birthdate (or any precedence criterion).
> Therefore the ancestor of  someone  is to be selected among  older ones.
> To make the ancestor allocation more easy  the ancestor's list may be 
> filtered on birthdate credibility.
> The ranking property eliminates the circular link concern.
> 
> 
> Jan a écrit :
>> Hi Mark,
>>
>> I think that wont work:
>>
>> Scenario: A calf is born from a mother within your flock but from a 
>> father outside. The father appears for the first time and you are not 
>> able to gather information on his father (or grand-grand father). 
>> Therefore his father is NULL. But later you get the information on his 
>> father and add it to the animal list: The id of the fathers father is 
>> then greater then the id of his grandchild (the calf).
>> I could start the id initially with 10 to allocate <10 ids in 
>> theses cases, but I am unsure if this is a good way to start.
>>
>> Mark Hamburg schrieb:
>>   
>>> One of the questions that I believe was raised but not answered on  
>>> this thread was how to make sure that you don't have circular  
>>> relationships particularly given that SQLite isn't good at scanning  
>>> the tree. If you can control the id's then simply require that the id  
>>> of the child be greater than the id's of the parents.
>>>
>>> Mark
>>>
>>> ___
>>> 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] rtree insert performance

[Stephen Woodbridge]

Oyvind Idland wrote:
> Thanks for responses :)
> 
> Whereby "objects" you mean "rows," no? You are getting upward of 5500
>> sustained inserts per second. That sounds pretty good. That said, are
>> you using transactions? See what difference that makes.
>>
> 
> Yep, I meant rows. Inserting rows in the data table is much faster
> (1.000.000 in 20 secs or so).
> I am of course not expecting that inserts into a tree is as fast as a flat
> table, but a little
> boost wouldnt hurt. Its probably worth mentioning, that I am using a memory
> resident
> db, which I create at startup. The idea is to simply have a fast memory
> cache.
> 
> I am have tried to wrap it inside a transaction, my pattern is basically
> 
> sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, 0);
> sqlite3_prepare_v2( );
> sqlite3_prepare_v2( );
> for(.)
> {
>   sqlite3_bind();
>   sqlite3_step();
>   sqlite3_reset();
>   sqlite3_bind();
>   sqlite3_step();
>   sqlite3_reset();
> }
> sqlite3_finalize();
> sqlite3_finalize();
> sqlite3_exec(db, "COMMIT TRANSACTION;", 0, 0, 0);
> 
> One thing I havent figured out, is, how is a transaction related to prepared
> statements ?
> I mean, using bind(), step() etc, there is a transaction handle, while
> BEGIN/END seems to be
> "global" ?  (thinking of threading etc)
> 
> I'll try to fiddle with Julian's idea.
> 

Transactions are global on a database connection. Once you issue a BEGIN 
TRANSACTION; on a database connection then all work on that same 
connection is part of that transaction untill you COMMIT or ROLLBACK the 
transaction.

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

Re: [sqlite] rtree insert performance

[Stephen Woodbridge]

Oyvind Idland wrote:
> Hi,
> 
> I am fiddling around with the r-tree module a bit, which works great and
> gives the effect I am looking for.
> 
> The only thing is that I wish I could speed up inserts. Populating the
> rtree-index with 1 million objects
> takes about 180 seconds (using prepared statements).
> 
> Is there any trick to speed up the inserts here ?

You should be doing all your inserts inside a transaction.

begin;



commit;

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

Re: [sqlite] Adding index to table makes SELECT much slower. Why?

[Stephen Woodbridge]

James,

I'm not sure I can answer your questions about speed issues other than 
to suggest the you read up on the rtree index which is designed for this 
type of query and should be extremely fast.

-Steve

James Pringle wrote:
> Hi-
> 
>   I am new to sqlite, and am having a puzzling problem.  I have read
> that adding an INDEX to a sqlite table can never make a SELECT
> statement slower.  However, in my case, it seems to be making the
> select statement an order of magnitude slower, which is not what I
> wanted!  What could be going on?
> 
>   I am calling SQLite from python 2.5 from fink useing the sqlite3
> module on OS X 10.5 with 2Gb of memory.  My data base contains 32
> million lines of ocean hydrographic data, stored on disk, with the
> table created with the following statement:
> 
> CREATE TABLE hydro (lat REAL, lon REAL, year INTEGER,
> month INTEGER, day INTEGER, time REAL, cast_id INTEGER,
> depth REAL, T REAL, S REAL, water_depth REAL)
> 
> When I perform a SELECT that returns about 0.6% of the data in the
> database (about 200,000 records)
> 
> SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND depth <= 50.99
> 
> It selects and returns the records in 82 seconds.  I wished to improve
> on this time, so I created an index with the following command:
> 
> CREATE INDEX hydro_indx ON hydro (depth)
> 
> I then tried the SELECT command again, and read the results into
> memory, and it took 717 seconds!?!
> 
> The "depth" field contains many distinct numeric values, with no
> specific value matching more then 0.5% of the data base.  When I DROP
> the index with the command
> 
> DROP INDEX hydro_indx
> 
> The SELECT time returns to about 80 seconds, confirming that it is the
> index which is slowing things down.  What is going on?  I have
> repeated and confirmed these timings.
> 
> I have listened for disk chatter and monitored the system, and it does
> not seem to be thrashing swap, or otherwise becoming unresponsive.
> 
> I have two questions:
> 
> 1) Why is the index making things slower?
> 2) How can I make my SELECT statement faster?  The primary
> selection will be done
>on the "depth" and "water_depth" keys.
> 
> I thank you for your time.
> 
> Cheers,
> Jamie Pringle
> ___
> 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] how to connect to existing db with sqlite3.exe

[Stephen Woodbridge]

I think that the problem is with your understanding of what the 
.databases command does. I could be mistaken, but I thought is only 
listed attached databases. It is not a command to search for possible 
databases that may exist on your file system.

So when you use the command:

sqlite3 foo.db

This command connects to the file foo.db in the current working 
directory. Here is an example:

> [EMAIL PROTECTED]:~/work/navteq-tools$ sqlite3 nt2rgeo-sqlite.db
> SQLite version 3.6.0
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .databases
> seq  name file
> ---  ---  
> --
> 0main /home/woodbri/work/navteq-tools/nt2rgeo-sqlite.db
> sqlite>

If you want to attache other databases to the process you should read 
the SQLite SQL Syntax doc on the web site.

http://www.sqlite.org/lang_attach.html

After you have attached more they will show up here.

-Steve

Ryan Walker wrote:
> Hi Enrique,
> I'm not referring to the syntax.  I know the syntax.  I'm wondering how 
> to connect to a local database with sqlite3.exe.  That is, how do I tell 
> sqlite3.exe where the database file is on my computer?  When I use the 
> .databases command, my database is not listed, so I assume I need to 
> tell sqlite3.exe where it is.
> -Ryan
> 
> 
> Enrique Ramirez wrote:
>> You mean a command like ALTER TABLE? Maybe check the SQL Syntax
>> available to SQLite (http://sqlite.org/lang_altertable.html)
>>
>> On Tue, Sep 30, 2008 at 4:09 PM, Ryan Walker <[EMAIL PROTECTED]> wrote:
>>   
>>> Hi,
>>> I have a database file in windows that I want to connect to with
>>> sqlite3.exe so I can add a column to a table.  Not sure how to so it.
>>> There doesn't appear to be any relevant command when I enter .help.
>>> Thanks,
>>> Ryan
>>>
>>>
>>> --
>>> Ryan Walker
>>> www.WebCommunicate.net:
>>> Insights and resources for effective websites.
>>>
>>> ___
>>> 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] seeking storage design assistance

[Stephen Woodbridge]

It also helps to know how you want to access/manipulate your data and 
what you want to do with it in SQL versus with your application. For 
instance, you could just store all the cad data as a blob, along with 
the attributes in columns.

Then you can fetch the data based on attributes, and manipulate the blob 
within your application.

But if you expect to manipulate the coordinate info within SQL then you 
will need to export some or all of it into columns. For example it might 
be enough to just export the bounding box of each object into an rtree 
index so you can select objects from a region.

Or you may need more detailed coordinate info available.

So, the model you use needs to support the ways you plan to access and 
manipulate the data.

-Steve W

Jeffrey Becker wrote:
> ORM is always a tricky business.  My experience is that unless the
> entity is very very simple, it's often inappropriate to attempt to do
> 1 row = 1 entity mapping.  If things are collections of points, then
> by all means have a points table.  A lot of this type of stuff can be
> made easier by 1) really understanding your object-model first and 2)
> comming up with good mapping strategies for your objects.
> 
> On Tue, Sep 30, 2008 at 2:08 PM, Jeff Godfrey <[EMAIL PROTECTED]> wrote:
>> Hi All,
>>
>> I've got some general db storage design questions that I hope someone
>> can offer some advice on...
>>
>> I have a need to store some CAD-type geometry in a SQLite database, and
>> I'm trying to decide on the best db design.  Specifically, I need to
>> store "entity" data, which will include such things as points, lines,
>> arcs, circles, and text data.  Along with the physical coordinate data
>> that defines each entity, I also need to store various "attributes"
>> about each entity (color, layer, style, font (for a text object), etc)...
>>
>> Ideally, I'd like a single record to tell me everything about a given
>> entity.  So, one option would be to create a unique table for each
>> entity-type in question, with columns as appropriate.  While that makes
>> sense to me on the surface, I also have the need to "step through" the
>> geometry in an ordered fashion.  Obviously, I could keep some kind of
>> "entity order" table with references to each entity and the table it was
>> stored in, but then I don't see a clean way to walk through the geometry.
>>
>> For instance, my "order" table might direct me to first get a line from
>> the line table, then a circle from the circle table, then a text string
>> from the text table.  While this isn't too difficult to accomplish via
>> program logic, it seems a bit "messy", which has me wondering if there
>> might be a better way.
>>
>> Since the table for each unique entity type would contain (at least
>> some) columns unique to the specific entity, I don't think there's a way
>> to combine the tables into a single, ordered view that could be easily
>> "walked", is there?
>>
>> The other thought I had was to create a simple "POINT" table, and store
>> all the points that make up every entity in that one table.  Then, I'd
>> need a way, per entity, to reference which points belonged to the
>> current entity.  So, a line would reference 2 records in the POINT
>> table, an arc would reference 3 POINT records, etc.
>>
>> One obvious drawback to this approach is that now there's not a single
>> record that contains an "entire" entity, as there would be in the first
>> approach.
>>
>> Since I'm not an expert in this arena, I'm hoping that I'm missing an
>> obvious solution.  Any thoughts appreciated.  Also, if you need further
>> clarification on any of the above, feel free to ask.
>>
>> Thanks for any input.
>>
>> Jeff
>> ___
>> 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] Performance/bug in multikey 'group by' in 3.6.2

[Stephen Woodbridge]

D. Richard Hipp wrote:
> On Sep 21, 2008, at 8:51 AM, Russ Leighton wrote:
> 
>> I am interested in ... a way
>> to constraint/control index selection on queries.
>>
> 
> What other SQL database engines have this capability and what syntax  
> do they use?

Richard,

Hope this are useful:

Oracle:
http://www.adp-gmbh.ch/ora/sql/hints/index.html
http://download.oracle.com/docs/cd/E12096_01/books/admintool/admintool_PhysicalSetup32.html

Postgresql:
Does not support it unless it is a very recent addition, but its planner 
occasionally/frequently? makes a poor choice and adding support for 
hints ala Oracle and/or discussions like "Why didn't Postgresql use my 
index?" regularly occur on the user list.

DB2:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/t0005308.htm

MySQL:
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

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

Re: [sqlite] Virtual tables

[Stephen Woodbridge]

Kishor,

I think Marco may want to be able to know how to determine which tables 
in a DB are real tables and which ones below to virtual tables. If you 
want to do something like dump tables from the database, you do not want 
to be dumping all the internally generated real tables. It might be nice 
if there were some way to identify if a given table was:

1) a normal table
2) a virtual table
3) a child of a virtual table

But I'm only guess that this might be what Marco wants? Marco?

-Steve

P Kishor wrote:
> On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
>> Yes but creating a virtual tables involves the creations of other
>>  related tables ...
> 
> Well, the FTSn mechanism does all the extra table voodoo for you, so
> you don't have to be bothered about it. From what it seems like, the
> other magic tables are not virtual tables. In any case, we are not
> advised to mess with them unless we have security clearance.
> 
>> does all the virtual table implementations (fts1,
>>  fts2, fts3) follow the same schema or it is implementation dependent?
> 
> Probably there is some difference from FTS1..3, but I have no
> recollection of 2, and I never implemented 1.
> 
> In any case, the table that is VIRTUAL is the one that you create
> yourself. And, per your original question of how to identify it, well,
> it says so in the schema. There might be a PRAGMA command for it as
> well, but nothing could be clearer than the word VIRTUAL right there
> in the schema.
> 
> 
>>
>>  ---
>>  Marco Bambini
>>  http://www.sqlabs.net
>>  http://www.sqlabs.net/blog/
>>  http://www.sqlabs.net/realsqlserver/
>>
>>
>>
>>
>> On Sep 13, 2008, at 4:02 PM, P Kishor wrote:
>>
>>  > On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
>>  >> What is the best way to identify virtual tables inside a sqlite
>>  >> database?
>>  >
>>  >
>>  > isn't the schema enough? In my world it says
>>  >
>>  > CREATE VIRTUAL TABLE ...
>>  >
>>  >>
>>  >> Thanks a lot.
>>  >> ---
>>  >> Marco Bambini
>>  >> http://www.sqlabs.net
>>  >> http://www.sqlabs.net/blog/
>>  >> http://www.sqlabs.net/realsqlserver/
>>  >>
>>
>>> ___
>>  > 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] Proposed removal of (mis-)feature

[Stephen Woodbridge]

I'm +1 on this change.

While I have not run into it as a feature or mis-feature, I would concur 
that it is ambiguous and problematic. A simple clear syntax benefits 
everyone.

BTW, Thanks for all you effort to produce this wonder software.

Best regards,
   -Steve W

D. Richard Hipp wrote:
> String literals in SQL are suppose to be enclosed in single-quotes -  
> Pascal-style.  Double-quotes are used around table and/or column names  
> in cases where the name would otherwise be a keyword or when the name  
> contains non-standard characters.
> 
> But SQLite tries to be flexible and accommodating.  To this end, it  
> accepts some non-standard quoting mechanisms:
> 
> 1.  Names can be enclosed in [...] for compatibility with Access  
> and SQLServer.
> 2.  Names can be enclosed in grave accents for compatibility with  
> MySQL.
> 3.  Double-quoted names fall back to being string literals if  
> there is no matching table or column name.
> 
> In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
> leads to all kinds of confusion.  For example, if double-quotes are  
> being used correctly (which is to say to quote table or column names)  
> but a misspelling occurs in the name, the token reverts to being a  
> string literal rather than throwing an error.  Or if a double-quoted  
> string really is being used as a string literal, but later a new  
> column is added to a table that has the same name as the string text,  
> the string literal will suddenly take on the value of the column.  It  
> seems like we have one or two problem reports per month on this  
> mailing list that involve people using double-quoted names where they  
> should be using single-quoted string literals instead.
> 
> So I'm giving some thought to removing feature (3) above and  
> disallowing double-quoted string literals.  My concern is, though,  
> that this might break many existing applications.
> 
> What opinion do you, gentle users, have of this matter?
> 
> D. Richard Hipp
> [EMAIL PROTECTED]
> 
> 
> 
> ___
> 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] Loading a existing database 100% into memory

[Stephen Woodbridge]

Stephen Oberholtzer wrote:
> On Wed, Aug 6, 2008 at 6:26 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
> 
>> Good afternoon list,
>>
>> I would like to load my current database file completely into memory,
>> mostly as an experiment to check SQLite's maximum memory footprint,
>> however searching through the documentation I can only find references
>> about how to create new databases that are completely memory resident.
>> Is there a way to do this?  I'm currently using the SQlite console
>> application for my testing if that makes a difference.
> 
> 
> What, exactly, is it you're after?  I can load a SQLite database 100% into
> memory quite quickly:
> 
> int fd = open("sqlitedb.dat");
> struct stat info;
> fstat(fd, );
> char *buf = malloc(info.st_size);
> read(fd, buf, info.st_size);
> 
> I find it extremely unlikely that this is what you want (although it might
> be an interesting academic exercise to make a VFS port of SQLite that uses
> memory arrays for read/write ops.)
> 
> At the other end of the spectrum, you could just dump the entire database on
> disk and then insert all the data into a :memory: database.  However, this
> doesn't seem like it would be very useful, either.
> 
> This sounds like an XY problem.  What are you really trying to accomplish?
> What constraints are preventing you from simply using an on-disk database?
> 

Another interesting option might be to mmap the DB file so you use the 
OS virtual memory paging to map the file to memory as you need access to 
it. But this probably has the downside that writes are not sync'd to 
disk so in the event of a crash you out of luck, but that is the case 
with any memory DB. The upside is that when you shutdown your DB is 
sync'd to disk and the OS paging is pretty efficient.

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

Re: [sqlite] Creating Indexes

[Stephen Woodbridge]

Jeffrey Becker wrote:
> I have a table 'SiteMap' defined as :
> 
> Create Table SiteMap
> (
>   NodeID blob not null PRIMARY KEY,
>   Title text NOT NULL UNIQUE,
>   Url text NOT NULL
> );
> 
> I'd like to index on the node's parent value as defined by the
> expression ancestor(NodeID,1).  'ancestor' being a user defined
> function.
> However sqlite doesnt permit indicies on expressions, only columns.
> Why is this? More importantly what's my best bet on achieveing
> something similar?

Create Table SiteMap
(
NodeID blob not null PRIMARY KEY,
ParentID blob not null,
Title text NOT NULL UNIQUE,
Url text NOT NULL
);

Then index on (ParentID, NodeID) or something like that.

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

Re: [sqlite] Clone SQLite databases

[Stephen Woodbridge]

csabi81 wrote:
> Hi everyone
> 
> I want to clone a database: copy all data from original database to the
> destination database with a condition, so not all the entries need to be
> copied. I have tried the following:
> Obtain SQL from mastertable and create the tables in the new database, and
> using INSERT to copy all data with a WHERE. My problem is that the
> destination file size became very small; original was 160KB the destination
> is 55KB, I have deleted only a few entries. When I opened each database with
> a SQLite Browser it seems everything OK, but I do not understand why the
> filesize became so small?
> Am I loosing something to copy, or create?
> 
> Tx for any help.
> 
> P.S> sorry for my english :P

Do you have indexes on the original DB that you have not added to the 
new DB?

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

Re: [sqlite] Does sqlite support stored procedure?

[Stephen Woodbridge]

John Stanton wrote:
> Adding Javascript to Sqlite as a stored procedure language was a fairly 
> simple operation.  Try it if you need stored procedures.

This sounds really interesting.

How to you make access to the sqlite3 api in javascript? Would you need 
to? Seems like you would need to at least provide some interface to the 
DB functions so you can do things like INSERT, UPDATE, DELETE, SELECT, 
and step through the results, etc.

-Steve

> BareFeet wrote:
>> Hi John,
>>
>>
>>> I would like to know if SQLite supports stored procedures.
>>
>> Technically, no it doesn't.
>>
>> For what purpose do you want to store procedures?
>>
>> You can store some procedures in triggers, if you want to have SQLite  
>> trigger a task when some data is changed.
>>
>> You can simply create a "Procedures" table like this:
>>
>> create table "Procedures" (Name, SQL);
>>
>> and populate it with SQL procedures. You can call those procedures  
>> later from within your program and sqlite3 command line and execute  
>> them.
>>
>> Tom
>> BareFeet
>>
>>   --
>> Comparison of SQLite GUI applications:
>> http://www.tandb.com.au/sqlite/compare/
>>
>> ___
>> 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] db vs shell

[Stephen Woodbridge]

I'm seeing a similar speed different with the 3X performance difference:

[EMAIL PROTECTED]:~/work$ true && ( set -x
 > sqlite3 sample.db 'create table bar (foo text)'
 > seq -w 1 200 | sed 's/^/id/' > list.txt
 > sqlite3 sample.db '.imp "list.txt" "bar"'
 > time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | 
uniq | wc -l
 > time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
 > )
+ sqlite3 sample.db 'create table bar (foo text)'
+ seq -w 1 200
+ sed 's/^/id/'
+ sqlite3 sample.db '.imp "list.txt" "bar"'
+ uniq
+ sort
+ uniq
+ sqlite3 sample.db 'select foo from bar ; '
+ wc -l
110
real 7.05
user 8.71
sys 0.36
+ sqlite3 sample.db 'select count(distinct foo) from bar ; '
110
real 21.53
user 21.03
sys 0.24

SQLite version 3.6.0

Linux carto 2.6.15-1-em64t-p4-smp #2 SMP Tue Mar 7 08:19:39 UTC 2006 
x86_64 GNU/Linux

-Steve

Robert Citek wrote:
> On Tue, Jul 29, 2008 at 2:35 AM,  <[EMAIL PROTECTED]> wrote:
>> On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote:
>>> $ sqlite3 -version
>>> 3.4.2
>> On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell
>> pipe. Could you tell us more about the contents of your database?
> 
> The column contains a list of text items.  This script demonstrates
> the phenomenon:
> 
> true && ( set -x
> sqlite3 sample.db 'create table bar (foo text)'
> seq -w 1 200 | sed 's/^/id/' > list.txt
> sqlite3 sample.db '.imp "list.txt" "bar"'
> time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc 
> -l
> time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
> )
> 
> Output:
> 
> + sqlite3 sample.db 'create table bar (foo text)'
> + seq -w 1 200
> + sed 's/^/id/'
> + sqlite3 sample.db '.imp "list.txt" "bar"'
> + sqlite3 sample.db 'select foo from bar ; '
> + uniq
> + sort
> + uniq
> + wc -l
> 200
> real 3.25
> user 3.71
> sys 0.47
> + sqlite3 sample.db 'select count(distinct foo) from bar ; '
> 200
> real 22.48
> user 20.98
> sys 0.28
> 
> Regards,
> - Robert
> ___
> 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] FTS statistics and stemming

[Stephen Woodbridge]

Scott Hess wrote:
> On Sat, Jul 26, 2008 at 1:28 PM, Stephen Woodbridge
> <[EMAIL PROTECTED]> wrote:
>> Alexey Pechnikov wrote:
>>> I'm know that ispell, myspell, hunspell and trigrams are used in PostgreSQL
>>> FTS. A lot of languages are supported this. And soundex function useful for
>>> morphology search if to write word by latin alphabet (transliteration by
>>> replace each symbol of national alphabet by one or more latin):
> 
>>> There is stemming in Apache Lucene, Sphinx (included morphology by soundex)
>>> and Xapian too.
>>>
>>> Are these futures planned to be in SQLIte FTS?
>> Well, I will leave the question of plans to Scott Hess the FTS developer
>> to answer.
> 
> Unfortunately, my interests don't really run towards implementing
> useful new stemmers.  I mean, I could, but I'm unlikely to do a good
> job unless I'm doing it because it scratches some engineering itch I
> have.  I tend to have more interest in infrastructure-y things, like
> how to safely encode/decode data structures.  I know this is an
> unsatisfactory answer :-).

Scott,

This makes perfect sense to me. The stemmers are all written and C 
source is available from the snowball site for anyone that needs one in 
another language, and the existing porter and simple stemmers provide an 
adequate examples of how to integrate additional stemmers into sqlite so 
there is probably no need for you to mess with them.

I think that more interesting infrastructure stuff might be along the 
lines of:

1) looking at how postgresql has integrated fts into it which make is 
very flexible and extensible
2) looking into ways to do fast fuzzy FTS with scoring of results
3) looking at a simple extension that would allow an FTS column of 
soundex tokens to be automatically built from the source documents.

create virtual table mytable using fts3
   (sndx tokenize soundex on doc, doc tokenize porter);

select docid from mytable where sndx MATCH 'list of words';

So the idea would be that doc is tokenized using porter, like today, and 
the sndx column is build from soundex tokens generated from the doc 
column tokens. And on the select it would be smart enough to compute the 
soundex tokens for 'list of words' before doing the query if the column 
is soundex encoded. The syntax should be fixed to something appropriate, 
I just wanted to get the idea across.

Anyway, I'm sure that whatever you decide to do, it will be useful and 
helpful to the community and I for one really appreciate your efforts.

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

Re: [sqlite] FTS statistics and stemming

[Stephen Woodbridge]

Alexey Pechnikov wrote:
> Hello!
> 
> В сообщении от Saturday 26 July 2008 21:37:19 Stephen Woodbridge написал(а):
>> I have thought a lot about these issues and would appreciate any
>> thoughts or ideas on how to implement any of these concepts or others
>> for fuzzy searching and matching.
> 
> I'm know that ispell, myspell, hunspell and trigrams are used in PostgreSQL 
> FTS. A lot of languages are supported this. And soundex function useful for 
> morphology search if to write word by latin alphabet (transliteration by 
> replace each symbol of national alphabet by one or more latin):
> 
> sqlite> select soundex('Moskva');
> M210
> sqlite> select soundex('Moscva');
> M210
> sqlite> select soundex('Mouscva');
> M210
> sqlite> select soundex('Mouskva');
> M210
> sqlite> select soundex('moskva');
> M210
> 
> Note: compile SQLite with -DSQLITE_SOUNDEX=1
> 
> There is stemming in Apache Lucene, Sphinx (included morphology by soundex) 
> and Xapian too.
> 
> Are these futures planned to be in SQLIte FTS?

Well, I will leave the question of plans to Scott Hess the FTS developer 
to answer.

I just read a bunch of the FTS overview documents for Postgresql, which 
I use a lot for other projects and I like the way they have things 
broken down and integrated with the database. I haven't tried 8.3 yet, 
but it is nice to see the FTS is now part of the main distribution.

http://www.sai.msu.su/~megera/postgres/fts/doc/fts-history.html
http://www.sai.msu.su/~megera/postgres/fts/doc/fts-basic.html
http://www.sai.msu.su/~megera/postgres/fts/doc/fts-dict.html

I think you can add dictionaries as stemmers the same way you would add 
a stemmer to SQLlite. Look at the code in the SQLite source tree:

ext/fts3/fts3_porter.c
ext/fts3/fts3_tokenizer.[ch]
ext/fts3/fts3_tokenizer1.c

As far as other lexemes, there is nothing stopping you from creating 
your FTS table with additional lexeme columns that you can populate with 
the appropriate lexemes from the full text column. Of course, you have 
to generate the lexemes yourself and add them as the text for that column.

For example if you wanted to have a soundex column, you could preprocess 
you document through the simple or porter stemmer and then take each of 
the tokens and generate the soundex key for them and concatenate them 
all with a separating space and then use that as the contents for the 
soundex lexeme column. Then to do a query, you would tokenize the 
incoming words, generate the soundex keys and to an FTS search on that 
column. It would obviously be nicer if this was built into the existing 
FTS engine, but you could do it today with some additional programming.

As I said before, I will leave questions of planning for FTS up to 
Scott. I have read through his fts3 code, but I confess I do not 
understand how it all works, but a relatively small amount of code it 
works impressively well.

All the best,
   -Steve
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS statistics and stemming

[Stephen Woodbridge]

Alexey Pechnikov wrote:
> Hello!
> 
> And how about stemming? Can I'm using ispell, myspell, hunspell? And trigrams?

Hello Alexey,

There are currently two stemmers available:

simple - which just breaks the input into tokens based on spaces and 
punctuation that is first converted to spaces.

porter - the porter stemmer which is based on English and collapses the 
extensions of many words to the base word, like: runs, runner, running 
to run (not sure if it works for this sequence but you get the idea).
http://snowball.tartarus.org/algorithms/english/stemmer.html

You can code additional stemmers and register them for use with the fts 
system. You might want to look at:
http://snowball.tartarus.org/
which has additional stemmers written in snowball for many languages and 
these have C code that can be adapted for use with SQLite fts.

I'm not familiar with the use of ispell, myspell, hunspell are all 
spelling checkers, I'm not sure if they have built-in stemmers that 
could be adapted. The idea behind the stemmer is to reduce variants of a 
word to its root, so that there is a higher probability of matching. For 
example if someone searches for the word "geocoder" it is reasonable to 
assume they would also be interested in documents with the word 
"geocode" or "geocoding".

Trigrams I think are more valuable for fuzzy match but I do not think 
they can be used for FTS because of the way it works. For example if you 
want to search for "fuzy" and hope to find "fuzzy" using trigrams, I 
assume you would do something like this:

fuzy => _fu, fuz, uzy, zy_

You would then search for all documents that had this set of trigrams, 
but a document with fuzzy in it would have:

fuzzy => _fu, fuz, uzz, zzy, zy_

So while 3 of the trigrams match, you would not get a match for "uzy" so 
the document would not get selected.

This is in fact where a spelling checker/correcter might be useful if it 
could check that fuzy was misspelled and correct it, but most spelling 
correction needs human interaction to validate the change and the 
interaction would need to be done at the application layer and probably 
not at the database layer. This also begs the question of searching for 
documents the have spelling errors in them.

One idea I had with respect to fuzzy search using FTS and trigrams that 
if it could search documents that had some percentage of the trigrams like:

MATCH 'fuzy' AT 75%  (3 of 4 trigrams)
MATCH 'fuzy' AT 50%  (2 of 4 trigrams)

then it would find all documents that had 3 of the 4 trigrams present 
and rank the results based on number of matched trigrams. Obviously for 
larger words or multiple words this would work better.

This would provide some for a fuzzy search capability given the existing 
FTS machinery and the cost of significantly more index entries and may 
be additional search time because of the additional entries. This would 
also require some additional post processing to filter out documents 
that were selected because trigrams from multiple unrelated words 
matched your selection set. I'm not convinced this would work in reality 
because bigram/trigram comparison works better for fuzzy match of words 
and the pollution of additional terms from other words in a document may 
not make it useful.

I have thought a lot about these issues and would appreciate any 
thoughts or ideas on how to implement any of these concepts or others 
for fuzzy searching and matching.

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

Re: [sqlite] Severe documentation bug in sqlite3_close leading to crash

[Stephen Woodbridge]

Hartwig Wiesmann wrote:
> The latest sqlite3_close(sqlite3*) documentation states:
> 
> Applications should finalize all prepared statements and close all  
> BLOB handles associated with the sqlite3 object prior to attempting to  
> close the object. The sqlite3_next_stmt() interface can be used to  
> locate all prepared statements associated with a database connection  
> if desired. Typical code might look like this:
> 
> sqlite3_stmt *pStmt;
> while( (pStmt = sqlite3_next_stmt(db, 0))!=0 ){
>  sqlite3_finalize(pStmt);
> }
> This is dangerous and does NOT always work!
> Actually, this causes a terrible bug in the RTree module: the rtree  
> module stores dynamically 9 prepared statement (see rtreeSqlInit). If  
> now all prepared statements are closed before sqlite3_close is called  
> these statements are also finalized.
> Now, sqlite3_close is called. This call also terminates the rtree  
> module. But this module does not know that the prepared and stored  
> statements (pointers to the previously prepared statements) have  
> already been finalized and do not exist anymore. So, it tries to clean  
> up the not anymore existing part again and crashes!
> 
> Any workarounds are welcome!!

Seems like the sqlite3_finalize() should check if the pointer is null 
before destroying it, and after destroying it set the freed pointers to 
NULL so that they are not destroyed again. Or do the equivalent if they 
are not all pointers.

Then again maybe I should not comment without looking at the code :) 
that works pretty darn well most all the time for me.

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

Re: [sqlite] FTS statistics and stemming

[Stephen Woodbridge]

Scott Hess wrote:
> Sorry for the long delay - I was out-of-town, and on return my email
> box had seemingly grown without bound.  So I've been working my LIFO
> way through giving each email it's five minutes of fame, and here we
> are...

Scott,

The long delay was well worth the information below and the new test 
functions. I hear I should not expect these to be around in the future 
and that hacking the low level code and structure is subject to change 
on any commit. This is good enough for now and I can use this for my 
initial analysis of my data. Luckily my data is relatively static.

An alternative approach that I thought of was to reparse all the 
documents through the stemmer and take the output tokens and add them 
and counts directly to a stats table. This approach might might be more 
stable in the long term, but is probably slower than using the debug 
routines, but I do not think this is a time critical step in the first 
place. You debug routines do provide some additional information that my 
approach does not provide.

I will definitely give your code a try.

Thanks,
   -Steve

> On Sat, Jul 5, 2008 at 9:30 PM, Stephen Woodbridge
> <[EMAIL PROTECTED]> wrote:
>> My particular use for FTS is not document text but addresses and it
>> would be very useful if there were a way to analyze the FTS index to get
>> statistics on the keys. I could then use this information to make a
>> custom parser/stemmer that could eliminate stop words.
>>
>> For example, Rd, road, st, street, etc would be overly represented and
>> not very discriminating, so these should/could be removed. Ideally this
>> list should be generated based on loading the data, the analyzing the
>> index, then updating the stemmer to remove the new stop works and again
>> analyzing and adjusting if needed.
>>
>> Is this possible? How?
>>
>> If I had to code this where would I start, I would like to get a list of
>> the keys and a count of how many rows that a given key is represented
>> in. I assume a token that is represented multiple times in a document is
>> represented by a list of offsets, so I can also get a count of the
>> number of time it show in each document somehow. I think I have figured
>> this much out by reading all the posts on FTS in the archive.
> 
> You're (maybe) in luck!  I recently added an optimize() function to
> fts3, which merges all segments into one.  As part of this, I added
> dump_terms() and dump_doclist() functions, only available in test
> mode, so that I could add tests that make assertions about the state
> of the fts3 index.  dump_terms() can be used to dump the terms from
> the fts3 index, while dump_doclist() can be used to dump the hits.
> It's probably not a perfect match for what you want to do, but you
> could probably start with something like:
> 
>   SELECT dump_terms(t) FROM t LIMIT 1;
> 
> This will generate a space-separated list of unique terms from t's
> index.  Not so useful if your tokenizer allows spaces in words, but
> this is an index-debugging function.  Given those words, you can pass
> each term into:
> 
>   SELECT dump_doclist(t, ?) FROM t LIMIT 1;
> 
> to get the doclist.  Depending on your schema, the doclist will look
> something like:
> 
> [2 0[3] 1[2 9 14]] [12 1[1 4]]
> 
> The inner [N N N] sequences are the positions of hits in a column,
> there can be 1 or more, they should be in order.  The number before
> the inner [] is the column number in the row, there can be one for
> each column, if there are none this is a delete document (shouldn't
> see that case in the dump_doclist() call described above).  The number
> after the opening of the outer [] is the docid of the doclist.  There
> can be zero or more doclists.  The above has 1 hit for column 0 of
> docid 2, 3 hits for column 1 of docid 2, and 2 hits for column 1 of
> docid 12.
> 
> So, you could probably either compile fts3.c with SQLITE_TEST and hack
> something together in your code, or go in and hack your own function
> using the dump_terms() and dump_doclist() implementations as examples
> of how to walk the index.  It would be pretty quick and easy to write
> something to analyze an fts3 index using tclsqlite3, I think.
> 
> NOTE: These functions are for testing purposes, and are not intended
> for production use.  They scan the entire index, and zero effort has
> been spent on making them efficient in any way.  If I think of a
> different/better way to write the tests, I might just remove them.
> It's possible similar functionality might be exposed by a future
> iteration of fts, but these functions are not an early version of
> that.
> 
> -scott
> ___
> 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] Ultimate noob question: What do I do to reference another table?

[Stephen Woodbridge]

LMcLain wrote:
> Thanks for the replay Igor!
> 
> I would heartily agree about getting a textbook.  Do you have one that you
> could recommend?

I like "The definitive Guide to SQLite" by Michael Owens, it is 
excellent, except the index which sucks. It is a very good read and 
covers a lot of the basics of SQL, rational theory, SQLite design, 
usage, C-api, and other stuff.

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

Re: [sqlite] Does changing the db change result sets?

[Stephen Woodbridge]

Igor Tandetnik wrote:
> "Nikolaus Rath" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> What happens if
>>
>> 1. I prepare and execute a select statement
>> 2. I retrieve a couple of rows
>> 3. I execute a new query that would change the result of the
>>query in 1
> 
> On the same connection, I assume.
> 
>> 4. I continue to retrieve the results of 1)
>>
>> Will I get the results as if step 3 hasn't happened, is the result
>> undefined, or will my result set somehow be updated?
> 
> You may or may not see the changes introduced by step 3. E.g. if step 3
> updates a record that you've already visited, you naturally won't visit
> it again and thus won't see the changes. But if it updates a record you
> haven't visited yet, when you get to it you will see the new data.

Hmmm, that does not seem right, but what do I know. The reason I say 
this is because if the update change a column value on a record the 
would cause it to ne not selected or it changes some other that had not 
been selected before but would be after the change I'm not sure how 
these would magically get included or excluded.

I assume the when you make a query your results on in a set of data the 
is private to your session/connection and that other transactions on the 
database can not change you private data. But I'm not an expert on this 
stuff.

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

[sqlite] [Fwd: FTS statistics and stemming]

[Stephen Woodbridge]

Hello Scott et al,

I posted this to the list a while back but never got a response from 
anyone. It would be great if you could take 5 mins to read and respond 
to this. I have been reading through the fts3 code, but there is a lot 
of it and I'm not sure how it works from a big picture let along in any 
of its details. My assumption is that there are no stats at the moment, 
but I'm not opposed to writing some code the cycles through the indexes 
and tables to compute some stats for my needs or even the general case 
if I had some pointers on:

1) where are the words indexed
2) how can I cycle through the words in C to build some stats

pointers to existing code would be fine and I can focus reading that code.

So from reading through the various fts documents and posts and the code 
I think I understand the there is a blob with a structure something like:

word [doc_id, offset, offset, ...], [doc_id, offset, ...], ...

is this correct? where is this stored?

Best regards,
   -Stephen Woodbridge

 Original Message 
Subject: [sqlite] FTS statistics and stemming
Date: Sat, 05 Jul 2008 23:30:55 -0500
From: Stephen Woodbridge <[EMAIL PROTECTED]>
Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>

Hi,

First let me say that FTS3 is really awesome. This is my first
experience playing with FTS and it works very nicely with the PORTER
stemming.

My particular use for FTS is not document text but addresses and it
would be very useful if there were a way to analyze the FTS index to get
statistics on the keys. I could then use this information to make a
custom parser/stemmer that could eliminate stop words.

For example, Rd, road, st, street, etc would be overly represented and
not very discriminating, so these should/could be removed. Ideally this
list should be generated based on loading the data, the analyzing the
index, then updating the stemmer to remove the new stop works and again
analyzing and adjusting if needed.

Is this possible? How?

If I had to code this where would I start, I would like to get a list of
the keys and a count of how many rows that a given key is represented
in. I assume a token that is represented multiple times in a document is
represented by a list of offsets, so I can also get a count of the
number of time it show in each document somehow. I think I have figured
this much out by reading all the posts on FTS in the archive.

Thanks,
-Steve
___
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] partial search matches using FTS

[Stephen Woodbridge]

Rael Bauer wrote:
> Hi,
>  
> Is it possible to obtain partial matches to a search term using the fts 
> search?
>  
> E.g. field data contains: apple
> Search term: app
> will return a match (optionally with match info).
>  
> If not, please add this as a feature request :).

MATCH 'app*'

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

Re: [sqlite] patch to allow integer rtree keys

[Stephen Woodbridge]

Dan wrote:
> On Jul 12, 2008, at 2:42 AM, Steve Friedman wrote:
> 
>>
>> Filip Navara wrote:
>>> how about actually attaching the patch? :)
>>>
>>> - Filip
>>>
>>> On Fri, Jul 11, 2008 at 9:23 PM, Steve Friedman  
>>> <[EMAIL PROTECTED]> wrote:
 I've just started using the rtree extension, and have found that  
 the 32-bit
 float for the range keys is not appropriate for me.  Please find  
 attached a
 patch for rtree.c (based on v1.5) that allows for int -OR-  
 unsigned int -OR-
 float operation.
> 
> What kind of advantages does using int over float have here?
> 
> With a little work it might be possible to select int or float at
> runtime. Do other people who know about such things think that this
> would be a good option to have?

Dan,

I think the need for integer support is to avoid floating point rounding 
errors that might cause you to miss a key otherwise.

I think this would be a nice feature to have. I think it should be 
implemented at runtime because if I ever have an application that need 
both say time (int) and spatial rtrees (floats) then it puts me into a 
problem of not being able to support both in a single build.

-Steve

 Steve Friedman
>> Not sure where it got deleted (since my inbox shows the attachment).
>> Included inline...
>>
>> --- rtree.c 2008-07-11 15:04:42.0 -0400
>> +++ rtreemod.c  2008-07-11 15:04:31.0 -0400
>> @@ -149,13 +149,36 @@
>> RtreeConstraint *aConstraint; /* Search constraints. */
>>   };
>>
>> +#if defined( SQLITE_RTREE_TYPE_INT)
>> +typedef int ConstraintType;
>> +# define sqlite3_result_ConstraintType  sqlite3_result_int
>> +# define sqlite3_value_ConstraintType(x)  ((int) sqlite3_value_int 
>> ((x)))
>> +# define sqlite3_snprintf_ConstraintType( a, b, c) \
>> + sqlite3_snprintf( (a), (b), " %d", (c))
>> +
>> +#elif defined(SQLITE_RTREE_TYPE_UINT)
>> +typedef u32 ConstraintType;
>> +# define sqlite3_result_ConstraintType  sqlite3_result_int64
>> +# define sqlite3_value_ConstraintType(x)  ((u32)  
>> sqlite3_value_int64((x)))
>> +# define sqlite3_snprintf_ConstraintType( a, b, c) \
>> + sqlite3_snprintf( (a), (b), " %u", (c))
>> +
>> +#else
>> +typedef float ConstraintType;
>> +# define sqlite3_result_ConstraintType  sqlite3_result_double
>> +# define sqlite3_value_ConstraintType(x)  ((float)
>> sqlite3_value_double((x)))
>> +# define sqlite3_snprintf_ConstraintType( a, b, c) \
>> + sqlite3_snprintf( (a), (b), " %f", (double) (c))
>> +#endif
>> +
>> +
>>   /*
>>   ** A search constraint.
>>   */
>>   struct RtreeConstraint {
>> int iCoord;   /* Index of constrained  
>> coordinate */
>> int op;   /* Constraining operation */
>> -  float rValue; /* Constraint value. */
>> +  ConstraintType rValue;/* Constraint value. */
>>   };
>>
>>   /* Possible values for RtreeConstraint.op */
>> @@ -198,7 +221,7 @@
>>   */
>>   struct RtreeCell {
>> i64 iRowid;
>> -  float aCoord[RTREE_MAX_DIMENSIONS*2];
>> +  ConstraintType aCoord[RTREE_MAX_DIMENSIONS*2];
>>   };
>>
>>   #define MAX(x,y) ((x) < (y) ? (y) : (x))
>> @@ -211,14 +234,14 @@
>>   static int readInt16(u8 *p){
>> return (p[0]<<8) + p[1];
>>   }
>> -static float readReal32(u8 *p){
>> +static ConstraintType readReal32(u8 *p){
>> u32 i = (
>>   (((u32)p[0]) << 24) +
>>   (((u32)p[1]) << 16) +
>>   (((u32)p[2]) <<  8) +
>>   (((u32)p[3]) <<  0)
>> );
>> -  return *(float *)
>> +  return *(ConstraintType *)
>>   }
>>   static i64 readInt64(u8 *p){
>> return (
>> @@ -243,9 +266,9 @@
>> p[1] = (i>> 0)&0xFF;
>> return 2;
>>   }
>> -static int writeReal32(u8 *p, float f){
>> +static int writeReal32(u8 *p, ConstraintType f){
>> u32 i;
>> -  assert( sizeof(float)==4 );
>> +  assert( sizeof(ConstraintType)==4 );
>> assert( sizeof(u32)==4 );
>> i = *(u32 *)
>> p[0] = (i>>24)&0xFF;
>> @@ -543,7 +566,7 @@
>>   /*
>>   ** Return coordinate iCoord from cell iCell in node pNode.
>>   */
>> -static float nodeGetCoord(
>> +static ConstraintType nodeGetCoord(
>> Rtree *pRtree,
>> RtreeNode *pNode,
>> int iCell,
>> @@ -721,8 +744,8 @@
>> for(ii=0; iinConstraint; ii++){
>>   RtreeConstraint *p = >aConstraint[ii];
>>
>> -float cell_min = cell.aCoord[(p->iCoord>>1)*2];
>> -float cell_max = cell.aCoord[(p->iCoord>>1)*2+1];
>> +ConstraintType cell_min = cell.aCoord[(p->iCoord>>1)*2];
>> +ConstraintType cell_max = cell.aCoord[(p->iCoord>>1)*2+1];
>>   assert( cell_min<=cell_max );
>>
>>   switch( p->op ){
>> @@ -769,7 +792,7 @@
>> nodeGetCell(pRtree, pCursor->pNode, pCursor->iCell, );
>> for(ii=0; iinConstraint; ii++){
>>   RtreeConstraint *p = >aConstraint[ii];
>> -float cell_val = cell.aCoord[p->iCoord];
>> +ConstraintType cell_val = cell.aCoord[p->iCoord];
>>   int res;
>>   switch( p->op ){
>> case RTREE_LE: res = 

Re: [sqlite] Fuzzy Matching

[Stephen Woodbridge]

OK, thanks for that. I had seen the references to the n-gram stuff and 
just started reading about them and not my head is ready to explode! 
Between n-grams, bit vectors, Bloom filters, perfect hashes, lots of 
academic papers spouting fancy equations and statistics, I'm not sure 
this is gelling into any course of action for me. While this technology 
seems very cool, I still get the sense that you have to scan each 
documents n-gram vector against the query n-gram vector to get a set of 
potential documents that might have what I want in them. Or am I missing 
something? What? While this type of search is probably faster then some 
alternatives, it does not lend itself to the fundamentals of RDBMS or 
indexed searches.

I would appreciate any thoughts on how to proceed with this.

Levenshtein edit distance is a good way to score the results of a fuzzy 
match. I have also used metaphone and double metaphone and soundex in 
the past for fuzzy searching.

-Steve

Harold Wood wrote:
> I cant go into too much detail because of my current job, but for
> fuzzy matching levenstien isnt very good, you need to try looking
> into ngram matching techniques, it is absolutely awesome in reducing
> over/under matches.
> 
> Woody
> 
> --- On Sat, 7/5/08, Stephen Woodbridge <[EMAIL PROTECTED]>
> wrote:
> 
> From: Stephen Woodbridge <[EMAIL PROTECTED]> Subject: Re:
> [sqlite] Fuzzy Matching To: "General Discussion of SQLite Database"
> <sqlite-users@sqlite.org> Date: Saturday, July 5, 2008, 11:24 PM
> 
> Stephen Woodbridge wrote:
>> I would be interested in having something like this also.
>> 
>> What I don't understand in your approach is how you compute the 
>> (Levenstein) distance during a search. It seems like you have a
>> fixed set of tokens from your document text and these are indexed.
>> Then you have a query token the you want to compare to the index
>> based on some fuzzy distance. Since every query can be different I
>> think you have to compute the distance for every key in the index?
>> that would require doing a full index scan.
>> 
>> If there ware a function that you could run a token through that
>> would given you that tokens "location" in some space then you could
>> 
> generate a
>> similar "location" for the query token and then use the rtree
> and
>> distance. I'm not aware of any such functions, but my expertise is
> more
>> in GIS the search searching.
> 
> Hmmm, that was supposed to say text searching.
> 
>> Thoughts?
>> 
>> Best, -Steve
>> 
>> Martin Pfeifle wrote:
>>> Hi, I think there is nothing available except FTS. Doing a full
>>> table scan and computing for each string the (Levenstein)
>>> distance to the query object is too time consuming. So what I
>>> would like to see is the implementation of a generic metric index
>>> which needs as one parameter a metric distance function. Based on
>>> such a distance function you could then do similarity search on
>>> any objects , e.g. images, strings, etc. One possible index would
>>> be the M-tree (which you can also organize relational as it was
>>> done with the R*-tree). The idea is that you have a hierarchical
>>> index and each node is represented by a database  object o and a
>>> covering radius r reflecting the maximal distance of all objects
>>> in that subtree to the object o. If you do a range query now, you
>>> compute the distance of your query object to the object o. If
>>> this distance minus the coverage radius r is bigger than your
>>> query range you can prune that subtree. You can either implement
>>> such a similarity module as an own extension similar toFTS or the
>>> Spatial module, or integrate it into FTS and use it only for
>>> strings. Personally, I need the second solution because I'd like
>>> to do full and fuzzy text search. Are
> there
>>> any plans to implement something like this, if yes, I would like
>>> to take part in such a development. . Best Martin
>>> 
>>> 
>>> 
>>> 
>>> - Ursprüngliche Mail  Von: Alberto Simões 
>>> <[EMAIL PROTECTED]> An: General Discussion of SQLite Database 
>>> <sqlite-users@sqlite.org> Gesendet: Donnerstag, den 3. Juli
> 2008,
>>> 21:52:05 Uhr Betreff: [sqlite] Fuzzy Matching
>>> 
>>> Hello
>>> 
>>> Although I am quite certain that the answer is that SQLite does
>>> not provide any mechanism to help me on this, it doesn't hurt to
>>> ask.
> Who
>>> know if a

Re: [sqlite] Fuzzy Matching

[Stephen Woodbridge]

Stephen Woodbridge wrote:
> I would be interested in having something like this also.
> 
> What I don't understand in your approach is how you compute the 
> (Levenstein) distance during a search. It seems like you have a fixed 
> set of tokens from your document text and these are indexed. Then you 
> have a query token the you want to compare to the index based on some 
> fuzzy distance. Since every query can be different I think you have to 
> compute the distance for every key in the index? that would require 
> doing a full index scan.
> 
> If there ware a function that you could run a token through that would 
> given you that tokens "location" in some space then you could generate a 
> similar "location" for the query token and then use the rtree and 
> distance. I'm not aware of any such functions, but my expertise is more 
> in GIS the search searching.

Hmmm, that was supposed to say text searching.

> Thoughts?
> 
> Best,
>-Steve
> 
> Martin Pfeifle wrote:
>> Hi, I think there is nothing available except FTS. Doing a full table
>> scan and computing for each string the (Levenstein) distance to the
>> query object is too time consuming. So what I would like to see is
>> the implementation of a generic metric index which needs as one
>> parameter a metric distance function. Based on such a distance
>> function you could then do similarity search on any objects , e.g.
>> images, strings, etc. One possible index would be the M-tree (which
>> you can also organize relational as it was done with the R*-tree).
>> The idea is that you have a hierarchical index and each node is
>> represented by a database  object o and a covering radius r
>> reflecting the maximal distance of all objects in that subtree to the
>> object o. If you do a range query now, you compute the distance of
>> your query object to the object o. If this distance minus the
>> coverage radius r is bigger than your query range you can prune that
>> subtree. You can either implement such a similarity module as an own
>> extension similar toFTS or the Spatial module, or integrate it into
>> FTS and use it only for strings. Personally, I need the second
>> solution because I'd like to do full and fuzzy text search. Are there
>> any plans to implement something like this, if yes, I would like to
>> take part in such a development. . Best Martin
>>
>>
>>
>>
>> - Ursprüngliche Mail  Von: Alberto Simões
>> <[EMAIL PROTECTED]> An: General Discussion of SQLite Database
>> <sqlite-users@sqlite.org> Gesendet: Donnerstag, den 3. Juli 2008,
>> 21:52:05 Uhr Betreff: [sqlite] Fuzzy Matching
>>
>> Hello
>>
>> Although I am quite certain that the answer is that SQLite does not 
>> provide any mechanism to help me on this, it doesn't hurt to ask. Who
>>  know if anybody have any suggestion.
>>
>> Basically, I am using SQLite for a dictionary, and I want to let the 
>> user do fuzzy searches. OK, some simple Levenshtein distance of one
>> or two would do the trick, probably.
>>
>> I imagine that SQLite (given the lite), does not provide any kind of 
>> nearmisses search. But probably, somebody here did anything similar
>> in any language?
>>
>> Cheers Alberto
> 
> ___
> 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] Fuzzy Matching

[Stephen Woodbridge]

I would be interested in having something like this also.

What I don't understand in your approach is how you compute the 
(Levenstein) distance during a search. It seems like you have a fixed 
set of tokens from your document text and these are indexed. Then you 
have a query token the you want to compare to the index based on some 
fuzzy distance. Since every query can be different I think you have to 
compute the distance for every key in the index? that would require 
doing a full index scan.

If there ware a function that you could run a token through that would 
given you that tokens "location" in some space then you could generate a 
similar "location" for the query token and then use the rtree and 
distance. I'm not aware of any such functions, but my expertise is more 
in GIS the search searching.

Thoughts?

Best,
   -Steve

Martin Pfeifle wrote:
> Hi, I think there is nothing available except FTS. Doing a full table
> scan and computing for each string the (Levenstein) distance to the
> query object is too time consuming. So what I would like to see is
> the implementation of a generic metric index which needs as one
> parameter a metric distance function. Based on such a distance
> function you could then do similarity search on any objects , e.g.
> images, strings, etc. One possible index would be the M-tree (which
> you can also organize relational as it was done with the R*-tree).
> The idea is that you have a hierarchical index and each node is
> represented by a database  object o and a covering radius r
> reflecting the maximal distance of all objects in that subtree to the
> object o. If you do a range query now, you compute the distance of
> your query object to the object o. If this distance minus the
> coverage radius r is bigger than your query range you can prune that
> subtree. You can either implement such a similarity module as an own
> extension similar toFTS or the Spatial module, or integrate it into
> FTS and use it only for strings. Personally, I need the second
> solution because I'd like to do full and fuzzy text search. Are there
> any plans to implement something like this, if yes, I would like to
> take part in such a development. . Best Martin
> 
> 
> 
> 
> - Ursprüngliche Mail  Von: Alberto Simões
> <[EMAIL PROTECTED]> An: General Discussion of SQLite Database
>  Gesendet: Donnerstag, den 3. Juli 2008,
> 21:52:05 Uhr Betreff: [sqlite] Fuzzy Matching
> 
> Hello
> 
> Although I am quite certain that the answer is that SQLite does not 
> provide any mechanism to help me on this, it doesn't hurt to ask. Who
>  know if anybody have any suggestion.
> 
> Basically, I am using SQLite for a dictionary, and I want to let the 
> user do fuzzy searches. OK, some simple Levenshtein distance of one
> or two would do the trick, probably.
> 
> I imagine that SQLite (given the lite), does not provide any kind of 
> nearmisses search. But probably, somebody here did anything similar
> in any language?
> 
> Cheers Alberto

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

Re: [sqlite] Spatial data in SQLite

[Stephen Woodbridge]

Alexey Pechnikov wrote:
> Hello!
> 
> I found article about spatial extensions for SQLite
> http://www.perrygeo.net/wordpress/?p=115
> 
> Do you know other GIS extensions? I'm migrating from PostgreSQL+PostGIS to 
> SQLite and searching GIS extensions for SQLIte.

http://www.gaia-gis.it/spatialite-2.0/index.html

This is a pretty complete version of postGIS in sqlite included support 
for the rtree indexing.

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

Re: [sqlite] counting tables

[Stephen Woodbridge]

Tim Streater wrote:
> Hi,
> 
> I have a PHP script that I run in Terminal under OS X, which opens an sqlite 
> db and works on it. Trouble is, if the db doesn't exist then the PHP library 
> silently creates an empty db with the given name. Given this, I need to 
> detect that the empty db has 
> been created, delete it, and exit.
> 
> As a work around I thought I might check that the newly opened db has no 
> tables, and clean up if so. My question is, what query can I make of the db 
> to determine that it has no tables? I couldn't find one (or perhaps I don't 
> know SQL well enough). If I 
> run sqlite3 I can give it the .tables command - I suppose I could have a call 
> to this module from my script, but I'd prefer to avoid that. Alternatively is 
> then another approach?

Why don't you check for the existence of the file using PHP BEFORE you 
open it in? Trivial file exist test in PHP.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building ft3?

[Stephen Woodbridge]

Stephen Woodbridge wrote:
> Hi all,
> 
> I'm on Linux and I would like to build sqlite3 with rtree and ft3 
> support. How do I do that? I have read through the docs, website, the 
> wiki and have evidently missed the needed page(s).
> 
> For example:
> http://www.sqlite.org/compile.html
> talks about compilation options but does not say where/how to use these 
> options and it does not discuss rtree of fts3.
> 
> Help or pointers appreciated.

OK, for the record, after search the archive and various wiki pages the 
sort of hinted at how to do it, etc.

Anyway, I got this to work like this:


#
rm -rf bld lib

mkdir bld lib
cd bld
CFLAGS="-Os -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_RTREE=1" LDFLAGS=-ldl 
../sqlite/configure \
 --prefix=$HOME \
 --disable-tcl \
 --enable-load-extension \
 --with-pic \
 --enable-threadsafe \
 --enable-tempstore \

make
make install

exit

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

[sqlite] Building ft3?

[Stephen Woodbridge]

Hi all,

I'm on Linux and I would like to build sqlite3 with rtree and ft3 
support. How do I do that? I have read through the docs, website, the 
wiki and have evidently missed the needed page(s).

For example:
http://www.sqlite.org/compile.html
talks about compilation options but does not say where/how to use these 
options and it does not discuss rtree of fts3.

Help or pointers appreciated.

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

Re: [sqlite] SQLite and updating VIEWs

[Stephen Woodbridge]

Shawn Wilsher wrote:
> Hey all,
> 
> I'm working with a partitioned table setup with a permanent table and
> a temp table with the same columns and indexes.  Every X time we dump
> all the records in the temp table over to the permanent one.  In order
> to make selection queries easier to manage, I've gone and created a
> view like so:
> CREATE TEMPORARY VIEW table_view AS
> SELECT * FROM table_temp
> UNION
> SELECT * FROM table
> 
> This was all going well, until I realized that updating was going to
> be very hard (insertion always goes to the temporary table).  That
> seemed easy enough to manage if I use an INSTEAD OF trigger on the
> view for UPDATE statements.  The problem is what I want to do in the
> trigger, which is this:
> 1) if the data is in the temporary table, update that
> 2) if the data is not in the temporary table, copy the data from the
> permanent table into the temp one, and then update the temp table
> Sadly, my SQL-fu isn't strong enough to know how to do this, and I'm
> starting to wonder if it's even possible.  If someone could tell me if
> I can do it, and then provide a pointer as to how to go about it, I'd
> really appreciate it.

Given a row that you have to work with, your current setup is lacking 
enough info to know which table the row came from in the view. You might 
define you view like:

  CREATE TEMPORARY VIEW table_view AS
  SELECT *, 'T' as which FROM table_temp
  UNION
  SELECT *, 'P' as which FROM table

Now in your trigger you can check "which" and know if you need to copy 
the row to table_temp.

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

Re: [sqlite] prepackaged sql statement

[Stephen Woodbridge]

Alex Katebi wrote:
> The problem with the view is that you can not pass a parameter or variable
> from the outer select to the views select.

Right that is not the purpose of a view. Think of a view as a virtual 
TABLE. You can not pass a parameter to a table either. You can just 
query the table or view and you CAN query with a parameter in the select 
statement.

select * from  where 

Maybe it would be help to restate what you are trying to do.

-Steve

> On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]>
> wrote:
> 
>> "Henrik Bechmann" <[EMAIL PROTECTED]>
>> wrote in message news:[EMAIL PROTECTED]
>>> Thanks Igor! And the SQL statement can be bound to parameters in the
>>> usual ways?
>> No, not in the view. You can, of course, select from the view (as if it
>> were a table), and _that_ query can be parameterized.
>>
>> Igor Tandetnik
>>
>>
>>
>>
>> ___
>> 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] How does the database file grow?

[Stephen Woodbridge]

D. Richard Hipp wrote:
> On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote:
> 
>> Hi all,
>>
>> i create and fill database files which reach quite a large size  
>> after a
>> while, because i only add data and never remove it.
>> The database files themselves become quite fragmented on the disk.
>> Because I can in many cases calculate in advance the size to which the
>> file will grow, i would like to reserve the disk space before  
>> inserting
>> all the data. Can this be done?
> 
> 
> Perhaps like this:
> 
>  CREATE TABLE dummy(x);
>  INSERT INTO dummy VALUES(zeroblob(1000));
>  DROP TABLE dummy;
> 
> Change the argument of zeroblob to get the size you want.

Would something like this work (on linux)?

dd if=/dev/zero of=mydatabase.db bs=1024 count=65536

would create a file that is 64MB in size and zero it out. I'm not use 
how sqlite would react to being given a preallocated but empty file that 
exists.

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

[sqlite] DROP TABLE IF EXISTS foo does not seem to work via the C-API

[Stephen Woodbridge]

Hi all,

DROP TABLE IF EXISTS foo;

does not seem to work via the C-API. If I type it in sqlite3 it works fine.

When I tried via the C-API, I got the following:

SQL: drop table if exists addr;

SQL error: near "exists": syntax error


My code is:

 strcpy(sql, "drop table if exists ");
 strcat(sql, tablename);
 strcat(sql, ";\n");

 rc = sqlite3_exec(db, sql, NULL, NULL, );
 if( rc!=SQLITE_OK ){
 fprintf(stderr, "SQL: %s\n", sql);
 fprintf(stderr, "SQL error: %s\n", zErrMsg);
 sqlite3_free(zErrMsg);
 }

Can anyone reproduce this?
Did I do something wrong? (likely :)

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

Re: [sqlite] SQL questions

[Stephen Woodbridge]

c.panel wrote:
> Oh yes! "set" is the solution.
> I know that I have missed something...
> thanks a lot.
> 
> no possibilities for indexing on an expression : is it a particularity of
> SQL or SQLite ?

postgresql provides function based indexes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selecting an indexed table

[Stephen Woodbridge]

Alex Katebi wrote:
> I have no way of knowing which rows a select command has visited for an
> indexed table.
> 
> create table t(a);
> create index it on t(a);
> insert ...
> select * from t where a=5;
> 
> Is there a select hook for debugging?

How about:

explain select * from t where a=5;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite with client/server support

[Stephen Woodbridge]

This looks interesting:
http://sqlitedbms.sourceforge.net/index.htm

Alexey would this work for your multi-master replication? Maybe you 
modify this to work for the specific task you have in mind.

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

Re: [sqlite] Multy-master replication of SQLite databases

[Stephen Woodbridge]

Alexey Pechnikov wrote:
> В сообщении от Thursday 19 June 2008 20:23:22 Stephen Woodbridge написал(а):
>> Alexey Pechnikov wrote:
>>> Hello!
>>>
>>> Is any method for multy-master replication of SQLite databases?
>> http://www.google.com/search?num=100=en=1=sqlite++replication
> 
> Are you really saw solution for  replication in this links? 
> Especially as sync as async.

I did do a search "multi master sqlite replication" and got a different 
set of results, but I did not read all the results so there might not be 
a solution, but if there is it is likely in these results.

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

Re: [sqlite] Multy-master replication of SQLite databases

[Stephen Woodbridge]

Alexey Pechnikov wrote:
> Hello!
> 
> Is any method for multy-master replication of SQLite databases?
> 
http://www.google.com/search?num=100=en=1=sqlite++replication
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting a table size without counting

[Stephen Woodbridge]

Dennis Cote wrote:
> Alexey Pechnikov wrote:
>> I think, you can use index directly as
>> create index id_idx on mytable(id);
>>
>> select count(id) from mytable where id>0;
>>
>> explain query plan select count(id) from mytable where id>0;
>> 0|0|TABLE mytable WITH INDEX id_idx
>>
>> It's better than use count(*) with "where" condition.
> 
> I can't see how this improves the situation any. You are making SQLite 
> do a full scan of the index (assuming all records have an id > 0) 
> instead of a full scan of the table. It still has to count each record.

Well if the index requires fewer page reads then it should be 
proportionally faster. For example if you can only get 5 rows on a page 
but 25 index entries, you have 1/5 the number of pages to read.

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

Re: [sqlite] The APress SQLite Book

[Stephen Woodbridge]

In general, I have been very happy with it. It is a great introduction 
and provides lots of examples. I think of it as more of a tutorial than 
a reference manual. The SQLite web site is a better reference manual, 
but lacks the examples and sample code that the book offers. Once you 
get the basics down, it is pretty easy to out grow the usefulness of the 
book or any book for that matter. Any printed document for a living 
project is obsolete at the time of publication.

-Steve

Stefan Arentz wrote:
> Sorry if this is a little offtopic but I am curious what other people thing.
> 
> Is it just me, or is the APress book 'The definitive guide to SQLIte'
> not very high quality?
> 
> First, the index is completely unusable. The index is grouped by
> subject, so if you want to for example lookup where the book talks
> about sqlite3_reset you first have to know that it is grouped under
> the 'Prepared Query'. This is completely useless for looking up things
> and I keep for a full index scan (heh) to find things. My copy of the
> book mentions 'Find it faster at http://superindex.apress.com !' but
> that site gives a 404.
> 
> Also, the book seems to be inaccurate in a bunch of places. For
> example, when I did find the section about sqlite_reset, I read the
> following:
> 
> "If you want to reuse the statement, you can do so using
> sqlite3_reset(). It will keep the compiled SQL statement (and any
> bound parameters), but commits any changes related to the current
> statement to the database."
> 
> Maybe the terminology used here is simply very confusing but I'm
> pretty sure that sqlite3_reset does not commit anything. It only
> resets the state of the prepared statement so that it can be used
> again.
> 
> The terminology used is also very inconsistent. For example the books
> talks about 'Compiled SQL Queries', 'Prepared Queries', 'Prepared
> Statements' and 'Compiled SQL Statements' instead of using simply
> 'Prepared Statement' as the rest of the database world does.
> 
> Some sections are pretty good and give a lot of insight, but in
> general I am very disappointed and I think I will simply toss it away
> and keep using the documentation on the SQLite web site.
> 
>  S.
> ___
> 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] Windows VC++ 2008 Express Edition compile howto?

[Stephen Woodbridge]

Hi all,

I would like to compile sqlite and spatialite using Windows VC++ 2008 
Express Edition. I looked around on the website and wiki but knowing 
almost nothing about these tools there was no obvious answer to me.

Does anyone have a step by step howto compile sqlite with VC++ 2008 
Express Edition that they can share. Shouldn't we also have a wiki or 
documentation page for this?

Likewise I need to compile the rtree extension. If you can include that 
step I am pretty sure I can mirror that to figure out the Spatialite 
extensions.

Thanks for any help you can provide to get me started.

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

Re: [sqlite] How to build an extension that gets autoloaded

[Stephen Woodbridge]

Hi Shane,

Thank you for your response. I do need the rtree extension but I also 
have a need to some others and would like to know how to statically link 
in extensions anyway.

-Steve


Shane Harrelson wrote:
> If you just need the rtree extension, I believe it will be included by
> default in the next version of the core sqlite library.
> 
> -Shane
> 
> On Tue, Jun 10, 2008 at 12:39 AM, Stephen Woodbridge <
> [EMAIL PROTECTED]> wrote:
> 
>> Hi all,
>>
>> I was reading through the sqlite source and noticed that there appears
>> to be an ability to build and extension that is statically linked and
>> autoloaded, but I can not find any details on how to do this.
>>
>> For example, if I want to build an application that uses the rtree
>> extension on Linux, how would I compile and link link it and get it
>> autoloaded.
>>
>> For a simple example, maybe just an explanation of how to do this for
>> the sqlite3 shell would suffice. It makes it much simpler to statically
>> link these into the application when you want to deploy it, because
>> there are less files and paths and things that can go wrong if the user
>> messes with things and since it is a dedicated application it will
>> always need the extension.
>>
>> If this is already documented somewhere, please just point to it.
>>
>> Thanks,
>>   -Stephen Woodbridge
>>http://imaptools.com/
>> ___
>> 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] How to build an extension that gets autoloaded

[Stephen Woodbridge]

Hi all,

I was reading through the sqlite source and noticed that there appears 
to be an ability to build and extension that is statically linked and 
autoloaded, but I can not find any details on how to do this.

For example, if I want to build an application that uses the rtree 
extension on Linux, how would I compile and link link it and get it 
autoloaded.

For a simple example, maybe just an explanation of how to do this for 
the sqlite3 shell would suffice. It makes it much simpler to statically 
link these into the application when you want to deploy it, because 
there are less files and paths and things that can go wrong if the user 
messes with things and since it is a dedicated application it will 
always need the extension.

If this is already documented somewhere, please just point to it.

Thanks,
   -Stephen Woodbridge
http://imaptools.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is quicker?

[Stephen Woodbridge]

Christophe Leske wrote:
> Shane Harrelson schrieb:
>> Dennis-
>>
>> Your last "simplification":
>>   
> I never got that email from Dennis, I would be very interested in it.
> 
> Dennis, this is actually what i am currently doing.
> 
> However:
> 
> i see no speed up for large areas (half the globe, e.g.), but 
> considerable ones for small areas (a country like france for instance), 
> as well as very small areas (maximum zoom).
> 
> I added an index on the ID field for the search in the city database, 
> that helped a bit, but i am dissapointed that the rtree search is not 
> faster than the normal search for bigger areas...
> 

Why would you expect it to be faster?

Think about it for a second ...

1) if we do a search that covers the whole world, are we not doing the 
equivalent of a full table scan + the additional over head of the the 
rtree processing. This is just like doing a query the selects all 
entries in a btree indexed table. A full scan is the slowest.

Then in a btree indexed table selecting a single record via the index is 
fastest, likewise with an rtree, if you narrow the search area to a very 
small area you eliminate most of the records and return just one I would 
expect that to be the fastest.

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