Re: [sqlite] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

[Kevin Martin]

> On 13 Aug 2019, at 14:08, Richard Hipp  wrote:
> 
> I think that is correct.
> 

Great, thanks.

> But it never occurred to me that somebody might do this on the PRIMARY
> KEY.  I don't see any reason why it wouldn't work, though.

I have a c++ interface built on top of the virtual table api which multiple 
modules are then built on top of, it doesn't know which columns are 
large/expensive, so the idea was just to use call sqlite3_vtab_nochange for all 
of them, which includes the primary key.


> On 13 Aug 2019, at 13:00, Hick Gunter  wrote:
> 
> Very strange and AFAICT not documented. I would not have though that calling 
> sqlite3_value_nochange on argv[1] was even legal, given that it would 
> correspond to field number -1. Could you provide an "explain" (SQlite 
> bytecode program dump) of your statement?

See below JSON for the explain as the table is not accessible in the command 
line tool.

Thanks,
Kev

---

[
  {
"sql":"pragma table_info(modeloption_vt_writable);",
"cols":["cid", "name", "type", "notnull", "dflt_value", "pk"],
"time":0.016,
"results":[
  ["0", "option", "text", "1", "", "1"],
  ["1", "value", "text", "0", "", "0"]
]
  },
  {
"sql":"\n\nexplain update modeloption_vt_writable set value = 'v' where 
option='o';",
"cols":["addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment"],
"time":0,
"results":[
  ["0", "Init", "0", "25", "0", "", "00", "Start at 25"],
  ["1", "OpenEphemeral", "2", "4", "0", "", "00", "nColumn=4"],
  ["2", "VOpen", "1", "0", "0", "vtab:C3289DFC0", "00", ""],
  ["3", "Integer", "0", "10", "0", "", "00", "r[10]=0"],
  ["4", "Integer", "0", "11", "0", "", "00", "r[11]=0"],
  ["5", "VFilter", "1", "16", "10", "", "00", "iplan=r[10] zplan=''"],
  ["6", "VColumn", "1", "0", "12", "", "00", "r[12]=vcolumn(0); 
modeloption_vt_writable.option"],
  ["7", "Ne", "13", "15", "12", "(BINARY)", "52", "if r[12]!=r[13] goto 
15"],
  ["8", "VColumn", "1", "0", "6", "", "01", "r[6]=vcolumn(0)"],
  ["9", "String8", "0", "7", "0", "v", "00", "r[7]='v'"],
  ["10", "VColumn", "1", "0", "4", "", "00", "r[4]=vcolumn(0)"],
  ["11", "SCopy", "6", "5", "0", "", "00", "r[5]=r[6]"],
  ["12", "MakeRecord", "4", "4", "8", "", "00", "r[8]=mkrec(r[4..7])"],
  ["13", "NewRowid", "2", "9", "0", "", "00", "r[9]=rowid"],
  ["14", "Insert", "2", "8", "9", "", "00", "intkey=r[9] data=r[8]"],
  ["15", "VNext", "1", "6", "0", "", "00", ""],
  ["16", "Rewind", "2", "23", "0", "", "00", ""],
  ["17", "Column", "2", "0", "4", "", "00", "r[4]="],
  ["18", "Column", "2", "1", "5", "", "00", "r[5]="],
  ["19", "Column", "2", "2", "6", "", "00", "r[6]="],
  ["20", "Column", "2", "3", "7", "", "00", "r[7]="],
  ["21", "VUpdate", "0", "4", "4", "vtab:C3289DFC0", "02", "data=r[4..7]"],
  ["22", "Next", "2", "17", "0", "", "00", ""],
  ["23", "Close", "2", "0", "0", "", "00", ""],
  ["24", "Halt", "0", "0", "0", "", "00", ""],
  ["25", "Transaction", "0", "1", "1", "0", "01", "usesStmtJournal=1"],
  ["26", "VBegin", "0", "0", "0", "vtab:C3289DFC0", "00", ""],
  ["27", "String8", "0", "13", "0", "o", "00", "r[13]='o'"],
  ["28", "Goto", "0", "1", "0", "", "00", ""]
]
  }
]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

[Kevin Martin]

> On 12 Aug 2019, at 07:53, Hick Gunter  wrote:
> 
> You did not state your argc and argv[0] values,

Apologies, it is a 2 column table. Full details are:

- argc is 4
- argv[0] is the value of the primary key for the row I want to update.
- argv[1] is SQLITE_NULL, but as described, sqlite3_value_nochange(argv[1]) 
returns true
- argv[2] is the same as argv[1] (null, but sqlite3_value_nochange returns true)
- argv[3] is the new value of the non-primary key column. 

> so looking at the documentation would suggest that SQLite is actually asking 
> for an INSERT into a WITHOUT ROWID virtual table.

I see that is what the documentation leads you to believe, but I can assure you 
I am exciting an update of the form

update t set notprimarykey='some value' where primarykey='other value'

Removing the sqlite3_vtab_nochange from the xColumn call gets the documented 
behaviour. If I keep the sqlite3_vtab_nochange and change my code in xUpdate as 
described in the last email, everything seems to work. I just want to check it 
is correct.

Thanks,
Kev
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Correct use of sqlite3_vtab_nochange/sqlite3_value_nochange

[Kevin Martin]

Hi,

I have a without rowid virtual table with an implementation of xColumn that 
begins with

if(sqlite3_vtab_nochange(ctx)) return SQLITE_OK;

If I try to perform an update on this table that doesn't involve a primary key 
change, then my understanding from the documentation is that xUpdate will be 
called and the value of argv[0] and argv[1] will be the same. What I am seeing 
is that argv[1] is set an sql null value, although when I call 
sqlite3_value_nochange(argv[1]) I do get true returned.

Am I therefore right in thinking that the correct detection of whether there is 
an update without a primary key change when using sqlite3_vtab_nochange is 
actually

sqlite3_value_nochange(argv[1]) || values_are_equal(argv[0], argv[1])

Thanks,
Kev
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Optimising multiple group by clauses

[Kevin Martin]

Hi,

I am trying to find the best way to write a query that has two levels of group 
by where the outer group by columns are a subset of the inner group by columns. 
In my example below I want to do an aggregation grouping by per, prod, and mar, 
then I want aggregate the results of this aggregation, grouping by just prod, 
and per. From the results of explain query plan, I can see a B-Tree is not used 
if I only do the first group by - this is mentioned in the query optimisation 
page. However, a B-Tree is used for the second group by when both group bys are 
present, and I don't understand why, as I think the rows from the subquery come 
out in an order already suitable for the second group by?

My actual data is a bit more complex, but I am seeing a 10x-20x speed 
difference between the query with the single group by and the query with both. 
If the B-Tree is necessary, it would be good to be able to understand why, and 
if not, it would be great if there were some way to communicate this to the 
query planner.

This below was run on a freshly downloaded and compiled 3.28.

Thanks,
Kev

---

QUERY PLAN
`--SCAN TABLE data USING INDEX sqlite_autoindex_data_1
QUERY PLAN
|--CO-ROUTINE 1
|  `--SCAN TABLE data USING INDEX sqlite_autoindex_data_1
|--SCAN SUBQUERY 1
`--USE TEMP B-TREE FOR GROUP BY



create table data(
  prod integer not null,
  per integer not null,
  mar integer not null,
  off integer not null,
  val real not null,
  primary key(prod, per, mar, off)
);

explain query plan select
  prod,
  per,
  mar,
  sum(val) as val
from
  data
group by
  prod,
  per,
  mar
;

explain query plan select
  prod,
  per,
  min(val)
from
  (select
prod,
per,
mar,
sum(val) as val
  from
data
  group by
prod,
per,
mar)
group by
  prod,
  per
;


   
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] picking random subset of rows

[Kevin Martin]

> On 18 Mar 2019, at 16:15, Dan Kennedy  wrote:
> 
> 
> In SQLite, a correlated sub-query on the RHS of an IN(...) operator may be 
> rerun every time the IN(...) test is required. And if that sub-query contains 
> "random()" it might return a different result every time.
> 
> Your words suggest that you are hoping it will be run once for each different 
> value of "da.area", with different results each time. But it will not.

Ah yes, this makes complete sense now, thanks. I was going off a stack overflow 
post as to how to emulate outer apply in sqlite3. I didn't really think through 
what I was writing.

In my case, I only need a different ordering each time I create the database, 
as opposed to each time the query is run. So, I can remove the random() from 
the subquery, and instead create a new table populated by a trigger mapping 
product to a random number and order by that in the subquery instead.

I would be interested if there is a solution for sqlite 3.22 (i.e. no window 
functions) where it can be done so that the query gives a possibly different 
result each time it is executed.

Thanks,
Kevin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] picking random subset of rows

[Kevin Martin]

Hi,

I am trying to use a correlated subquery with an 'order by random() limit 2' to 
pick upto two random rows for each value in the outer query. I am not sure if I 
am doing this correctly, but the number of rows I am getting seems to vary 
randomly which doesn't make sense to me. If i replace the order by random() 
with order by product I always get the expected number of rows. I have tried to 
create a simplified version of the code below to replicate the issue.

I am experiencing the problem on sqlite 3.22.0, but I have tried on 
sqliteonline.com which I think is using 3.27.2 and am seeing similar results.

Thanks,
Kevin

---

create table if not exists test_productattribs (product text primary key, attr, 
val);   


insert or ignore into test_productattribs values

  ('1', 'area', 'a'),   
   
  ('2', 'area', 'b'),   

  ('3', 'area', 'a'),   
   
  ('4', 'area', 'a')

;   
   


--In the real query, this is done inside the with, but it does not seem 
relevant
--to the issue. 
   
create table if not exists  

  test_productarea  
   
as select   

  product,  
   
  val as area   

from
   
  test_productattribs   

where   
   
  attr='area'   
   
;   



--I have two areas, 'a' and 'b'. I limit to two random products from each area  

--As area 'b' only has one product, I always expect to get 3 rows, 1 for area 
'b', and 2 for   
--area 'a'. 

with
   
  dareas as (select distinct

area
   
  from  

test_productarea)   

  select
  

[sqlite] weekday time modifier

[Kevin Martin]

Hi,

Not sure if this is me misreading it, but the description of the weekday 
modifier in the documentation seems a bit ambiguous.

It says:

> The "weekday" modifier advances the date forward to the next date where the 
> weekday number is N. Sunday is 0, Monday is 1, and so forth.

It is not clear what happens when the date before the modifier is already the 
correct weekday. I interpreted this as it would advance by a full week, but it 
does't, it leaves the date untouched:

SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select strftime('%Y-%m-%d', '2018-09-23', 'weekday 0');
2018-09-23
sqlite> 

I seem the same behaviour in 3.24.

Thanks,
Kev
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Accessing sqlite3Apis pointer when loading sqlite3 dll/so.

[Kevin Martin]

Hi,

I have a shared library that internally uses a statically linked sqlite for a 
few different internal tasks. Amongst these there is some code that provides a 
few virtual tables. I would like to extend the interface of the library so that 
as well as it’s normal interface, it can be accessed as an sqlite extension 
through some of these virtual tables. This will allow my library to be used in 
two ways:

a) Through its normal C interface.
b) From an sqlite3 extension loadable from the sqlite3 client app, or the R or 
python interfaces to sqlite3.

The problem is how to build the internal code that accesses sqlite3, including 
the virtual table code that I also want to make available through the 
extension. For the virtual table code I think I would need to build two copies, 
one with SQLITE_CORE defined (to be used internally), and the other without (to 
be used in case b). Doing this, in case b, I would suffer the problems I have 
read about where there would be two copies of the static global that implements 
the posix locking workaround. Although in my use case it is almost certain this 
would not cause a problem (I can’t envisage a use case where both my statically 
linked sqlite3 and the one being used at the interface layer would open the 
same db). It still feels like an ugly solution.

My thought instead is to build all of sqlite code using sqlite3ext.h and 
without SQLITE_CORE defined, and in case a, dynamically load an sqlite3 
library. However i need to get access to the sqlite3_api_routines pointer 
within whichever sqlite3 library is being used. My question is how to do this?

In case b, the pointer is passed when the extension is first loaded, and I can 
pass it through to the rest of my library. For case a I’m struggling a bit. The 
best I can figure is:

(Assume I have called dlopen/LoadLibrary and can access symbols with 
dlsym/GetProcAddress)

1. Set up a fake extension function whose purpose is to capture the sqlite3Apis 
pointer passed to xInit within sqlite3AutoLoadExtensions.
2. Use dlsym/GetProcAddress to find sqlite3_auto_extension, 
sqlite3_cancel_auto_extension, sqlite3_open_v2, sqlite3_close.
3. Register my extension with sqlite3_auto extension.
4. Open an in memory database to load the extension and capture the 
sqlite3_api_routines pointer.
5. Close the database.
6. Cancel the auto extension.

My questions are:

1) Am I missing anything, i.e. does this work.
2) Will it be guaranteed in the future that the sqlite3_api_routines pointer 
will be passed to the extensions loaded by sqlite3_auto_extension.
3) Is there a better way to do this as it feels a little hacky.

Thanks,
Kevin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Application with 'grid' form for entering table data wanted

[Kevin Martin]

On 3 Jun 2014, at 15:07, c...@isbd.net wrote:

> I'm looking for an application (or *simple* development framework)
> which will provide me with an easily accessible grid form for entering
> data into a table.

I don't know anything about coding a web program, so all this advice related 
purely to a desktop application

It's possibly not what you're looking for, as it's a console application, but I 
think the spreadsheet calculator, sc, satisfies most of your requirements. The 
file format is very simple, so the plumbing to get data out of the table and 
back in should be fairly simple. If you need a quick solution that's _ok_ then 
I'd consider it. If you want a nice solution, then it will take the time to 
code something as someone else already pointed out.

If you are competent with Gtk, someone has kindly created a spreadsheet like 
widget called GtkSheet, embedding this in your won Gtk application shouldn't be 
too difficult.

Thanks,
Kev
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[Kevin Martin]

Are you forming your query with sprintf? It may be worth printing the query you 
are preparing, to make sure it says what you think it is.

Thanks,
Kev

Sent from my iPhone

> On 3 Jun 2014, at 14:53, Micka  wrote:
> 
> Hi,
> 
> I'm having trouble with the percent character .
> 
> 
> By example in my table I have :
> 
> id name
> 1 micka
> 2 mickael
> 
> I would like to do that :
> 
> Select * from table name where name LIKE '%micka%'
> 
> with my linux c program, the result is 0
> 
> but with the sqlite3 command program it works 
> 
> I also tested this :
> 
> Select * from table name where name LIKE 'micka%'
> 
> and this time, it works in my linux c program ...
> 
> Why ?
> ___
> 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] calculation of a fraction stored in a text column

[Kevin Martin]

On 20 Feb 2014, at 12:54, Patrick Proniewski  wrote:

> My problem is that my database holds too many different values for 
> ExposureTime, so the resulting plot is unreadable. I want to be able to "bin" 
> those values to create a proper histogram. It's not possible to "bin" string 
> values, because SQLite has no idea that "1/60" has nothing to do near 
> "1/6000".

Are you able to use an extension? A custom collation on the ExposureTime column 
seems pretty simple (although not thought about it in detail). You should then 
be able to bin the values as they are.

Thanks,
Kevin  


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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

[Kevin Martin]

On 10 Feb 2014, at 17:57, Richard Hipp  wrote:

> I think I know how to detect a double-click launch versus a command-line
> launch on windows.  But I don't know how to do this, or even if it is
> possible to do, on Mac or Linux.  Anybody have any ideas?

For me, It's not so much how it is launched that matters, but whether it is 
running interactively. I would only want the behaviour altered if stdin is a 
terminal. What about something as simple as

isatty(STDIN_FILENO);

Thanks,
Kevin

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

Re: [sqlite] struggling with a query

[Kevin Martin]

On 8 Feb 2014, at 10:03, Stephan Beal  wrote:

> i am trying like mad to, but can't seem formulate a query with 2 version
> number inputs (1 and 2 in this case) and creates a result set with these
> columns:
> 
> - name. must include all names across both versions
> - status: -1 if in version 1 but not v2, 0 if in both, 1 if in v2 but not
> v1.

Only tried on your example dataset, but try this:

CREATE VIEW answer as
select 
name,
count(v2) - count(v1) as result
from
(select 
a.name as name,
b.name as v1,
c.name as v2
from
v as a
left join
(select name from v where vid = 1) as b
on a.name = b.name
left join
(select name from v where vid = 2) as c
on a.name = c.name)
group by
name
order by
result asc;

Thanks,
Kev
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Send Mail from sqlite

[Kevin Martin]

On 7 Feb 2014, at 09:59, Vairamuthu  wrote:

> Thanks for your response, it will be great help if you can get me some
> sample code or algorithms, on that.


I'm assuming you're using SQLite embedded within another application on 
something unix like.

Off the top of my head, here's how I would do it  - there may be an extension 
like this lying around somewhere already?

Create an SQLite3 extension that implements the following functions

sqlnotify_open(, , )
sqlnotify_close()
sqlnotify_send(, , , …)

The open function will connect to the specified socket, the close function will 
obviously close the connection.

When sqlnotify_send is called from your trigger, it will insert its arguments 
into the associated template and then send the resultant string to the socket.

Implement a daemon in your favourite language with a mail() function to send 
the mails. I wouldn't recommend that you send a mail for each notify, maybe 
save them up and send every 5 minutes or so.

PERL/Python are probably good choices for the daemon. As for writing the 
extension, it depends how you're using SQLite, but you're probably going to end 
up in C. I recommend chapter 9 of the book "Using Sqlite"

Thanks,
Kevin


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

Re: [sqlite] Virtual Table xRowid

[Kevin Martin]

On 5 Feb 2014, at 18:40, Dan Kennedy  wrote:

> But is it the case that your virtual table doesn't handle
> anything other than full scans of the entire table contents? If
> so, it's probably not a problem if rowids are inconsistent.

Thanks for the heads up on the 'or' case, I didn't know that. I don't use 
xBestIndex, so the table only supports full scans.

I think I'll try it with returning SQLITE_ERROR in xRowid, because I'd rather 
see the error than have strange results happen silently, I'll, watch what 
happens. One of the columns in the underlying table/view is unique, so one 
solution is to order by that column when doing the internal select, and then 
the auto generated row ids will be consistent, but that just feels like it adds 
a lot of calculation I don't want to do, and won't be necessary most of the 
time.

Thanks,
Kevin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Table xRowid

[Kevin Martin]

Hi,

My questions are basically:

1) What causes sqlite3 to call xRowid
2) If I don't want to update my virtual table, or do a select ROWID, can I just 
use an incremental counter, increased on every call to xNext (bearing in mind 
the order of my data is not guaranteed, so this won't necessarily return the 
same rowid for the same row on separate calls to xFilter), or even better just 
return SQLITE_ERROR?

The problem itself is detailed below.

Thanks,
Kevin

---

I am creating virtual table that unpacks the contents of another table/view.

As a simple example, consider the following:

create table t(week, performance_csv)

Where each row contains a csv file for the performance of the given week, and 
each csv file has two columns, person and percentage.

I want to create a virtual table as follows:

create virtual table t2 using unpack_performance(t)

This table will declare the table as follows

create table t2(week, person, percentage)

and it will iterate over each row of each csv in the table t.

Implementing xRowid is easy because I can base it off the ROWID of t and number 
of lines in the csv file.

However, the problem comes when t is a view, I've only just discovered that 
views don't have ROWIDs!

I can quite happily just increment a simple counter, but the view isn't 
necessarily ordered, so the rowid won't match on multiple filters.

In my actual application the view is the union of a few selects, so actually 
including the rowid in the view is going to be a complete pain, and is not an 
option I want to consider.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Logic error or missing database

[Kevin Martin]

On 5 Apr 2013, at 14:12, Rob Collie wrote:

> I'm pretty much just including sqlite3.h, sqlite3ext.h, sqlite3.c in a C++
> project and compiling it as a static lib.

I don't really know anything about Windows, but this looks a bit different to 
how I do it on Linux. I think you should only include one of sqlite.h and 
sqlite3ext.h, depending whether the code is being built as part of an sqlite 
extension. I'm not sure if including both may have some strange effects.

Thanks,
Kevin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Joining tow tables with subset of columns from one

[Kevin Martin]

On 13 Mar 2013, at 17:44, Paul Sanderson wrote:

> I want to join two table by doing a select in the form
> 
> select col1, col2, col3 from table1 as t1, * from table2 as t2 where
> t1.col1 = t2.x

Are you trying to do:

select t1.col1, t1.col2, t1.col3, t2.* from table1 as t1 join table2 as t2 on 
t1.col1=t2.x

If not, I'm not sure what you want to achieve.

Thanks,
Kevin

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

Re: [sqlite] Current Time in WHEN clause.

[Kevin Martin]

For now I've decided to play safe and I've added a added an 'ok' column to tell 
me if I'm within 5s of an update.

> CREATE VIEW near_update as select count(*) as n from aliases where 
> abs(strftime('%s', 'now') - validFrom) < 5;
> CREATE VIEW alias_info as select a.name, s.url, s.type, nu.n == 0 as ok from 
> latest_aliases as a join services as s on a.assignedTo = s.name join 
> near_update as nu;

In the scripts, I just need to check the ok column, and if it's 0, then return 
a temporary error to the client.

Probably not the nicest solution, but it keeps complicated sql out of the 
scripts, and makes sure all the services reported back are always in sync - 
which are my main aims. Plus, there are only likely to be a few updates a 
month, most in the middle of the night, so the occasional temporary error won't 
be a problem.

On 22 Jul 2012, at 21:54, Keith Medcalf wrote:

> if they are tiny, then it matters not, if they will always be tiny


Yeah, we're not anticipating many updates, and eventually, I intend writing a 
cron script to clear the old ones out.

Thanks,
Kevin


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

Re: [sqlite] Current Time in WHEN clause.

[Kevin Martin]

On 22 Jul 2012, at 18:35, Keith Medcalf wrote:

Thanks for that. I can't say I understand much of that output at the moment. 
I'm just reading http://www.sqlite.org/eqp.html to try and figure it out.

Is the behaviour it will only run once guaranteed by any standard, or likely to 
change in future/previous versions of sqlite?

Thanks,
Kevin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sql statement CREATE TABLE

[Kevin Martin]

On 22 Jul 2012, at 16:15, Arbol One wrote:

> I am getting a run time error that reads

> Library routine out of sequence

http://sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOutOfSequence

My guess is #1. What's the return value from the function you're calling to 
open the db?

Thanks,
Kevin 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Current Time in WHEN clause.

[Kevin Martin]

On 22 Jul 2012, at 16:37, Pavel Ivanov wrote:

> You can execute "select strftime('%s', 'now')" first and then put its
> result into your query. 

It seems as though the opinion is that it may execute multiple times.

> But that won't work with view, of course.

I'd rather keep the alias_info view as it is because it's simple - I prefer DB 
logic to be in the DB, rather than the PHP script.

I'm toying with the idea of creating a temporary table with the current time in 
and selecting from that in the latest_alias subquery, but would prefer 
better/cleaner options.

Thanks,
Kevin Martin

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

Re: [sqlite] Current Time in WHEN clause.

[Kevin Martin]

On 22 Jul 2012, at 17:06, Keith Medcalf wrote:

> (select name, max(validfrom) as mvf from aliases where validFrom <= (select 
> strftime('%s', 'now')) group by name)

I don't think that solves the problem because each time the group by query is 
executed, your subquery will be executed again.

Thanks,
Kev
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Current Time in WHEN clause.

[Kevin Martin]

Hi,

I have the following schema:

> CREATE TABLE aliases (name text not null, assignedTo text, validFrom integer 
> not null);
> CREATE TABLE services (name text primary key, url text not null, type text 
> not null);
> CREATE VIEW latest_aliases as select a.name, a.assignedTo, a.validFrom from 
> aliases as a join (select name, max(validfrom) as mvf from aliases where 
> validFrom <= strftime('%s', 'now') group by name) as b on a.name=b.name and 
> a.validFrom=b.mvf;
> CREATE VIEW alias_info as select a.name, s.url, s.type from latest_aliases as 
> a join services as s on a.assignedTo = s.name;

The aliases table maps names to services at different times. So for example 
with the following data:

> sqlite> select * from services;
> svc1|http://somewhere|type1
> svc2|http://somewhere.else|type1

> sqlite> select *,datetime(validFrom, 'unixepoch') from aliases;
> env1|svc1|1342967110|2012-07-22 14:25:10
> env1|svc2|1342967185|2012-07-22 14:26:25

I want env1 to be mapped to svc1 after 14:25:10, until 14:26:25 after which 
point I want it to be mapped to svc2.

This is done with the latest_aliases view, alias_info just joins latest_aliases 
to the services table to get the connection info.

However, I'm quite concerned about executing strftime('%s', 'now') inside the 
subquery, specifically does it execute multiple times? I don't want this to 
happen because it could cause a rare bug when the list of services returned is 
partly pre and partly post an update.

I'm trying to convince myself that the subquery in latest_aliases only executes 
once, and also that alias_info only runs latest_aliases once. However, I'm not 
doing very well at convincing myself. Can someone confirm this is true, or 
suggest a nice solution to get the current time function out of the when clause.

Thanks,
Kevin Martin.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database busy error

[Kevin Martin]

> If I have a pool of external (c++) progs, all writing into the same sqlit3 db,
> how do i handle this correctly ?


I think it depends on your applications.

We use sqlite for IPC and data sharing within processes on the same machine. 
I'm not suggesting it for you, but the following works for us:

o We do all our modifications within exclusive transactions (by this I mean we 
do any selects, calculations, updates, inserts which are required for the 
modification within a single exclusive transaction) 

o We work under the assumption that the db will become unblocked eventually.

o We have a few attempts to begin exclusive (5 I think) with random pauses 
between them. If we still haven't locked it after that we tell nagios of this 
fact which alerts us something is wrong then we enter an infinite loop 
attempting to get the lock.

The only time we've ever had nagios alerts was due to filesystem/SAN issues. 
Under normal working we've never had a problem. There are probably around 5 
small writes/second to the database.

Thanks,
Kev

On 2 Jul 2012, at 14:13, deltagam...@gmx.net wrote:

> Now, sometimes I get db busy error (5)
> ___
> 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] 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) from x where time 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 I create a view which gives me:
> 
> unix_time  val  prev_unix_time
> --++--
> 1325376000|val1|
> 1325376300|val2|1325376000
> 1325376600|val3|1325376300
> 1325376900|val4|1325376600
> 
> Something like this will not work:
> 
> create view new as select unix_time, val, (select unix_time from old where 
> new.unix_time < old.unix_time order by unix_time desc limit 1) as 
> prev_unix_time from old;
> 
> as I can't refer to new.unix_time inside the view that defines "new".
> 
> The idea is, if this is possible, then I should be able to get my
> weighted average by something like this (not verified, but you get the
> idea):
> 
> select sum(val * (unix_time - prev_unix_time)) / sum(unix_time - 
> prev_unix_time) from new;
> 
> -- 
> Steinar
> ___
> 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] Strange behaviour with fork/close.

[Kevin Martin]

On 14 Aug 2011, at 12:18, Kevin Martin wrote:

> I am however very confused If
> anyone has the time to read this and give a hint as to what might be
> going on, it would be greatly appreciated.

Why do you always figure things out after you have posted them to the  
mailing list?

The problem was I had closed stdin, so when I opened the database in  
the execed child it was being opened on file descriptor 0. Then, when  
I did close(STDIN_FILENO) I inadvertently closed the database. Hence  
why the query was failing, and why closing/opening it fixed the problem.

Kev

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

[sqlite] Strange behaviour with fork/close.

[Kevin Martin]

Hello,

I have read the FAQ, and know that you can't fork with an open  
database connection. I'm not doing that. I am however very confused If  
anyone has the time to read this and give a hint as to what might be  
going on, it would be greatly appreciated.

Thanks,
Kevin Martin

---

Firstly, this is running on mac os x 10.5.8 with sqlite 3.7.7.1  
compiled directly into all binaries.

Here is the situation:

parent process
   opens database
   does stuff
   closes database (sqlite3_close returns SQLITE_OK)
   creates pipe
   forks
   closes write end of pipe
   reopens database
   reads data from pipe until it closes
   continues on (all database queries issued here work)

child (relevant code)
 global_ios->notify_fork(boost::asio::io_service::fork_child);

 signal(SIGHUP, SIG_IGN);
 setsid();

 //close the socket, pipe, and stdin
 global_currentSocket.reset();
 close(pipefd[0]);
 close(STDIN_FILENO);

 //send stdout and stderr to the pipe
 dup2(pipefd[1], STDERR_FILENO);
 dup2(pipefd[1], STDOUT_FILENO);
 close(pipefd[1]);

 execl(arguments)

Now, in the execed child the database is opened and I can run queries  
and everything is fine until I do
 close(STDIN_FILENO);
 close(STDOUT_FILENO);
 close(STDERR_FILENO);
 open("/dev/null",O_RDONLY);
 open("/dev/null",O_WRONLY);
 open("/dev/null",O_WRONLY);

After this point, any sqlite3_step() calls return SQLITE_ERROR.  
Although I have forgotten the exact details, there was some very  
similar code that consistently produced SQLITE_IOERR instead.

If I close the database before I close stdout/stdin/stderr and reopen  
it afterwards there is no problem and everything works as expected.

The thing I find really confusing is if I run the execed child  
directly from the shell instead of from the parent, there is no  
problem and everything works fine. Which makes me think I am doing  
something hideously wrong whilst forking.

Any insight appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help! -- SQlite database on server

[Kevin Martin]

On 4 Aug 2011, at 18:15, Vinoth raj wrote:

> I have been using SQlite database since three years. Suddenly I have a
> requirement for client/server support for my project.
> So, the requirement is to save sqlite database on a server from a C++
> application.

We use sqlite in a client/server situation. However, the main focus of  
the server is data analysis, it just happens to store the data/results  
in sqlite database. The client contains minimal code and just allows  
us to submit data and export analysis results.

The set up is essentially:

Server Side:
Database is a file on server
RPCServer runs on server on localhost:35790
Server runs sshd.

Client Side
Client runs ssh and establishes tunnel to 35790 on the server
RPCClient connects to localhost:35790

The RPC Client/Server are built with boost iostreams and boost  
serialization. However, if you take this approach it may be better to  
use something like Ice (http://zeroc.com/) if the license suits you.

Kevin Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exclusive locking mode not working as expected?

[Kevin Martin]

On 29 Jul 2011, at 07:49, Jonathan Little wrote:

Hello,

I am no expert in sqlite, but I do use it one of our projects. My  
interpretation of the documentation is slightly different to yours -  
see below

> Specifically, that page says that locking_mode = EXCLUSIVE is useful  
> if "The application wants to prevent other processes from accessing  
> the database file."

The documentation on that page also says:

``This pragma sets or queries the database connection locking-mode.''

> However, if I start up a SQLite shell, set the locking_mode to  
> exclusive, and perform an insert or other write operation (such that  
> the exclusive lock should be acquired and not released), I can still  
> read the file from other processes (e.g. I can copy it using a file  
> manager UI).

Are these processes making a connection to the database? If no, why  
would you expect them to obey locking_mode?

> Am I missing something here or if I want to prevent this, is my only  
> option to modify our build of SQLite to open the file for exclusive  
> access?

That is certainly not your only option. Personally, I would create a  
program that makes a database connection (which will take account of  
the locks), and copies everything relevant into another database.  
After the user has run this to create their copy, they can then do  
what they want with it.

Kevin Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Kevin Martin]

> actually I don't know how to get my rules yet, but let's assume the  
> rules
> exist and we can get it from a function.
> get_forbidden_ids()

I've never done it, so don't know whether it's possible, but you may  
be able to create a virtual table on top of the real table which calls  
get_forbidden_ids() and skips the appropriate rows. I think it could  
be  very difficult to handle the situation where the ids change mid  
query though.

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

Re: [sqlite] Write locking - subquery

[Kevin Martin]

On 16 Jul 2011, at 21:30, Kevin Martin wrote:
> insert into x values ('abc', -1);
> update x set pos = 1+max(0,(select max(pos) from x));

Oops, deliberate mistake there. As I'm sure you all realise that  
should be

update x set pos = 1+max(0,(select max(pos) from x)) where name='abc';

Kevin


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

[sqlite] Write locking - subquery

[Kevin Martin]

Hello,

I'm just looking for some clarification of the documentation if that's  
ok. I did a quick search of the mailing list but couldn't find  
anything relevant.

If I run an update query which has a subquery, will the database be  
locked before the subquery is run.

I'm thinking something like:

create table x(name text, pos int);
insert into x values ('abc', -1);
update x set pos = 1+max(0,(select max(pos) from x));

If two processes run this at once (with different values for name), am  
I right in thinking the lock will occur before the subquery runs,  
hence it is impossible for the subquery to return -1 in both processes.

Thanks,
Kevin Martin.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users