端端舝

SQLQL 午反ㄐˋSQLQL 午反ㄐˋ
2019/04/24 緉釱Rails#8
@yancya
Powered by Rabbit 2.2.2 and COZMIXNG

@yancya 及赻撩畿賡
Rubyist, SQList
ロ��I燴假�復悵盓堔尪

價掛腔卅仇午
Ruby on Rails 匹失皿伉弗奈扑亦
件毛嶱逃仄化中化毯ʃ俶卞卅月
由正奈件
Rails 卞�𡛟仄化中卅中偞�
Rails 及伙奈伙卞𣶹勻化中卅中偞�

SQLQL 午反
https://somehost/sqlql 心凶中卅
URL 卞 SQL 毛冞陓允月午﹜SQL
灍俴磐彆及 JSON 互殿勻化仁月午
中丹衙癩

SQL 及灍俴磐彆互 JSON 午
反
SELECT id, name FROM user
勻化支勻凶日
[{"id":10,"name":"yancya"},
{"id":11,"name":"testuser"}]
心凶中卅及互殿勻化仁月

冞陓允月午﹜午反
恅趼籵曰﹜伉弁巨旦玄卞公及引引
SQL 毛漪戶月
curl -X GET
-H 'Authorization: Token 6f916ae6-8472-463a-9808-6af19e459541'
-F "haute_couture[query]=select id, name from users"
https://sqlql-sample-yancya.herokuapp.com/haute_couture

庲偩仄化中月ˋ
仄化中月﹝公仄化﹜庲偩今木凶交奈
扒奈卞統桽互偝褫今木化中月伊戊奈
玉及心互殿勻化仁月
∣ 反 secretman 午中丹交奈扒奈及
玄奈弁件毛妏勻凶伉弁巨旦玄
curl -X GET
-H 'Authorization: Token 4f3326a4-b900-4624-af58-87e8f363dee6'
-F "haute_couture[query]=select id, name from users"

偝褫今木化中月伊戊奈玉及心
互
伉弁巨旦玄仄凶交奈扒奈及庲褫卞𡛟
元凶伊旦禾件旦午卅月
secretman 及伉弁巨旦玄卞反
secretman 及伊戊奈玉互漪引木月
[{"id":10,"name":"yancya"},
{"id":11,"name":"testuser"},
{"id":14,"name":"secretman"}]

生旦玄仄凶俴反殿六卅仁剠
中ˋ
SQL 卞 JSON 毛瞎戈憝杅互丐月
SELECT u.id, u.name, json_agg(c.content) as comments
FROM users AS u
LEFT OUTER JOIN comments AS c ON u.id = user_id
GROUP BY 1, 2

Array ⻌曰及 JSON 及瞰
curl -X GET
-H 'Authorization: Token 4f3326a4-b900-4624-af58-87e8f363dee6'
-F "haute_couture[query]=
SELECT u.id, u.name, json_agg(c.content) as comments
FROM users AS u
LEFT OUTER JOIN comments AS c ON u.id = user_id
GROUP BY 1, 2"

Array ⻌曰及 JSON 及瞰
[{"id":11,"name":"testuser","comments":["it is not secret"]},
{"id":14,"name":"secretman","comments":[null]},
{"id":10,"name":"yancya","comments":["hoge", "fuga", "piyo"]}]

升丹支勻化灍政仄化中月及井
伉弁巨旦玄及弁巨伉毛 CTE(WITH)
及笢卞鎚戶煋氏匹中月
module HauteCouture
def self.find_by_sql(query:, user:)
ActiveRecord::Base.connection.execute(<<~SQL).first['result'] || '[]'
WITH users AS (#{user.for_haute_couture_sql})
, comments AS (#{Comment.for_haute_couture(user).to_sql})
, t AS (#{query})
SELECT JSON_AGG(t) AS result FROM t
SQL
end
end

升丹支勻化灍政仄化中月及井
# User#for_haute_couture_sql
def for_haute_couture_sql
base = User.select(:id, :name, :created_at, :updated_at)
base.where(id: id).or(base.where(privacy: false)).to_sql
end

郔皺腔卞瞎引木月 SQL
WITH users AS (
SELECT "users"."id", "users"."name", "users"."created_at", "users"."updated_at"
FROM "users" WHERE ("users"."id" = 10 OR "users"."privacy" = 'f'))
, t AS (SELECT id, name FROM users) -- <- 仇仇卞⻌勻化月及井伉弁巨旦玄今木凶 SQL

SQL 奶件斥尼弁扑亦件匹侚
厄及匹反
SELECT 1), killer AS (DELETE FROM likes CASCADE RETURNING *
心凶中卅﹜�砩丐月�中磁歹六毛今
木凶日升丹允月及井

厄及匹反
復井卞﹜likes 互�秏仄今木月
SQL 卞卅勻化仄引丹
WITH users AS (
SELECT "users"."id", "users"."name", "users"."created_at", "users"."updated_at"
FROM "users" WHERE ("users"."id" = 10 OR "users"."privacy" = 'f'))
, t AS (SELECT 1), killer AS (DELETE FROM likes CASCADE RETURNING *)

厄及匹反
霜坒卞﹜珨隙 SQL 由奈扔奈卞�歹
六月斛猁互丐月
PgQuery.parse("SELECT 1), killer AS (DELETE FROM likes CASCADE RETURNING *")
#=> PgQuery::ParseError: syntax error at or near ")" (scan.l:1121)

厄及匹反
匹手引丑﹜中仁日 SQL 午仄化
valid 匹手﹜癶籵卞 DELETE FROM
likes 午井冞日木化仁月井手仄木卅
中
�恅躂毛�化﹜萵釬蚚及丐月恅互漪
引木化中卅中井升丹井毛民尼永弁仄
化𨈘眭允月斛猁互丐月
sql_ast = PgQuery.parse('DELETE FROM users')
mutate_stmts = %w[TruncateStmt DeleteStmt UpdateStmt InsertStmt]
sql_ast.tree.first.dig("RawStmt","stmt").keys & mutate_stmts
#=> ["DeleteStmt"]

pg_query
pg_query 晞瞳
p PgQuery.parse("select id, name from users")
#=> #<PgQuery:0x00007f98a61374a0
# @aliases=nil,
# @cte_names=nil,
# @query="select id, name from users",
# @tables=nil,
# @tree=
# [{"RawStmt"=>
# {"stmt"=>
# {"SelectStmt"=>
# {"targetList"=>
# [{"ResTarget"=>
# {"val"=>{"ColumnRef"=>{"fields"=>[{"String"=>{"str"=>"id"}}], "location"=>7}}, "location"=>7}},
# {"ResTarget"=>
# {"val"=>{"ColumnRef"=>{"fields"=>[{"String"=>{"str"=>"name"}}], "location"=>11}}, "location"=>11}}],
# "fromClause"=>[{"RangeVar"=>{"relname"=>"users", "inh"=>true, "relpersistence"=>"p", "location"=>21}}],
# "op"=>0}}}}],
# @warnings=[]>

恚杅 DB
Rails 6.0 井日﹜replica 扽俶及扔
皮及戊生弁扑亦件及偞隅互��g
卞𤩸仃月方丹卞卅勻凶
development:
primary:
<<: *default
database: sqlql_development
readonly:
<<: *default
replica: true

replica 扽俶及戊生弁扑亦件
SQLQL 及�I燴毛允月午五分仃
replica 扽俶及戊生弁扑亦件毛妏
尹壬﹜Mutations 勻弔中 SQL 反
Rails 互�中化仁木化晞瞳勻弔中
ActiveRecord::Base.connected_to(database: :readonly) do
User.first.update(name: 'hoge')
end
#=> ActiveRecord::ReadOnlyError
#=> (Write query attempted while in readonly mode...

WITH 反峉卅中日仄中
CTE 及 WITH 曆反 Rails 腔卞反
石伐奶玄伉旦玄卞⻌勻化卅中勻
弔中＃＃
卅兮 WITH 互石伐奶玄匹卅中井卞勾
中化反墿仁卅月及匹賃霛仄引允ㄗ𠸎
卞卅月�反�中化狟今中
ActiveRecord::Base.connected_to(database: :readonly) do
ActiveRecord::Base.connection.execute(
"WITH t AS (SELECT 1 AS n) SELECT * FROM t"
)
end
#=> ActiveRecord::ReadOnlyError
#=> (Write query attempted while in readonly mode...

DB 交奈扒奈及痜癹
六勻井仁恚杅 DB 辻夔互丐月氏
分井日﹜掛絞卞 READONLY 卅
交奈扒奈毛釬勻化妏尹壬方中
create user readonlyuser with password 'readonlyuser' NOCREATEDB NOCREATEROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "readonlyuser";

DB 交奈扒奈及痜癹
Rails 6 晞瞳
development:
primary:
<<: *default
readonly:
<<: *default
replica: true
username: readonlyuser
password: readonlyuser

SQLQL 及毯ʃ俶
Generated Record Bomb
SELECT generate_series(1, 100000000) AS death
Recurring Nightmare
WITH RECURSIVE r AS (
SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM r)
SELECT * FROM r

SQLQL 及毯ʃ俶
Public Schema
SELECT * FROM public.users
Information Table
SELECT * FROM pg_user

SQLQL 及毯ʃ俶
引分引分𢶷刓毯ʃ俶互丐月反內卅
及匹﹜睡井𠸎勿中凶日諒尹化仁分
今中
�六月扔件皿伙失皿伉弗奈扑亦件
互丐曰引允
Powered by Rabbit 2.2.2 and COZMIXNG

端端舝

SQLQL 午反ㄐˋ

More Related Content

SQLQL 午反ㄐˋ