From 3eeee5bc83b886ec859fe5a25a6d63ca08b7fddd Mon Sep 17 00:00:00 2001 From: itsWill Date: Sun, 27 Dec 2015 09:27:03 +0100 Subject: [PATCH] started working on a wrapper for postgres Signed-off-by: itsWill --- lib/postgresql/native_postgres.nit | 142 ++++++++++++++++++++++++++++++++++++ lib/postgresql/package.ini | 11 +++ tests/sav/test_postgres_native.res | 3 + tests/test_postgres_native.nit | 73 ++++++++++++++++++ 4 files changed, 229 insertions(+) create mode 100644 lib/postgresql/native_postgres.nit create mode 100644 lib/postgresql/package.ini create mode 100644 tests/sav/test_postgres_native.res create mode 100644 tests/test_postgres_native.nit diff --git a/lib/postgresql/native_postgres.nit b/lib/postgresql/native_postgres.nit new file mode 100644 index 0000000..4d22201 --- /dev/null +++ b/lib/postgresql/native_postgres.nit @@ -0,0 +1,142 @@ +# This file is part of NIT ( http://www.nitlanguage.org ). +# +# Copyright 2015-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 native_postgres is pkgconfig("libpq") + +in "C header" `{ + #include +`} + +extern class ExecStatusType `{int`} + new empty `{ return PGRES_EMPTY_QUERY; `} + new command_ok `{ return PGRES_COMMAND_OK; `} + new tuples_ok `{ return PGRES_TUPLES_OK; `} + new copy_out `{ return PGRES_COPY_OUT; `} + new copy_in `{ return PGRES_COPY_IN; `} + new bad_response `{ return PGRES_BAD_RESPONSE; `} + 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; `} + + redef fun to_s import NativeString.to_s `{ + char * err = PQresStatus(self); + if(err == NULL) err = ""; + return NativeString_to_s(err); + `} +end + +extern class ConnStatusType `{int`} + new connection_ok `{ return CONNECTION_OK; `} + new connection_bad `{ return CONNECTION_BAD; `} + + fun is_ok: Bool `{return self == CONNECTION_OK; `} +end + +extern class PGResult `{PGresult *`} + # Frees the memory block associated with the result + fun clear `{PQclear(self); `} + + # Returns the number of rows in the query result + fun ntuples:Int `{ return PQntuples(self); `} + + # Returns the number of columns in each row of the query result + fun nfields:Int `{return PQnfields(self); `} + + # Returns the ExecStatusType of a result + fun status: ExecStatusType `{ return PQresultStatus(self); `} + + # Returns the field name of a given column_number + fun fname(column_number:Int):String import NativeString.to_s `{ + return NativeString_to_s( PQfname(self, column_number)); + `} + + # Returns the column number associated with the column name + fun fnumber(column_name:String):Int import String.to_cstring `{ + return PQfnumber(self, String_to_cstring(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 import NativeString.to_s `{ + return NativeString_to_s(PQgetvalue(self, row_number, column_number)); + `} + + # Tests wether a field is a null value + fun is_null(row_number:Int, column_number: Int): Bool `{ + return PQgetisnull(self, row_number, column_number); + `} + +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 `{ + PGconn * self = NULL; + self = PQconnectdb(String_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)); + return res; + `} + + # Prepares a statement with the given parameters + fun prepare(stmt: String, query: String, nParams: Int):PGResult 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].[] `{ + const char * stmtName = String_to_cstring(stmt); + const char * paramValues[nParams]; + int paramLengths[nParams]; + int paramFormats[nParams]; + int i; + for(i = 0; i < nParams; i++) + paramValues[i] = String_to_cstring(Array_of_String__index(values, i)); + for(i = 0; i < nParams; i++) + paramLengths[i] = Array_of_Int__index(pLengths, i); + for(i = 0; i < nParams; i++) + paramFormats[i] = Array_of_Int__index(pFormats, i); + PGresult * res = PQexecPrepared(self, stmtName, nParams, paramValues, paramLengths, paramFormats, resultFormat); + return res; + `} + + # Returns the error message of the last operation on the connection + fun error: String import NativeString.to_s `{ + char * error = PQerrorMessage(self); + return NativeString_to_s(error); + `} + + # Returns the status of this connection + fun status: ConnStatusType `{ + return PQstatus(self); + `} + + # Closes the connection to the server + fun finish `{ + PQfinish(self); + `} + + # Closes the connection to the server and attempts to reconnect with the previously used params + fun reset `{ + PQreset(self); + `} +end diff --git a/lib/postgresql/package.ini b/lib/postgresql/package.ini new file mode 100644 index 0000000..dc82d85 --- /dev/null +++ b/lib/postgresql/package.ini @@ -0,0 +1,11 @@ +[package] +name=postgresql +tags=database,lib +maintainer=Guilherme Mansur +license=Apache-2.0 +[upstream] +browse=https://github.com/nitlang/nit/tree/master/lib/postgresql/ +git=https://github.com/nitlang/nit.git +git.directory=lib/postgresql/ +homepage=http://nitlanguage.org +issues=https://github.com/nitlang/nit/issues \ No newline at end of file diff --git a/tests/sav/test_postgres_native.res b/tests/sav/test_postgres_native.res new file mode 100644 index 0000000..0c511fe --- /dev/null +++ b/tests/sav/test_postgres_native.res @@ -0,0 +1,3 @@ +aname class sex +Whale mammal 1 +Snake reptile 0 diff --git a/tests/test_postgres_native.nit b/tests/test_postgres_native.nit new file mode 100644 index 0000000..ff3be6f --- /dev/null +++ b/tests/test_postgres_native.nit @@ -0,0 +1,73 @@ +# 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_native + +import postgresql::native_postgres + +var db = new NativePostgres.connectdb("dbname=postgres") +assert postgres_open: db.status.is_ok else print_error db.error + +var result = db.exec("CREATE TABLE IF NOT EXISTS animals (aname TEXT PRIMARY KEY, class TEXT NOT NULL, sex INTEGER)") +assert postgres_create_table: result.status.is_ok else print_error db.error + +result = db.exec("INSERT INTO animals VALUES('Whale', 'mammal', 1)") +assert postgres_insert_1: result.status.is_ok else print_error db.error + +result = db.exec("INSERT INTO animals VALUES('Snake', 'reptile', 0)") +assert postgres_insert_2: result.status.is_ok else print_error db.error + +result = db.exec("SELECT * FROM animals") +assert postgres_select: result.status.is_ok else print_error db.error + +assert postgres_ntuples: result.ntuples == 2 else print_error db.error +assert postgres_nfields: result.nfields == 3 else print_error db.error +assert postgres_fname: result.fname(0) == "aname" 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) == "Whale" else print_error db.error + +var cols: Int = result.nfields +var rows: Int = result.ntuples +var fields: String = "" +for c in [0..cols[ do fields += result.fname(c) + " " +print fields +for i in [0..rows[ do + fields = "" + for j in [0..cols[ do fields += result.value(i, j) + " " + print fields +end + +result = db.exec("DELETE FROM animals WHERE aname = 'Lioness'") +assert postgres_delete_1: result.status.is_ok else print_error db.error + +result = db.exec("DELETE FROM animals WHERE aname = 'Snake'") +assert postgres_delete_2: result.status.is_ok else print_error db.error + +result = db.prepare("PREPARED_INSERT", "INSERT INTO animals(aname, class, sex) VALUES ($1, $2, $3)", 3) +assert postgres_prepare: result.status.is_ok else print_error db.error + +result = db.exec("DELETE FROM animals WHERE aname = 'Frog'") +assert postgres_delete_3: result.status.is_ok else print_error db.error + +var values = ["Frog", "Anphibian", "1"] +var lengths = [values[0].length, values[1].length, values[2].length] +var formats = [0,0,0] +result = db.exec_prepared("PREPARED_INSERT", 3, values, lengths, formats,0) +assert postgres_exec_prepared: result.status.is_ok else print_error db.error + +result = db.exec("DROP TABLE animals") +assert postgres_drop_table: result.status.is_ok else print_error db.error +db.finish -- 1.7.9.5