Symfony从SQL Server生成实体[导入]

Update1: I've tested this on another project running with mysql - i've deleted one entity and tried to "restore" it by importing the table, I'm getting the same error there as well. So this is not a mssql problem, it must be a config problem. What did I miss to configure?

Update2: When not using the filter, it is importing all and no errors shown. What is wrong with the filter?

I am trying to generate entities from an existing mssql database. For that, some information about what I'm using:

  • microsoft sql server 2012
  • Symfony 2.8 with php7
  • Driver in parameters.yml for db connection: sqlsrv (pdo not supported yet by mssql). The driver for sqlsrv (not pdo) can be downloaded here, I'm using the thread safe version.

Config.yml:

# Doctrine Configuration
doctrine:
  dbal:
    driver:   "%database_driver%"
    host:     "%database_host%"
    port:     "%database_port%"
    dbname:   "%database_name%"
    user:     "%database_user%"
    password: "%database_password%"

  orm:
    default_entity_manager: default
    auto_generate_proxy_classes: "%kernel.debug%"
    entity_managers:
      default:
        auto_mapping: true
        naming_strategy: doctrine.orm.naming_strategy.underscore

Connecting to the database with the values specified in dbal section is working, I can create a new entity in my code and run schema update command to create the corresponding table. On the other hand, when trying to generate an entity from an existing table - which is named "dbo.tblPlaces" - with the following command:

php app/console doctrine:mapping:import --force AppBundle xml --filter="tblPlaces"

(I've also tried "dbo.tblPlaces" within the filter), I keep getting the error:

Database does not have any mapping information.

I've read almost every other post I could find here on stackoverflow and official documentation of symfony regarding the error described above, but wasn't able to find a solution. Why do I keep getting this annoying error message? The table is very small and only consists of the following fields:

tbl.Places

I am going to put my two cents in here and tell you that only 70-80% of the necessary mapping information based on fields, indexes and foreign key constraints.

From the Docs,

Doctrine is able to convert approximately 70-80% of the necessary mapping information based on fields, indexes and foreign key constraints. Doctrine can't discover inverse associations, inheritance types, entities with foreign keys as primary keys or semantical operations on associations such as cascade or lifecycle events

I was facing the same problem with MsSQL and Symfony a while back.

I ended up with an alternative solution.

What I ended up with was that I created a stored procedure in MsSQL which generates the entities in the SQL server in the form of records. You can then just copy and paste this into your entity file and you will be able to generate working entities. It works perfectly for me and hopefully for you too.

You can download it from here https://github.com/dipens/W3_APP_GEN_DOCTRINE.sql/blob/master/W3_APP_GEN_Doctrine

Code here:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[W3_APP_GEN_Doctrine] @tablename varchar(255)
AS

WITH D AS (
select data_type + 
    case
        when data_type like '%text' or data_type like 'image' or data_type like 'sql_variant' or data_type like 'xml'
            then ''
        when data_type = 'float'
            then '(' + convert(varchar(10), isnull(numeric_precision, 18)) + ')'
        when data_type = 'numeric' or data_type = 'decimal'
            then '(' + convert(varchar(10), isnull(numeric_precision, 18)) + ',' + convert(varchar(10), isnull(numeric_scale, 0)) + ')'
        when (data_type like '%char' or data_type like '%binary') and character_maximum_length = -1
            then '(max)'
        when character_maximum_length is not null
            then '(' + convert(varchar(10), character_maximum_length) + ')'
        else ''
    end as CONDENSED_TYPE
    , 

    case 
        when data_type like '%text%' or data_type like '%char%' OR data_type ='uniqueidentifier'  OR data_type ='bigint'
            then 'string'
        when data_type ='int' or data_type ='smallint' or data_type ='tinyint'
            then 'integer'
        when data_type ='date'
            then 'datetime'
        when data_type ='bit'
            then 'boolean'
        when data_type ='decimal' 
            then 'string' --'decimal'
        when data_type ='datetime'
            then data_type
        when data_type ='double' OR data_type ='float'
            then 'float'
        when data_type ='money'
            then 'string' --'decimal'
        when data_type ='bigint'
            then 'string'
    end as DoctrineType


    ,object_id(table_name) AS TID ,C.*, SC.is_computed, SC.is_identity
from information_schema.columns C
INNER JOIN sys.columns SC ON object_id(table_name)=SC.object_id and C.COLUMN_NAME=SC.name
where table_name= @tablename

)
SELECT 
'/**' 
+ CHAR(13)+CHAR(10) + '* @var ' + IIF(D.DoctrineType='datetime','\'+D.DoctrineType,D.DoctrineType)
+ CHAR(13)+CHAR(10) + '*'
+ CHAR(13)+CHAR(10) + '* @ORM\Column(name="' + D.COLUMN_NAME + '", type="' + D.DoctrineType + '"' 
    + IIF(D.DoctrineType='string' and D.DATA_TYPE NOT IN ('uniqueidentifier','decimal','money','bigint'),', length=' + convert(varchar(25),D.CHARACTER_MAXIMUM_LENGTH),'')
    + IIF(D.DoctrineType='decimal',', precision=' + convert(varchar(25),D.NUMERIC_PRECISION) + ', scale=' + convert(varchar(25),D.NUMERIC_SCALE),'')
    + IIF(D.IS_NULLABLE='NO' and D.is_identity=0,', nullable=false','')
    + ')'
+ IIF(D.is_identity=1,CHAR(13)+CHAR(10) + '* @ORM\Id','')
+ IIF(D.is_identity=1,CHAR(13)+CHAR(10) + '* @ORM\GeneratedValue(strategy="AUTO")','')
+ CHAR(13)+CHAR(10) + '*/'
+ CHAR(13)+CHAR(10)
+ CHAR(13)+CHAR(10) + 'private $' + D.COLUMN_NAME  +';'
+ CHAR(13)+CHAR(10)

AS DOCTRINE
--,D.ORDINAL_POSITION
FROM D
--ORDER BY D.ORDINAL_POSITION

UNION ALL

SELECT
'/**' 
+ CHAR(13)+CHAR(10) + '* @return ' + IIF(D.DoctrineType='datetime','\'+D.DoctrineType,D.DoctrineType)
+ CHAR(13)+CHAR(10) + '*/'
+ CHAR(13)+CHAR(10) + 'public function get' + replace(D.COLUMN_NAME,'_','') +  '()'
+ CHAR(13)+CHAR(10) + '{'
+ CHAR(13)+CHAR(10) + ' return $this->' + D.COLUMN_NAME + ';'
+ CHAR(13)+CHAR(10) + '}'
+ CHAR(13)+CHAR(10)
+ CHAR(13)+CHAR(10)+ '/**' 
+ CHAR(13)+CHAR(10) + '* @param ' + IIF(D.DoctrineType='datetime','\'+D.DoctrineType,D.DoctrineType) + ' $' + D.COLUMN_NAME
+ CHAR(13)+CHAR(10) + '*/'
+ CHAR(13)+CHAR(10) + 'public function set' + replace(D.COLUMN_NAME,'_','') +  '('+ '$' + D.COLUMN_NAME + ')'
+ CHAR(13)+CHAR(10) + '{'
+ CHAR(13)+CHAR(10) + ' $this->' + D.COLUMN_NAME + '=' + '$' + D.COLUMN_NAME + ';'
+ CHAR(13)+CHAR(10) + '}'
+ CHAR(13)+CHAR(10)

--,D.ORDINAL_POSITION
FROM D