code_notebook_migration_sql

CREATE VIEW `code_notebook_migration_sql` AS 
  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