search

elimu-ai / webapp

🖥 Web application for hosting Android applications and educational content
https://hin.elimu.ai
MIT License
30 stars 54 forks source link

Rename from `time` to `timestamp` in the database #1678

Closed jo-elimu closed 2 weeks ago

jo-elimu commented 1 month ago

To avoid the confusion of having to use .getTime().getTime(), replace usages of Calendar time; with Calendar timestamp;.

[!WARNING] Requires DB schema migration. See DB_MIGRATION.md for details.

alexander-kuruvilla commented 2 weeks ago

Ill try to take this up if nobody else is working on it.

jo-elimu commented 2 weeks ago

@alexander-kuruvilla The database migration script failed for the 2.4.15 release, so we will have to make some adjustments to the SQL in 2004014.sql.

[HIN] 08:00:33.959 [main] INFO  ai.elimu.util.db.DbMigrationHelper - performDatabaseMigration
[HIN] 08:00:33.960 [main] INFO  ai.elimu.util.db.DbMigrationHelper - pomVersion: 2.4.15
[HIN] 08:00:33.960 [main] INFO  ai.elimu.util.db.DbMigrationHelper - pomVersionAsInteger: 2004015
[HIN] 08:00:34.379 [main] INFO  ai.elimu.util.db.DbMigrationHelper - dbMigrations.size(): 43
[HIN] 08:00:34.379 [main] INFO  ai.elimu.util.db.DbMigrationHelper - versionOfMostRecentMigration: 2004011
[HIN] 08:00:34.379 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Looking up pending DB migrations after version 2004011
[HIN] 08:00:34.380 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Looking up file "db/migration/2004012.sql"...
[HIN] 08:00:34.381 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Looking up file "db/migration/2004013.sql"...
[HIN] 08:00:34.381 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Looking up file "db/migration/2004014.sql"...
[HIN] 08:00:34.381 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Migration script found for version 2004014
[HIN] 08:00:34.382 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `AudioContributionEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 08:00:34.399 [main] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 1060, SQLState: 42S21
[HIN] 08:00:34.400 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Duplicate column name 'timestamp'
[HIN] 08:00:34.408 [main] ERROR ai.elimu.web.servlet.CustomDispatcherServlet - Context initialization failed
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

The reason why this happens is because Hibernate is configured to automatically add new columns. So the current state of the tables is now two columns for the same property:

MariaDB [webapp-HIN]> DESCRIBE AudioContributionEvent;
+----------------+---------------+------+-----+---------+----------------+
| Field          | Type          | Null | Key | Default | Extra          |
+----------------+---------------+------+-----+---------+----------------+
| id             | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| comment        | varchar(1000) | YES  |     | NULL    |                |
| revisionNumber | int(11)       | NO   |     | NULL    |                |
| time           | datetime      | NO   |     | NULL    |                |
| timeSpentMs    | bigint(20)    | NO   |     | NULL    |                |
| audio_id       | bigint(20)    | NO   | MUL | NULL    |                |
| contributor_id | bigint(20)    | NO   | MUL | NULL    |                |
| timestamp      | datetime      | NO   |     | NULL    |                |
+----------------+---------------+------+-----+---------+----------------+
8 rows in set (0.002 sec)

The solution is then to delete the new (auto-generated) column before renaming the original one. More information about this here: https://github.com/elimu-ai/webapp/blob/main/DB_MIGRATION.md

Since your migration script hasn't been executed yet, you can make changes to the 2004014.sql file, and then we can do another release.

So instead of

ALTER TABLE `AudioContributionEvent` CHANGE `time` `timestamp` DATETIME;

it would be 

ALTER TABLE `AudioContributionEvent` DROP COLUMN `timestamp`;
ALTER TABLE `AudioContributionEvent` CHANGE `time` `timestamp` DATETIME;

etc

alexander-kuruvilla commented 2 weeks ago

@jo-elimu understood. Will work on this asap.

jo-elimu commented 2 weeks ago

@alexander-kuruvilla Thank you for the quick fix 🙂

I can confirm that your updated DB migration script ran successfully:

[HIN] 10:33:17.105 [main] INFO  ai.elimu.util.db.DbMigrationHelper - pomVersion: 2.4.16
[HIN] 10:33:17.108 [main] INFO  ai.elimu.util.db.DbMigrationHelper - pomVersionAsInteger: 2004016
[HIN] 10:33:17.484 [main] INFO  ai.elimu.util.db.DbMigrationHelper - dbMigrations.size(): 43
[HIN] 10:33:17.484 [main] INFO  ai.elimu.util.db.DbMigrationHelper - versionOfMostRecentMigration: 2004011
[HIN] 10:33:17.485 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Looking up pending DB migrations after version 2004011
[HIN] 10:33:17.486 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Looking up file "db/migration/2004012.sql"...
[HIN] 10:33:17.487 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Looking up file "db/migration/2004013.sql"...
[HIN] 10:33:17.487 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Looking up file "db/migration/2004014.sql"...
[HIN] 10:33:17.487 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Migration script found for version 2004014
[HIN] 10:33:17.487 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `AudioContributionEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.502 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `AudioContributionEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.508 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `AudioPeerReviewEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.512 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `AudioPeerReviewEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.522 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `ImageContributionEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.527 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `ImageContributionEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.532 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `LetterContributionEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.537 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `LetterContributionEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.541 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `LetterLearningEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.545 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `LetterLearningEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.552 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `LetterSoundContributionEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.557 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `LetterSoundContributionEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.565 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `LetterSoundPeerReviewEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.569 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `LetterSoundPeerReviewEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.574 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `NumberContributionEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.583 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `NumberContributionEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.589 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `NumberPeerReviewEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.596 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `NumberPeerReviewEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.601 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `SoundContributionEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.605 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `SoundContributionEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.610 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `StoryBookContributionEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.617 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `StoryBookContributionEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.623 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `StoryBookLearningEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.632 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `StoryBookLearningEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.639 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `StoryBookPeerReviewEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.645 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `StoryBookPeerReviewEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.651 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `WordContributionEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.657 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `WordContributionEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.663 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `WordLearningEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.668 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `WordLearningEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.676 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `WordPeerReviewEvent` DROP COLUMN `timestamp`;
[HIN] 10:33:17.682 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Executing sql: ALTER TABLE `WordPeerReviewEvent` CHANGE `time` `timestamp` DATETIME;
[HIN] 10:33:17.688 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Updating current version (2004014)
[HIN] 10:33:17.718 [main] INFO  ai.elimu.util.db.DbMigrationHelper - Database migration complete!
alexander-kuruvilla commented 2 weeks ago

@jo-elimu, glad it worked. Thanks for your support and letting me know.