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 > group by CustomerID > having sum(Type = 'Apple')>0 and sum(Type != 'Apple')=0; > >> > #2) Query for customers who bought apples*AND* bananas > select CustomerID from Customers > group by CustomerID > having sum(Type = 'Apple')>0 and sum(Type = 'Banana')>0; > >> > #3) Query for customers who bought exactly 2 apples? > select CustomerID from Customers > group by CustomerID > having sum(Type = 'Apple') = 2; > > -- or > > select CustomerID from Customers > where Type = 'Apple' > group by CustomerID > having count(*) = 2; This is exactly what I needed thank you so much! I thought of one other case that I couldn't figure out. How would I get a count (instead of a list) of all the customers that bought exactly two apples? I spent about 45 minutes hacking on the above but I couldn't get it. I'm thinking that would have to be a subselect? -- Scott Baker - Canby Telcom System Administrator - RHCE - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How do I query for a specific count of items?
If I have the following (highly simplified) customer table how do I: #1) Query for customers who *ONLY* bought apples #2) Query for customers who bought apples *AND* bananas #3) Query for customers who bought exactly 2 apples? -- DROP TABLE IF EXISTS Customers; CREATE Table Customers ( EntryID INTEGER PRIMARY KEY, CustomerID INT, Type ENUM ); INSERT INTO Customers VALUES (NULL, 1234, 'Banana'); INSERT INTO Customers VALUES (NULL, 1234, 'Banana'); INSERT INTO Customers VALUES (NULL, 1235, 'Apple'); INSERT INTO Customers VALUES (NULL, 1236, 'Banana'); INSERT INTO Customers VALUES (NULL, 1237, 'Banana'); INSERT INTO Customers VALUES (NULL, 1237, 'Banana'); INSERT INTO Customers VALUES (NULL, 1237, 'Apple'); INSERT INTO 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 - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .import on a .csv file
I'm trying to .import a CSV file and I can't quite figure out the syntax. I created a table, and then did: .separator , .import /tmp/foo.csv mytable This works sort of, unless my data has , in it. Something like "last, first". Because it tries to split at that , and then the number of rows doesn't match my table. Then I tried setting the separator to .separator \",\" Which works correctly (i.e. it splits the data properly). However, now my first and last columns have " on the beginning/end of them. Is there a way to import a well formed CSV. 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 System Administrator - RHCE - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 key); sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 13; 0|0|TABLE foo USING PRIMARY KEY I must have typod and not noticed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 command "EXPLAIN QUERY PLAN {your sql command}". This will give
> you a high level overview of all the tables being accessed, and which
> indices (if any) will be used for each.
>
> For a simple query like your example, SQLite will almost certainly use
> an index on "id" if one is available. You can use above to verify this.
That did exactly what I needed... and it pointed out that I *wasn't* using
an index. I didn't realize "INTEGER PRIMARY KEY" was case sensitive. Thanks
for the help.
- Scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 result in an error message. If a user-defined function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator. http://www.sqlite.org/lang_expr.html It's something you'll have to add. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 output SQLite 2 files? -- Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CURRENT_DATE Behavior
On 10/21/2008 05:23 AM, Doug wrote: > In an effort to share some hard-won insight: > > I used to store local times in the database, but it's a bad idea and I got > bit. Think about what gets stored: A number of seconds/nanoseconds/whatever > from some time in the past (Jan 1, 1970 for example). When daylight savings > arrives, all of your stored values are now off by an hour (and if they're > around midnight, they're on the wrong date too). And times you save now > will similarly be off when you leave day light savings. It's much better to > store UTC and then convert to local time as needed. > > I (and some customers) went through a lot of pain as I switched everything > to UTC -- life has been good since then. I complete agree with Doug here said. In my years of programming it's ALWAYS a good idea to *store* dates in UTC, and convert them to your timezone when you *display* them. Otherwise you'll always have issues of "this date was when DST was in effect" and this date was "when DST didn't count" so you're always trying to compensate. DON'T DO THAT! Let SQLite do that 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 flawlessly with unixtime values also. -- 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] Problem with inserting and integer primary key
On 10/15/2008 09:27 AM, Karl Lautman wrote:
> To insert data I do (this is in Python):
>
>
>
> x = dcur.execute('insert into Lists values (?, ?, ?, ?, ?, ?)', (123,
> 'YouTube Videos','http://www.youtube.com', 'views', '10/14/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 using NULL.
--
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] 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: > - create table my_table(date_stuff real); Where did you find the "right" way to do timestamps? I've always used unixtimes and never had a problem. It's extremely portable across all systems and languages. Working with Julian datetimes is a little more complicated, in my experience. - Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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. Aside, it is easier to remember than a > pragma. This is done pretty easily. With sqlite3 do a .schema and it will tell you what tables are in the database, as well as the command used to create them. > * Two simple functions would make SQLite much more useful to me are: > > [1] the simple logarithm function "log" or "ln" (which would make a > power function easy), > > [2] the aggregate function "standard deviation" (which together with > the average may well be the most commonly used statistical function). > > > * sqlitebrowser is very buggy under linux when it comes to importing > csv files. often, nothing happens. sometimes, trying a second import > works. just buggy. > > > These should all be trivially easy to add (I hope). A more complex > request would be to build an "import csv file" functionality into > sqlite itself, but this could have other negative consequences. sqlitebrowser is not authored by the sqlite people, so if it doesn't work you should report it to them. Sqlite3 does have a .import option that works for CSV files great. I've used it several times and it works great. ___ 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 like that? I'm running Windows pre-built binaries. I'm using the fedora packages: [EMAIL PROTECTED](~) :rpm -q sqlite sqlite-3.5.9-1.fc9.i386 It works for small numbers: sqlite> select 1.0 / 2.0; 0.5 -- 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:
> Myk Melez <[EMAIL PROTECTED]> wrote:
>> I noticed today that JavaScript Date values (the number of
>> milliseconds since the Unix epoch) degrade to second precision when
>> converted to Julian date values and back using naive SQLite date/time
>> function calls, for example:
>>
>> sqlite> SELECT strftime('%s', julianday(1219441430151/1000,
>> 'unixepoch')) * 1000;
>> 121944143
>>
>> I suppose this is because "Unix has no tradition of directly
>> representing non-integer Unix time numbers as binary fractions."
>
> No. You have two problems. First, 1219441430151/1000 is done as C-style
> integer truncating division, so you are losing your fractions right
> there. Try
>
> select 1219441430151/1000, 1219441430151/1000.0;
Did I do 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] Equivalent of mysql_real_escape_string() ?
Skip Evans wrote:
> Hey all,
>
> Okay, I'm looking all through the PDO docs on
> php.net, but am unable to find the SQLite
> equivalent to the MySQL function
>
> mysql_real_escape_string()
>
> in case, among other things, a text field contains
> single quotes, etc.
>
> How is this done in SQLite? I'm still scouring the
> the docs but having no luck.
>
> Does it have something to do with
>
> $dbh->prepare()
>
> ...or am I on the wrong track with that one?
As mentioned above the BEST way to do it is with prepared statement and
bound variables. If you have to use raw SQL then just use the PDO::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 - 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...
I have a very simple table:
CREATE TABLE Log (
LogDate,
LogText
);
Just when did the event happen, and what was it. So now I'm populating the
fields and I'm trying to decide whether LogDate should be in unixtime
format, or raw date format ('2008-01-01 13:12:11').
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 mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] indexing rows from a query
[EMAIL PROTECTED] wrote: > Sorry if this is a silly question - I don't have much experience with > databases. > > Say I have a table with many (millions+) of rows and I have a query: > > SELECT * FROM mytable WHERE some_condition ORDER BY rowid > > First, I'm assuming that in addition to whatever time some_condition > takes, I'll see an overhead of O( N log N ) for the sort in the worst > case, but probably much less (O(N) or O(1)?) because it's probably be > sorted anyway by rowid. Is that correct? > > My real question is if there is an efficient way to index the results > of such a query. In other words, I'm looking for rows N through N+100 > of the result. Can I do much better than just executing the query and > throwing away the first N rows? I thought of making an auxiliary > table 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 first 100 rows? -- 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] Calculating Difference between Time using SQLite
Miguel wrote:
> Estimates,
> First of all, excuse my English, I recognise that it is not my strong.
> I need to do a query on a table and I return the difference in minutes
> between
> two times loaded in the table.
> Which would be the best way to make these differences.
> 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=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] Timestamp DataType
Joanne Pham wrote: > Hi All, > As mentioned in the document of SQLite the list below is all the datatype of > SQLite. > How about Timestamp fromat? Can we havethe column's datatype as Timestamp . Datatypes aren't strictly enforced like other databases. If you insert the date 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 mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Who is using SQLite prior to version 3.3.0?
D. Richard Hipp wrote: > Is there anybody still using a version of SQLite prior to version > 3.3.0 in a context where they might want to read a database file that > was created by the latest version of SQLite? > > The reason I ask is this: SQLite understands two slightly different > file formats, format 1 and format 4. (Ok, it also understands formats > 2 and 3, but they don't come into play here so ignore that fact for > the moment.) In format 4, SQLite uses a tighter encoding for boolean > values and it honors the DESC keyword on indices. > > All versions of SQLite since 3.3.0 can read and write both format 1 > and format 4. But versions of SQLite prior to 3.3.0 could only > understand format 1. New database files are created in format 1 by > default in order to be readable and writable by older versions of > SQLite. I'm wondering if we have reached a point where this can > change. Can we move to creating new database files in format 4 by > default. > > Please note that this is not a backwards compatibility break. All > prior database files will still be readable and writable by any > version of SQLite after 3.3.0. But this is a forwards compatibility > break. Database files created by SQLite version 3.6.0 or later will > likely be unreadable by SQLite version 3.2.8 and earlier. > > If we do move to creating new database files in format 4 by default, > an application will still be able to create format 1 database files > using a pragma. Or, a compile-time option can be used to force format > 1 databases to be used as the default. > > FWIW, SQLite version 3.3.0 was released over 2 years ago on January > 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 breaking forwards compatibility... I dunno. I like the idea of keeping things as compatible as possible. -- 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] SQLite full text speed
I'm curious about the speed trade off between a full table scan and just a flat file search... Say I have a database with 2 records in it. If I do a query like: SELECT foo FROM table WHERE bar LIKE '%glaven%'; That will be a full text scan across the table. Would that be any 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 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
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 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. You probably just want: -MM-DD. I guess I should include a URL: 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
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 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. You probably just want: -MM-DD. -- 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
Zbigniew Baniewski wrote:
> #v+
> SQLite version 3.5.6
> Enter ".help" for instructions
> sqlite> select date('now');
> 2008-03-28
> sqlite> select date('now')-14;
> 1994
> sqlite>
I think what you want is date math:
sqlite> SELECT 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
Zbigniew Baniewski wrote:
> #v+
> SQLite version 3.5.6
> Enter ".help" for instructions
> sqlite> select date('now');
> 2008-03-28
> sqlite> select date('now')-14;
> 1994
> sqlite>
No I think it's doing exactly what it should. You'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 as an integer.
2008 - 14 = 1994.
--
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 - 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] Generating new rowid algo
Igor Tandetnik wrote: > B V, Phanisekhar <[EMAIL PROTECTED]> wrote: >> I wanted to know the algorithm used by sqlite to generate the new >> rowid. Assume there can be N distinct rowid's possible, now insert N >> records, followed by random deletion of some records. Now what rowid >> will be assigned to a new row that is added? > > http://www.sqlite.org/autoinc.html > > Note that N=2^63-1. A typical application would have to work for a very > long time to create N records. > > Igor Tandetnik Interesting reading... If a large 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
Mike McGonagle wrote: > Oh, I forgot to mention (if it matters), the "MAIN" table has about 3000 > rows in it, while the "LIST" table has about 6 rows. > Mike > > > On Mon, Mar 3, 2008 at 3:32 PM, Mike McGonagle <[EMAIL PROTECTED]> wrote: > >> Hello all, >> I was working with some queries last night, and ran accross something that >> I don't quite understand. Basically, this is what I have... >> >> *** >> >> CREATE TABLE MAIN ( >> id integer primary key autoincrement not null, >> name varchar(30), >> [other fields left out, as they are not used] >> ); >> >> CREATE TABLE LIST ( >> mid integer, >> ord integer, >> data float >> ); >> >> -- Compound Query >> SELECT data FROM LIST WHERE 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 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 mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
Michael Hooker wrote: > This is an issue which interests me too. The answer Scott gives makes > absolute sense to me, but all the PHP/MySQL books I've seen (and MySQL can't > be that different from Sqlite3 in this respect) seem to go the way of a new > query to the database every time a different set of data is needed, rather > than keeping all the data in an array, even with small inconsequential > tables. I suppose if there is a possibility that the db tables may be > updated while the user is online this approach is valid. Or maybe there is > a downside to PHP arrays, I can see that a huge array might have memory > implications, especially on a busy shared server. My experience has been that each sqlite DB hit on my server takes about .01 seconds to run. So if you're hitting that DB 20 times to get the 20 most popular images that really adds up. I've spent countless hours optimizing my site to do things like: SELECT * FROM Entry WHERE ID IN (1,2,3,4); instead of SELECT * FROM Entry WHERE ID = 1; SELECT * FROM Entry WHERE ID = 2; SELECT * FROM Entry WHERE ID = 3; SELECT * FROM Entry WHERE ID = 4; If you can (sometimes 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 optimization. -- 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?
Sam Carleton wrote: > I am new to SQLite and databases, so I am stil learning how to > optimize their use... > > I am working on a "shopping cart" type of feature, it is actually a > favorites feature for a system that displays images in multiple > galleries. There is a SQLite table that contains the user_id, > gallery_id, and image_id. When a index page is displayed, only a sub > set of the images in the gallery are displayed. So the question is > what will be faster: > > 1: Doing a SELECT for each image on the favorites table to see if it is > selected > 2: Doing one SELECT to get all the images for the current gallery and > store that into a PHP array and then simply look in the PHP for each > image? > > My thought is option 2. Is that correct? The less database hits you have to do, the faster your code will 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] SQLite Web Site
Steven Fisher wrote: > On 06-Feb-2008, at 12:33 PM, [EMAIL PROTECTED] wrote: > >> What do you mean "no longer found"? Do you mean that that >> you cannot see anything at all, or that the new design is such >> that it is not displayed correctly? > > Well, 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 with my firefox plugin. -- 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] Using LIKE to check the first digits?
Gilles wrote: Hello I'm no SQL guru, and need to look up phone numbers in a SQLite database that start with certain digits. Some customers use http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the first part is common to all the numbers assigned to this customer, so I 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 Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] make test problems on Fedora 7
Alexander Batyrshin wrote: Looks like you built it without TCL support Heh... duh.. Still have problems though... I have tcl and tcl-devel install and I get this: checking whether to use an in-ram database for temporary tables... no checking if executables have the .exe suffix... unknown 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
I downloaded sqlite 3.5.5 and ran make test. I get the following errors: /tmp/cc9i7nKr.o: In function `Sqlitetest1_Init': /tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference to `Tcl_CreateCommand' /tmp/sqlite-3.5.5/./src/test1.c:4565: undefined reference to `Tcl_CreateObjCommand' /tmp/sqlite-3.5.5/./src/test1.c:4568: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4570: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4572: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4574: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4576: undefined reference to `Tcl_LinkVar' /tmp/cc9i7nKr.o:/tmp/sqlite-3.5.5/./src/test1.c:4578: more undefined references to `Tcl_LinkVar' follow /tmp/cc9i7nKr.o: In function `test_io_trace': /tmp/sqlite-3.5.5/./src/test1.c:251: undefined reference to `Tcl_AppendResult' /tmp/cc9i7nKr.o: In function `test_printf': /tmp/sqlite-3.5.5/./src/test1.c:1103: undefined reference to `Tcl_AppendResult'/tmp/cc9i7nKr.o: In function `Sqlitetest1_Init': /tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference to `Tcl_CreateCommand' /tmp/sqlite-3.5.5/./src/test1.c:4565: undefined reference to `Tcl_CreateObjCommand' /tmp/sqlite-3.5.5/./src/test1.c:4568: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4570: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4572: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4574: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4576: undefined reference to `Tcl_LinkVar' /tmp/cc9i7nKr.o:/tmp/sqlite-3.5.5/./src/test1.c:4578: more undefined references to `Tcl_LinkVar' follow /tmp/cc9i7nKr.o: In function `test_io_trace': /tmp/sqlite-3.5.5/./src/test1.c:251: undefined reference to `Tcl_AppendResult' /tmp/cc9i7nKr.o: In function `test_printf': /tmp/sqlite-3.5.5/./src/test1.c:1103: undefined reference to `Tcl_AppendResult' /tmp/cc9i7nKr.o: In function `getDbPointer': /tmp/sqlite-3.5.5/./src/test1.c:108: undefined reference to `Tcl_GetCommandInfo' /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_AppendResult' /tmp/cc9i7nKr.o: In function `getDbPointer': /tmp/sqlite-3.5.5/./src/test1.c:108: undefined reference to `Tcl_GetCommandInfo' /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_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 - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] order by issue?
Ken wrote: I'm not sure if this a bug or if working as intended: the order by b,a seems to cause B to use asc.. Instead of the desc as written in the order by statement. create table tst (a integer, b integer ); sqlite> insert into tst values(1,1); sqlite> insert into tst values(1,2); sqlite> insert into tst values(1,3); sqlite> insert into tst values(2,3); sqlite> insert into tst values(2,2); sqlite> insert into tst values(2,1); sqlite> select * from tst order by b, a desc; a|b 2|1 1|1 2|2 1|2 2|3 1|3 But that's not what you wrote... You told it 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
Joanne Pham wrote: Any help for this question please! I would like to convert from t1 to t2. and my table is store t2. t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ?-> 2008-01-28 00:00:00 I think your numbers got cutoff... you mean 1201561222 right? Anyway 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] How to truncate the hour fraction
Joanne Pham wrote:
Thank Ken,
But this is not what I want. I would like to truncate the hour.
So
t1: 201561222 -> 2008-01-28 15:00:22
and I want
t2 ? -> 2008-01-28 00:00:00
So I want to find out t2? which has no hour number.
So you just want the date (i.e. No time at all)? Seems like you have
two options. You can mod against 86400 (number of seconds in a day),
or you can just use the date(DateField) option, to only output the date.
sqlite> SELECT date('2007-01-19 12:54:32');
2007-01-19
sqlite> SELECT date('120159','unixepoch','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
Fowler, Jeff wrote: Yes - I've looked over the current date functions. I would propose a single function addition that's hugely valuable in the business world. SQL Server has a function called "datediff" for date arithmetic. It accepts three parameters. The first indicates the unit of scale (years, months, weeks, days, etc. - I think it even goes into milliseconds!). The next two parameters are the dates to compute the difference between. It returns an integer. I don't think you answered Richard's original question. What constitutes a month? Since month lengths 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 RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database Commoditization and SQLite
Rick Langschultz wrote: I think commercial database solutions have their place in the market, and I don't think Richard looks bad in this article. He simply created something that needed to be created. When I am developing an application that uses a database I first lay everything out on whiteboard, then I write some SQLite SQL code, then start writing my application using sqlite3.h. Once I have everything worked out in the sqlite3 code I am able to plug in other APIs like postgresql, mysql, etc. It is so much easier to do it this way and test everything without having continuous access to the database servers or software. More or less it is a database on the go with stability, and community support. I want SQLite to continue to add features and remain stable. Keep up the great work. The way I read that quote is: "SQLite is so good, and so 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] Database Commoditization and SQLite
[EMAIL PROTECTED] wrote: Says Brian Aker, Director of Architecture at MySQL AB: "SQLite has commoditized the entire low end of the database market out of existence." http://www.linuxworld.com/community/?q=node/2106 SWEET! Congrats on the commoditization of the low end database 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 Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexes not being used after INNER JOINS?
Hugo Ferreira wrote: > Hi everyone, > > I seem to be having a problem here with LEFT JOINS between tables and > results of INNER JOINS. Take for example the following example (table > definition is in the end): > > TABLE COUNT esparqueologico: 750 > TABLE COUNT data: 3828 > TABLE COUNT reftemporal: 3972 > > This query would take 6.7s to run (750 rows): > > select * from esparqueologico oe left join > (data d cross join reftemporal r on d.reftemporal_id = r.id) x > on oe.datacao_id = x.id > > However this takes virtually zero time (750 rows): > > select * from esparqueologico oe left join data d on oe.datacao_id = d.id > > And this takes 0.1s (3828 rows): > > select * from data d inner join reftemporal r on d.reftemporal_id = r.id > > It seems to me that indexes are lost in the first query. Here is the data > definition I'm using: > > CREATE TABLE data ( > id guid NOT NULL, > reftemporal_id guid NOT NULL, > PRIMARY KEY (id), > FOREIGN KEY (reftemporal_id) REFERENCES reftemporal(id), > ) > > CREATE TABLE reftemporal ( > id guid NOT NULL, > subtype varchar, > PRIMARY KEY (id) > ) > > CREATE TABLE esparqueologico ( > id guid NOT NULL, > datacao_id guid, > PRIMARY KEY (id), > FOREIGN KEY (datacao_id) REFERENCES data(id), > ) > > CREATE INDEX idx_data_reftemporal_id ON 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
Joanne Pham wrote: > Hi P > Sorry for the confusion! > Current my hourAppAcclTable is store the following > 1, 1198990800 > 2, 1198998000 > which is 2007-12-29 21:00:00 and 2007-12-29 23:00:00 if I used the datetime > function to show the time format. > What I want is to store this time stamp in different table but truncate all > the hour fraction, dayAppAcclTable >1, 1198915200 >2, 1198915200 > which is 2007-12-29 00:00:00 which GUI run the datetime function > > Thanks a lot, > Joanne If you're dealing with dates 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 Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: [Linux + PHP] Recommended way to access SQLite?
Kees Nuyt wrote: >> So the options are: >> 1. use the old SQLite2 sqlite_() functions (or some class that turns this >> into OO) >> 2. PDO to use the SQLite3 linked-in library >> 3. PDO to access the SQLite3 DLL >> >> ... with 2 being the recommended 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 each DBMS. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unix Epoch Time Support
Mark Riehl wrote: > All - We're in the process of porting an application that used MySQL > to an embedded platform running SQLite. Two of the built-in functions > we used frequently were the functions to create and convert from Unix > epochs (UNIX_TIMESTAMP(), FROM_UNIXTIME). > > 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 Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
[EMAIL PROTECTED] wrote: > The new look for the SQLite website is now in place, > if you haven't already noticed: > > http://www.sqlite.org/ > > Even though the new look is "in place" you should > understand this as a work in progress, not a done > deal. I am still looking for suggestions, comments, > and bug reports. I am particularly interested in > help in the following ways: > > * Suggestions for something better to put on > the home page. There is a typo on the main page: "The developers are continue to expand 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/obvious and could probably be left out. Just my $.02 - Scott -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Spatial searches
David Thieme wrote: > I've been looking for a WinCE embedded database that supports spatial > searches. We are already using SQLite for a very small application; we're > hoping that someone may have some tricks/hints on how to implement fast > searches on spatial data with SQLite. A typical search would be finding > items whose lat/lon falls within a given rectangle (e.g., hotel's closest to > my car). If not, can someone recommend a WinCE database engine that > supports spatial searches? Wouldn't that be something simple like... SELECT Foo WHERE 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 - To unsubscribe, send email to [EMAIL PROTECTED] -
[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
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SELECT ORDER BY failure
Mark Brown wrote: > Hi- > > We have a query that is failing with SQLite error code 10: > > SELECT A, B, C, D, E, F, G > FROM Table1 > WHERE F=? > ORDER BY E > > but succeeds when the ORDER BY clause is removed. > > This database does not have any indicies 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 Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Milliseconds
Christian Smith wrote:
> If you use the julianday representation, the integer component is the
> number of days since "noon in Greenwich on November 24, 4714 B.C", with
> the fractional part being the fraction of that day. Hence, the
> resolution is determined by the fractional component of the real number.
> Now, in the UK, I get the following:
> sqlite> select julianday('now');
> 2454295.1407767
>
> The integer component consumes probably 21 bits of the available 52 bits
> mantissa of an IEEE-754 64-bit real. That leaves 31 bits for the
> 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.
I'm a little confused by the math... help me work this out.
sqlite> SELECT julianday('now');
2454295.20404931
That gives me days since the Julian epoch. If I multiply by 86400 I
should get seconds since the Julian epoch.
sqlite> SELECT julianday('now') * 86400;
212051105903.613
That leaves 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
Christian Smith wrote:
> If you use the julianday representation, the integer component is the
> number of days since "noon in Greenwich on November 24, 4714 B.C", with
> the fractional part being the fraction of that day. Hence, the
> resolution is determined by the fractional component of the real number.
> Now, in the UK, I get the following:
> sqlite> select julianday('now');
> 2454295.1407767
>
> The integer component consumes probably 21 bits of the available 52 bits
> mantissa of an IEEE-754 64-bit real. That leaves 31 bits for the
> 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/24856th second precision...
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] DELETE using a join?
Let's say I have two really simple tables like so Users - UserName UserID Payments UserID PaymentAmount Is there an easy way to do something like "delete all entries in the payment table where the Username is 'John Smith'" I'm thinking it would be trivial 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 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 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] baffled by dates
What you want is here:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
I'm thinking you want:
select First_Capture from PIT_manatees where date(First_Capture) <
'2000-1-1' limit 5;
- Scott
Chris Fonnesbeck wrote:
> I'm at a complete loss about how to work with dates in SQLite. The
> documentation doesnt seem to be helping me. I have a table with some date
> fields, in the proper -mm-dd format:
>
> sqlite> select First_Capture from PIT_manatees limit 5;
> 1997-6-17
> 1998-5-6
> 1997-6-24
> 1998-5-6
> 2000-3-15
>
> however, it does not respond properly to comparisons to dates:
>
> sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
> limit 5;
> sqlite>
>
> this doesnt work either:
>
> sqlite> select First_Capture from PIT_manatees where
> First_Capture<date('2000-1-1') limit 5;
> sqlite>
>
> note that these columns are in the 'date' format.
>
> Yet more strangeness:
>
>
> sqlite> select date(First_Capture) from PIT_manatees limit 10;
>
>
>
>
>
>
>
> 1999-10-13
>
>
> sqlite>
>
> 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
If there is no redundancy to remove then the database size will remain the same. Vacuum is only really effective after you delete a large amount of data from a DB. - Scott Li, Charles wrote: > Hi, > I use the vacuum command on a database, but the size remains the same. What > should 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?
echo "SELECT * FROM Table" | sqlite database.bin John Salerno wrote: > Hi everyone. Can someone tell me the proper syntax for running a sql > script when starting up sqlite from the command line interface? > > Thanks, > John > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > --------- > > > > -- Scott Baker - RHCE Canby Telcom System Administrator 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] mixing GROUP and non-GROUP columns in a query
I would certainly think the way MySQL treats this is correct. SQLite
may be trying to assume what you meant, and interpreting. Which may
confuse the client if they don't know what they're asking.
Will Leshner wrote:
Say I have a table defined and populated as follows:
CREATE TABLE test (a TEXT);
INSERT INTO test (a) VALUES ('hello');
INSERT INTO test (a) VALUES ('hello');
INSERT INTO test (a) VALUES ('hello');
And I perform the following query:
SELECT rowid,count(a) FROM test
In SQLite I get back:
3|hello
But in MySQL I get back an error:
#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 ... ?
Not necessarily. All you're telling it to do is sort on the first column, anything beyond that is just random luck. You could easily sort it on both columns. ORDER BY XIVONO, XICODE; Shum [Ming Yik] wrote: > Hi All, > > I have problem with Order by ...: > > There is a table as follow: mytbl01 > > XIVONO XICODE XCNT > X001 001 1 > X001 002 2 > X001 003 3 > X001 004 4 > > > 1.) SELECT * from mytbl01 WHERE XIVONO='X001'; it return as expected ! > ... it is fine ! > X001 001 1 > X001 002 2 > X001 003 3 > X001 004 4 > > 2.) However SELECT * from mytbl01 WHERE XIVONO='X001' ORDER BY XIVONO; it > return as follow: > X001 004 4 > X001 003 3 > X001 002 2 > X001 001 1 > > > 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....
If you're looking for a cool "test-suite" I highly recommend the Ultimate Boot CD. It includes approxmiately 8.7 million tools (not really, but it is a lot), one of which is MemTest86. http://www.ultimatebootcd.com/ It's solved ALL kinds of hardware issues. I highly recommend it. Jay wrote: --- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: I find it utterly amazing that a machine with bad memory could run a full-blown Linux desktop and a copy of Win2K running in VMWare for days on end without showing a problem, then suddenly begin having trouble with the SQLite regression suite. Yet that is what appears to have happened. I had the same sort of thing happen. The machine just would not compile the linux source. Luckily it had different errors each time which is what tripped me to look for a hardware problem. http://www.memtest86.com/ Has a nifty tester 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 Telephone - Network Administrator - RHCE Ph: 503.266.8253
[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 date I entered in the DB was in UTC time (which it's not it's
compensated to my localtime zone). Is there any way to have SQLite be
aware that the date entered is not UTC? Can I append the timezone on the
end of the string?
Also when calculating dates using the 'localtime' modifier where does
SQLite get the information regarding the local timezone? Is it an
environment variable somewhere?
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
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 progress bar. Regards, Stephen Drew
