.. ================================================== .. Header hierarchy .. == .. -- .. ^^ .. "" .. ;; .. ,, .. .. --------------------------------------------used to the update the records specified ------ .. Best Practice T3 reST: https://docs.typo3.org/m/typo3/docs-how-to-document/master/en-us/WritingReST/CheatSheet.html .. Reference: https://docs.typo3.org/m/typo3/docs-how-to-document/master/en-us/WritingReST/Index.html .. Italic *italic* .. Bold **bold** .. Code ``text`` .. External Links: `Bootstrap `__ .. Internal Link: :ref:`downloadButton` (default url text) or :ref:`Download Button` (explicit url text) .. Anker for internal link: .. _`download`: .. Add Image: .. image:: ./Images/a4.jpg .. .. Add image with caption: .. figure:: ./Images/black_dot.png .. :class: with-border .. :width: 20px .. Add Attachment (place in Documentation/_static): `Download CSV <_static/yourfile.csv>`__ .. .. Admonitions .. .. note:: .. important:: .. tip:: .. warning:: .. Color: (blue) (orange) (green) (red) .. .. Definition: .. some text becomes strong (only one line) .. description has to indented .. .. Code Block: .. code-block:: sql .. .. -*- coding: utf-8 -*- with BOM. .. include:: Includes.txt .. _`CodingGuideline`: Coding Guideline ================ .. note:: For regular QFQ users, these are just 'best practices'. For persons in our team, these are guidelines which we ask to follow. * 'T3 BE' means 'Typo3 Backend'. * The following shows some best practices. .. _`cg-ap-constants`: App Constants ------------- Definition of constants (reused on different places) is a good practice of structured programming. In QFQ, custom values can be set in STORE_SYSTEM, see :ref:`user-defined-constants-variables`. Set values: * Define constants in ``T3 BE > Settings > Configure Extensions > QFQ > Custom > ...`` * Dynamic values under ``T3 BE > Settings > Configure > Extensions > QFQ > Dynamic > ...`` .. _`cg-content-record`: QFQ content record ------------------ * In T3 BE, label the tt-content record in the *header field* with a title which describes the functionality and prefix the title with: * Regular content: ``[QFQ] ...`` * Content in the left column: ``[QFQ,L] ...`` * Content in english: ``[QFQ,E] ...`` * API Content: ``[QFQ,api] ...`` .. image:: ./Images/t3-be-tt-content-header.png * **Comment / Signature**: The first lines of the bodytext should be comments, explaining what the record does and list all passed variables. Optional variables are indicated by using STORE_EMPTY or STORE_ZERO: .. code-block:: sql # # Shows list of Persons living in {{country:SE}} # # {{country:SE}} - name of country # * **Comment Query**: best above the query, not inline (see above). * **Normalize**: A good practice is to define all possible STORE_SIP Parameter in a SQL SELECT at the beginning and copy them to STORE_RECORD: .. code-block:: sql normalize { # Normalize variables sql = SELECT '{{country:SE}}' AS _country # List selected persons per country sub { sql = SELECT p.name FROM Person AS p WHERE p.country LIKE '{{country:R}}' } } * **Indention nested queries**: Nested queries should be indented by 2 spaces (not tabs). * **Indention long query** * **80 chars**: If the query is longer than one row / 80 chars, split the query over several lines. * **Logical block**: Split each block of a query on a single row and indent them according to the block: * SELECT ... * [LEFT] JOIN * ON ... * WHERE ... * AND|OR ... * GROUP BY ... * ORDER BY ... * LIMIT ... * **SELECT Columns**: * **Column per row**: After the SELECT, put one column per row. This improves readability of the column names (=variable names). * **Coma**: Unusual, but handy: place the coma (column separator) at the beginning of the row. With this small trick, it's very easy to comment/uncomment a block of rows, without worrying about the correct number of coma's. .. _`cg-form`: QFQ Form -------- * Mandatory SIP parameter should to be mentioned in `Form.requiredNew` and/or `Form.requiredEdit`. This helps to understand a form functionality better. * Every form should show a descriptive title (shown to the user) to identify the task and current record. E.g. Not 'Person' but 'Person: John Doe'. * The record id is always offered as a `tooltip` on the `save button` (together with `created` and `modified`). * Put a ```` in the title, if a project/record/application id is important for the user. * If the title of a `FormElement` isn't descriptive enough, use `tooltip`, `note` or `extraButtonInfo` to explain to a user. * Often the length of a pill title if not sufficient, use a tooltip to give a more descriptive hint. .. _`cg-table-structure`: Table structure --------------- * Table names are written in camel-case with starting `upper` case letter. * Colum names are written in camel-case with starting `lower` case letter. * The first column is always ``id`` with ``auto increment`` and ``primary key``. * The last two columns are always * ``modified`` (datetime, current_timestamp(), ON UPDATE CURRENT_TIMESTAMP()) * ``created`` (datetime, current_timestamp()) * (Foreign) Keys * In our databases we mostly `don't` use hard coded foreign keys. * ``key`` column name: each table, referenced by another table column (key), should have an abbreviation. Such abbreviation becomes the column prefix together with `id` and optional a description. E.g. in table ``Book`` the column who points to a person (table `Person`, abbr. `p`) who is the book author, is named ``pIdAuthor``. Here ``p`` is the prefix, ``Id`` indicates this a key to a table, and ``Author`` gives the meaning of the column. .. _`cg-multi-purpose-tables`: Multi purpose tables & column keys ---------------------------------- There are many situations where only a few records per normalized table are given. In a clean database design, it is wished to create a table with a meaningful name for each use case. A side effect of this clean design is that you get a lot of tables in bigger databases and these tables often differ only in small parts. The result, many tables, lead to the situation that no one has an overview anymore what table supports what, and the programmer, who needs some new records to be stored, creates the next specialized table, and so on. Also the table names becomes longer and longer. Our best practice is somewhat strange, but very powerful and it becomes so handy, that we really appreciate the design. Table ``Grp`` ^^^^^^^^^^^^^ * Used to partition other tables. The meaning is similar the `MariaDB partitioning `__ but has nothing to do with it - both mean splitting the records of a table by a filter criteria, for MariaDB this has performance reasons, for our `best practice` it's flexibility. * In a table, just define a column ``grId``. Example: * In table ``Book`` you like to assign a book to a `category`. * For each `category` (Novel, Poem, Science, ...) create a record in table ``Grp``. * For better understanding, name the column not only ``grId`` but ``grIdCategory``. You might suggest to use an ENUM for book category - but think of: adding a new category by creating a new record can easily be done, but a changing a table definition need more effort like required privileges and documentation. Why not define a separate table ``BookCategory``? Maybe we need other categories too. Like different address categories, or cities per country and so on, this is an endless list. The ``Grp`` / partitioning concept solves it a general way.more Why the name `Grp`? Meaning is `group`, but `group` is a reserved SQL word, so ``Grp`` Extension 1: Parent Grp record * We define a column ``Grp.grId``. * This can be understand as grouping a set of records under a parent record. * Example: create a ``Grp`` record `Book categories`. Put the `id` of this record in each of the individual book categories Novel, Poem, Science, ... - this is the next partition, this time inside of the ``Grp`` table. Extension 2: Generic settings UI * Create a form for table ``Grp`` with an input for `name` and a FormElement `subrecord` for all it's child records. * Ready is your general settings store. Define as many settings , grouping, categories, ... as you like. .. _`grp-string-reference`: Extension 3: Use descriptive strings instead of hard coded id's * Create a column ``Grp.reference`` with an uniq index, nullable, default is 'null'. * Instead of selecting your subset of records by a fixed numeric id, use the reference. * Example: ``SELECT grCat.name FROM Grp AS grCat, Grp AS gr WHERE 'Book categories'=gr.reference AND gr.id=grCat.grId`` That's pretty cool, no hard coded id's anymore - nowhere. Column `reference` ------------------ * Main usage is already described in `grp-string-reference`_ * Add a column ``reference`` to each table, if it is necessary to select a specific record via application logic (=SQL statements). * Hint: In the FormElement for column ``reference`` set `FE.parameter.emptyMeansNull` - this will save NULL if there is nothing given. Entity Relation Model (ERD) --------------------------- .. image:: ./Images/Tutorial/tutorial-erd.png * All ERD Schemas are drawn with ``__ * `tutorial-erd.drawio <_static/tutorial-erd.drawio>`__ .. _`cg-sql-queries`: SQL Queries ----------- * Write all SQL keywords uppercase: ``SELECT Grp.name FROM Grp WHERE reference='Book categories'`` * AS '' SELCT ... FROM Grp AS grp .. _`cg-bpmn-workflow`: BPMN - Workflow --------------- .. _`cg-custom-css`: Custom CSS ---------- * QFQ comes with a set of predefined CSS classes :ref:`qfq-css-classes` and :ref:`qfq-icons`. * If further classes / functionality / icons are required, please create a ticket for the request - the main idea is to not expand custom.css, but to expand QFQ itself. So please report your requirements. .. _`cg-custom-javascript`: Custom Javascript ----------------- * If further Javascript is required, please create a ticket for the request - the main idea implement functionality into the QFQ core. Otherwise, the logic has to reimplemented every time from scratch. So please report your requirements.