Dump the transformation as sql dump

[rokde]

It should be possible, to dump the whole transformation process as SQL statement dump with the correct dialect for the receiving database management system. So sql for postgresql differs from mysql. The result should be a sql file that can be inserted to the target dbms on any system.

Maybe we introduce a "virtual" or "dump" connection type which has just the database type as configuration value and this dumps the whole thing as one file right in the current workdir.

This is a way to work within multi-stage environments, there the source and the target connection will not get connected at any time. So we can transfer the file dump.

We could add an optional way to zip the dump file and protect it with a password. then this password can be configured by the connection data. like the password for the database user access.

Because the size is big, we should always zip the dump file.

[rokde]

@claude please try to make a proposal

[claude]

Claude encountered an error after 2s —— View job

---

I'll analyze this and get back to you.

[rokde]

@claude please try again

[claude]

Claude finished @rokde's task in 5m 13s —— View job • claude/issue-124-20260524-0942 • Create PR ➔

---

Proposal: SQL Dump Connection Type

• Gathered context and analyzed the codebase
• Design proposal (see below)
• Created spec file: specs/PRD-connection-dump.md

---

Summary

The feature introduces a dump connection type that, when used as the target in cloning:run, produces a dialect-correct SQL file compressed into a password-protected ZIP archive — no live target database required.

---

Core Idea: A New dump Connection Type

Add "type": "dump" to clonio.json alongside existing database types. A dump connection only needs two fields:

"staging-dump": {
  "type": "dump",
  "dialect": "pgsql",
  "password": "encrypted:eyJpdiI6..."
}

Field	Description
dialect	Target DBMS SQL dialect — mysql, mariadb, pgsql, sqlsrv, sqlite
password	ZIP archive password (optional, encrypted at rest like all other passwords)

No host, port, database, username needed — it's a virtual connection.

---

Output

• Filename: <source-database>_<YYYYMMDD>_<HHmmss>.sql
• Archive: <filename>.zip (always zipped)
• Encryption: AES-256 via ZipArchive::EM_AES_256 (available in libzip ≥ 1.2.0, already included in SPC builds)
• Location: current working directory

---

Pipeline Changes in cloning:run

Phase	Normal	With Dump Target
2	PDO ping target DB	Validate dialect + check cwd is writable
4	DDL via PDO	Write CREATE TABLE DDL to .sql file
6	Bulk INSERT via PDO	Write INSERT INTO batches to .sql file
7	Audit log	Same + records dump file path + sha256
8	Summary	Print output zip path and size

After Phase 6: finalise .sql → compress to .zip → delete intermediate .sql.

---

SQL Dialects

Each dialect wraps the dump with the appropriate FK-disable preamble and postamble:

MySQL/MariaDB — backtick identifiers, SET FOREIGN_KEY_CHECKS, ENGINE=InnoDB:

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (`id` int unsigned NOT NULL AUTO_INCREMENT, ...);
INSERT INTO `users` (`id`, `name`) VALUES (1, 'John'), (2, 'Jane');
SET FOREIGN_KEY_CHECKS = 1;

PostgreSQL — double-quoted identifiers, SERIAL, sequence reset, session_replication_role:

SET session_replication_role = 'replica';
CREATE TABLE "users" ("id" SERIAL NOT NULL, ...);
INSERT INTO "users" ("id", "name") VALUES (1, 'John'), (2, 'Jane');
SELECT setval(pg_get_serial_sequence('"users"', 'id'), MAX("id")) FROM "users";
SET session_replication_role = 'origin';

SQL Server — bracket identifiers, IDENTITY_INSERT, N'' Unicode strings:

SET IDENTITY_INSERT [dbo].[users] ON;
INSERT INTO [dbo].[users] ([id], [name]) VALUES (1, N'John'), (2, N'Jane');
SET IDENTITY_INSERT [dbo].[users] OFF;

SQLite — PRAGMA foreign_keys, AUTOINCREMENT, TEXT/INTEGER affinity types.

---

Architecture

Modified files:

• app/Enums/DatabaseConnectionType.php — add case Dump = 'dump'
• app/Data/ConnectionData.php — add readonly ?DatabaseConnectionType $dialect
• app/Commands/Connection/AddCommand.php — dump-specific flow (ask dialect + zip password, skip host/port/etc.)
• app/Commands/Connection/TestCommand.php — skip PDO test; verify writable cwd
• app/Services/Database/DatabaseConnectionService.php — guard open() against dump type
• app/Services/Cloning/CloningRunOrchestrator.php — detect dump target; route phases 4 & 6
• app/Services/Config/ConfigService.php — persist/load dialect field

New files:

app/Services/SqlDump/
├── SqlDumpService.php          # Orchestrates file creation
├── SqlDumpDialect.php          # Interface: header/preamble/ddl/dml/postamble
├── DumpArchiver.php            # ZIP + optional AES-256 password
└── Dialects/
    ├── MySqlDumpDialect.php
    ├── MariaDbDumpDialect.php
    ├── PostgreSqlDumpDialect.php
    ├── SqlServerDumpDialect.php
    └── SqliteDumpDialect.php

---

Open Questions for Discussion

1. INSERT batch size — default 500 rows per statement; should this be configurable in the YAML or the dump connection?
2. DDL strategy — always DROP TABLE IF EXISTS + CREATE TABLE, or offer CREATE TABLE IF NOT EXISTS mode?
3. SQL Server schema prefix — always dbo? Or derive from a future schema field on the connection?
4. Cross-dialect type mapping — for MySQL→PostgreSQL dumps, how conservative should type mapping be? (e.g. varchar → VARCHAR, tinyint(1) → BOOLEAN)
5. Large BLOBs — binary columns should be hex-encoded per dialect; confirm this is in scope.

---

The full spec is in specs/PRD-connection-dump.md on the branch.

|

[rokde]

@claude

1. the batch size should be used with the value of options.chunk_size from the yaml file.
2. also the DDL strategy should rely on the options settings.
3. I guess we take the schema definition from the source connection - if not sql server, then use dbo.
4. use a good mixture -> it should be successful in any case.
5. yes, hexcode the blobs.

[claude]

Claude finished @rokde's task in 3m 0s —— View job • claude/issue-124-20260524-0956 • Create PR ➔

---

Updated spec with all 5 resolved decisions

• Load tools
• Read existing specs and understand options structure
• Write specs/PRD-connection-dump.md incorporating all 5 decisions
• Commit and push

All 5 open questions from the previous proposal have been resolved and documented in specs/PRD-connection-dump.md:

#	Question	Decision
1	INSERT batch size	Uses options.chunk_size from the cloning YAML — same value governing chunked reads in the standard pipeline
2	DDL strategy	Driven by options.drop_unknown_tables: true → DROP TABLE IF EXISTS + CREATE TABLE; false → CREATE TABLE IF NOT EXISTS
3	SQL Server schema prefix	Derived from the source connection's schema field when source is sqlsrv; defaults to dbo for all other source types
4	Cross-dialect type mapping	Conservative widest-compatible mapping (full table in §8): tinyint(1) → BOOLEAN/BIT, unsigned integers widen to next signed type, SQL Server always uses Unicode NVARCHAR/NCHAR, unknown types fall back to TEXT/NVARCHAR(MAX)
5	BLOB / binary columns	Always hex-encoded: 0x<hex> for MySQL/SQL Server, E'\\x<hex>' for PostgreSQL, X'<hex>' for SQLite

• claude/issue-124-20260524-0956