sqlparser 0.7.0

  • Readme
  • Changelog
  • Example
  • Installing
  • 92

sqlparser #

Sql parser and static analyzer written in Dart. At the moment, this library targets the sqlite dialect only.

Features #

This library aims to support every sqlite feature, which includes parsing and detailed static analysis. We can resolve what type a column in a SELECT statement has, infer types for variables, find semantic errors and more.

This library supports most sqlite features:

  • CRUD: Full support, including joins, group by, nested and compound selects, WITH clauses and window functions
  • DDL: Supports CREATE TABLE statements, including advanced features like foreign keys or virtual tables (when a matching module like fts5 is enabled). This library also supports CREATE TRIGGER and CREATE INDEX statements.

Using the parser #

To obtain an abstract syntax tree from an sql statement, use SqlEngine.parse.

import 'package:sqlparser/sqlparser.dart';

final engine = SqlEngine();
final result = engine.parse('''
SELECT f.* FROM frameworks f
  INNER JOIN uses_language ul ON ul.framework = f.id
  INNER JOIN languages l ON l.id = ul.language
WHERE l.name = 'Dart'
ORDER BY f.name ASC, f.popularity DESC
LIMIT 5 OFFSET 5 * 3
  ''');
// result.rootNode contains the select statement in tree form

Analysis #

Given information about all tables and a sql statement, this library can:

  1. Determine which result columns a query is going to have, including types and nullability
  2. Make an educated guess about what type the variables in the query should have (it's not really possible to be 100% accurate about this because sqlite is very flexible at types, but this library gets it mostly right)
  3. Issue basic warnings about queries that are syntactically valid but won't run (references unknown tables / columns, uses undefined functions, etc.)

To use the analyzer, first register all known tables via SqlEngine.registerTable. Then, SqlEngine.analyze(sql) gives you an AnalysisContext which contains an annotated AST and information about errors. The type of result columns and expressions can be inferred by using AnalysisContext.typeOf(). Here's an example:

final id = TableColumn('id', const ResolvedType(type: BasicType.int));
final content = TableColumn('content', const ResolvedType(type: BasicType.text));
final demoTable = Table(
  name: 'demo',
  resolvedColumns: [id, content],
);
final engine = SqlEngine()..registerTable(demoTable);

final context =
    engine.analyze('SELECT id, d.content, *, 3 + 4 FROM demo AS d');

final select = context.root as SelectStatement;
final resolvedColumns = select.resolvedColumns;

resolvedColumns.map((c) => c.name)); // id, content, id, content, 3 + 4
resolvedColumns.map((c) => context.typeOf(c).type.type) // int, text, int, text, int, int

But why? #

Moor, a persistence library for Dart apps, uses this package to generate type-safe methods from sql.

Limitations #

Most on this list is just not supported yet because I didn't found a use case for them yet. If you need them, just leave an issue and I'll try to implement them soon.

  • An UPSERT clause is not yet supported on insert statements

If you run into parsing errors with what you think is valid sql, please create an issue.

Thanks #

0.7.0 #

  • New feature: Table valued functions.
  • Breaking: Removed the enableJson1 parameter on EngineOptions. Add a Json1Extension instance to enabledExtensions instead.
  • Parse rowid as a valid reference when needed (SELECT rowid FROM tbl is now parsed correctly)
  • Parse CURRENT_TIME, CURRENT_DATE and CURRENT_TIMESTAMP
  • Parse UPSERT clauses for insert statements

0.6.0 #

  • Breaking: Added an argument type and argument to the visitor classes
  • Experimental new type inference algorithm (SqlEngine.withOptions(EngineOptions(enableExperimentalTypeInference: true)))
  • Support CAST expressions and the ISNULL / NOTNULL postfixes
  • Support parsing CREATE TRIGGER statements
  • Support parsing CREATE INDEX statements

0.5.0 #

  • Optionally support the json1 module
  • Optionally support the fts5 module

0.4.0 #

  • Support common table expressions
  • Handle special rowid, oid, __rowid__ references
  • Support references to sqlite_master and sqlite_sequence tables

0.3.0 #

  • parse compound select statements
  • scan comment tokens
  • experimental auto-complete engine (only supports a tiny subset based on the grammar only)
  • some features that are specific to moor

0.3.0+1: Accept \r characters as whitespace

0.2.0 #

  • Parse CREATE TABLE statements
  • Extract schema information from parsed create table statements with SchemaFromCreateTable.

0.1.2 #

  • parse COLLATE expressions
  • fix wrong order in parsed LIMIT clauses

0.1.1 #

Attempt to recognize when a bound variable should be an array (eg. in WHERE x IN ?). Also fixes a number of parsing bugs:

  • Parses tuples, proper type resolution for IN expressions
  • Don't resolve references to tables that don't appear in the surrounding statement.
  • Parse joins without any additional operator, e.g. table1 JOIN table2 instead of table1 CROSS JOIN table2.
  • Parser now complains when parsing a query doesn't fully consume the input

0.1.0 #

Initial version, can parse most statements but not DELETE, common table expressions and other advanced features.

example/sqlparser_example.dart

import 'package:sqlparser/sqlparser.dart';

// Example that parses a select statement on some tables defined below and
// prints what columns would be returned by that statement.
void main() {
  final engine = SqlEngine()
    ..registerTable(frameworks)
    ..registerTable(languages)
    ..registerTable(frameworkToLanguage);

  final result = engine.analyze('''
SELECT f.* FROM frameworks f
  INNER JOIN uses_language ul ON ul.framework = f.id
  INNER JOIN languages l ON l.id = ul.language
WHERE l.name = 'Dart'
ORDER BY f.name ASC, f.popularity DESC
LIMIT 5 OFFSET 5 * 3
  ''');

  result.errors.forEach(print);

  final select = result.root as SelectStatement;
  final columns = select.resolvedColumns;

  print('the query returns ${columns.length} columns');

  for (final column in columns) {
    final type = result.typeOf(column);
    print('${column.name}, which will be a $type');
  }
}

// declare some tables. I know this is verbose and boring, but it's needed so
// that the analyzer knows what's going on.
final Table frameworks = Table(
  name: 'frameworks',
  resolvedColumns: [
    TableColumn(
      'id',
      const ResolvedType(type: BasicType.int),
    ),
    TableColumn(
      'name',
      const ResolvedType(type: BasicType.text),
    ),
    TableColumn(
      'popularity',
      const ResolvedType(type: BasicType.real),
    ),
  ],
);

final Table languages = Table(
  name: 'languages',
  resolvedColumns: [
    TableColumn(
      'id',
      const ResolvedType(type: BasicType.int),
    ),
    TableColumn(
      'name',
      const ResolvedType(type: BasicType.text),
    ),
  ],
);

final Table frameworkToLanguage = Table(
  name: 'uses_language',
  resolvedColumns: [
    TableColumn(
      'framework',
      const ResolvedType(type: BasicType.int),
    ),
    TableColumn(
      'language',
      const ResolvedType(type: BasicType.int),
    ),
  ],
);

Use this package as a library

1. Depend on it

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


dependencies:
  sqlparser: ^0.7.0

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:sqlparser/sqlparser.dart';
  
Popularity:
Describes how popular the package is relative to other packages. [more]
83
Health:
Code health derived from static analysis. [more]
100
Maintenance:
Reflects how tidy and up-to-date the package is. [more]
100
Overall:
Weighted score of the above. [more]
92
Learn more about scoring.

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

  • Dart: 2.7.1
  • pana: 0.13.6

Dependencies

Package Constraint Resolved Available
Direct dependencies
Dart SDK >=2.6.0 <3.0.0
collection ^1.14.11 1.14.12
meta ^1.1.0 1.1.8
source_span ^1.5.5 1.7.0
Transitive dependencies
charcode 1.1.3
term_glyph 1.1.0
Dev dependencies
path ^1.6.0 1.6.4
test ^1.9.4