PXVIEW
Synopsis
pxview [-h | --help ] [--verbose ] [-i | --info ] [-c | --csv ] [-s | --sql ] [-x | --html ] [-t | --schema ] [-v | --verbose ] [--mode=MODE ] [-o FILE | --output-file=FILE ] [-b FILE | --blobfile=FILE ] [-p PREFIX | --blobprefix=PREFIX ] [--blobextension=EXT ] [-n FILE | --primary-index-file=FILE ] [-r ENCODING | --recode=ENCODING ] [--separator=CHAR ] [--enclosure=CHAR ] [--fields=REGEX ] [--tablename=NAME ] [--delete-table ] [--skip-schema ] [--use-copy ] [--short-insert ] [--set-sql-type=SPEC ] [--timestamp-format=FORMAT ] [--time-format=FORMAT ] [--date-format=FORMAT ] [--empty-string-is-null ] [--output-deleted ] [--mark-deleted ] [FILE ]
DESCRIPTION
This manual page documents the pxview command.
pxview reads Paradox files and prints information about the file or dumps the records to stdout or a file. pxview is mostly applied on Paradox .DB files but can also be used to show information about other Paradox files. It can also read associated blob files (.MB) and write each blob into a separate file.
pxview can be used to convert Paradox databases into spreadsheet readable CSV files (comma separated values) or SQL insert statements to import the records into a relational database. The sql output is optimized to be used with PostgreSQL.
The programm can read encrypted an unencrypted .db and .mb files. There is not need to provide a password in order to read encrypted files.
OPTIONS
The program follows the usual GNU command line syntax, with long options starting with two dashes (`-'). A summary of options is included below.
-h--helpShow summary of options.
--versionShow version of program.
-o--output-fileAll output except for usage information and error messages will be written to the given file instead of stdout.
-i--infoShow information about Paradox file. If no other output format is specified this will be the default.
-t--schemaDump schema of database as it would be created by the official Paradox software, when a database is exported in CSV format. This option is the short form for --mode=schema.
-v--verboseOutput some more information from the header. This option is only needed if you want see some internal information which are usually not of any interessed.
-c--csvDump data records in CSV format. The first line of the output contains the names of each field, its type and size. This option is the short form for --mode=csv.
-s--sqlDump data records in SQL format. This option is the short form for --mode=sql.
-x--htmlDump data records in HTML table format. This option is the short form for --mode=html.
-q--sqliteDump data records into a sqlite database. This option is the short form for --mode=sqlite. sqlite output always requires to specify an output file with the option --output-file. If the sqlite database file already exists, then pxview will create a new table within that file. If a table with the same name already exists, it will not be overwritten unless you pass the option --delete-table.
--mode=MODESets the ouput mode. This options provides a different way to set the output format. --mode=sql is equivalent to --sql, --mode=csv to --csv, --mode=html to --html, --mode=sqlite to --sqlite and --mode=schema to --schema.
-b FILE--blobfile=FILEIf the database files references blobs (binary large objects) then read them from the given file and write each blob of type Blob, OLE, and Graphic into a single file. The filename will be constructed from the blobprefix option (or the tablename if no prefix is given), an underscore, the internal number of the blob and the value from the blobextension option (`blob' if no extension is given) separated by a dot. Blobs of type Memo and FmtMemo are treated like alpha fields and its content will be inserted into the regular output.
-p PREFIX--blobprefix=PREFIXUse this prefix for each filename of a blob. You can write all blobs into a single directory by specifying a valid directory path name. You will have to make sure the directory exists before running pxview.
--blobextension=EXTUse this extension for each filename of a blob. If no extension is given, then `blob' will be used instead.
-n FILE--primary-index-file=FILERead the primary index from file. This option is only valid it you are reading a database file.
-r ENCODING--recode=ENCODINGRecode all fields of type alpha to the given encoding. You will have to specify the encoding similar to the recode command, by passing only the part on the right hand side of the `..' of what you usually pass to recode.
--separator=CHARIf the database is exported in csv format the given character will be used to separate field values. Other output formats will not be affected by this option. The default value is ','.
--enclosure=CHARIf the database is exported in csv format the given character will be used to enclose field values if the field is of type char(). The enclosure will only take place if the delimiter between fields is part of the field value. Other output formats will not be affected by this option. The default value is '"'.
--fields=REGEXThis option allows to select certain fields by specifying an extended regular expression. It will only effect the csv, html, sql, and sqlite output. "field1|field23$" will select all fields whose name contains "field1" or end in "field23". If this option is not used als fields will be shown. The field name is case insensitive.
--output-deletedOutput even those records that has been deleted. Paradox does not mark a record as deleted, but rather marks a complete data block (which usually contains several records) or sets its size to a lower value. If this option is active, then pxview will assume each data block is filled with its maximum number of records. This assumption may not always be true, especially for the last data block. Therefore you should handle the output with care.
--mark-deletedAdds an additional column to the csv and html output which indicates whether a record is deleted or actually available. This option is only available in csv and html mode and makes only sense if the option
--ouput-deletedis passed as well. If you do not pass--output-deletedyou will get an extra column with just zeros because none of the records is deleted, otherwise it would not be shown.--delete-tableAdds a sql command at the begining of the sql output to delete an existing table before creating the new one. If used in sqlite mode it will drop the table before creating a new one. This option only affects sql and sqlite output.
--skip-schemaDo not add at the beginning of the sql output the commands to create the table schema. This is useful if an existing table shall be populated with new records. This option only affects sql and sqlite output.
--use-copyUse the sql COPY statement in the sql output instead of the default INSERT. Using COPY is usually faster when the data is imported into a database, but less supported by databases. This option only affects sql output.
--short-insertUse a short version of INSERT statements in the sql output. This will skip the list of field names which are being affected by the insert statement, but uses a form like 'insert into tablename values (...)'. Such statements will only work if the table has the same number of fields in the same order as the INSERT statement lists the field values. This option will mostly not be appropriate if you want to extract some fields from a larger table (--fields) and populate an existing sql table whose schema is not identical to the paradox file.
--tablename=NAMEUse given tablename instead of the one stored in the database file. This option only affects schema, sql and sqlite output.
--set-sql-type=SPECOverrides the default mapping from a paradox field type to a sql field type. This option only affects sql and sqlite output and should be used with care. You can output the predefined mapping by passing the option
--help. SPEC is a colon separated pair of field types. The one left to the colon is the paradox type, the other one is the sql type. The paradox type is one of `alpha', `date', `short', `long', `currency', `number', `logical', `memoblob', `blob', `fmtblob', `ole', `graphic', `time', `timestamp', `autoinc', `bcd', or `bytes'. The sql type can be any available type like real, integer or text. It is up to the user to specify a valid type. If the sql type requires a length (e.g char(x)), then it can put into the type specification with the placeholder `%d', e.g. `alpha:char(%d)'. There is currently no check for reasonable type mapping.--empty-string-is-nullOutputs NULL in the sql ouput for all empty strings in the paradox file. This option only affects sql and sqlite output. Paradox does not distinguish between fields of type alpha which has no characters or contain the NULL string. The default is to tread them as empty strings. By passing this option they will be treaded as NULL strings in the sql output.
--timestamp-format=FORMATOverrides the default format for outputing timestamps. The default is set to 'Y-m-d H:i:s'. Check the section on 'Date/Time formats' for a complete list of placeholders in the format string.
--time-format=FORMATOverrides the default format for outputing times. The default is set to 'H:i:s'. Check the section on 'Date/Time formats' for a complete list of placeholders in the format string.
--date-format=FORMATOverrides the default format for outputing dates. The default is set to 'Y-m-d'. Check the section on 'Date/Time formats' for a complete list of placeholders in the format string.
The none optional parameter FILE is the Paradox file which shall be read.
If you pass two or more options to set the output format then each format will be output one after the other starting with csv followed by html and finally sql.
The default field separator if exporting csv format is a tabulator. Fields will be enclosed in " if required.
BLOB FILES
Fields of type blob will only be regarded in the output
if a file is set with the option --blobfile
containing the blobs (usually a .MB file). Paradox distinguishes
five different types of blobs of which two of them are actually pure
text and the remaining three are binary data. The two text blobs
(Memo and FmtMemo) are treaded like alpha fields and its content will
be included in the regular output. The binary blobs are separately
written to a file.
PRIMARY INDEX FILES
pxview supports not just the reading and outputting of DB
files but also
of primary and secondary index files (PX, XGx, YGx files). This section
will only focus on primary index files. Do not confuse this with the
option --primary-index-file which is just
accelerated access on database records in a .DB file, but this section
is about outputting the content of a primary index file.
An index file has the same basename as the database file. If it exists, then the database file will have the header field 'Num. of prim. Key fields' set to a value greater than 0. An index file is actually a regular database file with some extra columns and a slightly different header. There is no further reference between the database and the index file beside the file name. You can always read a database without its primary index, but access will be slower.
Each database file may have one or more primary key fields. The number is stored in the header of the database file. Primary key fields must be in the first n columns of the database file. If for example the number of primary key fields is set to two, then the first two fields in the database file are primary keys. Consequently the .PX file will have these two fields as well. The values of the primay key fields in the index file are those of the first record in the referenced data block (see below).
Each primary index file has beside the key fields three further columns which point to data blocks in the database file. In order to understand these columns you need to know that database files are segmented into datablocks. The size of each data block can be calculated from the 'Max. Table size' value in the database file header. Datablocks are organized in a double linked list. Each data block is prefaced by a six bytes data block header which contains references to the previous and next data block in the list and the size of the block.
The n+1 column (with n being the number of primary key fields) in the primary index file contains the block number in the database file. The first block has number 1. The block number is used to locate the data block in the database file. It can be calculated by `(blocknumber - 1) * datablocksize + headersize'. The n+2 column contains the number of records in the referenced data block.
Beside records whose block numbers point to blocks in the database file, there are also so called index blocks. Its block numbers reffer other index blocks in the primary index itself. The reason for this is the tree structure of the index which is explained in the file /usr/share/doc/pxlib-doc/paradox4.txt. The number of records in this index blocks is the sum of all records referenced by the records in the block this block references. If for example you have 100 records each pointing to a data block in the database with 5 records each, and those 100 records are store in a datablock number 1 in the index file, then the reference from an index record to this block number 1 will have a record count 100*5. The index record itself will be part of an index block.
The last column has some unknown meaning. It is often set to zero.
When you output the content of an primary index file you will notice a forth extra column. This one is added by pxview itself. It is the block number the current record is stored in. It is very helpful for investigating the tree structure of the index file. Quite often one will find index blocks at the begining of an index file.
DATE/TIME FORMATS
Paradox knows three field types for date and time values. Each
of those can be formated by specifying a format string with the
options --timestamp-format,
--time-format, --date-format.
A format string contains arbitrary characters with some of them having a special meaning. The following list describes the characters with a special meaning.
- Y
A full numeric representation of a year, 4 digits (e.g. 1999 or 2004).
- y
A two digit representation of a year (e.g. 99 or 04).
- m
Numeric representation of a month, with leading zeros, 01 to 12.
- n
Numeric representation of a month, without leading zeros, 1 to 12.
- d
Day of the month, 2 digits with leading zeros, 01 to 31.
- j
Day of the month without leading zeros, 1 to 31
- H
24-hour format of an hour with leading zeros, 00 to 23.
- h
12-hour format of an hour with leading zeros, 01 to 12.
- G
24-hour format of an hour without leading zeros, 0 to 23.
- g
12-hour format of an hour without leading zeros, 1 to 12.
- i
Minutes with leading zeros, 00 to 59.
- s
Seconds, with leading zeros, 00 to 59.
- A
Uppercase Ante meridiem and Post meridiem, AM or PM.
- a
Lowercase Ante meridiem and Post meridiem, am or pm.
- S
English ordinal suffix for the day of the month, 2 characters, st, nd, rd, or th.
- L
Whether it's a leap year, 1 if it is a leap year, 0 otherwise.
ENCRYPTED FILES
Paradox supports file encryption for .db and .mb files. pxview can read encrypted files without specifying a password. You will actually not notice while reading a paradox file whether it is encrypted or not. The only difference is an extra line in the information output, listing the encryption vector which is a 4 byte hash value of the password. There is currently no way for pxview to regain the passwort from the hash.
AUTHOR
This manual page was written by Uwe Steinmann <<uwe@steinmann.cx>>.
Permission is
granted to copy, distribute and/or modify this document under
the terms of the GNU Free Documentation
License, Version 1.1 or any later version published by the Free
Software Foundation; with no Invariant Sections, no Front-Cover
Texts and no Back-Cover Texts.
Latest News
Released version 0.2.5August 9, 2005
- output some more information about blobs in debug mode
- Output password checksum if file is encrypted
- output format of timestamp, date, time can be set with program option. The default has been changed to the sql format.
02 Feb 05
After a long time the web site has been overhauled with much more information about pxview.
Released version 0.2.4January 24, 2005
- check return value of PX_get_data_alpha() and output an error message if needed
- mask special chars in memoblobs (sql output)
- use delimeter char for fields if they contain a line break (csv output)
- surrounds field data of memoblobs by enclosure char (csv output)
- primary key fields are treated as unique(field1, field2, field3, ...) and not unique(field1), unique(field2), ...
- fixed various memory leaks
- fixed many compiler warnings
- configure checks for PX_timestamp2string() to ensure pxlib >= 0.4.3 is present
- use functions like PX_get_num_fields() and PX_get_value() instead of directly accessing the file header structure
- create html version of manual pages on request