Re: [sqlite] speeding up an FTS5 search with a JOIN

2020-03-08 Thread Puneet Kishor
Update: so, after much hitting of my head against the wall of sql, I came up with the following – as noted above, I really have two distinct set of queries I can do separately like so Q1: (SELECT t1Id FROM t1 WHERE …) AS a Q2: (SELECT t1Id FROM vt1 WHERE vt1 MATCH ‘bar’) AS b Then, I can do

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

2012-04-18 Thread Mr. Puneet Kishor
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 =

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

2012-04-18 Thread Mr. Puneet Kishor
c 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

2012-04-18 Thread Mr. Puneet Kishor
g. 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

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

2012-04-18 Thread Mr. Puneet Kishor
power FROMstations WHERE state = 'CA' ORDER BY power ); -- 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

2012-04-18 Thread Mr. Puneet Kishor
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

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

2012-04-18 Thread Mr. Puneet Kishor
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] auto-incrementing integer in composite primary key

2012-04-16 Thread Puneet Kishor
On Apr 16, 2012, at 5:51 PM, Simon Slavin wrote: > > On 16 Apr 2012, at 11:25pm, Puneet Kishor <punk.k...@gmail.com> wrote: > >> I absolutely don't get any of the above. Why is "keeping the data" worse >> than keeping the commands? I am not even sure wha

Re: [sqlite] auto-incrementing integer in composite primary key

2012-04-16 Thread Puneet Kishor
y, a composite PK), then I could reconstruct exact queries easily. > > Whether you are keeping copies of the rows in the table, or timestamping SQL > commands, I suggest that for SQLite your timestamps should be unixepoch > stored as a REAL rather than a text expression of seconds. &g

Re: [sqlite] auto-incrementing integer in composite primary key

2012-04-16 Thread Puneet Kishor
On Apr 16, 2012, at 3:47 PM, Petite Abeille wrote: > > On Apr 16, 2012, at 10:37 PM, Puneet Kishor wrote: > >> Thanks for your wise words. I am not at all under any illusion that this is >> going to be easy, but it is worthy of an honest try. Two reactions -- >> &

Re: [sqlite] auto-incrementing integer in composite primary key

2012-04-16 Thread Puneet Kishor
On Apr 16, 2012, at 2:45 PM, Petite Abeille wrote: > > On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote: > >> I am trying to create a data versioning system so that a query done at a >> particular time can be reproduced identically as to the original query even >

Re: [sqlite] auto-incrementing integer in composite primary key

2012-04-16 Thread Puneet Kishor
On Apr 16, 2012, at 1:14 PM, Kit wrote: > 2012/4/16 Puneet Kishor <punk.k...@gmail.com>: >> I am experimenting with a home-grown versioning system where every >> "significant" modification to row would be performed on a copy of the row, >> the original bein

Re: [sqlite] auto-incrementing integer in composite primary key

2012-04-16 Thread Puneet Kishor
On Apr 16, 2012, at 1:08 PM, Simon Slavin wrote: > > On 16 Apr 2012, at 6:58pm, Puneet Kishor <punk.k...@gmail.com> wrote: > >> I am experimenting with a home-grown versioning system where every >> "significant" modification to row would be performed o

Re: [sqlite] auto-incrementing integer in composite primary key

2012-04-16 Thread Puneet Kishor
On Apr 16, 2012, at 12:32 PM, Igor Tandetnik wrote: > On 4/16/2012 12:51 PM, Mr. Puneet Kishor wrote: >> >> On Apr 16, 2012, at 11:47 AM, Patrik Nilsson wrote: >> >>> You can use: >>> >>> create table t ( id integer primary key autoincrement, c

Re: [sqlite] auto-incrementing integer in composite primary key

2012-04-16 Thread Mr. Puneet Kishor
d' 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, >>

[sqlite] auto-incrementing integer in composite primary key

2012-04-16 Thread Mr. Puneet Kishor
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

Re: [sqlite] Why are two select statements 2000 times faster than one?

2012-04-13 Thread Puneet Kishor
On Apr 13, 2012, at 3:14 PM, Steinar Midtskogen wrote: > Puneet Kishor <punk.k...@gmail.com> writes: > >> If you want the results in separate columns, you can do something like >> >> SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' >

Re: [sqlite] Why are two select statements 2000 times faster than one?

2012-04-13 Thread Puneet Kishor
Try the following sqlite> EXPLAIN QUERY PLAN SELECT Min(a) FROM t UNION ALL SELECT Max(a) FROM t; selectid|order|from|detail 1|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows) 2|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) Should be a

Re: [sqlite] brain failed: help needed

2012-03-17 Thread Puneet Kishor
You need LEFT JOIN. Also, keep in mind that no operator other than IS NULL or IS NOT NULL works on NULL. On Mar 17, 2012, at 9:06 AM, RhinosoRoss wrote: > > Hi everyone, > Sorry to be a pest, but I'm drowning in manual pages and clearly missing > something simple. I

Re: [sqlite] Bug? Empty string stored as NULL?

2012-03-08 Thread Puneet Kishor
On Mar 8, 2012, at 1:48 PM, Marc L. Allen wrote: > Is that expected? To me, '' is different than NULL. > > create table a > ( >a text > ); > > insert into a select ''; > select * from a; > > Is there a way to store an empty string? > works for me punkish@mumbai ~$sqlite3

Re: [sqlite] why is count of rows in LEFT JOIN more than the rows inthe left table

2012-02-20 Thread Puneet Kishor
On Feb 20, 2012, at 12:46 PM, Jay A. Kreibich wrote: > On Mon, Feb 20, 2012 at 05:01:17PM +, Simon Slavin scratched on the wall: >> >> On 20 Feb 2012, at 4:11pm, Puneet Kishor <punk.k...@gmail.com> wrote: >> >>> is there are way to force the query to

Re: [sqlite] why is count of rows in LEFT JOIN more than the rows inthe left table

2012-02-20 Thread Puneet Kishor
On Feb 20, 2012, at 9:34 AM, Igor Tandetnik wrote: > Puneet Kishor <punk.k...@gmail.com> wrote: >> But, why is #5 329743 and not 329686? Where are the extra 57 rows coming >> from? I thought a LEFT JOIN was supposed to include >> *all* the rows from the left table

[sqlite] why is count of rows in LEFT JOIN more than the rows in the left table

2012-02-20 Thread Puneet Kishor
rows as the rows in the left table. Certainly not more. Seems like my understanding is wrong. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] replacing several nested queries and UNION ALL with a single query

2012-02-19 Thread Puneet Kishor
cumbersome. I am doing one query, then UNIONing it with the results of second query from the complement of the first query then UNIONing that with with results of the third query from the complement of the first and the second query and so on. -- Puneet Kishor

[sqlite] replacing several nested queries and UNION ALLs with one query

2012-02-19 Thread Puneet Kishor
tmp.b1 != '' AND tmp.b2 != AND tmp.b1 = tmp.b2 UNION ALL and so on... -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explanation

2012-02-09 Thread Puneet Kishor
> Do you have an explanation? perhaps because in the second query 'rowid' is being treated as a string and not a column. You might want to try "rowid" instead (double quotes). -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite

Re: [sqlite] ALTER TABLE

2012-02-06 Thread Puneet Kishor
db should care about the order of the column. The results come out in the order you specify. CREATE TABLE t (a, b, c); SELECT b, c, a FROM t WHERE... -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/ma

Re: [sqlite] DBI->connect doesn't fail if no DB

2012-02-02 Thread Puneet Kishor
On Feb 2, 2012, at 2:18 PM, Bill McCormick wrote: > In the following statement ... > >my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","") or croak > $DBI::errstr; > > if $dbfile does not exist, it is created. I would like to croak if this > happens. > > Is there some option for

Re: [sqlite] FIRST/LAST function

2012-02-02 Thread Mr. Puneet Kishor
ut 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 ma

Re: [sqlite] Perl script to output to file

2012-02-01 Thread Mr. Puneet Kishor
> 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-bi

Re: [sqlite] SQLite and Perl

2012-01-26 Thread Puneet Kishor
On Jan 26, 2012, at 2:53 PM, Bill McCormick wrote: > Puneet Kishor wrote, On 1/26/2012 12:02 PM: >> On Jan 26, 2012, at 11:59 AM, Bill McCormick wrote: >> >>> I need to get going with Perl& SQLite and I'm wondering what I need to get >>> off CPAN, if any

Re: [sqlite] SQLite and Perl

2012-01-26 Thread Puneet Kishor
On Jan 26, 2012, at 11:59 AM, Bill McCormick wrote: > I need to get going with Perl & SQLite and I'm wondering what I need to get > off CPAN, if anything. $ cpanm DBD::SQLite there is no step two > > Here's what I think I have now: > > root@fiberio:/etc/cron.daily# locate perl | grep

[sqlite] finding pairwise similarity

2012-01-14 Thread Mr. Puneet Kishor
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

Re: [sqlite] Database Diagram

2011-12-06 Thread Mr. Puneet Kishor
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

Re: [sqlite] Slow INDEX

2011-11-02 Thread Mr. Puneet Kishor
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 >>

Re: [sqlite] Slow INDEX

2011-11-02 Thread Mr. Puneet Kishor
ain 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] Tables as ASCII - is it possible?

2011-10-22 Thread Mr. Puneet Kishor
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

Re: [sqlite] Tables as ASCII - is it possible?

2011-10-22 Thread Mr. Puneet Kishor
nt 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] Error Message near ".": syntax error

2011-10-08 Thread Mr. Puneet Kishor
. 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] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Puneet Kishor
On Oct 7, 2011, at 11:52 AM, Simon Slavin wrote: > > On 7 Oct 2011, at 5:31pm, Puneet Kishor wrote: > >> assuming you have some other application level language you are using to get >> the data, you could stuff the result set into an array and then report the >>

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Puneet Kishor
On Oct 7, 2011, at 9:46 AM, Simon Slavin wrote: > > On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote: > >> SQLite has a non-standard extension whereby aliases assigned to expressions >> in the SELECT clause may be used in the WHERE and other clauses: >> >> select 1+2 as alias from mytable

Re: [sqlite] how to disable a trigger

2011-10-02 Thread Mr. Puneet Kishor
gt; 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] how to disable a trigger

2011-10-02 Thread Mr. Puneet Kishor
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,

Re: [sqlite] speeding up FTS4

2011-09-29 Thread Puneet Kishor
all of the below is really good advice that I shall follow over this weekend. Many thanks. On Sep 29, 2011, at 10:05 AM, Petite Abeille wrote: > > On Sep 29, 2011, at 3:30 PM, Mr. Puneet Kishor wrote: > >> Well, defeated by FTS4 for now, I will try the following approach

Re: [sqlite] speeding up FTS4

2011-09-29 Thread Puneet Kishor
en slog through the uris to winnow out the > matches? > Probably a LOT faster than letting the db do it. > > > > Michael D. Black > Senior Scientist > NG Information Systems > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite

Re: [sqlite] speeding up FTS4

2011-09-29 Thread Mr. Puneet Kishor
, 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

Re: [sqlite] speeding up FTS4

2011-09-28 Thread Puneet Kishor
...> 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 K

Re: [sqlite] speeding up FTS4

2011-09-28 Thread Puneet Kishor
able where the column is INTEGER. sqlite> SELECT typeof(u_downloaded_on) FROM uris LIMIT 1; integer -- 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

2011-09-28 Thread Puneet Kishor
__ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Puneet Kishor [punk.k...@gmail.com] > Sent: Wednesday, September 28, 2011 2:00 PM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] speeding up FTS4 >

Re: [sqlite] speeding up FTS4

2011-09-28 Thread Puneet Kishor
es for project id = 3 or matches throttled by u_downloaded_on (both of which are achievable only via a JOIN with uris table). > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > __

Re: [sqlite] speeding up FTS4

2011-09-28 Thread Puneet Kishor
> Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Puneet Kishor [punk.

Re: [sqlite] EXT : speeding up FTS4

2011-09-28 Thread Puneet Kishor
On Sep 28, 2011, at 9:44 AM, Simon Slavin wrote: > > On 28 Sep 2011, at 3:41pm, Puneet Kishor wrote: > >> WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start) > > Why are you doing 'Datetime' here ? Not only does the conversion take time, > but it mea

Re: [sqlite] EXT : speeding up FTS4

2011-09-28 Thread Puneet Kishor
means. Thankfully I had a backup (clever boy). So, I am now back with a 27 GB file, and a query that takes forever thereby locking up my web app. > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Puneet Kishor [punk.k...@gma

Re: [sqlite] speeding up FTS4

2011-09-28 Thread Puneet Kishor
oject_end DATETIME ); > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Puneet Kishor [punk.k...@gmail.com] > Sent: Tuesday, September 27, 2011 5:46 PM > To: General Discussion of SQLite Database &g

[sqlite] speeding up FTS4

2011-09-27 Thread Puneet Kishor
BLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY Is there anything I can do to speed this up? -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mail

Re: [sqlite] new column

2011-09-25 Thread Mr. Puneet Kishor
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

Re: [sqlite] INSERT syntax missing key SQL syntax

2011-09-17 Thread Mr. Puneet Kishor
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.

Re: [sqlite] INSERT syntax missing key SQL syntax

2011-09-17 Thread Mr. Puneet Kishor
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 >

Re: [sqlite] INSERT syntax missing key SQL syntax

2011-09-17 Thread Mr. Puneet Kishor
ould/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 h

Re: [sqlite] Does coalesce terminate early?

2011-09-15 Thread Puneet Kishor
On Sep 15, 2011, at 11:00 AM, Sam Carleton wrote: > On Thu, Sep 15, 2011 at 10:05 AM, Simon Slavin wrote: >> >> Documentation for COALESCE is here: >> >> http://www.sqlite.org/lang_corefunc.html >> >> It does not say whether it does short-circuit evaluation but the

Re: [sqlite] help with a complicated join of two tables

2011-09-12 Thread Mr . Puneet Kishor
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,

Re: [sqlite] help with a complicated join of two tables

2011-09-11 Thread Mr. Puneet Kishor
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 >&g

[sqlite] help with a complicated join of two tables

2011-09-11 Thread Mr. Puneet Kishor
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

Re: [sqlite] PRAGMA user_version

2011-09-03 Thread Mr. Puneet Kishor
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

[sqlite] speeding up an fts query

2011-08-26 Thread Mr. Puneet Kishor
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 (

Re: [sqlite] Is it okay to use SQLite + PHP for Library Software?

2011-08-15 Thread Mr. Puneet Kishor
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

Re: [sqlite] Will SQLite supports UnQL?

2011-07-30 Thread Mr. Puneet Kishor
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

Re: [sqlite] Simple schema design help

2011-06-29 Thread Mr. Puneet Kishor
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. > >

Re: [sqlite] Compute percentage?

2011-06-29 Thread Mr. Puneet Kishor
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

Re: [sqlite] Selecting indexes to use & NOT INDEXED

2011-06-27 Thread Mr. Puneet Kishor
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

Re: [sqlite] ISNULL in sqlite

2011-06-25 Thread Mr. Puneet Kishor
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]

Re: [sqlite] Indexes on columns

2011-06-24 Thread Mr. Puneet Kishor
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

Re: [sqlite] lol: sqlite3 db smaller than the sum of its contents...

2011-06-23 Thread Mr. Puneet Kishor
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: >> >>>

Re: [sqlite] lol: sqlite3 db smaller than the sum of its contents...

2011-06-23 Thread Mr. Puneet Kishor
On Jun 23, 2011, at 10:18 AM, Stephan Beal 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

Re: [sqlite] Help with CASE WHEN

2011-06-19 Thread Mr. Puneet Kishor
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

Re: [sqlite] Help with CASE WHEN

2011-06-19 Thread Mr. Puneet Kishor
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.

Re: [sqlite] Help with CASE WHEN

2011-06-17 Thread Mr. Puneet Kishor
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

Re: [sqlite] Do I need to migrate to MySQL?

2011-06-04 Thread Mr. Puneet Kishor
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 h

Re: [sqlite] Do I need to migrate to MySQL?

2011-06-03 Thread Mr. Puneet Kishor
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

Re: [sqlite] alter table add column

2011-05-31 Thread Mr. Puneet Kishor
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] using a view for fts

2011-05-26 Thread Mr. Puneet Kishor
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

[sqlite] compartmentalizing FTS4 searches

2011-05-19 Thread Mr. Puneet Kishor
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

Re: [sqlite] time in AM/PM?

2011-05-16 Thread Mr. Puneet Kishor
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 tim

Re: [sqlite] Need to be able to rename attributes

2011-05-15 Thread Mr. Puneet Kishor
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] time in AM/PM?

2011-05-15 Thread Mr. Puneet Kishor
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] FTS4 ranking function in Perl

2011-05-15 Thread Mr. Puneet Kishor
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

Re: [sqlite] Need to be able to rename attributes

2011-05-15 Thread Mr. Puneet Kishor
On May 15, 2011, at 5:05 PM, romtek wrote: > On Sun, May 15, 2011 at 4:39 PM, Simon Slavin 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

[sqlite] selecting unique list of latest timestamps

2011-05-14 Thread Mr. Puneet Kishor
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

Re: [sqlite] Dynamic SQL for SQLite?

2011-05-11 Thread Mr. Puneet Kishor
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 >

Re: [sqlite] Licensing and copyright info?

2011-05-10 Thread Mr. Puneet Kishor
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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Mr. Puneet Kishor
On May 8, 2011, at 11:46 AM, Sam Carleton wrote: > On May 8, 2011, at 11:09 AM, Jean-Christophe Deschamps > 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

Re: [sqlite] Need help understanding how to post to this list

2011-05-03 Thread Mr. Puneet Kishor
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

Re: [sqlite] How to Use an Apostrophe in a Text Field?

2011-04-17 Thread Mr. Puneet Kishor
On Apr 17, 2011, at 5:54 PM, Alan Holbrook 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

Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?

2011-04-04 Thread Mr. Puneet Kishor
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? >> >>

Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?

2011-04-03 Thread Mr. Puneet Kishor
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

Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)

2011-04-01 Thread Mr. Puneet Kishor
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,

Re: [sqlite] using sqlite3_get_table --additional info...

2011-03-31 Thread Mr. Puneet Kishor
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"? > >

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

2011-03-09 Thread Mr. Puneet Kishor
On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote: > Hi there, > > I'm trying this for quite some time... and I'm totally stuck. > > I have the following table: > > CREATE TABLE example( > date integer primary key not null, > text text, > ctime TIMESTAMP, > mtime TIMESTAMP); > >

Re: [sqlite] SELECT (string field) returns part of contents, then asterisks

2011-02-22 Thread Puneet Kishor
On Tue, Feb 22, 2011 at 04:03:22PM +0100, Haldrup Office wrote: > Hello list, > > I'm in the process of writing a little interface tool for notes and > adress databases from an iPad. > > Using MS Word VBA and SQLite3_StdCall.dll. > My query looks quite simply put: > SELECT

Re: [sqlite] COUNT() extremely slow first time!

2011-02-21 Thread Puneet Kishor
On Mon, Feb 21, 2011 at 10:17:03AM -0500, Jim Wilcoxson wrote: > This is a common issue on the mailing list. The first time you do > count(*), SQLite (actually your OS) has to load data into memory. > Most OS's will keep the file in a buffer cache, so the 2nd count(*) > doesn't have to read from

  1   2   3   >