0 votes

This may be a bug, but being a new user of Skipper, I'm hoping it's user error, so I am posting this to "How To".

I am using Skipper 3.2.14.1430, Doctrine DBAL v2.8.0, and MySQL v14.14.

Using YML ORM files, I have gotten my database to be completely in sync with Skipper. However, because of the issue with Gedmo Timestampable, I have to manually remove the dashes in order for this to work. After reading this forum, I decided to try XML ORM files as I learned that YML will soon be deprecated in Doctrine.

When I simply changed the Skipper setting to output as XML, Doctrine now reports numerous SQL updates. I found this to be disturbing, but let me continue.

An issue I found is with the default value of a nullable string field. In Skipper, under the General Properties of the field, if "not-null" is set to false (in other words, nullable is true), and default is set to NULL, the resulting XML in the ORM file will be:

<field name="label" type="string" length="50" nullable="true">
  <options>
    <option name="default">NULL</option>
  </options>
</field>

When I run the doctrine command:

bin/console doctrine:schema:update --dump-sql

The resulting SQL will shows:

ALTER TABLE network CHANGE label label VARCHAR(50) DEFAULT 'NULL';

Notice, NULL is wrapped in quotes.

If I try leaving the default option blank in Skipper the resulting XML is:

<field name="label" type="string" length="50" nullable="true">
  <options>
    <option name="default"/>
  </options>
</field>

So now when I run the doctrine command, the resulting SQL is:

ALTER TABLE network CHANGE label label VARCHAR(50) DEFAULT '';

Notice it will not set the field with a default empty string.

The only way I have found to get the result I am wanting, is to manually remove the options block from the ORM file.

<field name="label" type="string" length="50" nullable="true">
</field>

Now the resulting SQL is:

ALTER TABLE network CHANGE label label VARCHAR(50) DEFAULT NULL;

Notice, NULL is no longer wrapped in quotes, but is a literal NULL value. This is what I want.

Is there any way to have Skipper create the correct XML ORM file for me?

in How To & Manuals by (160 points)
edited by

1 Answer

+1 vote
Best answer

Hi Rob,

Is there any reason why you need to enter "NULL" to options? As you mentioned, if you leave "default" without value and do not check NN (not-null), Skipper and Doctrine correctly generates result SQL query.

Option- default serves only for special cases where you need to enter string default value to SQL command.

It's redundant to enter "NULL" to defaults, because NULL is already default value.

by Skipper developer (141k points)
selected by

Ludek,
Thanks for the response. However, this is the problem. When I leave default blank, and not-null set to false, the exported XML looks like this:

<field name="label" type="string" length="255" nullable="true">
  <options>
    <option name="default"/>
  </options>
</field>

Which causes the SQL to look like this:

ALTER TABLE status CHANGE label label VARCHAR(255) DEFAULT '';

So somehow Skipper is causing what should be default NULL (no quotes) to be default empty string instead.

enter image description here

This is strange.

Why there is

<options> <option name="default"/> </options>

in the first place. When you delete default value at all, this element shouldn't be stored at all.

Can you please send me your project to [email protected]?

I just tried it here and everything works as expected.

<?xml version="1.0"?>
  <entity name="SampleEntity">
    <id name="id" type="integer">
      <generator strategy="AUTO"/>
    </id>
    <field name="name" type="string" length="255" nullable="true"/>
  </entity>
</doctrine-mapping>

Can you please check how looks label field XML element in Skipper project file?

    <entity name="\SampleEntity" local-name="SampleEntity" namespace="\" uuid="14576a8c-ba2d-4202-8efc-62edaf0a8c2e">
      <field name="id" type="integer" required="true" unique="true" primary="true" auto-increment="true" uuid="f8ac0b4e-dd64-4a6c-bd32-f5c00ba736fe"/>

>>> something like this:
  <field name="name" type="string" size="255" uuid="2cff8ce0-bf7e-4382-b030-
<<<

0d6dfdfc0226"/>
    </entity>

Here is what mine looks like. I removed the other fields, associations, etc. from this. Let me know if you need the full file.

<entity name="\Bundle\SchemaBundle\Entity\Status" local-name="Status" namespace="\Bundle\SchemaBundle\Entity" uuid="0ed62c3f-7707-4c86-868a-704de1bc6f81">
  <field name="label" type="string" size="255" default="" enum-values="" uuid="ae8356a6-9b2b-4b19-b63c-f7b15b3305a2">
    <orm-attributes>
      <attribute name="options"/>
    </orm-attributes>
  </field>

Interesting.

Can I ask, is it an imported project from XML, or manually created through Skipper?

Because there is several issues which should never happen. The first thing is empty "default" attribute. There is no way how to enter empty value in this form.

When the empty value is entered in Skipper, Skipper automatically removes it.

Also, when there is no child for "options", Skipper will automatically remove it.

The same for attribute enum-values. It's also in invalid state. This is definitely not created through Skipper, right?

Ludek,

You are correct. We did not create this from scratch in Skipper. We imported it. We have 92 entities in this database with many relationships so it would be very challenging to create this from the start.

Is there anything I can do to get it to function correctly?

Rob

And what import logic did you use? Some of your own or one available in Skipper (Doctrine scheme import, MySQL workbench import,...?)

I used Skipper (File -> Import to Project -> Import ORM Schema File). At the time, my ORM files were in YML, so that's how Imported it. Once I get everything completely in sync, we decided to switch to XML. So I exported as XML and now this issue has come up.

This is the correct approach. Maybe some bug in YAML importer.

Can you please share yaml definition of Status entity? (please send it to [email protected]).

And regarding your current model. Do you want us to fix the importer and re-import your model again, or did you already did some changes and need to fix current model?

I'm emailing this to you now. There may not be a problem with your importer as I see now that I have options set in the yml:

label:
  type: string
  length: 255
  nullable: true
  options:
    default: NULL

It's interesting though that when using yml doctrine:schema:update does not show any changes. And in my database label is default null (literal not string) as it should be. But in XML, it wants to update NULL to a string.

no problem. we will fix the default: NULL import so this will not occur again.

We can update importer tomorrow so you can import it again.

The other solution is to manually remove (through mass search and replace) all of these strings:

default=""
enum-values=""

This will fix the issue too.

Great! Thanks Ludek

Fix is available in 3.2.15.1464 beta
https://support.skipper18.com/402/downloads-skipper-beta

This fix completely resolved my issue. This is what I did:

  1. I downloaded the 3.2.15.1464 beta
  2. I deleted the Skipper file
  3. I started a new project by importing my YML ORM files
  4. I changed the format from YML to XML
  5. I deleted all of the YML files
  6. I exported the XML ORM files
  7. I ran doctrine:schema:update --dump-sql
  8. I then used the outputted SQL to correct any configuration issues I should have had in my ORM files to begin with.

Thanks for your timely help!

Perfect. I'm glad it's working now.

And thanks for your step-by-step description. It's exactly the correct way.