Re: [sqlite] Keeping -wal and -shm files

James K. Lowden jklow...@schemamania.org writes: and -o grpid changes the directory's *gid* effect, not setgid. Are you sure that the directory is setgid? $ ls -ld steinar drwxrwxr-x 2 jklowden wheel 512 Oct 28 09:54 steinar $ chmod 2775 steinar $ ls -ld steinar drwxrwsr-x 2

[sqlite] Keeping -wal and -shm files

files from being deleted after use, so that I can have them always have the right group? Or is there a way to tell Linux to observe the setgid flag on a directory (ext4)? -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http

Re: [sqlite] Keeping -wal and -shm files

James K. Lowden jklow...@schemamania.org writes: See -o grpid in mount(8). I think that's what you want. Thanks. It works! -- Steinar ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Full outer joins

Richard Hipp d...@sqlite.org writes: In 14 years, you are the first person to ask for them. That tells me that probably not many people would use them even if we did put them in. I've only written one program using sqlite a few years ago, and I had to make an ugly workaround using UNIONs and

[sqlite] Decimal point and localisation

? -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] xColumn called twice for a value

a second time for c, which seems wasteful if c takes time to compute. Or very bad if the computation has side effects (which it probably shouldn't have). Is there any reason for this behaviour? My SQlite version is 3.7.14. -- Steinar Midtskogen ___ sqlite

[sqlite] Find first non-NULL values of several columns

(similar to coalesce) since the order will not be defined. But perhaps I'm missing the obvious solution. -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find first non-NULL values of several columns

IS NOT NULL ORDER BY a LIMIT 1); to get 1|5|2|4|9? -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find first non-NULL values of several columns

IS NOT NULL ORDER BY unix_time DESC LIMIT 1 will stop early. But this will not be a problem for me since I want to have a modest upper limit (1 hour) anyway. -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org

Re: [sqlite] Find first non-NULL values of several columns

Ryan Johnson ryan.john...@cs.utoronto.ca writes: On 19/10/2012 3:09 PM, Steinar Midtskogen wrote: I have tables with a timestamp (unix time) and columns containing sensor readings which are inserted continuously. I frequently need to access the most recent values (or NULL

Re: [sqlite] Find first non-NULL values of several columns

(context, 0); if (p p-flag == 1) sqlite3_result_double(context, p-fnn); } -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find first non-NULL values of several columns

and perhaps SQLite can optimise that better than my current use of UNIONs and NATURAL LEFT OUTER JOIN. -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Read-only media

if you copy the database file (using cp/tar/whatever) while an application wants to write to it? -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to update the Moving average value

interface for my weather data: http://voksenlia.net/met/data/plot.php (Norwegian only) -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views Optimization

Black, Michael (IS) michael.bla...@ngc.com writes: Perhaps the query flattener should ignore any nondeterministic functions? Are there any others besides random() or date/time functions? User defined functions. Or views on virtual tables. -- Steinar

[sqlite] Force drop table

and if the interface has changed, the constructor might fail. -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FULL OUTER JOIN

http://www.sqlite.org/omitted.html lists what's not supported and those features near the top of the list are likely to be added in the near future. The list has been unchanged for a few years now, and RIGHT and FULL OUTER JOIN are on the top of the list. I wonder, is it still likely that RIGHT

[sqlite] db-pVtabCtx and xCreate accessing a virtual table

The sqlite3 struct has a pVtabCtx pointer. It seems to me that it will be shared between multiple xCreate in action simultaniously and in that case cause disaster. This can happen even if there is no thread concurrency going on using the same database connection. My xCreate has to run some

Re: [sqlite] db-pVtabCtx and xCreate accessing a virtual table

Richard Hipp d...@sqlite.org writes: On Thu, Apr 26, 2012 at 5:07 PM, Steinar Midtskogen stei...@latinitas.orgwrote: My xCreate has to run some queries in order to build its declare statement. If a virtual table is queried inside xCreate causing a another xCreate to be called

Re: [sqlite] Permissions

[Richard Hipp] On Sun, Apr 22, 2012 at 12:40 PM, Steinar Midtskogen stei...@latinitas.orgwrote: Any reason why sqlite doesn't use the same file permissions as the database file when creating these extra files? There was a change in version 3.7.11 to do exactly that. http

[sqlite] Permissions

This might be slightly off topic, but perhaps a common problem for many sqlite users. I have a database (wal mode) that apache (the web server) needs to access, readonly. Since it needs to be able to lock it for reading, apache needs write access. So the database has these permissions:

Re: [sqlite] Permissions

apache run with umask 002 should fix this, but I wonder if there is another workaround (and I haven't figured yet out how to configure apache to do this, anyway). -Steinar On 04/22/2012 10:31 AM, Steinar Midtskogen wrote: This might be slightly off topic, but perhaps a common problem for many

Re: [sqlite] Permissions

Stephan Beal sgb...@googlemail.com writes: Try the sticky bit: chown user:apache theDir chmod 4775 theDir I think the effect of that only is to restrict anyone but root or the owner of a file from deleting or renaming an otherwise writeable file in that directory. -- Steinar

Re: [sqlite] Permissions

[Simon Slavin] The solution I came up with is that the database file owner also uses Apache to look at it: I use web-facing database administration software rather than opening the database in another application. (I wrote a simple one myself in PHP and JavaScript.) However this is

Re: [sqlite] Why are two select statements 2000 times faster than one?

To answer my own question, whether there is an efficient way to find max() of an increasingly sorted column in a virtual array: What is needed is to make sure that xBestIndex sets orderByConsumed, and that the module takes care of all sorting. -- Steinar

Re: [sqlite] Why are two select statements 2000 times faster than one?

Hello again, Another question about max()/min() optimisation. Is there a way I can implement a virtual table so that max()/min() of a sorted (incrementing) column (which could be an integer primary key in a regular table) gets fast? For example, sqlite explain query plan select max(unix_time)

Re: [sqlite] Why are two select statements 2000 times faster than one?

[Simon Slavin] On 15 Apr 2012, at 1:31pm, Steinar Midtskogen stei...@latinitas.org wrote: Another question about max()/min() optimisation. Is there a way I can implement a virtual table so that max()/min() of a sorted (incrementing) column (which could be an integer primary key

Re: [sqlite] Why are two select statements 2000 times faster than one?

[Kit] 2012/4/15 Steinar Midtskogen stei...@latinitas.org: So, is there really a way to create an index in a virtual table, or a way to emulate this? Why? You don't need this. Use index on base tables. My base tables are indexed. Let's say I want to make a very simple virtual table this way

Re: [sqlite] Why are two select statements 2000 times faster than one?

[Gerry Snyder] At worst you could use another table to keep track of the maximum and minimum, and update it with triggers when something is added to or deleted from the virtual table. My module knows what the maximum and minimum values are at all times. It also knows that the column is

Re: [sqlite] Select time('now')

YAN HONG YE yanhong...@mpsa.com writes: the current time is 15:15 when I use this following command: sqlite Select time('now'); return 07:15:42 not current time,why? Read http://www.sqlite.org/lang_datefunc.html Format 11, the string 'now', is converted into the current date and time as

[sqlite] Why are two select statements 2000 times faster than one?

Hello, I have a table with unix_time as primary key and I want to get the minimum and maximum values of unix_time. When I do: SELECT min(unix_time), max(unix_time) from table; it is very slow. It takes about 250ms, nearly everything in the step() call. However, if I do: SELECT

Re: [sqlite] Why are two select statements 2000 times faster than one?

Alessandro Marzocchi alessandro.marzoc...@gmail.com writes: What does EXPLAIN QUERY PLAN says? sqlite EXPLAIN QUERY PLAN SELECT min(unix_time) FROM table; 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows) sqlite EXPLAIN QUERY PLAN SELECT max(unix_time) FROM table; 0|0|0|SEARCH TABLE

Re: [sqlite] Why are two select statements 2000 times faster than one?

Puneet Kishor punk.k...@gmail.com writes: If you want the results in separate columns, you can do something like SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' minimum, Max(a) minimum FROM t; Then it does a full scan again. But Igor's suggestion SELECT (SELECT

[sqlite] xCreate calling itself

Hello, In certain cases when I try to create a virtual table, sqlite3_declare_vtab() returns SQLITE_MISUSE. Nothing appears to be wrong with the string I pass to the sqlite3_declare_vtab(). That is, if I execute the create table statement in that string, a regular table will be created - no

Re: [sqlite] xCreate calling itself

v1 (causing xCreate to be called for v1). After sqlite3_prepare_v2() returns, pVtabCtx is 0. Is this a bug, or is it intentional (possibly for a good reason)? My xCreate function needs to run queries on the input tables to find the column names needed for the vtab declaration. -Steinar Steinar

[sqlite] Selecting every nth row efficiently

I have a big database with timestamps and sensor readings, which I access with SELECT to have gnuplot draw graphs. However, sometimes I have readings every minute and want to plot several years of data, and feeding everything to gnuplot is overkill. In these cases it would be sufficient to

Re: [sqlite] Selecting every nth row efficiently

[Jean-Christophe Deschamps] If your sampling is essentially regular, why not make it select ... where timestamp % N between min_interval and max_interval N being the typical time delta of your n rows above and interval bounds reducing the possiblity of gross under- and over-sampling. May

Re: [sqlite] Selecting every nth row efficiently

[Jean-Christophe Deschamps] You're going to have at most one random sample in every slice of 320 s. The GROUP BY clause will select only one for you and the query can be as simple as: select sample from from mytable group by timestamp / 320 order by timestamp; Ah. I didn't think of that.

Re: [sqlite] Selecting every nth row efficiently

is the primary key) -Steinar Steinar Midtskogen stei...@latinitas.org writes: [Jean-Christophe Deschamps] You're going to have at most one random sample in every slice of 320 s. The GROUP BY clause will select only one for you and the query can be as simple as: select sample from from mytable

Re: [sqlite] about sqlite3_exec function

[YAN HONG YE] sqlite3_exec( db, ???, 0, 0, pErrMsg); I wanna add this following command into sqlite3_exec func: sqlite3 -html -header mydzh.db \select * from dhq where ph1510;\ mm.html like this: sqlite3_exec( db, -html -header \select * from dhq where ph1510;\ mm.html, 0, 0, pErrMsg);

Re: [sqlite] how to wrie table char in my html file?

[YAN HONG YE] sqlite3 -html C:\mydatabase\mydzh.db select 'table'mm.html this command result is not table in the mm.html file, it's this following text: TRTDlt;tablegt;/TD /TR not I wanted, how to wrie table char in my html file? That would create invalid html. lt;tablegt; will be

Re: [sqlite] how to export to html file?

[YAN HONG YE] my sqlite database want to export to html file, I know the command sqlite3 -html film.db select * from film; could show the table in cmd window, but how to export to the html file like sqlite3 -html film.db mm.html select * from film; sqlite3 -html film.db select * from

[sqlite] User input checking

I'm planning to allow users to make database queries through a web page. I'm thinking of letting the user provide the string that goes between SELECT and FROM using the sqlite3 command tool, but what kind of input checking is then needed? Obviously, I need to check that the input doesn't contain

[sqlite] SQLite bug?

I've created a module which will take a table as an argument. In Xconnect it will run a query on that table. Then I accidently used the name of the virtual table in the argument list: CREATE VIRTUAL TABLE v USING my_module(v); which seems to have triggered an infinite call loop. I suppose

Re: [sqlite] SQLite bug?

Jay A. Kreibich j...@kreibi.ch writes: On Wed, Mar 07, 2012 at 08:58:27PM +0100, Steinar Midtskogen scratched on the wall: I've created a module which will take a table as an argument. In Xconnect it will run a query on that table. Then I accidently used the name of the virtual table

Re: [sqlite] SQLite bug?

Steinar Midtskogen stei...@latinitas.org writes: I can easily check that the argument doesn't match argv[2], but if it's still possible to get into a loop like this if virtual tables refer to eachother, then I don't think it's possible to detect this in the VT code. I think indeed

Re: [sqlite] SQLite bug?

Roger Binns rog...@rogerbinns.com writes: On 07/03/12 13:46, Steinar Midtskogen wrote: I think indeed that this is a problem: I cannot reproduce it using my own virtual tables. This strongly implies that it is something to do with the code for your virtual tables. Doesn't your

[sqlite] xRowid doesn't get called

NULLs and my xRowid function never gets called. If it never gets called for read-only tables, why then is it required? -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite

Re: [sqlite] xFilter or xBestIndex needs to know which columns were selected

to do the actual computations. That will give me a tradeoff between speed and memory usage. -Steinar Steinar Midtskogen stei...@latinitas.org writes: Thanks to Dan and Roger for the information and suggestions. I have about 100 columns (and millions of rows), but usually queries will only ask

Re: [sqlite] xFilter or xBestIndex needs to know which columns were selected

needed and hopefully reduce it to only the interesting columns at the same time. It will require a bit of bookkeeping, but seems doable. -Steinar Roger Binns rog...@rogerbinns.com writes: On 23/02/12 23:02, Steinar Midtskogen wrote: I know that xColumn will only get called for these columns

Re: [sqlite] SELECT average timestamp to get average time of day?

[C M cmpyt...@gmail.com] For example, the average I'd want from these three timestamps: '2012-02-18 22:00:00.00' '2012-02-19 23:00:00.00' '2012-02-28 01:00:00.00' Should be 11:20pm, as they are all within a few hours of each other at night. I have not been able to find a

[sqlite] xFilter or xBestIndex needs to know which columns were selected

Hello Is it possible to find out in xFilter or xBestIndex which columns were selected? That is, if I do SELECT a, b, c FROM t where t is a virtual table, I would like to know in xFilter or xBestIndex that the result will only consist of the rows a, b and c. I know that xColumn will only get

[sqlite] Lifetime of an sqlite_value object

like to deal with the value type until I actually need the value. -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lifetime of an sqlite_value object

Larry Brasfield larry_brasfi...@iinet.com writes: Steinar Midtskogen wrote: Is it safe to assume that the object pointed to by sqlite3_column_value() will exist until sqlite3_finalize() is called? Or will it only be valid until the next call to sqlite3_step()? From the API doc titled Result

Re: [sqlite] Lifetime of an sqlite_value object

Steinar Midtskogen stei...@latinitas.org writes: Thank you. A followup question: Is there a portable way to find the size of an sqlite_value object? I had a quick look in the sqlite3 source code. With knowledge of the internal structures it seems possible to copy a value if everything inside

[sqlite] Function context

Hello Is it possible to have a context for a custom SQL function that is NOT an aggregate function? It might sound silly, but if a SELECT statement with this function causes the function to be called in a defined order (as with the step function of an aggregate function), this can be useful to

Re: [sqlite] Function context

[Peter Aronson] (2) You can associate data with an argument to a regular user-defined function using sqlite3_set_auxdata() and sqlite3_get_auxdata() as long as the value of the argument is static. If you don't normally have a static argument to your function, you can add one (say a string

Re: [sqlite] Function context

[Scott Hess] I think you're making unwarranted assumptions about the order your custom function will be called. Even if you added ORDER BY to the end of the query, that wouldn't necessarily order the calls to your custom function. Even if you find a workaround which allows you to implement

Re: [sqlite] Function context

[Simon Slavin] On 13 Feb 2012, at 7:51pm, Steinar Midtskogen wrote: One should think that the ability to calculate a moving average would be a pretty common request. But people do it in their application code instead? Actually, my expectation is the other way. I'm continually surprised

[sqlite] SQLite libraries

Hello Has anyone collected a library of extensions to SQLite, such as useful aggregate functions, modules, etc? There is http://www.sqlite.org/contrib and extension-functions.c, but is there more out there? -- Steinar ___ sqlite-users mailing list

Re: [sqlite] Interpolation

if an SQL statement to do this sounds unrealistic, that would be a good answer as well. Thanks, -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interpolation

[Igor Tandetnik] timestamp|value1|value2|value3|value4|value5|value6 1328873000|1|2| | | | 1328873050| | |7| | | 1328873075| | | |10|13|16 1328873100|3|4| | | | 1328873150| | |8| | | 1328873175| | | |11|14|17 1328873200|5|6| | | | 1328873250| | |9| | | 1328873275| | |

Re: [sqlite] Interpolation

[Igor Tandetnik] Steinar Midtskogen stei...@latinitas.org wrote: Thanks, I didn't think in that simple terms. :) I think about listing all the values, so I got lost. I lost a word there: I didn't think about listing... But what if the tables share a timestamp, then I would get, say

Re: [sqlite] Interpolation

[Igor Tandetnik] If you need a particular order, it's best to add an explicit ORDER BY. Otherwise, you are at the mercy of an implementation. Your current version of SQLite chooses an execution plan that happens, by accident, to produce rows in the desired order. Tomorrow you upgrade to a

Re: [sqlite] Interpolation

[Simon Slavin] On 10 Feb 2012, at 3:24pm, Steinar Midtskogen wrote: I feared that. As it is, it takes 6 seconds to do a SELECT * FROM Combined LIMIT 1 (Combined is a view representing the merged table). If I add an ORDER BY, it takes 35 seconds. Any way to speed up the ordering

Re: [sqlite] Interpolation

[Roger Binns] I'd recommend you write code in your application first that knows how to calculate the values you want. That way you can ensure the calculations are correct, you have something for test harnesses that produces good values and you have something to port to your final solution.

Re: [sqlite] Interpolation

Related to this thread, I wonder if it's possible to create a view which can give me a value from the row immediately above. E.g. given the table: unix_time val --+--- 1325376000|val1 1325376300|val2 1325376600|val3 1325376900|val4 (the first column is a unix timestamp and unique) can

Re: [sqlite] Interpolation

[Kevin Martin] For the quick and dirty solution, I think you can use something like this to create your view. You would need to index time, and even with the index, I'm not too sure about speed. select x1.time, x1.value, x2.time from x as x1 left join x as x2 on x2.time=(select max(time)

Re: [sqlite] Interpolation

[Kit] SELECT a.temp+(b.temp-a.temp)/(b.time-a.time)*(strftime('%s','2012-02-08 11:37:00')-a.time) FROM (select time, temp FROM tp WHERE strftime('%s','2012-02-08 11:37:00')*1=time ORDER BY time DESC LIMIT 1) AS a, (select time, temp FROM tp WHERE strftime('%s','2012-02-08

[sqlite] Inserts get slower and slower

Hello I'm having trouble with one table in my database. When I build my database from scratch using millions of inserts, one table causes problems. Inserts get slower and slower. I have about 830,000 inserts for that table. It gets to 300,000 pretty fast, but then it gets slower and slower,

Re: [sqlite] Inserts get slower and slower

Thanks to all for suggestions. My guesses: - Your unix_time values are not successive. In this case your first fast results are due to advantages of memory caching. The following slowness is the result of the internal fragmentation All unix_time values should be successive, but in the

[sqlite] Interpolation

the interpolation for every second and store the interpolated values in the database, that would be very simple, but that would also make the database way too large and slow. Thanks. -- Steinar Midtskogen ___ sqlite-users mailing list sqlite-users@sqlite.org http