to:owensc@enc.edu
cc:
subject:Re:tkined as network inventory database
Hello Charles,
in message <30BB64A9.2781E494@enc.edu> you wrote:
>Has anyone done anything to enhance the usage of tkined as a full-blown
>database for network equipment inventory? My first thought is to create
>some scripts of some sort (of course, knowing tcl might help...:-) to
>shoe-horn the object data from the map files into a msql database and
>visa versa. Has anyone done this? Any comments?
I wrote a script which does something like that. To run it you need the
msqltcl-interface and you must insert the tables with the tkined-objects
(NODE, NETWORK, ...) into your bones-database:
You can do this by adding (as an example) the following lines to the
'createdb.tcl'-file comming with the bones-package:
- ---cut here ---------------------------
bones {
create table NODE (
id int primary key,
name char(80) not null,
address char(40),
room int,
department int,
administrator int,
administrator1 int,
user int,
user1 int,
user2 int,
type char(40),
operatingsystem char(40),
status char(40),
network int,
)
}
##
## Tables used to store network information.
##
bones {
create table NETWORK (
id int not null primary key,
name char(40),
address char(40),
department int,
status char(40),
layer int,
type char(20),
speed int,
mask char(40),
length int
)
}
bones {
create table LINK (
id int primary key,
objekt1 int not null,
objekt2 int not null,
type char(40),
length int
)
}
bones {
create table GROUP (
id int not null primary key,
name char(16) not null
)
}
bones {
create table IMAGE (
id int primary key,
name char(80) not null
)
}
bones {
create table REFERENCE (
id int primary key,
name char(80) not null,
address char(40)
)
}
- ---cut here ---------------------------
The following script then puts all the NODEs, NETWORKs, etc. appearing in the
current tkined-map into the bones-database. I guess you also want to copy
the corresponding attributes to bones, to do this you have to modify the script
(I hope it's readable for you allthough it isn't well documented ).
Good luck,
Udo
- ---cut-here-------------------
#! /usr/local/bin/scotty -nf
if {![info exists msqlserver]} {
set msqlserver birke
}
##
## Check which database systems is available.
##
if {[info commands msqlconnect] == ""} {
puts stderr "This scotty version is not able to talk to a msql server."
exit 42
}
##
## Connect to the bones database.
##
if {[catch {
set db [msqlconnect $msqlserver]
msqluse $db bones
} msg]} {
ined acknowledge "Failed to connect to bones database server: $msg"
exit 42
}
if {[catch {
set tables [msqlinfo $db tables]
} msg]} {
ined acknowledge "Failed to retrieve table names: $msg"
exit 42
}
#This proc returns a list containing the ID's of all the objects of any type
#mentioned in typelist
proc getids { {typelist {REFERENCE NETWORK NODE TEXT GROUP IMAGE MENU \
INTERPRETER LOG STRIPCHART BARCHART GRAPH}} } {
foreach object [ined retrieve] {
if {[lsearch $typelist [lindex $object 0]] != -1} {
lappend idlist [lindex $object 1]
}
}
if {[info exists idlist]} {
return $idlist
} else {
return
}
}
proc UpDateEntry { text table tki_list sql_list } {
global db
set id [lindex $tki_list 0]
set name [lindex $tki_list 1]
set address [lindex $tki_list 2]
set id1 [lindex $sql_list 0]
set name1 [lindex $sql_list 1]
set address1 [lindex $sql_list 2]
set result [ined confirm [join $text " "] \
" tki-name: $name SQL-name: $name1" \
"tki-address: $address SQL-address: $address1" \
"Which entry is correct ?" \
[list tki-entry SQL-entry "They\'re not identical"]]
switch $result {
"tki-entry" {
set query "UPDATE $table SET name='$name',\
address='$address' WHERE id = $id1"
# ined acknowledge "$query"
msqlexec $db $query
return 1
}
"SQL-entry" {
ined name $id $name1
ined address $id $address1
return 1
}
"They\'re not identical" {
return 0
}
}
}
#This proc first gets the msql entries id, name and address of the table
#belonging to the type of id. If there already is a msql entry belonging
#to id (that means the tkined name and address attributes are identical to
#the name and address stored in the msql database; if only name or only
#address are identical, the user is requested whether the msql entry belongs
#to the tkined object with name or address. If the answer is "YES" the user
#is asked which of the differing entries is the correct one, and the
#inconsistence is repaired.) the tkined attribute
#"SQL-ID" with the value of the id of the msql entry is created and the
#return-value of GetMinID is -1. If no msql entry belonging to id is found
#and the tkined attribute SQL-ID exists SQL-ID is returned. If SQL-ID does
#not yet exist the value of the minimal nonused msql-id is returned.
proc GetMinID { id } {
global db
set table [ined type $id]
set name [ined name $id]
set address [ined address $id]
msqlsel $db "SELECT $table.id,$table.name,$table.address FROM $table \
ORDER BY $table.id,$table.name,$table.address"
for {set row [msqlnext $db]} {$row != ""} {set row [msqlnext $db]} {
lappend list $row
}
set minid 1
if {[info exists list]} {
foreach tuple $list {
set id1 [lindex $tuple 0]
set name1 [lindex $tuple 1]
set address1 [lindex $tuple 2]
if {($name == $name1) && ($address == $address1)} {
ined attribute $id SQL-ID $id1
return -1
} else {
if {($name == $name1) && ($address != $address1)} {
set text "Object of type $table and name '$name' \
already exist with different address."
if {[UpDateEntry $text $table [list $id $name $address] \
[list $id1 $name1 $address1]]} {
ined attribute $id SQL-ID $id1
return -1
}
}
if {($name != $name1) && ($address == $address1) && \
($address != "")} {
set text "Object of type $table and address \
'$address' already exist with different name."
if {[UpDateEntry $text $table [list $id $name $address] \
[list $id1 $name1 $address1]]} {
ined attribute $id SQL-ID $id1
return -1
}
}
}
lappend idlist $id1
}
for {} {[lsearch $idlist $minid] != -1} {} {incr minid}
set sqlid [ined attribute $id SQL-ID]
# wenn das Attribut SQL-ID schon vorhanden ist und der Wert in bones
# noch nicht vergeben ist, verwende den alten Wert.
if {( $sqlid != "" )} {
set idindex [lsearch $idlist $sqlid]
if {($idindex == -1)} {
return $sqlid
} else {
set tuple [lindex $list $idindex]
if {([lindex $tuple 0] != $sqlid)} {
ined acknowledge "wrong tuple sqlid=$sqlid \
id1=[lindex $tuple 0]"
return -1
}
set text [list "SQL entry in table $table with \
SQL-ID $sqlid already exists" \
"with different name and (or) address."]
if {[UpDateEntry $text $table [list $id $name $address] \
$tuple]} {
return -1
}
}
}
}
ined attribute $id SQL-ID $minid
return $minid
}
proc FillTuple { id } {
global db
set table [ined type $id]
set query "insert into $table (id,name,address) values \
([ined attribute $id SQL-ID],'[ined name $id]','[ined address $id]')"
# ined acknowledge $query
msqlexec $db $query
}
foreach id [getids {GROUP}] {
if {( [ined expand $id] != "" )} { lappend collapseids $id}
}
foreach id [getids {NODE NETWORK REFERENCE}] {
# ined acknowledge [ined type $id]
set color [ined color $id]
ined color $id yellow
set sqlid [GetMinID $id]
if {( $sqlid != -1 )} {
FillTuple $id
}
ined color $id $color
}
if {[info exists collapseids]} {
foreach id $collapseids {
ined collapse $id
}
}
exit
- ---cut-here-------------------
------- End of Forwarded Message