Revision 5367
Added by Aaron Marcuse-Kubitza about 12 years ago
| lib/sql_gen.py | ||
|---|---|---|
| 13 | 13 |
import iters |
| 14 | 14 |
import lists |
| 15 | 15 |
import objects |
| 16 |
import regexp |
|
| 16 | 17 |
import strings |
| 17 | 18 |
import util |
| 18 | 19 |
|
| ... | ... | |
| 1089 | 1090 |
''' |
| 1090 | 1091 |
try: return ensure_not_null(db, value) |
| 1091 | 1092 |
except ensure_not_null_excs: return value |
| 1093 |
|
|
| 1094 |
##### Expression transforming |
|
| 1095 |
|
|
| 1096 |
true_expr = 'true' |
|
| 1097 |
false_expr = 'false' |
|
| 1098 |
|
|
| 1099 |
true_re = true_expr |
|
| 1100 |
false_re = false_expr |
|
| 1101 |
bool_re = r'(?:'+true_re+r'|'+false_re+r')' |
|
| 1102 |
atom_re = r'(?:'+bool_re+r'|\([^()]*\)'+r')' |
|
| 1103 |
|
|
| 1104 |
def logic_op_re(op, value_re, expr_re=''): |
|
| 1105 |
op_re = ' '+op+' ' |
|
| 1106 |
return '(?:'+expr_re+op_re+value_re+'|'+value_re+op_re+expr_re+')' |
|
| 1107 |
|
|
| 1108 |
and_false_re = logic_op_re('AND', false_re, atom_re)
|
|
| 1109 |
and_true_re = logic_op_re('AND', true_re)
|
|
| 1110 |
or_re = logic_op_re('OR', bool_re)
|
|
| 1111 |
or_and_true_re = '(?:'+and_true_re+'|'+or_re+')' |
|
| 1112 |
|
|
| 1113 |
def simplify_parens(expr): |
|
| 1114 |
return regexp.sub_nested(r'\(('+atom_re+')\)', r'\1', expr)
|
|
| 1115 |
|
|
| 1116 |
def simplify_recursive(sub_func, expr): |
|
| 1117 |
''' |
|
| 1118 |
@param sub_func See regexp.sub_recursive() sub_func param |
|
| 1119 |
''' |
|
| 1120 |
return simplify_parens(regexp.sub_recursive( |
|
| 1121 |
lambda s: sub_func(simplify_parens(s)), expr)) |
|
| 1122 |
|
|
| 1123 |
def simplify_expr(expr): |
|
| 1124 |
def simplify_logic_ops(expr): |
|
| 1125 |
total_n = 0 |
|
| 1126 |
expr, n = re.subn(and_false_re, false_expr, expr) |
|
| 1127 |
total_n += n |
|
| 1128 |
expr, n = re.subn(or_and_true_re, r'', expr) |
|
| 1129 |
total_n += n |
|
| 1130 |
return expr, total_n |
|
| 1131 |
|
|
| 1132 |
expr = expr.replace('(NULL IS NULL)', true_expr)
|
|
| 1133 |
expr = expr.replace('(NULL IS NOT NULL)', false_expr)
|
|
| 1134 |
expr = simplify_recursive(simplify_logic_ops, expr) |
|
| 1135 |
return expr |
|
| 1136 |
|
|
| 1137 |
name_re = r'(?:\w+|(?:"[^"]*")+)' |
|
| 1138 |
|
|
| 1139 |
def parse_expr_col(str_): |
|
| 1140 |
match = re.match(r'^\('+name_re+r'\(('+name_re+r').*\)\)$', str_)
|
|
| 1141 |
if match: str_ = match.group(1) |
|
| 1142 |
return unesc_name(str_) |
|
| 1143 |
|
|
| 1144 |
def map_expr(db, expr, mapping, in_cols_found=None): |
|
| 1145 |
'''Replaces output columns with input columns in an expression. |
|
| 1146 |
@param in_cols_found If set, will be filled in with the expr's (input) cols |
|
| 1147 |
''' |
|
| 1148 |
for out, in_ in mapping.iteritems(): |
|
| 1149 |
orig_expr = expr |
|
| 1150 |
out = to_name_only_col(out) |
|
| 1151 |
in_str = to_name_only_col(remove_col_rename(in_)).to_str(db) |
|
| 1152 |
|
|
| 1153 |
# Replace out both with and without quotes |
|
| 1154 |
expr = expr.replace(out.to_str(db), in_str) |
|
| 1155 |
expr = re.sub(r'(?<!["\'\.\[])\b'+out.name+r'\b(?!["\'\.=\]])', in_str, |
|
| 1156 |
expr) |
|
| 1157 |
|
|
| 1158 |
if in_cols_found != None and expr != orig_expr: # replaced something |
|
| 1159 |
in_cols_found.append(in_) |
|
| 1160 |
|
|
| 1161 |
return simplify_expr(expr) |
|
| lib/sql.py | ||
|---|---|---|
| 12 | 12 |
import profiling |
| 13 | 13 |
from Proxy import Proxy |
| 14 | 14 |
import rand |
| 15 |
import regexp |
|
| 16 | 15 |
import sql_gen |
| 17 | 16 |
import strings |
| 18 | 17 |
import util |
| ... | ... | |
| 1019 | 1018 |
|
| 1020 | 1019 |
##### Database structure introspection |
| 1021 | 1020 |
|
| 1022 |
#### Expressions |
|
| 1023 |
|
|
| 1024 |
true_expr = 'true' |
|
| 1025 |
false_expr = 'false' |
|
| 1026 |
|
|
| 1027 |
true_re = true_expr |
|
| 1028 |
false_re = false_expr |
|
| 1029 |
bool_re = r'(?:'+true_re+r'|'+false_re+r')' |
|
| 1030 |
atom_re = r'(?:'+bool_re+r'|\([^()]*\)'+r')' |
|
| 1031 |
|
|
| 1032 |
def logic_op_re(op, value_re, expr_re=''): |
|
| 1033 |
op_re = ' '+op+' ' |
|
| 1034 |
return '(?:'+expr_re+op_re+value_re+'|'+value_re+op_re+expr_re+')' |
|
| 1035 |
|
|
| 1036 |
and_false_re = logic_op_re('AND', false_re, atom_re)
|
|
| 1037 |
and_true_re = logic_op_re('AND', true_re)
|
|
| 1038 |
or_re = logic_op_re('OR', bool_re)
|
|
| 1039 |
or_and_true_re = '(?:'+and_true_re+'|'+or_re+')' |
|
| 1040 |
|
|
| 1041 |
def simplify_parens(expr): |
|
| 1042 |
return regexp.sub_nested(r'\(('+atom_re+')\)', r'\1', expr)
|
|
| 1043 |
|
|
| 1044 |
def simplify_recursive(sub_func, expr): |
|
| 1045 |
''' |
|
| 1046 |
@param sub_func See regexp.sub_recursive() sub_func param |
|
| 1047 |
''' |
|
| 1048 |
return simplify_parens(regexp.sub_recursive( |
|
| 1049 |
lambda s: sub_func(simplify_parens(s)), expr)) |
|
| 1050 |
|
|
| 1051 |
def simplify_expr(expr): |
|
| 1052 |
def simplify_logic_ops(expr): |
|
| 1053 |
total_n = 0 |
|
| 1054 |
expr, n = re.subn(and_false_re, false_expr, expr) |
|
| 1055 |
total_n += n |
|
| 1056 |
expr, n = re.subn(or_and_true_re, r'', expr) |
|
| 1057 |
total_n += n |
|
| 1058 |
return expr, total_n |
|
| 1059 |
|
|
| 1060 |
expr = expr.replace('(NULL IS NULL)', true_expr)
|
|
| 1061 |
expr = expr.replace('(NULL IS NOT NULL)', false_expr)
|
|
| 1062 |
expr = simplify_recursive(simplify_logic_ops, expr) |
|
| 1063 |
return expr |
|
| 1064 |
|
|
| 1065 |
name_re = r'(?:\w+|(?:"[^"]*")+)' |
|
| 1066 |
|
|
| 1067 |
def parse_expr_col(str_): |
|
| 1068 |
match = re.match(r'^\('+name_re+r'\(('+name_re+r').*\)\)$', str_)
|
|
| 1069 |
if match: str_ = match.group(1) |
|
| 1070 |
return sql_gen.unesc_name(str_) |
|
| 1071 |
|
|
| 1072 |
def map_expr(db, expr, mapping, in_cols_found=None): |
|
| 1073 |
'''Replaces output columns with input columns in an expression. |
|
| 1074 |
@param in_cols_found If set, will be filled in with the expr's (input) cols |
|
| 1075 |
''' |
|
| 1076 |
for out, in_ in mapping.iteritems(): |
|
| 1077 |
orig_expr = expr |
|
| 1078 |
out = sql_gen.to_name_only_col(out) |
|
| 1079 |
in_str = sql_gen.to_name_only_col(sql_gen.remove_col_rename(in_) |
|
| 1080 |
).to_str(db) |
|
| 1081 |
|
|
| 1082 |
# Replace out both with and without quotes |
|
| 1083 |
expr = expr.replace(out.to_str(db), in_str) |
|
| 1084 |
expr = re.sub(r'(?<!["\'\.\[])\b'+out.name+r'\b(?!["\'\.=\]])', in_str, |
|
| 1085 |
expr) |
|
| 1086 |
|
|
| 1087 |
if in_cols_found != None and expr != orig_expr: # replaced something |
|
| 1088 |
in_cols_found.append(in_) |
|
| 1089 |
|
|
| 1090 |
return simplify_expr(expr) |
|
| 1091 |
|
|
| 1092 | 1021 |
#### Tables |
| 1093 | 1022 |
|
| 1094 | 1023 |
def tables(db, schema_like='public', table_like='%', exact=False, |
| ... | ... | |
| 1175 | 1104 |
module = util.root_module(db.db) |
| 1176 | 1105 |
if module == 'psycopg2': |
| 1177 | 1106 |
qual_index = sql_gen.Literal(index.to_str(db)) |
| 1178 |
return map(parse_expr_col, values(run_query(db, '''\ |
|
| 1107 |
return map(sql_gen.parse_expr_col, values(run_query(db, '''\
|
|
| 1179 | 1108 |
SELECT pg_get_indexdef(indexrelid, generate_series(1, indnatts), true) |
| 1180 | 1109 |
FROM pg_index |
| 1181 | 1110 |
WHERE indexrelid = '''+qual_index.to_str(db)+'''::regclass |
| lib/sql_io.py | ||
|---|---|---|
| 472 | 472 |
sql.table_col_names(db, out_table))) |
| 473 | 473 |
|
| 474 | 474 |
in_cols = [] |
| 475 |
cond = sql.map_expr(db, cond, mapping, in_cols) |
|
| 476 |
cond = sql.map_expr(db, cond, out_table_cols) |
|
| 475 |
cond = sql_gen.map_expr(db, cond, mapping, in_cols)
|
|
| 476 |
cond = sql_gen.map_expr(db, cond, out_table_cols)
|
|
| 477 | 477 |
|
| 478 | 478 |
track_data_error(db, errors_table_, sql_gen.cols_srcs(in_cols), |
| 479 | 479 |
None, e.cause.pgcode, |
Also available in: Unified diff
Moved expression transforming functions from sql.py to sql_gen.py because they do not manipulate an actual database and merely generate SQL