odoo 达梦数据库SQL适配

计算两个时间相差的天数

psql：DATE_PART('day', s.date_order::timestamp - s.create_date::timestamp)
达梦：DAYS_BETWEEN(s.date_order::timestamp, s.create_date::timestamp)

达梦没有ON CONFLICT 的适配

# 参考
# mysql：
INSERT INTO mRowUpdate(id,cnt) VALUES 
                         ('2','UPDATE') ,
                         ('4','INSERT')
 ON DUPLICATE KEY UPDATE cnt =  VALUES(cnt);
# 达梦：
MERGE INTO mRowUpdate T1  
USING (SELECT 2 as ID,'UPDATE' as CNT FROM DUAL
UNION ALL 
SELECT '4','INSERT' FROM DUAL) T2 ON (T1.id = T2.id)
WHEN NOT MATCHED THEN INSERT(id, CNT) VALUES
(T2.id, T2.CNT)
WHEN MATCHED THEN UPDATE
SET T1.CNT = T2.CNT

修复复制群组触发的翻译报错

 query = """
 INSERT INTO ir_translation (name, lang, res_id, src, type,
                             module, value, state, comments)
 VALUES {}
 ON CONFLICT (type, lang, name, res_id) WHERE type='model'
 DO UPDATE SET (name, lang, res_id, src, type, value, module, state, comments) =
     (EXCLUDED.name, EXCLUDED.lang, EXCLUDED.res_id, EXCLUDED.src, EXCLUDED.type,
      EXCLUDED.value,
      COALESCE(EXCLUDED.module, ir_translation.module),
      COALESCE(EXCLUDED.state, ir_translation.state),
      COALESCE(EXCLUDED.comments, ir_translation.comments))
 WHERE EXCLUDED.value IS NOT NULL AND EXCLUDED.value != '';
""".format(", ".join(["%s"] * len(rows_by_type['model'])))
self.env.cr.execute(query, rows_by_type['model'])
--------------------
dual_table = """
SELECT %s as name, %s as lang, %s as res_id, %s as src, %s as type, %s as module,
    %s as value, %s as state, %s as comments FROM DUAL
"""
model_params = rows_by_type['model'][0]
if len(rows_by_type['model']) > 1:
    for i in range(1, len(rows_by_type['model'])):
        dual_table += """
        UNION ALL SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s FROM DUAL
        """
        model_params.extend(rows_by_type['model'][i])
query = """
MERGE INTO ir_translation t1 USING ({}) AS t2
ON (t1.type=t2.type AND t1.lang=t2.lang AND t1.name=t2.name AND t1.res_id=t2.res_id AND t1.type = 'model')
WHEN MATCHED THEN
    UPDATE SET
        t1.value=t2.value,
        t1.module=COALESCE(t2.module, t1.module),
        t1.state=COALESCE(t2.state, t1.state),
        t1.comments=COALESCE(t2.comments, t1.comments)
    WHERE t2.value IS NOT NULL AND t2.value != ''
WHEN NOT MATCHED THEN
    INSERT (name, lang, res_id, src, type, value, module, state, comments)
    VALUES (t2.name, t2.lang, t2.res_id, t2.src, t2.type, t2.value, t2.module, t2.state, t2.comments)
""".format(dual_table)
self.env.cr.execute(query, model_params)