# HG changeset patch
# User Julien Cristau <julien.cristau@logilab.fr>
# Date 1431941767 -7200
# Node ID 2d3834df64ab1250b764966fca0fd69a2dd7ec6c
# Parent  b2d2099dfae8e2ffb1869f19bfbba03deca9c863
[schema2sql] insert some constraints into the backend

BoundaryConstraint, IntervalBoundConstraint and
StaticVocabularyConstraint can be implemented in SQL with CHECK
constraints.

Next steps, not part of this changeset:
- migrations
- removing no longer necessary pre-insertion work on the python side
- translating backend exception due to constraint violation into
  ValidationError

Note: this means that these constraints can no longer be ignored by
disabling integrity hooks.

Related to #5154406

diff -r b2d2099dfae8 -r 2d3834df64ab server/schema2sql.py
--- a/server/schema2sql.py	Tue Jun 23 10:05:34 2015 +0200
+++ b/server/schema2sql.py	Mon May 18 11:36:07 2015 +0200
@@ -21,9 +21,10 @@
 
 from hashlib import md5
 
+from six import string_types
 from six.moves import range
 
-from yams.constraints import SizeConstraint, UniqueConstraint
+from yams.constraints import SizeConstraint, UniqueConstraint, Attribute
 
 # default are usually not handled at the sql level. If you want them, set
 # SET_DEFAULT to True
@@ -122,6 +123,15 @@
             w(' %s%s %s' % (prefix, rschema.type, sqltype))
         else:
             w(' %s%s %s,' % (prefix, rschema.type, sqltype))
+    for rschema, aschema in attrs:
+        if aschema is None:  # inline relation
+            continue
+        attr = rschema.type
+        rdef = rschema.rdef(eschema.type, aschema.type)
+        for constraint in rdef.constraints:
+            cstrname, check = check_constraint(eschema, aschema, attr, constraint, prefix=prefix)
+            if cstrname is not None:
+                w(', CONSTRAINT %s CHECK(%s)' % (cstrname, check))
     w(');')
     # create indexes
     for i in range(len(attrs)):
@@ -136,6 +146,40 @@
     w('')
     return '\n'.join(output)
 
+def check_constraint(eschema, aschema, attr, constraint, prefix=''):
+    # XXX should find a better name
+    cstrname = 'cstr' + md5(eschema.type + attr + constraint.type() +
+                            (constraint.serialize() or '')).hexdigest()
+    if constraint.type() == 'BoundaryConstraint':
+        if isinstance(constraint.boundary, Attribute):
+            value = prefix + constraint.boundary.attr
+        else:
+            value = constraint.boundary
+        return cstrname, '%s%s %s %s' % (prefix, attr, constraint.operator, value)
+    elif constraint.type() == 'IntervalBoundConstraint':
+        condition = []
+        if constraint.minvalue is not None:
+            if isinstance(constraint.minvalue, Attribute):
+                value = prefix + constraint.minvalue.attr
+            else:
+                value = constraint.minvalue
+            condition.append('%s%s >= %s' % (prefix, attr, value))
+        if constraint.maxvalue is not None:
+            if isinstance(constraint.maxvalue, Attribute):
+                value = prefix + constraint.maxvalue.attr
+            else:
+                value = constraint.maxvalue
+            condition.append('%s%s <= %s' % (prefix, attr, value))
+        return cstrname, ' AND '.join(condition)
+    elif constraint.type() == 'StaticVocabularyConstraint':
+        sample = next(iter(constraint.vocabulary()))
+        if not isinstance(sample, string_types):
+            values = ', '.join(str(word) for word in constraint.vocabulary())
+        else:
+            # XXX better quoting?
+            values = ', '.join("'%s'" % word.replace("'", "''") for word in constraint.vocabulary())
+        return cstrname, '%s%s IN (%s)' % (prefix, attr, values)
+    return None, None
 
 def aschema2sql(dbhelper, eschema, rschema, aschema, creating=True, indent=''):
     """write an attribute schema as SQL statements to stdout"""
diff -r b2d2099dfae8 -r 2d3834df64ab server/test/data-schema2sql/schema/schema.py
--- a/server/test/data-schema2sql/schema/schema.py	Tue Jun 23 10:05:34 2015 +0200
+++ b/server/test/data-schema2sql/schema/schema.py	Mon May 18 11:36:07 2015 +0200
@@ -17,6 +17,7 @@
 # with yams. If not, see <http://www.gnu.org/licenses/>.
 from yams.buildobjs import (EntityType, RelationDefinition, RelationType,
                             SubjectRelation, String, Int, Float, Date, Boolean)
+from yams.constraints import Attribute, BoundaryConstraint
 
 class Affaire(EntityType):
     sujet = String(maxsize=128)
@@ -64,7 +65,7 @@
     nom  = String(maxsize=64, fulltextindexed=True)
     web = String(maxsize=128)
     tel  = Int()
-    fax  = Int()
+    fax  = Int(constraints=[BoundaryConstraint('<=', Attribute('tel'))])
     rncs = String(maxsize=32)
     ad1  = String(maxsize=128)
     ad2  = String(maxsize=128)
diff -r b2d2099dfae8 -r 2d3834df64ab server/test/unittest_schema2sql.py
--- a/server/test/unittest_schema2sql.py	Tue Jun 23 10:05:34 2015 +0200
+++ b/server/test/unittest_schema2sql.py	Mon May 18 11:36:07 2015 +0200
@@ -96,6 +96,7 @@
  datenaiss date,
  test boolean,
  salary float
+, CONSTRAINT cstr41fe7db9ce1d5be95de2477e26590386 CHECK(promo IN ('bon', 'pasbon'))
 );
 CREATE UNIQUE INDEX unique_e6c2d219772dbf1715597f7d9a6b3892 ON Person(nom,prenom);
 
@@ -113,6 +114,7 @@
  datenaiss date,
  test boolean,
  salary float
+, CONSTRAINT cstrc8556fcc665865217761cdbcd220cae0 CHECK(promo IN ('bon', 'pasbon'))
 );
 CREATE UNIQUE INDEX unique_98da0f9de8588baa8966f0b1a6f850a3 ON Salaried(nom,prenom);
 
@@ -127,6 +129,7 @@
  ad3 varchar(128),
  cp varchar(12),
  ville varchar(32)
+, CONSTRAINT cstrc51dd462e9f6115506a0fe468d4c8114 CHECK(fax <= tel)
 );
 
 CREATE TABLE State(
@@ -155,6 +158,8 @@
  author_email varchar(100) NOT NULL,
  mailinglist varchar(100),
  debian_handler varchar(6)
+, CONSTRAINT cstr70f766f834557c715815d76f0a0db956 CHECK(license IN ('GPL', 'ZPL'))
+, CONSTRAINT cstr831a117424d0007ae0278cc15f344f5e CHECK(debian_handler IN ('machin', 'bidule'))
 );