使用PG'OCaml进行嵌入式SQL查询的语法扩展。- tizoc / ppx_pgsql

  • d2_471155
    了解作者
  • 13.3KB
    文件大小
  • zip
    文件格式
  • 0
    收藏次数
  • VIP专享
    资源类型
  • 0
    下载次数
  • 2022-04-09 03:29
    上传日期
使用PG'OCaml进行嵌入式SQL查询的语法扩展。- tizoc / ppx_pgsql-源码
ppx_pgsql-master.zip
  • ppx_pgsql-master
  • lib
  • runtime
  • dune
    93B
  • ppx_pgsql_runtime.mli
    667B
  • ppx_pgsql_runtime.ml
    1.8KB
  • example
  • dune
    142B
  • todo_lwt.ml
    4.9KB
  • ppx
  • dune
    322B
  • ppx_pgsql.ml
    12.8KB
  • tests
  • test_ppx.ml
    335B
  • dune
    116B
  • ppx_pgsql.opam
    630B
  • dune-project
    16B
  • LICENSE
    1.5KB
  • README.md
    3.8KB
  • Makefile
    288B
  • .gitignore
    33B
内容介绍
# ppx_pgsql Syntax extension for embedded SQL queries using PG'OCaml. ## How it works Expressions of the form `[%sqlf <sql string>]` are validated and converted into a function that will execute the query. The generated function will take each named argument (denoted as names starting with the dollar symbol, e.g. `$parameter_name`) as a keyword argument with the same name, and a database handle as the last argument. ### Example: ```ocaml let update_account = [%sqlf {| UPDATE accounts SET email = $email, account_type = $account_type WHERE account_id = $account_id RETURNING account_id, created_at, account_type, email |}] ``` The type of `update_account` is: ```ocaml email:string -> account_type:string -> account_id:int64 -> (string, bool) Hashtbl.t Pg_store_helpers.PGOCaml.t -> (int64 * CalendarLib.Calendar.t * string * string) list PGOCaml.monad ``` ## Named arguments syntax - `$name` - normal value - `$@name` - list expression value - `$?name` - option value (None becomes NULL) - `$@?name` - option list expression value ## To install ``` opam pin add ppx_pgsql -k git https://github.com/tizoc/ppx_pgsql.git ``` ## Some tips ### Views and NULL-able heuristic This rewriter tries its best to figure out which columns are NULL-able and which are not, but sometimes it fails to do so. One case is with columns in views, which will be assumed to always be NULL-able. To fix this, you can alter the view metadata contained in the `pg_attribute` table, and set `attnotnull` to `true`: ```sql UPDATE pg_attribute SET attnotnull = 't' WHERE attrelid IN ( SELECT oid FROM pg_class WHERE relname = 'name_of_view'); ``` ### Outer joins, and using `COALESCE` When performing joins, columns that on the original table are qualified as not NULL-able, may become NULL-able, this will make the heuristic fail. One workaround is to create a view for such query, and then use the trick described above. [Another option](https://github.com/tizoc/ppx_pgsql/issues/4#issuecomment-479106321) it to use the `COALESCE` function to force the column to be NULL-able: ```sql -- Given these tables CREATE TABLE authors (id serial PRIMARY KEY, name varchar(255) NOT NULL); INSERT INTO authors (id, name) VALUES (1, 'John Doe'); CREATE TABLE books (id serial PRIMARY KEY, title varchar(255) NOT NULL, author int NOT NULL REFERENCES authors(id) ON DELETE CASCADE); -- This join could result in NULL values for books.title SELECT authors.name, coalesce(books.title) -- inferred as NULL-able now FROM authors LEFT OUTER JOIN books ON books.author = authors.id ``` Credit to @NightBlues for coming up with this solution. ### IN/NOT IN operator when using a possibly empty dynamic list of values Using list expressions to build `IN`/`NOT IN` query expresions (`IN $@name` or `NOT IN $@name`) is not encouraged when the list of values is dynamic and has the potential of being empty. The problem with doing so is that the list may be empty, resulting in an invalud query being generated (`IN ()` and `NOT IN ()` are not valid SQL). What is worse, this failure will happen at runtime. Additionaly, by doing so with lists of varying length, a new prepared statement will be created at runtime for each one of the lengths. An alternative is to use the `ANY` and `ALL` operators with arrays: ```sql -- This SELECT COUNT(*) FROM users WHERE id IN $@user_ids_list -- Becomes SELECT COUNT(*) FROM users WHERE id = ANY($user_ids_list::int[]) -- And this SELECT COUNT(*) FROM users WHERE id NOT IN $@user_ids_list -- Becomes SELECT COUNT(*) FROM users WHERE id <> ALL($user_ids_list::int[]) ``` Another option is to use the `unnest` array function and an inner `SELECT`: ```sql SELECT COUNT(*) FROM users WHERE id IN (SELECT unnest($user_ids_list::int[])) SELECT COUNT(*) FROM users WHERE id NOT IN (SELECT unnest($user_ids_list::int[])) ```
评论
    相关推荐
    • 嵌入式SQL课件 数据库复习
      嵌入式SQL课件 数据库复习 来自广东~~ 嘿嘿
    • 为Firebird嵌入式SQL Server编写UDF
      我们将描述如何创建您自己的本地Firebird扩展,并展示一些在托管代码应用程序中使用它的方法
    • sqlite:嵌入式SQL数据库
      SQLite是一个进程内库,可实现自包含的,无服务器的,零配置的事务型SQL数据库引擎。 SQLite的代码在公共领域,因此可以免费用于任何目的,无论是商业目的还是私人目的。 当前,SQLite的应用程序数量超过了我们所能...
    • 嵌入式SQL编程材料
      详细讲解嵌入式SQL语言编程,简单易学,以ppt形式展现在读者面前,是读者眼前一亮,更感兴趣,更加简单易学
    • sql的c语言嵌入式开发
      在Visual Studio C++环境中通过ODBC实现与实验1建立的数据库StuManagement的互联,进行实验要求的各种操作,关系模式... 界面执行SQL语句操作(注:用户通过界面输入SQL语句,应用程序执行该语句,对数据库进行操作)。
    • 数据库作业 JAVa 嵌入式SQL
      数据库作业 JAVa 嵌入式SQL数据库作业 JAVa 嵌入式SQL
    • 嵌入式SQL编程 附有操作说明 student
      嵌入式SQL编程的范例,使用VC6编译Oracle 10g 嵌入式SQL程序操作步骤 一、 基本要求 1、所需软件Visual C++ 6.0 2、 源代码包括 PRO C 文件: student.pc 3、安装ORACLE10G 或其客户端 在ORACLE10G CLIENT 安装时并...
    • 用C#实现嵌入式SQL访问数据库
      用C#实现嵌入式SQL访问数据库,比较有用。windows 平台下可以成功运行。
    • 嵌入式SQL——C语言访问数据库
      用C语言访问数据库SQL,里面涵盖了操作的详细步骤的截图和源代码。
    • GaussDB_100_1.0.1-DATABASE-REDHAT-64bit.tar.gz
      guassdb100在redhat上安装包,单机部署的包,安装步骤请看我的文中介绍,经过大量实验搭建总结出来的文档