doc/src/sgml/isn.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="isn">
   4  <title>isn</title>
   5
   6  <indexterm zone="isn">
   7   <primary>isn</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>isn</filename> module provides data types for the following
  12   international product numbering standards: EAN13, UPC, ISBN (books), ISMN
  13   (music), and ISSN (serials).  Numbers are validated on input, and correctly
  14   hyphenated on output.
  15  </para>
  16
  17  <sect2>
  18   <title>Data types</title>
  19
  20   <table>
  21    <title><filename>isn</filename> data types</title>
  22    <tgroup cols="2">
  23     <thead>
  24      <row>
  25       <entry>Data type</entry>
  26       <entry>Description</entry>
  27      </row>
  28     </thead>
  29
  30     <tbody>
  31      <row>
  32       <entry><type>EAN13</type></entry>
  33       <entry>
  34        European Article Numbers, always displayed in the EAN13 display format
  35       </entry>
  36      </row>
  37
  38      <row>
  39       <entry><type>ISBN13</type></entry>
  40       <entry>
  41        International Standard Book Numbers to be displayed in
  42        the new EAN13 display format
  43       </entry>
  44      </row>
  45
  46      <row>
  47       <entry><type>ISMN13</type></entry>
  48       <entry>
  49        International Standard Music Numbers to be displayed in
  50        the new EAN13 display format
  51       </entry>
  52      </row>
  53      <row>
  54       <entry><type>ISSN13</type></entry>
  55       <entry>
  56        International Standard Serial Numbers to be displayed in the new
  57        EAN13 display format
  58       </entry>
  59      </row>
  60      <row>
  61       <entry><type>ISBN</type></entry>
  62       <entry>
  63        International Standard Book Numbers to be displayed in the old
  64        short display format
  65       </entry>
  66      </row>
  67      <row>
  68       <entry><type>ISMN</type></entry>
  69       <entry>
  70        International Standard Music Numbers to be displayed in the
  71        old short display format
  72       </entry>
  73      </row>
  74      <row>
  75       <entry><type>ISSN</type></entry>
  76       <entry>
  77        International Standard Serial Numbers to be displayed in the
  78        old short display format
  79       </entry>
  80      </row>
  81      <row>
  82       <entry><type>UPC</type></entry>
  83       <entry>
  84        Universal Product Codes
  85       </entry>
  86      </row>
  87     </tbody>
  88    </tgroup>
  89   </table>
  90
  91   <para>
  92    Some notes:
  93   </para>
  94
  95   <orderedlist>
  96    <listitem>
  97     <para>ISBN13, ISMN13, ISSN13 numbers are all EAN13 numbers.</para>
  98    </listitem>
  99    <listitem>
 100     <para>EAN13 numbers aren't always ISBN13, ISMN13 or ISSN13 (some
 101     are).</para>
 102    </listitem>
 103    <listitem>
 104     <para>Some ISBN13 numbers can be displayed as ISBN.</para>
 105    </listitem>
 106    <listitem>
 107     <para>Some ISMN13 numbers can be displayed as ISMN.</para>
 108    </listitem>
 109    <listitem>
 110     <para>Some ISSN13 numbers can be displayed as ISSN.</para>
 111    </listitem>
 112    <listitem>
 113     <para>UPC numbers are a subset of the EAN13 numbers (they are basically
 114     EAN13 without the first <literal>0</> digit).</para>
 115    </listitem>
 116    <listitem>
 117     <para>All UPC, ISBN, ISMN and ISSN numbers can be represented as EAN13
 118     numbers.</para>
 119    </listitem>
 120   </orderedlist>
 121
 122   <para>
 123    Internally, all these types use the same representation (a 64-bit
 124    integer), and all are interchangeable.  Multiple types are provided
 125    to control display formatting and to permit tighter validity checking
 126    of input that is supposed to denote one particular type of number.
 127   </para>
 128
 129   <para>
 130    The <type>ISBN</>, <type>ISMN</>, and <type>ISSN</> types will display the
 131    short version of the number (ISxN 10) whenever it's possible, and will show
 132    ISxN 13 format for numbers that do not fit in the short version.
 133    The <type>EAN13</type>, <type>ISBN13</type>, <type>ISMN13</type> and
 134    <type>ISSN13</type> types will always display the long version of the ISxN
 135    (EAN13).
 136   </para>
 137  </sect2>
 138
 139  <sect2>
 140   <title>Casts</title>
 141
 142   <para>
 143    The <filename>isn</> module provides the following pairs of type casts:
 144   </para>
 145
 146   <itemizedlist>
 147    <listitem>
 148     <para>
 149      ISBN13 &lt;=&gt; EAN13
 150     </para>
 151    </listitem>
 152    <listitem>
 153     <para>
 154      ISMN13 &lt;=&gt; EAN13
 155     </para>
 156    </listitem>
 157    <listitem>
 158     <para>
 159      ISSN13 &lt;=&gt; EAN13
 160     </para>
 161    </listitem>
 162    <listitem>
 163     <para>
 164      ISBN &lt;=&gt; EAN13
 165     </para>
 166    </listitem>
 167    <listitem>
 168     <para>
 169      ISMN &lt;=&gt; EAN13
 170     </para>
 171    </listitem>
 172    <listitem>
 173     <para>
 174      ISSN &lt;=&gt; EAN13
 175     </para>
 176    </listitem>
 177    <listitem>
 178     <para>
 179      UPC  &lt;=&gt; EAN13
 180     </para>
 181    </listitem>
 182    <listitem>
 183     <para>
 184      ISBN &lt;=&gt; ISBN13
 185     </para>
 186    </listitem>
 187    <listitem>
 188     <para>
 189      ISMN &lt;=&gt; ISMN13
 190     </para>
 191    </listitem>
 192    <listitem>
 193     <para>
 194      ISSN &lt;=&gt; ISSN13
 195     </para>
 196    </listitem>
 197   </itemizedlist>
 198
 199   <para>
 200    When casting from <type>EAN13</> to another type, there is a run-time
 201    check that the value is within the domain of the other type, and an error
 202    is thrown if not.  The other casts are simply relabelings that will
 203    always succeed.
 204   </para>
 205  </sect2>
 206
 207  <sect2>
 208   <title>Functions and Operators</title>
 209
 210   <para>
 211    The <filename>isn</> module provides the standard comparison operators,
 212    plus btree and hash indexing support for all these datatypes.  In
 213    addition there are several specialized functions; shown in <xref linkend="isn-functions">.
 214    In this table,
 215    <type>isn</> means any one of the module's data types.
 216   </para>
 217
 218   <table id="isn-functions">
 219    <title><filename>isn</> functions</title>
 220    <tgroup cols="3">
 221     <thead>
 222      <row>
 223       <entry>Function</entry>
 224       <entry>Returns</entry>
 225       <entry>Description</entry>
 226      </row>
 227     </thead>
 228
 229     <tbody>
 230      <row>
 231       <entry><function>isn_weak(boolean)</function></entry>
 232       <entry><type>boolean</type></entry>
 233       <entry>Sets the weak input mode (returns new setting)</entry>
 234      </row>
 235      <row>
 236       <entry><function>isn_weak()</function></entry>
 237       <entry><type>boolean</type></entry>
 238       <entry>Gets the current status of the weak mode</entry>
 239      </row>
 240      <row>
 241       <entry><function>make_valid(isn)</function></entry>
 242       <entry><type>isn</type></entry>
 243       <entry>Validates an invalid number (clears the invalid flag)</entry>
 244      </row>
 245      <row>
 246       <entry><function>is_valid(isn)</function></entry>
 247       <entry><type>boolean</type></entry>
 248       <entry>Checks for the presence of the invalid flag</entry>
 249      </row>
 250     </tbody>
 251    </tgroup>
 252   </table>
 253
 254   <para>
 255    <firstterm>Weak</firstterm> mode is used to be able to insert invalid data
 256    into a table. Invalid means the check digit is wrong, not that there are
 257    missing numbers.
 258   </para>
 259
 260   <para>
 261    Why would you want to use the weak mode? Well, it could be that
 262    you have a huge collection of ISBN numbers, and that there are so many of
 263    them that for weird reasons some have the wrong check digit (perhaps the
 264    numbers were scanned from a printed list and the OCR got the numbers wrong,
 265    perhaps the numbers were manually captured... who knows). Anyway, the point
 266    is you might want to clean the mess up, but you still want to be able to
 267    have all the numbers in your database and maybe use an external tool to
 268    locate the invalid numbers in the database so you can verify the
 269    information and validate it more easily; so for example you'd want to
 270    select all the invalid numbers in the table.
 271   </para>
 272
 273   <para>
 274    When you insert invalid numbers in a table using the weak mode, the number
 275    will be inserted with the corrected check digit, but it will be displayed
 276    with an exclamation mark (<literal>!</>) at the end, for example
 277    <literal>0-11-000322-5!</>.  This invalid marker can be checked with
 278    the <function>is_valid</> function and cleared with the
 279    <function>make_valid</> function.
 280   </para>
 281
 282   <para>
 283    You can also force the insertion of invalid numbers even when not in the
 284    weak mode, by appending the <literal>!</> character at the end of the
 285    number.
 286   </para>
 287
 288   <para>
 289    Another special feature is that during input, you can write
 290    <literal>?</> in place of the check digit, and the correct check digit
 291    will be inserted automatically.
 292   </para>
 293  </sect2>
 294
 295  <sect2>
 296   <title>Examples</title>
 297
 298   <programlisting>
 299 --Using the types directly:
 300 SELECT isbn('978-0-393-04002-9');
 301 SELECT isbn13('0901690546');
 302 SELECT issn('1436-4522');
 303
 304 --Casting types:
 305 -- note that you can only cast from ean13 to another type when the
 306 -- number would be valid in the realm of the target type;
 307 -- thus, the following will NOT work: select isbn(ean13('0220356483481'));
 308 -- but these will:
 309 SELECT upc(ean13('0220356483481'));
 310 SELECT ean13(upc('220356483481'));
 311
 312 --Create a table with a single column to hold ISBN numbers:
 313 CREATE TABLE test (id isbn);
 314 INSERT INTO test VALUES('9780393040029');
 315
 316 --Automatically calculate check digits (observe the '?'):
 317 INSERT INTO test VALUES('220500896?');
 318 INSERT INTO test VALUES('978055215372?');
 319
 320 SELECT issn('3251231?');
 321 SELECT ismn('979047213542?');
 322
 323 --Using the weak mode:
 324 SELECT isn_weak(true);
 325 INSERT INTO test VALUES('978-0-11-000533-4');
 326 INSERT INTO test VALUES('9780141219307');
 327 INSERT INTO test VALUES('2-205-00876-X');
 328 SELECT isn_weak(false);
 329
 330 SELECT id FROM test WHERE NOT is_valid(id);
 331 UPDATE test SET id = make_valid(id) WHERE id = '2-205-00876-X!';
 332
 333 SELECT * FROM test;
 334
 335 SELECT isbn13(id) FROM test;
 336   </programlisting>
 337  </sect2>
 338
 339  <sect2>
 340   <title>Bibliography</title>
 341
 342   <para>
 343    The information to implement this module was collected from
 344    several sites, including:
 345   </para>
 346   <programlisting>
 347    http://www.isbn-international.org/
 348    http://www.issn.org/
 349    http://www.ismn-international.org/
 350    http://www.wikipedia.org/
 351   </programlisting>
 352
 353   <para>
 354    The prefixes used for hyphenation were also compiled from:
 355   </para>
 356   <programlisting>
 357    http://www.gs1.org/productssolutions/idkeys/support/prefix_list.html
 358    http://www.isbn-international.org/en/identifiers.html
 359    http://www.ismn-international.org/ranges.html
 360   </programlisting>
 361
 362   <para>
 363    Care was taken during the creation of the algorithms and they
 364    were meticulously verified against the suggested algorithms
 365    in the official ISBN, ISMN, ISSN User Manuals.
 366   </para>
 367  </sect2>
 368
 369  <sect2>
 370   <title>Author</title>
 371   <para>
 372    Germán Méndez Bravo (Kronuz), 2004 - 2006
 373   </para>
 374
 375   <para>
 376    This module was inspired by Garrett A. Wollman's
 377    isbn_issn code.
 378   </para>
 379  </sect2>
 380
 381 </sect1>