Skip to main content

DB design is it wise to have huge JSON row or column inside Relational Database ?

Image
By
https://softwareengineering.stackexchange.com/questions/215429/is-it-wise-to-store-a-big-lump-of-json-on-a-database-row


Debug datetime sort error

 // get last updated review inside a shop (max two latest reviews)
 if(isset($a['my_reviews'][1])) {
     if( strtotime(@$a['my_reviews'][1]['updated_at']) > strtotime($a_last_updated) ) {
         if (strpos(@$a['my_reviews'][0]['updated_at'], '2019-01-17') !== false) {
             Log::debug(@$a['my_reviews'][0]['updated_at']. " " .@$a['my_reviews'][1]['updated_at']);
         }
         @$a['my_reviews'][0]['updated_at'] = @$a['my_reviews'][1]['updated_at'];
         if (strpos(@$a['my_reviews'][0]['updated_at'], '2019-01-17') !== false) {
             Log::debug(@$a['my_reviews'][0]['updated_at']. " after ");
         }
     }
 }
 if(isset($b['my_reviews'][1])) {
     if( strtotime(@$b['my_reviews'][1]['updated_at']) > strtotime($b_last_updated) ) {
         if (strpos(@$b['my_reviews'][0]['updated_at'], '2019-01-17') !== false) {
             Log::debug(@$b['my_reviews'][0]['updated_at']. " " .@$b['my_reviews'][1]['updated_at']);
         }
         @$b['my_reviews'][0]['updated_at'] = @$b['my_reviews'][1]['updated_at'];
         //Log::debug(@$b['my_reviews'][0]['updated_at']. " " .@$b['my_reviews'][1]['updated_at']);
         if (strpos(@$b['my_reviews'][0]['updated_at'], '2019-01-17') !== false) {
             Log::debug(@$b['my_reviews'][0]['updated_at']. " after ");
         }
     }
 }

This one is working correctly but a very interesting bug here:
 I only update the latest updated_at but NOT THE POST_ID

So when get latest POST_ID by updated_at it return wrong ID (it HAVE TO update within the updated_at)

==> my_reviews][0][review_id] = my_reviews[1]... ?
It seem swapping two review inside a shop not work. So I end up using simple loop to get latest one.



Careful with sort return data. We should double check data return on critical data like id, updated_at...


Migration reset ka boom :)
Don't worry, use path for specific schema table:
sudo  php artisan migrate:reset --path=database/migrations/2020_03_04_193041_create_abc_table.php




Comments

Post a Comment

Popular posts from this blog

AWS Elasticache Memcached connection

By
https://docs.aws.amazon.com/AmazonElastiCache/latest/mem-ug/accessing-elasticache.html#access-from-outside-aws http://hourlyapps.blogspot.com/2010/06/examples-of-memcached-commands.html Access memcached https://docs.aws.amazon.com/AmazonElastiCache/latest/mem-ug/GettingStarted.AuthorizeAccess.html Zip include hidden file https://stackoverflow.com/questions/12493206/zip-including-hidden-files phpmemcachedadmin ~ phpMyAdmin or phpPgAdmin ... telnet mycachecluster.eaogs8.0001.usw2.cache.amazonaws.com 11211 stats items stats cachedump 27 100 https://docs.aws.amazon.com/AmazonElastiCache/latest/mem-ug/VPCs.EC.html https://lzone.de/cheat-sheet/memcached VPC ID Security Group ID (sg-...) Cluster: The identifier for the cluster memcached1 Creation Time: The time (UTC) when the cluster was created January 9, 2019 at 11:47:16 AM UTC+7 Configuration Endpoint: The configuration endpoint of the cluster memcached1.ahgofe.cfg.usw1.cache.amazonaws.com:11211 St...
44 comments
Read more

Rocket.Chat DB schema

By
_raix_push_notifications avatars.chunks avatars.files instances meteor_accounts_loginServiceConfiguration meteor_oauth_pendingCredentials meteor_oauth_pendingRequestTokens migrations rocketchat__trash rocketchat_cron_history rocketchat_custom_emoji rocketchat_custom_sounds rocketchat_import rocketchat_integration_history rocketchat_integrations rocketchat_livechat_custom_field rocketchat_livechat_department rocketchat_livechat_department_agents rocketchat_livechat_external_message rocketchat_livechat_inquiry rocketchat_livechat_office_hour rocketchat_livechat_page_visited rocketchat_livechat_trigger rocketchat_message rocketchat_oauth_apps rocketchat_oembed_cache rocketchat_permissions rocketchat_raw_imports rocketchat_reports rocketchat_roles rocketchat_room rocketchat_settings rocketchat_smarsh_history rocketchat_statistics rocketchat_subscription rocketchat_uploads system.indexes users usersSessions https://rocket.chat/docs/developer-guides/sc...
24 comments
Read more

Common Command

By
Mysql: Create database with default character set, encoding: create database DB_NAME default character set utf8 default collate  utf8_general_ci; import mysql -u root -p DB_NAME < dump.sql mysql -u root -p DB_NAME < dump.sql /Application/MAMP/Library/bin/mysql /Application/MAMP/Library/bin/mysqlimport MAMP (MAC OSX + Apache + Mysql + PHP) PostgreSQL Others Export: mysqldump -u root -p Tutorials > tut_backup.sql Dump a table in DB: $ mysqldump -u root -p Tutorials php_tutorials asp_tutorials > tut_backup.sql Link stackoverflow  how-do-you-mysqldump-specific-tables Dump many DB : mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT ...
25 comments
Read more