[Typo3-dev] New feature in 4.0: Reference Index Table
Ries van Twisk
typo3 at rvt.dds.nl
Sat Dec 17 21:26:02 CET 2005
>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
>>
>>
>>
>>
Really good feature Kasper!
I always missed good relations in CMS systems based on mySql which is hard,
if not impossibly because of the lacking features in past mysql versions
and lot's of systems are based on old versions of mysql (3.x).
Looks like it also getting more and more importand to use typo's API
really well, otherwise lot's of things will break
like undo/ready, workspaces and this neat feature.
I have scanned also quite a number of extentions (including mine and I
am not fully up to speed using typo's full API).
but looking forward to use them all....
happy X-mass to ya all!
cheers,
Ries
More information about the TYPO3-dev
mailing list