[sqlite] Ranking in fts.

[Scott Hess]

I've lined up some time to work on fts, again, which means fts3.  One
thing I'd like to include would be to order doclists by some baked-in
ranking.  The idea is to sort to most important items to the front of
the list, and then you can do queries which limit the number of hits
and can thus be significantly faster for popular terms.  [Note that
"limit the number of hits" cannot currently be done at the fts layer,
but I'm thinking on that problem, too.]

Here's a proposal for how this might look:

CREATE VIRTUAL TABLE MyTable USING fts3(
 column_name,
 other_column,
 RANK rank_column_name DESCENDING,
 TOKENIZER simple
);

column_name and other_column would be just like in fts2, and would
manifest as TEXT columns.  Likewise the TOKENIZER clause.  RANK would
introduce a new column, in this case called rank_column_name, which
would be INTEGER.  The developer could then insert values into that
column which would be used by fts3 to order document lists.

ASCENDING or DESCENDING are listed after the column name to describe
whether higher values are better or lower values.  I think this is
necessary to handle open-ended ranges reasonably.  If you assigned
incrementing values (such as from an autoincrement rowid) to a
DESCENDING rank, then earlier documents will sort to the beginning
(like how fts2 currently works, where things are ordered by docids).
If you assigned a timestamp to an ASCENDING rank, then later documents
will sort to the beginning.  You can somewhat map one to the other by
setting the rank to "very big constant - actual number", but I think
the syntactic sugar is nice.  I'd expect the default to be ASCENDING.
[I'm open to the notion that I have ASCENDING and DESCENDING exactly
backwards.  There's the logical sense of how importance is handled,
and the physical sense of how it is stored.]

If no RANK is given at all, then things work just like they do now
(essentially ranked by rowid, DESCENDING).

Anyone out there have any thoughts which might be important to consider?

Thanks,
scott

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

[sqlite] Re: Why is there no sqlite3_exec16() method?

[Igor Tandetnik]

Rob Richardson <[EMAIL PROTECTED]>
wrote:

Our shop writes in Visual C++ 6, and our applications are all
developed
with _UNICODE defined.  But if we want to use sqlite3_exec(), we are
forced to convert the SQL statements to single-byte characters.  Why
is
there no sqlite3_exec16() method?  Or is there a version of the SQLite
API that does have that method?


sqlite3_exec is maintained for backward compatibility only. It is 
trivially implemented in terms of other public SQLite API functions. You 
are encouraged to migrate to these functions (see sqlite3_prepare, 
sqlite3_step, sqlite3_finalize et al).


If for some reason you really need sqlite3_exec16, you can implement it 
yourself. Take source code for sqlite3_exec and replace all narrow calls 
with their *16 equivalents were appropriate.


Igor Tandetnik 



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

[sqlite] Why is there no sqlite3_exec16() method?

[Rob Richardson]

Greetings!

Our shop writes in Visual C++ 6, and our applications are all developed
with _UNICODE defined.  But if we want to use sqlite3_exec(), we are
forced to convert the SQL statements to single-byte characters.  Why is
there no sqlite3_exec16() method?  Or is there a version of the SQLite
API that does have that method?

Thank you very much.

Rob Richardson

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

Re: [sqlite] SQL query help...

[Jeff Godfrey]

- Original Message - 
From: "Trey Mack" <[EMAIL PROTECTED]>

To: 
Sent: Friday, June 08, 2007 1:08 PM
Subject: Re: [sqlite] SQL query help...



Here's what I tried, which didn't work...

select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
where diff > 0.0005
group by zone,location

That causes a "misuse of aggregate" error.


select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
group by zone,location
having diff > 0.0005;



Trey,

That seems to work great.  I appreciate the assistance - thank you.

Jeff



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

Re: [sqlite] SQL query help...

[Trey Mack]

Here's what I tried, which didn't work...

select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
where diff > 0.0005
group by zone,location

That causes a "misuse of aggregate" error.


select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
group by zone,location
having diff > 0.0005;

- tmack

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

[sqlite] SQL query help...

[Jeff Godfrey]

Hi All,

I need a little help in constructing a SQLite query..

Here's what I have so far that works...

select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
group by zone,location

The above properly calculates a range value for a group of records and 
returns the groups accordingly.


Now, what I need to do is only return records where the calculated 
range value exceeds some value (say, 0.0005).


Here's what I tried, which didn't work...

select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
where diff > 0.0005
group by zone,location

That causes a "misuse of aggregate" error.

Thanks for any assistance.

Jeff 



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

Re: [sqlite] Why do you use SQLite? Comments for an article needed

[John Elrick]

A.J.Millan wrote:

John Elrick wrote:

  

Tim Anderson wrote:

We are working on a project for the Census Bureau and needed an
embeddable database that was zero configuration for the user and fast.
We evaluated SQLite against numerous competitors...



IMHO, a bit exaggerated the "numerous competitors" part of the sentence.  In
fact there are not so many dbases that match the proposed conditions for
it's Bureau.

  


We evaluated a total of six, including two which would have required 
some configuration by the user.  I guess it depends upon your definition 
of "numerous", but I'd personally accept a half dozen as fitting that 
fuzzy description.



John

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

Re: [sqlite] Why do you use SQLite? Comments for an article needed

[Clay Dowling]

Tim Anderson wrote:
> I'm writing an article about SQLite and I'd love to get some comments
> from users about why you use it. Performance? Features? Reliability?
> Cost? Is the open source aspect important? Anything else? For that
> matter, anything you really don't like about SQLite?
>
> You can email me at tim(at)itwriting.com or comment here if you prefer -
> but to use your quote I'd need at least a full name, what you do and the
> company you work for

I'm using SQLite as an intermediate database in a shrinkwrap product.  As
part of a major version upgrade we're moving data from a Paradox database
with little to no data normalization into a SQL Server database normalized
to Boyce-Cod normal form.  SQLite is used to keep track of primary keys in
the new database to be used later in the process for maintaining data
relations.

SQLite was chosen for the following reasons:

1. Easy deployment.
2. Zero configuration.
3. Easy API to access from Delphi.
4. Licensing costs
5. Speed.  Speed was the primary deciding factor, because these lookups
must be fast.

Clay Dowling
-- 
Simple Content Management
http://www.ceamus.com


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

Re: [sqlite] Why do you use SQLite? Comments for an article needed

[Jan Kandziora]

Am Donnerstag, 7. Juni 2007 19:49 schrieb Tim Anderson:
> I'm writing an article about SQLite and I'd love to get some comments
>
> >from users about why you use it. Performance? Features? Reliability?
>
> Cost? Is the open source aspect important? Anything else? For that
> matter, anything you really don't like about SQLite?
>
I prefer SQLite as for applications where a substancial amount of information 
otherwise would be stored in made-up-from-scratch file formats. As soon as you 
need more structure than key-value pairs, an SQL database comes in handy.
It's pretty easy to extend an application with a SQLite db backend. You don't 
have to run a database server process just for storing some tables. 

What I especially like about SQLite is it's Tcl language binding. We do a lot 
of programming and scripting in Tcl and SQLite's Tcl interface handles the 
variable quoting automatically, so SQL injection bugs are very unlikely to 
happen.

The ability to extend SQLite's features -- e.g. adding custom collation 
sequences -- easily is another ++ for me.


> You can email me at tim(at)itwriting.com or comment here if you prefer -
> but to use your quote I'd need at least a full name, what you do and the
> company you work for
>
My company TuxTAP develops and manufactures Linux based cash registers and 
electronic dispensers for restaurants.

Kind regards

Jan
-- 
The first law of demos:
 - never try to actually use the system for anything

[sqlite] Re: Syntax help with UPDATE in SQLite Database Browser

[Igor Tandetnik]

Ellis Robin (Bundaberg)
<[EMAIL PROTECTED]> wrote:

Update Parameter set ParameterValue = (select NewParams.parametervalue
from Scenario, Link, Catchment, FunctionalUnit,
FunctionalUnitDefinition, StandardFU, Parameter, NewParams where
Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and
Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID =
Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID =
FunctionalUnitDefinition.FunctionalUnitDefinitionID and
FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and
StandardFU.RainfallRunoffModelID = Parameter.ModelID and
Catchment.Name = NewParams.Subcatchments and
FunctionalUnitDefinition.Name = NewParams.FU_name and
Parameter.Parameter = NewParams.parameter)


Remove Parameter from the FROM clause of the subselect. You want to tie 
the subselect to Parameter table you are updating, rather than giving it 
its own.


Igor Tandetnik 



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

Re: [sqlite] Truncate Issue

[Dan Kennedy]

On Fri, 2007-06-08 at 09:50 +0530, Jimmy Mathew Ambalathuruthel wrote:
> Hi all,
> 
>   I am working in a porting project of SQLite from windows.
> 
>   I Could not port the Truncate () function (For Eg: WinTruncate () in
> windows code in the file os_win.c ) as the same logic cannot be applied
> to my platform.
> 
>   Can I make it a dummy function by just returning SQLITE_OK?
> 
>   Please help.

I think it's important to implement. SQLite sometimes needs 
to truncate files during VACUUM, when committing transactions 
in either auto-vacuum or exclusive access mode, and during 
rollback or statement rollback. There might be more uses in
the future (based on the idea that truncating a file is 
often cheaper than deleting and recreating it). 

Why can't you truncate files? What platform are you working on?

Dan.



>  
> 
> Regards
> 
> Jimmy Mathew
> 
>  
> 


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

Re: [sqlite] Why do you use SQLite? Comments for an article needed

[A.J.Millan]

John Elrick wrote:

> Tim Anderson wrote:
>
> We are working on a project for the Census Bureau and needed an
> embeddable database that was zero configuration for the user and fast.
> We evaluated SQLite against numerous competitors...

IMHO, a bit exaggerated the "numerous competitors" part of the sentence.  In
fact there are not so many dbases that match the proposed conditions for
it's Bureau.

A.J.Millan
ZATOR Systems


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