/
pg.cr
167 lines (141 loc) · 4.62 KB
/
pg.cr
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
require "./base"
require "pg"
# PostgreSQL implementation of the Adapter
class Granite::Adapter::Pg < Granite::Adapter::Base
QUOTING_CHAR = '"'
module Schema
TYPES = {
"Float32" => "REAL",
"Float64" => "DOUBLE PRECISION",
"String" => "TEXT",
"AUTO_Int32" => "SERIAL",
"AUTO_Int64" => "BIGSERIAL",
"AUTO_UUID" => "UUID",
"UUID" => "UUID",
"created_at" => "TIMESTAMP",
"updated_at" => "TIMESTAMP",
"Array(String)" => "TEXT[]",
"Array(Int16)" => "SMALLINT[]",
"Array(Int32)" => "INT[]",
"Array(Int64)" => "BIGINT[]",
"Array(Float32)" => "REAL[]",
"Array(Float64)" => "DOUBLE PRECISION[]",
"Array(Bool)" => "BOOLEAN[]",
}
end
# remove all rows from a table and reset the counter on the id.
def clear(table_name : String)
statement = "DELETE FROM #{quote(table_name)}"
elapsed_time = Time.measure do
open do |db|
db.exec statement
end
end
log statement, elapsed_time
end
def insert(table_name : String, fields, params, lastval) : Int64
statement = String.build do |stmt|
stmt << "INSERT INTO #{quote(table_name)} ("
stmt << fields.map { |name| "#{quote(name)}" }.join(", ")
stmt << ") VALUES ("
stmt << position_str(fields.size)
stmt << ")"
stmt << " RETURNING #{quote(lastval)}" if lastval
end
last_id = -1_i64
elapsed_time = Time.measure do
open do |db|
if lastval
last_id = db.scalar(statement, args: params).as(Int32 | Int64).to_i64
else
db.exec statement, args: params
end
end
end
log statement, elapsed_time, params
last_id
end
def import(table_name : String, primary_name : String, auto : Bool, fields, model_array, **options)
params = [] of Granite::Columns::Type
# PG fails when inserting null into AUTO INCREMENT PK field.
# If AUTO INCREMENT is TRUE AND all model's pk are nil, remove PK from fields list for AUTO INCREMENT to work properly
fields.reject! { |field| field == primary_name } if model_array.all? { |m| m.to_h[primary_name].nil? } && auto
index = 0
statement = String.build do |stmt|
stmt << "INSERT"
stmt << " INTO #{quote(table_name)} ("
stmt << fields.map { |field| quote(field) }.join(", ")
stmt << ") VALUES "
model_array.each do |model|
model.set_timestamps
next unless model.valid?
stmt << '('
stmt << fields.map_with_index { |_f, idx| "$#{index + idx + 1}" }.join(',')
params.concat fields.map { |field| model.read_attribute field }
stmt << "),"
index += fields.size
end
end.chomp(',')
if options["update_on_duplicate"]?
if columns = options["columns"]?
statement += " ON CONFLICT (#{quote(primary_name)}) DO UPDATE SET "
columns << "updated_at" if fields.includes? "updated_at"
columns.each do |key|
statement += "#{quote(key)}=EXCLUDED.#{quote(key)}, "
end
end
statement = statement.chomp(", ")
elsif options["ignore_on_duplicate"]?
statement += " ON CONFLICT DO NOTHING"
end
elapsed_time = Time.measure do
open do |db|
db.exec statement, args: params
end
end
log statement, elapsed_time, params
end
# This will update a row in the database.
def update(table_name : String, primary_name : String, fields, params)
statement = String.build do |stmt|
stmt << "UPDATE #{quote(table_name)} SET "
stmt << fields.map_with_index { |name, i| "#{quote(name)}=$#{i + 1}" }.join(", ")
stmt << " WHERE #{quote(primary_name)}=$#{fields.size + 1}"
end
elapsed_time = Time.measure do
open do |db|
db.exec statement, args: params
end
end
log statement, elapsed_time, params
end
# This will delete a row from the database.
def delete(table_name : String, primary_name : String, value)
statement = "DELETE FROM #{quote(table_name)} WHERE #{quote(primary_name)}=$1"
elapsed_time = Time.measure do
open do |db|
db.exec statement, value
end
end
log statement, elapsed_time, value
end
protected def ensure_clause_template(clause : String) : String
if clause.includes?("?")
num_subs = clause.count("?")
num_subs.times do |i|
clause = clause.sub("?", "$#{i + 1}")
end
end
clause
end
private def position_str(n : Int32) : String
i = 1
String.build do |str|
while i <= n
str << "$" << i
i += 1
str << ", " if i <= n
end
end
end
end