Perplexity SPACE to FIX-IMPROVE SQL sentences for third party modules :-)

Developing for Dolibarr
1

Hello, i have created a SPACE on Perplexity (AI) to FIX/IMPROVE my SQL sentences for my third party modules for Dolibarr. And i share here the URL to be used by anyone:

https://www.perplexity.ai/spaces/fix-sql-for-dolibarr-K6caLLU5SmqcqCCradLEKA

How to use it

Very simple:

  1. only paste your SQL sentence
  2. the chatbot will return the “fixed/improved” sentence

Privacy

  • None :sweat_smile: : your requests will be visible to anyone.
  • The good new: this is not something important to not be saw by other developers.

The magic

This system prompt:

You are an SQL optimization expert specializing in Dolibarr ERP module development. Your task is to convert MySQL CREATE TABLE statements to follow Dolibarr best practices and ensure cross-database compatibility (MySQL, MariaDB, PostgreSQL).

## Context
Dolibarr uses a database abstraction layer (DoliDB) that automatically converts MySQL syntax to the target database during module installation. The DDLCreateTable() method handles compatibility, but following best practices ensures cleaner code and better maintainability.

## Required Transformations

### 1. Table Definition
- Add `IF NOT EXISTS` clause for safe reinstallation
- Remove backticks from table and column names (Dolibarr handles them)
- Keep the `llx_` prefix (Dolibarr standard)

### 2. Column Types
- Keep `int(11)` for rowid, foreign keys, and large counters
- Use `tinyint`, not `tinyint(1)` or `tinyint(2)` (for small integers 0-255 range)
- Use `smallint`, not `tinyint(3)` or `tinyint(4)` (for medium integers 0-65535 range)
- Keep `varchar(n)` as-is
- Keep `text` and `timestamp` as-is
- Keep `decimal(precision,scale)` as-is

### 3. Collation and Character Sets
- Remove all inline `COLLATE utf8_unicode_ci` declarations
- Database-level collation settings handle this globally
- Dolibarr manages character sets at the database level

### 4. Default Values
- Use unquoted numbers: `DEFAULT 1` not `DEFAULT '1'`
- Keep `DEFAULT NULL` for nullable columns
- Keep `DEFAULT CURRENT_TIMESTAMP` for timestamp columns
- Keep string defaults with quotes: `DEFAULT 'value'`

### 5. Primary Key
- Format as: `PRIMARY KEY (rowid)` at the end
- Remove extra spaces
- No backticks around column name

### 6. Auto-Increment
- Use `AUTO_INCREMENT` (uppercase, no display value)
- Place on the rowid column definition line

### 7. Engine Declaration
- End with: `) ENGINE=innodb;`
- Remove `AUTO_INCREMENT=1` (unnecessary)
- Remove trailing spaces before semicolon

### 8. Formatting
- Use consistent indentation (2 spaces per level)
- One column definition per line
- Align column definitions for readability
- Remove double spaces

## Example Transformation

### Before:

CREATE TABLE `llx_module_table` (
  `rowid` int(11) NOT NULL auto_increment,
  `entity` tinyint(4) NOT NULL DEFAULT '1',
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `description` text COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `status` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY  (`rowid`)
) ENGINE=innodb AUTO_INCREMENT=1 ;


### After:

CREATE TABLE IF NOT EXISTS llx_module_table (
  rowid int(11) NOT NULL AUTO_INCREMENT,
  entity smallint NOT NULL DEFAULT 1,
  name varchar(128) NOT NULL DEFAULT '',
  description text,
  status tinyint NOT NULL DEFAULT 0,
  PRIMARY KEY (rowid)
) ENGINE=innodb;


## Your Task

Apply all these transformations to the provided SQL CREATE TABLE statement. Maintain the original column order and structure, only applying the formatting and compatibility fixes listed above. Output only the corrected SQL statement without explanations unless requested.

Note: respond in the user language to their request (spanish, french, german, etc.)

Note

It also fixes syntax errors. In fact, I initially built this “complex” bot to fix one of my broken SQL sentences. As you know, these kinds of bots always recommend improving certain details… so I decided to enhance everything possible.

Ultimately, I discovered so many interesting improvements and fixes for my SQL sentence that I decided to crystallize this “intelligence” in a detailed prompt to be easily reused by simply copying and pasting any SQL sentence.

Indeed, it also repairs “ALTER TABLE” statements.

Enjoy it. Or improve it, please!

4 Likes
2

This prompt works well. AI is powerful. I always use the Claudemodels for coding in Perplexity; it gives me better results than the standard Sonar model. Here is my prompt:

Write a SQL query that, given a vendor ID (socid=829), returns all products this vendor sells to us. Including a current stock, virtual stock, and the number of sales/usage in manufacturing orders over the last 365 days

SELECT 
  p.rowid AS product_id,
  p.ref AS product_ref,
  p.label AS product_label,
  pfp.ref_fourn AS supplier_ref,
  pfp.unitprice AS supplier_unit_price,
  pfp.tva_tx AS vat_rate,
  COALESCE(SUM(ps.reel), 0) AS current_stock,
  COALESCE(p.stock, 0) AS virtual_stock,
  COALESCE(sales.total_qty_sold, 0) AS qty_sold_365d,
  COALESCE(mo_usage.total_qty_consumed, 0) AS qty_consumed_mo_365d,
  COALESCE(sales.total_qty_sold, 0) + COALESCE(mo_usage.total_qty_consumed, 0) AS total_usage_365d
FROM llx_product_fournisseur_price pfp
INNER JOIN llx_product p ON p.rowid = pfp.fk_product
LEFT JOIN llx_product_stock ps ON ps.fk_product = p.rowid
LEFT JOIN (
  SELECT 
    cd.fk_product,
    SUM(cd.qty) AS total_qty_sold
  FROM llx_commandedet cd
  INNER JOIN llx_commande c ON c.rowid = cd.fk_commande
  WHERE c.fk_statut >= 1
    AND c.datec >= DATE_SUB(NOW(), INTERVAL 365 DAY)
  GROUP BY cd.fk_product
) sales ON sales.fk_product = p.rowid
LEFT JOIN (
  SELECT 
    sm.fk_product,
    SUM(ABS(sm.value)) AS total_qty_consumed
  FROM llx_stock_mouvement sm
  WHERE sm.origintype = 'mo'
    AND sm.value < 0
    AND sm.datem >= DATE_SUB(NOW(), INTERVAL 365 DAY)
  GROUP BY sm.fk_product
) mo_usage ON mo_usage.fk_product = p.rowid
WHERE pfp.fk_soc = 829
  AND p.entity IN (0, 1)
GROUP BY 
  p.rowid, p.ref, p.label, p.stock, 
  pfp.ref_fourn, pfp.unitprice, pfp.tva_tx,
  sales.total_qty_sold, mo_usage.total_qty_consumed
ORDER BY p.ref;

Error in the above syntax unknow column ‘c.datac’ in where clause

2nd try it was fixed

SELECT
    p.rowid AS product_id,
    p.ref AS product_ref,
    p.label AS product_label,
    pfp.ref_fourn AS supplier_ref,
    pfp.unitprice AS supplier_unit_price,
    COALESCE(SUM(ps.reel), 0) AS current_stock,
    COALESCE(p.stock, 0) AS virtual_stock,
    COALESCE(sales.total_qty_sold, 0) AS qty_sold_365d,
    COALESCE(mo_usage.total_qty_consumed, 0) AS qty_consumed_mo_365d,
    COALESCE(sales.total_qty_sold, 0) + COALESCE(mo_usage.total_qty_consumed, 0) AS total_usage_365d
FROM llx_product_fournisseur_price pfp
         INNER JOIN llx_product p ON p.rowid = pfp.fk_product
         LEFT JOIN llx_product_stock ps ON ps.fk_product = p.rowid
         LEFT JOIN (
    SELECT
        cd.fk_product,
        SUM(cd.qty) AS total_qty_sold
    FROM llx_commandedet cd
             INNER JOIN llx_commande c ON c.rowid = cd.fk_commande
    WHERE c.fk_statut >= 1
      AND c.date_creation >= DATE_SUB(NOW(), INTERVAL 365 DAY)
    GROUP BY cd.fk_product
) sales ON sales.fk_product = p.rowid
         LEFT JOIN (
    SELECT
        sm.fk_product,
        SUM(ABS(sm.value)) AS total_qty_consumed
    FROM llx_stock_mouvement sm
    WHERE sm.origintype = 'mo'
      AND sm.value < 0
      AND sm.datem >= DATE_SUB(NOW(), INTERVAL 365 DAY)
    GROUP BY sm.fk_product
) mo_usage ON mo_usage.fk_product = p.rowid
WHERE pfp.fk_soc = 829
  AND p.entity IN (0, 1)
GROUP BY
    p.rowid, p.ref, p.label, p.stock,
    pfp.ref_fourn, pfp.unitprice, pfp.tva_tx,
    sales.total_qty_sold, mo_usage.total_qty_consumed
ORDER BY p.ref;

it took me 2 minutes make this query I can now fully concentrate on the correctness of data in stead of spending hours on getting the MySQL query right.

1 Like