code_notebook_migration_sql

CREATE VIEW "code_notebook_migration_sql" AS
    
            -- code provenance: `RssdInitSqlNotebook.notebookBusinessLogicViews` (file:///home/baasit/www.surveilr.com/lib/std/lifecycle.sql.ts)
            -- Creates a dynamic migration script by concatenating all interpretable_code for cells that should be migrated.
            -- Excludes cells with names containing '_once_' if they have already been executed.
            -- Includes comments before each block and special comments for excluded cells.
            -- Wraps everything in a single transaction
            SELECT
                'BEGIN TRANSACTION;
    
    '||
                'CREATE TEMP TABLE IF NOT EXISTS "session_state_ephemeral" (
        "key" TEXT PRIMARY KEY NOT NULL,
        "value" TEXT NOT NULL
    );
    
    ' ||
                GROUP_CONCAT(
                  CASE
                      -- Case 1: Non-idempotent and already executed
                      WHEN c.is_idempotent = FALSE AND s.code_notebook_cell_id IS NOT NULL THEN
                          '-- ' || c.notebook_name || '.' || c.cell_name || ' not included because it is non-idempotent and was already executed on ' || s.transitioned_at || '
    '
    
                      -- Case 2: Idempotent and not yet executed, idempotent and being reapplied, or non-idempotent and being run for the first time
                      ELSE
                          '-- ' || c.notebook_name || '.' || c.cell_name || '
    ' ||
                          CASE
                              -- First execution (non-idempotent or idempotent)
                              WHEN s.code_notebook_cell_id IS NULL THEN
                                  '-- Executing for the first time.
    '
    
                              -- Reapplying execution (idempotent)
                              ELSE
                                  '-- Reapplying execution. Last executed on ' || s.transitioned_at || '
    '
                          END ||
                          c.interpretable_code || '
    ' ||
                          'INSERT INTO code_notebook_state (code_notebook_state_id, code_notebook_cell_id, from_state, to_state, transition_reason, created_at) ' ||
                          'VALUES (' ||
                          '''' || c.code_notebook_cell_id || '__' || strftime('%Y%m%d%H%M%S', 'now') || '''' || ', ' ||
                          '''' || c.code_notebook_cell_id || '''' || ', ' ||
                          '''MIGRATION_CANDIDATE''' || ', ' ||
                          '''EXECUTED''' || ', ' ||
                          CASE
                              WHEN s.code_notebook_cell_id IS NULL THEN '''Migration'''
                              ELSE '''Reapplication'''
                          END || ', ' ||
                          'CURRENT_TIMESTAMP' || ')' || '
    ' ||
                          'ON CONFLICT(code_notebook_cell_id, from_state, to_state) DO UPDATE SET updated_at = CURRENT_TIMESTAMP, ' ||
                            'transition_reason = ''Reapplied ' || datetime('now', 'localtime') || ''';' || '
    '
                  END,
                  '
    '
                ) || '
    
    COMMIT;' AS migration_sql
            FROM
                code_notebook_sql_cell_migratable c
            LEFT JOIN
                code_notebook_state s
                ON c.code_notebook_cell_id = s.code_notebook_cell_id AND s.to_state = 'EXECUTED'
            ORDER BY
                c.cell_name