[ t("Plugins") => '?menu=admin&action=plugins', t("AI Schema Exporter"), ], 'FORM' => [ 'name' => 'aiExportForm', 'autocomplete' => 'off', ], 'HIDDEN_FIELDS' => [ ['name' => 'submitForm', 'value' => '1'], ], 'BUTTONS' => [ ['name' => 'back_to_plugins', 'label' => 'Back to Plugins >>', 'onclick' => 'window.location="?menu=admin&action=plugins";', 'type' => 'button'], ], ]; // Add content separately - this keeps it more readable $adminUI['CONTENT'] = <<<__HTML__

AI Schema Export Tool

This tool exports your database schema optimized for AI tools (ChatGPT, Claude, etc.) with built-in coding standards for PHP, MySQL, and ZenDB.

What You Can Do
  • Generate optimized SQL queries
  • Get database structure improvements
  • Create schema documentation
  • Analyze data relationships
  • Debug complex queries
How to Use
  1. Start a conversation with ChatGPT/Claude
  2. Ask your specific question about the database
  3. Copy all text from the box below
  4. Paste it after your question

The export includes coding standards for PSR-12, ZenDB query syntax, and MySQL 5.7 compatibility requirements.

__HTML__; // show page adminUI($adminUI); } /** * Generate the export data * @return string The formatted export data */ function aiExporter_generateExportData(): string { $settings = SmartArray::new(Settings::loadArray()); $serverInfo = aiExporter_getServerInfo($settings); $data = []; // Get Server Info $data['serverInfo'] = [ "about" => "This document provides a comprehensive schema definition for a CMS Builder application, including server configuration, database structure, UI schema metadata, and field-specific documentation. It serves as a reference for AI systems to accurately understand relationships between tables, interpret field types, and generate or validate database queries and application code.", 'cms' => [ 'name' => "CMS Builder", 'version' => $settings->programVersion, 'build' => $settings->programBuild, ], 'web' => [ 'cdn' => $serverInfo->get('Content Delivery Network') ?: null, 'os' => $serverInfo->get('Operating System'), 'server' => $serverInfo->get('Web Server'), 'controlPanel' => $serverInfo->get('Control Panel'), ], 'php' => [ 'version' => preg_replace("/^v/", "", $serverInfo->get('PHP Version')), 'user' => $serverInfo->get('PHP User'), ], 'db' => [ 'server' => $serverInfo->get('Database Server'), 'database' => $settings->mysql->database, 'tablePrefix' => $settings->mysql->tablePrefix, ], ]; // get table DDL info, e.g. SHOW CREATE TABLE ... $data['tableDDL'] = []; $dbTableNames = DB::query("SHOW TABLES")->pluckNth(0)->sort()->toArray(); $excludedTables = aiExporter_getExcludedTables(); $tablePrefix = DB::$tablePrefix; foreach ($dbTableNames as $fullTable) { // skip excluded tables $baseTable = DB::getBaseTable($fullTable); if (in_array($baseTable, $excludedTables, true)) { continue; } $data['tableDDL'][$fullTable] = aiExporter_getSimpleTableDDL($fullTable); } // Get CMS Schemas $data['cmsSchema'] = []; $schemaTableNames = Schema::tables(); $excludedTables = aiExporter_getExcludedTables(); foreach ($schemaTableNames as $baseTable) { if (!in_array($baseTable, $excludedTables, true)) { $data['cmsSchema'][$baseTable] = aiExporter_getSimpleCmsSchema($baseTable); } } // Get additional docs $data['_docs']['cmsSchemaTypes'] = aiExporter_getCmsSchemaTypesDocs(); // Format the output with enhanced coding standard instructions $output = "\n\nHere is the latest JSON schema export for my site at: " . $_SERVER['HTTP_HOST']. "\n\n"; $output .= "Please analyze it thoroughly so you can assist with queries, optimizations, and customizations based on this architecture. "; $output .= "Included below are MySQL table definitions, relationships, field metadata, and UI schema configurations.\n\n"; $output .= "IMPORTANT DEVELOPMENT GUIDELINES:\n"; $output .= "1. Write all PHP code following PSR-12 standards (https://www.php-fig.org/psr/psr-12/)\n"; $output .= "2. For database queries, use ZenDB library (https://github.com/interactivetools-com/ZenDB) which is already initialized\n"; $output .= "3. Write MySQL compatible with version 5.7 for maximum compatibility\n"; $output .= json_encode($data, JSON_UNESCAPED_SLASHES | JSON_THROW_ON_ERROR | JSON_PRETTY_PRINT) . "\n\n"; return $output; } //endregion //region Schema Export Functions /** * Get the latest server info from the server changelog. */ function aiExporter_getServerInfo(SmartArray $settings): SmartArrayHtml { $serverInfo = new SmartArrayHtml(); $serverChangeLog = $settings->serverChangeLog ? json_decode($settings->serverChangeLog, true) : []; // default to empty array on error usort($serverChangeLog, fn($a, $b) => $b[0] <=> $a[0]); // sort by timestamp descending foreach ($serverChangeLog as [$timestamp, $key, $value]) { $serverInfo->{$key} ??= $value; } return $serverInfo; } /** * Return SHOW CREATE TABLE statement for a table with comment header */ function aiExporter_getSimpleTableDDL(string $fullTable): string { $result = DB::query("SHOW CREATE TABLE `?`", $fullTable)->asRaw(); $createSQL = $result->first()->nth(1); // format CREATE TABLE statement $charset = preg_match("/\bDEFAULT CHARSET=(\w+)\b/", $createSQL, $matches) ? $matches[1] : ''; // get charset from last line, e.g., CHARSET=utf8mb4 $collation = preg_match("/\bCOLLATE=(\w+)\b/", $createSQL, $matches) ? $matches[1] : ''; // get collation from last line, e.g., COLLATE=utf8mb4_unicode_ci $createSQL = preg_replace("/(int)\(\d*\)/i", "$1", $createSQL); // replace int(displayWidth) with int (displayWidth deprecated and removed in MySQL 8) $createSQL = preg_replace("/ COLLATE $collation\b/", "", $createSQL); // remove default collation from column definitions (if it matches table collation) $createSQL = preg_replace("/ CHARACTER SET $charset\b/", "", $createSQL); // remove default charset from column definitions (if it matches table charset) // JSON minification $createSQL = str_replace(" ENGINE=InnoDB ", " ", $createSQL); // remove default engine and charset $createSQL = str_replace(" DEFAULT CHARSET=utf8mb4 ", " ", $createSQL); // remove default engine and charset $createSQL = str_replace(" COLLATE=utf8mb4_unicode_ci ", " ", $createSQL); // remove default engine and charset $createSQL = str_replace(" ROW_FORMAT=DYNAMIC", "", $createSQL); // remove default engine and charset $createSQL = preg_replace("/\(\s*/", "(", $createSQL); // remove whitespace around parentheses $createSQL = preg_replace("/\s*\)/", ")", $createSQL); // remove whitespace around parentheses $createSQL = preg_replace("/\s*\n\s*/", " ", $createSQL); // remove extra whitespace $createSQL = str_replace('`', '', $createSQL); // remove backticks // output return $createSQL; } /** * Extract simplified version of CMS schema for a table */ function aiExporter_getSimpleCmsSchema(string $baseTable): array { $schema = loadSchema($baseTable); $keepTableKeys = ['menuName','_primaryKey','menuType']; foreach ($schema as $key => $value) { // skip field schemas if (is_array($value)) { continue; } if (!in_array($key, $keepTableKeys, true)) { unset($schema[$key]); } } // minify field schemas $removeFsKeys = aiExporter_getExcludedFieldSchemaKeys(); foreach (array_filter($schema, 'is_array') as $key => $fieldSchema) { // remove separator fields if (in_array($fieldSchema['type'] ?? '', aiExporter_getExcludedFieldSchemaTypes(), true)) { unset($schema[$key]); continue; } foreach ($fieldSchema as $fsKeyName => $fsKeyValue) { // rename type=none fields to type=system if ($fsKeyName === 'type' && $fsKeyValue === "none") { $schema[$key][$fsKeyName] = 'system'; } // remove blank value fields if (is_string($fsKeyValue) && trim($fsKeyValue) === '') { unset($schema[$key][$fsKeyName]); continue; } // remove unnecessary fieldSchema fields if (in_array($fsKeyName, $removeFsKeys, true)) { unset($schema[$key][$fsKeyName]); continue; } } } return $schema; } //endregion //region Configuration Functions /** * Returns a list of base table names (without prefix) that should be excluded from the export * @return array */ function aiExporter_getExcludedTables(): array { return [ // Internal tables "_sessions", "_log_audit", "_error_log", "_cron_log", "_outgoing_mail", "_accesslist", "_email_templates", "_media", "uploads", ]; } /** * Returns a list of field schema types that should be excluded from the export * @return array */ function aiExporter_getExcludedFieldSchemaTypes(): array { return [ 'separator', 'tabGroup', 'relatedRecords', ]; } /** * Returns a list of field schema keys that should be excluded from the export * @return array */ function aiExporter_getExcludedFieldSchemaKeys(): array { return [ // Name (same as key); 'name', // Internal '_tableName', 'order', 'isSystemField', 'myAccountField', 'customColumnType', // UI layout and display 'description', 'fieldPrefix', 'fieldHeight', 'fieldWidth', 'fieldAddonBefore', 'fieldAddonAfter', // Validation 'isRequired', 'adminOnly', 'indexed', 'isUnique', 'isPasswordField', 'readOnly', 'charset', 'charsetRule', // Checkbox related 'checkedByDefault', 'checkedValue', 'uncheckedValue', // Upload related 'allowUploads', 'allowedExtensions', 'checkMaxUploadSize', 'maxUploadSizeKB', 'checkMaxUploads', 'maxUploads', 'resizeOversizedImages', 'maxImageHeight', 'maxImageWidth', 'createThumbnails', 'maxThumbnailHeight', 'maxThumbnailWidth', 'createThumbnails2', 'maxThumbnailHeight2', 'maxThumbnailWidth2', 'createThumbnails3', 'maxThumbnailHeight3', 'maxThumbnailWidth3', 'createThumbnails4', 'maxThumbnailHeight4', 'maxThumbnailWidth4', 'useCustomUploadDir', 'customUploadDir', 'customUploadUrl', 'infoField1','infoField2', 'infoField3', 'infoField4', 'infoField5', // Wysiwyg Related 'useCodeEditor', '_aceDataAttr', 'autoFormat', 'defaultContent', // Date/Time Related 'defaultDate', 'defaultDateString', 'showTime', 'showSeconds', 'use24HourFormat', ]; } /** * Returns documentation for CMS schema types * @return array */ function aiExporter_getCmsSchemaTypesDocs(): array { return [ 'list' => [ 'description' => 'Fields that allow selecting one or more values from a predefined set of options. List fields are often used to establish relationships between tables.', 'keys' => [ 'listType' => [ 'pulldown' => 'Single value selection. Stored as a simple value in the database.', 'radios' => 'Single value selection using radio buttons. Stored as a simple value in the database.', 'pulldownMulti' => 'Multiple value selection via dropdown. Stored as a TAB-separated string with leading/trailing tabs (e.g., \'\\tValue1\\tValue2\\t\').', 'checkboxes' => 'Multiple value selection via checkboxes. Stored as a TAB-separated string with leading/trailing tabs (e.g., \'\\tValue1\\tValue2\\t\').', ], 'optionsType' => [ 'text' => 'Static options defined directly in \'optionsText\'. Format each line as \'value|label\' or simply \'label\' if the value equals the label.', 'table' => 'Dynamic options fetched from another table. Requires \'optionsTablename\', \'optionsValueField\', and \'optionsLabelField\'.', 'query' => 'Custom SQL query used to dynamically generate options. Use \'optionsQuery\' with placeholder \'::\' for table prefix (e.g., \'::tableName\').', ], ], 'relationships' => [ 'description' => 'List fields commonly represent foreign key relationships:', 'examples' => [ 'Single-value lists (pulldown/radios) represent a many-to-one relationship by pointing to another table\'s ID.', 'Multi-value lists (pulldownMulti/checkboxes) represent many-to-many relationships.', ], ], 'search_patterns' => [ 'single_value' => 'For single-value lists, use equality checks: `WHERE field_name = \'value\'`.', 'multi_value' => [ 'Find records containing a specific value: `WHERE field_name LIKE \'%\\\\tValue\\\\t%\'`.', 'Find records matching any value in a set: `WHERE field_name LIKE \'%\\\\tValue1\\\\t%\' OR field_name LIKE \'%\\\\tValue2\\\\t%\'`, or use separate queries to build a list suitable for an IN clause.', ], ], ], ]; } //endregion