PRAGMA foreign_keys=ON not working with SQL.js

488 views Asked by At

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.

0

There are 0 answers