import sqlbuilder.uda; import sqlbuilder.types; import sqlbuilder.dataset; import sqlbuilder.dialect.mysql; // foreign table static struct FT { int id; int foo; } // local table static struct LT { int id; @refersTo!FT("byColumnMap") @mapping("foo") int bar; @refersTo!FT @mapping("foo", "bar") Relation byFieldMap; @refersTo!FT @mapping("foo") Relation byFooWithId; @refersTo!FT @mapping("foo", "IS NULL") Relation byFooIsNull; @refersTo!FT @mapping("foo", "= 42") Relation byFoo42; @refersTo!FT @mapping("IS NULL", "bar") Relation byBarIsNull; @refersTo!FT @mapping("= 1", "bar") Relation byBar1; @refersTo!FT @mapping("1", "= 0") Relation noMatch; } DataSet!LT ds; assert(select(ds.byColumnMap).sql == "SELECT `LT_L_byColumnMap`.* FROM `LT` LEFT JOIN `FT` AS `LT_L_byColumnMap` ON (`LT_L_byColumnMap`.`foo` = `LT`.`bar`)"); assert(select(ds.byFieldMap).sql == "SELECT `LT_L_byFieldMap`.* FROM `LT` LEFT JOIN `FT` AS `LT_L_byFieldMap` ON (`LT_L_byFieldMap`.`foo` = `LT`.`bar`)"); assert(select(ds.byFooWithId).sql == "SELECT `LT_L_byFooWithId`.* FROM `LT` LEFT JOIN `FT` AS `LT_L_byFooWithId` ON (`LT_L_byFooWithId`.`foo` = `LT`.`id`)"); assert(select(ds.byFooIsNull).sql == "SELECT `LT_L_byFooIsNull`.* FROM `LT` LEFT JOIN `FT` AS `LT_L_byFooIsNull` ON (`LT_L_byFooIsNull`.`foo` IS NULL)"); assert(select(ds.byFoo42).sql == "SELECT `LT_L_byFoo42`.* FROM `LT` LEFT JOIN `FT` AS `LT_L_byFoo42` ON (`LT_L_byFoo42`.`foo` = 42)"); assert(select(ds.byBarIsNull).sql == "SELECT `LT_L_byBarIsNull`.* FROM `LT` LEFT JOIN `FT` AS `LT_L_byBarIsNull` ON (`LT`.`bar` IS NULL)"); assert(select(ds.byBar1).sql == "SELECT `LT_L_byBar1`.* FROM `LT` LEFT JOIN `FT` AS `LT_L_byBar1` ON (`LT`.`bar` = 1)"); assert(select(ds.noMatch).sql == "SELECT `LT_L_noMatch`.* FROM `LT` LEFT JOIN `FT` AS `LT_L_noMatch` ON (1 = 0)");
This defines a mapping for a relationship. Each item can either represent a field in the foreign table or a literal string to use as the mapping. The library distinguishes between the two by checking to see if the key could be a valid identifier. Typically, literals are not valid identifiers in any SQL.
If one key is an identifier, and the other is a literal, then the identifier is always provided first, to allow any relationship to be expressed.
The default key is "id", as this is the most common name to map tables with.
Only when both fields are identifiers is the '=' operator inserted. Otherwise, the operator must be provided in the literal portion.
If the key field is an identifier, and the mapping attribute is attached to an actual column, the field is ignored, and the column name is used instead for the local field. Relations that need multiple column mappings should be specified as Relation types.
Examples (assuming ftable = foreign table and ltable = local table):
mapping("foo", "bar") => ftable.foo = ltable.bar mapping("foo") => ftable.foo = ltable.id (or ftable.foo = ltable.field when this is attached to a field) mapping("foo", "IS NULL") => ftable.foo IS NULL mapping("foo", "= 7") => ftable.foo = 7
note the following reverse the order of expression mapping("IS NULL", "bar") => ltable.bar IS NULL mapping(" = 1", "bar") => ltable.bar = 1
mapping("1", " = 0") => 1 = 0 (i.e. never match)