Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 10:27 PM, starvingpilot wrote: Greetings! I am currently working on an iOS app and not too well versed in SQLite. Here's what I am trying to do. I have a table stations and the fields state populated by US States and power which is populated with strings similar to 1.0 kW, 50.0 kW 10.0 kW etc... so the query I need... I need something along the lines of SELECT * FROM stations WHERE state like '%x%' ORDER BY ... Here lies the issue. I need to trim kW from the number and turn it into an integer/double/float what have you so that I can order it by power lowest to highest or vice versa... So essentially I searching for a antennas in a specific state like CA Where x is my placeholder and I'd like to sort them by their power. sqlite CREATE TABLE t (s TEXT, p TEXT); sqlite INSERT INTO t VALUES ('WI', '10.3 kW'); sqlite INSERT INTO t VALUES ('CA', '2.13 kW'); sqlite INSERT INTO t VALUES ('MI', '31.4 kW'); sqlite SELECT * FROM t ORDER BY p; WI|10.3 kW CA|2.13 kW MI|31.4 kW sqlite SELECT s, p || ' kW' p FROM (SELECT s, Cast(Rtrim(p, 'kW') AS 'numeric') p FROM t ORDER BY p); CA|2.13 kW WI|10.3 kW MI|31.4 kW sqlite -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 10:47 PM, starvingpilot wrote: Thanks for the quick reply Puneet. However I had a syntax error on App as well as when I typed that query on my sqlite browser. Here's my syntax SELECT state, power || ' kW' power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROM stations ORDER BY power balance your parens. you are missing a closing parens after ORDER BY power--- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 11:01 PM, starvingpilot wrote: Also, I need to select from a specific state like CA Where is that part in the query? SELECT state, power || ' kW' power FROM ( SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROMstations WHERE state = 'CA' ORDER BYpower ); -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 11:04 PM, starvingpilot wrote: I actually did have the parenthesis there... I didnt copy and paste the last one. It was there however and I am still having problems Well, it is hard to help you unless you tell us exactly what problem you are having. You might want to include the actual command you are running and the actual response you are getting. Without that its just a guessing game. As I showed you with the entire transcript, the query runs just fine. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 11:10 PM, starvingpilot wrote: Here's a query that works sqlStatement = [NSString stringWithFormat:@SELECT * FROM stations WHERE state like '%@ %',theState]; --- this yields a result: 0 sqlStatement = [NSString stringWithFormat:@SELECT state, power || ' kW' power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROM stations ORDER BY power),theState]; I get a result: 1 from that last one... theState in this code is a variable for that specific state I need. I don't see a question above. It's not clear if you are asking something. Wrt to filtering by state, I already sent you an example for that. Hope that helped. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 11:20 PM, starvingpilot wrote: Puneet Kishor-2 wrote: On Apr 18, 2012, at 11:10 PM, starvingpilot wrote: Here's a query that works sqlStatement = [NSString stringWithFormat:@SELECT * FROM stations WHERE state like '%@ %',theState]; --- this yields a result: 0 sqlStatement = [NSString stringWithFormat:@SELECT state, power || ' kW' power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROM stations ORDER BY power),theState]; I get a result: 1 from that last one... theState in this code is a variable for that specific state I need. I don't see a question above. It's not clear if you are asking something. Wrt to filtering by state, I already sent you an example for that. Hope that helped. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Your query netted a result of ALL states... I'd like to search a specific state for example.. I'd like to find antennas in CA only and order by power. didn't you get the following? It returns rows for only 'CA' SELECT state, power || ' kW' power FROM ( SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROMstations WHERE state = 'CA' ORDER BYpower ); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] auto-incrementing integer in composite primary key
Given CREATE TABLE t ( id INTEGER NOT NULL, created_on DATETIME DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (id, created_on) ); how can I make just the 'id' column auto-increment? -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] auto-incrementing integer in composite primary key
On Apr 16, 2012, at 11:47 AM, Patrik Nilsson wrote: You can use: create table t ( id integer primary key autoincrement, created_on DATETIME DEFAULT CURRENT_TIMESTAMP ) No, the above will create a PK on only the 'id' column. I want a composite PK with 'id' and 'created_on' columns, but 'autoincrement' keyword seems to work only with 'primary key' invocation. On 04/16/2012 06:27 PM, Mr. Puneet Kishor wrote: Given CREATE TABLE t ( id INTEGER NOT NULL, created_on DATETIME DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (id, created_on) ); how can I make just the 'id' column auto-increment? -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FIRST/LAST function
On Feb 2, 2012, at 9:46 AM, Petite Abeille wrote: On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote: Does SQLite have FIRST and LAST aggregate function? No, sadly, SQLite doesn't support any analytic functions (aka window function) such as first, last, lead, lag, rank, etc, etc... [1] To achieve the same, you will have to roll your own, which is not always a piece of cake. Oh, well... [1] http://orafaq.com/node/55 to the OP... since you are using Perl (I believe, based on your earlier emails), you can get your data out and then use the most excellent List::Util (https://metacpan.org/module/List::Util). List::MoreUtils (https://metacpan.org/module/List::MoreUtils) and Scalar::Util (https://metacpan.org/module/Scalar::Util) -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Perl script to output to file
On Feb 1, 2012, at 10:20 AM, Bill McCormick wrote: From the sqlite command line interface, the .output command will output the result of a query to a file. How can I do this from a Perl script? I'm not finding any built-in facility to do this in the Perl module. pseudocode ahead 1. open filehandle $fh 2. prepare and execute query $query 3. write results to filehandle `say $fh $query` 4. close filehandle $fh -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] finding pairwise similarity
I have two tables like so CREATE TABLE c (c_no INTEGER PRIMARY KEY, c_name TEXT); CREATE TABLE t (t_no INTEGER PRIMARY KEY, t_name TEXT); CREATE TABLE c_t (c_no INTEGER, t_no INTEGER); Every row in `c` has one or more `t`. I want, in a single SQL, the following source_c, target_c, similarity where `similarity` is a measure of overlap of `t` between each pair of `c` and is calculated as the number of `t` common between a given pair divided by the total number of unique `t` in the pair. For example, given the following records c t --- --- 1 1 1 2 1 3 2 1 2 5 I want the result to be source_c target_c similarity -- 1 2 0.2 (= 1 common `t` between the 2 `c` / by total 4 `t`) Would appreciate any nudge toward a solution. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Diagram
On Dec 6, 2011, at 6:45 AM, priya786 wrote: Hello i want to know how to get the database diagram from sqlite.Please tell me the solution. If you are on a Mac, SQL Editor is a very nice product for about $80. http://www.malcolmhardie.com/sqleditor/ -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow INDEX
On Nov 2, 2011, at 11:24 AM, Fabian wrote: Now if I re-open the database, I can add an additional 10.000 rows very fast (1 sec). But if I reboot the (Windows) PC, and insert an additional 10.000 rows, it takes at least 30 secs, which seems very slow, if I can add the first 1 million in under 10 seconds. Others will have better answers, but methinks that when you reboot the computer, the operating system's caches are flushed out, which slows the operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.) and notice if the speed increases again to what you expect. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow INDEX
On Nov 2, 2011, at 11:31 AM, Fabian wrote: 2011/11/2 Mr. Puneet Kishor punk.k...@gmail.com Others will have better answers, but methinks that when you reboot the computer, the operating system's caches are flushed out, which slows the operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.) and notice if the speed increases again to what you expect. The reason I reboot the PC for the test, is because I want to have the caches flushed out, and I fully expect it to make things slower, but not by the degree (factor 300) i'm experiencing. ahh, so you *are* getting expected behavior, just not what *you* expected. Did you have a different number in mind instead of a factor of 300? And, if so, why? I am genuinely curious -- I know nothing about Windows. I rarely reboot my computer -- my desktop iMac hasn't been rebooted in several weeks now. I did reboot my MacBook Air a few days ago for a software update, but usually that too goes through a few weeks before it is rebooted... when I do reboot them, I experience everything to be slow for the first 10-15 mins or so. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tables as ASCII - is it possible?
On Oct 22, 2011, at 11:06 PM, Paul Linehan wrote: Hi all, Is there a way of storing SQLite data (tables) as ASCII text rather than as binary data? I want to be able to run scripts against my data as well as use SQLite. .. Are you suggesting that you want to treat text data as a SQL data store? You might want to look at DBD::CSV [http://search.cpan.org/~hmbrand/DBD-CSV-0.33/lib/DBD/CSV.pm] -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tables as ASCII - is it possible?
On Oct 22, 2011, at 11:34 PM, Paul Linehan wrote: If I could go with a scripting language, it would be Python - vastly superior IMHO to Perl - YMMV. Yup. My mileage does vary. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error Message near .: syntax error
On Oct 8, 2011, at 8:28 PM, James Brison wrote: I am receiving the following error message when running a query against a sqlite db: Error Message near .: syntax error What does this mean? and how do I debug it? I'm new to sqlite and don't understand the 'near'. Is it saying that I have syntax error somewhere involving '.'? Wouldn't it have been super-easy to actually show us your query? How can anyone divine what is wrong when no knows what your query is. You are likely missing a comma, or have an extra space... please resend with the actual query that causes the above error. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
On Oct 2, 2011, at 10:12 PM, Sam Carleton wrote: Ok, how do I list what a trigger is so that I can add it back once I want to reactive it? You are looking to temporarily deactivate a TRIGGER, but there is no such mechanism. You could simply copy the code for the TRIGGER, then DROP it, and then add it back again when you need it. Generally the idea behind a TRIGGER is that it fires on the set event without any intervention. Giving the ability to deactivate it would make it discretional, so there isn't any SUSPEND TRIGGER command. On Sun, Oct 2, 2011 at 9:07 PM, Igor Tandetnik itandet...@mvps.org wrote: Sam Carleton scarle...@miltonstreet.com wrote: Is there any way to disable a trigger in sqlite? DROP TRIGGER -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
On Oct 2, 2011, at 10:29 PM, Igor Tandetnik wrote: Sam Carleton scarle...@miltonstreet.com wrote: Is there any way to disable a trigger in sqlite? If you have control over the trigger's definition, you could do something like this: create trigger MyTrigger on ... when (select enabled from TriggerControl where name='MyTrigger') begin ... end; where TriggerControl(name text, enabled integer) is a table with a row for each trigger you want to manage. You can effectively turn a trigger on and off with update TriggerControl set enabled=? where name='MyTrigger'; very clever. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
Well, defeated by FTS4 for now, I will try the following approach -- 1. drop the fts tables and rebuild them and test. 2. if the above doesn't work, then either migrate the data to Postgres and use its fts, or implement e-Swish or httpdig for full text search. On Sep 28, 2011, at 4:35 PM, Puneet Kishor wrote: On Sep 28, 2011, at 4:18 PM, Black, Michael (IS) wrote: I have no idea if this would work...but...here's some more thoughts... #1 How long does this take: select count(*) from fts_uri match 'education school'; #2 Create a view on uris with just what you need and use that in your join (I'm guessing that uri_content takes up most of your database space). create view v_uris as select uri_id,feed_history_id from uri; .. snipped a bunch of stuff I did a query on just the fts table and got the answers relatively quickly. Not instantly, but very fast compared to all the attempts so far. So, assuming that the bottleneck is the multiple JOINs to get the data for the correct project_id, I created a temp table with all that JOIN nonsense sqlite CREATE TEMP TABLE tmp_uris AS SELECT u.uri_id, u.uri uri, u.u_downloaded_on ... FROM projects p ... JOIN feeds f ON f.project_id = p.project_id ... JOIN feed_history fh ON f.feed_id = fh.feed_id ... JOIN uris u ON fh.feed_history_id = u.feed_history_id ... WHERE p.project_id = 3 AND u.u_downloaded_on = p.u_project_start; CPU Time: user 16.369556 sys 81.393235 sqlite EXPLAIN QUERY PLAN SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on, ... Snippet(fts_uri, 'span class=hilite', '/span', 'hellip;', -1, 64) snippet ... FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id ... WHERE fts_uri MATCH 'education school' ... ORDER BY u.uri_id, u_downloaded_on DESC; 0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows) 0|1|1|SEARCH TABLE tmp_uris AS u USING INDEX tmp_uris_uri_id (uri_id=?) (~10 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY CPU Time: user 0.86 sys 0.06 and yet sqlite SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on, ... Snippet(fts_uri, 'span class=hilite', '/span', 'hellip;', -1, 64) snippet ... FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id ... WHERE fts_uri MATCH 'education school' ... ORDER BY u.uri_id, u_downloaded_on DESC; CPU Time: user 21.871541 sys 26.414337 A lot better, but simply not usable for a web application. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] new column
On Sep 25, 2011, at 10:53 PM, 守株待兔 wrote: there are tow column x1,x2 in table t ,all real alter table t add column c1 real; select x1+x2 as c1 from t; there ara output in my screen,but in the database, c1 column has no value,how can i make the value of c1 column =x1+x2? UPDATE t SET c1 = x1 + x2; That said, you have a basic misunderstanding of SQL, so some tutorials, many freely available on the internet, would help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT syntax missing key SQL syntax
On Sep 17, 2011, at 10:11 AM, Igor Tandetnik wrote: Jim Michaels jimm-VsnNql4zhRrV7NJZ79vff+jYdJvx94icpqFO/160wmvqt0dzr+a...@public.gmane.org wrote: INSERT is supposed to handle multiple rows for VALUES. Supposed by whom? What is the basis for this claim? The multiline INSERT capability may not be a SQL standard, but it is not only highly convenient, it is also supported by Pg, the ostensible role model and inspiration for SQLite. Not that I particularly care either way because I almost always use Perl to deal with Pg and SQLite, but it would/might be nice to have for some. Perhaps those folks should continue to ask Richard nicely instead of seeming to demand it on the basis of some political claim. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT syntax missing key SQL syntax
On Sep 17, 2011, at 10:33 AM, Simon Slavin wrote: On 17 Sep 2011, at 4:29pm, Mr. Puneet Kishor wrote: The multiline INSERT capability may not be a SQL standard, but it is not only highly convenient, it is also supported by Pg, the ostensible role model and inspiration for SQLite. Not that I particularly care either way because I almost always use Perl to deal with Pg and SQLite, but it would/might be nice to have for some. Perhaps those folks should continue to ask Richard nicely instead of seeming to demand it on the basis of some political claim. How would you support multiple INSERTs with binding ? I wouldn't. Multiple INSERT statement seems to be better suited for large scale dump/restore, or even for manual entry, but without binding. Essentially, if I had my druthers, I would support whatever Pg supports and be done with it. For most part SQLite seems to follow good for Pg good for the gander philosophy. Given a statement like INSERT INTO table(digit,dialpadstr) VALUES (2,'abc'),(3,'def'),(4,'ghi'),(5,'jkl'),(6,'mno'),(7,'pqrs'),(8,'tuv'),(9,'wxyz'); would you expect 16 binding values ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT syntax missing key SQL syntax
On Sep 17, 2011, at 1:06 PM, Simon Slavin wrote: On 17 Sep 2011, at 6:42pm, Petite Abeille wrote: On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote: As was clear from my post, I was referring to SQL standards. What various implementation vendors choose to do is up to them. But the multi-spec syntax referred to in the OP is not in any SQL standard I've seen. In BNF Grammar for ISO/IEC 9075:1999 - Database Language SQL (SQL-99), under contextually typed row value expression list: http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list insert columns and source::= from subquery | from constructor | from default ? Which one, and where is the expansion that allows for multiple sets of brackets after VALUES ? All this chattering among us doesn't really matter (other than for academic purposes). All that matters is whether or not Richard and co. deem it worth including as a capability in factory provided SQLite. While I can't attest to it, there are probably instances where SQLite deviates from the so called standard (the problem with standards is there are plenty of them and all that). For me, if Pg does it, the SQLite could do it if those who make SQLite could be convinced of its usefulness. For me, I don't care either way. I am glad Pg has it because I am trying to convert MySQL data to Pg. While I am failing to do so painlessly for the most part, a few of the (simpler) tables convert fine because both MySQL and Pg support multi-line INSERTs. Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help with a complicated join of two tables
On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: Mr. Puneet Kishor punk.k...@gmail.com wrote: The table geo can also have rows with min_age = max_age. I want a result set with geo.id, min_age, max_age, age_bottom, age_top, name, color like so: - every row should be for one and only one geo record. I have 39K rows in geo table, so the result set should have 39K rows. - when min_age = max_age, list the corresponding intervals.name and color - when min_age != max_age, find the interval with the smallest different between age_bottom and age_top that would span min_age and max_age of geo. In other words, the interval whose age_bottom is bigger than the age_bottom of the max_age and whose age_top is smaller than the age_top of the min_age. Something like this: select geo.id, min_age, max_age, age_bottom, age_top, name, color from geo left join intervals i on i.id = ( select id from intervals where age_bottom = (select age_bottom from intervals where name = geo.max_age) and age_top = (select age_top from intervals where name = geo.min_age) order by (age_bottom - age_top) limit 1 ); Thanks Igor. The above does work and produces the correct result. The query speed, however, is pretty slow ~ 75 seconds. So, I created indexes on intervals.name, geo.max_age, and geo.min_age, and that brought the query time to ~ 11 seconds. Still too slow. So, I created an intermediate table to hold the results, and the speed is more satisfactory. Many thanks again. Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] help with a complicated join of two tables
Apologies in advance for a terrible subject line -- I didn't know quite how to phrase it better. I have the following two tables (with sample data) CREATE TABLE geo ( id INTEGER PRIMARY KEY, max_age TEXT, min_age TEXT ); geo table: 39K rows id max_age min_age --- --- 1 HoloceneHolocene 5 CambrianSilurian 12 CambrianOrdovician 229 Cretaceous Quaternary CREATE TABLE intervals ( id INTEGER PRIMARY KEY, age_bottom REAL, age_top REAL, name TEXT, color TEXT ); intervals table: ~450 rows id age_bottom age_top namecolor --- -- --- --- --- 3 0.0117 0. Holocene#FEF2E0 105 443.7000416.Silurian#B3E1B6 112 488.3000443.7000Ordovician #009270 421 2.5880 0. Quaternary #F9F97F 122 542.488.3000Cambrian#7FA056 33 145.500065.5000 Cretaceous #7FC64E Keep in mind, max_age is older than min_age, and age_bottom is older than age_top. The table geo can also have rows with min_age = max_age. I want a result set with geo.id, min_age, max_age, age_bottom, age_top, name, color like so: - every row should be for one and only one geo record. I have 39K rows in geo table, so the result set should have 39K rows. - when min_age = max_age, list the corresponding intervals.name and color - when min_age != max_age, find the interval with the smallest different between age_bottom and age_top that would span min_age and max_age of geo. In other words, the interval whose age_bottom is bigger than the age_bottom of the max_age and whose age_top is smaller than the age_top of the min_age. I've gotten so far SELECT g.id, max_age, min_age, i.age_bottom, i.age_top, i.interval_name, i.interval_color FROM geo g LEFT JOIN intervals i_max ON g.max_age = i_max.interval_name LEFT JOIN intervals i_min ON g.min_age = i_min.interval_name JOIN intervals i ON i.age_bottom = i_max.age_bottom AND i.age_top = i_min.age_top WHERE g.id = ? ORDER BY i.age_bottom - i.age_top LIMIT 1; The above query finds the correct values for a single g.id, but I want a result set with all the rows. Suggestions? Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help with a complicated join of two tables
On Sep 11, 2011, at 9:58 PM, Igor Tandetnik wrote: Mr. Puneet Kishor punk.k...@gmail.com wrote: geo table: 39K rows id max_age min_age --- --- 1 Holocene Holocene 5 Cambrian Silurian 12 Cambrian Ordovician 229 Cretaceous Quaternary intervals table: ~450 rows id age_bottom age_top name color --- -- --- --- --- 3 0.0117 0. Holocene #FEF2E0 105 443.7000 416. Silurian #B3E1B6 112 488.3000 443.7000 Ordovician #009270 421 2.5880 0. Quaternary #F9F97F 122 542. 488.3000 Cambrian #7FA056 33 145.5000 65.5000 Cretaceous #7FC64E Keep in mind, max_age is older than min_age, and age_bottom is older than age_top. The table geo can also have rows with min_age = max_age. I want a result set with geo.id, min_age, max_age, age_bottom, age_top, name, color like so: - every row should be for one and only one geo record. I have 39K rows in geo table, so the result set should have 39K rows. - when min_age = max_age, list the corresponding intervals.name and color - when min_age != max_age, find the interval with the smallest different between age_bottom and age_top that would span min_age and max_age of geo. In other words, the interval whose age_bottom is bigger than the age_bottom of the max_age and whose age_top is smaller than the age_top of the min_age. I'm not sure I understand. Let's take geo.id = 5, max_age=Cambrian, min_age=Silurian. You say you want a record whose age_bottom is greater than that corresponding to Cambrian, that is 542.; and whose age_top is smaller than that corresponding to Silurian, or 416.. I don't seem to see any such record in your example. Hi Igor, I muddied the issue by providing limited sample data that doesn't have an answer that fits my needs. Needless to say, in the complete tables there would be answers. My (slightly simplified) query below shows the kind of result I want SELECT g.id, max_age, min_age, Min(i.age_bottom - i.age_top) age_range, i.interval_name, i.interval_color FROM geo g LEFT JOIN intervals i_max ON g.max_age = i_max.interval_name LEFT JOIN intervals i_min ON g.min_age = i_min.interval_name JOIN intervals i ON i.age_bottom = i_max.age_bottom AND i.age_top = i_min.age_top GROUP BY g.gid, g.max_age, g.min_age, i.interval_name, i.interval_color ORDER BY g.gid, age_range; produces the following --- g.idmax_age min_age age_range interval_name interval_color --- --- --- --- --- -- 1 Paleozoic Paleozoic 291.Paleozoic #99C08D 1 Paleozoic Paleozoic 542.Phanerozoic #9AD9DD 4 Precambrian Precambrian 3458. Precambrian #F04370 5 CambrianSilurian291.Paleozoic #99C08D 5 CambrianSilurian542.Phanerozoic #9AD9DD 6 SilurianSilurian27.7000 Silurian#B3E1B6 6 SilurianSilurian291.Paleozoic #99C08D .. 94K+ rows What I want from the above table is only the first row of each g.id group because that has the interval that has the smallest age_range that spans the max_age and min_age Sorry, I am not able to articulate this more clearly, but I hope the above example makes things clearer. Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA user_version
On Sep 3, 2011, at 8:21 PM, Walter wrote: On 9/2/2011 11:02 PM, Walter wrote: Is there any way to get the user_version from an Attached database PRAGMA attachedName.user_version; -- Igor Tandetnik Thank you Igor I had the database name but did not think of the the dot in between. Perhaps some one could update the documentation to show this Cheers everyone Walter it is a pretty common SQL convention, using periods to separate the database_from.schema_from.table_from.column You don't have to specify the left-side qualifier if the entity on the right of the period can be located unambiguously. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] speeding up an fts query
I have the following schema (slightly simplified for this post) CREATE TABLE projects ( project_id INTEGER PRIMARY KEY, project_start DATETIME ); CREATE TABLE feeds ( feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id INTEGER ); CREATE TABLE feed_history ( feed_history_id INTEGER PRIMARY KEY, feed_id INTEGER ); CREATE TABLE uris ( uri_id INTEGER PRIMARY KEY, uri_content TEXT, downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP, feed_history_id INTEGER ); CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content); The db file is about 3 GB, with 79 entries in the `feeds` table and 6847 entries in the `uris` table. The following query takes way too long -- SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on FROM fts_uri f JOIN uris u ON f.uri_id = u.uri_id JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id JOIN feeds f ON f.feed_id = f.feed_id JOIN projects p ON f.project_id = p.project_id WHERE p.project_id = 3 AND Datetime(u.downloaded_on) = Datetime(p.project_start) AND fts_uri MATCH ? ORDER BY u.uri_id, downloaded_on DESC; EXPLAIN on the above query is as below, but I don't know how to read EXPLAIN's output. Could one of you suggest on what index I might have to make/use to speed up the query? 0|Trace|0|0|0||00| 1|OpenEphemeral|5|4|0|keyinfo(2,BINARY,-BINARY)|00| 2|Integer|3|1|0||00| 3|Goto|0|58|0||00| 4|VOpen|0|0|0|vtab:7FF44A407B38:10D01E5C0|00| 5|OpenRead|1|9|0|6|00| 6|OpenRead|2|6|0|0|00| 7|OpenRead|4|2|0|6|00| 8|OpenRead|3|3|0|3|00| 9|String8|0|4|0|education|00| 10|Integer|4|2|0||00| 11|Integer|1|3|0||00| 12|VFilter|0|43|2||00| 13|VColumn|0|0|6||00| 14|MustBeInt|6|42|0||00| 15|NotExists|1|42|6||00| 16|Column|1|5|7||00| 17|MustBeInt|7|42|0||00| 18|NotExists|2|42|7||00| 19|MustBeInt|1|42|0||00| 20|NotExists|4|42|1||00| 21|Column|1|4|2||00| 22|Function|0|2|9|datetime(-1)|01| 23|Column|4|5|3||00| 24|Function|0|3|10|datetime(-1)|01| 25|Lt|10|42|9||6a| 26|Rewind|3|42|0||00| 27|Rowid|3|8|0||00| 28|Ne|8|41|8||6b| 29|Column|3|2|10||00| 30|Ne|1|41|10|collseq(BINARY)|6b| 31|Rowid|1|11|0||00| 32|Column|1|1|12||00| 33|Column|1|4|13||00| 34|MakeRecord|11|3|10||00| 35|Rowid|1|14|0||00| 36|Column|1|4|15||00| 37|Sequence|5|16|0||00| 38|Move|10|17|1||00| 39|MakeRecord|14|4|8||00| 40|IdxInsert|5|8|0||00| 41|Next|3|27|0||01| 42|VNext|0|13|0||00| 43|Close|0|0|0||00| 44|Close|1|0|0||00| 45|Close|2|0|0||00| 46|Close|4|0|0||00| 47|Close|3|0|0||00| 48|OpenPseudo|6|10|3||00| 49|Sort|5|56|0||00| 50|Column|5|3|10||00| 51|Column|6|0|11||20| 52|Column|6|1|12||00| 53|Column|6|2|13||00| 54|ResultRow|11|3|0||00| 55|Next|5|50|0||00| 56|Close|6|0|0||00| 57|Halt|0|0|0||00| 58|Transaction|0|0|0||00| 59|VerifyCookie|0|21|0||00| 60|TableLock|0|9|0|uris|00| 61|TableLock|0|6|0|feed_history|00| 62|TableLock|0|2|0|projects|00| 63|TableLock|0|3|0|feeds|00| 64|Goto|0|4|0||00| ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it okay to use SQLite + PHP for Library Software?
On Aug 16, 2011, at 1:23 AM, Darren Duncan wrote: Raouf Athar wrote: I have to develop a Library Management System using *PHP* for a medium sized college library. The library has about 5,000 members and 50,000 books. On an average, about 500 members will look for books and will be issued books on daily basis. *Kindly let me know if it is okay to use SQLite?**Kindly add a few words in support of your answer.* *Can you give me links/ references of a few applications supported by SQLite?* Have you looked at existing projects before starting a new one? Look at Evergreen, http://open-ils.org/ which is an open source project developed by and for libraries, and that has been used in production for a few years now in many libraries. You should adapt this or modify it to meet your needs rather than start a new one, unless you can justify otherwise. Yeah, while SQLite can be used to happily power your library system, try finding an existing open source solution, adapting it, making it better, and contributing your enhancements back for everyone to benefit. Consider Evergreen. Also look at bibapp.org, and I am sure there are many other. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
On Jul 31, 2011, at 6:04 AM, Roger Binns wrote: A different JSON database server product used 4GB and 6 minutes and I never bothered optimising for it. Perhaps more shocking is that the other product would usually answer unindexed queries faster than CouchDB did with indices, mostly helped by this database fitting entirely in memory. could you please tell what this other JSON database server product was? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compute percentage?
On Jun 29, 2011, at 6:53 AM, Roger Andersson wrote: On 06/29/11 12:34 PM, Gilles Ganault wrote: Thanks, that worked: SELECT COUNT(*) FROM people; 400599 SELECT COUNT(*) FROM people WHERE zip=12345; 12521 SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) FROM people WHERE zip=12345; 3 Is it possible to display the number with decimals instead of an integer? SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM people),2) FROM people WHERE zip=12345; SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) * 1.00 AS percentage FROM people WHERE zip=12345; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple schema design help
On Jun 29, 2011, at 10:35 AM, Ian Hardingham wrote: Hey guys. I have an existing table, the matchTable, where each entry holds a lot of information about a match. I am adding a tournament system, and each match will either be in a tournament or not in a tournament. Should I add a tournamentID column to matchTable? Or should I create a new tournamentMembershipTable which holds a simple relationship between matchid and tournament id? If each match will belong to only one tournament while each tournament may relate to more than one match then create a tournatmentID in the match table. If you leave that NULL or, say, 0, then that match will not be in any tournament, else will be in the tournament with that tournamentID. On the other hand, if each match may relate to zero or more tournaments, and each tournament may relate to zero or more matches, then create a separate tournamentMembershipTable. Thanks, Ian ___ 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] Selecting indexes to use NOT INDEXED
On Jun 27, 2011, at 11:18 AM, Mohit Sindhwani wrote: select * FROM Objects, Objects_Index WHERE Objects.id = Objects_Index.id AND minx = 668632 + 250 AND maxx = 668632 - 250 AND miny = 1518661 + 250 AND maxy = 1518661 - 250 AND CAT=25; Doing an explain query plan revealed that SQlite was scanning Objects with the IDX_OBJ_CAT first and then using the R-Tree. Without restricting by CAT, we found the query was much faster and was using only the virtual R-Tree index. We wanted to get SQLite to use the R-Tree first, so we tried a few different things - eventually, we killed the IDX_OBJ_CAT index and the query became (yes, wait for it) almost 100x faster! Try something like SELECT * FROM ( SELECT * FROM Objects, Objects_Index WHERE Objects.id = Objects_Index.id AND minx = 668632 + 250 AND maxx = 668632 - 250 AND miny = 1518661 + 250 AND maxy = 1518661 - 250 ) WHERE CAT=25; You might have to prefix the columns with the appropriate table names (I can't tell which table cat and the bounds are coming from). If the bounds are a part of the Objects table, you could try SELECT * FROM ( SELECT * FROM Objects WHERE minx = 668632 + 250 AND maxx = 668632 - 250 AND miny = 1518661 + 250 AND maxy = 1518661 - 250 ) foo, Objects_Index WHERE foo.Id = Objects_Index.id AND CAT=25; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ISNULL in sqlite
On Jun 25, 2011, at 3:33 PM, logan...@gmail.com wrote: Hello, How do I check for a null or empty string in SQLite. SQL server has ISNULL but it doesn't seem to be supported in SQLite. ifnull() and nullif() [http://www.sqlite.org/lang_corefunc.html] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on columns
On Jun 24, 2011, at 1:58 PM, logan...@gmail.com wrote: Sorry, but seems like I'm missing something here. From my understanding it looks like for Integer ID columns that are PK SQLite doesn't generate any indexes. Is this true? No, what you think is not true. SQLite does generate an index for INTEGER PRIMARY KEY columns. If the above is true then I want to create an index to improve the perf of my queries that are run against it. Thanks, Hitesh On Fri, Jun 24, 2011 at 5:31 AM, Igor Tandetnik itandet...@mvps.org wrote: logan...@gmail.com wrote: Yes, that's exactly what it is. Here is the definition of one of the table: CREATE TABLE [Attributes] ( [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Name] VARCHAR(50) NOT NULL ) Will creating explicit index on Id fix this issue? What issue? Why is having an explicit index, separate from that built into the table itself, important to you? What exactly do you feel is wrong with the way things are now? -- 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] lol: sqlite3 db smaller than the sum of its contents...
On Jun 23, 2011, at 10:18 AM, Stephan Beal sgb...@googlemail.com wrote: Hi, all! Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db and the db file is only 400kb. HTF can that possibly be? After poking around i found that the wiki files actually total 360kb (when i added up their sizes manually, as opposed to using 'df' to get it), and the extra 80kb were from the hard drive's large block size (slack space reported by 'df'). Kinda funny, though, that sqlite3 actually decreases the amount of storage required in this case. Lots of small files will take up more space because of the fixed minimum block size. For large corpuses this won't matter. Putting them all in one db makes logistical management easier, but you will lose the ability to update just a single file individually. I used to store all my wiki files (punkish.org) in one SQLite db, but now I have them as separate files which allows me to just ssh and edit a single file easily. Six of one, and all that. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ 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] lol: sqlite3 db smaller than the sum of its contents...
On Jun 23, 2011, at 10:49 AM, Jean-Denis Muys jdm...@kleegroup.com wrote: On 23 juin 2011, at 16:22, Mr. Puneet Kishor wrote: On Jun 23, 2011, at 10:18 AM, Stephan Beal sgb...@googlemail.com wrote: Hi, all! Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db and the db file is only 400kb. HTF can that possibly be? After poking around i found that the wiki files actually total 360kb (when i added up their sizes manually, as opposed to using 'df' to get it), and the extra 80kb were from the hard drive's large block size (slack space reported by 'df'). Kinda funny, though, that sqlite3 actually decreases the amount of storage required in this case. Lots of small files will take up more space because of the fixed minimum block size. For large corpuses this won't matter. Putting them all in one db makes logistical management easier, but you will lose the ability to update just a single file individually. I used to store all my wiki files (punkish.org) in one SQLite db, but now I have them as separate files which allows me to just ssh and edit a single file easily. Six of one, and all that. Let me add two other drawbacks as well: - incremental backups: now everytime you change one small file, the whole database needs to be backed up, increasing needlessly storage size, and backup time. This applies to system that do versioning as well as backups (such as Time Machine). - system level indexing: it now becomes much more difficult, if not impossible, to do system level indexing and searching (as eg in Spotlight). This is the reason why Apple stopped using a monolithic database for its email application, now storing each mail individually: so that system-wide user search can hit emails too. Yup. Very good points, both of them. I still use the db for metadata, but my files are stored in a tree directory structure much like CPAN's directories -- /path/1/12/123/filename.txt where 1, 2, and 3 are the first, second and third letters of the filename. I could store the metadata per file within each file, however, I haven't yet found a way to find the ten most recently edited files or find all files edited by person name. These two drawbacks may or may not apply to your situation. Jean-Denis ___ 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] Help with CASE WHEN
On Jun 19, 2011, at 5:12 PM, jose isaias cabrera wrote: Nico Williams wrote... On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor punk.k...@gmail.com wrote: The above is not SQL. You can't have a SQL statement begin with CASE. SQL statements can only begin with either SELECT or UPDATE or CREATE or DELETE or ALTER, etc. CASE is an expression, and has to be a replacement for a column. I can't even begin to help you rewrite your statement, but what you are trying to accomplish is something like -- if (some condition) UPDATE this else UPDATE that Either accomplish the above in a programming language, or rewrite it as separate queries, or hope Igor or someone can help you rewrite the above into a single query. Rewrite the statements as: INSERT ... WHERE ... AND some condition; Similarly for SELECT, UPDATE, and DELETE. I have rewritten the code: BEGIN IMMEDIATE TRANSACTION; UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .10,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'PM' AND PSubClass != 'Rush-Job'), Xtra8 = (SELECT round(sum(Xtra8) * .10,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'PM' AND PSubClass != 'Portal-Fee' AND PSubClass != 'Rush-Job'), Xtra6 = '10% of total', XtraB = '2011-06-19 18:02:16' WHERE subProjID = 9144 AND lang = 'ES-LA' AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job'; UPDATE LSOpenJobs SET ProjFund = CASE PSubClass WHEN 'Portal-Fee' THEN CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' WHEN 5000 THEN SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' WHEN BETWEEN 5000 AND 2 THEN SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' ELSE 0.0 END END; COMMIT TRANSACTION; This is what I get when I run that code: sqlite sqlite BEGIN IMMEDIATE TRANSACTION; sqlite sqlite UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .10,2) FR OM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'PM' AND PSubClass != 'Rush-Job'), ... Xtra8 = (SELECT round(sum(Xtra8) * .10,2) FROM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'PM' ... AND PSubClass != 'Portal-Fee' ... AND PSubClass != 'Rush-Job'), ... Xtra6 = '10% of total', ... XtraB = '2011-06-19 18:02:16' ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job'; sqlite sqlite UPDATE LSOpenJobs SET ProjFund = ... CASE PSubClass ... WHEN 'Portal-Fee' THEN ... CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs ...WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'Portal-Fee' ... WHEN 5000 THEN ... SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'Portal-Fee' ... WHEN BETWEEN 5000 AND 2 THEN ... SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'Portal-Fee' ... ELSE ... 0.0 ... END ... END; Error: near SELECT: syntax error sqlite COMMIT TRANSACTION; sqlite Try to do the two queries separately and see which one croaks. Most likely it is the second UPDATE query. You might need to enclose the sub-SELECTs (the ones in the CASE and WHENs) in parens. From the docs Scalar Subqueries A SELECT statement enclosed in parentheses may appear as a scalar quantity. A SELECT used as a scalar quantity must return a result set with a single column. The result of the expression is the value of the only column in the first row returned by the SELECT statement. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the expression is NULL. All types of SELECT statement, including aggregate and compound SELECT queries (queries with keywords like UNION or EXCEPT) are allowed as scalar subqueries. [http://www.sqlite.org/lang_expr.html] I am trying to hit two things in one shot. Here is the idea, if a project has a Portal-Fee charge, then if the total of Xtra8 is 5000, the ProjFund portal fee is 1.75% of the ProjFund, if = 5000, but less then 2, then 1.25%, otherwise Portal-Fee is 0. I know how to do it programmatically, but, I would like
Re: [sqlite] Help with CASE WHEN
On Jun 19, 2011, at 10:04 PM, jose isaias cabrera wrote: Mr. Puneet Kishor wrote... On Jun 19, 2011, at 5:12 PM, jose isaias cabrera wrote: Nico Williams wrote... On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor punk.k...@gmail.com wrote: The above is not SQL. You can't have a SQL statement begin with CASE. SQL statements can only begin with either SELECT or UPDATE or CREATE or DELETE or ALTER, etc. CASE is an expression, and has to be a replacement for a column. I can't even begin to help you rewrite your statement, but what you are trying to accomplish is something like -- if (some condition) UPDATE this else UPDATE that Either accomplish the above in a programming language, or rewrite it as separate queries, or hope Igor or someone can help you rewrite the above into a single query. Rewrite the statements as: INSERT ... WHERE ... AND some condition; Similarly for SELECT, UPDATE, and DELETE. I have rewritten the code: BEGIN IMMEDIATE TRANSACTION; UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .10,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'PM' AND PSubClass != 'Rush-Job'), Xtra8 = (SELECT round(sum(Xtra8) * .10,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'PM' AND PSubClass != 'Portal-Fee' AND PSubClass != 'Rush-Job'), Xtra6 = '10% of total', XtraB = '2011-06-19 18:02:16' WHERE subProjID = 9144 AND lang = 'ES-LA' AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job'; UPDATE LSOpenJobs SET ProjFund = CASE PSubClass WHEN 'Portal-Fee' THEN CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' WHEN 5000 THEN SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' WHEN BETWEEN 5000 AND 2 THEN SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' ELSE 0.0 END END; COMMIT TRANSACTION; This is what I get when I run that code: sqlite sqlite BEGIN IMMEDIATE TRANSACTION; sqlite sqlite UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .10,2) FR OM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'PM' AND PSubClass != 'Rush-Job'), ... Xtra8 = (SELECT round(sum(Xtra8) * .10,2) FROM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'PM' ... AND PSubClass != 'Portal-Fee' ... AND PSubClass != 'Rush-Job'), ... Xtra6 = '10% of total', ... XtraB = '2011-06-19 18:02:16' ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job'; sqlite sqlite UPDATE LSOpenJobs SET ProjFund = ... CASE PSubClass ... WHEN 'Portal-Fee' THEN ... CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs ...WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'Portal-Fee' ... WHEN 5000 THEN ... SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'Portal-Fee' ... WHEN BETWEEN 5000 AND 2 THEN ... SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'Portal-Fee' ... ELSE ... 0.0 ... END ... END; Error: near SELECT: syntax error sqlite COMMIT TRANSACTION; sqlite Try to do the two queries separately and see which one croaks. Most likely it is the second UPDATE query. You might need to enclose the sub-SELECTs (the ones in the CASE and WHENs) in parens. From the docs Your-re right, the second one croaks. When I use the parens, it appears to work, but, I now find a new error. sqlite UPDATE LSOpenJobs SET ProjFund = ... CASE PSubClass ... WHEN 'Portal-Fee' THEN ... CASE (SELECT round(sum(Xtra8),2) FROM LSOpenJobs ...WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'Portal-Fee') ... WHEN 5000 THEN ... (SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'Portal-Fee') ... WHEN BETWEEN 5000 AND 2 THEN ... (SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs ... WHERE subProjID = 9144 AND lang = 'ES-LA' ... AND PSubClass != 'Portal-Fee' ) ... ELSE ... 0.0 ... END ... END; Error: near : syntax error Apparently, WHEN does not like or . It just wants one value. It also does not like BETWEEN
Re: [sqlite] Help with CASE WHEN
On Jun 17, 2011, at 4:56 PM, jose isaias cabrera wrote: CASE (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') WHEN 5000 THEN UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') = 5000), Xtra8 = (SELECT round(sum(Xtra8) * .0175,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') = 5000), Xtra6 = '1.75% of total', XtraB = '2011-06-17 17:40:05', XtraD = '1.75%' WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass = 'Portal-Fee' WHEN BETWEEN 5000 AND 2 THEN UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') 5000), Xtra8 = (SELECT round(sum(Xtra8) * .0125,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') 5000), Xtra6 = '1.25% of total', XtraB = '2011-06-17 17:40:05', XtraD = '1.25%' WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass = 'Portal-Fee' ELSE UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .0,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') = 2), Xtra8 = (SELECT round(sum(Xtra8) * .0,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') = 2), Xtra6 = 'No fee charged', XtraB = '2011-06-17 17:40:05', XtraD = '0.00%' WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass = 'Portal-Fee' The above is not SQL. You can't have a SQL statement begin with CASE. SQL statements can only begin with either SELECT or UPDATE or CREATE or DELETE or ALTER, etc. CASE is an expression, and has to be a replacement for a column. I can't even begin to help you rewrite your statement, but what you are trying to accomplish is something like -- if (some condition) UPDATE this else UPDATE that Either accomplish the above in a programming language, or rewrite it as separate queries, or hope Igor or someone can help you rewrite the above into a single query. Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
On Jun 3, 2011, at 6:16 PM, Darren Duncan wrote: Mr. Puneet Kishor wrote: On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: MySQL should be avoided like the plague. why? This is a long standing (un)conventional wisdom to which I too have hewed. Now, it so happens, I will be starting work on a project that uses MySQL exclusively, and has done so for years. They have been very happy with it. And, even though I feel like telling them that they should move to Pg, I don't really know what the reasons are. I am not sure if all the reasons that might be, are still valid. Of course, I don't want this to become a long, religious threat that might be inappropriate for this list, or bore most of the other readers to tears. But, it merits asking, why should MySQL be avoided like the plague? It is a strong statement that requires backing evidence, else it would be construed FUD. Perhaps my statement was a bit strong, so I will clarify a bit. You don't say. Should be avoided like the plague is way more than a bit, hence, my response. *And* I'll give concrete examples. 1. Firstly, the context for my statement is someone who is not currently using MySQL, and so they don't already have an investment in it and codebase designed for it. If one is already using MySQL, then that is the status quo and the question is on whether benefits from a change to something else is worth the effort or not. But if one is not already using it, and their current DBMS can't be used how they need, then they have to change anyway and the question is between whether to move to MySQL or to something else instead; I am addressing this latter situation, and you'll notice I also said sticking with SQLite is even better if its usage can be fixed. The same could be said of anything... if its usage can be fixed is a clause that can be interpreted widely. 2. I consider MySQL to be an 80% solution. It does the job for which it is used adequately in many cases, and it is successfully used in many places, including to drive many businesses and organizations for mission-critical purposes. If it is does the job for which it is used adequately then it is a 100% solution. .. a. MySQL silently ignores all CHECK constraints in all engines, so for example you can't even tell it you want a column to only hold values between 1 and 10. Its in the MySQL docs: The CHECK clause is parsed but ignored by all storage engines. Yes and no. Apparently the above was true before 5.0.2, but apparently it has been fixed since then. From the docs, Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that remains the default behavior, but you can change the server SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur. and In MySQL 5.0.2 and up, you can select stricter treatment of input values by using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes: SET sql_mode = 'STRICT_TRANS_TABLES'; SET sql_mode = 'STRICT_ALL_TABLES'; b. That's just an example of how MySQL silently ignores lots of errors or silently changes data on you, such as silently truncating text values that are too long for a field when you insert them, so you've lost data without even knowing it. (Okay, I knew about this one previously.) Shown by another poster to not be true. No point in going through all the issues you pointed out. I am sure some of them are true, while others no longer true, or true but have work-arounds. .. I say avoid MySQL like the plague because it will bite you in so many ways, while an alternative like Postgres will only bite you in relatively few and less painful ways. Postgres is more of a 90-95% solution relative to MySQL's 80%, assuming that there is no 100% solution. I also know quite a number of savvy people in the developer communities who have used both Postgres and MySQL, and a vast majority of those prefer Postgres and strongly recommend it over MySQL when one has a choice, and so do I. The main thing is to be aware of as much as possible, and then figure out work-arounds. No technology is perfect. There could be serious, project-specific show-stoppers that would cause one to choose a particular tech over another, but those would be project-specific. I have no love for MySQL. All I want to emphasize is that statements like avoid it like the plague become sound-bytes with no substance. They are hollow and misleading as Having problems? Get a Mac or, on the other side, Don't use Macs for serious work or Perl is dead or Apple is dead and the like. These statements are fine for bloggers and tech journalists to make for their aim is to get page-hits and their opinions are usually worthless anyway. All that said, I too would personally choose SQLite
Re: [sqlite] Do I need to migrate to MySQL?
On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: MySQL should be avoided like the plague. why? This is a long standing (un)conventional wisdom to which I too have hewed. Now, it so happens, I will be starting work on a project that uses MySQL exclusively, and has done so for years. They have been very happy with it. And, even though I feel like telling them that they should move to Pg, I don't really know what the reasons are. I am not sure if all the reasons that might be, are still valid. Of course, I don't want this to become a long, religious threat that might be inappropriate for this list, or bore most of the other readers to tears. But, it merits asking, why should MySQL be avoided like the plague? It is a strong statement that requires backing evidence, else it would be construed FUD. Use Postgres instead if you have to switch to a larger SQL DBMS. But hopefully the help you've gotten so far will extend your mileage with SQLite and you won't have to switch to anything yet. -- Darren Duncan Ian Hardingham wrote: Guys, the server for this game - http://www.frozensynapse.com uses SQLite. We've had an unexpectedly successful launch which has resulted in the server being swamped with players, and I'm trying to optimise everywhere I can. I've always been under the impression that SQLite is pefectly fast and it's the scripting language I wrote the server in which is too blame. (Yes, I know writing a back-end in a single-threaded scripting language is an absolutely terrible idea). However, everyone in the industry I talk to says that SQLite must be one of the problems. I may be looking at a complete re-write. I may also need to have a solution which scales beyond one machine. Can anyone give me advice on this matter specifically? (The video on that website at 2.04 gives a good idea of what kind of functions are being powered by the database). Thanks, Ian ___ 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] alter table add column
On May 31, 2011, at 10:11 AM, Fabio Spadaro wrote: Alter table add column command drop data from table. Can you keep the data or should I store the data before the alter and then put them in the table? ALTER TABLE ADD COLUMN does not drop data from the table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] using a view for fts
Consider CREATE VIRTUAL TABLE fts_text USING fts4 (id, content); .. populate the above table, then .. SELECT rowid, Snippet(fts_text) FROM fts_text WHERE fts_text MATCH ?; If I try to do something like CREATE VIEW v_find AS SELECT rowid, Snippet(fts_text) content FROM fts_text; SELECT rowid, content FROM v_find WHERE fts_text MATCH ?; I understandably get an error no such column fts_text. The above is a contrived example, but can I build a view out of a complicated fts query so I can then pass parameters to the view later on? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] compartmentalizing FTS4 searches
My program stores a bunch of text in an FTS4 table and makes it available for search. The wrinkle is, there are conceptually different projects for which the search has to be compartmentalized. CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT); CREATE TABLE documents (document_id INTEGER PRIMARY KEY, document_name TEXT .., project_id INTEGER); CREATE VIRTUAL TABLE fts_docs USING fts4 (document_id, content); Now, when I search for terms, I would like to target only the content for a particular project. Would something like the following work -- SELECT Snippet(fts_docs), f.document_id FROM fts_docs f JOIN documents d ON f.document_id = d.document_id JOIN projects p ON p.project_id = d.project_id WHERE fts_docs MATCH ? AND p.project_name = 'this old project'; or, is there some other way to restrict the MATCH search to only certain rows in the fts table? Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] time in AM/PM?
On May 15, 2011, at 9:24 PM, Igor Tandetnik wrote: Mr. Puneet Kishor punk.k...@gmail.com wrote: I am trying to get time stamps to be reported as '10:33 AM' and '1:27 PM' instead of '10:33' or '13:27'. I don't see any formatting options to return the time in 12-hour format with AM/PM suffixed. Am I missing something, or do I have to roll my own? You are not missing anything. You'll have to roll your own. Assuming 't' is a DATETIME column, the following seems work -- CASE WHEN CAST(strftime('%H', t, 'localtime') AS INTEGER) = 12 THEN strftime('%H:%M', t, 'localtime') || ' PM' WHEN CAST(strftime('%H', t, 'localtime') AS INTEGER) 12 THEN strftime('%H:%M', t, '-12 Hours', 'localtime') || ' PM' ELSE strftime('%H:%M', t, 'localtime') || ' AM' END ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need to be able to rename attributes
On May 15, 2011, at 5:05 PM, romtek wrote: On Sun, May 15, 2011 at 4:39 PM, Simon Slavin slav...@bigfraud.org wrote: On 15 May 2011, at 10:33pm, romtek wrote: So, I am asking developers of SQLite to make it easy for tool developers to offer the ability to rename attributes. The SQL specification does not use the term 'attribute' in any way that would give them names. Can you explain what you mean by 'rename attributes' ? Perhaps give an example. Simon. OK, I will give you an example, and you correct my use of the terms, please. A table: id, eventName, date I want to rename date to dateAdded. sqlite doesn't support changing the name of a table column (and, neither you nor your user should be doing this -- there is something strange with your app requirements). That said, you can rename a column by creating a new table with the new column definitions and copy data from the old table to the new table. CREATE TABLE new_table (id, eventName, dateAdded); INSERT INTO new_table (id, eventName, dateAdded) SELECT id, eventName, date FROM old_table; People currently jump through hoops in order to achieve such a simple (from the user's point of view) and needed goal: http://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table . ___ 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] FTS4 ranking function in Perl
Has anyone implemented the search result ranking function in Perl? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] time in AM/PM?
I am trying to get time stamps to be reported as '10:33 AM' and '1:27 PM' instead of '10:33' or '13:27'. I don't see any formatting options to return the time in 12-hour format with AM/PM suffixed. Am I missing something, or do I have to roll my own? Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need to be able to rename attributes
On May 15, 2011, at 10:49 PM, romtek wrote: Let's not make this issue into something that it's not. Let's not. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] selecting unique list of latest timestamps
I have a bunch of uris stored in a table CREATE TABLE uris ( uri_id INTEGER PRIMARY KEY, uri TEXT ); uri_id uri -- -- 1 http://foo.com 2 http://bar.com 3 http://baz.com 4 http://qux.com A program periodically downloads the content of the above web sites. Another table stores the download history, that is, when the content was last downloaded. CREATE TABLE history ( history_id INTEGER PRIMARY KEY, uri_id INTEGER, downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP ); history_id uri_id downloaded_on -- -- --- 1 4 2011-05-04 02:25:09 2 3 2011-05-03 02:34:08 3 1 2011-05-01 02:50:43 4 2 2011-05-02 02:50:45 6 4 2011-05-14 02:50:48 The content itself is stored in an FTS4 table linked by history_id. CREATE VIRTUAL TABLE fts_uri ( history_id, content ); I am looking for an efficient way to select the uris, and the latest 'downloaded_on' time stamp for each uri, but am drawing a blank. The list should look like so uri_id uri downloaded_on -- -- --- 1 http://foo.com 2011-05-01 02:50:43 2 http://bar.com 2011-05-02 02:50:45 3 http://baz.com 2011-05-03 02:34:08 4 http://qux.com 2011-05-14 02:50:48 Suggestions? Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dynamic SQL for SQLite?
On May 11, 2011, at 7:37 PM, John wrote: Igor, What you are suggesting will not work. You can only select values not columns using case. select case strftime('%w', 'now') when 0 then sunday_value when 1 then monday_value ... else saturday_value end from seven_days; You must be new here. You have to understand the first rule of SQL. Igor is never wrong. His query works just fine. See below -- sqlite CREATE TABLE seven_days ( ... mon INTEGER, ... tue INTEGER, ... wed INTEGER ... ); sqlite INSERT INTO seven_days VALUES (5, 3, 2); sqlite INSERT INTO seven_days VALUES (1, 4, 3); sqlite INSERT INTO seven_days VALUES (7, 8, 3); sqlite SELECT CASE Strftime('%w', 'now') ... WHEN 1 THEN mon ... WHEN 2 THEN tue ... ELSE wed ... END AS day_val ... FROM seven_days; day_val -- 2 3 3 sqlite On Wed, May 11, 2011 at 8:30 PM, Igor Tandetnik itandet...@mvps.org wrote: On 5/11/2011 8:14 PM, John wrote: I am in situation where I need to keep as much logic as possible within SQLite. However the query that I need to perform does not seem to be possible to perform. let's say I have a table with columns for each day of the week create table seven_days (monday_value integer, tueday_value integer, wednesday_value integer, ... ); I want to select value from whatever day it is today. So if today is Tuesday, select from tuesday_value. Can I do it with pure SQlite? select case strftime('%w', 'now') when 0 then sunday_value when 1 then monday_value ... else saturday_value end from seven_days; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- ~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] Licensing and copyright info?
On May 10, 2011, at 1:59 PM, Don Ireland wrote: I am writing an app and plan to embed SQLite in my app as a means to store the data. What licensing/copyright statements do I need to make RE SQLite? Nothing. sqlite, the program, is in the Public Domain. SQLite, the term, is trademarked. Embed sqlite in your app, don't call your app SQLite or something that can be confused with the term SQLite, make loads of money, become a millionaire, and then do some public good with it. Go forth and multiply. TIA! Don Ireland ___ 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] Determining how many columns were returned in a query
On May 8, 2011, at 11:46 AM, Sam Carleton wrote: On May 8, 2011, at 11:09 AM, Jean-Christophe Deschamps j...@antichoc.net wrote: How does one go about finding out how many rows a query returns? This is the number of time sqlite3_step can be called successfully until it returns SQLITE_DONE. I had it wrong in the email body, I meant how many columns are in query? Since, ostensibly, you are the one who queried in the first place, shouldn't you know that already? Maybe there is more to this question that you are not stating. Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help understanding how to post to this list
On May 3, 2011, at 6:04 PM, Rolf Marsh wrote: Hello.. I just joined today and can't seem to figure out how to start a new thread... Can someone please enlighten me? You just did. Just post a question with the subject line indicating clearly what is bothering you and take a seat. The doctor will be with you shortly. Regards, Rolf ___ 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 Use an Apostrophe in a Text Field?
On Apr 17, 2011, at 5:54 PM, Alan Holbrook isaac.laqu...@gmail.com wrote: I'm using SQLite with VBE2008. I've defined a table with a number of text fields in it. If the information I want to write to the database contains an embedded apostrophe, the program throws an error. That is, if I set textfield1 to *Going Down the Road Feeling Bad*, the data gets written correctly and the program continues. But if I set textfield1 to *Goin' Down the Road Feelin' Bad*, I get an error. Is there a way I can use an apostrophe in the data to be written? Escape the apostrophe using the conventions of your VBE2008, or better yet, use bind values. ___ 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] Question:how to insert row with multiple values from same field of different rows of another table?
On Apr 4, 2011, at 7:59 AM, Colin Cuthbert wrote: From: punk.k...@gmail.com Date: Sun, 3 Apr 2011 07:52:42 -0500 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table? On Apr 3, 2011, at 7:50 AM, Luuk wrote: On 03-04-2011 14:43, Colin Cuthbert wrote: First time I've used this (or any!) mailing list, so sorry if I've done something wrong. Pretty sure my question (in the subect) is phrased badly but it's the best I could do! create table People(id integer primary key, name text); insert into People (name) values ('bob'); insert into People (name) values ('fred'); create table Cars(id integer primary key, name text); insert into Cars (name) values ('ford'); insert into Cars (name) values ('volvo'); create table CarOwners(id integer primary key, carId integer references Cars(id), ownerId integer references People(id)); insert into CarOwners (carId, ownerId) select Cars.id, People.id from Cars, People where Cars.name='ford' and People.name='bob'; create table Couples(id integer primary key, personId1 integer references People(id), personId2 integer references People(id)); The last 'insert' statement seems to work for inserting a row into the 'CarOwners' table, but I'm not sure that's the right/best way to do it. But how can I do a similar insert into the 'Couples' table? ie, how can I insert a row (specifying 'personId1' and 'personId2' via queries based on 'People.name') into the 'Couples' table? You forgot to define 'Couples'. Does it start something like this? insert into Couples (personId1, personId2) select id, id from People where... select id, id from People will return the same id (from the same record) twice somehting like: select a.id, b.id from People a join People b on a.idb.id will give other results, but what youactually want to be returned depends on the definition of a 'Couple'... You also want to do all of the above in a TRANSACTION, preferably with a TRIGGER, to ensure the correct relationships are preserved. Ok I looked into transactions (I'm new to sql!). Isn't a transaction automatically created with the insert statement? Or are you saying I need to explicitly begin/end one as part of the solution to my problem? Yes, you need explicit BEGIN/END to perform a transaction. And regarding a trigger to ensure the correct relationships are preserved... yeah you're right, but that's another issue isn't it? Or is it related to this issue in a way that I'm not seeing? Another, but related issue. Regarding your original problem, the following works INSERT INTO Couples (personId1, personId2) VALUES ((SELECT id FROM People WHERE name = 'bob'), (SELECT id FROM People WHERE name = 'fred')); sqlite SELECT * FROM Couples; id personId1 personId2 -- -- -- 1 1 2 Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
On Apr 3, 2011, at 7:50 AM, Luuk wrote: On 03-04-2011 14:43, Colin Cuthbert wrote: First time I've used this (or any!) mailing list, so sorry if I've done something wrong. Pretty sure my question (in the subect) is phrased badly but it's the best I could do! create table People(id integer primary key, name text); insert into People (name) values ('bob'); insert into People (name) values ('fred'); create table Cars(id integer primary key, name text); insert into Cars (name) values ('ford'); insert into Cars (name) values ('volvo'); create table CarOwners(id integer primary key, carId integer references Cars(id), ownerId integer references People(id)); insert into CarOwners (carId, ownerId) select Cars.id, People.id from Cars, People where Cars.name='ford' and People.name='bob'; create table Couples(id integer primary key, personId1 integer references People(id), personId2 integer references People(id)); The last 'insert' statement seems to work for inserting a row into the 'CarOwners' table, but I'm not sure that's the right/best way to do it. But how can I do a similar insert into the 'Couples' table? ie, how can I insert a row (specifying 'personId1' and 'personId2' via queries based on 'People.name') into the 'Couples' table? You forgot to define 'Couples'. Does it start something like this? insert into Couples (personId1, personId2) select id, id from People where... select id, id from People will return the same id (from the same record) twice somehting like: select a.id, b.id from People a join People b on a.idb.id will give other results, but what youactually want to be returned depends on the definition of a 'Couple'... You also want to do all of the above in a TRANSACTION, preferably with a TRIGGER, to ensure the correct relationships are preserved. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)
On Mar 31, 2011, at 2:28 PM, Doug Currie wrote: On Mar 31, 2011, at 2:27 PM, Mike Rychener wrote: I have tried the latest Explorer and it gets a syntax error on STDEV. However, that function works in Eclipse just fine, to take the standard deviation of a column (like min, max, avg). Is there a workaround or other fix available? See http://www.sqlite.org/contrib extension-functions.c Is there a guide on how to compile the above extension-functions.c into sqlite, so it is always available without have to do load the extension explicitly? Pointers appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] using sqlite3_get_table --additional info...
On Mar 31, 2011, at 9:30 AM, john darnell wrote: BTW, if there is a better way to get a row count without using sqlite3_get_table() that would also work. maybe I am missing something, but what is wrong with SELECT Count(*) FROM table? _ From: john darnell Sent: Thursday, March 31, 2011 9:27 AM To: 'General Discussion of SQLite Database' Subject: using sqlite3_get_table All I need to do is see how many rows a table has. I stumbled across this function and used it thusly in my code (I removed the error checking for the sake of brevity): Result = sqlite3_initialize(); sqlite3 *db_ptr; Result = 0; Result = sqlite3_open_v2(DBEnginePath, db_ptr, SQLITE_OPEN_READWRITE, NULL); char ***CArray = NULL; int iRow, iCol; char **err = NULL; sqlite3_get_table(db_ptr, Select * from Admin, CArray, iRow, iCol, err); Unfortunately, when I execute the sqlite_get_table call, I get an error message telling me that I have an unhandled error. It says that the error is occurring here: struct unixShm { unixShmNode *pShmNode; /* The underlying unixShmNode object */ unixShm *pNext;/* Next unixShm with the same unixShmNode */ u8 hasMutex; /* True if holding the unixShmNode mutex */ u16 sharedMask;/* Mask of shared locks held */ u16 exclMask; /* Mask of exclusive locks held */ #ifdef SQLITE_DEBUG u8 id; /* Id of this connection within its unixShmNode */ #endif I am QUITE CERTAIN that my problem lies with the way I am declaring the arrays, but not having an example to teach me, I have no idea what the correct method is. If anyone has an example of how he or she is using sqlite3_get_table that he or she wouldn't mind sharing, I would be appreciative. TIA! R, John A.M. Darnell Senior Programmer Walsworth Publishing Company Brookfield, MO John may also be reached at johnamdarn...@gmail.commailto:johnamdarn...@gmail.com Trivia question: Who saved Gandalf from his imprisonment at the Tower of Isengard in book 1 of The Lord of the Rings (i.e. The Fellowship of the Ring)? ___ 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