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

"James K. Lowden" 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 >

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

"James K. Lowden" 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

[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] Full outer joins

Richard Hipp 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

[sqlite] Decimal point and localisation

ERIC for the output - no ambiguity would then arise? -- 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

uld want to upgrade as NATURAL FULL OUTER JOIN would simplify my code 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] Find first non-NULL values of several columns

[0]); > p->flag = 1; > } > } > > SQLITE_PRIVATE void fnnFinal(sqlite3_context *context) > { > fnnCtx* p = sqlite3_aggregate_context(context, 0); > if (p && p->flag == 1) > sqlite3_result_double(context, p->fnn); > } -- Steinar Midtskogen

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

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

'-1 hour') AND col_x 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://s

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

1), (SELECT f FROM v WHERE f 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

[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

[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

Re: [sqlite] Read-only media

Can similar things happen 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

wed me to make a web 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)" 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

able" will call the constructor 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

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.org>wrote: > >> My xCreate has to run some queries in order to build its declare >> statement. If a virtual table is queried inside xCreate causing

[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] Permissions

[Richard Hipp] > On Sun, Apr 22, 2012 at 12:40 PM, Steinar Midtskogen > <stei...@latinitas.org>wrote: > >> >> Any reason why sqlite doesn't use the same file permissions as the >> database file when creating these extra files? >> >> > Th

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] Permissions

Stephan Beal 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

e unless it's group writeable. Having 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 slightl

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

[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] 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

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 c

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?

Puneet Kishor 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

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

Alessandro Marzocchi 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

[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] Select time('now')

YAN HONG YE 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

Re: [sqlite] xCreate calling itself

n. -Steinar Steinar Midtskogen <stei...@latinitas.org> writes: > 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,

[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] 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 simpl

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

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

[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] how to wrie "" char in my html file?

[YAN HONG YE] >>sqlite3 -html C:\mydatabase\mydzh.db "select ''">mm.html > this command result is not in the mm.html file, it's this following > text: > table > > not I wanted, how to wrie char in my html file? That would create invalid html. "table" will be displayed as "" by the html

Re: [sqlite] about sqlite3_exec function

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

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

[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

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

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 cod

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.

[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

[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

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

[C M ] > 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

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

[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

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 i

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_s

[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] 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 t

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 >

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

[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

[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

[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 s

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

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

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

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

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

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

[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

[sqlite] Interpolation

that does all 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-use