Re: [sqlite] WAL and updates
Then something like
WHERE a||'-'||b||'-'||c||'-' != ...
Maybe there are other drawbacks?
//Roger
-Ursprungligt meddelande-
From: Imanuel
Sent: Sunday, November 25, 2012 5:22 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] WAL and updates
I'm not saying your statement is inefficient, I'm saying it's wrong
because it produces unwanted results.
If the fields a,b,c ('12','34','56') should be updated to
('1','2345','6') your statement would fail instead of doing the expected
update.
Which means with every false hit it has less data to write and thus is
faster because of the false hits (instead of "even with").
Imanuel
Am 25.11.2012 17:13, schrieb Simon Slavin:
On 25 Nov 2012, at 4:11pm, Imanuel wrote:
Hi Keith
UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3;
It seems to me that this is not reliable.
Think the the following text values:
a='12'
b='34'
c='56'
If you want to update these values to:
a='1'
b='2345'
c='6'
Then your statement would not update because '123456' = '123456'.
You're quite right, but in some cases it's sufficiently faster than doing
WHERE a!=1 OR b!=2 OR c!=3
that even with the false hits it takes less time to process.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"
-Ursprungligt meddelande-
From: Baruch Burstein
Sent: Sunday, November 04, 2012 10:43 AM
To: Григорий Григоренко ; General Discussion of SQLite Database
Subject: Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"
CURRENT_TIMESTAMP returns "-MM-DD HH:MM:SS", not a unix timestamp. I
think other DB systems all use this function to return a unix timestamp.
- Reply -
There might be some that does, but
Oracle11g
SQL> select CURRENT_TIMESTAMP from dual;
CURRENT_TIMESTAMP
---
2012-11-04 11:05:23,537000 +01:00
Oracle9i
SQL> select CURRENT_TIMESTAMP from dual;
CURRENT_TIMESTAMP
---
2012-11-04 11:08:48,942211 EUROPE/STOCKHOLM
MySQL 5
mysql> select CURRENT_TIMESTAMP;
+-+
| CURRENT_TIMESTAMP |
+-+
| 2012-11-04 11:06:31 |
+-+
Regards
Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shared in-memory SQLite database in shared memory
On 11/03/12 16:26, Jaco Breitenbach wrote: I wanted to have the database in shared memory. Maybe a ram drive? What operating system are you running? Cheers Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqllite db - remote access on a shared host
On 07/20/12 11:21, Kieran Hever wrote: Hi, I there any application/program paid or free that will allow me to connect to a remote shared host sqlite DB. A program which will allow me to make changes to the db design and do backups. I have very little control on the shared host. I am hoping for something that will let me connect directly to the DB through a URL path and I will have a password on the db so that will be my security. I have looked at various apps and most are for local db access. ThanksKieran ___ Google did return e.g. https://code.google.com/p/phpliteadmin/ /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building an SQLite Extension: How to check if a table exists in the database?
On 03/09/12 19:39, Wei Song wrote: Hello, I'm developing an SQLite extension which uses a function to set data into a table. I'd like to know how to check if a table exists in a database? It's hard to say what you need but maybe select count(*) from sqlite_master where type='table' and name='tablename'; /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite, portable, image store
On 02/25/12 19:35, Stewart wrote: In fact I would like my first project to be a database of all my photos. This might not be the response you was hoping for but Coppermine might give you some ideas regarding a database with all your photos? http://coppermine-gallery.net/ -- Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help request for a query...
On 02/19/12 23:04, Igor Tandetnik wrote:
The same can be achieved in a less convoluted manner: insert or
replace into History (Path, Hits) VALUES ('c:\', (select ifnull(Hits,
0) + 1 from History where path='c:\'));
Thanks Igor!
--
Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help request for a query...
On 02/19/12 16:59, Jörgen Hägglund wrote:
Hi all!
I'm not sure if it's my brain melting or what it might be... :-)
I have a table defined as:
CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL, Hits
INTEGER DEFAULT 0)
Is there any way of making inserting data as follows;
- If Path does not exist in the table;
INSERT INTO History VALUES ('c:\', 1)
Should yield:
c:\,1
- But, if Path already exists do this:
UPDATE History SET Hits = Hits + 1 WHERE Path = 'c:\'
Should yield:
c:\,2
Of course, the 'c:\' is entered programmatically (using params).
Anyone up for modifying, explaining and solving this? :-)
This seems to work but I do not fully understand why ;-)
sqlite> .header on
sqlite> CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL,
Hits INTEGER);
sqlite> insert or replace into History (Path, Hits) VALUES ('c:\',
(select count(*)+ifnull(Hits,1) from History where path='c:\'));
sqlite> select * from History;
Path|Hits
c:\|1
sqlite> insert or replace into History (Path, Hits) VALUES ('c:\',
(select count(*)+ifnull(Hits,1) from History where path='c:\'));
sqlite> select * from History;
Path|Hits
c:\|2
sqlite> insert or replace into History (Path, Hits) VALUES ('c:\',
(select count(*)+ifnull(Hits,1) from History where path='c:\'));
sqlite> select * from History;
Path|Hits
c:\|3
--
mvh Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 windows command line: writing the sql errors to a file (and not to the screen)
On 02/16/12 19:48, Gert Van Assche wrote: I have put this line in a batch file: dbscript.cmd and I just execute this on the command line. (or via a Windows shortcut on my desktop) This works fine, but I would like to redirect the echo to a file, so that I can capture the errors that might occur when dbscript.sql is executed. Does anyone have an idea how to do this? dbscript.cmd > dbscript.log 2>&1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Support EXTRACT SQL standard function
On 02/12/12 20:34, Yuriy Kaminskiy wrote:
I wonder, how it will be handled if you issue such request at month/year/...
change (23:59.59.999 GMT -> 00:00:00.000 GMT)?
Is timestamp for current_date/current_time generated once and cached at start of
SELECT evaluation?
It is certainly *not* cached for different rows:
SELECT *, current_date, current_time, current_time FROM t
while($row = $sth -> fetch) { print ++$i," row: @$row"; sleep 5; }
1 row: 0 2012-02-12 19:20:40 19:20:40
2 row: 1 2012-02-12 19:20:40 19:20:40
3 row: 2 2012-02-12 19:20:45 19:20:45
4 row: 3 2012-02-12 19:20:50 19:20:50
5 row: 4 2012-02-12 19:20:55 19:20:55
(two first are same due to sqlite [or perl DBI binding?] seems executes one row
ahead).
But do they use same cached value *within one row*?
If not, results may be randomly inconsistent and broken (race condition).
No idea!
/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Support EXTRACT SQL standard function
On 02/11/12 15:22, Kit wrote: 2012/2/10 Willian Gustavo Veiga: SQLite is a great database to unit test (TDD) applications. You can run it in memory with your tests ... I've found a problem when I was unit testing my application. MySQL (production database) supports EXTRACT SQL standard function. SQLite don't support it. It would be great to have support in this standard. Unfortunately, strftime isn't a solution. It's not a standard. Function strftime is your solution. Write two models. One for MySQL, one for SQLite. These databases are quite different and require different SQL queries. Maybe views could be used to handle differences, at least some of them ;-) sqlite> create view dateCurrent as ...> select ...> substr(date(),0,5) as year, ...> substr(date(),6,2) as month, ...> substr(date(),9,2) as day; sqlite> .header on sqlite> select * from dateCurrent; year|month|day 2012|02|12 mysql> create view dateCurrent as -> select -> substr(current_date,1,4) as year, -> substr(current_date,6,2) as month, -> substr(current_date,9,2) as day; mysql> select * from dateCurrent; +--+---+-+ | year | month | day | +--+---+-+ | 2012 | 02| 12 | +--+---+-+ -- Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] command line to get number of tables in sqlite
Somthing like sqlite3 sqlite.file sqlite> select count(*) from sqlite_master where type = 'table'; /Roger On 12/21/11 19:32, smallboat wrote: Hello, I have a sqlite file. I would like to open it and know how many tables in it. What is the command line to open a sqlite file and get to know how many tables in it? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Runfile script over existing Database
On 11/29/11 23:25, Steffen Mangold wrote: Now i have the problem that the sqlite3.exe has a problem with "ä, ö, ü" in Database filename. :( It makes a new db called " D�sseldorf " for example and fails then :( Steffen Mangold Try creating a bat-file with the cmd-commands and run it. /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character
On 11/09/11 19:42, Yuriy Kaminskiy wrote: Paul Corke wrote: On 09 November 2011 15:32, hmas wrote: sqlite> select hex(foocol) from footable where foocol like '98012470700566'; 39393939393830313234373037303035363600 It looks like there's an extra 00 on the end. x'3900' != x'39' That said, it seems LIKE operator is buggy. sqlite> SELECT X'1245005679' LIKE X'1245001234'; 1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users On Windows sqlite3.exe SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT X'1245005679',X'1245001234'; ↕E|↕E Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] complete sqlite software
On 10/22/11 19:25, saeed ahmed wrote: i want a software,something like microsoft's Access but no microsoft.a software that can be used for making tables,queries and reports. 2011/10/22 gabriel.b...@gmail.com Maybe you will find what you need on http://www.sqlite.org/cvstrac/wiki?p=ManagementTools /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit COUNT
On 10/16/11 14:21, Fabian wrote: I want to allow users to paginate through a result set. The pages are retreived through LIMIT/OFFSET, but to calculate the total number of pages, I have execute a separate COUNT() query (without LIMIT) once. Because I'm basicly executing the same query twice just to get a total count, I'm trying to optimize this. Restricting the maximum number of pages to 10 should improve performance, if there was some way to put make COUNT() respect the LIMIT specified. ___ I can't tell if this is more efficient but it's one query select *,(select count(*) from table) as total_record_count from table limit 100; /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit COUNT
On 10/16/11 14:21, Fabian wrote: I want to allow users to paginate through a result set. The pages are retreived through LIMIT/OFFSET, but to calculate the total number of pages, I have execute a separate COUNT() query (without LIMIT) once. Because I'm basicly executing the same query twice just to get a total count, I'm trying to optimize this. Restricting the maximum number of pages to 10 should improve performance, if there was some way to put make COUNT() respect the LIMIT specified. ___ Maybe GROUP BY and HAVING can help you? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
On 09/28/11 21:55, Puneet Kishor wrote: Perhaps, but I have inserted that in my table where the column is INTEGER. sqlite> SELECT typeof(u_downloaded_on) FROM uris LIMIT 1; integer -- OK! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
On 09/28/11 21:10, Black, Michael (IS) wrote:
'scuse meI was wrong (again)...I guess strftime does return an
integerseems to me that belies the name as it's a mismatch to the
unix function.
?
SQLite version 3.7.8 2011-09-19 14:49:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select typeof(strftime('%s','now','localtime'));
text
/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
On 09/28/11 20:14, Black, Michael (IS) wrote:
strftime returns a text representation. So you didn't really change anything.
You need to use juliandays() as I said.
And you want a REAL number...not integer...though SQLite doesn't really care
what you call it. It's more for your own reference.
Assuming that second resolution is sufficient.
Would
UPDATE table SET new_column = cast(strftime('%s', old_column) as integer);
make any difference?
/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed "endless" data into sqlite, thru a shell script
On 09/27/11 20:14, David Garfield wrote: Any entry in a pipe could be buffering. In a quick test here, awk is buffering. To find the buffering, try using the pieces up to a given stage with " | cat " added at the end. If this buffers, you've found the problem. Unbuffered output is usually slower, so it is normally done only to a terminal. I think the only easy way to externally disable the buffer is to wrap the program in a pseudo-tty. Alternatively, look for an option that lets you explicitly unbuffer. (for instance, in perl, do: $| = 1; ) stdbuf? unbuffer? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed "endless" data into sqlite, thru a shell script
On 09/27/11 07:48, Patrick Proniewski wrote:
I though I could easily pipe data into SQLite:
iostat -d -w 10 disk0 |\
awk '!/[a-zA-Z]/ {print "INSERT INTO io
VALUES(datetime(\"now\",\"localtime\"),"$1","$2","$3");"}' |\
sqlite3 iostat.db
but it won't work, because sqlite3 won't record any data until the iostat
command ends. And of course, this iostat command will never end.
So I'm stuck with a working but very ugly script:
while true; do
iostat -c 2 -d -w 10 disk0 |\
tail -1 |\
awk '!/[a-zA-Z]/ {print "INSERT INTO io
VALUES(datetime(\"now\",\"localtime\"),"$1","$2","$3");"}' |\
sqlite3 iostat.db
done
endless loop, forking iostat for 2 rows of data (-c 2), keeping only the last
row because the first one is an artifact (tail -1).
I've tried various solutions with named pipes, file descriptors redirections…
but none worked, because they all seem to require the data steam to end before
feeding data into the DB.
Any idea?
I do not know if tee makes any difference or if it's available on Mac?
http://unixhelp.ed.ac.uk/CGI/man-cgi?tee
iostat -d -w 10 disk0 | tee -a logfile
and then
tail -f logfile | awk '!/[a-zA-Z]/ {print "INSERT INTO io
VALUES(datetime(\"now\",\"localtime\"),"$1","$2","$3");"}' |\
sqlite3 iostat.db
/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL query help
On 08/20/11 05:42 PM, Paul Sanderson wrote: > Hi all > > I am trying to create a query that works to craete a subset of a table > based on duplicate items > > Examples work best so consider the contrived table with the following rows > 10 socata > 7 socata > 13 cessna > 2 piper > 7 piper > 55 piper > 1 diamond > > I want to see the subset that is > 10 socata > 7 socata > 2 piper > 7 piper > 55 piper > > i.e. all rows that have a matching value in any other row in the second column > > any ideas? > ___ Might be more efficient queries if there is a LOT of records but this seems to do the trick. create table tbl (id,text); insert into tbl values (10, 'socata'); insert into tbl values (7, 'socata'); insert into tbl values (13, 'cessna'); insert into tbl values (2, 'piper'); insert into tbl values (7, 'piper'); insert into tbl values (55,'piper'); insert into tbl values (1, 'diamond'); select * from tbl where text in (select text from tbl group by text having count(*) > 1); 10|socata 7|socata 2|piper 7|piper 55|piper Cheers Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How best to determine changes in a db
On 07/23/11 01:09 PM, Kent Tenney wrote: > Right, but I really want a generic solution, since so many apps > store data in sqlite. if I can monitor Shotwell changes, I can > do the same for Banshee, Firefox, Zotero ... > Something like http://www.softwareaddins.com/CompareDataWiz.htm but for sqlite? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changing row separator to null when accessing sqlite frombash script
On 07/19/11 01:41 PM, Igor Tandetnik wrote: > Richard Taubo wrote: >> I have a bash script like this: >> >> #!/bin/bash >> OIFS=$IFS >> IFS=$'\n'; >> sql_command=`sqlite3 -noheader /My/Path/To/DB/ex1 "select one from tbl1 >> WHERE one LIKE '%this%';"` >> for i in $sql_command >> do >> echo "$i" >> done >> IFS=$OIFS; >> >> >> Since the column "one" consists of text with new lines in them, I am >> investigating of it is possible to >> set a row seperator (if that exists in sqlite) to null so I won't have any >> problems with text splitting up >> incorectly. > Try changing your query to something like > > select one || x'00' from tbl1 WHERE one LIKE '%this%'; > > You can add any other separator this way. Note that it will be in addition > to, not in place of, the line feed character. Maybe something like select replace(one,x'0a','') from tbl1 WHERE one LIKE '%this%'; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Defining a relationship as unique
On 06/30/11 02:31 PM, Black, Michael (IS) wrote: > sqlite> create table user(userid integer primary key autoincrement,name > varchar, login varchar); > sqlite> create unique index index1 on user(userid); Isn't userid already unique by "userid integer primary key"? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compute percentage?
On 06/29/11 01:01 PM, Cecil Westerhof wrote: > 2011/6/29 Roger Andersson mailto:r...@telia.com>> > > SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM > people),2) FROM people WHERE zip="12345"; > > > Would it not be better to do the CAST on the second SELECT? Then there > is only one CAST needed. In this case it does not matter much, but in > the general case it could. I can only see one CAST ;-) > Or maybe even better instead of doing * 100 in the first select, do * > .01 in the second. > :) No CAST needed SELECT round((COUNT(rowid))/(SELECT COUNT(*)*0.01 FROM people),2) FROM people WHERE zip="12345"; /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compute percentage?
On 06/29/11 01:02 PM, Mr. Puneet Kishor wrote: > SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) * 1.00 AS percentage > FROM people > WHERE zip="12345"; Seems to always return .0 ? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compute percentage?
On 06/29/11 12:34 PM, Gilles Ganault wrote: > Thanks, that worked: > SELECT COUNT(*) FROM people; > 400599 > > SELECT COUNT(*) FROM people WHERE zip="12345"; > 12521 > > SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) FROM people > WHERE zip="12345"; > 3 > > Is it possible to display the number with decimals instead of an > integer? SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM people),2) FROM people WHERE zip="12345"; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compute percentage?
On 06/29/11 11:22 AM, Gilles Ganault wrote: > Hello > > Using a table that lists people and the zipcode where they live, I > need to compute the percentage of those living in, say, NYC. > > I googled for this, but I'm not sure how to do this in SQLite. > > I wonder if it's done through a sub-query or maybe some temporary > variable? > > This computes the absolute: > SELECT COUNT(rowid) FROM people WHERE zip="12345"; > What about SELECT (COUNT(rowid)*100)/(select count(*) from people) FROM people WHERE zip="12345"; /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The last records of a query
On 06/25/11 09:26 AM, Cecil Westerhof wrote: > With LIMIT you can get the first N records of a SELECT. Is it also possible > to get the last N records? Search for LIMIT/OFFSET on http://www.sqlite.org/lang_select.html /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help
On 06/12/11 01:52 PM, Marco Bambini wrote:
> things are recently changed in my app and ping_timeout is now a client
> property set inside the Clients table (and no longer a global property), so I
> would like to perform the query:
> snprintf(sql, sizeof(sql), "select id from Clients where last_activity<
> datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout);
> using just the ping_timeout column in the Clients table instead of the
> settings.ping_timeout global property.
>
> Any idea?
snprintf(sql, sizeof(sql), "select id from Clients where last_activity <
datetime('now', '-%d.00 seconds', 'localtime');", ping_timeout);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unlocking the database
On 05/28/11 07:00 PM, Simon Slavin wrote: > SQLite locking is a function of your OS. It's not static things > something like "Byte 4 of the file is set to 'L'", it's transient > things handled with file handles or low level FS stuff. > So reboot. Or possibly find everything that might have that file open and > kill them all. > Maybe http://technet.microsoft.com/en-us/sysinternals/bb896655 can be of some use? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automating the build of a sqlite database
On 04/23/11 06:50 PM, Mihai Militaru wrote: > On Sat, 23 Apr 2011 12:17:54 -0400 > Tom Holden wrote: > > On Unices I use: "sqlite3.exe default.db3< schema.sql" as exemplified by DRH > (IIRC), but I guess > there's no way to do something similar on Windows cmd? > Have you tried exactly the same on Windows cmd? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cast(count(1) as integer) result is text?
> i've got no idea! I use ... > Using both ZeosLib and SQLite Expert the SubTotal (and count > if inserted) is returned as text > >From the command line sqlite3 -- Loading resources from C:\Documents and Settings\Roger/.sqliterc SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select cast(((1.2*2.1)/100) as real); cast(((1.2*2.1)/100) as real) 0.0252 AND sqlite> select typeof(cast(((1.2*2.1)/100) as real)); typeof(cast(((1.2*2.1)/100) as real)) real Maybe you should check with ZeosLib and SQLite Expert? //Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
> I'm getting there now ;) > > Keith I don't know if this will be of any help but you can do something like UPDATE file_downloads set dl_count = dl_count + 1 where filename = "$dl_file"; /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Round was Mistake in documentation and question
> In addition a quick question. > Is there a way to perform a division of two columns (real > type) and force the result to be presented/rounded in 2 > decimal places ? > ROUND should do the trick ;-) http://www.sqlite.org/lang_corefunc.html#round Please note http://www.sqlite.org/faq.html#q16 /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with auto boot
> Ämne: [sqlite] problem with auto boot > > Hi, > > no idea if it's the right way or place but I have a question > about the sqlite database. I did not found any solution in > other forums or by using google. > My problem: I wrote a simple program in c-sharp. This program > does refers to a sqlite database. The program is in my auto > boot so it shall run by starting by pc. But every time the is > an exception that the database could not be opened. I have no > idea why. If I run the program manual it works. Do I have to > start any process seperate to get access to the database? Do > you have any idea what I can do? > I haven't seen any progress on your issue and it might be as simple as: Unless you have the full path to the database in your program, what folder have you specified in Start in:? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why "select count(distinct f1, f2) from tbl_test" can not work?
> >> Now, I want to get the num of records which (f1, f2) are distinct. > >> > > Maybe something like > > SELECT f1,f2,count(*) FROM tbl_test GROUP BY f1,f2; > > That would return, for each (f1, f2) bucket, the number of > records that fall into this bucket. That doesn't sound like > what the OP wants. > -- > Igor Tandetnik OK, maybe I did misunderstand? /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why "select count(distinct f1, f2) from tbl_test" can not work?
> Ämne: [sqlite] Why "select count(distinct f1, f2) from > tbl_test" can not work? > > tbl_test maybe like this: > create table tbl_test(f1, f2, f3); > > Now, I want to get the num of records which (f1, f2) are distinct. > > I try "select count(distinct f1, f2) from tbl_test", but > error occur: SQL > error: wrong number of arguments to function count() > > > although, I can do like this, but I think it not very good : > select count(1) from (select distinct f1, f2 from tbl_test); > Maybe something like SELECT f1,f2,count(*) FROM tbl_test GROUP BY f1,f2; /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT question (computing day of week the usingstrftime() function)
> I need to find out how many specific weekdays (e.g., how many
> Sundays) I have in any given range of dates.
> My problem: How to use the COUNT function in combination with
> the strftime() function.
>
Maybe something like
sqlite3 test.db
SQLite version 3.6.23
sqlite> CREATE TABLE test (date TEXT, money INTEGER);
sqlite> INSERT INTO test VALUES('2007-07-20', 1000);
sqlite> INSERT INTO test VALUES('2007-07-21', 2100);
sqlite> INSERT INTO test VALUES('2007-07-22', 2200);
sqlite> INSERT INTO test VALUES('2007-07-27', 7000);
sqlite> INSERT INTO test VALUES('2007-07-28', 2800);
sqlite> INSERT INTO test VALUES('2007-07-29', 2900);
sqlite> INSERT INTO test VALUES('2007-07-22', 9200);
sqlite> SELECT strftime('%w', date) weekday, count(*) cnt FROM test GROUP BY
weekday;
weekday|cnt
0|3
5|2
6|2
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to submit a file with sql to sqlite"
> -Ursprungligt meddelande- > Från: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] För Gabor Grothendieck > Skickat: den 18 april 2010 18:17 > Till: General Discussion of SQLite Database > Ämne: Re: [sqlite] how to submit a file with sql to sqlite" > > On Sun, Apr 18, 2010 at 12:02 PM, Wensui Liu > wrote: > > dear listers, > > i am wondering if there is a way to submit a file with many sql > > statements, say several hundred lines, to sqlite. > > > > thanks for your insight. > > C:\tmp2>type a.sql > create table tab (a,b); > insert into tab values(1, 2); > insert into tab values(1, 2); > select * from tab; > > C:\tmp2>sqlite3 a.db < a.sql > 1|2 > 1|2 Or > type a.sql create table tab (a,b); insert into tab values(1, 2); insert into tab values(1, 2); select * from tab; > sqlite3 sqlite> select * from tab; Error: no such table: tab sqlite> .read a.sql a|b 1|2 1|2 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key support in Sqlite
> -Ursprungligt meddelande- > Från: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] För Luciano de Souza > Skickat: den 3 januari 2010 19:05 > Till: General Discussion of SQLite Database > Ämne: Re: [sqlite] Foreign key support in Sqlite > > I can't comprehend! I downloaded the two packs in c:\test. > Three files were > unpacked: sqlite3.exe, sqlite3.dll and sqlite3.def. > Maybe you need to check what Igor Tandetnik did say? http://www.sqlite.org/foreignkeys.html#fk_enable http://www.sqlite.org/pragma.html#pragma_foreign_keys /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selective result columns
> -Ursprungligt meddelande- > Från: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] För nomorecaddy > Skickat: den 31 december 2009 17:44 > Till: sqlite-users@sqlite.org > Ämne: Re: [sqlite] selective result columns > > > I'm operating at the user level in a java application. The > application allows me to run an SQL query, then it renders > the output into HTML. I want to avoid showing empty colums > in the HTML output, so I wanted the include/exclude column > logic in my actual SQL statement. > It might not be possible for you but still. Create the "actual SQL" with something like below, assuming that f1 never is NULL select distinct 'select f1'|| case when f2 is null then '' else ',f2' end|| case when f3 is null then '' else ',f3'|| ' from t1;' end from t1; And then use the result to query. If there are some records where f2 and/or f3 is NULL and others where one or both isn't you will get 2 or maybe even 3 lines from the above SQL. /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar of "X is Y"
> sqlite> select 1 is 2; > SQL error: near "2": syntax error > sqlite> select 1 is null; > 0 > > It seems to me the documentation is wrong here. That said I'd > much rather the behaviour of sqlite changed to match the docs > rather than vice-versa because I really want to write neat > queries like: > > select col1 is col2 from table > > Cheers > > Tom Sillence It looks like you are PRE SQLite version 3.6.19? D:\SQLite3.6.18> sqlite3 SQLite version 3.6.18 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select 1 is 2; SQL error: near "2": syntax error sqlite> D:\SQLite3> sqlite3 SQLite version 3.6.19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select 1 is 2; 0 sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?
> Unless I overlooked it, it won't let me copy all the rows > into the clipboard (tried CTRL-A, also tried selecting the > first and the last row followed by CTRL-C, to no avail). > > Also... > > "V3.01 29/11/2007" > > "Access violation at address 006C304F in module 'sqlite3Explorer.exe'. > Read of address 0039." > > Too bad :-/ Thanks anyway. > = > Never had any access violation but regarding number of records Options -> Fetch Size -> 0 And then like said by Donald, right click and Copy Result Set To Clipboard /Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table Exists Query
Maybe something like select db1.* from database1.lists db1 union select db2.* from database2.lists db2; and select db1.* from database1.list_2 db1 union all select db2.* from database2.list_2 db2; /Roger -Ursprungligt meddelande- Från: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] För Andrew Gatt Skickat: den 15 augusti 2009 16:13 Till: General Discussion of SQLite Database Ämne: [sqlite] Table Exists Query Hi all, I have multiple databases, both have a table called "lists" inside this table is the names of other tables in the database that make up the lists. Each individual list is made up of the tables that have the same name in each database (the list is split between different storage devices in this case). e.g. database 1 "lists" list_1, list_2 "list_1" item_1, item_2 "list_2" item_1, item_2 database 2 "lists" list_1, list_3 "list_1" item_3, item_4 "list_3" item_1,item_2 To get an output with all the lists of both databases i can use a union command on the "lists" table: select * from database1.lists union select * from database2.lists; However if i'm not sure which approach to take to get the contents of a specific list. I'm trying to replicate this sort of statement: select * from database1.list_2 union all select * from database2.list_2; but by my scheme you can't be sure if the list_1 table exists in database2, which if i'm right will error the statement and provide no output? The other option is to run a set of statements first checking whether the table exists and keeping a record of which database does and building the statement that way, but this seems inelegant. I was hoping someone would have seen this kind of behaviour before and could point me in the right direction of a solution? All comments are welcome. Thanks, Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IP from number with SQL
Thanks John, appreciated! -Ursprungligt meddelande- Från: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] För John Machin Skickat: den 16 mars 2009 00:51 Till: sqlite-users@sqlite.org Ämne: Re: [sqlite] IP from number with SQL On 16/03/2009 8:48 AM, Kees Nuyt wrote: > On Sun, 15 Mar 2009 21:10:02 +0100, "Roger Andersson" > wrote: > >> Hi! >> >> The SQL below might be out there but I didn't find it and since there >> might be other that need to get 32-bit integer IP in a sqlite3 >> database to the a.b.c.d format using SQL >> >> I did get started from >> http://acidlab.sourceforge.net/acid_faq.html#faq_e1 >> and for me what's below does the trick in sqlite3 :-) >> >> SELECT >> CAST((intIP & 4278190080) >> 24 AS text)||'.'|| CAST((intIP & >> 16711680) >> 16 AS text)||'.'|| CAST((intIP & 65280) >> 8 AS >> text)||'.'|| CAST((intIP & 255) AS text) AS strIP FROM IP_table; > > Cute code, thanks. We appear to have differing meanings for "cute" :-) Following are two iterations of make-over: SQLite version 3.6.10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE IP_table (intIP integer); sqlite> sqlite> INSERT INTO IP_table VALUES(12345678); INSERT INTO IP_table sqlite> VALUES(9876543210123); sqlite> sqlite> SELECT rowid, intIP, ...> CAST((intIP & 4278190080) >> 24 AS text)||'.'|| ...> CAST((intIP & 16711680) >> 16 AS text)||'.'|| ...> CAST((intIP & 65280) >> 8 AS text)||'.'|| ...> CAST((intIP & 255) AS text) AS strIP ...> FROM IP_table; 1|12345678|0.188.97.78 2|9876543210123|143.217.130.139 sqlite> sqlite> SELECT rowid, intIP, ...> CAST((intIP >> 24) & 255 AS text)||'.'|| ...> CAST((intIP >> 16) & 255 AS text)||'.'|| ...> CAST((intIP >> 8) & 255 AS text)||'.'|| ...> CAST((intIP ) & 255 AS text) AS strIP ...> FROM IP_table; 1|12345678|0.188.97.78 2|9876543210123|143.217.130.139 sqlite> sqlite> SELECT rowid, intIP, ...> ((intIP >> 24) & 255) ||'.'|| ...> ((intIP >> 16) & 255) ||'.'|| ...> ((intIP >> 8) & 255) ||'.'|| ...> ((intIP ) & 255) AS strIP ...> FROM IP_table; 1|12345678|0.188.97.78 2|9876543210123|143.217.130.139 sqlite> Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] IP from number with SQL
Hi! The SQL below might be out there but I didn't find it and since there might be other that need to get 32-bit integer IP in a sqlite3 database to the a.b.c.d format using SQL I did get started from http://acidlab.sourceforge.net/acid_faq.html#faq_e1 and for me what's below does the trick in sqlite3 :-) SELECT CAST((intIP & 4278190080) >> 24 AS text)||'.'|| CAST((intIP & 16711680) >> 16 AS text)||'.'|| CAST((intIP & 65280) >> 8 AS text)||'.'|| CAST((intIP & 255) AS text) AS strIP FROM IP_table; Cheers Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
