[sqlite] BUG JUST RUN ON THE DUAL :) --> select replace( cast (1 as real) , "." , "") , 1

[Hakan Yüksel]

Hi i detect a bug theirs result should be same but it is not

select   replace( cast (1 as real)  , "." ,  "")   ,   1

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

[sqlite] Redundant open *.sqlite-wal file

[pavel.pimenov]

Hi

 journal_mode=PERSIST ! but sqlite 3.13.0 tries to open a file 
*.sqlite-wal


Process monitor log:
https://yadi.sk/i/nf6MQ4cNtBD4j

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

[sqlite] Updating two virtual table in WinRT under one transaction with debug mode

[Volford Bence]

Dear SQLite developers,

I'm creating a plugin under Cordova framework targeting Win10 (UWP), so 
my project is in c# with WinRT. I'm using SQLite3 successfully. I have 
the reference for the SQLite.Net.Core-PCL and SQLite.Net-PCL (v3.1.1), 
and added the SQLite3 for my Win10 project, using the SQLite.UWP.2015 
version 3.13.0. I have to do some work on a given database with virtual 
tables, but whenever I want to update some row from 2 virtual table in a 
single transaction, my application breaks with a message in the attachment:


Assertion failed!
Program: ..\sqlite3.dll
File: tsrc/fts3.c
Line: 3772
Expression: |((Fts3Table*)pVtab)->mxSavepoint I've tested when this can be throwed, I created a simple example, 
creating two virtual table, inserting some data, and in the next 
transaction, I'm updating the two data. The first update is ok, but the 
second one fails.


|varpath 
=Path.Combine(ApplicationData.Current.LocalFolder.Path,"test.db");using(SQLiteConnectionconn 
=newSQLiteConnection(newSQLite.Net.Platform.WinRT.SQLitePlatformWinRT(),path)){try{conn.BeginTransaction();conn.Execute("CREATE 
VIRTUAL TABLE T1 USING fts4(ID,VALUE)"); //Just creating a test 
databaseconn.Execute("CREATE VIRTUAL TABLE T2 USING 
fts4(ID,VALUE)");conn.Execute("INSERT INTO T1 VALUES 
('1','VALUE')");conn.Execute("INSERT INTO T2 VALUES 
('2','VALUE')");conn.Commit();conn.BeginTransaction();conn.Execute("UPDATE 
T1 SET VALUE = 'value2' WHERE ID = '1'"); //Executed 
wellconn.Execute("UPDATE T2 SET VALUE = 'value2' WHERE ID = '2'"); 
//Assertion Fail!conn.Commit();}catch(Exceptionex){Log.Error("Error 
occured "+ex.Message);}}|


I found the rows in the fts3: 
https://github.com/mackyle/sqlite/blob/master/ext/fts3/fts3.c


|/* ** The xSavepoint() method. ** ** Flush the contents of the 
pending-terms table to disk. */staticintfts3SavepointMethod(sqlite3_vtab 
*pVtab,intiSavepoint){intrc 
=SQLITE_OK;UNUSED_PARAMETER(iSavepoint);assert(((Fts3Table*)pVtab)->inTransaction 
);assert(((Fts3Table*)pVtab)->mxSavepoint );TESTONLY(((Fts3Table*)pVtab)->mxSavepoint =iSavepoint 
);if(((Fts3Table*)pVtab)->bIgnoreSavepoint==0){rc 
=fts3SyncMethod(pVtab);}returnrc;}|


If I'm running it in release mode, i don't get this error, because the 
asserts are excluded, and as i see, the rows are modified successfully, 
but in debug mode, throws me out always.


I have to use all update in one transaction, because if some of them 
fail, I have to rollback everything. I tried with unique savepoint, but 
the same effect. I have the same problem if I target Win8.1 and 
WindowsPhone8.1 too with the corresponding sqlite binaries.


Do you have any idea, how to resolve this, or why is this happening? Or 
maybe this is a bug? If the assert fails, maybe it have some effect on 
release mode too, but can't see now?


Regards,
Bence

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

Re: [sqlite] BUG JUST RUN ON THE DUAL :) --> select replace( cast (1 as real) , "." , "") , 1

[Richard Hipp]

On 7/10/16, Hakan Yüksel  wrote:
> Hi i detect a bug theirs result should be same but it is not
>
> select   replace( cast (1 as real)  , "." ,  "")   ,   1

The "cast(1 as real)" expression results in "1.0".  The
"replace('1.0', '.', '')" expression results in "10".  SQLite appears
to be doing the right thing here.

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


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

Re: [sqlite] Redundant open *.sqlite-wal file

[Richard Hipp]

On 7/11/16, pavel.pimenov  wrote:
> Hi
>
>   journal_mode=PERSIST ! but sqlite 3.13.0 tries to open a file
> *.sqlite-wal

SQLite does not know the journal mode until it has opened the
database.  And it cannot safely open the database without first
checking for the existance of a -wal file that some prior process
might have left laying around due to a (non-SQLite related) crash.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using CEROD with Python

[David Lederkremer]

I am using a library that is CEROD-enabled and still I cannot open my
CEROD-encrypted DB. It has no password so I'm supposed to use the prefix
':cerod::' but when I use it like this:

> import sqlite3

> conn = sqlite3.connect(':cerod::example.db')

> cursor = conn.cursor()

> cursor.execute('...')

> ..."


It simply creates a new empy DB named :cerod::example.db .

Am I doing it wrong or perhaps the program doesn't recognize CEROD?

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

[sqlite] ENABLE_UPDATE_DELETE_LIMIT

[Janos Levai]

Dear sqlite devs,

Are there any downsides to setting ENABLE_UPDATE_DELETE_LIMIT when creating
the official amalgamation? Will users of the amalgamation, not
needing ENABLE_UPDATE_DELETE_LIMIT, see any issues?

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

Re: [sqlite] ENABLE_UPDATE_DELETE_LIMIT

[Clemens Ladisch]

Janos Levai wrote:
> Are there any downsides to setting ENABLE_UPDATE_DELETE_LIMIT when creating
> the official amalgamation? Will users of the amalgamation, not
> needing ENABLE_UPDATE_DELETE_LIMIT, see any issues?

They might be tempted to use it.


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

Re: [sqlite] ENABLE_UPDATE_DELETE_LIMIT

[Simon Slavin]

On 11 Jul 2016, at 3:17pm, Clemens Ladisch  wrote:

> Janos Levai wrote:
>> Are there any downsides to setting ENABLE_UPDATE_DELETE_LIMIT when creating
>> the official amalgamation? Will users of the amalgamation, not
>> needing ENABLE_UPDATE_DELETE_LIMIT, see any issues?
> 
> They might be tempted to use it.

To expand on this, the commands this enables will do different things depending 
on the version of SQLite, whether the database has been ANALYZEd, or what data 
is in the table.  They are extremely unpredictable.  It is kinder to 
programmers just to make sure they don't use them.

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

Re: [sqlite] Updating two virtual table in WinRT under one transaction with debug mode

[Chris Brody]

Did you see the Cordova-sqlite-storage plugin that I maintain? It
supports Windows 8.1, Windows Phone 8.1, and Windows 10 UWP in
addition to Android and iOS. It uses the SQLite3-WinRT C++ library
which I think is better than using .NET/C#.

The following test case works fine for me when I try it on Windows
8.1, Windows Phone 8.1, Windows 10 on desktop, and Windows 10 on
mobile device, all in Debug mode:

it(suiteName + 'XXX FTS4 test', function(done) {
  var db = openDatabase('xxx-fts4-test.db', '1.0', 'Test',
DEFAULT_SIZE);

  db.transaction(function(tx) {
tx.executeSql("DROP TABLE IF EXISTS T1");
tx.executeSql("DROP TABLE IF EXISTS T2");

tx.executeSql("CREATE VIRTUAL TABLE T1 USING fts4(ID,VALUE)");
tx.executeSql("CREATE VIRTUAL TABLE T2 USING fts4(ID,VALUE)");

tx.executeSql("INSERT INTO T1 VALUES ('1','VALUE')");
tx.executeSql("INSERT INTO T2 VALUES ('2','VALUE')");
  }, function(e) {
// not expected:
expect(false).toBe(true);
expect(JSON.stringify(e)).toBe('---');
done();
  }, function() {
//console.log('first tx success cb OK');
db.transaction(function(tx) {
  tx.executeSql("UPDATE T1 SET VALUE = 'value2' WHERE ID = '1'");
  tx.executeSql("UPDATE T2 SET VALUE = 'value2' WHERE ID = '2'");
}, function(e) {
  // not expected:
  expect(false).toBe(true);
  expect(JSON.stringify(e)).toBe('---');
  done();
}, function() {
  expect(true).toBe(true);
  done();
});
  });
}, MYTIMEOUT);

I am currently using SQLite 3.8.10.2 but would be happy to try it out
with 3.12.2 or 3.13.0 if you suspect a problem in a newer release.

On Mon, Jul 11, 2016 at 12:42 PM, Volford Bence
 wrote:
> Dear SQLite developers,
>
> I'm creating a plugin under Cordova framework targeting Win10 (UWP), so my
> project is in c# with WinRT. I'm using SQLite3 successfully. I have the
> reference for the SQLite.Net.Core-PCL and SQLite.Net-PCL (v3.1.1), and added
> the SQLite3 for my Win10 project, using the SQLite.UWP.2015 version 3.13.0.
> I have to do some work on a given database with virtual tables, but whenever
> I want to update some row from 2 virtual table in a single transaction, my
> application breaks with a message in the attachment:
>
> Assertion failed!
> Program: ..\sqlite3.dll
> File: tsrc/fts3.c
> Line: 3772
> Expression: |((Fts3Table*)pVtab)->mxSavepoint  |
>
> I've tested when this can be throwed, I created a simple example, creating
> two virtual table, inserting some data, and in the next transaction, I'm
> updating the two data. The first update is ok, but the second one fails.
>
> |varpath
> =Path.Combine(ApplicationData.Current.LocalFolder.Path,"test.db");using(SQLiteConnectionconn
> =newSQLiteConnection(newSQLite.Net.Platform.WinRT.SQLitePlatformWinRT(),path)){try{conn.BeginTransaction();conn.Execute("CREATE
> VIRTUAL TABLE T1 USING fts4(ID,VALUE)"); //Just creating a test
> databaseconn.Execute("CREATE VIRTUAL TABLE T2 USING
> fts4(ID,VALUE)");conn.Execute("INSERT INTO T1 VALUES
> ('1','VALUE')");conn.Execute("INSERT INTO T2 VALUES
> ('2','VALUE')");conn.Commit();conn.BeginTransaction();conn.Execute("UPDATE
> T1 SET VALUE = 'value2' WHERE ID = '1'"); //Executed
> wellconn.Execute("UPDATE T2 SET VALUE = 'value2' WHERE ID = '2'");
> //Assertion Fail!conn.Commit();}catch(Exceptionex){Log.Error("Error occured
> "+ex.Message);}}|
>
> I found the rows in the fts3:
> https://github.com/mackyle/sqlite/blob/master/ext/fts3/fts3.c
>
> |/* ** The xSavepoint() method. ** ** Flush the contents of the
> pending-terms table to disk. */staticintfts3SavepointMethod(sqlite3_vtab
> *pVtab,intiSavepoint){intrc
> =SQLITE_OK;UNUSED_PARAMETER(iSavepoint);assert(((Fts3Table*)pVtab)->inTransaction
> );assert(((Fts3Table*)pVtab)->mxSavepoint  );TESTONLY(((Fts3Table*)pVtab)->mxSavepoint =iSavepoint
> );if(((Fts3Table*)pVtab)->bIgnoreSavepoint==0){rc
> =fts3SyncMethod(pVtab);}returnrc;}|
>
> If I'm running it in release mode, i don't get this error, because the
> asserts are excluded, and as i see, the rows are modified successfully, but
> in debug mode, throws me out always.
>
> I have to use all update in one transaction, because if some of them fail, I
> have to rollback everything. I tried with unique savepoint, but the same
> effect. I have the same problem if I target Win8.1 and WindowsPhone8.1 too
> with the corresponding sqlite binaries.
>
> Do you have any idea, how to resolve this, or why is this happening? Or
> maybe this is a bug? If the assert fails, maybe it have some effect on
> release mode too, but can't see now?
>
> Regards,
> Bence
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinf

Re: [sqlite] Updating two virtual table in WinRT under one transaction with debug mode

[Volford Bence]

Hi Chris,

Your plugin is looking good, but my problem is, that I can maintain only 
my plugin. I don't have access to the main application. I just get the 
commands from there, and my main job with this plugin is that I have to 
keep updated the database, communicate with the server, etc... The main 
app will use that database. So I can't communicate with other plugins, 
or it will be a hard effort i think. I've done this on ios and android 
too, but on these platforms this problem didnt occured, only on Windows 
with this settings in Debug mode, and maybe there can be some 
consequence on release mode too, I don't know yet.


Thanks,
Bence
2016. 07. 11. 16:28 keltezéssel, Chris Brody írta:

Did you see the Cordova-sqlite-storage plugin that I maintain? It
supports Windows 8.1, Windows Phone 8.1, and Windows 10 UWP in
addition to Android and iOS. It uses the SQLite3-WinRT C++ library
which I think is better than using .NET/C#.

The following test case works fine for me when I try it on Windows
8.1, Windows Phone 8.1, Windows 10 on desktop, and Windows 10 on
mobile device, all in Debug mode:

it(suiteName + 'XXX FTS4 test', function(done) {
   var db = openDatabase('xxx-fts4-test.db', '1.0', 'Test',
DEFAULT_SIZE);

   db.transaction(function(tx) {
 tx.executeSql("DROP TABLE IF EXISTS T1");
 tx.executeSql("DROP TABLE IF EXISTS T2");

 tx.executeSql("CREATE VIRTUAL TABLE T1 USING fts4(ID,VALUE)");
 tx.executeSql("CREATE VIRTUAL TABLE T2 USING fts4(ID,VALUE)");

 tx.executeSql("INSERT INTO T1 VALUES ('1','VALUE')");
 tx.executeSql("INSERT INTO T2 VALUES ('2','VALUE')");
   }, function(e) {
 // not expected:
 expect(false).toBe(true);
 expect(JSON.stringify(e)).toBe('---');
 done();
   }, function() {
 //console.log('first tx success cb OK');
 db.transaction(function(tx) {
   tx.executeSql("UPDATE T1 SET VALUE = 'value2' WHERE ID = '1'");
   tx.executeSql("UPDATE T2 SET VALUE = 'value2' WHERE ID = '2'");
 }, function(e) {
   // not expected:
   expect(false).toBe(true);
   expect(JSON.stringify(e)).toBe('---');
   done();
 }, function() {
   expect(true).toBe(true);
   done();
 });
   });
 }, MYTIMEOUT);

I am currently using SQLite 3.8.10.2 but would be happy to try it out
with 3.12.2 or 3.13.0 if you suspect a problem in a newer release.

On Mon, Jul 11, 2016 at 12:42 PM, Volford Bence
 wrote:

Dear SQLite developers,

I'm creating a plugin under Cordova framework targeting Win10 (UWP), so my
project is in c# with WinRT. I'm using SQLite3 successfully. I have the
reference for the SQLite.Net.Core-PCL and SQLite.Net-PCL (v3.1.1), and added
the SQLite3 for my Win10 project, using the SQLite.UWP.2015 version 3.13.0.
I have to do some work on a given database with virtual tables, but whenever
I want to update some row from 2 virtual table in a single transaction, my
application breaks with a message in the attachment:

Assertion failed!
Program: ..\sqlite3.dll
File: tsrc/fts3.c
Line: 3772
Expression: |((Fts3Table*)pVtab)->mxSavepoint https://github.com/mackyle/sqlite/blob/master/ext/fts3/fts3.c

|/* ** The xSavepoint() method. ** ** Flush the contents of the
pending-terms table to disk. */staticintfts3SavepointMethod(sqlite3_vtab
*pVtab,intiSavepoint){intrc
=SQLITE_OK;UNUSED_PARAMETER(iSavepoint);assert(((Fts3Table*)pVtab)->inTransaction
);assert(((Fts3Table*)pVtab)->mxSavepoint mxSavepoint =iSavepoint
);if(((Fts3Table*)pVtab)->bIgnoreSavepoint==0){rc
=fts3SyncMethod(pVtab);}returnrc;}|

If I'm running it in release mode, i don't get this error, because the
asserts are excluded, and as i see, the rows are modified successfully, but
in debug mode, throws me out always.

I have to use all update in one transaction, because if some of them fail, I
have to rollback everything. I tried with unique savepoint, but the same
effect. I have the same problem if I target Win8.1 and WindowsPhone8.1 too
with the corresponding sqlite binaries.

Do you have any idea, how to resolve this, or why is this happening? Or
maybe this is a bug? If the assert fails, maybe it have some effect on
release mode too, but can't see now?

Regards,
Bence


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


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


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

Re: [sqlite] Using CEROD with Python

[Richard Hipp]

On 7/11/16, David Lederkremer  wrote:
> I am using a library that is CEROD-enabled and still I cannot open my
> CEROD-encrypted DB. It has no password so I'm supposed to use the prefix
> ':cerod::' but when I use it like this:
>
>> import sqlite3
>
>> conn = sqlite3.connect(':cerod::example.db')
>
>> cursor = conn.cursor()
>
>> cursor.execute('...')
>
>> ..."
>
>
> It simply creates a new empy DB named :cerod::example.db .
>
> Am I doing it wrong or perhaps the program doesn't recognize CEROD?

My guess is that Python is not using the CEROD-enabled SQLite that you
think it is using, but is instead using either a public-domain SQLite
system library, or a statically linked copy of the SQLite library.

If you use Python to open a connection to ":memory:" and then run commands:

PRAGMA compile_options;
SELECT sqlite_source_id();

what outputs do you see?

In particular, I'm expecting to see "ENABLE_CEROD" among the outputs
from the pragma if you are really using a CEROD-enabled SQLite.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ENABLE_UPDATE_DELETE_LIMIT

[Richard Hipp]

On 7/11/16, Janos Levai  wrote:
> Dear sqlite devs,
>
> Are there any downsides to setting ENABLE_UPDATE_DELETE_LIMIT when creating
> the official amalgamation? Will users of the amalgamation, not
> needing ENABLE_UPDATE_DELETE_LIMIT, see any issues?

Yes.  ENABLE_UPDATE_DELETE_LIMIT makes changes to the LALR(1) parser
tables which cannot be #ifdef-ed out.  So there is not a convenient
way to turn that feature off and on at compile-time except to rerun
the Lemon parser generator, which basically means rebuilding from
canonical sources.

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

Re: [sqlite] Updating two virtual table in WinRT under one transaction with debug mode

[Chris Brody]

Hi Bence,

Is the plugin you maintain open source, or is it a custom plugin for
your application?

I tried my version with SQLite 3.13.0 on Windows 8.1 (both x86 and
x64), Windows Phone 8.1 (arm), Windows 10 (both x86 and x64), and
Windows 10 mobile (arm), all in Debug mode, and with my test case
extended to check the results of SELECT at the end (posted below). It
works 100% for me. My plugin also uses BEGIN/COMMIT to demarcate the
transactions.

You may want to take a look at the flags I use and see if you can
identify any major differences.

If you really want to solve the problem with SQLite3.Net you may want
to isolate a test program and ideally try it with Windows 8.1, Windows
Phone 8.1, and Windows 10 builds. You may want to raise this on the
SQLite.Net project as well in case they may have any further insights.

You may also want to take a quick look at how my version includes and
builds the SQLite3-WinRT library from plugin.xml. An old, synchronous
version of the SQLite3-WinRT library is actually embedded with a few
small modifications to deal with insertId, rowsAffected, and 64-bit
integers. Just an idea.

Here is the test case now extended to check the SELECT results after
the UPDATEs:

it(suiteName + 'XXX FTS4 test', function(done) {
  var db = openDatabase('xxx-fts4-test.db', '1.0', 'Test',
DEFAULT_SIZE);

  db.transaction(function(tx) {
tx.executeSql("DROP TABLE IF EXISTS T1");
tx.executeSql("DROP TABLE IF EXISTS T2");

tx.executeSql("CREATE VIRTUAL TABLE T1 USING fts4(ID,VALUE)");
tx.executeSql("CREATE VIRTUAL TABLE T2 USING fts4(ID,VALUE)");

tx.executeSql("INSERT INTO T1 VALUES ('1','VALUE')");
tx.executeSql("INSERT INTO T2 VALUES ('2','VALUE')");
  }, function(e) {
// not expected:
expect(false).toBe(true);
expect(JSON.stringify(e)).toBe('---');
done();
  }, function() {
//console.log('first tx success cb OK');
db.transaction(function(tx) {
  tx.executeSql("UPDATE T1 SET VALUE = 'value2' WHERE ID = '1'");
  tx.executeSql("UPDATE T2 SET VALUE = 'value2' WHERE ID = '2'");
}, function(e) {
  // not expected:
  expect(false).toBe(true);
  expect(JSON.stringify(e)).toBe('---');
  done();
}, function() {
  var checkCount = 0;
  db.transaction(function(tx) {
tx.executeSql("SELECT * FROM T1", [], function(tx, rs) {
  ++checkCount;
  expect(rs.rows.length).toBe(1);
  expect(rs.rows.item(0).ID).toBe('1');
  expect(rs.rows.item(0).VALUE).toBe('value2');
});
tx.executeSql("SELECT * FROM T2", [], function(tx, rs) {
  ++checkCount;
  expect(rs.rows.length).toBe(1);
  expect(rs.rows.item(0).ID).toBe('2');
  expect(rs.rows.item(0).VALUE).toBe('value2');
});
  }, function(e) {
// not expected:
expect(false).toBe(true);
expect(JSON.stringify(e)).toBe('---');
done();
  }, function() {
expect(checkCount).toBe(2);
done();
  });
});
  });
}, MYTIMEOUT);

Good luck!

Chris

On Mon, Jul 11, 2016 at 5:04 PM, Volford Bence
 wrote:
> Hi Chris,
>
> Your plugin is looking good, but my problem is, that I can maintain only my
> plugin. I don't have access to the main application. I just get the commands
> from there, and my main job with this plugin is that I have to keep updated
> the database, communicate with the server, etc... The main app will use that
> database. So I can't communicate with other plugins, or it will be a hard
> effort i think. I've done this on ios and android too, but on these
> platforms this problem didnt occured, only on Windows with this settings in
> Debug mode, and maybe there can be some consequence on release mode too, I
> don't know yet.
>
> Thanks,
> Bence
>
> 2016. 07. 11. 16:28 keltezéssel, Chris Brody írta:
>>
>> Did you see the Cordova-sqlite-storage plugin that I maintain? It
>> supports Windows 8.1, Windows Phone 8.1, and Windows 10 UWP in
>> addition to Android and iOS. It uses the SQLite3-WinRT C++ library
>> which I think is better than using .NET/C#.
>>
>> The following test case works fine for me when I try it on Windows
>> 8.1, Windows Phone 8.1, Windows 10 on desktop, and Windows 10 on
>> mobile device, all in Debug mode:
>>
>> it(suiteName + 'XXX FTS4 test', function(done) {
>>var db = openDatabase('xxx-fts4-test.db', '1.0', 'Test',
>> DEFAULT_SIZE);
>>
>>db.transaction(function(tx) {
>>  tx.executeSql("DROP TABLE IF EXISTS T1");
>>  tx.executeSql("DROP TABLE IF EXISTS T2");
>>
>> 

Re: [sqlite] Using CEROD with Python

[Keith Medcalf]

Which OS are you running on?  

On Windows for example, PYTHON will use the version of the sqlite3.dll found in 
%PYTHONHOME%\DLLs by default.  You can change the DLL in that location to the 
version that you want to use and it will usually work just fine ...

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of David Lederkremer
> Sent: Monday, 11 July, 2016 07:56
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Using CEROD with Python
> 
> I am using a library that is CEROD-enabled and still I cannot open my
> CEROD-encrypted DB. It has no password so I'm supposed to use the prefix
> ':cerod::' but when I use it like this:
> 
> > import sqlite3
> 
> > conn = sqlite3.connect(':cerod::example.db')
> 
> > cursor = conn.cursor()
> 
> > cursor.execute('...')
> 
> > ..."
> 
> 
> It simply creates a new empy DB named :cerod::example.db .
> 
> Am I doing it wrong or perhaps the program doesn't recognize CEROD?
> 
> Any suggestions?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Bug Report: All database opening blocked awaiting wal index rebuild

[Brian Vincent]

Yes, you seem to understand the issue.  The issue only happens when using
shared caches.

I've reproduced the issue using both SERIALIZED and MULTITHREADED modes.

I'm surprised to hear you say that it might be an inherit limitation or
something not necessarily undesirable.  Of course, when using shared
caches, there must be locks to protect the global list of caches.  But
ideally, I would expect these times to be short lived.  If a situation ever
arises where all unrelated database opening, from all threads, is blocked
for long periods of time, that seems to me to be an obvious bug.  That's
the bug that I'm reporting.

Being an inherit limitation would seem to imply that there is no solution
to this problem, that having shared caches and WAL indexes rebuilding
necessarily should block all unrelated databases opening.  I don't see why
that should be the case and I'll explain some reasons why.  It's a little
bit hard for me to talk about it though, because I'm not entirely sure what
the lock SQLITE_MUTEX_STATIC_OPEN is protecting.  When iterating through
the list of shared caches, it acquires the lock SQLITE_MUTEX_STATIC_MASTER,
so the other OPEN lock must be for something else.  The comments say it's
to prevent a race condition and references "Ticket #3537", but I can't seem
to find that ticket.

The problematic line seems to be this one:
https://github.com/mackyle/sqlite/blob/master/src/btree.c#L2405

It seems that the only purpose of this line is to check to see if this is
an already shared pager-cache.  The problem is that this line ends up
acquiring a lock on the btree.  If there was simply a way to check if this
is an already shared pager-cache, without the possibility of waiting on a
lock, then this bug would be solved.

This is the checkin that originally created the open lock
https://www.sqlite.org/src/info/19fa5a29b97f017a

It doesn't appear that the problematic line existed at this time, which
leads me to believe that there were probably versions of sqlite released
that didn't have this bug.

It seems to me that when this open lock was created, it was expected that
the open code would always execute within a very short period of time.
Later on, someone added the call to sqlite3BtreeSchema, not realizing that
it could possibly get hung up on a lock, which could block all opening for
all databases.

Please let me know if I'm thinking about this problem clearly, or if you
would like me to test some things or write a simple test case.

Thanks,
Brian Vincent


On Sat, Jul 9, 2016 at 5:58 AM Olivier Mascia  wrote:

> > Le 9 juil. 2016 à 12:33, Simon Slavin  a écrit :
> >
> >> I'm really interested in knowing wether you use the engine in
> SERIALIZED or MULTITHREADED mode during this event reproduction?
> >
> > In other words, please read the last part of
> >
> > 
> >
> > and try to reproduce your problem in "Multi-thread" mode.  If the
> problem still occurs, please try again in "Serialized" mode.
>
> In between I had a quick look at sqlite3.c code.  It looks like the source
> of the issue is expected. The MULTITHREADED mode relaxes usage of mutexes,
> albeit only on connections and statements structures.  The remaining bits
> of the engine still make uses of multiple mutexes to protect its integrity
> from multiple threads using the engine.
>
> See sqlite3BtreeOpen() and this section of code where the
> SQLITE_MUTEX_STATIC_OPEN is acquired.
> The SERIALIZED and MULTITHREADED modes both have SQLITE_THREADSAFE != 0
> (which is right).
>
> #if !defined(SQLITE_OMIT_SHARED_CACHE) && !defined(SQLITE_OMIT_DISKIO)
>   /*
>   ** If this Btree is a candidate for shared cache, try to find an
>   ** existing BtShared object that we can share with
>   */
>   if( isTempDb==0 && (isMemdb==0 || (vfsFlags&SQLITE_OPEN_URI)!=0) ){
> if( vfsFlags & SQLITE_OPEN_SHAREDCACHE ){
>   int nFilename = sqlite3Strlen30(zFilename)+1;
>   int nFullPathname = pVfs->mxPathname+1;
>   char *zFullPathname = sqlite3Malloc(MAX(nFullPathname,nFilename));
>   MUTEX_LOGIC( sqlite3_mutex *mutexShared; )
>
>   p->sharable = 1;
>   if( !zFullPathname ){
> sqlite3_free(p);
> return SQLITE_NOMEM_BKPT;
>   }
>   if( isMemdb ){
> memcpy(zFullPathname, zFilename, nFilename);
>   }else{
> rc = sqlite3OsFullPathname(pVfs, zFilename,
>nFullPathname, zFullPathname);
> if( rc ){
>   sqlite3_free(zFullPathname);
>   sqlite3_free(p);
>   return rc;
> }
>   }
> #if SQLITE_THREADSAFE
>   mutexOpen = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_OPEN);
>   sqlite3_mutex_enter(mutexOpen);
>   mutexShared = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER);
>   sqlite3_mutex_enter(mutexShared);
> #endif
>
> The whole issue then revolves around the SHARED_CACHE/PRIVATE_CACHE and
> not the SERIALIZED/MULTITHREADED mode. Chances are that if OP Brian tries
> the same scen

[sqlite] Query question: order by ascending, return the two largest values in ascending order

[Keith Christian]

A table has a column of dates and times that look like this:

2015-10-02 07:55:02
2015-10-02 07:55:02
2015-10-02 10:00:03
2015-10-02 10:05:02
2015-10-02 10:10:02


Schema:
CREATE TABLE general ( id integer primary key autoincrement, server
text, date_time_stamp text);


Would like to get the latest two dates and times, kept in ascending
order, e.g. the query should return these two values:

2015-10-02 10:05:02
2015-10-02 10:10:02


Is there a way to store the number of values in the date_time_stamp
column and use the count minus N to get the largest N values in the
column?


Query:
select date_time_stamp a from general, c as count(a) from general
where date_time_stamp!='date_time_stamp_isempty' order by a limit c,2;


Error: incomplete SQL: select date_time_stamp a from general, c as
count(a) from general where date_time_stamp!='_isempty' order by a
limit c,2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

[Simon Slavin]

On 12 Jul 2016, at 12:25am, Keith Christian  wrote:

> A table has a column of dates and times that look like this:
> 
> 2015-10-02 07:55:02
> 2015-10-02 07:55:02
> 2015-10-02 10:00:03
> 2015-10-02 10:05:02
> 2015-10-02 10:10:02
> 
> 
> Schema:
> CREATE TABLE general ( id integer primary key autoincrement, server
> text, date_time_stamp text);
> 
> 
> Would like to get the latest two dates and times, kept in ascending
> order, e.g. the query should return these two values:
> 
> 2015-10-02 10:05:02
> 2015-10-02 10:10:02

SELECT date_time_stamp FROM general ORDER BY date_time_stamp DESC LIMIT 2

The only difference is that the rows will always be in the reverse order to 
what you asked for: biggest timestamp first.  But since it's consistent that 
shouldn't be a problem.

I recommend you create an index on the date_time_stamp column, since that will 
make the above query work far faster.

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

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

[Stephen Chrzanowski]

Simons + My answer;

select * from (SELECT date_time_stamp FROM general ORDER BY date_time_stamp
DESC LIMIT 2) a order by date_time_stamp;

On Mon, Jul 11, 2016 at 7:33 PM, Simon Slavin  wrote:

>
> On 12 Jul 2016, at 12:25am, Keith Christian 
> wrote:
>
> > A table has a column of dates and times that look like this:
> >
> > 2015-10-02 07:55:02
> > 2015-10-02 07:55:02
> > 2015-10-02 10:00:03
> > 2015-10-02 10:05:02
> > 2015-10-02 10:10:02
> >
> >
> > Schema:
> > CREATE TABLE general ( id integer primary key autoincrement, server
> > text, date_time_stamp text);
> >
> >
> > Would like to get the latest two dates and times, kept in ascending
> > order, e.g. the query should return these two values:
> >
> > 2015-10-02 10:05:02
> > 2015-10-02 10:10:02
>
> SELECT date_time_stamp FROM general ORDER BY date_time_stamp DESC LIMIT 2
>
> The only difference is that the rows will always be in the reverse order
> to what you asked for: biggest timestamp first.  But since it's consistent
> that shouldn't be a problem.
>
> I recommend you create an index on the date_time_stamp column, since that
> will make the above query work far faster.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

[R Smith]

On 2016/07/12 1:25 AM, Keith Christian wrote:

A table has a column of dates and times that look like this:

2015-10-02 07:55:02
2015-10-02 07:55:02
2015-10-02 10:00:03
2015-10-02 10:05:02
2015-10-02 10:10:02


Schema:
CREATE TABLE general ( id integer primary key autoincrement, server
text, date_time_stamp text);


Would like to get the latest two dates and times, kept in ascending
order, e.g. the query should return these two values:

2015-10-02 10:05:02
2015-10-02 10:10:02


Is there a way to store the number of values in the date_time_stamp
column and use the count minus N to get the largest N values in the
column?


Yes, there is. Here are two pure SQL methods which will work in near any 
DB engine. Both are basically the same idea, but the first one expects 
non-duplicate entries (which your example data isn't, but possibly a 
copy-paste mistake, so I added it anyway), and the second will handle 
duplicate entries (assuming your data duplication is expected/correct 
and you don't want to see the duplicates).
The number "2" in the HAVING clauses can be any N and will show the 
latest N values. here we show only dates, but you can of course select 
any of the other fields with it:


SELECT * FROM general;

  --  id  | server | date_time_stamp
  --  | -- | -
  --   1  |A   | 2015-10-02 07:55:02
  --   2  |A   | 2015-10-02 07:55:02
  --   3  |A   | 2015-10-02 10:00:03
  --   4  |A   | 2015-10-02 10:05:02
  --   5  |A   | 2015-10-02 10:10:02


SELECT a.date_time_stamp
  FROM general AS a
  LEFT JOIN general AS b ON b.date_time_stamp > a.date_time_stamp
 GROUP BY a.date_time_stamp
 HAVING count(b.date_time_stamp) < 2
 ORDER BY a.date_time_stamp;


  -- date_time_stamp
  -- -
  -- 2015-10-02 10:05:02
  -- 2015-10-02 10:10:02



WITH g(date_time_stamp) AS (SELECT DISTINCT date_time_stamp FROM general)
 SELECT a.date_time_stamp
  FROM g AS a
  LEFT JOIN g AS b ON b.date_time_stamp > a.date_time_stamp
 GROUP BY a.date_time_stamp
 HAVING count(b.date_time_stamp) < 2
 ORDER BY a.date_time_stamp;


  -- date_time_stamp
  -- -
  -- 2015-10-02 10:05:02
  -- 2015-10-02 10:10:02


Cheers,
Ryan

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

Re: [sqlite] Bug: SQLite's include guards are reserved identifiers

[Richard Damon]

On 7/9/16 11:47 PM, J Decker wrote:

Okay if this should change, I would recommand a new standard for all
libraries;  and since standards are so important maybe make them know about
it too...

do ... ORG_DOMAIN_APPLICATION_LIBRARY_MODULE_SOURCE_INCLUDED

where each piece becomes unqiqe so there's no collision.  I'd expect
compilers these days would support more than 32 characters required by the
standard (goodbye Borland BCC 3.1).

#ifndef ORG_SQLITE_SQLITE_SQLITE_INCLUDED

and copY and paste one other time...
I mean what editor doesnt' support double click to mark a word (the above
is within the genaral defitition of a 'word' ) to copy and click somewhere
to paste?

I mean ; how many times have I had a symbol in a header that collided with
sqlite? or ffmpeg? or zlib? or... wait like never.

someone probably just never considered deprecation of reservation as a
'law' and at least demote to 'recommended practice' because some obscure
compiler somewhere would start failing?


Try to write a fully standard compliant standard library without using 
any of the reserved namespace!!!


By the standard, a header defined by the standard is only allow to 
define the symbols it is EXPLICITLY defined to, and the symbols in the 
appropriate reserved namespaces. ANY other symbol is allowed to be 
used/defined by the user.


Your suggest has a couple of problems. First, it handles the include 
guard, but not a bunch of other things that need symbols. The second, 
what is to be used by a programmer who doesn't own a domain? Owning a 
domain is not a requirement for writing software.


--
Richard Damon

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

Re: [sqlite] Bug: SQLite's include guards are reserved identifiers

[dandl]

> Try to write a fully standard compliant standard library without using any
of
> the reserved namespace!!!

This is the key point that has been missed so far. The C/C++ standards do
not provide a mechanism by which the supplier of a library can reserve or
sequester some range of identifiers, for both historic and possible future
use, and at the same time be guaranteed to conflict with neither the
provider of an implementation nor the user of the library. Therefore a
choice has to be made.

Intruding on the implementation space in violation of the standard is not
the best solution to the problem.

The safest solution (and one widely adopted) is to choose a prefix of
sufficient length and starting with a letter. It should be one that is
highly likely to be unique to the enterprise and then be applied to all
visible identifiers. The ownership of that prefix could be backed up by
ownership of a matching domain, trademark, registered company name, etc.
[The Java domain thing is just plain silly, and widely breached.]

In fact Sqlite has done an excellent job of exactly that, with one
exception: they chose to add an unnecessary underscore before the guard
prefix and thereby became non-compliant. It should simply be removed (or
have been removed -- perhaps it's now too late).

> By the standard, a header defined by the standard is only allow to define
the
> symbols it is EXPLICITLY defined to, and the symbols in the appropriate
> reserved namespaces. ANY other symbol is allowed to be used/defined by the
> user.
> 
> Your suggest has a couple of problems. First, it handles the include
guard,
> but not a bunch of other things that need symbols. The second, what is to
be
> used by a programmer who doesn't own a domain? Owning a domain is not a
> requirement for writing software.

Tell that to the Java guys.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

[J Decker]

SELECT DISTINCT date_time_stamp FROM general ORDER BY date_time_stamp DESC
LIMIT 2

isn't it simply to use DISTINCT?

On Mon, Jul 11, 2016 at 4:25 PM, Keith Christian 
wrote:

> A table has a column of dates and times that look like this:
>
> 2015-10-02 07:55:02
> 2015-10-02 07:55:02
> 2015-10-02 10:00:03
> 2015-10-02 10:05:02
> 2015-10-02 10:10:02
>
>
> Schema:
> CREATE TABLE general ( id integer primary key autoincrement, server
> text, date_time_stamp text);
>
>
> Would like to get the latest two dates and times, kept in ascending
> order, e.g. the query should return these two values:
>
> 2015-10-02 10:05:02
> 2015-10-02 10:10:02
>
>
> Is there a way to store the number of values in the date_time_stamp
> column and use the count minus N to get the largest N values in the
> column?
>
>
> Query:
> select date_time_stamp a from general, c as count(a) from general
> where date_time_stamp!='date_time_stamp_isempty' order by a limit c,2;
>
>
> Error: incomplete SQL: select date_time_stamp a from general, c as
> count(a) from general where date_time_stamp!='_isempty' order by a
> limit c,2
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users