Extract CREATE statements from a table

[ndmitchell]

Should be pretty easy to generate a default CREATE statement.

[tomjaguarpaw]

This would be very useful. It requires some thought. When we create a table we have to tell the DBMS what types the columns have. This means we need a mapping from (type level) PGTypes to value level encodings of these. The latter don't exist yet, but I'll try to think of the most judicious way to add them.

[ryskajakub]

One option, how to do this would be to use something similar to the Writer, with the difference in the a type, something like this:

newtype SchemaMaker read dummy = SchemaMaker (PM.PackMap ColumnDescription () read ())

the mapping from the type level to the pg types should be pretty straightforward if we don't want to have the option of specifying the range of the pg type, if we are ok with one pg type per type on haskell type level.

Or do you have something that is more elegant in mind?

[tomjaguarpaw]

I think I would prefer to have all the data you need to create the table already stored in the Table type. This is quite easily doable, but is a breaking change since will need

required :: String -> Writer (Column a) (Column a)

to change to

required :: PGType a => String -> Writer (Column a) (Column a)

for some new class PGType which knows how to convert PGText into "Text" and PGInt4 into "Int4" etc..

Your suggestion doesn't require a breaking change, however, since it only introduces new names.

[ryskajakub]

So you mean, that the table properties would get one more field:

data TableProperties writerColumns viewColumns =
  TableProperties (Writer writerColumns viewColumns) (View viewColumns) ColumnDescription

and the information, how to create a row in a CREATE statement would be stored there.

Also, the user should be able to specify other attributes, that can't be taken from the type system like not null, that is indices, generation from sequence etc. We would probably need to add variants of required and optional with more arguments.

[tomjaguarpaw]

So you mean, that the table properties would get one more field

That's right.

Also, the user should be able to specify other attributes, that can't be taken from the type system

Yes, and probably most importantly, keys and foreign keys. However, it seems a bit awkward to do these.

[ryskajakub]

How would that look like? I'm imagining, that the referencing of foreign key could work like runUpdate, the required would accept a Table and a function read -> Column a. Although I'm not sure, if it would work for foreign keys aiming at a column in the same table.

[tomjaguarpaw]

I think it would look something like (taking liberties a bit with the types -- this is not the full specification of Table!)

foreignKey :: Table columns -> (columns -> columnSubset)
           -> Table columns' -> (columns' -> columnSubset')

For example

a :: Table (Column PGInt4, Column PGBool, Column PGString)
b :: Table (Column PGBool, Column PGInt4)

foreignKey a (\(x, y) -> (y, x)) b id

The problem is that we have to make sure those functions can only project, not do any operations, so they can't actually work on Column. We'd have to have a newtyped thing, TableColumn maybe. It's a bit awkward.

[ryskajakub]

Regarding the foreign keys, what about this signature:

foreignKey :: 

  (D.Default FKMaker from from' , 
  D.Default FKMaker to to'
  D.Default U.Unpackspec fk fk)

  => Table a0 from 
     -> (from' -> fk) 
     -> Table a1 to 
     -> (to' -> fk) 
     -> ForeignKey

where the FKMaker is just taking the columns and wrapping them in newtypes. It is defined in the following snippet. Also we could strip the Identity and create ProductProfunctor by hand. Moreover, the FKColumn could wrap the column name directly, not wrapped in PrimExpr, so one does not need to pattern match on the BaseTableAttrExpr.

newtype FKMaker a b = FKMaker (a -> Identity b)
newtype FKColumn a = FKColumn HPQ.PrimExpr
instance D.Default FKMaker (IC.Column a) (FKColumn a)

---

With regards to the column specification, what about having this:

optional :: (PGType a) => 
  Options a -> String -> TableProperties (Maybe (Column a)) (Column a)

The Options datatype further specifies the type, for example precision for numeric pg type.

class PGType a where
  data Options a
  pgTypeName :: a -> TypeName
  pgTypeOptions :: Options a -> TypeOptions

---

I did a basic implementation of this and it typechecks, so I'd like to hear from you if it's a way worth going or not.

[tomjaguarpaw]

This is a good start. I think it won't work because a user can apply operators to the Columns that appear in the Table before applying your foreignKey function. This can lead to invalid foreign key specifications. For example we might end up with

FOREIGN KEY (column1 + column2) REFERENCES table2 column3

which is invalid. I think what we actually need is to create tables like

table1 :: Table something (TableColumn a, TableColumn b)

Then I think your foreignKey signature would work (but it wouldn't need the FKMakers). We would need a new product profunctor for queryTable which would have to convert TableColumns to Columns.

[ryskajakub]

At which point can the user change the Column in the Table? You mean something like:

1. User defines a Table: table1 :: Table (Column PGInt4) (Column PGInt4)
2. User will define a new table table1' with modified the column name by extracting table properties of table1 and fmaping on it.
3. User calls mistakenly foreignKey on table1' and that would generate an incorrect fk.

This is the same as if the user would call the queryTable on the table1', which is currently permitted.

---

Anyway I also feel that it's good idea to have some TableColumn that would hold any information about the column that the user entered in the required and then make multiple extractors, one for queryTable, the other for foreignKey.

[tomjaguarpaw]

I don't understand your point about queryTable. The return value of queryTable is a Query, which one will not be allowed to generate foreign keys from.

[ryskajakub]

I meant that it we consider the situation I explained a bad thing, that the user can somehow create a new table from an existing definition and then using it in foreignKey, then a similar bad thing happens now when the user would run a queryTable on table with altered name of some columns.

This is the only point I can see where the changing of columns in Table can happen, in the (from' -> fk) function the user is already operating on the newtypes over the Column, so it's just projection there - we would not export the FKColumn constructor so the user cannot look under the columns and modify it there.

[tomjaguarpaw]

Ah but it's not a bad thing to alter the columns of a Table and then run runQuery! Or at least it's not an SQL crash bug. It may be indeed a strange thing though.

[ryskajakub]

So can you, please, point out, where the application of the operator before running the foreignKey would happen? Which function the user will apply , from Table to Table in order to create a Table that will create invalid foreign keys when passed in the foreignKey function?