Database approach to verify customer project

There are several ways how to verify an existing project, in my current case for an update of TYPO3. Verifying the usage of extensions can be done easily by having a view in the database.

The database is in many, perhaps even most, Content Management Systems (CMS) the place where all the individual content is stored. In TYPO3 the images and media by themselves are not stored in the database directly but as references, technically still with a file-abstraction-layer (FAL) combined which enables an easy switch from one storage to another i.e from local storage to cloud-storage.

Also concerning usage of extensions database is the best information as all content-elements are stored together with the kind of plugins which are used inside an element.

Verifying the database for a website alone might not be enough as it's possible, that there are still plugins stored that are not used anymore long time. So combined with a look in the extension-manager the overview gets more complete and also is able to show in which pages plugins reside that are out of use but still active as content-elements (and though able to produce errors).

To give an impression about some basic verification I show the SQL-Code beside the result of one TYPO3-installation:

SELECT CType,list_type,deleted,hidden, count(*)
FROM tt_content
GROUP BY CType,list_type,deleted,hidden
ORDER BY CType,list_type,deleted,hidden;


The red surrounded areas in the screenshot show the usage of extensions and in the columns "deleted" and "hidden" is shown whether the elements are visible on the website or not.

Visibility is not only depending on these settings but also on the visibility of the pages where the plugins reside. Analyzing the pages themselves is quite easy by SQL but it still has to be verified if the pages are included in a visible page-structure respectively page-hierarchy, the latter verification is easier by the TYPO3-backend or a special script.

Here is the code to verify at least the pages where the plugins reside:

SELECT pages.uid,, pages.title, pages.deleted, pages.hidden,
     tt_content.CType, tt_content.list_type, tt_content.deleted, tt_content.hidden, count( * ) FROM tt_content, pages
WHERE pages.uid =
GROUP BY pages.uid, tt_content.CType, tt_content.list_type, tt_content.deleted, tt_content.hidden
ORDER BY pages.uid, tt_content.CType, tt_content.list_type, tt_content.deleted, tt_content.hidden

In  the data below I omitted "pages.title" to keep the customers individual data out of this article. Depending on the project, this list can get very huge as for each page several plugins can be listed, each with an own row in the list. So the best is to store it as CSV and open it with OpenOffice or MS-Excel. There you can sort and mark whatever you need to and also export for any report, i.e. as PDF-document.


Comments (0)

No comments found!

Write new comment

CAPTCHA image for SPAM prevention If you can't read the word, click here.