sql_commander

Description:

sql commander

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');
}
copied to clipboard
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");
}

copied to clipboard
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.