Joomla! Issue Tracker | Joomla! CMS #8615 - adding Sub_part info to mySQLi db import/export code

Closed
21 May 2017
Medium
Build: staging
# 8615
Diff
pollen8:db-import-export-keylengths

Failure

Failure continuous-integration/travis-ci/pr The Travis CI build failed Details
Pending JTracker/HumanTestResults Human Test Results: 2 Successful 1 Failed. Details

User tests: Successful: Unsuccessful:

pollen8
8 Dec 2015

Allows for export and import of keys with length values

To test:

Run this sql query to create a test table

CREATE TABLE `dbkeytest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   `fileupload` text,
  PRIMARY KEY (`id`),
  KEY `fb_join_fileupload_INDEX` (`fileupload`(10)) )

In index.php after:

$app = JFactory::getApplication('site');

add:

jimport('joomla.filesystem.file');
$res =  (string) JFactory::getDbo()->getExporter()->from('dbkeytest');
JFile::write(JPATH_SITE . '/tmp/dbkeytest.xml', $res);
echo $res; exit;

refresh your site and you should see a white page, view the page source and you should see.


<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <database name="">
  <table_structure name="dbkeytest">
   <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
   <field Field="fileupload" Type="text" Null="YES" Key="MUL" Extra="" />
   <key Table="dbkeytest" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Sub_part ="" Null="" Index_type="BTREE" Comment="" />
   <key Table="dbkeytest" Non_unique="1" Key_name="fb_join_fileupload_INDEX" Seq_in_index="1" Column_name="fileupload" Collation="A" Sub_part ="10" Null="YES" Index_type="BTREE" Comment="" />
  </table_structure>
 </database>
</mysqldump>

An xml file should also have been saved in /tmp/dbkeytest.xml.

Note that Sub_part ="10" has been added to the key 'fb_join_fileupload_INDEX'

The importer will also work IF you have this PR merged in as well
https://issues.joomla.org/tracker/joomla-cms/7378

To test:

Drop the table dbkeytest

Replace the test code you added to index.php with:

JFactory::getDbo()->getImporter()->from(file_get_contents(JPATH_SITE . '/tmp/dbkeytest.xml'))
		->asXml()->mergeStructure();
exit;

Refresh the browser and you should see that the db table dbkeytest has been re-created in the database and that the key 'fb_join_fileupload_INDEX' has the sub_part applied. ie if you run:

show create table dbkeytest;

the results should be

CREATE TABLE `dbkeytest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fileupload` text,
  PRIMARY KEY (`id`),
  KEY `fb_join_fileupload_INDEX` (`fileupload`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

43e8acb 8 Dec 2015

adding Sub_part info to mySQLi db import/export code, allows for export and import of keys with length values

pollen8 - open - 8 Dec 2015

pollen8 - change - 8 Dec 2015

Status

New

⇒

Pending

joomla-cms-bot - change - 8 Dec 2015

Labels

Added: ?

wilsonge - comment - 8 Dec 2015

Out of interest is this the same behaviour as when you do a xml export in mysql? Which is what these database dumps seem to be based on?

Also can you merge into/rebase on staging in your PR so the unit tests run please?

cheesegrits - test_item - 8 Dec 2015 - Tested successfully

cheesegrits - comment - 8 Dec 2015

I have tested this item successfully on 43e8acb

Output without patch has no Sub_part ...

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <database name="">
  <table_structure name="dbkeytest">
   <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
   <field Field="fileupload" Type="text" Null="YES" Key="MUL" Extra="" />
   <key Table="dbkeytest" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Sub_part ="" Null="" Index_type="BTREE" Comment="" />
   <key Table="dbkeytest" Non_unique="1" Key_name="fb_join_fileupload_INDEX" Seq_in_index="1" Column_name="fileupload" Collation="A" Null="YES" Index_type="BTREE" Comment="" />
  </table_structure>
 </database>
</mysqldump>

Output with patch is correct ...

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <database name="">
  <table_structure name="dbkeytest">
   <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
   <field Field="fileupload" Type="text" Null="YES" Key="MUL" Extra="" />
   <key Table="dbkeytest" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Sub_part ="" Null="" Index_type="BTREE" Comment="" />
   <key Table="dbkeytest" Non_unique="1" Key_name="fb_join_fileupload_INDEX" Seq_in_index="1" Column_name="fileupload" Collation="A" Sub_part ="10" Null="YES" Index_type="BTREE" Comment="" />
  </table_structure>
 </database>
</mysqldump>

Couldn't apply PR 7378 automagically (patch tool says it conflicts, which it doesn't really), but after adding the xmlToCreate() function from that PR by hand, the import test worked, with a structure of:

CREATE TABLE `dbkeytest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fileupload` text,
  PRIMARY KEY (`id`),
  KEY `fb_join_fileupload_INDEX` (`fileupload`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

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

0c9c7cb 11 Dec 2015

Merge branch 'staging' of https://github.com/joomla/joomla-cms into staging

joomla-cms-bot - comment - 11 Dec 2015

This PR has received new commits.

CC: @cheesegrits

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

pollen8 - comment - 11 Dec 2015

Out of interest is this the same behaviour as when you do a xml export in mysql?

Yes that's the SQL generated by Navicat when I export the table.

I believe I have updated the PR to staging - please let me know if I haven't its not something I have had a lot of experience with and could well have moofed it!

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

brianteeman - change - 12 Dec 2015

Category

⇒

SQL

Eighke - comment - 16 May 2016

@pollen8 Don't forget PDO importer / exporter. For some reason it doesn't extends mysqli.

mertozer94 - test_item - 1 Aug 2016 - Tested unsuccessfully

mertozer94 - comment - 1 Aug 2016

I have tested this item ? unsuccessfully on 0c9c7cb

I created the test table with the query and then i added the code but i get this error :
Fatal error: Method JDatabaseExporterMysqli::__toString() must not throw an exception in C:\xampp\htdocs\joomla\index.php on line 0

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

Curtista - test_item - 2 Aug 2016 - Tested successfully

Curtista - comment - 2 Aug 2016

I have tested this item ✅ successfully on 0c9c7cb

I followed the test steps which where given to test this patch and I have pretty much the same result that cheesegrits gets.

@icampus

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

brianteeman - comment - 2 Aug 2016

@pollen8

Can you please

Also can you merge into/rebase on staging in your PR so the unit tests run please?

Without that this will never b considered to be merged into core

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

toxic2302 - test_item - 2 Aug 2016 - Tested successfully

toxic2302 - comment - 2 Aug 2016

I have tested this item ✅ successfully on 0c9c7cb

With your test instructions, my output looks like cheesegrits and Curtista's output. I can export and import the dbkeyset table.

My output at the end of this instructions:

CREATE TABLE `dbkeytest` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `fileupload` text,
 PRIMARY KEY (`id`),
 KEY `fb_join_fileupload_INDEX` (`fileupload`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

@icampus

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