1

I am trying to set up a table with dbDelta() and one of the columns will have as default a serialized array with predefined values. The issue here is that dbDelta() explodes the query using semicolons to separate the queries. Is there any workaround for this? The charset I'm using for said column is utf8_bin.

$sql ="CREATE TABLE my_table_name (
    Id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    serialized_data varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'a:7:{i:0;b:1;i:1;b:1;i:2;b:1;i:3;b:1;i:4;b:1;i:5;b:1;i:6;b:1;}',
    PRIMARY KEY  (Id)  );"

dbDelta($sql);

I've already tried using \x3B instead of the character itself, but it is still parsed as a semicolon before being exploded so it doesn't work.

1
  • Please edit your question and paste in the relevant code (in this case, your dbDelta() call and the context to go with it).
    – Pat J
    Sep 29, 2015 at 18:14

1 Answer 1

0

You're right that the problem is a result of the explode on semi-colons. We can use the dbdelta_queries filter to fix it.

In this solution we're assuming that all queries start with CREATE, INSERT or UPDATE. If you have queries you're running through dbDelta where this isn't the case, the preg_match line will need adjusting or it won't work correctly.

add_filter( 'dbdelta_queries', 'my_dbDelta_queries' );

function my_dbDelta_queries( $queries ) {

    $toJoin = array();
    $start  = array();
    $count  = 0;
        foreach( $queries as $i => $query ) :
                if ( !preg_match( "/CREATE|INSERT|UPDATE( [^ ]*)/", $query ) ) :
                        if( !empty( $start ) ) :
                            $toJoin[$count] = array( $start[key( $start )] );
                            unset( $queries[key( $start )] );
                        endif;
                    $toJoin[$count][] = $query;
                    unset( $queries[$i] );
                    $start = array();
                else :
                    $start = array( $i => $query );
                    $count++;
                endif;
        endforeach;

        if( !empty( $toJoin ) ) :
                foreach( $toJoin as $join ) :
                    $queries[] = implode( ';', $join );
                endforeach;
        endif;
    return $queries;

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.