2.2.13 Cycle 13 - Introducing a sql database

Design

Objectives

Now that you can send messages between nodes, it's time to introduce a way of storing these messages and the best way in which I can think of that to be done is by creating a local database such that the node can store and load data onto that database and use it as it's memory. The benefit of introducing a database for this reason also means that the references that were introduced in Cycle 11 - Remembering Nodes can be moved to the database for faster and better storage.

The way I'm going to go to do this is through the use of docker, which is a service for generating containerised "virtual machines" that *should* run identically no matter the hardware. This means all the program has to do is tell docker to generate a postgreSQL database image and as long as the user has docker installed, it should then be able to be connect and use this just like it would any other database.

PostgreSQL was chosen because V - the language the node is written in - supports it and I have used it in other projects before so should be able to work with it easily.

• Generate a postgreSQL docker container and connect to it upon startup

• Build the postgreSQL table with required fields.

• Refactor references to use the database rather than the json file used at the moment.

• Save messages to the database and only forward them through the network if the node hasn't seen it before.

Usability Features

• Docker container and database should be controlled automatically by the node software by default to minimise the amount of interactions users have to have with the software.

• Contain the ability to change database settings to use a dedicated, custom, database server to make grouping nodes together easier for the user.

Key Variables

Variable Name	Use
init_psql_docker	This is a constant that defines the shell command that is run by the software and creates a docker container that runs the postgreSQL database.
start_psql_docker	This is a constant that runs a shell command that runs to start a pre-made docker container.
stop_psql_docker	This is a constant that runs a shell command to stop a currently running docker container

Pseudocode

The two primary files to be created in this cycle are for the generation of the docker container which runs the database and the initialisation of the database tables. The other additions that need to be made are logically the same as they were with the JSON file storage method so I will not include psuedocode for them but will show the code after they are completed in the outcome section of this cycle.

Docker Container Handling

This code will allow the node software to send direct bash commands to the Operating System and through that create, start and stop a docker container which houses the postgreSQL database used by the software.

// external imports
IMPORT operating_systemtem as	// os stands for operating system
IMPORT time

// config constants
image_name = "monochain-postgresql"
db_name = "postgres"
db_password = "password"

// shell commands
init_psql_docker = "docker run --name $image_name -p 5432:5432 -e POSTGRES_PASSWORD=$db_password -d postgres"
start_psql_docker = "docker start $image_name"
stop_psql_docker = "docker stop $image_name"

// returns whether or not the command executed properly
FUNCTION sh(cmd):
	//sh stands for shell as in a shell commadn
	  OUTPUT "[shell] > $cmd"
	  executed = os.execute(cmd)
	  OUTPUT "[shell] - $executed.output"
	
	  // 0 means the command executed properly
	  RETURN (executed.exit_code == 0)
END FUNCTION

FUNCTION launch():
  	OUTPUT "[Database] Launching database..."
  	// try to start the container
  	started = sh(start_psql_docker)

  	IF (!started):
		OUTPUT "[Database] No database found, or other error, trying to initialise a new database..."
		initialised = sh(init_psql_docker)

		IF (!initialised):
			OUTPUT "\n\n[Database] Could not start or initialise database container, docker is probably not running."
			EXIT
		END IF

		OUTPUT "[Database] Database initialised successfully."
  	END IF
  
	time.sleep(3 SECONDS)
  	OUTPUT "[Database] Database running."
END FUNCTION

FUNCTION stop(){
	OUTPUT "[Database] Stopping database..."
	stopped = sh(stop_psql_docker)

	IF (!stopped):
		OUTPUT "[Database] Failed to stop database, may be due to container not existing, please check docker yourself..."
	ELSE
		OUTPUT "[Database] Stopped database."
	END IF
END FUNCTION

Database initliasation

All that needs to be done in this section is defining what the tables will look like in the database and creating functions to create those tables.

// external imports - postgreSQL is the database type
IMPORT postgres as pg

// config constants
host = "localhost"
port = 5432

config = pg.Config{
	host: host
	port: port
	user: "postgres"
	password: db_password
	dbname: db_name
}


OBJECT Http_Reference:
	id        	[primary; sql: serial; sql_type: 'SERIAL']	// just for the db
	domain		[default: '']	// domain of node
	key        	// key that is attached to node
	last_connected 	[default: 'CURRENT_TIMESTAMP'; sql_type: 'TIMESTAMP']	// when the reference was last used
END OBJECT

OBJECT Message_Table:
	id        	[primary; sql: serial; sql_type: 'SERIAL']	// just for the db
	timestamp  	[default: 'CURRENT_TIMESTAMP'; sql_type: 'TIMESTAMP']	// when the message was sent
	sender     	// key of the sender
	receiver   	// key of the receiver
	contents   	// contents of the message
	signature  	// the signature of the message 
END OBJECT

OBJECT DatabaseConnection:
	connection

	FUNCTION init_tables(this):
		OUTPUT "[Database] Creating http reference table..."
		
		SQL this.connection:
			CREATE table Http_Reference
		END SQL
		
		OUTPUT "[Database] Creating message table..."
		
		SQL this.connection {
			CREATE table Message_Table
		END SQL
		
		OUTPUT "[Database] Tables created.\n"
	END FUNCTION
END OBJECT

// interfacing with the tables using the pg module
FUNCTION connect(over_ride_config, supplied_config):
	config_to_use = config

	IF (over_ride_config):
		config_to_use = supplied_config
	END IF

	connection = {}
	
	TRY:
		connection = pg.connect(config_to_use)
	CATCH:
		OUTPUT "[Database] Could not connect to database, docker container probably not running.\n[Database] Raw error: $err\n[Database] There is a chance this was due to trying to connect before the database was ready, if so restarting the program should fix it."
		EXIT
	END TRY

	db = DatabaseConnection{connection: connection}

	OUTPUT "[Database] Connected to database. ($host:$port)"
	
	OUTPUT "[Database] Creating tables..."
	db.init_tables()
	RETURN db
END FUNCTION

Development

Although all the code shown in this cycle does not mention web-sockets, it is worth mentioning that whilst during the development of this cycle I was primarily working on switching from using json files to a database, I was also starting to work on the web-socket functionality that will be shown in the next cycle, hence the code for this cycle is available here but exploring outside the database module will show evidence of code that is referenced in the next cycle.

On that note, all of the following code is available in separate files within the 'database' file, stored at /packages/node/src/modules/database/ with the specific file names commented at the top of the code below.

Outcome

Docker Containerisation

Creating and handling the docker container

This code allows the node software to send direct bash commands to the Operating System and through that create, start and stop a docker container which houses the postgreSQL database used by the software.

// found at /database/containerHandling.v
module database

// external imports
import os
import time

// config constants
pub const image_name = "monochain-postgresql"
pub const db_name = "postgres"
pub const db_password = "password"

// shell commands
const init_psql_docker = "docker run --name $image_name -p 5432:5432 -e POSTGRES_PASSWORD=$db_password -d postgres"
const start_psql_docker = "docker start $image_name"
const stop_psql_docker = "docker stop $image_name"

// returns whether or not the command executed properly
fn sh(cmd string) bool {
  println("[shell] > $cmd")
  executed := os.execute(cmd)
  print("[shell] - $executed.output")

  // 0 means the command executed properly
  return executed.exit_code == 0 
}

pub fn launch(){
  	println("[Database] Launching database...")
  	// try to start the container
  	started := sh(start_psql_docker)

  	if !started {
		println("[Database] No database found, or other error, trying to initialise a new database...")
		initialised := sh(init_psql_docker)

		if !initialised {
			eprintln("\n\n[Database] Could not start or initialise database container, docker is probably not running.")
			exit(300)
		}

		println("[Database] Database initialised successfully.")
  	}
	time.sleep(3 * time.second)
  	println("[Database] Database running.")
}

pub fn stop(){
	println("[Database] Stopping database...")
	stopped := sh(stop_psql_docker)

	if !stopped {
		eprintln("[Database] Failed to stop database, may be due to container not existing, please check docker yourself...")
	} else {
		println("[Database] Stopped database.")
	}
}

Database Interactions

Interfacing with the database and creating relevant tables

The way in which the postgreSQL module works in vlang is to create a struct that represents a table and then to use it in the table's generation, hence a lot of this section of code is just constants and structs to handle these tables and connect to the database in the first place.

The second half of the code is then just to connect to the database and create the tables based upon those references.

// found at /database/postgresInterface.v
module database

// external imports
import pg

// config constants
const host = "localhost"
const port = 5432

const config = pg.Config{
	host: host
	port: port
	user: "postgres"
	password: db_password
	dbname: db_name
}


pub struct Http_Reference {
	pub:
		id        	int    	[primary; sql: serial; sql_type: 'SERIAL']	// just for the db
		domain     	string 	[default: '']	// domain of node
		key        	string 	// key that is attached to node
		last_connected 		string	[default: 'CURRENT_TIMESTAMP'; sql_type: 'TIMESTAMP']	// when the reference was last used
}

pub struct Message_Table {
	pub:
		id        	int    	[primary; sql: serial; sql_type: 'SERIAL']	// just for the db
		timestamp  	string 	[default: 'CURRENT_TIMESTAMP'; sql_type: 'TIMESTAMP']	// when the message was sent
		sender     	string	// key of the sender
		receiver   	string	// key of the receiver
		contents   	string 	// contents of the message
		signature  	string	// the signature of the message 
}

pub struct DatabaseConnection {
	pub mut:
		connection pg.DB
}

// interfacing with the tables using the pg module
pub fn connect(over_ride_config bool, supplied_config pg.Config) DatabaseConnection {
	mut config_to_use := config

	if over_ride_config {
		config_to_use = supplied_config
	}

	connection := pg.connect(config_to_use) or {
		eprintln("[Database] Could not connect to database, docker container probably not running.\n[Database] Raw error: $err\n[Database] There is a chance this was due to trying to connect before the database was ready, if so restarting the program should fix it.")
		exit(310)
	}

	db := DatabaseConnection{connection: connection}

	println("[Database] Connected to database. ($host:$port)")
	
	println("[Database] Creating tables...")
	db.init_tables()
	return db
}

pub fn (db DatabaseConnection) init_tables() {
	println("[Database] Creating http reference table...")
	sql db.connection {
		create table Http_Reference
	}
	println("[Database] Creating message table...")
	sql db.connection {
		create table Message_Table
	}
	println("[Database] Tables created.\n")
}

References Refactor

References refactor file

This is very similar to the original references file, but is simply adjusted to using databases rather than the old json method, so I won't go into too much detail on it but I've added it below as it will be tested in the tests at the end of this cycle.

// found at /database/references.v
module database

import time

pub fn (db DatabaseConnection) aware_of(input_domain string) bool {
	println("[Database] Checking if domain $input_domain is in database")
	http_matches := sql db.connection {
		select from Http_Reference where domain == input_domain order by last_connected limit 2
	}

	if http_matches.len > 0  {
		println("[Database] Found a reference to $input_domain")
		return true
	}

	println("[Database] No reference to $input_domain found.")
	return false
}

pub fn (db DatabaseConnection) get_key_http(input_domain string) []u8 {
	ref := sql db.connection {
		select from Http_Reference where domain == input_domain order by last_connected limit 1
	}

	return ref.key.bytes()
}


pub fn (db DatabaseConnection) create_ref(input_domain string, pubkey []u8) bool {
	println("[Database] Checking if a reference to $input_domain already exists")
	if db.aware_of(input_domain) {
		println("[Database] Reference to $input_domain already exists, updating it...")
		db.submit_update(input_domain, pubkey)
		return true
	}

	println("[Database] Creating reference for $input_domain")
	db.submit_create(input_domain, pubkey)
	println("[Database] Reference created successfully")
	return true
}


fn (db DatabaseConnection) submit_create(input_domain string, pubkey []u8)  {
	key_str := pubkey.str()
	println("[Database] Creating http reference for $input_domain")
	ref := Http_Reference{
		domain: input_domain
		key: key_str
	}
	sql db.connection {
		insert ref into Http_Reference
	}
	
}

fn (db DatabaseConnection) submit_update(input_domain string, pubkey []u8)  {
	cur_time := time.utc()
	key_str := pubkey.str()

	println("[Database] Updating http reference for $input_domain")
	sql db.connection {
		update Http_Reference set key = key_str, last_connected = cur_time where domain == input_domain
	}
}

pub fn (db DatabaseConnection) get_refs() []Http_Reference {
	http_refs := sql db.connection {
		select from Http_Reference
	}

	return http_refs
}

Additional Functions

Some basic wrappers used for key functionality

These wrapper functions exist so that if a user wanted to change the node software to use a different database type or module, they would only have to change the code in the database module and wouldn't have to change sql requests in external modules.

// found at /database/queryCreator.v
module database

pub fn (db DatabaseConnection) get_message(parsed_signature string, parsed_sender string, parsed_receiver string, time string, data string) []Message_Table {
	existing_message := sql db.connection {
		select from Message_Table where signature == parsed_signature && sender == parsed_sender && receiver == parsed_receiver && timestamp == time && contents == data limit 3
	}
	return existing_message
}

pub fn (db DatabaseConnection) get_latest_messages(offset_quantity int, num int) []Message_Table {
	println("[Database] Getting latest messages")
	existing_message := sql db.connection {
		select from Message_Table where id > 0 order by timestamp desc limit num offset offset_quantity 
	}
	return existing_message
}

pub fn (db DatabaseConnection) save_message(message Message_Table) {
	sql db.connection {
		insert message into Message_Table
	}
}

Challenges

The main challenge this cycle was to figure out the best way of setting up a database whilst balancing how many additional things the user would have to download with how much setup and usage they would need to do.

After various testing and trialing of different methods that resulted in the use of a docker container to host the database as the setup and management of docker can be handled by the node software and simply requires the user to download docker, although I later realised that the user would also need to install postgreSQL and libpq in order to have all the header files required to run the node. (This can be avoided by compiling directly to binary with all the required files but in most cases this isn't a great idea as most users will need to recompile the software to fit their systems)

This then means that three new requirements are needed to run the software:

• Docker

• PostgreSQL

• libpq (or libpq-dev for linux)

However a benefit to moving to docker is that in the future the entire software could be moved into a docker container, allowing the software to simply run within two docker containers, one for the software and the other for the database, which would prevent users from having to download anything except docker directly to their computer and instead all other downloads would be handled within docker without any user interference.

Testing

All of the tests for this cycle were automated using a basic script I wrote in order to be able to test the whole module quickly and easily.

Test Table

Tests

Test	Instructions	What I expect	What actually happens	Passed?
1	Use the Shell function to echo a message	The message to be echo'ed from the terminal	As Expected
2	Launch the database.	The database to launch and no crashes to occur.	As Expected
3	Stop the database.	The database to stop and no crashes to occur.	As Expected
4	Launch and then connect to the database.	The database to launch then accept the program's connection without any crashes.	As Expected
5	Get the last 5 messages from the database.	An array of messages to be returned in the expected type.	As Expected
6	Create a node reference in the database.	The reference to be created in the database.	As Expected
7	Create a node reference and then check if the software is aware of that node.	The "aware_of" check should return true for the reference test that already exists.	As Expected
8	Collect all references from the database.	Should return an array of node references in the expected type.	As Expected

Testing Code

All the functions in this script that start with "test_" are testing functions that can be run using the vlang test functionality which allows the use of a shell command that looks something like the following: v test ./file_test.v

import database
import pg

// The actual tests:

fn test_shell() {
	cmd := 'echo "hello world"'
	assert database.sh(cmd)

}

fn test_db_launch() {
	database.launch()
	// this test will crash if it fails
	assert true
}

fn test_db_stop() {
	database.launch()
	database.stop()
	// this test will crash if it fails
	assert true
}

fn test_db_connection() {
	database.launch()
	db := database.connect(false, pg.Config{})
	database.stop()
	// this test will crash if it fails
	assert true
}

fn test_messages() {
	eprintln("WARNING - THIS TEST RELIES ON THE DATABASE HAVING ATLEAST 1 MESSAGE, IF IT DOES NOT, IT WILL FAIL")
	database.launch()
	db := database.connect(false, pg.Config{})
	messages := db.get_latest_messages(0, 5)
	database.stop()
	// this test will crash if it fails
	assert messages.len > 0
}

fn test_create_ref() {
	database.launch()
	db := database.connect(false, pg.Config{})
	create_test_ref(db)
	database.stop()
	// this test will crash if it fails
	assert true
}

fn test_aware_ref() {
	database.launch()
	db := database.connect(false, pg.Config{})
	create_test_ref(db)
	aware := db.aware_of('test')
	database.stop()
	assert aware
}

fn test_refs() {
	database.launch()
	db := database.connect(false, pg.Config{})
	create_test_ref(db)	// create a ref if the test one doesn't already exist
	refs := db.get_refs()
	database.stop()
	assert refs.len > 0
}

// This is a function used just to make the other tests easier
fn create_test_ref(db database.DatabaseConnection) {
	mut aware := db.aware_of('test')

	if !aware {
		// just in case the test ref doesn't exist
		db.create_ref('test', 'test'.bytes())
		aware = db.aware_of('test')
	}
}

Evidence

Test 1

Test 2

Test 3

Test 4

Test 5

Test 6

Test 7

Test 8