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