doc/src/sgml/isn.sgml - cloudberry - Git at Google

 <!-- doc/src/sgml/isn.sgml -->

 <sect1 id="isn" xreflabel="isn">
  <title>isn</title>

  <indexterm zone="isn">
   <primary>isn</primary>
  </indexterm>

  <para>
   The <filename>isn</filename> module provides data types for the following
   international product numbering standards: EAN13, UPC, ISBN (books), ISMN
   (music), and ISSN (serials).  Numbers are validated on input according to a
   hard-coded list of prefixes; this list of prefixes is also used to hyphenate
   numbers on output.  Since new prefixes are assigned from time to time, the
   list of prefixes may be out of date.  It is hoped that a future version of
   this module will obtain the prefix list from one or more tables that
   can be easily updated by users as needed; however, at present, the
   list can only be updated by modifying the source code and recompiling.
   Alternatively, prefix validation and hyphenation support may be
   dropped from a future version of this module.
  </para>

  <para>
   This module is considered <quote>trusted</quote>, that is, it can be
   installed by non-superusers who have <literal>CREATE</literal> privilege
   on the current database.
  </para>

  <sect2>
   <title>Data Types</title>

   <para>
    <xref linkend="isn-datatypes"/> shows the data types provided by
    the <filename>isn</filename> module.
   </para>

   <table id="isn-datatypes">
    <title><filename>isn</filename> Data Types</title>
    <tgroup cols="2">
     <colspec colname="col1" colwidth="1*"/>
     <colspec colname="col2" colwidth="2*"/>
     <thead>
      <row>
       <entry>Data Type</entry>
       <entry>Description</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><type>EAN13</type></entry>
       <entry>
        European Article Numbers, always displayed in the EAN13 display format
       </entry>
      </row>

      <row>
       <entry><type>ISBN13</type></entry>
       <entry>
        International Standard Book Numbers to be displayed in
        the new EAN13 display format
       </entry>
      </row>

      <row>
       <entry><type>ISMN13</type></entry>
       <entry>
        International Standard Music Numbers to be displayed in
        the new EAN13 display format
       </entry>
      </row>
      <row>
       <entry><type>ISSN13</type></entry>
       <entry>
        International Standard Serial Numbers to be displayed in the new
        EAN13 display format
       </entry>
      </row>
      <row>
       <entry><type>ISBN</type></entry>
       <entry>
        International Standard Book Numbers to be displayed in the old
        short display format
       </entry>
      </row>
      <row>
       <entry><type>ISMN</type></entry>
       <entry>
        International Standard Music Numbers to be displayed in the
        old short display format
       </entry>
      </row>
      <row>
       <entry><type>ISSN</type></entry>
       <entry>
        International Standard Serial Numbers to be displayed in the
        old short display format
       </entry>
      </row>
      <row>
       <entry><type>UPC</type></entry>
       <entry>
        Universal Product Codes
       </entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    Some notes:
   </para>

   <orderedlist>
    <listitem>
     <para>ISBN13, ISMN13, ISSN13 numbers are all EAN13 numbers.</para>
    </listitem>
    <listitem>
     <para>EAN13 numbers aren't always ISBN13, ISMN13 or ISSN13 (some
     are).</para>
    </listitem>
    <listitem>
     <para>Some ISBN13 numbers can be displayed as ISBN.</para>
    </listitem>
    <listitem>
     <para>Some ISMN13 numbers can be displayed as ISMN.</para>
    </listitem>
    <listitem>
     <para>Some ISSN13 numbers can be displayed as ISSN.</para>
    </listitem>
    <listitem>
     <para>UPC numbers are a subset of the EAN13 numbers (they are basically
     EAN13 without the first <literal>0</literal> digit).</para>
    </listitem>
    <listitem>
     <para>All UPC, ISBN, ISMN and ISSN numbers can be represented as EAN13
     numbers.</para>
    </listitem>
   </orderedlist>

   <para>
    Internally, all these types use the same representation (a 64-bit
    integer), and all are interchangeable.  Multiple types are provided
    to control display formatting and to permit tighter validity checking
    of input that is supposed to denote one particular type of number.
   </para>

   <para>
    The <type>ISBN</type>, <type>ISMN</type>, and <type>ISSN</type> types will display the
    short version of the number (ISxN 10) whenever it's possible, and will show
    ISxN 13 format for numbers that do not fit in the short version.
    The <type>EAN13</type>, <type>ISBN13</type>, <type>ISMN13</type> and
    <type>ISSN13</type> types will always display the long version of the ISxN
    (EAN13).
   </para>
  </sect2>

  <sect2>
   <title>Casts</title>

   <para>
    The <filename>isn</filename> module provides the following pairs of type casts:
   </para>

   <itemizedlist>
    <listitem>
     <para>
      ISBN13 &lt;=&gt; EAN13
     </para>
    </listitem>
    <listitem>
     <para>
      ISMN13 &lt;=&gt; EAN13
     </para>
    </listitem>
    <listitem>
     <para>
      ISSN13 &lt;=&gt; EAN13
     </para>
    </listitem>
    <listitem>
     <para>
      ISBN &lt;=&gt; EAN13
     </para>
    </listitem>
    <listitem>
     <para>
      ISMN &lt;=&gt; EAN13
     </para>
    </listitem>
    <listitem>
     <para>
      ISSN &lt;=&gt; EAN13
     </para>
    </listitem>
    <listitem>
     <para>
      UPC  &lt;=&gt; EAN13
     </para>
    </listitem>
    <listitem>
     <para>
      ISBN &lt;=&gt; ISBN13
     </para>
    </listitem>
    <listitem>
     <para>
      ISMN &lt;=&gt; ISMN13
     </para>
    </listitem>
    <listitem>
     <para>
      ISSN &lt;=&gt; ISSN13
     </para>
    </listitem>
   </itemizedlist>

   <para>
    When casting from <type>EAN13</type> to another type, there is a run-time
    check that the value is within the domain of the other type, and an error
    is thrown if not.  The other casts are simply relabelings that will
    always succeed.
   </para>
  </sect2>

  <sect2>
   <title>Functions and Operators</title>

   <para>
    The <filename>isn</filename> module provides the standard comparison operators,
    plus B-tree and hash indexing support for all these data types.  In
    addition there are several specialized functions; shown in <xref linkend="isn-functions"/>.
    In this table,
    <type>isn</type> means any one of the module's data types.
   </para>

   <table id="isn-functions">
    <title><filename>isn</filename> Functions</title>
     <tgroup cols="1">
      <thead>
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         Function
        </para>
        <para>
         Description
        </para></entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm><primary>isn_weak</primary></indexterm>
         <function>isn_weak</function> ( <type>boolean</type> )
         <returnvalue>boolean</returnvalue>
        </para>
        <para>
         Sets the weak input mode, and returns new setting.
        </para></entry>
       </row>

       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <function>isn_weak</function> ()
         <returnvalue>boolean</returnvalue>
        </para>
        <para>
         Returns the current status of the weak mode.
        </para></entry>
       </row>

       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm><primary>make_valid</primary></indexterm>
         <function>make_valid</function> ( <type>isn</type> )
         <returnvalue>isn</returnvalue>
        </para>
        <para>
         Validates an invalid number (clears the invalid flag).
        </para></entry>
       </row>

       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm><primary>is_valid</primary></indexterm>
         <function>is_valid</function> ( <type>isn</type> )
         <returnvalue>boolean</returnvalue>
        </para>
        <para>
         Checks for the presence of the invalid flag.
        </para></entry>
       </row>
      </tbody>
     </tgroup>
   </table>

   <para>
    <firstterm>Weak</firstterm> mode is used to be able to insert invalid data
    into a table. Invalid means the check digit is wrong, not that there are
    missing numbers.
   </para>

   <para>
    Why would you want to use the weak mode? Well, it could be that
    you have a huge collection of ISBN numbers, and that there are so many of
    them that for weird reasons some have the wrong check digit (perhaps the
    numbers were scanned from a printed list and the OCR got the numbers wrong,
    perhaps the numbers were manually captured... who knows). Anyway, the point
    is you might want to clean the mess up, but you still want to be able to
    have all the numbers in your database and maybe use an external tool to
    locate the invalid numbers in the database so you can verify the
    information and validate it more easily; so for example you'd want to
    select all the invalid numbers in the table.
   </para>

   <para>
    When you insert invalid numbers in a table using the weak mode, the number
    will be inserted with the corrected check digit, but it will be displayed
    with an exclamation mark (<literal>!</literal>) at the end, for example
    <literal>0-11-000322-5!</literal>.  This invalid marker can be checked with
    the <function>is_valid</function> function and cleared with the
    <function>make_valid</function> function.
   </para>

   <para>
    You can also force the insertion of invalid numbers even when not in the
    weak mode, by appending the <literal>!</literal> character at the end of the
    number.
   </para>

   <para>
    Another special feature is that during input, you can write
    <literal>?</literal> in place of the check digit, and the correct check digit
    will be inserted automatically.
   </para>
  </sect2>

  <sect2>
   <title>Examples</title>

 <programlisting>
 --Using the types directly:
 SELECT isbn('978-0-393-04002-9');
 SELECT isbn13('0901690546');
 SELECT issn('1436-4522');

 --Casting types:
 -- note that you can only cast from ean13 to another type when the
 -- number would be valid in the realm of the target type;
 -- thus, the following will NOT work: select isbn(ean13('0220356483481'));
 -- but these will:
 SELECT upc(ean13('0220356483481'));
 SELECT ean13(upc('220356483481'));

 --Create a table with a single column to hold ISBN numbers:
 CREATE TABLE test (id isbn);
 INSERT INTO test VALUES('9780393040029');

 --Automatically calculate check digits (observe the '?'):
 INSERT INTO test VALUES('220500896?');
 INSERT INTO test VALUES('978055215372?');

 SELECT issn('3251231?');
 SELECT ismn('979047213542?');

 --Using the weak mode:
 SELECT isn_weak(true);
 INSERT INTO test VALUES('978-0-11-000533-4');
 INSERT INTO test VALUES('9780141219307');
 INSERT INTO test VALUES('2-205-00876-X');
 SELECT isn_weak(false);

 SELECT id FROM test WHERE NOT is_valid(id);
 UPDATE test SET id = make_valid(id) WHERE id = '2-205-00876-X!';

 SELECT * FROM test;

 SELECT isbn13(id) FROM test;
 </programlisting>
  </sect2>

  <sect2>
   <title>Bibliography</title>

   <para>
    The information to implement this module was collected from
    several sites, including:
    <itemizedlist>
     <listitem><para><ulink url="https://www.isbn-international.org/"></ulink></para></listitem>
     <listitem><para><ulink url="https://www.issn.org/"></ulink></para></listitem>
     <listitem><para><ulink url="https://www.ismn-international.org/"></ulink></para></listitem>
     <listitem><para><ulink url="https://www.wikipedia.org/"></ulink></para></listitem>
    </itemizedlist>

    The prefixes used for hyphenation were also compiled from:
    <itemizedlist>
     <listitem><para><ulink url="https://www.gs1.org/standards/id-keys"></ulink></para></listitem>
     <listitem><para><ulink url="https://en.wikipedia.org/wiki/List_of_ISBN_identifier_groups"></ulink></para></listitem>
     <listitem><para><ulink url="https://www.isbn-international.org/content/isbn-users-manual"></ulink></para></listitem>
     <listitem><para><ulink url="https://en.wikipedia.org/wiki/International_Standard_Music_Number"></ulink></para></listitem>
     <listitem><para><ulink url="https://www.ismn-international.org/ranges.html"></ulink></para></listitem>
    </itemizedlist>

    Care was taken during the creation of the algorithms and they
    were meticulously verified against the suggested algorithms
    in the official ISBN, ISMN, ISSN User Manuals.
   </para>
  </sect2>

  <sect2>
   <title>Author</title>
   <para>
    Germ&aacute;n M&eacute;ndez Bravo (Kronuz), 2004&ndash;2006
   </para>

   <para>
    This module was inspired by Garrett A. Wollman's
    <filename>isbn_issn</filename> code.
   </para>
  </sect2>

 </sect1>
	<!-- doc/src/sgml/isn.sgml -->

	<sect1 id="isn" xreflabel="isn">
	<title>isn</title>

	<indexterm zone="isn">
	<primary>isn</primary>
	</indexterm>

	<para>
	The <filename>isn</filename> module provides data types for the following
	international product numbering standards: EAN13, UPC, ISBN (books), ISMN
	(music), and ISSN (serials). Numbers are validated on input according to a
	hard-coded list of prefixes; this list of prefixes is also used to hyphenate
	numbers on output. Since new prefixes are assigned from time to time, the
	list of prefixes may be out of date. It is hoped that a future version of
	this module will obtain the prefix list from one or more tables that
	can be easily updated by users as needed; however, at present, the
	list can only be updated by modifying the source code and recompiling.
	Alternatively, prefix validation and hyphenation support may be
	dropped from a future version of this module.
	</para>

	<para>
	This module is considered <quote>trusted</quote>, that is, it can be
	installed by non-superusers who have <literal>CREATE</literal> privilege
	on the current database.
	</para>

	<sect2>
	<title>Data Types</title>

	<para>
	<xref linkend="isn-datatypes"/> shows the data types provided by
	the <filename>isn</filename> module.
	</para>

	<table id="isn-datatypes">
	<title><filename>isn</filename> Data Types</title>
	<tgroup cols="2">
	<colspec colname="col1" colwidth="1*"/>
	<colspec colname="col2" colwidth="2*"/>
	<thead>
	<row>
	<entry>Data Type</entry>
	<entry>Description</entry>
	</row>
	</thead>

	<tbody>
	<row>
	<entry><type>EAN13</type></entry>
	<entry>
	European Article Numbers, always displayed in the EAN13 display format
	</entry>
	</row>

	<row>
	<entry><type>ISBN13</type></entry>
	<entry>
	International Standard Book Numbers to be displayed in
	the new EAN13 display format
	</entry>
	</row>

	<row>
	<entry><type>ISMN13</type></entry>
	<entry>
	International Standard Music Numbers to be displayed in
	the new EAN13 display format
	</entry>
	</row>
	<row>
	<entry><type>ISSN13</type></entry>
	<entry>
	International Standard Serial Numbers to be displayed in the new
	EAN13 display format
	</entry>
	</row>
	<row>
	<entry><type>ISBN</type></entry>
	<entry>
	International Standard Book Numbers to be displayed in the old
	short display format
	</entry>
	</row>
	<row>
	<entry><type>ISMN</type></entry>
	<entry>
	International Standard Music Numbers to be displayed in the
	old short display format
	</entry>
	</row>
	<row>
	<entry><type>ISSN</type></entry>
	<entry>
	International Standard Serial Numbers to be displayed in the
	old short display format
	</entry>
	</row>
	<row>
	<entry><type>UPC</type></entry>
	<entry>
	Universal Product Codes
	</entry>
	</row>
	</tbody>
	</tgroup>
	</table>

	<para>
	Some notes:
	</para>

	<orderedlist>
	<listitem>
	<para>ISBN13, ISMN13, ISSN13 numbers are all EAN13 numbers.</para>
	</listitem>
	<listitem>
	<para>EAN13 numbers aren't always ISBN13, ISMN13 or ISSN13 (some
	are).</para>
	</listitem>
	<listitem>
	<para>Some ISBN13 numbers can be displayed as ISBN.</para>
	</listitem>
	<listitem>
	<para>Some ISMN13 numbers can be displayed as ISMN.</para>
	</listitem>
	<listitem>
	<para>Some ISSN13 numbers can be displayed as ISSN.</para>
	</listitem>
	<listitem>
	<para>UPC numbers are a subset of the EAN13 numbers (they are basically
	EAN13 without the first <literal>0</literal> digit).</para>
	</listitem>
	<listitem>
	<para>All UPC, ISBN, ISMN and ISSN numbers can be represented as EAN13
	numbers.</para>
	</listitem>
	</orderedlist>

	<para>
	Internally, all these types use the same representation (a 64-bit
	integer), and all are interchangeable. Multiple types are provided
	to control display formatting and to permit tighter validity checking
	of input that is supposed to denote one particular type of number.
	</para>

	<para>
	The <type>ISBN</type>, <type>ISMN</type>, and <type>ISSN</type> types will display the
	short version of the number (ISxN 10) whenever it's possible, and will show
	ISxN 13 format for numbers that do not fit in the short version.
	The <type>EAN13</type>, <type>ISBN13</type>, <type>ISMN13</type> and
	<type>ISSN13</type> types will always display the long version of the ISxN
	(EAN13).
	</para>
	</sect2>

	<sect2>
	<title>Casts</title>

	<para>
	The <filename>isn</filename> module provides the following pairs of type casts:
	</para>

	<itemizedlist>
	<listitem>
	<para>
	ISBN13 <=> EAN13
	</para>
	</listitem>
	<listitem>
	<para>
	ISMN13 <=> EAN13
	</para>
	</listitem>
	<listitem>
	<para>
	ISSN13 <=> EAN13
	</para>
	</listitem>
	<listitem>
	<para>
	ISBN <=> EAN13
	</para>
	</listitem>
	<listitem>
	<para>
	ISMN <=> EAN13
	</para>
	</listitem>
	<listitem>
	<para>
	ISSN <=> EAN13
	</para>
	</listitem>
	<listitem>
	<para>
	UPC <=> EAN13
	</para>
	</listitem>
	<listitem>
	<para>
	ISBN <=> ISBN13
	</para>
	</listitem>
	<listitem>
	<para>
	ISMN <=> ISMN13
	</para>
	</listitem>
	<listitem>
	<para>
	ISSN <=> ISSN13
	</para>
	</listitem>
	</itemizedlist>

	<para>
	When casting from <type>EAN13</type> to another type, there is a run-time
	check that the value is within the domain of the other type, and an error
	is thrown if not. The other casts are simply relabelings that will
	always succeed.
	</para>
	</sect2>

	<sect2>
	<title>Functions and Operators</title>

	<para>
	The <filename>isn</filename> module provides the standard comparison operators,
	plus B-tree and hash indexing support for all these data types. In
	addition there are several specialized functions; shown in <xref linkend="isn-functions"/>.
	In this table,
	<type>isn</type> means any one of the module's data types.
	</para>

	<table id="isn-functions">
	<title><filename>isn</filename> Functions</title>
	<tgroup cols="1">
	<thead>
	<row>
	<entry role="func_table_entry"><para role="func_signature">
	Function
	</para>
	<para>
	Description
	</para></entry>
	</row>
	</thead>

	<tbody>
	<row>
	<entry role="func_table_entry"><para role="func_signature">
	<indexterm><primary>isn_weak</primary></indexterm>
	<function>isn_weak</function> ( <type>boolean</type> )
	<returnvalue>boolean</returnvalue>
	</para>
	<para>
	Sets the weak input mode, and returns new setting.
	</para></entry>
	</row>

	<row>
	<entry role="func_table_entry"><para role="func_signature">
	<function>isn_weak</function> ()
	<returnvalue>boolean</returnvalue>
	</para>
	<para>
	Returns the current status of the weak mode.
	</para></entry>
	</row>

	<row>
	<entry role="func_table_entry"><para role="func_signature">
	<indexterm><primary>make_valid</primary></indexterm>
	<function>make_valid</function> ( <type>isn</type> )
	<returnvalue>isn</returnvalue>
	</para>
	<para>
	Validates an invalid number (clears the invalid flag).
	</para></entry>
	</row>

	<row>
	<entry role="func_table_entry"><para role="func_signature">
	<indexterm><primary>is_valid</primary></indexterm>
	<function>is_valid</function> ( <type>isn</type> )
	<returnvalue>boolean</returnvalue>
	</para>
	<para>
	Checks for the presence of the invalid flag.
	</para></entry>
	</row>
	</tbody>
	</tgroup>
	</table>

	<para>
	<firstterm>Weak</firstterm> mode is used to be able to insert invalid data
	into a table. Invalid means the check digit is wrong, not that there are
	missing numbers.
	</para>

	<para>
	Why would you want to use the weak mode? Well, it could be that
	you have a huge collection of ISBN numbers, and that there are so many of
	them that for weird reasons some have the wrong check digit (perhaps the
	numbers were scanned from a printed list and the OCR got the numbers wrong,
	perhaps the numbers were manually captured... who knows). Anyway, the point
	is you might want to clean the mess up, but you still want to be able to
	have all the numbers in your database and maybe use an external tool to
	locate the invalid numbers in the database so you can verify the
	information and validate it more easily; so for example you'd want to
	select all the invalid numbers in the table.
	</para>

	<para>
	When you insert invalid numbers in a table using the weak mode, the number
	will be inserted with the corrected check digit, but it will be displayed
	with an exclamation mark (<literal>!</literal>) at the end, for example
	<literal>0-11-000322-5!</literal>. This invalid marker can be checked with
	the <function>is_valid</function> function and cleared with the
	<function>make_valid</function> function.
	</para>

	<para>
	You can also force the insertion of invalid numbers even when not in the
	weak mode, by appending the <literal>!</literal> character at the end of the
	number.
	</para>

	<para>
	Another special feature is that during input, you can write
	<literal>?</literal> in place of the check digit, and the correct check digit
	will be inserted automatically.
	</para>
	</sect2>

	<sect2>
	<title>Examples</title>

	<programlisting>
	--Using the types directly:
	SELECT isbn('978-0-393-04002-9');
	SELECT isbn13('0901690546');
	SELECT issn('1436-4522');

	--Casting types:
	-- note that you can only cast from ean13 to another type when the
	-- number would be valid in the realm of the target type;
	-- thus, the following will NOT work: select isbn(ean13('0220356483481'));
	-- but these will:
	SELECT upc(ean13('0220356483481'));
	SELECT ean13(upc('220356483481'));

	--Create a table with a single column to hold ISBN numbers:
	CREATE TABLE test (id isbn);
	INSERT INTO test VALUES('9780393040029');

	--Automatically calculate check digits (observe the '?'):
	INSERT INTO test VALUES('220500896?');
	INSERT INTO test VALUES('978055215372?');

	SELECT issn('3251231?');
	SELECT ismn('979047213542?');

	--Using the weak mode:
	SELECT isn_weak(true);
	INSERT INTO test VALUES('978-0-11-000533-4');
	INSERT INTO test VALUES('9780141219307');
	INSERT INTO test VALUES('2-205-00876-X');
	SELECT isn_weak(false);

	SELECT id FROM test WHERE NOT is_valid(id);
	UPDATE test SET id = make_valid(id) WHERE id = '2-205-00876-X!';

	SELECT * FROM test;

	SELECT isbn13(id) FROM test;
	</programlisting>
	</sect2>

	<sect2>
	<title>Bibliography</title>

	<para>
	The information to implement this module was collected from
	several sites, including:
	<itemizedlist>
	<listitem><para><ulink url="https://www.isbn-international.org/"></ulink></para></listitem>
	<listitem><para><ulink url="https://www.issn.org/"></ulink></para></listitem>
	<listitem><para><ulink url="https://www.ismn-international.org/"></ulink></para></listitem>
	<listitem><para><ulink url="https://www.wikipedia.org/"></ulink></para></listitem>
	</itemizedlist>

	The prefixes used for hyphenation were also compiled from:
	<itemizedlist>
	<listitem><para><ulink url="https://www.gs1.org/standards/id-keys"></ulink></para></listitem>
	<listitem><para><ulink url="https://en.wikipedia.org/wiki/List_of_ISBN_identifier_groups"></ulink></para></listitem>
	<listitem><para><ulink url="https://www.isbn-international.org/content/isbn-users-manual"></ulink></para></listitem>
	<listitem><para><ulink url="https://en.wikipedia.org/wiki/International_Standard_Music_Number"></ulink></para></listitem>
	<listitem><para><ulink url="https://www.ismn-international.org/ranges.html"></ulink></para></listitem>
	</itemizedlist>

	Care was taken during the creation of the algorithms and they
	were meticulously verified against the suggested algorithms
	in the official ISBN, ISMN, ISSN User Manuals.
	</para>
	</sect2>

	<sect2>
	<title>Author</title>
	<para>
	Germán Méndez Bravo (Kronuz), 2004–2006
	</para>

	<para>
	This module was inspired by Garrett A. Wollman's
	<filename>isbn_issn</filename> code.
	</para>
	</sect2>

	</sect1>