From cad6496e94707f840e62f4a7b06fc43c16451b89 Mon Sep 17 00:00:00 2001 From: itsWill Date: Tue, 17 May 2016 23:32:38 +0200 Subject: [PATCH] started working on the nit wrapper over the native postgres wrapper Signed-off-by: itsWill --- lib/postgresql/native_postgres.nit | 19 +++-- lib/postgresql/postgres.nit | 144 ++++++++++++++++++++++++++++++++++++ tests/test_postgres_nity.nit | 49 ++++++++++++ 3 files changed, 204 insertions(+), 8 deletions(-) create mode 100644 lib/postgresql/postgres.nit create mode 100644 tests/sav/test_postgres_nity.res create mode 100644 tests/test_postgres_nity.nit diff --git a/lib/postgresql/native_postgres.nit b/lib/postgresql/native_postgres.nit index 4d22201..d32c908 100644 --- a/lib/postgresql/native_postgres.nit +++ b/lib/postgresql/native_postgres.nit @@ -14,6 +14,7 @@ # See the License for the specific language governing permissions and # limitations under the License. +# A native wrapper ove the postgres c api module native_postgres is pkgconfig("libpq") in "C header" `{ @@ -30,7 +31,9 @@ extern class ExecStatusType `{int`} new nonfatal_error `{ return PGRES_NONFATAL_ERROR; `} new fatal_error `{ return PGRES_FATAL_ERROR; `} - fun is_ok: Bool `{return self == PGRES_TUPLES_OK || self == PGRES_COMMAND_OK; `} + fun is_ok: Bool `{ + return !(self == PGRES_BAD_RESPONSE || self == PGRES_NONFATAL_ERROR || self == PGRES_FATAL_ERROR); + `} redef fun to_s import NativeString.to_s `{ char * err = PQresStatus(self); @@ -46,7 +49,7 @@ extern class ConnStatusType `{int`} fun is_ok: Bool `{return self == CONNECTION_OK; `} end -extern class PGResult `{PGresult *`} +extern class NativePGResult `{PGresult *`} # Frees the memory block associated with the result fun clear `{PQclear(self); `} @@ -83,27 +86,27 @@ end extern class NativePostgres `{PGconn *`} # Connect to a new database using the conninfo string as a parameter - new connectdb(conninfo: String) import String.to_cstring `{ + new connectdb(conninfo: Text) import Text.to_cstring `{ PGconn * self = NULL; - self = PQconnectdb(String_to_cstring(conninfo)); + self = PQconnectdb(Text_to_cstring(conninfo)); return self; `} # Submits a query to the server and waits for the result returns the ExecStatustype of the query - fun exec(query: String): PGResult import String.to_cstring `{ - PGresult *res = PQexec(self, String_to_cstring(query)); + fun exec(query: Text): NativePGResult import Text.to_cstring `{ + PGresult *res = PQexec(self, Text_to_cstring(query)); return res; `} # Prepares a statement with the given parameters - fun prepare(stmt: String, query: String, nParams: Int):PGResult import String.to_cstring `{ + fun prepare(stmt: String, query: String, nParams: Int): NativePGResult import String.to_cstring `{ const char * stmtName = String_to_cstring(stmt); const char * queryStr = String_to_cstring(query); PGresult * res = PQprepare(self, stmtName, queryStr, nParams, NULL); return res; `} - fun exec_prepared(stmt: String, nParams: Int, values: Array[String], pLengths: Array[Int], pFormats: Array[Int], resultFormat: Int):PGResult import String.to_cstring, Array[String].[], Array[Int].[] `{ + fun exec_prepared(stmt: String, nParams: Int, values: Array[String], pLengths: Array[Int], pFormats: Array[Int], resultFormat: Int): NativePGResult import String.to_cstring, Array[String].[], Array[Int].[] `{ const char * stmtName = String_to_cstring(stmt); const char * paramValues[nParams]; int paramLengths[nParams]; diff --git a/lib/postgresql/postgres.nit b/lib/postgresql/postgres.nit new file mode 100644 index 0000000..22e498f --- /dev/null +++ b/lib/postgresql/postgres.nit @@ -0,0 +1,144 @@ +# This file is part of NIT ( http://www.nitlanguage.org ). +# +# Copyright 2016 Guilherme Mansur +# +# Licensed under the Apache License, Version 2.0 (the "License"); +# you may not use this file except in compliance with the License. +# You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. + +# Services to manipulate a Postgres database +# +# For more information, refer to the documentation of http://www.postgresql.org/docs/manuals/ +# +# ### Usage example +# +# ~~~ +# class Animal +# var name: String +# var kind: String +# var age: Int +# end +# +# var animals = new Array[Animal] +# var dog = new Animal("Lassy", "dog", 10) +# var cat = new Animal("Garfield", "cat", 3) +# var turtle = new Animal("George", "turtle", 123) +# +# animals.add(dog) +# animals.add(cat) +# animals.add(turtle) +# +# var db = new Postgres.open("dbname=postgres") +# +# assert db_is_open: not db.is_closed +# assert create_table: db.create_table("IF NOT EXISTS animals (aname TEXT PRIMARY KEY, kind TEXT NOT NULL, age INT NOT NULL)") else print db.error +# +# for animal in animals do +# assert insert: db.insert("INTO animals VALUES('{animal.name}', '{animal.kind}', {animal.age})") else print db.error +# end +# +# var result = db.raw_execute("SELECT * FROM animals") +# assert result.is_ok +# assert drop_table: db.execute("DROP TABLE animals") +# db.finish +# assert db_is_closed: db.is_closed +# ~~~ +module postgres + +private import native_postgres + +# A connection to a Postgres database +class Postgres + private var native_connection: NativePostgres + + var is_closed = true + + # Open the connnection with the database using the `conninfo` + init open(conninfo: Text) + do + init(new NativePostgres.connectdb(conninfo)) + if native_connection.status.is_ok then is_closed = false + end + + # Close this connection with the database + fun finish + do + if is_closed then return + + is_closed = true + + native_connection.finish + end + + fun prepare(stmt_name:String, query:String, num_params: Int):PGResult do return new PGResult(native_connection.prepare(stmt_name, query, num_params)) + + 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 + return new PGResult(native_connection.exec_prepared(stmt_name, num_params, values, param_lengths, param_formats, result_format)) + end + + # Executes a `query` and returns the raw `PGResult` + fun raw_execute(query: Text): PGResult do return new PGResult(native_connection.exec(query)) + + # Execute the `sql` statement and returns `true` on success + fun execute(query: Text): Bool do return native_connection.exec(query).status.is_ok + + # Create a table on the DB with a statement beginning with "CREATE TABLE ", followed by `rest` + # + # This method does not escape special characters. + fun create_table(rest: Text): Bool do return execute("CREATE TABLE " + rest) + + # Insert in the DB with a statement beginning with "INSERT ", followed by `rest` + # + # This method does not escape special characters. + fun insert(rest: Text): Bool do return execute("INSERT " + rest) + + # Replace in the DB with a statement beginning with "REPLACE", followed by `rest` + # + # This method does not escape special characters. + fun replace(rest: Text): Bool do return execute("REPLACE " + rest) + + # The latest error message on the connection an empty string if none + fun error: String do return native_connection.error + + # The status of this connection + fun is_valid: Bool do return native_connection.status.is_ok + + # Resets the connection to the database + fun reset do native_connection.reset +end + +# The result of a given query +class PGResult + private var pg_result: NativePGResult + + fun clear do pg_result.clear + + # Returns the number of rows in the query result + fun ntuples:Int do return pg_result.ntuples + + # Returns the number of columns in each row of the query result + fun nfields:Int do return pg_result.nfields + + # Returns the ExecStatusType of a result + fun is_ok:Bool do return pg_result.status.is_ok + + # Returns the field name of a given `column_number` + fun fname(column_number:Int):String do return pg_result.fname(column_number) + + # Returns the column number associated with the `column_name` + fun fnumber(column_name:String):Int do return pg_result.fnumber(column_name) + + # Returns a single field value of one row of the result at `row_number`, `column_number` + fun value(row_number:Int, column_number:Int):String do return pg_result.value(row_number, column_number) + + # Tests wether a field specified by the `row_number` and `column_number` is null. + fun is_null(row_number:Int, column_number: Int): Bool do return pg_result.is_null(row_number, column_number) +end diff --git a/tests/sav/test_postgres_nity.res b/tests/sav/test_postgres_nity.res new file mode 100644 index 0000000..e69de29 diff --git a/tests/test_postgres_nity.nit b/tests/test_postgres_nity.nit new file mode 100644 index 0000000..9565335 --- /dev/null +++ b/tests/test_postgres_nity.nit @@ -0,0 +1,49 @@ +# This file is part of NIT ( http://www.nitlanguage.org ). +# +# Copyright 2016 Guilherme Mansur +# +# Licensed under the Apache License, Version 2.0 (the "License"); +# you may not use this file except in compliance with the License. +# You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. + +module test_postgres_nity + +import postgresql::postgres + +var db = new Postgres.open("dbname=postgres") +assert open_db: not db.is_closed else print db.error + +assert create_table: db.create_table("IF NOT EXISTS users (uname TEXT PRIMARY KEY, pass TEXT NOT NULL, activated INTEGER, perc FLOAT)") else + print db.error +end + +assert insert1: db.insert("INTO users VALUES('Bob', 'zzz', 1, 77.7)") else + print db.error +end + +assert insert2: db.insert("INTO users VALUES('Guilherme', 'xxx', 1, 88)") else + print db.error +end + +var result = db.raw_execute("SELECT * FROM users") + +assert raw_exec: result.is_ok else print db.error + +assert postgres_nfields: result.nfields == 4 else print_error db.error +assert postgres_fname: result.fname(0) == "uname" else print_error db.error +assert postgres_isnull: result.is_null(0,0) == false else print_error db.error +assert postgres_value: result.value(0,0) == "Bob" else print_error db.error + +assert drop_table: db.execute("DROP TABLE users") else print db.error + +db.finish + +assert db.is_closed else print db.error -- 1.7.9.5