def set_default(db, migrator, table_name, column_name, field):
default = field.default
if callable(default): default = default()
migration = ( migrator.make_context()
.literal('UPDATE ').sql(pw.Entity(table_name))
.literal(' SET ').sql(pw.Expression(pw.Entity(column_name), pw.OP.EQ, field.db_value(default), flat=True))
.literal(' WHERE ').sql(pw.Expression(pw.Entity(column_name), pw.OP.IS, pw.SQL('NULL'), flat=True))
)
return extract_query_from_migration(migration)
def validate_unique_indexes(self):
for names, unique in self.model._meta.indexes:
if not unique:
continue
where = []
for name in names:
field = getattr(self.model, name)
value = None
if name in self.data:
value = self.data.get(name)
else:
if self.instance:
value = getattr(self.instance, name)
if value and name not in self.model._meta.case_ignoring:
where.append(field == value)
elif value and name in self.model._meta.case_ignoring:
where.append(peewee.Expression(field, peewee.OP.ILIKE, value))
if where != []:
qs = self.model.select().where(*where)
else:
qs = self.model.select()
if self.instance:
qs = qs.where(self.model.pk != self.instance.pk)
if qs.exists():
self.foundDuplicate = qs.get()
msg = 'Duplicate entries: {}'.format(', '.join(names))
for name in names:
self.error(name, msg)
def as_expr(self, arg):
if isinstance(self.field, ArrayField):
return self.field.contains(*arg)
elif isinstance(self.field, JSONField):
return self.field.contains(arg)
# or for substring
return peewee.Expression(self.field, peewee.OP.LIKE,
'%{0}%'.format(arg))
def get_queryset(self):
qs = super().get_queryset()
version = request.args.get('version')
resource = request.args.get('resource')
if version is not None and resource is not None:
qs = self.filter_versions(qs)
dep = request.args.get('dep')
if dep:
qs = qs.where(
peewee.Expression(versioning.Anomaly.insee, peewee.OP.LIKE,
dep + '%'))
insee = request.args.get('insee')
if insee:
qs = qs.where(versioning.Anomaly.insee == insee)
return qs
def cast(self, as_type):
return peewee.Expression(self, '::', peewee.SQL(as_type))
def BeerPerCharacter(character, amount):
"""Helper function to generate the SQL expression for the total amount
of beer drunk."""
return peewee.Expression(character.glass, '*', amount)
def contains(self, geom):
return peewee.Expression(self, peewee.OP.BBOXCONTAINS, geom)
def search(self, **kwargs):
ponctuation = '[\.\(\)\[\]\"\'\-,;:\/]'
articles = '(^| )((LA|L|LE|LES|DU|DE|DES|D|ET|A|AU) )*'
if kwargs['type'] is None or kwargs['search'] is None:
raise ValueError('None value for search.')
if kwargs['type'] == 'strict':
return peewee.Expression(self, peewee.OP.EQ, kwargs['search'])
elif kwargs['type'] == 'case':
return peewee.Expression(peewee.fn.unaccent(self), peewee.OP.ILIKE,
peewee.fn.unaccent(kwargs['search']))
elif kwargs['type'] == 'ponctuation':
return peewee.Expression(
peewee.fn.regexp_replace(peewee.fn.unaccent(self), ponctuation,
' ', 'g'), peewee.OP.ILIKE,
peewee.fn.regexp_replace(peewee.fn.unaccent(kwargs['search']),
ponctuation, ' ', 'g'))
elif kwargs['type'] == 'abbrev':
import csv
abbrev = []
with open('../../abbrev_type_voie.csv', newline='') as csvfile:
csv = csv.reader(csvfile, delimiter=';')
for row in csv:
if re.match(r"^{} ".format(row[1]),
re.sub(ponctuation, ' ', kwargs['search'].upper())) is not None \
or re.match(r"^{} ".format(row[0]),
re.sub(ponctuation, ' ', kwargs['search'].upper())) is not None:
abbrev = row
break
if not abbrev:
return peewee.Expression(
peewee.fn.regexp_replace(peewee.fn.unaccent(self),
ponctuation, ' ', 'g'),
peewee.OP.ILIKE,
peewee.fn.regexp_replace(
peewee.fn.unaccent(kwargs['search']), ponctuation,
' ', 'g'))
return peewee.Expression(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.unaccent(peewee.fn.upper(self)),
ponctuation, ' ', 'g'), "^{} ".format(abbrev[1]),
"{} ".format(abbrev[0]), 'g'), peewee.OP.REGEXP,
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.unaccent(
peewee.fn.upper(kwargs['search'])),
ponctuation, ' ', 'g'), "^{} ".format(abbrev[1]),
"{} ".format(abbrev[0]), 'g'))
elif kwargs['type'] == 'libelle':
import csv
abbrev = '^('
with open('../../abbrev_type_voie.csv', newline='') as csvfile:
csv = csv.reader(csvfile, delimiter=';')
for row in csv:
abbrev = '{}|{}|{}'.format(abbrev, row[0], row[1])
abbrev = '{})( |$)'.format(abbrev)
return peewee.Expression(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.unaccent(peewee.fn.upper(self)),
ponctuation, ' ', 'g'), abbrev, "", 'g'),
peewee.OP.ILIKE,
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.unaccent(
peewee.fn.upper(kwargs['search'])),
ponctuation, ' ', 'g'), abbrev, "", 'g'))
elif kwargs['type'] == 'direct':
import csv
abbrev = '^('
with open('../../abbrev_type_voie.csv', newline='') as csvfile:
csv = csv.reader(csvfile, delimiter=';')
for row in csv:
abbrev = '{}|{}|{}'.format(abbrev, row[0], row[1])
abbrev = '{})( |$)'.format(abbrev)
return peewee.Expression(
peewee.fn.trim(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.unaccent(peewee.fn.upper(self)),
ponctuation, ' ', 'g'), abbrev, "", 'g'),
articles, ' ', 'g')), peewee.OP.ILIKE,
peewee.fn.trim(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.unaccent(
peewee.fn.upper(kwargs['search'])),
ponctuation, ' ', 'g'), abbrev, "", 'g'),
articles, ' ', 'g')))
elif kwargs['type'] == 'approx':
import csv
abbrev = '^('
with open('../../abbrev_type_voie.csv', newline='') as csvfile:
csv = csv.reader(csvfile, delimiter=';')
for row in csv:
abbrev = '{}|{}|{}'.format(abbrev, row[0], row[1])
abbrev = '{})( |$)'.format(abbrev)
return peewee.Expression(
peewee.fn.levenshtein(
peewee.fn.trim(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.unaccent(
peewee.fn.upper(self)),
ponctuation, ' ', 'g'), abbrev, "",
'g'), articles, ' ', 'g')),
peewee.fn.trim(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.regexp_replace(
peewee.fn.unaccent(
peewee.fn.upper(kwargs['search'])),
ponctuation, ' ', 'g'), abbrev, "",
'g'), articles, ' ', 'g'))), peewee.OP.LTE,
2)
else:
raise ValueError('Search type {} is unknown'.format(
kwargs['type']))
def contains(self, dt):
return peewee.Expression(self, peewee.OP.ACONTAINS, dt)
def as_expr(self, arg):
return peewee.Expression(self.field, peewee.OP.ILIKE,
'%{0}'.format(arg))
