Hello,
I'm creating a list of all my files anywhere in a 60' diameter on any
possible media around me.
For such purpose I'm planning to use PostgreSQL.
The main table might look like :
create table file (
name varchar(255), -- file : name; tape : name / #; tapedata : #
am int, -- method of accessing contained file(s)/data
-- one of tape,gz,tar,Z,zip,spio,afio,cab,crypt
-- pgp,AUTO
size int8, -- file : file size up to 2 ^ 64
-- tape : sum of datablock sizes
md5 char(32), -- MD5 sum of the data of size
created datetime, -- date of creation timestamp
modified datetime, -- date of last modification timestamp
type int, -- directory entry type :
-- file,dir,link,device,pipe
mode int2, -- 16 mode bits
ownernm char(8), -- owner name
ownerid int2, -- owner id
groupnm char(8), -- group name
groupid int2, -- group id
links int, -- number of hard links
mjmn int2 -- if device, major / minor #
);
However, the first line "name varchar(255)" will waste a lot of
space, since only about %0.02 files will be over 200 chars, %0.1 over 100,
%0.2 over 64 and %1.6 over 32.
I'm looking for alternate ways to store a full 255 char filename. I
would prefer to be able to get all filenames through a single SELECT that
looks the same for both short filenames (<32) and long filenames
(>32,<256).
It is not terribly important that select or insert access is fast.
Though having faster insert might be good. Almost no updates will be
issued. But the ability to index on the filename is of some importance (at
least on the first about 32 chars).
I was wondering whether "name text," would save the space or not.
Perhaps there would be smarter or more elegant suggestions than "name
text" for the field type, so I'm writing to the list to get some ideas.
TIA,
All the best for PostgreSQL team in 1999,
Jan.
-- Gospel of Jesus is the saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Vicherek.Waterloo.on.ca/
