Re: [sqlite] using vacuum

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

Re: [sqlite] baffled by dates

> > > Not sure what that is all about -- all the results are blank except one, > ewven though every record is in exactly the same format. > > I'm totally perplexed by date handling in SQLite, so any help 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

[sqlite] DELETE using a join?

a subquery, 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 - 50

Re: [sqlite] Milliseconds

tions of a day, giving a resolution of 1/24855 of a second: > 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 s

Re: [sqlite] Milliseconds

ision for seconds. So that's thousandths of a second? Where do you get 24000ths of a second? -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] SELECT ORDER BY failure

s on any of the tables. Is this why > 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 Bake

[sqlite] Strings vs Integers

Internally does sqlite store the following SQL statements differently? INSERT INTO table (foo) VALUES (1024); vs INSERT INTO table (foo) VALUES ('1024'); -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253

Re: [sqlite] Spatial searches

LAT < 1.4 AND LONG > 5.6 AND LONG < 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 - Sy

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

the capabilities of SQLite" I'm assuming that should say "continuing" 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/obvio

Re: [sqlite] Unix Epoch Time Support

> Are there any plans 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 Administra

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

ommended choice. > > yes, > - 2 for speed and reliability, > - 3 if one 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 ea

Re: [sqlite] How to truncate the hour

times I always store the full unixtime (like you are) and then use the functions date, time, and datetime to get the parts you need. That way you have the most functionality as well as requiring the least amount of storage space (4 bytes for a unixtime). -- Scott Baker - Canby Telc

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

ata(reftemporal_id) > CREATE INDEX idx_esparqueologico_datacao_id ON esparqueologico(datacao_id) I'm not an expert, but don't you want an index on reftemporal.id as well? You're querying it in your JOIN 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] 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 T

Re: [sqlite] Database Commoditization and SQLite

se market 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

free, that there is no reason to use anything else. Period" -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Date arithmetic question

no exactly 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 Administ

Re: [sqlite] How to truncate the hour fraction

-01-28 Lots of good 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

y try 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?

o order a desc, and b by the default (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] -

[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?

ildcard, and you're good to go. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] SQLite Web Site

ll, the page definitely doesn't validate: > http://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

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

ll be. Getting all the data into a PHP data structure should be the way to go. -- 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?

d programatically) reducing the number 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 -

Re: [sqlite] running a script?

; > Thanks, > John > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > --------- > > > > -- Scott Baker -

[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

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 >> > ); >> > >&g

Re: [sqlite] Question on Queries

= (SELECT id FROM MAIN WHERE name = >> "something") ORDER BY ord; >> >> -- Individual Queries >> SELECT id FROM MAIN WHERE name = "something"; >> SELECT data FROM LIST WHERE mid = id_as_returned_above; This just screams inner join. SELECT data

Re: [sqlite] Generating new rowid algo

nt of records are DELETED and then a VACUUM is run do the rowids change? -- 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 --

Re: [sqlite] Faulty "date" function

question. My guess is SQLite returns '2008-03-28' 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

Re: [sqlite] Faulty "date" function

','-14 days'); 2008-03-14 All well documented on the wiki: 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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert date

the following insert > INSERT INTO Sighting (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. Y

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

[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] Who is using SQLite prior to version 3.3.0?

What's the advantage to making all new DBs created format 4? I'm against breaking backwards compatibility. One of the most 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 compatibilit

Re: [sqlite] Timestamp DataType

ormat SQLite 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

Re: [sqlite] Calculating Difference between Time using SQLite

; Since already thank you very 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=DateAndTim

Re: [sqlite] indexing rows from a query

e with row number of the query for large > chunks of the table, 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

[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() ?

discussion about whether or not this is this is still vulnerable to SQL injection. -- 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

QLite version 3.5.9 Enter ".help" for instructions sqlite> select 1219441430151/1000, 1219441430151/1000.0; 1219441430| I don't get a result 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| > &

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

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

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

key value. SQLite will autopopulate 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 Administra

Re: [sqlite] CURRENT_DATE Behavior

convert in your select statement. It will be 100x more accurate. 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 -

[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] 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] 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? >

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>

[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] 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] .import on a .csv file

looks like this: "38665","101977","Deadly Sparrows Inc.","1435 S. Doolis Ln","Donkville","OR","90210","Doolis, Jason","5032349422","Active" Help! -- Scott Baker - Canby Telcom System Administrator

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

[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] Sometimes it really is a hardware problem....

an ISO image. You can make 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 - Ne

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

at the result of the Selectd statement (1) and (2) should be the > same (even in order) > But it does not > > Any idea ? > > Shum > www.mingyik.com > > > > -- Scott Baker Canby Telephone - Network Administrator - RHCE Ph: 503.266.8253