[sqlite] error with authorizer, onecolumn and TCL
The following sequence gives an incorrect result value:
(ramsan) package require sqlite3
3.6.22
(ramsan) 2 % sqlite3 db :memory:
(ramsan) 3 % db authorizer myauth
(ramsan) 4 % proc myauth { cmd args } {
return SQLITE_OK
}
(ramsan) 6 % db eval { create table t(a,b) }
(ramsan) 7 % db onecolumn { select a from t }
SQLITE_OK
The error is probably due to the fact that the onecolumn TCL code,
in some cases (if there are no results), does not store a void string
as result. Then, a previous not valid TCL result (SQLITE_OK) is
returned as result of the onecolumn method.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what extension do you recommend
You should consider the extension for, at least, three situations: - Anti virus can check files differently depending on the extension - Backup programs can also react on this - Windows takes it into account when indexing files Compass Ing. y Sistemas Dr. Ramon Ribo http://www.compassis.com[EMAIL PROTECTED] c/ Tuset, 8 7-2 tel. +34 93 218 19 89 08006 Barcelona, Spain fax. +34 93 396 97 46 Daniel Önnerby escribió: Well... From the lack of responses I gather that you actually don't give a damn what file extension I use :) I think this is fare enough and maybe a SQLite database shouldn't be tied to a single file extension. Anyway.. Thanks for listening and thanks for the best database ever! Daniel Önnerby wrote: Hi all! I have developed a small windows application that use a SQLite database as it's main format. The file is NOT a kind of document, it's more of a settings-file stored in the users "Application data"-directory and the extension will not be associated with the application. I know I can use any kind of extension on this file, but what file-extension is the most common for SQLite-files, .db, .db3, .SQLite? What file-extensions do the SQLite management tools associate? What do you recommend? Best regards! Daniel - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Dennis,
I cannot agree here. Just imagine that the user decision is based on the
imported data.
Of course, you can read the data of the file, store in temporal structures
on memory, ask
the user and then, enter the data into the database. But the advantage of
using sqlite as
storage mechanism, as drh pointed out, is to use it as file format, an
avoid creating
intermediate data structures.
My opinion is that it is completely possible to live without nested
transactions, in
fact we all do now and survive, but they are very convenient to solve a
full range of
problems. You are right that a different range of problems can be easily
solved by just
maintaining a counter of transactions.
Written in an abstract form, you base your reasoning on the fact that
you know, before
beginning the first transaction, all the future steps that you are going
to perform and
the order of performing them. This is not always true, as some decisions
can be based on
external input, like an interative user, data coming from an external
source, other types
of events. Also, the decision is often taken based on the data itself, so
it is more
convenient to put the data in the database, operate with it and decide
after operation.
Finally, I am not advocating to include nested transactions into sqlite
or not, as this
is a problem to be solved by the people who is developing the library. But
it is important
to include all the facts in the discussion.
Best regards,
--
Compass Ing. y Sistemas Dr. Ramon Ribo
http://www.compassis.com[EMAIL PROTECTED]
c/ Tuset, 8 7-2 tel. +34 93 218 19 89
08006 Barcelona, Spain fax. +34 93 396 97 46
En Thu, 12 Apr 2007 00:37:21 +0200, Dennis Cote <[EMAIL PROTECTED]>
escribió:
Ramon Ribó wrote:
Imagine one application that can import data from a file. You want
that, in case of computer crash, either all the data of the file is
imported or none. At the same time, you want the user to manually
accept or reject every section of the file.
This example can be modelled in a very natural way with a transaction
covering the full file import and a nested transaction covering every
section.
Ramon,
I don't see that where nested transactions are needed for this example.
You seem to be suggesting a loop reading each file section and writing
it into the database in a nested transaction and then rolling back a
nested transaction if the user says they want to skip that section.
begin
for each section in file {
read section
begin nested
insert section
if promp_user(section) == keep
commit nested else
rollback nested
}
commit
The same thing can be done far more efficiently by prompting the user
first and only inserting the sections the user wants to keep.
begin
for each section in file {
read section
if promp_user(section) == keep
insert section
}
commit
If the program completes all users selected sections are inserted into
the database atomically. If the program crashes the entire file will be
deleted when the incomplete transaction is rolled back. Similarly if an
I/O error occur when reading the file or a disk full condition happens
when inserting a section, those and any other errors would cause the
transaction to be rolled back so that none of the file sections are
inserted. I want to insert all of the user selected sections or none of
them.
Nested transaction only create more work and make the application more
complicated.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Compass Ing. y Sistemas Dr. Ramon Ribo
http://www.compassis.com[EMAIL PROTECTED]
c/ Tuset, 8 7-2 tel. +34 93 218 19 89
08006 Barcelona, Spain fax. +34 93 396 97 46
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
Dennis, There are many examples that can be solved better with nested transactions. One of them could be the following: Imagine one application that can import data from a file. You want that, in case of computer crash, either all the data of the file is imported or none. At the same time, you want the user to manually accept or reject every section of the file. This example can be modelled in a very natural way with a transaction covering the full file import and a nested transaction covering every section. I am sure that it is easy to find many examples more where nested transactions can be the most natural way to solve a problem. -- Compass Ing. y Sistemas Dr. Ramon Ribo http://www.compassis.com[EMAIL PROTECTED] c/ Tuset, 8 7-2 tel. +34 93 218 19 89 08006 Barcelona, Spain fax. +34 93 396 97 46 En Wed, 11 Apr 2007 23:33:52 +0200, Dennis Cote <[EMAIL PROTECTED]> escribió: Darren Duncan wrote: While it is true in some cases that an application can be written to know in advance whether certain SQL statements need to be run or not, there are other cases where it can only easily know after having tried it. One type of situation that stands out the most to me is if you have state constraints defined (in some way) on the database for which it is only really feasible to calculate the constraint definition after DML has occurred, because you want SQLite to do the calculation itself on the post-DML-state and it is technically simpler that way; if the constraint fails, we would want to be able to just rollback the DML that caused the state to break, but not the other valid stuff before that, since we could have more stuff after this attempt that needs to be atomic with stuff before the attempt. Well, the key thing in that example, and many situations, is that the child transaction is doing something that we may or may not want to rollback and we won't know until after it is tried. Darren, You have lost me here. If this transaction is considered successful without executing the middle step (which is the same as executing it and then undoing that execution) then that step does not belong in this transaction. Instead of begin step 1 savepoint after_1 optional step 2 if error rollback to savepoint after_1 step 3 commit You can do begin step 1 step 3 commit begin optional step 2 if error rollback else commit This said, I can also see situations where there is an unconditional child rollback, and that is where we want SQLite itself to perform a querying or calculating task using a temporarily changed database state as input. We want the result of the query, but not the changed state that went into its input. But there is other changed state before that which does need to go into the input, and it needs to persist, and be atomic with other state changes done after the calculation. Now, you could say make the application do that work, but the fact is that certain kinds of data processing are more efficiently done by a DBMS itself. This *is* an example of an application where a nested transaction or a savepoint could be useful. However there is a fairly simple workaround that gets the same result without a nested transaction. Instead of: begin step 1 savepoint after_1 temp step 2 var = query current state rollback to savepoint after_1 step 3 using var commit or begin step 1 begin nested temp step 2 var = query current state rollback nested step 3 using var commit You could do: begin step 1 temp step 2 var = query current state rollback begin step 1 step 3 using var commit And in a multiple access system you could use a variation using chained transactions to ensure that no other process changes the database state between the two transactions. begin step 1 temp step 2 var = query current state rollback and chain step 1 step 3 using var commit The cost of this approach is repeating the work done in step 1. Another approach that could be used in some, but definitely not all, cases is to use additional SQL statements to undo or invert the effects of step 2. begin step 1 temp step 2 var = query current state inverse step 2 step 3 using var commit All this said, I look forward to rhurst's specific problem being explained, so it is easier for us to see whether child transaction support would help *that* problem. Likewise. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - -- Compass Ing. y Sistemas Dr. Ramon Ribo http://www.com
[sqlite] New conflict clause: update
Hello, I was thinking on a new option that could be useful for sqlite. A new conflict clause called "update" so that the following SQL command: insert or update into table1 (a,b) values (1,2) was equivalent to update table1 set a=1,b=2 where ?unique-key-constraint? when a unique key contraint is hit. I think that it could have advantages over the "replace" conflict clause for the very usual case that a new record must be updated or inserted if it does not exist. What is your opinion on this? -- Compass Ing. y Sistemas Dr. Ramon Ribo http://www.compassis.com[EMAIL PROTECTED] c/ Tuset, 8 7-2 tel. +34 93 218 19 89 08006 Barcelona, Spain fax. +34 93 396 97 46 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] aggregate funtion in the TCL binding
Hello,
It also looks to my that the interface should be something as as simple as:
db aggregate_function step_func final_func
> But it seemed to me that all it should take is to create a normal TCL
> function to be called for each row selected (say, to compute the sums
> and squares and products), with some initialization code run before
> the select, and some more code to run when the select is done (say, to
> compute the correlation coeffiecient between two columns).
I would like to expain the type of problem that I am trying to solve in order to
recieve comments and opinions about its feasibility.
I work in the field of computer simulation for engineering, notably Finite
Element analysis. These types of problems have traditionally been solved
programming in either FORTRAN or C/C++ due to the big amount of
data to deal with and that they are very computer intensive. One run can
be of minutes, hours or even days.
The approach has some disadvantages. For example, the amount of developing
time just dedicated to deal with data structs is huge and also, any change
to the logic of the program can require a big amount of programming.
I want to experiment with a new way of dealing with the problem, by using
sqlite and a mixure of C++ and TCL.
Instead of just fetch data from the database and operate on it, I want to try
a different approach: make the process database driven.
So, instead of:
loop on "select "
do my algorithm
end loop
I want to try something like:
db eval {
select add_elem_k_triangle(e.num,
e.euler1,e.euler2,e.euler3,
m.units,m.thickness,m.specific_weight,m.E,m.nu,
n1.num*6,n2.num*6,n3.num*6,
n1.x,n1.y,n1.z,n2.x,n2.y,n2.z,n3.x,n3.y,n3.z)
from elements_triangles as e
join material_isotropic_shell as m on m.num = e.mat
join nodes as n1 on e.c1 = n1.num
join nodes as n2 on e.c2 = n2.num
join nodes as n3 on e.c3 = n3.num
}
where function "add_elem_k_triangle" is the one that make the
real algorithm work and can be implemented either in TCL or in C++
Currently "add_elem_k_triangle" is a normal function that operates on
external structures and has nothing to return for every element. I think
that in this case, it would be better that "add_elem_k_triangle" was an
aggregate function that returned something trivial, like the number of
elements that has operated with. In this way, A lot of void returns could
be avoided.
I understand that it is not simple to explain a problem like this in a few
lines but I hope that some "big picture" has been given.
> What version are you looking at? The latest Tcl bindings already
> do this.
I looked at version 3.2.1 and the code is:
static void tclSqlFunc(sqlite3_context *context, int argc, sqlite3_value**argv){
SqlFunc *p = sqlite3_user_data(context);
Tcl_DString cmd;
int i;
int rc;
Tcl_DStringInit(&cmd);
Tcl_DStringAppend(&cmd, p->zScript, -1);
for(i=0; iinterp, Tcl_DStringValue(&cmd));
if( rc ){
sqlite3_result_error(context, Tcl_GetStringResult(p->interp), -1);
}else{
sqlite3_result_text(context, Tcl_GetStringResult(p->interp), -1,
SQLITE_TRANSIENT);
}
}
So, it uses strings for the arguments.
Best regards,
Compass Ing. y Sistemas
Dr. Ramon Ribó
http://www.compassis.com <http://www.compassis.com/>
[EMAIL PROTECTED]
c/ Tuset, 8 7-2
tel. +34 93 218 19 89
08006 Barcelona, Spain
fax. +34 93 396 97 46
[sqlite] aggregate funtion in the TCL binding
Hello, Is there a way to define an aggregate function in the TCL binding? In case it is not there, would it be a nice addition for symmetry with the C-C++ interface? Also, I briefly reviewed the TCL binding C code and it looks like the "function" interface does not take advantage of the new features of sqlite version 3 with the integers and doubles, as it convert everything to one string. Are there any plans to change it? Regards, Compass Ing. y Sistemas Dr. Ramon Ribó http://www.compassis.com <http://www.compassis.com/> [EMAIL PROTECTED] c/ Tuset, 8 7-2 tel. +34 93 218 19 89 08006 Barcelona, Spain fax. +34 93 396 97 46
