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

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

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

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

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

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

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 ___

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] Compute percentage?

On 06/29/11 01:01 PM, Cecil Westerhof wrote: > 2011/6/29 Roger Andersson <r...@telia.com <mailto:r...@telia.com>> > > SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM > people),2) FROM people WHERE zip="12345"; > > > Would it

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

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

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

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

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

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

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

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

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

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

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

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

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

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,

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!

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

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

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

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

Re: [sqlite] IP from number with SQL

wrote: > On Sun, 15 Mar 2009 21:10:02 +0100, "Roger Andersson" > <r...@telia.com> 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 >> d

[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