Java Code Examples for org.skife.jdbi.v2.Handle#update()

private static int deleteJsonRecords(Handle dbi, String baseDBPath, String like) {

        ArrayList<String> expressions = new ArrayList<>();
        ArrayList<String> queryParams = new ArrayList<>();
        for (String p : getAllParentPaths(baseDBPath)) {
            expressions.add("path = ?");
            queryParams.add(p);
        }
        expressions.add("path LIKE ?");
        queryParams.add(like);

        StringBuilder sql = new StringBuilder("DELETE FROM jsondb WHERE ");
        sql.append(String.join(" OR ", expressions));

        return dbi.update(sql.toString(), queryParams.toArray());
    }
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    if (context.isPostgres()) {
        handle.update("alter table projects" +
                " add column deleted_at timestamp with time zone");
        handle.update("alter table projects" +
                " add column deleted_name text");
        handle.update("alter table projects" +
                " alter column name drop not null");
    }
    else {
        handle.update("alter table projects" +
                " add column deleted_at timestamp");
        handle.update("alter table projects" +
                " add column deleted_name varchar(255)");
        handle.update("alter table projects" +
                " alter column name drop not null");
    }
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    handle.update(
            context.newCreateTableBuilder("secrets")
                    .addLongId("id")
                    .addLong("site_id", "not null")
                    .addLong("project_id", "not null references projects (id)")
                    .addString("scope", "not null")
                    .addString("engine", "not null")
                    .addString("key", "not null")
                    .addLongText("value", "not null")
                    .addTimestamp("updated_at", "not null")
                    .build());

    handle.update("create index secrets_on_site_id_and_project_id_and_scope_and_key on secrets (site_id, project_id, scope, key)");
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    if (context.isPostgres()) {
        handle.update("alter table schedules" +
                " add column disabled_at timestamp with time zone");
    }
    else {
        handle.update("alter table schedules" +
                " add column disabled_at timestamp");
    }

    if (context.isPostgres()) {
        handle.update("drop index schedules_on_next_run_time");
        handle.update("create index schedules_on_next_run_time on schedules (next_run_time) where disabled_at is null");
    }
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    if (context.isPostgres()) {
        handle.update("alter table tasks" +
                " add column started_at timestamp with time zone");
    }
    else {
        handle.update("alter table tasks" +
                " add column started_at timestamp");
    }

    if (context.isPostgres()) {
        handle.update("create index tasks_on_state_and_started_at on tasks (state, started_at, id asc) where started_at is not null");
    } else {
        handle.update("create index tasks_on_state_and_started_at on tasks (state, started_at, id asc)");
    }
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    if (context.isPostgres()) {
        handle.update("alter table sessions" +
                " add column last_attempt_created_at timestamp with time zone");
        handle.update(
                "update sessions" +
                " set last_attempt_created_at = session_attempts.created_at" +
                " from session_attempts" +
                " where session_attempts.id = sessions.last_attempt_id");
    }
    else {
        handle.update("alter table sessions" +
                " add column last_attempt_created_at timestamp");
        handle.update("update sessions" +
                " set last_attempt_created_at = (" +
                    " select created_at from session_attempts" +
                    " where session_attempts.id = sessions.last_attempt_id" +
                ")");
    }

    handle.update("create index sessions_on_project_id_and_workflow_name_and_last_attempt_created_at on sessions (project_id, workflow_name, last_attempt_created_at desc)");
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    // DatabaseSessionStoreManager.getActiveAttemptCount uses this index.
    if (context.isPostgres()) {
        handle.update("create index concurrently session_attempts_on_site_id_and_state_flags_partial_2 on session_attempts"
                + " using btree(site_id) where state_flags & 2 = 0");
    }
    else {
        // H2 does not support partial index
    }
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    handle.update(
            context.newCreateTableBuilder("delayed_session_attempts")
            .addLongIdNoAutoIncrement("id", "references session_attempts (id)")
            .addLong("dependent_session_id", "")
            .addLong("next_run_time", "not null")
            .addTimestamp("updated_at", "not null")
            .build());
    handle.update("create index delayed_session_attempts_on_next_run_time on delayed_session_attempts (next_run_time)");
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    // DatabaseProjectStoreManager.PgDao.getLatestActiveWorkflowDefinitions uses these indexes.
    handle.update("create index workflow_definitions_on_revision_id_and_id on workflow_definitions (revision_id, id)");
    if (context.isPostgres()) {
        handle.update("create index projects_on_site_id_and_id on projects (site_id, id) where deleted_at is null");
    }
    else {
        handle.update("create index projects_on_site_id_and_id on projects (site_id, id)");
    }
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    if (context.isPostgres()) {
        // make sure records are unique
        handle.update("delete from secrets where id = any(select id from (select row_number() over (partition by site_id, project_id, scope, key order by id) as i, id from secrets) win where i > 1)");
    }
    else {
        // h2 doesn't support window function...
    }
    handle.update("create unique index secrets_unique_on_site_id_and_project_id_and_scope_and_key on secrets (site_id, project_id, scope, key)");
    handle.update("drop index secrets_on_site_id_and_project_id_and_scope_and_key");
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    handle.update("alter table task_state_details" +
            " add column reset_store_params text");
    handle.update("alter table resuming_tasks" +
            " add column reset_store_params text");
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    // resuming_tasks
    handle.update(
            context.newCreateTableBuilder("resuming_tasks")
            .addLongId("id")
            .addLong("attempt_id", "not null references session_attempts (id)")
            .addLong("source_task_id", "not null")
            .addMediumText("full_name", "not null")
            .addTimestamp("updated_at", "not null")
            .addMediumText("local_config", "")
            .addMediumText("export_config", "")
            .addMediumText("subtask_config", "")
            .addMediumText("export_params", "")
            .addMediumText("store_params", "")
            .addMediumText("report", "")
            .addMediumText("error", "")
            .build());
    if (context.isPostgres()) {
        handle.update("create index resuming_tasks_on_attempt_id_and_full_name on resuming_tasks (attempt_id, full_name)");
    }
    else {
        // h2 doesn't support index on text
        handle.update("create index resuming_tasks_on_attempt_id on resuming_tasks (attempt_id)");
    }

    // task_details.resuming_task_id
    handle.update("alter table task_details" +
            " add column resuming_task_id bigint");
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    if (context.isPostgres()) {
        handle.update("create index concurrently sessions_on_project_id_and_workflow_name_desc on sessions (project_id, workflow_name, id DESC)");
    } else {
        handle.update("create index sessions_on_project_id_and_workflow_name_desc on sessions (project_id, workflow_name, id DESC)");
    }
}
 

@VisibleForTesting
public void createSchemaMigrationsTable(Handle handle, MigrationContext context)
{
    handle.update(
            context.newCreateTableBuilder("schema_migrations")
            .addString("name", "not null")
            .addTimestamp("created_at", "not null")
            .build());
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    if (context.isPostgres()) {
        handle.update(
                "update session_attempts set index = seq.index " +
                "from (" +
                    "select id, rank() over (partition by session_id order by id) as index from session_attempts" +
                ") seq " +
                "where session_attempts.id = seq.id");
    }
    else {
        List<IdAndSessionId> list =
            handle.createQuery("select id, session_id from session_attempts order by session_id, id")
            .map((index, r, ctx) -> new IdAndSessionId(r.getLong("id"), r.getLong("session_id")))
            .list();
        long lastSessionId = 0L;
        long lastIndex = 0;
        for (IdAndSessionId s : list) {
            if (lastSessionId != s.sessionId) {
                lastSessionId = s.sessionId;
                lastIndex = 0;
            }
            lastIndex++;
            handle.createStatement("update session_attempts set index = :index where id = :id")
                .bind("id", s.id)
                .bind("index", lastIndex)
                .execute();
        }
    }

    handle.update("alter table session_attempts" +
            " alter column index set not null");

    handle.update("create unique index session_attempts_on_session_id_and_index on session_attempts (session_id, index desc)");
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    handle.update("alter table session_attempts" +
            " add column index int");
}
 

private static boolean doDelete(Handle h, String path) {
    return h.update("DELETE FROM filestore WHERE path=?", path) > 0;
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    // queued_task_locks
    handle.update("drop table queued_task_locks");
    handle.update("drop table queued_shared_task_locks");

    handle.update(
            context.newCreateTableBuilder("queued_task_locks")
            .addLongId("id")  // references queued_tasks.id
            .addInt("site_id", "")
            .addInt("queue_id", "")
            .addInt("priority", "not null")
            .addInt("retry_count", "not null default 0")
            .addLong("lock_expire_time", "")
            .addString("lock_agent_id", "")
            .build());

    handle.update("insert into queued_task_locks" +
            " (id, site_id, queue_id, priority)" +
            " select id, site_id, NULL, priority" +
            " from queued_tasks");

    // queues
    handle.update("alter table queues" +
            " add column shared_site_id int");

    // resource_types
    handle.update("drop table resource_types");

    // queued_tasks
    handle.update("alter table queued_tasks" +
            " alter column task_id drop not null");
    handle.update("alter table queued_tasks" +
            " alter column queue_id drop not null");
    handle.update("alter table queued_tasks" +
            " alter column data drop not null");
    handle.update("alter table queued_tasks" +
            " drop column priority");
    handle.update("alter table queued_tasks" +
            " drop column resource_type_id");
    handle.update("create unique index queued_tasks_on_site_id_task_id on queued_tasks (site_id, task_id)");

    if (context.isPostgres()) {
        handle.update(
            "CREATE FUNCTION lock_shared_tasks(target_site_id int, target_site_max_concurrency bigint, limit_count int, lock_expire_seconds int, agent_id text) returns setof bigint as $$\n" +
            "BEGIN\n" +
            "  IF pg_try_advisory_xact_lock(23300, target_site_id) THEN\n" +
            "    RETURN QUERY\n" +
            "      with updated as (\n" +
            "        update queued_task_locks\n" +
            "        set lock_expire_time = cast(extract(epoch from statement_timestamp()) as bigint) + lock_expire_seconds,\n" +
            "            lock_agent_id = agent_id\n" +
            "        where id = any(\n" +
            "          select queued_task_locks.id\n" +
            "          from queued_task_locks\n" +
            "          where lock_expire_time is null\n" +
            "          and site_id = target_site_id\n" +
            "          and not exists (\n" +
            "            select * from (\n" +
            "              select queue_id, count(*) as count\n" +
            "              from queued_task_locks\n" +
            "              where lock_expire_time is not null\n" +
            "                and site_id = target_site_id\n" +
            "              group by queue_id\n" +
            "            ) runnings\n" +
            "            join queues on queues.id = runnings.queue_id\n" +
            "            where runnings.count >= queues.max_concurrency\n" +
            "              and runnings.queue_id = queued_task_locks.queue_id\n" +
            "          )\n" +
            "          and not exists (\n" +
            "            select count(*)\n" +
            "            from queued_task_locks\n" +
            "            where lock_expire_time is not null\n" +
            "              and site_id = target_site_id\n" +
            "            having count(*) >= target_site_max_concurrency\n" +
            "          )\n" +
            "          order by queue_id, priority desc, id\n" +
            "          limit limit_count\n" +
            "        )\n" +
            "        returning queue_id, priority, id\n" +
            "      )\n" +
            "      select id from updated\n" +
            "      order by queue_id, priority desc, id;\n" +
            "  END IF;\n" +
            "END;\n" +
            "$$ LANGUAGE plpgsql VOLATILE\n" +
        "");

        handle.update("create index queued_tasks_shared_grouping on queued_task_locks (site_id, queue_id) where site_id is not null and lock_expire_time is not null");
        handle.update("create index queued_tasks_ordering on queued_task_locks (site_id, queue_id, priority desc, id) where lock_expire_time is null");
        handle.update("create index queued_tasks_expiration on queued_task_locks (lock_expire_time) where lock_expire_time is not null");
    }
    else {
        handle.update("create index queued_tasks_shared_grouping on queued_task_locks (lock_expire_time, site_id, queue_id)");
        handle.update("create index queued_tasks_ordering on queued_task_locks (site_id, queue_id, lock_expire_time, priority desc, id)");
    }
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    handle.update("alter table revisions" +
            " add column user_info text");
}
 

@Override
public void migrate(Handle handle, MigrationContext context)
{
    handle.update("create index sessions_on_project_id_desc on sessions (project_id, id desc)");
    handle.update("drop index sessions_on_project_id");
}