⍝ Databases Overview DEMO

⍝ ===== LOAD CSV =====
(customers customers_cols)←⎕CSV'CSV/customers.csv' ⍬ 4 1
(retailers retailers_cols)←⎕CSV'CSV/retailers.csv' ⍬ 4 1
(products products_cols)←⎕CSV'CSV/products.csv' ⍬ 4 1
(purchase purchase_cols)←⎕CSV'CSV/purchase.csv' ⍬ 4 1

]box on -fns=on
]rows -fold=3

⎕←{⍵↑⍨5⌊≢⍵}¨customers products retailers
⎕←5↑purchase

⍝ ===== NESTED MATRIX DSL =====

'In' 'Of' 'Where'⎕CY'demo'

]defs

⎕←'customers' Where 'Ugly' In 'address' Of 'customers'
⎕←'Ugly'⎕R'Stinky'¨ 'customers' Where 'Ugly' In 'address' Of 'customers'

⍝ Through regularity, we can create our own query language
⍝ e.g. Primary key always in first column, first row is header

⍝ ===== INVERTED TABLE =====

i_purchase ← ↓⍉purchase
⎕←3↑¨i_purchase

fi_purchase←↑¨i_purchase
⎕←3↑¨fi_purchase

⍝ ===== SQAPL =====
⎕CY'loaddata'

]names 3 -filter=*SQL

⎕CY'sqapl'
]names SQA

SQA.Connect'MD' 'MariaDB' 'apl' 'root'
SQA.Do'MD' 'show databases'
SQA.Do'MD' 'use retail'

'create_purchase_table'⎕CY'demo'
⎕←create_purchase_table
SQA.Do'MD' create_purchase_table

SaveSQL purchase ('MD') ('purchase'('tid' 'pcode' 'cid' 'rid' 'price' 'qty' 'datetime')) ('overwrite')
⎕←5↑p←LoadSQL ('MD') 'purchase' ('tid' 'pcode' 'cid' 'rid' 'price' 'qty' 'datetime')
p≡⍥(⊂∘⍋⌷⊢)purchase

SQA.Do'MD' 'show fields in purchase'
⎕←SQA.Do'MD' 'select * from purchase limit 5'

SQA.Close'MD'