1 /*
2  * Copyright (C) 2024-2024 Huawei Device Co., Ltd.
3  * Licensed under the Apache License, Version 2.0 (the "License");
4  * you may not use this file except in compliance with the License.
5  * You may obtain a copy of the License at
6  *
7  *     http://www.apache.org/licenses/LICENSE-2.0
8  *
9  * Unless required by applicable law or agreed to in writing, software
10  * distributed under the License is distributed on an "AS IS" BASIS,
11  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12  * See the License for the specific language governing permissions and
13  * limitations under the License.
14  */
15 
16 #ifndef FRAMEWORKS_SERVICES_MEDIA_MULTI_STAGES_CAPTURE_INCLUDE_VISION_DB_SQLS_H
17 #define FRAMEWORKS_SERVICES_MEDIA_MULTI_STAGES_CAPTURE_INCLUDE_VISION_DB_SQLS_H
18 
19 #include "vision_aesthetics_score_column.h"
20 #include "vision_album_column.h"
21 #include "vision_column_comm.h"
22 #include "vision_column.h"
23 #include "vision_composition_column.h"
24 #include "vision_face_tag_column.h"
25 #include "vision_head_column.h"
26 #include "vision_image_face_column.h"
27 #include "vision_video_face_column.h"
28 #include "vision_label_column.h"
29 #include "vision_object_column.h"
30 #include "vision_ocr_column.h"
31 #include "vision_photo_map_column.h"
32 #include "vision_pose_column.h"
33 #include "vision_recommendation_column.h"
34 #include "vision_saliency_detect_column.h"
35 #include "vision_segmentation_column.h"
36 #include "vision_total_column.h"
37 #include "vision_video_label_column.h"
38 #include "vision_multi_crop_column.h"
39 
40 namespace OHOS {
41 namespace Media {
42 const std::string CREATE_TAB_ANALYSIS_OCR = "CREATE TABLE IF NOT EXISTS " + VISION_OCR_TABLE + " (" +
43     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
44     FILE_ID + " INT UNIQUE, " +
45     OCR_TEXT + " TEXT, " +
46     OCR_VERSION + " TEXT, " +
47     OCR_TEXT_MSG + " TEXT, " +
48     OCR_WIDTH + " INT, " +
49     OCR_HEIGHT + " INT, " +
50     ANALYSIS_VERSION + " TEXT)";
51 
52 const std::string CREATE_TAB_ANALYSIS_LABEL = "CREATE TABLE IF NOT EXISTS " + VISION_LABEL_TABLE + " (" +
53     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
54     FILE_ID + " INT UNIQUE, " +
55     CATEGORY_ID + " INT, " +
56     SUB_LABEL + " TEXT, " +
57     PROB + " REAL, " +
58     FEATURE + " TEXT, " +
59     SIM_RESULT + " TEXT, " +
60     LABEL_VERSION + " TEXT, " +
61     SALIENCY_SUB_PROB + " TEXT, " +
62     ANALYSIS_VERSION + " TEXT)";
63 
64 const std::string CREATE_TAB_ANALYSIS_VIDEO_LABEL = "CREATE TABLE IF NOT EXISTS " + VISION_VIDEO_LABEL_TABLE + " (" +
65     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
66     FILE_ID + " INT, " +
67     CATEGORY_ID + " TEXT, " +
68     CONFIDENCE_PROBABILITY + " REAL, " +
69     SUB_CATEGORY + " TEXT, " +
70     SUB_CONFIDENCE_PROB + " REAL, " +
71     SUB_LABEL + " TEXT, " +
72     SUB_LABEL_PROB + " REAL, " +
73     SUB_LABEL_TYPE + " INT, " +
74     TRACKS + " TEXT, " +
75     VIDEO_PART_FEATURE + " BLOB, " +
76     FILTER_TAG + " TEXT, " +
77     ALGO_VERSION + " TEXT, " +
78     ANALYSIS_VERSION + " TEXT)";
79 
80 const std::string CREATE_TAB_ANALYSIS_AESTHETICS = "CREATE TABLE IF NOT EXISTS " + VISION_AESTHETICS_TABLE + " (" +
81     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
82     FILE_ID + " INT UNIQUE, " +
83     AESTHETICS_SCORE + " INT, " +
84     AESTHETICS_VERSION + " TEXT, " +
85     PROB + " REAL, " +
86     ANALYSIS_VERSION + " TEXT)";
87 
88 const std::string CREATE_TAB_ANALYSIS_SALIENCY_DETECT = "CREATE TABLE IF NOT EXISTS " + VISION_SALIENCY_TABLE + " (" +
89     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
90     FILE_ID + " INT UNIQUE, " +
91     SALIENCY_X + " REAL, " +
92     SALIENCY_Y + " REAL, " +
93     SALIENCY_VERSION + " TEXT, " +
94     ANALYSIS_VERSION + " TEXT)";
95 
96 const std::string CREATE_TAB_ANALYSIS_OBJECT = "CREATE TABLE IF NOT EXISTS " + VISION_OBJECT_TABLE + " (" +
97     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
98     FILE_ID + " INT, " +
99     OBJECT_ID + " INT, " +
100     OBJECT_LABEL + " INT, " +
101     OBJECT_SCALE_X + " INT, " +
102     OBJECT_SCALE_Y + " INT, " +
103     OBJECT_SCALE_WIDTH + " INT, " +
104     OBJECT_SCALE_HEIGHT + " INT, " +
105     PROB + " REAL, " +
106     OBJECT_VERSION + " TEXT, " +
107     SCALE_X + " REAL DEFAULT 0, " +
108     SCALE_Y + " REAL DEFAULT 0, " +
109     SCALE_WIDTH + " REAL DEFAULT 0, " +
110     SCALE_HEIGHT + " REAL DEFAULT 0, " +
111     ANALYSIS_VERSION + " TEXT)";
112 
113 const std::string CREATE_TAB_ANALYSIS_RECOMMENDATION = "CREATE TABLE IF NOT EXISTS " + VISION_RECOMMENDATION_TABLE +
114     " (" +
115     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
116     FILE_ID + " INT, " +
117     RECOMMENDATION_ID + " INT, " +
118     RECOMMENDATION_RESOLUTION + " TEXT, " +
119     RECOMMENDATION_SCALE_X + " INT, " +
120     RECOMMENDATION_SCALE_Y + " INT, " +
121     RECOMMENDATION_SCALE_WIDTH + " INT, " +
122     RECOMMENDATION_SCALE_HEIGHT + " INT, " +
123     RECOMMENDATION_VERSION + " TEXT, " +
124     SCALE_X + " REAL DEFAULT 0, " +
125     SCALE_Y + " REAL DEFAULT 0, " +
126     SCALE_WIDTH + " REAL DEFAULT 0, " +
127     SCALE_HEIGHT + " REAL DEFAULT 0, " +
128     MOVEMENT_CROP + " TEXT, " +
129     MOVEMENT_VERSION + " TEXT, " +
130     ANALYSIS_VERSION + " TEXT)";
131 
132 const std::string CREATE_TAB_ANALYSIS_SEGMENTATION = "CREATE TABLE IF NOT EXISTS " + VISION_SEGMENTATION_TABLE + " (" +
133     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
134     FILE_ID + " INT UNIQUE, " +
135     SEGMENTATION_AREA + " TEXT, " +
136     SEGMENTATION_NAME + " INT, " +
137     PROB + " REAL, " +
138     SEGMENTATION_VERSION + " TEXT, " +
139     ANALYSIS_VERSION + " TEXT)";
140 
141 const std::string CREATE_TAB_ANALYSIS_COMPOSITION = "CREATE TABLE IF NOT EXISTS " + VISION_COMPOSITION_TABLE + " (" +
142     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
143     FILE_ID + " INT, " +
144     COMPOSITION_ID + " INT, " +
145     COMPOSITION_RESOLUTION + " TEXT, " +
146     CLOCK_STYLE + " INT, " +
147     CLOCK_LOCATION_X + " INT, " +
148     CLOCK_LOCATION_Y + " INT, " +
149     CLOCK_COLOUR + " TEXT, " +
150     COMPOSITION_SCALE_X + " INT, " +
151     COMPOSITION_SCALE_Y + " INT, " +
152     COMPOSITION_SCALE_WIDTH + " INT, " +
153     COMPOSITION_SCALE_HEIGHT + " INT, " +
154     COMPOSITION_VERSION + " TEXT, " +
155     SCALE_X + " REAL DEFAULT 0, " +
156     SCALE_Y + " REAL DEFAULT 0, " +
157     SCALE_WIDTH + " REAL DEFAULT 0, " +
158     SCALE_HEIGHT + " REAL DEFAULT 0, " +
159     ANALYSIS_VERSION + " TEXT)";
160 
161 const std::string CREATE_TAB_ANALYSIS_HEAD = "CREATE TABLE IF NOT EXISTS " + VISION_HEAD_TABLE + " (" +
162     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
163     FILE_ID + " INT, " +
164     HEAD_ID + " INT, " +
165     HEAD_LABEL + " INT, " +
166     HEAD_SCALE_X + " INT, " +
167     HEAD_SCALE_Y + " INT, " +
168     HEAD_SCALE_WIDTH + " INT, " +
169     HEAD_SCALE_HEIGHT + " INT, " +
170     PROB + " REAL, " +
171     HEAD_VERSION + " TEXT, " +
172     SCALE_X + " REAL DEFAULT 0, " +
173     SCALE_Y + " REAL DEFAULT 0, " +
174     SCALE_WIDTH + " REAL DEFAULT 0, " +
175     SCALE_HEIGHT + " REAL DEFAULT 0, " +
176     ANALYSIS_VERSION + " TEXT)";
177 
178 const std::string CREATE_TAB_ANALYSIS_POSE = "CREATE TABLE IF NOT EXISTS " + VISION_POSE_TABLE + " (" +
179     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
180     FILE_ID + " INT, " +
181     POSE_ID + " INT, " +
182     POSE_LANDMARKS + " BLOB, " +
183     POSE_SCALE_X + " INT, " +
184     POSE_SCALE_Y + " INT, " +
185     POSE_SCALE_WIDTH + " INT, " +
186     POSE_SCALE_HEIGHT + " INT, " +
187     PROB + " REAL, " +
188     POSE_VERSION + " TEXT, " +
189     POSE_TYPE + " INT, " +
190     SCALE_X + " REAL DEFAULT 0, " +
191     SCALE_Y + " REAL DEFAULT 0, " +
192     SCALE_WIDTH + " REAL DEFAULT 0, " +
193     SCALE_HEIGHT + " REAL DEFAULT 0, " +
194     ANALYSIS_VERSION + " TEXT)";
195 
196 const std::string CREATE_TAB_ANALYSIS_TOTAL = "CREATE TABLE IF NOT EXISTS " + VISION_TOTAL_TABLE + " (" +
197     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
198     FILE_ID + " INT UNIQUE, " +
199     STATUS + " INT, " +
200     OCR + " INT, " +
201     LABEL + " INT, " +
202     AESTHETICS_SCORE + " INT) ";
203 
204 const std::string CREATE_TAB_ANALYSIS_TOTAL_FOR_ONCREATE = "CREATE TABLE IF NOT EXISTS " + VISION_TOTAL_TABLE + " (" +
205     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
206     FILE_ID + " INT UNIQUE, " +
207     STATUS + " INT, " +
208     OCR + " INT, " +
209     LABEL + " INT, " +
210     AESTHETICS_SCORE + " INT, " +
211     FACE + " INT, " +
212     OBJECT + " INT, " +
213     RECOMMENDATION + " INT, " +
214     SEGMENTATION + " INT, " +
215     COMPOSITION + " INT, " +
216     SALIENCY + " INT, " +
217     HEAD + " INT, " +
218     POSE + " INT, " +
219     GEO + " INT DEFAULT 0) ";
220 
221 const std::string CREATE_TAB_IMAGE_FACE = "CREATE TABLE IF NOT EXISTS " + VISION_IMAGE_FACE_TABLE + " (" +
222     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
223     FILE_ID + " INTEGER, " +
224     FACE_ID + " TEXT, " +
225     TAG_ID +  " TEXT, " +
226     SCALE_X + " REAL, " +
227     SCALE_Y + " REAL, " +
228     SCALE_WIDTH + " REAL, " +
229     SCALE_HEIGHT + " REAL, " +
230     LANDMARKS + " BLOB, " +
231     PITCH + " REAL, " +
232     YAW + " REAL, " +
233     ROLL + " REAL, " +
234     PROB + " REAL, " +
235     TOTAL_FACES + " INTEGER, " +
236     IMAGE_FACE_VERSION + " TEXT, " +
237     IMAGE_FEATURES_VERSION + " TEXT, " +
238     FEATURES + " BLOB, " +
239     FACE_OCCLUSION + " INT, " +
240     ANALYSIS_VERSION + " TEXT, " +
241     BEAUTY_BOUNDER_X + " REAL, " +
242     BEAUTY_BOUNDER_Y + " REAL, " +
243     BEAUTY_BOUNDER_WIDTH + " REAL, " +
244     BEAUTY_BOUNDER_HEIGHT + " REAL, " +
245     FACE_AESTHETICS_SCORE + " REAL, " +
246     BEAUTY_BOUNDER_VERSION + " TEXT DEFAULT '', " +
247     IS_EXCLUDED + " INT DEFAULT 0) ";
248 
249 const std::string CREATE_TAB_VIDEO_FACE = "CREATE TABLE IF NOT EXISTS " + VISION_VIDEO_FACE_TABLE + " (" +
250     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
251     FILE_ID + " INTEGER, " +
252     FACE_ID + " TEXT, " +
253     TAG_ID +  " TEXT, " +
254     SCALE_X + " BLOB, " +
255     SCALE_Y + " BLOB, " +
256     SCALE_WIDTH + " BLOB, " +
257     SCALE_HEIGHT + " BLOB, " +
258     LANDMARKS + " BLOB, " +
259     PITCH + " BLOB, " +
260     YAW + " BLOB, " +
261     ROLL + " BLOB, " +
262     PROB + " BLOB, " +
263     TOTAL_FACES + " INTEGER, " +
264     FRAME_ID + " INT, " +
265     FRAME_TIMESTAMP + " INT, " +
266     TRACKS + " TEXT, " +
267     ALGO_VERSION + " TEXT, " +
268     FEATURES + " BLOB, " +
269     ANALYSIS_VERSION + " TEXT) ";
270 
271 const std::string CREATE_TAB_FACE_TAG = "CREATE TABLE IF NOT EXISTS " + VISION_FACE_TAG_TABLE + " (" +
272     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
273     TAG_ID +  " TEXT UNIQUE, " +
274     TAG_NAME +  " TEXT, " +
275     USER_OPERATION + " INTEGER, " +
276     GROUP_TAG +  " TEXT, " +
277     RENAME_OPERATION + " INTEGER, " +
278     CENTER_FEATURES + " BLOB, " +
279     TAG_VERSION + " TEXT, " +
280     USER_DISPLAY_LEVEL + " INTEGER, " +
281     TAG_ORDER + " INTEGER, " +
282     IS_ME + " INTEGER, " +
283     COVER_URI +  " TEXT, " +
284     COUNT + " INTEGER, " +
285     PORTRAIT_DATE_MODIFY + " BIGINT, " +
286     ALBUM_TYPE + " INTEGER, " +
287     IS_REMOVED + " INTEGER, " +
288     ANALYSIS_VERSION + " TEXT)";
289 
290 const std::string CREATE_ANALYSIS_ALBUM = "CREATE TABLE IF NOT EXISTS " + ANALYSIS_ALBUM_TABLE + " (" +
291     ALBUM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
292     ALBUM_TYPE + " INT, " +
293     ALBUM_SUBTYPE + " INT, " +
294     ALBUM_NAME + " TEXT, " +
295     COVER_URI + " TEXT, " +
296     COUNT + " INT DEFAULT 0, " +
297     DATE_MODIFIED + " BIGINT, " +
298     RANK + " INT) ";
299 
300 const std::string CREATE_ANALYSIS_ALBUM_MAP = "CREATE TABLE IF NOT EXISTS " + ANALYSIS_PHOTO_MAP_TABLE + " (" +
301     MAP_ALBUM + " INT, " +
302     MAP_ASSET + " INT, " +
303     "PRIMARY KEY (" + MAP_ALBUM + "," + MAP_ASSET + ")) ";
304 
305 
306 const std::string INIT_TAB_ANALYSIS_TOTAL = "INSERT INTO " + VISION_TOTAL_TABLE + " (" +
307     FILE_ID + ", " +
308     STATUS + ", " +
309     OCR + ", " +
310     AESTHETICS_SCORE + ", " +
311     LABEL + ") " +
312     "SELECT " + FILE_ID +
313     ", CASE WHEN date_trashed > 0 THEN 2 ELSE 0 END," +
314     " 0," +
315     " CASE WHEN subtype = 1 THEN -1 ELSE 0 END," +
316     " CASE WHEN subtype = 1 THEN -1 ELSE 0 END," +
317     " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE MEDIA_TYPE = 1";
318 
319 // trigger
320 const std::string CREATE_VISION_DELETE_TRIGGER = "CREATE TRIGGER IF NOT EXISTS delete_vision_trigger AFTER DELETE ON " +
321     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
322     " BEGIN " +
323     " UPDATE " + VISION_TOTAL_TABLE +
324     " SET " + STATUS + " = -1 " +
325     " WHERE " + FILE_ID +
326     " = OLD.file_id;" +
327     " END;";
328 
329 const std::string CREATE_VISION_UPDATE_TRIGGER = "CREATE TRIGGER IF NOT EXISTS update_vision_trigger AFTER UPDATE ON " +
330     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
331     " WHEN ((NEW.date_trashed > 0 AND OLD.date_trashed = 0)" +
332     " OR (NEW.date_trashed = 0 AND OLD.date_trashed > 0))" +
333     " AND (NEW.MEDIA_TYPE = 1 OR NEW.MEDIA_TYPE = 2)" +
334     " BEGIN " +
335     " UPDATE " + VISION_TOTAL_TABLE +
336     " SET " + STATUS + " = " +
337     " (CASE WHEN NEW.date_trashed > 0 THEN 2 ELSE 0 END)" +
338     " WHERE file_id = OLD.file_id;" +
339     " END;";
340 
341 const std::string CREATE_VISION_UPDATE_TRIGGER_FOR_ADD_VIDEO_LABEL =
342     "CREATE TRIGGER IF NOT EXISTS update_vision_trigger AFTER UPDATE ON " +
343     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
344     " WHEN ((NEW.date_trashed > 0 AND OLD.date_trashed = 0)" +
345     " OR (NEW.date_trashed = 0 AND OLD.date_trashed > 0))" +
346     " AND (NEW.MEDIA_TYPE = 1 OR NEW.MEDIA_TYPE = 2)" +
347     " BEGIN " +
348     " UPDATE " + VISION_TOTAL_TABLE +
349     " SET " + STATUS + " = " +
350     " (CASE WHEN NEW.date_trashed > 0 THEN 2 ELSE 0 END)" +
351     " WHERE file_id = OLD.file_id;" +
352     " END;";
353 
354 const std::string CREATE_VISION_INSERT_TRIGGER = "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
355     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
356     " WHEN NEW.MEDIA_TYPE = 1" +
357     " BEGIN " +
358     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
359     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
360     AESTHETICS_SCORE + ", " +
361     LABEL + ") " +
362     " VALUES (" +
363     " NEW.file_id, 0, 0," +
364     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
365     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END));" +
366     " END;";
367 
368 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ONCREATE =
369     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
370     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
371     " WHEN (NEW.MEDIA_TYPE = 1 OR NEW.MEDIA_TYPE = 2)" +
372     " BEGIN " +
373     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
374     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
375     SALIENCY + ", " + HEAD + ", " + POSE + ") " +
376     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" + " END;";
377 
378 const std::string DROP_INSERT_VISION_TRIGGER = "DROP TRIGGER IF EXISTS insert_vision_trigger";
379 const std::string DROP_UPDATE_VISION_TRIGGER = "DROP TRIGGER IF EXISTS update_vision_trigger";
380 
381 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_VIDEO_LABEL =
382     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
383     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
384     " WHEN (NEW.MEDIA_TYPE = 1 OR NEW.MEDIA_TYPE = 2)" +
385     " BEGIN " +
386     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
387     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
388     SALIENCY + ", " + HEAD + ", " + POSE + ") " +
389     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" + " END;";
390 
391 const std::string CREATE_INSERT_VISION_TRIGGER_FOR_ADD_FACE =
392     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
393     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
394     " WHEN NEW.MEDIA_TYPE = 1" +
395     " BEGIN " +
396     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
397     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
398     AESTHETICS_SCORE + ", " +
399     LABEL + ", " +
400     FACE + ") " +
401     " VALUES (" +
402     " NEW.file_id, 0, 0," +
403     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
404     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
405     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END));" +
406     " END;";
407 
408 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_AC =
409     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
410     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
411     " WHEN NEW.MEDIA_TYPE = 1" +
412     " BEGIN " +
413     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
414     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
415     AESTHETICS_SCORE + ", " +
416     LABEL + ", " +
417     FACE + ", " +
418     OBJECT + ", " +
419     RECOMMENDATION + ", " +
420     SEGMENTATION + ", " +
421     COMPOSITION + ")" +
422     " VALUES (" +
423     " NEW.file_id, 0, 0," +
424     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
425     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
426     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
427     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
428     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
429     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
430     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END));" +
431     " END;";
432 
433 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_SALIENCY =
434     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
435     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
436     " WHEN NEW.MEDIA_TYPE = 1" +
437     " BEGIN " +
438     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
439     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
440     AESTHETICS_SCORE + ", " +
441     LABEL + ", " +
442     FACE + ", " +
443     OBJECT + ", " +
444     RECOMMENDATION + ", " +
445     SEGMENTATION + ", " +
446     COMPOSITION + "," +
447     SALIENCY + ") " +
448     " VALUES (" +
449     " NEW.file_id, 0, 0," +
450     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
451     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
452     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
453     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
454     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
455     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
456     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
457     " 0 );" +
458     " END;";
459 
460 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_UPDATE_SPEC =
461     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
462     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
463     " WHEN NEW.MEDIA_TYPE = 1" +
464     " BEGIN " +
465     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
466     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
467     SALIENCY + ") " +
468     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" + " END;";
469 
470 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_HEAD_AND_POSE =
471     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
472     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
473     " WHEN NEW.MEDIA_TYPE = 1" +
474     " BEGIN " +
475     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
476     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
477     SALIENCY + ", " + HEAD + ", " + POSE + ") " +
478     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" + " END;";
479 
480 const std::string ADD_FACE_STATUS_COLUMN = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " + FACE + " INT";
481 const std::string UPDATE_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " + FACE +
482     " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE subtype != 1)";
483 const std::string UPDATE_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + FACE +
484     " = -1 WHERE " + FACE + " IS NULL";
485 const std::string IMAGE_FACE_INDEX = "image_face_index";
486 const std::string CREATE_IMAGE_FACE_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + IMAGE_FACE_INDEX + " ON " +
487     VISION_IMAGE_FACE_TABLE + " (" + FILE_ID + "," + FACE_ID + ")";
488 
489 const std::string VIDEO_FACE_INDEX = "video_face_index";
490 const std::string CREATE_VIDEO_FACE_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + VIDEO_FACE_INDEX + " ON " +
491     VISION_VIDEO_FACE_TABLE + " (" + FILE_ID + "," + FACE_ID + ")";
492 
493 const std::string ADD_SALIENCY_STATUS_COLUMN = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " + SALIENCY + " INT";
494 const std::string UPDATE_SALIENCY_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE +
495     " SET " + STATUS + " = 0, " + SALIENCY + " = 0 WHERE " +
496     FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE media_type = 1)";
497 const std::string UPDATE_SALIENCY_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + SALIENCY +
498     " = -1 WHERE " + SALIENCY + " IS NULL";
499 
500 const std::string AC_ADD_OBJECT_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
501     OBJECT + " INT";
502 const std::string AC_UPDATE_OBJECT_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " + OBJECT +
503     " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE subtype != 1)";
504 const std::string AC_UPDATE_OBJECT_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + OBJECT +
505     " = -1 WHERE " + OBJECT + " IS NULL";
506 const std::string OBJECT_INDEX = "object_index";
507 const std::string CREATE_OBJECT_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + OBJECT_INDEX + " ON " +
508     VISION_OBJECT_TABLE + " (" + FILE_ID + "," + OBJECT_ID + ")";
509 
510 const std::string AC_ADD_RECOMMENDATION_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
511     RECOMMENDATION + " INT";
512 const std::string AC_UPDATE_RECOMMENDATION_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
513     RECOMMENDATION + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
514     " WHERE subtype != 1)";
515 const std::string AC_UPDATE_RECOMMENDATION_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " +
516     RECOMMENDATION + " = -1 WHERE " + RECOMMENDATION + " IS NULL";
517 const std::string RECOMMENDATION_INDEX = "recommendation_index";
518 const std::string CREATE_RECOMMENDATION_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + RECOMMENDATION_INDEX + " ON " +
519     VISION_RECOMMENDATION_TABLE + " (" + FILE_ID + "," + RECOMMENDATION_ID + ")";
520 
521 const std::string AC_ADD_SEGMENTATION_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
522     SEGMENTATION + " INT";
523 const std::string AC_UPDATE_SEGMENTATION_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
524     SEGMENTATION + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
525     " WHERE subtype != 1)";
526 const std::string AC_UPDATE_SEGMENTATION_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " +
527     SEGMENTATION + " = -1 WHERE " + SEGMENTATION + " IS NULL";
528 
529 const std::string AC_ADD_COMPOSITION_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
530     COMPOSITION + " INT";
531 const std::string AC_UPDATE_COMPOSITION_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
532     COMPOSITION + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
533     " WHERE subtype != 1)";
534 const std::string AC_UPDATE_COMPOSITION_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " +
535     COMPOSITION + " = -1 WHERE " + COMPOSITION + " IS NULL";
536 const std::string COMPOSITION_INDEX = "composition_index";
537 const std::string CREATE_COMPOSITION_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + COMPOSITION_INDEX + " ON " +
538     VISION_COMPOSITION_TABLE + " (" + FILE_ID + "," + COMPOSITION_ID + ")";
539 
540 const std::string ADD_HEAD_STATUS_COLUMN = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " + HEAD + " INT";
541 const std::string UPDATE_HEAD_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " + HEAD +
542     " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
543     " WHERE media_type = 1)";
544 const std::string UPDATE_HEAD_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + HEAD + " = -1 WHERE " +
545     HEAD + " IS NULL";
546 const std::string HEAD_INDEX = "head_index";
547 const std::string CREATE_HEAD_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + HEAD_INDEX + " ON " + VISION_HEAD_TABLE +
548     " (" + FILE_ID + "," + HEAD_ID + ")";
549 } // namespace Media
550 } // namespace OHOS
551 #endif  // FRAMEWORKS_SERVICES_MEDIA_MULTI_STAGES_CAPTURE_INCLUDE_VISION_DB_SQLS_H