Forums

Get block database schema

Permalink
Is there any way inside of concrete to get a block types database structure?
adz
 
INTcommunications replied on at Permalink Reply
INTcommunications
The blocks database structure is in the db.xml file - open it up in a text editor and it is pretty easy to see the structure. To see where they are go to your root and click on the folder concrete then blocks/ pick any core block to view - example /concrete/blocks/html/db.xml in a texteditor looks like this
<?xml version="1.0"?>
<schema version="0.3">
<table name="btContentLocal">
<field name="bID" type="I">
<key />
<unsigned />
</field>
<field name="content" type="X2">
</field>
</table>
</schema>

So the structure is tablename - btContentLocal
fields - bID ( key )
content
If there is 2 tables for the block - you may see them here but sometimes they are loaded with the package and then another table loaded with the block inside the package - that's where it gets a little confusing. In the Concrete documents for developers there is a link to the database definitions - i.e. type="X2"

Jim
adz replied on at Permalink Reply
adz
Hi Jim, thanks for taking the time to post a reply. But I'm looking for a way within the concrete system to be able to find out all the tables and fields that each block has.

At the moment I am doing:

$btInstalledArray = BlockTypeList::getInstalledList();
$internalBlockTypes = array();
$normalBlockTypes = array(); 
foreach($btInstalledArray as $_bt) { 
    if ($_bt->isInternalBlockType()) { 
        $internalBlockTypes[] = $_bt; 
    } else { 
        $normalBlockTypes[] = $_bt; 
    } 
}
foreach ($normalBlockTypes as $nbt) { 
    $schema = Database::getADOSChema(); 
    $sql = $schema->ParseSchema($nbt->getBlockTypePathDBXMLFile().'/'.$this->dbFile);
}


the getBlockTypePathDBXMLFile() function is a modified version of BlockType::getBlockTypePath(), so that it searches for the db.xml file in the package dir first, then the root/blocks, then finally the root/concrete/blocks, as the original only searches for the existence of the directory.

I then use the database ParseSchema function to find out everything about the tables, however the results are returned as ALTER TABLE tbl_name .... etc.

Anyone know of any quicker way of doing this?
INTcommunications replied on at Permalink Reply
INTcommunications
Sorry - wasn't clear just what level of knowledge you had. What would be the purpose of finding this info in code except to look at it. Why not just open the database and browse it? I am sure you have a good reason - just curious.

Jim
adz replied on at Permalink Reply
adz
No worries Jim, I have a tendency to kind of just expect people to know what I'm going on about! Haha.

I'm building a new package for a client of mine, and need to be able to get a list of the fields in other blocks.
JohntheFish replied on at Permalink Reply
JohntheFish
You can get it straight from the db table name with some SQL:
SHOW COLUMNS FROM MyTableName

DESC MyTableName


Run a GetAll and dump the resulting array from each of the above and you will see everything.
adz replied on at Permalink Reply
adz
I thought of doing this originally, but how can you be sure you got all the tables that are related to a block. You're then relying on either them creating a table with a prefix of bt possibly, or that they have put all tables that are related to the block into the $btExportTables array for the block controller.

Or are you suggesting that after getting the xml file, and working out the tabels that exist in it, then doing a show columns on that?
JohntheFish replied on at Permalink Reply
JohntheFish
I have used the latter. Its simple to pull table names from xml and the sql definitions of data types are easier to reverse-parse than adodb xml.

It works fine for addon blocks. With core blocks, the problem is they are all set up from one big xml file, so you need to rely on naming or proximity to associate secondary tables.

There is also the issue of blocks that interact with tables defined in a packages db.xml and tables from attributes. So the mapping of tables : blocks can be many : many and non exclusive.
adz replied on at Permalink Reply
adz
It's a shame there isn't a function inside of concrete that has a BlockType::getDBSchema()

I'll have a look at doing this later, cheers for the tip John!

Adam.