Repair Sqitch data structure that has been nuked

[fluca1978]

By accindent, the tables into a sqitch database have been removed, so that now I've a not working target.
I have data from another target that I can manually copy, but I would like to have data structure rebuilt by sqitch. The engine is Oracle.

% sqitch status test
# On database test
No changes deployed


% sqitch deploy test
Adding registry tables to test
CREATE TYPE test.sqitch_array AS varray(1024) OF VARCHAR2(512);
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object



/opt/oracle/instantclient_21_11/sqlplus unexpectedly returned exit value 187





% sqitch log test
ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 1155 in 'SELECT * FROM ( SELECT ROWNUM AS rnum, i.* FROM (
        SELECT event
             , project
             , change_id
             , change
             , note
             , requires
             , conflicts
             , tags
             , committer_name
             , committer_email
             , to_char(committed_at AT TIME ZONE 'UTC', '"year":YYYY') || to_char(committed_at AT TIME ZONE 'UTC', ':"month":MM') || to_char(committed_at AT TIME ZONE 'UTC', ':"day":DD') || to_char(committed_at AT TIME ZONE 'UTC', ':"hour":HH24') || to_char(committed_at AT TIME ZONE 'UTC', ':"minute":MI') || to_char(committed_at AT TIME ZONE 'UTC', ':"second":SS') || ':time_zone:UTC' AS committed_at
             , planner_name
             , planner_email
             , to_char(planned_at AT TIME ZONE 'UTC', '"year":YYYY') || to_char(planned_at AT TIME ZONE 'UTC', ':"month":MM') || to_char(planned_at AT TIME ZONE 'UTC', ':"day":DD') || to_char(planned_at AT TIME ZONE 'UTC', ':"hour":HH24') || to_char(planned_at AT TIME ZONE 'UTC', ':"minute":MI') || to_char(planned_at AT TIME ZONE 'UTC', ':"second":SS') || ':time_zone:UTC' AS planned_at
          FROM <*>events
         ORDER BY events.committed_at DESC
    ) i ) WHERE rnum <= :p1')

The sqitch upgrade command does not work.
Any chance I can resume the data structure (and possibly copy the last change from another target)?

[theory]

So the tables don't exist but the array type does? Can you drop that type?

[fluca1978]

So the tables don't exist but the array type does? Can you drop that type?

Yes, dropping also the type fixed the issue (I was wondering other objects could have been spread across the schema).

Now, however, I have the plan file that contains changes already applied, so sqitch is not able to re-deploy them.

% sqitch deploy test  
Deploying changes to test
  + anno_definitivo ................       ANNO_DEFINITIVO INTEGER
      *
ERROR at line 2:
ORA-01430: column being added already exists in table

Is there a way to make sqitch "jump" to the HEAD of the changes or do I have to manually copy data from the other database into the sqitch tables?

[theory]

Yeah, use sqitch deploy --log-only. That will record the changes without running the scripts.

[fluca1978]

Yeah, use sqitch deploy --log-only. That will record the changes without running the scripts.

Thanks, @theory, it worked!