Re: [sqlite] SQLite latest version RPM download location

>> We're planning to upgrade SQLite to the latest version (atleast to v3.5.0) > The latest version is 3.7.3. >> Can somebody point me to the RPM download link location of the same? > SQLite doesn't have RPM. You can download SQLite's sources or prebuild > library from here:

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

Quoth "Jay A. Kreibich" , on 2010-11-10 18:43:06 -0600: > > The observed useful behavior is to have such a reference return the > > value from the first row in each group, > > I haven't verified this since 3.6.23.1, but in that version the > *last* row is the one that is

Re: [sqlite] GROUP BY driving me crazy

Filter based on what? Igor Tandetnik James wrote: > Thanks Igor, that seems to work. It's extremely slow with my real > data though. I'll have to look into that. Also, to actually filter > the products, I would have to add additional joins outside that > sub-select

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

On Wed, Nov 10, 2010 at 03:15:35PM -0700, Drake Wilson scratched on the wall: > The observed useful behavior is to have such a reference return the > value from the first row in each group, I haven't verified this since 3.6.23.1, but in that version the *last* row is the one that is

Re: [sqlite] GROUP BY driving me crazy

I think this might work for me, and provide good performance: SELECT products.name, items.id,images.filename, images.featured FROM products INNER JOIN items ON items.product_id = products.id LEFT JOIN images ON images.item_id = items.id WHERE (images.featured = '1' OR images.featured IS NULL) AND

Re: [sqlite] GROUP BY driving me crazy

Thanks Igor, that seems to work. It's extremely slow with my real data though. I'll have to look into that. Also, to actually filter the products, I would have to add additional joins outside that sub-select correct? On Wed, Nov 10, 2010 at 4:26 PM, Igor Tandetnik wrote:

Re: [sqlite] GROUP BY driving me crazy

James wrote: > This will only display products which have items with images. True. Make it select name, filename from products p left join images im on im.item_id = ( select im2.item_id from items left join images im2 on (items.id = im2.item_id) where items.product_id =

Re: [sqlite] GROUP BY driving me crazy

Thanks Jim, The position is what I wanted to use to control the display order of the images. So yes, which should be first, second, third. Items can have more than one image. If you remove name='White' from your query, you'll see what I mean. I didn't want to rely on something like

Re: [sqlite] Strange WAL mode on 5GB+ database

On 11/11/2010 6:47 AM, Alexey Pechnikov wrote: > $ ls -lh merch.db* > -rw-r--r-- 1 - - 5,8G Ноя 10 23:01 merch.db > -rw-r--r-- 1 - - 32K Ноя 10 23:04 merch.db-shm > -rw-r--r-- 1 - - 449M Ноя 10 23:01 merch.db-wal > > sqlite> pragma journal_mode; > wal > sqlite> pragma journal_mode=delete; >

Re: [sqlite] GROUP BY driving me crazy

If you would explain why/how the position value is significant that might help. I fixed your pseudo SQL to run in SQLite Manager and I don't understand from the sample data what your trying to do. There is only one image per item. Do you have multiple images per item and only want to return the

Re: [sqlite] GROUP BY driving me crazy

This will only display products which have items with images. I think I'm going to sit back and see if there's a simpler way to achieve what I'm trying to do. Maybe I'm going about this the wrong way, or I need to make some compromises. Thanks On Wed, Nov 10, 2010 at 3:01 PM, Igor Tandetnik

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

Quoth Alexey Pechnikov , on 2010-10-20 11:32:04 +0400: > This is just one replacement for "distinct on" clause, as example. And you > can use any sort order for non-aggregate values in your group so some > queries are more simple than equal "distinct on" form in other DBMS

Re: [sqlite] GROUP BY driving me crazy

James wrote: > CREATE products (id, category_id, name, description) > CREATE items (id, product_id, part_number, name, price, buyable) > CREATE images (id, item_id, filename, position) > > INSERT INTO products VALUES (1, 1, 'SQLite T-Shirt'); > INSERT INTO products VALUES (2,

Re: [sqlite] GROUP BY driving me crazy

Sorry, I probably should've been clearer. I do have the data in place to filter by color. The problem is I can't get the image.filenames returned in the desired order. Even if I wanted to use group_concat, I still would want them in a particular order (based on images.position). On Wed, Nov

Re: [sqlite] GROUP BY driving me crazy

There is no logic way to show you intended result. You need some sort of data that can be used as a filter. If you want to filter by color why not add color to the item and use a WHERE clause? Or maybe style? ___ sqlite-users mailing list

Re: [sqlite] GROUP BY driving me crazy

Hi Jim and Igor, Here's basically what the schema looks like: CREATE products (id, category_id, name, description) CREATE items (id, product_id, part_number, name, price, buyable) CREATE images (id, item_id, filename, position) I'm grouping in this case because I only want the unique

Re: [sqlite] Ratio of test code to database code

On Wed, 10 Nov 2010 08:53:40 -0500, Dr. David Kirkby wrote: > Someone recently said he felt that a test:code ratio of 1:1 is about > optimal, so Where do people get notions like this? Cf.: : Someone recently said he felt that programmer productivity is about :

[sqlite] Strange WAL mode on 5GB+ database

$ ls -lh merch.db* -rw-r--r-- 1 - - 5,8G Ноя 10 23:01 merch.db -rw-r--r-- 1 - - 32K Ноя 10 23:04 merch.db-shm -rw-r--r-- 1 - - 449M Ноя 10 23:01 merch.db-wal sqlite> pragma journal_mode; wal sqlite> pragma journal_mode=delete; Error: database is locked sqlite> pragma integrity_check; ok

Re: [sqlite] Question about SQLite features.

Hey "Andy Gibbs". Why your code isn't added into sqlite? - "Andy Gibbs" escreveu: > On Tuesday, November 09, 2010 8:29 AM, Tran Van Hoc wrote: > > > Dear all. > > > > I'm using SQLite and many thanks for your supports. > > > > I have problem about SQLite

Re: [sqlite] specific behavior of busy_timeout with different transaction modes

Thanks, Edzard. For the record, we also found that there is a good explanation of this in the book "Using SQLite" from O'Reilly on pages 153-154. We opened an issue [1] on the Rails project. best, Peter [1] https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/5941 On 11/10/10

Re: [sqlite] GROUP BY driving me crazy

James wrote: > Is this a fairly simple problem and solution? What would you search > for to find solutions to this? That rather depends on what the problem is, which as far as I can tell you've never explained. Show the schema of your tables, and a small sample of the

Re: [sqlite] GROUP BY driving me crazy

Can you better explain your intent? Why are you grouping? This is normally for creating sums, averages, counts etc. Do you have a small sample of input vs output desired? On 11/10/2010 11:11 AM, James wrote: > I've been fighting with this for a couple days now. I've been > searching like mad,

Re: [sqlite] specific behavior of busy_timeout with different transaction modes

On 9-nov-2010, at 18:22 Peter Pawlowski wrote: > While debugging an issue with using SQLite with a Rails > application, we > discovered that the behavior of SQLite when setting the > 'sqlite3_busy_timeout' option was unexpected. > > After reading and grokking the SQLite documentation about

[sqlite] GROUP BY driving me crazy

I've been fighting with this for a couple days now. I've been searching like mad, and thought I found solutions, but nothing seems to work. I think I may have reached the limit of my understanding :) This is just a simplified example of what I'm going after: SELECT products.id, products.name,

Re: [sqlite] Ratio of test code to database code

On 10 Nov 2010, at 4:28pm, Roger Binns wrote: > However some environments can not adequately run TCL to test SQLite which is > why there is TH3 that does the tests in C. That C code is generated with > control over how it is done as for example having it all done at once may > produce something

Re: [sqlite] Ratio of test code to database code

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2010 05:53 AM, Dr. David Kirkby wrote: > Someone recently said he felt that a test:code ratio of 1:1 is about optimal, That of course is bunk. The optimal amount depends on what the project does, the resources available, the consequences of

Re: [sqlite] Question about SQLite features.

"Tran Van Hoc" schrieb im Newsbeitrag news:43a0ef604a674b3fb80d1447b41f8...@isbvietnam.com... [Stored procedures in SQLite] IMO stored procedure-support only makes sense in "Server-Instances" which run on their own (and communicate over different IPC-mechanisms, mainly

[sqlite] Ratio of test code to database code

Sorry if this is a bit off topic, but I read from http://www.sqlite.org/testing.html that your test code is 647 time bigger than the database code. I've often pointed developers of the Sage maths software to your link. I feel more attention should be paid to testing Sage, and less to adding

Re: [sqlite] accessing table column names other then via pragma, or...

Sam Roberts wrote: > On Tue, Nov 9, 2010 at 9:08 PM, Chris Wolf wrote: > >> ...can pragma result sets be accessed in other sql statements? >> >> I wish to use SQLite to perform some data re-formatting, as such, I need >> to output the results >> in CSV format.

Re: [sqlite] Question about SQLite features.

On Tuesday, November 09, 2010 8:29 AM, Tran Van Hoc wrote: > Dear all. > > I'm using SQLite and many thanks for your supports. > > I have problem about SQLite features. > > That's I don't know SQLite have stored procedure support? > How're your C skills? If you are comfortable with the idea,

Re: [sqlite] INSERT OR UPDATE

On 10 Nov 2010, at 8:19am, Michele Pradella wrote: > Hi all, I have to INSERT a row in a DB but I have first to check if the > Key I'm inserting already exist. > Now I'm doing a "SELECT count..." first to check if the key exist and > then INSERT or UPDATE records. > Do you know if there's a

Re: [sqlite] INSERT OR UPDATE

Michele Pradella wrote: > Hi all, I have to INSERT a row in a DB but I have first to check if the > Key I'm inserting already exist. > Now I'm doing a "SELECT count..." first to check if the key exist and > then INSERT or UPDATE records. > Do you know if there's a

[sqlite] INSERT OR UPDATE

Hi all, I have to INSERT a row in a DB but I have first to check if the Key I'm inserting already exist. Now I'm doing a "SELECT count..." first to check if the key exist and then INSERT or UPDATE records. Do you know if there's a better or faster way to do that? Perhaps with an ON CONFLICT