database design – Constructing relational model from ER

I’m given the following ER model.

enter image description here

It’s unclear to me how I’d identify copies. For the image entity I think it’s sufficient to take the ‘ArtistName’ and ‘ImageName’ as a combined primary key.

Another problem is that if I am going to use this combined primary key, any table referencing this entity would need 2 columns to identify the image. It all seems easier using simple IDs. So considering (1) disk space and (2) performance (comparing 2 string keys vs 1 integer key) unique IDs seem to be the superior option.

Because the ‘buys’ relationship contains an attribute and in the exercise we’re explicitly told to avoid NULL-attributes I can’t really add a ‘boughtBy’ attribute to the copy entity (as it is perhaps not bought yet)

So using an auxiliary table I clearly need to have a primary key for copies. The easiest way would be to simply add an CopyID attribute but from the exercise it’s not clear to me if I should only construct PKs from existing attributes only.

If there’s an in-between solution I’m grateful hearing your suggestions. 🙂

magento2.3 – What is the most popular database choice for Magento 2?

this question sounds more like a poll because I’m just curious as to what database is the most commonly used for Magento2. I currently use MariaDB 10 from following an installation tutorial online, but I’m a bit wary because I’ve recently read the official documentation and it says that MariaDB may not be fully compatible with Magento2. What is the most widely used database for Magento2?

database size – What (kind of) DB can store store variable-length blobs without padding?

I have an offline task: store a HUGE amount of variable-length binary data on multiple hosts. Average data size is about 16Kb, total size: maybe hunderds of Tb. I have no consistency or speed requirement as it will likely have 1 client only.

When doing an interface mockup, I used SQlite and found this: when data pieces were all of the same length, the db file was only 4% larger than the raw data, but when I added variable-length data, the DB became almost 50% bigger. So it obviously uses some padding or reserving, and I don’t want to waste space like that.

Are there free databases that are known to be most efficient in terms of space for storing variable lengths of blobs? And provides an easy way to spread data across multiple hosts at same time?

magento2 – Database schema realted change reverted

I have one of my Magento 2 projects running with version 2.3.3. There are so many simple products for one single configurable product sue to which the SKU length issue occurred. TO solve I have taken all possible steps

1) Override file : MagentoCatalogModelProductAttributeBackendSku and change const SKU_MAX_LENGTH = 255;

Check below code for this

<?php
namespace CustomSkusizeModelProductAttributeBackend;


class Sku extends MagentoCatalogModelProductAttributeBackendSku
{
    /**
     * Maximum SKU string length
     *
     * @var string
     */
    const SKU_MAX_LENGTH = 255;


    /**
     * Validate SKU
     *
     * @param Product $object
     * @return bool
     * @throws MagentoFrameworkExceptionLocalizedException
     * @throws MagentoFrameworkExceptionLocalizedException
     */
    public function validate($object)
    {
        $attrCode = $this->getAttribute()->getAttributeCode();
        $value = $object->getData($attrCode);
        if ($this->getAttribute()->getIsRequired() && strlen($value) === 0) {
            throw new MagentoFrameworkExceptionLocalizedException(
                __('The "%1" attribute value is empty. Set the attribute and try again.', $attrCode)
            );
        }

        if ($this->string->strlen($object->getSku()) > self::SKU_MAX_LENGTH) {
            throw new MagentoFrameworkExceptionLocalizedException(
                __('SKU length should be %1 characters maximum.', self::SKU_MAX_LENGTH)
            );
        }
        return true;
    }

    /**
     * Generate and set unique SKU to product
     *
     * @param Product $object
     * @return void
     */
    protected function _generateUniqueSku($object)
    {
        $attribute = $this->getAttribute();
        $entity = $attribute->getEntity();
        $attributeValue = $object->getData($attribute->getAttributeCode());
        $increment = null;
        while (!$entity->checkAttributeUniqueValue($attribute, $object)) {
            if ($increment === null) {
                $increment = $this->_getLastSimilarAttributeValueIncrement($attribute, $object);
            }
            $sku = trim($attributeValue);
            if (strlen($sku . '-' . ++$increment) > self::SKU_MAX_LENGTH) {
                $sku = substr($sku, 0, -strlen($increment) - 1);
            }
            $sku = $sku . '-' . $increment;
            $object->setData($attribute->getAttributeCode(), $sku);
        }
    }

2) Change eav_attribute table , find sku in attribute_code and change frontend_class

3) Change different tables column’s length to 255 containing sku like catalog_product_entity , inventory_low_stock_notification_configuration , inventory_reservation , inventory_source_item

Above 2 steps are working fine. But for last steps whenever I run “setup:upgrade” , its again revert to 64 length (For all tables)

So that I have created Installation schema also for same

namespace CustomSkusizeSetup;
use MagentoFrameworkSetupInstallSchemaInterface;
use MagentoFrameworkSetupModuleContextInterface;
use MagentoFrameworkSetupSchemaSetupInterface;
class InstallSchema implements InstallSchemaInterface
{
  public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
  {
            $setup->startSetup();

            $setup->getConnection()->changeColumn(
                            $setup->getTable('catalog_product_entity'),
                            'sku',
                            'sku',
                            (
                                            'type' => MagentoFrameworkDBDdlTable::TYPE_TEXT,
                                            'length' => 225
                            )
            );

            $setup->getConnection()->changeColumn(
                            $setup->getTable('inventory_low_stock_notification_configuration'),
                            'sku',
                            'sku',
                            (
                                            'type' => MagentoFrameworkDBDdlTable::TYPE_TEXT,
                                            'length' => 225
                            )
            );

            $setup->getConnection()->changeColumn(
                            $setup->getTable('inventory_reservation'),
                            'sku',
                            'sku',
                            (
                                            'type' => MagentoFrameworkDBDdlTable::TYPE_TEXT,
                                            'length' => 225
                            )
            );

            $setup->getConnection()->changeColumn(
                            $setup->getTable('inventory_source_item'),
                            'sku',
                            'sku',
                            (
                                            'type' => MagentoFrameworkDBDdlTable::TYPE_TEXT,
                                            'length' => 225
                            )
            );






  }
}

After this when I run “setup:upgrade” first time, its working fine and above all table’s sku column remain with 255 length

But if I execute same command again then its again reverted to 64

I also tried schema patch

<?php
/**
* Copyright © 2019 Magenest. All rights reserved.
* See COPYING.txt for license details.
*/
namespace CustomSkusizeSetupPatchSchema;


use MagentoFrameworkDBDdlTable;
use MagentoFrameworkSetupPatchSchemaPatchInterface;
use MagentoFrameworkSetupModuleDataSetupInterface;


class ChangeColumn implements SchemaPatchInterface
{
   private $moduleDataSetup;


   public function __construct(
       ModuleDataSetupInterface $moduleDataSetup
   ) {
       $this->moduleDataSetup = $moduleDataSetup;
   }


   public static function getDependencies()
   {
       return ();
   }


   public function getAliases()
   {
       return ();
   }


   public function apply()
   {
       $this->moduleDataSetup->startSetup();


       $this->moduleDataSetup->getConnection()->changeColumn(
         $this->moduleDataSetup->getTable('catalog_product_entity'),
         'sku',
         'sku',
         (
             'type' => MagentoFrameworkDBDdlTable::TYPE_TEXT,
             'length' => 225,             
             'comment' => 'Product sku lenght is 225'
         )
     );


       $this->moduleDataSetup->getConnection()->changeColumn(
         $this->moduleDataSetup->getTable('inventory_low_stock_notification_configuration'),
         'sku',
         'sku',
         (
             'type' => MagentoFrameworkDBDdlTable::TYPE_TEXT,
             'length' => 225,             
             'comment' => 'Product sku lenght is 225'
         )
     );

       $this->moduleDataSetup->getConnection()->changeColumn(
         $this->moduleDataSetup->getTable('inventory_reservation'),
         'sku',
         'sku',
         (
             'type' => MagentoFrameworkDBDdlTable::TYPE_TEXT,
             'length' => 225,             
             'comment' => 'Product sku lenght is 225'
         )
     );

       $this->moduleDataSetup->getConnection()->changeColumn(
         $this->moduleDataSetup->getTable('inventory_source_item'),
         'sku',
         'sku',
         (
             'type' => MagentoFrameworkDBDdlTable::TYPE_TEXT,
             'length' => 225,             
             'comment' => 'Product sku lenght is 225'
         )
     );


       $this->moduleDataSetup->endSetup();
   }
}

Still same issue. How can I achieve this please help.

c# – Logging to database in ASP.NET Core Entity Framework Core application

I have an ASP.NET Core Entity Framework Core application and I want to implement my own custom logger. I don’t want to use ready solutions like NLog or Serilog.

I added a new class library project (“Logger”) to my application and added classes that do the logging. Mostly they call the service provider to get the db context and add a log object to it and then save changes.

The problem is that my db context is located in another project of type class library (“Data”), so “Logger” references “Data” in order to use the db context class. However to be able to save logs in the database I also need the db context to have a db set of type “Log”, which would mean a circular dependency.

How do I remedy this situation?

Want to bind my custom grid with existing core database table of magento 2

I have created a custom module under system tab. Now I want to create a custom grid on that page and binds this grid with the core magento database table “directory_country_region”.

This is the custom module on which I want to create table

This is my folder structure used to achieve this task, registration.php file is there at bottom which is not visible in screenshort

postgresql – Non-Super user permissions required to allow a user to create a schema within a database that is owned newly created user

I’m able to run the following as postgres or any other user which has SUPERUSER (“dba1” has SUPERUSER in this case)

psql -U dba1 dev << SQL
create user udev1;
create schema udev1 authorization udev1;
SQL

what is the minimal set of discrete privileges required for dba1 which would enable dba1 to create a new user “udev2” and a schema owned by that user “udev2” in the dev database?

Time study / Data fetch and input from database

Below is an example , where Blue is user input either entering/drop-down , Yellow is from the database that the user can pick from drop-down list and Green is automatically calculated by the system . Hopefully I'm getting my point across. If you worked on a similar project , I would really appreciate if you can share your examples / tutorials / experience you had making a web-page that takes in value and stores in a database , either SQL, MySQL, access or any DB and guide me please. Any…

Time study / Data fetch and input from database

php – How to insert multiple rows and columns in database using array

Please i want to know if it is posible to use one sql query to insert into multiple columns and rows using array in php
$sql = "INSERT INTO MyGuests (firstname, lastname, email) <br> VALUES ('John', 'Doe', 'john@example.com');";
but i want to represent column names in array as arrays and then values as array values, something that will look like this in php
$sql = "INSERT INTO myguest (array(firstname => 'john'));

postgresql – Why is autovacuum running during a VACUUM FREEZE on the whole database?

I have a 14.7 TB Postgres(9.1) database that has to endure heavy writes for prolonged periods of time. I know that I need to closely manage my transaction ids to prevent db lockup. Recently I noticed the queries slowing, and saw that there were multiple autovacuums running on our massive read-only tables, with the “(to prevent wraparound)” subscript. I stopped the running software and executed a vacuumdb -F -a command. However, when I run select current_query from pg_stat_activity I see that the autovacuum processes are still running, even during a manual vacuum. I tried killing the autovacuums with select pg_cancel_backend(pid) and they died but then immediately restart again. My questions:

  1. Is autovacuum supposed to continue to run during a manual db vacuum?
  2. How do I effectively get these autovacuum processes to stop?
  3. Why would these autovacuums continue to run on a read-only table? What is there to vacuum?