def checkInsert(dbName, tableName, columns, values):
lstErr.clear()
table = S.extractTable(dbName, tableName)
if table == 0:
syntaxPostgreErrors.append("Error: 42000: La base de datos " +
str(dbName) + " no existe")
return ["Error: No existe la base de datos"]
elif table == 1:
syntaxPostgreErrors.append("Error: 42P01: La tabla " +
str(tableName) + " no existe")
return ["Error: No existe la tabla"]
if columns != None:
if len(columns) != len(values):
syntaxPostgreErrors.append(
"Error: 42611: definicion en numero de columnas invalida ")
return ["Columnas fuera de los limites"]
else:
if len(values) != len(table["columns"]):
syntaxPostgreErrors.append(
"Error: 42611: definicion en numero de columnas invalida ")
return ["Columnas fuera de los limites"]
values = S.getValues(table, columns, values)
if not values:
syntaxPostgreErrors.append("Error: 42P10: Columnas no identificadas ")
return ["Error: Columnas no identificadas"]
pks = []
indexCol = 0
for col in table["columns"]:
x = Type.get(col["type"])
value = values[indexCol]
if not isinstance(value, Primitive):
value = Primitive(x, value, 0, 0, 0)
values[indexCol] = value
if col["PK"]:
pks.append(indexCol)
indexCol += 1
# Validar la llave primaria
if pks:
validatePrimary(dbName, tableName, values, pks)
indexCol = 0
for value in values:
column = table["columns"][indexCol]
if value.value != None and value.type != TYPE.NULL:
value.value = convertDateTime(value.value, column["type"])
if column["Unique"]:
validateUnique(dbName, tableName, value.value, indexCol)
if column["FK"] != None:
validateForeign(dbName, column["FK"], value.value)
if column["Constraint"] != None:
validateConstraint(column["Constraint"], values, dbName,
tableName, column["type"])
select(column, value)
else:
value.value = None
validateNotNull(column["NN"], column["name"])
indexCol += 1
return [listError(), values]
def execute(self, environment):
col = self.colData.execute(environment)
df = self.subquery.execute(environment)[0]
# TODO: Falta agregar la verificacion de types
if len(list(df.columns)) != 1:
expression.list_errors.append(
"Error: XX000: Error interno (Exist Relational Operation)" +
"\n En la linea: " + str(self.row))
value = col.value.isin(df.iloc[:, 0])
if self.optNot == "NOT":
value = ~value
return Primitive(TYPE.BOOLEAN, value, self.temp, self.row, self.column)
def checkValue(dbName, tableName):
lstErr.clear()
table = S.extractTable(dbName, tableName)
if table == 0 and table == 1:
return
for col in table["columns"]:
if col["Default"] != None:
if col["Default"][1] != 9:
value = Primitive(TypeNumber.get(col["Default"][1]),
col["Default"][0], 0, 0, 0)
select(col, value)
if len(lstErr) != 0:
col["Default"] = None
else:
col["Default"] = None
return listError()
def execute(self, environment):
try:
df1 = environment.dataFrame.copy()
names = {}
for n in list(df1.columns):
names[n] = n.split(".")[1]
df1.rename(columns=names, inplace=True)
df2 = self.subquery.execute(environment)[0]
y = df1.columns.intersection(df2.columns)
lst = list(y)
if len(lst) < 1:
expression.list_errors.append(
"Error: 42P10: Referencia de columnas invalidas EXIST" +
"\n En la linea: " + str(self.row))
value = df1[lst].apply(tuple, 1).isin(df2[lst].apply(tuple, 1))
return Primitive(TYPE.BOOLEAN, value, self.temp, self.row,
self.column)
except:
expression.list_errors.append(
"Error: XX000: Error interno (Exist Relational Operation)" +
"\n En la linea: " + str(self.row))
def execute(self, environment):
try:
if self.val == "CURRENT_DATE":
value = datetime.now().strftime("%Y/%m/%d")
elif self.val == "CURRENT_TIME":
value = datetime.now().strftime("%H:%M:%S")
elif self.val == "TIMESTAMP":
if self.optStr == "now":
value = datetime.now().strftime("%Y/%m/%d %H:%M:%S")
else:
value = self.optStr
else:
# ERROR
expression.list_errors.append(
"Error: 22007: Formato de fecha invalido " + str(self.str))
value = self.val
return Primitive(TYPE.STRING, value, self.temp, self.row,
self.column)
except:
expression.list_errors.append(
"Error: P0001: Error en expresiones de fechas")
pass
def invokeFunction(id, *params):
temp = None
list_ = params
params = []
for p in list_:
if isinstance(p, str):
p = p.strip('"')
p = p.strip("'")
params.append(p)
if id == "extract":
temp = ExtractDate(params[0], params[1], params[2], 0, 0)
temp = temp.execute(None)
elif id == "date_part":
temp = DatePart(params[0], params[1], params[2], 0, 0)
temp = temp.execute(None)
else:
parameters = []
for p in params:
parameters.append(Primitive(TYPE.NULL, p, p, 0, 0))
temp = FunctionCall(id, parameters, 0, 0)
temp = temp.execute(None)
if temp:
return temp.value
return temp
def execute(self, environment):
countGr = environment.groupCols
if countGr == 0:
if self.colData != "*":
c = self.colData.execute(environment).value
if self.func == "sum":
newDf = c.sum()
elif self.func == "count":
newDf = c.count()
elif self.func == "prom":
newDf = c.mean()
else:
newDf = None
expression.list_errors.append(
"Error: 42725: Error en la funcion " + str(self.func) +
"\n En la linea: " + str(self.row))
else:
c = environment.dataFrame.iloc[:, -1:]
if self.func == "count":
newDf = len(c)
else:
newDf = None
expression.list_errors.append(
"Error: 42725: Error en la funcion " + str(self.func) +
"\n En la linea: " + str(self.row))
return Primitive(TYPE.NUMBER, newDf, self.temp, self.row,
self.column)
if self.colData != "*":
# Obtiene las ultimas columnas metidas (Las del group by)
df = environment.dataFrame.iloc[:, -countGr:]
c = self.colData.execute(environment)
x = c.value
x = pd.DataFrame(x)
x.rename(columns={x.columns[0]: c.temp}, inplace=True)
if len(list(x.columns)) > 1:
df = pd.concat([df, x.iloc[:, :1]], axis=1)
else:
df = pd.concat([df, x], axis=1)
cols = list(df.columns)[:-1]
if self.func == "sum":
newDf = df.groupby(cols).sum().reset_index()
elif self.func == "count":
newDf = df.groupby(cols).count().reset_index()
elif self.func == "prom":
newDf = df.groupby(cols).mean().reset_index()
else:
newDf = None
expression.list_errors.append(
"Error: 42725: Error en la funcion " + str(self.func) +
"\n En la linea: " + str(self.row))
value = newDf.iloc[:, -1:]
else:
# Obtiene las ultimas columnas metidas (Las del group by)
df = environment.dataFrame.iloc[:, -countGr:]
x = df.iloc[:, -1:]
x = pd.DataFrame(x)
x.rename(columns={x.columns[0]: "count(*)"}, inplace=True)
df = pd.concat([df, x], axis=1)
cols = list(df.columns)[:-1]
if self.func == "count":
newDf = df.groupby(cols).count().reset_index()
else:
newDf = None
expression.list_errors.append(
"Error: 42725: Error en la funcion " + str(self.func) +
"\n En la linea: " + str(self.row))
value = newDf.iloc[:, -1:]
return Primitive(TYPE.NUMBER, value, self.temp, self.row, self.column)
def execute(self, environment):
try:
if self.type == "TIMESTAMP":
if self.str[0] == "now":
self.str = datetime.now().strftime("%Y/%m/%d %H:%M:%S").split()
if self.opt == "YEAR":
val = self.str[0][:4]
elif self.opt == "MONTH":
val = self.str[0][5:7]
elif self.opt == "DAY":
val = self.str[0][8:10]
elif self.opt == "HOUR":
val = self.str[1][:2]
elif self.opt == "MINUTE":
val = self.str[1][3:5]
elif self.opt == "SECOND":
val = self.str[1][6:8]
else:
val = self.str
raise Exception
elif self.type == "DATE":
if self.opt == "YEAR":
val = self.str[0][:4]
elif self.opt == "MONTH":
val = self.str[0][5:7]
elif self.opt == "DAY":
val = self.str[0][8:10]
else:
val = self.str
raise Exception
elif self.type == "TIME":
if self.opt == "HOUR":
val = self.str[0][:2]
elif self.opt == "MINUTE":
val = self.str[0][3:5]
elif self.opt == "SECOND":
val = self.str[0][6:8]
else:
val = self.str
raise Exception
elif self.type == "INTERVAL":
if self.opt == "YEAR":
idx = self.str.index("years")
val = self.str[idx - 1]
elif self.opt == "MONTH":
idx = self.str.index("months")
val = self.str[idx - 1]
elif self.opt == "DAY":
idx = self.str.index("days")
val = self.str[idx - 1]
elif self.opt == "HOUR":
idx = self.str.index("hours")
val = self.str[idx - 1]
elif self.opt == "MINUTE":
idx = self.str.index("minutes")
val = self.str[idx - 1]
elif self.opt == "SECOND":
idx = self.str.index("seconds")
val = self.str[idx - 1]
else:
val = self.str
raise Exception
else:
val = self.str
raise Exception
return Primitive(TYPE.NUMBER, int(val), self.temp, self.row, self.column)
except TypeError:
raise expression.list_errors.append(
"Error: 42804: discrepancia de tipo de datos "
)
except ValueError: # cuando no tiene el valor INTERVAL
raise expression.list_errors.append(
"Error: 22007:sintaxis de entrada no válida para el tipo 'interval' "
)
except:
raise expression.list_errors.append(
"Error: 22007: Formato de fecha invalido " + str(self.str)
)
def execute(self, environment):
try:
valores = self.colData.execute(environment)
if isinstance(valores.value, pd.core.series.Series):
lst = valores.value.tolist()
lst = [v.split() for v in lst]
else:
lst = [valores.split()]
if valores.type == TYPE.TIMESTAMP or valores.type == TYPE.DATETIME:
if self.opt == "YEAR":
val = [date[0][:4] for date in lst]
elif self.opt == "MONTH":
val = [date[0][5:7] for date in lst]
elif self.opt == "DAY":
val = [date[0][8:10] for date in lst]
elif self.opt == "HOUR":
val = [date[1][:2] for date in lst]
elif self.opt == "MINUTE":
val = [date[1][3:5] for date in lst]
elif self.opt == "SECOND":
val = [date[1][6:8] for date in lst]
else:
# ERROR
expression.list_errors.append(
"Error: 22007: Formato de fecha invalido " + str(self.str)
)
val = self.str
elif valores.type == TYPE.DATE:
if self.opt == "YEAR":
val = [date[0][:4] for date in lst]
elif self.opt == "MONTH":
val = [date[0][5:7] for date in lst]
elif self.opt == "DAY":
val = [date[0][8:10] for date in lst]
else:
val = self.str
raise Exception
elif valores.type == TYPE.TIME:
if self.opt == "HOUR":
val = [date[0][:2] for date in lst]
elif self.opt == "MINUTE":
val = [date[0][3:5] for date in lst]
elif self.opt == "SECOND":
val = [date[0][6:8] for date in lst]
else:
val = self.str
raise Exception
else:
val = self.str
raise Exception
if isinstance(val, list):
if len(val) <= 1:
val = val[0]
else:
val = pd.Series(val)
return Primitive(TYPE.NUMBER, val, self.temp, self.row, self.column)
except TypeError:
expression.list_errors.append(
"Error: 42804: discrepancia de tipo de datos "
)
except ValueError: # cuando no tiene el valor INTERVAL
expression.list_errors.append(
"Error: 22007:sintaxis de entrada no válida para el tipo 'interval' "
)
except:
raise expression.list_errors.append(
"Error: 22007: Formato de fecha invalido " + str(self.str)
)
def execute(self, environment):
type_ = TYPE.NUMBER
try:
valores = []
types = []
for p in self.params:
obj = p.execute(environment)
val = obj.value
t = obj.type
if isinstance(val, pd.core.series.Series):
val = val.tolist()
valores.append(val)
types.append(t)
# Se toma en cuenta que las funcines matematicas
# y trigonometricas producen un tipo NUMBER
type_ = TYPE.NUMBER
if self.function == "abs":
value = mf.absolute(*valores)
elif self.function == "cbrt":
value = mf.cbrt(*valores)
elif self.function == "ceil":
value = mf.ceil(*valores)
elif self.function == "ceiling":
value = mf.ceiling(*valores)
elif self.function == "degrees":
value = mf.degrees(*valores)
elif self.function == "div":
value = mf.div(*valores)
elif self.function == "exp":
value = mf.exp(*valores)
elif self.function == "factorial":
value = mf.factorial(*valores)
elif self.function == "floor":
value = mf.floor(*valores)
elif self.function == "gcd":
value = mf.gcd(*valores)
elif self.function == "lcm":
value = mf.lcm(*valores)
elif self.function == "ln":
value = mf.ln(*valores)
elif self.function == "log":
value = mf.log(*valores)
elif self.function == "log10":
value = mf.log10(*valores)
elif self.function == "mod":
value = mf.mod(*valores)
elif self.function == "pi":
value = mf.pi()
elif self.function == "power":
value = mf.pow(*valores)
elif self.function == "radians":
value = mf.radians(*valores)
elif self.function == "round":
value = mf.round_(*valores)
elif self.function == "sign":
value = mf.sign(*valores)
elif self.function == "sqrt":
value = mf.sqrt(*valores)
elif self.function == "trunc":
value = mf.truncate_col(*valores)
elif self.function == "width_bucket":
value = mf.with_bucket(*valores)
elif self.function == "random":
value = mf.random_()
elif self.function == "acos":
value = trf.acos(*valores)
elif self.function == "acosd":
value = trf.acosd(*valores)
elif self.function == "asin":
value = trf.asin(*valores)
elif self.function == "asind":
value = trf.asind(*valores)
elif self.function == "atan":
value = trf.atan(*valores)
elif self.function == "atand":
value = trf.atand(*valores)
elif self.function == "atan2":
value = trf.atan2(*valores)
elif self.function == "atan2d":
value = trf.atan2d(*valores)
elif self.function == "cos":
value = trf.cos(*valores)
elif self.function == "cosd":
value = trf.cosd(*valores)
elif self.function == "cot":
value = trf.cot(*valores)
elif self.function == "cotd":
value = trf.cotd(*valores)
elif self.function == "sin":
value = trf.sin(*valores)
elif self.function == "sind":
value = trf.sind(*valores)
elif self.function == "tan":
value = trf.tan(*valores)
elif self.function == "tand":
value = trf.tand(*valores)
elif self.function == "sinh":
value = trf.sinh(*valores)
elif self.function == "cosh":
value = trf.cosh(*valores)
elif self.function == "tanh":
value = trf.tanh(*valores)
elif self.function == "asinh":
value = trf.asinh(*valores)
elif self.function == "acosh":
value = trf.acosh(*valores)
elif self.function == "atanh":
value = trf.atanh(*valores)
elif self.function == "length":
value = strf.lenght(*valores)
elif self.function == "substring":
type_ = TYPE.STRING
value = strf.substring(*valores)
elif self.function == "trim":
type_ = TYPE.STRING
value = strf.trim_(*valores)
elif self.function == "get_byte":
value = strf.get_byte(*valores)
elif self.function == "md5":
type_ = TYPE.STRING
value = strf.md5(*valores)
elif self.function == "set_byte":
type_ = TYPE.STRING
value = strf.set_byte(*valores)
elif self.function == "sha256":
type_ = TYPE.STRING
value = strf.sha256(*valores)
elif self.function == "substr":
type_ = TYPE.STRING
value = strf.substring(*valores)
elif self.function == "convert_date":
type_ = TYPE.DATETIME
value = strf.convert_date(*valores)
elif self.function == "convert_int":
value = strf.convert_int(*valores)
elif self.function == "encode":
type_ = TYPE.STRING
value = strf.encode(*valores)
elif self.function == "decode":
type_ = TYPE.STRING
value = strf.decode(*valores)
# Se toma en cuenta que la funcion now produce tipo DATE
elif self.function == "now":
type_ = TYPE.DATETIME
value = datetime.now().strftime("%Y/%m/%d %H:%M:%S")
else:
# TODO: Agregar un error de funcion desconocida
value = valores[0]
if isinstance(value, list):
if len(value) <= 1:
value = value[0]
else:
value = pd.Series(value)
return Primitive(type_, value, self.temp, self.row, self.column)
except TypeError:
expression.list_errors.append(
"Error: 42883: La funcion "
+ str(self.function)
+ "("
+ str(type_)
+ ") no existe"
+ "\n En la linea: "
+ str(self.row)
)
except:
expression.list_errors.append("Error: P0001: Error en funciones")
