DB Structure Viewer: Instructions and Plugin Description for Cotonti Siena

DB Structure Viewer: Complete Guide to the Cotonti Siena Plugin: From Installation to Advanced Use.

A plugin for viewing the database structure in the Cotonti admin panel, table fields, database backup via the control panel, and export in formats: JSON, SQL, CSV, PHP Array.

Introduction: Why Do You Need a Database Structure Viewer Plugin?

Imagine you are a Cotonti Siena site administrator or a developer just starting to work with this CMS. The database is the heart of your site: it stores all posts, users, settings, comments, and more. But how difficult it is to understand without special tools! You open phpMyAdmin, see dozens of tables prefixed with "cot_", and think: "Where are my data? What is this 'user_theme' field? Why does the pages table have so many columns, and I don’t even know what they’re for?"

This is where DB Structure Viewer comes to the rescue — a simple yet powerful plugin that turns database chaos into a clear table. This tool not only lets you view the structure of all tables (field names, types, keys, default values), but also export data in convenient formats: JSON for developers, SQL for backups, CSV for Excel analysis, and even PHP Array for quick code integration. If needed, the plugin shows the first 10 rows of a table or a specific row by ID, so you can quickly check how the data looks in practice.

The plugin is designed specifically for Cotonti Siena v.0.9.26 (and above), runs on PHP 8.4+ and MySQL 8.0+, and requires no additional libraries. It’s like an "X-ray" for your database: safe, fast, and intuitive. If you’re a beginner, don’t worry — we’ll go through everything step by step, from installation to export nuances. If you’re an experienced developer, you’ll appreciate the clean code and ease of customization.

In this article, we’ll cover the full journey: from understanding why it’s needed to practical examples. Ready? Let’s get started!

Why Do Admins and Developers Need a DB Analysis Tool?

Cotonti is a great CMS, but its database can be complex for newcomers. Imagine: you added a plugin, and suddenly the site slows down. Why? Maybe a new table conflicts with an existing one? Or a field in the users table has the wrong type, and data isn’t saving? Without a visual tool, you’re forced to write SQL queries manually or dig through documentation.

DB Structure Viewer solves these issues:

• For administrators: Quick inspection — and you know how many rows are in the pages table, what fields are in users (and whether email has an index for search). No need for phpMyAdmin — everything is in the Cotonti admin panel.
• For developers: Exporting structure to JSON or PHP gives a ready template for documentation. Want to migrate data to a new server? CSV file — import in seconds. Plus, viewing data by ID helps debug: "Why can’t user ID 123 see the post?"

In short, this plugin saves hours of work. If you’ve ever spent time wondering "what is this table?", DB Structure Viewer is your lifesaver. Now let’s see how it works under the hood.

Key Features: What Can the Plugin Do?

DB Structure Viewer is not just a "list of tables". It’s a full-featured tool with three tabs that combines viewing, analysis, and export. Let’s break it down point by point so even a beginner can understand.

1. Automatic Table Discovery and Display

The plugin immediately scans your database and shows only your tables (with the "cot_" prefix). No clutter — just what’s needed for Cotonti. For each table, you see:

• Name: Short name without prefix (e.g., "users").
• Number of fields: How many columns in the table.
• Engine: InnoDB or MyISAM — important for performance.
• Number of rows: How many records (useful for monitoring data growth).

It’s like a "treasure map" — one glance, and you know where everything is.

2. Detailed Table Field Analysis

Select a table from the dropdown — and the plugin shows every field with full details:

• Field name: E.g., "user_name" or "page_title".
• Data type: varchar(255), int(11), text — to understand what’s stored.
• NULL: Can the field be empty? (YES/NO).
• Key: PRIMARY, UNIQUE, INDEX — to understand relationships.
• Default value: What’s inserted if nothing is specified.
• Extra: auto_increment or on update.

Perfect for debugging: "Why isn’t the email field indexed? That’s why search is slow!"

3. View Real Data

Not just theory — the plugin shows live data:

• First 10 rows: Quick look at content (without dumping the whole database).
• Specific row by ID: Enter an ID — see only that row. Useful for checking: "What’s in user 42’s profile?"

Data is displayed in a clean table with HTML escaping (so it doesn’t break).

4. Export: Flexible and Secure

Export is the heart of the plugin. Choose tables, format, and data mode:

• Structure only: Table and field names (for documentation).
• 10 rows: For testing or examples.
• All rows: Full dump (except PHP — too much data).

Formats:

• JSON: For APIs or JavaScript.
• SQL: For backup or migration.
• CSV: For Excel, data analysis.
• PHP Array: For code (only with small data).

File is saved in export/ and downloaded automatically. Plus — logs: "Who exported, when, what?"

5. Logging and History

If logging is enabled, every export is recorded in a table. The "Logs" tab is a journal:

• File, format, number of tables.
• Whether data was included.
• Date and time.

With pagination — convenient for large logs.

Directory Structure: How the Plugin Is Organized

The plugin follows Cotonti standards — everything is logical and predictable. Here’s the file tree:

plugins/dbviewstructure/
├── dbviewstructure.setup.php          # Main plugin registration file
├── dbviewstructure.tools.php          # Admin panel logic (tabs, forms)
├── inc/
│   └── dbviewstructure.functions.php  # Functions: database work, export
├── tpl/
│   └── dbviewstructure.tools.tpl      # HTML interface template
├── export/                            # Export files folder, set permissions to 755
├── lang/
│   ├── dbviewstructure.ru.lang.php    # Russian translations
│   └── dbviewstructure.en.lang.php    # English translations
└── setup/
   ├── dbviewstructure.install.sql    # SQL to create logs table
   └── dbviewstructure.uninstall.sql  # SQL to remove table

Detailed File Description

dbviewstructure.setup.php — Plugin "entry point". Contains metadata: name, version, author, license. Cotonti reads this file to register the plugin. Also sets up the export/ folder with 755 permissions (read/write for owner).

dbviewstructure.tools.php — Admin panel core. Handles GET/POST requests: determines tab (structure/export/logs), loads data, renders template. Contains export logic: format/table/data mode validation. Uses XTemplate for HTML output.

inc/dbviewstructure.functions.php — Plugin "brain". Contains functions:

• dbview_get_tables() — scans database, returns table list.
• dbview_get_table_info() — for one table: fields, engine, data.
• dbview_export_tables() — exports structure + 10 rows.
• dbview_export_tables_full() — full export of all rows.

tpl/dbviewstructure.tools.tpl — Interface template. Cotonti XTemplate: blocks for tabs, tables, forms. Bootstrap 5 for styling, responsive.

lang/dbviewstructure.ru.lang.php and en.lang.php — Translations. All text in $L['key'] — change file, change entire interface.

setup/dbviewstructure.install.sql — Creates cot_dbviewstructure_logs table for export history. Indexes for fast search.

setup/dbviewstructure.uninstall.sql — Removes table on uninstall (does not touch your export/ data).

export/ — Auto-created folder. Created on install, files saved here.

Installation: Step-by-Step for Beginners

If you’re new to Cotonti, don’t worry — installation is as simple as copying files.

1. Download the archive from GitHub: go to repository, download ZIP.
2. Unpack into your site’s plugins/ folder. Should result in plugins/dbviewstructure/ with all files.
3. Check permissions: The export/ folder must be writable. In SSH, run:

chmod 775 plugins/dbviewstructure/export/
chown www-data:www-data plugins/dbviewstructure/export/

4. (www-data is the web server user; replace with yours).
5. Install the plugin: Go to admin → Plugins → Install. Find "DB Structure Viewer", click Install.
6. Verify: Go to Other → DB Structure Viewer. If all is OK — you’ll see the table list.

If error "Tables not found" — check prefix in config.php (Cot::$db_x). If database is empty — plugin shows nothing.

Configuration: What Can Be Changed?

After installation, go to Plugins → DB Structure Viewer → Settings. Three parameters:

• export_path: Path to export folder. Default: plugins/dbviewstructure/export/. Change if you want to store elsewhere (e.g., /backup/db/).
• log_enabled: Enable/disable logs. Turn on — every export is recorded (useful for audit).
• max_rows_per_page: How many log entries per page. 10 for small sites, 50 for large.

Save — and the plugin is ready. No complex settings — everything is simple.

Usage: Step-by-Step Guide

The plugin is in the admin panel under Other → DB Structure Viewer. Three tabs, each solving a task.

"Structure" Tab: Explore the Database

1. Table list: Open the tab — see a table with columns:
   • Table: Name (without "cot_").
   • Fields: Number of columns (e.g., 15 for users).
   • Engine: InnoDB (fast) or MyISAM (old).
   • Rows: How many records (e.g., 5000 users).
2. Select table: Below — form with dropdown (all tables) and "Row ID" field (optional).
3. Click "Show":
   • Fields: Table with full field details (name, type, NULL, key, default, extra).
   • Data: If ID entered — one row. Otherwise — first 10. In a clean table with HTML escaping.

Example: Select "users", ID 1 — see first user’s profile. Useful for debugging: "Why is the password hashed wrong?"

"Export" Tab: Save Data

1. Select format: Dropdown: JSON (for API), SQL (backup), CSV (Excel), PHP Array (code).
2. Data mode: Radio buttons:
   • Structure only: Table and field names (small file).
   • 10 rows: + sample data (for testing).
   • All rows: Full dump (caution with large databases!).
3. Select tables: Checkboxes (with "Select all").
4. Click "Export":
   • File saved in export/.
   • "Download" button appears (file deleted after download).

Example: Select "pages" + "users", CSV + all rows — get .csv for Excel analysis.

"Logs" Tab: Track History

If logs enabled — here’s the journal:

• File: Name (e.g., db_export_20251031_1430.csv).
• Format: JSON, SQL, etc.
• Tables: How many selected.
• Data: Yes/No (10 rows or all).
• Date: When exported.

Pagination (10 entries per page). Click file — download (if not deleted).

Export Formats: What to Choose and Why?

Export is the main feature. Here’s the breakdown:

• JSON: Universal. Structure + data in JSON — ideal for APIs or JS. Example: {"users": {"fields": [...], "data": [...]}}. For "all rows" — large file, but readable.
• SQL: For backup. Generates CREATE TABLE + INSERT. Full dump — ready .sql for restore. Warning: "all rows" = huge file!
• CSV: For analysis. First row — headers, then data. Import to Excel/Google Sheets. For "all rows" — millions of rows, but Excel handles it (with limits).
• PHP Array: For code. return ['users' => [...]]; — paste into config. Only for "structure" or "10 rows" (PHP can’t handle millions in var_export()).

Tip: For large databases — CSV + "10 rows" for testing, SQL for full.

Security: Why the Plugin Won’t Break Your Database

Security is a priority. Here’s how it’s protected:

• Access: Only admins (cot_auth('plug', 'dbviewstructure', 'A')).
• SQL: Prepared statements (? + parameters), identifier escaping (dbview_quote_identifier()).
• Validation: Format, tables, ID checks (intval).
• Errors: Logged in Cotonti (cot_log()), no leaks.
• Files: In export/ (not root), deleted after download.
• No AJAX: Pure PHP — no JS vulnerabilities.

The plugin does not modify data — only reads and exports.

Technical Details: Under the Hood

Requirements

• Cotonti: Siena v.0.9.26+ (XTemplate, cot_* functions).
• PHP: 8.4+ (JSON, PDO).
• MySQL: 8.0+ (InnoDB/MyISAM).
• Permissions: 775 on export/.

How Export Works

1. dbview_get_tables() — scans database (SHOW TABLES LIKE 'cot_%').
2. dbview_get_table_info() — for table: SHOW FULL COLUMNS + SELECT * LIMIT 10.
3. dbview_export_tables() — generates file by format.
4. For "all rows" — dbview_export_tables_full() (no LIMIT).

Logs in cot_dbviewstructure_logs (with indexes for search).

Localization

All text in $L[] — change in lang/*.lang.php.

Possible Improvements: What Can Be Added?

The plugin is already solid, but here are ideas for enhancement:

• YAML export: If PHP extension installed, add yaml_emit().
• Field search: Search bar by name/type.
• Database comparison: "Before/after" — for migrations.
• Import: Reverse export — upload SQL/JSON.
• Charts: Table size diagram (with Chart.js).
• API: Endpoint /api/db/structure for external calls.
• Cache: Store structure in Redis for 1 hour.
• PostgreSQL: Adapt for another DB.

If needed — fork and add!

License

BSD 3-Clause License
Copyright (c) 2025, webitproff
All rights reserved.

GitHub: webitproff/cot-dbviewstructure Version: 1.1.2 Date: October 31, 2025

Support

Questions, issues, and plugin discussion on the forum

Offer the developer a task or job