doc/src/sgml/isn.sgml

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