json_sqlite 0.0.6
json_sqlite: ^0.0.6 copied to clipboard
A JSON-oriented Dart interface to SQLite, with indexes and full-text search.
example/json_sqlite_example.dart
//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\
import "dart:io";
import "package:auxiliary/auxiliary.dart";
import "package:json_sqlite/json_sqlite.dart";
import "package:meta/meta.dart";
//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\
void theSimplestUseCaseExample(String? path) {
log.info("----------------------------------------");
SQLite sqlite = createDb(path);
sqlite.initialize();
populateDb(sqlite);
log.info("----------------------------------------");
swapApartments(sqlite, "Taylor, Michael", "Miller, Sarah");
log.info("----------------------------------------");
removePerson(sqlite, "Smith, Mary");
log.info("----------------------------------------");
findAllBySurname(sqlite, "Smith");
sqlite.deactivate();
log.info("----------------------------------------");
}
SQLite createDb(String? filename) {
if (filename != null) {
if (File(filename).existsSync())
log.info("Opening an existing database at `$filename`...");
else
log.info("Creating a new database at `$filename`...");
return SQLite(filename);
}
log.info("The database was created in memory.");
return SQLite.inMemory();
}
void populateDb(SQLite sqlite) {
Transaction transaction = Transaction()
..insert(
KeyId("Anderson, Laura"), ////
"Kelmara, Tolmsby, Thistlemere Road, 56, 12",
)
..insert(
KeyId("Brown, David"), ////
"Brevonia, Vaucrenne, Rue Tessemont, 9B, 3",
)
..insert(
KeyId("Garcia, Maria"), ////
"Valdoria, Merenza, Corso Ardemonte, 120, 24",
)
..insert(
KeyId("Johnson, Robert"), ////
"Brevonia, Rouvenne, Avenue Perrelin, 33, 7",
)
..insert(
KeyId("Miller, Sarah"), ////
"Orsavia, Teveldorf, Orrenweg, 14, 6",
)
..insert(
KeyId("Smith, Emma"), ////
"Valdoria, Castelvane, Via Lorvenza, 17, 8",
)
..insert(
KeyId("Smith, John"), ////
"Valdoria, Castelvane, Via Lorvenza, 17, 8",
)
..insert(
KeyId("Smith, John (not related to the other Smiths)"), ////
"Orsavia, Hallenfurt, Marnauer Strasse, 205, 31",
)
..insert(
KeyId("Smith, Mary"), ////
"Valdoria, Castelvane, Via Lorvenza, 17, 8",
)
..insert(
KeyId("Taylor, Michael"), ////
"Kelmara, Wexmoor, Quillow Street, 43, 5",
);
int applied = sqlite.apply(transaction);
if (transaction.length case var n when n != applied)
log.info("The number of entries already present is ${n - applied}.");
log.info("The number of entries added is $applied.");
}
String? getValue(SQLite sqlite, String key) =>
sqlite.select(KeyId(key))?.value as String?;
void printByKeys(SQLite sqlite, List<String> keys, String joiner) {
for (String key in keys)
if (getValue(sqlite, key) case var value?)
log.info("`$key` $joiner at `$value`.");
else
log.info("`$key` was not found.");
}
void swapApartments(SQLite sqlite, String person1, String person2) {
log.info("Swapping apartments of `$person1` and `$person2`...");
printByKeys(sqlite, [person1, person2], "lived");
String? appartment1 = getValue(sqlite, person1);
String? appartment2 = getValue(sqlite, person2);
int applied = sqlite.apply(
Transaction()
..update(KeyId(person1), appartment2!)
..update(KeyId(person2), appartment1!),
);
log.info("The number of updated entries is $applied.");
printByKeys(sqlite, [person1, person2], "now lives");
}
void removePerson(SQLite sqlite, String person) {
log.info("Removing an entry for `$person`...");
printByKeys(sqlite, [person], "lived");
int applied = sqlite.apply(Transaction()..delete(KeyId(person)));
String not = applied == 1 ? "" : " NOT";
log.info("The entry for `$person` was$not removed; verifying...");
printByKeys(sqlite, [person], "lives");
}
void findAllBySurname(SQLite sqlite, String surname) {
log.info("Finding all entries with the surname `$surname`...");
QueryBySurname query = QueryBySurname(sqlite);
List<String> result = query.run(surname + ",*");
query.deactivate();
printByKeys(sqlite, result, "lives");
}
//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\
base class QueryBySurname extends SQLiteQuery {
QueryBySurname(super.sqlite);
List<String> run(String surname) {
checkActive();
QueryParameters parameters = {_parameter: surname};
QueryRows rows = sqlite.query(queryId, parameters: parameters);
return rows.map((QueryRow r) => r.id.keyid as String).xToList();
}
@protected
@override
QueryId prepare() =>
sqlite.prepare(where: SqlI(columnKeyId).glob(SqlP(_parameter)));
static const String _parameter = "@surname";
}
//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\
void main(List<String> args) {
dartSetupLogging("main");
theSimplestUseCaseExample(args.singleOrNull);
}
//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\