github.com/voedger/voedger@v0.0.0-20240520144910-273e84102129/design/schemas/progress/air2-before-meeting-20032023.vsql (about) 1 -- Copyright (c) 2020-present unTill Pro, Ltd. 2 3 SCHEMA air; 4 5 IMPORT SCHEMA github.com/untillpro/airs-bp3/packages/untill 6 7 -- Principles: 8 ---- 1. The following DDLs can only be declared in WORKSPACE: 9 ---- QUERY, COMMAND, PROJECTOR, RATE, GRANT, USE TABLE 10 ---- 2. The following DDLs can only be declared out of WORKSPACE: 11 ---- TEMPLATE 12 13 WORKSPACE Restaurant ( 14 15 ------------------------------------------------------------------------------------- 16 -- Roles 17 -- 18 ROLE UntillPaymentsUser; 19 ROLE LocationManager; 20 ROLE LocationUser; 21 22 ------------------------------------------------------------------------------------- 23 -- Checks 24 -- 25 FUNCTION MyTableValidator(sys.TableRow) RETURNS void ENGINE BUILTIN; 26 FUNCTION MyTableValidator RETURNS void ENGINE BUILTIN; -- parameters may be omitted 27 FUNCTION MyFieldsValidator(fieldA text, fieldB text) RETURNS void ENGINE BUILTIN; 28 PROCEDURE MyFieldsValidator ENGINE BUILTIN; -- same as previous 29 FUNCTION ApproxEqual(param1 float, param2 float) RETURNS boolean ENGINE BUILTIN; 30 31 CHECK ON TABLE untill.bill IS MyTableValidator; 32 CHECK ON TABLE untill.bill AS PROCEDURE MyTableValidator(sys.TableRow) ENGINE BUILTIN; 33 CHECK MyBillCheck ON TABLE untill.bill(name text, pcname text) IS MyFieldsValidator; -- name is optional 34 CHECK ON TABLE untill.bill(name text, pcname text) AS FUNCTION MyFieldsValidator(text, text) RETURNS void ENGINE BUILTIN; 35 CHECK ON TABLE untill.bill(name text, pcname text) AS PROCEDURE MyFieldsValidator ENGINE BUILTIN; -- same as previous 36 CHECK ON TABLE untill.bill(name text, pcname text) AS (text != pcname); 37 CHECK ON FIELD name OF TABLE untill.bill AS (name != '') 38 CHECK ON FIELD working_day OF TABLE untill.bill AS '^[0-9]{8}$' 39 CHECK NettoBruttoCheck ON TABLE sometable(netto float, brutto float) AS (!ApproxEqual(netto, brutto)); 40 ------------------------------------------------------------------------------------- 41 -- Projectors 42 -- 43 FUNCTION FillUPProfile(sys.Event) RETURNS void ENGINE WASM; 44 PROCEDURE FillUPProfile(sys.Event) ENGINE WASM; -- same as previous 45 46 PROJECTOR ApplyUPProfile ON COMMAND IN (air.CreateUPProfile, air.UpdateUPProfile) IS FillUPProfile; -- name is optional 47 PROJECTOR ON COMMAND air.CreateUPProfile AS FUNCTION FillUPProfile(sys.Event) RETURNS void ENGINE WASM; 48 PROJECTOR ON COMMAND air.CreateUPProfile AS PROCEDURE FillUPProfile(sys.Event) ENGINE WASM; -- same as previous 49 PROJECTOR ON COMMAND ARGUMENT untill.QNameOrders AS PROCEDURE OrdersDatesProjector(sys.Event) ENGINE BUILTIN; 50 51 52 ------------------------------------------------------------------------------------- 53 -- Commands 54 -- 55 PROCEDURE OrdersFunc(untill.orders) ENGINE BUILTIN; 56 FUNCTION PbillFunc(untill.pbill) RETURNS PbillResult ENGINE BUILTIN; 57 58 COMMAND Orders(untill.orders) IS PbillFunc; 59 COMMAND Pbill(untill.pbill) IS PbillFunc; 60 COMMAND LinkDeviceToRestaurant(LinkDeviceToRestaurantParams) RETURNS void AS PROCEDURE MyFunc(LinkDeviceToRestaurantParams) ENGINE WASM; 61 62 ------------------------------------------------------------------------------------- 63 -- Comments 64 -- 65 STRING BackofficeComment AS "This is a backoffice table"; 66 67 COMMENT ON QUERY TransactionHistory AS 'Transaction History'; 68 COMMENT ON QUERY IN (TransactionHistory, ...) AS 'Transaction History'; 69 COMMENT ON ALL QUERIES WITH TAG Backoffice IS BackofficeComment; 70 71 -- ??? optional name 72 COMMENT BackofficeQueriesComment ON ALL QUERIES WITH TAG Backoffice IS BackofficeComment; 73 74 TYPE QueryResellerInfoResult ( 75 reseller_phone text, 76 reseller_company text, 77 reseller_email text, 78 reseller_website text 79 ) WITH Comment AS 'Contains information about Reseller'; 80 81 ------------------------------------------------------------------------------------- 82 -- Rates and Limits 83 -- 84 85 -- "Limit defines the maximum frequency of some events. 86 -- Limit is represented as number of events per second." 87 -- https://pkg.go.dev/golang.org/x/time/rate 88 89 LIMIT BackofficeFuncLimit AS 100 PER MINUTE PER IP; 90 91 RATE ON QUERY TransactionHistory IS BackofficeFuncLimit; 92 RATE ON QUERY TransactionHistory AS 101 PER MINUTE PER IP; 93 94 -- ??? optional name 95 RATE TransactionHistoryRate ON QUERY TransactionHistory AS 101 PER MINUTE PER IP; 96 97 ------------------------------------------------------------------------------------- 98 -- Tags 99 -- 100 101 STRING BackofficeTag AS "Backoffice"; 102 STRING PosTag AS "Pos"; 103 STRING CollectionTag AS "Collection"; 104 105 TAG ON TABLE bill IS PosTag; 106 TAG ON COMMAND Orders IS PosTag; 107 TAG ON QUERY QueryResellerInfo AS "Resellers"; 108 109 -- Collection is applied to all tables with tag "sys.Collection" 110 TAG ON ALL TABLES WITH TAG "Backoffice" AS "sys.Collection"; 111 TAG ON ALL TABLES WITH TAG BackofficeTag AS "sys.Collection"; --same as previous 112 TAG ON ALL TABLES WITH TAG BackofficeTag IS CollectionTag; --same as previous 113 114 -- ??? optional name 115 TAG AllBackofficeTablesHaveCollection ON ALL TABLES WITH TAG BackofficeTag IS CollectionTag; 116 117 ------------------------------------------------------------------------------------- 118 -- Sequences 119 -- 120 121 SEQUENCE bill_numbers AS int START WITH 1; 122 SEQUENCE bill_numbers AS int MINVALUE 1; -- same as previous 123 SEQUENCE SomeDecrementSeqneuce AS int MAXVALUE 1000000 INCREMENT BY -1; 124 125 ------------------------------------------------------------------------------------- 126 -- Types 127 -- 128 129 TYPE TransactionHistoryParams ( 130 BillIDs text NOT NULL, 131 EventTypes text NOT NULL, 132 ); 133 134 TYPE TransactionHistoryResult ( 135 Offset offset NOT NULL, 136 EventType int64 NOT NULL, 137 Event text NOT NULL, 138 ); 139 140 ------------------------------------------------------------------------------------- 141 -- Queries 142 -- 143 144 FUNCTION MyFunc(reseller_id text) RETURNS QueryResellerInfoResult ENGINE WASM; 145 146 QUERY QueryResellerInfo(reseller_id text) RETURNS QueryResellerInfoResult IS MyFunc 147 WITH Rate IS BackofficeFuncRate 148 AND Comment AS 'Transaction History'; 149 150 -- same as: 151 QUERY TransactionHistory(TransactionHistoryParams) AS 152 FUNCTION MyFunc(TransactionHistoryParams) RETURNS TransactionHistoryResult[] ENGINE WASM 153 WITH Rate AS PosRate 154 AND Comment IS PosComment 155 AND Tag IS PosTag; 156 157 158 ------------------------------------------------------------------------------------- 159 -- Tables 160 -- 161 162 -- Every workspace Restaurant has all tables from schema `untill` 163 USE TABLE untill.*; 164 165 -- ??? Do we need to USE something else besides TABLEs? 166 167 TABLE air_table_plan OF CDOC ( 168 fstate int, 169 name text, 170 ml_name text, 171 num int, 172 width int, 173 height int 174 ) 175 176 -- see also: untill-tables.vsql 177 178 ------------------------------------------------------------------------------------- 179 -- ACLs 180 -- 181 GRANT ALL ON ALL TABLES WITH TAG untill.Backoffice TO LocationManager; 182 GRANT INSERT,UPDATE ON ALL TABLES WITH TAG sys.ODoc TO LocationUser; 183 GRANT SELECT ON TABLE untill.orders TO LocationUser; 184 GRANT EXECUTE ON COMMAND PBill TO LocationUser; 185 GRANT EXECUTE ON COMMAND Orders TO LocationUser; 186 GRANT EXECUTE ON QUERY TransactionHistory TO LocationUser; 187 GRANT EXECUTE ON ALL QUERIES WITH TAG PosTag TO LocationUser; 188 189 ------------------------------------------------------------------------------------- 190 -- Singletones 191 -- 192 TABLE Restaurant OF SINGLETONE ( 193 WorkStartTime text, 194 DefaultCurrency int64, 195 NextCourseTicketLayout int64, 196 TransferTicketLayout int64, 197 DisplayName text, 198 Country text, 199 City text, 200 ZipCode text, 201 Address text, 202 PhoneNumber text, 203 VATNumber text, 204 ChamberOfCommerce text, 205 ) 206 207 ------------------------------------------------------------------------------------- 208 -- Views 209 -- 210 VIEW HourlySalesView( 211 yyyymmdd text, 212 hour int, 213 total int, 214 count int, 215 primary key((yyyymmdd, hour)) 216 ) AS SELECT 217 working_day as yyyymmdd, 218 EXTRACT(hour from ord_datetime) as hour, 219 SUM(price * quantity) as total, 220 SUM(quantity) as count 221 from untill.orders 222 join order_item on order_item.id_orders=orders.id 223 group by working_day, hour 224 WITH Comment IS PosComment; 225 226 VIEW XZReports( 227 Year int32, 228 Month int32, 229 Day int32, 230 Kind int32, 231 Number int32, 232 XZReportWDocID id, 233 PRIMARY KEY((Year), Month, Day, Kind, Number) 234 ) AS RESULT OF UpdateXZReportsView 235 236 -- see also air-views.vsql 237 238 ) 239 240 ------------------------------------------------------------------------------------- 241 -- Child Workspaces 242 -- 243 WORKSPACE Resellers { 244 245 ROLE ResellersAdmin; 246 247 -- Child workspace 248 WORKSPACE Reseller { 249 ROLE UntillPaymentsReseller; 250 ROLE AirReseller; 251 USE Table PaymentsProfile 252 } 253 } 254 255 ------------------------------------------------------------------------------------- 256 -- WORKSPACE Templates 257 -- 258 TEMPLATE demo OF WORKSPACE air.Restaurant WITH SOURCE wsTemplate_demo; 259 TEMPLATE resdemo OF WORKSPACE untill.Resellers WITH SOURCE wsTemplate_demo_resellers; 260