V8-archive: Drop column when switching to Alias

[wellingguzman]

If a non-alias field is changed to an alias type the column is dropped. There's a bug when the field exists (it has an actual column) and it's changed to an alias type the column is not dropped because it only checks for its type to be a non-alias instead to check fi the field is actual a non-alias.

The conclusion is to drop the column if exists when switching to an alias type even if the type already is an alias type.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[wellingguzman]

We need to make a difference between an alias type and an alias column. a column that exists in a table in the database could have alias as its type, even if actually is not an alias column.

Making this difference will help solve this issue.

Originally from: #540

[benhaynes]

Any recommendations for differentiating between alias types and columns @WellingGuzman?

[wellingguzman]

If it doesn't exists in the column schema, it's an alias.

[benhaynes]

Ahh, so then shouldn't we have all the info we need to do this?

[wellingguzman]

We don't have the info yet. Directus API returns any fields that their type is one of the following as alias: alias, group, o2m, translation. Any other field are ignored.

In the other hand if the field actually exists but it's set to an alias type, it doesn't remove the column because it assume it doesn't exists, because we determine if a field is alias based on the type and not in its existence.

[benhaynes]

OK. Well I defer to you on how best to solve this — but we should be able to tell if a field actually has a column.

Can't we just try to remove the column if it's an ALIAS and catch the error if it doesn't work?

[wellingguzman]

... we should be able to tell if a field actually has a column.

We can, but actually doesn't work that way. What we gotta do now is any column in directus_fields that doesn't match one in INFORMATION_SCHEMA.COLUMNS is an alias, no matter its type. That would solve this issue.

Can't we just try to remove the column if it's an ALIAS and catch the error if it doesn't work?

That's a good workaround, but I wanted to save the request to MySQL, and just don't do it unless we know before hand it's an actual column in the database.

[benhaynes]

Understood.

I don't think that users will be deleting fields very often, so I think the try/catch method would work just fine. INFORMATION_SCHEMA.COLUMNS seems like another MySQL specific test that will cause us issues when we want to support other DB vendors.

[wellingguzman]

This will be implemented in the MySQL adapter only, this won't cause any issue to other database implementation.

I will implement it this way if possible, easy (which apparently seems to be). I will give you an update when I start working on it.

[itsmerhp]

@benhaynes
I have tried to reproduce this issue, but when converting a non-alias field as alias(O2M, Translate field), it is been removing from database table.
What is difference between alias column and alias type, can you please give an example in context of Directus to understand this difference?
Can you please also provide steps to reproduce this issue?

[benhaynes]

An "alias" is just any field within Directus that doesn't have an actual database column associated with it. I think what Welling was saying is that if you have a o2m field type, the API treats it as an "alias" since it doesn't actually have a column.

I haven't tried recently, but I assume you would reproduce this by:

1. Create an actual field, like text input (eg: varchar(100))
2. Update that field to be an alias, o2m, translation, etc
3. It updates the type but doesn't delete the database column

Basically the solution would be to delete the column when switching to any of the field types that don't actually use a column. Technically this would delete the whole column and all its data... so we should offer any safeguards possible (eg: warning through the app before changing).

[itsmerhp]

Hello @benhaynes
I have followed above mentioned steps but this issue not reproducing.

But, I have noticed two issues in app during this process(Don't know these are related to #430 or not):

1. On converting a normal field(text field) to alias field(O2M) throwing error.

This is taking place due to on click of O2M interface on updating TextField, on click of O2M interface API call has been executed directly and relational data has not been selected and passed in it.
After selection of proper relational options from Relation tab, it is executing successfully.

1. On converting an alias field(O2M) to normal field(text field), it is not fully converting(database field is not creating).
   > This issue is taking place due to onclick of normal field, API call has been executed and Field type value is passed O2M instead of String in it.
   > While updating that field again by changing Field Type as String and by selecting proper Mysql Datatype, it is converting properly and field is also creating in database table.

[benhaynes]

Thanks @itsmerhp! If this ticket's original issue is not reproducible we can mark it as such (I just did) and close it. As for the other two issues:

1. Converting Field to Relational
   We actually just resolved this one a few hours ago and it should be in the newest release! You are exactly right on the cause.

2. Alias (O2M) Field to Normal
   I see... good catch! Is this an issue with the API or App? Either way, it'd be great if you could post a detailed bug report so we can track its resolution. :)

[itsmerhp]

@benhaynes
I have checked for Point 2 : Alias (O2M) Field to Normal in latest build of APP and it is working fine now.

[bjgajjar]

As it is resolved this in the latest build; I am closing this. Feel free to reopen it if it occurs in the latest one.