database: fields createdAt, updatedAt on every table

syndr0m commented 7 years ago

sequelize gère par défaut des champs de date de création / date de modification sequelize a été dé-configuré pour ne pas les utiliser, et ces champs sont absents de la base avoir ces dates permettrait à l'avenir de répondre à des questions de type :

combien de vidéos a quelle date, etc.

syndr0m commented 7 years ago

pour les vidéos, il est possible de récupérer cette information depuis la mam

syndr0m commented 7 years ago

liste des tables :

SELECT "tablename" FROM pg_catalog.pg_tables where schemaname='public' order by "tablename" asc;

"AccessTokens"
"Actors"
"Assets"
"AuthCodes"
"Broadcasters"
"CacheUsersSubscriptions"
"Captions"
"CatchupProviders"
"Categories"
"CategoryAdSpots"
"CategoryMovies"
"Clients"
"Comments"
"Configs"
"Countries"
"Episodes"
"ExportsBouygues"
"ExportsOCI"
"ExportsOSearch"
"Genres"
"GiftGivers"
"Images"
"Languages"
"Licensors"
"LifePins"
"LifeSpots"
"LifeThemePins"
"LifeThemeSpots"
"LifeThemes"
"LifeUsersPins"
"LifeUsersPinsLikes"
"Logs"
"Movies"
"MoviesActors"
"PFGroups"
"PFGroupsProfiles"
"PFProfiles"
"Posts"
"Presses"
"RefreshTokens"
"Seasons"
"Stores"
"Tags"
"Users"
"UsersFavoritesEpisodes"
"UsersFavoritesMovies"
"UsersFavoritesSeasons"
"UsersVideos"
"Videos"
"VideosComments"
"WaitingUsers"
"WallNotes"
"WallNotesUsers"
"Widgets"
"Works"
"backupAccessTokens"
"backupAccessTokens20160802"
"backupClients20160823"
"backupEpisodes"
"backupExportsBouygues"
"backupExportsBouygues11072016"
"backupExportsBouygues22062016"
"backupUsersVideos"
"billing_imported_users"
"platform"
"platform_export"
"spatial_ref_sys"

liste des tables ayant déjà un champ createdAt ou updatedAt

SELECT table_name, column_name
FROM information_schema.columns 
WHERE table_schema = 'public'
  AND table_name  IN (SELECT "tablename" FROM pg_catalog.pg_tables where schemaname='public') 
  and column_name = 'createdAt' or column_name = 'updatedAt'
order by "table_name" asc

result

"Logs";"createdAt"
"Users";"createdAt"
"VideosComments";"createdAt"
"WaitingUsers";"createdAt"
"WallNotes";"createdAt"
"WallNotes";"updatedAt"
"WallNotesUsers";"createdAt"
"WallNotesUsers";"updatedAt"

on exclu cette liste de la création :


ALTER TABLE "AccessTokens"                 ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Actors"                       ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Assets"                       ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "AuthCodes"                    ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Broadcasters"                 ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "CacheUsersSubscriptions"      ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Captions"                     ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "CatchupProviders"             ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Categories"                   ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "CategoryAdSpots"              ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "CategoryMovies"               ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Clients"                      ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Comments"                     ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Configs"                      ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Countries"                    ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Episodes"                     ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "ExportsBouygues"              ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "ExportsOCI"                   ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "ExportsOSearch"               ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Genres"                       ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "GiftGivers"                   ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Images"                       ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Languages"                    ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Licensors"                    ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "LifePins"                     ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "LifeSpots"                    ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "LifeThemePins"                ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "LifeThemeSpots"               ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "LifeThemes"                   ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "LifeUsersPins"                ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "LifeUsersPinsLikes"           ADD COLUMN "createdAt" timestamp with time zone;
-- ALTER TABLE "Logs"                         ADD COLUMN "createdAt" timestamp with time zone; -- already exist
ALTER TABLE "Movies"                       ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "MoviesActors"                 ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "PFGroups"                     ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "PFGroupsProfiles"             ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "PFProfiles"                   ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Posts"                        ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Presses"                      ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "RefreshTokens"                ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Seasons"                      ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Stores"                       ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Tags"                         ADD COLUMN "createdAt" timestamp with time zone;
-- ALTER TABLE "Users"                        ADD COLUMN "createdAt" timestamp with time zone; -- already exist
ALTER TABLE "UsersFavoritesEpisodes"       ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "UsersFavoritesMovies"         ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "UsersFavoritesSeasons"        ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "UsersVideos"                  ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Videos"                       ADD COLUMN "createdAt" timestamp with time zone;
-- ALTER TABLE "VideosComments"               ADD COLUMN "createdAt" timestamp with time zone; -- already exist
-- ALTER TABLE "WaitingUsers"                 ADD COLUMN "createdAt" timestamp with time zone; -- already exist
-- ALTER TABLE "WallNotes"                    ADD COLUMN "createdAt" timestamp with time zone; -- already exist
-- ALTER TABLE "WallNotesUsers"               ADD COLUMN "createdAt" timestamp with time zone; -- already exist
ALTER TABLE "Widgets"                      ADD COLUMN "createdAt" timestamp with time zone;
ALTER TABLE "Works"                        ADD COLUMN "createdAt" timestamp with time zone;
--
ALTER TABLE "AccessTokens"                 ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Actors"                       ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Assets"                       ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "AuthCodes"                    ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Broadcasters"                 ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "CacheUsersSubscriptions"      ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Captions"                     ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "CatchupProviders"             ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Categories"                   ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "CategoryAdSpots"              ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "CategoryMovies"               ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Clients"                      ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Comments"                     ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Configs"                      ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Countries"                    ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Episodes"                     ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "ExportsBouygues"              ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "ExportsOCI"                   ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "ExportsOSearch"               ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Genres"                       ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "GiftGivers"                   ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Images"                       ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Languages"                    ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Licensors"                    ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "LifePins"                     ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "LifeSpots"                    ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "LifeThemePins"                ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "LifeThemeSpots"               ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "LifeThemes"                   ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "LifeUsersPins"                ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "LifeUsersPinsLikes"           ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Logs"                         ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Movies"                       ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "MoviesActors"                 ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "PFGroups"                     ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "PFGroupsProfiles"             ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "PFProfiles"                   ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Posts"                        ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Presses"                      ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "RefreshTokens"                ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Seasons"                      ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Stores"                       ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Tags"                         ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Users"                        ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "UsersFavoritesEpisodes"       ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "UsersFavoritesMovies"         ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "UsersFavoritesSeasons"        ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "UsersVideos"                  ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Videos"                       ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "VideosComments"               ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "WaitingUsers"                 ADD COLUMN "updatedAt" timestamp with time zone;
-- ALTER TABLE "WallNotes"                    ADD COLUMN "updatedAt" timestamp with time zone; -- already exist
-- ALTER TABLE "WallNotesUsers"               ADD COLUMN "updatedAt" timestamp with time zone; -- already exist
ALTER TABLE "Widgets"                      ADD COLUMN "updatedAt" timestamp with time zone;
ALTER TABLE "Works"                        ADD COLUMN "updatedAt" timestamp with time zone;

syndr0m commented 7 years ago

recherche de champs type 'timestamp with time zone'


SELECT table_name, column_name, data_type
FROM information_schema.columns 
WHERE table_schema = 'public'
  AND table_name  IN (SELECT "tablename" FROM pg_catalog.pg_tables where schemaname='public') 
  and data_type = 'timestamp with time zone'
order by "table_name" asc

"AccessTokens";"created"                 <= a migrer dans createdAt
"AccessTokens";"expirationDate"
"backupAccessTokens";"created"      (rien faire, table de backup)
"backupAccessTokens";"expirationDate"
"backupAccessTokens20160802";"created"    (rien faire, table de backup)
"backupAccessTokens20160802";"expirationDate"
"backupEpisodes";"dateTo"
"backupEpisodes";"dateFrom"
"backupExportsBouygues";"last"
"backupExportsBouygues11072016";"last"
"backupExportsBouygues22062016";"last"
"backupUsersVideos";"dateLastRead"
"backupUsersVideos";"dateStartRead"
"CacheUsersSubscriptions";"cacheCreatedAt"  (rien faire: table a supprimer)
"CacheUsersSubscriptions";"cacheUpdatedAt"  (rien faire: table a supprimer)
"CacheUsersSubscriptions";"expiresAt"
"Episodes";"dateFrom"
"Episodes";"dateTo"
"ExportsBouygues";"last"
"ExportsOCI";"last"
"ExportsOSearch";"last"
"Licensors";"dateTo"
"Licensors";"negoStart"
"Licensors";"dateFrom"
"LifePins";"date"
"LifeSpots";"date"
"LifeSpots";"dateTo"
"LifeSpots";"dateFrom"
"Logs";"createdAt"       skip
"Movies";"dateTo"
"Movies";"dateFrom"
"Movies";"dateReleased"
"platform_export";"last"
"Posts";"date"
"Presses";"date"
"RefreshTokens";"created"                   <= a migrer dans createdAt
"RefreshTokens";"expirationDate"
"Seasons";"dateTo"
"Seasons";"dateFrom"
"Users";"createdAt"   skip
"Users";"birthDate"
"UsersVideos";"dateStartRead"
"UsersVideos";"dateLastRead"
"VideosComments";"createdAt"  skip
"WaitingUsers";"date"
"WaitingUsers";"createdAt"  skip
"WallNotes";"scoreUpdatedAt"
"WallNotes";"updatedAt"  skip
"WallNotes";"createdAt"  skip
"WallNotesUsers";"createdAt"  skip
"WallNotesUsers";"updatedAt"  skip
"Works";"date"

recap: une fois en prod, migrer :

"AccessTokens"."created" => "AccessTokens"."createdAt"
"RefreshTokens"."created" => "RefreshTokens"."createdAt"

UPDATE "AccessTokens" SET "createdAt" = "created" where "created" is not null;
UPDATE "RefreshTokens" SET "createdAt" = "created" where "created" is not null;

syndr0m commented 7 years ago

done

Afrostream / afrostream-back-end

database: fields createdAt, updatedAt on every table #359