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
(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
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
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>
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?
>
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
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
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
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 -
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
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
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
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|
>
&
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
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
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
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
; 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
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
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
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
Scott Baker wrote:
> lrjanzen wrote:
>> I have the following table
>> CREATE TABLE Sighting (
>> SightingIdinteger PRIMARY KEY AUTOINCREMENT NOT NULL,
>> SpeciesId integer,
>> LocationIdinteger,
>> SightingDate
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
','-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
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
[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
--
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
= (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
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 -
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
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
ildcard, and you're good to go.
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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]
-
: 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
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]
-
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]
-
-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]
-
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
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]
-
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]
-
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
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]
-
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
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
> 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
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
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
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
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
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]
-
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
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
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
>
>
> 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]
-
d I check?
>
> Thanks,
> Charles Li
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
;
> Thanks,
> John
>
> -
>
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---------
>
>
>
>
--
Scott Baker -
(),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
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
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
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
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
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
62 matches
Mail list logo