def find_holes(db, table_name, column_name, _range, filter=None):
"""
FIND HOLES IN A DENSE COLUMN OF INTEGERS
RETURNS A LIST OF {"min"min, "max":max} OBJECTS
"""
if not filter:
filter = {"match_all": {}}
_range = wrap(_range)
params = {
"min": _range.min,
"max": _range.max - 1,
"column_name": db.quote_column(column_name),
"table_name": db.quote_column(table_name),
"filter": esfilter2sqlwhere(db, filter)
}
min_max = db.query("""
SELECT
min({{column_name}}) `min`,
max({{column_name}})+1 `max`
FROM
{{table_name}} a
WHERE
a.{{column_name}} BETWEEN {{min}} AND {{max}} AND
{{filter}}
""", params)[0]
db.execute("SET @last={{min}}-1", {"min": _range.min})
ranges = db.query("""
SELECT
prev_rev+1 `min`,
curr_rev `max`
FROM (
SELECT
a.{{column_name}}-@last diff,
@last prev_rev,
@last:=a.{{column_name}} curr_rev
FROM
{{table_name}} a
WHERE
a.{{column_name}} BETWEEN {{min}} AND {{max}} AND
{{filter}}
ORDER BY
a.{{column_name}}
) a
WHERE
diff>1
""", params)
if ranges:
ranges.append({"min": min_max.max, "max": _range.max})
else:
if min_max.min:
ranges.append({"min": _range.min, "max": min_max.min})
ranges.append({"min": min_max.max, "max": _range.max})
else:
ranges.append(_range)
return ranges
def test_filter2where(self):
v = [856000, 856001, 856002, 856003, 856004, 856006, 856007, 856008, 856009, 856011, 856012, 856013, 856014, 856015, 856016, 856017, 856018, 856020, 856021, 856022, 856023, 856024, 856025, 856026,
856027, 856028, 856030, 856031, 856032, 856034, 856037, 856038, 856039, 856040, 856041, 856043, 856045, 856047, 856048, 856049, 856050, 856051, 856052, 856053, 856054, 856055, 856056, 856058,
856059, 856062, 856070, 856071, 856072, 856073, 856074, 856075, 856076, 856077, 856078, 856079, 856080, 856081, 856082, 856083, 856084, 856085, 856086, 856087, 856088, 856089, 856090, 856092,
856093, 856094, 856095, 856096, 856097, 856098, 856100, 856101, 856102, 856103, 856105, 856107, 856108, 856109, 856110, 856111, 856112, 856113, 856114, 856115, 856116, 856117, 856118, 856119,
856120, 856121, 856122, 856123, 856124, 856127, 856128, 856129, 856130, 856131, 856132, 856133, 856134, 856137, 856138, 856139, 856140, 856141, 856142, 856143, 856144, 856145, 856146, 856147,
856148, 856149, 856150, 856151, 856152, 856153, 856154, 856155, 856156, 856158, 856159, 856160, 856163, 856165, 856166, 856167, 856168, 856169, 856170, 856171, 856172, 856176, 856177, 856178,
856179, 856180, 856182, 856183, 856184, 856186, 856187, 856188, 856189, 856190, 856191, 856192, 856193, 856194, 856195, 856196, 856197, 856198, 856199, 856201, 856202, 856203, 856204, 856205,
856206, 856207, 856208, 856209, 856210, 856211, 856212, 856213, 856214, 856215, 856216, 856217, 856222, 856223, 856224, 856225, 856226, 856227, 856228, 856229, 856230, 856232, 856233, 856234,
856235, 856238, 856239, 856240, 856241, 856242, 856244, 856245, 856246, 856247, 856248, 856249, 856250, 856251, 856252, 856253, 856254, 856255, 856256, 856257, 856258, 856260, 856261, 856262,
856263, 856264, 856265, 856266, 856267, 856268, 856269, 856270, 856272, 856273, 856275, 856276, 856277, 856278, 856279, 856280, 856281, 856282, 856283, 856284, 856285, 856286, 856287, 856288,
856289, 856290, 856291, 856292, 856295, 856296, 856297, 856298, 856299, 856300, 856301, 856302, 856303, 856304, 856305, 856306, 856307, 856308, 856309, 856310, 856311, 856312, 856313, 856314,
856315, 856316, 856317, 856318, 856319, 856321, 856322, 856323, 856324, 856325, 856327, 856328, 856329, 856330, 856331, 856332, 856333, 856335, 856337, 856338, 856339, 856340, 856341, 856342,
856344, 856345, 856346, 856349, 856350, 856351, 856352, 856353, 856354, 856355, 856356, 856357, 856358, 856359, 856360, 856361, 856362, 856363, 856364, 856365, 856366, 856367, 856368, 856369,
856370, 856371, 856372, 856373, 856375, 856378, 856381, 856383, 856385, 856386, 856387, 856388, 856389, 856390, 856391, 856392, 856393, 856394, 856396, 856397, 856400, 856401, 856402, 856403,
856404, 856405, 856406, 856407, 856408, 856409, 856410, 856411, 856412, 856413, 856414, 856415, 856417, 856418, 856419, 856420, 856421, 856422, 856423, 856424, 856425, 856426, 856427, 856429,
856430, 856431, 856432, 856433, 856434, 856436, 856437, 856438, 856439, 856440, 856441, 856442, 856443, 856444, 856445, 856448, 856450, 856451, 856452, 856453, 856454, 856455, 856456, 856457,
856458, 856459, 856460, 856461, 856462, 856463, 856464, 856465, 856466, 856467, 856468, 856469, 856470, 856471, 856472, 856474, 856475, 856476, 856477, 856478, 856479, 856481, 856482, 856484,
856485, 856486, 856487, 856489, 856490, 856491, 856492, 856493, 856494, 856495, 856496, 856497, 856498, 856499, 856500, 856501, 856502, 856503, 856504, 856505, 856506, 856507, 856508, 856509,
856511, 856512, 856513, 856514, 856515, 856516, 856517, 856518, 856519, 856520, 856521, 856522, 856523, 856524, 856525, 856526, 856527, 856528, 856529, 856530, 856531, 856532, 856533, 856534,
856535, 856536, 856538, 856540, 856541, 856542, 856543, 856544, 856545, 856546, 856547, 856548, 856549, 856550, 856551, 856552, 856553, 856554, 856555, 856556, 856557, 856558, 856559, 856560,
856561, 856562, 856565, 856566, 856567, 856568, 856569, 856571, 856572, 856574, 856575, 856576, 856577, 856579, 856580, 856581, 856582, 856583, 856584, 856585, 856586, 856587, 856588, 856590,
856591, 856592, 856593, 856594, 856595, 856596, 856598, 856599, 856600, 856601, 856602, 856603, 856604, 856605, 856606, 856607, 856608, 856609, 856611, 856612, 856613, 856614, 856615, 856616,
856617, 856618, 856619, 856620, 856621, 856622, 856623, 856624, 856625, 856626, 856627, 856629, 856630, 856631, 856632, 856633, 856634, 856635, 856637, 856638, 856639, 856640, 856641, 856642,
856643, 856644, 856645, 856646, 856647, 856651, 856653, 856654, 856657, 856658, 856659, 856660, 856661, 856662, 856664, 856665, 856666, 856670, 856671, 856672, 856673, 856674, 856675, 856676,
856677, 856678, 856679, 856680, 856681, 856682, 856683, 856684, 856685, 856687, 856688, 856689, 856690, 856691, 856692, 856693, 856694, 856695, 856696, 856697, 856698, 856699, 856700, 856701,
856702, 856703, 856705, 856707, 856708, 856709, 856710, 856711, 856712, 856713, 856715, 856716, 856717, 856718, 856720, 856728, 856729, 856731, 856732, 856733, 856734, 856736, 856738, 856739,
856740, 856741, 856742, 856743]
where = esfilter2sqlwhere(MySQL(host="", port=1, username="", password=""), {"terms": {"bug_id": v}})
reference = """
(
`bug_id` in (856000, 856001, 856002, 856003, 856004, 856006, 856007, 856008, 856009, 856011, 856012, 856013, 856014, 856015, 856016, 856017, 856018, 856020, 856021, 856022, 856023, 856024, 856025, 856026, 856027, 856028, 856030, 856031, 856032, 856034, 856037, 856038, 856039, 856040, 856041, 856043, 856045, 856047, 856048, 856049, 856050, 856051, 856052, 856053, 856054, 856055, 856056, 856058, 856059, 856062, 856165, 856166, 856167, 856168, 856169, 856170, 856171, 856172, 856176, 856177, 856178, 856179, 856180, 856182, 856183, 856184, 856222, 856223, 856224, 856225, 856226, 856227, 856228, 856229, 856230, 856232, 856233, 856234, 856235, 856238, 856239, 856240, 856241, 856242, 856381, 856383, 856651, 856653, 856654, 856657, 856658, 856659, 856660, 856661, 856662, 856664, 856665, 856666, 856728, 856729, 856731, 856732, 856733, 856734, 856736, 856738, 856739, 856740, 856741, 856742, 856743) OR
(
(
`bug_id` BETWEEN 856070 AND 856163 OR
`bug_id` BETWEEN 856186 AND 856217 OR
`bug_id` BETWEEN 856244 AND 856378 OR
`bug_id` BETWEEN 856385 AND 856448 OR
`bug_id` BETWEEN 856450 AND 856647 OR
`bug_id` BETWEEN 856670 AND 856720
) AND
NOT (`bug_id` in (856091, 856099, 856104, 856106, 856125, 856126, 856135, 856136, 856157, 856161, 856162, 856200, 856259, 856271, 856274, 856293, 856294, 856320, 856326, 856334, 856336, 856343, 856347, 856348, 856374, 856376, 856377, 856395, 856398, 856399, 856416, 856428, 856435, 856446, 856447, 856473, 856480, 856483, 856488, 856510, 856537, 856539, 856563, 856564, 856570, 856573, 856578, 856589, 856597, 856610, 856628, 856636, 856686, 856704, 856706, 856714, 856719))
)
)"""
reference = re.sub(r"\s+", " ", reference).strip()
where = re.sub(r"\s+", " ", where).strip()
self.assertAlmostEqual(where, reference)
def find_holes(db, table_name, column_name, _range, filter=None):
"""
FIND HOLES IN A DENSE COLUMN OF INTEGERS
RETURNS A LIST OF {"min"min, "max":max} OBJECTS
"""
if not filter:
filter = {"match_all": {}}
_range = wrap(_range)
params = {
"min": _range.min,
"max": _range.max - 1,
"column_name": db.quote_column(column_name),
"table_name": db.quote_column(table_name),
"filter": esfilter2sqlwhere(db, filter)
}
min_max = db.query(
"""
SELECT
min({{column_name}}) `min`,
max({{column_name}})+1 `max`
FROM
{{table_name}} a
WHERE
a.{{column_name}} BETWEEN {{min}} AND {{max}} AND
{{filter}}
""", params)[0]
db.execute("SET @last={{min}}-1", {"min": _range.min})
ranges = db.query(
"""
SELECT
prev_rev+1 `min`,
curr_rev `max`
FROM (
SELECT
a.{{column_name}}-@last diff,
@last prev_rev,
@last:=a.{{column_name}} curr_rev
FROM
{{table_name}} a
WHERE
a.{{column_name}} BETWEEN {{min}} AND {{max}} AND
{{filter}}
ORDER BY
a.{{column_name}}
) a
WHERE
diff>1
""", params)
if ranges:
ranges.append({"min": min_max.max, "max": _range.max})
else:
if min_max.min:
ranges.append({"min": _range.min, "max": min_max.min})
ranges.append({"min": min_max.max, "max": _range.max})
else:
ranges.append(_range)
return ranges
