端端舝

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 午反ㄐˋ

Recommended

More Related Content

What's hot (20)

Similar to SQLQL 午反ㄐˋ (20)

More from yancya (10)

Recently uploaded (6)

SQLQL 午反ㄐˋ