LeetCode 175. Combine Two Tables

-- PrjectM36 Schema for LeetCode175

-- if sql's nullable is not considered.

person :: {personId Int, firstName Text, lastName Text}; 

key person_pkey {personId} person;

address :: {addressId Int, personId Int, city Text, state Text};
key address_pkey {addressId} address;
foreign key personId_in_address address{personId} in person{personId}; 

person := relation{tuple{personId int(1), firstName "Ted", lastName "Liao"},
                  tuple{personId int(2), firstName "Johnny", lastName "Lee"}};
address := relation{tuple{addressId int(1), personId int(1), city "Taipei", state "R.O.C"}};

-- if nullable is considered

personNullable :: {personId Int, firstName Maybe Text, lastName Maybe Text};
key personNullabe_pkey {personId} personNullable;
addressNullable :: {addressId Int, personId Maybe Int, city Maybe Text, state Maybe Text};
key addressNullabe_pkey {addressId} addressNullable;
personNullable := relation{tuple{personId int(1), firstName (Just "Ted"), lastName (Just "Liao")},
                  tuple{personId int(2), firstName (Just "Johnny"), lastName (Just "Lee")}};
addressNullable := relation{tuple{addressId int(1), personId (Just int(1)), city (Just "Taipei"), state (Just "R.O.C")}};

 

--when typing tutd, 

-- 1. it seems typing relation type once and inputting a relation as simple as {(2,Just "Ted", Just "Liao")} maybe a good way to enjoy, although I am aware of attributes order is not an necessity for relational algebra.

-- 2. int(n) is a bit annoying, I wish Int and Integer can be deduced.

-- 3. I saw there is a commented-out uniqueJust. It seems needed for mimicking nullable columns in sql. And is it worth looking into how to express foreign key for this kind of value?

-- 4 If constraints can be shown with types, that will make this relvar's type more clear. But I am not sure if if feels curbersome. Maybe placing it in :type is enough. For example,

TutorialD (master/main): :type person
┌───────────────┬──────────────┬─────────────────┐
│firstName::Text│lastName::Text│u. personId::Int │
└───────────────┴──────────────┴─────────────────┘
┌──────────────┬──────────┬────────────────┬───────────┐
│addressId::Int│city::Text│f. personId::Int│state::Text│
├──────────────┼──────────┼────────────────┼───────────┤
-- Anyway, it is not a mature idea right now.

-- And what if a relvar is undefined but the constraint is still there?

 

-- When answering questions,

:showexpr (address join person){firstName, lastName, city, state}

:showexpr (((personNullable:{tmp := Just @personId}){all but personId} rename {tmp as personId}) join addressNullable){firstName, lastName, city, state}

-- I found join operator can express "people who have address". That's a cool discovery for me.

-- the pattern :{attrTransformed := ...}{all but attrOriginal} rename {tmp as attrOriginal} keeps showing. Maybe it can be a relation function? or some persist macro that shorten the expression?

-- maybe extend and project operator can be used consecutively without parentheses.

 

-- However, the question says "regardless if there is an address for each of those people"

-- So, I need a special "union" that unions with attribute difference, making them a Maybe value. Maybe it is about Type Constructor isomorphic schema on Maybe.

TutorialD (master/main): :showexpr ((person matching address) join address)
┌──────────────┬──────────┬───────────────┬──────────────┬─────────────┬───────────┐
│addressId::Int│city::Text│firstName::Text│lastName::Text│personId::Int│state::Text│
├──────────────┼──────────┼───────────────┼──────────────┼─────────────┼───────────┤
│1             │"Taipei"  │"Ted"          │"Liao"        │1            │"R.O.C"    │
└──────────────┴──────────┴───────────────┴──────────────┴─────────────┴───────────┘
TutorialD (master/main): :showexpr (person not matching address)
┌───────────────┬──────────────┬─────────────┐
│firstName::Text│lastName::Text│personId::Int│
├───────────────┼──────────────┼─────────────┤
│"Johnny"       │"Lee"         │2            │
└───────────────┴──────────────┴─────────────┘
-- I can't just extend an attribute of Nothing because TypeConstructorTypeVarMissing problem still exists.

-- Is a Nothing :: Maybe Int AtomExpr useful when there haven't been a type deduction?

{-

Revision 2021/04/01: 

Maybe I can cross prouduct typed Nothings and not-matching list.

-- imature Pesudocode: (not-matching cross-product relation{city Maybe Text, state Maybe Text}{tuple{city Nothing, state Nothing}) union matched

-- cross-product is just to extend one-value, same-attribute-names type on both relvars and then join them. And you got all possibilities of tuple combination. Another fun thing to find out!

--  First, make personId a Maybe type in order to joio with addressNullable.

-- caveat: assignment will loose constraints on relvars. We better have a persistent shorthand feature like "tutorialD: name as <relational expression>". Right now, using assignment here is just a (not right) way to compose expressions.

-- Hmm, can just add a Just constructor get you a uniqueJust constraint? What does adding a Just constructor on a unique attribute mean? Good Question...Here we don't think update problems later... Or should I remove Just in personId in addressNullable? That sounds more correct when you just want to focus on address who have people on. Maybe next time.

TutorialD (master/main): personWithJustId := (personNullable:{tmp := Just @personId}){all but personId} rename {tmp as personId}                                                               
TutorialD (master/main): :showexpr personWithJustId                                                                                                                                            
┌──────────────────────────┬─────────────────────────┬────────────────────────┐                                                                                                                
│firstName::Maybe (a::Text)│lastName::Maybe (a::Text)│personId::Maybe (a::Int)│                                                                                                                
├──────────────────────────┼─────────────────────────┼────────────────────────┤                                                                                                                
│Just "Johnny"             │Just "Lee"               │Just 2                  │                                                                                                                
│Just "Ted"                │Just "Liao"              │Just 1                  │                                                                                                                
└──────────────────────────┴─────────────────────────┴────────────────────────┘   
TutorialD (master/main): matched := (((personNullable:{tmp := Just @personId}){all but personId} rename {tmp as personId}) join addressNullable){firstName, lastName, city, state}
TutorialD (master/main): :showexpr matched
┌─────────────────────┬──────────────────────────┬─────────────────────────┬──────────────────────┐
│city::Maybe (a::Text)│firstName::Maybe (a::Text)│lastName::Maybe (a::Text)│state::Maybe (a::Text)│
├─────────────────────┼──────────────────────────┼─────────────────────────┼──────────────────────┤
│Just "Taipei"        │Just "Ted"                │Just "Liao"              │Just "R.O.C"          │
└─────────────────────┴──────────────────────────┴─────────────────────────┴──────────────────────┘

TutorialD (master/main): notMatched := personWithJustId not matching addressNullable                                                                                                           
TutorialD (master/main): :showexpr notMatched                                                                                                                                                  
┌──────────────────────────┬─────────────────────────┬────────────────────────┐                                                                                                                
│firstName::Maybe (a::Text)│lastName::Maybe (a::Text)│personId::Maybe (a::Int)│                                                                                                                
├──────────────────────────┼─────────────────────────┼────────────────────────┤                                                                                                                
│Just "Johnny"             │Just "Lee"               │Just 2                  │                                                                                                                
└──────────────────────────┴─────────────────────────┴────────────────────────┘   
 

-- here comes the fun part: cross product
TutorialD (master/main): data Unit = Unit
TutorialD (master/main): :showexpr ((notMatched:{tmp := Unit}) join relation{city Maybe Text, state Maybe Text, tmp Unit}{tuple{city Nothing, state Nothing, tmp Unit}}){all but tmp}
┌─────────────────────┬──────────────────────────┬─────────────────────────┬────────────────────────┬──────────────────────┐
│city::Maybe (a::Text)│firstName::Maybe (a::Text)│lastName::Maybe (a::Text)│personId::Maybe (a::Int)│state::Maybe (a::Text)│
├─────────────────────┼──────────────────────────┼─────────────────────────┼────────────────────────┼──────────────────────┤
│Nothing              │Just "Johnny"             │Just "Lee"               │Just 2                  │Nothing               │
└─────────────────────┴──────────────────────────┴─────────────────────────┴────────────────────────┴──────────────────────┘

 TutorialD (master/main): :showexpr matched union notMatched_{all but personId}
┌─────────────────────┬──────────────────────────┬─────────────────────────┬──────────────────────┐
│city::Maybe (a::Text)│firstName::Maybe (a::Text)│lastName::Maybe (a::Text)│state::Maybe (a::Text)│
├─────────────────────┼──────────────────────────┼─────────────────────────┼──────────────────────┤
│Nothing              │Just "Johnny"             │Just "Lee"               │Nothing               │
│Just "Taipei"        │Just "Ted"                │Just "Liao"              │Just "R.O.C"          │
└─────────────────────┴──────────────────────────┴─────────────────────────┴──────────────────────┘

-- I got a type constructor union! Cool!

-}

And that's my first attempt in LeetCode using project-m36.

Thanks for reading.

{-

revision2:

--Oops.

TutorialD (master/main): :showexpr (addressNullable where not personId = Nothing)
ERR: TupleAttributeTypeMismatchError [Attribute "personId" (ConstructedAtomType "Maybe" (fromList [("a",IntAtomType)]))]

-- Okay, let's try to remove Just constructor in addressNullable and focus on what we have.

-- maybe a more generalized constructor matcher isConstructor() could help more? pattern match and bring args into attributes?
TutorialD (master/main): addressFocusOnJust := ((addressNullable where ^isJust(@personId)):{tmp := fromMaybe(int(0), @personId)}){all but personId} rename {tmp as personId}
TutorialD (master/main): :showexpr addressFocusOnJust
┌──────────────┬─────────────────────┬─────────────┬──────────────────────┐
│addressId::Int│city::Maybe (a::Text)│personId::Int│state::Maybe (a::Text)│
├──────────────┼─────────────────────┼─────────────┼──────────────────────┤
│1             │Just "Taipei"        │1            │Just "R.O.C"          │
└──────────────┴─────────────────────┴─────────────┴──────────────────────┘


TutorialD (master/main): matched := (personNullable join addressFocusOnJust){all but addressId}

TutorialD (master/main):  :showexpr matched
┌─────────────────────┬──────────────────────────┬─────────────────────────┬─────────────┬──────────────────────┐
│city::Maybe (a::Text)│firstName::Maybe (a::Text)│lastName::Maybe (a::Text)│personId::Int│state::Maybe (a::Text)│
├─────────────────────┼──────────────────────────┼─────────────────────────┼─────────────┼──────────────────────┤
│Just "Taipei"        │Just "Ted"                │Just "Liao"              │1            │Just "R.O.C"          │
└─────────────────────┴──────────────────────────┴─────────────────────────┴─────────────┴──────────────────────┘

TutorialD (master/main): notMatched := (((personNullable not matching addressFocusOnJust):{tmp := Unit}) join relation{city Maybe Text, state Maybe Text, tmp Unit}{tuple{city Nothing, state Nothing, tmp Unit}}){all but tmp}  

TutorialD (master/main): :showexpr notMatched
┌─────────────────────┬──────────────────────────┬─────────────────────────┬─────────────┬──────────────────────┐                                                                              
│city::Maybe (a::Text)│firstName::Maybe (a::Text)│lastName::Maybe (a::Text)│personId::Int│state::Maybe (a::Text)│                                                                              
├─────────────────────┼──────────────────────────┼─────────────────────────┼─────────────┼──────────────────────┤                                                                              
│Nothing              │Just "Johnny"             │Just "Lee"               │2            │Nothing               │                                                                              
└─────────────────────┴──────────────────────────┴─────────────────────────┴─────────────┴──────────────────────┘  

TutorialD (master/main): :showexpr matched union notMatched
┌─────────────────────┬──────────────────────────┬─────────────────────────┬─────────────┬──────────────────────┐                                                                              
│city::Maybe (a::Text)│firstName::Maybe (a::Text)│lastName::Maybe (a::Text)│personId::Int│state::Maybe (a::Text)│                                                                              
├─────────────────────┼──────────────────────────┼─────────────────────────┼─────────────┼──────────────────────┤                                                                              
│Just "Taipei"        │Just "Ted"                │Just "Liao"              │1            │Just "R.O.C"          │                                                                              
│Nothing              │Just "Johnny"             │Just "Lee"               │2            │Nothing               │                                                                              
└─────────────────────┴──────────────────────────┴─────────────────────────┴─────────────┴──────────────────────┘ 

Voila! type constructor union with missing data patched.

 

-- PS. I definitely need to find out command line history in nix...  ~/.tutd_history

-- PS2. If we have a universe / enum feature, it will be easier to explore options that we have in a relvar, although universe for large type may explore the compiler's mind.

TutorialD (master/main): data A = A | B | C  

enum :: A  == relation{tuple{enum A}, tuple{enum B}, tuple{enum C}}

TutorialD (master/main): :showexpr (enum :: A)
┌───────┐
│ a::A  │
├───────┤
│B      │
│C      │
│A      │
└───────┘

TutorialD (master/main): data D = D | E

TutorialD (master/main): enum :: A X D

┌────────┬────────┐
│  a::A  │  d::D  │
├────────┼────────┤
│B       │D       │
│A       │D       │
│C       │E       │
│C       │D       │
│A       │E       │
│B       │E       │
└────────┴────────┘

 
-}