What is the correct way to insert the first category called "Uncategorized" in the categories table in the SQL file during installation of component?

Question

I also need to know if the insert of the table #__content_types is correct and if there is something missing in the SQL installation code.

SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `#__tabapapo` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `asset_id` int unsigned NOT NULL DEFAULT 0,
    `title` varchar(255) NOT NULL DEFAULT '',
    `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    `description` VARCHAR(4000) NOT NULL DEFAULT '',
    `state` tinyint NOT NULL DEFAULT 0,
    `catid` int unsigned NOT NULL DEFAULT 0,
    `created` datetime NOT NULL,
    `created_by` int unsigned NOT NULL DEFAULT 0,
    `created_by_alias` varchar(255) NOT NULL DEFAULT '',
    `modified` datetime NOT NULL,
    `modified_by` int unsigned NOT NULL DEFAULT 0,
    `checked_out` int unsigned,
    `checked_out_time` datetime,
    `published` tinyint NOT NULL DEFAULT 0,
    `publish_up` datetime,
    `publish_down` datetime,
    `version` int unsigned NOT NULL DEFAULT 1,
    `metakey` text,
    `metadesc` text NOT NULL DEFAULT '',
    `access` int unsigned NOT NULL DEFAULT 0,
    `hits` int unsigned NOT NULL DEFAULT 0,
    `metadata` text NOT NULL DEFAULT '',
    `featured` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Set if article is featured.',
    `language` varchar(7) NOT NULL,
    `params` text NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_access` (`access`),
    KEY `idx_checkout` (`checked_out`),
    KEY `idx_state` (`published`),
    KEY `idx_catid` (`catid`),
    KEY `idx_createdby` (`created_by`),
    KEY `idx_featured_catid` (`featured`,`catid`),
    KEY `idx_language` (`language`)
)
    ENGINE=InnoDB 
    DEFAULT CHARSET=utf8mb4 
    DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__tabapapo_msg`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `reservado` tinyint(4) NOT NULL DEFAULT '0',
    `sala_id` INT(11) NOT NULL,
    `usu_id`  INT(11) NOT NULL,
    `params` VARCHAR(1024) NOT NULL DEFAULT '',
    `msg` TEXT NOT NULL,
    `falacom_id`  INT(11) NOT NULL,
    `tempo` DATETIME NOT NULL,
    PRIMARY KEY(`id`)
    )
    ENGINE=InnoDB
    DEFAULT CHARSET=utf8mb4 
    DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__tabapapo_usu`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `sala_id` INT(11) NOT NULL,
    `usu_id` VARCHAR (40) NOT NULL,
    `status` VARCHAR (140) NULL,
    `params` VARCHAR(1024) NOT NULL DEFAULT '',
    `ip` VARCHAR (20) NOT NULL,
    `tempo` DATETIME NOT NULL,
    PRIMARY KEY(`id`)
    )
    ENGINE=InnoDB 
    DEFAULT CHARSET=utf8mb4 
    DEFAULT COLLATE=utf8mb4_unicode_ci;

INSERT INTO `#__content_types` (`type_title`, `type_alias`, `table`, `rules`, `field_mappings`, `router`, `content_history_options`) 
VALUES
('Tabapapo', 'com_tabapapo.tabapapo',
'{"special":{"dbtable":"#__tabapapo","key":"id","type":"tabapapo","prefix":"TabaPapoTable"}}', 
'',
'',
'',
'{"formFile":"administrator\\/components\\/com_tabapapo\\/models\\/forms\\/tabapapo.xml", 
"hideFields":["params"], 
"ignoreChanges":["created","created_by"],
"convertToInt":[], 
"displayLookup":[
{"sourceColumn":"created_by","targetTable":"#__users","targetColumn":"id","displayColumn":"name"},
{"sourceColumn":"catid","targetTable":"#__categories","targetColumn":"id","displayColumn":"title"}]}');

Answer 1

I use a function in the install script to insert any required categories during the postflight() function. This uses an instance of the category table so that data can be checked and alias created etc. It also allows several categories to be created with whatever category fields you require filled in.

function postflight($type, $parent) {
    if ($type=='install') {
        //do any other stuff required before creating categories
        $app = Factory::getApplication();
        // create an array with info for each category 
        //  In this example I'm creating 2 categories with title & description for each
        $cats = array(
            array("title"=>"Uncategorised","desc"=>"default fallback category for all items"),
            array("title"=>"Imported","desc"=>"default category for imported data"));
        $message .= $this->createCategory($cats);
        $app->enqueueMessage($message,'Info');
        // do any other stuff you need to after creating categories
}

protected function createCategory($cats) {
    $message = 'Creating '.$this->extension.' categories. ';
    $db = Factory::getDBO();
    foreach ($cats as $cat) {
        //first check the category doesn't already exist 
        // eg if a partial unnistall has been done - it does happen!
        // or if you want to use it during update() to add a new category
        $query = $db->getQuery(true);
        $query->select('id')->from($db->quoteName('#__categories'))
            ->where($db->quoteName('title')." = ".$db->quote($cat['title']))
            ->where($db->quoteName('extension')." = ".$db->quote('com_mycomponent'));
        $db->setQuery($query);
        if ($db->loadResult()>0) {
            $message .= '"'.$cat['title'].' already exists<br /> ';
        } else {
            $category = Table::getInstance('Category');
            $category->extension = $this->extension;
            $category->title = $cat['title'];
            // allow overriding the default alias create by including a specific alias (you should make sure this is unique)
            if (array_key_exists('alias', $cat)) { $category->alias = $cat['alias']; }
            $category->description = $cat['desc'];
            $category->published = 1;
            $category->access = 1;
            $category->params = '{"category_layout":"","image":"","image_alt":""}';
            $category->metadata = '{"page_title":"","author":"","robots":""}';
            $category->language = '*';
            // Set the location in the tree
            $category->setLocation(1, 'last-child');
            // Check to make sure our data is valid
            if ($category->check()) {
                if ($category->store(true)) {
                    // Build the path for our category; has to happen after it is created
                    $category->rebuildPath($category->id);
                    $message .= $cat['title'].' id:'.$category->id.' created ok. '
                } else {
                    throw new Exception(500, $category->getError());
                }
            } else {
                throw new Exception(500, $category->getError());
            }
        }
    }
    return $message;
    }
    

Error handling could certainly be made more graceful. As always this is only a fragment so you'll need to have the appropriate use statements for Factory and Table etc