Re: [sqlite] Updating two virtual table in WinRT under one transaction with debug mode
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
Re: [sqlite] Updating two virtual table in WinRT under one transaction with debug mode
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] Updating two virtual table in WinRT under one transaction with debug mode
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
>
