SQL Relay Client API's

The SQL Relay client API's are ultimately based on the C++ API and the Rudiments library. A more portable approach would be to implement the API natively in a language like Java, Python or Perl. A more stripped down and potentially higher performance version of the API could be written in any language.

SQL Relay has always had an open-protocol by virtue of being open-source, but reverse-engineering it from the API code can be a daunting task. If it were documented in plainer terms, it would be "more open".

Born of these motivations, the following pseudocode demonstrates the protocol and its capabilities. This code should provide enough detail about the protocol to enable a developer to write his or her own API.

Note that this specification will likely change a bit as new features are added to SQL Relay.


client() {
	connect to the sqlr-listener using a tcp or unix datagram socket
	send an authentication string()
	get a result string()

	if (successful authentication) {

		string contains the inet port and unix socket filename that an available sqlr-connection-"dbase" is listening on
		disconnect from the sqlr-listener
		connect to the sqlr-connection-"dbase" using a tcp or unix datagram socket 
	
		start a new session or resume a suspended session
	
		if (new session) {
			getLong() a -1
			session()
		} else if (resume session) {
			getLong() the index of the row that the sqlr-connection-"dbase" is currently on
			process rows() to deal with the suspended result set
			session()
		}
	
	} else if (unsuccessful authentication) {
		receive and parse the error()
	}
}



send an authentication string() {
	sendLong() the length of the user name
	sendString() the user name
	sendLong() the length of the password
	sendString() the password
}



get a result string() {

	size=getShort(): the size of the result string

	if (size is -2) {
		there has been an authentication error
		close the connection
	} else {
		read "size" 8 bit characters from the socket: the unix port
		inetport=getShort(): the inet port
	}
}



session() {

	loop {
		query, ping or identify()
		end or suspend the session() or keep going
	}

	close the connection
}



query, ping or identify() {

	if (query) {
		send query()
		send bind variables and values()
		receive and parse the header()
		receive and parse the output bind values()

		if (there has been an error) {
			receive and parse the error()
		} else {
			process rows()
		}
	} else if (ping) {
		sendString() "ping"
		getShort() 1 or 0
	} else if (identify) {
		sendString() "identify"
		size=getShort(): the size of the string
		read "size" 8 bit characters from the socket: the identification string
	}
}



end or suspend the session() {
	if (end) {
		sendString() a ~
		break out of loop
	} else if (suspend) {
		sendString() a !
		break out of loop
	}
}



send query() {
	sendLong() the length of the query
	sendString() the query
}



send bind variables and values() {

	Note: the connection daemon will put the colon on the front of the
		bind variables, you shouldn't send one

	sendShort() the number of input bind variables

	loop through the input bind variables {
		sendShort() the size of a bind variable
		sendString() the bind variable
		sendShort() the size of a bind value
		sendString() the bind value
	}

	sendShort() the number of output bind variables

	loop through the output bind variables {
		sendShort() the size of a bind variable
		sendString() the bind variable
		sendShort() the size of a bind value
	}
}



receive and parse the header() {

	totalrows=getLong(): the total number of rows in the result set

	if (totalrows is -2) {
		there was an error in the query
		return
	}

	affectedrows=getLong(): the number of rows affected by the query

	Note: some databases support one, the other or neither of these
		row counts, those databases return -1 instead

	loop {

		size=getShort(): the size of the column name
		if (size is 0) {
			break out of loop
		}

		read "size" 8 bit characters from the socket: the column name

		type=getShort(): the column type
		length=getLong(): the column length

		(refer to src/common/datatypes.h of the distribution)
		datatypestring[type] is the string corresponding to the column type
	}
}



receive and parse the output bind values() {

	loop {
		size=getShort(): the size of the value

		if (size is 0) {
			break out of loop
		}

		read "size" 8 bit characters from the socket: the value
	}
}


process rows() {

	loop {
		receive rows, suspend rows or quit receiving rows

		if (receive rows) {
			sendLong() the number of rows to skip
			sendLong() the number of rows to fetch
			receive and parse the rows()
		} else if (quit receiving rows) {
			sendLong() a -1
			sendLong() a 0
			getLong() a -1 (end of result set)
			break out of loop
		} else if (suspend rows) {
			sendLong() a -2
			sendLong() a 0
			getLong() a -1 (end of result set)
			break out of loop
		}

		if (got end of result set) {
			break out of loop
		}
	}
}


receive and parse the rows() {

	loop {
		if (we've read as many rows as we asked for) {
			break out of the loop
		}

		size=getLong(): the size of the field data

		if (size is -1) {
			break out of the loop, we've hit the end of the result set
		} else if (size is -3) {
			getLongField(); the field is a long/LOB datatype
		} else {
			read "size" 8 bit characters from the socket: the field data
		}
	}
}


getLongField() {
	
	loop {
		size=getLong(): the size of this chunk

		if (size is -3) {
			break out of the loop
		}

		read "size" 8 bit characters from the socket: a chunk of data
	}
}


receive and parse the error() {
	size=getShort(): the size of the error message
	read "size" 8 bit characters from the socket: the error message
}


sendString() {
	write the string to the socket
}


sendShort() {
	write the 16 bit number to the socket
}


sendLong() {
	write the 32 bit number to the socket
}


getShort() {
	read a 16 bit number from the socket
}


getLong() {
	read a 32 bit number from the socket
}