• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
1 /*
2  * Copyright (C) 2023 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 MEDIALIBRARY_VISION_COLUMN_H
17 #define MEDIALIBRARY_VISION_COLUMN_H
18 
19 #include "media_column.h"
20 #include "userfile_manager_types.h"
21 #include "userfilemgr_uri.h"
22 
23 namespace OHOS {
24 namespace Media {
25 // table name
26 const std::string VISION_OCR_TABLE = "tab_analysis_ocr";
27 const std::string VISION_LABEL_TABLE = "tab_analysis_label";
28 const std::string VISION_AESTHETICS_TABLE = "tab_analysis_aesthetics_score";
29 const std::string VISION_SALIENCY_TABLE = "tab_analysis_saliency_detect";
30 const std::string VISION_OBJECT_TABLE = "tab_analysis_object";
31 const std::string VISION_RECOMMENDATION_TABLE = "tab_analysis_recommendation";
32 const std::string VISION_SEGMENTATION_TABLE = "tab_analysis_segmentation";
33 const std::string VISION_COMPOSITION_TABLE = "tab_analysis_composition";
34 const std::string VISION_TOTAL_TABLE = "tab_analysis_total";
35 const std::string VISION_SHIELD_TABLE = "tab_application_shield";
36 const std::string VISION_IMAGE_FACE_TABLE = "tab_analysis_image_face";
37 const std::string VISION_FACE_TAG_TABLE = "tab_analysis_face_tag";
38 const std::string ANALYSIS_ALBUM_TABLE = "AnalysisAlbum";
39 const std::string ANALYSIS_PHOTO_MAP_TABLE = "AnalysisPhotoMap";
40 
41 // create vision table
42 const std::string ID = "id";
43 const std::string FILE_ID = "file_id";
44 const std::string OCR_TEXT = "ocr_text";
45 const std::string OCR_VERSION = "ocr_version";
46 const std::string OCR_TEXT_MSG = "ocr_text_msg";
47 const std::string OCR_WIDTH = "width";
48 const std::string OCR_HEIGHT = "height";
49 const std::string CREATE_TAB_ANALYSIS_OCR = "CREATE TABLE IF NOT EXISTS " + VISION_OCR_TABLE + " (" +
50     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
51     FILE_ID + " INT UNIQUE, " +
52     OCR_TEXT + " TEXT, " +
53     OCR_VERSION + " TEXT, " +
54     OCR_TEXT_MSG + " TEXT, " +
55     OCR_WIDTH + " INT, " +
56     OCR_HEIGHT + " INT)";
57 
58 const std::string CATEGORY_ID = "category_id";
59 const std::string SUB_LABEL = "sub_label";
60 const std::string PROB = "prob";
61 const std::string LABEL_VERSION = "label_version";
62 const std::string FEATURE = "feature";
63 const std::string SIM_RESULT = "sim_result";
64 const std::string CREATE_TAB_ANALYSIS_LABEL = "CREATE TABLE IF NOT EXISTS " + VISION_LABEL_TABLE + " (" +
65     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
66     FILE_ID + " INT UNIQUE, " +
67     CATEGORY_ID + " INT, " +
68     SUB_LABEL + " TEXT, " +
69     PROB + " REAL, " +
70     FEATURE + " TEXT, " +
71     SIM_RESULT + " TEXT, " +
72     LABEL_VERSION + " TEXT) ";
73 
74 const std::string AESTHETICS_SCORE = "aesthetics_score";
75 const std::string AESTHETICS_VERSION = "aesthetics_version";
76 const std::string CREATE_TAB_ANALYSIS_AESTHETICS = "CREATE TABLE IF NOT EXISTS " + VISION_AESTHETICS_TABLE + " (" +
77     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
78     FILE_ID + " INT UNIQUE, " +
79     AESTHETICS_SCORE + " INT, " +
80     AESTHETICS_VERSION + " TEXT, " +
81     PROB + " REAL) ";
82 
83 const std::string SALIENCY_X = "saliency_x";
84 const std::string SALIENCY_Y = "saliency_y";
85 const std::string SALIENCY_VERSION = "saliency_version";
86 const std::string CREATE_TAB_ANALYSIS_SALIENCY_DETECT = "CREATE TABLE IF NOT EXISTS " + VISION_SALIENCY_TABLE + " (" +
87     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
88     FILE_ID + " INT UNIQUE, " +
89     SALIENCY_X + " REAL, " +
90     SALIENCY_Y + " REAL, " +
91     SALIENCY_VERSION + " TEXT) ";
92 
93 const std::string OBJECT_ID = "object_id";
94 const std::string OBJECT_LABEL = "object_label";
95 const std::string OBJECT_SCALE_X = "object_scale_x";
96 const std::string OBJECT_SCALE_Y = "object_scale_y";
97 const std::string OBJECT_SCALE_WIDTH = "object_scale_width";
98 const std::string OBJECT_SCALE_HEIGHT = "object_scale_height";
99 const std::string OBJECT_VERSION = "object_version";
100 const std::string CREATE_TAB_ANALYSIS_OBJECT = "CREATE TABLE IF NOT EXISTS " + VISION_OBJECT_TABLE + " (" +
101     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
102     FILE_ID + " INT, " +
103     OBJECT_ID + " INT, " +
104     OBJECT_LABEL + " INT, " +
105     OBJECT_SCALE_X + " INT, " +
106     OBJECT_SCALE_Y + " INT, " +
107     OBJECT_SCALE_WIDTH + " INT, " +
108     OBJECT_SCALE_HEIGHT + " INT, " +
109     PROB + " REAL, " +
110     OBJECT_VERSION + " TEXT) ";
111 
112 const std::string RECOMMENDATION_ID = "recommendation_id";
113 const std::string RECOMMENDATION_RESOLUTION = "recommendation_resolution";
114 const std::string RECOMMENDATION_SCALE_X = "recommendation_scale_x";
115 const std::string RECOMMENDATION_SCALE_Y = "recommendation_scale_y";
116 const std::string RECOMMENDATION_SCALE_WIDTH = "recommendation_scale_width";
117 const std::string RECOMMENDATION_SCALE_HEIGHT = "recommendation_scale_height";
118 const std::string RECOMMENDATION_VERSION = "recommendation_version";
119 const std::string CREATE_TAB_ANALYSIS_RECOMMENDATION = "CREATE TABLE IF NOT EXISTS " + VISION_RECOMMENDATION_TABLE +
120     " (" +
121     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
122     FILE_ID + " INT, " +
123     RECOMMENDATION_ID + " INT, " +
124     RECOMMENDATION_RESOLUTION + " TEXT, " +
125     RECOMMENDATION_SCALE_X + " INT, " +
126     RECOMMENDATION_SCALE_Y + " INT, " +
127     RECOMMENDATION_SCALE_WIDTH + " INT, " +
128     RECOMMENDATION_SCALE_HEIGHT + " INT, " +
129     RECOMMENDATION_VERSION + " TEXT) ";
130 
131 const std::string SEGMENTATION_AREA = "segmentation_area";
132 const std::string SEGMENTATION_VERSION = "segmentation_version";
133 const std::string CREATE_TAB_ANALYSIS_SEGMENTATION = "CREATE TABLE IF NOT EXISTS " + VISION_SEGMENTATION_TABLE + " (" +
134     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
135     FILE_ID + " INT UNIQUE, " +
136     SEGMENTATION_AREA + " TEXT, " +
137     SEGMENTATION_VERSION + " TEXT) ";
138 
139 const std::string COMPOSITION_ID = "composition_id";
140 const std::string COMPOSITION_RESOLUTION = "composition_resolution";
141 const std::string CLOCK_STYLE = "clock_style";
142 const std::string CLOCK_LOCATION_X = "clock_location_x";
143 const std::string CLOCK_LOCATION_Y = "clock_location_y";
144 const std::string CLOCK_COLOUR = "clock_colour";
145 const std::string COMPOSITION_SCALE_X = "composition_scale_x";
146 const std::string COMPOSITION_SCALE_Y = "composition_scale_y";
147 const std::string COMPOSITION_SCALE_WIDTH = "composition_scale_width";
148 const std::string COMPOSITION_SCALE_HEIGHT = "composition_scale_height";
149 const std::string COMPOSITION_VERSION = "composition_version";
150 const std::string CREATE_TAB_ANALYSIS_COMPOSITION = "CREATE TABLE IF NOT EXISTS " + VISION_COMPOSITION_TABLE + " (" +
151     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
152     FILE_ID + " INT, " +
153     COMPOSITION_ID + " INT, " +
154     COMPOSITION_RESOLUTION + " TEXT, " +
155     CLOCK_STYLE + " INT, " +
156     CLOCK_LOCATION_X + " INT, " +
157     CLOCK_LOCATION_Y + " INT, " +
158     CLOCK_COLOUR + " TEXT, " +
159     COMPOSITION_SCALE_X + " INT, " +
160     COMPOSITION_SCALE_Y + " INT, " +
161     COMPOSITION_SCALE_WIDTH + " INT, " +
162     COMPOSITION_SCALE_HEIGHT + " INT, " +
163     COMPOSITION_VERSION + " TEXT) ";
164 
165 const std::string STATUS = "status";
166 const std::string OCR = "ocr";
167 const std::string LABEL = "label";
168 const std::string SALIENCY = "saliency";
169 const std::string OBJECT = "object";
170 const std::string RECOMMENDATION = "recommendation";
171 const std::string SEGMENTATION = "segmentation";
172 const std::string COMPOSITION = "composition";
173 const std::string FACE = "face";
174 const std::string CREATE_TAB_ANALYSIS_TOTAL = "CREATE TABLE IF NOT EXISTS " + VISION_TOTAL_TABLE + " (" +
175     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
176     FILE_ID + " INT UNIQUE, " +
177     STATUS + " INT, " +
178     OCR + " INT, " +
179     LABEL + " INT, " +
180     AESTHETICS_SCORE + " INT) ";
181 
182 const std::string CREATE_TAB_ANALYSIS_TOTAL_FOR_ONCREATE = "CREATE TABLE IF NOT EXISTS " + VISION_TOTAL_TABLE + " (" +
183     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
184     FILE_ID + " INT UNIQUE, " +
185     STATUS + " INT, " +
186     OCR + " INT, " +
187     LABEL + " INT, " +
188     AESTHETICS_SCORE + " INT, " +
189     FACE + " INT, " +
190     OBJECT + " INT, " +
191     RECOMMENDATION + " INT, " +
192     SEGMENTATION + " INT, " +
193     COMPOSITION + " INT, " +
194     SALIENCY + " INT) ";
195 
196 const std::string SHIELD_KEY = "shield_key";
197 const std::string SHIELD_VALUE = "shield_value";
198 const std::string CREATE_TAB_APPLICATION_SHIELD = "CREATE TABLE IF NOT EXISTS " + VISION_SHIELD_TABLE + " (" +
199     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
200     SHIELD_KEY + " TEXT, " +
201     SHIELD_VALUE + " TEXT) ";
202 
203 const std::string FACE_ID = "face_id";
204 const std::string TAG_ID = "tag_id";
205 const std::string SCALE_X = "scale_x";
206 const std::string SCALE_Y = "scale_y";
207 const std::string SCALE_WIDTH = "scale_width";
208 const std::string SCALE_HEIGHT = "scale_height";
209 const std::string LANDMARKS = "landmarks";
210 const std::string PITCH = "pitch";
211 const std::string YAW = "yaw";
212 const std::string ROLL = "roll";
213 const std::string TOTAL_FACES = "total_faces";
214 const std::string FEATURES = "features";
215 const std::string IMAGE_FACE_VERSION = "face_version";
216 const std::string IMAGE_FEATURES_VERSION = "features_version";
217 const std::string CREATE_TAB_IMAGE_FACE = "CREATE TABLE IF NOT EXISTS " + VISION_IMAGE_FACE_TABLE + " (" +
218     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
219     FILE_ID + " INTEGER, " +
220     FACE_ID + " TEXT, " +
221     TAG_ID +  " TEXT, " +
222     SCALE_X + " REAL, " +
223     SCALE_Y + " REAL, " +
224     SCALE_WIDTH + " REAL, " +
225     SCALE_HEIGHT + " REAL, " +
226     LANDMARKS + " BLOB, " +
227     PITCH + " REAL, " +
228     YAW + " REAL, " +
229     ROLL + " REAL, " +
230     PROB + " REAL, " +
231     TOTAL_FACES + " INTEGER, " +
232     IMAGE_FACE_VERSION + " TEXT, " +
233     IMAGE_FEATURES_VERSION + " TEXT, " +
234     FEATURES + " BLOB) ";
235 
236 const std::string TAG_NAME = "tag_name";
237 const std::string GROUP_TAG = "group_tag";
238 const std::string RENAME_OPERATION = "rename_operation";
239 const std::string CENTER_FEATURES = "center_features";
240 const std::string TAG_VERSION = "tag_version";
241 const std::string USER_DISPLAY_LEVEL = "user_display_level";
242 const std::string TAG_ORDER = "tag_order";
243 const std::string IS_ME = "is_me";
244 const std::string COVER_URI = "cover_uri";
245 const std::string COUNT = "count";
246 const std::string PORTRAIT_DATE_MODIFY = "date_modify";
247 const std::string ALBUM_TYPE = "album_type";
248 const std::string IS_REMOVED = "is_removed";
249 const std::string USER_OPERATION = "user_operation";
250 const std::string CREATE_TAB_FACE_TAG = "CREATE TABLE IF NOT EXISTS " + VISION_FACE_TAG_TABLE + " (" +
251     ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
252     TAG_ID +  " TEXT UNIQUE, " +
253     TAG_NAME +  " TEXT, " +
254     USER_OPERATION + " INTEGER, " +
255     GROUP_TAG +  " TEXT, " +
256     RENAME_OPERATION + " INTEGER, " +
257     CENTER_FEATURES + " BLOB, " +
258     TAG_VERSION + " TEXT, " +
259     USER_DISPLAY_LEVEL + " INTEGER, " +
260     TAG_ORDER + " INTEGER, " +
261     IS_ME + " INTEGER, " +
262     COVER_URI +  " TEXT, " +
263     COUNT + " INTEGER, " +
264     PORTRAIT_DATE_MODIFY + " BIGINT, " +
265     ALBUM_TYPE + " INTEGER, " +
266     IS_REMOVED + " INTEGER) ";
267 
268 const std::string ALBUM_ID = "album_id";
269 const std::string ALBUM_SUBTYPE = "album_subtype";
270 const std::string ALBUM_NAME = "album_name";
271 const std::string DATE_MODIFIED = "date_modified";
272 const std::string RANK = "rank";
273 // fake column for merge album
274 const std::string TARGET_ALBUM_ID = "target_album_id";
275 const std::string CREATE_ANALYSIS_ALBUM = "CREATE TABLE IF NOT EXISTS " + ANALYSIS_ALBUM_TABLE + " (" +
276     ALBUM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
277     ALBUM_TYPE + " INT, " +
278     ALBUM_SUBTYPE + " INT, " +
279     ALBUM_NAME + " TEXT, " +
280     COVER_URI + " TEXT, " +
281     COUNT + " INT DEFAULT 0, " +
282     DATE_MODIFIED + " BIGINT, " +
283     RANK + " INT) ";
284 
285 const std::string MAP_ALBUM = "map_album";
286 const std::string MAP_ASSET = "map_asset";
287 const std::string CREATE_ANALYSIS_ALBUM_MAP = "CREATE TABLE IF NOT EXISTS " + ANALYSIS_PHOTO_MAP_TABLE + " (" +
288     MAP_ALBUM + " INT, " +
289     MAP_ASSET + " INT, " +
290     "PRIMARY KEY (" + MAP_ALBUM + "," + MAP_ASSET + ")) ";
291 
292 const std::string INIT_TAB_ANALYSIS_TOTAL = "INSERT INTO " + VISION_TOTAL_TABLE + " (" +
293     FILE_ID + ", " +
294     STATUS + ", " +
295     OCR + ", " +
296     AESTHETICS_SCORE + ", " +
297     LABEL + ") " +
298     "SELECT " + FILE_ID +
299     ", CASE WHEN date_trashed > 0 THEN 2 ELSE 0 END," +
300     " 0," +
301     " CASE WHEN subtype = 1 THEN -1 ELSE 0 END," +
302     " CASE WHEN subtype = 1 THEN -1 ELSE 0 END," +
303     " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE MEDIA_TYPE = 1";
304 
305 // trigger
306 const std::string CREATE_VISION_DELETE_TRIGGER = "CREATE TRIGGER IF NOT EXISTS delete_vision_trigger AFTER DELETE ON " +
307     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
308     " BEGIN " +
309     " UPDATE " + VISION_TOTAL_TABLE +
310     " SET " + STATUS + " = -1 " +
311     " WHERE " + FILE_ID +
312     " = OLD.file_id;" +
313     " END;";
314 
315 const std::string CREATE_VISION_UPDATE_TRIGGER = "CREATE TRIGGER IF NOT EXISTS update_vision_trigger AFTER UPDATE ON " +
316     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
317     " WHEN ((NEW.date_trashed > 0 AND OLD.date_trashed = 0)" +
318     " OR (NEW.date_trashed = 0 AND OLD.date_trashed > 0))" +
319     " AND NEW.MEDIA_TYPE = 1" +
320     " BEGIN " +
321     " UPDATE " + VISION_TOTAL_TABLE +
322     " SET " + STATUS + " = " +
323     " (CASE WHEN NEW.date_trashed > 0 THEN 2 ELSE 0 END)" +
324     " WHERE file_id = OLD.file_id;" +
325     " END;";
326 
327 const std::string CREATE_VISION_INSERT_TRIGGER = "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
328     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
329     " WHEN NEW.MEDIA_TYPE = 1" +
330     " BEGIN " +
331     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
332     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
333     AESTHETICS_SCORE + ", " +
334     LABEL + ") " +
335     " VALUES (" +
336     " NEW.file_id, 0, 0," +
337     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
338     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END));" +
339     " END;";
340 
341 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ONCREATE =
342     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
343     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
344     " WHEN NEW.MEDIA_TYPE = 1" +
345     " BEGIN " +
346     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
347     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
348     SALIENCY + ") " +
349     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" + " END;";
350 
351 const std::string DROP_INSERT_VISION_TRIGGER = "DROP TRIGGER IF EXISTS insert_vision_trigger";
352 
353 const std::string CREATE_INSERT_VISION_TRIGGER_FOR_ADD_FACE =
354     "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     FACE + ") " +
363     " VALUES (" +
364     " NEW.file_id, 0, 0," +
365     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
366     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
367     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END));" +
368     " END;";
369 
370 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_AC =
371     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
372     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
373     " WHEN NEW.MEDIA_TYPE = 1" +
374     " BEGIN " +
375     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
376     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
377     AESTHETICS_SCORE + ", " +
378     LABEL + ", " +
379     FACE + ", " +
380     OBJECT + ", " +
381     RECOMMENDATION + ", " +
382     SEGMENTATION + ", " +
383     COMPOSITION + ")" +
384     " VALUES (" +
385     " NEW.file_id, 0, 0," +
386     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
387     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
388     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
389     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
390     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
391     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
392     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END));" +
393     " END;";
394 
395 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_ADD_SALIENCY =
396     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
397     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
398     " WHEN NEW.MEDIA_TYPE = 1" +
399     " BEGIN " +
400     " INSERT INTO " + VISION_TOTAL_TABLE +" (" +
401     FILE_ID + ", " + STATUS + ", " + OCR + ", " +
402     AESTHETICS_SCORE + ", " +
403     LABEL + ", " +
404     FACE + ", " +
405     OBJECT + ", " +
406     RECOMMENDATION + ", " +
407     SEGMENTATION + ", " +
408     COMPOSITION + "," +
409     SALIENCY + ") " +
410     " VALUES (" +
411     " NEW.file_id, 0, 0," +
412     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
413     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
414     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
415     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
416     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
417     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
418     " (CASE WHEN NEW.subtype = 1 THEN -1 ELSE 0 END)," +
419     " 0 );" +
420     " END;";
421 
422 const std::string CREATE_VISION_INSERT_TRIGGER_FOR_UPDATE_SPEC =
423     "CREATE TRIGGER IF NOT EXISTS insert_vision_trigger AFTER INSERT ON " +
424     PhotoColumn::PHOTOS_TABLE + " FOR EACH ROW " +
425     " WHEN NEW.MEDIA_TYPE = 1" +
426     " BEGIN " +
427     " INSERT INTO " + VISION_TOTAL_TABLE +" (" + FILE_ID + ", " + STATUS + ", " + OCR + ", " + AESTHETICS_SCORE + ", " +
428     LABEL + ", " + FACE + ", " + OBJECT + ", " + RECOMMENDATION + ", " + SEGMENTATION + ", " + COMPOSITION + "," +
429     SALIENCY + ") " +
430     " VALUES (" + " NEW.file_id, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );" + " END;";
431 
432 const std::string URI_OCR = MEDIALIBRARY_DATA_URI + "/" + PAH_ANA_OCR;
433 const std::string URI_LABEL = MEDIALIBRARY_DATA_URI + "/" + PAH_ANA_LABEL;
434 const std::string URI_AESTHETICS = MEDIALIBRARY_DATA_URI + "/" + PAH_ANA_ATTS;
435 const std::string URI_OBJECT = MEDIALIBRARY_DATA_URI + "/" + VISION_OBJECT_TABLE;
436 const std::string URI_RECOMMENDATION = MEDIALIBRARY_DATA_URI + "/" + VISION_RECOMMENDATION_TABLE;
437 const std::string URI_SEGMENTATION = MEDIALIBRARY_DATA_URI + "/" + VISION_SEGMENTATION_TABLE;
438 const std::string URI_COMPOSITION = MEDIALIBRARY_DATA_URI + "/" + VISION_COMPOSITION_TABLE;
439 const std::string URI_TOTAL = MEDIALIBRARY_DATA_URI + "/" + PAH_ANA_TOTAL;
440 const std::string URI_SHIELD = MEDIALIBRARY_DATA_URI + "/" + VISION_SHIELD_TABLE;
441 const std::string URI_SALIENCY = MEDIALIBRARY_DATA_URI + "/" + VISION_SALIENCY_TABLE;
442 const std::string URI_IMAGE_FACE = MEDIALIBRARY_DATA_URI + "/" + VISION_IMAGE_FACE_TABLE;
443 const std::string URI_FACE_TAG = MEDIALIBRARY_DATA_URI + "/" + VISION_FACE_TAG_TABLE;
444 
445 const std::string ADD_FACE_STATUS_COLUMN = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " + FACE + " INT";
446 const std::string UPDATE_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " + FACE +
447     " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE subtype != 1)";
448 const std::string UPDATE_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + FACE +
449     " = -1 WHERE " + FACE + " IS NULL";
450 const std::string IMAGE_FACE_INDEX = "image_face_index";
451 const std::string CREATE_IMAGE_FACE_INDEX = "CREATE UNIQUE INDEX IF NOT EXISTS " + IMAGE_FACE_INDEX + " ON " +
452     VISION_IMAGE_FACE_TABLE + " (" + FILE_ID + "," + FACE_ID + ")";
453 
454 const std::string ADD_SALIENCY_STATUS_COLUMN = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " + SALIENCY + " INT";
455 const std::string UPDATE_SALIENCY_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE +
456     " SET " + STATUS + " = 0, " + SALIENCY + " = 0 WHERE " +
457     FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE media_type = 1)";
458 const std::string UPDATE_SALIENCY_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + SALIENCY +
459     " = -1 WHERE " + SALIENCY + " IS NULL";
460 
461 const std::string AC_ADD_OBJECT_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
462     OBJECT + " INT";
463 const std::string AC_UPDATE_OBJECT_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " + OBJECT +
464     " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE + " WHERE subtype != 1)";
465 const std::string AC_UPDATE_OBJECT_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + OBJECT +
466     " = -1 WHERE " + OBJECT + " IS NULL";
467 const std::string OBJECT_INDEX = "object_index";
468 const std::string CREATE_OBJECT_INDEX = "CREATE UNIQUE INDEX " + OBJECT_INDEX + " ON " +
469     VISION_OBJECT_TABLE + " (" + FILE_ID + "," + OBJECT_ID + ")";
470 
471 const std::string AC_ADD_RECOMMENDATION_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
472     RECOMMENDATION + " INT";
473 const std::string AC_UPDATE_RECOMMENDATION_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
474     RECOMMENDATION + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
475     " WHERE subtype != 1)";
476 const std::string AC_UPDATE_RECOMMENDATION_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " +
477     RECOMMENDATION + " = -1 WHERE " + RECOMMENDATION + " IS NULL";
478 const std::string RECOMMENDATION_INDEX = "recommendation_index";
479 const std::string CREATE_RECOMMENDATION_INDEX = "CREATE UNIQUE INDEX " + RECOMMENDATION_INDEX + " ON " +
480     VISION_RECOMMENDATION_TABLE + " (" + FILE_ID + "," + RECOMMENDATION_ID + ")";
481 
482 const std::string AC_ADD_SEGMENTATION_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
483     SEGMENTATION + " INT";
484 const std::string AC_UPDATE_SEGMENTATION_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
485     SEGMENTATION + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
486     " WHERE subtype != 1)";
487 const std::string AC_UPDATE_SEGMENTATION_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " +
488     SEGMENTATION + " = -1 WHERE " + SEGMENTATION + " IS NULL";
489 
490 const std::string AC_ADD_COMPOSITION_COLUMN_FOR_TOTAL = "ALTER TABLE " + VISION_TOTAL_TABLE + " ADD COLUMN " +
491     COMPOSITION + " INT";
492 const std::string AC_UPDATE_COMPOSITION_TOTAL_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " + STATUS + " = 0, " +
493     COMPOSITION + " = 0 WHERE " + FILE_ID + " IN (SELECT " + FILE_ID + " FROM " + PhotoColumn::PHOTOS_TABLE +
494     " WHERE subtype != 1)";
495 const std::string AC_UPDATE_COMPOSITION_TOTAL_NOT_SUPPORT_VALUE = "UPDATE " + VISION_TOTAL_TABLE + " SET " +
496     COMPOSITION + " = -1 WHERE " + COMPOSITION + " IS NULL";
497 const std::string COMPOSITION_INDEX = "composition_index";
498 const std::string CREATE_COMPOSITION_INDEX = "CREATE UNIQUE INDEX " + COMPOSITION_INDEX + " ON " +
499     VISION_COMPOSITION_TABLE + " (" + FILE_ID + "," + COMPOSITION_ID + ")";
500 const std::string ALTER_WIDTH_COLUMN = "ALTER TABLE tab_analysis_ocr ADD COLUMN width INT;";
501 const std::string ALTER_HEIGHT_COLUMN = "ALTER TABLE tab_analysis_ocr ADD COLUMN height INT;";
502 const std::string DROP_TABLE_ANALYSISALBUM = "DROP TABLE AnalysisAlbum;";
503 const std::string DROP_TABLE_ANALYSISPHOTOMAP = "DROP TABLE AnalysisPhotoMap;";
504 
505 const std::string ADD_TAG_ID_COLUMN_FOR_ALBUM = "ALTER TABLE " + ANALYSIS_ALBUM_TABLE + " ADD COLUMN " +
506     TAG_ID + " TEXT";
507 const std::string ADD_USER_OPERATION_COLUMN_FOR_ALBUM = "ALTER TABLE " + ANALYSIS_ALBUM_TABLE + " ADD COLUMN " +
508     USER_OPERATION + " INT";
509 const std::string ADD_GROUP_TAG_COLUMN_FOR_ALBUM = "ALTER TABLE " + ANALYSIS_ALBUM_TABLE + " ADD COLUMN " +
510     GROUP_TAG + " TEXT";
511 const std::string ADD_USER_DISPLAY_LEVEL_COLUMN_FOR_ALBUM = "ALTER TABLE " + ANALYSIS_ALBUM_TABLE + " ADD COLUMN " +
512     USER_DISPLAY_LEVEL + " INT";
513 const std::string ADD_IS_ME_COLUMN_FOR_ALBUM = "ALTER TABLE " + ANALYSIS_ALBUM_TABLE + " ADD COLUMN " +
514     IS_ME + " INT";
515 const std::string ADD_IS_REMOVED_COLUMN_FOR_ALBUM = "ALTER TABLE " + ANALYSIS_ALBUM_TABLE + " ADD COLUMN " +
516     IS_REMOVED + " INT";
517 const std::string ADD_RENAME_OPERATION_COLUMN_FOR_ALBUM = "ALTER TABLE " + ANALYSIS_ALBUM_TABLE + " ADD COLUMN " +
518     RENAME_OPERATION + " INT";
519 const std::string CREATE_ANALYSIS_ALBUM_FOR_ONCREATE = "CREATE TABLE IF NOT EXISTS " + ANALYSIS_ALBUM_TABLE + " (" +
520     ALBUM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
521     ALBUM_TYPE + " INT, " +
522     ALBUM_SUBTYPE + " INT, " +
523     ALBUM_NAME + " TEXT, " +
524     COVER_URI + " TEXT, " +
525     COUNT + " INT, " +
526     DATE_MODIFIED + " BIGINT, " +
527     RANK + " INT, " +
528     TAG_ID + " TEXT, " +
529     USER_OPERATION + " INT, " +
530     GROUP_TAG + " TEXT, " +
531     USER_DISPLAY_LEVEL + " INT, " +
532     IS_ME + " INT, " +
533     IS_REMOVED + " INT, " +
534     RENAME_OPERATION + " INT) ";
535 const std::string ANALYSIS_ALBUM_UPDATE_SEARCH_TRIGGER = "analysis_album_update_search_trigger";
536 const std::string CREATE_ANALYSIS_ALBUM_UPDATE_SEARCH_TRIGGER = "CREATE TRIGGER " +
537     ANALYSIS_ALBUM_UPDATE_SEARCH_TRIGGER +
538     " AFTER UPDATE OF " + ALBUM_NAME +
539     " ON " + ANALYSIS_ALBUM_TABLE +
540     " FOR EACH ROW WHEN (NEW." + ALBUM_SUBTYPE + " = " + std::to_string(PORTRAIT) + ")" +
541     " BEGIN " +
542     " UPDATE " + "tab_analysis_search_index" +
543     " SET " + "cv_status" + " = 0 " +
544     " WHERE (" + FILE_ID + " IN( " +
545     " SELECT " + MAP_ASSET +
546     " FROM " + ANALYSIS_PHOTO_MAP_TABLE +
547     " WHERE (old." + ALBUM_ID + " = " + ANALYSIS_PHOTO_MAP_TABLE + "." + MAP_ALBUM + ")" +
548     ")); END;";
549 } // namespace Media
550 } // namespace OHOS
551 #endif // MEDIALIBRARY_VISION_COLUMN_H