Thank you for your interest in my problem.
Here’s what I did:
seed.sql :
CREATE TABLE IF NOT EXISTS category(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descriptions TEXT,img TEXT);
INSERT or IGNORE INTO categorie VALUES (1, 'Tâches Professionelle', 'Description', '');
INSERT or IGNORE INTO categorie VALUES (2, 'Tâches Personnelle', 'Description', '');
INSERT or IGNORE INTO categorie VALUES (3, 'Sport', 'Description', '');
CREATE TABLE IF NOT EXISTS task(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,description TEXT,categoryId INTEGER);
INSERT or IGNORE INTO task(id, name, description, categoryId) VALUES (1, 'Finir mon application', 'Description', 1);
INSERT or IGNORE INTO task(id, name, description, categoryId) VALUES (2, 'Ajouter des fonctionnalitées', 'Description', 1);
INSERT or IGNORE INTO task(id, name, description, categoryId) VALUES (3, 'Faire les courses', 'Description', 2);
INSERT or IGNORE INTO task(id, name, description, categoryId) VALUES (4, 'Test', 'Description', 2);
INSERT or IGNORE INTO task(id, name, description, categoryId) VALUES (5, 'Lorem Ipsum', 'Description', 3);
INSERT or IGNORE INTO task(id, name, description, categoryId) VALUES (6, 'Exemple', 'Description', 3);
database.service.ts :
import { Platform } from '@ionic/angular';
import { Injectable } from '@angular/core';
import { SQLitePorter } from '@ionic-native/sqlite-porter/ngx';
import { HttpClient } from '@angular/common/http';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite/ngx';
import { BehaviorSubject, Observable } from 'rxjs';
export interface Cat {
id: number,
name: string,
descriptions: any[],
img: string
}
@Injectable({
providedIn: 'root'
})
export class DatabaseService {
private database: SQLiteObject;
private dbReady: BehaviorSubject<boolean> = new BehaviorSubject(false);
categories = new BehaviorSubject([]);
tasks = new BehaviorSubject([]);
constructor(private plt: Platform, private sqlitePorter: SQLitePorter, private sqlite: SQLite, private http: HttpClient) {
this.plt.ready().then(() => {
this.sqlite.create({
name: 'mydatabase.db',
location: 'default'
})
.then((db: SQLiteObject) => {
this.database = db;
this.seedDatabase();
});
});
}
seedDatabase() {
this.http.get('assets/seed.sql', { responseType: 'text'})
.subscribe(sql => {
this.sqlitePorter.importSqlToDb(this.database, sql)
.then(_ => {
this.loadCategories();
this.loadTasks();
this.dbReady.next(true);
})
.catch(e => console.error(e));
});
}
getDatabaseState() {
return this.dbReady.asObservable();
}
getCats(): Observable<Cat[]> {
return this.categories.asObservable();
}
getTasks(): Observable<any[]> {
return this.tasks.asObservable();
}
loadCategories() {
return this.database.executeSql('SELECT * FROM category', []).then(data => {
let categories: Cat[] = [];
if (data.rows.length > 0) {
for (var i = 0; i < data.rows.length; i++) {
let descriptions = [];
if (data.rows.item(i).descriptions != '') {
descriptions = JSON.parse(data.rows.item(i).descriptions);
}
categories.push({
id: data.rows.item(i).id,
name: data.rows.item(i).name,
descriptions: descriptions,
img: data.rows.item(i).img
});
}
}
this.categories.next(categories);
});
}
addCategory(name, descriptions, img) {
let data = [name, JSON.stringify(descriptions), img];
return this.database.executeSql('INSERT INTO category (name, descriptions, img) VALUES (?, ?, ?)', data).then(data => {
this.loadCategories();
});
}
getCategory(id): Promise<Cat> {
return this.database.executeSql('SELECT * FROM category WHERE id = ?', [id]).then(data => {
let descriptions = [];
if (data.rows.item(0).descriptions != '') {
descriptions = JSON.parse(data.rows.item(0).descriptions);
}
return {
id: data.rows.item(0).id,
name: data.rows.item(0).name,
descriptions: descriptions,
img: data.rows.item(0).img
}
});
}
deleteCategory(id) {
return this.database.executeSql('DELETE FROM category WHERE id = ?', [id]).then(_ => {
this.loadCategories();
this.loadTasks();
});
}
updateCategory(cat: Cat) {
let data = [cat.name, JSON.stringify(cat.descriptions), cat.img];
return this.database.executeSql(`UPDATE category SET name = ?, descriptions = ?, img = ? WHERE id = ${cat.id}`, data).then(data => {
this.loadCategories();
})
}
loadTasks() {
let query = 'SELECT task.name, task.description, task.id, task.name AS category FROM task JOIN category ON category.id = task.categoryId';
return this.database.executeSql(query, []).then(data => {
let tasks = [];
if (data.rows.length > 0) {
for (var i = 0; i < data.rows.length; i++) {
tasks.push({
description: data.rows.item(i).description,
name: data.rows.item(i).name,
id: data.rows.item(i).id,
category: data.rows.item(i).category,
});
}
}
this.tasks.next(tasks);
});
}
addTask(name, description, category) {
let data = [name, description, category];
return this.database.executeSql('INSERT INTO task (name, description, categoryId) VALUES (?, ?, ?)', data).then(data => {
this.loadTasks();
});
}
}
categories.page.ts :
import { DatabaseService, Cat } from './../../services/database.service';
import { Component, OnInit } from '@angular/core';
import { Observable } from 'rxjs';
@Component({
selector: 'app-categories',
templateUrl: './categories.page.html',
styleUrls: ['./categories.page.scss'],
})
export class CategoriesPage implements OnInit {
categories: Cat[] = [];
tasks: Observable<any[]>;
category = {};
task = {};
selectedView = 'cats';
constructor(private db: DatabaseService) { }
ngOnInit() {
this.db.getDatabaseState().subscribe(rdy => {
if (rdy) {
console.log('READY DB');
this.db.getCats().subscribe(cats => {
this.categories = cats;
})
this.tasks = this.db.getTasks();
}
});
}
addCategory() {
let descriptions = this.category['descriptions'].split(',');
descriptions = descriptions.map(description => description.trim());
this.db.addCategory(this.category['name'], descriptions, this.category['img'])
.then(_ => {
this.category = {};
});
}
addTask() {
this.db.addTask(this.task['name'], this.task['description'], this.task['category'])
.then(_ => {
this.task = {};
});
}
}
and my html :
<ion-header>
<ion-toolbar color="primary">
<ion-title>Catégories</ion-title>
</ion-toolbar>
</ion-header>
<ion-content padding>
<ion-segment [(ngModel)]="selectedView">
<ion-segment-button value="cats">
<ion-label>Catégories</ion-label>
</ion-segment-button>
<ion-segment-button value="tasks">
<ion-label>Tâches</ion-label>
</ion-segment-button>
</ion-segment>
<div [ngSwitch]="selectedView">
<div *ngSwitchCase="'cats'">
<ion-item>
<ion-label position="stacked">Nom de la catégorie</ion-label>
<ion-input [(ngModel)]="category.name" placeholder="Nom de la catégorie"></ion-input>
</ion-item>
<ion-item>
<ion-label position="stacked">Descripteur (séparés par des virgules)</ion-label>
<ion-input [(ngModel)]="category.descriptions" placeholder="Descripteurs"></ion-input>
</ion-item>
<ion-item>
<ion-label position="stacked">URL image</ion-label>
<ion-input [(ngModel)]="category.img" placeholder="https://..."></ion-input>
</ion-item>
<ion-button expand="block" (click)="addCategory()">Ajouter Infos Catégorie</ion-button>
<ion-list>
<ion-item button *ngFor="let cat of categories" [routerLink]="['/', 'categories', cat.id]">
<ion-avatar slot="start">
<img [src]="cat.img">
</ion-avatar>
<ion-label>
<h2>{{ cat.name }}</h2>
<p>{{ cat.descriptions }}</p>
</ion-label>
</ion-item>
</ion-list>
</div>
<div *ngSwitchCase="'tasks'">
<ion-item>
<ion-label position="stacked">Votre Tâche</ion-label>
<ion-input [(ngModel)]="task.name" placeholder="Votre tâche"></ion-input>
</ion-item>
<ion-item>
<ion-label position="stacked">Description</ion-label>
<ion-input [(ngModel)]="task.description" placeholder="Description"></ion-input>
</ion-item>
<ion-item>
<ion-label position="stacked">Catégorie</ion-label>
<ion-select [(ngModel)]="task.category">
<ion-select-option *ngFor="let cat of categories" [value]="cat.id">{{ cat.name }}</ion-select-option>
</ion-select>
</ion-item>
<ion-button expand="block" (click)="addTask()">Ajouter Tâche</ion-button>
<ion-list>
<ion-item *ngFor="let task of tasks | async">
<ion-label>
<h2>{{ task.name }}</h2>
<p>Catégorie : {{ task.category }}</p>
</ion-label>
</ion-item>
</ion-list>
</div>
</div>
</ion-content>
