[sqlite] PHP + SQLite + NFS = No go?

My VPS provider mounts my /home partition via NFSv3 and it's causing me all kinds of headaches. I have my website served from ~/html/ using SQLite as the DB and I'm constantly seeing hung Apache processes that are threads with an open connection to my SQLite DB. I've read all the caveats about doin

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

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

O Customers VALUES (NULL, 1238, 'Apple'); INSERT INTO Customers VALUES (NULL, 1238, 'Apple'); INSERT INTO Customers VALUES (NULL, 1239, 'Apple'); INSERT INTO Customers VALUES (NULL, 1239, 'Banana'); -- Scott Baker - Canby Telcom System Administrator - RHCE -

[sqlite] .import on a .csv file

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

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: sqli

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

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

Re: [sqlite] CURRENT_DATE Behavior

hat work for you by storing UTC and doing the 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 f

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

/08', 4)) Just add NULL for the primary 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 usi

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 th

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

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] millisecond precision for unixepoch values

something wrong? SQLite 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] Getting a table size without counting

ould be very fast. -- 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

[sqlite] Math Functions

Home FROM Table WHERE PlaceGroup = 3; What mathematical functions can I do at the sqlite level? I'll preface this with, with stock SQLite (no added components) as it's on a shared web host. -- Scott Baker - Canby Telcom RHCE - System Administ

Re: [sqlite] Equivalent of mysql_real_escape_string() ?

. "\n"; I'm open to 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

[sqlite] Very simple table...

x27;). I'm assuming 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

Re: [sqlite] indexing rows from a query

to map rowid in the table 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 firs

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; ht

Re: [sqlite] Timestamp DataType

ate as a format 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 mai

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

10, 2006. 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

[sqlite] SQLite full text speed

ny faster than 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 Administ

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, >> No

Re: [sqlite] Insert date

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 SQ

Re: [sqlite] Faulty "date" function

ECT date('now','-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] Faulty "date" function

u're asking a very vague 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

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] Generating new rowid algo

amount 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] Question on Queries

mid = (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

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

ometimes its hard 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 op

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

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

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

7;; Use % as your wildcard, 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] 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] -

[sqlite] make test problems on Fedora 7

; /tmp/cc9i7nKr.o: In function `test_busy_timeout': /tmp/sqlite-3.5.5/./src/test1.c:4012: undefined reference to `Tcl_AppendResult' /tmp/sqlite-3.5.5/./src/test1.c:4017: undefined reference to `Tcl_GetInt' /tmp/sqlite-3.5.5/./src/test1.c:4019: undefined reference to `Tcl_Appe

Re: [sqlite] order by issue?

to 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] -

Re: [sqlite] How to truncate the hour fraction

y 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] How to truncate the hour fraction

ch','localtime'); 2008-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] Date arithmetic question

gths vary, there is 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 RH

Re: [sqlite] Database Commoditization and SQLite

here 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] 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] 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] Indexes not being used after INNER JOINS?

data(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 truncate the hour

es or 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

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

nded 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

Re: [sqlite] Unix Epoch Time Support

gt; > 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 Admin

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

xpand 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 redun

Re: [sqlite] Spatial searches

RE Lat > 1.2 AND 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 - System Administrator - 503.266.8253 -

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

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

Re: [sqlite] Milliseconds

ves me three decimal points of precision 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] Milliseconds

> fractions 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

[sqlite] DELETE using a join?

ivial to do it with 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

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

Re: [sqlite] baffled by dates

t; > > 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] -

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] running a script?

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

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

r: #1140 - Mixing of GROUP columns (MIN(),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 ... ?

> I expect that 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

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

ster with 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 Tele

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

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 p