Export SQL content

Content export

plugin Content IO also allows you to export your content to a SQL database. Currently, export only works on a MySQL or Oracle database.


How it works :

The SQL export will browse the structure of the defined content types and create the various associated tables. It will then browse the contents and populate these tables.
Here are the various export features:

  • A table is created for each type of content
  • For each piece of content data, it will create a column with its name, except for :
    • Geocode data: creates 2 columns (longitude_name and latitude_name)
    • User data: creates 2 columns (login_name and population_name)
    • Multilanguage data: creates a column for each language (nom_fr, nom_en, nom_it, etc.).
    • File and binary data: creates 4 columns (nom_data, nom_mimetype, nom_size, nom_lastmodified)
    • Composite data: creates composite data columns prefixed with the composite name
    • Multiple data: it will create another table with an identifier referencing the content table with column (PID_parentName, name, position)
      See parameter exportNoMultiValuedTable below.
    • Data type repeater: it will create another table with an identifier referencing the content table with column (id_repeater, PID_parentName, name1, name2, ...., position) where name1, name2 are the names of the repeater data.

Export does not support multiple geocode and multilingual data


Configuration :

You need to define your database SQL in the CMS configuration.
You can also define an administrator email . This is the person who will receive the export information SQL.


Start export :

There are two ways to launch the export:

  • Via the task scheduler in the CMS 



  • Use the "Export SQL content" button in the back office of the CMS

            

           Then confirm the export launch:

            

Configuration file : 

The SQL content export is linked to a content-export configuration file.xml :

<export exportNoMultiValuedTable="false" separator="," exportOnlyValidatedContent="false" prefix="AE">        
  <contents>        
     <content id='content-type.ExportContentType' name='export' />        
     <content id='content-type.ExportContentType2' name='export2' />        
     <content id='content-type.ExportTableRef' name='tableref' />        
  </contents>        
  <sql>        
     <oracle>        
     </oracle>        
     <mysql>        
        <long>INT</long>        
        <string>TEXT</string>        
     </mysql>        
  </sql>        
  <mapping-policy value="FULL" />        
  <reserved-words override="false">        
     <mysql>        
         <word name="ACCESSIBLE" alias="ACCESSIBLE_2"/>        
         ...        
         <word name="ZEROFILL" alias="ZEROFILL_2"/>        
     </mysql>        
     <oracle>        
         <word name="ACCESS" alias="ACCESS_2" />        
         ...        
         <word name="ZONE" alias="ZONE_2" />        
     </oracle>        
  </reserved-words>        
</export>        

 

Tag or Attribute 

 Description

exportNoMultiValuedTable

When exporting SQL, a table is created for each multiple data item. If this parameter is set to true, no table is created for these multiple values. Instead, all values will be concatenated in a single column.

 

Default: false

separator

This parameter is linked to exportNoMultiValuedTable. When exportNoMultiValuedTable is set to true, the different values of a multiple field are concatenated. These values are separated by a character defined by the separator attribute.

 

Default value: ", ".

exportOnlyValidatedContent

When this parameter is set to true, only the published (LIVE) version of the content is exported.

 

Default: false

 

Each project must be able to position the LIVE tag when validating content.
This is usually done by the plugin WEB org.ametys.web.workflow.ValidateContentFunction.

 prefix

All exported tables will begin with this prefix.

 

Default value : AmetysExport

 

It is advisable to set a prefix, as before each export, all tables beginning with this prefix are deleted. If the prefix is empty, the export will delete all tables in the SQL database.

 content

In each content tag, we define theid of the content type we want to export, and in name we define the name of the table associated with this content type. If you don't define anything for name, the default name of the content type will be chosen.

 

Default value: if no content tag is defined, all content types will be exported.

sql

These tags are used to define a SQL type for a content data type.
For example, above, we say that all data of type Long in Ametys will be of type INT for MySQL
Here are the different types that can be configured with their default values:
  • string: TEXT for MySQL and VARCHAR(4000) for Oracle
  • long: INT for MySQL and NUMBER for Oracle
  • boolean: TINYINT for MySQL and CHAR(1) for Oracle
  • double: DOUBLE for MySQL and NUMBER for Oracle
  • content : VARCHAR(512) for MySQL and VARCHAR(4000) for Oracle
  • richtext: MEDIUMTEXT for MySQL and VARCHAR(4000) for Oracle
  • date: DATE for MySQL and DATE for Oracle
  • datetime: DATETIME for MySQL and DATE for Oracle
  • file: BLOB for MySQL and BLOB for Oracle
  • binary: BLOB for MySQL and BLOB for Oracle

mapping-policy

Depending on the configuration of the various SQL databases, there may be problems with the size of table and column names.
That's why we provide 3 different name formatting rules:
  • FULL (default value): no processing at all
  • CAMELCASE: concatenate the different words in the name, deleting spaces and other characters and capitalizing each word.
  • FIRSTCHAR: concatenate the different words in the name, taking only the first 3 letters of the name.
reserved-words

This is the list of reserved words for MySQL or ORACLE, which can create problems when creating tables.
For example, if I have a "binary" data content, I'm going to create a column with the name "binary", and on an ORACLE database this will crash. So we add this word to the reserved words by choosing "binary_2" as an alias. So the column SQL will be called "binary_2".

 

By default, it includes all reserved words from MySQL and ORACLE. 

override

This attribute is linked to reserved-words and is used to indicate whether the words added override the default words or are added to them.

If override is set to true, this will override all reserved words in MySQL and ORACLE.

 

It defaults to false.

 

It's advisable to leave override set to false, otherwise there's a risk that the export won't work.

 

Back to top

Content IO