Debian Codegraph @

doc/src/sgml/isn.sgml

raw
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>make_valid</primary></indexterm>
256 <function>make_valid</function> ( <type>isn</type> )
257 <returnvalue>isn</returnvalue>
258 </para>
259 <para>
260 Clears the invalid-check-digit flag of the value.
261 </para></entry>
262 </row>
263
264 <row>
265 <entry role="func_table_entry"><para role="func_signature">
266 <indexterm><primary>is_valid</primary></indexterm>
267 <function>is_valid</function> ( <type>isn</type> )
268 <returnvalue>boolean</returnvalue>
269 </para>
270 <para>
271 Checks for the presence of the invalid-check-digit flag.
272 </para></entry>
273 </row>
274
275 <row>
276 <entry role="func_table_entry"><para role="func_signature">
277 <indexterm><primary>isn_weak</primary></indexterm>
278 <function>isn_weak</function> ( <type>boolean</type> )
279 <returnvalue>boolean</returnvalue>
280 </para>
281 <para>
282 Sets the weak input mode, and returns the new setting.
283 This function is retained for backward compatibility.
284 The recommended way to set weak mode is via
285 the <varname>isn.weak</varname> configuration parameter.
286 </para></entry>
287 </row>
288
289 <row>
290 <entry role="func_table_entry"><para role="func_signature">
291 <function>isn_weak</function> ()
292 <returnvalue>boolean</returnvalue>
293 </para>
294 <para>
295 Returns the current status of the weak mode.
296 This function is retained for backward compatibility.
297 The recommended way to check weak mode is via
298 the <varname>isn.weak</varname> configuration parameter.
299 </para></entry>
300 </row>
301 </tbody>
302 </tgroup>
303 </table>
304 </sect2>
305
306 <sect2 id="isn-configuration-parameters">
307 <title>Configuration Parameters</title>
308
309 <variablelist>
310 <varlistentry id="isn-configuration-parameters-weak">
311 <term>
312 <varname>isn.weak</varname> (<type>boolean</type>)
313 <indexterm>
314 <primary><varname>isn.weak</varname> configuration parameter</primary>
315 </indexterm>
316 </term>
317 <listitem>
318 <para>
319 <varname>isn.weak</varname> enables the weak input mode, which allows
320 ISN input values to be accepted even when their check digit is wrong.
321 The default is <literal>false</literal>, which rejects invalid check
322 digits.
323 </para>
324 </listitem>
325 </varlistentry>
326 </variablelist>
327
328 <para>
329 Why would you want to use the weak mode? Well, it could be that
330 you have a huge collection of ISBN numbers, and that there are so many of
331 them that for weird reasons some have the wrong check digit (perhaps the
332 numbers were scanned from a printed list and the OCR got the numbers wrong,
333 perhaps the numbers were manually captured... who knows). Anyway, the point
334 is you might want to clean the mess up, but you still want to be able to
335 have all the numbers in your database and maybe use an external tool to
336 locate the invalid numbers in the database so you can verify the
337 information and validate it more easily; so for example you'd want to
338 select all the invalid numbers in the table.
339 </para>
340
341 <para>
342 When you insert invalid numbers in a table using the weak mode, the number
343 will be inserted with the corrected check digit, but it will be displayed
344 with an exclamation mark (<literal>!</literal>) at the end, for example
345 <literal>0-11-000322-5!</literal>. This invalid marker can be checked with
346 the <function>is_valid</function> function and cleared with the
347 <function>make_valid</function> function.
348 </para>
349
350 <para>
351 You can also force the insertion of marked-as-invalid numbers even when not
352 in the weak mode, by appending the <literal>!</literal> character at the
353 end of the number.
354 </para>
355
356 <para>
357 Another special feature is that during input, you can write
358 <literal>?</literal> in place of the check digit, and the correct check digit
359 will be inserted automatically.
360 </para>
361 </sect2>
362
363 <sect2 id="isn-examples">
364 <title>Examples</title>
365
366 <programlisting>
367 --Using the types directly:
368 SELECT isbn('978-0-393-04002-9');
369 SELECT isbn13('0901690546');
370 SELECT issn('1436-4522');
371
372 --Casting types:
373 -- note that you can only cast from ean13 to another type when the
374 -- number would be valid in the realm of the target type;
375 -- thus, the following will NOT work: select isbn(ean13('0220356483481'));
376 -- but these will:
377 SELECT upc(ean13('0220356483481'));
378 SELECT ean13(upc('220356483481'));
379
380 --Create a table with a single column to hold ISBN numbers:
381 CREATE TABLE test (id isbn);
382 INSERT INTO test VALUES('9780393040029');
383
384 --Automatically calculate check digits (observe the '?'):
385 INSERT INTO test VALUES('220500896?');
386 INSERT INTO test VALUES('978055215372?');
387
388 SELECT issn('3251231?');
389 SELECT ismn('979047213542?');
390
391 --Using the weak mode:
392 SET isn.weak TO true;
393 INSERT INTO test VALUES('978-0-11-000533-4');
394 INSERT INTO test VALUES('9780141219307');
395 INSERT INTO test VALUES('2-205-00876-X');
396 SET isn.weak TO false;
397
398 SELECT id FROM test WHERE NOT is_valid(id);
399 UPDATE test SET id = make_valid(id) WHERE id = '2-205-00876-X!';
400
401 SELECT * FROM test;
402
403 SELECT isbn13(id) FROM test;
404 </programlisting>
405 </sect2>
406
407 <sect2 id="isn-bibliography">
408 <title>Bibliography</title>
409
410 <para>
411 The information to implement this module was collected from
412 several sites, including:
413 <itemizedlist>
414 <listitem><para><ulink url="https://www.isbn-international.org/"></ulink></para></listitem>
415 <listitem><para><ulink url="https://www.issn.org/"></ulink></para></listitem>
416 <listitem><para><ulink url="https://www.ismn-international.org/"></ulink></para></listitem>
417 <listitem><para><ulink url="https://www.wikipedia.org/"></ulink></para></listitem>
418 </itemizedlist>
419
420 The prefixes used for hyphenation were also compiled from:
421 <itemizedlist>
422 <listitem><para><ulink url="https://www.gs1.org/standards/id-keys"></ulink></para></listitem>
423 <listitem><para><ulink url="https://en.wikipedia.org/wiki/List_of_ISBN_registration_groups"></ulink></para></listitem>
424 <listitem><para><ulink url="https://www.isbn-international.org/content/isbn-users-manual/29"></ulink></para></listitem>
425 <listitem><para><ulink url="https://en.wikipedia.org/wiki/International_Standard_Music_Number"></ulink></para></listitem>
426 <listitem><para><ulink url="https://www.ismn-international.org/ranges/tools"></ulink></para></listitem>
427 </itemizedlist>
428
429 Care was taken during the creation of the algorithms and they
430 were meticulously verified against the suggested algorithms
431 in the official ISBN, ISMN, ISSN User Manuals.
432 </para>
433 </sect2>
434
435 <sect2 id="isn-author">
436 <title>Author</title>
437 <para>
438 Germ&aacute;n M&eacute;ndez Bravo (Kronuz), 2004&ndash;2006
439 </para>
440
441 <para>
442 This module was inspired by Garrett A. Wollman's
443 <filename>isbn_issn</filename> code.
444 </para>
445 </sect2>
446
447 </sect1>