dumping sybase schema

Posted by anton
on Tuesday, October 30, 2007

currently i have a privilege to work with sybase 12.5. perhaps i am spoiled with the ease of mysqldump db_name [tables] or echo .dump [tables] | sqlite3, but i expect any modern database to have a scriptable way to dump schema for selected tables in create statements, as well as data in insert statements that simply could be piped back when needed.

while dumping data is easy enough using bcp, scriptable schema extracts are a bit trickier (especially if you want it to be cross-platform).

but we are lucky – it took sybase only 20+ years to introduce a command-line utility written in java called ddlgen in version 15 of its flagship enterprise product (in my case it worked against 12.5 as well).

ddlgen

$ ls -lR c:/programs/sybase/ddlgen/
c:/programs/sybase/ddlgen/:
    ddlgen.sh
    lib/

c:/programs/sybase/ddlgen/lib:
    DDLGen.jar
    dsparser.jar
    jconn3.jar
  • rig up the wrapper script:
$ cat c:/programs/sybase/ddlgen/ddlgen.sh 
JAVA_HOME=c:/programs/java/jdk/jdk1.6.0_03
LIB_DIR=`dirname $0`/lib
CLASSPATH=$LIB_DIR/jconn3.jar:$LIB_DIR/dsparser.jar:$LIB_DIR/DDLGen.jar

$JAVA_HOME/bin/java \
-mx500m \
-classpath `cygpath --mixed --path $CLASSPATH` \
com.sybase.ddlgen.DDLGenerator $*

backup scripts

  • schema-out.sh
source env.sh

[ ! -d $OUT_DIR ] && mkdir -p $OUT_DIR

for table in $TABLES; do
    out_file=`cygpath --mixed --absolute $OUT_DIR/${table}-schema.txt`
    printf "dumping $table schema to $out_file... " 
    $DDLGEN -U $USERNAME -P $PASSWORD -S $SERVER:$PORT -D $DATABASE -TU -N$table -O $out_file
    printf "done\n" 
done
  • bcp-out.sh
source env.sh

[ ! -d $OUT_DIR ] && mkdir -p $OUT_DIR

LOG=`dirname $0`/bcp-out.log
cat /dev/null > $LOG

for table in $TABLES; do
    out_file=`cygpath --mixed --absolute $OUT_DIR/${table}-bcp.txt`
    printf "dumping $table to $out_file... " 
    bcp $DATABASE.dbo.$table out $out_file -c -t, -S $SERVER -U $USERNAME -P $PASSWORD >> $LOG
    printf "done\n" 
done
  • schema-in.sh
source env.sh

LOG=`dirname $0`/schema-in.log
cat /dev/null > $LOG

for table in $TABLES; do
    in_file=`cygpath --mixed --absolute $OUT_DIR/${table}-schema.txt`
    [ ! -f $in_file ] && echo "$in_file does not exist for $table, skipping" && continue
    printf "loading $table schema from $in_file... " 
    isql -S$SERVER -U$USERNAME -P$PASSWORD < $in_file >> $LOG
    printf "done\n" 
done
  • bcp-in.sh
source env.sh

[ ! -d $OUT_DIR ] && mkdir -p $OUT_DIR

LOG=`dirname $0`/bcp-in.log
cat /dev/null > $LOG

for table in $TABLES; do
    in_file=`cygpath --mixed --absolute $OUT_DIR/${table}-bcp.txt`
    [ ! -f $in_file ] && echo "$in_file does not exist for $table, skipping" && continue
    printf "loading $table from $in_file... " 
    bcp $DATABASE.dbo.$table in $in_file -c -t, -S $SERVER -U $USERNAME -P $PASSWORD >> $LOG
    printf "done\n" 
done

do i feel silly? yes. do i feel petty? yes. does it make me feel better about myself, given that the sybase DBA told me to contact dbartisan support to see if i could script their tool to do this? oh yes.

cafe babe

Posted by anton
on Saturday, October 27, 2007

background: 10K+ compiled class files and sources that got out of sync1; need to figure out which sources are valid, and which ones are not.

decompiling things is the last resort, since sources produced are not easily diff‘able against the sources you’ve got. the likes of diffj is not much help either, and i did not even want to go down the rabbit hole of normalization through obfuscators.

so if you do not feel like wielding antlr or javacc to normalize two sources, the obvious approach is to simply recompile and compare with the existing class files (just beware of missing class files that might not have any sources at all).

however, keep in mind that javac by default includes line number table in the class file it produces2. this means that even if you added or removed a line of comments or even a blank line before any sources, it would result in a classfile that is different from the original.

sometimes you have another class inside the .java file (not to be confused with inner classes). in this case it gets compiled into a separate class file. so if your main class’ source code has changed, it will affect the line number table of the other class as well. this means that even though another class’ source has not changed, its generated class file will be different.

in my case i also had to check which jdk compiler produced the class files. one can always opt for javap that prints minor and major versions, or if you are feeling manly enough, whip out your favorite hex editor and check bytes 6 and 7 (according to the vm spec). in general, javap is the easiest way to check the internal structure of the class file.

finally, to diff files and directories i simply used svn – check the originals into the local repo, then put your stuff in a working copy – it will do the rest. after all, this is what it’s good at.

oh and why CAFE BABE? look it up

1 this is a whole different and interesting topic – how any sort of generated content creates a possibility of this disconnect. all these xdoclets, jaxb-generated sources, and even compiled classfiles create artifacts that now have a potential of getting out of sync with the sources. yes, proper engineering practices mitigate the risks, but all things being equal, i like the fact that with scripting languages this problem is largely non-existent. what you see is what you run.

2 you could always run javac with -g:none to get rid of line numbers, but it was no help in my case.

quote on scaling up vs. scaling out

Posted by anton
on Monday, October 01, 2007

Only focusing on 50X just gives you faster Elephants, not the revolutionary new breeds of animals that can serve us better.

(by werner vogels)