PLUGIN sqlite
lang: "C++"
author: "Julien BRUGUIER, Julien TALLON, Francois GOASMAT"
date: "2022-08-11"
version: "1.0"
maintainer: "Julien BRUGUIER <projet.svm@pappy.tf>"
synopsis:
%{
Simple Virtual Machine plugin to handle basic sqlite operations.
%}
description:
%{
This plugin manages sqlite databases in read-only or read-write modes.

The plugin does not manage the concurrent write access to the databases and this shall be handled by the application.

The plugin handles prepared queries with binded values.
%}
example: "Simple select"
%{
.nf
#!===SVMBIN===
LOG
PLUGIN "svmcom.so"
PLUGIN "===PLUGINLIB==="
ARGUMENT STR database
PROCESS "select"
	CODE "main" INLINE
		:memory sqlite.database/db, sqlite.query/q, STR/n, PTR/r, INT/a
		:interruption !sqlite.error no_database
		:sqlite.database @&database -> &db
		:interruption !sqlite.error
		:sqlite.query @&db "SELECT age FROM person WHERE name = ?;" -> &q
	:label loop
		:com.prompt "Name" STR -> &n
		:shutdown :unless &n INITIALISED
		:sqlite.init @&q @&n
		:sqlite.exec @&q a -> &r
		:goto no_result :unless &r INITIALISED
		:com.message @&n " is " @(@&r/0) " years old."
		:goto loop
	:label no_result
		:com.message @&n " is not referenced!"
		:goto loop 
	:label no_database
		:com.message STDERR @&database " is not a valid database."
	END
	MEMORY database
END
.fi
%}
checks:
%{
AC_CHECK_HEADERS([sqlite3.h],,[AC_MSG_ERROR([Header file required to compile not available !])])
%}
includes:
%{
#include <iostream>
#include <vector>
#include <map>
#include <memory>
#include <mutex>
#include <sqlite3.h>
%}
link: "-lsqlite3"
initialisation:
%{
	if(::sqlite3_config(SQLITE_CONFIG_SERIALIZED)==SQLITE_ERROR)
	{
		std::cerr << "SQLite library not compiled with multi-threading support." << std::endl
			<< "Your application may crash!" << std::endl;
	}
	::sqlite3_initialize();
%}
finalisation:
%{
	::sqlite3_shutdown();
%}
code:
%{
struct Value
{
	typedef std::shared_ptr<Value> SP;
	virtual int bind(sqlite3_stmt* q, int i) const = 0;
};

struct Integer : public Value
{
	Integer(const long long int i)
	:_i(i) {}
	virtual int bind(sqlite3_stmt* q, int i) const override
	{
		return ::sqlite3_bind_int64(q,i,_i);
	}
	long long int _i;
};

struct String : public Value
{
	String(const std::string& s)
	:_s(s) {}
	virtual int bind(sqlite3_stmt* q, int i) const override
	{
		return ::sqlite3_bind_text(q,i,_s.c_str(),_s.size(),SQLITE_TRANSIENT);
	}
	std::string _s;
};

struct Variables
{
	static std::shared_ptr<Variables>& instance()
	{
		static auto v = std::make_shared<Variables>();
		return v;
	}
	mutable std::mutex _lock;
	std::map<std::string,Value::SP> _variables;
};
%}

test: "create_and_select"
%{
PLUGIN "svmcom.so"
PLUGIN "svmstr.so"
PLUGIN "svmint.so"
PROCESS "test"
	CODE "main" INLINE
		:memory sqlite.database/d, sqlite.query/q, STR/s, PTR/p, BLN/b
		:sqlite.database ":memory:" RW -> &d
		:sqlite.query @&d "create table t(i integer, t text);" -> &q
		:sqlite.init @&q
		:sqlite.exec @&q
		:sqlite.query @&d "insert into t values (17, 'text');" -> &q
		:sqlite.init @&q
		:sqlite.exec @&q
		:sqlite.query @&d "select * from t where i = ?;" -> &q
		:sqlite.init @&q 17
		:sqlite.exec @&q -> &p
		:str.cmp @(@&p/1) = "text" -> &b
		:shutdown 1 :unless @&b TRUE
		:sqlite.query @&d "select * from t where t = ?;" -> &q
		:sqlite.init @&q "text"
		:sqlite.exec @&q -> &p
		:int.cmp @(@&p/0) = 17 -> &b
		:shutdown 1 :unless @&b TRUE
		:sqlite.query @&d "drop table t;" -> &q
		:sqlite.init @&q 
		:sqlite.exec @&q
	END
END
%}

test: "bindings_and_variables"
%{
PLUGIN "svmcom.so"
PLUGIN "svmstr.so"
PROCESS "test"
	CODE "main" INLINE
		:memory sqlite.database/d, sqlite.query/q, STR/s, PTR/p, BLN/b
		:sqlite.database ":memory:" RW -> &d
		:sqlite.query @&d "create table t(i integer, t text);" -> &q
		:sqlite.init @&q
		:sqlite.exec @&q
		:sqlite.query @&d "insert into t values (17, 'text');" -> &q
		:sqlite.init @&q
		:sqlite.exec @&q
		:sqlite.query @&d "insert into t values (18, 'again');" -> &q
		:sqlite.init @&q
		:sqlite.exec @&q
		:sqlite.var "var" 18
		:sqlite.query @&d "select * from t where i = $var;" -> &q
		:sqlite.init @&q
		:sqlite.exec @&q -> &p
		:str.cmp @(@&p/1) = "again" -> &b
		:shutdown 1 :unless @&b TRUE
		:sqlite.query @&d "select * from t where i = ?;" -> &q
		:sqlite.init @&q 17
		:sqlite.exec @&q -> &p
		:str.cmp @(@&p/1) = "text" -> &b
		:shutdown 1 :unless @&b TRUE
		:sqlite.query @&d "select * from t where i = :var;" -> &q
		:sqlite.init @&q VAR = 17
		:sqlite.exec @&q -> &p
		:str.cmp @(@&p/1) = "text" -> &b
		:shutdown 1 :unless @&b TRUE
		:sqlite.query @&d "drop table t;" -> &q
		:sqlite.init @&q 
		:sqlite.exec @&q
	END
END
%}

test: "emplace_result"
%{
PLUGIN "svmcom.so"
PLUGIN "svmstr.so"
PROCESS "test"
	CODE "main" INLINE
		:memory sqlite.database/d, sqlite.query/q, STR/s, PTR/p, BLN/b, (INT, STR)/r
		:sqlite.database ":memory:" RW -> &d
		:sqlite.query @&d "create table t(i integer, t text);" -> &q
		:sqlite.init @&q
		:sqlite.exec @&q
		:sqlite.query @&d "insert into t values (17, 'text');" -> &q
		:sqlite.init @&q
		:sqlite.exec @&q
		:sqlite.query @&d "insert into t values (18, null);" -> &q
		:sqlite.init @&q
		:sqlite.exec @&q
		:sqlite.query @&d "select * from t where i = ?;" -> &q
		:sqlite.init @&q 17
		:sqlite.exec @&q r -> &p
		:shutdown 1 :unless &p INITIALISED
		:str.cmp @(@&p/1) = "text" -> &b
		:shutdown 1 :unless @&b TRUE
		:sqlite.init @&q 18
		:sqlite.exec @&q r -> &p
		:shutdown 1 :unless &p INITIALISED
		:shutdown 1 :when (@&p/1) INITIALISED
		:sqlite.init @&q 19
		:sqlite.exec @&q r -> &p
		:shutdown 1 :when &p INITIALISED
	END
END
%}

DEFINE

INTERRUPTION sqlite.error
help:
%{
This interruption is raised when the sqlite library returns an error code.
%}

INTERRUPTION sqlite.busy
help:
%{
This interruption is raised when concurrent write access is attempted on a database file.
%}

TYPE sqlite.database
%{
	type_database(const std::string& file, const bool ro)
	:_file(file), _ro(ro),_db(nullptr) {}
	~type_database()
	{
		if(_db)
		{
			::sqlite3_close_v2(_db);
		}
	}
	operator std::string () const
	{
		return _file+" "+(_ro?"RO":"RW")+" ("+(_db?"open":"clos")+"ed)";
	}
	std::string _file;
	bool _ro;
	sqlite3* _db;
%}
delete default: %{}
print default: %{}
help:
%{
This type represents a connection to a database.
%}

TYPE sqlite.query
%{
	type_query()
	:_q(nullptr) {}
	~type_query()
	{
		if(_q)
		{
			::sqlite3_finalize(_q);
		}
	}
	operator std::string () const
	{
		if(_q)
		{
			return ::sqlite3_sql(_q);
		}
		return "<no query>";
	}
	sqlite3_stmt *_q;
%}
delete default: %{}
print default: %{}
help:
%{
This type represents a prepared SQL query on a specific database.
%}

SYSTEM INSTRUCTION sqlite.database STR:file ['RO' 'RW']:mode -> sqlite.database
%{
	SVM_String raw_file = ARGV_VALUE(0,string);
	auto mode = ARGV_KEYWORD(1);
	std::string file(raw_file.string,raw_file.size);
	bool ro = mode =="RO";
	auto db = new type_database(file,ro);
	int rc = ::sqlite3_open_v2(raw_file.string,&db->_db,ro?SQLITE_OPEN_READONLY:(SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE),nullptr);
	if(rc == SQLITE_OK)
	{
		return NEW_PLUGIN(sqlite,database,db);
	}
	delete db;
	std::string err = ::sqlite3_errstr(rc);
	ERROR_EXTERNAL(sqlite,error,err.c_str());
	return nullptr;
%}
help:
%{
This instruction opens a connection to an sqlite database.
The database name should follow the sqlite specifications.
.P
When a file name is provided, this file shall exist when the database is opened in read-only mode (RO).
A sqlite.error interruption is raised when the file does not exist.
When the database is opened in read-write mode (RW), the file is created when it does not exist.
.P
The database remains opened until the returned value is destroyed.
%}

INSTRUCTION sqlite.query sqlite.database STR:query -> sqlite.query
%{
	auto db = ARGV_PLUGIN(0,sqlite,database);
	auto query = ARGV_VALUE(1,string);
	auto q = new type_query();
	int rc = ::sqlite3_prepare_v2(db->_db,query.string,query.size,&q->_q,nullptr);
	if(rc==SQLITE_OK)
	{
		return NEW_PLUGIN(sqlite,query,q);
	}
	delete q;
	std::string err = ::sqlite3_errstr(rc);
	ERROR_EXTERNAL(sqlite,error,err.c_str());
	return nullptr;
%}
help:
%{
This instruction compiles a SQL query for an opened database.
.P
The query can be used several times. It is called a prepared SQL statement.
.P
When the query text is invalid, a sqlite.error interruption is raised.
%}

INSTRUCTION sqlite.init MUTABLE sqlite.query ( VALUE | KEYWORD = VALUE )*:parameters
%{
	auto q = ARGV_PLUGIN(0,sqlite,query);
	::sqlite3_reset(q->_q);
	::sqlite3_clear_bindings(q->_q);
	SVM_Size global_index = 1;
	for(SVM_Size i = 1 ; i<argc ; ++i)
	{
		SVM_Size index = global_index;
		if(::svm_parameter_type_is_keyword(svm,argv[i]))
		{
			auto n = ::svm_parameter_keyword_get(svm,argv[i]);
			std::string nn = ":";
			for(size_t ii=0 ; ii<n.size ; ++ii)
			{
				if((n.string[ii]>='A') and (n.string[ii]<='Z'))
				{
					nn += static_cast<char>(n.string[ii]-'A'+'a');
				}
				else
				{
					nn += n.string[ii];
				}
			}
			index = ::sqlite3_bind_parameter_index(q->_q,nn.c_str());
			if(index==0)
			{
				ERROR_EXTERNAL(sqlite,error,"Invalid binding name.");
			}
			i += 2;
		}
		else
		{
			++global_index;
		}
		SVM_Value v = ::svm_parameter_value_get(svm,argv[i]);
		int rc = 0;
		if(::svm_value_state_is_null(svm,v))
		{
			rc = ::sqlite3_bind_null(q->_q,index);
		}
		else if(::svm_value_type_is_integer(svm,v))
		{
			auto vv = ::svm_value_integer_get(svm,v);
			rc = ::sqlite3_bind_int64(q->_q,index,vv);
		}
		else if(::svm_value_type_is_string(svm,v))
		{
			auto vv = ::svm_value_string_get(svm,v);
			rc = ::sqlite3_bind_text(q->_q,index,vv.string,vv.size,SQLITE_TRANSIENT);
		}
		else
		{
			SVM_String vv = ::svm_value_print(svm,v);
			rc = ::sqlite3_bind_text(q->_q,index,vv.string,vv.size,SQLITE_TRANSIENT);
		}
		if(rc!=SQLITE_OK)
		{
			std::string err = ::sqlite3_errstr(rc);
			ERROR_EXTERNAL(sqlite,error,err.c_str());
		}
	}
	auto vars = Variables::instance();
	std::lock_guard<std::mutex> protect(vars->_lock);
	for(const auto& v: vars->_variables)
	{
		std::string name("$");
		name += v.first;
		int index = ::sqlite3_bind_parameter_index(q->_q,name.c_str());
		if(index==0) continue;
		v.second->bind(q->_q,index);
	}
%}
help:
%{
This instruction initiates a query execution, taking arguments for bindings.
.P
Anonymous bindings are supported and uses positional values.
Named bindings are supported. They are specified by the alternative with a keyword.
The named binding shall start with a colon and be in small letters within the query text.
Variable bindings are supported.
The values are not passed as arguments of this instruction, but defined by the sqlite.var instruction.
The variable binding shall start with a dollar sign and be in small letters within the query text.
.P
When the binding is invalid, or a named binding does not exist into the query text, a sqlite.error interruption is raised.
%}

INSTRUCTION sqlite.exec MUTABLE sqlite.query PTR ? -> PTR ?
%{
	auto q = ARGV_PLUGIN(0,sqlite,query);
	int rc = ::sqlite3_step(q->_q);
	switch(rc)
	{
		case SQLITE_ROW:
			{
				int count = ::sqlite3_column_count(q->_q);
				std::vector<SVM_Value> values;
				SVM_Memory_Zone zone = ::svm_memory_zone_new(svm);
				for(size_t i=0 ; i<count ; ++i)
				{
					int t = ::sqlite3_column_type(q->_q,i);
					if(t==SQLITE_INTEGER)
					{
						::svm_memory_zone_append_internal__raw(svm,zone,INTEGER,1);
						auto v = ::sqlite3_column_int64(q->_q,i);
						values.push_back(::svm_value_integer_new(svm,v));
					}
					else if(t==SQLITE_NULL)
					{
						::svm_memory_zone_append_internal__raw(svm,zone,AUTOMATIC,1);
						values.push_back(nullptr);
					}
					else
					{
						::svm_memory_zone_append_internal__raw(svm,zone,STRING,1);
						auto v = ::sqlite3_column_text(q->_q,i);
						values.push_back(::svm_value_string_new__raw(svm,reinterpret_cast<const char*>(v)));
					}
				}
				SVM_Value_Pointer pointer;
				if(argc==1)
				{
					pointer = ::svm_memory_allocate(svm,CURRENT(kernel),zone);
				}
				else
				{
					pointer = ::svm_parameter_value_get(svm,argv[1]);
					if(values.size()!=::svm_value_pointer_get_size(svm,pointer))
					{
						ERROR_INTERNAL(MEMORY,"Pointer size mismatch");
					}
				}
				SVM_Address a = ::svm_value_pointer_get_address(svm,pointer);
				for(const auto& v: values)
				{
					if(v)
					{
						::svm_memory_write_address(svm,CURRENT(kernel),a,v);
					}
					else
					{
						switch(::svm_memory_address_get_type(svm,CURRENT(kernel),a))
						{
							case INTEGER:
								::svm_memory_write_address(svm,CURRENT(kernel),a,::svm_value_integer_new_null(svm));
								break;
							case STRING:
								::svm_memory_write_address(svm,CURRENT(kernel),a,::svm_value_string_new_null(svm));
								break;
							case AUTOMATIC:
								break;
							default:
								ERROR_INTERNAL(MEMORY,"Invalid type for null value: Only INT, STR and AUTO allowed.");
								break;
						}
					}
					++a;
				}
				return pointer;
			}
			break;
		case SQLITE_DONE:
			{
				return NEW_NULL_VALUE(pointer);
			}
			break;
		case SQLITE_BUSY:
		case SQLITE_LOCKED:
			{
				std::string err = ::sqlite3_errstr(rc);
				ERROR_EXTERNAL(sqlite,busy,err.c_str());
			}
			break;
		default:
			{
				std::string err = ::sqlite3_errstr(rc);
				ERROR_EXTERNAL(sqlite,error,err.c_str());
			}
			break;
	}
	return nullptr;
%}
help:
%{
This instruction runs the query and returns one row whenever possible.
.P
When no data is returned, a null pointer is returned.
Otherwise a pointer indicating the row values is returned.
.P
When no extra pointer is provided as parameter, the row is instanciated as local memory.
In the row values, a null value is represented by a null AUTO value.
An integer is represented by an initialised INT value.
Other values are represented by an initialised STR value.
.P
When an extra pointer is provided as parameter, the corresponding memory region is used to store the row.
The size of the region has to be the same as the returned row, or a MEMORY interruption is raised.
If a type mismatches, a MEMORY interruption is raised.
A null value will be represented by a null value in memory.
.P
If a concurrent access to the database file is attempted, a sqlite.busy interruption is raised.
%}

INSTRUCTION sqlite.var STR : name VALUE ? : value
%{
	auto raw_name = ARGV_VALUE(0,string);
	std::string name(raw_name.string,raw_name.size);
	auto vars = Variables::instance();
	std::lock_guard<std::mutex> protect(vars->_lock);
	auto it = vars->_variables.find(name);
	if(argc==1)
	{
		if(it==vars->_variables.end()) return nullptr;
		vars->_variables.erase(it);
		return nullptr;
	}
	Value::SP value;
	SVM_Value v = ::svm_parameter_value_get(svm,argv[1]);
	if(::svm_value_type_is_integer(svm,v))
	{
		value = std::make_shared<Integer>(::svm_value_integer_get(svm,v));
	} 
	else if(::svm_value_type_is_string(svm,v))
	{
		SVM_String s = ::svm_value_string_get(svm,v);
		value = std::make_shared<String>(std::string(s.string,s.size));
	}
	else
	{
		SVM_String s = ::svm_value_print(svm,v);
		value = std::make_shared<String>(std::string(s.string,s.size));
	}
	if(it==vars->_variables.end())
	{
		vars->_variables.insert(std::make_pair(name,value));
	}
	else
	{
		it->second = value;
	}
%}
help:
%{
This instruction sets or resets a variable.
Variables are values accessibles from every query, regardless the process where it is run.
.P
When the value is not specified, the variable is deleted.
Otherwise, the variable is created or updated.
%}