Joomla! Issue Tracker | Joomla! CMS #7378 - Add create table syntax for mysqli database importer

Closed
8 Dec 2015
Medium
Build: staging
# 7378
Diff
wilsonge:importcreatefix

Success

Success continuous-integration/travis-ci/pr The Travis CI build passed Details
Success JTracker/HumanTestResults Human Test Results: 2 Successful 0 Failed. Details

User tests: Successful: Unsuccessful:

wilsonge
8 Jul 2015

This is called in https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/importer.php#L214 When using the database importer

Note the database import and export is NOT used anywhere in core so you will have to design a script to test this if you want to test this (you'll also need to ensure #7373 is applied to test it)

This probably needs to be added to the PostgreSQL importer as well - but that's for another PR.

Votes

# of Users Experiencing Issue

1/1

Average Importance Score

5.00

wilsonge - open - 8 Jul 2015

wilsonge - change - 8 Jul 2015

Status

New

⇒

Pending

joomla-cms-bot - change - 8 Jul 2015

Labels

Added: ?

brianteeman - comment - 8 Jul 2015

Please provide a script so that this can be tested. Untested PR should
never be merged

On 8 July 2015 at 12:41, George Wilson notifications@github.com wrote:

This is called in
https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/importer.php#L214
When using the database importer

Note the database import and export is NOT used anywhere in core so you
will have to design a script to test this if you want to test this.

This probably needs to be added to the PostgreSQL importer as well - but

that's for another PR.

You can view, comment on, or merge this pull request online at:

#7378
Commit Summary

Add create table syntax for mysqli database importer

File Changes

M libraries/joomla/database/importer/mysqli.php https://github.com/joomla/joomla-cms/pull/7378/files#diff-0 (40)

Patch Links:

https://github.com/joomla/joomla-cms/pull/7378.patch

https://github.com/joomla/joomla-cms/pull/7378.diff

—
Reply to this email directly or view it on GitHub
#7378.

Brian Teeman
Co-founder Joomla! and OpenSourceMatters Inc.
http://brian.teeman.net/

wilsonge - comment - 8 Jul 2015

It can't be any more broken than it is now :P We are calling a function that doesn't exist lol. But yah I'll try and draw something together (this was for a work project so I'll need to do it later)

mbabker - comment - 8 Jul 2015

This should also be added to JDatabaseImporterPdomysql since it isn't inheriting from JDatabaseImporterMysqli. Also unit tests could be practical here if we're testing converting an XML schema to a database query.

zero-24 - change - 8 Jul 2015

Category

⇒

Libraries SQL

wilsonge - comment - 8 Jul 2015

It should - but like the Postgres for another PR - I haven't tested that nor got a test bed set up for that. Unit tests are doable - your right - I'll see what I can spin up (although technically we shouldn't be testing protected methods like the one I'm adding)

mbabker - comment - 8 Jul 2015

It's MySQL, it should work the same way; all you need to do is enable the PDO extension in your local install if it isn't already and you can validate the behavior on that.

The Framework version of the iterator has a stronger test base than this code, so I'd with that as a reference for the unit tests.

I would exclude the engine part completely if there isn't a way for it to be set via the dumped XML instead of force the engine. Remember also that InnoDB isn't enabled by default on all MySQL installations. Granted, the CMS core requirements call for InnoDB to be present, but this feature isn't checked for support nor does the core use it. Also keep in mind that the engine requirement was an application level decision; the database API (and inherently the Framework code) do not force the engine.

wilsonge - comment - 8 Jul 2015

I know - but what should work and what does work are different things. I'm just contributing for this driver because I have a client project set up using this code.

I've dumped the engine setting code as per your recommendation

alikon - comment - 19 Jul 2015

i was able to export a table

$result =   JFactory::getDbo()->getExporter()->from('#__content')->asXml();

but was unable to import
what should be the call ??

 JFactory::getDbo()->getImporter()->from(JFile::read(JPATH_ROOT . '/logs/contentx.xml'))->asXml()

don't work for me

so i've made as usual a dirty hack
i've defined an import() method on import.php
to invoke mergeStructure() wich should call xmlToCreate()

/**
     *  function to import the data structure.
     *
     * @return  string
     *
     * @since   11.1
     * @throws  Exception if an error is encountered.
     */
    public function import()
    {

        // Get the format.
        switch ($this->asFormat)
        {
            case 'xml':
            default:
                $buffer = $this->mergeStructure();
                break;
        }
        return $buffer;
    }

and now with this i 'm able to call the new xmlToCreate()

$result =   JFactory::getDbo()->getImporter()->from(JFile::read(JPATH_ROOT . '/logs/contentx.xml'))->asXml()->import();

so i've discovered that composite index like this one from #__content table

KEY `idx_featured_catid` (`featured`,`catid`),

are not managed cause of Seq_in_index

attached the xml exported in the first test step

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <database name="">
  <table_structure name="#__contenty">
   <field Field="id" Type="int(10) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
   <field Field="asset_id" Type="int(10) unsigned" Null="NO" Key="" Default="0" Extra="" />
   <field Field="title" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" />
   <field Field="alias" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" />
   <field Field="introtext" Type="mediumtext" Null="NO" Key="" Extra="" />
   <field Field="fulltext" Type="mediumtext" Null="NO" Key="" Extra="" />
   <field Field="state" Type="tinyint(3)" Null="NO" Key="MUL" Default="0" Extra="" />
   <field Field="catid" Type="int(10) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
   <field Field="created" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
   <field Field="created_by" Type="int(10) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
   <field Field="created_by_alias" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" />
   <field Field="modified" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
   <field Field="modified_by" Type="int(10) unsigned" Null="NO" Key="" Default="0" Extra="" />
   <field Field="checked_out" Type="int(10) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
   <field Field="checked_out_time" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
   <field Field="publish_up" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
   <field Field="publish_down" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
   <field Field="images" Type="text" Null="NO" Key="" Extra="" />
   <field Field="urls" Type="text" Null="NO" Key="" Extra="" />
   <field Field="attribs" Type="varchar(5120)" Null="NO" Key="" Extra="" />
   <field Field="version" Type="int(10) unsigned" Null="NO" Key="" Default="1" Extra="" />
   <field Field="ordering" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
   <field Field="metakey" Type="text" Null="NO" Key="" Extra="" />
   <field Field="metadesc" Type="text" Null="NO" Key="" Extra="" />
   <field Field="access" Type="int(10) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
   <field Field="hits" Type="int(10) unsigned" Null="NO" Key="" Default="0" Extra="" />
   <field Field="metadata" Type="text" Null="NO" Key="" Extra="" />
   <field Field="featured" Type="tinyint(3) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
   <field Field="language" Type="char(7)" Null="NO" Key="MUL" Extra="" />
   <field Field="xreference" Type="varchar(50)" Null="NO" Key="MUL" Extra="" />
   <key Table="#__contenty" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Null="" Index_type="BTREE" Comment="" />
   <key Table="#__contenty" Non_unique="1" Key_name="idx_access" Seq_in_index="1" Column_name="access" Collation="A" Null="" Index_type="BTREE" Comment="" />
   <key Table="#__contenty" Non_unique="1" Key_name="idx_checkout" Seq_in_index="1" Column_name="checked_out" Collation="A" Null="" Index_type="BTREE" Comment="" />
   <key Table="#__contenty" Non_unique="1" Key_name="idx_state" Seq_in_index="1" Column_name="state" Collation="A" Null="" Index_type="BTREE" Comment="" />
   <key Table="#__contenty" Non_unique="1" Key_name="idx_catid" Seq_in_index="1" Column_name="catid" Collation="A" Null="" Index_type="BTREE" Comment="" />
   <key Table="#__contenty" Non_unique="1" Key_name="idx_createdby" Seq_in_index="1" Column_name="created_by" Collation="A" Null="" Index_type="BTREE" Comment="" />
   <key Table="#__contenty" Non_unique="1" Key_name="idx_featured_catid" Seq_in_index="1" Column_name="featured" Collation="A" Null="" Index_type="BTREE" Comment="" />
   <key Table="#__contenty" Non_unique="1" Key_name="idx_featured_catid" Seq_in_index="2" Column_name="catid" Collation="A" Null="" Index_type="BTREE" Comment="" />
   <key Table="#__contenty" Non_unique="1" Key_name="idx_language" Seq_in_index="1" Column_name="language" Collation="A" Null="" Index_type="BTREE" Comment="" />
   <key Table="#__contenty" Non_unique="1" Key_name="idx_xreference" Seq_in_index="1" Column_name="xreference" Collation="A" Null="" Index_type="BTREE" Comment="" />
  </table_structure>
 </database>

wilsonge - comment - 19 Jul 2015

What's wrong with calling mergeStructure directly? It's been a public function since 3.4.2 https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/importer.php#L164

alikon - comment - 24 Jul 2015

nothing, only me that don't read code carefully

testing export

$result =   JFactory::getDbo()->getExporter()->from('#__content')->asXml();

works

testing import

$result =  JFactory::getDbo()->getImporter()->from(JFile::read(JPATH_ROOT . '/logs/contentx.xml'))->asXml()->mergeStructure();

works for new tables with no composite indexes

wilsonge - comment - 25 Jul 2015

I just added the fix for composite keys - can you try again please @alikon

f4e6e2a 25 Jul 2015

Add create table syntax for mysqli database importer

alikon - comment - 25 Jul 2015

it works....

wilsonge - comment - 25 Jul 2015

Woohoo thanks!!

alikon - comment - 25 Jul 2015

we need the import/export data side too...

wilsonge - comment - 25 Jul 2015

Yah. For someone else to work on :p I'm just fixing a missing function - not reworking the entire import and export functionality :p

pollen8 - test_item - 8 Dec 2015 - Tested successfully

pollen8 - comment - 8 Dec 2015

I have tested this item successfully on f4e6e2a

Works well with mySQLi

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/7378.}

pollen8 - comment - 8 Dec 2015

@alikon if it worked for you could you add a successful test please?

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/7378.}

pollen8 - comment - 8 Dec 2015

Test:
create a file in /logs/contentx.xml
paste in the content and save:

<?xml version="1.0"?>
<mysqldump>
    <database name="">
        <table_structure name="#__contenty">
            <field Field="id" Type="int(10) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
            <field Field="asset_id" Type="int(10) unsigned" Null="NO" Key="" Default="0" Extra="" />
            <field Field="title" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" />
            <field Field="alias" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" />
            <field Field="introtext" Type="mediumtext" Null="NO" Key="" Extra="" />
            <field Field="fulltext" Type="mediumtext" Null="NO" Key="" Extra="" />
            <field Field="state" Type="tinyint(3)" Null="NO" Key="MUL" Default="0" Extra="" />
            <field Field="catid" Type="int(10) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
            <field Field="created" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
            <field Field="created_by" Type="int(10) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
            <field Field="created_by_alias" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" />
            <field Field="modified" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
            <field Field="modified_by" Type="int(10) unsigned" Null="NO" Key="" Default="0" Extra="" />
            <field Field="checked_out" Type="int(10) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
            <field Field="checked_out_time" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
            <field Field="publish_up" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
            <field Field="publish_down" Type="datetime" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" />
            <field Field="images" Type="text" Null="NO" Key="" Extra="" />
            <field Field="urls" Type="text" Null="NO" Key="" Extra="" />
            <field Field="attribs" Type="varchar(5120)" Null="NO" Key="" Extra="" />
            <field Field="version" Type="int(10) unsigned" Null="NO" Key="" Default="1" Extra="" />
            <field Field="ordering" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
            <field Field="metakey" Type="text" Null="NO" Key="" Extra="" />
            <field Field="metadesc" Type="text" Null="NO" Key="" Extra="" />
            <field Field="access" Type="int(10) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
            <field Field="hits" Type="int(10) unsigned" Null="NO" Key="" Default="0" Extra="" />
            <field Field="metadata" Type="text" Null="NO" Key="" Extra="" />
            <field Field="featured" Type="tinyint(3) unsigned" Null="NO" Key="MUL" Default="0" Extra="" />
            <field Field="language" Type="char(7)" Null="NO" Key="MUL" Extra="" />
            <field Field="xreference" Type="varchar(50)" Null="NO" Key="MUL" Extra="" />
            <key Table="#__contenty" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Null="" Index_type="BTREE" Comment="" />
            <key Table="#__contenty" Non_unique="1" Key_name="idx_access" Seq_in_index="1" Column_name="access" Collation="A" Null="" Index_type="BTREE" Comment="" />
            <key Table="#__contenty" Non_unique="1" Key_name="idx_checkout" Seq_in_index="1" Column_name="checked_out" Collation="A" Null="" Index_type="BTREE" Comment="" />
            <key Table="#__contenty" Non_unique="1" Key_name="idx_state" Seq_in_index="1" Column_name="state" Collation="A" Null="" Index_type="BTREE" Comment="" />
            <key Table="#__contenty" Non_unique="1" Key_name="idx_catid" Seq_in_index="1" Column_name="catid" Collation="A" Null="" Index_type="BTREE" Comment="" />
            <key Table="#__contenty" Non_unique="1" Key_name="idx_createdby" Seq_in_index="1" Column_name="created_by" Collation="A" Null="" Index_type="BTREE" Comment="" />
            <key Table="#__contenty" Non_unique="1" Key_name="idx_featured_catid" Seq_in_index="1" Column_name="featured" Collation="A" Null="" Index_type="BTREE" Comment="" />
            <key Table="#__contenty" Non_unique="1" Key_name="idx_featured_catid" Seq_in_index="2" Column_name="catid" Collation="A" Null="" Index_type="BTREE" Comment="" />
            <key Table="#__contenty" Non_unique="1" Key_name="idx_language" Seq_in_index="1" Column_name="language" Collation="A" Null="" Index_type="BTREE" Comment="" />
            <key Table="#__contenty" Non_unique="1" Key_name="idx_xreference" Seq_in_index="1" Column_name="xreference" Collation="A" Null="" Index_type="BTREE" Comment="" />
        </table_structure>
    </database>
</mysqldump>

Add this to index.php:
$result = JFactory::getDbo()->getImporter()->from(file_get_contents(JPATH_ROOT . '/logs/contentx.xml'))->asXml()->mergeStructure();

refresh your site

Check your db - you should see a new table #__contenty with the corresponding fields and keys

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/7378.}

cheesegrits - test_item - 8 Dec 2015 - Tested successfully

cheesegrits - comment - 8 Dec 2015

I have tested this item successfully on f4e6e2a

Tested as per @pollen8 instructions, works, table created, structure is correct.

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/7378.}

Bakual - reference | c118da7 - 8 Dec 15

Bakual - merge - 8 Dec 2015

Bakual - change - 8 Dec 2015

Status	Pending	⇒	Closed
Closed_Date	0000-00-00 00:00:00	⇒	2015-12-08 20:00:42
Closed_By		⇒	Bakual

Bakual - close - 8 Dec 2015

Bakual - change - 8 Dec 2015

Milestone

Added:

wilsonge - head_ref_deleted - 8 Dec 2015

Eighke - comment - 16 May 2016

@wilsonge You forgot to add the method on PDO Importer. :(

Bakual - comment - 16 May 2016

@Eighke Can you open a new issue for that? It gets lost on a merged PR.

Eighke - comment - 17 May 2016

@Bakual Done :)

Add a Comment

Older
Newer

Joomla! Issue Tracker - CMS

[#7378] - Add create table syntax for mysqli database importer

Votes

that's for another PR.

Add a Comment