Forums

C5-8.2.1: Please help convert SQL to db.xml

Permalink
Hello,

Could anyone please help me convert the following SQL into the db.xml format:
$sql = "CREATE TABLE IF NOT EXISTS `Spam` (
                `ip_id` int(11) NOT NULL AUTO_INCREMENT,
                `ip` INT(32) UNSIGNED NOT NULL,
                `timestamp` VARCHAR(40) NOT NULL,
                PRIMARY KEY (`ip_id`),
                UNIQUE KEY (`ip`))";

I can't find any information how to make that UNIQUE KEY and INT(32). If I use type integer, it becomes INT(10). The <unique/> throws an exception.

Thank you.
linuxoid
 
hutman replied on at Permalink Reply
hutman
I think this should do it, here is the db.xml reference I use -https://legacy-documentation.concrete5.org/tutorials/creating-and-wo...

<?xml version="1.0"?>
<schema version="0.3">
    <table name="Spam">
        <field name="ip_id" type="I">
            <key />
            <unsigned />
        </field>
        <field name="ip" type="I8">
            <unsigned />
        </field>
        <field name="timestamp" type="C" size="40">
        </field>
    </table>
</schema>
linuxoid replied on at Permalink Reply
linuxoid
Hi.

That's an old schema. It's version 0.5 now.

What I found in the Doctrine XML documentation is that they have a variable type 'bigint'. But it's only int(20). Using size="32" doesn't make it INT(32).

And how do I set the 'ip' as 'UNIQUE KEY'?
MrKDilkington replied on at Permalink Reply
MrKDilkington
Hi linuxoid,

Community member mlocati has created a tool for converting AXMLS to Doctrine XML and normalizing and validating XML.
https://github.com/concrete5/doctrine-xml...
http://concrete5.github.io/doctrine-xml/...

There looks to be a 'unique="true"' property you could try.
http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/re...

I am not sure about your bigint question.
JohntheFish replied on at Permalink Reply
JohntheFish
That is decimal, not bits
linuxoid replied on at Permalink Reply
linuxoid
Thank you for the links, it worked:
<table name="btSpam">
    <field name="ip" type="bigint"><unsigned/><notnull/></field>
    <field name="timestamp" type="string" size="100"><notnull/></field>
    <index name="ip"><unique /><col>ip</col></index>
  </table>