FreeSql.Generator PostgreSQL生成Model没有对uuid_generate_v4()默认值生成InsertValueSql

[466974367]

问题描述及重现代码：

PostgreSQL

CREATE TABLE "public"."t_word_segment" (
  "id" varchar(36) COLLATE "pg_catalog"."default" NOT NULL DEFAULT uuid_generate_v4(),
  CONSTRAINT "t_word_segment_pkey" PRIMARY KEY ("id")
);

生成Model

namespace Geo_compass.Daxt.Model.Entities {
	[Table(Name = "t_word_segment", DisableSyncStructure = true)]
	public partial class T_word_segment {
		[Column(Name = "id", StringLength = 36, IsPrimary = true, IsNullable = false)]
		public string Id { get; set; } = "uuid_generate_v4()";
	}
}

预期Model

namespace Geo_compass.Daxt.Model.Entities {
	[Table(Name = "t_word_segment", DisableSyncStructure = true)]
	public partial class T_word_segment {
		[Column(Name = "id", StringLength = 36, IsPrimary = true, IsNullable = false, InsertValueSql = "uuid_generate_v4()")]
		public string Id { get; set; };
	}
}

数据库版本

PostgreSQL 14.13

安装的Nuget包

FreeSql.Generator 3.5.212

.net framework/. net core？ 及具体版本

——

[466974367]

理论上可以通过修改__razor.cshtml.txt 使结果符合预期
但是这个功能应该是一个普适的情况 希望通过修改FreeSql.Generator 使默认值统一行为

[2881099]

把控制台打印的 SQL 发一下

[466974367]

把控制台打印的 SQL 发一下

select * from (select
b.nspname || '.' || a.tablename,
a.schemaname,
a.tablename ,
d.description,
'TABLE'
from pg_tables a
inner join pg_namespace b on b.nspname = a.schemaname
inner join pg_class c on c.relnamespace = b.oid and c.relname = a.tablename
left join pg_description d on d.objoid = c.oid and objsubid = 0
where a.schemaname not in ('pg_catalog', 'information_schema', 'topology')
and b.nspname || '.' || a.tablename not in ('public.spatial_ref_sys')

union all

select
b.nspname || '.' || a.relname,
b.nspname,
a.relname,
d.description,
'VIEW'
from pg_class a
inner join pg_namespace b on b.oid = a.relnamespace
left join pg_description d on d.objoid = a.oid and objsubid = 0
where b.nspname not in ('pg_catalog', 'information_schema') and a.relkind in ('m','v')
and b.nspname || '.' || a.relname not in ('public.geography_columns','public.geometry_columns','public.raster_columns','public.raster_overviews')
) ft_dbf where lower(schemaname)='public' and lower(tablename)='t_word_segment'

select
ns.nspname || '.' || c.relname as id,
a.attname,
t.typname,
case when a.atttypmod > 0 and a.atttypmod < 32767 then a.atttypmod - 4 else a.attlen end len,
case when t.typelem = 0 then t.typname else t2.typname end,
case when a.attnotnull then 0 else 1 end as is_nullable,
--e.adsrc as is_identity, pg12以下
(select pg_get_expr(adbin, adrelid) from pg_attrdef where adrelid = e.adrelid and adnum = e.adnum limit 1) is_identity,
d.description as comment,
a.attndims,
case when t.typelem = 0 then t.typtype else t2.typtype end,
ns2.nspname,
a.attnum, a.attidentity
from pg_class c
inner join pg_attribute a on a.attnum > 0 and a.attrelid = c.oid
inner join pg_type t on t.oid = a.atttypid
left join pg_type t2 on t2.oid = t.typelem
left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_attrdef e on e.adrelid = a.attrelid and e.adnum = a.attnum
inner join pg_namespace ns on ns.oid = c.relnamespace
inner join pg_namespace ns2 on ns2.oid = t.typnamespace
where (ns.nspname || '.' || c.relname in ('public.t_word_segment'))

select
ns.nspname || '.' || d.relname as table_id,
c.attname,
b.relname as index_id,
case when a.indisunique then 1 else 0 end IsUnique,
case when a.indisprimary then 1 else 0 end IsPrimary,
case when a.indisclustered then 0 else 1 end IsClustered,
case when pg_index_column_has_property(b.oid, c.attnum, 'desc') = 't' then 1 else 0 end IsDesc,
a.indkey::text,
c.attnum
from pg_index a
inner join pg_class b on b.oid = a.indexrelid
inner join pg_attribute c on c.attnum > 0 and c.attrelid = b.oid
inner join pg_namespace ns on ns.oid = b.relnamespace
inner join pg_class d on d.oid = a.indrelid
where (ns.nspname || '.' || d.relname in ('public.t_word_segment'))

 OUT Table -> xxxx\T_word_segment.cs

[08-06 08:46:10] 生成完毕，总共生成了 1 个文件，目录："xxxx\Entities\"

[2881099]

select
ns.nspname || '.' || c.relname as id,
a.attname,
t.typname,
case when a.atttypmod > 0 and a.atttypmod < 32767 then a.atttypmod - 4 else a.attlen end len,
case when t.typelem = 0 then t.typname else t2.typname end,
case when a.attnotnull then 0 else 1 end as is_nullable,
--e.adsrc as is_identity, pg12以下
(select pg_get_expr(adbin, adrelid) from pg_attrdef where adrelid = e.adrelid and adnum = e.adnum limit 1) is_identity,
d.description as comment,
a.attndims,
case when t.typelem = 0 then t.typtype else t2.typtype end,
ns2.nspname,
a.attnum, a.attidentity
from pg_class c
inner join pg_attribute a on a.attnum > 0 and a.attrelid = c.oid
inner join pg_type t on t.oid = a.atttypid
left join pg_type t2 on t2.oid = t.typelem
left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_attrdef e on e.adrelid = a.attrelid and e.adnum = a.attnum
inner join pg_namespace ns on ns.oid = c.relnamespace
inner join pg_namespace ns2 on ns2.oid = t.typnamespace
where (ns.nspname || '.' || c.relname in ('public.t_word_segment'))

这个没有查询默认值，拿到数据库管理工具上试试

[466974367]

select ns.nspname || '.' || c.relname as id, a.attname, t.typname, case when a.atttypmod > 0 and a.atttypmod < 32767 then a.atttypmod - 4 else a.attlen end len, case when t.typelem = 0 then t.typname else t2.typname end, case when a.attnotnull then 0 else 1 end as is_nullable, --e.adsrc as is_identity, pg12以下 (select pg_get_expr(adbin, adrelid) from pg_attrdef where adrelid = e.adrelid and adnum = e.adnum limit 1) is_identity, d.description as comment, a.attndims, case when t.typelem = 0 then t.typtype else t2.typtype end, ns2.nspname, a.attnum, a.attidentity from pg_class c inner join pg_attribute a on a.attnum > 0 and a.attrelid = c.oid inner join pg_type t on t.oid = a.atttypid left join pg_type t2 on t2.oid = t.typelem left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum left join pg_attrdef e on e.adrelid = a.attrelid and e.adnum = a.attnum inner join pg_namespace ns on ns.oid = c.relnamespace inner join pg_namespace ns2 on ns2.oid = t.typnamespace where (ns.nspname || '.' || c.relname in ('public.t_word_segment'))

这个没有查询默认值，拿到数据库管理工具上试试

id	attname	typname	len	typname(1)	is_nullable	is_identity	comment	attndims	typtype	nspname	attnum	attidentity
public.t_test	id	varchar	36	varchar	0	uuid_generate_v4()		0	b	pg_catalog	1

sql执行结果的is_identity可以和其他列一样拿到默认值

[2881099]

FreeSql.Generator\RazorModel.cs
可以看这里的代码逻辑。

var defval = GetColumnDefaultValue(col, false);
if (defval == null) //c#默认属性值，就不需要设置 InsertValueSql 了
{
	defval = GetColumnDefaultValue(col, true);
	if (defval != null)
	{
		sb.Add("InsertValueSql = \"" + defval.Replace("\"", "\\\"") + "\"");
		//sb.Add("CanInsert = false");
	}
}

这断根据实际情况 debug 一下

[466974367]

FreeSql.Generator\RazorModel.cs 可以看这里的代码逻辑。

var defval = GetColumnDefaultValue(col, false);
if (defval == null) //c#默认属性值，就不需要设置 InsertValueSql 了
{
defval = GetColumnDefaultValue(col, true);
if (defval != null)
{
sb.Add("InsertValueSql = "" + defval.Replace(""", "\"") + """);
//sb.Add("CanInsert = false");
}
}
这断根据实际情况 debug 一下

GetColumnDefaultValue内

if (cstype == typeof(string)) return isInsertValueSql ? (fsql.Select<TestTb>() as Select0Provider)._commonUtils.FormatSql("{0}", defval) : LiteralString(defval);

这部分内容没有理解
对于非 ' 开头的字符串 是不是应该增加一个 是否以 ( ) 结尾的判断 以判断是否为函数
对于函数的行为应该是不使用C#默认值 转而使用InsertValueSQL

[2881099]

有两种默认值生成代码，你看看现在是不是生成了第一种：

public string xxx { get; set; } = "uuid_generate_v4()";

[Column(InsertValueSql = "uuid_generate_v4()"]
public string xxx { get; set; };

关键在于 .razor 中调用的 GetColumnAttribute(col, true)，内部先尝试使用第一种生成，如果失败就尝试第二种。

so，可以判断如果 col.DefaultValue 是数据库函数，则使用直接使用第二种方式生成。

核心问题，怎么判断 col.DefaultValue 是普通字符串值，还是数据库函数

[466974367]

有两种默认值生成代码，你看看现在是不是生成了第一种：

public string xxx { get; set; } = "uuid_generate_v4()";

[Column(InsertValueSql = "uuid_generate_v4()"]
public string xxx { get; set; };
关键在于 .razor 中调用的 GetColumnAttribute(col, true)，内部先尝试使用第一种生成，如果失败就尝试第二种。

so，可以判断如果 col.DefaultValue 是数据库函数，则使用直接使用第二种方式生成。

核心问题，怎么判断 col.DefaultValue 是普通字符串值，还是数据库函数

特指针对PostgreSQL 是不是可以根据 字符串 开始 以【 ’ 】判断为字符串值 走第一种
非【 ‘ 】开始 且以【 ) 】结尾 认为是数据库函数调用 走第二种
剩下的情况再按照目前的逻辑 处理 即【内部先尝试使用第一种生成，如果失败就尝试第二种】

[2881099]

看怎么判断特征了，我看你查询返回的 DefaultValue 值是 "uuid_generate_v4()"

[2881099]

看下我提交的代码

[466974367]

看下我提交的代码

提交之后生成的Model是可以符合预期 但是不仅限于这个函数 是不是所有的函数都应该使用InsertValueSQL 因为数据库函数在C#程序端都无法解析

[2881099]

判断是不是数据库函数代价大