| 493 | * @since 4.0.0 |
| 494 | */ |
| 495 | export const layerSql = ( |
| 496 | options: LayerSqlOptions = {} |
| 497 | ): Layer.Layer<KeyValueStore, never, SqlClient.SqlClient> => |
| 498 | Layer.effect(KeyValueStore)( |
| 499 | Effect.gen(function*() { |
| 500 | const sql = (yield* SqlClient.SqlClient).withoutTransforms() |
| 501 | const table = sql(options.table ?? "effect_key_value_store") |
| 502 | |
| 503 | yield* sql.onDialectOrElse({ |
| 504 | mysql: () => |
| 505 | sql` |
| 506 | CREATE TABLE IF NOT EXISTS ${table} ( |
| 507 | id VARCHAR(191) PRIMARY KEY, |
| 508 | value BLOB NOT NULL, |
| 509 | value_type SMALLINT NOT NULL |
| 510 | ) |
| 511 | `, |
| 512 | pg: () => |
| 513 | sql` |
| 514 | CREATE TABLE IF NOT EXISTS ${table} ( |
| 515 | id TEXT PRIMARY KEY, |
| 516 | value BYTEA NOT NULL, |
| 517 | value_type SMALLINT NOT NULL |
| 518 | ) |
| 519 | `, |
| 520 | mssql: () => |
| 521 | sql` |
| 522 | IF NOT EXISTS (SELECT * FROM sysobjects WHERE name=${table} AND xtype='U') |
| 523 | CREATE TABLE ${table} ( |
| 524 | id NVARCHAR(450) PRIMARY KEY, |
| 525 | value VARBINARY(MAX) NOT NULL, |
| 526 | value_type SMALLINT NOT NULL |
| 527 | ) |
| 528 | `, |
| 529 | // sqlite |
| 530 | orElse: () => |
| 531 | sql` |
| 532 | CREATE TABLE IF NOT EXISTS ${table} ( |
| 533 | id TEXT PRIMARY KEY, |
| 534 | value BLOB NOT NULL, |
| 535 | value_type INTEGER NOT NULL |
| 536 | ) |
| 537 | ` |
| 538 | }).pipe(Effect.orDie) |
| 539 | |
| 540 | type UpsertFn = (entry: { |
| 541 | id: string |
| 542 | value: Uint8Array |
| 543 | value_type: number |
| 544 | }) => Effect.Effect<unknown, SqlError> |
| 545 | |
| 546 | const upsert = sql.onDialectOrElse({ |
| 547 | pg: (): UpsertFn => (entry) => |
| 548 | sql` |
| 549 | INSERT INTO ${table} (id, value, value_type) VALUES (${entry.id}, ${entry.value}, ${entry.value_type}) |
| 550 | ON CONFLICT (id) DO UPDATE SET value=EXCLUDED.value, value_type=EXCLUDED.value_type |
| 551 | `.unprepared, |
| 552 | mysql: (): UpsertFn => (entry) => |