def CreateDB(UseExistingDatabase=False):
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
if UseExistingDatabase:
print(
"You have specified the UseExistingDatabase option, the the database itself will be kept, but all his content will be removed"
)
if input(
"This operation will create a new empty DB.\n If a database exists, it will DESTROY all existings data of the existing database.\nAre you SURE ? Confirm by Y !"
).lower() != "y":
print("Import Aborted !!!")
exit()
print("Configuration is Database:", app.config['DB_DATABASE'])
print("Login: "******"/", app.config['DB_PASSWORD'])
print("Host: ", app.config['DB_HOST'])
import psycopg2
if os.path.exists("vault"):
print("Drop existings images")
shutil.rmtree("vault")
os.mkdir("vault")
print("Connect Database")
if UseExistingDatabase:
conn = psycopg2.connect(user=app.config['DB_USER'],
password=app.config['DB_PASSWORD'],
host=app.config['DB_HOST'],
database=app.config['DB_DATABASE'])
else:
# On se loggue en postgres pour dropper/creer les bases qui doit être déclaré trust dans hba_conf
conn = psycopg2.connect(user='******',
host=app.config['DB_HOST'])
cur = conn.cursor()
conn.set_session(autocommit=True)
if UseExistingDatabase:
print("Drop the existing public schema")
sql = "DROP SCHEMA public cascade"
else:
print("Drop the existing database")
sql = "DROP DATABASE IF EXISTS " + app.config['DB_DATABASE']
cur.execute(sql)
if UseExistingDatabase:
print("Create the public schema")
sql = "create schema public AUTHORIZATION " + app.config['DB_USER']
else:
print("Create the new database")
sql = "create DATABASE " + app.config[
'DB_DATABASE'] + " WITH ENCODING='LATIN1' OWNER=" + app.config[
'DB_USER'] + " TEMPLATE=template0 LC_CTYPE='C' LC_COLLATE='C' CONNECTION LIMIT=-1 "
cur.execute(sql)
print("Create the Schema")
dbcreate()
print("Create Roles & Users")
createadminuser()
print("Creation Done")
def CreateDB():
with app.app_context(): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
if input("This operation will create a new empty DB.\n If a database exists, it will DESTROY all existings data of the existing database.\nAre you SURE ? Confirm by Y !").lower()!="y":
print("Import Aborted !!!")
exit()
print("Configuration is Database:",app.config['DB_DATABASE'])
print("Login: "******"/",app.config['DB_PASSWORD'])
print("Host: ",app.config['DB_HOST'])
import psycopg2
print("Connect Database")
# On se loggue en postgres pour dropper/creer les bases qui doit être déclaré trust dans hba_conf
conn=psycopg2.connect(user='******',host=app.config['DB_HOST'])
cur=conn.cursor()
conn.set_session(autocommit=True)
print("Drop the existing database")
sql="DROP DATABASE IF EXISTS "+app.config['DB_DATABASE']
cur.execute(sql)
print("Create the new database")
sql="create DATABASE "+app.config['DB_DATABASE']+" WITH ENCODING='LATIN1' OWNER="+app.config['DB_USER']+" TEMPLATE=template0 LC_CTYPE='C' LC_COLLATE='C' CONNECTION LIMIT=-1 "
cur.execute(sql)
print("Create the Schema")
dbcreate()
print("Create Roles & Users")
createadminuser()
print("Creation Done")
def dbcreate():
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
db.create_all()
from flask_migrate import stamp
stamp(revision='head')
database.ExecSQL("""create view objects as select oh.*,ofi.*
from obj_head oh left join obj_field ofi on oh.objid=ofi.objfid""")
def ResetDBSequence(cur=None):
with app.app_context(): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
print("Start Sequence Reset")
if cur is None:
cur=db.session
cur.execute("SELECT setval('seq_taxonomy', (SELECT max(id) FROM taxonomy), true)")
cur.execute("SELECT setval('seq_users', (SELECT max(id) FROM users), true)")
cur.execute("SELECT setval('roles_id_seq', (SELECT max(id) FROM roles), true)")
print("Sequence Reset Done")
def RecomputeStats():
"""
Recompute stats related on Taxonomy and Projects
"""
import appli.cron
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
appli.cron.RefreshAllProjectsStat()
appli.cron.RefreshTaxoStat()
def ExtractCategoriesFromRFModel(modeldir):
from sklearn.externals import joblib
import json
from pathlib import Path
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
ModelFolder = Path("RF_models") / modeldir
Meta = json.load((ModelFolder / "meta.json").open("r"))
Classifier = joblib.load(ModelFolder / 'random_forest.jbl')
# Meta['categories']=[int(x) for x in Classifier.classes_]
Meta['categories'] = {
r[0]: r[1]
for r in database.GetTaxoNameFromIdList(
[int(x) for x in Classifier.classes_])
}
json.dump(Meta, (ModelFolder / "meta.json").open("w"), indent="\t")
def UpdateSunPos(ProjId):
"""
will update Sunpos field for object of the given project
if projid = * all project are updated
"""
from appli import CalcAstralDayTime
from astral import AstralError
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
param = []
sql = """select distinct objdate,objtime,round(cast(latitude as NUMERIC),4) latitude,round(cast(longitude as NUMERIC),4) longitude
from obj_head
where objdate is not null and objtime is not null and longitude is not null and latitude is not null
"""
if ProjId != '*':
sql += " and projid=%s "
param.append(ProjId)
Obj = database.GetAll(sql, param)
for o in Obj:
# l=Location()
# l.latitude=o['latitude']
# l.longitude = o['longitude']
# s=l.sun(date=o['objdate'],local=False)
# dt=datetime.datetime(o['objdate'].year,o['objdate'].month,o['objdate'].day,o['objtime'].hour,o['objtime'].minute,o['objtime'].second,tzinfo=pytz.UTC)
# if s['sunset'].time()>s['sunrise'].time() \
# and dt.time()>=s['sunset'].time(): Result='N'
# elif dt>=s['dusk']: Result='U'
# elif dt>=s['sunrise']: Result='D'
app.logger.info("Process %s %s %s %s", o['objdate'], o['objtime'],
o['latitude'], o['longitude'])
try:
Result = CalcAstralDayTime(o['objdate'], o['objtime'],
o['latitude'], o['longitude'])
sql = "update obj_head set sunpos=%s where objdate=%s and objtime=%s and round(cast(latitude as NUMERIC),4)=%s and round(cast(longitude as NUMERIC),4)=%s "
param = [
Result, o['objdate'], o['objtime'], o['latitude'],
o['longitude']
]
if ProjId != '*':
sql += " and projid=%s "
param.append(ProjId)
database.ExecSQL(sql, param)
app.logger.info(Result)
except AstralError as e:
app.logger.error("Astral error : %s", e)
def ResetDBSequence(cur=None):
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
print("Start Sequence Reset")
if cur is None:
cur = db.session
cur.execute(
"SELECT setval('seq_acquisitions', (SELECT max(acquisid) FROM acquisitions), true)"
)
cur.execute(
"SELECT setval('seq_images', (SELECT max(imgid) FROM images), true)"
)
cur.execute(
"SELECT setval('seq_objects', (SELECT max(objid) FROM obj_head), true)"
)
cur.execute(
"SELECT setval('seq_process', (SELECT max(processid) FROM process), true)"
)
cur.execute(
"SELECT setval('seq_projects', (SELECT max(projid) FROM projects), true)"
)
cur.execute(
"SELECT setval('seq_projectspriv', (SELECT max(id) FROM projectspriv), true)"
)
cur.execute(
"SELECT setval('seq_samples', (SELECT max(sampleid) FROM samples), true)"
)
cur.execute(
"SELECT setval('seq_taxonomy', (SELECT max(id) FROM taxonomy), true)"
)
cur.execute(
"SELECT setval('seq_temp_tasks', (SELECT max(id) FROM temp_tasks), true)"
)
cur.execute(
"SELECT setval('seq_users', (SELECT max(id) FROM users), true)")
cur.execute(
"SELECT setval('roles_id_seq', (SELECT max(id) FROM roles), true)")
cur.execute(
"SELECT setval('part_projects_pprojid_seq', (SELECT max(pprojid) FROM part_projects), true)"
)
cur.execute(
"SELECT setval('part_samples_psampleid_seq', (SELECT max(psampleid) FROM part_samples), true)"
)
print("Sequence Reset Done")
def FullDBRestore(UseExistingDatabase=False):
"""
Will restore an exported DB as is and replace all existing data
"""
from appli.tasks.taskimportdb import RestoreDBFull
if UseExistingDatabase:
print(
"You have specified the UseExistingDatabase option, the the database itself will be kept, but all his content will be removed"
)
if input(
"This operation will import an exported DB and DESTROY all existings data of the existing database.\nAre you SURE ? Confirm by Y !"
).lower() != "y":
print("Import Aborted !!!")
exit()
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
RestoreDBFull(UseExistingDatabase)
def RecomputePart(ProjectID, What, User, Email):
if 'C' in What:
if User is None or Email is None:
print("-u and -e options are required for CTD import")
quit(-1)
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
import appli.part.database as partdatabase
import appli.part.prj as prj
import appli.part.common_sample_import as common_import
Prj = partdatabase.part_projects.query.filter_by(
pprojid=ProjectID).first()
Samples = database.GetAll(
"select psampleid,profileid from part_samples where pprojid=(%s)",
[ProjectID])
for S in Samples:
print("Processing particle sample %s:%s" %
(S['psampleid'], S['profileid']))
if 'D' in What:
print("Det=",
prj.ComputeHistoDet(S['psampleid'], Prj.instrumtype))
if 'R' in What:
print("Red=",
prj.ComputeHistoRed(S['psampleid'], Prj.instrumtype))
if 'M' in What:
print("Match=", prj.ComputeZooMatch(S['psampleid'],
Prj.projid))
if 'C' in What:
print(
"CTD=", "Imported" if common_import.ImportCTD(
S['psampleid'], User, Email) else 'CTD No file')
Samples = database.GetAll(
"select psampleid,profileid,sampleid from part_samples where pprojid=(%s)",
[ProjectID])
for S in Samples:
if 'T' in What and S['sampleid']:
print(
"Zoo for particle sample %s:%s=" %
(S['psampleid'], S['profileid']),
prj.ComputeZooHisto(S['psampleid']))
def dbcreate():
with app.app_context(): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
db.create_all()
import flask_migrate
flask_migrate.stamp(revision='head')
def RecomputeDisplayName():
print ("RecomputeDisplayName")
with app.app_context(): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
import appli.services
appli.services.ComputeDisplayName([])
and coalesce(taxonomy.nbrobjcum,0)<>q.nbr""")
print("RefreshTaxoStat updated %d level %d taxo" % (n, i))
if n == 0:
break
appli.part.prj.GlobalTaxoCompute()
if __name__ == "__main__":
# RefreshTaxoStat()
from appli import app
from flask import g
import traceback, appli.tasks, logging
app.logger.setLevel(logging.DEBUG)
for h in app.logger.handlers:
h.setLevel(logging.DEBUG)
app.logger.info("Start Daily Task")
try:
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
RefreshAllProjectsStat()
RefreshTaxoStat()
app.logger.info(appli.tasks.taskmanager.AutoClean())
except Exception as e:
s = str(e)
tb_list = traceback.format_tb(e.__traceback__)
for i in tb_list[::-1]:
s += "\n" + i
app.logger.error("Exception on Daily Task : %s" % s)
app.logger.info("End Daily Task")