Absolutely basic SQL database

kredenc.red

Red[]

kredenc!: context [
	storage: #()

	init: does [
		self/storage: copy #()
	]


	table: col: cols: var: vars: order: none
	get-table: [set table word!]
	insert-rule: [
	; -- add row
		'insert 'into get-table
		set cols block!
		'values
		set vals block! (
			; init table
			unless storage/:table [
				storage/:table: copy #()
				foreach col cols [
					storage/:table/:col: copy []
				]
			]
			; add values
				forall cols [
					col: first cols
					append storage/:table/:col pick vals index? cols
				]
		)
	]
	update-rule: [
		'update get-table
		'set
		set col word! ; TODO: add string! too?
		'= ; TODO: more comparators
		set val skip
		'where
		set cols word! ; TODO: add string! too?
		'= ; TODO: more comparators
		set vals skip (
			; TODO: error handling
			mark: index? find storage/:table/:cols vals
			change at storage/:table/:col mark val
		)
	]
	delete-rule: [
		'delete 'from get-table
		'where
		set col word!
		'=
		set val skip (
			mark: index? find storage/:table/:col val
			foreach col words-of storage/:table [
				col: storage/:table/:col
				remove at col mark
			]
		)
	]
	select-rule: [
		'select
		'* ; TODO: more select types
		'from get-table
		'where
		; TODO: more predicates
		set col word!
		set predicate skip
		set val skip
		opt [
			'order 'by
			set order word!
		] (
			; init result table
			result: copy #()
			foreach c words-of storage/:table [result/:c: copy []]
			; prepare values
			first-col: first words-of storage/:table
			first-col: storage/:table/:first-col
			; go thru all values and keep those fitting the predicate
			repeat i length? first-col [
				if do reduce [storage/:table/:col/:i predicate val][
					foreach c words-of storage/:table [
						append result/:c storage/:table/:c/:i
					]
				]
			]
			; return result
			print mold result
		)
	]
	query: func [
		dialect
	][
		parse dialect [
			insert-rule
		|	update-rule
		|	delete-rule
		|	select-rule
		]
	]
]

test: does [
	k: make kredenc! []
	k/init
	k/query [insert into example [c1 c2 c3] values ["john" 1940 guitar]]
	k/query [insert into example [c1 c2 c3] values ["paul" 1942 guitar]]
	k/query [insert into example [c1 c2 c3] values ["stuart" 1940 bass-guitar]]
	k/query [delete from example where c1 = "stuart"]
	k/query [update example set c3 = bass-guitar where c1 = "paul"]
	k/query [select * from example where c2 > 1940]
	print mold k/storage
]