“This is my fourth day of participating in the First Challenge 2022. For more details: First Challenge 2022.”

preface

With the Flutter_novel project moving into the bookshelf section, designing a framework that combines local and network co-storage and processing is now the first goal; One of the questions that this raises is:

How should local persistence be done

Of course, there are many, many answers to this question. 99.99% of us can pick our feet and name several options, but have you considered the differences, advantages and disadvantages?

In Flutter, the data storage schemes with high Like in dev Package are as follows:

• sqflite
• floor
• Drift
• Hive

So, let’s analyze it now;

First, we need to understand the basics:

What is SQL and what is NoSQL

The full name of SQL is Structured Query Language, or Structured Query Language. Of course, remember this full name in addition to force hanging with no, we generally understand it as: relational database;

NoSQL is a structured Query Language with No structured query language.

As a matter of fact, the specific meaning of this No seems to be uncertain. At least I have seen two opinions when LOOKING for information:

• One is the literal translation of the non-relational meaning mentioned above;
• Another way to interpret it is to take the word “No” apart and define “No” as an abbreviation for “not only,” which is not just relational;

But there is at least one thing we can agree on:

NoSQL is not based on relational tables;

So SQL knows is relational database this kind of thing, NoSQL is in a kind of posture to reflect?

There are four main NoSql types:

• Document database: Stores data in documents that resemble JSON objects. Each document contains paired fields and values.
• Key-value database: Each item contains keys and values. Values can usually only be retrieved by referring to keys, so learning how to query for a particular key-value pair is often straightforward. Key-value databases are ideal for use cases where you need to store large amounts of data but do not need to perform complex queries to retrieve the data.
• Wide column storage: Stores data in tables, rows, and dynamic columns. Wide column storage provides greater flexibility than a relational database because every row does not need to have the same column. Wide column storage is ideal for situations where you need to store large amounts of data and can predict query patterns.
• Graphical database: Stores data in nodes and edges. Nodes typically store information about people, places, and things, while edges store information about the relationships between nodes. Graphical databases are excellent in use cases where relationships need to be traversed to find patterns, such as social networks, fraud detection, and recommendation engines.

In general, key-value databases and document databases are the ones we deal with a lot;

Getting back to the point, what are the advantages and disadvantages of these data storage schemes in Flutter? Which one should I choose?

First of all, SQL is the first thing most people are exposed to

First sqflite:

Sqflite is a standard SQL database, which uses SQL statements to query data.

// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');

// Delete the database
await deleteDatabase(path);

// open the database
Database database = await openDatabase(path, version: 1,
    onCreate: (Database db, int version) async {
  // When creating the db, create the table
  await db.execute(
      'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
});

// Insert some records in a transaction
await database.transaction((txn) async {
  int id1 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
  print('inserted1: $id1');
  int id2 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES(? ,? ,?) '['another name'.12345678.3.1416]);
  print('inserted2: $id2');
});

// Update some record
int count = await database.rawUpdate(
    'UPDATE Test SET name = ? , value = ? WHERE name = ? '['updated name'.'9876'.'some name']);
print('updated: $count');

// Get the records
List<Map> list = await database.rawQuery('SELECT * FROM Test');
List<Map> expectedList = [
  {'name': 'updated name'.'id': 1.'value': 9876.'num': 456.789},
  {'name': 'another name'.'id': 2.'value': 12345678.'num': 3.1416}];print(list);
print(expectedList);
assert(const DeepCollectionEquality().equals(list, expectedList));

// Count the records
count = Sqflite
    .firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count == 2);

// Delete a record
count = await database
    .rawDelete('DELETE FROM Test WHERE name = ? '['another name']);
assert(count == 1);

// Close the database
await database.close();

Copy the code

It’s not really complicated. The first time YOU use it, it’s okay, right?

But people who have used this for a period of time, encounter a few changes to the structure or other requirements that lead to the upgrade of the database upgrade operation, will not feel that this thing is easy to use, maintenance costs are visible to the naked eye increase;

This is also a common problem with relational databases:

Relational database is suitable for fixed data structure, will not change too much; The data pattern is relatively uniform, not a little more this, a little less that;

In addition, according to the website I consulted:

The project delay is not actually to blame you, are predecessors to create the use of SQL world’s fault ～～～～

Looking at the floor

Floor is also SQL, but compared with SQflite, it does a lot of abstraction and encapsulation, especially the concept of DAO, which reminds me of the days when I used to write Android with greenDao

It’s much better than SQFlite, because it’s much easier to automatically generate code from a script based on annotations than to write it by hand:

Its use is divided into several steps:

Create Entity and create table

// entity/person.dart

import 'package:floor/floor.dart';

@entity
class Person {
  @primaryKey
  final int id;

  final String name;

  Person(this.id, this.name);
}

Copy the code

Create the DAO layer

// dao/person_dao.dart

import 'package:floor/floor.dart';

@dao
abstract class PersonDao {
  @Query('SELECT * FROM Person')
  Future<List<Person>> findAllPersons();

  @Query('SELECT * FROM Person WHERE id = :id') Stream<Person? > findPersonById(int id);

  @insert
  Future<void> insertPerson(Person person);
}

Copy the code

Create a database agent

// database.dart

// required package imports
import 'dart:async';
import 'package:floor/floor.dart';
import 'package:sqflite/sqflite.dart' as sqflite;

import 'dao/person_dao.dart';
import 'entity/person.dart';

part 'database.g.dart'; // the generated code will be there

@Database(version: 1, entities: [Person])
abstract class AppDatabase extends FloorDatabase {
  PersonDao get personDao;
}
Copy the code

4. Run the build_runner script and it is ready to use

But since it is SQL, the shortcomings mentioned above, there should be, as SQL is unavoidable;

Drift

The process for Drift is similar to floor, but it does a little more on the encapsulation side:

Now the main differences are as follows:

• Generate tables through inheritance and methods rather than entity classes:

import 'package:drift/drift.dart';

// assuming that your file is called filename.dart. This will give an error at first,
// but it's needed for drift to know about the generated code
part 'filename.g.dart';

// this will generate a table called "todos" for us. The rows of that table will
// be represented by a class called "Todo".
class Todos extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(min: 6, max: 32) (); TextColumnget content => text().named('body') (); IntColumnget category => integer().nullable()();
}

// This will make drift generate a class called "Category" to represent a row in this table.
// By default, "Categorie" would have been used because it only strips away the trailing "s"
// in the table name.
@DataClassName("Category")
class Categories extends Table {
  
  IntColumn get id => integer().autoIncrement()();
  TextColumn get description => text()();
}

// this annotation tells drift to prepare a database class that uses both of the
// tables we just defined. We'll see how to use that database class in a moment.
@DriftDatabase(tables: [Todos, Categories])
class MyDatabase {}Copy the code

Drift, of course, is still SQL

Now the pressure is on NoSQL

hive

SQL > create a relational table query statement

var box = Hive.box('products');

box.put('name'.'foo');

var name = box.get('name');

print('Product Name: $name');

Copy the code

If you’re going to put custom types, you’re going to put them in entity classes;

@HiveType(typeId: 0)
class Person extends HiveObject {

  @HiveField(0)
  String name;

  @HiveField(1)
  int age;
}
Copy the code

var box = await Hive.openBox('myBox');

varperson = Person() .. name ='Dave'
  ..age = 22;
box.add(person);

print(box.getAt(0)); // Dave - 22

person.age = 30;
person.save();

print(box.getAt(0)) // Dave - 30

Copy the code

As you can see, it’s very, very easy to use, it’s not so abstract to write classes, it doesn’t require scripts or anything like that;

And performance boosts SQLite:

So, Gul ‘dan, at what cost?

The existence of relational table improves the operation difficulty and reduces the query speed, but its existence is not meaningless.

If, for example, the relational table is like a housekeeper, customs, and a series of help you check, no relational table, there may be a lot of repeated or useless data; And because there is no relational table to constrain the data structure, there may be a lot of disorderly data in NOSQL database;

To summarize SQL and NOSQL:

• SQL:
  • Data requirements compliance and unified format, although the performance is worse than NOSQL, but its data can ensure the correct compliance with the requirements of the facts, but also bring modification problems, poor scalability;
  • Because of the existence of SQL query statement, it will not be too complicated in the complex multi-table joint query.
• No:
  • There is no special specification for storage, so it is very extensible;
  • Query speed because there is no need to go through the relational table, so the query is particularly fast;
  • But too much freedom can cause problems, such as the existence of large amounts of duplicate data; It may not be accurate to get the data for a particular indicator (two fake numbers will make you confused and hard to find out);
  • For multilevel query of complex cases, it is necessary to write their own logical judgment, there will be hundreds of if else, switch case that is unknown;

conclusion

Look at this, do you think you should make the next conclusion, which one is better?

In fact, as summarized above, SQL and NoSQL have their own advantages and disadvantages, even if the same NoSQL, because of technology selection and other factors there are different advantages and disadvantages;

Therefore, there is no such thing as one good or the other good, or the best data storage solution that meets the needs of the project and oneself;

PS: But if I had to pick one, I would not pick any of the four mentioned in the article. MMKV(although Like very few, Tencent is not bad reputation) is the best plan in my mind;

As the old key-value pair storage framework, its stability is great; In terms of performance that’s also at the Top of the native level; Personal feeling, general quality of storage query, MMKV is completely enough

(Now that you think about it, it seems that the client side, in general, is not complicated enough to use a relational database to store data relationships. IM is one, are there others?