1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
|
Content-type: text/html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML><HEAD><TITLE>Man page of PGDBF</TITLE>
</HEAD><BODY>
<H1>PGDBF</H1>
Section: User Commands (1)<BR>Updated: February 3 2011<BR><A HREF="#index">Index</A>
<A HREF="/cgi-bin/man/man2html">Return to Main Contents</A><HR>
<A NAME="lbAB"> </A>
<H2>NAME</H2>
pgdbf - convert XBase / FoxPro tables to PostgreSQL
<P>
<A NAME="lbAC"> </A>
<H2>SYNOPSIS</H2>
<B>pgdbf</B>
[-cCdDeEhqQtTuU] [-m memofile] filename [indexcolumn ...]
<P>
<A NAME="lbAD"> </A>
<H2>DESCRIPTION</H2>
PgDBF is a program for converting XBase databases - particularly FoxPro
tables with memo files - into a format that PostgreSQL can directly import.
It's a compact C project with no dependencies other than standard Unix
libraries. While the project is relatively tiny and simple, it's also
heavily optimized via profiling - routine benchmark were many times faster
than with other Open Source programs. In fact, even on slower systems,
conversions are typically limited by hard drive speed.
<P>
<A NAME="lbAE"> </A>
<H3>Features</H3>
PgDBF was designed with a few core principles:
<P>
<DL COMPACT><DT><DD>
<B>Simplicity.</B>
This code should be understandable by anyone who wants to hack it.
<P>
<B>Robustness.</B>
Every syscall that might possibly fail is checked for success.
<P>
<B>Speed.</B>
PgDBF was born to be the fastest conversion available anywhere.
<P>
<B>Completeness.</B>
It has full support for FoxPro memo files.
<P>
<B>Portability.</B>
PgDBF runs on 32- and 64-bit systems, and both little-endian (eg x86) and
big-endian (eg PowerPC) architectures.
</DL>
<P>
<A NAME="lbAF"> </A>
<H3>Performance</H3>
PgDBF's speed is generally limited by how fast it can read your hard drives.
A striped RAID of quick disks can keep PgDBF pretty well fed on a
single-processor system. One problem area is with memo files, which may
become very internally fragmented as memo fields are created, deleted, and
updated. For best results, consider placing the DBF and FPT files on a RAM
drive so that there's no seek penalty as there is with spinning hard drives,
or using a filesystem such as ZFS that caches aggressively.
<P>
One particularly fragmented 160MB table with memo fields used to take over
three minutes on a FreeBSD UFS2 filesystem. Moving the files to a RAM disk
dropped the conversion time to around 1.2 seconds.
<P>
A certain test table used during development comprises a 280MB DBF file and
a 660MB memo file. PgDBF converts this to a 1.3 million row PostgreSQL table
in about 11 seconds, or at a rate of almost 120,000 rows per second.
<P>
<A NAME="lbAG"> </A>
<H2>OPTIONS</H2>
<DL COMPACT>
<DT><B>-c</B>
<DD>
Generate a
<B>CREATE TABLE</B>
statement to make a table with similar datatypes and column names as the DBF
file. Default.
<DT><B>-C</B>
<DD>
Suppress the
<B>CREATE TABLE</B>
statement.
<DT><B>-d</B>
<DD>
Generate a
<B>DROP TABLE</B>
statement before the
<B>CREATE TABLE</B>
statement. This is useful for replacing the contents of a table that already
exists in PostgreSQL. Default.
<DT><B>-D</B>
<DD>
Suppress the
<B>DROP TABLE</B>
statement.
<DT><B>-e</B>
<DD>
Change the
<B>DROP TABLE</B>
statement to
<B>DROP TABLE IF EXISTS</B>
so that newer versions of PostgreSQL (8.2+) will only attempt to drop the
table if it's already defined. PostgreSQL will return an error when
attempting to drop a table that does not exist unless
<B>IF EXISTS</B>
is used. Default.
<DT><B>-E</B>
<DD>
Do not use the
<B>IF EXISTS</B>
modifier to
<B>DROP TABLE</B>
for compatibility with versions of PostgreSQL older than 8.2.
<DT><B>-h</B>
<DD>
Print a help message, then exit.
<DT><B>-m memofile</B>
<DD>
The name of the associated memo file (if necessary).
<DT><B>-q</B>
<DD>
Enclose the name of the table in quotation marks in statements like "CREATE
TABLE", "DROP TABLE", and so on. This is useful in cases where the table
name is a PostgreSQL reserved word, and almost certainly harmless in all
other cases.
<DT><B>-Q</B>
<DD>
Do not enclose the name of the table in quotation marks. Default.
<DT><B>-t</B>
<DD>
Wrap the entire script in a transaction. Default.
<DT><B>-T</B>
<DD>
Remove the wrapper transaction. This is generally not a good idea as it can
cause the table to appear completely empty to other clients during the data
copying phase. If the entire process occurs inside a transaction, the
update is atomic and other clients will have full access to all data in the
table at all times.
<DT><B>-u</B>
<DD>
Issue a
<B>TRUNCATE TABLE</B>
statement to clear the contents of a table before copying data into it.
<DT><B>-U</B>
<DD>
Suppress the
<B>TRUNCATE TABLE</B>
statement. Default.
<P>
</DL>
<A NAME="lbAH"> </A>
<H2>OPTION NOTES</H2>
The
<B>-c</B>
and
<B>-d</B>
arguments are incompatible with
<B>-u</B>
as it's pointless to truncate a newly-created table. Specifying
<B>-c</B>
or
<B>-d</B>
will disable the
<B>TRUNCATE TABLE</B>
statement as though
<B>-U</B>
was given. Similarly, using the
<B>-u</B>
argument will disable the
<B>CREATE TABLE</B>
and
<B>DROP TABLE</B>
statements as if
<B>-C</B>
and
<B>-D</B>
were given.
<P>
<A NAME="lbAI"> </A>
<H2>BUGS</H2>
When multiple incompatible interpretations of a type are available, such as
the
<B>B</B>
type which can mean
<I>binary object</I>
in dBASE V or
<I>double-precision float</I>
in FoxPro, PgDBF currently uses the FoxPro interpretation.
<P>
Most XBase datatypes are supported, but some are not (yet). As of this
writing, PgDBF can handle <I>boolean</I>, <I>currency</I>, <I>date</I>,
<I>double-precision float</I>, <I>float</I>, <I>general</I> (although only
outputs empty strings; it's unclear how to resolve OLE objects at this
time), <I>integer</I>, <I>memo</I>, <I>numeric</I>, <I>timestamp</I>, and
<I>varchar</I> fields. If you need other datatypes, send a small sample
database for testing.
<P>
<A NAME="lbAJ"> </A>
<H2>AUTHOR</H2>
Kirk Strauser <<A HREF="mailto:kirk@strauser.com">kirk@strauser.com</A>>
<P>
<HR>
<A NAME="index"> </A><H2>Index</H2>
<DL>
<DT><A HREF="#lbAB">NAME</A><DD>
<DT><A HREF="#lbAC">SYNOPSIS</A><DD>
<DT><A HREF="#lbAD">DESCRIPTION</A><DD>
<DL>
<DT><A HREF="#lbAE">Features</A><DD>
<DT><A HREF="#lbAF">Performance</A><DD>
</DL>
<DT><A HREF="#lbAG">OPTIONS</A><DD>
<DT><A HREF="#lbAH">OPTION NOTES</A><DD>
<DT><A HREF="#lbAI">BUGS</A><DD>
<DT><A HREF="#lbAJ">AUTHOR</A><DD>
</DL>
<HR>
This document was created by
<A HREF="/cgi-bin/man/man2html">man2html</A>,
using the manual pages.<BR>
Time: 15:33:19 GMT, February 03, 2011
</BODY>
</HTML>
|
