How do I use `insertFrom`? about beam

[tathougies]

Should be something like

data TableT f =
  Table { _column1 :: C f Int
        , _column2 :: C f Text }
  deriving Generic
deriving instance Beamable TableT

instance Table TableT ...

data Table2T f =
  Table2 { _column3 :: C f Text }
  deriving Generic
deriving instance Beamable Table2T

instance Table Table2T ...

data Db entity =
  Db { _tbl1 :: entity (TableEntity TableT)
     , _tbl2 :: entity (TableEntity Table2) }
  deriving Generic
instance Database Db

db :: DatabaseSettings be Db
db = autoDbSettings

insert (_tbl1 db) $
insertFrom $ select $
fmap (\(Table2 column3) -> Tablet (charLength_ column3) column3) $
all_ (_tbl2 db)

[3noch]

In my case _tbl1 has a primary key so I wanted to use default_ to not specify that. However, you can't select DEFAULT on the RHS.

[tathougies]

@3noch That's right. That's a SQL limitation. Are you asking about using projections (like INSERT INTO tbl(field1, field2,...)) in insert? If so, you're right, there's no current way to do this, but we can add this in if you need it

[3noch]

Yes that must be what I'm looking for, a way to do a projection on the FROM SELECT so that I can specify some (but not all) of the target table columns.

[tathougies]

I think we could potentially do a syntax along the lines of:

insertFields (_tbl1 db) (\tbl -> _field1 tbl) $ insertFrom $ select $ fmap (\(Table2 column3) -> column3) $ all_ (_tbl2 db)

So basically you specify the tbl to insert into, a projection from that table, and then we'd rework the types a bit to allow insertFrom to return arbitrary projections instead of tables only. Would that work?

[luigy]

in the case of falling back to default isn't that what Auto Nothing is for or does that not help in this case?

[tathougies]

Auto Nothing is a beam convenience. It's unrepresentable in general in SQL

[3noch]

@tathougies I have very little opinion or wisdom to offer regarding how you represent this in Beam. I don't quite see how your example would work but I don't think that means much. In particular, the trick is saying how fields from two different tables would correspond. That doesn't seem trivial to me.

[tathougies]

Many months later, but insertOnly is now a thing. From the docs:

runInsert $
  insertOnly (invoice chinookDb)
             (\i -> ( invoiceCustomer i, invoiceDate i, invoiceBillingAddress i, invoiceTotal i ) ) $
  insertFrom $ do
    c <- all_ (customer chinookDb)

    -- We'll just charge each customer $10 to be mean!
    pure (primaryKey c, currentTimestamp_, customerAddress c, as_ @Scientific $ val_ 10)

inserts values only into the customer, date, billing address, and total columns.

[3noch]

Sweet!