PRAGMA foreign_keys not working
Ace-GIS opened this issue · 2 comments
Ace-GIS commented
I am using the following snippet on an Android Samsung Galaxy Tab S2 without success.
initDatabase: function () {
window.resolveLocalFileSystemURL(cordova.file.externalDataDirectory, function(externalDataDirectoryEntry) {
//window.sqlitePlugin.deleteDatabase({name: 'event_manager.db', androidDatabaseLocation: externalDataDirectoryEntry.toURL()});
db = window.sqlitePlugin.openDatabase({name: 'event_manager.db', androidDatabaseLocation: externalDataDirectoryEntry.toURL()});
db.executeSql('PRAGMA foreign_keys = ON;', [], function(res) {
db.executeSql('PRAGMA foreign_keys;', [], function(res){
console.log('PRAGMA res: ' + JSON.stringify(res));
})
})
db.transaction(function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS events( EventID Integer Primary Key NOT NULL, RaceName Text NOT NULL, '
+ 'BeginDate DateTime NOT NULL, EndDate DateTime NOT NULL, LocationName Text, '
+ 'LocationAddrLine1 Text, LocationAddrLine2 Text, LocationPostCode Integer, '
+ 'LocationCity Text, LocationCountry Text, CrossRefID Integer NOT NULL);');
tx.executeSql('CREATE TABLE IF NOT EXISTS groups(EventID Integer Primary Key NOT NULL, GroupID Integer NOT NULL, ' //Primary Key
+ 'GroupName Text, HasComments Boolean NOT NULL, FOREIGN KEY(EventID) REFERENCES events(EventID));');
tx.executeSql('CREATE TABLE IF NOT EXISTS questions(GroupID Integer Primary Key NOT NULL, QuestionID Integer NOT NULL, ' //Primary Key
+ 'AnswerType Text NOT NULL, SortOrder Integer NOT NULL, QuestionText Text NOT NULL, '
+ 'FOREIGN KEY(GroupID) REFERENCES groups(GroupID));');
tx.executeSql('CREATE TABLE IF NOT EXISTS comments(GroupID Integer Primary Key NOT NULL, CommentText Text, ' //Primary Key
+ 'FOREIGN KEY(GroupID) REFERENCES groups(GroupID));');
tx.executeSql('CREATE TABLE IF NOT EXISTS answers(GroupID Integer Primary Key NOT NULL, QuestionID Integer NOT NULL, '
+ 'Yes Integer, No Integer, MCAnswer1 Integer, MCAnswer2 Integer, MCAnswer3 Integer, MCAnswer4 Integer, '
+ 'MCAnswer5 Integer, DateTime DateTime, Quantity Integer, AnswerText Text, '
+ 'FOREIGN KEY(GroupID) REFERENCES groups(GroupID), FOREIGN KEY(QuestionID) REFERENCES questions(QuestionID));');
tx.executeSql('CREATE TABLE IF NOT EXISTS file_upload(EventID Integer NOT NULL, Purpose Text, Description Text, '
+ 'RidernetID Text, QuestionID Integer, TempInjuryID Integer, MediaType Text, MediaContent Text, '
+ 'MediaFileName Text, CrossRefID Integer, FOREIGN KEY(EventID) REFERENCES events(EventID), '
+ 'FOREIGN KEY(QuestionID) REFERENCES questions(QuestionID), FOREIGN KEY(CrossRefID) REFERENCES events(CrossRefID));');
tx.executeSql('CREATE TABLE IF NOT EXISTS media(EventID Integer NOT NULL, mediaItemPath Text, '
+ 'FOREIGN KEY(EventID) REFERENCES events(EventID));');
}, function(error) {
console.log('Transaction ERROR: ' + error.message);
db.close();
}, function() {
console.log('Tables created - Database OK');
});
});
}
Not sure why the begining is not shown as code. Sorry...
When I open the database to check using "PRAGMA foreign_keys;" the result is 0 which is OFF. Can anybody help me enable the foreign keys?
brodybits commented
My apologies for the delay. I hope to test this within the next few weeks. Labeling as a bug for now.
brodybits commented
I fixed JavaScript syntax highlighting in the original question for better readability, added your question to the list of items for the next minor release of cordova-sqlite-storage.