FEATURE: add sql_literal for injecting sql (#40)

FEATURE: add sql_literal for injecting sql (#40)

  • add sql_literal for injecting sql
  • inject sql only for builder
  • inject_sql → sql_literal
  • check predefined sql literals
diff --git a/README.md b/README.md
index afd17f4..f3f98f0 100644
--- a/README.md
+++ b/README.md
@@ -77,7 +77,46 @@ builder.query.each do |t|
 end
 `‍``
 
-The builder allows for `order_by`, `where`, `select`, `set`, `limit`, `join`, `left_join` and `offset`.
+The builder predefined next _SQL Literals_
+
+| Method | SQL Literal |
+| ------ | ----------- |
+|`select`    |`/*select*/`|
+|`where`     |`/*where*/`|
+|`where2`    |`/*where2*/`|
+|`join`      |`/*join*/`|
+|`left_join` |`/*left_join*/`|
+|`group_by`  |`/*group_by*/`|
+|`order_by`  |`/*order_by*/`|
+|`limit`     |`/*limit*/`|
+|`offset`    |`/*offset*/`|
+|`set`       |`/*set*/`|
+
+### Custom SQL Literals
+Use `sql_literal` for injecting custom sql into Builder
+
+`‍``ruby
+user_builder = conn
+  .build("select date_trunc('day', created_at) day, count(*) from user_topics /*where*/")
+  .where('type = ?', input_type)
+  .group_by("date_trunc('day', created_at)")
+
+guest_builder = conn
+  .build("select date_trunc('day', created_at) day, count(*) from guest_topics /*where*/")
+  .where('state = ?', input_state)
+  .group_by("date_trunc('day', created_at)")
+
+conn
+  .build(<<~SQL)
+     with as (/*user*/) u, (/*guest*/) as g
+     select COALESCE(g.day, u.day), g.count, u.count
+     from u
+     /*custom_join*/
+  SQL
+  .sql_literal(user: user_builder, guest: guest_builder) # builder
+  .sql_literal(custom_join: "#{input_cond ? 'FULL' : 'LEFT'} JOIN g on g.day = u.day") # or string
+  .query
+`‍``
 
 ## Is it fast?
 Yes, it is very fast. See benchmarks in [the bench directory](https://github.com/discourse/mini_sql/tree/master/bench).
diff --git a/lib/mini_sql/builder.rb b/lib/mini_sql/builder.rb
index 497b9de..7527720 100644
--- a/lib/mini_sql/builder.rb
+++ b/lib/mini_sql/builder.rb
@@ -11,32 +11,46 @@ class MiniSql::Builder
     @is_prepared = false
   end
 
-  [:set, :where2, :where, :order_by, :left_join, :join, :select, :group_by].each do |k|
-    define_method k do |sql_part, *args|
-      if Hash === args[0]
-        @args.merge!(args[0])
-      else # convert simple params to hash
-        args.each do |v|
-          # for compatability with AR param encoded we keep a non _
-          # prefix (must be [a-z])
-          param = "mq_auto_#{@count_variables += 1}"
-          sql_part = sql_part.sub('?', ":#{param}")
-          @args[param.to_sym] = v
+  literals1 =
+    [:set, :where2, :where, :order_by, :left_join, :join, :select, :group_by].each do |k|
+      define_method k do |sql_part, *args|
+        if Hash === args[0]
+          @args.merge!(args[0])
+        else # convert simple params to hash
+          args.each do |v|
+            # for compatability with AR param encoded we keep a non _
+            # prefix (must be [a-z])
+            param = "mq_auto_#{@count_variables += 1}"
+            sql_part = sql_part.sub('?', ":#{param}")
+            @args[param.to_sym] = v
+          end
         end
+
+        @sections[k] ||= []
+        @sections[k] << sql_part
+        self
       end
+    end
 
-      @sections[k] ||= []
-      @sections[k] << sql_part
-      self
+  literals2 =
+    [:limit, :offset].each do |k|
+      define_method k do |value|
+        @args["mq_auto_#{k}".to_sym] = value
+        @sections[k] = true
+        self
+      end
     end
-  end
 
-  [:limit, :offset].each do |k|
-    define_method k do |value|
-      @args["mq_auto_#{k}".to_sym] = value
-      @sections[k] = true
-      self
+  PREDEFINED_SQL_LITERALS = (literals1 | literals2).to_set
+
+  def sql_literal(literals)
+    literals.each do |name, part_sql|
+      if PREDEFINED_SQL_LITERALS.include?(name)
+        raise "/*#{name}*/ is predefined, use method `.#{name}` instead `sql_literal`"
+      end
+      @sections[name] = part_sql.is_a?(::MiniSql::Builder) ? part_sql.to_sql : part_sql
     end
+    self
   end
 
   [:query, :query_single, :query_hash, :query_array, :exec].each do |m|
@@ -93,9 +107,13 @@ class MiniSql::Builder
         joined = (+"GROUP BY ") << v.join(" , ")
       when :set
         joined = (+"SET ") << v.join(" , ")
+      else # for sql_literal
+        joined = v
       end
 
-      sql.sub!("/*#{k}*/", joined)
+      unless sql.sub!("/*#{k}*/", joined)
+        raise "The section for the /*#{k}*/ clause was not found!"
+      end
     end
 
     sql
diff --git a/lib/mini_sql/inline_param_encoder.rb b/lib/mini_sql/inline_param_encoder.rb
index b200de1..84c6ddb 100644
--- a/lib/mini_sql/inline_param_encoder.rb
+++ b/lib/mini_sql/inline_param_encoder.rb
@@ -58,10 +58,7 @@ module MiniSql
       when false      then "false"
       when nil        then "NULL"
       when []         then "NULL"
-      when Array
-        value.map do |v|
-          quote_val(v)
-        end.join(', ')
+      when Array      then value.map { |v| quote_val(v) }.join(', ')
       else raise TypeError, "can't quote #{value.class.name}"
       end
     end
diff --git a/test/mini_sql/builder_tests.rb b/test/mini_sql/builder_tests.rb
index a083b65..787fa8b 100644
--- a/test/mini_sql/builder_tests.rb
+++ b/test/mini_sql/builder_tests.rb
@@ -194,4 +194,33 @@ module MiniSql::BuilderTests
 
     assert_equal(builder.to_sql(id: 10, is_sale: true), sql)
   end
+
+  def test_exception_when_section_not_defined
+    builder = @connection.build("SELECT * FROM products /*where2*/").where('id = ?', 10)
+
+    err = assert_raises(RuntimeError) { builder.to_sql }
+    assert_match 'The section for the /*where*/ clause was not found!', err.message
+  end
+
+  def test_sql_literal
+    builder = @connection.build("SELECT * FROM products /*product_where*/")
+    builder.sql_literal(product_where: 'WHERE id = 10')
+
+    assert_equal(builder.to_sql, 'SELECT * FROM products WHERE id = 10')
+  end
+
+  def test_sql_literal_for_builder
+    user_builder = @connection.build("SELECT * FROM users /*where*/").where('id = ?', 10)
+    builder = @connection.build("SELECT * FROM (/*user_table*/) AS t")
+    builder.sql_literal(user_table: user_builder)
+
+    assert_equal(builder.to_sql, 'SELECT * FROM (SELECT * FROM users WHERE (id = 10)) AS t')
+  end
+
+  def test_sql_literal_predefined
+    builder = @connection.build("select 1 /*where*/")
+
+    err = assert_raises(RuntimeError) { builder.sql_literal(where: "where 1 = 1") }
+    assert_match '/*where*/ is predefined, use method `.where` instead `sql_literal`', err.message
+  end
 end
diff --git a/test/mini_sql/inline_param_encoder_test.rb b/test/mini_sql/inline_param_encoder_test.rb
index 4a37643..3dfbe35 100644
--- a/test/mini_sql/inline_param_encoder_test.rb
+++ b/test/mini_sql/inline_param_encoder_test.rb
@@ -51,5 +51,6 @@ module MiniSql
       result = @encoder.encode("select :t", t: t)
       assert_equal("select '2010-10-01'", result)
     end
+
   end
 end

GitHub sha: b36df7983e241f2f45e79a82361009c9c3ea9ea3

This commit appears in #40 which was approved by SamSaffron. It was merged by SamSaffron.