Module: Webhookdb::DBAdapter::DefaultSql

Included in:
PG, Snowflake
Defined in:
lib/webhookdb/db_adapter/default_sql.rb

Constant Summary collapse

PG_RESERVED_KEYWORDS =

These are all PG reserved keywords, as per www.postgresql.org/docs/current/sql-keywords-appendix.html

Set.new(
  [
    "ALL",
    "ANALYSE",
    "ANALYZE",
    "AND",
    "ANY",
    "ARRAY",
    "AS",
    "ASC",
    "ASYMMETRIC",
    "AUTHORIZATION",
    "BINARY",
    "BOTH",
    "CASE",
    "CAST",
    "CHECK",
    "COLLATE",
    "COLLATION",
    "COLUMN",
    "CONCURRENTLY",
    "CONSTRAINT",
    "CREATE",
    "CROSS",
    "CURRENT_CATALOG",
    "CURRENT_DATE",
    "CURRENT_ROLE",
    "CURRENT_SCHEMA",
    "CURRENT_TIME",
    "CURRENT_TIMESTAMP",
    "CURRENT_USER",
    "DECODE",
    "DEFAULT",
    "DEFERRABLE",
    "DESC",
    "DISTINCT",
    "DISTRIBUTED",
    "DO",
    "ELSE",
    "END",
    "EXCEPT",
    "FALSE",
    "FETCH",
    "FOR",
    "FOREIGN",
    "FREEZE",
    "FROM",
    "FULL",
    "GRANT",
    "GROUP",
    "HAVING",
    "ILIKE",
    "IN",
    "INITIALLY",
    "INNER",
    "INTERSECT",
    "INTO",
    "IS",
    "ISNULL",
    "JOIN",
    "LATERAL",
    "LEADING",
    "LEFT",
    "LIKE",
    "LIMIT",
    "LOCALTIME",
    "LOCALTIMESTAMP",
    "LOG",
    "NATURAL",
    "NOT",
    "NOTNULL",
    "NULL",
    "OFFSET",
    "ON",
    "ONLY",
    "OR",
    "ORDER",
    "OUTER",
    "OVERLAPS",
    "PLACING",
    "PRIMARY",
    "REFERENCES",
    "RETURNING",
    "RIGHT",
    "SCATTER",
    "SELECT",
    "SESSION_USER",
    "SIMILAR",
    "SOME",
    "SYMMETRIC",
    "TABLE",
    "THEN",
    "TO",
    "TRAILING",
    "TRUE",
    "UNION",
    "UNIQUE",
    "USER",
    "USING",
    "VARIADIC",
    "VERBOSE",
    "WHEN",
    "WHERE",
    "WINDOW",
    "WITH",
  ],
)
RESERVED_KEYWORDS =

Reserved keywords must be quoted to be used as identifiers.

PG_RESERVED_KEYWORDS

Instance Method Summary collapse

Instance Method Details

#assign_columns_sql(source, destination, columns, &block) ⇒ Object

Return the SQL string for column assignment. Like for src and dest of :src and :tgt, and columns with names :spam and :foo, return “tgt.spam = src.spam, tgt.foo = src.foo” Column names will be escaped; the source and destination values should already be valid identifiers (usually aliases for a table or query).

If a block is given, call it with (column, left hand side string, right hand side string). It should return the new lhs/rhs strings.

Parameters:

  • source (String, nil) —

    Prefix (like table alias) for right hand side columns.

  • destination (String, nil) —

    nil Prefix (like table alias) for left hand side columns.

  • columns (Array<Webhookdb::DBAdapter::Column>) 


65
66
67
68
69
70
71
72
73
74
 
# File 'lib/webhookdb/db_adapter/default_sql.rb', line 65

def assign_columns_sql(source, destination, columns, &block)
  stmts = columns.map do |c|
    cname = self.escape_identifier(c.name)
    lhs = destination ? "#{destination}.#{cname}" : cname
    rhs = source ? "#{source}.#{cname}" : cname
    lhs, rhs = block[c, lhs, rhs] if block
    "#{lhs} = #{rhs}"
  end
  return stmts.join(", ")
end

#create_schema_sql(schema, if_not_exists: false) ⇒ Object 



4
5
6
7
8
9
 
# File 'lib/webhookdb/db_adapter/default_sql.rb', line 4

def create_schema_sql(schema, if_not_exists: false)
  s = +"CREATE SCHEMA "
  s << "IF NOT EXISTS " if if_not_exists
  s << self.escape_identifier(schema.name)
  return s
end

#create_table_sql(table, columns, if_not_exists: false) ⇒ Object 



11
12
13
14
15
16
17
18
19
20
 
# File 'lib/webhookdb/db_adapter/default_sql.rb', line 11

def create_table_sql(table, columns, if_not_exists: false)
  createtable = +"CREATE TABLE "
  createtable << "IF NOT EXISTS " if if_not_exists
  createtable << self.qualify_table(table)
  lines = ["#{createtable} ("]
  columns[0...-1]&.each { |c| lines << "  #{self.column_create_sql(c)}," }
  lines << "  #{self.column_create_sql(columns.last)}"
  lines << ")"
  return lines.join("\n")
end

#escape_identifier(s) ⇒ Object

We write our own escaper because we want to only escape what’s needed; otherwise we want to avoid quoting identifiers.

Raises:

  • (ArgumentError) 


28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'lib/webhookdb/db_adapter/default_sql.rb', line 28

def escape_identifier(s)
  s = s.to_s
  raise ArgumentError, "#{s} is an invalid identifier and should have been validated previously" unless
    Webhookdb::DBAdapter.valid_identifier?(s)

  quo = self.identifier_quote_char
  return "#{quo}#{s}#{quo}" if RESERVED_KEYWORDS.include?(s.upcase) ||
    s.include?(" ") ||
    s.include?("-") ||
    s.start_with?(/\d/)
  return s
end

#identifier_quote_char ⇒ Object

Raises:

  • (NotImplementedError) 


22
23
24
 
# File 'lib/webhookdb/db_adapter/default_sql.rb', line 22

def identifier_quote_char
  raise NotImplementedError
end

#qualify_table(table) ⇒ Object

Parameters:



42
43
44
45
46
47
48
49
50
 
# File 'lib/webhookdb/db_adapter/default_sql.rb', line 42

def qualify_table(table)
  s = +""
  if table.schema
    s << self.escape_identifier(table.schema.name)
    s << "."
  end
  s << self.escape_identifier(table.name)
  return s
end