Spaces:
Paused
Paused
File size: 9,172 Bytes
b759b87 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 |
"""
Copyright (c) 2020, salesforce.com, inc.
All rights reserved.
SPDX-License-Identifier: BSD-3-Clause
For full license text, see the LICENSE file in the repo root or https://opensource.org/licenses/BSD-3-Clause
Encode DB content.
"""
import difflib
from typing import List, Optional, Tuple
from rapidfuzz import fuzz
import sqlite3
import functools
# fmt: off
_stopwords = {'who', 'ourselves', 'down', 'only', 'were', 'him', 'at', "weren't", 'has', 'few', "it's", 'm', 'again',
'd', 'haven', 'been', 'other', 'we', 'an', 'own', 'doing', 'ma', 'hers', 'all', "haven't", 'in', 'but',
"shouldn't", 'does', 'out', 'aren', 'you', "you'd", 'himself', "isn't", 'most', 'y', 'below', 'is',
"wasn't", 'hasn', 'them', 'wouldn', 'against', 'this', 'about', 'there', 'don', "that'll", 'a', 'being',
'with', 'your', 'theirs', 'its', 'any', 'why', 'now', 'during', 'weren', 'if', 'should', 'those', 'be',
'they', 'o', 't', 'of', 'or', 'me', 'i', 'some', 'her', 'do', 'will', 'yours', 'for', 'mightn', 'nor',
'needn', 'the', 'until', "couldn't", 'he', 'which', 'yourself', 'to', "needn't", "you're", 'because',
'their', 'where', 'it', "didn't", 've', 'whom', "should've", 'can', "shan't", 'on', 'had', 'have',
'myself', 'am', "don't", 'under', 'was', "won't", 'these', 'so', 'as', 'after', 'above', 'each', 'ours',
'hadn', 'having', 'wasn', 's', 'doesn', "hadn't", 'than', 'by', 'that', 'both', 'herself', 'his',
"wouldn't", 'into', "doesn't", 'before', 'my', 'won', 'more', 'are', 'through', 'same', 'how', 'what',
'over', 'll', 'yourselves', 'up', 'mustn', "mustn't", "she's", 're', 'such', 'didn', "you'll", 'shan',
'when', "you've", 'themselves', "mightn't", 'she', 'from', 'isn', 'ain', 'between', 'once', 'here',
'shouldn', 'our', 'and', 'not', 'too', 'very', 'further', 'while', 'off', 'couldn', "hasn't", 'itself',
'then', 'did', 'just', "aren't"}
# fmt: on
_commonwords = {"no", "yes", "many"}
def is_number(s: str) -> bool:
try:
float(s.replace(",", ""))
return True
except:
return False
def is_stopword(s: str) -> bool:
return s.strip() in _stopwords
def is_commonword(s: str) -> bool:
return s.strip() in _commonwords
def is_common_db_term(s: str) -> bool:
return s.strip() in ["id"]
class Match(object):
def __init__(self, start: int, size: int) -> None:
self.start = start
self.size = size
def is_span_separator(c: str) -> bool:
return c in "'\"()`,.?! "
def split(s: str) -> List[str]:
return [c.lower() for c in s.strip()]
def prefix_match(s1: str, s2: str) -> bool:
i, j = 0, 0
for i in range(len(s1)):
if not is_span_separator(s1[i]):
break
for j in range(len(s2)):
if not is_span_separator(s2[j]):
break
if i < len(s1) and j < len(s2):
return s1[i] == s2[j]
elif i >= len(s1) and j >= len(s2):
return True
else:
return False
def get_effective_match_source(s: str, start: int, end: int) -> Match:
_start = -1
for i in range(start, start - 2, -1):
if i < 0:
_start = i + 1
break
if is_span_separator(s[i]):
_start = i
break
if _start < 0:
return None
_end = -1
for i in range(end - 1, end + 3):
if i >= len(s):
_end = i - 1
break
if is_span_separator(s[i]):
_end = i
break
if _end < 0:
return None
while _start < len(s) and is_span_separator(s[_start]):
_start += 1
while _end >= 0 and is_span_separator(s[_end]):
_end -= 1
return Match(_start, _end - _start + 1)
def get_matched_entries(
s: str, field_values: List[str], m_theta: float = 0.85, s_theta: float = 0.85
) -> Optional[List[Tuple[str, Tuple[str, str, float, float, int]]]]:
if not field_values:
return None
if isinstance(s, str):
n_grams = split(s)
else:
n_grams = s
matched = dict()
for field_value in field_values:
if not isinstance(field_value, str):
continue
fv_tokens = split(field_value)
sm = difflib.SequenceMatcher(None, n_grams, fv_tokens)
match = sm.find_longest_match(0, len(n_grams), 0, len(fv_tokens))
if match.size > 0:
source_match = get_effective_match_source(
n_grams, match.a, match.a + match.size
)
if source_match: # and source_match.size > 1
match_str = field_value[match.b : match.b + match.size]
source_match_str = s[
source_match.start : source_match.start + source_match.size
]
c_match_str = match_str.lower().strip()
c_source_match_str = source_match_str.lower().strip()
c_field_value = field_value.lower().strip()
if c_match_str and not is_common_db_term(c_match_str): # and not is_number(c_match_str)
if (
is_stopword(c_match_str)
or is_stopword(c_source_match_str)
or is_stopword(c_field_value)
):
continue
if c_source_match_str.endswith(c_match_str + "'s"):
match_score = 1.0
else:
if prefix_match(c_field_value, c_source_match_str):
match_score = fuzz.ratio(c_field_value, c_source_match_str) / 100
else:
match_score = 0
if (
is_commonword(c_match_str)
or is_commonword(c_source_match_str)
or is_commonword(c_field_value)
) and match_score < 1:
continue
s_match_score = match_score
if match_score >= m_theta and s_match_score >= s_theta:
if field_value.isupper() and match_score * s_match_score < 1:
continue
matched[match_str] = (
field_value,
source_match_str,
match_score,
s_match_score,
match.size,
)
if not matched:
return None
else:
return sorted(
matched.items(),
key=lambda x: (1e16 * x[1][2] + 1e8 * x[1][3] + x[1][4]),
reverse=True,
)
@functools.lru_cache(maxsize=1000, typed=False)
def get_column_picklist(table_name: str, column_name: str, db_path: str) -> list:
fetch_sql = "SELECT DISTINCT `{}` FROM `{}`".format(column_name, table_name)
try:
conn = sqlite3.connect(db_path)
conn.text_factory = bytes
c = conn.cursor()
c.execute(fetch_sql)
picklist = set()
for x in c.fetchall():
if isinstance(x[0], str):
picklist.add(x[0].encode("utf-8"))
elif isinstance(x[0], bytes):
try:
picklist.add(x[0].decode("utf-8"))
except UnicodeDecodeError:
picklist.add(x[0].decode("latin-1"))
else:
picklist.add(x[0])
picklist = list(picklist)
except Exception as e:
picklist = []
finally:
conn.close()
return picklist
def get_database_matches(
question: str,
table_name: str,
column_name: str,
db_path: str,
top_k_matches: int = 2,
match_threshold: float = 0.85,
) -> List[str]:
picklist = get_column_picklist(
table_name=table_name, column_name=column_name, db_path=db_path
)
# only maintain data in ``str'' type
picklist = [ele.strip() for ele in picklist if isinstance(ele, str)]
# picklist is unordered, we sort it to ensure the reproduction stability
picklist = sorted(picklist)
matches = []
if picklist and isinstance(picklist[0], str):
matched_entries = get_matched_entries(
s=question,
field_values=picklist,
m_theta=match_threshold,
s_theta=match_threshold,
)
if matched_entries:
num_values_inserted = 0
for _match_str, (
field_value,
_s_match_str,
match_score,
s_match_score,
_match_size,
) in matched_entries:
if "name" in column_name and match_score * s_match_score < 1:
continue
if table_name != "sqlite_sequence": # Spider database artifact
matches.append(field_value.strip())
num_values_inserted += 1
if num_values_inserted >= top_k_matches:
break
return matches |