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
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
<link rel="shortcut icon" href="favicon.ico" type="image/x-icon">
<link rel="stylesheet" type="text/css" href="style.css">
<title>PgDBF</title>
</head>
<body>
<div id="navbar">
<img src="pgdbflogo.png" alt="PgDBF" id="pgdbflogo">
<ul id="navlist">
<li><a href="http://sourceforge.net/project/showfiles.php?group_id=257285">Download</a></li>
<li><a href="http://sourceforge.net/tracker/?group_id=257285&atid=1127043">Bug tracker</a></li>
<li><a href="http://sourceforge.net/forum/?group_id=257285">Forums</a></li>
<li><a href="https://github.com/kstrauser/pgdbf">GitHub</a></li>
<li><a href="http://sourceforge.net/projects/pgdbf/">SourceForge page</a></li>
<li><a href="https://groups.google.com/d/forum/pgdbf">Mailing list home page</a></li>
</ul>
</div>
<div id="content">
<h1>News! PgDBF is moving!</h1>
<p>PgDBF is moving fully to its GitHub home at <a href="https://github.com/kstrauser/pgdbf">https://github.com/kstrauser/pgdbf</a>. This page will soon be replaced with a redirect to the GitHub page.</p>
<p><span class="standout">PgDBF</span> 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>
<h2>Features</h2>
<p>PgDBF was designed with a few core principles:</p>
<ul>
<li>Simplicity. This code should be understandable by anyone who wants to hack it.</li>
<li>Robustness. Every syscall is checked for success.</li>
<li>Speed. PgDBF was born to be the fastest conversion available anywhere.</li>
<li>Completeness. It has full support for FoxPro memo files.</li>
<li>Portability. PgDBF runs on 32- and 64-bit systems, and both little-endian (eg x86) and big-endian (eg PowerPC) architectures.
</ul>
<h2>Performance</h2>
<p>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>
<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>
<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>
<h2>Downloading</h2>
<p>Downloadable tarballs of numbered releases are available at <a href="http://sourceforge.net/project/showfiles.php?group_id=257285">the SourceForge download page</a>. Development happens on GitHub at <a href="https://github.com/kstrauser/pgdbf">https://github.com/kstrauser/pgdbf</a>.</p>
<h2>Release Notes</h2>
<p>Version 0.6.2 (September 30, 2012) adds built-in iconv translation from the given encoding to UTF-8. Thanks to Philipp Wollermann, PgDBF's new co-author/co-maintainer, for the heavy lifting! This also fixes </p>
<p>Version 0.6.1 (March 1, 2012) is exactly identical in operation to 0.6.0, but I'm an awful release engineer and always forget to update the version number in the man page, etc. This bumps the revision to indicate that files have changed.</p>
<p>Version 0.6.0 (February 29, 2012) breaks compatibility with older versions by creating NUMERIC fields as type NUMERIC instead of TEXT by default. Use the "-N" flag to get the old behavior. Use the new(ish) PRId64 definition from inttypes.h to print 64-bit CURRENCY fields. Adds an optional progress bar. Adds 64-bit file offset support for Linux (and possibly Solaris, although it's not tested). Increased the default buffer and batch sizes from 1MB and 128KB to 4MB and 16MB respectively. Cleaned up lots of endian-handling code.</p>
<p>Version 0.5.5 (February 3, 2011) adds the "-q" option to place quotation marks around the name of the table when using it in statements like "CREATE TABLE", "DROP TABLE", etc.</p>
<p>Version 0.5.4 (November 16, 2010) does away with the single memofile size check at the beginning of the run and checks for valid memo record offsets every time one is requested.</p>
<p>Version 0.5.3 (November 15, 2010) fixes an unsigned int comparison bug.</p>
<p>Version 0.5.2 (November 3, 2010) adds tests for the validity of specified memofiles. Older versions would possibly segfault if an invalid memofile was given.</p>
<p>Version 0.5.1 (March 9, 2010) enables the "IF EXISTS" (the "-e" option) to "DROP TABLE" by default. If you are using PgDBF with a version of PostgreSQL older than 8.2 (which was released in December 2006), you will need to use the "-E" flag to disable it. Also adds "-u" and "-U" flags to control whether a "TRUNCATE TABLE" statement is issued to clear the table before copying data.</p>
<p>Version 0.5.0 (November 24, 2009) is not a drop-in replacement for older versions. Specifically:</p>
<ol>
<li>It no longer tries to guess the names of memofiles. If a table has a memofile, it must be specified with the "-m" command line argument.</li>
<li>The "N" (numeric) and "F" (float) datatypes now emit \N (NULL) instead of empty strings.</li>
</ol>
<h2>Building and Installation</h2>
<p><tt>$ ./configure; make; make install</tt></p>
<h2>Usage</h2>
<p>Use PgDBF to convert your XBase tables into a format suitable for piping into the psql client. PgDBF will generate the commands necessary to create near-exact replicas of your tables. Its output looks something like: </p>
<pre>
BEGIN;
SET statement_timeout=60000; DROP TABLE IF EXISTS invoice; SET statement_timeout=0;
CREATE TABLE invoice (invoiceid INTEGER, note VARCHAR(50), billdate DATE, submitted TIMESTAMP, memofield TEXT, approved BOOLEAN);
\COPY invoice FROM STDIN
...
...
...
\.
COMMIT;
CREATE INDEX invoice_invoiceid ON invoice(invoiceid);
</pre>
<p>Note that the entire process is usually wrapped inside a transaction so that other clients will have access to the old data until the transaction is completed. </p>
<h3>Command Line</h3>
<p><tt>Usage: pgdbf [-cCdDeEhtTuU] [-m memofilename] filename [indexcolumn ...]</tt></p>
<p>The only required argument is the filename of the table to be converted. If the table has a memo field, then use the "-m" option to specify the path to the memo file.</p>
<p>The "-h" flag prints this usage information and then exits.</p>
<p>The "-c" flag cause PgDBF to print a "CREATE TABLE" statement to make a table with similar datatypes and column names as the DBF file. This is the default behavior.</p>
<p>The "-C" suppresses the "CREATE TABLE" statement.</p>
<p>The "-d" flag causes PgDBF to print a "DROP TABLE" statement before the "CREATE TABLE" command. This is useful for replacing the contents of a table that already exists in PostgreSQL. This is the default behavior.</p>
<p>The "-D" flag causes PgDBF <em>not</em> to print the "DROP TABLE" statement. If "-C" was used to disable the "CREATE TABLE" statement, then "-D" will be silently ignored (as it makes no sense to drop a table and then try to insert into it).</p>
<p>The "-e" flag changes the "DROP TABLE" statement to "DROP TABLE IF EXISTS". PostgreSQL will return an error when attempting to drop a table that does not exist. Version 8.2 and newer can use the "IF EXISTS" modifier to drop the table only if it's already defined, and otherwise continue without error. This is the default.</p>
<p>The "-E" flag disables the "IF EXISTS" modifier to "DROP TABLE" for compatibility with old versions of PostgreSQL.</p>
<p>Use the "-m" argument to specify the memofile (if any) associated with the table.</p>
<p>The "‐n" flag creates NUMERIC fields with type NUMERIC. <b>This is a new default and different from old versions of PgDBF!</b></p>
<p>The "‐N" flag creates NUMERIC fields with type TEXT. This is the historical default setting. Use this if rows contain invalid number data in NUMERIC fields, which are essentially CHARACTER fields behind the scenes, and you receive errors when importing the data into PostgreSQL.</p>
<p>The "‐p" argument shows a progress bar during the conversion process.</p>
<p>The default "-P" argument hides the progress bar.</p>
<p>The "‐q" flag encloses 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.</p>
<p>The "‐Q" flag doesn't enclose the name of the table in quotation marks. This is the default.</p>
<p>The "-s" flag uses iconv to convert from the named encoding to UTF-8. This is useful for importing databases originally encoded in non-ASCII charsets without losing the non-ASCII data.</p>
<p>The "-t" flag wraps the entire script in a transaction. Since transaction commits are atomic, there will never be an instant in time where the table appears empty to other clients. Instead, the old table data will seem to be instantaneously replaced with the new contents. This is the default.</p>
<p>The "-T" flag removes the wrapper transaction. This is generally not a good idea as it causes the table to be completely empty at times.</p>
<p>The "-u" flag generates a "TRUNCATE TABLE" statement to quickly empty a table without dropping and re-creating it.</p>
<p>"-U" disables the "TRUNCATE TABLE" statement. This is the default.
<p>Indices are automatically created if you specify the columns (or expressions!) you want indexed on the command line. For example, </p>
<pre>
pgdbf foo.dbf rowid "substr(textfield,1,4)" price
</pre>
<p>will create three indices on the new foo table: one each for the rowid and price columns, and one for the substr() expression. It tries to give each index a reasonable name.</p>
<h2>Bugs</h2>
<p>When multiple incompatible interpretations of a type are available, such as the 'B' type which can mean 'binary object' in dBASE V or 'double-precision float' in FoxPro, I've used the FoxPro version.</p>
<p>Not all XBase datatypes are supported right now. As of this writing, PgDBF can handle boolean, currency, date, double-precision float, float, general (although only outputs empty strings; it's unclear how to resolve OLE objects at this time), integer, memo, numeric, timestamp, and varchar fields. If you need other datatypes, send a small sample database for testing.</p>
<h2>Contributors</h2>
<p>PgDBF was originally written by Kirk Strauser <<a href="mailto:kirk@strauser.com">kirk@strauser.com</a>> and approved for release under the GPLv3 by the owner of his company, Brandon Day. Philipp Wollermann joined the team leading up to the release of version 0.6.2.</p>
<p>Special thanks to the following for suggestions and bug reports:</p>
<ul>
<li>John Easton</li>
<li>Alan Polinsky</li>
<li>Fabrízio de Royes Mello</li>
<li>Gordon Sweet</li>
</ul>
<hr>
<p>
<br>
<a href="http://sourceforge.net/projects/pgdbf"><img src="http://sflogo.sourceforge.net/sflogo.php?group_id=257285&type=11" width="120" height="30" alt="Get PgDBF at SourceForge.net. Fast, secure and Free Open Source software downloads"></a>
<a href="http://validator.w3.org/check?uri=referer"><img src="http://www.w3.org/Icons/valid-html401-blue" alt="Valid HTML 4.01 Strict" height="31" width="88"></a>
<a href="http://jigsaw.w3.org/css-validator/check/referer"><img style="border:0;width:88px;height:31px" src="http://jigsaw.w3.org/css-validator/images/vcss-blue" alt="Valid CSS!"></a>
</p>
</div>
<!-- Piwik -->
<script type="text/javascript">
var pkBaseURL = (("https:" == document.location.protocol) ? "https://apps.sourceforge.net/piwik/pgdbf/" : "http://apps.sourceforge.net/piwik/pgdbf/");
document.write(unescape("%3Cscript src='" + pkBaseURL + "piwik.js' type='text/javascript'%3E%3C/script%3E"));
</script><script type="text/javascript">
piwik_action_name = '';
piwik_idsite = 1;
piwik_url = pkBaseURL + "piwik.php";
piwik_log(piwik_action_name, piwik_idsite, piwik_url);
</script>
<object><noscript><p><img src="http://apps.sourceforge.net/piwik/pgdbf/piwik.php?idsite=1" alt="piwik"></p></noscript></object>
<!-- End Piwik Tag -->
<!-- GA tag -->
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-29576688-1']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
<!-- End GA tag -->
</body>
</html>
|
