Monthly Archives: June 2013

Perl script to extract and present hex colors found in arbitrary text

The following script searches STDIN for 3 or 6 symbol hex values and prints a html structure that represents those values as colors on page.

#!/usr/bin/perl -w
use Set::Scalar;

$uniqueColors = Set::Scalar->new;
while( defined( $_ = <STDIN> ) ){
while ( $_ =~/(#[a-fA-F0-9]{1,6})/g ){
$uniqueColors->insert( $1 )
}
}
print <<HEADER;
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<HTML>
<HEAD>
<TITLE>Found HEX colors</TITLE>
</HEAD>
<BODY>
HEADER
while ( defined( my $color = $uniqueColors->each ) ){
print "<p style="background: " . $color . "">" . $color . "</p>n"
}
print "</BODY>
</HTML>"

Used this one to parse color-theme-tangotango.el emacs color theme file.
To view example output hit “read more”.

#ee3436

#2E4034

#8ae234

#426f9f

#bbbbbc

#888a85

#252b2b

#343434

#222222

#2e3434

#eeeeec

#243436

#6ac214

#555753

#fce94f

#729fcf

#2e3436

#ad7fa8

#2E3440

#edd400

#e9b96e

#f57900

#73d216

Interesting remarks on using standard libraries

While searching for implementation of Sets in Perl bumped into these interesting arguments for using standard libraries on StackOverflow.

Use one of the many Set modules on CPAN. Judging from your example, Set::Light or Set::Scalar seem appropriate.


I can defend this advice with the usual arguments pro CPAN (disregarding possible synergy effects).

  1. How can we know that look-up is all that is needed, both now and in the future? Experience teaches that even the simplest programs expand and sprawl. Using a module would anticipate that.
  2. An API is much nicer for maintenance, or people who need to read and understand the code in general, than an ad-hoc implementation as it allows to think about partial problems at different levels of abstraction.
  3. Related to that, if it turns out that the overhead is undesirable, it is easy to go from a module to a simple by removing indirections or paring data structures and source code. But on the other hand, if one would need more features, it is moderately more difficult to achieve the other way around.
  4. CPAN modules are already tested and to some extent thoroughly debugged, perhaps also the API underwent improvement steps over the time, whereas with ad-hoc, programmers usually implement the first design that comes to mind.

Rarely it turns out that picking a module at the beginning is the wrong choice.

Oracle SQL selects to get familiar with table naming standard and to find junk tables in scheme

Run following selects to discover prefix and suffix hierarchies and to get count of particular prefix/suffix occurrences. That will help you become familiar with naming standard and to find suspicious prefixes/suffixes that belong to tables that probably store junk data.

with prefix_generator( prefix, p_prefix, object_name, lvl ) as (
select regexp_substr( object_name, '.*?_' ) prefix
, null as p_prefix
, object_name as object_name
, 1 as lvl
from (
select table_name as object_name
from all_tables
where owner like 'YOUR_OWNER'
and temporary like 'N'
) q0
union all
select g.prefix || regexp_substr(
substr( g.object_name, length( g.prefix ) + 1 ), '.*?_'
) as prefix
, g.prefix as p_prefix
, g.object_name
, g.lvl + 1
from prefix_generator g
where regexp_substr(
substr( g.object_name, length( g.prefix ) + 1 ), '.*?_'
) is not null
)
, groupped_generator as (
select prefix,
p_prefix
from prefix_generator
group by prefix,
p_prefix
)
, forward_builder(
prefix
, lvl
, padded_prefix
) as (
select g.prefix
, 0 as lvl
, g.prefix padded_prefix
from groupped_generator g
where g.p_prefix is null
group by g.prefix
union all
select n.prefix
, p.lvl + 1 as lvl
, lpad( ' ', ( p.lvl + 1 ) * 4 ) || n.prefix padded_prefix
from groupped_generator n
join forward_builder p
on p.prefix = n.p_prefix
) search depth first by prefix set order_by
, count_by_prefix as (
select prefix
, count( * ) cnt
from prefix_generator g
group by prefix
)
select b.padded_prefix
, c.cnt
from forward_builder b
join count_by_prefix c
on c.prefix = b.prefix
where 2 < c.cnt
order by order_by
;/

with postfix_generator( postfix, p_postfix, object_name, lvl ) as (
select regexp_substr( object_name, '_.*' ) postfix
, object_name as p_postfix
, object_name as object_name
, 1 as lvl
from (
select table_name as object_name
from all_tables
where owner like 'YOUR_OWNER'
and temporary like 'N'
) q0
union all
select regexp_substr(
substr( g.postfix, 2 ), '_.*'
) as postfix
, g.postfix as p_postfix
, g.object_name
, g.lvl + 1
from postfix_generator g
where g.postfix is not null -- recursion breaker
)
, groupped_generator as (
select postfix,
p_postfix
from postfix_generator
where p_postfix <> object_name
group by postfix,
p_postfix

)
, backward_builder(
p_postfix
, lvl
, padded_p_postfix
) as (
select g.p_postfix
, 0 as lvl
, g.p_postfix padded_p_postfix
from groupped_generator g
where g.postfix is null
group by g.p_postfix
union all
select n.p_postfix
, p.lvl + 1 as lvl
, lpad( ' ', ( p.lvl + 1 ) * 4 ) || n.p_postfix padded_p_postfix
from groupped_generator n
join backward_builder p
on p.p_postfix = n.postfix
) search depth first by p_postfix set order_by
, count_by_postfix as (
select postfix
, count( * ) cnt
from postfix_generator g
where postfix is not null
group by postfix
)
select b.padded_p_postfix
, c.cnt
from backward_builder b
join count_by_postfix c
on c.postfix = b.p_postfix
where 2 < c.cnt
order by b.order_by
;/

To select all prefixes and suffixes (not only those having more than two occurrences) delete ‘where 2 < c.cnt’ clause.

Highlight empty tables in PowerDesigner model with VB Script

The wonderful thing about PowerDesigner automation is that you can actually connect to database from VB Script to get some crucial information that is unavailable (or was not captured) at a reverse engineering phase.
The following script queries Oracle ALL_TABLES system view to find out if table in the model stores any data. Then those tables that are empty are highlighted with thick maroon frame.


On Error Resume Next

function ConnectToOra( ByRef aConnection )
Const adUseClient = 3
aConnection.CursorLocation = adUseClient
aConnection.ConnectionTimeout = 300
Dim strConnection
strConnection = "Provider=OraOLEDB.Oracle;Data Source=your_tns_name;User ID=your_id;Password=your_pass"
output "Connecting to Oracle"
aConnection.Open strConnection
if ( 0 <> err ) then
output "An error occurred trying to open Oracle System DSN: " & _
err.number & " " & err.description & " " & err.Source
ConnectToOra = vbFalse
exit function
End if

ConnectToOra = vbTrue
Exit Function
End Function

const GET_ROW_COUNT_ERROR = -1
const GET_ROW_COUNT_NOT_FOUND = -2

function GetRowCount( ByRef aConnection, ByRef aRecordSet, aTableName )
Dim SQLSelect
SQLSelect = "select num_rows from all_tables where owner like 'SHOPS_MOD' and table_name like '" & aTableName & "'"
Set aRecordSet = aConnection.Execute( SQLSelect )
if ( 0 <> err ) then
output "An error occurred trying to execute query " & _
SQLSelect & ": " & err.number & " " & err.description & " " & err.Source
GetRowCount = GET_ROW_COUNT_ERROR
exit function
End if
if ( aRecordSet.BOF and aRecordSet.EOF ) then
GetRowCount = GET_ROW_COUNT_NOT_FOUND
Exit Function
end if
GetRowCount = aRecordSet.Fields(0).Value
Exit Function
end function


sub main( ByRef aModel )
Dim ADODBConnection: Set ADODBConnection = CreateObject("ADODB.Connection")
Dim connected: connected = ConnectToOra( ADODBConnection )

if ( not connected ) then
output "Not connected. Terminating"
Set ADODBConnection = Nothing
exit sub
end if
output "Successfully connected to Oracle"

Dim ADODBRecordSet
Set ADODBRecordSet = CreateObject("ADODB.Recordset")

Dim rowCount
Dim tableName
Dim strRowCount
For each table in aModel.Tables
tableName = Trim( table.Name )
rowCount = GetRowCount( ADODBConnection, ADODBRecordSet, tableName )
strRowCount = "" & rowCount
output tableName & "->" & rowCount
if ( "0" = strRowCount ) then
For each symbol in table.Symbols
symbol.LineWidth = 75 ' double
symbol.LineColor = RGB( 138, 0, 21 )
Next
end if
Next

ADODBConnection.Close
Set ADODBRecordSet = Nothing
Set ADODBConnection = Nothing
exit sub
end sub

Dim model
Set model = ActiveModel
main model

Working with PowerDesigner models through VB interface

PowerDesigner has great automation abilities. The easiest way to work with a model programmatically is to use PowerDesigner’s embedded Visual Basic script interface and to run scripts through ‘Edit/Run script’ window.
Here’s an example script that uses given color to highlight tables that have comments or that have at least one column with a comment:


Dim model
Set model = ActiveModel
For each table in model.Tables
doPaint = false
tableComment = Trim( table.Comment )
if ( "" <> tableComment ) then
doPaint = true
end if

if ( not doPaint ) then
For each column in table.Columns
comment = Trim( column.Comment )
if ( "" <> comment ) then
doPaint = true
Exit For
end if
Next
end if

if ( doPaint ) then
For each symbol in table.Symbols
symbol.BrushStyle = 6 'Gradient
symbol.GradientFillMode = 64
symbol.FillColor = RGB( 252, 178, 104 )
symbol.GradientEndColor = RGB( 255, 255, 255 )
Next
end if
Next

More info about PowerDesigner automation at SyBooks and PowerDesigner’s ‘Metadata Objects’ help file.
Tip: when reverse engineering big unfamiliar scheme you can easily select and move apart particular symbols by adjusting their ‘Position’ property. For example you can select and move tables with particular prefix:


Dim model
Set model = ActiveModel
PREFIX = "hh_"
For each table in model.Tables
tableName = LCase( Trim( table.Name ) )
if ( PREFIX = Left( tableName, Len( prefix) ) ) then
For each symbol in table.Symbols
symbol.Position = NewPoint( 0, 0 )
Next
end if
Next

After desired symbols are moved to the (0,0) position they can be selected and arranged with ‘Symbol->Auto layout command’.
This simple solution of how to distill big scheme came to me after I had reversed database with 2K of tables with a few foreign key constants defined. I faced a brick wall of tables arranged by their column count. All semantics were encoded in table names so I just had to set apart tables with equal prefixes. I’ve searched for a function to add symbols to selection but with no success. So I decided to move desired symbols to the center of workspace and then to select them with single mouse movement. That’s when ‘Auto layout’ command came in very handy.