sqljocky5 2.2.1

  • Readme
  • Changelog
  • Example
  • Installing
  • 61

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

Alternatively, your editor might support 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]
81
Health:
Code health derived from static analysis. [more]
42
Maintenance:
Reflects how tidy and up-to-date the package is. [more]
38
Overall:
Weighted score of the above. [more]
61
Learn more about scoring.

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

  • Dart: 2.7.1
  • pana: 0.13.6

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

No valid SDK. (-20 points)

The analysis could not detect a valid SDK that can use this package.

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.

Package is getting outdated. (-12.05 points)

The package was last published 58 weeks ago.

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.2.0
typed_buffer ^2.1.1 2.2.1
Transitive dependencies
charcode 1.1.3
convert 2.1.1
typed_data 1.1.6
Dev dependencies
test ^1.3.0