Add support for more field types to schema API

This issue appears to be directed towards the core schema API, not the contributed schema module.

Bumping this to 7.X. If this ability is implemented, it will be in 7.X, and possibly backported to 6.X.

seanburlington,

Can you make sure to add and test similar additions to the drivers for pgsql and the other supported databases?

We can't just add it to MySQL; otherwise, any modules written to use it will only be usable on MySQL.

Unless I am missing something... you've posted the exact same patch twice...

subscribing. I'd like to see this fixed too as it seems extremely limiting in a framework that's normally all about being stupidly flexible.

I'm not familiar enough with the innards of schema API, but it seems like at the very least we need this changed in pgsql and sqlite, and probably adding an alter hook here would be a good idea too for contrib. I'd love to get someone like bjaspan or Frando to weigh in on this patch.

That was bugging me. ;)

Yes, adding new types to schema api is this easy.

I remember when I picked the original set of types, I asked around for what date/time types I should include. I included what date.module used/needed, and did not want to try to figure out what the equivalent types across mysql/pgsql were among all the other things going on with the patch. I don't particularly want to now, but if someone else has and these three mappings for 'date' work the same, great.

I observe that the issue title is wrong. This patch adds 'date'. What we probably need is something like hook_elements() that lets contrib modules add additional schema types for various databases. This would let a contrib module define whatever types it needs for its own purposes, or just be a "schema types add-on" module for other modules to use. OTOH, well, maybe we don't, because schema api does allow the "dbname_type" property of a column structure, but hook_elements() would be more Drupalish.

But anyway, adding 'date' as this module does is fine, provided that the types are equivalent.

Addendum: I don't want to RTBC the patch because I haven't verified that the 'date' type is consistent across the dbs, but if someone else has verified it, great.

This is a duplicate of #200953: Schema API lacks the 'time' and 'date' type.

And no, the DATE type is far from being consistent across databases, and date-time related functions are not consistent either. I suggest to stay as away as possible from the DATE type.

Hi All,

I've taken a first crack at getting the spatial side of this issue resolves. I've gotten quite far I think, but i'm at a point where I think I need a little help from the database masters.

Attached is a patch that adds a 'geometry' field type for mysql and postgres (sqlite does have a spatial extension so we can add support, but for now let's try to get mysql and postgres sorted). The basic idea behind it is this:

1. MySQL always has spatial support, so we can always map the type 'geometry' to 'geometry'.
2. Postgres may or may not have spatial support (only if the postGIS extension is installed), so we need to detect postgis, and map 'geometry' either to 'geometry' (postgis is present) or to 'bytea' (postgis is not present). We map it to byeta (postgres equivalent of a BLOB) because geospatial data is always stored in "Well-Known-Binary", which is a structured binary chunk. This means that if postgis is not installed, we can still store spatial data in the database, but we won't be able to do fancy spatial queries on it.

The problem I am running into is that mysql and postgres, although they are storing the data in the same binary format, communicate different about how to get data into and out-of these fields. The problem comes down to packing.

A. MySQL likes to have it's binary data packed like so: $wkb_point = pack('H*','0101000000000000000000f03f000000000000f03f');
B. Postgres does NOT like it's data packed, and would rather be fed the string: $wkb_point = '0101000000000000000000f03f000000000000f03f';

I suspect that the implementation of BLOBs ran into a similar issue, and I see some funny goings-on with BLOBs in postgres in schema.inc and query.inc, but I don't quite know enough to figure this out. Any help would be greatly appreciated.

Thanks!

Patrick Hayes

P.S More info about WKB is here: http://dev.mysql.com/doc/refman/5.0/en/gis-wkb-format.html

Unmarking as a duplicate. Date and Time are only a part of this issue. Spatial and enum remain.

The problem I am running into is that mysql and postgres, although they are storing the data in the same binary format, communicate different about how to get data into and out-of these fields.

The database layer will take care of that. Why do you need to pack yourself?

We cannot add any more field types for Drupal 7 at this point. For Drupal 8, I want to refactor schema API to, among other things, allow contrib modules to add additional field types outside of core. I suspect this will become a moot point then.

As Damien said, BLOBs are handled by the DB driver itself so I don't know why you need to do extra packing on it.

BLOBs are handled by the DB driver itself so I don't know why you need to do extra packing on it.

I guess the problem is not that mySQL requires extra packing, but rather that postgres expects a string for inputting what ultimately is stored as binary. Feel free to checkout the patch and see what I mean. That extra snippet .txt has a clean example of the problem.

If this will be a D8 fix instead of D7, do I need to reroll the patch in any way? I patched against HEAD.

Also, do you know if there will be any way to do something like this for D7. My understanding (which is limited), was that the OO nature of the new database API allowed it to be overridden, and therefore modified to do things like this in special circumstances.

Drupal 8 development won't begin for several more months, I suspect, so this will not be on anyone's radar until Drupal 7.0 ships.

The OO rewrite of the DB layer did not, sadly, include the schema API yet. That's still mostly procedural wrapped up in classes, and I don't believe the field list is modifiable at this point. It sucks, I agree. :-( A lot of things can be overridden per-driver that were not possible before, but not the schema field list IIRC.

Only feature requests and tasks can be assigned to D8 for now, because the Drupal 8 tree is not opened yet.

Fortunately, this is a feature request :)

Please confirm as I cannot figure out where to apply this patch.

There is no and there will not be any patch for Drupal 6.

Please add

'int:bit'                  => 'BIT',
'blob:medium'     => 'MEDIUMBLOB',

as well

Quick question, would be it useful to split each of these new database column types into separate issues on the issue cue? This particular issue is getting a little long in the tooth and confusing. I'll split them out if everyone is amenable to that...

subscribe

subscribe

Re-rolled phayes' patch from #22.

Subscribing, and saying +1 for this initiative. I cannot review the patch as I don't have enough knowlegde in Spatial database, but I'm clearly in favor of this as i'm a proponent of Drupal as a GeoCMS.

sub

Just noting that Date, Time, and Datetime were all removed from D7 core late in the release cycle, so if we start adding other types back in those should be added back too.

The concern with date fields was that they weren't truly cross-database compatible, or that we couldn't be sure they were. That will be just as true, if not more so, for a geo field. We need to address at a high level what to do for database types that we are not sure about or have not tested, i.e. what should happen with these fields if used in databases other than PostgreSQL and MYSQL? One thought is to fall back to a textfield so the data can at least be stored successfully without fatal errors. Modules that use these types could be responsible for warning users that they may not behave as expected (or at all) in non-supported databases, while still providing core support for the ones they do know.

KarenS, I think your suggestion of having a hi-def option for databases that support it, and a low-def fallback for those who don't is exactly the right solution.

Regarding #41: the problem is absolutely not storing data. Any database engine can store any type of data you throw at it, even if it is using a binary field. The problem is about doing something useful with the data (querying it, having operators on it, having functions to transform it).

That last part is precisely why we removed the date/time fields from Drupal 7: even if every database can store them, there is no consistent support for operators and functions to do something useful with them (as you know, given you wrote the portability layer in the Date module).

Saying a type is supported while we only know how to store it is just lying to people. So before we can move forward with this, we should try to add support for portable functions and operators to the database layer.

So, thinking out loud, what's the DX of adding portable functions/operators? I'm fairly ignorant of the guts of DBTNG, but it seems like we'd want a system where one could either write a plugin or hook into the system and add methods like...

db_select('geospatial_field')
  ->distanceSearch($lng, $lat, $dist);

The module would (in theory) provide multiple ways to perform the same functionality depending on database setup. This seems similar to what it takes to provide the overall interface for any of our supported databases, but it would be limited to a subset of functions, so (again in theory) it would be simpler to maintain.

It's true that getFieldTypeMap() could do with some more documentation (I'm looking at D7 version). I am trying to patch for the MySQL ENUM datatype and was baffled what I should put after the colon for enum.

I didn't have a clue what might make sense for enum, so I originally wrote:

public function getFieldTypeMap() {
...
'enum:strange'  =>  'ENUM

Now I notice that this type map is as follows:

  protected function processField($field) {
...
      $map = $this->getFieldTypeMap();
      $field['mysql_type'] = $map[$field['type'] . ':' . $field['size']];
...

So, for documentation purposes:

• the item before the colon is "type"
• the item after the colon is "size"

It may not add much to the understanding, but now I understand this, I know that size is irrelevant for enum, and so I suppose it should be:

public function getFieldTypeMap() {
...
'enum:normal'  =>  'ENUM',

// or
'enum:irrelevant'  =>  'ENUM',

I can't help feeling this whole $map is done weirdly and should probably be done with arrays or something like that.

A big thanks to seanburlington for giving me a head start with the getFieldTypeMap() function with his post above on this.

I've now got a working solution for the ENUM datatype for MySQL:
http://drupal.org/node/1464354

(well, it works for me)

See now also the following for solution for timestamp datatype for MySQL
http://drupal.org/node/1466122

My conclusion is that for simple datatypes like timestamp, it really is just as easy as adding a line to the getFieldTypeMap() function:
i.e. for TIMESTAMP it is just:

'timestamp:normal'=> 'TIMESTAMP', // This is a line you need to add for timestamp

and for other datatypes, if they don't have any other fancy requirements, this is all that's needed:

'a_weird_datatype:normal'=> 'A_WEIRD_DATATYPE', // This is a line you need to add for A_WEIRD_DATATYPE

The only reason ENUM was a little trickier (see http://drupal.org/node/1464354) was that the ENUM datatype also has an array of the enumerated values. It still needed a line of similar format in the getFieldTypeMap() function.

Subscribe

Please do not change the version on an issue. It must be resolved in the later version first.

#14: schema_inc_patch_to_add_date_type_3_engines.diff queued for re-testing.

Thank you for sharing your idea for improving Drupal.

We are working to decide if this proposal meets the Criteria for evaluating proposed changes. There hasn't been any discussion here for over 8 years which suggests that this has either been implemented or there is no community support. Your thoughts on this will allow a decision to be made.

Since we need more information to move forward with this issue, the status is now Postponed (maintainer needs more info). If we don't receive additional information to help with the issue, it may be closed after three months.

Thanks!

Going to bump 1 more time before closing.

Since there's been no follow up and a feature request going to close out.

Thanks