3.2. Skaffa fram rådatat

Vi utgår från att vi har PostgreSQL körande, med möjlighet att koppla upp via TCP/IP. Vi har endast 2 tabeller tills vidare, skapade så här:

        create table master (
          m_id integer,
          some_data char(20),
          primary key (m_id));

        create table slave (
          m_id integer,
          s_id integer,
          some_other_data char(30),
          primary key (m_id, s_id),
          foreign key (m_id) references master (m_id));
      

Vi ska nu få till en fil med detta useende:

      <Tables>
        <Table>
          <Name>
          <Keys>
            <Key>
              <Name>
              <IsPrimary>
              <IsUnique>
              <Definition>
            </Key>
          <Keys>
          .
          .
          <Relations>
          </Relations>
          .
          .
          <Fields>
            <Fielddesc>
              <Name>
              <Type>
              <NotNull>
              <HasDef>
              <FieldNum>
            </Fielddesc>
          </Fields>
          .
          .
        </Table>
        .
        .
       </Tables>
      

Vi bryr oss inte om relationerna i detta exempel.

Ett sätt att få fram detta utseende är att köra detta tcl-skript. Vi kör det genom att skicka stdout till tables.xml. Databasen vi skall dokumentera heter bnl. Se till att skriptet är exekverbart.

       
[bnl@della source]$make_tables.tcl bnl > tables.xml
    

# ExtractDefinition :
# Get the description of tables in database provided
# as first argument to the script, in XML format, to stdout
# Björn Lundin 2003-06-21

#!/bin/sh
# \
exec pgtclsh $0 $*

proc getFields {Connection The_Table} {
        set Fields {}
        set res [pg_exec $Connection \
          "SELECT a.attname, \
             pg_catalog.format_type(a.atttypid, a.atttypmod), \
             a.attnotnull, a.atthasdef, a.attnum \
           FROM pg_catalog.pg_attribute a \
           WHERE a.attrelid = (select relfilenode from pg_catalog.pg_class \
                               where relname = '$The_Table') \
           AND a.attnum > 0 AND NOT a.attisdropped \
           ORDER BY a.attnum"]
        set ntups [pg_result $res -numTuples]
        for {set i 0} {$i < $ntups} {incr i} {
           lappend Fielddata [pg_result $res -getTuple $i]
        }
        puts "    <Fields>"
        foreach Field $Fielddata {
          puts "      <Fielddesc>"
          puts "        <Name>[lindex $Field 0]</Name>"
          puts "        <Type>[lindex $Field 1]</Type>"
          puts "        <NotNull>[lindex $Field 2]</NotNull>"
          puts "        <HasDef>[lindex $Field 3]</HasDef>"
          puts "        <FieldNum>[lindex $Field 4]</FieldNum>"
          puts "      </Fielddesc>"
        }
        puts "    <Fields>"
        pg_result $res -clear
}

proc getKeys {Connection The_Table} {
        set Fields {}
        set res [pg_exec $Connection \
          "SELECT c2.relname, i.indisprimary, i.indisunique, \
                 pg_catalog.pg_get_indexdef(i.indexrelid) \
           FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i \
           WHERE c.oid = (select relfilenode from pg_catalog.pg_class \
                               where relname = '$The_Table') \
           AND c.oid = i.indrelid AND i.indexrelid = c2.oid \
           ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname"]
        set ntups [pg_result $res -numTuples]
        for {set i 0} {$i < $ntups} {incr i} {
           lappend Keydata [pg_result $res -getTuple $i]
        }
        puts "    <Keys>"
        foreach Key $Keydata {
          puts "      <Key>"
          puts "        <Name>[lindex $Key 0]</Name>"
          puts "        <IsPrimary>[lindex $Key 1]</IsPrimary>"
          puts "        <IsUnique>[lindex $Key 2]</IsUnique>"
          puts "        <Definition>[lindex $Key 3]</Definition>"
          puts "      </Key>"
      }
      puts "    </Keys>"
      pg_result $res -clear
}

proc getRelations {Connection The_Table} {
        puts "    <Relations>"
        puts "    </Relations>"
}


proc ExtractDefinition { db {host "localhost"} {port "5432"} } {
    set conn [pg_connect $db -host $host -port $port]
    #Put all tables in list
    set res [pg_exec $conn \
    "SELECT  c.relname as Name \
     FROM pg_catalog.pg_class c \
       LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner \
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \
     WHERE c.relkind ='r' \
       AND n.nspname NOT IN ('pg_catalog', 'pg_toast') \
       AND pg_catalog.pg_table_is_visible(c.oid) \
     ORDER BY c.relname"]
    set ntups [pg_result $res -numTuples]
    for {set i 0} {$i < $ntups} {incr i} {
        lappend Tables [pg_result $res -getTuple $i]
    }
    pg_result $res -clear

    puts "<Tables>"
    foreach Table $Tables {
        puts "  <Table>"
        puts "    <Name>$Table</Name>"
    #For each table put keys
        getKeys $conn $Table
    #For each table put relations (implemented as dummy)
        getRelations $conn $Table
    #For each table put fields
        getFields $conn $Table
        puts "  </Table>"
    }
    puts "</Tables>"

    pg_disconnect $conn
    return ""
}

#Main
ExtractDefinition [lindex $argv 0]