#!/usr/bin/python


"""
design pattern
sql trigger may cover all busines logic and game mecanic in an abstracted world.
the front end only need to query view and render them on screen and emit event on user input
with sqlite the front end also need to emit timer

some guideline
#1 business logic is bound to the schema and should be documented along with it
	trigger along with the table it affect. keep trigger to one affected table
	business logic or game mecanic are best documented using code with comment
#2 functional programming allow any transformation
	use view, it transform a table into another. abuse it
	even sprite placement into a grid may be a view
#3 cant help but having procedure, do it wisely
	a table may act as a emit queue for a procedure
	this procedure have a trigger instead of insert that act accordingly
#4 logging is not an option
	will be easy with trigger and view
	you need metric, you need trace, you need history
#5 unit testing is not part of delivery package, paradoxly it is as much important
	no need for portability, just use the best framework possible for testing (junit ?)
#6 documentation is part of the delivery package, just put it in the database
	as long as its there no rush to make it accessible, 
	almost any table must have a documentation accessible from the instance
	like a man table with table name and its documentation

some productivity rule
#A avoid altering scheme
	if you do so , keep old one, provide transistion script
	you can easily change a trigger but not the schema
#B avoid trashing
	unless it become a problem

"""

#transSQL emit queue
sql_emit_queue = """
/*

*/
CREATE TABLE IF NOT EXISTS channel (
	id INTEGER PRIMARY KEY,
	name TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS message (
	id INTEGER PRIMARY KEY,
	content TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS event (
	timestamp TEXT,
	channel_id INT NOT NULL,
	message_id INT NOT NULL
);
/*
view will interpret this structure into a more readable way
*/
CREATE VIEW IF NOT EXISTS message_queue AS
	SELECT  event.timestamp AS timestamp,
		channel.name AS channel,
		message.content AS msg
	FROM event
	INNER JOIN message ON event.message_id = message.id
	INNER JOIN channel ON event.channel_id = channel.id
	ORDER BY event.timestamp ASC;
/*
recognised as a business logic that only a pair of (channel,message) will be sent from frontend
*/
CREATE TABLE IF NOT EXISTS emit_queue (
	channel TEXT NOT NULL,
	message TEXT NOT NULL
);

CREATE TRIGGER IF NOT EXISTS emit_queue_trigger_on_insert 
INSTEAD OF INSERT ON emit_queue_table FOR EACH ROW
BEGIN
END;
"""

###############################################################################
"""

transactional SQL
consider all operation as update from one view into another
CREATE TABLE tbl_transSQL AS
	id INT PRIMARY KEY
	select TEXT
	update TEXT

trigger being already a part of the spec. the only realworld operation are the moment and condition a transaction is triggered
by that a simple data maybe inserted from outside and trigger many change . most transaction end up being a view into a table

SQLite dont support anycron job that can make the machine run by itself.
need to implement a cron like functionality where a thread may execute a transaction on given time
thus also propagate to trigger
CREATE TABLE tbl_cronSQL AS
	id INT PRIMAnRY KEY
	minutes INT NULL
	hours INY NULL
	dayofmonth INT NULL
	month INT NULL
	year INT NULL
	dayofweek INT NULL
	fk_id_transSQL INT NOT NULL
CREATE VIEW view_cronSQL AS
SELECT id
       maketimestamp() timestamp
WHERE timestamp >= now()
boom we already have a cron job manager, just need a minutes thread that execute them all from the view

SQLite dont support user input or any random input from the outside
unlike time we cant almost get it in one simple query
CREATE TABLE tbl_event AS
	id INT PRIMARY KEY
	timestamp NOW()
	event TEXT 
just insert "key_up" and the trigger may do the thing

i am still tempted to map a predicate into a tbl event thoug sql trigger already do it.

mysql already implement EVENTS timebased.
all event should go by this table and trigger will propagate into actual processing.
then the viewer will view the result when he'll peek a query

"""