schema/field: add the option to use dialect specific types #277

deloz · 2020-01-11T09:36:39Z

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

a8m · 2020-01-11T19:27:20Z

Hey @deloz and thanks for opening this issue.
I agree. We should allow users to choose the dialect type they want, and this should be addressed.

Update: since #305 and #306 were merged into this issue, I'll try to share our thoughts on the implementation soon.

cliedeman · 2020-02-12T22:01:11Z

8000

I am also interested in this for postgres range types.

I created #343 to get an idea of how much effort it would be.

I can generate a hstore type, run migrations, save and retrieve.

I got stuck with implementing the hstore specific filters.(The postgres range types are stuck in a PR :()

a8m · 2020-02-27T09:06:54Z

My comment in #343:

Hey @cliedeman, sorry for the delay, I'm focused on the mutation and hooks layers and didn't have time to finalize the custom fields interface.

I thought about creating an interface that will provides the following values: database-type, Go-type and predicates (maybe create a separate package named fieldtype).

When creating a new fieldtype, a user needs to define its database type, Go-type (that implements the driver.Valuer, and the sql.Scanner interfaces). If the defined type is supporting predicates, they can be defined as well - we'll need to provide some interface for it as well (like UnaryOp, BinaryOp, etc).

IMO, we should migrate all existing fields to it (without breaking existing programs), and support the popular database types per dialect (like pg.hstore, mysql.datetime, etc).

One of the advantages of migrating existing types, is to allow users define database primitive types as Go typedefs. For example, use this Go type: type StatusCode string, and a string field in the database.

cliedeman · 2020-02-28T08:04:37Z

@a8m that makes sense and feel like a natural evolution of the api. I created an updated Draft PR using the public Append method and injecting a custom predicate which works nicely - #369 but this should wait until after the custom field interface is done

So from the comment above If I understand correctly all fields should be generalised to be similar to the UUID example (ignoring UnaryOp, BinaryOp and custom predicates for the moment)

func UUID(name string, typ driver.Valuer) *uuidBuilder {
	rt := reflect.TypeOf(typ)
	return &uuidBuilder{&Descriptor{
		Name: name,
		Info: &TypeInfo{
			Type:     TypeUUID,
			Nillable: true,
			Ident:    rt.String(),
			PkgPath:  rt.PkgPath(),
		},
	}}
}

I know this feature is not on the roadmap. Does that mean it will only be implemented post v1 or is it up for grabs?

a8m · 2020-05-01T09:21:59Z

Hey, I got back to this now, and I hope to push this ASAP.
I'm writing here a few things that I think we need to address and make it possible for users to define arbitrary types.

Allow users to override the schema-type for the existing field builders. For example:
```
field.Time("created_at").
    SchemaType(map[string]string{
        dialect.MySQL:    "datetime",
        dialect.Postgres: "date",
    })
```
In the future, we'll give users more granularity to control also the migration behavior, like defining a Migrate function.

Edit: Add another option to override the Go type. For example:
```
 field.Int("count").
     GoType(MyIntType(0))
```

Allow users defining custom database types:

field.Other("point", &Point{}). // Point is a ValueScanner type.
    SchemaType(map[string]string{
        dialect.MySQL:    "point",
        dialect.Postgres: "point",
    }).
    Optional()

Support the common dialect specific types (like point, hstore, etc) in a dialect specific package under schema/field/<dialect>field. Makes life easier for users and support proper workaround for testing using SQLite.

Last, I'm playing with an implementation for a generic query-language for ent called entql (I'll post a proper proposal before adding it to the framework), but once it will be added, users we'll be able to define custom predicates to their types. Something like this:

 // Under field declarations:
 Predicate([]entql.Predicate{
     entql.BinOp{
         Op: "?",
         Left: "?1",
         Right: "?2",
         Name: "ContainsKey",
         Type: "string",
     },
     entql.BinOp{
         Name: "Ancestor",
         Op: "@>",
         Left: "?2",
         Right: "?1",
         // Type defaults to field type.
      },
      entql.Template{
         Name: "Ancestor",
         Format: "?2 @> custom_func(?1)",
         // Type defaults to field type.
     },
 })

Of course, nothing is set in stone yet and you're welcome to share your thoughts.

DeedleFake · 2020-06-26T18:06:24Z

Last, I'm playing with an implementation for a generic query-language for ent called entql (I'll post a proper proposal before adding it to the framework), but once it will be added, users we'll be able to define custom predicates to their types.

Would this be able to handle a situation like what I'm dealing with right now? I've been attempting to use ent for PostGIS queries. It partially works, but the problem that I've run into is that even if I use a custom predicate in a Where(), I haven't been able to figure out a way to call PostGIS's custom WHERE functions. For example, let's I've got two tables, locations (id bigint, location geography(POINT)) and areas (id bigint, area geography), and I want to get all locations in an area, there doesn't seem to be a way to do it. The actual SQL query would be something like SELECT locations.location FROM locations JOIN areas ON ST_Contains(geometry(areas.area), geometry(locations.location)) WHERE areas.id = $1, but while I can construct custom col1 = col2 join conditions, there doesn't seem to be a way to construct conditions that call a custom function.

Speaking of which, would it be possible to get a method of client that returns the underlying driver so that in cases like these it's easier to get around limitations of ent and do raw SQL when I really need to? I can get around it by creating the driver manually and using ent.NewClient(), but then I also have to pass the driver around with the client to anything that might need it, and that seems quite awkward.

Edit: Just wanted to clarify that there doesn't seem to be a way to do it with predicates and WHERE clauses, either. I also just found dialect.sql.Raw(), but when I try to use it with the underlying sql.Builder, it seems to get the ordering wrong and it prepends the JOIN to the whole query instead of inserting it where I call it. I think I'm going to have no choice except to do the whole query with raw SQL.

Edit 2: Was able to get it working using

areaTable := sql.Table(areaTable)
locs, err := db.Location.Query().
  Where(func(s *sql.Selector) {
    s.Join(areaTable).On(areaTable.C(area.FieldID), areaID)
  }).
  Where(func(s *sql.Selector) {
    s.P().Join(sql.Raw(fmt.Sprintf(
      `ST_Contains(geometry(%v), geometry(%v))`,
      areaTable.C(area.FieldArea),
      s.Table().C(location.FieldLocation),
    ))).Pad().Join(sql.Raw(`AND`))
  }).
  All(ctx)

Seems a bit finicky, but it worked, at least.

a8m · 2020-06-28T07:22:09Z

Hey @DeedleFake,

Speaking of which, would it be possible to get a method of client that returns the underlying driver so that in cases like these it's easier to get around limitations of ent and do raw SQL when I really need to?

You can use the template option for this case. It's also mentioned in #85.

Edit 2: Was able to get it working using

I'll give it a look later today and I'll try to find a nicer solution for it.

DeedleFake · 2020-06-29T18:38:16Z

I think that this could be solved quite a bit simpler if two things were changed:

Add a way to construct custom predicates more reliably. Using the underlying Builder's Join() method with sql.Raw() doesn't actually properly update the state of the predicate, which causes some bizarre behavior. If I could do something like sql.P().Custom(sql.Raw(fmt.Sprintf("ST_Contains(geometry(%v), geometry(%v))", col1, col2))) instead, then that should fix the problems with needing to manually call .Pad().Join(sql.Raw("AND")) afterwards.
Make sql.Selector.On() take an sql.Predicate instead of two column names, allowing for custom joins. The current functionality should be possible to replicate with s.Join(table2).On(sql.EQ(s.Table().C(col1), table2.C(col2))).

If both of these were changed, it should be possible to do

areaTable := sql.Table(areaTable)
locs, err := db.Location.Query().
  Where(func(s *sql.Selector) {
    s.Join(areaTable).On(sql.P().Custom(sql.Raw(fmt.Sprintf(
      "ST_Contains(geometry(%v), geometry(%v))",
      areaTable.C(area.FieldArea),
      s.Table().C(location.FieldLocation),
    ))))
  }).
  All(ctx)

masseelch · 2022-02-24T08:58:45Z

This has been adressed. See the docs for GoType and Other

a8m changed the title ~~missing datetime type~~ schema/field: add the option to use dialect specific types Jan 11, 2020

This was referenced Jan 21, 2020

Add date type #305

Closed

Feature request: spatial data types #306

Closed

cliedeman mentioned this issue Feb 28, 2020

Feat/custom types #369

Closed

a8m mentioned this issue Mar 7, 2020

tsvector #379

Closed

a8m mentioned this issue Mar 20, 2020

Feature: DECIMAL type #399

Closed

This was referenced Apr 20, 2020

Feature: Views, Triggers, Functions #437

Closed

Roadmap for v1 #46

Open

This was referenced May 1, 2020

Tree traversal support #416

Closed

Feature: Policy on Mixins #465

Closed

a8m mentioned this issue May 26, 2020

Question: granularity of the future privacy layer #494

Closed

a8m mentioned this issue Aug 26, 2020

Question: How to use Custom Predicates #693

Closed

masseelch closed this as completed Feb 24, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

schema/field: add the option to use dialect specific types #277

schema/field: add the option to use dialect specific types #277

schema/field: add the option to use dialect specific types #277

schema/field: add the option to use dialect specific types #277

Comments