lib/postgresql/postgres.nit

   1 # This file is part of NIT ( http://www.nitlanguage.org ).
   2 #
   3 # Copyright 2016 Guilherme Mansur<guilhermerpmansur@gmail.com>
   4 #
   5 # Licensed under the Apache License, Version 2.0 (the "License");
   6 # you may not use this file except in compliance with the License.
   7 # You may obtain a copy of the License at
   8 #
   9 #     http://www.apache.org/licenses/LICENSE-2.0
  10 #
  11 # Unless required by applicable law or agreed to in writing, software
  12 # distributed under the License is distributed on an "AS IS" BASIS,
  13 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  14 # See the License for the specific language governing permissions and
  15 # limitations under the License.
  16
  17 # Services to manipulate a Postgres database
  18 #
  19 # For more information, refer to the documentation of http://www.postgresql.org/docs/manuals/
  20 #
  21 # ### Usage example
  22 #
  23 # ~~~
  24 # class Animal
  25 #   var name: String
  26 #   var kind: String
  27 #   var age: Int
  28 # end
  29 #
  30 # var animals = new Array[Animal]
  31 # var dog = new Animal("Lassy", "dog", 10)
  32 # var cat = new Animal("Garfield", "cat", 3)
  33 # var turtle = new Animal("George", "turtle", 123)
  34 #
  35 # animals.add(dog)
  36 # animals.add(cat)
  37 # animals.add(turtle)
  38 #
  39 # var db_suffix = "NIT_TESTING_ID".environ
  40 # var db = new Postgres.open("dbname=postgres")
  41 #
  42 # assert db_is_open: not db.is_closed
  43 # assert create_table: db.create_table("IF NOT EXISTS animals_{db_suffix} (aname TEXT PRIMARY KEY, kind TEXT NOT NULL, age INT NOT NULL)") else print db.error
  44 #
  45 # for animal in animals do
  46 #   assert insert: db.insert("INTO animals_{db_suffix} VALUES('{animal.name}', '{animal.kind}', {animal.age})") else print db.error
  47 # end
  48 #
  49 # var result = db.raw_execute("SELECT * FROM animals_{db_suffix}")
  50 # assert  result.is_ok
  51 # assert drop_table: db.execute("DROP TABLE animals_{db_suffix}")
  52 # db.finish
  53 # assert db_is_closed: db.is_closed
  54 # ~~~
  55 module postgres
  56
  57 private import native_postgres
  58
  59 # A connection to a Postgres database
  60 class Postgres
  61   private var native_connection: NativePostgres
  62
  63   # Is the connection closed?
  64   var is_closed = true
  65
  66   # Open the connnection with the database using the `conninfo`
  67   init open(conninfo: Text)
  68   do
  69     init(new NativePostgres.connectdb(conninfo))
  70     if native_connection.status.is_ok then is_closed = false
  71   end
  72
  73   # Close this connection with the database
  74   fun finish
  75   do
  76     if is_closed then return
  77
  78     is_closed = true
  79
  80     native_connection.finish
  81   end
  82
  83   # Prepares the statement `query` with `stmt_name` to be executed later
  84   #
  85   # `num_params` specifies the number of parameters expected at the statement
  86   # execution.
  87   #
  88   # See `exec_prepared` for execution.
  89   fun prepare(stmt_name:String, query:String, num_params: Int):PGResult do return new PGResult(native_connection.prepare(stmt_name, query, num_params))
  90
  91   # Execute prepared statement named `stmt_name` with `values`
  92   #
  93   # * `num_params` specifies the number of parameters given to the prepared statement
  94   # * `param_lengths` specifies the length of each parameters
  95   # * `param_formats` and `result_format` specifies the format used as input/output.
  96   #   Should be 0 for text results, 1 for binary.
  97   #
  98   # See `prepare`.
  99   fun exec_prepared(stmt_name: String, num_params: Int, values: Array[String], param_lengths: Array[Int], param_formats: Array[Int], result_format: Int):PGResult do
 100     return new PGResult(native_connection.exec_prepared(stmt_name, num_params, values, param_lengths, param_formats, result_format))
 101   end
 102
 103   # Executes a `query` and returns the raw `PGResult`
 104   fun raw_execute(query: Text): PGResult do return new PGResult(native_connection.exec(query))
 105
 106   # Execute the `sql` statement and returns `true` on success
 107   fun execute(query: Text): Bool do return native_connection.exec(query).status.is_ok
 108
 109   # Create a table on the DB with a statement beginning with "CREATE TABLE ", followed by `rest`
 110   #
 111   # This method does not escape special characters.
 112   fun create_table(rest: Text): Bool do return execute("CREATE TABLE " + rest)
 113
 114   # Insert in the DB with a statement beginning with "INSERT ", followed by `rest`
 115   #
 116   # This method does not escape special characters.
 117   fun insert(rest: Text): Bool do return execute("INSERT " + rest)
 118
 119   # Replace in the DB with a statement beginning with "REPLACE", followed by `rest`
 120   #
 121   # This method does not escape special characters.
 122   fun replace(rest: Text): Bool do return execute("REPLACE " + rest)
 123
 124   # The latest error message on the connection an empty string if none
 125   fun error: String do return native_connection.error
 126
 127   # The status of this connection
 128   fun is_valid: Bool do return native_connection.status.is_ok
 129
 130   # Resets the connection to the database
 131   fun reset do native_connection.reset
 132 end
 133
 134 # The result of a given query
 135 class PGResult
 136   private var pg_result: NativePGResult
 137
 138   # Clears the result object and frees the memory allocated to the underlying C struct
 139   fun clear do pg_result.clear
 140
 141   # Returns the number of rows in the query result
 142   fun ntuples:Int do return pg_result.ntuples
 143
 144   # Returns the number of columns in each row of the query result
 145   fun nfields:Int do return pg_result.nfields
 146
 147   # Returns the ExecStatusType of a result
 148   fun is_ok:Bool do return pg_result.status.is_ok
 149
 150   # Returns the field name of a given `column_number`
 151   fun fname(column_number:Int):String do return pg_result.fname(column_number)
 152
 153   # Returns the column number associated with the `column_name`
 154   fun fnumber(column_name:String):Int do return pg_result.fnumber(column_name)
 155
 156   # Returns a single field value of one row of the result at `row_number`, `column_number`
 157   fun value(row_number:Int, column_number:Int):String  do return pg_result.value(row_number, column_number)
 158
 159   # Tests wether a field specified by the `row_number` and `column_number` is null.
 160   fun is_null(row_number:Int, column_number: Int): Bool do return pg_result.is_null(row_number, column_number)
 161 end