[Typo3-dev] New feature in 4.0: Reference Index Table
Brice Bernard
brikou at gmail.com
Sat Dec 17 19:08:53 CET 2005
Great, we will next be able to make some cleanup in the fileadmin folder
:-)...
Happy xMAS too
2005/12/17, Kasper Skårhøj <kasper2005 at typo3.com>:
>
> Hi TYPO3 Developers.
>
> >From the saturday-pre-christmas-lab of TYPO3, I have used my electronic
> paper
> and scissors the last week to cut you some cool features for christmas
> fun:
>
>
> Databases like MySQL allows you to specify "an index" over a number of
> rows.
> The point of the index is to allow quick look ups which doesn't involve
> scanning the whole table. Essentially, the information in a database index
> table is a condensed redundant reflection of selected "real" content in
> the
> actual table rows. The index is automatically kept up-to-date during any
> change made to the database so the user has nothing to worry about in
> terms
> of consistency.
>
> Now, TYPO3 also have an index table which is a redundant reflections of
> all
> relations (db / file / softrefs + traversing flexforms) found in
> tables/field
> registered in the $TCA. The main motivation to introduce this table is to
> provide an information base for the up-coming integrity check functions of
> version 4.0. However it quickly shows its usefulness in many other cases
> as
> well. For instance: In TemplaVoila you can "unlink" content elements from
> the
> page structure but you can't safely delete them because what if some other
> page in the system also refers to that element? With the new reference
> index
> its quick to detect any such references and if none are found the system
> could delete the element right away. Example:
>
> mysql> SELECT tablename,recuid FROM sys_refindex WHERE
> ref_table="tt_content"
> AND ref_uid=39;
> +-----------+--------+
> | tablename | recuid |
> +-----------+--------+
> | pages | 20 |
> | pages | 8 |
> +-----------+--------+
> 2 rows in set (0.00 sec)
>
> This query quickly tells us that the element "tt_content:39" is referenced
> on
> two pages, namely 20 and 8. Since page 8 is the parent page of the element
> TemplaVoila now knows that another reference is found on page 20! This had
> been completely unrealistic to discover otherwise, except if a full search
> through the page tree had been done.
>
>
> VARIOUS EXAMPLES:
> Another application of the reference table can remedy the typical rants
> over
> the comma separated reference lists in TYPO3. For example, backend users
> list
> references to their usergroups by "1,3,4". This makes it very hard to
> create
> a reverse lookup to find which backend users are members of usergroup "1".
> That is now possible (not taking subgroups into account though!):
>
> mysql> SELECT recuid FROM sys_refindex WHERE tablename="be_users" AND
> ref_table="be_groups" AND ref_uid=1;
> +--------+
> | recuid |
> +--------+
> | 2 |
> | 10 |
> +--------+
> 2 rows in set (0.00 sec)
>
> It turns out that two records from the "be_users" table has a reference to
> "be_groups:1".
>
>
>
> Want to know which backend users has this group as their "main" (first
> listed)
> usergroup? Just use the sorting field:
>
> mysql> SELECT recuid FROM sys_refindex WHERE tablename="be_users" AND
> ref_table="be_groups" AND ref_uid=1 AND sorting=0;
>
>
>
> Wanna know which records might contain a reference to the HTML template
> file
> "fileadmin/templates/cmf2005.html"?
>
> mysql> SELECT tablename,recuid FROM sys_refindex WHERE ref_table="_FILE"
> AND
> ref_string="fileadmin/templates/cmf2005.html";
> +------------------------+--------+
> | tablename | recuid |
> +------------------------+--------+
> | tx_templavoila_tmplobj | 3 |
> | tx_templavoila_tmplobj | 4 |
> | tx_templavoila_tmplobj | 5 |
> | tx_templavoila_tmplobj | 7 |
> | tx_templavoila_tmplobj | 9 |
> | tx_templavoila_tmplobj | 21 |
> | tx_templavoila_tmplobj | 23 |
> | tx_templavoila_tmplobj | 26 |
> | tx_templavoila_tmplobj | 31 |
> | tx_templavoila_tmplobj | 30 |
> | tx_templavoila_tmplobj | 28 |
> | tx_templavoila_tmplobj | 24 |
> | tx_templavoila_tmplobj | 32 |
> | tx_templavoila_tmplobj | 34 |
> | tx_templavoila_tmplobj | 36 |
> +------------------------+--------+
> 15 rows in set (0.00 sec)
>
> .... 15 TemplaVoila Template Objects did! Now you know you should NOT
> delete
> this file!
>
>
>
> Wanna know if any TypoScript records contain references in their setup or
> constants fields to "fileadmin/menuoftypes.php"?
>
> mysql> SELECT recuid,field,softref_key FROM sys_refindex WHERE
> tablename="sys_template" AND ref_table="_FILE" AND
> ref_string="fileadmin/menuoftypes.php";
> +--------+--------+-------------+
> | recuid | field | softref_key |
> +--------+--------+-------------+
> | 32 | config | TStemplate |
> | 37 | config | TStemplate |
> | 56 | config | TStemplate |
> | 57 | config | TStemplate |
> +--------+--------+-------------+
> 4 rows in set (0.00 sec)
>
> In this case you can see the references were found in the "config" field
> (TypoScript Setup field) and it was found by the softreference parser
> named
> "TStemplate". Conclusion: The reference index even contains "soft
> references"
> - references to files and records located in a plain text field!
>
>
>
>
> Now, find all tt_content elements where a "<LINK 1>" tag is found with a
> link
> to a page with id "1":
>
> mysql> SELECT tablename,recuid,field FROM sys_refindex WHERE
> tablename="tt_content" AND ref_table="pages" AND ref_uid=1 AND
> softref_key='typolink_tag' ;
> +------------+--------+----------+
> | tablename | recuid | field |
> +------------+--------+----------+
> | tt_content | 8 | bodytext |
> | tt_content | 110 | bodytext |
> | tt_content | 128 | bodytext |
> | tt_content | 128 | bodytext |
> | tt_content | 128 | bodytext |
> | tt_content | 128 | bodytext |
> | tt_content | 128 | bodytext |
> | tt_content | 372 | bodytext |
> | tt_content | 437 | bodytext |
> +------------+--------+----------+
> 9 rows in set (0.00 sec)
>
>
>
> Making a true JOIN based sql query between backend users and backend
> groups
> (something otherwise impossible due to the comma separated reference
> principle)?
>
> mysql> SELECT be_users.username, be_groups.uid,
> be_groups.title,sys_refindex.sorting FROM be_users,be_groups,sys_refindex
> WHERE sys_refindex.tablename="be_users" AND
> sys_refindex.ref_table="be_groups" AND be_users.uid=sys_refindex.recuid
> AND
> be_groups.uid=sys_refindex.ref_uid ORDER BY be_users.username;
> +------------------+-----+---------------------+---------+
> | username | uid | title | sorting |
> +------------------+-----+---------------------+---------+
> | be_test | 1 | be_testgroup | 0 |
> | news_article | 6 | Common editor group | 0 |
> | news_article | 4 | Article Writers | 1 |
> | news_editor | 3 | Editor | 1 |
> | news_editor | 6 | Common editor group | 0 |
> | news_news | 2 | News Writers | 1 |
> | news_news | 6 | Common editor group | 0 |
> | reviewer | 6 | Common editor group | 0 |
> | reviewer | 2 | News Writers | 1 |
> | reviewer | 4 | Article Writers | 2 |
> | template_be_test | 1 | be_testgroup | 0 |
> | tony | 7 | tony | 0 |
> +------------------+-----+---------------------+---------+
> 12 rows in set (0.00 sec)
>
> There you go!
>
>
> What about finding an email address? How do I find all occurencies on the
> site?
>
> mysql> SELECT tablename,recuid,field FROM sys_refindex WHERE softref_key =
> "email" AND ref_string="mail at foo.bar.com";
> +------------+--------+--------------+
> | tablename | recuid | field |
> +------------+--------+--------------+
> | tt_content | 457 | bodytext |
> | tt_content | 457 | imagecaption |
> | tt_content | 467 | bodytext |
> | tt_content | 475 | bodytext |
> | tt_content | 475 | bodytext |
> | tt_content | 558 | bodytext |
> | tt_content | 558 | imagecaption |
> | tt_content | 568 | bodytext |
> | tt_content | 576 | bodytext |
> | tt_content | 576 | bodytext |
> | tt_content | 650 | bodytext |
> | tt_content | 650 | imagecaption |
> | tt_content | 660 | bodytext |
> | tt_content | 668 | bodytext |
> | tt_content | 668 | bodytext |
> | tt_content | 735 | bodytext |
> | tt_content | 735 | imagecaption |
> | tt_content | 740 | bodytext |
> | tt_content | 744 | bodytext |
> | tt_content | 744 | bodytext |
> +------------+--------+--------------+
> 20 rows in set (0.00 sec)
>
> Like that!
>
>
>
>
> POSSIBLE APPLICATIONS:
> OK, so we can proceed like this for a long time. This should give you all
> ideas for a bunch of nice applications. Here are some ideas I suggest:
>
> - Global Data Integrity Check [I'm going to do this in January for version
> 4.0]
> - TemplaVoila clean-up [Robert is going to do that for 4.0]
> - In a context menu for a database record or file from Web > Filelist,
> imagine
> a function that will show us all references to this element! Could be very
> useful to know but until now has been impossible to get an exact answer.
> - Web > Filelist: Show "flag" for all files and directories in the list if
> they are referenced somewhere in the system. Click the "flag" and see
> where
> (or let it be a mouse-over function?). Would also make it easy to remove
> unused files.
> - A search (and replace?) function using the reference index: Could search
> for
> typolink-tags pointing to some page and let you change the value across
> the
> whole system. Even more useful: For email addresses!
> - Delete file or record: Displays a warning if there are references to the
> element! Or it could even reject the deletion unless those references were
> removed?
> - One could also imagine a hook in the t3lib_refindex class that lets
> other
> application provide indexing services; That could be the maintenance of a
> word index used for searching.
> - Another usage of such a hook could be generating a similar reference
> table
> for all values in flexform XML: This could enable exact look-ups of
> information inside flexform XML which is currently impossible, almost like
> a
> sort of "XPath" surrogate.
>
>
>
> TECHNICAL INFORMATION:
>
> 1) Creation, validation and correction of the Index is done in the
> extension
> "lowlevel", accessed through "Tools > DB check", selecting "Manage
> Reference
> Index".
> 2) Runtime updates are created with TCEmain and as long as TCEmain is used
> for
> ANY manipulation of relations to database records and files the index
> integrity should be intact.
> 3) t3lib_refindex is used to maintain the index.
>
> Table structure:
>
> mysql> show columns from sys_refindex;
> +-------------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+-------------+------+-----+---------+-------+
> | hash | varchar(32) | | PRI | | |
> | tablename | varchar(40) | | MUL | | |
> | recuid | int(11) | | | 0 | |
> | field | varchar(40) | | | | |
> | flexpointer | tinytext | | | | |
> | softref_key | varchar(30) | | | | |
> | softref_id | varchar(40) | | | | |
> | sorting | int(11) | | | 0 | |
> | ref_table | varchar(40) | | MUL | | |
> | ref_uid | int(11) | | | 0 | |
> | ref_string | varchar(40) | | | | |
> +-------------+-------------+------+-----+---------+-------+
> 11 rows in set (0.00 sec)
>
>
> TODO:
> - (For 4.0) In "Help > About Modules" screen we might want to look if the
> index is empty and if so, present a link to "Tools > DB check" tool so the
> index can be created initially for updated sites?
> - (For 4.0) Improve the "Tools > DB check" tool?
> - Improve MySQL database indexes on the "sys_refindex" table if needed for
> various applications?
> - Make t3lib_refindex available in the frontend scope for integration in
> plugins that manipulate the database on their own and need to update the
> reference index. Actually, I'm inclined to think we should rather think
> about
> how to provide t3lib_tcemain for the frontend instead...!
>
>
>
> Merry Christmas everyone!
>
> --
> - kasper
>
> -----------------
> "A 'please' would be nice", John Travolta, Pulp Fiction
> _______________________________________________
> Typo3-dev mailing list
> Typo3-dev at lists.netfielders.de
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev
>
--
- Brice -
More information about the TYPO3-dev
mailing list