Class: AutoREST::OracleDB

Inherits:
DBAdapter show all
Defined in:
lib/autorest/db/oracle.rb

Overview

Oracle DB adapter for AutoREST.

Uses the ‘oci8` gem to connect and interact with an Oracle database. Retrieves tables and primary key details from Oracle’s user-owned tables and constraints.

Examples:

Initialize adapter

db = AutoREST::OracleDB.new("localhost", 1521, "sys", "secret", "ORCL")

Instance Method Summary collapse

Methods inherited from DBAdapter

#columns, #del_row, #insert, #row, #rows, #set_access_tables, #tables, #update

Constructor Details

#initialize(host, port, user, passwd, sid) ⇒ OracleDB

Returns a new instance of OracleDB.

Parameters:

  • host (String) —

    Hostname of the Oracle server

  • port (Integer) —

    Port number

  • user (String) —

    Username

  • passwd (String) —

    Password

  • sid (String) —

    Oracle SID (System Identifier)



26
27
28
29
30
 
# File 'lib/autorest/db/oracle.rb', line 26

def initialize(host, port, user, passwd, sid)
    conn = OCI8.new(user, passwd, "//#{host}:#{port}/#{sid}")
    conn.autocommit = true
    super(:orcl, sid, conn)
end

Instance Method Details

#close ⇒ void

This method returns an undefined value.

Closes the database connection.



80
81
82
 
# File 'lib/autorest/db/oracle.rb', line 80

def close
    @db_conn.logoff
end

#escape(input) ⇒ String

Escapes a string input to safely use in SQL queries.

Parameters:

  • input (String) —

    Raw user input

Returns:

  • (String) —

    Escaped string



87
88
89
 
# File 'lib/autorest/db/oracle.rb', line 87

def escape(input)
    input.to_s.gsub("'", "''")
end

#exec_sql(sql) ⇒ Array<Hash>

Executes a raw SQL query.

Parameters:

  • sql (String) —

    The SQL query to run

Returns:

  • (Array<Hash>) —

    Resulting rows



68
69
70
71
72
73
74
75
76
 
# File 'lib/autorest/db/oracle.rb', line 68

def exec_sql(sql)
    cursor = @db_conn.exec(sql)
    cols = cursor.get_col_names
    res = []
    while row = cursor.fetch
        res << Hash[cols.zip(row)]
    end
    res
end

#prepare ⇒ void

This method returns an undefined value.

Loads table metadata including columns and primary keys.

Queries Oracle’s ‘user_tab_columns` and `user_cons_columns` system views to get the column details and primary key information.



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/autorest/db/oracle.rb', line 38

def prepare
    desc_query = <<~SQL
    SELECT c.column_name,
            c.data_type,
            CASE WHEN pk.pk_column IS NOT NULL THEN 'YES' ELSE 'NO' END AS primary_key
    FROM user_tab_columns c
    LEFT JOIN (
        SELECT ucc.column_name AS pk_column
        FROM user_cons_columns ucc
        JOIN user_constraints uc
        ON ucc.constraint_name = uc.constraint_name
        WHERE uc.constraint_type = 'P'
        AND uc.table_name = :1
    ) pk ON c.column_name = pk.pk_column
    WHERE c.table_name = :1
    SQL

    @tables = {}
    @db_conn.exec("select * from cat") do |t|
        tname = t[0]
        @tables[tname] = {}
        @db_conn.exec(desc_query, tname) do |row|
            @tables[tname][row[0]] = {type: row[1], pk: row[2] == "YES"}
        end
    end
end