|
1 | 1 | CREATESCHEMAIF NOT EXISTS pgml_rust; |
2 | 2 |
|
| 3 | +--- |
| 4 | +--- Track of updates to data |
| 5 | +--- |
| 6 | +CREATE OR REPLACEFUNCTIONpgml_rust.auto_updated_at(tbl regclass) |
| 7 | +RETURNS VOID |
| 8 | +AS $$ |
| 9 | + DECLARE name_partsTEXT[]; |
| 10 | + DECLARE nameTEXT; |
| 11 | +BEGIN |
| 12 | + name_parts := string_to_array(tbl::TEXT,'.'); |
| 13 | + name := name_parts[array_upper(name_parts,1)]; |
| 14 | + |
| 15 | + EXECUTE format('DROP TRIGGER IF EXISTS %s_auto_updated_at ON %s', name, tbl); |
| 16 | + EXECUTE format('CREATE TRIGGER %s_auto_updated_at BEFORE UPDATE ON %s |
| 17 | + FOR EACH ROW EXECUTE PROCEDURE pgml_rust.set_updated_at()', name, tbl); |
| 18 | +END; |
| 19 | +$$ |
| 20 | +LANGUAGE plpgsql; |
| 21 | + |
| 22 | + |
| 23 | +--- |
| 24 | +--- Called via trigger whenever a row changes |
| 25 | +--- |
| 26 | +CREATE OR REPLACEFUNCTIONpgml_rust.set_updated_at() |
| 27 | +RETURNS TRIGGER |
| 28 | +AS $$ |
| 29 | +BEGIN |
| 30 | + IF ( |
| 31 | + NEW IS DISTINCTFROM OLD |
| 32 | +ANDNEW.updated_at IS NOT DISTINCTFROMOLD.updated_at |
| 33 | + ) THEN |
| 34 | +NEW.updated_at := clock_timestamp(); |
| 35 | + END IF; |
| 36 | + RETURN NEW; |
| 37 | +END; |
| 38 | +$$ |
| 39 | +LANGUAGE plpgsql; |
| 40 | + |
| 41 | +--- |
| 42 | +--- Projects organize work |
| 43 | +--- |
| 44 | +CREATETABLEIF NOT EXISTSpgml_rust.projects( |
| 45 | +idBIGSERIALPRIMARY KEY, |
| 46 | +nameTEXTNOT NULL UNIQUE, |
| 47 | +taskTEXTNOT NULL, |
| 48 | +created_atTIMESTAMP WITHOUT TIME ZONENOT NULL DEFAULT clock_timestamp(), |
| 49 | +updated_atTIMESTAMP WITHOUT TIME ZONENOT NULL DEFAULT clock_timestamp() |
| 50 | +); |
| 51 | +SELECTpgml_rust.auto_updated_at('pgml_rust.projects'); |
| 52 | + |
| 53 | + |
3 | 54 | CREATETABLEIF NOT EXISTSpgml_rust.models ( |
4 | 55 | idBIGSERIALPRIMARY KEY, |
| 56 | +project_idBIGINTNOT NULLREFERENCESpgml_rust.projects(id), |
5 | 57 | algorithmVARCHAR, |
6 | 58 | dataBYTEA |
7 | 59 | ); |
| 60 | + |
| 61 | +--- |
| 62 | +--- Deployments determine which model is live |
| 63 | +--- |
| 64 | +CREATETABLEIF NOT EXISTSpgml_rust.deployments( |
| 65 | +idBIGSERIALPRIMARY KEY, |
| 66 | +project_idBIGINTNOT NULL, |
| 67 | +model_idBIGINTNOT NULL, |
| 68 | +strategyTEXTNOT NULL, |
| 69 | +created_atTIMESTAMP WITHOUT TIME ZONENOT NULL DEFAULT clock_timestamp(), |
| 70 | +CONSTRAINT project_id_fkFOREIGN KEY(project_id)REFERENCESpgml_rust.projects(id), |
| 71 | +CONSTRAINT model_id_fkFOREIGN KEY(model_id)REFERENCESpgml_rust.models(id) |
| 72 | +); |
| 73 | +CREATEINDEXIF NOT EXISTS deployments_project_id_created_at_idxONpgml_rust.deployments(project_id); |
| 74 | +CREATEINDEXIF NOT EXISTS deployments_model_id_created_at_idxONpgml_rust.deployments(model_id); |
| 75 | +SELECTpgml_rust.auto_updated_at('pgml_rust.deployments'); |