Class: AutoREST::PostgresDB

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

Overview

PostgreSQL adapter for AutoREST.

Uses the ‘pg` gem to connect and interact with a PostgreSQL database. Detects tables and their primary keys by querying PostgreSQL system catalogs.

Examples:

Initialize adapter

db = AutoREST::PostgresDB.new("localhost", 5432, "postgres", "secret", "mydb")

Instance Method Summary collapse

Methods inherited from DBAdapter

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

Constructor Details

#initialize(host, port, user, passwd, dbname) ⇒ PostgresDB

Returns a new instance of PostgresDB.

Parameters:

  • host (String) —

    Hostname of the PostgreSQL server

  • port (Integer) —

    Port number

  • user (String) —

    Username

  • passwd (String) —

    Password

  • dbname (String) —

    Name of the PostgreSQL database



25
26
27
28
 
# File 'lib/autorest/db/postgres.rb', line 25

def initialize(host, port, user, passwd, dbname)
    conn = PG.connect(host: host, port: port, user: user, password: passwd, dbname: dbname)
    super(:pg, dbname, conn)
end

Instance Method Details

#escape(input) ⇒ String

Escapes a string input to safely use in SQL queries.

Parameters:

  • input (String) —

    Raw user input

Returns:

  • (String) —

    Escaped string



77
78
79
 
# File 'lib/autorest/db/postgres.rb', line 77

def escape(input)
    @db_conn.escape_string(input)
end

#exec_sql(sql) ⇒ Array<Hash>

Executes a raw SQL query.

Parameters:

  • sql (String) —

    The SQL query to run

Returns:

  • (Array<Hash>) —

    Resulting rows



70
71
72
 
# File 'lib/autorest/db/postgres.rb', line 70

def exec_sql(sql)
    @db_conn.exec(sql).to_a
end

#prepare ⇒ void

This method returns an undefined value.

Loads table metadata including columns and primary keys.

It excludes system tables by filtering out ‘pg_catalog` and `information_schema` schemas.



35
36
37
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
64
65
 
# File 'lib/autorest/db/postgres.rb', line 35

def prepare
    desc_query = <<-SQL
    SELECT
        a.attname AS cname,
        pg_catalog.format_type(a.atttypid, a.atttypmod) AS dtype,
        coalesce(i.indisprimary, false) AS pk
    FROM
        pg_catalog.pg_attribute a
    JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
    JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
    LEFT JOIN pg_catalog.pg_index i
        ON c.oid = i.indrelid
        AND a.attnum = ANY(i.indkey)
        AND i.indisprimary
    WHERE
        c.relname = $1
        AND a.attnum > 0
        AND NOT a.attisdropped
    ORDER BY a.attnum;
    SQL
    @tables = {}
    @db_conn.exec("SELECT tablename FROM pg_catalog.pg_tables
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')").each do |t|
        tname = t["tablename"]
        row_details = @db_conn.exec_params(desc_query, [tname])
        @tables[tname] = {}
        row_details.each do |row|
            @tables[tname][row["cname"]] = {type: row["type"], pk: row["pk"]}
        end
    end
end