2

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"}]}');
3
  • I see that you've safeguarded your table creation SQLs with CREATE TABLE IF NOT EXISTS, but the row INSERT SQL is unconditional. Do you perhaps want to check if that row already exists and contains the exact desired data? Jun 1, 2022 at 23:27
  • The correct way is not use installation SQL scripts for inserting data.
    – Sharky
    Jun 2, 2022 at 8:02
  • Perhaps make the insertions through the script.php components file ?
    – framontb
    Jun 2, 2022 at 11:12

1 Answer 1

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

1
  • PS Note that I only use J3, but I believe this should work on J4 as well if you've gone that way (its good to tag questions with which version you are using)
    – RogerCO
    Jun 4, 2022 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.