Hello hackers,
For a project of ours we need GTIN14 data type support. The isn
extension already supports EAN13, and a '0' prefixed EAN13 is a valid
GTIN14. The leftmost "new" 14th digit is a packaging level indicator
which we need (= using EAN13 and faking a leading 0 in output doesn't
cut it).
Looking at the code I saw every format that isn-extension supports is
stored as an EAN13. Theoretically that can be changed to be GTIN14, but
that would mean quite a lot of rewrite I feared, so I chose to code only
GTIN14 I/O separetely to not interfere with any existing conversion
magic. This yields an easier to understand patch and doesn't touch
existing functionality. However it introduces redundancy to a certain
extent.
Find my patch attached. Please let me know if there are things that need
changes, I'll do my best to get GTIN support into postgresql.
thanks in advance
mike
diff --git a/expected/isn.out b/expected/isn.out
index 18fe37a..b313641 100644
--- a/expected/isn.out
+++ b/expected/isn.out
@@ -18,6 +18,8 @@ INFO: operator family "isn_ops" of access method btree is
missing cross-type op
INFO: operator family "isn_ops" of access method btree is missing cross-type
operator(s)
INFO: operator family "isn_ops" of access method btree is missing cross-type
operator(s)
INFO: operator family "isn_ops" of access method btree is missing cross-type
operator(s)
+INFO: operator family "isn_ops" of access method btree is missing cross-type
operator(s)
+INFO: operator family "isn_ops" of access method hash is missing cross-type
operator(s)
INFO: operator family "isn_ops" of access method hash is missing cross-type
operator(s)
INFO: operator family "isn_ops" of access method hash is missing cross-type
operator(s)
INFO: operator family "isn_ops" of access method hash is missing cross-type
operator(s)
@@ -29,6 +31,7 @@ INFO: operator family "isn_ops" of access method hash is
missing cross-type ope
amname | opcname
--------+------------
btree | ean13_ops
+ btree | gtin14_ops
btree | isbn13_ops
btree | isbn_ops
btree | ismn13_ops
@@ -37,6 +40,7 @@ INFO: operator family "isn_ops" of access method hash is
missing cross-type ope
btree | issn_ops
btree | upc_ops
hash | ean13_ops
+ hash | gtin14_ops
hash | isbn13_ops
hash | isbn_ops
hash | ismn13_ops
@@ -44,11 +48,20 @@ INFO: operator family "isn_ops" of access method hash is
missing cross-type ope
hash | issn13_ops
hash | issn_ops
hash | upc_ops
-(16 rows)
+(18 rows)
--
-- test valid conversions
--
+SELECT '1234567890128'::GTIN14, -- EAN is a GTIN14
+ '123456789012?'::GTIN14, -- compute check digit for me
+ '11234567890125'::GTIN14,
+ '01234567890128'::GTIN14;
+ gtin14 | gtin14 | gtin14 | gtin14
+-----------------+-----------------+-----------------+-----------------
+ 0123456789012-8 | 0123456789012-8 | 1123456789012-5 | 0123456789012-8
+(1 row)
+
SELECT '9780123456786'::EAN13, -- old book
'9790123456785'::EAN13, -- music
'9791234567896'::EAN13, -- new book
@@ -249,6 +262,67 @@ SELECT 9780123456786::ISBN;
ERROR: cannot cast type bigint to isbn
LINE 1: SELECT 9780123456786::ISBN;
^
+SELECT '91234567890125'::GTIN14; -- invalid indicator
+ERROR: Indicator digit out of range for GTIN14 number: "91234567890125"
+LINE 1: SELECT '91234567890125'::GTIN14;
+ ^
+SELECT '123456789012'::GTIN14; -- too short
+ERROR: invalid input syntax for GTIN14 number: "123456789012"
+LINE 1: SELECT '123456789012'::GTIN14;
+ ^
+SELECT '1234567890127'::GTIN14; -- wrong checkdigit
+ERROR: invalid check digit for GTIN14 number: "1234567890127", should be 8
+LINE 1: SELECT '1234567890127'::GTIN14;
+ ^
+--
+-- test validity helpers
+--
+SELECT make_valid('1234567890120!'::GTIN14); -- EAN-13
+ make_valid
+-----------------
+ 0123456789012-8
+(1 row)
+
+SELECT make_valid('11234567890120!'::GTIN14); -- GTIN-14
+ make_valid
+-----------------
+ 1123456789012-5
+(1 row)
+
+SELECT is_valid(make_valid('1234567890120!'::GTIN14)); -- EAN-13
+ is_valid
+----------
+ t
+(1 row)
+
+SELECT is_valid(make_valid('11234567890120!'::GTIN14)); -- GTIN-14
+ is_valid
+----------
+ t
+(1 row)
+
+CREATE TABLE gtin_valid (gtin GTIN14 NOT NULL);
+INSERT INTO gtin_valid VALUES
+-- all invalid because of ! marking
+ ('1234567890120!'), -- invalid EAN-13
+ ('1234567890128!'), -- valid EAN-13
+ ('11234567890120!'), -- invalid GTIN-14
+ ('11234567890125!'), -- valid GTIN-14
+-- valid
+ ('1234567890128'::GTIN14), -- valid EAN-13
+ ('11234567890125'::GTIN14); -- valid GTIN-14
+SELECT gtin, is_valid(gtin) FROM gtin_valid;
+ gtin | is_valid
+------------------+----------
+ 0123456789012-8! | f
+ 0123456789012-8! | f
+ 1123456789012-5! | f
+ 1123456789012-5! | f
+ 0123456789012-8 | t
+ 1123456789012-5 | t
+(6 rows)
+
+DROP TABLE gtin_valid;
--
-- test some comparisons, must yield true
--
diff --git a/isn--1.1.sql b/isn--1.1.sql
index 5206961..fd3f157 100644
--- a/isn--1.1.sql
+++ b/isn--1.1.sql
@@ -15,6 +15,26 @@
-- Input and output functions and data types:
--
---------------------------------------------------
+CREATE FUNCTION gtin14_in(cstring)
+ RETURNS gtin14
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
+CREATE FUNCTION gtin14_out(gtin14)
+ RETURNS cstring
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
+CREATE TYPE gtin14 (
+ INPUT = gtin14_in,
+ OUTPUT = gtin14_out,
+ LIKE = pg_catalog.int8
+);
+COMMENT ON TYPE gtin14
+ IS 'Global Trade Item Number (GTIN-14)';
+
CREATE FUNCTION ean13_in(cstring)
RETURNS ean13
AS 'MODULE_PATHNAME'
@@ -181,6 +201,44 @@ COMMENT ON TYPE upc
-- Operator functions:
--
---------------------------------------------------
+-- GTIN-14:
+CREATE FUNCTION isnlt(gtin14, gtin14)
+ RETURNS boolean
+ AS 'int8lt'
+ LANGUAGE 'internal'
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
+CREATE FUNCTION isnle(gtin14, gtin14)
+ RETURNS boolean
+ AS 'int8le'
+ LANGUAGE 'internal'
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
+CREATE FUNCTION isneq(gtin14, gtin14)
+ RETURNS boolean
+ AS 'int8eq'
+ LANGUAGE 'internal'
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
+CREATE FUNCTION isnge(gtin14, gtin14)
+ RETURNS boolean
+ AS 'int8ge'
+ LANGUAGE 'internal'
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
+CREATE FUNCTION isngt(gtin14, gtin14)
+ RETURNS boolean
+ AS 'int8gt'
+ LANGUAGE 'internal'
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
+CREATE FUNCTION isnne(gtin14, gtin14)
+ RETURNS boolean
+ AS 'int8ne'
+ LANGUAGE 'internal'
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
+
-- EAN13:
CREATE FUNCTION isnlt(ean13, ean13)
RETURNS boolean
@@ -1236,6 +1294,61 @@ CREATE FUNCTION isnne(upc, ean13)
-- Now the operators:
--
+--
+-- GTIN-14 operators:
+--
+---------------------------------------------------
+CREATE OPERATOR < (
+ PROCEDURE = isnlt,
+ LEFTARG = gtin14,
+ RIGHTARG = gtin14,
+ COMMUTATOR = >,
+ NEGATOR = >=,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel);
+CREATE OPERATOR <= (
+ PROCEDURE = isnle,
+ LEFTARG = gtin14,
+ RIGHTARG = gtin14,
+ COMMUTATOR = >=,
+ NEGATOR = >,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel);
+CREATE OPERATOR = (
+ PROCEDURE = isneq,
+ LEFTARG = gtin14,
+ RIGHTARG = gtin14,
+ COMMUTATOR = =,
+ NEGATOR = <>,
+ RESTRICT = eqsel,
+ JOIN = eqjoinsel,
+ MERGES,
+ HASHES);
+CREATE OPERATOR >= (
+ PROCEDURE = isnge,
+ LEFTARG = gtin14,
+ RIGHTARG = gtin14,
+ COMMUTATOR = <=,
+ NEGATOR = <,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel );
+CREATE OPERATOR > (
+ PROCEDURE = isngt,
+ LEFTARG = gtin14,
+ RIGHTARG = gtin14,
+ COMMUTATOR = <,
+ NEGATOR = <=,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel );
+CREATE OPERATOR <> (
+ PROCEDURE = isnne,
+ LEFTARG = gtin14,
+ RIGHTARG = gtin14,
+ COMMUTATOR = <>,
+ NEGATOR = =,
+ RESTRICT = neqsel,
+ JOIN = neqjoinsel);
+
--
-- EAN13 operators:
--
@@ -2708,6 +2821,34 @@ CREATE OPERATOR FAMILY isn_ops USING hash;
-- Operator classes:
--
---------------------------------------------------
+-- GTIN-14:
+CREATE FUNCTION btgtin14cmp(gtin14, gtin14)
+ RETURNS int4
+ AS 'btint8cmp'
+ LANGUAGE 'internal'
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
+
+CREATE OPERATOR CLASS gtin14_ops DEFAULT
+ FOR TYPE gtin14 USING btree FAMILY isn_ops AS
+ OPERATOR 1 <,
+ OPERATOR 2 <=,
+ OPERATOR 3 =,
+ OPERATOR 4 >=,
+ OPERATOR 5 >,
+ FUNCTION 1 btgtin14cmp(gtin14, gtin14);
+
+CREATE FUNCTION hashgtin14(gtin14)
+ RETURNS int4
+ AS 'hashint8'
+ LANGUAGE 'internal' IMMUTABLE STRICT
+ PARALLEL SAFE;
+
+CREATE OPERATOR CLASS gtin14_ops DEFAULT
+ FOR TYPE gtin14 USING hash FAMILY isn_ops AS
+ OPERATOR 1 =,
+ FUNCTION 1 hashgtin14(gtin14);
+
-- EAN13:
CREATE FUNCTION btean13cmp(ean13, ean13)
RETURNS int4
@@ -3314,6 +3455,12 @@ CREATE CAST (issn13 AS issn) WITHOUT FUNCTION AS
ASSIGNMENT;
--
-- Validation stuff for lose types:
--
+CREATE FUNCTION make_valid(gtin14)
+ RETURNS gtin14
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
CREATE FUNCTION make_valid(ean13)
RETURNS ean13
AS 'MODULE_PATHNAME'
@@ -3363,6 +3510,12 @@ CREATE FUNCTION make_valid(upc)
IMMUTABLE STRICT
PARALLEL SAFE;
+CREATE FUNCTION is_valid(gtin14)
+ RETURNS boolean
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C
+ IMMUTABLE STRICT
+ PARALLEL SAFE;
CREATE FUNCTION is_valid(ean13)
RETURNS boolean
AS 'MODULE_PATHNAME'
diff --git a/isn.c b/isn.c
index 0c2cac7..d7baa78 100644
--- a/isn.c
+++ b/isn.c
@@ -36,10 +36,10 @@ PG_MODULE_MAGIC;
enum isn_type
{
- INVALID, ANY, EAN13, ISBN, ISMN, ISSN, UPC
+ INVALID, ANY, EAN13, ISBN, ISMN, ISSN, UPC, GTIN14
};
-static const char *const isn_names[] = {"EAN13/UPC/ISxN", "EAN13/UPC/ISxN",
"EAN13", "ISBN", "ISMN", "ISSN", "UPC"};
+static const char *const isn_names[] = {"EAN13/UPC/ISxN", "EAN13/UPC/ISxN",
"EAN13", "ISBN", "ISMN", "ISSN", "UPC", "GTIN14"};
static bool g_weak = false;
@@ -325,7 +325,10 @@ checkdig(char *num, unsigned size)
}
num++;
}
- check = (check + 3 * check3) % 10;
+ if (pos % 2 == 0) /* for even length strings */
+ check = (check + 3 * check3) % 10;
+ else
+ check = (check3 + 3 * check) % 10;
if (check != 0)
check = 10 - check;
return check;
@@ -500,11 +503,9 @@ ean2UPC(char *isn)
}
/*
- * ean2* --- Converts a string of digits into an ean13 number.
- * Assumes the input string is a string with only digits
- * on it, and that it's within the range of ean13.
+ * str2ean --- Converts a string of digits into binary storage format.
*
- * Returns the ean13 value of the string.
+ * Returns the ean13 with valid-flag set to true.
*/
static ean13
str2ean(const char *num)
@@ -520,6 +521,71 @@ str2ean(const char *num)
return (ean << 1); /* also give room to a flag */
}
+/*
+ * gtin14_2string --- Try to convert GTINumber to a hyphenated string.
+ * Assumes there's enough space in result to hold
+ * the string (maximum MAXEAN13LEN+1 bytes)
+ * This doesn't verify for a valid check digit.
+ *
+ * If errorOK is false, ereport a useful error message if the string is bad.
+ * If errorOK is true, just return "false" for bad input.
+ */
+static bool
+gtin14_2string(ean13 ean, bool errorOK, char *result)
+{
+ enum isn_type type = INVALID;
+
+ char *aux;
+ unsigned digval;
+ unsigned search;
+ char valid = '\0'; /* was the number initially written
with a
+ * valid check
digit? */
+
+ if ((ean & 1) != 0)
+ valid = '!';
+ ean >>= 1;
+ /* verify it's in the EAN13 range */
+ if (ean > UINT64CONST(99999999999999))
+ goto eantoobig;
+
+ /* convert the number */
+ search = 0;
+ aux = result + MAXEAN13LEN;
+ *aux = '\0'; /* terminate string; aux points
to last digit */
+ *--aux = valid; /* append '!' for numbers with
invalid but
+ * corrected
check digit */
+ do
+ {
+ digval = (unsigned) (ean % 10); /* get the decimal value */
+ ean /= 10; /* get next digit */
+ *--aux = (char) (digval + '0'); /* convert to ascii and store */
+ if (search == 0)
+ *--aux = '-'; /* the check digit is always
there */
+ } while (ean && search++ < 14);
+ while (search++ < 14)
+ *--aux = '0'; /* fill the remaining GTIN14
with '0' */
+
+ search = hyphenate(result, result + 2, NULL, NULL);
+
+ return true;
+
+eantoobig:
+ if (!errorOK)
+ {
+ char eanbuf[64];
+
+ /*
+ * Format the number separately to keep the machine-dependent
format
+ * code out of the translatable message text
+ */
+ snprintf(eanbuf, sizeof(eanbuf), EAN13_FORMAT, ean);
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("value \"%s\" is out of range for %s
type",
+ eanbuf, isn_names[type])));
+ }
+ return false;
+}
/*
* ean2string --- Try to convert an ean13 number to a hyphenated string.
* Assumes there's enough space in result to hold
@@ -673,6 +739,162 @@ eantoobig:
return false;
}
+/*
+ * string2gtin14 --- try to parse a string into an gtin14.
+ *
+ * If errorOK is false, ereport a useful error message if the string is bad.
+ * If errorOK is true, just return "false" for bad input.
+ *
+ * if the input string ends with '!' it will always be treated as invalid
+ * (even if the check digit is valid)
+ */
+static bool
+string2gtin14(const char *str, bool errorOK, ean13 *result,
+ enum isn_type accept)
+{
+ bool digit,
+ last;
+ char buf[17] = " ";
+ char *aux1 = buf + 2; /* leave space for the first part, in case
+ * it's needed
*/
+ const char *aux2 = str;
+ enum isn_type type = INVALID;
+ unsigned check = 0,
+ rcheck = (unsigned) -1;
+ unsigned length = 0;
+ bool magic = false,
+ valid = true;
+
+ /* recognize and validate the number: */
+ while (*aux2 && length <= 14)
+ {
+ last = (*(aux2 + 1) == '!' || *(aux2 + 1) == '\0'); /* is the
last character */
+ digit = (isdigit((unsigned char) *aux2) != 0); /* is current
character
+
* a digit? */
+ if (*aux2 == '?' && last) /* automagically calculate
check digit if
+ * it's
'?' */
+ magic = digit = true;
+ if (*aux2 == '!' && *(aux2 + 1) == '\0')
+ {
+ /* the invalid check digit suffix was found, set it */
+ if (!magic)
+ valid = false;
+ magic = true;
+ }
+ else if (!digit)
+ {
+ goto eaninvalid;
+ }
+ else
+ {
+ *aux1++ = *aux2;
+ if (++length > 14)
+ goto eantoobig;
+ }
+ aux2++;
+ }
+ *aux1 = '\0'; /* terminate the string */
+
+ /* find the current check digit value */
+ if (length == 13)
+ {
+ /* is likely EAN13, prefix with 0 to make valid GTIN14 */
+ type = EAN13;
+ buf[1] = '0'; /* prefix 0 */
+ check = buf[14] - '0';
+ }
+ else if (length == 14)
+ {
+ /* is likely GTIN14 */
+ type = GTIN14;
+ check = buf[15] - '0';
+
+ if (buf[2] - '0' == 9) /* valid range of indicator digit is 0-8
*/
+ goto indicatorinvalid;
+ }
+ else
+ goto eaninvalid;
+
+ if (type == INVALID)
+ goto eaninvalid;
+
+ /* compute check digit: */
+ for (aux1 = buf; *aux1 && *aux1 <= ' '; aux1++);
+ rcheck = checkdig(aux1, 14);
+ /* validate check digit and convert to gtin14: */
+ switch (type)
+ {
+ case GTIN14:
+ case EAN13:
+ valid = (valid && (rcheck == check || magic));
+ break;
+ default:
+ break;
+ }
+ /* always fix the check digit: */
+ aux1[13] = rcheck + '0';
+ aux1[14] = '\0';
+
+ if (!valid && !magic)
+ goto eanbadcheck;
+
+ *result = str2ean(aux1);
+ *result |= valid ? 0 : 1;
+ return true;
+
+eanbadcheck:
+ if (g_weak)
+ { /* weak input
mode is activated: */
+ /* set the "invalid-check-digit-on-input" flag */
+ *result = str2ean(aux1);
+ *result |= 1;
+ return true;
+ }
+
+ if (!errorOK)
+ {
+ if (rcheck == (unsigned) -1)
+ {
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid %s number: \"%s\"",
+ isn_names[accept],
str)));
+ }
+ else
+ {
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid check digit for %s
number: \"%s\", should be %c",
+ isn_names[accept], str,
(rcheck == 10) ? ('X') : (rcheck + '0'))));
+ }
+ }
+ return false;
+
+eaninvalid:
+ if (!errorOK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for %s number:
\"%s\"",
+ isn_names[accept], str)));
+ return false;
+
+indicatorinvalid:
+ if (!errorOK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("Indicator digit out of range for %s
number: \"%s\"",
+ isn_names[accept], str)));
+ return false;
+
+eantoobig:
+ if (!errorOK)
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("value \"%s\" is out of range for %s
type",
+ str, isn_names[accept])));
+ return false;
+}
+
/*
* string2ean --- try to parse a string into an ean13.
*
@@ -990,6 +1212,35 @@ ean13_in(PG_FUNCTION_ARGS)
PG_RETURN_EAN13(result);
}
+/* gtin14_out
+ */
+PG_FUNCTION_INFO_V1(gtin14_out);
+Datum
+gtin14_out(PG_FUNCTION_ARGS)
+{
+ ean13 val = PG_GETARG_EAN13(0);
+ char *result;
+ char buf[MAXEAN13LEN + 1];
+
+ (void) gtin14_2string(val, false, buf);
+
+ result = pstrdup(buf);
+ PG_RETURN_CSTRING(result);
+}
+
+/* gtin14_in
+ */
+PG_FUNCTION_INFO_V1(gtin14_in);
+Datum
+gtin14_in(PG_FUNCTION_ARGS)
+{
+ const char *str = PG_GETARG_CSTRING(0);
+ ean13 result;
+
+ (void) string2gtin14(str, false, &result, GTIN14);
+ PG_RETURN_EAN13(result);
+}
+
/* isbn_in
*/
PG_FUNCTION_INFO_V1(isbn_in);
diff --git a/sql/isn.sql b/sql/isn.sql
index 71577d5..ede461f 100644
--- a/sql/isn.sql
+++ b/sql/isn.sql
@@ -16,6 +16,11 @@ WHERE NOT amvalidate(oid);
--
-- test valid conversions
--
+SELECT '1234567890128'::GTIN14, -- EAN is a GTIN14
+ '123456789012?'::GTIN14, -- compute check digit for me
+ '11234567890125'::GTIN14,
+ '01234567890128'::GTIN14;
+
SELECT '9780123456786'::EAN13, -- old book
'9790123456785'::EAN13, -- music
'9791234567896'::EAN13, -- new book
@@ -100,6 +105,30 @@ SELECT 'postgresql...'::ISBN;
SELECT 9780123456786::EAN13;
SELECT 9780123456786::ISBN;
+SELECT '91234567890125'::GTIN14; -- invalid indicator
+SELECT '123456789012'::GTIN14; -- too short
+SELECT '1234567890127'::GTIN14; -- wrong checkdigit
+
+--
+-- test validity helpers
+--
+SELECT make_valid('1234567890120!'::GTIN14); -- EAN-13
+SELECT make_valid('11234567890120!'::GTIN14); -- GTIN-14
+SELECT is_valid(make_valid('1234567890120!'::GTIN14)); -- EAN-13
+SELECT is_valid(make_valid('11234567890120!'::GTIN14)); -- GTIN-14
+
+CREATE TABLE gtin_valid (gtin GTIN14 NOT NULL);
+INSERT INTO gtin_valid VALUES
+-- all invalid because of ! marking
+ ('1234567890120!'), -- invalid EAN-13
+ ('1234567890128!'), -- valid EAN-13
+ ('11234567890120!'), -- invalid GTIN-14
+ ('11234567890125!'), -- valid GTIN-14
+-- valid
+ ('1234567890128'::GTIN14), -- valid EAN-13
+ ('11234567890125'::GTIN14); -- valid GTIN-14
+SELECT gtin, is_valid(gtin) FROM gtin_valid;
+DROP TABLE gtin_valid;
--
-- test some comparisons, must yield true
--
