~ralcini


#103 oracle batch script migrations 2 months ago

Comment by ~ralcini on ~olly/yoyo

It's not the best patch, but I've not analyzed where to build and define a more clean piece of code

#103 oracle batch script migrations 2 months ago

Ticket created by ~ralcini on ~olly/yoyo

From cx_oracle documentation: https://cx-oracle.readthedocs.io/en/latest/user_guide/batch_statement.html

You need to use cursor.executemany(sql, data)

the following patch works for me..

--- /python3.7/site-packages/yoyo/migrations.py 2024-05-08 14:41:38.000000000 +0200 +++ /python3.7/site-packages/yoyo/migrations.py_new 2024-05-20 13:35:13.209572376 +0200 @@ -375,7 +375,7 @@ logger.debug(" - executing %r", stmt.encode("ascii", "replace")) else: logger.debug(" - executing %r", stmt)

  •    cursor.execute(stmt)
    
  •    cursor.executemany(stmt, [])
       if cursor.description:
           result = [[str(value) for value in row] for row in cursor.fetchall()]
           column_names = [desc[0] for desc in cursor.description]
    

#100 cx_oracle, db API and named param style 2 months ago

Comment by ~ralcini on ~olly/yoyo

Hi, the target version is yoyo 9? It seems not working on python 3.7.9

File "----lib/python3.7/site-packages/yoyo/utils.py", line 133, in bind_parameters: t.Optional[abc.Mapping[str, t.Any]] TypeError: 'ABCMeta' object is not subscriptable

#102 cx_oracle _yoyo_log "comment" column 2 months ago

Comment by ~ralcini on ~olly/yoyo

"comment" value as None is bassed as parameter to the query executed causing an insert error because not used...

#102 cx_oracle _yoyo_log "comment" column 2 months ago

Ticket created by ~ralcini on ~olly/yoyo

In oracle, comment is a keyword... it cannot be used in a schema definition as:

CREATE TABLE "_yoyo_log" ( id VARCHAR(36), migration_hash VARCHAR(64), migration_id VARCHAR(255), operation VARCHAR(10), username VARCHAR(255), hostname VARCHAR(255), comment VARCHAR(255), created_at_utc TIMESTAMP, PRIMARY KEY (id))

you need to add doublequotes in order to avoid this issue in internalmigrations/v2.py

def create_log_table(backend): backend.execute( "CREATE TABLE {0.log_table_quoted} ( " "id VARCHAR(36), " "migration_hash VARCHAR(64), " "migration_id VARCHAR(255), " "operation VARCHAR(10), " "username VARCHAR(255), " "hostname VARCHAR(255), " ""comment" VARCHAR(255), " "created_at_utc TIMESTAMP, " "PRIMARY KEY (id))".format(backend) )

But the comment column is really used? _yoyo_log seems to be used only in base.py and there the insert stament do not fullfill the "comment" column. Or I'm wrong?

#101 cx_oracle list_tables invalid binding 2 months ago

Ticket created by ~ralcini on ~olly/yoyo

the method list_tables in base.py assumes to pass to the query execution the database name in a dict, but in the query executed there is not requested any parameters.

I've a traceback: yoyo/internalmigrations/init.py", line 45, in get_current_version tables = set(backend.list_tables()) File "/opt/omcgc_env/lib/python3.7/site-packages/yoyo/backends/base.py", line 254, in list_tables dict({"database": self.uri.database}, **kwargs), File "/opt/omcgc_env/lib/python3.7/site-packages/yoyo/backends/base.py", line 390, in execute cursor.execute(sql, params) cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

I fixed inserting into backends/contrib/oracle.py the method override

def list_tables(self, **kwargs):
    """
    Return a list of tables present in the backend.
    This is used by the test suite to clean up tables
    generated during testing
    """
    cursor = self.execute(
        self.list_tables_sql,
        dict(**kwargs),
    )
    return [row[0] for row in cursor.fetchall()]

#100 cx_oracle, db API and named param style 2 months ago

Comment by ~ralcini on ~olly/yoyo

let me know if you need some other details

#100 cx_oracle, db API and named param style 2 months ago

Ticket created by ~ralcini on ~olly/yoyo

Hi, during yoyo initialization using cx_oracle drivers we had the below issue:

<<<<>>>>/lib/python3.7/site-packages/yoyo/backends/base.py", line 388, in execute cursor.execute(sql, params) TypeError: expecting a dictionary, sequence or keyword args

when executing the following statement:

CREATE TABLE "yoyo_lock" (locked INT DEFAULT 1, ctime TIMESTAMP,pid INT NOT NULL,PRIMARY KEY (locked))

I've a look to the sources and I suppose that the fix can be placed in yoyo/utils.py in method def change_param_style(target_style, sql, bind_parameters): assigning a default value to bind_parameters

if target_style == "named": return sql, bind_parameters if bind_parameters else {}