Re: [sqlite] WAL and updates

2012-11-25 Thread Roger Andersson

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')"

2012-11-04 Thread Roger Andersson
-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

2012-11-03 Thread Roger Andersson

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

2012-07-20 Thread Roger Andersson

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?

2012-03-09 Thread Roger Andersson

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

2012-02-25 Thread Roger Andersson

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

2012-02-19 Thread Roger Andersson

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

2012-02-19 Thread Roger Andersson

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)

2012-02-16 Thread Roger Andersson

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

2012-02-12 Thread Roger Andersson

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

2012-02-12 Thread Roger Andersson

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

2011-12-21 Thread Roger Andersson

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

2011-11-29 Thread Roger Andersson

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

2011-11-09 Thread Roger Andersson

 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

2011-10-22 Thread Roger Andersson

 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

2011-10-16 Thread Roger Andersson

 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

2011-10-16 Thread Roger Andersson

 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

2011-09-28 Thread Roger Andersson

 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

2011-09-28 Thread Roger Andersson

 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

2011-09-28 Thread Roger Andersson

 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

2011-09-27 Thread Roger Andersson

 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

2011-09-27 Thread Roger Andersson

 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

2011-08-20 Thread Roger Andersson
  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

2011-07-23 Thread Roger Andersson
  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

2011-07-19 Thread Roger Andersson
  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

2011-06-30 Thread Roger Andersson
  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?

2011-06-29 Thread Roger Andersson
  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?

2011-06-29 Thread Roger Andersson
  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?

2011-06-29 Thread Roger Andersson
  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?

2011-06-29 Thread Roger Andersson
  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

2011-06-25 Thread Roger Andersson
  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

2011-06-12 Thread Roger Andersson
  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

2011-05-28 Thread Roger Andersson
  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

2011-04-23 Thread Roger Andersson
  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?

2011-01-06 Thread Roger Andersson
> 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?

2010-09-17 Thread Roger Andersson
> 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

2010-08-09 Thread Roger Andersson
> 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

2010-07-02 Thread Roger Andersson
> Ä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?

2010-06-26 Thread Roger Andersson
> >> 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?

2010-06-26 Thread Roger Andersson
> Ä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)

2010-05-01 Thread Roger Andersson
> 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"

2010-04-18 Thread Roger Andersson
> -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

2010-01-03 Thread Roger Andersson
> -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

2009-12-31 Thread Roger Andersson

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

2009-10-28 Thread Roger Andersson
> 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?

2009-10-23 Thread Roger Andersson
> 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

2009-08-15 Thread Roger Andersson
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

2009-03-15 Thread Roger Andersson
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

2009-03-15 Thread Roger Andersson
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