sqljocky5 2.1.5

  • README.md
  • CHANGELOG.md
  • Example
  • Installing
  • Versions
  • 59

SQLJocky5 #

** This is a fork of the original SQLJocky. The goal is to maintain SQLJocky5 and give Dart a proper MySQL connector **

This is a MySQL connector for the Dart programming language. It isn't finished, but should work for most normal use. The API is getting reasonably close to where I want it to be now, so hopefully there shouldn't be too many breaking changes in the future.

It will only work in the command-line VM, not in a browser.

News #

The changelog has now been moved to CHANGELOG.md

Usage #

Create a connection pool:

var pool = new ConnectionPool(
    host: 'localhost', port: 3306,
    user: 'bob', password: 'wibble',
    db: 'stuff', max: 5);

Execute a query:

var results = await pool.query('select name, email from users');

Use the results: (Note: forEach is asynchronous.)

results.forEach((row) {
  print('Name: ${row[0]}, email: ${row[1]}');
});

Or access the fields by name:

results.forEach((row) {
  print('Name: ${row.name}, email: ${row.email}');
});

Prepare a query:

var query = await pool.prepare(
  'insert into users (name, email, age) values (?, ?, ?)');

Execute the query:

var result = await query.execute(['Bob', 'bob@bob.com', 25]);

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}");

Execute a query with multiple sets of parameters:

var results = await query.executeMulti([['Bob', 'bob@bob.com', 25],
    ['Bill', 'bill@bill.com', 26],
    ['Joe', 'joe@joe.com', 37]]);

Use the list of results:

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

Use a transaction:

var trans = await pool.startTransaction();
var result = await trans.query('...');
await trans.commit();

Development #

To run the examples and tests, you'll need to create a 'connection.options' file by copying 'connection.options.example' and modifying the settings.

Licence #

It is released under the GPL, because it uses a modified part of mysql's include/mysql_com.h in constants.dart, which is licensed under the GPL. I would prefer to release it under the BSD Licence, but there you go.

The Name #

It is named after Jocky Wilson, the late, great darts player. (Hence the lack of an 'e' in Jocky.)

Things to do #

  • Compression
  • COM_SEND_LONG_DATA
  • CLIENT_MULTI_STATEMENTS and CLIENT_MULTI_RESULTS for stored procedures
  • More connection pool management (close after timeout, change pool size...)
  • 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 #

v0.14.5 #

  • Fix package references

v0.14.3 #

  • Merged in Kevin Moore's PR from original SQLJockey

v0.14.1 #

  • Fix the changelog formatting, so you can actually see what changed in v0.14.0

v0.14.0 #

  • Requires Dart 1.11
  • Use newer logging library
  • Use async/await in library code and examples.
  • Fix bug with closing prepared queries, where it sometimes tried to close a query which was in use.
  • Don't throw an error if username is null.
  • Fix bug in blobs, where it was trying to decode binary blobs as UTF-8 strings.
  • Close connections and return them to the pool when a connection times out on the server.

v0.13.0 #

  • Fixes an issue with executeMulti being broken.
  • Fixes an issue with query failing if the first field in a SELECT is an empty string

v0.12.0 #

  • Breaking change: ConnectionPool.close() has been renamed to ConnectionPool.closeConnectionsNow. It is a dangerous method to call as it closes all connections even if they are in the middle of an operation. ConnectionPool.closeConnectionsWhenNotInUse has been added, which is much safer.
  • Fixed an issue with closing prepared queries which caused connections to remain open.

v0.11.0 #

  • Added support for packets larger than 16 MB. ConnectionPool's constructor has a new parameter, 'maxPacketSize', which specifies the maximum packet size in bytes. Using packets larger than 16 MB is not currently particularly optimised.
  • Fixed some issues with authentication. In particular, errors should now be thrown when you try to connect to a server which is using an old or unsupported authentication protocol.

v0.10.0 #

  • Added SSL connections. Pass 'useSSL: true' to ConnectionPool constructor. If server doesn't support SSL, connection will continue unsecured. You can check if the connections are secure by calling pool.getConnection().then((cnx) {print(cnx.usingSSL); cnx.release();});

v0.9.0 #

  • Added ConnectionPool.getConnection() which returns a RetainedConnection. Useful if you need to keep a specific connection around (for example, if you need to lock tables).

v0.8.3 #

  • Fixed connection retention error in Query.executeMulti

v0.8.1 #

  • Can now access fields by name.

v0.8.0 #

  • Breaking change: Results no longer has a 'stream' property - it now implements Stream itself. As a result, it also no longer has a 'rows' property, or a 'toResultsList()' method - you can use 'toList()' to convert it into a list instead.

v0.7.0 #

  • Rewritten some connection handling code to make it more robust, and so that it handles stream operations such as 'first' correctly (i.e. without hanging forever).
  • Updated spec for Dart 1.0

v0.6.2 #

  • Support for latest SDK (removal of dart:utf8 library)

v0.6.1 #

  • Support for latest SDK

v0.6.0 #

  • Change prepared statement syntax. Values must now be passed into the execute() method in an array. This change was made because otherwise prepared statements couldn't be used asynchronously correctly - if you used the same prepared query object for multiple queries 'at the same time', the wrong values could get used.

v0.5.8 #

  • Handle errors in the utils package properly
  • Pre-emptively fixed some errors, wrote more tests.

v0.5.7 #

  • Fixed error with large fields.

v0.5.6 #

  • Hopefully full unicode support
  • Fixed problem with null values in prepared queries.

v0.5.5 #

  • Some initial changes for better unicode handling.

v0.5.4 #

  • Blobs and Texts which are bigger than 250 characters now work.

v0.5.3 #

  • Make ConnectionPool and Transaction implement QueriableConnection
  • Improved tests.

v0.5.2 #

  • Fix for new SDK

v0.5.1 #

  • Made an internal class private

v0.5.0 #

  • Breaking change: Now uses streams to return results.

v0.4.1 #

  • Major refactoring so that only the parts of sqljocky which are supposed to be exposed are.

v0.4.0 #

  • Support for M4.

v0.3.0 #

  • Support for M3.
  • Bit fields are now numbers, not lists.
  • Dates now use the DateTime class instead of the Date class.
  • Use new IO classes.

v0.2.0 #

  • Support for the new SDK.

v0.1.3 #

  • SQLJocky now uses a connection pooling model, so the API has changed somewhat.

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<Results> r1 =
      await conn.queryMulti("INSERT INTO people (name, age) VALUES (?, ?)", [
    ["Dave", 15],
    ["John", 16],
    ["Mavis", 93],
  ]);
  print("People table insert ids: " + r1.map((r) => r.insertId).toString());
  List<Results> r2 = await conn.queryMulti(
      "INSERT INTO pets (name, species, owner_id) VALUES (?, ?, ?)", [
    ["Rover", "Dog", 1],
    ["Daisy", "Cow", 2],
    ["Spot", "Dog", 2]
  ]);
  print("Pet table insert ids: " + r2.map((r) => r.insertId).toString());
  print("Rows inserted!");
}

Future<void> readData(MySqlConnection conn) async {
  Results result =
      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');
  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.1.5

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/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';
  
Version Uploaded Documentation Archive
2.2.1 Feb 15, 2019 Go to the documentation of sqljocky5 2.2.1 Download sqljocky5 2.2.1 archive
2.2.0 Sep 6, 2018 Go to the documentation of sqljocky5 2.2.0 Download sqljocky5 2.2.0 archive
2.1.5 Sep 5, 2018 Go to the documentation of sqljocky5 2.1.5 Download sqljocky5 2.1.5 archive
1.0.0 Jul 12, 2017 Go to the documentation of sqljocky5 1.0.0 Download sqljocky5 1.0.0 archive
Popularity:
Describes how popular the package is relative to other packages. [more]
79
Health:
Code health derived from static analysis. [more]
23
Maintenance:
Reflects how tidy and up-to-date the package is. [more]
60
Overall:
Weighted score of the above. [more]
59
Learn more about scoring.

We analyzed this package on May 8, 2019, and provided a score, details, and suggestions below. Analysis was completed with status completed using:

  • Dart: 2.3.0
  • pana: 0.12.15

Platforms

Detected platforms:

Error(s) prevent platform classification:

Error(s) in lib/comm/comm.dart: Target of URI doesn't exist: 'package:pool/pool.dart'.

Health issues and suggestions

Fix lib/comm/comm.dart. (-44.03 points)

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

line 5 col 8: Target of URI doesn't exist: 'package:pool/pool.dart'.

line 32 col 16: The method 'Pool' isn't defined for the class 'Comm'.

line 52 col 7: Future results in async function bodies must be awaited or marked unawaited using package:pedantic.

Fix lib/connection/settings.dart. (-3.45 points)

Analysis of lib/connection/settings.dart reported 7 hints, including:

line 20 col 24: Use = to separate a named parameter from its default value.

line 21 col 20: Use = to separate a named parameter from its default value.

line 25 col 31: Use = to separate a named parameter from its default value.

line 26 col 23: Use = to separate a named parameter from its default value.

line 27 col 29: Use = to separate a named parameter from its default value.

Fix lib/handlers/handler.dart. (-2.48 points)

Analysis of lib/handlers/handler.dart reported 5 hints:

line 16 col 1: Prefer using /// for doc comments.

line 31 col 3: Prefer using /// for doc comments.

line 40 col 3: Prefer using /// for doc comments.

line 69 col 1: Prefer using /// for doc comments.

line 86 col 7: Don't explicitly initialize variables to null.

Fix additional 9 files with analysis or formatting issues. (-8.48 points)

Additional issues in the following files:

  • lib/results/row.dart (4 hints)
  • lib/results/binary_data_packet.dart (3 hints)
  • lib/auth/handshake_handler.dart (2 hints)
  • lib/comm/buffered_socket.dart (2 hints)
  • lib/connection/impl.dart (2 hints)
  • lib/auth/auth_handler.dart (1 hint)
  • lib/exceptions/mysql_exception.dart (1 hint)
  • lib/handlers/ping_handler.dart (1 hint)
  • lib/results/results.dart (1 hint)

Maintenance issues and suggestions

Fix platform conflicts. (-20 points)

Error(s) prevent platform classification:

Error(s) in lib/comm/comm.dart: Target of URI doesn't exist: 'package:pool/pool.dart'.

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.11
crypto ^2.0.6 2.0.6
logging ^0.11.3 0.11.3+2
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