sql_commander 1.1.0

A SQL command chain handler and executor that is database-agnostic, with built-in support for MySQL and PostgreSQL.

sql_commander

A SQL command chain handler and executor that is database-agnostic, with built-in support for MySQL and PostgreSQL.

Motivation

The primary motivation behind creating this package was to facilitate the execution of SQL queries chain across various flavors of databases in remote locations.

Rather than deploying software with hardcoded database operations in remote devices/servers, opting for a robust and easily updatable solution across many devices, involves deploying a database-agnostic SQL chain command executor (sql_commander). This executor is designed to receive DBCommands and perform their execution remotely, adapting them to the specific database dialect in use while also resolving SQL chain references and IDs.

If any operation requires modification or updating for system compatibility, the generated DBCommand sent to the remote sql_commander can be adjusted without the necessity of updating the software deployed on the remote devices. This simplifies maintenance and minimizes issues in remote locations.

Empowered by Dart's multi-platform support, this package simplifies the creation of robust solutions with ease.

Usage

DBCommand Example

import 'package:sql_commander/sql_commander_postgres.dart';
//import 'package:sql_commander/sql_commander_mysql.dart';

void main() async {
  // DBCommand SQLs chain:
  var dbCommandSQLs = [
    // Provide the parameter %SYS_USER% in the INSERT below:
    SQL(
      '%SYS_USER%',
      'user',
      SQLType.SELECT,
      where: SQLConditionValue('id', '>', 0),
      returnColumns: {'user_id': 'id'},
      orderBy: '>user_id',
      limit: 1,
    ),
    // Provide the parameter %TAB_NUMBER% in the INSERT below:
    SQL(
      '%TAB_NUMBER%',
      'tab',
      SQLType.SELECT,

      where: SQLConditionGroup.and([
        SQLConditionValue('serie', '=', 'tabs'),
        SQLConditionGroup.or([
          SQLConditionValue('status', '=', 'free'),
          SQLConditionValue('status', '=', null),
        ])
      ]),
      returnColumns: {'num': null},
      // ORDER BY num DESC:
      orderBy: '>num',
      // LIMIT 1:
      limit: 1,
    ),
    // INSERT into table `order` using `%SYS_USER%` and `%TAB_NUMBER%` as parameters:
    SQL(
      // The ID of this SQL for references in the command chain: `#order:1001#`
      '1001',
      'order',
      SQLType.INSERT,
      parameters: {
        'product': 123,
        'price': 10.20,
        'title': 'Water',
        'user': '%SYS_USER%',
        'tab': '%TAB_NUMBER%',
      },
      // Variables to resolve in this SQL:
      variables: {'SYS_USER': null, 'TAB_NUMBER': null},
      returnLastID: true,
    ),
    // Another INSERT, using the INSERT above: `#order:1001#`
    SQL(
      // The ID of this SQL for references:
      '1',
      'order_history',
      SQLType.INSERT,
      parameters: {
        // The order inserted above:
        'order': '#order:1001#',
        'date': DateTime.now(),
      },
      returnLastID: true,
    ),
  ];

  // A `DBCommand` as JSON:
  var commandJSON = {
    "host": 'localhost',
    "port": 5432,
    "user": 'root',
    "pass": 'abc123',
    "db": 'dev',
    "software": 'postgres',
    "sqls": dbCommandSQLs.map((e) => e.toJson()).toList(),
  };

  // Load a `DBCommand` from JSON:
  var dbCommand = DBCommand.fromJson(commandJSON);

  // Register the `PostgreSQL` connection implementation:
  DBConnectionPostgres.register();
  // For MySQL:
  //DBConnectionMySQL.register();

  // Execute the SQL chain:
  var ok = await dbCommand.execute(
    logInfo: (m) => print('[INFO] $m'),
    logError: (m, [e, s]) => print('[ERROR] $m >> $e\n$s'),
  );

  print('SQL chain execution: $ok');
}

Procedure Example

You can define a Procedure with a dynamic Dart code that can be loaded by the ApolloVM in any platform supported by Dart.

import 'package:sql_commander/sql_commander_postgres.dart';
//import 'package:sql_commander/sql_commander_mysql.dart';

void main() async {
  // Register the `PostgreSQL` connection implementation:
  DBConnectionPostgres.register();
  // For MySQL:
  //DBConnectionMySQL.register();
  
  // DBCommand SQLs chain:
  var sqls = [
    // Provide the parameter %SYS_USER% in the INSERT below:
    SQL(
      '%SYS_USER%',
      'user',
      SQLType.SELECT,
      where: SQLConditionValue('id', '>', 0),
      returnColumns: {'user_id': 'id'},
      orderBy: '>user_id',
      limit: 1,
    ),
    // Provide the parameter %TAB_NUMBER% in the INSERT below:
    SQL(
      '%TAB_NUMBER%',
      'tab',
      SQLType.SELECT,

      where: SQLConditionGroup.and([
        SQLConditionValue('serie', '=', 'tabs'),
        SQLConditionGroup.or([
          SQLConditionValue('status', '=', 'free'),
          SQLConditionValue('status', '=', null),
        ])
      ]),
      returnColumns: {'num': null},
      // ORDER BY num DESC:
      orderBy: '>num',
      // LIMIT 1:
      limit: 1,
    ),
    // INSERT into table `order` using `%SYS_USER%` and `%TAB_NUMBER%` as parameters:
    SQL(
      // The ID of this SQL for references in the command chain: `#order:1001#`
      '1001',
      'order',
      SQLType.INSERT,
      parameters: {
        'product': 123,
        'price': 10.20,
        'title': 'Water',
        'user': '%SYS_USER%',
        'tab': '%TAB_NUMBER%',
      },
      // Variables to resolve in this SQL:
      variables: {'SYS_USER': null, 'TAB_NUMBER': null},
      returnLastID: true,
    ),
    // Another INSERT, using the INSERT above: `#order:1001#`
    SQL(
      // The ID of this SQL for references:
      '1',
      'order_history',
      SQLType.INSERT,
      parameters: {
        // The order inserted above:
        'order': '#order:1001#',
        'date': DateTime.now(),
      },
      returnLastID: true,
    ),
  ];

  var dbCommand = DBCommand(
      id: 'cmd_1', 'localhost', 5432, 'root', '123456', 'postgres', '', sqls);

  var procedure = ProcedureDart(
    name: 'do',
    dbCommands: [dbCommand],
    code: r'''
  
      int do() {
        var cmdOK = executeDBCommandByID("cmd_1");
        
        if (!cmdOK) {
          print('** Error executing DBCommand!');
          return false;
        }
        
        print('DBCommand `cmd_1` executed.');
        
        var tabNumber = getSQLResult('%TAB_NUMBER%');
        print('TAB_NUMBER: tabNumber');
        
        return tabNumber ;
      }
      
    ''',
  );

  var tabNumber = await procedure.execute();
  print("Procedure result> tabNumber: $tabNumber");
}

Features and bugs

Please file feature requests and bugs at the issue tracker.

Author

Graciliano M. Passos: gmpassos@GitHub.

Sponsor

Don't be shy, show some love, and become our GitHub Sponsor. Your support means the world to us, and it keeps the code caffeinated! ☕✨

Thanks a million! 🚀😄

See Also

• ApolloVM: A portable VM (native, JS/Web, Flutter) that can parse, translate and run multiple languages, like Dart, Java and JavaScript.

License

Dart free & open-source license.