dml_tools/
sql.rs

1use serde::{Deserialize, Serialize};
2use std::collections::HashSet;
3use std::fmt::Debug;
4use std::option::*;
5use linked_hash_map::LinkedHashMap;
6
7// use crate::Processor;
8
9/// Trait for a type that can convert a FieldType to String
10pub trait TypeWriter {
11    fn id(&self) -> &str;
12    fn field_type(&self, field_type:&FieldType) -> String;
13    fn schema(&self, op:&ObjectPath) -> String {
14        if let Some(schema) = &op.schema {
15            format!("{}.{}", schema, op.name)
16        } else {
17            op.name.to_owned()
18        }
19    }
20    fn index_type(&self) -> String { " USING btree".to_string() }
21    fn supports_schemas(&self) -> bool { true }
22    fn supports_permissions(&self) -> bool { true }
23    fn supports_auto_increment(&self) -> bool { true }
24    fn supports_sequences(&self) -> bool { false }
25}
26
27/// Trait for serializing a database object to as String
28// #[typetag::serde(tag = "type")]
29#[typetag::serde(tag = "tag")]
30pub trait DBObject : Debug {
31    fn to_sql(&self, type_writer:&dyn TypeWriter) -> String;
32    fn is_top_level(&self) -> bool { false }
33    fn top_level_to_sql(&self, _type_writer:&dyn TypeWriter, _delayed: &Vec<&Box<& dyn DBObject>>) -> String {
34        if self.is_top_level() {
35            panic!("should not run a non-reimplemented top_level_to_sql()!")
36        }
37        "".to_owned()
38    }
39}
40
41fn default_false() -> bool {
42    false
43}
44
45fn default_true() -> bool {
46    true
47}
48
49fn default_type() -> FieldType {
50    FieldType::Txt
51}
52
53fn is_default_dtype(t:&FieldType) -> bool {
54    *t == default_type()
55}
56
57fn is_default_false(b:&bool) -> bool {
58    *b == false
59}
60fn is_default_true(b:&bool) -> bool {
61    *b == true
62}
63fn is_none(opt:&Option<String>) -> bool {
64    opt.is_none()
65}
66/// Types of table fields
67#[derive(Serialize, Deserialize, Debug, PartialEq, Clone)]
68pub enum FieldType {
69    #[serde(rename = "int")]
70    Int,
71    #[serde(rename = "bigint")]
72    BigInt,
73    #[serde(rename = "text")]
74    Txt,
75    #[serde(rename = "double")]
76    Dbl,
77    #[serde(rename = "bool")]
78    Bool,
79    #[serde(rename = "auto_increment")]
80    AutoInc,
81}
82#[typetag::serde]
83impl DBObject for FieldType {
84    fn to_sql(&self, type_writer:&dyn TypeWriter) -> String {
85        type_writer.field_type(&self)
86    }
87}
88
89/// Attributes for fields
90// #[derive(Default)]
91#[derive(Serialize, Deserialize, Debug, PartialEq, Clone)]
92pub struct FieldAttributes {
93    /// Type of field
94    #[serde(rename = "type", default="default_type")]
95    #[serde(skip_serializing_if = "is_default_dtype")]
96    pub dtype: FieldType,
97    /// Is it a UNIQUE field?
98    #[serde(default="default_false")]
99    #[serde(skip_serializing_if = "is_default_false")]
100    pub unique: bool,
101    /// Can be NULL?
102    #[serde(default="default_true")]
103    #[serde(skip_serializing_if = "is_default_true")]
104    pub empty: bool,
105    /// Is it a roster?
106    #[serde(default="default_false")]
107    #[serde(skip_serializing_if = "is_default_false")]
108    pub roster: bool,
109    /// Optional default value for thes field
110    #[serde(skip_serializing_if = "is_none")]
111    pub defval: Option<String>,
112    #[serde(default="default_false")]
113    #[serde(skip_serializing_if = "is_default_false")]
114    /// Is it PRIMARY KEY
115    pub primary_key: bool,
116    #[serde(default="default_false")]
117    #[serde(skip_serializing_if = "is_default_false")]
118    /// Is it INDEXed?
119    pub index: bool,
120    /// Should not be used for De/Serialization?
121    #[serde(default="default_false")]
122    #[serde(skip_serializing_if = "is_default_false")]
123    pub only_db: bool,
124    /// Optional name for this field
125    // name when searching in InterData
126    #[serde(skip_serializing_if = "is_none")]
127    pub meta_name: Option<String>,
128}
129impl FieldAttributes {
130    fn new_default(dt:FieldType) -> Self {
131        FieldAttributes{
132            dtype: dt,
133            unique: false,
134            empty: true,
135            roster: false,
136            defval: None,
137            primary_key: false,
138            index: false,
139            only_db: false,
140            meta_name: None,
141        }
142    }
143    /// Initialize default FieldAttributes for this FieldType
144    pub fn new(dt:FieldType) -> Self {
145        FieldAttributes::new_default(dt)
146    }
147    /// Initialize NOT NULL FieldAttributes for this FieldType
148    pub fn new_nn(dt:FieldType) -> Self {
149        let mut me = FieldAttributes::new_default(dt);
150        me.empty = false;
151        me
152    }
153    /// Initialize NOT NULL + INDEX FieldAttributes for this FieldType
154    pub fn new_nn_idx(dt:FieldType) -> Self {
155        let mut me = FieldAttributes::new_nn(dt);
156        me.index = true;
157        me
158    }
159    /// Initialize NOT NULL + default value FieldAttributes for this FieldType
160    pub fn new_nn_def(dt:FieldType, defval:&str) -> Self {
161        let mut me = FieldAttributes::new_nn(dt);
162        me.defval = Some(defval.to_string());
163        me
164    }
165    /// Initialize PrimaryKey FieldAttributes for this FieldType
166    pub fn new_pk(dt:FieldType) -> Self {
167        let mut me = FieldAttributes::new_nn(dt);
168        me.primary_key = true;
169        me
170    }
171    /// Initialize UNIQUE FieldAttributes for this FieldType
172    pub fn new_uk(dt:FieldType) -> Self {
173        let mut me = FieldAttributes::new_default(dt);
174        me.unique = true;
175        me
176    }
177    /// Initialize PrimaryKey+UNIQUE FieldAttributes for this FieldType
178    pub fn new_uk_pk(dt:FieldType) -> Self {
179        let mut me = FieldAttributes::new_uk(dt);
180        me.primary_key = true;
181        me
182    }
183    pub fn new_meta(dt:FieldType, meta:&str) -> Self {
184        let mut me = FieldAttributes::new_default(dt);
185        me.meta_name = Some(meta.to_string());
186        me
187    }
188}
189
190/// Field of a Table
191#[derive(Serialize, Deserialize, Debug, PartialEq, Clone)]
192pub struct Field {
193    pub name: String,
194    pub attributes: FieldAttributes,
195}
196impl Field {
197    /// Initialize Field named name with FieldAttributes
198    pub fn new(name:&str, attrs:&FieldAttributes) -> Self {
199        Field {
200            name:name.to_string(),
201            attributes:attrs.clone(),
202        }
203    }
204    /// Initialize Field named name with FieldAttributes with only_db attributes on
205    pub fn new_only_db(name:&str, attrs:&FieldAttributes) -> Self {
206        let mut me = Field::new(name, attrs);
207        me.attributes.only_db = true;
208        me
209    }
210}
211#[typetag::serde]
212impl DBObject for Field {
213    fn to_sql(&self, type_writer:&dyn TypeWriter) -> String {
214        // TODO: escape all SQL reserved words
215        let mut s = match self.name.as_str() {
216            "role"=>format!("\"role\""),
217            _=>self.name.to_owned(),
218        };            
219        let att = &self.attributes;
220        s += format!(" {} ", att.dtype.to_sql(type_writer)).as_str();
221        s += if att.empty {
222            "NULL"
223        } else {
224            "NOT NULL"
225        };
226        if let Some(def) = &att.defval {
227            s += format!(" DEFAULT {}", match att.dtype {
228                FieldType::Txt => format!("\"{}\"", def),
229                _=>def.to_owned()
230            }).as_str()
231        }
232        s
233    }
234}
235
236/// Vector of Field's
237pub type Fields = Vec<Field>;
238type FieldNames = Vec<String>;
239
240/// Types of GRANT permissions
241#[derive(Serialize, Deserialize, Debug, PartialEq, Clone)]
242pub enum GrantType {
243    Select,
244    Insert,
245    Update,
246    Delete,
247    Truncate,
248    References,
249    Trigger,
250    All,
251    Usage,
252}
253impl ToString for GrantType {
254    fn to_string(&self) -> String {
255        match self {
256            GrantType::Select=>"SELECT".to_owned(),
257            GrantType::Insert=>"INSERT".to_owned(),
258            GrantType::Update=>"UPDATE".to_owned(),
259            GrantType::Delete=>"DELETE".to_owned(),
260            GrantType::Truncate=>"TRUNCATE".to_owned(),
261            GrantType::References=>"REFERENCES".to_owned(),
262            GrantType::Trigger=>"TRIGGER".to_owned(),
263            GrantType::All=>"ALL".to_owned(),
264            GrantType::Usage=>"USAGE".to_owned(),
265        }
266    }
267}
268
269/// GRANT generator
270#[derive(Serialize, Deserialize, Debug, Clone)]
271pub struct Grant {
272    pub permission: GrantType,
273    pub to: String,
274    pub on: ObjectPath,
275}
276impl Grant {
277    /// Create a GRANT sepecifying permissions, grantee and affected object
278    pub fn new(perm:GrantType, to:&str, on:&ObjectPath) -> Self {
279        Grant { permission: perm.to_owned(), to: to.to_string(), on: on.to_owned() }
280    }
281}
282#[typetag::serde]
283impl DBObject for Grant {
284    fn to_sql(&self, type_writer:&dyn TypeWriter) -> String {
285        let mut rv = "".to_owned();
286        if type_writer.supports_permissions() {
287            if self.on.otype != ObjectType::Sequence || type_writer.supports_sequences() {
288                rv = format!("GRANT {} ON {} {} TO {};", self.permission.to_string(), self.on.otype.to_string(), type_writer.schema(&self.on), self.to)
289            }
290        }
291        rv
292    }
293    fn is_top_level(&self) -> bool { true }
294    fn top_level_to_sql(&self, type_writer:&dyn TypeWriter, _delayed: &Vec<&Box<& dyn DBObject>>) -> String {
295        self.to_sql(type_writer)
296    }
297}
298
299/// Owner of a database object generator
300#[derive(Serialize, Deserialize, Debug, Clone)]
301pub struct Owner {
302    pub to: String,
303    pub of: ObjectPath,
304}
305impl Owner {
306    /// Create a Owner specifying user/role and affected object
307    pub fn new(to:&str, of:&ObjectPath) -> Self {
308        Owner { to: to.to_string(), of: of.to_owned() }
309    }
310}
311#[typetag::serde]
312impl DBObject for Owner {
313    fn to_sql(&self, type_writer:&dyn TypeWriter) -> String {
314        let mut rv = "".to_owned();
315        if type_writer.supports_permissions() {
316            if self.of.otype != ObjectType::Sequence || type_writer.supports_sequences() {
317                rv = format!("ALTER {} {} OWNER TO {};", self.of.otype.to_string(), type_writer.schema(&self.of), self.to)
318            }
319        }
320        rv
321    }
322    fn is_top_level(&self) -> bool { true }
323    fn top_level_to_sql(&self, type_writer:&dyn TypeWriter, _delayed: &Vec<&Box<& dyn DBObject>>) -> String {
324        self.to_sql(type_writer)
325    }
326}
327
328/// INDEX generator
329#[derive(Serialize, Deserialize, Debug)]
330pub struct Index {
331    pub table: ObjectPath,
332    pub fields: FieldNames,
333}
334impl Index {
335    pub fn new(table: &ObjectPath, fields:&FieldNames) -> Self {
336        Index {
337            table:table.to_owned(),
338            fields:fields.to_owned(),
339        }
340    }
341}
342#[typetag::serde]
343impl DBObject for Index {
344    fn to_sql(&self, type_writer:&dyn TypeWriter) -> String {
345        format!("CREATE INDEX {}_{}_idx ON {}{} ({});",
346                self.table.name,
347                self.fields.join("_"),
348                type_writer.schema(&self.table),
349                type_writer.index_type(),
350                self.fields.join(","))
351    }
352	fn is_top_level(&self) -> bool { true }
353}
354
355type Indexes = Vec<Index>;
356
357/// UniqueKey generator
358#[derive(Serialize, Deserialize, Debug)]
359pub struct UniqueKey {
360    pub name: String,
361    pub fields: FieldNames, 
362}
363#[typetag::serde]
364impl DBObject for UniqueKey {
365    fn to_sql(&self, _type_writer:&dyn TypeWriter) -> String {
366        format!("CONSTRAINT {}_uk UNIQUE ({})", self.name, self.fields.join(","))
367    }
368}
369
370/// PrimaryKey generator
371#[derive(Serialize, Deserialize, Debug)]
372pub struct PrimaryKey {
373    pub name: String,
374    pub fields: FieldNames, 
375}
376#[typetag::serde]
377impl DBObject for PrimaryKey {
378    fn to_sql(&self, _type_writer:&dyn TypeWriter) -> String {
379        format!("CONSTRAINT {}_pk PRIMARY KEY ({})", self.name, self.fields.join(","))
380    }
381}
382
383/// Types of ForeignKey ON clause
384#[derive(Serialize, Deserialize, Debug, Clone)]
385pub enum FKOn {
386    Restrict,
387    Cascade,
388}
389impl ToString for FKOn {
390    fn to_string(&self) -> String {
391        match self {
392            FKOn::Cascade=>"CASCADE".to_owned(),
393            FKOn::Restrict=>"RESTRICT".to_owned(),
394        }
395    }
396}
397
398fn default_on_clause() -> FKOn {
399    FKOn::Restrict
400}
401
402/// ForeignKey generator
403#[derive(Serialize, Deserialize, Debug)]
404pub struct ForeignKey {
405    pub table: ObjectPath,
406    pub fields: FieldNames,
407    pub ref_table: ObjectPath,
408    pub ref_fields: FieldNames,
409    #[serde(default="default_on_clause")]
410    pub on_delete: FKOn,
411    #[serde(default="default_on_clause")]
412    pub on_update: FKOn,
413}
414
415#[typetag::serde]
416impl DBObject for ForeignKey {
417    fn to_sql(&self, type_writer:&dyn TypeWriter) -> String {
418        let (prefix, end, sep) = ("".to_string(), "", "");
419        format!("{prefix}CONSTRAINT {}_{}_{}_fk{sep} FOREIGN KEY ({}){sep} REFERENCES {} ({}){sep} ON DELETE {} ON UPDATE {}{end}",
420                self.table.name, self.ref_table.name, self.fields.join("_"),
421                self.fields.join(","),
422                type_writer.schema(&self.ref_table),
423                self.ref_fields.join(","),
424                self.on_delete.to_string(),
425                self.on_update.to_string()
426        )
427    }
428}
429
430/// Types of upper-level objects
431#[derive(Serialize, Deserialize, Debug, Clone, PartialEq)]
432pub enum ObjectType {
433    Table,
434    Sequence,
435    Schema,
436}
437impl ToString for ObjectType {
438    fn to_string(&self) -> String {
439        match self {
440            ObjectType::Table=>"TABLE".to_owned(),
441            ObjectType::Sequence=>"SEQUENCE".to_owned(),
442            ObjectType::Schema=>"SCHEMA".to_owned(),
443        }
444    }
445}
446impl Default for ObjectType {
447    fn default() -> Self {
448        ObjectType::Table
449    }
450}
451
452
453/// Path of an object
454#[derive(Serialize, Deserialize, Debug, Clone)]
455pub struct ObjectPath {
456    pub schema: Option<String>,
457    pub name: String,
458    #[serde(default)]
459    pub otype: ObjectType,
460}
461impl ObjectPath {
462    /// Create ObjectPath of Table on schema with name
463    pub fn new_table(schema:&str, name:&str) -> Self {
464        ObjectPath { schema: Some(schema.to_string()), name: name.to_string(), otype:ObjectType::Table }
465    }
466    /// Create ObjectPath of Schema
467    pub fn new_table_only(name:&str) -> Self {
468        ObjectPath { schema: None, name: name.to_string(), otype:ObjectType::Schema }
469    }
470    /// Create ObjectPath of a Sequence
471    pub fn new_sequence(schema:&str, name:&str) -> Self {
472        ObjectPath { schema: Some(schema.to_string()), name: name.to_string(), otype:ObjectType::Sequence }
473    }
474    /// Get the full name of this ObjectPath
475    pub fn full_name(&self) -> String {
476        if let Some(schema) = &self.schema {
477            format!("{}.{}", schema, self.name)
478        } else {
479            self.name.to_owned()
480        }
481    }
482    pub fn is_equal(&self, other:&ObjectPath) -> bool {
483        let my_schema = if let Some(schema) = &self.schema {
484            schema
485        } else {
486            ""
487        };
488        let other_schema = if let Some(schema) = &other.schema {
489            schema
490        } else {
491            ""
492        };
493        return my_schema == other_schema
494            && self.name == other.name
495            && self.otype == other.otype
496    }
497}
498
499/// TABLE generator
500#[derive(Serialize, Deserialize, Debug)]
501pub struct Table {
502    pub path: ObjectPath,
503    pub fields: Fields,
504    pub fks: Option<ForeignKeys>,
505}
506impl Table {
507    /// Create a table with ObjectPath and Fields
508    pub fn new(path:&ObjectPath, fields:Fields, fks:Option<ForeignKeys>) -> Self {
509        // lets check for duplicates
510        {
511            let mut unicos = HashSet::new();
512            let mut dups = Vec::new();
513            for f in fields.iter() {
514                if ! unicos.insert(&f.name) {
515                    dups.push(&f.name)
516                }
517            }
518            if ! dups.is_empty() {                
519                panic!("{} has duplicated fields: {dups:?}", path.full_name())
520            }
521            if let Some(thefks) = &fks {
522                for fk in thefks.iter() {
523                    if ! path.is_equal(&fk.table) {
524                        panic!("{} is not a valid fk for {}", fk.table.full_name(), path.full_name())
525                    }
526                }
527            }
528        }
529        Table {
530            path: path.to_owned(),
531            fields,
532            fks,
533        }
534    }
535    /// Get the indexed fields in this Table, if any
536    pub fn indexes(&self) -> Option<Indexes> {
537        let mut idxs:Vec<String> = Vec::new();
538        for f in self.fields.iter() {
539            if f.attributes.index {
540                idxs.push(f.name.clone())
541            }
542        }
543        if ! idxs.is_empty() {
544            Some(vec![Index{ table:self.path.to_owned(), fields:idxs}])
545        } else {
546            None
547        }
548    }
549    fn gen_sql(&self, type_writer:&dyn TypeWriter, extras:Option<&Vec<String>>) -> String {
550        let cols : Vec<String> = self.fields.iter().map(|f| f.to_sql(type_writer).to_owned()).collect();
551        let mut cts:Vec<Box<dyn DBObject>> = Vec::new();
552        let mut uks:Vec<String> = Vec::new();
553        let mut pks:Vec<String> = Vec::new();
554        for f in self.fields.iter() {
555            if f.attributes.primary_key {
556                if type_writer.supports_auto_increment() || f.attributes.dtype != FieldType::AutoInc {
557                    pks.push(f.name.to_owned())
558                }                
559            }
560            if f.attributes.unique {
561                uks.push(f.name.to_owned())
562            }
563        }
564        if ! pks.is_empty() {
565            cts.push(Box::new(PrimaryKey{ name: format!("{}_{}", self.path.name, pks.join("_")), fields:pks }))
566        }
567        if ! uks.is_empty() {
568            cts.push(Box::new(UniqueKey{ name: format!("{}_{}", self.path.name, uks.join("_")), fields:uks}))
569        }
570        let mut refs : Vec<String> = cts.iter().map(|f| f.to_sql(type_writer).to_owned()).collect();
571        if let Some(fks) = &self.fks {
572           for fk in fks.iter() {
573               refs.push(fk.to_sql(type_writer))
574           }
575        }
576        let exts = if let Some(ext) = extras {
577            format!(",\n  {}", ext.join(",\n  "))
578        } else {
579            "".to_owned()
580        };
581        let mut t=format!("CREATE TABLE {} (\n  {}{}", type_writer.schema(&self.path), cols.join(",\n  "), exts);
582        if ! refs.is_empty() {
583            t += format!(",\n  {}", refs.join(",\n  ")).as_str()
584        }
585        t += "\n);";
586        t
587    }
588}
589#[typetag::serde]
590impl DBObject for Table {
591    fn to_sql(&self, type_writer:&dyn TypeWriter) -> String {
592        self.gen_sql(type_writer, None)
593    }
594    fn is_top_level(&self) -> bool { true }
595    fn top_level_to_sql(&self, type_writer:&dyn TypeWriter, delayed: &Vec<&Box<& dyn DBObject>>) -> String {
596        let mut extras = Vec::new();
597        for obj in delayed.iter() {
598            let sql = obj.to_sql(type_writer);
599            // println!("sql [{sql}]");    
600            if ! sql.is_empty() {
601                extras.push(sql)
602            }
603        }
604        if extras.is_empty() {
605            self.gen_sql(type_writer, None)
606        } else {
607            self.gen_sql(type_writer, Some(&extras))
608        }
609    }
610}
611
612/// SCHEMA generator
613#[derive(Serialize, Deserialize, Debug)]
614pub struct Schema {
615    pub name: String,
616    pub owner: String,
617}
618impl Schema {
619    ///  Create a new Schema
620    pub fn new(name:&str, owner:&str) -> Self {
621        Schema{ name: name.to_string(), owner: owner.to_string(), }
622    }
623}
624#[typetag::serde]
625impl DBObject for Schema {
626    fn to_sql(&self, type_writer:&dyn TypeWriter) -> String {
627        if type_writer.supports_schemas() {
628            format!("CREATE SCHEMA {} AUTHORIZATION {};", self.name, self.owner)
629        } else {
630            "".to_owned()
631        }
632    }
633    fn is_top_level(&self) -> bool { true }
634    fn top_level_to_sql(&self, type_writer:&dyn TypeWriter, _delayed: &Vec<&Box<& dyn DBObject>>) -> String {
635        self.to_sql(type_writer)
636    }
637}
638
639/// List of Field definitions (for De/Serialization)
640pub type DynFields = LinkedHashMap<String, FieldAttributes>;
641/// Vector of ForeignKeys
642pub type ForeignKeys = Vec<ForeignKey>;
dml_tools/sql.rs

dml_tools/
sql.rs
