date:20090303

On Wed, Mar 4, 2009 at 12:24 PM, John Machin  wrote:

> On 4/03/2009 2:48 PM, Peng Huang wrote:
> > On Wed, Mar 4, 2009 at 11:40 AM, John Machin 
> wrote:
> >
> >> On 4/03/2009 2:12 PM, Peng Huang wrote:
> >>> Hi Igor Tandetnik,
> >>>
> >>> Thanks for your quick reply.
> >>>
> >>> Your solution works. But in some cases, each y%d may has two or three
> >>> choices. So the SQL will become very complex, we need ( 2 * 2 * 2 * 2)
> >> sub
> >>> where statements. Does SQLite have some build-in features to optimize
> >> those
> >>> kinds of SQL statements? Or do you have other suggestions to optimize
> the
> >>> database of SQL statements?
> >>>
> >>> For example:
> >>> "SELECT * FROM py_phrase WHERE ylen = ? and (y0 = ? or y0 = ?) and (y1
> =
> >> ?
> >>> or y1 =? ) and (y2 = ? or y2 = ?) and (y3 = ? or y3 = ? or y3 = ?)
> ORDERY
> >> BY
> >>> user_freq DESC, freq DESC"
> >>>
> >> (y3 = ? or y3 = ? or y3 = ?)
> >> is *logically* equivalent to
> >> (y3 IN (?, ?, ?))
> >>
> >> Does it run at the same speed?
> >
> >
> > Its improvement is not obviously. :(
>
> OK, next try:
>
> Input is 1 to 4 pinyin characters ... with tone or without tone?
> Even with tone, (number of different pinyin characters (a few hundred,
> about 410 IIRC)) * 5 tones will fit easily in 16 bits. So a possible
> pronunciation of a 4-character phrase will easily fit in a 64-bit number.
>
> So instead of complications with y0, y1, y2, y3 just use one composite
> number e.g. (((y0 * 1 + y1) * 1) + y2 * 1) + y3 -- note that
> for phrases shorter tan 4 characters, you would need a special code
> (zero) for "no character at all".
>
> Instead of complicated logic to generate varing sizes and shapes of
>  union all  , you just have to produce a list of
> the possible composite keys and select * from the_table where
> composite_key in (?, ?, ?,...)
>

This is a good idea to make thing easily. But it is not suitable for other
kinds of use cases. I don't know if you know Chinese and Hanyu Pinyin. Let
me explain it for you. Most of Chinese chars' pronunciation (Pinyin) has two
parts - vowel and consonant. One use case is some users will not type
complete Pinyin for each Chinese chars. For example: User types complete
Pinyin for first char, but just types consonant for second and third chars.
In this case, I will use below SQL cmd. The columns s%d is for chars'
consonant. So composing four y%d into one is not suitable. :(


>
> I'd be interested to get Igor's thoughts on how much speed up might be
> achievable by doing this.


For Igor's idea. I did not write test code. Just type SQL command in sqlite3
program. The improvement is obviously. I think it is acceptable for user
typing.


>
>
> Another thought: if you have enough memory (is this for a PC or a
> phone?), an in-memory hash-table might be a good idea.


It is a desktop service. Other applications need it to type Chinese. So it
should not use many memory. BTW, the database of phrases is about 100M
includes index.


>
>
> HTH,
>
> John
> ___
> 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] Wiki page on Management Tools - should it explicitely state Mac OS X support?

On Tue, Mar 3, 2009 at 3:53 PM, BareFeet  wrote:
> Hi,
>
>>> on the Wiki page listing SQLite Management Tools
>>> 
>>> , there are columns for Web, Windows, Linux and Misc. as supported
>>> platforms. Since Mac OS X is a well-supported SQLite platform and there are
>>> more and more applications on Mac OS X that are directly or indirectly using
>>> SQLite, I'd suggest to add a column for "Mac OS X" support.
>
>> sounds good to me... go for it. I don't think there are many Mac-based
>> tools though, but I could be wrong.
>
> I have a page of SQLite GUI apps listed and compared at:
> http://www.tandb.com.au/sqlite/compare/?ml
>
> If you know of any more applications or would like to see another feature
> compared, reply here in this forum and I'll see what I can do.

http://www.malcolmhardie.com/sqleditor/
Not a db GUI, but a db schema editor... not specific to SQLite, but
works excellently with and for SQLite. Can import existing dbs and
reconstruct their schema, and can export to SQLite. Uses Jdbc driver.

Not free either as in speech or beer, but worth every Scottish penny.

>
> Tom
> BareFeet
>
>  --
> Comparison of SQLite GUI applications:
> http://www.tandb.com.au/sqlite/compare/
>
>
>
>



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to improve performance for some query statements

2009-03-03 Thread John Machin

On 4/03/2009 2:48 PM, Peng Huang wrote:
> On Wed, Mar 4, 2009 at 11:40 AM, John Machin  wrote:
> 
>> On 4/03/2009 2:12 PM, Peng Huang wrote:
>>> Hi Igor Tandetnik,
>>>
>>> Thanks for your quick reply.
>>>
>>> Your solution works. But in some cases, each y%d may has two or three
>>> choices. So the SQL will become very complex, we need ( 2 * 2 * 2 * 2)
>> sub
>>> where statements. Does SQLite have some build-in features to optimize
>> those
>>> kinds of SQL statements? Or do you have other suggestions to optimize the
>>> database of SQL statements?
>>>
>>> For example:
>>> "SELECT * FROM py_phrase WHERE ylen = ? and (y0 = ? or y0 = ?) and (y1 =
>> ?
>>> or y1 =? ) and (y2 = ? or y2 = ?) and (y3 = ? or y3 = ? or y3 = ?) ORDERY
>> BY
>>> user_freq DESC, freq DESC"
>>>
>> (y3 = ? or y3 = ? or y3 = ?)
>> is *logically* equivalent to
>> (y3 IN (?, ?, ?))
>>
>> Does it run at the same speed?
> 
> 
> Its improvement is not obviously. :(

OK, next try:

Input is 1 to 4 pinyin characters ... with tone or without tone?
Even with tone, (number of different pinyin characters (a few hundred, 
about 410 IIRC)) * 5 tones will fit easily in 16 bits. So a possible 
pronunciation of a 4-character phrase will easily fit in a 64-bit number.

So instead of complications with y0, y1, y2, y3 just use one composite 
number e.g. (((y0 * 1 + y1) * 1) + y2 * 1) + y3 -- note that 
for phrases shorter tan 4 characters, you would need a special code 
(zero) for "no character at all".

Instead of complicated logic to generate varing sizes and shapes of 
 union all  , you just have to produce a list of 
the possible composite keys and select * from the_table where 
composite_key in (?, ?, ?,...)

I'd be interested to get Igor's thoughts on how much speed up might be 
achievable by doing this.

Another thought: if you have enough memory (is this for a PC or a 
phone?), an in-memory hash-table might be a good idea.

HTH,

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

Re: [sqlite] Wiki page on Management Tools - should it explicitely state Mac OS X support?

2009-03-03 Thread Dennis Cote

BareFeet wrote:
>
> I have a page of SQLite GUI apps listed and compared at:
> http://www.tandb.com.au/sqlite/compare/?ml
>
> If you know of any more applications or would like to see another  
> feature compared, reply here in this forum and I'll see what I can do.
>
>   
Tom,

You should add a column for the Spatialite GUI which can be found at 
http://www.gaia-gis.it/spatialite/

It is a free open source Mac OS X native GUI DB management tool.

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

Re: [sqlite] How to improve performance for some query statements

On Wed, Mar 4, 2009 at 11:40 AM, John Machin  wrote:

> On 4/03/2009 2:12 PM, Peng Huang wrote:
> > Hi Igor Tandetnik,
> >
> > Thanks for your quick reply.
> >
> > Your solution works. But in some cases, each y%d may has two or three
> > choices. So the SQL will become very complex, we need ( 2 * 2 * 2 * 2)
> sub
> > where statements. Does SQLite have some build-in features to optimize
> those
> > kinds of SQL statements? Or do you have other suggestions to optimize the
> > database of SQL statements?
> >
> > For example:
> > "SELECT * FROM py_phrase WHERE ylen = ? and (y0 = ? or y0 = ?) and (y1 =
> ?
> > or y1 =? ) and (y2 = ? or y2 = ?) and (y3 = ? or y3 = ? or y3 = ?) ORDERY
> BY
> > user_freq DESC, freq DESC"
> >
>
> (y3 = ? or y3 = ? or y3 = ?)
> is *logically* equivalent to
> (y3 IN (?, ?, ?))
>
> Does it run at the same speed?


Its improvement is not obviously. :(
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to improve performance for some query statements

2009-03-03 Thread John Machin

On 4/03/2009 2:12 PM, Peng Huang wrote:
> Hi Igor Tandetnik,
> 
> Thanks for your quick reply.
> 
> Your solution works. But in some cases, each y%d may has two or three
> choices. So the SQL will become very complex, we need ( 2 * 2 * 2 * 2) sub
> where statements. Does SQLite have some build-in features to optimize those
> kinds of SQL statements? Or do you have other suggestions to optimize the
> database of SQL statements?
> 
> For example:
> "SELECT * FROM py_phrase WHERE ylen = ? and (y0 = ? or y0 = ?) and (y1 = ?
> or y1 =? ) and (y2 = ? or y2 = ?) and (y3 = ? or y3 = ? or y3 = ?) ORDERY BY
> user_freq DESC, freq DESC"
> 

(y3 = ? or y3 = ? or y3 = ?)
is *logically* equivalent to
(y3 IN (?, ?, ?))

Does it run at the same speed?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to improve performance for some query statements

Hi Igor Tandetnik,

Thanks for your quick reply.

Your solution works. But in some cases, each y%d may has two or three
choices. So the SQL will become very complex, we need ( 2 * 2 * 2 * 2) sub
where statements. Does SQLite have some build-in features to optimize those
kinds of SQL statements? Or do you have other suggestions to optimize the
database of SQL statements?

For example:
"SELECT * FROM py_phrase WHERE ylen = ? and (y0 = ? or y0 = ?) and (y1 = ?
or y1 =? ) and (y2 = ? or y2 = ?) and (y3 = ? or y3 = ? or y3 = ?) ORDERY BY
user_freq DESC, freq DESC"

Thanks
Peng Huang

On Wed, Mar 4, 2009 at 10:49 AM, Igor Tandetnik  wrote:

> "Peng Huang" 
> wrote in message
> news:fb7465350903031832w20777903kc5bb1f641f455...@mail.gmail.com
> > But we need add some new requirements. We need query phrases by
> > length of phrases (ylen) and pronounce of each characters in phrase,
> > but the pronounce of one or two characters may be two different
> > integer. The SQL may like: "SELECT * FROM py_phrase WHERE ylen = ?
> > and (y0 = ? or y0 = ?) and y1 = ? and (y2 = ? or y2 = ?) ORDERY BY
> > user_freq DESC, freq DESC".
> > If I use "OR" operator in WHERE statement, the SQL performance will
> > be not acceptable. It is very slow.
>
> select * from py_phrase where rowid in (
>  select rowid from py_phrase where ylen=:ylen and y0=:y0_1 and y1=:y1
> and y2=:y2_1
>  union all
>  select rowid from py_phrase where ylen=:ylen and y0=:y0_1 and y1=:y1
> and y2=:y2_2
>  union all
>  select rowid from py_phrase where ylen=:ylen and y0=:y0_2 and y1=:y1
> and y2=:y2_1
>  union all
>  select rowid from py_phrase where ylen=:ylen and y0=:y0_2 and y1=:y1
> and y2=:y2_2
> )
> ORDERY BY user_freq DESC, freq DESC;
>
> 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

Re: [sqlite] How to improve performance for some query statements

2009-03-03 Thread Igor Tandetnik

"Peng Huang" 
wrote in message
news:fb7465350903031832w20777903kc5bb1f641f455...@mail.gmail.com
> But we need add some new requirements. We need query phrases by
> length of phrases (ylen) and pronounce of each characters in phrase,
> but the pronounce of one or two characters may be two different
> integer. The SQL may like: "SELECT * FROM py_phrase WHERE ylen = ?
> and (y0 = ? or y0 = ?) and y1 = ? and (y2 = ? or y2 = ?) ORDERY BY
> user_freq DESC, freq DESC".
> If I use "OR" operator in WHERE statement, the SQL performance will
> be not acceptable. It is very slow.

select * from py_phrase where rowid in (
  select rowid from py_phrase where ylen=:ylen and y0=:y0_1 and y1=:y1 
and y2=:y2_1
  union all
  select rowid from py_phrase where ylen=:ylen and y0=:y0_1 and y1=:y1 
and y2=:y2_2
  union all
  select rowid from py_phrase where ylen=:ylen and y0=:y0_2 and y1=:y1 
and y2=:y2_1
  union all
  select rowid from py_phrase where ylen=:ylen and y0=:y0_2 and y1=:y1 
and y2=:y2_2
)
ORDERY BY user_freq DESC, freq DESC;

Igor Tandetnik



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

[sqlite] How to improve performance for some query statements

Hi SQLite hackers,

I am using sqlite in a Chinese input method. The db schema is below, and the
table contains about 100 records.

CREATE TABLE py_phrase (
ylen INTEGER, /* length of phrase. How many characters in phrase */
y0 INTEGER, y1 INTEGER, y2 INTEGER, y3 INTEGER, /* pronounce of
characters in phrases. y0: first characters, y1 is the second. and ... */
s0 INTEGER, s1 INTEGER, s2 INTEGER, s3 INTEGER,
phrase TEXT, /* phrase */
freq INTEGER, /* frequency */
user_freq INTEGER /* user frequency */
);

I create INDEX like:
CREATE INDEX py_phrase_index_2 ON py_phrase (ylen, y0, y1, y2, y3);
CREATE INDEX py_phrase_index_3 ON py_phrase (ylen, s0, s1, s2, s3);

In the input method, we need query phrases by length of phrases (ylen) and
pronounce of each characters in phrase.

My SQL query statements is like:
"SELECT * FROM py_phrase WHERE ylen = ? and y0 = ? and y1 = ? and y2 = ?
ORDERY BY user_freq DESC, freq DESC"
The performance is good.

But we need add some new requirements. We need query phrases by length of
phrases (ylen) and pronounce of each characters in phrase, but the pronounce
of one or two characters may be two different integer. The SQL may like:
"SELECT * FROM py_phrase WHERE ylen = ? and (y0 = ? or y0 = ?) and y1 = ?
and (y2 = ? or y2 = ?) ORDERY BY user_freq DESC, freq DESC".
If I use "OR" operator in WHERE statement, the SQL performance will be not
acceptable. It is very slow.

Anybody can give me some suggestions to improve the performance of my SQLite
database?

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

Re: [sqlite] Slow performance with Sum function

2009-03-03 Thread D. Richard Hipp

On Mar 3, 2009, at 8:01 PM, Trainor, Chris wrote:

> I'm not sure how much we can do about preventing adds and deletes.   
> It *may* be possible to replace them with updates, but I am not sure  
> yet.  These adds and deletes are happening in a different table than  
> the one being summed.  This other table contains a large blob  
> column.  Would changing to updates help or will updates fragment the  
> database as much as adds and deletes?

SQLite implements an UPDATE by first deleting the old row then  
inserting a new one in its place.  So I don't think changing DELETE/ 
INSERT pairs into UPDATEs will help much with fragmentation.  And,  
besides, deleting and inserting does not really cause much  
fragmentation, as long as the data inserted is roughly the same size  
as the data deleted.

Fragmentation occurs for many reasons, but one important reason is  
that two or more b-trees within the database file are growing at the  
same time.  As each b-tree grows, it needs to allocate new pages.  New  
pages are allocated from the end of the database file (unless there  
were previously deleted pages that can be reused).  Imagine that you  
have (say) 10 b-trees all growing at roughly the same rate.  As the b- 
trees all grow, they will each allocates pages off the end of the file  
as they need time.  And you will end up with pages of the 10 b-tress  
all interleaved rather than being bunched together.

Note that there is one b-tree for each table and for each index.  So  
if you have a single SQL table with 3 unique columns (there is one  
implied index for each UNIQUE constraint) and 2 explicit indices, you  
will have 1+3+2=6 b-trees.  As you insert new information into this  
table, all 6 b-trees are updated together, so there iwill be some  
interleaving and hence fragmentation.

When you run the VACUUM command, it rebuilds each b-tree one by one,  
so all the pages for a single b-tree are bunched together in the file.

Note that using auto_vacuum does *not* help with fragmentation.  In  
fact, auto_vacuum makes fragmentation worse.  Auto_vacuum is designed  
for used on small flash-memory drives (such as found on cell-phones)  
that have low capacity and zero seek latency.  Auto_vacuum is a very  
helpful feature for the right problem, but fragmentation is not the  
right problem.

When there are free pages in the database file and new pages are  
needed by a growing b-tree, an attempt is made to reuse free pages  
that are as close as possible to the rest of the b-tree.  But  
typically the free list is short and the choices are limited, so it  
does not often happen that the chosen free page is immediately  
adjacent to the growing b-tree.

Decades of experience with filesystems have taught us that various  
heuristics can prevent filesystem fragmentation, as long as the  
filesystem is less than about 80% or 90% full.  Once a filesystem gets  
close to being full, fragmentation is inevitable.  To transfer this  
experience to SQLite, recognize that SQLite attempts to keep its  
database file as small as possible.  In other words, SQLite tries to  
keep itself 100% full at all times.  Hence, fragmentation of data in  
SQLite is pretty much inevitable.  One could envision future versions  
of SQLite that allowed you to preallocate a large database files such  
that the database always stayed less than 80% full.  Then we could use  
filesystem techniques to keep fragmentation down.  The penalty, of  
course, is that your database file is larger.  Probably much larger.   
And just to be clear: SQLite does not have that capability at this time.

>
>
> The second option is the one I am considering.  It looks like there  
> might be a good time to run vacuum.  I need to do some more timings  
> to tell for sure.

Do not be tempted by the incremental vacuum feature.  Incremental  
vacuum will reduce the database size as content is deleted, but it  
will not reduce fragmentation.  In fact, incremental vacuum will  
likely increase fragmentation.  Incremental vacuum is just a variation  
on auto_vacuum.  It is designed for flash memory with zero seek latency.

D. Richard Hipp
d...@hwaci.com

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

Re: [sqlite] WHERE clause analysis - find referenced columns

2009-03-03 Thread Alex Ousherovitch

Unfortunately, for the SQLITE_SELECT action code
(http://www.sqlite.org/c3ref/c_alter_table.html) the registered auth
callback will not get the column information.
It is only available for SQLITE_READ, which, I guess, is not what I need
- I would like prepare statement to fail if there are "hidden columns"
in the search criteria.
Is this possible?

 

> http://www.sqlite.org/c3ref/set_authorizer.html

> 

> This feature is used by systems such as

> 

>http://www.cvstrac.org/

>http://www.fossil-scm.org/

> 

> to prevent unauthorized users from enters hostile SQL, or from  

> entering SQL that uses sensitive information such as the "password"  

> column of the "user" table.

> 

> D. Richard Hipp

> drh at hwaci.com
 

 

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

Re: [sqlite] WHERE clause analysis - find referenced columns

2009-03-03 Thread D. Richard Hipp


On Mar 3, 2009, at 8:18 PM, Alex Ousherovitch wrote:

> Hello list,
>
>
>
> I need to implement a security model where certain columns (e.g. named
> super_secret_key) will be hidden.
>
> This needs to be enforced not only for the requested result columns  
> but
> also for the columns referenced in the WHERE clause.
>
> Is there a relatively simple way to either get each column  
> referenced in
> the WHERE clause or build a query that would check if the WHERE clause
> (I could place it in a temporary table) contains the  
> super_secret_key as
> a column name, but not as a value.
>


http://www.sqlite.org/c3ref/set_authorizer.html

This feature is used by systems such as

http://www.cvstrac.org/
http://www.fossil-scm.org/

to prevent unauthorized users from enters hostile SQL, or from  
entering SQL that uses sensitive information such as the "password"  
column of the "user" table.

D. Richard Hipp
d...@hwaci.com



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

[sqlite] WHERE clause analysis - find referenced columns

2009-03-03 Thread Alex Ousherovitch

Hello list,

 

I need to implement a security model where certain columns (e.g. named
super_secret_key) will be hidden. 

This needs to be enforced not only for the requested result columns but
also for the columns referenced in the WHERE clause.

Is there a relatively simple way to either get each column referenced in
the WHERE clause or build a query that would check if the WHERE clause
(I could place it in a temporary table) contains the super_secret_key as
a column name, but not as a value.

 

Thank you,

Alex

 

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

Re: [sqlite] Slow performance with Sum function

The blob is fairly small but is referenced very often, so it wouldn't be
feasible to move it to another table.  Does the blob make a difference
in this case?  I thought that since the blob column is to the right of
the column being summed that it would never get read during the
summation.

Thanks

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin
Sent: Tuesday, March 03, 2009 7:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Slow performance with Sum function

On 4/03/2009 5:52 AM, Trainor, Chris wrote:
> I am trying to use the Sum function on a column in a table with ~450K
> rows in it.  
> 
>   Select sum(Col4) from Table1
> 
> Where Table1 looks like this:
> 
> Create TABLE Table1 (
>   Col1 INTEGER NOT NULL,
>   Col2 INTEGER NOT NULL,
>   Col3 INTEGER NOT NULL,
>   Col4 BIGINT NOT NULL,
>   Col5 BIGINT NOT NULL,
>   Col6 BLOB NOT NULL,

What is the min/max/average size of this blob and how often do you need 
to access it? If the answer tends towards "huge and rarely", consider 
putting it in a separate table.


>   Col7 CHAR(1) DEFAULT '0',
>   Col8 NUMERIC(2) NOT NULL,
>   Col9 NUMERIC(2) NOT NULL,
>   Col10 INTEGER NOT NULL,
> Col11 INTEGER NOT NULL,
>   CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1)
>   ON DELETE CASCADE
>   ON UPDATE CASCADE,
>   CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC)
> );
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
The information contained in this email message and its attachments
is intended
only for the private and confidential use of the recipient(s) named
above, unless the sender expressly agrees otherwise. Transmission
of email over the Internet
 is not a secure communications medium. If you are requesting or
have requested
the transmittal of personal data, as defined in applicable privacy
laws by means
 of email or in an attachment to email you must select a more
secure alternate means of transmittal that supports your
obligations to protect such personal data. If the reader of this
message is not the intended recipient and/or you have received this
email in error, you must take no action based on the information in
this email and you are hereby notified that any dissemination,
misuse, copying, or disclosure of this communication is strictly
prohibited. If you have received
this communication in error, please notify us immediately by email
and delete the original message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow performance with Sum function

I'm not sure how much we can do about preventing adds and deletes.  It *may* be 
possible to replace them with updates, but I am not sure yet.  These adds and 
deletes are happening in a different table than the one being summed.  This 
other table contains a large blob column.  Would changing to updates help or 
will updates fragment the database as much as adds and deletes?

The second option is the one I am considering.  It looks like there might be a 
good time to run vacuum.  I need to do some more timings to tell for sure.

Thanks for the suggestions.

 
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Tuesday, March 03, 2009 7:41 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Slow performance with Sum function

On Tue, Mar 3, 2009 at 6:36 PM, Greg Palmer  wrote:
> Trainor, Chris wrote:
>> After running vacuum, sum is fast on the original table.  However,
>> running vacuum took a long time, so I'm not sure if that is a feasible
>> solution.  Is there any way to prevent fragmentation in the first place?
>> If not for the whole database, then for a specific table?  (e.g. is it
>> possible to preallocate space for a table?)
>>
>> Thanks
>>
> I'm not an expert on SQLite but generally speaking fragmentation in a
> database is usually a result of records being added and deleted. Are you
> doing a lot of these and if so, can you change your algorithm to cut
> down on this activity?

exactly the right approach. Even better, make your application do the
vaccuming when your users are away, much like housekeeping in a hotel.



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



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
The information contained in this email message and its attachments
is intended
only for the private and confidential use of the recipient(s) named
above, unless the sender expressly agrees otherwise. Transmission
of email over the Internet
 is not a secure communications medium. If you are requesting or
have requested
the transmittal of personal data, as defined in applicable privacy
laws by means
 of email or in an attachment to email you must select a more
secure alternate means of transmittal that supports your
obligations to protect such personal data. If the reader of this
message is not the intended recipient and/or you have received this
email in error, you must take no action based on the information in
this email and you are hereby notified that any dissemination,
misuse, copying, or disclosure of this communication is strictly
prohibited. If you have received
this communication in error, please notify us immediately by email
and delete the original message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow performance with Sum function

2009-03-03 Thread John Machin

On 4/03/2009 5:52 AM, Trainor, Chris wrote:
> I am trying to use the Sum function on a column in a table with ~450K
> rows in it.  
> 
>   Select sum(Col4) from Table1
> 
> Where Table1 looks like this:
> 
> Create TABLE Table1 (
>   Col1 INTEGER NOT NULL,
>   Col2 INTEGER NOT NULL,
>   Col3 INTEGER NOT NULL,
>   Col4 BIGINT NOT NULL,
>   Col5 BIGINT NOT NULL,
>   Col6 BLOB NOT NULL,

What is the min/max/average size of this blob and how often do you need 
to access it? If the answer tends towards "huge and rarely", consider 
putting it in a separate table.


>   Col7 CHAR(1) DEFAULT '0',
>   Col8 NUMERIC(2) NOT NULL,
>   Col9 NUMERIC(2) NOT NULL,
>   Col10 INTEGER NOT NULL,
> Col11 INTEGER NOT NULL,
>   CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1)
>   ON DELETE CASCADE
>   ON UPDATE CASCADE,
>   CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC)
> );
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow performance with Sum function

On Tue, Mar 3, 2009 at 6:36 PM, Greg Palmer  wrote:
> Trainor, Chris wrote:
>> After running vacuum, sum is fast on the original table.  However,
>> running vacuum took a long time, so I'm not sure if that is a feasible
>> solution.  Is there any way to prevent fragmentation in the first place?
>> If not for the whole database, then for a specific table?  (e.g. is it
>> possible to preallocate space for a table?)
>>
>> Thanks
>>
> I'm not an expert on SQLite but generally speaking fragmentation in a
> database is usually a result of records being added and deleted. Are you
> doing a lot of these and if so, can you change your algorithm to cut
> down on this activity?

exactly the right approach. Even better, make your application do the
vaccuming when your users are away, much like housekeeping in a hotel.



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



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow performance with Sum function

2009-03-03 Thread Greg Palmer

Trainor, Chris wrote:
> After running vacuum, sum is fast on the original table.  However,
> running vacuum took a long time, so I'm not sure if that is a feasible
> solution.  Is there any way to prevent fragmentation in the first place?
> If not for the whole database, then for a specific table?  (e.g. is it
> possible to preallocate space for a table?)
>
> Thanks
>   
I'm not an expert on SQLite but generally speaking fragmentation in a 
database is usually a result of records being added and deleted. Are you 
doing a lot of these and if so, can you change your algorithm to cut 
down on this activity?

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

Re: [sqlite] Slow performance with Sum function

After running vacuum, sum is fast on the original table.  However,
running vacuum took a long time, so I'm not sure if that is a feasible
solution.  Is there any way to prevent fragmentation in the first place?
If not for the whole database, then for a specific table?  (e.g. is it
possible to preallocate space for a table?)

Thanks

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Tuesday, March 03, 2009 2:00 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Slow performance with Sum function

Trainor, Chris 
wrote:
> I am trying to use the Sum function on a column in a table with ~450K
> rows in it.
>
> Select sum(Col4) from Table1
>
> It takes over 2 minutes to execute when using the original table.  I
> created an exact copy of the table with the same indices and
> constraints and inserted all the data from the original table into
> it.  Summing that column on the copied table only takes a few seconds.

Try running VACUUM on your database. Your original table is probably 
badly fragmented and results in excessive disk seeking.

Igor Tandetnik 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
The information contained in this email message and its attachments
is intended
only for the private and confidential use of the recipient(s) named
above, unless the sender expressly agrees otherwise. Transmission
of email over the Internet
 is not a secure communications medium. If you are requesting or
have requested
the transmittal of personal data, as defined in applicable privacy
laws by means
 of email or in an attachment to email you must select a more
secure alternate means of transmittal that supports your
obligations to protect such personal data. If the reader of this
message is not the intended recipient and/or you have received this
email in error, you must take no action based on the information in
this email and you are hereby notified that any dissemination,
misuse, copying, or disclosure of this communication is strictly
prohibited. If you have received
this communication in error, please notify us immediately by email
and delete the original message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

2009-03-03 Thread William Kyngesburye

On Mar 3, 2009, at 4:18 PM, D. Richard Hipp wrote:

> Another issue, that I only discovered two days ago, is that binaries
> compiled for Leopard (OS 10.5) will only run on Leopard and not older
> OS X systems.  And the precompiled shell found on the website is
> compiled on Leopard (which is all I have at hand.)  So downloading and
> running the pre-compiled shell will not work unless you have the
> latest Mac OS.  I don't know how to fix this problem.  Probably in the
> next release we will just label the binaries as "Leopard or later  
> only".
>
> D. Richard Hipp
> d...@hwaci.com


The default deployment target on Leopard is Leopard.  On earlier  
systems it's 10.3 (or 10.2?).  To get a Tiger-compatible Sqlite built  
on Leopard:

export MACOSX_DEPLOYMENT_TARGET=10.4
export CFLAGS="-Os -arch ppc -arch i386 -isysroot /Developer/SDKs/ 
MacOSX10.4u.sdk"

then configure as you normally would.

If you have the 10.3 SDK installed, you could go as far back as 10.3  
deployment+SDK.

-
William Kyngesburye 
http://www.kyngchaos.com/

"Those people who most want to rule people are, ipso-facto, those  
least suited to do it."

- A rule of the universe, from the HitchHiker's Guide to the Galaxy


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

Re: [sqlite] Installing SQLite

2009-03-03 Thread D. Richard Hipp

On Mar 3, 2009, at 5:04 PM, BareFeet wrote:
>
> In answer to your question about the .bin pre-compiled SQLite command
> line tool download from the SQLite web site, you need to:
>
> 1. Download it.
> 2. Rename it to "sqlite3" (no .bin extension, which I think shouldn't
> be there since it's confused with being a MacBinary file).

I did not know that.   I'll make a note to adjust the name in the next  
release.

>
> 3. Move it to a location where you want to run it. Somewhere like / 
> usr/
> local/bin/sqlite3. Do NOT replace the sqlite3 that is installed in the
> system /usr/bin/sqlite3.

Another issue, that I only discovered two days ago, is that binaries  
compiled for Leopard (OS 10.5) will only run on Leopard and not older  
OS X systems.  And the precompiled shell found on the website is  
compiled on Leopard (which is all I have at hand.)  So downloading and  
running the pre-compiled shell will not work unless you have the  
latest Mac OS.  I don't know how to fix this problem.  Probably in the  
next release we will just label the binaries as "Leopard or later only".

D. Richard Hipp
d...@hwaci.com

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

Re: [sqlite] Installing SQLite

2009-03-03 Thread BareFeet

Hi Dan,

> I am an experienced Windows user and application developer.  I am  
> now attempting to actually use a Mac!!  God forbid!!
>
> I have an SQLite database and I'm trying to figure out how to  
> install SQLite so I can view it and update it.  I downloaded the bin  
> files but I'm obviously missing something.

As others have mentioned, SQLite 3.40 is already installed as part of  
Mac OS X and drives half of the applications on the platform.

As other have pointed out, you can install the Mac OS X developer  
tools and compile/make the SQLite source code.

In answer to your question about the .bin pre-compiled SQLite command  
line tool download from the SQLite web site, you need to:

1. Download it.
2. Rename it to "sqlite3" (no .bin extension, which I think shouldn't  
be there since it's confused with being a MacBinary file).
3. Move it to a location where you want to run it. Somewhere like /usr/ 
local/bin/sqlite3. Do NOT replace the sqlite3 that is installed in the  
system /usr/bin/sqlite3.

I have a list of SQLite GUI apps for Mac OS X here:
http://www.tandb.com.au/sqlite/compare/?ml

Tom
BareFeet


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

Re: [sqlite] Wiki page on Management Tools - should it explicitely state Mac OS X support?

2009-03-03 Thread BareFeet

Hi,

>> on the Wiki page listing SQLite Management Tools
>> 
>> , there are columns for Web, Windows, Linux and Misc. as supported  
>> platforms. Since Mac OS X is a well-supported SQLite platform and  
>> there are more and more applications on Mac OS X that are directly  
>> or indirectly using SQLite, I'd suggest to add a column for "Mac OS  
>> X" support.

> sounds good to me... go for it. I don't think there are many Mac-based
> tools though, but I could be wrong.

I have a page of SQLite GUI apps listed and compared at:
http://www.tandb.com.au/sqlite/compare/?ml

If you know of any more applications or would like to see another  
feature compared, reply here in this forum and I'll see what I can do.

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] Bug in SQLITE? "Joins + Order By" Changing row count!?!

2009-03-03 Thread Jonathon

Hello,

I am executing a query such as:


   1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER
   JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND
   a.d_id = d.id AND c.e_id = e.id;
   2.
   3. CREATE TABLE tableA (
   4. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   5. b_idINTEGER NOT NULL,
   6. c_idINTEGER NOT NULL,
   7. d_idINTEGER NOT NULL
   8. )
   9.
   10. CREATE TABLE tableB (
   11. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
   12. );
   13.
   14. CREATE TABLE tableC (
   15. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   16. e_idINTEGER NOT NULL
   17. );
   18.
   19. CREATE TABLE tableD (
   20. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
   21. );
   22.
   23. CREATE TABLE tableE (
   24. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
   25. );


This seems to be returning the correct records.   However, when I append an
ORDER BY at the end of the query, it seems to be changing the number of
records that are returned back to me.  From my understanding, an ORDER BY
can not change the number of records correct?  If I do not use the ORDER BY,
I get around 150 records.   If I do an ORDER BY on any column that is not in
tableA, the number of records blows up (~4k) and there are duplicates.

Any ideas?

I thought that it was because of a buggy parser, so I wrote the query again
as:


   1. SELECT * FROM tableA a
   2. INNER JOIN tableB b
   3. ON a.b_id = b.id
   4. INNER JOIN tableD d
   5. ON a.d_id = d.id
   6. INNER JOIN tableC c
   7. LEFT JOIN tableE e ON c.e_id = e.id
   8. ON ON a.c_id = c.id

and it still gave me the same results...

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

Re: [sqlite] Installing SQLite

2009-03-03 Thread William Kyngesburye

Nothing wrong with Macs ;)

As others have pointed out, OSX includes Sqlite, though a bit old.   
Also note that extension loading is disabled in this version.

If you don't want to mess with installing Xcode, and want an up to  
date and feature-packed Sqlite installer, try my Sqlite framework.  It  
includes most extra features available in the source, plus the  
Spatialite extension.  Though it's made for building other  
applications with Sqlite support, the sqlite program is included (for  
use in Terminal.app).

On Mar 3, 2009, at 9:48 AM, Dan M. Wall wrote:

>
> I am an experienced Windows user and application developer.  I am  
> now attempting to actually use a Mac!!  God forbid!!
>
> I have an SQLite database and I'm trying to figure out how to  
> install SQLite so I can view it and update it.  I downloaded the bin  
> files but I'm obviously missing something.
>
> Please forgive my Mac OSX ignorance!  Any help you can provide would  
> be greatly appreciated!
>
> Dan
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-
William Kyngesburye 
http://www.kyngchaos.com/

"I ache, therefore I am.  Or in my case - I am, therefore I ache."

- Marvin

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

Re: [sqlite] Slow performance with Sum function

2009-03-03 Thread Igor Tandetnik

Trainor, Chris 
wrote:
> I am trying to use the Sum function on a column in a table with ~450K
> rows in it.
>
> Select sum(Col4) from Table1
>
> It takes over 2 minutes to execute when using the original table.  I
> created an exact copy of the table with the same indices and
> constraints and inserted all the data from the original table into
> it.  Summing that column on the copied table only takes a few seconds.

Try running VACUUM on your database. Your original table is probably 
badly fragmented and results in excessive disk seeking.

Igor Tandetnik 



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

[sqlite] Slow performance with Sum function

I am trying to use the Sum function on a column in a table with ~450K
rows in it.  

Select sum(Col4) from Table1

Where Table1 looks like this:

Create TABLE Table1 (
Col1 INTEGER NOT NULL,
Col2 INTEGER NOT NULL,
Col3 INTEGER NOT NULL,
Col4 BIGINT NOT NULL,
Col5 BIGINT NOT NULL,
Col6 BLOB NOT NULL,
Col7 CHAR(1) DEFAULT '0',
Col8 NUMERIC(2) NOT NULL,
Col9 NUMERIC(2) NOT NULL,
Col10 INTEGER NOT NULL,
Col11 INTEGER NOT NULL,
CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC)
);


It takes over 2 minutes to execute when using the original table.  I
created an exact copy of the table with the same indices and constraints
and inserted all the data from the original table into it.  Summing that
column on the copied table only takes a few seconds.

I am guessing that using the copied table is faster because it has all
of its data arranged contiguously, but that is just a guess.

Can anyone shed some light on this?  Making a copy of the table is not
an option, so is there anything I can do to get better performance from
the original table?

Thanks
The information contained in this email message and its attachments
is intended
only for the private and confidential use of the recipient(s) named
above, unless the sender expressly agrees otherwise. Transmission
of email over the Internet
 is not a secure communications medium. If you are requesting or
have requested
the transmittal of personal data, as defined in applicable privacy
laws by means
 of email or in an attachment to email you must select a more
secure alternate means of transmittal that supports your
obligations to protect such personal data. If the reader of this
message is not the intended recipient and/or you have received this
email in error, you must take no action based on the information in
this email and you are hereby notified that any dissemination,
misuse, copying, or disclosure of this communication is strictly
prohibited. If you have received
this communication in error, please notify us immediately by email
and delete the original message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query - finding objects with given properties

2009-03-03 Thread inZania

Rich,

You're certainly right.  However I've actually simplified the table
structure here in order to not over-complicate matters.  I'm pretty certain
the schema is good, but if you have the time/willingness to work through the
whole thing with me, my AIM is NeonNinjaSan or ICQ 51549640 - I don't want
to waste your time if you don't have it though.  Cheers.

Also, to answer your question, yes an object with the property "green" might
also have the property "yellow" with a different score.

Rich Shepard wrote:
> 
>Why have the ID columns? If each Object_name and Property_name is
> unique,
> they don't need a surrogate/artificial integer primary key.
> 
>I'm not trying to sound pompous, but your schema may be causing your
> difficulties in writing quick running queries.
> 
> 
>Can an object also have the properties of "yellow," "cool," and "dull?"
> 
>If you are concerned with query running times it's often good to look
> at
> the DDL and see if it's as efficient as can be.
> 
> Rich
> 
> -- 
> Richard B. Shepard, Ph.D.   |  Integrity   
> Credibility
> Applied Ecosystem Services, Inc.|Innovation
>  Voice: 503-667-4517  Fax:
> 503-667-8863
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Query---finding-objects-with-given-properties-tp22312313p22313518.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Query - finding objects with given properties

2009-03-03 Thread inZania


Igor,

Thanks for the help!  However, I'm running into an error with the
sub-queries returning more than 1 result.  I suppose it has to do with the
fact that the property "hot" may have more than 1 entry (one for each
object).  Thanks!


Igor Tandetnik wrote:
> 
> select * from objects where
> object_id in (select object_id from properties where 
> property_name='green' and score > 3) and
> object_id in (select object_id from properties where property_name='hot' 
> and score < 2) and
> object_id in (select object_id from properties where 
> property_name='bright' and score > 0);
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Query---finding-objects-with-given-properties-tp22312313p22313457.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Installing SQLite

2009-03-03 Thread Nikolas Stevenson-Molnar

Mac OS X also comes with SQLite; just open up the Terminal app and  
perform: sqlite3 

_Nik

On Mar 3, 2009, at 7:50 AM, P Kishor wrote:

> On Tue, Mar 3, 2009 at 9:48 AM, Dan M. Wall   
> wrote:
>>
>> I am an experienced Windows user and application developer.  I am  
>> now attempting to actually use a Mac!!  God forbid!!
>
> Welcome to sanity. Don't worry, you will get used to it.
>
>>
>> I have an SQLite database and I'm trying to figure out how to  
>> install SQLite so I can view it and update it.  I downloaded the  
>> bin files but I'm obviously missing something.
>
> step 1. Download the source.
> step 2. type /.configure followed by make && make install
> step 3. there is no step 3.
>
>
>>
>> Please forgive my Mac OSX ignorance!  Any help you can provide  
>> would be greatly appreciated!
>>
>> Dan
>>
>
>
>
>
> -- 
> Puneet Kishor http://www.punkish.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Carbon Model http://carbonmodel.org/
> Open Source Geospatial Foundation http://www.osgeo.org/
> Sent from: Madison WI United States.
> ___
> 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] Installing SQLite

2009-03-03 Thread Jay A. Kreibich

On Tue, Mar 03, 2009 at 07:48:02AM -0800, Dan M. Wall scratched on the wall:
> 
> I am an experienced Windows user and application developer.  I am now
> attempting to actually use a Mac!!  God forbid!!
> 
> I have an SQLite database and I'm trying to figure out how to install
> SQLite so I can view it and update it.  I downloaded the bin files
> but I'm obviously missing something.  

  Mac OS X 10.5 already has SQLite 3.4.0 installed.  That's not exactly
  new, but if all you need to do is open a vanilla file and poke
  around, you could do OK.

  I assume you've already found /Applications/Utilities/Terminal.app,
  and are working in the command line.

  You should be able to use the installed "/usr/bin/sqlite3" shell by just
  issuing the command:

  $ sqlite3 



  If you need to install a newer version, make sure you've got the full
  Development tools installed.  You don't need the IDE for SQLite, but
  you need the compilers.

  If you download and unpack the amalgamation source, you can just
  issue the command:

  $ cc -o sqlite3 sqlite3.c shell.c

  And that will build the sqlite3 binary.  And any defines (-D), like
  SQLITE_ENABLE_FTS, that you might require:

  $ cc -DSQLITE_ENABLE_FTS -DSQLITE_ENABLE_RTREE -o sqlite3 sqlite3.c shell.c

> Please forgive my Mac OSX ignorance!  Any help you can provide would be
> greatly appreciated!

  Unless you're doing desktop stuff with XCode, developing for Mac OS X
  is really about developing for UNIX.  OS X has a more BSD flavor than
  Linux, but knowledge is generally somewhat cross-compatible.

   -j

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

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

Re: [sqlite] Query - finding objects with given properties

2009-03-03 Thread Rich Shepard

On Tue, 3 Mar 2009, inZania wrote:

> The situation is this: there are two tables, one called "objects" (fields:
> object_id[int] and object_name) and properties (fields: object_id[int] and
> property_name and score[int]).  Objects have one or more properties.

   Why have the ID columns? If each Object_name and Property_name is unique,
they don't need a surrogate/artificial integer primary key.

   I'm not trying to sound pompous, but your schema may be causing your
difficulties in writing quick running queries.

> For example, I might look for the object_id of an object who has the
> property "green" scored above 3, the property "hot" scored below 2, and the
> property "bright" scored above 0.  It might have other properties, too, but
> it must have these 3.

   Can an object also have the properties of "yellow," "cool," and "dull?"

   If you are concerned with query running times it's often good to look at
the DDL and see if it's as efficient as can be.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query - finding objects with given properties

2009-03-03 Thread Igor Tandetnik

inZania  wrote:
> The situation is this: there are two tables, one called "objects"
> (fields: object_id[int] and object_name) and properties (fields:
> object_id[int] and property_name and score[int]).  Objects have one
> or more properties.
>
> The objective of the query is to return an object who has all of the
> given properties scored above or below the given value.
>
> For example, I might look for the object_id of an object who has the
> property "green" scored above 3, the property "hot" scored below 2,
> and the property "bright" scored above 0.  It might have other
> properties, too, but it must have these 3.

select * from objects where
object_id in (select object_id from properties where 
property_name='green' and score > 3) and
object_id in (select object_id from properties where property_name='hot' 
and score < 2) and
object_id in (select object_id from properties where 
property_name='bright' and score > 0);

> And then, for the second part, I need a query that will select
> properties which are NOT in this set of given properties but are
> relevant to the returned set of objects.  In this part, I'm not
> concerned with the property's score - just that it is relevant to the
> objects.  In other words, I might now find that the property "hard"
> is relevant to 3/4 objects (even though one of them had a very low
> score for the hard property, another had a very high one, etc).
> Basically I want to know which property, once I can determine the
> score, will help narrow down the object-set the most, so that I can
> collect data for the most relevant property to narrow down the object
> set.

select property_name, count(*) cnt from properties where
object_id in (select object_id from properties p where 
p.property_name='green' and p.score > 3) and
object_id in (select object_id from properties p where 
p.property_name='hot' and p.score < 2) and
object_id in (select object_id from properties p where 
p.property_name='bright' and p.score > 0)
and property_name not in ('green', 'hot', 'bright')
group by property_name
order by cnt desc;

An index on properties(property_name, object_id) will likely help a lot.

Igor Tandetnik 



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

Re: [sqlite] Installing SQLite

2009-03-03 Thread Peter ZhaoKai

http://www.sqlite.org/cvstrac/wiki?p=HowToCompile
For compile on windows, Cygwin could be used, see cygwin section.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query - finding objects with given properties

2009-03-03 Thread inZania

I am attempting to write a query that runs as fast as humanly possible... to
be honest, I'm having a really hard time even getting started with it. I'm
generally decent with SQL but for some reason this one is eluding me in
terms of doing it within a single query, which would help my application's
speed.

The situation is this: there are two tables, one called "objects" (fields:
object_id[int] and object_name) and properties (fields: object_id[int] and
property_name and score[int]). Objects have one or more properties.

The objective of the query is to return an object who has all of the given
properties scored above or below the given value.

For example, I might look for the object_id of an object who has the
property "green" scored above 3, the property "hot" scored below 2, and the
property "bright" scored above 0. It might have other properties, too, but
it must have these 3.

And then, for the second part, I need a query that will select properties
which are NOT in this set of given properties but are relevant to the
returned set of objects. In this part, I'm not concerned with the
property's score - just that it is relevant to the objects. In other words,
I might now find that the property "hard" is relevant to 3/4 objects (even
though one of them had a very low score for the hard property, another had a
very high one, etc). Basically I want to know which property, once I can
determine the score, will help narrow down the object-set the most, so that
I can collect data for the most relevant property to narrow down the object
set.

Thanks so much for your help!
--
View this message in context:
http://www.nabble.com/Query---finding-objects-with-given-properties-tp22312313p22312313.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Read back the specified triggers

2009-03-03 Thread Peter ZhaoKai

sqlite3 test.db  .dump > test.sql
or
sqlite>.output file.sql.
sqlite>.dump
sqlite>.exit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

On Tue, Mar 3, 2009 at 9:48 AM, Dan M. Wall  wrote:
>
> I am an experienced Windows user and application developer.  I am now 
> attempting to actually use a Mac!!  God forbid!!

Welcome to sanity. Don't worry, you will get used to it.

>
> I have an SQLite database and I'm trying to figure out how to install SQLite 
> so I can view it and update it.  I downloaded the bin files but I'm obviously 
> missing something.

step 1. Download the source.
step 2. type /.configure followed by make && make install
step 3. there is no step 3.


>
> Please forgive my Mac OSX ignorance!  Any help you can provide would be 
> greatly appreciated!
>
> Dan
>




-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Installing SQLite

2009-03-03 Thread Dan M. Wall


I am an experienced Windows user and application developer.  I am now 
attempting to actually use a Mac!!  God forbid!!

I have an SQLite database and I'm trying to figure out how to install SQLite so 
I can view it and update it.  I downloaded the bin files but I'm obviously 
missing something.  

Please forgive my Mac OSX ignorance!  Any help you can provide would be greatly 
appreciated!

Dan

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

Re: [sqlite] Performance impact for heavy accessing the database file

2009-03-03 Thread Ribeiro, Glauber

How heavy? How many kg? :)

http://www.sqlite.org/faq.html#q5 
If it's all reads, you're fine, but if anyone is writing, all others are
blocked until that transaction is finished. Locks are at database, not
record or table level.

g

-Original Message-
From: Eversogood [mailto:eversog...@gmail.com] 
Sent: Monday, March 02, 2009 9:25 PM
To: sqlite-users@sqlite.org; sqlite-...@sqlite.org
Subject: [sqlite] Performance impact for heavy accessing the database
file

Hi,

How is the SQLite performance when a lot number of concurrent users
access
the database file?

Thanks,
Ev

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

[sqlite] core dump with TKSQLite

2009-03-03 Thread rahed

Hello,

I installed 3.6.11 on Solaris with 3_6_11-tea. 
When I try to open sqlite db with tksqlite there is core dump:

warning: core file may not match specified executable file.
Core was generated by `wish /export/home/user1/tksqlite'.
Program terminated with signal 10, Bus error.
[New process 79314    ]
#0  0xfe4b4b0c in ?? ()

sqlite from its command line is ok.

Any hints?

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

Re: [sqlite] Wiki page on Management Tools - should it explicitely state Mac OS X support?