Flutter, sqflite, and escaping quotes with SQL INSERT and UPDATE statements

CREATE TABLE quotes (
    id INTEGER PRIMARY KEY,
    quote TEXT UNIQUE,
    author TEXT,
    date_last_touched INTEGER DEFAULT (cast(strftime('%s','now') as int))
)

// INSERT (return the insert id)
Future<int> insertQuote(Quote quote) async {
    Database db = await instance.database;
    var row = {
        'quote'  : quote.quote,
        'author' : quote.author
    };
    return await db.insert('quotes', row);
}

// UPDATE (returns the number of rows updated)
Future<int> updateQuote(Quote quote) async {
    Database db = await instance.database;

    var row = {
        'id'     : quote.id,
        'quote'  : quote.quote,
        'author' : quote.author
    };

    return await db.update(
        'quotes', 
        row,
        where: 'id = ?', 
        whereArgs: [quote.id]
    );
}

import 'dart:io';
import 'dart:core';
import 'package:flutter/material.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import 'data_model.dart';

class DatabaseHelper {
    
    static final _databaseName = "JustBe.db";
    static final _databaseVersion = 1;

    // make this a singleton class
    DatabaseHelper._privateConstructor();
    static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

    // only have a single app-wide reference (getter) to the database
    static Database _database;
    Future<Database> get database async {  //GETTER
        if (_database != null) return _database;
        // lazily instantiate the db the first time it is accessed
        _database = await _initDatabase();
        return _database;
    }

    // this opens the database (and creates it if it doesn’t exist)
    _initDatabase() async {
        Directory documentsDirectory = await getApplicationDocumentsDirectory();
        String path = join(documentsDirectory.path, _databaseName);
        Database foo = await openDatabase(
            path,
            version: _databaseVersion,
            onOpen: (instance) {},
            onCreate: _onCreate
        );
        return foo;
    }

    // SQL code to create the database table
    Future _onCreate(Database db, int version) async {
        // note that the date/time field is stored as an integer because
        // that’s all you can do with SQLite. so this field is the Unix
        // epoch time.
        var createString = '''
            CREATE TABLE quotes (
                id INTEGER PRIMARY KEY,
                quote TEXT UNIQUE,
                author TEXT,
                date_last_touched INTEGER DEFAULT (cast(strftime('%s','now') as int))
            )
        ''';
        await db.execute(createString);

        //TODO do i need `await` before each of these?
        db.execute("insert into quotes (quote, author) values ('Be happy in the moment, that’s enough. Each moment is all we need, not more.', 'Mother Teresa')");
        db.execute("insert into quotes (quote, author) values ('Be here now', 'Ram Dass')");
        db.execute("insert into quotes (quote, author) values ('Do every act of your life as though it were the very last act of your life.', 'Marcus Aurelius')");
        // many more INSERTs here ...
    }
    

    /// INSERT
    Future<int> insertQuote(Quote quote) async {
        Database db = await instance.database;
        var row = {
            'quote'  : quote.quote,
            'author' : quote.author
        };
        return await db.insert('quotes', row);  //the id
    }

    /// SELECT ALL
    Future<List<Quote>> selectAllQuotes() async {
        Database db = await instance.database;
        var result = await db.rawQuery('SELECT * FROM quotes ORDER BY date_last_touched DESC');
        var quotes = result.map((qAsMap) => Quote.fromMap(qAsMap));
        return quotes.toList();
    }

    /// SELECT ALL BY ID
    Future<List<int>> selectAllQuoteIds() async {
        Database db = await instance.database;
        var result = await db.rawQuery('SELECT id FROM quotes');
        var ids = result.map((qAsMap) => _getIdFromMap(qAsMap));
        return ids.toList();
    }

    int _getIdFromMap(Map<String, dynamic> qMap) {
        return qMap['id'];
    }

    /// NOTE: can return `null`
    Future<Quote> selectQuote(int id) async {
        Database db = await instance.database;
        var results = await db.rawQuery('SELECT * FROM quotes WHERE id = $id');
        if (results.length > 0) {
            return Quote.fromMap(results.first);
        }
        return null;
    }

    /// UPDATE. Returns the number of rows updated.
    Future<int> updateQuote(Quote quote) async {
        Database db = await instance.database;

        var row = {
            'id'     : quote.id,
            'quote'  : quote.quote,
            'author' : quote.author
        };

        // returns num rows updated
        return await db.update(
            'quotes', 
            row,
            where: 'id = ?', 
            whereArgs: [quote.id]
        );
    }

    /// DELETE
    Future<int> deleteQuote(int id) async {
        Database db = await instance.database;
        return await db.rawDelete('DELETE FROM quotes WHERE id = $id');
    }

    Future<int> rowCount() async {
        debugPrint('ROW COUNT CALLED');
        Database db = await instance.database;
        return Sqflite.firstIntValue(await db.rawQuery('SELECT COUNT(1) FROM quotes'));
    }

}