Forums

How to set initial value of auto-incremented key in db.xml

Permalink
I'd like my primary key to include at least nine digits, which can't be too tough. Will a default value for the primary key in my db.xml do the job? For example...

<?xml version="1.0"?>
<schema version="0.3">
    <table name="btTable">
        <field name="key" type="I">
            <key/>
            <autoincrement/>
            <default value="500000000"/>
        </field>
        ...
        ...
    </table>
</schema>


I'd prefer something like that over "ALTER TABLE btTable AUTO_INCREMENT=500000000."
McCormick
 
McCormick replied on at Permalink Reply
McCormick
Well, that doesn't work. Instead, I clipped auto increment from the key and used a date string like the following.

packages/my_big_package/blocks/my_block/db.xml
<?xml version="1.0"?>
<schema version="0.3">
    <table name="btTable">
        <field name="key" type="C" size="12"><key></key></field>
        ...
        ...
    </table>
</schema>



packages/my_big_package/blocks/my_block/view.php
<form method="post" action="<?php  echo $this->action('form_save_entry'); ?>">
    ...
    ...
    <input type="hidden" name="key" value="<?php  echo date('ymjHis',strtotime(Loader::helper('date')->getSystemDateTime())); ?>" />
</form>


packages/my_big_package/blocks/my_block/controller.php
...
function action_form_save_entry(){
    ...
    ...
    // if(count($errors)){
        // $this->set('response',t('Uh oh, you focked up.'));
        // $this->set('errors',$errors);
    // }else{
        $key = $_POST['key'];
        ...
    // }
    ...
}
...


FYI, WordPress provides a handy guide plus examples on formatting date and time in PHP right here - codex.wordpress.org/Formatting_Date_and_Time
shahroq replied on at Permalink Best Answer Reply
shahroq
When you install the package, you can insert a dummy row with id=500000000, this way after installation your next record will have proper nine digits id.
McCormick replied on at Permalink Reply
McCormick
Outstanding, thanks shahroq.
shahroq replied on at Permalink Reply
shahroq
While we are on subject, i tried to insert a row into the table on package install, is there any way to insert data via db.xml file? i tried this one, but it does not seems working:
<data table="myTable">
    <record>
        <field1>data 1</field1>
        <field2>data 2</field2>
    </record>
</data>
McCormick replied on at Permalink Reply
McCormick
That would be ideal.

Thanks to your suggestion, I logged into my DB and inserted the dummy row manually, which worked out just fine. I installed the package, added the block to my page and then, before I tested out the block, inserted the dummy into my $btTable with an ID of 500000001. The test on the front end created a second entry with an ID of 500000002. That's right on.

Here's a start from C5's Contest block on how to create the dummy on install:

packages/contest/controller.php
$contestPageType=CollectionType::getByHandle('contest');
    if( !$contestPageType || !intval($contestPageType->getCollectionTypeID()) ){
        $data['ctHandle'] = 'contest';
        $data['ctName'] = t('Contest');        
        $contestPageType = CollectionType::add($data, $pkg);        
        $contestPageType->assignCollectionAttribute($contestExpiresAttrKey);
        $contestPageType->assignCollectionAttribute($contestDoesntExpireAttrKey);            
    }
    $contestEntryPageType=CollectionType::getByHandle('contest_entry');
    if( !$contestEntryPageType || !intval($contestEntryPageType->getCollectionTypeID()) ){        
        $data['ctHandle'] = 'contest_entry';
        $data['ctName'] = t('Contest Entry');
        $contestEntryPageType = CollectionType::add($data, $pkg);
    }
    //install single page types
Viewing 15 lines of 44 lines. View entire code block.