Re: [sqlite] sqlite-users Digest, Vol 38, Issue 4

On 02/04/2011 04:00 AM, sqlite-users-requ...@sqlite.org wrote: On 2/3/2011 12:10 PM, Scott Baker wrote: CREATE Table Customers ( EntryID INTEGER PRIMARY KEY, CustomerID INT, Type ENUM ); #1) Query for customers who*ONLY* bought apples select CustomerID from Customers

[sqlite] How do I query for a specific count of items?

(NULL, 1238, 'Apple'); INSERT INTO Customers VALUES (NULL, 1239, 'Apple'); INSERT INTO Customers VALUES (NULL, 1239, 'Banana'); -- Scott Baker - Canby Telcom System Administrator - RHCE - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] .import on a .csv file

,Deadly Sparrows Inc.,1435 S. Doolis Ln,Donkville,OR,90210,Doolis, Jason,5032349422,Active Help! -- Scott Baker - Canby Telcom System Administrator - RHCE - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin

[sqlite] Is it using an index?

If I have a query: SELECT foo FROM bar WHERE id = 14; How can I see if that query is optimized to use an index, and which index it's using. I thought if you did an EXPLAIN it would show that, but I'm not seeing it? Maybe it's not really using an index? - Scott

Re: [sqlite] Is it using an index?

Eric Minbiole wrote: If I have a query: SELECT foo FROM bar WHERE id = 14; How can I see if that query is optimized to use an index, and which index it's using. I thought if you did an EXPLAIN it would show that, but I'm not seeing it? Maybe it's not really using an index? Use the

Re: [sqlite] Is it using an index?

Gerry Snyder wrote: Scott Baker wrote: I didn't realize INTEGER PRIMARY KEY was case sensitive. Thanks Are you sure what you used before did not have a typo, or the words in a different order? Good question... must have been. Testing it: sqlite CREATE TABLE foo (bar integer primary

Re: [sqlite] REGEXP

Ben Marchbanks wrote: I am confused. Is REGEXP enabled in SQLite or does there have to be a regexp custom function created ? The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally

[sqlite] RoundCube + SQLite?

Has anyone here used RoundCube with SQLite? Apparently it still requries SQLite 2.x and I can't find any modern Linux box that still ships 2.x. I just need to run these commands: http://www.perturb.org/tmp/sqlite.initial.sql And get the 2.x binary DB from it. Is there a way to make SQLite 3

Re: [sqlite] CURRENT_DATE Behavior

. Depending on what you're doing with the dates, I almost always store dates in Unixtime, as they're much easier to work with than a string date value. SQLite works flawlessly with unixtime values also. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] Problem with inserting and integer primary key

the number, but you have to tell it to do it. Just because it's a primary key doesn't mean you can't ALSO provide it a value (like 73). So you have to tell it to pick one itself by using NULL. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] Date/Time Pains

jason weaver wrote: I've searched and searched but haven't found anything that really answers this question. If I've missed something, please point me in the right direction. I want to put the right type of timestamp in my dbase. According to my research, the right type is like this: -

Re: [sqlite] Simple Suggestions

ivo welch wrote: Sqlite is a wonderful program. A big thanks to its creator. As a new user, the following are nuisances, though, so I thought I would register these as simple suggestions: * SHOW columns FROM table--- would be a great addition, if only for compatibility with MySQL.

Re: [sqlite] millisecond precision for unixepoch values

for the second select... -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] millisecond precision for unixepoch values

Igor Tandetnik wrote: Scott Baker [EMAIL PROTECTED] wrote: Did I do something wrong? SQLite version 3.5.9 Enter .help for instructions sqlite select 1219441430151/1000, 1219441430151/1000.0; 1219441430| Works for me. Did you perhaps compile without floating point support, or something

[sqlite] Very simple table...

unixtime would store as less bytes? Are there any inherent speed advantages either way? Do the date functions work faster on either one? -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users

Re: [sqlite] Equivalent of mysql_real_escape_string() ?

::quote method: http://php.web-ster.com/manual/en/pdo.quote.php $conn = new PDO('sqlite:/home/lynn/music.sql3'); $string = 'Nice'; print Quoted string: . $conn-quote($string) . \n; I'm open to discussion about whether or not this is this is still vulnerable to SQL injection. -- Scott Baker

Re: [sqlite] Timestamp DataType

recognizes you can use all the date functions. See the documentation: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Calculating Difference between Time using SQLite

much and greetings. If you convert both dates to unixtime (seconds) and subtract you'll get seconds between the two dates. Then divide by 60. SELECT (strftime('%s','now') - strftime('%s','2004-01-01 02:34:56')) / 60; http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- Scott Baker

Re: [sqlite] indexing rows from a query

, but that can get to be a big memory footprint if some_condition changes often. Can't you just do: SELECT * FROM mytable WHERE some_condition ORDER BY rowid LIMIT 100 OFFSET 0; To get the first 100 rows? -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] Who is using SQLite prior to version 3.3.0?

annoying this about SQLite is that version 3.x can't open version 2.x databases (which unfortunately are still out there). Now we're talking about breaking forwards compatibility... I dunno. I like the idea of keeping things as compatible as possible. -- Scott Baker - Canby Telcom RHCE - System

[sqlite] SQLite full text speed

just a regexp against a flat text file? Obviously you get the advantages of SQL were it in a DB, versus a flat file. What other trade offs are there? My experience the above, is that in SQLITE it's still incredibly fast. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] Insert date

(SpeciesID,LocationID,SightingDate,Note) VALUES (3005,22,'2/26/2008','New Note') the insert works EXCEPT the date keeps coming in as NULL! What am I doing wrong? The date/time documentation details all the formats that SQLite understands. You probably just want: -MM-DD. -- Scott Baker - Canby

Re: [sqlite] Insert date

Scott Baker wrote: lrjanzen wrote: I have the following table CREATE TABLE Sighting ( SightingIdinteger PRIMARY KEY AUTOINCREMENT NOT NULL, SpeciesId integer, LocationIdinteger, SightingDate date, Note nvarchar(100) ); and the following insert INSERT

Re: [sqlite] Faulty date function

' for now(), and then you tell it to -14. Since '2008-03-28' is a string, and you're trying to subtract from that it converts it to a integer. '2008-03-28' converts to 2008 as an integer. 2008 - 14 = 1994. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] Faulty date function

://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to decode a date

[EMAIL PROTECTED] wrote: Hello, I would like to know how to decode a Date when I read a table. The same question for Time. The wiki on this question is quite good (and not just because I editted some of it). http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- Scott Baker

Re: [sqlite] Generating new rowid algo

? -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question on Queries

. SELECT data FROM LIST l INNER JOIN MAIN m ON l.mid = m.id WHERE m.name = something; My advice is ALWAYS to avoid subselects unless you ABSOLUTELY have to use them. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users

Re: [sqlite] which is faster, PHP or SQLite?

. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] which is faster, PHP or SQLite?

of database hits will always speed up your application. Unless of course the data you're loading in RAM is huge. When you're storing megs of data in ram just to speed up your queries you probably should look at other routes for optimization. -- Scott Baker - Canby Telcom RHCE - System Administrator

Re: [sqlite] SQLite Web Site

://validator.w3.org/check?uri=http%3A%2F%2Fsqlite.org I don't know if that's the issue or not, of course. :) I did a quicky patch for the homepage to make it compliant: http://www.perturb.org/tmp/sqlite_homepage.patch It at least validates with my firefox plugin. -- Scott Baker - Canby Telcom RHCE

[sqlite] make test problems on Fedora 7

: undefined reference to `Tcl_GetInt' /tmp/sqlite-3.5.5/./src/test1.c:4019: undefined reference to `Tcl_AppendResult' Etc, etc, etc. I have tcl-devel installed, but I'm assuming I need some other tcl package? Any idea what I need? -- Scott Baker - Canby Telcom RHCE - System Administrator

Re: [sqlite] make test problems on Fedora 7

checking for Tcl configuration... configure: error: yes directory doesn't contain tclConfig.sh -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Using LIKE to check the first digits?

don't need to enter every single one of them, and just assign the prefix, eg. 123 matches 1230001, 1230002, etc. Should I use the LIKE command for this? Does someone have an example to do this? SELECT * FROM Table WHERE Field LIKE '123%'; Use % as your wildcard, and you're good to go. -- Scott

Re: [sqlite] How to truncate the hour fraction

docs here: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] How to truncate the hour fraction

this: sqlite SELECT date(1201561222 - (1201561222 % 86400),'unixepoch','localtime'); 2008-01-27 -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] order by issue?

(which is asc). Which is exactly what the output shows. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Date arithmetic question

science to how many months between these two dates. Otherwise your best bet is what he already recommended. SELECT (julianday(date2) - julianday(date1)) / 30.43666 AS Months; -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] how to select first n records

Rael Bauer wrote: Hi, Can someone tell me how to select first n records from a query (for e.g. Interbase has syntax: rows 1 to n) SELECT * FROM Table LIMIT 10; or SELECT * FROM Table LIMIT 15,10; Shows 10 records, starting at the 15th. -- Scott Baker - Canby Telcom RHCE

Re: [sqlite] Database Commoditization and SQLite

work you've been doing :) I for one welcome our commoditized database market overlords. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Database Commoditization and SQLite

to use anything else. Period -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Indexes not being used after INNER JOINS?

clause, but there's no index on the field. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Re: [Linux + PHP] Recommended way to access SQLite?

needs the latest SQLite I highly recommend PDO for any and all PHP database access that needs doing. It's very full featured, fast, and easy to work with. It's not worth learning the proprietary commands for PHP has for each DBMS. -- Scott Baker - Canby Telcom RHCE - System Administrator

Re: [sqlite] Unix Epoch Time Support

to add this functionality to SQLite? This functionality already exists. Look up datetime processing in the Wiki or checking out my blog post: http://www.perturb.org/display/entry/629/ -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] Request for help with the SQLite Website

not continue Also the parathized quote (Some compiler optimizations such as agressive function inlining and loop unrolling can cause the object code to be much larger.) seems redundant/obvious and could probably be left out. Just my $.02 - Scott -- Scott Baker - Canby Telcom RHCE - System

Re: [sqlite] Spatial searches

6.0? That'll give you a rectangle of values pretty easy. In fact I've implemented that in another database. Pretty easy really. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe

Re: [sqlite] SELECT ORDER BY failure

it is failing? Are you actually searching for records where F is the string ? If so, why don't you try WHERE F=? instead of leaving it with the ? unquoted. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] Milliseconds

: 2^31/(60*60*24) = 24855.134814814814814814814814815 Plenty enough for milli-second resolution. Probably not very good for embedded applications if an FPU is not available. Darn it... I needed 1/24856th second precision... -- Scott Baker - Canby Telcom RHCE - System Administrator

Re: [sqlite] Milliseconds

? -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

[sqlite] DELETE using a join?

, but is there a way to do it with an inner join (wouldn't that be faster). Something like (it doesn't work): DELETE FROM Payments INNER JOIN Users USING (UserID) WHERE UserName = 'John Smith'; -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] baffled by dates

is most appreciated. Thanks, -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

[sqlite] sqlite function list?

Is there a list somewhere (I can't find it on the wiki) of all the functions (specifically math) functions that sqlite understands? I'm thinking things like: int, round, floor, ceil, sqrt etc. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] using vacuum

? Thanks, Charles Li -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] running a script?

- To unsubscribe, send email to [EMAIL PROTECTED] - -- Scott Baker - RHCE Canby Telcom System Administrator 503.266.8253

Re: [sqlite] mixing GROUP and non-GROUP columns in a query

(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause I'm wondering if MySQL isn't right to treat this as an error? -- Scott Baker Canby Telephone - Network Administrator - RHCE Ph: 503.266.8253

[sqlite] Mozilla + SQLite?

Looks like Firefox is gearing up to store some of its information in SQLite? Does anyone know anything more about this? http://gemal.dk/blog/2005/07/06/mozilla_firefox_bookmarks_in_for_a_rewrite/ -- Scott Baker Canby Telephone - Network Administrator - RHCE Ph: 503.266.8253

Re: [sqlite] Quoestion on Order By ... ?

? Shum www.mingyik.com -- Scott Baker Canby Telephone - Network Administrator - RHCE Ph: 503.266.8253

Re: [sqlite] Sometimes it really is a hardware problem....

a bootable cd to test your machine. It makes a great addition to your test tools suite. __ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250 -- Scott Baker Canby Telephone - Network Administrator - RHCE Ph

[sqlite] SQL DateTimes

If I insert a date into a SQLite DB like so: CREATE TABLE TestDate (foo); INSERT INTO TestDate VALUES ('2004-08-19 11:57:41'); and then select the data out: SELECT strftime(%s,foo) FROM TestDate; Output: 1092916661 Which is off by 7 hours, which I'm assuming is because SQLite assumes that the

Re: [sqlite] Row Count

SELECT count(*) FROM Table WHERE Foo = 'bar'; Drew, Stephen wrote: Hello, Is there any way to obtain the number of rows returned by a SELECT statement, before receiving all the rows returned? I am using the sqlite_exec() / sqlite_step() functions to return the data, but need to populate a