Firestore, MySQL & Serverpod

いろいろへめぐってきたので、まとめてみる

個人開発のために、いろんなDatabaseを試した。
同じ「全選択してListTileで表示」のコードが３種類できたので、比較してみる。

まずはFirebaseFirestore

ちょうど去年の今ごろ使っていた。
先月、共同開発で久しぶりに書いた。こんな感じ

  List<Map<String, dynamic>> displayList = [];

  Future<void> _readAll() async {
    final QuerySnapshot snapshot = 
        await FirebaseFirestore.instance.collection('events')
            .orderBy('year')
            .get();
    
    final List<Map<String, dynamic>> list = snapshot.docs.map((DocumentSnapshot document) {
      Map<String, dynamic> data = document.data() as Map<String, dynamic>;
      final int year = data['year'];
      final String date = data['date'];
      final String name = data['name'];
      final String country = data['country'];

      return {'year': year, 'date': date, 'name': name, 'country': country};
    }).toList();

    setState(() {
      displayList = list;
    });
  }

  @override
  Widget build(BuildContext context) {
  ***
    return Scaffold(
      appBar: AppBar(
        title: const Text('SHOW ALL'),
      ),
      body: Center(
          child: SingleChildScrollView(
            child: Column(children: displayList.map<Widget>((data) {
              return Padding(
                  padding: const EdgeInsets.fromLTRB(350, 10, 350, 10),
                child: Card(
                  color: const Color(0xFFe6e6fa),
                  elevation: 30,
                  child: Padding(
                    padding: const EdgeInsets.all(8.0),
                    child: ListTile(
                      leading: Text(data['date']?? ""),
                      title: Text(data['name']?? ""),
                      subtitle: Text(data['country']?? ""),
                    ),
                  ),
                ),
              );
            }).toList(),

検索機能を充実させたくてMySQLへ

去年の秋から冬にかけて書いていたcode。

  List<Map<String, String>> displayList = [];
  String? isSelectedCalendar = "HistoricalYears";

  Future<void> _readAll() async {
    print("Connecting to mysql server...");

    // create connection
    final conn = await MySQLConnection.createConnection(
      host: HOST,
      port: PORT,
      userName: NAME,
      password: PASSWORD,
      databaseName: DATABASE,
    );

    await conn.connect();

    // make query
    var result = await conn.execute("SELECT * FROM $isSelectedCalendar ORDER BY annee ASC");

    List<Map<String, String>> list = [];
    for (final row in result.rows) {
      final data = {
        'selectedId': row.colAt(0)!,
        'selectedAnnee': row.colAt(1)!,
        'selectedAffair': row.colAt(2)!,
        'selectedPays': row.colAt(3)!
      };
      list.add(data);
    }
    setState(() {
      displayList = list;
    });

    // close all connections
    await conn.close();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Center(
          child: Column(
            children: [
*****
              Expanded(
                flex: 9,
                child: SingleChildScrollView(
                  child:
                  Column(children: displayList.map<Widget>((data) {
                    return Padding(
                      padding: const EdgeInsets.fromLTRB(150, 0, 150, 0),
                      child: Card(color: const Color(0x4D6b8e23),
                          elevation: 10,
                          child: Padding(
                            padding: const EdgeInsets.all(8.0),
                            child: ListTile(
                              leading: Text(data['selectedYear']?? ""),
                              title: Text(data['selectedName']?? ""),
                              subtitle: Text(data['selectedCountry']?? ""),
                              trailing: TextButton(
                                child: const Text("detail"),
                                onPressed: () {
                                  Navigator.push<int>(
                                    context,
                                    MaterialPageRoute(builder: (context) => DetailPage(title: data['selectedId']??"")),
                                  );
                                },
                              ),
                            ),
                          )
                      ),
                    );
                  }
                  ).toList()
                  ),

そしてServerpod

DBはPostgreSQLだが、直接queryを書くのではなく、serverpodのendpointで作った関数をclient経由で呼んでいる。
前回まで使っていたSingleChildScrollViewになっていない。

import 'package:acorn_client/acorn_client.dart';
import 'package:flutter/material.dart';
import 'package:serverpod_flutter/serverpod_flutter.dart';

var client = Client('http://localhost:8080/')
  ..connectivityMonitor = FlutterConnectivityMonitor();

class ReadAllPage extends StatefulWidget {
  const ReadAllPage({Key? key, required this.title}) : super(key: key);
  final String title;

  @override
  State<ReadAllPage> createState() => ReadAllPageState();
}

class ReadAllPageState extends State<ReadAllPage> {
  List<Principal> _principal = [];

  @override
  void initState() {
    super.initState();
    fetchPrincipal();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: ListView.builder(
          itemCount: _principal.length,
          itemBuilder: (context, index) {
            return Padding(
              padding: const EdgeInsets.fromLTRB(350, 10, 350, 10),
              child: Card(
                color: const Color(0xFFe6e6fa),
                child: Padding(
                  padding: const EdgeInsets.all(8.0),
                  child: ListTile(
                    leading: Text(_principal[index].annee.toString()),
                    title: Text(_principal[index].affair),
                    subtitle: Text(_principal[index].pays),
                  ),
                ),
              ),
            );
          },
        ),
      ),
    );
  }

  fetchPrincipal() async {
    try {
      _principal = await client.principal.getPrincipal();
      setState(() {});
    } on Exception catch (e) {
      debugPrint('$e');
    }
  }
}