Mercurial > hg > monetdb-ruby
changeset 0:aab36be83762
Created a new repository for the Ruby driver for MonetDB.
The Ruby files are based on the files in changeset 5db87c0b5920 in the
default branch of the MonetDB repository.
| author | Sjoerd Mullender <sjoerd@acm.org> |
|---|---|
| date | Wed, 02 Mar 2016 14:50:19 +0100 (2016-03-02) |
| parents | |
| children | 4e3a4f2a58ff |
| files | .hgignore Changelog Makefile TODO debian/changelog debian/compat debian/control debian/copyright debian/rules debian/source/format doc/readme.txt lib/MonetDB.rb lib/MonetDBConnection.rb lib/MonetDBData.rb lib/MonetDBExceptions.rb lib/example.rb lib/hasher.rb monetdb-sql.gemspec rubygem-monetdb-sql.spec |
| diffstat | 19 files changed, 1409 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
new file mode 100644 --- /dev/null +++ b/Changelog @@ -0,0 +1,38 @@ +* Tue 30 Jun 2009 22:14:38 CEST +Added test cases for the standalone driver (ruby unit test). + +* Thu 25 Jun 2009 17:31:02 CEST +Fixed a major bug that resulted in data corruption; +MAPI implementation code cleanup and bug fixes; +Support to ruby 1.9; +Both the standalone driver and activerecord now support transactions; nested transactions +are simulated via savepoints in activerecord. +Added a Rakefile and script to run activerecord unit test suite. +Type conversion in the standalone driver is now performed after data has been retrieved and can be executed on single fields. + +* Mon 25 May 2009 17:52:01 CEST +Imported last week changes (protocol 9 support, parametrized the connection options in the activerecord adapter, fixed a bug in the auth protocol v8). +Fixed a bug in the mapi protocol that resulted in data loss (wrong handling of TRANSACTIONS). +Began to port the activerecord test suite to monetdb (not all test cases can be performed). +Removed an unneeded file ('lib/MonetDBStatement.rb') from the gemspec of the standalone driver (the feature will be moved to HEAD). +Began to port the driver to ruby 1.9. +Removed *.gem files from cvs. + +* Mon 18 May 2009 15:22:31 CEST +Fixed bugs that prevented the correct working of activerecords' migration; +The activerecord connector now supports insertion, update and alter table operations; +Type casting is working in activerecord; +Added a rubygem and rakefile for activerecord-monetdb-adapter; +Added a new usage example for activerecord to the README file; +Added an example directory to the cvs tree; +The driver now correctly works with merovingian. + + +* Sat 9 May 2009 15:58:36 CEST +Fixed bugs with the query processing in the standalone driver; +Added INSERT and UPDATE methods in the activerecord connector. + +* Thu 7 May 2009 17:03:01 CEST + +Added a check against the protocol version during authentication; +Imported the activerecord code (available under adapter/).
new file mode 100644 --- /dev/null +++ b/Makefile @@ -0,0 +1,9 @@ +VERSION = 1.0 + +monetdb-sql-$(VERSION).gem: monetdb-sql.gemspec \ + lib/MonetDB.rb lib/MonetDBConnection.rb \ + lib/MonetDBData.rb lib/MonetDBExceptions.rb lib/hasher.rb + gem build $< + +clean: + rm -f *.gem
new file mode 100644 --- /dev/null +++ b/TODO @@ -0,0 +1,5 @@ +* test and improve utf8 and type conversion + +* documentation cleanup + +* OSM on rails demo (slowed down due to third party plugins requirements)
new file mode 100644 --- /dev/null +++ b/debian/changelog @@ -0,0 +1,5 @@ +ruby-monetdb-sql (1.0-1) unstable; urgency=low + + * The Ruby interface to MonetDB is now a separate package. + + -- Sjoerd Mullender <sjoerd@acm.org> Wed, 02 Mar 2016 12:27:21 +0100
new file mode 100644 --- /dev/null +++ b/debian/control @@ -0,0 +1,29 @@ +Source: ruby-monetdb-sql +Section: ruby +Priority: optional +Maintainer: MonetDB BV <info@monetdb.org> +Build-Depends: debhelper (>= 9~), + gem2deb +Standards-Version: 3.9.6 +Vcs-Hg: http://dev.monetdb.org/hg/monetdb-ruby/ +Vcs-Browser: http://dev.monetdb.org/hg/monetdb-ruby/ +Homepage: http://www.monetdb.org/ +Testsuite: autopkgtest-pkg-ruby +XS-Ruby-Versions: all + +Package: ruby-monetdb-sql +Architecture: all +XB-Ruby-Versions: ${ruby:Versions} +Depends: ruby | ruby-interpreter, + ${misc:Depends}, + ${shlibs:Depends} +Recommends: monetdb5-sql (>= 11.22.0) +Conflicts: ruby-monetdb-client +Replaces: ruby-monetdb-client +Description: Pure Ruby database driver for MonetDB/SQL + MonetDB is a database management system that is developed from a + main-memory perspective with use of a fully decomposed storage model, + automatic index management, extensibility of data types and search + accelerators. It also has an SQL frontend. + . + This package contains a pure Ruby database driver for MonetDB/SQL.
new file mode 100644 --- /dev/null +++ b/debian/copyright @@ -0,0 +1,13 @@ +Format: http://www.debian.org/doc/packaging-manuals/copyright-format/1.0/ +Upstream-Name: monetdb-sql +Upstream-Contact: info@monetdb.org +Source: http://dev.monetdb.org/downloads/ + +Files: * +Copyright: 2016 MonetDB B.V. +License: MPL-2.0 + This Source Code Form is subject to the terms of the Mozilla Public + License, v. 2.0. If a copy of the MPL was not distributed with this + file, You can obtain one at http://mozilla.org/MPL/2.0/. + . + Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V.
new file mode 100755 --- /dev/null +++ b/debian/rules @@ -0,0 +1,18 @@ +#!/usr/bin/make -f +#export DH_VERBOSE=1 +# +# Uncomment to ignore all test failures (but the tests will run anyway) +#export DH_RUBY_IGNORE_TESTS=all +# +# Uncomment to ignore some test failures (but the tests will run anyway). +# Valid values: +#export DH_RUBY_IGNORE_TESTS=ruby2.1 ruby2.2 +# +# If you need to specify the .gemspec (eg there is more than one) +#export DH_RUBY_GEMSPEC=gem.gemspec +# +# Uncomment to check dependencies during build: +# export GEM2DEB_TEST_RUNNER = --check-dependencies + +%: + dh $@ --buildsystem=ruby --with ruby
new file mode 100644 --- /dev/null +++ b/doc/readme.txt @@ -0,0 +1,15 @@ +=== Install the standalone driver === + +First build a gem file starting from the given gemspec: + +$ gem build ruby-monetdb-sql-0.2.gemspec + +and install the resulting gem with the command: + +$ gem install --local ruby-monetdb-sql-0.2.gem + +=== Tutorial === + +A short example on how to use Ruby with MonetDB can be found in the lib/example.rb file. Make sure you create a database named "testdatabase2" and have a MonetDB server running on your system prior to trying the script. Instructions on how to run the server and create the database can be found here: + +https://www.monetdb.org/Documentation/UserGuide/Tutorial
new file mode 100644 --- /dev/null +++ b/lib/MonetDB.rb @@ -0,0 +1,209 @@ +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V. + +# = Introduction + # + # A typical sequence of events is as follows: + # Create a database instance (handle), invoke query using the database handle to send the statement to the server and get back a result set object. + # + # A result set object has methods for fetching rows, moving around in the result set, obtaining column metadata, and releasing the result set. + # A result set object is an instance of the MonetDBData class. + # + # Records can be returned as arrays and iterators over the set. + # + # A database handler (dbh) is and instance of the MonetDB class. + # + # = Connection management + # + # connect - establish a new connection + # * user: username (default is monetdb) + # * passwd: password (default is monetdb) + # * lang: language (default is sql) + # * host: server hostanme or ip (default is localhost) + # * port: server port (default is 50000) + # * db_name: name of the database to connect to + # * auth_type: hashing function to use during authentication (default is SHA1) + # + # is_connected? - returns true if there is an active connection to a server, false otherwise + # reconnect - recconnect to a server + # close - terminate a connection + # auto_commit? - returns ture if the session is running in auto commit mode, false otherwise + # auto_commit - enable/disable auto commit mode. + # + # query - fire a query + # + # Currently MAPI protocols 8 and 9 are supported. + # + # = Managing record sets + # + # + # A record set is represented as an instance of the MonetDBData class; the class provides methods to manage retrieved data. + # + # + # The following methods allow to iterate over data: + # + # fetch - iterates over the record set and retrieves one row at a time. Each row is returned as an array. + # each_record - works as ruby each method. The method takes a block as parameter and yields each record to this block. + # fetch_hash - iterates over the record set and retrieves one row at a time. Each row is returned as a hash. + # each_record_as_hash - works as ruby each method. The method takes a block as parameter and yields each record, as hash, to this block + # fetch_all - returns all rows as a two dimensional array + # fetch_all_as_column_hash - returns all records as a hash with the column name as the keys and an array with all column values as values + # + # Information about the retrieved record set can be obtained via the following methods: + # + # num_rows - returns the number of rows present in the record set + # num_fields - returns the number of fields (columns) that compose the schema + # name_fields - returns the (ordered) name of the schema's columns + # type_fields - returns the (ordered) types list of the schema's columns + # + # To release a record set MonetDBData#free can be used. + # + # = Type conversion + # + # All values from the database are converted to the closest ruby type, i.e: INTEGER to int, TIME to time, CLOB to string + # Some of the more complex datatypes are not recognized, such as INTERVAL, these are converted to strings + # + # = Transactions + # + # By default monetdb works in auto_commit mode. To turn this feature off MonetDB#auto_commit(flag=false) can be used. + # + # Once auto_commit has been disable it is possible to start transactions, create/delete savepoints, rollback and commit with + # the usual SQL statements. + # + # Savepoints IDs can be generated using the MonetDB#save method. To release a savepoint ID use MonetDB#release. + # + # Savepoints can be accessed (as a stack) with the MonetDB#transactions method. + # + # demo.rb contains usage example of the above mentioned methods. + +require_relative 'MonetDBConnection' +require_relative 'MonetDBData' +require_relative 'MonetDBExceptions' + +class MonetDB + DEFAULT_USERNAME = "monetdb" + DEFAULT_PASSWORD = "monetdb" + DEFAULT_LANG = MonetDBConnection::LANG_SQL + DEFAULT_HOST = "127.0.0.1" + DEFAULT_PORT = 50000 + DEFAULT_DATABASE = "test" + DEFAULT_AUTHTYPE = "SHA1" + + def initalize() + @connection = nil + end + + # Establish a new connection. + # * username: username (default is monetdb) + # * password: password (default is monetdb) + # * lang: language (default is sql) + # * host: server hostanme or ip (default is localhost) + # * port: server port (default is 50000) + # * db_name: name of the database to connect to + # * auth_type: hashing function to use during authentication (default is SHA1) + def connect(username=DEFAULT_USERNAME, password=DEFAULT_PASSWORD, lang=DEFAULT_LANG, host=DEFAULT_HOST, port=DEFAULT_PORT, db_name=DEFAULT_DATABASE, auth_type=DEFAULT_AUTHTYPE) + # TODO: handle pools of connections + + @username = username + @password = password + @lang = lang + @host = host + @port = port + @db_name = db_name + @auth_type = auth_type + + @connection = MonetDBConnection.new(user = @username, passwd = @password, lang = @lang, host = @host, port = @port) + @connection.connect(@db_name, @auth_type) + end + + # Establish a new connection using named parameters. + # * user: username (default is monetdb) + # * passwd: password (default is monetdb) + # * language: lang (default is sql) + # * host: host to connect to (default is localhost) + # * port: port to connect to (default is 50000) + # * database: name of the database to connect to + # * auth_type: hashing function to use during authentication (default is SHA1) + # + # Conventionally named parameters are passed as an hash. + # + # Ruby 1.8: + # MonetDB::conn({ :user => "username", :passwd => "password", :database => "database"}) + # + # Ruby 1.9: + # MonetDB::conn(user: "username", passwd: "password", database: "database") + def conn(options) + user = options[:user] || DEFAULT_USERNAME + passwd = options[:passwd] || DEFAULT_PASSWORD + language = options[:language] || DEFAULT_LANG + host = options[:host] || DEFAULT_HOST + port = options[:port] || DEFAULT_PORT + database = options[:database] || DEFAULT_DATABASE + auth_type = options[:auth_type] || DEFAULT_AUTHTYPE + + connect(user, passwd, language, host, port, database, auth_type) + end + + # Send a <b> user submitted </b> query to the server and store the response. + # Returns and instance of MonetDBData. + def query(q="") + if @connection != nil + @data = MonetDBData.new(@connection) + @data.execute(q) + end + return @data + end + + # Return true if there exists a "connection" object + def is_connected? + if @connection == nil + return false + else + return true + end + end + + # Reconnect to the server + def reconnect + if @connection != nil + self.close + + @connection = MonetDBConnection.new(user = @username, passwd = @password, lang = @lang, host = @host, port = @port) + @connection.connect(db_name = @db_name, auth_type = @auth_type) + end + end + + # Turn auto commit on/off + def auto_commit(flag=true) + @connection.set_auto_commit(flag) + end + + # Returns the current auto commit (on/off) settings. + def auto_commit? + @connection.auto_commit? + end + + # Returns the name of the last savepoint in a transactions pool + def transactions + @connection.savepoint + end + + # Create a new savepoint ID + def save + @connection.transactions.save + end + + # Release a savepoint ID + def release + @connection.transactions.release + end + + # Close an active connection + def close() + @connection.disconnect + @connection = nil + end +end
new file mode 100644 --- /dev/null +++ b/lib/MonetDBConnection.rb @@ -0,0 +1,493 @@ +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V. + +# Implements the MAPI communication protocol + + +require 'socket' +require 'time' +require_relative 'hasher' +require_relative 'MonetDBExceptions' +require 'uri' # parse merovingian redirects + +class MonetDBConnection + Q_TABLE = "1" # SELECT operation + Q_UPDATE = "2" # INSERT/UPDATE operations + Q_CREATE = "3" # CREATE/DROP TABLE operations + Q_TRANSACTION = "4" # TRANSACTION + Q_PREPARE = "5" + Q_BLOCK = "6" # QBLOCK message + + MSG_REDIRECT = '^' # auth redirection through merovingian + MSG_QUERY = '&' + MSG_SCHEMA_HEADER = '%' + MSG_INFO = '!' # info response from mserver + MSG_TUPLE = '[' + MSG_PROMPT = "" + + + REPLY_SIZE = '-1' + + MAX_AUTH_ITERATION = 10 # maximum number of atuh iterations (thorough merovingian) allowed + + MONET_ERROR = -1 + + LANG_SQL = "sql" + + # Protocols + MAPIv9 = 9 + + MONETDB_MEROVINGIAN = "merovingian" + MONETDB_MSERVER = "monetdb" + + MEROVINGIAN_MAX_ITERATIONS = 10 + + + # enable debug output + @@DEBUG = false + + # hour in seconds, used for timezone calculation + @@HOUR = 3600 + + # maximum size (in bytes) for a monetdb message to be sent + @@MAX_MESSAGE_SIZE = 32766 + + # endianness of a message sent to the server + @@CLIENT_ENDIANNESS = "BIG" + + # MAPI protocols supported by the driver + @@SUPPORTED_PROTOCOLS = [ MonetDBConnection::MAPIv9 ] + + attr_reader :socket, :auto_commit, :transactions, :lang + + # Instantiates a new MonetDBConnection object + # * user: username (default is monetdb) + # * passwd: password (default is monetdb) + # * lang: language (default is sql) + # * host: server hostanme or ip (default is localhost) + # * port: server port (default is 50000) + + def initialize(user = "monetdb", passwd = "monetdb", lang = "sql", host="127.0.0.1", port = "50000") + @user = user + @passwd = passwd + @lang = lang.downcase + @host = host + @port = port + + @client_endianness = @@CLIENT_ENDIANNESS + + @auth_iteration = 0 + @connection_established = false + + @transactions = MonetDBTransaction.new # handles a pool of transactions (generates and keeps track of savepoints) + + if @@DEBUG == true + require 'logger' + end + + if @lang[0, 3] == 'sql' + @lang = "sql" + end + + end + + # Connect to the database, creates a new socket + def connect(db_name = 'demo', auth_type = 'SHA1') + @database = db_name + @auth_type = auth_type + + @socket = TCPSocket.new(@host, @port.to_i) + if real_connect + if @lang == MonetDBConnection::LANG_SQL + set_timezone + set_reply_size + end + true + end + false + end + + + # perform a real connection; retrieve challenge, proxy through merovinginan, build challenge and set the timezone + def real_connect + + server_challenge = retrieve_server_challenge() + if server_challenge != nil + salt = server_challenge.split(':')[0] + @server_name = server_challenge.split(':')[1] + @protocol = server_challenge.split(':')[2].to_i + @supported_auth_types = server_challenge.split(':')[3].split(',') + @server_endianness = server_challenge.split(':')[4] + if @@SUPPORTED_PROTOCOLS.include?(@protocol) == false + raise MonetDBProtocolError, "Protocol not supported. The current implementation of ruby-monetdb works with MAPI protocols #{@@SUPPORTED_PROTOCOLS} only." + end + @pwhash = server_challenge.split(':')[5] + else + raise MonetDBConnectionError, "Error: server returned an empty challenge string." + end + + # The server supports only RIPMED160 or crypt as an authentication hash function, but the driver does not. + if @supported_auth_types.length == 1 + auth = @supported_auth_types[0] + if auth.upcase == "RIPEMD160" + raise MonetDBConnectionError, auth.upcase + " " + ": algorithm not supported by ruby-monetdb." + end + end + + reply = build_auth_string_v9(@auth_type, salt, @database) + + if @socket != nil + @connection_established = true + + send(reply) + monetdb_auth = receive + + if monetdb_auth.length == 0 + # auth succedeed + true + else + if monetdb_auth[0].chr == MonetDBConnection::MSG_REDIRECT + #redirection + + redirects = [] # store a list of possible redirects + + monetdb_auth.split('\n').each do |m| + # strip the trailing ^mapi: + # if the redirect string start with something != "^mapi:" or is empty, the redirect is invalid and shall not be included. + if m[0..5] == "^mapi:" + redir = m[6..m.length] + # url parse redir + redirects.push(redir) + else + $stderr.print "Warning: Invalid Redirect #{m}" + end + end + + if redirects.size == 0 + raise MonetDBConnectionError, "No valid redirect received" + else + begin + uri = URI.split(redirects[0]) + # Splits the string on following parts and returns array with result: + # + # * Scheme + # * Userinfo + # * Host + # * Port + # * Registry + # * Path + # * Opaque + # * Query + # * Fragment + server_name = uri[0] + host = uri[2] + port = uri[3] + database = uri[5].gsub(/^\//, '') if uri[5] != nil + rescue URI::InvalidURIError + raise MonetDBConnectionError, "Invalid redirect: #{redirects[0]}" + end + end + + if server_name == MonetDBConnection::MONETDB_MEROVINGIAN + if @auth_iteration <= MonetDBConnection::MEROVINGIAN_MAX_ITERATIONS + @auth_iteration += 1 + real_connect + else + raise MonetDBConnectionError, "Merovingian: too many iterations while proxying." + end + elsif server_name == MonetDBConnection::MONETDB_MSERVER + begin + @socket.close + rescue + raise MonetDBConnectionError, "I/O error while closing connection to #{@socket}" + end + # reinitialize a connection + @host = host + @port = port + + connect(database, @auth_type) + else + @connection_established = false + raise MonetDBConnectionError, monetdb_auth + end + elsif monetdb_auth[0].chr == MonetDBConnection::MSG_INFO + raise MonetDBConnectionError, monetdb_auth + end + end + end + end + + def savepoint + @transactions.savepoint + end + + # Formats a <i>command</i> string so that it can be parsed by the server + def format_command(x) + return "X" + x + "\n" + end + + + # send an 'export' command to the server + def set_export(id, idx, offset) + send(format_command("export " + id.to_s + " " + idx.to_s + " " + offset.to_s )) + end + + # send a 'reply_size' command to the server + def set_reply_size + send(format_command(("reply_size " + MonetDBConnection::REPLY_SIZE))) + + response = receive + + if response == MonetDBConnection::MSG_PROMPT + true + elsif response[0] == MonetDBConnection::MSG_INFO + raise MonetDBCommandError, "Unable to set reply_size: #{response}" + end + + end + + def set_output_seq + send(format_command("output seq")) + end + + # Disconnect from server + def disconnect() + if @connection_established + begin + @socket.close + rescue => e + $stderr.print e + end + else + raise MonetDBConnectionError, "No connection established." + end + end + + # send data to a monetdb5 server instance and returns server's response + def send(data) + encode_message(data).each do |m| + @socket.write(m) + end + end + + # receive data from a monetdb5 server instance + def receive + is_final, chunk_size = recv_decode_hdr + + if chunk_size == 0 + return "" # needed on ruby-1.8.6 linux/64bit; recv(0) hangs on this configuration. + end + + data = @socket.recv(chunk_size) + + if is_final == false + while is_final == false + is_final, chunk_size = recv_decode_hdr + data += @socket.recv(chunk_size) + end + end + + return data + end + + # + # Builds and authentication string given the parameters submitted by the user (MAPI protocol v9). + # + def build_auth_string_v9(auth_type, salt, db_name) + if (auth_type.upcase == "MD5" or auth_type.upcase == "SHA1") and @supported_auth_types.include?(auth_type.upcase) + auth_type = auth_type.upcase + # Hash the password + pwhash = Hasher.new(@pwhash, @passwd) + + digest = Hasher.new(auth_type, pwhash.hashsum + salt) + hashsum = digest.hashsum + + elsif auth_type.downcase == "plain" # or not @supported_auth_types.include?(auth_type.upcase) + # Keep it for compatibility with merovingian + auth_type = 'plain' + hashsum = @passwd + salt + elsif @supported_auth_types.include?(auth_type.upcase) + if auth_type.upcase == "RIPEMD160" + auth_type = @supported_auth_types[@supported_auth_types.index(auth_type)+1] + $stderr.print "The selected hashing algorithm is not supported by the Ruby driver. #{auth_type} will be used instead." + end + # Hash the password + pwhash = Hasher.new(@pwhash, @passwd) + + digest = Hasher.new(auth_type, pwhash.hashsum + salt) + hashsum = digest.hashsum + else + # The user selected an auth type not supported by the server. + raise MonetDBConnectionError, "#{auth_type} not supported by the server. Please choose one from #{@supported_auth_types}" + end + # Build the reply message with header + reply = @client_endianness + ":" + @user + ":{" + auth_type + "}" + hashsum + ":" + @lang + ":" + db_name + ":" + end + + # builds a message to be sent to the server + def encode_message(msg = "") + message = Array.new + data = "" + + hdr = 0 # package header + pos = 0 + is_final = false # last package in the stream + + while (! is_final) + data = msg[pos..pos+[@@MAX_MESSAGE_SIZE.to_i, (msg.length - pos).to_i].min] + pos += data.length + + if (msg.length - pos) == 0 + last_bit = 1 + is_final = true + else + last_bit = 0 + end + + hdr = [(data.length << 1) | last_bit].pack('v') + + message << hdr + data.to_s # Short Little Endian Encoding + end + + message.freeze # freeze and return the encode message + end + + # Used as the first step in the authentication phase; retrives a challenge string from the server. + def retrieve_server_challenge() + server_challenge = receive + end + + # reads and decodes the header of a server message + def recv_decode_hdr() + if @socket != nil + fb = @socket.recv(1) + sb = @socket.recv(1) + + # Use execeptions handling to keep compatibility between different ruby + # versions. + # + # Chars are treated differently in ruby 1.8 and 1.9 + # try do to ascii to int conversion using ord (ruby 1.9) + # and if it fail fallback to character.to_i (ruby 1.8) + begin + fb = fb[0].ord + sb = sb[0].ord + rescue NoMethodError => one_eight + fb = fb[0].to_i + sb = sb[0].to_i + end + + chunk_size = (sb << 7) | (fb >> 1) + + is_final = false + if ( (fb & 1) == 1 ) + is_final = true + + end + # return the size of the chunk (in bytes) + return is_final, chunk_size + else + raise MonetDBSocketError, "Error while receiving data\n" + end + end + + # Sets the time zone according to the Operating System settings + def set_timezone() + tz = Time.new + tz_offset = tz.gmt_offset / @@HOUR + + if tz_offset <= 9 # verify minute count! + tz_offset = "'+0" + tz_offset.to_s + ":00'" + else + tz_offset = "'+" + tz_offset.to_s + ":00'" + end + query_tz = "sSET TIME ZONE INTERVAL " + tz_offset + " HOUR TO MINUTE;" + + # Perform the query directly within the method + send(query_tz) + response = receive + + if response == MonetDBConnection::MSG_PROMPT + true + elsif response[0].chr == MonetDBConnection::MSG_INFO + raise MonetDBQueryError, response + end + end + + # Turns auto commit on/off + def set_auto_commit(flag=true) + if flag == false + ac = " 0" + else + ac = " 1" + end + + send(format_command("auto_commit " + ac)) + + response = receive + if response == MonetDBConnection::MSG_PROMPT + @auto_commit = flag + elsif response[0].chr == MonetDBConnection::MSG_INFO + raise MonetDBCommandError, response + return + end + + end + + # Check the auto commit status (on/off) + def auto_commit? + @auto_commit + end + + # Check if monetdb is running behind the merovingian proxy and forward the connection in case + def merovingian? + if @server_name.downcase == MonetDBConnection::MONETDB_MEROVINGIAN + true + else + false + end + end + + def mserver? + if @server_name.downcase == MonetDBConnection::MONETDB_MSERVER + true + else + false + end + end +end + +# handles transactions and savepoints. Can be used to simulate nested transactions. +class MonetDBTransaction + SAVEPOINT_STRING = "monetdbsp" + + def initialize + @id = 0 + @savepoint = "" + end + + def savepoint + @savepoint = SAVEPOINT_STRING + @id.to_s + end + + def release + prev_id + end + + def save + next_id + end + + private + def next_id + @id += 1 + end + + def prev_id + @id -= 1 + end + +end
new file mode 100644 --- /dev/null +++ b/lib/MonetDBData.rb @@ -0,0 +1,328 @@ +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V. + +# Models a MonetDB RecordSet +require 'time' +require 'date' +require 'ostruct' + +require "bigdecimal" + +require_relative 'MonetDBConnection' + +require 'logger' + +class MonetDBData + @@DEBUG = false + + def initialize(connection) + @connection = connection + @lang = @connection.lang + + # Structure containing the header+results set for a fired Q_TABLE query + @header = [] + @query = {} + + @record_set = [] + @index = 0 # Position of the last returned record + + + @row_count = 0 + @row_offset = 10 + @row_index = Integer(MonetDBConnection::REPLY_SIZE) + end + + # Fire a query and return the server response + def execute(q) + # fire a query and get ready to receive the data + @connection.send(format_query(q)) + data = @connection.receive + + return if data == nil + + record_set = "" # temporarly store retrieved rows + record_set = receive_record_set(data) + + if (@lang == MonetDBConnection::LANG_SQL) + rows = receive_record_set(data) + # the fired query is a SELECT; store and return the whole record set + if @action == MonetDBConnection::Q_TABLE + @header = parse_header_table(@header) + @header.freeze + + if @row_index.to_i < @row_count.to_i + block_rows = "" + while next_block + data = @connection.receive + block_rows += receive_record_set(data) + end + record_set += block_rows + end + end + + # ruby string management seems to not properly understand the MSG_PROMPT escape character. + # In order to avoid data loss the @record_set array is built once that all tuples have been retrieved + @record_set = record_set.split("\t]\n") + + if @record_set.length != @query['rows'].to_i + raise MonetDBQueryError, "Warning: Query #{@query['id']} declared to result in #{@query['rows']} but #{@record_set.length} returned instead" + end + end + @record_set.freeze + end + + # Returns the record set entries hashed by column name orderd by column position + def fetch_all_as_column_hash + columns = {} + @header["columns_name"].each do |col_name| + columns[col_name] = fetch_by_column_name(col_name) + end + + return columns + end + + # returns a record hash (i.e: { id: 1, name: "John Doe", age: 42 } ) + def fetch_hash + return false if @index >= @query['rows'].to_i + + record_hash = record_hash(parse_tuple(@record_set[@index])) + @index += 1 + return record_hash + end + + # loops through all the hashes of the records and yields them to a given block + def each_record_as_hash + @record_set.each do |record| + parsed_record = parse_tuple(record) + yield(record_hash(parsed_record)) + end + end + + # Returns the values for the column 'field' + def fetch_by_column_name(column_name="") + position = @header["columns_order"].fetch(column_name) + + column_values = [] + @record_set.each do |row| + column_values << parse_tuple(row)[position] + end + + return column_values + end + + # fetches a single record, updates the iterator index + def fetch + return false if @index >= @query['rows'].to_i + + result = parse_tuple(@record_set[@index]) + @index += 1 + + return result + end + + # resets the internal iterator index used by fetch and fetch_hash + def reset_index + @index = 0 + end + + # loops through all records and yields to a given block paramter + def each_record + raise MonetDBDataError, "There is no record set currently available" unless @query['type'] == MonetDBConnection::Q_TABLE + @record_set.each { |record| yield(parse_tuple(record)) } + end + + # Cursor method that returns all the records + def fetch_all + result = [] + each_record do |record| + result.push(record) + end + return result + end + + # Returns the number of rows in the record set + def num_rows + return @query['rows'].to_i + end + + # Returns the number of fields in the record set + def num_fields + return @query['columns'].to_i + end + + # Returns the (ordered) name of the columns in the record set + def name_fields + return @header['columns_name'] + end + + # Returns the (ordered) name of the columns in the record set + def type_fields + return @header['columns_type'] + end + + # =================== + private + # =================== + + # store block of data, parse it and store it. + def receive_record_set(response) + rows = "" + response.each_line do |row| + case row[0] + when MonetDBConnection::MSG_QUERY then parse_query(row) + when MonetDBConnection::MSG_INFO then raise MonetDBQueryError, row + when MonetDBConnection::MSG_SCHEMA_HEADER then @header << row + when MonetDBConnection::MSG_TUPLE then rows += row + when MonetDBConnection::MSG_PROMPT then return rows + end + end + return rows # return an array of unparsed tuples + end + + def parse_query(row) + case row[1] + when MonetDBConnection::Q_TABLE + @action = MonetDBConnection::Q_TABLE + @query = parse_header_query(row) + @query.freeze + @row_count = @query['rows'].to_i #total number of rows in table + when MonetDBConnection::Q_BLOCK + @action = MonetDBConnection::Q_BLOCK # strip the block header from data + @block = parse_header_query(row) + when MonetDBConnection::Q_TRANSACTION + @action = MonetDBConnection::Q_TRANSACTION + when MonetDBConnection::Q_CREATE + @action = MonetDBConnection::Q_CREATE + end + end + + def record_hash(record) + result = {} + + @header["columns_name"].each do |column_name| + position = @header["columns_order"].fetch(column_name) + result[column_name] = record[position] + end + + return result + end + + def next_block + if @row_index == @row_count + return false + else + # The increment step is small to better deal with ruby socket's performance. + # For larger values of the step performance drop; + # + @row_offset = [@row_offset, (@row_count - @row_index)].min + + # export offset amount + @connection.set_export(@query['id'], @row_index.to_s, @row_offset.to_s) + @row_index += @row_offset + @row_offset += 1 + end + return true + + end + + # Formats a query <i>string</i> so that it can be parsed by the server + def format_query(q) + if @lang == MonetDBConnection::LANG_SQL + return "s" + q + "\n;" + else + raise LanguageNotSupported, @lang + end + end + + # parse one tuple as returned from the server + def parse_tuple(tuple) + fields = [] + # remove trailing "[" + tuple = tuple.gsub(/^\[\s+/,'') + tuple.split(/,\t/).each_with_index do |field, index| + field_value = convert_type(field, index) + fields << field_value + end + + return fields.freeze + end + + # converts the given value the correct type + def convert_type(value, index) + return nil if "NULL" == value.upcase + return case type_fields.values[index] + when "int", "tinyint", "smallint", "bigint", "hugeint" then value.to_i + when "double", "real", "decimal" then value.to_f + when "boolean" then value.downcase == true + when "date" then Date.parse(value) + when "time" then Time.parse(value, Time.new("2000-01-01")) + when "timestamp" then DateTime.parse(value) + when "timestamptz" then DateTime.parse(value) + else value.gsub(/\\/, '').gsub(/^"/,'').gsub(/"$/,'').gsub(/\"/, '') + end + end + + # Parses a query header and returns information about the query. + def parse_header_query(row) + type = row[1].chr + if type == MonetDBConnection::Q_TABLE + # Performing a SELECT: store informations about the table size, query id, total number of records and returned. + id = row.split(' ')[1] + rows = row.split(' ')[2] + columns = row.split(' ')[3] + returned = row.split(' ')[4] + + header = { "id" => id, "type" => type, "rows" => rows, "columns" => columns, "returned" => returned } + elsif type == MonetDBConnection::Q_BLOCK + # processing block header + + id = row.split(' ')[1] + columns = row.split(' ')[2] + remains = row.split(' ')[3] + offset = row.split(' ')[4] + + header = { "id" => id, "type" => type, "remains" => remains, "columns" => columns, "offset" => offset } + else + header = {"type" => type} + end + + return header.freeze + end + + # Parses a Q_TABLE header and returns information about the schema. + def parse_header_table(header_t) + if @query["type"] == MonetDBConnection::Q_TABLE + if header_t != nil + name_t = header_t[0].split(' ')[1].gsub(/,$/, '') + name_cols = Array.new + + header_t[1].split('%')[1].gsub(/'^\%'/, '').split('#')[0].split(' ').each do |col| + name_cols << col.gsub(/,$/, '') + end + + type_cols = { } + header_t[2].split('%')[1].gsub(/'^\%'/, '').split('#')[0].split(' ').each_with_index do |col, i| + if col.gsub(/,$/, '') != nil + type_cols[ name_cols[i] ] = col.gsub(/,$/, '') + end + end + + length_cols = { } + header_t[3].split('%')[1].gsub(/'^\%'/, '').split('#')[0].split(' ').each_with_index do |col, i| + length_cols[ name_cols[i] ] = col.gsub(/,$/, '') + end + + columns_order = {} + name_cols.each_with_index do |col, i| + columns_order[col] = i + end + + return {"table_name" => name_t, "columns_name" => name_cols, "columns_type" => type_cols, + "columns_length" => length_cols, "columns_order" => columns_order}.freeze + end + end + end +end
new file mode 100644 --- /dev/null +++ b/lib/MonetDBExceptions.rb @@ -0,0 +1,44 @@ +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V. + +# Exception classes for the ruby-monetdb driver + +class MonetDBQueryError < StandardError + def initialize(e) + $stderr.puts e + end +end + +class MonetDBDataError < StandardError + def initialize(e) + $stderr.puts e + end +end + +class MonetDBCommandError < StandardError + def initialize(e) + $stderr.puts e + end +end + +class MonetDBConnectionError < StandardError + def initialize(e) + $stderr.puts e + end +end + + +class MonetDBSocketError < StandardError + def initialize(e) + $stderr.puts e + end +end + +class MonetDBProtocolError < StandardError + def initialize(e) + $stderr.puts e + end +end
new file mode 100644 --- /dev/null +++ b/lib/example.rb @@ -0,0 +1,65 @@ +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V. + +require_relative 'MonetDB' + +def print_result(result, message="") + puts message + puts "========" + result.each_record do |record| + puts record + end + puts "========" + puts +end + +def get_database_connection + database_connection = MonetDB.new + database_connection.connect(user = "monetdb", passwd = "monetdb", lang = "sql", host="127.0.0.1", port = 50000, database_connection_name = "testdatabase2", auth_type = "SHA1") + return database_connection +end + +# Connect to the database. This assumes you have a MonetDB server running with a database called testdatabase2 +db = get_database_connection + +# Add a table and some data to the database +db.query("CREATE TABLE testtable (testnumber INTEGER)") +db.query("INSERT INTO testtable(testnumber) VALUES (1)") +db.query("INSERT INTO testtable(testnumber) VALUES (2)") + +# Get some data from the database +result = db.query("SELECT * FROM testtable") +print_result(result, "Showing all data in table") + +# Use of autocommit +db.auto_commit(false) +db.query("INSERT INTO testtable(testnumber) VALUES (100)") +db.query("INSERT INTO testtable(testnumber) VALUES (101)") +result = db.query("SELECT * FROM testtable") +print_result(result, "First connection: 100 and 101 have not yet been saved, but are shown because they were added by the same connection") + +# Show that the data is not really in the database yet +db2 = get_database_connection +result = db2.query("SELECT * FROM testtable") +print_result(result, "Second connection: 100 and 101 are not shown, because autocommit is off and it is a different connection") + +# Save the database data +db.query("COMMIT") +result = db2.query("SELECT * FROM testtable") +print_result(result, "Second connection: 100 and 101 are shown now, because they were committed in db connection 1") +db.auto_commit(true) + +# Get the data with other public interface methods +puts "number of rows: #{result.num_rows}" +puts "Fetch all: #{result.fetch_all}" +puts "As column hash: #{result.fetch_all_as_column_hash}" +puts "Iterate one at a time: #{result.fetch}" +result.reset_index # restart the iterator used in the single fetch methods +puts "Iterate one at a time as hash: #{result.fetch_hash}" + +# close the connection +db.query("DROP TABLE testtable") +db.close
new file mode 100644 --- /dev/null +++ b/lib/hasher.rb @@ -0,0 +1,45 @@ +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V. + +require 'digest/md5' +require 'digest/sha1' +require 'digest/sha2' + +class Hasher + # Constructor + # method = "SHA1" or "MD5" + # pwd = Password + def initialize(method, pwd) + if (method.upcase == "SHA1") + @hashfunc = Digest::SHA1.new + @hashname = method.upcase + elsif (method.upcase == "SHA256") + @hashfunc = Digest::SHA256.new + @hashname = method.upcase + elsif (method.upcase == "SHA384") + @hashfunc = Digest::SHA384.new + @hashname = method.upcase + elsif (method.upcase == "SHA512") + @hashfunc = Digest::SHA512.new + @hashname = method.upcase + else + # default to MD5 + @hashfunc = Digest::MD5.new + @hashname = "MD5" + end + @pwd = pwd + end + + + def hashname + @hashname + end + + # Compute hash code + def hashsum + return @hashfunc.hexdigest(@pwd) + end +end
new file mode 100644 --- /dev/null +++ b/monetdb-sql.gemspec @@ -0,0 +1,18 @@ + +Gem::Specification.new do |s| + s.required_ruby_version = '>= 1.8.0' + s.name = %q{monetdb-sql} + s.version = "1.0" + s.date = %q{2016-03-02} + s.authors = ["G Modena", "R Koopmanschap"] + s.email = "info@monetdb.org" + s.license = "MPL-2.0" + s.summary = %q{Pure Ruby database driver for MonetDB/SQL} + s.homepage = %q{http://www.monetdb.org/} + s.description = %q{Pure Ruby database driver for the MonetDB/SQL columnar database management system} + s.files = ["lib/MonetDB.rb", "lib/MonetDBConnection.rb", "lib/MonetDBData.rb", "lib/MonetDBExceptions.rb", "lib/hasher.rb"] + s.has_rdoc = true + s.require_path = './lib' + # placeholder project to avoid warning about not having a rubyforge_project + s.rubyforge_project = "nowarning" +end
new file mode 100644 --- /dev/null +++ b/rubygem-monetdb-sql.spec @@ -0,0 +1,70 @@ +%global gem_name monetdb-sql + +Name: rubygem-%{gem_name} +Epoch: 1 +Version: 1.0 +Release: 1%{?dist} +Summary: Pure Ruby database driver for MonetDB/SQL +Group: Applications/Databases + +License: MPLv2.0 +URL: http://www.monetdb.org/ +Source0: http://dev.monetdb.org/downloads/ruby/gems/%{gem_name}-%{version}.gem + +BuildRequires: ruby(release) +BuildRequires: rubygems-devel +BuildRequires: ruby >= 1.8.0 +BuildArch: noarch + +Requires: ruby(release) +Requires: rubygem-bigdecimal + +Recommends: MonetDB-SQL-server5 >= 11.22.0 +Suggests: %{name}-doc = %{version}-%{release} + +%description +MonetDB is a database management system that is developed from a +main-memory perspective with use of a fully decomposed storage model, +automatic index management, extensibility of data types and search +accelerators. It also has an SQL frontend. + +This package contains a pure Ruby database driver for MonetDB/SQL. + +%package doc +Summary: Documentation for %{name} +Group: Documentation +Requires: %{name} = %{version}-%{release} +BuildArch: noarch + +%description doc +This package contains documentation for %{name}. + +%prep +%setup -q -c -T +cp %{SOURCE0} . + + +%build +%gem_install + + +%install +mkdir -p %{buildroot}%{gem_dir} +cp -a .%{gem_dir}/* %{buildroot}/%{gem_dir} + +find %{buildroot}%{gem_instdir} -name \*.rb -exec chmod 0644 '{}' + + + +%files +%dir %{gem_instdir} +%{gem_libdir} +%exclude %{gem_cache} +%{gem_spec} + +%files doc +%doc %{gem_docdir} + + +%changelog +* Wed Mar 2 2016 Sjoerd Mullender <sjoerd@acm.org> - 1.0-1 +- The Ruby interface to MonetDB is now a separate package.