Missing unique keys on certain tables

[Program-O]

Below is a list of tables with missing unique keys

bot_personality - unique key on bot_id and user_id and name
bots - unique key on bot_name
client_properties - unique key on bot_id and user_id and name
spellcheck - unique key = misspelling
srai_lookup - unique key = bot_id and pattern and template_id
undefined_defaults - unique key = bot_id and user_id and pattern and template
unknown_inputs - unique key = bot_id and input and user_id
wordcensor - unique_key = word_to_censor

By adding these unique keys it will greatly improve performance

[Program-O]

**

DO NOT RUN THE TESTS BELOW WITHOUT BACKING UP YOUR DATABASE!!!

**

This has been put into a branch to wait to get peer reviewed before it will be merged into the develop branch as it is a pretty major change....
https://github.com/Program-O/Program-O/tree/Bug%23461-missing-unique-keys
Anyone that can help with testing that would be amazing...

**

The instructions below are brief, if you are able to help leave a comment below and i will supply more thorough details.

**

I am looking to verify that the

• initial install and rollback works
• upgrade and rollback worked

Instructions for useage.

Check out the Bug%23461-missing-unique-keys branch

Initial Install Test
Step 1. Run the install as normal and confirm
Expected Results

1. Program O works as normal/expected
   2.There is a migration table which contains 4 records

Initial Install Rollback Test
Step 1. Go to the command line (root directory) and type > php migrate.php rollback
Expected Results

1. Confirm the migrations table has been removed
2. Confirm the botpersonality table does not have a unique_botid_name key
3. Confirm the base tables still exist (this is a limitation) e.g.

• aiml
• aiml_userdefined
• botpersonality
• bots
• client_properties
• conversation_log
• migrations
• myprogramo
• spellcheck
• srai_lookup
• undefined_defaults
• unknown_inputs
• users
• wordcensor

Upgrade Install Test
Step 1. Go to the command line (root directory) and type > php migrate.php
Expected Results

1. Duplicates have been removed from the following tables

• aiml
• aiml_userdefined
• botpersonality
• bots
• client_properties
• conversation_log
• migrations
• myprogramo
• spellcheck
• srai_lookup
• undefined_defaults
• unknown_inputs
• users
• wordcensor

2. Program O works as normal/expected
   3.There is a migration table which contains 2 records

Upgrade Rollback Test
Step 1. Go to the command line (root directory) and type > php migrate.php rollback
Expected Results

1. Confirm the migrations table has been removed
2. Confirm the botpersonality table does not have a unique_botid_name key
3. Confirm the base tables still exist (this is a limitation) e.g.

• aiml
• aiml_userdefined
• botpersonality
• bots
• client_properties
• conversation_log
• migrations
• myprogramo
• spellcheck
• srai_lookup
• undefined_defaults
• unknown_inputs
• users
• wordcensor

SRAI Lookup
Step 1. Go to the SRAI Lookup page on in the admin area
Step 1. Hit the Fill Lookup Table
Results
The table is filled as expected

[StrangerGithuber]

Yep, srai works fine.
With the https://github.com/Program-O/Program-O/tree/Bug%23461-missing-unique-keys branch I got my error which I will update in here #463 .

P.S.: But yeah it is certain a heavy task for the webserver if there are many srai.
I suggest to add a cronjob for this instead load all srai at once.