Утилита для поиска ошибок в системных таблицах

Привет всем.

Пользователь провайдера, о котором я упоминал в «Адском домене», асилил обновление системной таблицы 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 Сентябрь 27th, 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;

Оставить комментарий