sqljocky2 0.14.4-dev

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

SQLJocky2 #

** This is a fork of the original SQLJocky. The goal is to maintain SqlJocky2 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.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 'package:sqljocky/sqljocky.dart';
import 'package:sqljocky/utils.dart';
import 'package:options_file/options_file.dart';
import 'dart:async';

/*
 * This example drops a couple of tables if they exist, before recreating them.
 * It then stores some data in the database and reads it back out.
 * You must have a connection.options file in order for this to connect.
 */

class Example {
  ConnectionPool pool;

  Example(this.pool);

  Future run() async {
    // drop the tables if they already exist
    await dropTables();
    print("dropped tables");
    // then recreate the tables
    await createTables();
    print("created tables");
    // add some data
    await addData();
    // and read it back out
    await readData();
  }

  Future dropTables() {
    print("dropping tables");
    var dropper = new TableDropper(pool, ['pets', 'people']);
    return dropper.dropTables();
  }

  Future createTables() {
    print("creating tables");
    var querier = new QueryRunner(pool, [
      'create table people (id integer not null auto_increment, '
          'name varchar(255), '
          'age integer, '
          'primary key (id))',
      '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("executing queries");
    return querier.executeQueries();
  }

  Future addData() async {
    var query =
        await pool.prepare("insert into people (name, age) values (?, ?)");
    print("prepared query 1");
    var parameters = [
      ["Dave", 15],
      ["John", 16],
      ["Mavis", 93]
    ];
    await query.executeMulti(parameters);

    print("executed query 1");
    query = await pool
        .prepare("insert into pets (name, species, owner_id) values (?, ?, ?)");

    print("prepared query 2");
    parameters = [
      ["Rover", "Dog", 1],
      ["Daisy", "Cow", 2],
      ["Spot", "Dog", 2]
    ];
//          ["Spot", "D\u0000og", 2]];
    await query.executeMulti(parameters);

    print("executed query 2");
  }

  Future readData() async {
    print("querying");
    var result =
        await pool.query('select p.id, p.name, p.age, t.name, t.species '
            'from people p '
            'left join pets t on t.owner_id = p.id');
    print("got results");
    return result.forEach((row) {
      if (row[3] == null) {
        print("ID: ${row[0]}, Name: ${row[1]}, Age: ${row[2]}, No Pets");
      } else {
        print(
            "ID: ${row[0]}, Name: ${row[1]}, Age: ${row[2]}, Pet Name: ${row[3]}, Pet Species ${row[4]}");
      }
    });
  }
}

main() async {
  OptionsFile options = new OptionsFile('connection.options');
  String user = options.getString('user');
  String password = options.getString('password');
  int port = options.getInt('port', 3306);
  String db = options.getString('db');
  String host = options.getString('host', 'localhost');

  // create a connection
  print("opening connection");
  var pool = new ConnectionPool(
      host: host, port: port, user: user, password: password, db: db, max: 1);
  print("connection open");
  // create an example class
  var example = new Example(pool);
  // run the example
  print("running example");
  await example.run();
  // finally, close the connection
  print("K THNX BYE!");
  pool.closeConnectionsNow();
}

Use this package as a library

1. Depend on it

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


dependencies:
  sqljocky2: ^0.14.4-dev

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:sqljocky2/constants.dart';
import 'package:sqljocky2/sqljocky.dart';
import 'package:sqljocky2/utils.dart';
  
Version Uploaded Documentation Archive
0.14.4-dev Sep 11, 2016 Go to the documentation of sqljocky2 0.14.4-dev Download sqljocky2 0.14.4-dev archive
0.14.3-dev Sep 11, 2016 Go to the documentation of sqljocky2 0.14.3-dev Download sqljocky2 0.14.3-dev archive
Popularity:
Describes how popular the package is relative to other packages. [more]
8
Health:
Code health derived from static analysis. [more]
--
Maintenance:
Reflects how tidy and up-to-date the package is. [more]
--
Overall:
Weighted score of the above. [more]
4
Learn more about scoring.

The package version is not analyzed, because it does not support Dart 2. Until this is resolved, the package will receive a health and maintenance score of 0.

Analysis issues and suggestions

Support Dart 2 in pubspec.yaml.

The SDK constraint in pubspec.yaml doesn't allow the Dart 2.0.0 release. For information about upgrading it to be Dart 2 compatible, please see https://dart.dev/dart-2#migration.

Maintenance issues and suggestions

Make sure dartdoc successfully runs on your package's source files. (-10 points)

Dependencies were not resolved.

Dependencies

Package Constraint Resolved Available
Direct dependencies
Dart SDK >=1.11.0 <2.0.0