Re: Ingres -> Oracle Conversion Kits.
From: Johan Montald <johan_at_dombo>
Date: 20 Sep 1994 07:42:47 GMT
Message-ID: <35m3pn$9v3_at_zebedee.ingres.co.uk>
#!/bin/sh
# to extract, remove the header and type "sh filename"
if `test ! -d ./db_unload`
then
mkdir ./db_unload
echo "mkdir ./db_unload"
fi
if `test ! -d ./db_unload/ORACLE`
then
mkdir ./db_unload/ORACLE
echo "mkdir ./db_unload/ORACLE"
fi
if `test ! -s ./db_unload/ORACLE/MAKEFILE` then
echo "writing ./db_unload/ORACLE/MAKEFILE" cat > ./db_unload/ORACLE/MAKEFILE << '\Rogue\Monster\'
# When using AT&T C++ the following symbols are defined
# ------------The following for AT&T C++ ---
# This program, although it compiles, will not run if you use the AT&T
# compiler. It will crash as soon as it tries to connect to the DB.
ORACLE_INCLUDE = $(ORACLE_HOME)/c/lib
PCCFLAGS= include=$(ORACLE_INCLUDE) ireclen=511 oreclen=132 host=C\
C: $(DOBJ:.o=.c)
*/
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/DEQUOTE.C" fi
if `test ! -s ./db_unload/ORACLE/DERESERV.C` then
echo "writing ./db_unload/ORACLE/DERESERV.C" cat > ./db_unload/ORACLE/DERESERV.C << '\Rogue\Monster\' /* DERESERVE.C This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/DERESERV.C" fi
if `test ! -s ./db_unload/ORACLE/BOOL.H` then
echo "writing ./db_unload/ORACLE/BOOL.H" cat > ./db_unload/ORACLE/BOOL.H << '\Rogue\Monster\' #ifndef _BOOL_
#define _BOOL_
enum bool {FALSE=0, TRUE=1};
#endif
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/BOOL.H" fi
if `test ! -s ./db_unload/ORACLE/DATA_DIC.HPP` then
echo "writing ./db_unload/ORACLE/DATA_DIC.HPP" cat > ./db_unload/ORACLE/DATA_DIC.HPP << '\Rogue\Monster\' /* DATA_DICT.HPP Oracle Data Dictionary structures
#define stricmp strcasecmp
const char *dereserve(const char *);
#else
extern "C" const char *dereserve(const char *); #endif
};
};
};
}
}
inline View::~View()
{
}
}
#endif
#endif
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/DATA_DIC.HPP" fi
if `test ! -s ./db_unload/ORACLE/COLUMN.PCC` then
echo "writing ./db_unload/ORACLE/COLUMN.PCC" cat > ./db_unload/ORACLE/COLUMN.PCC << '\Rogue\Monster\' /* COLUMN.PCC Process a column definition, while converting a database table from Oracle to Ingres.
EXEC SQL END DECLARE SECTION; Column::Column(long num, Table *up, Column *succ) {
}
strcpy(cname, sql_cname);
}
return s;
}
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/COLUMN.PCC" fi
if `test ! -s ./db_unload/ORACLE/C_DATA.PCC` then
echo "writing ./db_unload/ORACLE/C_DATA.PCC" cat > ./db_unload/ORACLE/C_DATA.PCC << '\Rogue\Monster\' /* C_DATA Convert an Oracle database to an Ingres database.
*/
void affix(const char *filename);
EXEC SQL END DECLARE SECTION; main(int argc, char *argv[])
}
cout << form("\ncommit%s\n", GoString); }
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/C_DATA.PCC" fi
if `test ! -s ./db_unload/ORACLE/INDEX.PCC` then
echo "writing ./db_unload/ORACLE/INDEX.PCC" cat > ./db_unload/ORACLE/INDEX.PCC << '\Rogue\Monster\' /* INDEX.PCC This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
*/
#include "data_dict.hpp"
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION; Index::Index(Table *up, const char iname[]) :
}
}
}
s << form(")%s\n", GoString);
return s;
}
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/INDEX.PCC" fi
if `test ! -s ./db_unload/ORACLE/TABLE.PCC` then
echo "writing ./db_unload/ORACLE/TABLE.PCC" cat > ./db_unload/ORACLE/TABLE.PCC << '\Rogue\Monster\' /* TABLE.PCC Build the representation of an Oracle table and all its indexes in memory. We can then output the appropriate SQL to reconstruct it in another database.
*/
#include <data_dict.hpp>
}
}
s << form(")%s%s\n",
}
for (int i=(OracleOut?0:1); i<t.IndexCount; i++) {
}
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/TABLE.PCC" fi
if `test ! -s ./db_unload/ORACLE/VIEW.PCC` then
echo "writing ./db_unload/ORACLE/VIEW.PCC" cat > ./db_unload/ORACLE/VIEW.PCC << '\Rogue\Monster\' /* VIEW.PCC This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
EXEC SQL END DECLARE SECTION; View::View(const char vname[], const char *user) :
}
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/VIEW.PCC" fi
if `test ! -s ./db_unload/ORACLE/PREFIX.SQL` then
echo "writing ./db_unload/ORACLE/PREFIX.SQL" cat > ./db_unload/ORACLE/PREFIX.SQL << '\Rogue\Monster\' set autocommit on;
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/PREFIX.SQL" fi
if `test ! -d ./db_unload/INGRES`
then
mkdir ./db_unload/INGRES
echo "mkdir ./db_unload/INGRES"
fi
if `test ! -s ./db_unload/INGRES/C_DATA.SCC` then
echo "writing ./db_unload/INGRES/C_DATA.SCC" cat > ./db_unload/INGRES/C_DATA.SCC << '\Rogue\Monster\' /* C_DATA Convert an Ingres database to an SQL database.
*/
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/C_DATA.SCC" fi
if `test ! -s ./db_unload/INGRES/VIEW.SCC` then
echo "writing ./db_unload/INGRES/VIEW.SCC" cat > ./db_unload/INGRES/VIEW.SCC << '\Rogue\Monster\' #include "data_dict.hpp"
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION; View::View(const char vname[], const char *vowner) :
}
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/VIEW.SCC" fi
if `test ! -s ./db_unload/INGRES/DATA_DIC.HPP` then
echo "writing ./db_unload/INGRES/DATA_DIC.HPP" cat > ./db_unload/INGRES/DATA_DIC.HPP << '\Rogue\Monster\' // DATA_DICT.HPP // Oracle Data Dictionary structures
};
};
};
}
}
inline View::~View()
{
}
}
#endif
#endif
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/DATA_DIC.HPP" fi
if `test ! -s ./db_unload/INGRES/MAKEFILE` then
echo "writing ./db_unload/INGRES/MAKEFILE" cat > ./db_unload/INGRES/MAKEFILE << '\Rogue\Monster\'
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/MAKEFILE" fi
if `test ! -s ./db_unload/INGRES/INDEX.SCC` then
echo "writing ./db_unload/INGRES/INDEX.SCC" cat > ./db_unload/INGRES/INDEX.SCC << '\Rogue\Monster\' /* INDEX.SCC This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk) */
#include "data_dict.hpp"
#include <assert.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION; /*
}
}
}
s << form(")%s\n", GoString);
return s;
}
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/INDEX.SCC" fi
if `test ! -s ./db_unload/INGRES/TABLE.SCC` then
echo "writing ./db_unload/INGRES/TABLE.SCC" cat > ./db_unload/INGRES/TABLE.SCC << '\Rogue\Monster\' /* TABLE.SCC Build the representation of an Ingres table and all its indexes in memory. We can then output the appropriate SQL to reconstruct it in another database.
}
}
}
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/TABLE.SCC" fi
if `test ! -s ./db_unload/INGRES/COLUMN.SCC` then
echo "writing ./db_unload/INGRES/COLUMN.SCC" cat > ./db_unload/INGRES/COLUMN.SCC << '\Rogue\Monster\' /* COLUMN.SCC Process a column definition, while converting a database table from Ingres to SQL.
EXEC SQL END DECLARE SECTION; Column::Column(long num, Table *up, Column *succ) {
}
}
}
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/COLUMN.SCC" fi
if `test ! -s ./db_unload/INGRES/DEQUOTE.C` then
echo "writing ./db_unload/INGRES/DEQUOTE.C" cat > ./db_unload/INGRES/DEQUOTE.C << '\Rogue\Monster\' /* DEQUOTE.C This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
*/
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/DEQUOTE.C" fi
if `test ! -s ./db_unload/INGRES/README` then
echo "writing ./db_unload/INGRES/README" cat > ./db_unload/INGRES/README << '\Rogue\Monster\' This program unloads an Ingres database as an SQL stream. I wrote it for converting Ingres DBs to Oracle. There is also an equivalent, and very similar, program for converting Oracle to Ingres.
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/DERESERV.C" fi
if `test ! -s ./db_unload/README`
then
echo "writing ./db_unload/README"
cat > ./db_unload/README << '\Rogue\Monster\' These programs unload an Ingres or Oracle database as an SQL stream. I wrote them for converting databases back and forth when porting an application from Oracle to Ingres and the data also had to be moved.
Date: 20 Sep 1994 07:42:47 GMT
Message-ID: <35m3pn$9v3_at_zebedee.ingres.co.uk>
ctr_at_cybernetics.net wrote:
: Hello. My company is looking into using Oracle as our primary database : application. Since we currently use Ingres, I was wondering if anyone has : found/used any conversion tools. Let me know what type of experiences, : good and bad, that you have had. Thank you for your time.
: Kevin Eberwein
: ClinTrials Research, Inc. - RTP Division
Hi,
This was posted some time ago for doing a conversion from Ingres to Oracle, its supposed to work in both ways.
DATABASE UNLOAD UTILITY
This is the sources of two programs to unload an Oracle or Ingres database as an SQL stream.
--------------Cut here-----------------
#!/bin/sh
# to extract, remove the header and type "sh filename"
if `test ! -d ./db_unload`
then
mkdir ./db_unload
echo "mkdir ./db_unload"
fi
if `test ! -d ./db_unload/ORACLE`
then
mkdir ./db_unload/ORACLE
echo "mkdir ./db_unload/ORACLE"
fi
if `test ! -s ./db_unload/ORACLE/MAKEFILE` then
echo "writing ./db_unload/ORACLE/MAKEFILE" cat > ./db_unload/ORACLE/MAKEFILE << '\Rogue\Monster\'
# When using AT&T C++ the following symbols are defined
# c_plusplus, __cplusplus, unix, sun
# When using g++ the folllowing symbols are defined
# __GNUG__, __GNUC__, __cplusplus SUFFIXES: .c .sc .pc .C .cc .pcc
# ------------The following for AT&T C++ ---
# This program, although it compiles, will not run if you use the AT&T
# compiler. It will crash as soon as it tries to connect to the DB.
#SUFFIX = C #G++ = CC #CC =/usr/5bin/cc #G++FLAGS= -sys5 -I. -g #CFLAGS = -I. -I/usr/5include -g #LDFLAGS = -Bstatic #---- AT&T C++ with gcc as the back end. This one works #SUFFIX = C #G++ = CC #CC=gcc#/usr/5bin/cc #G++FLAGS= -sys5 -I. -g #CFLAGS = -I/home/pisces/ingres_conv/raph/lib/gcc-include -I. -I/usr/5include -g #LDFLAGS = -Bstatic #SQLLIB = -L/usr/lang/SC1.0 -lC #------------ The following for GNU g++ SUFFIX = cc G++ = g++ CC = gcc G++FLAGS= -I. -I/usr/local/include -g -O -w CFLAGS = -I. -g -O LDFLAGS = -static #------------------------------------- GCC = gcc PCC = pcc DBNAME = rob_dev USERID = rob_dev/rob_dev
ORACLE_INCLUDE = $(ORACLE_HOME)/c/lib
PCCFLAGS= include=$(ORACLE_INCLUDE) ireclen=511 oreclen=132 host=C\
maxopencursors=20
ORA_C_LIBDIR = $(ORACLE_HOME)/c/lib
ORA_RDBMS_LIBDIR = $(ORACLE_HOME)/rdbms/lib
ORALIBS = $(ORA_RDBMS_LIBDIR)/libsql.a $(ORA_RDBMS_LIBDIR)/osntab.o \ $(ORA_RDBMS_LIBDIR)/libsqlnet.a $(ORA_RDBMS_LIBDIR)/libora.a
BINDIR=/home/pisces/ingres_conv/raph/bin SRC=\
makefile\ TODO\ data_dict.hpp\ c_data.pcc\ column.pcc\ dequote.c\ dereserve.c\ index.pcc\ table.pcc\ view.pcc
# Object files for the database conversion program
DOBJ=\
c_data.o\
column.o\
dequote.o\
dereserve.o\
index.o\
table.o\
view.o
YACC = bison
YFLAGS = -dvy
#.pcc.c:
# $(PCC) iname=$*.pcc oname=/tmp/$*.x.cc $(PCCFLAGS)
# sed -e '/^# *[0-9]/d' -e '/struct *sqlca/s//struct sql_ca/' \
# -e '/struct *SQLDA/s//struct SQL_DA/' \
# -e '/extern *sql.*();/d' < /tmp/$*.x.cc > /tmp/$*.${SUFFIX}
# $(G++) $(G++FLAGS) /tmp/$*.${SUFFIX} >$_at_
pcc.o:
$(PCC) iname=$*.pcc oname=/tmp/$*.x.cc $(PCCFLAGS)
sed -e '/^# *[0-9]/d' -e '/struct *sqlca/s//struct sql_ca/' \
-e '/struct *SQLDA/s//struct SQL_DA/' \
-e '/extern *sql.*();/d' < /tmp/$*.x.cc > /tmp/$*.${SUFFIX}
$(G++) $(G++FLAGS) -c /tmp/$*.${SUFFIX}
cc.o:
$(G++) $(G++FLAGS) -c $*.${SUFFIX}
#-------------------------------------------------------------------------
# NOTE: ORACLE_HOME must be either:
# . set in the user's environment
# . passed in on the command line
# . defined in a modified version of this makefile
#
OCILIB = #$(ORACLE_HOME)/c/lib/libocic.a
#PCCLIBS = $(ORACLE_HOME)/rdbms/lib/libpcc.a $(SQLLIB) $(OCILIB)
PCCLIBS = $(SQLLIB) $(OCILIB)
STLIBS= $(ORACLE_HOME)/rdbms/lib/osntabst.o \
$(ORACLE_HOME)/rdbms/lib/config.o
all: c_data
c_data: $(DOBJ) $(G++) $(G++FLAGS) $(LDFLAGS) -o $_at_ $(DOBJ)\ -L/usr/5lib $(PCCLIBS) $(NETLIBS) $(ORALIBS) $(SQLLIB) pc.c: $(PCC) $(PCCFLAGS) iname=$*.pc pc.o: $(PCC) $(PCCFLAGS) iname=$*.pc oname=/tmp/$*.c $(CC) $(CFLAGS) -c /tmp/$*.c pc: -$(PCC) iname=$*.pc oname=/tmp/$*.c $(PCCFLAGS) userid=$(USERID) $(CC) $(CFLAGS) -o $* /tmp/$*.c -L/usr/5lib $(SQLLIB) $(NETLIBS) $(ORALIBS) $(OTHERLIBS) # #.c.exe: # $(CC) $(CFLAGS) -o $* $*.c $(SQLLIB) $(OCILIB) $(NETLIBS) $(ORALIBS) $(OTHERLIBS)#
#------------------------------------------------------------------------- $(DOBJ): data_dict.hpp print: $(SRC) pr -f $? |lpr touch print printall: pr -f $(SRC) | lpr touch print clean: rm -f *.o core *.lis #------------------------------------------------------- sc.o: esqlc -o.sh -l -f$*.c $< $(CC) -c $(CFLAGS) $*.c sc.c: esqlc -o.sh -l -f$*.c $< test1: test1.o $(CC) -g -o $_at_ test1.o /usr/sun4/ingres/lib/libingres.a -lm backup: $(SRC) -mkdir /disk2/tmp/raph/vers6; chmod 777 /disk2/tmp/raph/vers6 -chmod u+w /disk2/tmp/raph/vers6/* cp $? /disk2/tmp/raph/vers6 -rm -f *.lis touch backup dereserve.o dequote.o: $$(_at_:.o=.c) $(CC) -c $(CFLAGS) $(_at_:.o=.c) test: c_data c_data -d $(DBNAME) install: c_data install -s -c c_data $(BINDIR)
C: $(DOBJ:.o=.c)
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/MAKEFILE"
fi
if `test ! -s ./db_unload/ORACLE/TODO`
then
echo "writing ./db_unload/ORACLE/TODO"
cat > ./db_unload/ORACLE/TODO << '\Rogue\Monster\'
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/TODO"
fi
if `test ! -s ./db_unload/ORACLE/DEQUOTE.C`
then
echo "writing ./db_unload/ORACLE/DEQUOTE.C"
cat > ./db_unload/ORACLE/DEQUOTE.C << '\Rogue\Monster\'
/* DEQUOTE.C
This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
*/
#ifdef SYSV #include <string.h> #define index strchr #else #include <strings.h> #endif /* If s.arr contains embedded apostrophes, we have to replace each one by a doubled apostrophe in order to keep Ingres happy. This we do by counting the total number of apostophes and shuffling data to the right by the number of apostrophes to the left of each byte.
*/
dequote(s)
struct varchar {
short len;
char arr[1];
} *s;
{ int i, j, len, count, count2;
char *p;
if (!(p=index(s->arr, '\'')))
return ;
count = 1;
while (p= index(p+1, '\''))
count++;
count2 = count;
for (i= s->len-1; count > 0; i--) {
if (s->arr[i] == '\'') {
s->arr[i+count] = s->arr[i];
count--;
}
s->arr[i+count] = s->arr[i];
}
s->len += count2;
s->arr[s->len] = 0;
return ;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/DEQUOTE.C" fi
if `test ! -s ./db_unload/ORACLE/DERESERV.C` then
echo "writing ./db_unload/ORACLE/DERESERV.C" cat > ./db_unload/ORACLE/DERESERV.C << '\Rogue\Monster\' /* DERESERVE.C This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
CHANGES:
7/4/92 Change returned type from void to char*
*/
#include <stdio.h>
#ifndef __GNUG__ /* For AT&T compiler */
#define stricmp strcasecmp
#include <strings.h>
#else
#include <string.h>
#endif
static char *reserved[] = {
"command",
"count",
"default",
"file",
"index",
0
};
const char *dereserve(const char name[])
{ int i;
static char buf[100];
for (i=0; reserved[i]; i++) {
if (!stricmp(name, reserved[i])) {
strcpy(buf, name);
strcat(buf, "_x");
return buf;
}
}
return name;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/DERESERV.C" fi
if `test ! -s ./db_unload/ORACLE/BOOL.H` then
echo "writing ./db_unload/ORACLE/BOOL.H" cat > ./db_unload/ORACLE/BOOL.H << '\Rogue\Monster\' #ifndef _BOOL_
#define _BOOL_
enum bool {FALSE=0, TRUE=1};
#endif
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/BOOL.H" fi
if `test ! -s ./db_unload/ORACLE/DATA_DIC.HPP` then
echo "writing ./db_unload/ORACLE/DATA_DIC.HPP" cat > ./db_unload/ORACLE/DATA_DIC.HPP << '\Rogue\Monster\' /* DATA_DICT.HPP Oracle Data Dictionary structures
This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
*/
#ifndef DATA_DICT_ #include <stream.h> #include <bool.h> #ifndef __GNUG__ /* For AT&T compiler */extern "C" void exit(int);
#define stricmp strcasecmp
extern "C" char *strcpy(...); extern "C" int strcasecmp(...); extern "C" int strncmp(...); extern "C" char *strchr(...); extern "C" int strlen(...); #include <bstring.h> /* for bzero() */
const char *dereserve(const char *);
#else
extern "C" const char *dereserve(const char *); #endif
#define DATA_DICT_
#define NAME_LENGTH 33
class Table;
class Index;
class View;
enum col_type { NUM, CHAR, DATE, FLOAT, MONEY};
class DatabaseObject {
char object_name[NAME_LENGTH];
char owner_name[NAME_LENGTH];
public:
DatabaseObject(const char name[], const char owner[]);
const char *name() { return object_name; }
const char *owner() { return owner_name; }
};
class Column {
char cname[NAME_LENGTH];
bool nulls; /* 'NULL' or 'NOT NULL' */
bool WithDefault;
col_type coltype; // CHAR, NUMBER or DATE
int colwidth;
Table *parent;
Column *next;
bool IndexColumn;
public:
const char *name();
Column *link();
int width();
int type();
Column(long number, Table *up, Column *prev);
Column(long number, View *up, Column *prev);
~Column();
friend ostream& operator<<(ostream & s, Column & c);
void set_index_col();
bool is_index_column();
bool non_null() { return !nulls; };
};
class Table :public DatabaseObject {
Column *cols; // The column chain Index **index_list; // The indexes on this table int IndexCount; // sizeof(index_list) bool NoDuplicates; // WITH NODUPLICATES clause public: Table(const char name[], const char *owner=""); ~Table(); friend ostream& operator<<(ostream& s, Table & t); void extract(ostream &s); Column *column(const char cname[]); void SetNoDuplicates(); void grant(ostream&);
};
class View : public DatabaseObject {
char *vtext; // The view text Column *cols; // The column chain public: View(); View(const char [], const char *owner=""); ~View(); friend ostream& operator<<(ostream &s, View& v);};
enum index_type { UNIQUE, NON_UNIQUE};
enum index_order {ASC, DESC};
class Index : public DatabaseObject {
Table *parent;
index_type type; // UNIQUE or non-unique
int index_count;
struct index_field {
index_order order;
char *name;
} *column_list;
public:
Index();
Index(Table *t, char unique);
Index(Table *t, const char iname[]);
~Index();
Index *link();
void modify(ostream& s);
friend ostream& operator<<(ostream &s, Index *ind);
bool OK();
};
inline DatabaseObject::DatabaseObject(const char name[], const char user[])
{ strcpy(object_name, name);
char *s = strchr(object_name, ' ');
if (s) *s = 0;
strcpy(owner_name, user);
s = strchr(owner_name, ' ');
if (s) *s = 0;
}
inline Column::~Column() { if(next) delete next; }
inline const char *Column::name() { return cname; }
inline Column *Column::link() { return next; }
inline Column::type() { return coltype; }
inline Column::width() { return colwidth; }
//inline void Column::set_non_null() { nulls = FALSE; }
inline void Column::set_index_col() { nulls = FALSE; IndexColumn = TRUE;}
inline bool Column::is_index_column() { return IndexColumn; }
inline View::View() : DatabaseObject("", "")
{ vtext = 0;
cols = 0;
}
inline View::~View()
{
delete vtext; if (cols) delete cols;
}
inline Index::Index() : DatabaseObject ("", "") {
parent = 0; column_list=0;
}
inline void Table::SetNoDuplicates() { NoDuplicates = TRUE; }
extern "C" sqlab2(...); extern "C" sqlad2(...); extern "C" sqlbs2(...); extern "C" sqlcls(...); extern "C" sqlexe(...); extern "C" sqlfcc(...); extern "C" sqlfch(...); extern "C" sqliem(...); extern "C" sqllo2(unsigned long *, unsigned char *[], unsigned long [], unsigned short[], unsigned long *, int *, int *, unsigned long *); extern "C" sqlopn(...); extern "C" sqlosq(...); extern "C" sqlsca(...); extern "C" sqlscc(...); extern "C" sqlsch(...); extern "C" sqltfl(...); extern "C" sqltoc(...); extern "C" sqlos2(...); extern "C" sqlclu(...); extern "C" sqlgd2(...); extern "C" void *sqlald(int, int, int); extern "C" void dequote(void *); #ifdef INGRES #define NOT_FOUND 100 #else #define NOT_FOUND 1403
#endif
#ifndef EXTERN #define EXTERN extern #endif #define SQLCA_STORAGE_CLASS static EXTERN char *GoString; EXTERN bool OracleOut; EXTERN bool Default; EXTERN bool NotNull;
#endif
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/DATA_DIC.HPP" fi
if `test ! -s ./db_unload/ORACLE/COLUMN.PCC` then
echo "writing ./db_unload/ORACLE/COLUMN.PCC" cat > ./db_unload/ORACLE/COLUMN.PCC << '\Rogue\Monster\' /* COLUMN.PCC Process a column definition, while converting a database table from Oracle to Ingres.
This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
CHANGES:
7/4/92 Deresrve all names.
*/
#include <data_dict.hpp>
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
static VARCHAR sql_tname[31];
static char
sql_cname[31], sql_coltype[7], sql_nulls[2], sql_defaultval[1000]; static long sql_width, /* Used for char types */ sql_precision, /* Used for numeric types */ sql_scale, sql_colno;
EXEC SQL END DECLARE SECTION; Column::Column(long num, Table *up, Column *succ) {
next = succ;
IndexColumn = FALSE;
parent = up;
sql_colno = num;
strcpy(sql_tname.arr, up->name());
sql_tname.len = strlen(sql_tname.arr);
EXEC SQL SELECT
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable,
data_default
INTO :sql_cname,
:sql_coltype,
:sql_width,
:sql_precision,
:sql_scale,
:sql_nulls,
:sql_defaultval
FROM accessible_columns
WHERE table_name = :sql_tname AND
column_id = :sql_colno;
if (sqlca.sqlcode) {
cerr << "Oracle select error " << sqlca.sqlcode << " in Column::Column(long, Table*, Column*)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
cerr << form("Table name: %s, column id %d\n",
sql_tname.arr, sql_colno);
exit(1);
}
sql_cname[sizeof(sql_cname)-1] = 0;
char *s = strchr(sql_cname, ' ');
if (s) *s = 0;
if (strlen(sql_cname) > 24) {
cerr << form("Column name '%s' exceeds 24 characters\n",
sql_cname);
}
sql_defaultval[sizeof(sql_defaultval)-1] = 0;
sql_coltype[sizeof(sql_coltype)-1] = 0;
sql_nulls[sizeof(sql_nulls)-1] = 0;
strcpy(cname, sql_cname);
if (sql_nulls[0] == 'Y' && !NotNull)
nulls = TRUE;
else
nulls = FALSE;
if (!strncmp(sql_coltype, "NUM", 3)) coltype= NUM;
else if (!strncmp(sql_coltype, "DATE", 4)) coltype= DATE;
else coltype = CHAR;
if (!strncmp(sql_coltype, "LONG", 4))
sql_width = 1000;
if (coltype==NUM)
colwidth = sql_precision;
else
colwidth = sql_width;
}
Column::Column(long num, View *up, Column *succ) {
next = succ;
IndexColumn = FALSE;
parent = (Table*)up;
sql_colno = num;
strcpy(sql_tname.arr, up->name());
sql_tname.len = strlen(sql_tname.arr);
EXEC SQL SELECT
column_name
INTO :sql_cname
FROM accessible_columns
WHERE table_name = :sql_tname AND
column_id = :sql_colno;
if (sqlca.sqlcode) {
cerr << "Oracle select error " << sqlca.sqlcode << " in Column::Column(long, View*, Column*)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
sql_cname[sizeof(sql_cname)-1] = 0;
char *s = strchr(sql_cname, ' ');
if (s) *s = 0;
if (strlen(sql_cname) > 24) {
cerr << form("Column name '%s' exceeds 24 characters\n",
sql_cname);
}
strcpy(cname, sql_cname);
}
ostream& operator<<(ostream& s, Column& c) {
s << form("\t%-.24s\t", dereserve(c.cname));
switch (c.coltype) {
case CHAR:
s << form("%s(%d)",
OracleOut?"VARCHAR":"VARCHAR",
c.colwidth);
break;
case NUM:
s << form(c.colwidth<3? "SMALLINT":
c.colwidth< 5? "INTEGER2":
"INTEGER");
break;
case DATE:
s << form("DATE");
break;
}
if (!c.nulls) {
s << " NOT NULL";
if (Default)
if (!c.IndexColumn)
s << " WITH DEFAULT";
}
return s;
}
// Locate a column by name
Column *Table::column(const char cname[])
{
for (Column *c = cols; c; c= c->link()) if (!stricmp(cname, c->name())) return c; return 0;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/COLUMN.PCC" fi
if `test ! -s ./db_unload/ORACLE/C_DATA.PCC` then
echo "writing ./db_unload/ORACLE/C_DATA.PCC" cat > ./db_unload/ORACLE/C_DATA.PCC << '\Rogue\Monster\' /* C_DATA Convert an Oracle database to an Ingres database.
This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
Options and arguments
-d convert the data as well as the structure -g generate GRANT commands on all tables and views -n generate NOT NULL on all columns -v generate WITH DEFAULT on all NOT NULL columns -o generate Oracle compatible output rather than Ingres. i.e. user ';' instead of '\p\g' 'create index' instead of 'modify' The Ingres manuals always say 'varchar' but the programs will not accept it. db_name name of the DB to be processed. We assume that the -V Views only password is the same as the db-name.
*/
#define EXTERN
EXEC SQL INCLUDE SQLCA;
#include "data_dict.hpp" #include <stream.h> #include <ctype.h>
void affix(const char *filename);
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR db_name[21], /* Database name */ owner[31], /* Pattern for matching owner */ tabtype[8]; /* VIEW or TABLE */ char tname[31], /* Table or view name */ uname[31]; /* Actual owner */
EXEC SQL END DECLARE SECTION; main(int argc, char *argv[])
{ bool ConvertData = FALSE,
Grant = FALSE;
int NumberOfTables = 0;
bool ViewsOnly = FALSE;
int stop;
GoString = "\\p\\g";
strcpy(owner.arr, "%");
owner.len = strlen(owner.arr);
while (argc >1 && argv[1][0] == '-') {
for (int j=1; argv[1][j]; j++) {
switch(argv[1][j]) {
case 'd':
ConvertData = TRUE;
break;
case 'g':
Grant = TRUE;
break;
case 'o': // Oracle compatible output
OracleOut = TRUE;
GoString = ";";
break;
case 'n': // Force NOT NULL on all columns
NotNull = TRUE;
break;
case 'u': // Specify form owner
strcpy(owner.arr, argv[2]);
owner.len = strlen(owner.arr);
argc--;
argv++;
break;
case 'v': // Enable WITH DEFAULT
Default = TRUE;
break;
case 'V':
ViewsOnly = TRUE;
break;
default:
break;
}
}
argc--;
argv++;
}
if (argc < 2) {
db_name.len = 4;
strcpy(db_name.arr, "pcms");
} else {
strcpy(db_name.arr, argv[1]);
db_name.len = strlen(argv[1]);
}
EXEC SQL CONNECT :db_name IDENTIFIED BY :db_name;
if (sqlca.sqlcode ) {
cerr << "Oracle connect error " << sqlca.sqlcode <<"\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
affix("prefix.sql");
if ( argc > 2) { /* If user specified a list */
for (int i = 2; i < argc; i++) {
Table *t = new Table(argv[i]);
cout << *t;
if (ConvertData) {
t->extract(cout);
}
if (Grant)
t->grant(cout);
delete t;
}
affix("postfix.sql");
cout << form("commit%s\n", GoString);
exit(0);
}
/*
Get all tables before all views
*/
if (!ViewsOnly) {
EXEC SQL DECLARE tb CURSOR FOR
SELECT DISTINCT
table_name
FROM user_tables
/*WHERE owner LIKE :owner*/
ORDER BY table_name;
EXEC SQL OPEN tb;
if (sqlca.sqlcode ) {
cerr << "Oracle 'tb' cursor open error " << sqlca.sqlcode << " in ::main()()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (stop=0; !stop; ) {
EXEC SQL FETCH tb
INTO :tname;
switch (sqlca.sqlcode) {
case 0:
tname[sizeof(tname)-1] = 0;
Table *t = new Table(tname);
cout << *t;
if (ConvertData) {
t->extract(cout);
}
if (Grant)
t->grant(cout);
delete t;
break;
default:
cout << form("rollback%s\n", GoString);
cerr << "Oracle fetch error on 'tb' " << sqlca.sqlcode << " in ::main()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
break;
}
if (NumberOfTables++ > 20) {
cout << form("commit%s\n", GoString);
NumberOfTables = 0;
}
}
EXEC SQL CLOSE tb;
} // End !ViewsOnly
EXEC SQL DECLARE vw CURSOR FOR
SELECT DISTINCT
view_name
FROM user_views
ORDER BY view_name;
EXEC SQL OPEN vw;
if (sqlca.sqlcode ) {
cerr << "Oracle 'vw' cursor open error " << sqlca.sqlcode << " in ::main()()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (stop=0; !stop; ) {
EXEC SQL FETCH vw
INTO :tname;
switch (sqlca.sqlcode) {
case 0:
tname[sizeof(tname)-1] = 0;
View *v = new View(tname);
cout << *v;
delete v;
break;
default:
cout << form("rollback%s\n", GoString);
cerr << "Oracle fetch error on 'vw' " << sqlca.sqlcode << " in ::main()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
break;
}
if (NumberOfTables++ > 20) {
cout << form("commit%s\n", GoString);
NumberOfTables = 0;
}
}
EXEC SQL CLOSE vw;
cout << form("commit%s\n", GoString);
affix("postfix.sql");
exit(0);
}
void affix(const char *filename)
{
#ifdef __GNUG__
one_arg_error_handler_t old =
set_File_error_handler(quiet_File_error_handler);
set_File_error_handler(old);
istream priv(filename, io_readonly, a_useonly);
#else
filebuf fb;
fb.open(filename, 1);
istream priv(&fb);
#endif
char privbuf[51];
while (priv.rdstate() == _good) {
priv.getline(privbuf, sizeof privbuf);
cout << privbuf;
}
cout << form("\ncommit%s\n", GoString); }
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/C_DATA.PCC" fi
if `test ! -s ./db_unload/ORACLE/INDEX.PCC` then
echo "writing ./db_unload/ORACLE/INDEX.PCC" cat > ./db_unload/ORACLE/INDEX.PCC << '\Rogue\Monster\' /* INDEX.PCC This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
At version 6.0 all Oracle indexes are in ascending order regardless of what the user requests. CHANGES: 7/4/92 Dereserve all names.
*/
#include "data_dict.hpp"
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
static VARCHAR sql_iname[31], sql_tname[31], sql_colname[31]; static char sql_itype[11], /* UNIQUE or NON UNIQUE */ sql_iorder[5]; static int sql_seq;
EXEC SQL END DECLARE SECTION; Index::Index(Table *up, const char iname[]) :
DatabaseObject(iname, "")
{
parent = up;
column_list = 0;
if (strlen(name()) > 24)
cerr << form("Index name '%s' exceeds 24 characters\n",
name());
strcpy(sql_iname.arr, name());
sql_iname.len = strlen(name());
strcpy(sql_tname.arr, up->name());
sql_tname.len = strlen(sql_tname.arr);
EXEC SQL SELECT count(*)
INTO :sql_seq
FROM user_ind_columns
WHERE index_name = :sql_iname AND
table_name = :sql_tname;
if (sqlca.sqlcode ) {
cerr << "Oracle select (count(*)) error " << sqlca.sqlcode << " in Index::Index(Table*, char[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
EXEC SQL SELECT DISTINCT
LOWER(uniqueness)/* UNIQUE or NONUNIQUE */
INTO :sql_itype
FROM user_indexes
WHERE index_name = :sql_iname AND
table_name = :sql_tname;
if (sqlca.sqlcode ) {
cerr << "Oracle select (index uniqueness) error " << sqlca.sqlcode << " in Index::Index(Table*, char[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
index_count = sql_seq;
column_list = new struct index_field[index_count];
if (!strncmp(sql_itype, "unique", 6))
type = UNIQUE;
else
type = NON_UNIQUE;
EXEC SQL DECLARE col CURSOR FOR
SELECT
column_name,
column_position
FROM user_ind_columns
WHERE index_name = :sql_iname AND
table_name = :sql_tname
ORDER BY column_position;
EXEC SQL OPEN col;
if (sqlca.sqlcode ) {
cerr << "Oracle cursor open (col) error " << sqlca.sqlcode << " in Index::Index(Table*, char[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (int stop = 0; stop == 0; ) {
EXEC SQL FETCH col INTO
:sql_colname,
:sql_seq;
switch (sqlca.sqlcode) {
case 0:
column_list[sql_seq-1].name = new char [sql_colname.len+1];
sql_colname.arr[sql_colname.len] = 0;
strcpy(column_list[sql_seq-1].name, sql_colname.arr);
column_list[sql_seq-1].order = ASC;
break;
default:
cerr << "Oracle fetch (col) error " << sqlca.sqlcode << " in Index::Index(Table*, char[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
break;
}
}
EXEC SQL CLOSE col;
}
Index::~Index()
{
if (!column_list) return; for (int i = 0; i < index_count; i++) delete column_list[i].name; delete column_list;
}
void Index::modify(ostream& s)
{
s << form("modify %.24s to btree %s on ",
dereserve(parent->name()),
type == UNIQUE ? "unique" : "");
char *sep = "\n\t";
for (int i = 0; i < index_count; i++){
s << form("%s%.24s%s",
sep,
dereserve(column_list[i].name),
column_list[i].order == DESC ? " desc" : "");
sep = ",\n\t";
}
s << form("%s\n", GoString);
}
ostream& operator<<(ostream& s, Index *ind) {
s << form("create %sindex %.24s on %.24s (",
OracleOut && ind->type == UNIQUE ? "unique " : "",
dereserve(ind->name()),
dereserve(ind->parent->name()));
char *sep = "\n\t";
for (int i = 0; i < ind->index_count; i++){
s << form("%s%.24s%s",
sep,
dereserve(ind->column_list[i].name),
OracleOut && ind->column_list[i].order == DESC ? " desc" : ""
);
sep = ",\n\t";
}
s << form(")%s\n", GoString);
return s;
}
bool Index::OK()
{ bool result = TRUE;
// Force all columns that are used in indexing to be NON NULL
for (int i = 0; i < index_count; i++) {
Column *c = parent -> column(column_list[i].name);
if (!c) {
cerr << form("Table %s index %s refers to non-existent column %s\n",
parent->name(),
name(),
column_list[i].name);
result = FALSE;
} else {
c->set_index_col();
}
}
// If the index is UNIQUE for the parent table to have WITH NODUPLICATES
if (type == UNIQUE) parent->SetNoDuplicates(); return result;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/INDEX.PCC" fi
if `test ! -s ./db_unload/ORACLE/TABLE.PCC` then
echo "writing ./db_unload/ORACLE/TABLE.PCC" cat > ./db_unload/ORACLE/TABLE.PCC << '\Rogue\Monster\' /* TABLE.PCC Build the representation of an Oracle table and all its indexes in memory. We can then output the appropriate SQL to reconstruct it in another database.
This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
CHANGES:
2/4/91 Restrict Index and table names to 24 characters. 17/4/91 'With NoDuplicates' is not valid for Oracle output. 7/4/92 Never generate 'With NoDuplicates', it is inefficient and busy nothing. Dereserve all names.
*/
#include <data_dict.hpp>
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
static SQLDA *fetchda;
EXEC SQL BEGIN DECLARE SECTION;
static VARCHAR sql_tname[31],
sql_iname[31];
static VARCHAR sql_data[100][240];/**/
static short sql_ind[100],/* */
sql_indcount;
static long sql_colno;
EXEC SQL END DECLARE SECTION;
typedef struct {short len; char arr[1];} VC;
Table::Table(const char tname[], const char *user) :
DatabaseObject(tname, user)
{
cols = 0;
NoDuplicates = FALSE;
if (strlen(name()) > 24)
cerr << form("Table name '%s' exceeds 24 characters\n",
name());
strcpy(sql_tname.arr, name());
sql_tname.len = strlen(name());
EXEC SQL DECLARE col CURSOR FOR
SELECT DISTINCT column_id
FROM accessible_columns
WHERE table_name = UPPER(:sql_tname)
ORDER BY column_id DESC;
EXEC SQL OPEN col;
if (sqlca.sqlcode ) {
cerr << "Oracle cursor open error " << sqlca.sqlcode << " in Table::Table(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (int stop = 0; !stop;) {
EXEC SQL FETCH col
INTO :sql_colno;
switch (sqlca.sqlcode) {
case 0:
cols = new Column(sql_colno, this, cols);
break;
default:
cerr << "Oracle fetch error " << sqlca.sqlcode << " in Table::Table(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
}
}
EXEC SQL CLOSE col;
EXEC SQL SELECT count(*)
INTO :sql_indcount
FROM user_indexes
WHERE table_name = :sql_tname;
if (sqlca.sqlcode ) {
cerr << "Oracle select (count(*)) error " << sqlca.sqlcode << " in Table::Table(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
IndexCount = 0;
index_list = new Index *[sql_indcount];
EXEC SQL DECLARE ind CURSOR FOR
SELECT DISTINCT index_name
FROM user_indexes
WHERE table_name = :sql_tname AND
index_name != :sql_tname
ORDER by index_name ;
EXEC SQL OPEN ind;
if (sqlca.sqlcode ) {
cerr << "Oracle cursor open (index) error " << sqlca.sqlcode << " in Table::Table()()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (stop=0; !stop; ) {
EXEC SQL FETCH ind
INTO :sql_iname;
switch (sqlca.sqlcode) {
case 0:
sql_iname.arr[sql_iname.len] = 0;
Index *ind =
index_list[IndexCount++] =
new Index(this, (char *)sql_iname.arr);
ind->OK();
break;
default:
cout << form("rollback%s\n", GoString);
cerr << "Oracle fetch (index) error " << sqlca.sqlcode << " in Table::Table()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
}
}
EXEC SQL CLOSE ind;
}
Table::~Table()
{
if (cols) delete cols;
for (int i = 0; i < IndexCount; i++) {
delete index_list[i];
index_list[i] = 0;
}
if (index_list)
delete index_list;
}
ostream& operator<<(ostream& s, Table & t) {
s << form("drop table %-.24s%s\n", dereserve(t.name()), GoString); s << form("create table %-.24s(\n", dereserve(t.name())); for (Column *c = t.cols; c; c = c->link()) {
s << *c; if (c->link()) s << ",\n"; else s << "\n";
}
s << form(")%s%s\n",
(0 && t.NoDuplicates && !OracleOut) ? " with noduplicates" : "",
GoString);
if (!OracleOut && t.IndexCount) {
t.index_list[0]->modify(s);
}
for (int i=(OracleOut?0:1); i<t.IndexCount; i++) {
s << t.index_list[i];
}
s.flush();
return s;
}
void Table::extract(ostream &s)
{ int i;
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR selectbuf[5000]; /* Oracle SELECT statement */
EXEC SQL END DECLARE SECTION;
char insertbuf[5000]; /* Ingres INSERT statement */
bzero(selectbuf.arr, sizeof(selectbuf.arr));
bzero(insertbuf, sizeof(insertbuf));
ostream b(sizeof(selectbuf.arr), (char *)selectbuf.arr);
ostream ins(sizeof(insertbuf), insertbuf);
/*
In selectbuf[] build a SELECT statement to use as a cursor to
fetch the Oracle data rows from the table. Simultaneously, in
insertbuf[] build the stem of an INSERT statement to insert
the data into the output table. The actual data values will be
added to the stem as each data row is fetched.
*/
int field_count = 0;
b << "SELECT ";
ins << form("insert into %-.24s (\n", name());
for (Column *c = cols; c; c= c->link()) {
switch (c->type()) {
case NUM:
b << form("TO_CHAR(%s)%s ",
c->name(),
c->link() ? "," : "");
break;
case DATE:
default:
b << form("%s%s", c->name(), c->link() ? ", " : " ");
break;
}
ins << form("\t%-.24s%s\n", c->name(), c->link() ? "," : "");
field_count++;
}
ins << ") values (\n";
b << form("FROM %s", name());
b.flush();
selectbuf.len = strlen(selectbuf.arr);
ins.flush();
/*
We have now built the text of the various SQL statements that
we need. The next step is to tell Oracle about it and the
target variables that we are going to use.
*/
EXEC SQL PREPARE s1 FROM :selectbuf;
if (sqlca.sqlcode ) {
cerr << "Oracle PREPARE error " << sqlca.sqlcode << " for S1 in Table::extract(ostream &)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
cerr << form("Text is: '%s'\n", selectbuf.arr);
s << form("rollback%s\n", GoString);
exit(1);
}
EXEC SQL DECLARE cc CURSOR FOR s1;
/*
Allocate a sqlda for 'field_count' columns with 30-char names.
This will be used to hold pointers to our work variables into
which Oracle will store the data rows fetched from the table.
*/
fetchda = (SQLDA*)sqlald(field_count, 30, 30);
if (!fetchda) {
cerr << form("Oracle error allocating sqlda\n");
s << form("rollback%s\n", GoString);
exit(1);
}
/*
sql_data[][], declared above, can hold 100 data items each of
up to 240 bytes. sql_ind[] are the corresponding 100 indicators.
*/
i = 0;
for (c = cols; c; c= c->link()) {
fetchda->T[i] = 9;
fetchda->V[i] = (char *)&sql_data[i];
fetchda->I[i] = &sql_ind[i];
i++;
}
EXEC SQL OPEN cc;
if (sqlca.sqlcode ) {
cerr << "Oracle cursor open error " << sqlca.sqlcode << " in Table::extract(ostream &)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
EXEC SQL DESCRIBE SELECT LIST FOR s1 INTO fetchda;
if (sqlca.sqlcode ) {
cerr << "Oracle describe-select error " << sqlca.sqlcode << " in Table::extract(ostream &)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
i = 0;
for (c = cols; c; c= c->link()) {
fetchda->T[i] = 9; /* VARCHAR */
switch (c->type()) {
case DATE:
fetchda->L[i] = 11;
break;
case CHAR:
fetchda->L[i] = c->width()+2;
break;
case NUM:
default:
break;
}
// fetchda->V[i] = new char[fetchda->L[i]];
// fetchda->I[i] = new short;
i++;
}
if (fetchda->F < 0) {
cerr << form("Oracle DESCRIBE error - %d fields allocated; %d fields found\n",
field_count, fetchda->F);
exit(1);
}
for (int stop = 0; !stop;) {
EXEC SQL FETCH cc
USING DESCRIPTOR fetchda;
switch (sqlca.sqlcode) {
case 0:
s << insertbuf;
int i = 0;
for (c=cols; c; c = c->link()) {
if (*fetchda->I[i] < 0) { // If NULL value
if (NotNull || c->non_null()) { // Force NOT NULL
cerr << form("Forcing non-null value for %s.%s\n",
name(), c->name());
switch(c->type()) {
case NUM:
s << form("\t0%s\n",
c->link() ? "," : "");
break;
case CHAR:
case DATE:
default:
s << form("\t''%s\n",
c->link() ? "," : "");
break;
}
} else { // Honour NULLs
s << form("\tNULL%s\n",
c->link() ? "," : "");
}
} else if (*fetchda->I[i] > 0) {
cerr << form("Table %s, field %s truncated from %d ch\n",
name(), c->name(), *fetchda->I[i]);
}
if (*fetchda->I[i] >= 0) {
switch(c->type()) {
case NUM:
if (!((VC *)fetchda->V[i])->len)
s << form("\t0%s\n",
c->link() ? "," : "");
else
s << form("\t%.*s%s\n",
((VC *)fetchda->V[i])->len,
((VC *)fetchda->V[i])->arr,
c->link() ? "," : "");
break;
case CHAR:
dequote((VC*)fetchda->V[i]);
case DATE:
default:
s << form("\t'%*.*s'%s\n",
((VC *)fetchda->V[i])->len,
((VC *)fetchda->V[i])->len,
((VC *)fetchda->V[i])->arr,
c->link() ? "," : "");
break;
}
}
i++;
}
s << form(")%s\n", GoString);
break;
default:
cerr << "Oracle fetch error " << sqlca.sqlcode << " in Table::extract(ostream&)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
cerr << form("Table name %s\n", name());
s << form("rollback%s", GoString);
case NOT_FOUND:
stop = 1;
}
}
EXEC SQL CLOSE cc;
#if 0
for (i=0; i < fetchda->N; i++) {
delete fetchda->V[i];
delete fetchda->I[i];
}
#endif
sqlclu(fetchda); // Release the SQLDA
s.flush();
}
void Table::grant(ostream &s)
{ for (Column *c = cols; c; c = c->link()) {
if (!c->is_index_column())
s << form("grant update(%.24s) on %.24s to public%s\n",
dereserve(c->name()), dereserve(name()), GoString);
}
s << form("grant select on %.24s to public%s\n",
dereserve(name()), GoString);
s << form("grant delete on %.24s to public%s\n",
dereserve(name()), GoString);
s << form("grant insert on %.24s to public%s\n",
dereserve(name()), GoString);
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/TABLE.PCC" fi
if `test ! -s ./db_unload/ORACLE/VIEW.PCC` then
echo "writing ./db_unload/ORACLE/VIEW.PCC" cat > ./db_unload/ORACLE/VIEW.PCC << '\Rogue\Monster\' /* VIEW.PCC This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
CHANGES:
7/4/92 Dereserve all names.
*/
#include "data_dict.hpp"
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
static VARCHAR sql_name[31], sql_text[2000]; static long sql_colno;
EXEC SQL END DECLARE SECTION; View::View(const char vname[], const char *user) :
DatabaseObject(vname, "")
{
cols = 0;
strcpy(sql_name.arr, name());
sql_name.len = strlen(name());
EXEC SQL SELECT text
INTO :sql_text
FROM user_views
WHERE view_name = :sql_name;
sql_text.arr[sql_text.len] = 0;
vtext = new char[sql_text.len+1];
strcpy(vtext, sql_text.arr);
EXEC SQL DECLARE col CURSOR FOR
SELECT DISTINCT column_id
FROM accessible_columns
WHERE table_name = UPPER(:sql_name)
ORDER BY column_id DESC;
EXEC SQL OPEN col;
if (sqlca.sqlcode ) {
cerr << "Oracle cursor open error " << sqlca.sqlcode << " in Table::Table(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (int stop = 0; !stop;) {
EXEC SQL FETCH col
INTO :sql_colno;
switch (sqlca.sqlcode) {
case 0:
cols = new Column(sql_colno, this, cols);
break;
default:
cerr << "Oracle fetch error " << sqlca.sqlcode << " in View::View(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
}
}
EXEC SQL CLOSE col;
}
ostream& operator<<(ostream& s,View& v)
{
s << form("drop view %s%s\n", dereserve(v.name()), GoString);
s << form("create view %-.24s (\n", dereserve(v.name()));
for (Column *c = v.cols; c; c = c->link()) {
s << form("\t\t%-.24s", dereserve(c->name()));
if (c->link())
s << ",\n";
else
s << "\n";
}
s << form("\t) as\n%s%s\n", v.vtext, GoString);
s.flush();
return s;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/VIEW.PCC" fi
if `test ! -s ./db_unload/ORACLE/PREFIX.SQL` then
echo "writing ./db_unload/ORACLE/PREFIX.SQL" cat > ./db_unload/ORACLE/PREFIX.SQL << '\Rogue\Monster\' set autocommit on;
\Rogue\Monster\
else
echo "will not over write ./db_unload/ORACLE/PREFIX.SQL" fi
if `test ! -d ./db_unload/INGRES`
then
mkdir ./db_unload/INGRES
echo "mkdir ./db_unload/INGRES"
fi
if `test ! -s ./db_unload/INGRES/C_DATA.SCC` then
echo "writing ./db_unload/INGRES/C_DATA.SCC" cat > ./db_unload/INGRES/C_DATA.SCC << '\Rogue\Monster\' /* C_DATA Convert an Ingres database to an SQL database.
This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
Options and arguments
-d convert the data as well as the structure -g generate GRANT commands on all tables and views -n generate NOT NULL on all columns -v generate WITH DEFAULT on all NOT NULL columns -o generate Oracle compatible output rather than Ingres. i.e. user ';' instead of '\p\g' 'create index' instead of 'modify' 'vchar' is spellt 'varchar'. The Ingres manuals always say 'varchar' but the programs will not accept it. -u name User who owns the tables etc to be converted db_name name of the DB to be processed. We assume that the -V Views only password is the same as the db-name.
*/
#define EXTERN
EXEC SQL INCLUDE SQLCA;
#include "data_dict.hpp" #include <stream.h> #include <ctype.h> EXEC SQL BEGIN DECLARE SECTION; static char db_name[NAME_LENGTH], /* Database name */ user[NAME_LENGTH], tabtype[8]; /* VIEW or TABLE */ static char tname[NAME_LENGTH]; /* Table or view name */EXEC SQL END DECLARE SECTION; main(int argc, char *argv[])
{ bool ConvertData = FALSE,
Grant = FALSE;
int NumberOfTables = 0;
bool ViewsOnly = FALSE;
int stop;
GoString = "\\p\\g";
strcpy(user, "%");
while (argc >1 && argv[1][0] == '-') {
for (int j=1; argv[1][j]; j++) {
switch(argv[1][j]) {
case 'd':
ConvertData = TRUE;
break;
case 'g':
Grant = TRUE;
break;
case 'o': // Oracle compatible output
OracleOut = TRUE;
GoString = ";";
break;
case 'n': // Force NOT NULL on all columns
NotNull = TRUE;
break;
case 'v': // Enable WITH DEFAULT
Default = TRUE;
break;
case 'V':
ViewsOnly = TRUE;
break;
case 'u': // User name
strcpy(user, argv[2]);
strcat(user, "%");
argc--;
argv++;
break;
default:
break;
}
}
argc--;
argv++;
}
if (argc < 2) {
strcpy(db_name, "raphdb");
} else {
strcpy(db_name, argv[1]);
}
EXEC SQL CONNECT :db_name ;
if (sqlca.sqlcode ) {
cerr << "Ingres connect error " << sqlca.sqlcode <<"\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
/*
Get all tables before all views
*/
if (!ViewsOnly) {
EXEC SQL DECLARE tb CURSOR FOR
SELECT DISTINCT
table_name
FROM iitables
WHERE table_owner LIKE :user and
table_type LIKE 'T%' and
system_use LIKE 'U%'
ORDER BY table_name;
EXEC SQL OPEN tb;
if (sqlca.sqlcode ) {
cerr << "Ingres 'tb' cursor open error " << sqlca.sqlcode << " in ::main()()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (stop=0; !stop; ) {
EXEC SQL FETCH tb
INTO :tname;
switch (sqlca.sqlcode) {
case 0:
Table *t = new Table(tname, user);
cout << *t;
if (ConvertData) {
t->extract(cout);
}
if (Grant)
t->grant(cout);
delete t;
break;
default:
cout << form("rollback%s\n", GoString);
cerr << "Ingres fetch error on 'tb' " << sqlca.sqlcode << " in ::main()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
break;
}
if (NumberOfTables++ > 20) {
cout << form("commit%s\n", GoString);
NumberOfTables = 0;
}
}
EXEC SQL CLOSE tb;
} // End !ViewsOnly
EXEC SQL DECLARE vw CURSOR FOR
SELECT DISTINCT
table_name
FROM iitables
WHERE table_owner LIKE :user and
table_type LIKE 'V%' and
system_use LIKE 'U%'
ORDER BY table_name;
EXEC SQL OPEN vw;
if (sqlca.sqlcode ) {
cerr << "Ingres 'vw' cursor open error " << sqlca.sqlcode << " in ::main()()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (stop=0; !stop; ) {
EXEC SQL FETCH vw
INTO :tname;
switch (sqlca.sqlcode) {
case 0:
View *v = new View(tname, user);
cout << *v;
delete v;
break;
default:
cout << form("rollback%s\n", GoString);
cerr << "Ingres fetch error on 'vw' " << sqlca.sqlcode << " in ::main()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
break;
}
if (NumberOfTables++ > 20) {
cout << form("commit%s\n", GoString);
NumberOfTables = 0;
}
}
EXEC SQL CLOSE vw;
cout << form("commit%s\n", GoString);
exit(0);
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/C_DATA.SCC" fi
if `test ! -s ./db_unload/INGRES/VIEW.SCC` then
echo "writing ./db_unload/INGRES/VIEW.SCC" cat > ./db_unload/INGRES/VIEW.SCC << '\Rogue\Monster\' #include "data_dict.hpp"
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
static char sql_name[NAME_LENGTH], sql_text[257]; static long sql_colno, sql_sequence; static char user[NAME_LENGTH];
EXEC SQL END DECLARE SECTION; View::View(const char vname[], const char *vowner) :
DatabaseObject(vname, vowner)
{
cols = 0;
strcpy(sql_name, name());
strcpy(user, owner());
EXEC SQL DECLARE vv CURSOR FOR
SELECT DISTINCT
text_segment,
text_sequence
FROM iiviews
WHERE table_name = :sql_name
ORDER BY text_sequence;
EXEC SQL OPEN vv;
if (sqlca.sqlcode ) {
cerr << "Ingres cursor 'vv' open error " << sqlca.sqlcode << " in View::View(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
char *s = new char[1];
*s = 0;
while (1) {
EXEC SQL FETCH vv
INTO :sql_text, :sql_sequence;
if (sqlca.sqlcode == NOT_FOUND)
break;
s = realloc(s, strlen(s)+strlen(sql_text));
strcat(s, sql_text);
}
EXEC SQL CLOSE vv;
vtext = s;
EXEC SQL DECLARE col CURSOR FOR
SELECT DISTINCT column_sequence
FROM iicolumns
WHERE table_name = :sql_name AND
table_owner LIKE :user
ORDER BY column_sequence DESC;
EXEC SQL OPEN col;
if (sqlca.sqlcode ) {
cerr << "Ingres cursor 'col' open error " << sqlca.sqlcode << " in View::View(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (int stop = 0; !stop;) {
EXEC SQL FETCH col
INTO :sql_colno;
switch (sqlca.sqlcode) {
case 0:
cols = new Column(sql_colno, this, cols);
break;
default:
cerr << "Ingres fetch error " << sqlca.sqlcode << " in View::View(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
}
}
EXEC SQL CLOSE col;
}
ostream& operator<<(ostream& s,View& v)
{
s << form("drop view %s%s\n", v.name(), GoString); #if 0
s << form("create view %s (\n", v.name());
for (Column *c = v.cols; c; c = c->link()) {
s << form("\t\t%s", c->name());
if (c->link())
s << ",\n";
else
s << "\n";
}
s << form("\t) as\n%s%s\n", v.vtext, GoString);
#else
s << form("%s%s\n", v.vtext, GoString);
#endif
s.flush();
return s;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/VIEW.SCC" fi
if `test ! -s ./db_unload/INGRES/DATA_DIC.HPP` then
echo "writing ./db_unload/INGRES/DATA_DIC.HPP" cat > ./db_unload/INGRES/DATA_DIC.HPP << '\Rogue\Monster\' // DATA_DICT.HPP // Oracle Data Dictionary structures
#ifndef DATA_DICT_ #include <stream.h> #include <bool.h> #define DATA_DICT_ #define NAME_LENGTH 33
class Table;
class Index;
class View;
enum col_type { NUM, CHAR, DATE, FLOAT, MONEY};
class DatabaseObject {
char object_name[NAME_LENGTH];
char owner_name[NAME_LENGTH];
public:
DatabaseObject(const char name[], const char owner[]);
const char *name() { return object_name; }
const char *owner() { return owner_name; }
};
class Column {
char cname[NAME_LENGTH];
bool nulls; /* 'NULL' or 'NOT NULL' */
bool WithDefault;
col_type coltype; // CHAR, NUMBER or DATE
int colwidth;
Table *parent;
Column *next;
public:
const char *name();
bool IndexColumn;
Column *link();
int width();
int type();
Column(long number, Table *up, Column *prev);
Column(long number, View *up, Column *prev);
~Column();
friend ostream& operator<<(ostream & s, Column & c);
void set_index_col();
void set_non_null();
};
class Table :public DatabaseObject {
Column *cols; // The column chain Index **index_list; // The indexes on this table int IndexCount; // sizeof(index_list) bool NoDuplicates; // WITH NODUPLICATES clause public: Table(const char name[], const char *owner=""); ~Table(); friend ostream& operator<<(ostream& s, Table & t); void extract(ostream &s); Column *column(const char cname[]); void SetNoDuplicates(); void grant(ostream&);
};
class View : public DatabaseObject {
char *vtext; // The view text Column *cols; // The column chain public: View(); View(const char [], const char *owner=""); ~View(); friend ostream& operator<<(ostream &s, View& v);};
enum index_type { UNIQUE, NON_UNIQUE};
enum index_order {ASC, DESC};
class Index : public DatabaseObject {
Table *parent;
index_type type; // UNIQUE or non-unique
int index_count;
struct index_field {
index_order order;
char *name;
} *column_list;
public:
Index();
Index(Table *t, char unique);
Index(Table *t, const char iname[]);
~Index();
Index *link();
void modify(ostream& s);
friend ostream& operator<<(ostream &s, Index *ind);
bool OK();
};
inline DatabaseObject::DatabaseObject(const char name[], const char user[])
{ strcpy(object_name, name);
char *s = strchr(object_name, ' ');
if (s) *s = 0;
strcpy(owner_name, user);
s = strchr(owner_name, ' ');
if (s) *s = 0;
}
inline Column::~Column() { if(next) delete next; }
inline const char *Column::name() { return cname; }
inline Column *Column::link() { return next; }
inline Column::type() { return coltype; }
inline Column::width() { return colwidth; }
inline void Column::set_non_null() { nulls = FALSE; }
inline void Column::set_index_col() { nulls = FALSE; IndexColumn = TRUE;}
inline View::View() : DatabaseObject("", "")
{ vtext = 0;
cols = 0;
}
inline View::~View()
{
delete vtext; if (cols) delete cols;
}
inline Index::Index() : DatabaseObject ("", "") {
parent = 0; column_list=0;
}
inline void Table::SetNoDuplicates() { NoDuplicates = TRUE; }
extern "C" sqlab2(...); extern "C" sqlad2(...); extern "C" sqlbs2(...); extern "C" sqlcls(...); extern "C" sqlexe(...); extern "C" sqlfcc(...); extern "C" sqlfch(...); extern "C" sqliem(...); extern "C" sqllo2(...); extern "C" sqlopn(...); extern "C" sqlosq(...); extern "C" sqlsca(...); extern "C" sqlscc(...); extern "C" sqlsch(...); extern "C" sqltfl(...); extern "C" sqltoc(...); extern "C" sqlos2(...); extern "C" sqlclu(...); extern "C" sqlgd2(...); extern "C" void *sqlald(int, int, int); extern "C" void dequote(void *); #ifdef INGRES #define NOT_FOUND 100 #else #define NOT_FOUND 1403
#endif
#ifndef EXTERN #define EXTERN extern #endif EXTERN char *GoString; EXTERN bool OracleOut; EXTERN bool Default; EXTERN bool NotNull;
#endif
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/DATA_DIC.HPP" fi
if `test ! -s ./db_unload/INGRES/MAKEFILE` then
echo "writing ./db_unload/INGRES/MAKEFILE" cat > ./db_unload/INGRES/MAKEFILE << '\Rogue\Monster\'
SUFFIXES: .c .sc .pc .cc .scc .pcc G++ = g++ CC = gcc G++FLAGS= -I.. -I/usr/local/include -g -O -w -DINGRES CFLAGS = -I.. -g -O LDFLAGS = -static GCC = gcc PCC = pcc DBNAME = test USERID = comp/comp PCCFLAGS= include=$(ORACLE_INCLUDE) ireclen=511 oreclen=132 host=C\ maxopencursors=20 # sqlcheck=limited userid=pcms/pcms SRC=\ makefile\ ../data_dict.hpp\ c_data.scc\ column.scc\ index.scc\ table.scc\ view.scc
# Object files for the database conversion program
DOBJ=\
c_data.o\ column.o\ dequote.o\ index.o\ table.o\ view.o YACC = bison YFLAGS = -dvy pcc.o: $(PCC) iname=$*.pcc oname=/tmp/$*.x.cc $(PCCFLAGS) sed -e '/^# *[0-9]/d' -e '/struct *sqlca/s//struct sql_ca/' \ -e '/struct *SQLDA/s//struct SQL_DA/' \ -e '/extern *sql.*();/d' < /tmp/$*.x.cc > /tmp/$*.cc $(G++) $(G++FLAGS) -c /tmp/$*.cc cc.o: $(G++) $(G++FLAGS) -c $*.cc #-------------------------------------------------------------------------#
# NOTE: ORACLE_HOME must be either:
# . set in the user's environment # . passed in on the command line # . defined in a modified version of this makefile
#CC=/usr/5bin/cc
OCILIB = #$(ORACLE_HOME)/c/lib/libocic.a
#PCCLIBS = $(ORACLE_HOME)/rdbms/lib/libpcc.a $(SQLLIB) $(OCILIB)
PCCLIBS = $(SQLLIB) $(OCILIB)
STLIBS= $(ORACLE_HOME)/rdbms/lib/osntabst.o \
$(ORACLE_HOME)/rdbms/lib/config.o
all: c_data
c_data: $(DOBJ) $(G++) $(G++FLAGS) $(LDFLAGS) -o $_at_ $(DOBJ)\ -L/usr/5lib $(II_SYSTEM)/ingres/lib/libingres.a -lm pc.c: $(PCC) $(PCCFLAGS) iname=$*.pc pc.o: $(PCC) $(PCCFLAGS) iname=$*.pc oname=/tmp/$*.c $(CC) $(CFLAGS) -c /tmp/$*.c pc: -$(PCC) iname=$*.pc oname=/tmp/$*.c $(PCCFLAGS) userid=$(USERID) $(CC) $(CFLAGS) -o $* /tmp/$*.c -L/usr/5lib $(SQLLIB) $(NETLIBS) $(ORALIBS) $(OTHERLIBS) # #.c.exe: # $(CC) $(CFLAGS) -o $* $*.c $(SQLLIB) $(OCILIB) $(NETLIBS) $(ORALIBS) $(OTHERLIBS)#
#------------------------------------------------------------------------- $(DOBJ): ../data_dict.hpp print: $(SRC) pr -f $? |lpr touch print printall: pr -f $(SRC) | lpr touch print safe: -chmod a-w $(SRC) clean: rm -f *.o core *.lis #------------------------------------------------------- scc.o: cp $< /tmp/$*.cc $(G++) $(G++FLAGS) -E /tmp/$*.cc > /tmp/$*.sc esqlc -o.sh -f/tmp/$*.cc /tmp/$*.sc $(G++) -c $(G++FLAGS) /tmp/$*.cc scc.cc: cp $< /tmp/$*.cc $(G++) $(G++FLAGS) -E /tmp/$*.cc > /tmp/$*.sc esqlc -o.sh -f$*.cc /tmp/$*.sc sc.o: esqlc -o.sh -f/tmp/$*.c $< $(CC) -c $(CFLAGS) /tmp/$*.c sc.c: esqlc -o.sh -f$*.c $< test1: test1.o gcc -g -o $_at_ test1.o /usr/sun4/ingres/lib/libingres.a -lm backup: $(SRC) -mkdir /common/tmp/raph/ingdata; chmod 777 /common/tmp/raph/ingdata cp $? /common/tmp/raph/ingdata -rm -f *.lis touch backup -$(MAKE) safe dequote.o: ../dequote.c $(CC) -c $(CFLAGS) ../dequote.c test: c_data c_data -d $(DBNAME)
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/MAKEFILE" fi
if `test ! -s ./db_unload/INGRES/INDEX.SCC` then
echo "writing ./db_unload/INGRES/INDEX.SCC" cat > ./db_unload/INGRES/INDEX.SCC << '\Rogue\Monster\' /* INDEX.SCC This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk) */
#include "data_dict.hpp"
#include <assert.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
static char sql_iname[NAME_LENGTH], sql_tname[NAME_LENGTH], sql_colname[NAME_LENGTH]; static char sql_itype[9], /* UNIQUE or NON UNIQUE */ sql_iorder[9]; static int sql_seq; static char user[NAME_LENGTH];
EXEC SQL END DECLARE SECTION; /*
Create a secondary index.
*/
Index::Index(Table *up, const char iname[]) :
DatabaseObject(iname, up->owner())
{
parent = up;
column_list = 0;
index_count = 0;
strcpy(sql_iname, name());
strcpy(sql_tname, up->name());
strcpy(user, up->owner());
EXEC SQL SELECT count(*)
INTO :sql_seq
FROM iiindex_columns
WHERE index_name = :sql_iname AND
index_owner LIKE :user;
if (sqlca.sqlcode ) {
cerr << "Ingres select (count(*)) error " << sqlca.sqlcode << " in Index::Index(Table*, char[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
if (sql_seq <= 0) {
cerr << form("Table %s index %s has no columns\n",
sql_tname, sql_iname);
return;
}
EXEC SQL SELECT DISTINCT
LOWERCASE(unique_rule)/* UNIQUE or NONUNIQUE */
INTO :sql_itype
FROM iiindexes
WHERE index_name = :sql_iname AND
index_owner LIKE :user AND
base_name = :sql_tname;
if (sqlca.sqlcode ) {
cerr << "Ingres select (index uniqueness) error " << sqlca.sqlcode << " in Index::Index(Table*, char[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
index_count = sql_seq;
column_list = new struct index_field[index_count];
if (sql_itype[0] == 'U')
type = UNIQUE;
else
type = NON_UNIQUE;
EXEC SQL DECLARE col CURSOR FOR
SELECT
column_name,
LOWERCASE(sort_direction),
key_sequence
FROM iiindex_columns
WHERE index_name = :sql_iname AND
index_owner LIKE :user
ORDER BY key_sequence;
EXEC SQL OPEN col;
if (sqlca.sqlcode ) {
cerr << "Ingres cursor open (col) error " << sqlca.sqlcode << " in Index::Index(Table*, char[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (int stop = 0; stop == 0; ) {
EXEC SQL FETCH col INTO
:sql_colname,
:sql_iorder,
:sql_seq;
assert(sql_seq > 0);
switch (sqlca.sqlcode) {
case 0:
char *s = strchr(sql_colname, ' ');
if (s) *s = 0;
column_list[sql_seq-1].name
= new char [strlen(sql_colname)+1];
strcpy(column_list[sql_seq-1].name, sql_colname);
column_list[sql_seq-1].order
= (sql_iorder[0] == 'a') ? ASC : DESC;
break;
default:
cerr << "Ingres fetch (col) error " << sqlca.sqlcode << " in Index::Index(Table*, char[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
break;
}
}
EXEC SQL CLOSE col;
}
/*
Create the primary index that arises from a MODIFY statement. It is quite possible to have secondary indexes but no primary index. */
Index::Index(Table *up, char unique) :
DatabaseObject(up->name(), up->owner()) {
parent = up;
column_list = 0;
strcpy(sql_tname, up->name());
strcpy(user, up->owner());
EXEC SQL SELECT count(*)
INTO :sql_seq
FROM iicolumns
WHERE table_name = :sql_tname AND
table_owner LIKE :user AND
key_sequence != 0;
if (sqlca.sqlcode ) {
cerr << "Ingres select (count(*)) error " << sqlca.sqlcode << " in Index::Index(Table*, char)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
index_count = sql_seq;
column_list = new struct index_field[index_count];
if (unique == 'U')
type = UNIQUE;
else
type = NON_UNIQUE;
EXEC SQL DECLARE col2 CURSOR FOR
SELECT
column_name,
LOWERCASE(sort_direction),
key_sequence
FROM iicolumns
WHERE table_name = :sql_tname AND
table_owner LIKE :user AND
key_sequence != 0
ORDER BY key_sequence;
EXEC SQL OPEN col2;
if (sqlca.sqlcode ) {
cerr << "Ingres cursor open (col) error " << sqlca.sqlcode << " in Index::Index(Table*, char)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (int stop = 0; stop == 0; ) {
EXEC SQL FETCH col2 INTO
:sql_colname,
:sql_iorder,
:sql_seq;
assert(sql_seq > 0);
switch (sqlca.sqlcode) {
case 0:
char *s = strchr(sql_colname, ' ');
if (s) *s = 0;
column_list[sql_seq-1].name
= new char [strlen(sql_colname)+1];
strcpy(column_list[sql_seq-1].name, sql_colname);
column_list[sql_seq-1].order
= (sql_iorder[0] == 'a') ? ASC : DESC;
break;
default:
cerr << "Ingres fetch (col) error " << sqlca.sqlcode << " in Index::Index(Table*, char)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
break;
}
}
EXEC SQL CLOSE col2;
}
Index::~Index()
{
if (!column_list) return; for (int i = 0; i < index_count; i++) delete column_list[i].name; delete column_list;
}
void Index::modify(ostream& s)
{
s << form("modify %s to btree %s on ",
parent->name(),
type == UNIQUE ? "unique" : "");
char *sep = "\n\t";
for (int i = 0; i < index_count; i++){
s << form("%s%s%s",
sep,
column_list[i].name,
column_list[i].order == DESC ? " desc" : "");
sep = ",\n\t";
}
s << form("%s\n", GoString);
}
ostream& operator<<(ostream& s, Index *ind) {
s << form("create %sindex %s on %s (",
OracleOut && ind->type == UNIQUE ? "unique " : "",
ind->name(),
ind->parent->name());
char *sep = "\n\t";
for (int i = 0; i < ind->index_count; i++){
s << form("%s%s%s",
sep,
ind->column_list[i].name,
OracleOut && ind->column_list[i].order == DESC ? " desc" : ""
);
sep = ",\n\t";
}
s << form(")%s\n", GoString);
return s;
}
bool Index::OK()
{ bool result = TRUE;
// Force all columns that are used in indexing to be NON NULL
for (int i = 0; i < index_count; i++) {
Column *c = parent -> column(column_list[i].name);
if (!c) {
cerr << form("Table %s index %s refers to non-existent column %s\n",
parent->name(),
name,
column_list[i].name);
result = FALSE;
} else {
c->set_index_col();
}
}
// If the index is UNIQUE for the parent table to have WITH NODUPLICATES
if (type == UNIQUE) parent->SetNoDuplicates(); return result;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/INDEX.SCC" fi
if `test ! -s ./db_unload/INGRES/TABLE.SCC` then
echo "writing ./db_unload/INGRES/TABLE.SCC" cat > ./db_unload/INGRES/TABLE.SCC << '\Rogue\Monster\' /* TABLE.SCC Build the representation of an Ingres table and all its indexes in memory. We can then output the appropriate SQL to reconstruct it in another database.
This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
*/
#include <data_dict.hpp>
#include <assert.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
static IISQLDA *fetchda;
EXEC SQL BEGIN DECLARE SECTION;
static char sql_tname[NAME_LENGTH],
sql_iname[NAME_LENGTH],
sql_duplicates[9], /* WITH NO DUPLICATES */
sql_unique[9]; /* MODIFY to UNIQUE */
static struct {
short len;
char arr[240];
} sql_data[IISQ_MAX_COLS];
static short sql_ind[IISQ_MAX_COLS],
sql_indcount;
static long sql_colno;
static char user[NAME_LENGTH];
EXEC SQL END DECLARE SECTION;
typedef struct {short len; char arr[1];} VC;
Table::Table(const char tname[], const char *towner) :
DatabaseObject(tname, towner)
{
cols = 0;
NoDuplicates = FALSE;
strcpy(sql_tname, name());
strcpy(user, owner());
EXEC SQL SELECT
duplicate_rows,
unique_rule
INTO :sql_duplicates, /* WITH NO DUPLICATES */
:sql_unique /* MODIFY to xxx UNIQUE */
FROM iitables
WHERE table_name = :sql_tname and
table_owner LIKE :user;
if (sqlca.sqlcode ) {
cerr << "Ingres SELECT duplicates error " << sqlca.sqlcode << " in Table::Table(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
NoDuplicates = (sql_duplicates[0] == 'U') ||
(sql_unique[0] == 'U');
EXEC SQL DECLARE col CURSOR FOR
SELECT DISTINCT column_sequence
FROM iicolumns
WHERE table_name = :sql_tname and
table_owner LIKE :user
ORDER BY column_sequence DESC;
EXEC SQL OPEN col;
if (sqlca.sqlcode ) {
cerr << "Ingres cursor open error " << sqlca.sqlcode << " in Table::Table(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (int stop = 0; !stop;) {
EXEC SQL FETCH col
INTO :sql_colno;
switch (sqlca.sqlcode) {
case 0:
cols = new Column(sql_colno, this, cols);
break;
default:
cerr << "Ingres fetch error " << sqlca.sqlcode << " in Table::Table(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
}
}
EXEC SQL CLOSE col;
/*
Count how many indexes there are and allocate space for them
plus one for the primary index, if any.
*/
EXEC SQL SELECT count(*)+1
INTO :sql_indcount
FROM iiindexes
WHERE base_name = :sql_tname AND
index_owner LIKE :user;
if (sqlca.sqlcode ) {
cerr << "Ingres select (count(*)+1) error " << sqlca.sqlcode << " in Table::Table(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
IndexCount = 0;
index_list = new Index *[sql_indcount];
/*
Is there a MODIFY statement for this table ?
*/
EXEC SQL SELECT COUNT(*)
INTO :sql_colno
FROM iicolumns
WHERE table_name = :sql_tname AND
table_owner LIKE :user AND
key_sequence != 0;
if (sqlca.sqlcode ) {
cerr << "Ingres select (count(*)) error " << sqlca.sqlcode << " in Table::Table(char name[])\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
/*
If there is a MODIFY create an index for it.
*/
if (sql_colno > 0) {
Index *ind =
index_list[IndexCount++]
= new Index(this, sql_unique[0]);
if (!ind->OK()) {
cerr << "Error on primary index \n";
}
}
/*
Now deal with the secondary indexes.
*/
EXEC SQL DECLARE ind CURSOR FOR
SELECT DISTINCT index_name
FROM iiindexes
WHERE base_name = :sql_tname AND
index_owner LIKE :user
ORDER by index_name ;
EXEC SQL OPEN ind;
if (sqlca.sqlcode ) {
cerr << "Ingres cursor open (index) error " << sqlca.sqlcode << " in Table::Table()()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
for (stop=0; !stop; ) {
EXEC SQL FETCH ind
INTO :sql_iname;
switch (sqlca.sqlcode) {
case 0:
Index *ind =
index_list[IndexCount++] =
new Index(this, sql_iname);
if (!ind->OK()) {
cerr << form("Error on index %s\n", sql_iname);
}
break;
default:
cout << form("rollback%s\n", GoString);
cerr << "Ingres fetch (index) error " << sqlca.sqlcode << " in Table::Table()\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
case NOT_FOUND:
stop = 1;
}
}
EXEC SQL CLOSE ind;
}
Table::~Table()
{
if (cols) delete cols;
for (int i = 0; i < IndexCount; i++) {
delete index_list[i];
index_list[i] = 0;
}
if (index_list)
delete index_list;
}
ostream& operator<<(ostream& s, Table & t) {
s << form("drop table %s%s\n", t.name(), GoString);
s << form("create table %s(\n", t.name());
for (Column *c = t.cols; c; c = c->link()) {
s << *c;
if (c->link())
s << ",\n";
else
s << "\n";
}
s << form(")%s%s\n",
t.NoDuplicates ? " with noduplicates" : "",
GoString);
if (!OracleOut && t.IndexCount) {
t.index_list[0]->modify(s);
}
for (int i=(OracleOut?0:1); i<t.IndexCount; i++) {
s << t.index_list[i];
}
s.flush();
return s;
}
void Table::extract(ostream &s)
{ int i;
EXEC SQL BEGIN DECLARE SECTION;
char selectbuf[5000]; /* Ingres SELECT statement */
EXEC SQL END DECLARE SECTION;
char insertbuf[5000]; /* Ingres INSERT statement */
bzero(selectbuf, sizeof(selectbuf));
bzero(insertbuf, sizeof(insertbuf));
ostream b(sizeof(selectbuf), selectbuf);
ostream ins(sizeof(insertbuf), insertbuf);
/*
In selectbuf[] build a SELECT statement to use as a cursor to
fetch the Ingres data rows from the table. Simultaneously, in
insertbuf[] build the stem of an INSERT statement to insert
the data into the output table. The actual data values will be
added to the stem as each data row is fetched.
*/
int field_count = 0;
b << "SELECT ";
ins << form("insert into %s (\n", name());
for (Column *c = cols; c; c= c->link()) {
switch (c->type()) {
case NUM:
b << form("VARCHAR(%s)%s ",
c->name(),
c->link() ? "," : "");
break;
case MONEY:
b << form("VARCHAR(FLOAT8(%s))%s ",
c->name(),
c->link() ? "," : "");
break;
case DATE:
default:
b << form("VARCHAR(%s)%s", c->name(), c->link() ? ", " : " ");
break;
}
ins << form("\t%s%s\n", c->name(), c->link() ? "," : "");
field_count++;
}
ins << ") values (\n";
b << form("FROM %s", name());
b.close();
ins.close();
/*
We have now built the text of the various SQL statements that
we need. The next step is to tell Ingres about it and the
target variables that we are going to use.
*/
EXEC SQL PREPARE s1 FROM :selectbuf;
if (sqlca.sqlcode ) {
cerr << "Ingres PREPARE error " << sqlca.sqlcode << " for S1 in Table::extract(ostream &)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
cerr << form("Text is: '%s'\n", selectbuf);
s << form("rollback%s\n", GoString);
exit(1);
}
EXEC SQL DECLARE cc CURSOR FOR s1;
/*
Allocate a sqlda for 'field_count' columns with 30-char names.
This will be used to hold pointers to our work variables into
which Ingres will store the data rows fetched from the table.
*/
fetchda = new IISQLDA;
if (!fetchda) {
cerr << form("Ingres error allocating sqlda\n");
s << form("rollback%s\n", GoString);
exit(1);
}
fetchda->sqln = field_count;
fetchda->sqld = field_count;
/*
sql_data[][], declared above, can hold 100 data items each of
up to 240 bytes. sql_ind[] are the corresponding 100 indicators.
*/
i = 0;
for (c = cols; c; c= c->link()) {
fetchda->sqlvar[i].sqltype = IISQ_VCH_TYPE;
fetchda->sqlvar[i].sqldata = (char *)&sql_data[i];
fetchda->sqlvar[i].sqlind = &sql_ind[i];
fetchda->sqlvar[i].sqllen = 240;
i++;
}
EXEC SQL OPEN cc;
if (sqlca.sqlcode ) {
cerr << "Ingres cursor open error " << sqlca.sqlcode << " in Table::extract(ostream &)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
EXEC SQL DESCRIBE s1 INTO fetchda;
if (sqlca.sqlcode ) {
cerr << "Ingres describe-select error " << sqlca.sqlcode << " in Table::extract(ostream &)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
i = 0;
for (c = cols; c; c= c->link()) {
switch (c->type()) {
case DATE:
fetchda->sqlvar[i].sqllen = IISQ_DTE_LEN+2;
break;
case CHAR:
fetchda->sqlvar[i].sqllen = c->width()+2;
break;
case MONEY:
case FLOAT:
case NUM:
break;
default:
assert(0);
}
i++;
}
if (fetchda->sqln < 0) {
cerr << form("Ingres DESCRIBE error - %d fields allocated; %d fields found\n",
field_count, fetchda->sqln);
exit(1);
}
for (int stop = 0; !stop;) {
EXEC SQL FETCH cc
USING DESCRIPTOR fetchda;
switch (sqlca.sqlcode) {
case 0:
s << insertbuf;
int i = 0;
for (c=cols; c; c = c->link()) {
if (*fetchda->sqlvar[i].sqlind < 0) { // If NULL value
if (NotNull) { // Force NOT NULL
switch(c->type()) {
case FLOAT:
case MONEY:
case NUM:
s << form("\t0%s\n",
c->link() ? "," : "");
break;
case CHAR:
case DATE:
default:
s << form("\t''%s\n",
c->link() ? "," : "");
break;
}
} else { // Honour NULLs
s << form("\tNULL%s\n",
c->link() ? "," : "");
}
} else if (*fetchda->sqlvar[i].sqlind > 0) {
cerr << form("Table %s, field %s truncated from %d ch\n",
name(), c->name(), *fetchda->sqlvar[i].sqlind);
}
if (*fetchda->sqlvar[i].sqlind >= 0) {
switch(c->type()) {
case MONEY:
case FLOAT:
case NUM:
if (!((VC *)fetchda->sqlvar[i].sqldata)->len)
s << form("\t0%s\n",
c->link() ? "," : "");
else
s << form("\t%.*s%s\n",
sql_data[i].len,
sql_data[i].arr,
c->link() ? "," : "");
break;
case CHAR:
dequote(sql_data[i].arr);
case DATE:
default:
s << form("\t'%.*s'%s\n",
sql_data[i].len,
sql_data[i].arr,
c->link() ? "," : "");
break;
}
}
i++;
}
s << form(")%s\n", GoString);
break;
default:
cerr << "Ingres fetch error " << sqlca.sqlcode << " in Table::extract(ostream&)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
cerr << form("Table name %s\n", name());
s << form("rollback%s", GoString);
case NOT_FOUND:
stop = 1;
}
s.flush();
} // End of FETCH loop
EXEC SQL CLOSE cc;
delete fetchda;
}
void Table::grant(ostream &s)
{ for (Column *c = cols; c; c = c->link()) {
if (!c->IndexColumn)
s << form("grant update(%s) on %s to public%s\n",
c->name(), name(), GoString);
}
s << form("grant select on %s to public%s\n",
name(), GoString);
s << form("grant delete on %s to public%s\n",
name(), GoString);
s << form("grant insert on %s to public%s\n",
name(), GoString);
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/TABLE.SCC" fi
if `test ! -s ./db_unload/INGRES/COLUMN.SCC` then
echo "writing ./db_unload/INGRES/COLUMN.SCC" cat > ./db_unload/INGRES/COLUMN.SCC << '\Rogue\Monster\' /* COLUMN.SCC Process a column definition, while converting a database table from Ingres to SQL.
This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
*/
#include <data_dict.hpp>
#include <assert.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
static char sql_tname[NAME_LENGTH];
static char
sql_cname[NAME_LENGTH], sql_coltype[NAME_LENGTH], sql_nulls[9], sql_defaultval[9]; static long sql_int_datatype, sql_width, sql_scale, sql_colno; static char user[NAME_LENGTH];
EXEC SQL END DECLARE SECTION; Column::Column(long num, Table *up, Column *succ) {
next = succ;
IndexColumn = FALSE;
parent = up;
nulls = FALSE;
WithDefault = FALSE;
sql_colno = num;
strcpy(sql_tname, up->name());
strcpy(user, up->owner());
EXEC SQL SELECT
column_name,
column_datatype,
column_length,
column_scale,
column_nulls,
column_defaults,
column_ingdatatype
INTO :sql_cname,
:sql_coltype,
:sql_width,
:sql_scale,
:sql_nulls,
:sql_defaultval,
:sql_int_datatype
FROM iicolumns
WHERE table_name = :sql_tname AND
table_owner LIKE :user AND
column_sequence = :sql_colno;
if (sqlca.sqlcode) {
cerr << "Ingres select error " << sqlca.sqlcode << " in Column::Column(long, Table*, Column*)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
char *s = strchr(sql_cname, ' ');
if (s) *s = 0;
strcpy(cname, sql_cname);
if (sql_int_datatype < 0 && !NotNull)
nulls = TRUE;
else
nulls = FALSE;
switch(sql_int_datatype) {
case 30: case -30:
coltype = NUM;
break;
case 31: case -31:
coltype = FLOAT;
break;
case 37: case -37:
case 20: case -20:
case 21: case -21:
coltype = CHAR;
break;
case 3: case -3:
coltype = DATE;
break;
case 5: case -5:
coltype = MONEY;
break;
}
colwidth = sql_width;
}
Column::Column(long num, View *up, Column *succ) {
next = succ;
IndexColumn = FALSE;
parent = (Table*)up;
sql_colno = num;
strcpy(sql_tname, up->name());
EXEC SQL SELECT
column_name
INTO :sql_cname
FROM iicolumns
WHERE table_name = :sql_tname AND
table_owner LIKE :user AND
column_sequence = :sql_colno;
if (sqlca.sqlcode) {
cerr << "Ingres select error " << sqlca.sqlcode << " in Column::Column(long, View*, Column*)\n";
cerr << sqlca.sqlerrm.sqlerrmc << "\n";
exit(1);
}
char *s = strchr(sql_cname, ' ');
if (s) *s = 0;
strcpy(cname, sql_cname);
}
ostream& operator<<(ostream& s, Column& c) {
s << form("\t%-.24s\t", c.cname);
switch (c.coltype) {
case CHAR:
s << form("%s(%d)",
OracleOut?"VARCHAR":"VCHAR",
c.colwidth);
break;
case NUM:
s << form("INTEGER");
break;
case DATE:
s << form("DATE");
break;
case FLOAT:
s << form("FLOAT");
break;
case MONEY:
s << form("MONEY");
break;
default:
assert(0);
}
if (!c.nulls) {
s << " NOT NULL";
if (Default || c.WithDefault)
if (!c.IndexColumn)
s << " WITH DEFAULT";
}
return s;
}
// Locate a column by name
Column *Table::column(const char cname[])
{
for (Column *c = cols; c; c= c->link()) if (!stricmp(cname, c->name())) return c; return 0;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/COLUMN.SCC" fi
if `test ! -s ./db_unload/INGRES/DEQUOTE.C` then
echo "writing ./db_unload/INGRES/DEQUOTE.C" cat > ./db_unload/INGRES/DEQUOTE.C << '\Rogue\Monster\' /* DEQUOTE.C This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
*/
#ifdef SYSV #include <string.h> #define index strchr #else #include <strings.h> #endif /* If s.arr contains embedded apostrophes, we have to replace each one by a doubled apostrophe in order to keep Ingres happy. This we do by counting the total number of apostophes and shuffling data to the right by the number of apostrophes to the left of each byte.
*/
dequote(s)
struct varchar {
short len;
char arr[1];
} *s;
{ int i, j, len, count, count2;
char *p;
if (!(p=index(s->arr, '\'')))
return ;
count = 1;
while (p= index(p+1, '\''))
count++;
count2 = count;
for (i= s->len-1; count > 0; i--) {
if (s->arr[i] == '\'') {
s->arr[i+count] = s->arr[i];
count--;
}
s->arr[i+count] = s->arr[i];
}
s->len += count2;
s->arr[s->len] = 0;
return ;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/DEQUOTE.C" fi
if `test ! -s ./db_unload/INGRES/README` then
echo "writing ./db_unload/INGRES/README" cat > ./db_unload/INGRES/README << '\Rogue\Monster\' This program unloads an Ingres database as an SQL stream. I wrote it for converting Ingres DBs to Oracle. There is also an equivalent, and very similar, program for converting Oracle to Ingres.
Not all data types are catered for: they did not occur in my DB. No attempt is made to deal with constraints, user groups or other esoterica.
The supplied ``man'' page is actually from the Oracle to Ingres version. It is _almost_ correct, but take it with a pinch of salt.
Since I am embedding SQL in C++, we have to be a little careful in how we write the code. In particular: 1. esql/C does not recognise // comments. 2. Function arguments and members of classes cannot be used as SQL variables
(they can't be in a DECLARE SECTION). 3. All sorts of functions have to be declared by hand as extern "C" to satisfy
C++'s calling convention.
4. We have to massage the output of esql/c with sed to deal with the conflict
between variable names and structure tags.
I offer no warranties as to the correctness of this code. You are welcome to use it, but at your own risk.
Raphael Mankin (raph_at_panache.demon.co.uk).
7 Jan 1993
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/README"
fi
if `test ! -s ./db_unload/INGRES/DERESERV.C`
then
echo "writing ./db_unload/INGRES/DERESERV.C"
cat > ./db_unload/INGRES/DERESERV.C << '\Rogue\Monster\'
/* DERESERVE.C
This program was written by Raphael Mankin. (raph_at_panache.demon.co.uk)
CHANGES:
7/4/92 Change returned type from void to char*
*/
#include <stdio.h>
#ifndef __GNUG__ /* For AT&T compiler */
#define stricmp strcasecmp
#include <strings.h>
#else
#include <string.h>
#endif
static char *reserved[] = {
"command",
"count",
"default",
"file",
"index",
0
};
const char *dereserve(const char name[])
{ int i;
static char buf[100];
for (i=0; reserved[i]; i++) {
if (!stricmp(name, reserved[i])) {
strcpy(buf, name);
strcat(buf, "_x");
return buf;
}
}
return name;
}
\Rogue\Monster\
else
echo "will not over write ./db_unload/INGRES/DERESERV.C" fi
if `test ! -s ./db_unload/README`
then
echo "writing ./db_unload/README"
cat > ./db_unload/README << '\Rogue\Monster\' These programs unload an Ingres or Oracle database as an SQL stream. I wrote them for converting databases back and forth when porting an application from Oracle to Ingres and the data also had to be moved.
Not all data types are catered for: they did not occur in my DB. No attempt is made to deal with constraints, user groups or other esoterica.
The supplied ``man'' page is actually from the Oracle to Ingres version. It is _almost_ correct, but take it with a pinch of salt.
Since I am embedding SQL in C++, we have to be a little careful in how we write the code. In particular: 1. esql/C does not recognise // comments. 2. Function arguments and members of classes cannot be used as SQL variables
(they can't be in a DECLARE SECTION). 3. All sorts of functions have to be declared by hand as extern "C" to satisfy
C++'s calling convention.
4. We have to massage the output of esql/c with sed to deal with the conflict
between variable names and structure tags.
I offer no warranties as to the correctness of this code. You are welcome to use it, but at your own risk.
Raphael Mankin (raph_at_panache.demon.co.uk).
7 Jan 1993
\Rogue\Monster\
else
echo "will not over write ./db_unload/README"
fi
echo "Finished archive 1 of 1"
exit
--------------Cut here----------------- ______________________________________________________________ Johan Montald / Customer Representative /// Computer Associates International Belgium /// / Woluwelaan 34 B13 /// / / 1200 Brussels ///// / / Belgium ///// / / / email: johan_at_ingres.com /////// / / / phone: +32-2/773.28.11 ext 865 ///////// / / / fax : +32-2/762.73.59 ///////////// / / ____________________________________________________///////////////////.
God is real, unless declared integer Received on Tue Sep 20 1994 - 09:42:47 CEST