• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
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 "search_column.h"
20 #include "vision_aesthetics_score_column.h"
21 #include "vision_video_aesthetics_score_column.h"
22 #include "vision_album_column.h"
23 #include "vision_column_comm.h"
24 #include "vision_column.h"
25 #include "vision_composition_column.h"
26 #include "vision_face_tag_column.h"
27 #include "vision_head_column.h"
28 #include "vision_image_face_column.h"
29 #include "vision_video_face_column.h"
30 #include "vision_label_column.h"
31 #include "vision_object_column.h"
32 #include "vision_ocr_column.h"
33 #include "vision_photo_map_column.h"
34 #include "vision_pose_column.h"
35 #include "vision_recommendation_column.h"
36 #include "vision_saliency_detect_column.h"
37 #include "vision_segmentation_column.h"
38 #include "vision_total_column.h"
39 #include "vision_video_label_column.h"
40 #include "vision_multi_crop_column.h"
41 
42 namespace OHOS {
43 namespace Media {
44 const std::string CREATE_TAB_ANALYSIS_OCR = "CREATE TABLE IF NOT EXISTS " + VISION_OCR_TABLE + " (" +
45     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
46     FILE_ID + " INT UNIQUE, " +
47     OCR_TEXT + " TEXT, " +
48     OCR_VERSION + " TEXT, " +
49     OCR_TEXT_MSG + " TEXT, " +
50     OCR_WIDTH + " INT, " +
51     OCR_HEIGHT + " INT, " +
52     OCR_CARD_TEXT + " TEXT, " +
53     OCR_CARD_TEXT_MSG + " TEXT, " +
54     ANALYSIS_VERSION + " TEXT)";
55 
56 const std::string CREATE_TAB_ANALYSIS_LABEL = "CREATE TABLE IF NOT EXISTS " + VISION_LABEL_TABLE + " (" +
57     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
58     FILE_ID + " INT UNIQUE, " +
59     CATEGORY_ID + " INT, " +
60     SUB_LABEL + " TEXT, " +
61     PROB + " REAL, " +
62     FEATURE + " TEXT, " +
63     SIM_RESULT + " TEXT, " +
64     LABEL_VERSION + " TEXT, " +
65     SALIENCY_SUB_PROB + " TEXT, " +
66     ANALYSIS_VERSION + " TEXT, " +
67     SEARCH_TAG_TYPE + " TEXT, " +
68     SEARCH_TAG_VECTOR + " BLOB)";
69 
70 const std::string CREATE_TAB_ANALYSIS_VIDEO_LABEL = "CREATE TABLE IF NOT EXISTS " + VISION_VIDEO_LABEL_TABLE + " (" +
71     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
72     FILE_ID + " INT, " +
73     CATEGORY_ID + " TEXT, " +
74     CONFIDENCE_PROBABILITY + " REAL, " +
75     SUB_CATEGORY + " TEXT, " +
76     SUB_CONFIDENCE_PROB + " REAL, " +
77     SUB_LABEL + " TEXT, " +
78     SUB_LABEL_PROB + " REAL, " +
79     SUB_LABEL_TYPE + " TEXT, " +
80     TRACKS + " TEXT, " +
81     VIDEO_PART_FEATURE + " BLOB, " +
82     FILTER_TAG + " TEXT, " +
83     ALGO_VERSION + " TEXT, " +
84     ANALYSIS_VERSION + " TEXT, " +
85     TRIGGER_GENERATE_THUMBNAIL + " INT DEFAULT 0)";
86 
87 const std::string CREATE_TAB_ANALYSIS_AESTHETICS = "CREATE TABLE IF NOT EXISTS " + VISION_AESTHETICS_TABLE + " (" +
88     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
89     FILE_ID + " INT UNIQUE, " +
90     AESTHETICS_SCORE + " INT, " +
91     AESTHETICS_VERSION + " TEXT, " +
92     PROB + " REAL, " +
93     ANALYSIS_VERSION + " TEXT, " +
94     AESTHETICS_ALL_VERSION + " TEXT, " +
95     AESTHETICS_SCORE_ALL + " INT NOT NULL DEFAULT 0, " +
96     IS_FILTERED_HARD + " BOOLEAN NOT NULL DEFAULT 0, " +
97     CLARITY_SCORE_ALL + " DOUBLE NOT NULL DEFAULT 0, " +
98     SATURATION_SCORE_ALL + " DOUBLE NOT NULL DEFAULT 0, " +
99     LUMINANCE_SCORE_ALL + " DOUBLE NOT NULL DEFAULT 0, " +
100     SEMANTICS_SCORE + " DOUBLE NOT NULL DEFAULT 0, " +
101     IS_BLACK_WHITE_STRIPE + " BOOLEAN NOT NULL DEFAULT 0, " +
102     IS_BLURRY + " BOOLEAN NOT NULL DEFAULT 0, " +
103     IS_MOSAIC + " BOOLEAN NOT NULL DEFAULT 0)";
104 
105 const std::string CREATE_TAB_VIDEO_ANALYSIS_AESTHETICS = "CREATE TABLE IF NOT EXISTS " +
106     VISION_VIDEO_AESTHETICS_TABLE + " (" +
107     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
108     FILE_ID + " INT UNIQUE, " +
109     VIDEO_AESTHETICS_SCORE + " INT, " +
110     VIDEO_AESTHETICS_VERSION + " TEXT, " +
111     PROB + " REAL)";
112 
113 const std::string CREATE_TAB_ANALYSIS_SALIENCY_DETECT = "CREATE TABLE IF NOT EXISTS " + VISION_SALIENCY_TABLE + " (" +
114     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
115     FILE_ID + " INT UNIQUE, " +
116     SALIENCY_X + " REAL, " +
117     SALIENCY_Y + " REAL, " +
118     SALIENCY_VERSION + " TEXT, " +
119     ANALYSIS_VERSION + " TEXT)";
120 
121 const std::string CREATE_TAB_ANALYSIS_OBJECT = "CREATE TABLE IF NOT EXISTS " + VISION_OBJECT_TABLE + " (" +
122     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
123     FILE_ID + " INT, " +
124     OBJECT_ID + " INT, " +
125     OBJECT_LABEL + " INT, " +
126     OBJECT_SCALE_X + " INT, " +
127     OBJECT_SCALE_Y + " INT, " +
128     OBJECT_SCALE_WIDTH + " INT, " +
129     OBJECT_SCALE_HEIGHT + " INT, " +
130     PROB + " REAL, " +
131     OBJECT_VERSION + " TEXT, " +
132     SCALE_X + " REAL DEFAULT 0, " +
133     SCALE_Y + " REAL DEFAULT 0, " +
134     SCALE_WIDTH + " REAL DEFAULT 0, " +
135     SCALE_HEIGHT + " REAL DEFAULT 0, " +
136     ANALYSIS_VERSION + " TEXT)";
137 
138 const std::string CREATE_TAB_ANALYSIS_RECOMMENDATION = "CREATE TABLE IF NOT EXISTS " + VISION_RECOMMENDATION_TABLE +
139     " (" +
140     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
141     FILE_ID + " INT, " +
142     RECOMMENDATION_ID + " INT, " +
143     RECOMMENDATION_RESOLUTION + " TEXT, " +
144     RECOMMENDATION_SCALE_X + " INT, " +
145     RECOMMENDATION_SCALE_Y + " INT, " +
146     RECOMMENDATION_SCALE_WIDTH + " INT, " +
147     RECOMMENDATION_SCALE_HEIGHT + " INT, " +
148     RECOMMENDATION_VERSION + " TEXT, " +
149     SCALE_X + " REAL DEFAULT 0, " +
150     SCALE_Y + " REAL DEFAULT 0, " +
151     SCALE_WIDTH + " REAL DEFAULT 0, " +
152     SCALE_HEIGHT + " REAL DEFAULT 0, " +
153     MOVEMENT_CROP + " TEXT, " +
154     MOVEMENT_VERSION + " TEXT, " +
155     ANALYSIS_VERSION + " TEXT)";
156 
157 const std::string CREATE_TAB_ANALYSIS_SEGMENTATION = "CREATE TABLE IF NOT EXISTS " + VISION_SEGMENTATION_TABLE + " (" +
158     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
159     FILE_ID + " INT UNIQUE, " +
160     SEGMENTATION_AREA + " TEXT, " +
161     SEGMENTATION_NAME + " INT, " +
162     PROB + " REAL, " +
163     SEGMENTATION_VERSION + " TEXT, " +
164     ANALYSIS_VERSION + " TEXT)";
165 
166 const std::string CREATE_TAB_ANALYSIS_COMPOSITION = "CREATE TABLE IF NOT EXISTS " + VISION_COMPOSITION_TABLE + " (" +
167     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
168     FILE_ID + " INT, " +
169     COMPOSITION_ID + " INT, " +
170     COMPOSITION_RESOLUTION + " TEXT, " +
171     CLOCK_STYLE + " INT, " +
172     CLOCK_LOCATION_X + " INT, " +
173     CLOCK_LOCATION_Y + " INT, " +
174     CLOCK_COLOUR + " TEXT, " +
175     COMPOSITION_SCALE_X + " INT, " +
176     COMPOSITION_SCALE_Y + " INT, " +
177     COMPOSITION_SCALE_WIDTH + " INT, " +
178     COMPOSITION_SCALE_HEIGHT + " INT, " +
179     COMPOSITION_VERSION + " TEXT, " +
180     SCALE_X + " REAL DEFAULT 0, " +
181     SCALE_Y + " REAL DEFAULT 0, " +
182     SCALE_WIDTH + " REAL DEFAULT 0, " +
183     SCALE_HEIGHT + " REAL DEFAULT 0, " +
184     ANALYSIS_VERSION + " TEXT)";
185 
186 const std::string CREATE_TAB_ANALYSIS_HEAD = "CREATE TABLE IF NOT EXISTS " + VISION_HEAD_TABLE + " (" +
187     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
188     FILE_ID + " INT, " +
189     HEAD_ID + " INT, " +
190     HEAD_LABEL + " INT, " +
191     HEAD_SCALE_X + " INT, " +
192     HEAD_SCALE_Y + " INT, " +
193     HEAD_SCALE_WIDTH + " INT, " +
194     HEAD_SCALE_HEIGHT + " INT, " +
195     PROB + " REAL, " +
196     HEAD_VERSION + " TEXT, " +
197     SCALE_X + " REAL DEFAULT 0, " +
198     SCALE_Y + " REAL DEFAULT 0, " +
199     SCALE_WIDTH + " REAL DEFAULT 0, " +
200     SCALE_HEIGHT + " REAL DEFAULT 0, " +
201     ANALYSIS_VERSION + " TEXT)";
202 
203 const std::string CREATE_TAB_ANALYSIS_POSE = "CREATE TABLE IF NOT EXISTS " + VISION_POSE_TABLE + " (" +
204     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
205     FILE_ID + " INT, " +
206     POSE_ID + " INT, " +
207     POSE_LANDMARKS + " BLOB, " +
208     POSE_SCALE_X + " INT, " +
209     POSE_SCALE_Y + " INT, " +
210     POSE_SCALE_WIDTH + " INT, " +
211     POSE_SCALE_HEIGHT + " INT, " +
212     PROB + " REAL, " +
213     POSE_VERSION + " TEXT, " +
214     POSE_TYPE + " INT, " +
215     SCALE_X + " REAL DEFAULT 0, " +
216     SCALE_Y + " REAL DEFAULT 0, " +
217     SCALE_WIDTH + " REAL DEFAULT 0, " +
218     SCALE_HEIGHT + " REAL DEFAULT 0, " +
219     ANALYSIS_VERSION + " TEXT)";
220 
221 const std::string CREATE_TAB_ANALYSIS_TOTAL = "CREATE TABLE IF NOT EXISTS " + VISION_TOTAL_TABLE + " (" +
222     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
223     FILE_ID + " INT UNIQUE, " +
224     STATUS + " INT, " +
225     OCR + " INT, " +
226     LABEL + " INT, " +
227     AESTHETICS_SCORE + " INT) ";
228 
229 const std::string CREATE_TAB_ANALYSIS_TOTAL_FOR_ONCREATE = "CREATE TABLE IF NOT EXISTS " + VISION_TOTAL_TABLE + " (" +
230     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
231     FILE_ID + " INT UNIQUE, " +
232     STATUS + " INT, " +
233     OCR + " INT, " +
234     LABEL + " INT, " +
235     AESTHETICS_SCORE + " INT, " +
236     FACE + " INT, " +
237     OBJECT + " INT, " +
238     RECOMMENDATION + " INT, " +
239     SEGMENTATION + " INT, " +
240     COMPOSITION + " INT, " +
241     SALIENCY + " INT, " +
242     HEAD + " INT, " +
243     POSE + " INT, " +
244     GEO + " INT DEFAULT 0, " +
245     AESTHETICS_SCORE_ALL_STATUS + " INT NOT NULL DEFAULT 0, " +
246     PRIORITY + " INT NOT NULL DEFAULT 1) ";
247 
248 const std::string CREATE_TAB_IMAGE_FACE = "CREATE TABLE IF NOT EXISTS " + VISION_IMAGE_FACE_TABLE + " (" +
249     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
250     FILE_ID + " INTEGER, " +
251     FACE_ID + " TEXT, " +
252     TAG_ID +  " TEXT, " +
253     SCALE_X + " REAL, " +
254     SCALE_Y + " REAL, " +
255     SCALE_WIDTH + " REAL, " +
256     SCALE_HEIGHT + " REAL, " +
257     LANDMARKS + " BLOB, " +
258     PITCH + " REAL, " +
259     YAW + " REAL, " +
260     ROLL + " REAL, " +
261     PROB + " REAL, " +
262     TOTAL_FACES + " INTEGER, " +
263     IMAGE_FACE_VERSION + " TEXT, " +
264     IMAGE_FEATURES_VERSION + " TEXT, " +
265     FEATURES + " BLOB, " +
266     FACE_OCCLUSION + " INT, " +
267     ANALYSIS_VERSION + " TEXT, " +
268     BEAUTY_BOUNDER_X + " REAL, " +
269     BEAUTY_BOUNDER_Y + " REAL, " +
270     BEAUTY_BOUNDER_WIDTH + " REAL, " +
271     BEAUTY_BOUNDER_HEIGHT + " REAL, " +
272     FACE_AESTHETICS_SCORE + " REAL, " +
273     BEAUTY_BOUNDER_VERSION + " TEXT DEFAULT '', " +
274     IS_EXCLUDED + " INT DEFAULT 0, " +
275     IS_PRIMARY_FACE + " REAL, " +
276     JOINT_BEAUTY_BOUNDER_X + " REAL, " +
277     JOINT_BEAUTY_BOUNDER_Y + " REAL, " +
278     JOINT_BEAUTY_BOUNDER_WIDTH + " REAL, " +
279     JOINT_BEAUTY_BOUNDER_HEIGHT + " REAL, " +
280     GROUP_VERSION + " TEXT )";
281 
282 const std::string CREATE_TAB_VIDEO_FACE = "CREATE TABLE IF NOT EXISTS " + VISION_VIDEO_FACE_TABLE + " (" +
283     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
284     FILE_ID + " INTEGER, " +
285     FACE_ID + " TEXT, " +
286     TAG_ID +  " TEXT, " +
287     SCALE_X + " BLOB, " +
288     SCALE_Y + " BLOB, " +
289     SCALE_WIDTH + " BLOB, " +
290     SCALE_HEIGHT + " BLOB, " +
291     LANDMARKS + " BLOB, " +
292     PITCH + " BLOB, " +
293     YAW + " BLOB, " +
294     ROLL + " BLOB, " +
295     PROB + " BLOB, " +
296     TOTAL_FACES + " INTEGER, " +
297     FRAME_ID + " BLOB, " +
298     FRAME_TIMESTAMP + " BLOB, " +
299     TRACKS + " TEXT, " +
300     ALGO_VERSION + " TEXT, " +
301     FEATURES + " BLOB, " +
302     ANALYSIS_VERSION + " TEXT) ";
303 
304 const std::string CREATE_TAB_FACE_TAG = "CREATE TABLE IF NOT EXISTS " + VISION_FACE_TAG_TABLE + " (" +
305     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
306     TAG_ID +  " TEXT UNIQUE, " +
307     TAG_NAME +  " TEXT, " +
308     USER_OPERATION + " INTEGER, " +
309     GROUP_TAG +  " TEXT, " +
310     RENAME_OPERATION + " INTEGER, " +
311     CENTER_FEATURES + " BLOB, " +
312     TAG_VERSION + " TEXT, " +
313     USER_DISPLAY_LEVEL + " INTEGER, " +
314     TAG_ORDER + " INTEGER, " +
315     IS_ME + " INTEGER, " +
316     COVER_URI +  " TEXT, " +
317     COUNT + " INTEGER, " +
318     PORTRAIT_DATE_MODIFY + " BIGINT, " +
319     ALBUM_TYPE + " INTEGER, " +
320     IS_REMOVED + " INTEGER, " +
321     ANALYSIS_VERSION + " TEXT)";
322 
323 const std::string CREATE_ANALYSIS_ALBUM = "CREATE TABLE IF NOT EXISTS " + ANALYSIS_ALBUM_TABLE + " (" +
324     ALBUM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
325     ALBUM_TYPE + " INT, " +
326     ALBUM_SUBTYPE + " INT, " +
327     ALBUM_NAME + " TEXT, " +
328     COVER_URI + " TEXT, " +
329     COUNT + " INT DEFAULT 0, " +
330     DATE_MODIFIED + " BIGINT, " +
331     RANK + " INT) ";
332 
333 const std::string CREATE_ANALYSIS_ALBUM_MAP = "CREATE TABLE IF NOT EXISTS " + ANALYSIS_PHOTO_MAP_TABLE + " (" +
334     MAP_ALBUM + " INT, " +
335     MAP_ASSET + " INT, " +
336     ORDER_POSITION + " INT, " +
337     "PRIMARY KEY (" + MAP_ALBUM + "," + MAP_ASSET + ")) ";
338 
339 const std::string CREATE_TAB_ANALYSIS_ALBUM_TOTAL = "CREATE TABLE IF NOT EXISTS " +
340     VISION_ANALYSIS_ALBUM_TOTAL_TABLE + " (" +
341     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
342     FILE_ID + " INT, " +
343     STATUS + " INT DEFAULT 0, " +
344     GEO + " INT DEFAULT 0, " +
345     LABEL + " INT DEFAULT 0, " +
346     FACE + " INT DEFAULT 0) ";
347 
348 const std::string INIT_TAB_ANALYSIS_ALBUM_TOTAL = "INSERT INTO " + VISION_ANALYSIS_ALBUM_TOTAL_TABLE + " (" +
349     FILE_ID + ", " +
350     STATUS + ", " +
351     GEO + ", " +
352     LABEL + ", " +
353     FACE + ") " +
354     "SELECT " + FILE_ID +
355     ", 0, 0, 0, 0" +
356     " FROM " + VISION_TOTAL_TABLE;
357 
358 
359 const std::string INIT_TAB_ANALYSIS_TOTAL = "INSERT INTO " + VISION_TOTAL_TABLE + " (" +
360     FILE_ID + ", " +
361     STATUS + ", " +
362     OCR + ", " +
363     AESTHETICS_SCORE + ", " +
364     LABEL + ") " +
365     "SELECT " + FILE_ID +
366     ", CASE WHEN date_trashed > 0 THEN 2 ELSE 0 END," +
367     " 0," +
368     " CASE WHEN subtype = 1 THEN -1 ELSE 0 END," +
369     " CASE WHEN subtype = 1 THEN -1 ELSE 0 END," +
370     " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE MEDIA_TYPE = 1";
371 
372 // trigger
373 const std::string CREATE_VISION_DELETE_TRIGGER = "CREATE TRIGGER IF NOT EXISTS delete_vision_trigger AFTER DELETE ON " +
374     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
375     " BEGIN " +
376     " UPDATE " + VISION_TOTAL_TABLE +
377     " SET " + STATUS + " = -1 " +
378     " WHERE " + FILE_ID +
379     " = OLD.file_id;" +
380     " END;";
381 
382 const std::string CREATE_TOTAL_INSERT_TRIGGER_FOR_ADD_ANALYSIS_ALBUM_TOTAL =
383     "CREATE TRIGGER IF NOT EXISTS insert_vision_total_trigger AFTER INSERT ON " +
384     VISION_TOTAL_TABLE + " FOR EACH ROW " +
385     " BEGIN " +
386    " INSERT INTO " + VISION_ANALYSIS_ALBUM_TOTAL_TABLE +" (" +
387     FILE_ID + ", " + STATUS + ", " + GEO + ", " +
388     LABEL + ", " +
389     FACE + ") " +
390     " VALUES (" +
391     " NEW.file_id, 0, 0, 0, 0);" +
392     " END;";
393 
394 const std::string CREATE_VISION_UPDATE_TRIGGER_FOR_UPDATE_ANALYSIS_ALBUM_TOTAL_STATUS =
395     "CREATE TRIGGER IF NOT EXISTS update_total_vision_trigger AFTER UPDATE OF status ON " +
396     VISION_TOTAL_TABLE + " FOR EACH ROW " +
397     " WHEN NEW.status = -1" +
398     " BEGIN " +
399     " UPDATE " + VISION_ANALYSIS_ALBUM_TOTAL_TABLE +
400     " SET " + STATUS + " = -1" +
401     " WHERE file_id = OLD.file_id;" +
402     " END;";
403 
404 const std::string CREATE_VISION_UPDATE_TRIGGER = "CREATE TRIGGER IF NOT EXISTS update_vision_trigger AFTER UPDATE ON " +
405     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
406     " WHEN ((NEW.date_trashed > 0 AND OLD.date_trashed = 0)" +
407     " OR (NEW.date_trashed = 0 AND OLD.date_trashed > 0))" +
408     " AND (NEW.MEDIA_TYPE = 1 OR NEW.MEDIA_TYPE = 2)" +
409     " BEGIN " +
410     " UPDATE " + VISION_TOTAL_TABLE +
411     " SET " + STATUS + " = " +
412     " (CASE WHEN NEW.date_trashed > 0 THEN 2 ELSE 0 END)" +
413     " WHERE file_id = OLD.file_id;" +
414     " END;";
415 
416 const std::string CREATE_VISION_UPDATE_TRIGGER_FOR_ADD_VIDEO_LABEL =
417     "CREATE TRIGGER IF NOT EXISTS update_vision_trigger AFTER UPDATE ON " +
418     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
419     " WHEN ((NEW.date_trashed > 0 AND OLD.date_trashed = 0)" +
420     " OR (NEW.date_trashed = 0 AND OLD.date_trashed > 0))" +
421     " AND (NEW.MEDIA_TYPE = 1 OR NEW.MEDIA_TYPE = 2)" +
422     " BEGIN " +
423     " UPDATE " + VISION_TOTAL_TABLE +
424     " SET " + STATUS + " = " +
425     " (CASE WHEN NEW.date_trashed > 0 THEN 2 ELSE 0 END)" +
426     " WHERE file_id = OLD.file_id;" +
427     " END;";
428 
429 const std::string CREATE_VISION_INSERT_TRIGGER = "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
430     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
431     " WHEN NEW.MEDIA_TYPE = 1" +
432     " BEGIN " +
433     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
434     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
435     AESTHETICS_SCORE + ", " +
436     LABEL + ") " +
437     " VALUES (" +
438     " NEW.file_id, 0, 0," +
439     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
440     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END));" +
441     " END;";
442 
443 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ONCREATE =
444     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
445     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
446     " WHEN (NEW.MEDIA_TYPE = 1 OR NEW.MEDIA_TYPE = 2)" +
447     " BEGIN " +
448     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
449     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
450     SALIENCY + ", " + HEAD + ", " + POSE + ") " +
451     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" +
452     " END;";
453 
454 const std::string DROP_INSERT_VISION_TRIGGER = "DROP TRIGGER IF EXISTS insert_vision_trigger";
455 const std::string DROP_UPDATE_VISION_TRIGGER = "DROP TRIGGER IF EXISTS update_vision_trigger";
456 
457 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_VIDEO_LABEL =
458     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
459     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
460     " WHEN (NEW.MEDIA_TYPE = 1 OR NEW.MEDIA_TYPE = 2)" +
461     " BEGIN " +
462     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
463     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
464     SALIENCY + ", " + HEAD + ", " + POSE + ") " +
465     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" + " END;";
466 
467 const std::string CREATE_INSERT_VISION_TRIGGER_FOR_ADD_FACE =
468     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
469     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
470     " WHEN NEW.MEDIA_TYPE = 1" +
471     " BEGIN " +
472     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
473     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
474     AESTHETICS_SCORE + ", " +
475     LABEL + ", " +
476     FACE + ") " +
477     " VALUES (" +
478     " NEW.file_id, 0, 0," +
479     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
480     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
481     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END));" +
482     " END;";
483 
484 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_AC =
485     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
486     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
487     " WHEN NEW.MEDIA_TYPE = 1" +
488     " BEGIN " +
489     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
490     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
491     AESTHETICS_SCORE + ", " +
492     LABEL + ", " +
493     FACE + ", " +
494     OBJECT + ", " +
495     RECOMMENDATION + ", " +
496     SEGMENTATION + ", " +
497     COMPOSITION + ")" +
498     " VALUES (" +
499     " NEW.file_id, 0, 0," +
500     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
501     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
502     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
503     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
504     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
505     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
506     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END));" +
507     " END;";
508 
509 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_SALIENCY =
510     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
511     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
512     " WHEN NEW.MEDIA_TYPE = 1" +
513     " BEGIN " +
514     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
515     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
516     AESTHETICS_SCORE + ", " +
517     LABEL + ", " +
518     FACE + ", " +
519     OBJECT + ", " +
520     RECOMMENDATION + ", " +
521     SEGMENTATION + ", " +
522     COMPOSITION + "," +
523     SALIENCY + ") " +
524     " VALUES (" +
525     " NEW.file_id, 0, 0," +
526     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
527     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
528     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
529     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
530     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
531     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
532     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
533     " 0 );" +
534     " END;";
535 
536 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_UPDATE_SPEC =
537     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
538     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
539     " WHEN NEW.MEDIA_TYPE = 1" +
540     " BEGIN " +
541     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
542     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
543     SALIENCY + ") " +
544     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" + " END;";
545 
546 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_HEAD_AND_POSE =
547     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
548     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
549     " WHEN NEW.MEDIA_TYPE = 1" +
550     " BEGIN " +
551     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
552     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
553     SALIENCY + ", " + HEAD + ", " + POSE + ") " +
554     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" + " END;";
555 
556 const std::string ADD_FACE_STATUS_COLUMN = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " + FACE + " INT";
557 const std::string UPDATE_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " + FACE +
558     " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE subtype != 1)";
559 const std::string UPDATE_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + FACE +
560     " = -1 WHERE " + FACE + " IS NULL";
561 const std::string IMAGE_FACE_INDEX = "image_face_index";
562 const std::string CREATE_IMAGE_FACE_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + IMAGE_FACE_INDEX + " ON " +
563     VISION_IMAGE_FACE_TABLE + " (" + FILE_ID + "," + FACE_ID + ")";
564 
565 const std::string IMAGE_FACE_TAG_ID_INDEX = "tag_id_index";
566 const std::string CREATE_IMAGE_FACE_TAG_ID_INDEX = "CREATE INDEX IF NOT EXISTS " + IMAGE_FACE_TAG_ID_INDEX +
567     " ON " + VISION_IMAGE_FACE_TABLE + " (" + TAG_ID + ")" + " WHERE " + TAG_ID + " LIKE " + "'ser%'";
568 
569 const std::string VIDEO_FACE_INDEX = "video_face_index";
570 const std::string CREATE_VIDEO_FACE_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + VIDEO_FACE_INDEX + " ON " +
571     VISION_VIDEO_FACE_TABLE + " (" + FILE_ID + "," + FACE_ID + ")";
572 
573 const std::string ADD_SALIENCY_STATUS_COLUMN = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " + SALIENCY + " INT";
574 const std::string UPDATE_SALIENCY_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE +
575     " SET " + STATUS + " = 0, " + SALIENCY + " = 0 WHERE " +
576     FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE media_type = 1)";
577 const std::string UPDATE_SALIENCY_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + SALIENCY +
578     " = -1 WHERE " + SALIENCY + " IS NULL";
579 
580 const std::string AC_ADD_OBJECT_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
581     OBJECT + " INT";
582 const std::string AC_UPDATE_OBJECT_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " + OBJECT +
583     " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE subtype != 1)";
584 const std::string AC_UPDATE_OBJECT_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + OBJECT +
585     " = -1 WHERE " + OBJECT + " IS NULL";
586 const std::string OBJECT_INDEX = "object_index";
587 const std::string CREATE_OBJECT_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + OBJECT_INDEX + " ON " +
588     VISION_OBJECT_TABLE + " (" + FILE_ID + "," + OBJECT_ID + ")";
589 
590 const std::string AC_ADD_RECOMMENDATION_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
591     RECOMMENDATION + " INT";
592 const std::string AC_UPDATE_RECOMMENDATION_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
593     RECOMMENDATION + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
594     " WHERE subtype != 1)";
595 const std::string AC_UPDATE_RECOMMENDATION_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " +
596     RECOMMENDATION + " = -1 WHERE " + RECOMMENDATION + " IS NULL";
597 const std::string RECOMMENDATION_INDEX = "recommendation_index";
598 const std::string CREATE_RECOMMENDATION_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + RECOMMENDATION_INDEX + " ON " +
599     VISION_RECOMMENDATION_TABLE + " (" + FILE_ID + "," + RECOMMENDATION_ID + ")";
600 
601 const std::string AC_ADD_SEGMENTATION_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
602     SEGMENTATION + " INT";
603 const std::string AC_UPDATE_SEGMENTATION_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
604     SEGMENTATION + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
605     " WHERE subtype != 1)";
606 const std::string AC_UPDATE_SEGMENTATION_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " +
607     SEGMENTATION + " = -1 WHERE " + SEGMENTATION + " IS NULL";
608 
609 const std::string AC_ADD_COMPOSITION_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
610     COMPOSITION + " INT";
611 const std::string AC_UPDATE_COMPOSITION_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
612     COMPOSITION + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
613     " WHERE subtype != 1)";
614 const std::string AC_UPDATE_COMPOSITION_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " +
615     COMPOSITION + " = -1 WHERE " + COMPOSITION + " IS NULL";
616 const std::string COMPOSITION_INDEX = "composition_index";
617 const std::string CREATE_COMPOSITION_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + COMPOSITION_INDEX + " ON " +
618     VISION_COMPOSITION_TABLE + " (" + FILE_ID + "," + COMPOSITION_ID + ")";
619 
620 const std::string ADD_HEAD_STATUS_COLUMN = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " + HEAD + " INT";
621 const std::string UPDATE_HEAD_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " + HEAD +
622     " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
623     " WHERE media_type = 1)";
624 const std::string UPDATE_HEAD_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + HEAD + " = -1 WHERE " +
625     HEAD + " IS NULL";
626 const std::string HEAD_INDEX = "head_index";
627 const std::string CREATE_HEAD_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + HEAD_INDEX + " ON " + VISION_HEAD_TABLE +
628     " (" + FILE_ID + "," + HEAD_ID + ")";
629 
630 const std::string UPDATE_VIDEO_LABEL_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
631     LABEL + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
632     " WHERE media_type = 2)";
633 const std::string UPDATE_SEARCH_INDEX_FOR_VIDEO = "UPDATE " + SEARCH_TOTAL_TABLE + " SET " + TBL_SEARCH_PHOTO_STATUS +
634     " = 2, " + TBL_SEARCH_CV_STATUS + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " +
635     PhotoColumn::PHOTOS_TABLE + " WHERE media_type = 2)";
636 const std::string MAP_ASSET_INDEX = "map_asset_index";
637 const std::string CREATE_ANALYSIS_PHOTO_MAP_MAP_ASSET_INDEX = "CREATE INDEX IF NOT EXISTS " + MAP_ASSET_INDEX + " ON " +
638     ANALYSIS_PHOTO_MAP_TABLE + " (" + MAP_ASSET + ")";
639 } // namespace Media
640 } // namespace OHOS
641 #endif  // FRAMEWORKS_SERVICES_MEDIA_MULTI_STAGES_CAPTURE_INCLUDE_VISION_DB_SQLS_H