zhy88项目(console.php)

<?php

use Illuminate\Foundation\Inspiring;
/**
 * 修改boolean,boolean要改为int
 */
Artisan::command('get_boolean_list', function () {
    //Artisan::call('config:cache');

    $full_sql = "";
    $db = \DB::connection("test_pgsql");
    $sql = <<<EOT
select "table_name" ,"column_name","column_default" from "information_schema"."columns" where "table_catalog" ='zhy0803' and "table_schema" ='public' and data_type='boolean';
EOT;
;
    $res = $db->select($sql);
    foreach ($res as $item){
        $table_name = $item->table_name;
        $column_name = $item->column_name;
        $column_default= $item->column_default;
        $sing_sql = sprintf("alter table %s alter column \"%s\" type integer;
alter table %s alter column \"%s\" set default %s;
",$table_name,$column_name,$table_name,$column_name,$column_default == "true" ? 1 : 0);
        $full_sql .=$sing_sql;
    }

    print_r($res);
    print_r($full_sql);
    file_put_contents("c:/pgsql_boolean.sql",$full_sql);
});

Artisan::command('get_not_nul_list', function () {
    //Artisan::call('config:cache');

    $full_sql = "";
    $db = \DB::connection("test_pgsql");
    $sql = <<<EOT
select "table_name" ,"column_name","column_default" from "information_schema"."columns" where "table_catalog" ='zhy0803' and "table_schema" ='public' and is_nullable='NO' and "column_name" !='id';
EOT;
    ;
    $res = $db->select($sql);
    foreach ($res as $item){
        $table_name = $item->table_name;
        $column_name = $item->column_name;
        $sing_sql = sprintf("alter table %s alter \"%s\" drop not null;
",$table_name,$column_name);
        $full_sql .=$sing_sql;
    }

    print_r($res);
    print_r($full_sql);
    file_put_contents("c:/pgsql_not_null.sql",$full_sql);

    //alter table ims_zhyshop_sun_goods alter column uniacid type int USING uniacid::int;
});
/**
 * uniacid有varchar,要改为int
 */
Artisan::command('get_uniacid_varchar', function () {
    //Artisan::call('config:cache');

    $full_sql = "";
    $db = \DB::connection("test_pgsql");
    $sql = <<<EOT
select
    "table_name","column_name" 
from
    "information_schema"."columns"
where
    "table_catalog" = 'zhy0803'
    and "table_schema" = 'public'
    and "column_name" = 'uniacid'
    and "data_type" ='varchar'
EOT;
    ;
    $res = $db->select($sql);
    foreach ($res as $item){
        $table_name = $item->table_name;
        $column_name = $item->column_name;
        $sing_sql = sprintf("alter table %s alter column \"%s\" type int USING uniacid::int;
",$table_name,$column_name);
        $full_sql .=$sing_sql;
    }

    print_r($res);
    print_r($full_sql);
    file_put_contents("c:/pgsql_uniacid_varchar.sql",$full_sql);

    //alter table ims_zhyshop_sun_goods alter column "%s" type int USING uniacid::int;
});