I am developing a ionic application. To test sqlite database with broswer, I followed this tutorial which uses the sql.js library.
tutorial : https://www.techiediaries.com/mocking-native-sqlite-plugin/
library : https://github.com/kripken/sql.js/
I need to do some cascade delete but when I run
PRAGMA foreign_keys=ON
the cascade delete doesn't work on browser (works fine with a real device)
If I run again
PRAGMA foreign_keys
in the browser the results are
foreign_keys[{"foreign_keys":0}]
Is there a way to enable cascade delete with sql.js ? I can't find anything about it in the documentation but every other statement works fine
sqliteMock
declare var SQL;
@Injectable()
export class SQLiteMock {
public create(config: SQLiteDatabaseConfig): Promise<SQLiteObject> {
var db;
var storeddb = localStorage.getItem("database");
if(storeddb)
{
var arr = storeddb.split(',');
db = new SQL.Database(arr);
}
else
{
db = new SQL.Database();
}
return new Promise((resolve,reject)=>{
resolve(new SQLiteObject(db));
});
}
public deleteDatabase(config: SQLiteDatabaseConfig): Promise<any>{
return new Promise((resolve,reject)=>{
localStorage.removeItem("database");
});
};
}
@Injectable()
export class SQLiteObject {
_objectInstance: any;
constructor(_objectInstance: any) {
this._objectInstance = _objectInstance;
};
public create(config: SQLiteDatabaseConfig): Promise<SQLiteObject> {
var db;
console.log("Open Mock SQLite Database.");
var storeddb = localStorage.getItem("database");
var arr = storeddb.split(',');
if (storeddb) {
db = new SQL.Database(arr);
}
else {
db = new SQL.Database();
}
return new Promise((resolve, reject) => {
resolve(new SQLiteObject(db));
});
}
executeSql(statement: string, params: any): Promise<any> {
return new Promise((resolve, reject) => {
try {
var st = this._objectInstance.prepare(statement, params);
var rows: Array<any> = [];
while (st.step()) {
var row = st.getAsObject();
rows.push(row);
}
var payload = {
rows: {
item: function (i) {
return rows[i];
},
length: rows.length
},
rowsAffected: this._objectInstance.getRowsModified() || 0,
insertId: this._objectInstance.insertId || void 0
};
//save database after each sql query
var arr: ArrayBuffer = this._objectInstance.export();
localStorage.setItem("database", String(arr));
resolve(payload);
} catch (e) {
reject(e);
}
});
};
}
database creator
@Injectable()
export class ConnectionBddProvider {
public db: any = null;
listfavoris: FavorisModel[] = [];
favoris: FavorisModel = null;
constructor(public platform: Platform, private alertCtrl: AlertController, private serveur: ConnectionServerProvider, private device: Device) {
}
CreateBdd(): Promise<any> {
let sql: any;
let lParamSql: SQLiteDatabaseConfig = null;
if(this.device.isVirtual == false){
console.log("Real Device")
//--- sur device
sql = new SQLite();
lParamSql = { name: 'test.db', location: 'default'};
}
else{
console.log("Virtual Device")
sql = new SQLiteMock()
lParamSql = { name: 'test.db', location: 'default', createFromLocation: 1};
}
return sql.create(lParamSql)
.then((db: any) => {
this.db = db;
}).catch((error) => console.error(error));
}
CreateTMOBILEFAVORIS(): Promise<any> {
return this.db.executeSql(`CREATE TABLE IF NOT EXISTS TMOBILEFAVORIS (
IdFavoris integer PRIMARY KEY AUTOINCREMENT,
IdFavorisParent int,
Nom varchar(255),
Description varchar(4000),
FOREIGN KEY(IdFavorisParent) REFERENCES TMOBILEFAVORIS(IdFavoris) ON DELETE CASCADE
)`, {})
.then(() => console.log('create TMOBILEFAVORIS ok'))
.catch(error => console.error(error));
}
enableForeignKeys(): Promise<any> {
return this.db.executeSql('PRAGMA foreign_keys=ON', [])
.then((data) => {
console.log("PRAGMA maybe ok");
})
.catch((error) => {
console.error(error);
console.log("erreur pragma");
});
}
checkForeignKeysEnabled(): Promise<any> {
return this.db.executeSql('PRAGMA foreign_keys', [])
.then((data) => {
console.log("PRAGMA res: " + JSON.stringify(data));
})
.catch((error) => {
console.error(error);
console.log("erreur pragma");
});
}
}
App initialization
this.bddService.CreateBdd().then(() => {
this.bddService.enableForeignKeys().then(( ) => {
this.bddService.checkForeignKeysEnabled().then(( ) => {
this.bddService.CreateTMOBILEFAVORIS().then(() => {
this.serveur.calculTokenConnexion().then(() => {
this.nav.setRoot(FavorisPage);
})
})
})
})
});
When I create two instances of table tmobilefavoris, one parent and one child of the other, when I delete the parent, the child is not deleted on browser.