sqljocky5 2.2.1

  • Readme
  • Changelog
  • Example
  • Installing
  • 68

SQLJocky5 #

MySQL client for Dart.

Creating a connection #

  var s = ConnectionSettings(
    user: "root",
    password: "dart_jaguar",
    host: "localhost",
    port: 3306,
    db: "example",
  );
  var conn = await MySqlConnection.connect(s);

Closing a connection #

  await conn.close();

Execute a query #

Results results = await conn.execute('select name, email from users');

Results is an iterable of Row. Columns can be accessed from Row using integer index or by name.

results.forEach((Row row) {
  // Access columns by index
  print('Name: ${row[0]}, email: ${row[1]}');
  // Access columns by name
  print('Name: ${row.name}, email: ${row.email}');
});

Prepared query #

await conn.prepared('insert into users (name, email, age) values (?, ?, ?)',
  ['Bob', 'bob@bob.com', 25]);

Insert id #

An insert query's results will be empty, but will have an id if there was an auto-increment column in the table:

print("New user's id: ${result.insertId}");

Prepared multiple queries #

var results = await query.preparedMulti(
  'insert into users (name, email, age) values (?, ?, ?)',
  [['Bob', 'bob@bob.com', 25],
   ['Bill', 'bill@bill.com', 26],
   ['Joe', 'joe@joe.com', 37]]);

Transactions #

Transaction trans = await pool.begin();
try {
  var result1 = await trans.execute('...');
  var result2 = await trans.execute('...');
  await trans.commit();
} catch(e) {
  await trans.rollback();
}

Safe transaction #

await pool.transaction((trans) {
  var result1 = await trans.execute('...');
  var result2 = await trans.execute('...');
});

TODO #

  • Compression
  • COM_SEND_LONG_DATA
  • CLIENT_MULTI_STATEMENTS and CLIENT_MULTI_RESULTS for stored procedures
  • Better handling of various data types, especially BLOBs, which behave differently when using straight queries and prepared queries.
  • Implement the rest of mysql's commands
  • Handle character sets properly? Currently defaults to UTF8 for the connection character set. Is it necessary to support anything else?
  • Improve performance where possible
  • Geometry type
  • Decimal type should probably use a bigdecimal type of some sort
  • MySQL 4 types (old decimal, anything else?)
  • Test against multiple mysql versions

Changelog #

2.2.1 #

  • Statements are queued

2.2.0 #

  • Better transactions

example/example.dart

import 'dart:async';
import 'package:sqljocky5/sqljocky.dart';

/// Drops the tables if they already exist
Future<void> dropTables(MySqlConnection conn) async {
  print("Dropping tables ...");
  await conn.execute("DROP TABLE IF EXISTS pets, people");
  print("Dropped tables!");
}

Future<void> createTables(MySqlConnection conn) async {
  print("Creating tables ...");
  await conn.execute('CREATE TABLE people (id INTEGER NOT NULL auto_increment, '
      'name VARCHAR(255), '
      'age INTEGER, '
      'PRIMARY KEY (id))');
  await conn.execute('CREATE TABLE pets (id INTEGER NOT NULL auto_increment, '
      'name VARCHAR(255), '
      'species TEXT, '
      'owner_id INTEGER, '
      'PRIMARY KEY (id),'
      'FOREIGN KEY (owner_id) REFERENCES people (id))');
  print("Created table!");
}

Future<void> insertRows(MySqlConnection conn) async {
  print("Inserting rows ...");
  List<StreamedResults> r1 = await (await conn
          .preparedWithAll("INSERT INTO people (name, age) VALUES (?, ?)", [
    ["Dave", 15],
    ["John", 16],
    ["Mavis", 93],
  ]))
      .toList();
  print("People table insert ids: " + r1.map((r) => r.insertId).toString());
  List<StreamedResults> r2 = await (await conn.preparedWithAll(
          "INSERT INTO pets (name, species, owner_id) VALUES (?, ?, ?)", [
    ["Rover", "Dog", 1],
    ["Daisy", "Cow", 2],
    ["Spot", "Dog", 2]
  ]))
      .toList();
  print("Pet table insert ids: " + r2.map((r) => r.insertId).toString());
  print("Rows inserted!");
}

Future<void> readData(MySqlConnection conn) async {
  Results result = await (await conn
          .execute('SELECT p.id, p.name, p.age, t.name AS pet, t.species '
              'FROM people p '
              'LEFT JOIN pets t ON t.owner_id = p.id'))
      .deStream();
  print(result);
  print(result.map((r) => r.byName('name')));
}

main() async {
  var s = ConnectionSettings(
    user: "root",
    password: "dart_jaguar",
    host: "localhost",
    port: 3306,
    db: "example",
  );

  // create a connection
  print("Opening connection ...");
  var conn = await MySqlConnection.connect(s);
  print("Opened connection!");

  await dropTables(conn);
  await createTables(conn);
  await insertRows(conn);
  await readData(conn);

  await conn.close();
}

Use this package as a library

1. Depend on it

Add this to your package's pubspec.yaml file:


dependencies:
  sqljocky5: ^2.2.1

2. Install it

You can install packages from the command line:

with pub:


$ pub get

with Flutter:


$ flutter pub get

Alternatively, your editor might support pub get or flutter pub get. Check the docs for your editor to learn more.

3. Import it

Now in your Dart code, you can use:


import 'package:sqljocky5/auth/auth_handler.dart';
import 'package:sqljocky5/auth/character_set.dart';
import 'package:sqljocky5/auth/handshake_handler.dart';
import 'package:sqljocky5/auth/ssl_handler.dart';
import 'package:sqljocky5/common/logging.dart';
import 'package:sqljocky5/comm/buffered_socket.dart';
import 'package:sqljocky5/comm/comm.dart';
import 'package:sqljocky5/comm/common.dart';
import 'package:sqljocky5/comm/receiver.dart';
import 'package:sqljocky5/comm/sender.dart';
import 'package:sqljocky5/comm/task_queue.dart';
import 'package:sqljocky5/connection/connection.dart';
import 'package:sqljocky5/connection/impl.dart';
import 'package:sqljocky5/connection/settings.dart';
import 'package:sqljocky5/constants.dart';
import 'package:sqljocky5/exceptions/client_error.dart';
import 'package:sqljocky5/exceptions/exceptions.dart';
import 'package:sqljocky5/exceptions/mysql_exception.dart';
import 'package:sqljocky5/exceptions/protocol_error.dart';
import 'package:sqljocky5/handlers/debug_handler.dart';
import 'package:sqljocky5/handlers/handler.dart';
import 'package:sqljocky5/handlers/ok_packet.dart';
import 'package:sqljocky5/handlers/parameter_packet.dart';
import 'package:sqljocky5/handlers/ping_handler.dart';
import 'package:sqljocky5/handlers/quit_handler.dart';
import 'package:sqljocky5/handlers/use_db_handler.dart';
import 'package:sqljocky5/prepared_statements/close_statement_handler.dart';
import 'package:sqljocky5/prepared_statements/execute_query_handler.dart';
import 'package:sqljocky5/prepared_statements/prepared_query.dart';
import 'package:sqljocky5/prepared_statements/prepare_handler.dart';
import 'package:sqljocky5/prepared_statements/prepare_ok_packet.dart';
import 'package:sqljocky5/query/query_stream_handler.dart';
import 'package:sqljocky5/query/result_set_header_packet.dart';
import 'package:sqljocky5/results/binary_data_packet.dart';
import 'package:sqljocky5/results/blob.dart';
import 'package:sqljocky5/results/field.dart';
import 'package:sqljocky5/results/results.dart';
import 'package:sqljocky5/results/row.dart';
import 'package:sqljocky5/results/standard_data_packet.dart';
import 'package:sqljocky5/sqljocky.dart';
import 'package:sqljocky5/utils/buffer.dart';
  
Popularity:
Describes how popular the package is relative to other packages. [more]
83
Health:
Code health derived from static analysis. [more]
42
Maintenance:
Reflects how tidy and up-to-date the package is. [more]
70
Overall:
Weighted score of the above. [more]
68
Learn more about scoring.

We analyzed this package on Jan 16, 2020, and provided a score, details, and suggestions below. Analysis was completed with status completed using:

  • Dart: 2.7.0
  • pana: 0.13.4

Health issues and suggestions

Fix lib/common/logging.dart. (-44.03 points)

Analysis of lib/common/logging.dart failed with 2 errors, 1 hint:

line 1 col 8: Target of URI doesn't exist: 'package:logging/logging.dart'.

line 3 col 20: Undefined class 'Logger'.

line 3 col 16: Unnecessary new keyword.

Fix lib/prepared_statements/execute_query_handler.dart. (-5.84 points)

Analysis of lib/prepared_statements/execute_query_handler.dart reported 12 hints, including:

line 38 col 27: Don't type annotate initializing formals.

line 38 col 48: Don't type annotate initializing formals.

line 44 col 17: Unnecessary new keyword.

line 46 col 22: Unnecessary new keyword.

line 240 col 19: Unnecessary new keyword.

Fix lib/auth/auth_handler.dart. (-4.89 points)

Analysis of lib/auth/auth_handler.dart reported 10 hints, including:

line 22 col 7: Don't type annotate initializing formals.

line 23 col 7: Don't type annotate initializing formals.

line 24 col 7: Don't type annotate initializing formals.

line 25 col 7: Don't type annotate initializing formals.

line 26 col 7: Don't type annotate initializing formals.

Fix additional 24 files with analysis or formatting issues. (-46.40 points)

Additional issues in the following files:

  • lib/comm/buffered_socket.dart (10 hints)
  • lib/connection/settings.dart (10 hints)
  • lib/results/results.dart (9 hints)
  • lib/auth/handshake_handler.dart (8 hints)
  • lib/results/binary_data_packet.dart (8 hints)
  • lib/comm/receiver.dart (6 hints)
  • lib/handlers/handler.dart (6 hints)
  • lib/comm/comm.dart (5 hints)
  • lib/prepared_statements/prepare_handler.dart (5 hints)
  • lib/query/query_stream_handler.dart (4 hints)
  • lib/connection/impl.dart (3 hints)
  • lib/results/standard_data_packet.dart (3 hints)
  • lib/comm/sender.dart (2 hints)
  • lib/exceptions/mysql_exception.dart (2 hints)
  • lib/handlers/use_db_handler.dart (2 hints)
  • lib/results/blob.dart (2 hints)
  • lib/results/row.dart (2 hints)
  • lib/auth/ssl_handler.dart (1 hint)
  • lib/handlers/debug_handler.dart (1 hint)
  • lib/handlers/parameter_packet.dart (1 hint)
  • lib/handlers/ping_handler.dart (1 hint)
  • lib/prepared_statements/close_statement_handler.dart (1 hint)
  • lib/query/result_set_header_packet.dart (1 hint)
  • lib/utils/buffer.dart (1 hint)

Maintenance issues and suggestions

Support latest dependencies. (-10 points)

The version constraint in pubspec.yaml does not support the latest published versions for 1 dependency (synchronized).

The package description is too short. (-20 points)

Add more detail to the description field of pubspec.yaml. Use 60 to 180 characters to describe the package, what it does, and its target use case.

Dependencies

Package Constraint Resolved Available
Direct dependencies
Dart SDK >=2.0.0-dev.68.0 <3.0.0
collection ^1.1.3 1.14.12
crypto ^2.0.6 2.1.4
synchronized ^1.5.1 1.5.3+2 2.1.1
typed_buffer ^2.1.1 2.2.1
Transitive dependencies
charcode 1.1.2
convert 2.1.1
typed_data 1.1.6
Dev dependencies
test ^1.3.0