implement the methods startTransaction , commit , rollback #30
Comments
|
I'm not sure: what's the goal here? |
|
I'm in need of a way to execute a transaction in the database in a similar way to PHP PDO /* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();
/* Change the database schema and data */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
SET name = 'hamburger'");
/* Recognize mistake and roll back changes */
$dbh->rollBack();
/* Database connection is now back in autocommit mode */ |
|
Have you checked the transaction example in the readme? await connection.transaction((ctx) async {
var result = await ctx.query("SELECT id FROM table");
await ctx.query("INSERT INTO table (id) VALUES (@a:int4)", substitutionValues: {
"a" : result.last[0] + 1
});
});To cancel the transaction (== rollback), you can do Wouldn't that be the same? |
|
yes I know about the transaction method passing a callback, but I needed to do it without a callback, that is in a more procedural or imperative way or in a less functional way like https://pub.dev/packages/galileo_sqljocky5 var trans = await pool.begin();
try {
var result1 = await trans.execute('...');
var result2 = await trans.execute('...');
await trans.commit();
} catch(e) {
await trans.rollback();
} |
There is no functional/procedural difference in this API, it just provides a failsafe wrapper, so that you don't need to call commit or rollback yourself. The parameter in the callback is I fail to see any benefit of providing a second, different way to handle transactions (the current code is complex in itself), but if you are up for it, I am open to review PRs wrt. this feature. |
|
A while ago I created a package called "fluent_query_builder" https://pub.dev/packages/fluent_query_builder The strange thing is that when I remove the select and insert operations from within the asynchronous method and put them directly in the transaction callback, the problem doesn't seem to happen. I tried to create a simpler code below that tries to reproduce the code that is in production that is bigger with more inserts and selects due to the normalization of the database and the business rules. import 'dart:async';
import 'package:fluent_query_builder/fluent_query_builder.dart';
import 'package:postgres/postgres.dart';
void main() async {
final pgsqlComInfo = DBConnectionInfo(
enablePsqlAutoSetSearchPath: true,
reconnectIfConnectionIsNotOpen: true,
host: '192.168.133.13',
database: 'test',
driver: ConnectionDriver.pgsql,
port: 5432,
username: 'user',
password: 'pass',
charset: 'utf8',
schemes: ['public'],
setNumberOfProcessorsFromPlatform: false,
numberOfProcessors: 1,
);
var db = await DbLayer().connect(pgsqlComInfo);
await db.execute('DROP TABLE IF EXISTS "public"."naturalPerson" CASCADE');
await db.execute('DROP TABLE IF EXISTS "public"."legalPerson" CASCADE');
//create tables
await db.execute('''
CREATE TABLE IF NOT EXISTS "public"."naturalPerson" (
"id" serial8 PRIMARY KEY,
"name" varchar(255) COLLATE "pg_catalog"."default",
"email" varchar(255) COLLATE "pg_catalog"."default"
);
''');
await db.execute('''
CREATE TABLE IF NOT EXISTS "public"."legalPerson" (
"idPerson" int8 PRIMARY KEY,
"socialSecurityNumber" varchar(12) COLLATE "pg_catalog"."default",
CONSTRAINT "ssn" UNIQUE ("socialSecurityNumber")
);
''');
//insert natural Person
await db.raw('''
INSERT INTO "naturalPerson" (name,email) VALUES ('John Doe', '[email protected]');
''').exec();
//insert legal Person
await db.raw('''
INSERT INTO "legalPerson" ("idPerson","socialSecurityNumber") VALUES ('1', '856-45-6789');
''').exec();
var repository = NaturalPersonRepository();
var person = NaturalPerson(name: 'John Doe 2', email: '[email protected]');
Timer.periodic(Duration(seconds: 3), (t) async {
var idPerson;
try {
await db.transaction((ctx) async {
idPerson = await repository.insert(person, ctx);
await ctx.raw(
'''INSERT INTO "legalPerson" ("idPerson","socialSecurityNumber") VALUES ('$idPerson', '956-45-6789');''').exec();
});
} catch (e) {
print(e);
}
});
}
class NaturalPerson {
int? id;
final String name;
final String email;
NaturalPerson({this.id, required this.name, required this.email});
Map<String, dynamic> toMap() {
return {
'name': name,
'email': email,
};
}
factory NaturalPerson.fromMap(Map<String, dynamic> map) {
return NaturalPerson(
id: map['id'],
name: map['name'] ?? '',
email: map['email'] ?? '',
);
}
}
class LegalPerson extends NaturalPerson {
final int idPerson;
final String socialSecurityNumber;
LegalPerson(
{required String name,
required String email,
required this.idPerson,
required this.socialSecurityNumber})
: super(name: name, email: email);
}
class NaturalPersonRepository {
Future<int> insert(NaturalPerson person, DbLayer ctx) async {
var result = await ctx
.insertGetId()
.into('naturalPerson')
.setAll(person.toMap())
.exec();
//...many other inserts and selects here
var idPerson = result!.first!.first;
return idPerson;
}
}
I don't know the cause of the problem yet, more because of this problem than suggesting to implement the methods startTransaction , commit , rollback, because I believe it is easier to create abstractions on top of the driver without the transaction callback but I could be wrong if you have any advice on this problem i appreciate it |
|
I would like a way to do PDO-style transactions without closing, so I decided to create a driver from scratch, I'm working on a postgreSQL driver here: https://github.com/insinfo/pg8000 I was strongly inspired by a driver written in python In this driver implementation journey, I noticed that your BytesBuffer, ByteDataWriter, ByteDataReader implementation seems to be much slower than the terrier989 RawReader, RawWriter implementation https://github.com/dart-protocol/raw |
@insinfo: Nice work there! Would you be interested in joining efforts? I have a plan to do breaking changes here anyway: #74
That is an intriguing questing, because on the surface they seem to be roughly doing the same, maybe with different buffer sizes. Did you do any benchmarking that I can look at? |
|
where I work I have many legacy PHP projects that I am porting to dart, all these projects use PDO and Eloquent, they use different versions of PHP some are very old using PostgreSQL 8 and charset latin1/ascii/utf8 because of that I have been working on porting some PHP libraries for dart such as Eloquent (I'm porting Eloquent here), and because of that I decided to try to implement a postgreSQL driver from scratch that is closer to the PDO API, having said that without a doubt I would like to add efforts but I have not had much time because where I work there is only me and another programmer. Regarding the performance of the ByteDataWriter, I did a very simple test of performance and it is not enough to be a well-structured benchmarking. I needed a pack and unpack function that is natively available in PHP and Python, as it doesn't exist in dart I implemented one to help me port the code from python to dart, I confess that I also don't understand what the code is doing of the terrier being so much faster. performance simple testimport 'dart:convert';
import 'package:pg8000/src/pack_unpack.dart';
void main(List<String> args) {
// implementation from @lrhn https://github.com/dart-lang/sdk/issues/50708
runBenchmark(() {
return packlrhn('iiii', [64, 65, 66, 67]);
}, 'pack from lrhn', 2);
// my pure dart implementation
runBenchmark(() {
return pack('iiii', [64, 65, 66, 67]);
}, 'my pure dart pack', 2);
// using isoos ByteDataWriter
runBenchmark(() {
return pack2('iiii', [64, 65, 66, 67]);
}, 'using isoos ByteDataWriter pack', 2);
// using terrier RawWriter
runBenchmark(() {
return pack3('iiii', [64, 65, 66, 67]);
}, 'using terrier RawWriter pack', 2);
}
void runBenchmark(Function closure, String title, [int runCount = 1]) {
for (var rc = 0; rc < runCount; rc++) {
var stopw3 = Stopwatch()..start();
var result3;
for (var i = 0; i < 100000000; i++) {
result3 = closure();
}
stopw3.stop();
print('$title result: ${utf8.decode(result3)}');
print('$title time: ${stopw3.elapsed}');
}
}
https://github.com/insinfo/pg8000/blob/main/lib/src/pack_unpack.dart resultsI opened a issue in the dart/sdk repository so that dart has the pack and unpack function natively, because I think it's a very easy way to work with bytes and implement protocols. |
|
@insinfo: Thanks for the benchmark! I've fixed updated |
implement the methods startTransaction , commit , rollback
I tried to run them but I got this error:
The text was updated successfully, but these errors were encountered: