⍝ 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'