# #2389 Database Schema review, FKs, null/not null columns etc...

USE alkemio;
SELECT CONCAT('FK_', SUBSTRING(MD5(RAND()), 1, 27));
-- Change a few columns to NOT NULLABLE 
ALTER TABLE challenge CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE hub CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE hub CHANGE visibility visibility VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE opportunity CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE location CHANGE city city VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE location CHANGE country country VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE activity CHANGE triggeredBy triggeredBy CHAR(36) NOT NULL;
ALTER TABLE activity CHANGE resourceId resourceId CHAR(36) NOT NULL;
ALTER TABLE activity CHANGE collaborationId collaborationId CHAR(36) NOT NULL;

-- Change a few column types:
ALTER TABLE aspect CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE project CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE canvas CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE organization CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE user CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE aspect CHANGE displayName displayName VARCHAR(255) NOT NULL;
ALTER TABLE aspect CHANGE type type varchar(255) NOT NULL;
ALTER TABLE aspect_template CHANGE type type text NOT NULL;
ALTER TABLE preference_definition CHANGE displayName displayName varchar(128) NOT NULL;
ALTER TABLE preference_definition CHANGE definitionSet definitionSet varchar(128) NOT NULL;

-- Moderate danger:
-- Add some missing FKs
-- PreferenceSet
ALTER TABLE challenge DROP CONSTRAINT FK_c07b5b4c96fa89cb80215827668;

ALTER TABLE challenge ADD CONSTRAINT FK_c890de5a08d363719a41703a638 FOREIGN KEY (preferenceSetId) REFERENCES preference_set(id) ON DELETE SET NULL;
ALTER TABLE hub ADD CONSTRAINT FK_6bf7adf4308991457fdb04624e2 FOREIGN KEY (preferenceSetId) REFERENCES preference_set(id) ON DELETE SET NULL;
ALTER TABLE organization ADD CONSTRAINT FK_c07b5b4c96fa89cb80215827668 FOREIGN KEY (preferenceSetId) REFERENCES preference_set(id) ON DELETE SET NULL;
ALTER TABLE user ADD CONSTRAINT FK_5ea996d22fbd9d522a59a39b74e FOREIGN KEY (preferenceSetId) REFERENCES preference_set(id) ON DELETE SET NULL;
ALTER TABLE canvas ADD CONSTRAINT FK_c7b34f838919f526f829295cf86 FOREIGN KEY (previewId) REFERENCES visual(id) ON DELETE SET NULL;

-- AuthorizationId
ALTER TABLE aspect ADD CONSTRAINT FK_00a8c330495ef844bfc6975ec89 FOREIGN KEY (authorizationId) REFERENCES authorization_policy(id) ON DELETE SET NULL;


-- Dangerous:
-- Change length of a few column types: 
ALTER TABLE canvas CHANGE displayName displayName VARCHAR(255) NOT NULL;
ALTER TABLE user CHANGE serviceProfile serviceProfile BOOLEAN NOT NULL;

-- Better not do this:
-- ALTER TABLE callout CHANGE nameID nameID VARCHAR(36) NOT NULL;
-- ALTER TABLE activity CHANGE description description VARCHAR(128) NOT NULL;
-- ALTER TABLE activity CHANGE type type VARCHAR(16) NOT NULL;

Inconsistency: striked through a comment about changing id columns to varchar - most of the id columns are are varchar, but I think they all should be changed to binary or to char anyway

- [x] Agent
- [x] Credential
- [x] Collaboration
- [x] Challenge
* ~~<Check manually> Challenge childChallenges Missing FK on table challenge column ChallengeId to challenge.id~~ (ParentChallengeId is ok)
* Missing FK on column preferenceSetId to preference_set.id
- [x] Hub
* ~~<Check manually> Hub challenges Missing FK on table challenge column HubId to hub.id~~ (ParentHubId is ok)
* Missing FK on column preferenceSetId to preference_set.id
- [x] Agreement
- [x] CardProfile
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [ ] Aspect
* Column nameID is char(36) but is expected to be: varchar(36)
* Column displayName is char(255) but is expected to be: varchar(255)
* ~~Entity:Aspect table:aspect property:type column:type Expected type:text but has type:char~~ I think it should be just varchar and remove this param from the decorator **CONFIRM!!**
* Missing FK on column authorizationId to authorization_policy.id
- [ ] Callout
* ~~NameID column doesn't match in callout [
{ prop: 'character_maximum_length', value: 255, expected: 36 },
{
prop: 'column_type',
value: 'varchar(255)',
expected: 'varchar(36)'
}
]~~ **better don't change this**
- [x] Opportunity
* NameID column doesn't match in opportunity [
{ prop: 'column_default', value: 'NULL', expected: null },
{ prop: 'is_nullable', value: 'YES', expected: 'NO' }
]
- [x] Project
* NameID column doesn't match in project [
{ prop: 'column_default', value: 'NULL', expected: null },
{ prop: 'is_nullable', value: 'YES', expected: 'NO' }
]
- [x] Relation
- [x] AuthorizationPolicy
- [x] CanvasCheckout
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Canvas
* ~~Column id is char(36) but is expected to be: varchar(36)~~
displayName column doesn't match in canvas [
{ prop: 'data_type', value: 'text', expected: 'varchar' },
{ prop: 'character_maximum_length', value: 65535, expected: 255 },
{ prop: 'column_type', value: 'text', expected: 'varchar(255)' }
]
* Column nameID is char(36) but is expected to be: varchar(36)
* Missing FK on column previewId to visual.id
- [x] Lifecycle
- [ ] Location
* ~~Column id is char(36) but is expected to be: varchar(36)~~
* **CONFIRM THE NEXT TWO ONES!!**
* Entity:Location table:location property:city column:city Expected Nullable:true but has:NO
* Entity:Location table:location property:country column:country Expected Nullable:true but has:NO
- [x] NVP
- [x] Preference
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [ ] PreferenceDefinition
* ~~Column id is char(36) but is expected to be: varchar(36)~~
* displayName column doesn't match in preference_definition [
{ prop: 'character_maximum_length', value: 128, expected: 255 },
{
prop: 'column_type',
value: 'varchar(128)',
expected: 'varchar(255)'
}
] - **Wrong in the entity**
* Entity:PreferenceDefinition table:preference_definition property:definitionSet column:definitionSet Expected Length:128 but has:32
* prop preference should be of type array if the relation is OneToMany **CONFIRM, CODE IN THE ENTITY IS WRONG??**
- [x] PreferenceSet
~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Reference
- [x] Tagset
- [x] Visual
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Comments
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Communication
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [ ] Discussion
* ~~Column id is char(36) but is expected to be: varchar(36)~~
* Entity:Discussion table:discussion property:createdBy column:createdBy Expected type:varchar but has type:char **CONFIRM: This entity has 'varchar' type in typeorm but I think it should be char, and it should have createdByUserId or some Id suffix if it's a char(36) or a varchar(36)**
- [x] Updates
~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Application
~~Unknown related entities Application questions~~ Many to Many checker not well implemented
~~<Check manually> ManyToMany relation {
entityName: 'Application',
manyToManyDecorator: {
name: 'ManyToMany',
documentation: '',
type: 'any',
parameters: [ [Object], [Object], [Object], [Object] ],
rawValue: 'ManyToMany(() => NVP, nvp => nvp.id, { eager: true, cascade: true })'
}
}~~ Checked, all good
- [x] CommunityPolicy
- [x] Community
~~Unknown related entities Community applications IApplication is not a valid entity~~ OK Checked manually
- [x] OrganizationVerification
~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Profile
- [x] Organization
* NameID column doesn't match in organization [
{ prop: 'column_default', value: 'NULL', expected: null },
{ prop: 'is_nullable', value: 'YES', expected: 'NO' }
]
* Missing FK on column preferenceSetId to preference_set.id
- [x] User
* NameID column doesn't match in user [
{ prop: 'column_default', value: 'NULL', expected: null },
{ prop: 'is_nullable', value: 'YES', expected: 'NO' }
]
* Column type mismatch: serviceProfile (boolean) Column serviceProfile (tinyint(4))
* Missing FK on column preferenceSetId to preference_set.id
- [x] UserGroup
- [x] Actor
- [x] ActorGroup
- [x] Context
- [x] EcosystemModel
- [x] TemplateInfo
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] AspectTemplate
* ~~Column id is char(36) but is expected to be: varchar(36)~~
* templatesSet OnDelete policies don't match NO ACTION CASCADE **CONFIRM: This is wrong in the typescript code**
* Entity:AspectTemplate table:aspect_template property:type column:type Expected type:text but has type:char
- [x] CanvasTemplate
* ~~Column id is char(36) but is expected to be: varchar(36)~~
* templatesSet OnDelete policies don't match NO ACTION CASCADE **CONFIRM: This is wrong in the typescript code ??**
- [x] LifecycleTemplate
* ~~Column id is char(36) but is expected to be: varchar(36)~~
* templatesSet OnDelete policies don't match NO ACTION CASCADE **CONFIRM: This is wrong in the typescript code ??**
- [x] TemplatesSet
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Library
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] InnovationPack
* ~~Column id is char(36) but is expected to be: varchar(36)~~
- [ ] Activity
* collaborationID needs to be renamed to collaborationId **Code is wrong in typescript**
* Entity:Activity table:activity property:triggeredBy column:triggeredBy Expected Nullable:false but has:YES
* Entity:Activity table:activity property:resourceID column:resourceID Expected Nullable:false but has:YES
* Entity:Activity table:activity property:collaborationID column:collaborationID Expected Nullable:false but has:YES
* Entity:Activity table:activity property:description column:description Expected Length:128 but has:255
Entity:Activity table:activity property:type column:type Expected Length:16 but has:128

**Done checking**
query-result-cache not used by typeorm?
application_questions not used by typeorm?
migrations_typeorm not used by typeorm?
**Done**