[sqlite] SQLITE_USER_AUTHENTICATION
Thanks, It works now. On Mon, Mar 14, 2016 at 10:06 PM, syl leo wrote: > Hi, > In your c++ program, if you use directly sqlite.c and .h and not the > dll+lib, you should remove the sqlite3.lib in your link.(project properties > -> linker -> input) > No ? > Regards. > Sylvain. > > De : siddharth kotian > ? : sqlite-users at mailinglists.sqlite.org > Envoy? le : Lundi 14 mars 2016 13h39 > Objet : [sqlite] SQLITE_USER_AUTHENTICATION > > Hi, > > I have downloaded the SQLITE amalgamation folder along with the entire > source code. > > Followed the steps mentioned here > http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt > > Using the following command to build the sqlite3.exe:- > > cl -D_SQLITE_USER_AUTHENTICATION shell.c sqlite3.c userauth.c > -Fesqlite3.exe > > But i can still open the database with sqlite3_open_v2. No authentication > is required. > > Another problem is that I am trying to add a user to the sqlite_user table. > I have appended userauth.c to the sqlite3.c amalgamation and have provided > sqlite3userauth.h to the C++ program. > I am able to access sqlite_user_add method but it gives error lnk1181: > cannot find sqlite3.lib > > I am using VS2012, x64, Release Mode. > > Regards, > Siddharth > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] SQLITE_USER_AUTHENTICATION
Hi, I'm not a expert but i played few days ago with that.What i have seen is :?1?)- I open a database with?sqlite3_open(..)- create a table and add record -> no problem- close the base 2?)- I open the same database with?sqlite3_open(..)- Add a user with?sqlite3_user_add( user, password.)- Close the database 3?)- I open the same database with?sqlite3_open(..) - try to add a record on the table -> failed- use?sqlite3_user_authenticate(.user, password.)?- Add a record on the table -> OK and if i try to use a common tool to open the database from now, it failed (from the moment I create a user) Hope it help :o) Regards. Sylvain. De?: siddharth kotian ??: SQLite mailing list Envoy? le : Lundi 14 mars 2016 20h32 Objet?: Re: [sqlite] SQLITE_USER_AUTHENTICATION Hi, I had modified that for my convenience. I had changed it in the code also. I was under the impression to make it compatible for both 64 and 32 bit the underscore is required. I have tried it with and without the underscore. Both times it compiled successfully and all the operations worked fine except for the authentication. Regards, Siddharth On Tue, Mar 15, 2016 at 12:19 AM, Robert Weiss wrote: > Tthe documentation says to use the flag as > -DSQLITE_USER_AUTHENTICATION > > whereas in your message you said you used > -D_SQLITE_USER_AUTHENTICATION > > So unless cl ignores a leading underscore (seems unlikely), you're > defining something the code doesn't care about. > > >? ? On Monday, March 14, 2016 10:26 AM, siddharth kotian < > siddse7en at gmail.com> wrote: > > >? Thanks, It works now. > > > On Mon, Mar 14, 2016 at 10:06 PM, syl leo wrote: > > > Hi, > > In your c++ program, if you use directly sqlite.c and .h and not the > > dll+lib, you should remove the sqlite3.lib in your link.(project > properties > > -> linker -> input) > > No ? > > Regards. > > Sylvain. > > > >? ? ? De : siddharth kotian > >? ? : sqlite-users at mailinglists.sqlite.org > >? Envoy? le : Lundi 14 mars 2016 13h39 > >? Objet : [sqlite] SQLITE_USER_AUTHENTICATION > > > > Hi, > > > > I have downloaded the SQLITE amalgamation folder along with the entire > > source code. > > > > Followed the steps mentioned here > > http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt > > > > Using the following command to build the sqlite3.exe:- > > > > cl -D_SQLITE_USER_AUTHENTICATION shell.c sqlite3.c userauth.c > > -Fesqlite3.exe > > > > But i can still open the database with sqlite3_open_v2. No authentication > > is required. > > > > Another problem is that I am trying to add a user to the sqlite_user > table. > > I have appended userauth.c to the sqlite3.c amalgamation and have > provided > > sqlite3userauth.h to the C++ program. > > I am able to access sqlite_user_add method but it gives error lnk1181: > > cannot find sqlite3.lib > > > > I am using VS2012, x64, Release Mode. > > > > Regards, > > Siddharth > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_USER_AUTHENTICATION
Tthe documentation says to use the flag as? -DSQLITE_USER_AUTHENTICATION whereas in your message you said you used? -D_SQLITE_USER_AUTHENTICATION So unless cl ignores a leading underscore (seems unlikely), you're defining something the code doesn't care about. On Monday, March 14, 2016 10:26 AM, siddharth kotian wrote: Thanks, It works now. On Mon, Mar 14, 2016 at 10:06 PM, syl leo wrote: > Hi, > In your c++ program, if you use directly sqlite.c and .h and not the > dll+lib, you should remove the sqlite3.lib in your link.(project properties > -> linker -> input) > No ? > Regards. > Sylvain. > >? ? ? De : siddharth kotian >? ? : sqlite-users at mailinglists.sqlite.org >? Envoy? le : Lundi 14 mars 2016 13h39 >? Objet : [sqlite] SQLITE_USER_AUTHENTICATION > > Hi, > > I have downloaded the SQLITE amalgamation folder along with the entire > source code. > > Followed the steps mentioned here > http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt > > Using the following command to build the sqlite3.exe:- > > cl -D_SQLITE_USER_AUTHENTICATION shell.c sqlite3.c userauth.c > -Fesqlite3.exe > > But i can still open the database with sqlite3_open_v2. No authentication > is required. > > Another problem is that I am trying to add a user to the sqlite_user table. > I have appended userauth.c to the sqlite3.c amalgamation and have provided > sqlite3userauth.h to the C++ program. > I am able to access sqlite_user_add method but it gives error lnk1181: > cannot find sqlite3.lib > > I am using VS2012, x64, Release Mode. > > Regards, > Siddharth > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_USER_AUTHENTICATION
Hi, I have downloaded the SQLITE amalgamation folder along with the entire source code. Followed the steps mentioned here http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt Using the following command to build the sqlite3.exe:- cl -D_SQLITE_USER_AUTHENTICATION shell.c sqlite3.c userauth.c -Fesqlite3.exe But i can still open the database with sqlite3_open_v2. No authentication is required. Another problem is that I am trying to add a user to the sqlite_user table. I have appended userauth.c to the sqlite3.c amalgamation and have provided sqlite3userauth.h to the C++ program. I am able to access sqlite_user_add method but it gives error lnk1181: cannot find sqlite3.lib I am using VS2012, x64, Release Mode. Regards, Siddharth
[sqlite] CAST STRING => INTEGER
On Mon, 14 Mar 2016 13:25:09 +0100 Clemens Ladisch wrote: > > that ``SELECT CAST(col AS INTEGER);'' should return (not so) random > > result set, and receiving any INTEGER should mean that a source > > string could have trillion or more possible values? > > The documentation does not specify how the textual representation of > an out-of-range integer is to be converted, so anything SQLite does > can be considered correct. While I'm sympathetic to the GIGO notion that invalid input (a domain error, in this case) is bound to produce unpredictable output, you have to admit there's some inconsistency here. I would argue the inconsistency is harmful, and would be better thought of as a constraint that needs to be enforced. Consider: 1) sqlite> select cast('1' as float); cast('1' as float) -- 1.0e+20 2) sqlite> select cast('1' as int); cast('1' as int) 7766279631452241920 3) sqlite> select 1; 1 - 1.0e+20 4) sqlite> select 100 * 100 ; 100 * 100 - 1.0e+20 5) sqlite> select cast(100 * 100 as int) ; cast(100 * 100 as int) -- 9223372036854775807 6) sqlite> select cast(1 as text); cast(1 as text) --- 1.0e+20 7) sqlite> select cast(cast(1 as text) as int); cast(cast(1 as text) as int) 1 Before you say, "don't do that", keep in mind that sum() could produce the same integer-overflow result, as could string concatenation. If SQLite produces erroneous results, and places the burden of error detection -- errors that orginate in the data -- on the application. It's hard to justify any of the above. Integer arithmetic produces floating point if the result can't be represented as an integer? That's dubious enough. When forced to be an integer, it's 2^63. The application could reasonable expect that integer arithmetic results in an integer (as it normally does); when the double is returned instead, it will still call sqlite3_column_int with its implicit cast. No error, just MAX_INT. In 7, we convert an integer to text and back to int, and get 1. I understand why, mechanically. But I would say any form of T -> TEXT -> T conversion should be lossless, for any type T, given sufficient width of TEXT. The problems could be avoided by observing simple but strict rules: 1. Integer arithmetic produces integer result. Error on overflow. 2. CAST raises a range error if the value would be outside the range of the target type. 3. Implicit conversion by e.g. sqlite3_column_* adhere to the same rule as CAST. --jkl
[sqlite] SQLITE_USER_AUTHENTICATION
Hi, In your c++ program, if you use directly sqlite.c and .h and not the dll+lib, you should remove the sqlite3.lib in your link.(project properties -> linker -> input) No ? Regards. Sylvain. De?: siddharth kotian ??: sqlite-users at mailinglists.sqlite.org Envoy? le : Lundi 14 mars 2016 13h39 Objet?: [sqlite] SQLITE_USER_AUTHENTICATION Hi, I have downloaded the SQLITE amalgamation folder along with the entire source code. Followed the steps mentioned here http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt Using the following command to build the sqlite3.exe:- cl -D_SQLITE_USER_AUTHENTICATION shell.c sqlite3.c userauth.c -Fesqlite3.exe But i can still open the database with sqlite3_open_v2. No authentication is required. Another problem is that I am trying to add a user to the sqlite_user table. I have appended userauth.c to the sqlite3.c amalgamation and have provided sqlite3userauth.h to the C++ program. I am able to access sqlite_user_add method but it gives error lnk1181: cannot find sqlite3.lib I am using VS2012, x64, Release Mode. Regards, Siddharth ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CAST STRING => INTEGER
Hello, On 2016-03-14 13:25, Clemens Ladisch wrote: > Cezary H. Noweta wrote: >> Is your opinion > > Why would my opinion matter, as opposed to what SQLite actually does? Because, SQLite behaves in a bit strange manner, which is opposite to extreme carefulness of SQLite in other areas. I'm trying to know if this behavior (or not so determined behavior) is intentional. SQLite's public domain source could be an endorsement to the official documentation, but under condition that the code's behavior is intentional and bug free --- this is what I am trying to lay down. >> that ``SELECT CAST(col AS INTEGER);'' should return (not so) random >> result set, and receiving any INTEGER should mean that a source string >> could have trillion or more possible values? > > The documentation does not specify how the textual representation of an > out-of-range integer is to be converted, so anything SQLite does can be > considered correct. I do not agree. Due to ``lang_expr.html#castexpr'': ``When casting a TEXT value to INTEGER, the longest possible prefix of the value that can be interpreted as an integer number is extracted from the TEXT value and the remainder ignored.'' Now, the algorithm is: (1) calculate value MOD 2^64; (2) return MAX(the (1)'s value, LARGEST_INT64). It looks a bit random with bias into LARGEST_INT64 direction. 1. Why REALs out of range are saturated to range, while TEXT integers are not (which is not documented)? 2. Why REAL => INTEGER conversion is extremely accurate described together with a previous versions' behavior and a rationale for it, while TEXT => INTEGER conversion is not? Pro for your consideration of correctness would be using of a textual comparision with -SMALLEST_INT64 in ``sqlite3Atoi64()'', however the comparision is made under certain condition that a number was exactly 18 digits long, so it could be reduced to ``return u-1>LARGEST_INT64 ? 1 : !neg && u-1==LARGEST_INT64 ? 2 : 0;'', which, in turn, cancels any rationales for using a textual comparision. Maybe somebody should put ``if ( i > 19 * incr || u > LARGEST_INT64 ) {'' instead of ``if( u>LARGEST_INT64 ){'' in mentioned ``sqlite3Atoi64()''? It would be nice to know that source STRING (or at least the leading digits as mentioned in the doc) was perfect INTEGER after non-distinctive number (i.e. != SMALLEST_INT64, LARGEST_INT64, 0) has been received from CAST, would not it be? -- best regards Cezary H. Noweta
[sqlite] problem with CURRENT_TIMESTAMP
On Mon, Mar 14, 2016 at 12:02 PM, Clemens Ladisch wrote: > Then the obvious explanation is that there is a bug in your insertion > code. (Which you have not shown.) Shame on me, the insert was fine (null value against timestamp column) but later on an update of such column was issued as: UPDATE tt SET ts=( SELECT datetime( 'now','localtime') ) ... that produced the time glitch. Sorry for the noise. Luca
[sqlite] CAST STRING => INTEGER
Cezary H. Noweta wrote: > On 2016-03-14 12:03, Clemens Ladisch wrote: >> The documentation is quite clear that INTEGERs have 64 bits. So trying >> to use integers above 9223372036854775807 is something that is better >> not to be done. > > How could somebody use integers above 9223372036854775807 if they do not > exist? Not at all. This is why I wrote "trying". > Is your opinion Why would my opinion matter, as opposed to what SQLite actually does? > that ``SELECT CAST(col AS INTEGER);'' should return (not so) random > result set, and receiving any INTEGER should mean that a source string > could have trillion or more possible values? The documentation does not specify how the textual representation of an out-of-range integer is to be converted, so anything SQLite does can be considered correct. Regards, Clemens
[sqlite] CAST STRING => INTEGER
Hello, On 2016-03-14 12:03, Clemens Ladisch wrote: > The documentation is quite clear that INTEGERs have 64 bits. So trying > to use integers above 9223372036854775807 is something that is better > not to be done. How could somebody use integers above 9223372036854775807 if they do not exist? '1' is a TEXT, not INTEGER. I asked about CASTing to INTEGER. Is your opinion that ``SELECT CAST(col AS INTEGER);'' should return (not so) random result set, and receiving any INTEGER should mean that a source string could have trillion or more possible values? -- best regards Cezary H. Noweta
[sqlite] CAST STRING => INTEGER
On Mon, Mar 14, 2016 at 12:03 PM, Clemens Ladisch wrote: > Cezary H. Noweta wrote: > > Is there some more-or-less official list posted somewhere with things > which is better not to be done? > > The documentation is quite clear that INTEGERs have 64 bits. So trying > to use integers above 9223372036854775807 is something that is better > not to be done. > Also beware that some scripting languages only support 48 bits of integer precision, so 64 bits may or may not be usable in any given scripting language environment. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] CAST STRING => INTEGER
Cezary H. Noweta wrote: > Is there some more-or-less official list posted somewhere with things which > is better not to be done? The documentation is quite clear that INTEGERs have 64 bits. So trying to use integers above 9223372036854775807 is something that is better not to be done. Regards, Clemens
[sqlite] problem with CURRENT_TIMESTAMP
Luca Ferrari wrote: > On Mon, Mar 14, 2016 at 10:52 AM, Richard Hipp wrote: >> I think your system clock was set incorrectly when you did the initial >> insert. > > I'm running on localhost and did not touch the clock. Then the obvious explanation is that there is a bug in your insertion code. (Which you have not shown.) Regards, Clemens
[sqlite] problem with CURRENT_TIMESTAMP
On Mon, Mar 14, 2016 at 10:52 AM, Richard Hipp wrote: > I think your system clock was set incorrectly when you did the initial insert. I'm running on localhost and did not touch the clock. The following is the result of a just inserted row: SELECT strftime('%s', ts ), ts, strftime( '%s', CURRENT_TIMESTAMP ), CURRENT_TIMESTAMP FROM ... 1457956766|2016-03-14 11:59:26|1457953182|2016-03-14 10:59:42 Any idea?
[sqlite] CAST STRING => INTEGER
Hello, On 2016-03-14 11:15, Simon Slavin wrote: > I would never write code which depended on the answer to that > question. There are many too many ways for it to fail. Hmmm... The longest paragraph in ``CAST expressions'' section in the documentation is about STRING => INTEGER. Nice to know that this is a strange, curious technique. Is there some more-or-less official list posted somewhere with things which is better not to be done? -- best regards Cezary H. Noweta
[sqlite] CAST STRING => INTEGER
Hello, What ``SELECT CAST('1' AS INTEGER);'' should give? (String of one and 20 zeroes). Actually, preceding ``SELECT'' gives ``7766279631452241920''. Should it be ``9223372036854775807'' (MAXINT64) or ``100'' (String of one and 18 zeroes --- maximum length INT64)? -- best regards Cezary H. Noweta
[sqlite] problem with CURRENT_TIMESTAMP
Hi all, I suspect I've a wrong timezone, even if I cannot understand how and why. I've got atable with a column defined as follows: ts timestamp DEFAULT CURRENT_TIMESTAMP this should get default values in GMT. Now if I try to compare a row inserted a few minutes before (without the ts value, so with the default) with the current timestamp I got 'now' is before the row timestamp: SELECT strftime('%s', ts ), ts, strftime( '%s', CURRENT_TIMESTAMP ), CURRENT_TIMESTAMP FROM 1457949984|2016-03-14 10:06:24|1457948222|2016-03-14 09:37:02 So the row appear to be one hour in the future. I kindly ask how to fix this and how to safely check all the already inserted tuples (e.g., update their ts values). Thanks, Luca
[sqlite] CAST STRING => INTEGER
On 14 Mar 2016, at 10:12am, Cezary H. Noweta wrote: > What ``SELECT CAST('1' AS INTEGER);'' should give? > (String of one and 20 zeroes). Actually, preceding ``SELECT'' gives > ``7766279631452241920''. Should it be ``9223372036854775807'' (MAXINT64) or > ``100'' (String of one and 18 zeroes --- maximum length > INT64)? I would never write code which depended on the answer to that question. There are many too many ways for it to fail. Simon.
[sqlite] problem with CURRENT_TIMESTAMP
On 3/14/16, Luca Ferrari wrote: > Hi all, > I suspect I've a wrong timezone, even if I cannot understand how and why. > I've got atable with a column defined as follows: > > ts timestamp DEFAULT CURRENT_TIMESTAMP > > this should get default values in GMT. Now if I try to compare a row > inserted a few minutes before (without the ts value, so with the > default) with the current timestamp I got 'now' is before the row > timestamp: > > SELECT strftime('%s', ts ), ts, strftime( '%s', CURRENT_TIMESTAMP ), > CURRENT_TIMESTAMP FROM > 1457949984|2016-03-14 10:06:24|1457948222|2016-03-14 09:37:02 > > So the row appear to be one hour in the future. > I kindly ask how to fix this and how to safely check all the already > inserted tuples (e.g., update their ts values). > I think your system clock was set incorrectly when you did the initial insert. -- D. Richard Hipp drh at sqlite.org
[sqlite] How to disambiguate duplicated column names when joining two tables?
On 13 Mar 2016, at 11:39pm, Peng Yu wrote: > Is there a syntax to disambigurate the duplicated > column names? You can use the table names in your SELECT in the same way as you use them in the JOIN: select * from A join B on A.a=B.a; a b c a b d -- -- -- -- -- -- 1 4 7 1 4 7 2 5 8 2 5 8 2 15 18 2 5 8 SELECT A.b from A join B on A.a=B.a; will give 4,5,15 SELECT B.b from A join B on A.a=B.a; will give 4,5,5 Simon.