-
Notifications
You must be signed in to change notification settings - Fork 23
/
sqlite.cr
99 lines (85 loc) · 3.58 KB
/
sqlite.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
require "./concerns/*"
module Marten
module DB
module Management
module Introspector
class SQLite < Base
include Core
def columns_details(table_name : String) : Array(ColumnInfo)
results = [] of ColumnInfo
@connection.open do |db|
db.query("PRAGMA table_info(#{table_name})") do |rs|
rs.each do
rs.read(::DB::Any) # column index
column_name = rs.read(String)
type = rs.read(String)
is_nullable = (rs.read(Int64) == 0)
column_default = rs.read(::DB::Any)
results << ColumnInfo.new(
name: column_name,
type: type,
nullable: is_nullable,
default: column_default
)
end
end
end
results
end
def foreign_key_constraint_names(table_name : String, column_name : String) : Array(String)
[] of String
end
def index_names(table_name : String, column_name : String) : Array(String)
indexes_to_columns = {} of String => Array(String)
@connection.open do |db|
db.query(
build_sql do |s|
s << "SELECT il.name, ii.name"
s << "FROM sqlite_master AS m, pragma_index_list(m.name) AS il, pragma_index_info(il.name) AS ii"
s << "WHERE m.type='table' AND m.tbl_name='#{table_name}'"
s << "ORDER BY 1;"
end
) do |rs|
rs.each do
index_name = rs.read(String)
index_column_name = rs.read(String)
indexes_to_columns[index_name] ||= [] of String
indexes_to_columns[index_name] << index_column_name
end
end
end
indexes_to_columns.select { |_k, v| v == [column_name] }.keys
end
def primary_key_constraint_names(table_name : String, column_name : String) : Array(String)
# SQLite does not name primary key constraints. We also don't really need them as they are never dropped by
# the framework (tables are re-created in the context of column changes).
[] of String
end
def unique_constraint_names(table_name : String, column_name : String) : Array(String)
unique_indexes_to_columns = {} of String => Array(String)
@connection.open do |db|
db.query(
build_sql do |s|
s << "SELECT il.name AS constraint_name, ii.name AS column_name"
s << "FROM sqlite_master AS m, pragma_index_list(m.name) AS il, pragma_index_info(il.name) AS ii"
s << "WHERE m.type = 'table' AND il.origin = 'u' AND m.tbl_name = '#{table_name}'"
end
) do |rs|
rs.each do
constraint_name = rs.read(String)
constraint_column_name = rs.read(String)
unique_indexes_to_columns[constraint_name] ||= [] of String
unique_indexes_to_columns[constraint_name] << constraint_column_name
end
end
end
unique_indexes_to_columns.select { |_k, v| v == [column_name] }.keys
end
private def list_table_names_statement : String
"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name"
end
end
end
end
end
end