Утилита для поиска ошибок в системных таблицах
Привет всем.
Пользователь провайдера, о котором я упоминал в «Адском домене», асилил обновление системной таблицы RDB$FIELDS и наконец-то обрел счастье и душевную умиротворенность. Вообще говоря, эти проблемы с доменами текстовых колонок Interbase — стандартные. Я бы даже сказал, что вообще все баги и тупости InterBase — это тщательно запрограммированный функционал. По другому ведь никак 🙂
Ключевую роль в победе над ошибками RDB$FIELDS сыграл VBS-скрипт, текст которого я приведу полностью. Хотя он и достаточно большой. Его последнюю версию можно найти в дистрибутиве — это файл prog_check_system_tables.vbs
Сам скрипт в базе ничего не меняет. Он работает в «READ ONLY» транзакции, анализирует текстовые колонки и формирует два файла: общий лог и шаблон скрипта с SQL запросами для исправления.
На текущий момент скрипт обнаруживает такие ошибки:
— Отсутствие кодовой страницы у текстовой колонки. Будут сгенерированы запросы, в которые потом нужно будет руками прописать идентификатор кодовой страницы.
— Рассогласование длины текстовой колонки в байтах и в символах.
—-
Параметры подключения к базе прописаны в константах, которые находятся в самом начале текста скрипта.
Так что, если у вас MSSQL ругается страшными ошибками вида
Message 7356, Level 16, State 1, Line 2
The OLE DB «LCPI.IBProvider.3» for linked server «FBLINKED» supplied inconsistent metadata for column. The column «COMPANY_NAME»(which had the compile-time ordinal 4) of the object «GENERIC_TABLE_X» appears to have «length» equal to 25 at compile time and 27 at run time.
попробуйте выполнит этот скрипт — скорее всего он найдет причину и упростит процесс исправления.
На всякий случай я напомню — сначала экспериментируйте с исправлениями на тестовой базе данных.
option explicit
'---------------------------------------------------------
const c_db_location="localhost:d:\database\ibp_test_fb25_d3_2.gdb"
const c_db_user ="sysdba"
const c_db_pswd ="masterkey"
const c_db_client ="fbclient.dll"
const c_file_log ="check_system_tables.log"
const c_file_correct_sql ="check_system_tables.sql"
'---------------------------------------------------------
const c_sql__unk_value ="<???>"
'---------------------------------------------------------
'BLR Types
const c_isc_blr_dtype__text =14
const c_isc_blr_dtype__text2 =15
const c_isc_blr_dtype__short =7
const c_isc_blr_dtype__long =8
const c_isc_blr_dtype__quad =9
const c_isc_blr_dtype__int64 =16
const c_isc_blr_dtype__float =10
const c_isc_blr_dtype__double =27
const c_isc_blr_dtype__d_float =11
const c_isc_blr_dtype__timestamp =35
const c_isc_blr_dtype__varying =37
const c_isc_blr_dtype__varying2 =38
const c_isc_blr_dtype__blob =261
const c_isc_blr_dtype__cstring =40
const c_isc_blr_dtype__cstring2 =41
const c_isc_blr_dtype__blob_id =45
const c_isc_blr_dtype__sql_date =12
const c_isc_blr_dtype__sql_time =13
const c_ib7_blr_dtype__bool =17
const c_fb30_blr_dtype__bool =23
'---------------------------------------------------------
dim g_fso
dim g_file_log
dim g_file_correct_sql
dim g_intend
dim g_ctx
dim g_cn
dim g_err_count
dim g_processed__domains
'------
set g_fso =nothing
set g_file_log =nothing
set g_file_correct_sql=nothing
g_intend=0
set g_ctx =nothing
set g_cn =nothing
g_err_count=0
'---------------------------------------------------------
set g_fso=createobject("Scripting.FileSystemObject")
set g_file_log=g_fso.CreateTextFile(c_file_log,true)
'---------------------------------------------------------
set g_ctx=new tag_context
set g_cn=createobject("ADODB.Connection")
g_cn.Provider="LCPI.IBProvider.3"
g_cn.Properties("location") = c_db_location
g_cn.Properties("user id") = c_db_user
g_cn.Properties("password") = c_db_pswd
g_cn.Properties("dbclient_library") = c_db_client
call g_cn.Open()
call g_ctx.set_cn(g_cn)
'---------------------------------------------------------
print "DBMS Name: "&cstr_sn(g_cn.Properties("DBMS Name").Value)
print "DBMS Version: "&cstr_sn(g_cn.Properties("DBMS Version").Value)
print "DBMS Dialect: "&cstr_sn(g_cn.Properties("IB Database Dialect").Value)
'----------------------------------------------------------
call g_ctx.cn.Execute("SET TRANSACTION READ ONLY")
call load_db_charsets(g_ctx)
call add(g_err_count,check_table_columns(g_ctx))
call g_ctx.cn.Execute("COMMIT")
set g_file_correct_sql=g_fso.CreateTextFile(c_file_correct_sql,true)
call g_ctx.write_correct_sql_to_file(g_file_correct_sql)
print ""
print "TOTAL ERRORS: "&cstr(g_err_count)
call wscript.quit(g_err_count)
'----------------------------------------------------------
class tag_charset_data
public id
public name
public bytes_per_char
end class' tag_charset_data
'----------------------------------------------------------
private sub load_db_charsets(ctx)
print ""
print "load database charsets ..."
dim rs
set rs=ctx.cn.Execute _
("select rdb$character_set_name,"&vbCrLf&_
"rdb$character_set_id,"&vbCrLf&_
"rdb$bytes_per_character"&vbCrLf&_
"from rdb$character_sets")
dim db_csets_by_id
set db_csets_by_id=createobject("Scripting.Dictionary")
dim cset
dim msg
while(not rs.eof)
set cset=new tag_charset_data
cset.name =get_nn(rs,0)
cset.id =get_nn(rs,1)
cset.bytes_per_char =get_nn(rs,2)
if(db_csets_by_id.Exists(cset.id))then
msg="Detect multiple charsets with equal id ["&cset(cset.id)&"]"
call err.raise(-1,,msg)
end if
call db_csets_by_id.Add(cset.id,cset)
call rs.movenext()
wend
ctx.set_db_csets_by_id(db_csets_by_id)
end sub 'load_db_charsets
'----------------------------------------------------------
private function check_table_columns(ctx)
check_table_columns=0
print ""
print "check table columns ..."
dim rs
set rs=ctx.cn.Execute _
("select rdb$relation_name,rdb$view_blr from rdb$relations")
dim n_table
n_table=0
dim rel_name
dim rel_type
while(not rs.eof)
n_table=n_table+1
rel_name=get_nn(rs,0)
if(IsNull(rs(1).value))then
rel_type="TABLE"
else
rel_type="VIEW"
end if
print cstr(n_table)&". ["&rel_name&"] - {"&rel_type&"}"
call add(check_table_columns, _
check_table_columns__impl(ctx, _
rel_name, _
rel_type))
call rs.movenext()
wend
end function 'check_table_columns
'----------------------------------------------------------
private function check_table_columns__impl(ctx, _
relation_name, _
relation_type)
check_table_columns__impl=0
dim intend
set intend=add_intend(5)
dim cmd
dim rs
set cmd=createobject("ADODB.Command")
cmd.ActiveConnection=ctx.cn
cmd.CommandText= _
"select rf.rdb$field_name as column_name,"&vbCrLf& _
"f.*"&vbCrLf& _
"from rdb$relation_fields rf"&vbCrLf& _
"join rdb$fields f"&vbCrLf& _
"on rf.rdb$field_source=f.rdb$field_name"&vbCrLf& _
"where rf.rdb$relation_name=?"
cmd(0)=relation_name
set rs=cmd.Execute()
dim col_name
dim domain_data
dim n_col
n_col=0
while(not rs.eof)
n_col=n_col+1
col_name=get_nn(rs,"column_name")
print cstr(n_col)&". ["&col_name&"]"
set domain_data=new tag_domain_data
set domain_data.fields=rs.fields
domain_data.data_location="{"&relation_type&"}["&relation_name&"].["&col_name&"]"
set domain_data.sql_update_builder _
=new tag_sql_builder__update_domain_of_relation_fields
domain_data.sql_update_builder.relation_name =relation_name
domain_data.sql_update_builder.field_name =col_name
call add(check_table_columns__impl, _
check_domain_data(ctx,domain_data))
call rs.movenext()
wend
if(n_col=0)then
proces_error check_table_columns__impl, "No Columns!"
end if
end function 'check_table_columns__impl
'----------------------------------------------------------
class tag_domain_data
public fields
public data_location
public sql_update_builder
private sub class_initialize()
set fields=nothing
data_location=""
set sql_update_builder=nothing
end sub 'class_initialize
end class 'tag_domain_data
'----------------------------------------------------------
private function check_domain_data(ctx,data)
check_domain_data=0
dim intend
set intend=add_intend(5)
dim domain_name
domain_name=get_nn(data.fields,"RDB$FIELD_NAME")
if(not ctx.mark_domain_as_processed(domain_name))then
print "domain ["&domain_name&"] already processed"
exit function
end if
print "domain ["&domain_name&"]"
dim blr_type_id
blr_type_id=get_nn(data.fields,"rdb$field_type")
select case blr_type_id
case c_isc_blr_dtype__text
call add(check_domain_data,check_domain_data__text(ctx,data))
case c_isc_blr_dtype__varying
call add(check_domain_data,check_domain_data__varying(ctx,data))
case else
print "unknown data type"
end select
end function 'check_domain_data
'----------------------------------------------------------
private function check_domain_data__text(ctx,data)
check_domain_data__text=0
print "CHAR"
'-----------
dim msg
dim domain__name
dim domain__is_system
dim domain__cset_id
dim domain__field_length
dim domain__char_length
dim domain__cset
domain__name =get_nn(data.fields,"RDB$FIELD_NAME")
domain__is_system =get_value_or_default(data.fields,"RDB$SYSTEM_FLAG",0)<>0
domain__cset_id =data.fields("RDB$CHARACTER_SET_ID").value
domain__field_length =data.fields("RDB$FIELD_LENGTH").value
domain__char_length =data.fields("RDB$CHARACTER_LENGTH").value
set domain__cset=nothing
'-----------
dim valid_cset_id
dim valid_field_length
dim valid_char_length
valid_cset_id =true
valid_field_length =true
valid_char_length =true
'------------------------------- Check NOT NULL
if(IsNull(domain__cset_id))then
valid_cset_id=false
valid_char_length=false
process_error check_domain_data__text, "Column charset not defined"
end if
'-----------
if(IsNull(domain__field_length))then
valid_field_length=false
process_error check_domain_data__text, "Length in bytes not defined"
end if
'-----------
if(IsNull(domain__char_length))then
valid_char_length=false
if(not domain__is_system)then
process_error check_domain_data__text, "Length in chars not defined"
end if
end if
'------------------------------- Check charsetID
if(valid_cset_id)then
if(ctx.db_csets_by_id.Exists(domain__cset_id))then
set domain__cset=ctx.db_csets_by_id.Item(domain__cset_id)
else
valid_cset_id=false
process_error check_domain_data__text, "Unknown charsetID: "&cstr_sn(domain__cset_id)
end if
end if 'valid_cset_id
'-------------------------------- Check char length
if(valid_char_length and valid_cset_id and valid_field_length)then
if((domain__char_length*domain__cset.bytes_per_char)<>domain__field_length)then
valid_char_length=false
msg="Wrong char count: "&cstr_sn(domain__char_length)&". "& _
"BytesPerChar: "&cstr_sn(domain__cset.bytes_per_char)&". "& _
"FieldLength: "&cstr_sn(domain__field_length)
process_error check_domain_data__text, msg
end if
end if 'valid_char_length and valid_cset_id
'-------------------------------- Create correction SQL
dim comment
dim old_value
dim new_value
dim sql
dim sql_is_valid
'-------------------------------- SQL FOR RDB$FIELD_LENGTH
if(not valid_field_length)then
old_value=domain__field_length
sql_is_valid=valid_char_length and valid_cset_id
if(sql_is_valid)then
new_value=domain__char_length*domain__cset.bytes_per_char
else
new_value=c_sql__unk_value
end if
sql=data.sql_update_builder.gen(domain__name, _
"RDB$FIELD_LENGTH", _
old_value, _
new_value)
'----
comment="CHAR DOMAIN USED AT "&data.data_location
'----
call ctx.reg_correct_sql(sql_is_valid,sql,comment)
end if 'not valid_field_length
'-------------------------------- SQL FOR RDB$CHARACTER_LENGTH
if((not valid_char_length) and (not domain__is_system) and valid_cset_id)then
old_value=domain__char_length
if(not valid_field_length)then
sql_is_valid=false
else
sql_is_valid=((domain__field_length mod domain__cset.bytes_per_char)=0)
end if
if(sql_is_valid)then
new_value=domain__field_length / domain__cset.bytes_per_char
else
new_value=c_sql__unk_value
end if
sql=data.sql_update_builder.gen(domain__name, _
"RDB$CHARACTER_LENGTH", _
old_value, _
new_value)
comment="CHAR DOMAIN USED AT "&data.data_location
if(valid_cset_id)then
comment=comment&vbCrLf
comment=comment&"CSET_NAME : ["&domain__cset.name&"]"&vbCrLf
comment=comment&"CSET_ID : ["&domain__cset.id&"]"&vbCrLf
comment=comment&"BYTES_PER_CHAR : ["&domain__cset.bytes_per_char&"]"
end if
if(valid_field_length)then
comment=comment&vbCrLf
comment=comment&"FIELD_LENGTH : ["&domain__field_length&"]"&vbCrLf
end if
comment=comment&vbCrLf
comment=comment&"CURRENT CHAR_LENGTH: ["&cstr_sn(domain__char_length)&"]"
'----
call ctx.reg_correct_sql(sql_is_valid,sql,comment)
end if' not valid_char_length
'-------------------------------- SQL FOR RDB$CHARACTER_SET_ID
if(not valid_cset_id)then
old_value=domain__cset_id
new_value=c_sql__unk_value
sql=data.sql_update_builder.gen(domain__name, _
"RDB$CHARACTER_SET_ID", _
old_value, _
new_value)
sql_is_valid=false
comment="CHAR DOMAIN USED AT "&data.data_location
'----
call ctx.reg_correct_sql(sql_is_valid,sql,comment)
end if 'not valid_cset_id
end function 'check_domain_data__text
'----------------------------------------------------------
private function check_domain_data__varying(ctx,data)
check_domain_data__varying=0
print "VARCHAR"
'-----------
dim msg
dim domain__name
dim domain__is_system
dim domain__cset_id
dim domain__field_length
dim domain__char_length
dim domain__cset
domain__name =get_nn(data.fields,"RDB$FIELD_NAME")
domain__is_system =get_value_or_default(data.fields,"RDB$SYSTEM_FLAG",0)<>0
domain__cset_id =data.fields("RDB$CHARACTER_SET_ID").value
domain__field_length =data.fields("RDB$FIELD_LENGTH").value
domain__char_length =data.fields("RDB$CHARACTER_LENGTH").value
set domain__cset=nothing
'-----------
dim valid_cset_id
dim valid_field_length
dim valid_char_length
valid_cset_id =true
valid_field_length =true
valid_char_length =true
'------------------------------- Check NOT NULL
if(IsNull(domain__cset_id))then
valid_cset_id=false
valid_char_length=false
process_error check_domain_data__varying, "Column charset not defined"
end if
'-----------
if(IsNull(domain__field_length))then
valid_field_length=false
process_error check_domain_data__varying, "Length in bytes not defined"
end if
'-----------
if(IsNull(domain__char_length))then
valid_char_length=false
if(not domain__is_system)then
process_error check_domain_data__varying, "Length in chars not defined"
end if
end if
'------------------------------- Check charsetID
if(valid_cset_id)then
if(ctx.db_csets_by_id.Exists(domain__cset_id))then
set domain__cset=ctx.db_csets_by_id.Item(domain__cset_id)
else
valid_cset_id=false
process_error check_domain_data__varying, "Unknown charsetID: "&cstr_sn(domain__cset_id)
end if
end if 'valid_cset_id
'-------------------------------- Check char length
if(valid_char_length and valid_cset_id and valid_field_length)then
if((domain__char_length*domain__cset.bytes_per_char)<>domain__field_length)then
valid_char_length=false
msg="Wrong char count: "&cstr_sn(domain__char_length)&". "& _
"BytesPerChar: "&cstr_sn(domain__cset.bytes_per_char)&". "& _
"FieldLength: "&cstr_sn(domain__field_length)
process_error check_domain_data__varying, msg
end if
end if 'valid_char_length and valid_cset_id
'-------------------------------- Create correction SQL
dim comment
dim old_value
dim new_value
dim sql
dim sql_is_valid
'-------------------------------- SQL FOR RDB$FIELD_LENGTH
if(not valid_field_length)then
old_value=domain__field_length
sql_is_valid=valid_char_length and valid_cset_id
if(sql_is_valid)then
new_value=domain__char_length*domain__cset.bytes_per_char
else
new_value=c_sql__unk_value
end if
sql=data.sql_update_builder.gen(domain__name, _
"RDB$FIELD_LENGTH", _
old_value, _
new_value)
'----
comment="VARCHAR DOMAIN USED AT "&data.data_location
'----
call ctx.reg_correct_sql(sql_is_valid,sql,comment)
end if 'not valid_field_length
'-------------------------------- SQL FOR RDB$CHARACTER_LENGTH
if((not valid_char_length) and (not domain__is_system) and valid_cset_id)then
old_value=domain__char_length
if(not valid_field_length)then
sql_is_valid=false
else
sql_is_valid=((domain__field_length mod domain__cset.bytes_per_char)=0)
end if
if(sql_is_valid)then
new_value=domain__field_length / domain__cset.bytes_per_char
else
new_value=c_sql__unk_value
end if
sql=data.sql_update_builder.gen(domain__name, _
"RDB$CHARACTER_LENGTH", _
old_value, _
new_value)
comment="VARCHAR DOMAIN USED AT "&data.data_location
if(valid_cset_id)then
comment=comment&vbCrLf
comment=comment&"CSET_NAME : ["&domain__cset.name&"]"&vbCrLf
comment=comment&"CSET_ID : ["&domain__cset.id&"]"&vbCrLf
comment=comment&"BYTES_PER_CHAR : ["&domain__cset.bytes_per_char&"]"
end if
if(valid_field_length)then
comment=comment&vbCrLf
comment=comment&"FIELD_LENGTH : ["&domain__field_length&"]"&vbCrLf
end if
comment=comment&vbCrLf
comment=comment&"CURRENT CHAR_LENGTH: ["&cstr_sn(domain__char_length)&"]"
'----
call ctx.reg_correct_sql(sql_is_valid,sql,comment)
end if' not valid_char_length
'-------------------------------- SQL FOR RDB$CHARACTER_SET_ID
if(not valid_cset_id)then
old_value=domain__cset_id
new_value=c_sql__unk_value
sql=data.sql_update_builder.gen(domain__name, _
"RDB$CHARACTER_SET_ID", _
old_value, _
new_value)
sql_is_valid=false
comment="VARCHAR DOMAIN USED AT "&data.data_location
'----
call ctx.reg_correct_sql(sql_is_valid,sql,comment)
end if 'not valid_cset_id
end function 'check_domain_data__varying
'----------------------------------------------------------
private function get_nn(rs,field_index)
get_nn=rs(field_index).value
if(IsNull(get_nn))then
dim msg
msg="Field ["&rs(field_index).name&"] has NULL value"
call err.raise(-1,,msg)
end if
end function 'get_nn
'----------------------------------------------------------
private function get_value_or_default(rs,field_index,default_value)
get_value_or_default=rs(field_index).value
if(IsNull(get_value_or_default))then
get_value_or_default=default_value
end if
end function 'get_nn
'----------------------------------------------------------
class tag_intend
private m_prev_intend
private sub class_initialize()
m_prev_intend=g_intend
end sub 'class_inialize
private sub class_terminate()
g_intend=m_prev_intend
end sub 'class_terminate
end class 'tag_intend
'----------------------------------------------------------
private function add_intend(delta)
set add_intend=new tag_intend
call add(g_intend,delta)
end function 'add_intend
'----------------------------------------------------------
private sub process_error(byref err_count,msg)
call add(err_count,1)
print_error msg
end sub 'process_error
'----------------------------------------------------------
private sub print_error(msg)
print "ERROR: "&msg
end sub 'print_error
'----------------------------------------------------------
private sub print(msg)
out_text string(g_intend," ")&msg
end sub 'print
'----------------------------------------------------------
private sub out_text(msg)
wscript.echo msg
if(not(g_file_log is nothing))then
call g_file_log.WriteLine(msg)
end if
end sub 'out_text
'----------------------------------------------------------
private function cstr_sn(x)
if(IsNull(x))then
cstr_sn="#NULL"
elseif(IsEmpty(x))then
cstr_sn="#EMPTY"
else
cstr_sn=cstr(x)
end if
end function 'cstr_sn
'----------------------------------------------------------
private function inc(byref x)
inc=add(x,1)
end function 'inc
'----------------------------------------------------------
private function add(byref x,byval delta)
x=x+delta
add=x
end function 'add
'----------------------------------------------------------
private function sql__pack_name(name)
dim s
s="'"
dim i
for i=1 to len(name)
if(mid(name,i,1)="'")then
s=s&"'"
end if
s=s&mid(name,i,1)
next 'i
s=s&"'"
sql__pack_name=s
end function 'sql__pack_name
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'class tag_correct_sql
class tag_correct_sql
public sql_is_valid
public sql
public comment
end class 'tag_correct_sql
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'class tag_context
class tag_context
private m_cn
private m_db_csets_by_id
private m_processed__domains
private m_correct_sql
'----------------------------------------------
private sub class_initialize()
set m_cn=nothing
set m_db_csets_by_id=nothing
set m_processed__domains=nothing
set m_correct_sql=nothing
end sub 'class_initialize
'----------------------------------------------
public sub set_cn(cn)
set m_cn=cn
end sub 'set_cn
'----------------------------------------------
public sub set_db_csets_by_id(db_csets_by_id)
set m_db_csets_by_id=db_csets_by_id
end sub 'set_db_csets_by_id
'----------------------------------------------
public property get cn()
set cn=m_cn
end property 'get cn
'----------------------------------------------
public property get db_csets_by_id()
set db_csets_by_id=m_db_csets_by_id
end property 'get csets_by_id
'----------------------------------------------
public function mark_domain_as_processed(domain_name)
mark_domain_as_processed=false
if(m_processed__domains is nothing)then
set m_processed__domains=createobject("Scripting.Dictionary")
elseif(m_processed__domains.Exists(domain_name))then
exit function
end if
call m_processed__domains.add(domain_name,null)
mark_domain_as_processed=true
end function 'mark_domain_as_processed
'----------------------------------------------
public sub reg_correct_sql(sql_is_valid,sql,comment)
if(m_correct_sql is nothing)then
set m_correct_sql=createobject("LCPI.IBP.Samples.Vector.1")
end if
dim x
set x=new tag_correct_sql
x.sql_is_valid =sql_is_valid
x.sql =sql
x.comment =comment
call m_correct_sql.PushBack(x)
end sub 'reg_correct_sql
'----------------------------------------------
public property get has_correct_sql
has_correct_sql=false
if(m_correct_sql is nothing)then
exit property
end if
if(m_correct_sql.IsEmpty())then
exit property
end if
has_correct_sql=true
end property 'get has_correct_sql
'----------------------------------------------
public sub write_correct_sql_to_file(file)
if(not me.has_correct_sql)then
exit sub
end if
dim n
n=0
dim data
dim i
for i=0 to m_correct_sql.Count-1
n=n+1
set data=m_correct_sql(i)
call file.WriteLine _
("/*"&n&" --------------------------------- */")
call file.WriteLine _
("/*"&vbCrLf&data.comment&vbCrLf&"*/")
call file.WriteLine _
("")
call file.WriteLine _
(data.sql)
next 'i
call file.WriteLine _
("")
call file.WriteLine _
("/* --------------------------------- */")
call file.WriteLine _
("COMMIT;")
call file.WriteLine _
("/**/")
end sub 'write_correct_sql_to_file
end class 'tag_context
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
class tag_sql_builder__update_domain_of_relation_fields
public relation_name
public field_name
public function gen(domain_name, _
domain_prop_name, _
domain_prop_old_value, _
domain_prop_new_value)
dim s
s="UPDATE RDB$FIELDS"&vbCrLf& _
"SET "&domain_prop_name&"="
if(IsNull(domain_prop_new_value))then
s=s&"NULL"
else
s=s&cstr(domain_prop_new_value)
end if
s=s&vbCrLf& _
"WHERE RDB$FIELD_NAME="&vbCrLf& _
" (SELECT RDB$FIELD_SOURCE"&vbCrLf& _
" FROM RDB$RELATION_FIELDS"&vbCrLf& _
" WHERE RDB$RELATION_NAME="&sql__pack_name(relation_name)&" AND"&vbCrLf& _
" RDB$FIELD_NAME="&sql__pack_name(field_name)&") AND"&vbCrLf& _
" "&domain_prop_name
if(IsNull(domain_prop_old_value))then
s=s&" IS NULL"
else
s=s&"="&cstr(domain_prop_old_value)
end if
s=s&";"
gen=s
end function 'gen
end class 'tag_sql_builder__update_domain_of_relation_fields
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
На выходе будет сформирован, к примеру, вот такой файл с исправлениями:
/*1 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[PROFILE].[CREATEBY]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [20]
CURRENT CHAR_LENGTH: [10]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=20
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='PROFILE' AND
RDB$FIELD_NAME='CREATEBY') AND
RDB$CHARACTER_LENGTH=10;
/*2 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[PAYMENT].[CALCFIXEDCCNO]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [35]
CURRENT CHAR_LENGTH: [0]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=35
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='PAYMENT' AND
RDB$FIELD_NAME='CALCFIXEDCCNO') AND
RDB$CHARACTER_LENGTH=0;
/*3 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[INVOICE].[REMARKS]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [4000]
CURRENT CHAR_LENGTH: [512]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=4000
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='INVOICE' AND
RDB$FIELD_NAME='REMARKS') AND
RDB$CHARACTER_LENGTH=512;
/*4 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[INVOICE].[CALCINVOICENUMBER]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [11]
CURRENT CHAR_LENGTH: [0]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=11
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='INVOICE' AND
RDB$FIELD_NAME='CALCINVOICENUMBER') AND
RDB$CHARACTER_LENGTH=0;
/*5 --------------------------------- */
/*
CHAR DOMAIN USED AT {TABLE}[BOOKING].[CALCTRIMVENDORREMARKS]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [253]
CURRENT CHAR_LENGTH: [0]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=253
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='BOOKING' AND
RDB$FIELD_NAME='CALCTRIMVENDORREMARKS') AND
RDB$CHARACTER_LENGTH=0;
/*6 --------------------------------- */
/*
CHAR DOMAIN USED AT {TABLE}[BOOKING].[CALCTRIMCLIENTREMARKS]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [253]
CURRENT CHAR_LENGTH: [0]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=253
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='BOOKING' AND
RDB$FIELD_NAME='CALCTRIMCLIENTREMARKS') AND
RDB$CHARACTER_LENGTH=0;
/*7 --------------------------------- */
/*
CHAR DOMAIN USED AT {TABLE}[BOOKING].[CALCTICKETCONFIRMNO]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [253]
CURRENT CHAR_LENGTH: [0]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=253
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='BOOKING' AND
RDB$FIELD_NAME='CALCTICKETCONFIRMNO') AND
RDB$CHARACTER_LENGTH=0;
/*8 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {VIEW}[PROFILEVIEW].[FIRSTANDLAST]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [81]
CURRENT CHAR_LENGTH: [#NULL]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=81
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='PROFILEVIEW' AND
RDB$FIELD_NAME='FIRSTANDLAST') AND
RDB$CHARACTER_LENGTH IS NULL;
/*9 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {VIEW}[PROFILEVIEW].[LASTANDFIRST]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [81]
CURRENT CHAR_LENGTH: [#NULL]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=81
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='PROFILEVIEW' AND
RDB$FIELD_NAME='LASTANDFIRST') AND
RDB$CHARACTER_LENGTH IS NULL;
/*10 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {VIEW}[PROFILEVIEW].[FIRSTANDLASTUPPER]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [81]
CURRENT CHAR_LENGTH: [#NULL]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=81
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='PROFILEVIEW' AND
RDB$FIELD_NAME='FIRSTANDLASTUPPER') AND
RDB$CHARACTER_LENGTH IS NULL;
/*11 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {VIEW}[PROFILEVIEW].[LASTANDFIRSTUPPER]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [81]
CURRENT CHAR_LENGTH: [#NULL]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=81
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='PROFILEVIEW' AND
RDB$FIELD_NAME='LASTANDFIRSTUPPER') AND
RDB$CHARACTER_LENGTH IS NULL;
/*12 --------------------------------- */
/*
CHAR DOMAIN USED AT {VIEW}[BOOKINGVIEW].[CLIENTREMARKS]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [253]
CURRENT CHAR_LENGTH: [#NULL]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=253
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='BOOKINGVIEW' AND
RDB$FIELD_NAME='CLIENTREMARKS') AND
RDB$CHARACTER_LENGTH IS NULL;
/*13 --------------------------------- */
/*
CHAR DOMAIN USED AT {VIEW}[BOOKINGVIEW].[VENDORREMARKS]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [253]
CURRENT CHAR_LENGTH: [#NULL]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=253
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='BOOKINGVIEW' AND
RDB$FIELD_NAME='VENDORREMARKS') AND
RDB$CHARACTER_LENGTH IS NULL;
/*14 --------------------------------- */
/*
CHAR DOMAIN USED AT {VIEW}[BOOKINGVIEW].[TICKETCONFIRMNO]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [253]
CURRENT CHAR_LENGTH: [#NULL]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=253
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='BOOKINGVIEW' AND
RDB$FIELD_NAME='TICKETCONFIRMNO') AND
RDB$CHARACTER_LENGTH IS NULL;
/*15 --------------------------------- */
/*
CHAR DOMAIN USED AT {VIEW}[BOOKINGPSGRVIEW].[PASSENGERDEPTUPPER]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [254]
CURRENT CHAR_LENGTH: [#NULL]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=254
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='BOOKINGPSGRVIEW' AND
RDB$FIELD_NAME='PASSENGERDEPTUPPER') AND
RDB$CHARACTER_LENGTH IS NULL;
/*16 --------------------------------- */
/*
CHAR DOMAIN USED AT {VIEW}[BOOKINGPSGRVIEW].[PASSENGERDEPT]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [254]
CURRENT CHAR_LENGTH: [#NULL]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=254
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='BOOKINGPSGRVIEW' AND
RDB$FIELD_NAME='PASSENGERDEPT') AND
RDB$CHARACTER_LENGTH IS NULL;
/*17 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[PNRBRANCHSETTINGS].[PSEUDOCITY]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [8]
CURRENT CHAR_LENGTH: [3]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=8
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='PNRBRANCHSETTINGS' AND
RDB$FIELD_NAME='PSEUDOCITY') AND
RDB$CHARACTER_LENGTH=3;
/*18 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[ACTIVITY].[USERNAME]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [20]
CURRENT CHAR_LENGTH: [15]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=20
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='ACTIVITY' AND
RDB$FIELD_NAME='USERNAME') AND
RDB$CHARACTER_LENGTH=15;
/*19 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[RESCARDRESERVATION].[SOURCEOFBOOKING]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [25]
CURRENT CHAR_LENGTH: [20]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=25
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='RESCARDRESERVATION' AND
RDB$FIELD_NAME='SOURCEOFBOOKING') AND
RDB$CHARACTER_LENGTH=20;
/*20 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[RESCARD].[GROUPNAME]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [128]
CURRENT CHAR_LENGTH: [20]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=128
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='RESCARD' AND
RDB$FIELD_NAME='GROUPNAME') AND
RDB$CHARACTER_LENGTH=20;
/*21 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[RESCARDPROVIDER].[DINING]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [30]
CURRENT CHAR_LENGTH: [15]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=30
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='RESCARDPROVIDER' AND
RDB$FIELD_NAME='DINING') AND
RDB$CHARACTER_LENGTH=15;
/*22 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[RESCARDPROVIDER].[AIRCRAFTLOCATOR]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [12]
CURRENT CHAR_LENGTH: [10]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=12
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='RESCARDPROVIDER' AND
RDB$FIELD_NAME='AIRCRAFTLOCATOR') AND
RDB$CHARACTER_LENGTH=10;
/*23 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[LIVECONNECTPROVIDER].[PROVIDERNAME]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [60]
CURRENT CHAR_LENGTH: [30]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=60
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='LIVECONNECTPROVIDER' AND
RDB$FIELD_NAME='PROVIDERNAME') AND
RDB$CHARACTER_LENGTH=30;
/*24 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[MARKETINGCODE].[CODEVALUE]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [80]
CURRENT CHAR_LENGTH: [20]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=80
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='MARKETINGCODE' AND
RDB$FIELD_NAME='CODEVALUE') AND
RDB$CHARACTER_LENGTH=20;
/*25 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[VENDORLIVECONNECT].[VENDORCODE]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [128]
CURRENT CHAR_LENGTH: [20]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=128
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='VENDORLIVECONNECT' AND
RDB$FIELD_NAME='VENDORCODE') AND
RDB$CHARACTER_LENGTH=20;
/*26 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[USERLIVECONNECT].[LCPASSWORD]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [80]
CURRENT CHAR_LENGTH: [20]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=80
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='USERLIVECONNECT' AND
RDB$FIELD_NAME='LCPASSWORD') AND
RDB$CHARACTER_LENGTH=20;
/*27 --------------------------------- */
/*
VARCHAR DOMAIN USED AT {TABLE}[USERLIVECONNECT].[USERNAME]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [80]
CURRENT CHAR_LENGTH: [20]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=80
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='USERLIVECONNECT' AND
RDB$FIELD_NAME='USERNAME') AND
RDB$CHARACTER_LENGTH=20;
/* --------------------------------- */
COMMIT;
/**/
Kovalenko on 27 сентября, 2011
Обновление скрипта.
1. Исправил багу, которая обнаружилась в процессе полевого испытания.
2. Добавил константу «c_db_client», которая упрощает указание клиентской библиотеки.
Пример запроса, который будет сформирован, если обнаружится текстовая колонка без кодовой страницы (это болезнь VIEW баз данных Interbase).
/*
VARCHAR DOMAIN USED AT {VIEW}[PNM_BYDESCR_VIEW].[DESCRIPTION]
*/
UPDATE RDB$FIELDS
SET RDB$CHARACTER_SET_ID=
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME=’PNM_BYDESCR_VIEW’ AND
RDB$FIELD_NAME=’DESCRIPTION’) AND
RDB$CHARACTER_SET_ID IS NULL;