TypeScript Storage.query方法代碼示例

        return new Promise<any>((resolve, reject) => {
            this.db = new Storage(SqlStorage, {
                name: 'ILIASMobile',
                backupFlag: SqlStorage.BACKUP_LOCAL,
                existingDatabase: true
            });
            /*only dev *//*
            this.db.query('DROP TABLE IF EXISTS users');
            this.db.query('DROP TABLE IF EXISTS courses');
            this.db.query('DROP TABLE IF EXISTS learning_modules');
            this.db.query('DROP TABLE IF EXISTS chapters');
            this.db.query('DROP TABLE IF EXISTS pages');*/

            this.db.query(`CREATE TABLE IF NOT EXISTS users (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        login TEXT,
                        password TEXT,
                        firstname TEXT,
                        lastname TEXT,
                        avatar TEXT
                       );`
            );

            this.db.query(`CREATE TABLE IF NOT EXISTS courses (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        title TEXT,
                        description TEXT,
                        ref_id INTEGER,
                        user_id INTEGER REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE       
                       );`
            );

            this.db.query(`CREATE TABLE IF NOT EXISTS learning_modules (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        title TEXT,
                        description TEXT,
                        ref_id INTEGER,
                        on_desktop INTEGER DEFAULT 0,
                        course_id INTEGER REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
                       );`
            );

            this.db.query(`CREATE TABLE IF NOT EXISTS chapters (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        title TEXT,
                        export_id INTEGER,
                        lm_id INTEGER REFERENCES learning_modules(id) ON UPDATE CASCADE ON DELETE CASCADE
                       );`
            );

            this.db.query(`CREATE TABLE IF NOT EXISTS pages (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        title TEXT,
                        content TEXT,
                        export_id INTEGER,
                        chapter_id INTEGER REFERENCES chapters(id) ON UPDATE CASCADE ON DELETE CASCADE
                       );`
            );
            resolve();
        });

 constructor(private http: Http) {
   this.storage = new Storage(SqlStorage);
   //CREATE GAMES
   this.storage.query('CREATE TABLE IF NOT EXISTS games (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, type TEXT, target INTEGER, final_event TEXT)');
   //CREATE MATCHES
   this.storage.query('CREATE TABLE IF NOT EXISTS matches (id INTEGER PRIMARY KEY AUTOINCREMENT, game_id INTEGER, date TEXT)');
   //CREATE PLAYERS
   this.storage.query('CREATE TABLE IF NOT EXISTS players (id INTEGER PRIMARY KEY AUTOINCREMENT, match_id INTEGER, player TEXT)');
   //CREATE POINTS
   this.storage.query('CREATE TABLE IF NOT EXISTS points (id INTEGER PRIMARY KEY AUTOINCREMENT, player_id INTEGER, point INTEGER)');
 }

 .then((data) => {
   this.storage.query('CREATE TABLE IF NOT EXISTS Characters_has_Skill (' +
     'Characters_idCharacters INTEGER,' +
     'Skill_idSkill INTEGER,' +
     'level INTEGER UNSIGNED NULL,' +
     'FOREIGN KEY(Characters_idCharacters) REFERENCES Characters(_id),' +
     'FOREIGN KEY(Skill_idSkill) REFERENCES Skill(_id)' +
     ');').then((data) => {
       this.storage.query('CREATE TABLE IF NOT EXISTS Characters_has_Secundary_atribute (' +
         'Characters_idCharacters INTEGER,' +
         'Secundary_atribute_idSecundary_atribute INTEGER,' +
         'value INTEGER,' +
         'FOREIGN KEY(Characters_idCharacters) REFERENCES Characters(_id),' +
         'FOREIGN KEY(Secundary_atribute_idSecundary_atribute) REFERENCES Secundary_atribute(_id)' +
         ');').then((data) => {
           this.storage.query('CREATE TABLE IF NOT EXISTS Characters_has_Base_atribute (' +
             'Characters_idCharacters INTEGER,' +
             'Base_atribute_idBase_atribute INTEGER,' +
             'value INTEGER,' +
             'FOREIGN KEY(Characters_idCharacters) REFERENCES Characters(_id),' +
             'FOREIGN KEY(Base_atribute_idBase_atribute) REFERENCES Base_atribute(_id)' +
             ');').then((data) => {
               console.log("DB OK");
             }, (error) => {
               console.log("Characters_has_Base_atribute ERROR -> " + JSON.stringify(error.err), error);
             });
         }, (error) => {
           console.log("Characters_has_Secundary_atribute ERROR -> " + JSON.stringify(error.err), error);
         });
     }, (error) => {
       console.log("Characters_has_Skill ERROR -> " + JSON.stringify(error.err), error);
     });
 }, (error) => {

    getListarDados() {
	 
	    let storage = new Storage(SqlStorage);
	    var lista = [];
	   
	    storage
	    	.query("SELECT * FROM coleta")
	    	.then((data) => {
	      		
	      		for (var i = 0; i < data.res.rows.length; i++) {
	       			
	       			let row = data.res.rows.item(i)
	       			//console.log(row);	       			
	       			let item = {
	       			 	    'id': row.id,
	       			 	    'peso': row.peso,
	       			 	    'pa': row.pa,
	       			 	    'hgt': row.hgt,
	       			 	    'controle': row.controle
	        		};
	               
	               lista.push(item);
	               //console.log(item.id);
	            //    console.log('l');
	            }
	            //sucessCalback(lista);
	            //cb(lista)
	
	         }, (error) => {
	      		console.log("Erro na criação da tabela " + JSON.stringify(error.err));
	         });
	    	
	    	return lista;
	}

 update(table: string,columns: Array<string>, values: Array<any>, id : number) {
   var insertCode = 'UPDATE ' + table + ' SET ';
   function stringyForSQL(value){
     if(typeof value === 'string'){
       return "'"+value+"'";
     }else{
       return value;
     }
   }
   for (var i = 0; i < columns.length; i++) {
     if (i === (values.length-1)) {
       insertCode += columns[i] + " = " + stringyForSQL(values[i])
     } else {
       insertCode += columns[i] + " = " + stringyForSQL(values[i]) + ", "
     }
   }
   insertCode += ' WHERE '+id;
   console.log(insertCode)
   return this.storage.query(insertCode)
     .then((data) => {
       console.log(data);
       return true;
     }, (error) => {
       console.error(error);
       return false;
     });
 }

    this.storage.query("SELECT count(*) AS EXIST FROM sqlite_master WHERE type='table' AND name='config'").then((data) => {

        if (data.res.rows[0]['EXIST'] == 1){
            //Exist table config
            this.storage.query("SELECT * FROM config").then((config) => {

            //Reconfiguring the app
            if(config.res.rows.length === 0){
              this._logger.log("Reconfiguring");
              //No Exists table config, start tutorial & config
              nav.push(ServerData);
            }else{
              //App Ready!
              this._logger.log("App ready to run!");
              nav.push(ServerData);

            }

           }, (error) => {
              //Put Alert < ---
              console.log("ERROR -> " + JSON.stringify(error.err));
          });
        } else if (data.res.rows[0]['EXIST'] == 0 ){
          //No Exists table config, start tutorial & config
          this._logger.log("Initial Config");
          nav.push(ServerData);
        }

    },(error) => {

 /**
  * Get user's learning modules saved on desktop
  * 
  * @param {UserData} userdata Object that contains required information of user to find necessary modules
  * @returns {Promise<LearningModule[]>} Promise that resolves with list of desktop's learning modules or rejects with error 
  */
 public getDesktopObjects(userdata: UserData): Promise<LearningModule[]> {
     var learningModules: LearningModule[] = [];
     return this.db.query(`SELECT * FROM learning_modules WHERE on_desktop=1`).then((data) => {
         var results = data.res.rows;
         for (let i = 0, length = results.length; i < length; i++) {
             learningModules.push({
                 lm_id: results.item(i).id,
                 title: results.item(i).title,
                 description: results.item(i).description,
                 ref_id: results.item(i).ref_id,
                 onDesktop: (results.item(i).on_desktop == 1) ? true : false,
                 status: Status.Local,
                 chapters: null,
                 course: null,
                 course_id: results.item(i).course_id
             });
         }
         return this.db.query(`SELECT id FROM courses WHERE user_id=?`, [userdata.user_id]);            
     }).then((data) => { 
         var results = data.res.rows;
         var ids = [];
         for (let i = 0, length = results.length; i < length; i++) {
             ids.push(results.item(i).id);
         }
         learningModules.filter(lm => ids.indexOf(lm.course_id) !== -1);
         return Promise.resolve(learningModules);
     }).then(() => { 
         return Promise.all(learningModules.map((lm) => this.getChapters(lm)));
     }).then(() => {
         return learningModules;
     }).catch((error) => {
         return Promise.reject(error);
     })
 }

 executeQuery(query:string, parameters:any[]){
     if ( ! parameters ){
         parameters = [];
     }
     return this.storage.query(query, parameters).then(function(response){
         return response.res;
     });
 }

 return this.checkUser(userdata).then((hasRecord) => {
     if (!hasRecord) {
         return this.db.query(`INSERT INTO users(login, password, firstname, lastname, avatar) VALUES (?,?,?,?,?)`, [
             userdata.login, userdata.password, userdata.firstname, userdata.lastname, userdata.avatar
         ]).then(() => {
             return this.db.query(`select seq from sqlite_sequence where name="users"`)
         }).then((data) => {
             userdata.user_id = data.res.rows.item(0).seq;
             return userdata;
         });
     } else
         return this.db.query(`UPDATE users SET firstname = ?, lastname = ?, avatar = ? WHERE id = ?`, [
             userdata.firstname, userdata.lastname, userdata.avatar, userdata.user_id
         ]).then(() => {
             return userdata;
         })
 }).catch((error) => {

 delete(conta, successCallback) {
   let storage = new Storage(SqlStorage);
   storage.query('DELETE FROM contas WHERE id=?', [conta.id]).then((data) => {
     successCallback(data);
   }, (error) => {
     console.log('error: ', JSON.stringify(error.err));
   });
 }