def sqlfile_convert_to_personal(self):
personal = ProductPersonal(self.mem)
personal.name = self.name
personal.amount = self.amount
personal.fat = self.fat
personal.protein = self.protein
personal.carbohydrate = self.carbohydrate
personal.company = self.company
personal.last = self.last
personal.elaboratedproducts_id = self.elaboratedproducts_id
personal.languages = self.languages
personal.calories = self.calories
personal.salt = self.salt
personal.cholesterol = self.cholesterol
personal.sodium = self.sodium
personal.potassium = self.potassium
personal.fiber = self.fiber
personal.sugars = self.sugars
personal.saturated_fat = self.saturated_fat
personal.system_company = self.system_company
personal.foodtype = self.foodtype
personal.save()
datestr = dtnaive2string(self.mem.inittime, "%Y%m%d%H%M")
sql_filename = "{}.sql".format(datestr)
sql = open(sql_filename, "w")
#Delete old personal products
sql.write("-- caloriestracker_maintenance_products_system2personal\n")
sql.write(personal.sql_insert("personalproducts") + "\n")
#UPDATING PRODUCTS IN THE REST OF TABLES
for table in ['formats', 'meals', 'products_in_elaboratedproducts']:
sql.write(
b2s(
self.mem.con.mogrify(
"update " + table +
" set products_id=%s, system_product=%s where products_id=%s and system_product=%s;",
(personal.id, personal.system_product, self.id,
self.system_product))) + "\n")
sql.write(
b2s(
self.mem.con.mogrify("delete from products where id=%s;",
(self.id, ))) + "\n")
sql.write("\n")
sql.close()
self.mem.con.rollback() #Save it's in script, not in database.
print(
self.
tr("We have generated '{}' to convert the system product '{}' to a personal product '{}'"
).format(sql_filename, self.fullName(), personal.fullName()))
print(
self.
tr("You mustn't use this file if your are not a caloriestracker developer ;)"
))
def sql_update(self, table="companies"):
self.last = datetime.now()
return b2s(
self.mem.con.mogrify(
"update public." + table +
" set name=%s, last=%s where id=%s;",
(self.name, self.last, self.id)))
def sql_insert(self, table="products", returning_id=True):
companies_id = None if self.company == None else self.company.id
foodtypes_id = None if self.foodtype == None else self.foodtype.id
self.last = datetime.now()
sql = """insert into """ + table + """ (
name, amount, fat, protein, carbohydrate, companies_id, last,
elaboratedproducts_id, languages, calories, salt, cholesterol, sodium,
potassium, fiber, sugars, saturated_fat, system_company, foodtypes_id, additives
) values (%s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s) returning id;"""
sql_parameters = (self.name, self.amount, self.fat, self.protein,
self.carbohydrate, companies_id, self.last,
self.elaboratedproducts_id, self.languages,
self.calories, self.salt, self.cholesterol,
self.sodium, self.potassium, self.fiber, self.sugars,
self.saturated_fat, self.system_company,
foodtypes_id, self.additives.array_of_ids())
if returning_id == True:
r = self.mem.con.mogrify(sql, sql_parameters)
else:
sql = sql.replace(") values (", ", id ) values (")
sql = sql.replace(") returning id", ", %s)")
# print(sql)
# print(sql_parameters)
r = b2s(self.mem.con.mogrify(sql, sql_parameters + (self.id, )))
return r
def sql_insert(self, table="companies", returning_id=True):
self.last = datetime.now()
sql = "insert into public." + table + "(name, last) values (%s, %s) returning id;"
sql_parameters = (self.name, self.last)
if returning_id == True:
r = self.mem.con.mogrify(sql, sql_parameters)
else:
sql = sql.replace(") values (", ", id ) values (")
sql = sql.replace(") returning id", ", %s)")
# print(sql)
# print(sql_parameters)
r = self.mem.con.mogrify(sql, sql_parameters + (self.id, ))
return b2s(r)
def sql_insert(self, table="formats", returning_id=True):
self.last=datetime.now()
sql="insert into "+table +"(name, amount, last, products_id, system_product) values (%s, %s, %s, %s, %s) returning id;"
sql_parameters=(self.name, self.amount, self.last, self.product.id, self.product.system_product)
if returning_id==True:
r=self.mem.con.mogrify(sql, sql_parameters)
else:
sql=sql.replace(") values (", ", id ) values (")
sql=sql.replace(") returning id", ", %s)")
# print(sql)
# print(sql_parameters)
r=self.mem.con.mogrify(sql, sql_parameters+(self.id, ))
return b2s(r)
def sql_update(self, table="products"):
companies_id = None if self.company == None else self.company.id
foodtypes_id = None if self.foodtype == None else self.foodtype.id
self.last = datetime.now()
sql = """update public.""" + table + """ set name=%s, amount=%s, fat=%s, protein=%s, carbohydrate=%s, companies_id=%s, last=%s,
elaboratedproducts_id=%s, languages=%s, calories=%s, salt=%s, cholesterol=%s, sodium=%s, potassium=%s, fiber=%s, sugars=%s, saturated_fat=%s,
system_company=%s, foodtypes_id=%s, additives=%s
where id=%s;"""
sql_parameters = (self.name, self.amount, self.fat, self.protein,
self.carbohydrate, companies_id, self.last,
self.elaboratedproducts_id, self.languages,
self.calories, self.salt, self.cholesterol,
self.sodium, self.potassium, self.fiber, self.sugars,
self.saturated_fat, self.system_company,
foodtypes_id, self.additives.array_of_ids(), self.id)
print(sql)
print(sql_parameters)
return b2s(self.mem.con.mogrify(sql, sql_parameters))
def parse_contribution_dump_generate_files_and_validates_them(
auxiliar_con, contribution_dump):
## 1. SETS A NEW DATABASE CALORIESTRACKER_CONTRIBUTION TO DEFAULT
datestr = dtnaive2string(datetime.now(), "%Y%m%d%H%M")
database = "caloriestracker_contribution"
admin = AdminPG(auxiliar_con.user, auxiliar_con.password,
auxiliar_con.server, auxiliar_con.port)
if admin.db_exists(database) == True:
admin.drop_db(database)
admin.create_db(database)
newcon = admin.connect_to_database(database)
database_update(newcon, "caloriestracker", __versiondatetime__, "Console")
print("1. After setting database to default", *print_table_status(newcon))
## 2. ADDS CONTRIBUTOR PERSONAL DATA TO CALORIESTRACKER_CONTRIBUTION
newcon.load_script(contribution_dump)
newcon.commit()
print("2. After loading personal data from collaborator",
*print_table_status(newcon))
## 3. GENERATES SYSTEM.SQL FILE AND RETURN TO CONTRIBUTOR
mem_temporary = MemInit(
) #It's an internal and temporaray Mem. Better to pass parameters
mem_temporary.con = newcon
mem_temporary.debuglevel = "DEBUG"
if mem_temporary.con.is_active() == False:
exit(1)
mem_temporary.load_db_data(False)
mem_temporary.user = mem_temporary.data.users.find_by_id(1)
## GENERATING XXXXXXXXXXXX.sql
package_sql_filename = "XXXXXXXXXXXX.sql".format(datestr)
package_sql = open(package_sql_filename, "w")
package_sql.write("select;\n") #For no personal data empty files
companies_map = {}
products_map = {}
formats_map = {}
#companies
new_system_companies_id = mem_temporary.con.cursor_one_field(
"select max(id)+1 from companies")
new_system_companies = CompanySystemManager(mem_temporary)
for company in mem_temporary.data.companies.arr:
if company.system_company == False:
question = input_YN(
"Do you want to convert this company '{}' to a system one?".
format(company), "Y")
if question == True:
system_company = CompanySystem(mem_temporary, company.name,
company.last,
new_system_companies_id)
new_system_companies.append(system_company)
companies_map[company.string_id()] = system_company.string_id()
new_system_companies_id = new_system_companies_id + 1
package_sql.write(
system_company.insert_string("companies") + ";\n")
mem_temporary.data.companies.append(
system_company
) ##Appends new sistem company to mem_temporary.data
#print ("Company will change from {} to {}".format(company.string_id(), system_company.string_id()))
#products
new_system_products_id = mem_temporary.con.cursor_one_field(
"select max(id)+1 from products")
new_system_products = ProductManager(mem_temporary)
for product in mem_temporary.data.products.arr:
if product.system_product == False:
question = input_YN(
"Do you want to convert this product '{}' to a system one?".
format(product.fullName()), "Y")
#Selects a company
if product.company != None:
if product.system_company == False:
company = mem_temporary.data.companies.find_by_id_system(
*CompanySystem.string_id2tuple(companies_map[
product.company.string_id()]))
else:
company = product.company
system_company = True
else:
company = None
system_company = None
#Create product
if question == True:
system_product = Product(
mem_temporary, product.name, product.amount, product.fat,
product.protein, product.carbohydrate, company,
product.last, product.elaboratedproducts_id,
product.languages, product.calories, product.salt,
product.cholesterol, product.sodium, product.potassium,
product.fiber, product.sugars, product.saturated_fat,
system_company, new_system_products_id)
new_system_products.append(system_product)
products_map[product.string_id()] = system_product.string_id()
new_system_products_id = new_system_products_id + 1
#print ("Product will change from {} to {}".format(product, system_product))
#if company!=None:
# print ("Its company will change from {} to {}".format(product.company.string_id(), company.string_id()))
package_sql.write(
system_product.insert_string("products") + ";\n")
#formats
new_system_formats_id = mem_temporary.con.cursor_one_field(
"select max(id)+1 from formats")
for product in mem_temporary.data.products.arr:
if product.system_product == False:
product.needStatus(1)
if product.formats.length() == 0:
continue
for format in product.formats.arr:
question = input_YN(
"Do you want to convert this format '{}' to a system one?".
format(format), "Y")
if question == True:
system_product = new_system_products.find_by_string_id(
new_system_products,
products_map[format.product.string_id(
)]) #Recently created in systemproducts
system_format = Format(mem_temporary, format.name,
system_product,
system_product.system_product,
format.amount, format.last,
new_system_formats_id)
system_product.needStatus(1) #Creates empty format manager
system_product.formats.append(system_format)
formats_map[format.string_id()] = system_format.string_id()
new_system_formats_id = new_system_formats_id + 1
package_sql.write(
system_format.insert_string("formats") + ";\n")
#print ("Format will change from {} to {}".format(format.string_id(), system_format.string_id()))
package_sql.close()
## GENERATING COLLABORATION UPDATE FOR COLLABORATOR
return_sql_filename = "XXXXXXXXXXXX_version_needed_update_first_in_github.sql"
return_sql = open(return_sql_filename, "w")
return_sql.write("select;\n") #For no personal data empty files
#COMPANIES
for origin, destiny in companies_map.items(): #k,v strings_id
origin_personal_company = mem_temporary.data.companies.find_by_string_id(
origin)
#destiny_system_company=new_system_companies.find_by_string_id(destiny)
#Delete old personal companies
return_sql.write("-- " + origin_personal_company.fullName() + "\n")
return_sql.write("delete from personalcompanies where id=" +
str(origin_personal_company.id) + ";\n")
return_sql.write("\n")
#PRODUCTS
for origin, destiny in products_map.items():
origin_personal_product = ProductManager.find_by_string_id(
mem_temporary.data.products, origin)
destiny_system_product = ProductManager.find_by_string_id(
new_system_products, destiny)
#Delete old personal products
return_sql.write("-- " + origin_personal_product.fullName() + "\n")
return_sql.write(
b2s(
mem_temporary.con.mogrify(
"delete from personalproducts where id=%s;",
(origin_personal_product.id, ))) + "\n")
#UPDATING PRODUCTS IN THE REST OF TABLES
for table in ['formats', 'meals', 'products_in_elaboratedproducts']:
return_sql.write(
b2s(
mem_temporary.con.mogrify(
"update " + table +
" set products_id=%s, system_product=%s where products_id=%s and system_product=%s;",
(destiny_system_product.id,
destiny_system_product.system_product,
origin_personal_product.id,
origin_personal_product.system_product))) + "\n")
#Formats
for format in origin_personal_product.formats.arr:
#Delete old personal formats
return_sql.write("-- " + origin_personal_product.fullName() + "-" +
format.name + "\n")
return_sql.write(
b2s(
mem_temporary.con.mogrify(
"delete from personalformats where id=%s;",
(format.id, ))) + "\n")
return_sql.write("\n")
return_sql.close()
print(
"3. After generating files collaboration. Emulates launching update_table",
*print_table_status(newcon))
## 5. GENERATES MEALS OF PERSONAL PRODUCTS FOR TESTING
for origin, destiny in products_map.items():
origin_personal_product = ProductManager.find_by_string_id(
mem_temporary.data.products, origin)
destiny_system_product = ProductManager.find_by_string_id(
new_system_products, destiny)
meal = Meal(mem_temporary, datetime.now(), origin_personal_product,
100, mem_temporary.user,
origin_personal_product.system_product, None)
meal.save()
## 4. TRIES SYSTEM.SQL
newcon.load_script("XXXXXXXXXXXX.sql")
print("4. After trying XXXXXXXXXXXX.sql", *print_table_status(newcon))
## 5. TRIES CONTRIBUTOR SCRIPT
newcon.load_script(
"XXXXXXXXXXXX_version_needed_update_first_in_github.sql")
print("5. After updating contributor database",
*print_table_status(newcon))
question = input_YN(
"Do you want to add {}.sql to caloriestracker/sql/?".format(datestr),
"?")
if question == True:
system(
"mv XXXXXXXXXXXX.sql caloriestracker/sql/{}.sql".format(datestr))
system(
"mv XXXXXXXXXXXX_version_needed_update_first_in_github.sql {}_version_needed_update_first_in_github.sql"
.format(datestr))
newcon.commit()
newcon.disconnect()
def sql_update(self, table):
self.last=datetime.now()
sql="update "+ table+ " set name=%s, products_id=%s, system_product=%s, amount=%s, last=%s where id=%s"
sql_parameters=(self.name, self.product.id, self.system_product, self.amount, self.last, self.id)
return b2s(self.mem.con.mogrify(sql, sql_parameters))
def sql_update(self, table="formats"):
return b2s(self.mem.con.mogrify("update "+table +" set name=%s, amount=%s, products_id=%s, system_product=%s,last=%s where id=%s;",
(self.name, self.amount, self.product.id, self.system_product, self.last, self.id)))
def sql_insert(self, table="formats"):
return b2s(self.mem.con.mogrify("insert into "+table +"(name, amount, products_id, system_product, last, id) values (%s, %s, %s, %s, %s, %s);",
(self.name, self.amount, self.product.id, self.system_product, self.last, self.id)))
