4
/ a tool for uploading JPEG/EXIF photos into a DB
5
/ preserving full Exif metadata and building Geometry from GPS tags [if present]
7
/ version 1.0, 2008 October 13
9
/ Author: Sandro Furieri a.furieri@lqt.it
11
/ Copyright (C) 2008 Alessandro Furieri
13
/ This program is free software: you can redistribute it and/or modify
14
/ it under the terms of the GNU General Public License as published by
15
/ the Free Software Foundation, either version 3 of the License, or
16
/ (at your option) any later version.
18
/ This program is distributed in the hope that it will be useful,
19
/ but WITHOUT ANY WARRANTY; without even the implied warranty of
20
/ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21
/ GNU General Public License for more details.
23
/ You should have received a copy of the GNU General Public License
24
/ along with this program. If not, see <http://www.gnu.org/licenses/>.
34
#include <sys/types.h>
36
#if defined(_WIN32) && !defined(__MINGW32__)
43
#ifdef SPATIALITE_AMALGAMATION
44
#include <spatialite/sqlite3.h>
49
#include <spatialite/gaiaexif.h>
50
#include <spatialite/gaiageo.h>
51
#include <spatialite.h>
58
#if defined(_WIN32) && !defined(__MINGW32__)
59
#define strcasecmp _stricmp
60
#endif /* not WIN32 */
63
getPixelX (gaiaExifTagListPtr tag_list, int *ok)
65
/* trying to retrieve the ExifImageWidth */
70
tag = tag_list->First;
73
if (tag->TagId == 0xA002)
75
/* ok, this one is the ExifImageWidth tag */
76
if (tag->Type == 3 && tag->Count == 1)
79
return *(tag->ShortValues + 0);
81
else if (tag->Type == 4 && tag->Count == 1)
84
return *(tag->LongValues + 0);
93
getPixelY (gaiaExifTagListPtr tag_list, int *ok)
95
/* trying to retrieve the ExifImageLength */
100
tag = tag_list->First;
103
if (tag->TagId == 0xA003)
105
/* ok, this one is the ExifImageLength tag */
106
if (tag->Type == 3 && tag->Count == 1)
109
return *(tag->ShortValues + 0);
111
else if (tag->Type == 4 && tag->Count == 1)
114
return *(tag->LongValues + 0);
123
getMake (gaiaExifTagListPtr tag_list, char *str, int len, int *ok)
125
/* trying to retrieve the Make */
131
tag = tag_list->First;
134
if (tag->TagId == 0x010F)
136
/* ok, this one is the Make tag */
140
l = strlen (tag->StringValue);
142
strcpy (str, tag->StringValue);
145
memset (str, '\0', len);
146
memcpy (str, tag->StringValue, len - 1);
157
getModel (gaiaExifTagListPtr tag_list, char *str, int len, int *ok)
159
/* trying to retrieve the Model */
165
tag = tag_list->First;
168
if (tag->TagId == 0x0110)
170
/* ok, this one is the Model tag */
174
l = strlen (tag->StringValue);
176
strcpy (str, tag->StringValue);
179
memset (str, '\0', len);
180
memcpy (str, tag->StringValue, len - 1);
191
getDate (gaiaExifTagListPtr tag_list, char *str, int len, int *ok)
193
/* trying to retrieve the Date */
199
tag = tag_list->First;
202
if (tag->TagId == 0x9003)
204
/* ok, this one is the DateTimeOriginal tag */
208
l = strlen (tag->StringValue);
210
strcpy (str, tag->StringValue);
213
memset (str, '\0', len);
214
memcpy (str, tag->StringValue, len - 1);
230
getGpsCoords (gaiaExifTagListPtr tag_list, double *longitude, double *latitude,
233
/* trying to retrieve the GPS coordinates */
236
char long_ref = '\0';
237
double lat_degs = -DBL_MAX;
238
double lat_mins = -DBL_MAX;
239
double lat_secs = -DBL_MAX;
240
double long_degs = -DBL_MAX;
241
double long_mins = -DBL_MAX;
242
double long_secs = -DBL_MAX;
249
tag = tag_list->First;
252
if (tag->Gps && tag->TagId == 0x01)
254
/* ok, this one is the GPSLatitudeRef tag */
256
lat_ref = *(tag->StringValue);
258
if (tag->Gps && tag->TagId == 0x03)
260
/* ok, this one is the GPSLongitudeRef tag */
262
long_ref = *(tag->StringValue);
264
if (tag->Gps && tag->TagId == 0x02)
266
/* ok, this one is the GPSLatitude tag */
267
if (tag->Type == 5 && tag->Count == 3)
269
dblval = gaiaExifTagGetRationalValue (tag, 0, &xok);
272
dblval = gaiaExifTagGetRationalValue (tag, 1, &xok);
275
dblval = gaiaExifTagGetRationalValue (tag, 2, &xok);
280
if (tag->Gps && tag->TagId == 0x04)
282
/* ok, this one is the GPSLongitude tag */
283
if (tag->Type == 5 && tag->Count == 3)
285
dblval = gaiaExifTagGetRationalValue (tag, 0, &xok);
288
dblval = gaiaExifTagGetRationalValue (tag, 1, &xok);
291
dblval = gaiaExifTagGetRationalValue (tag, 2, &xok);
298
if ((lat_ref == 'N' || lat_ref == 'S' || long_ref == 'E' || long_ref == 'W')
299
&& lat_degs != -DBL_MAX && lat_mins != -DBL_MAX && lat_secs != -DBL_MAX
300
&& long_degs != -DBL_MAX && long_mins != -DBL_MAX
301
&& long_secs != -DBL_MAX)
308
lat_degs = math_round (lat_degs * 1000000.0);
309
lat_mins = math_round (lat_mins * 1000000.0);
310
lat_secs = math_round (lat_secs * 1000000.0);
312
math_round (lat_degs + (lat_mins / 60.0) +
313
(lat_secs / 3600.0)) * (sign / 1000000.0);
319
long_degs = math_round (long_degs * 1000000.0);
320
long_mins = math_round (long_mins * 1000000.0);
321
long_secs = math_round (long_secs * 1000000.0);
323
math_round (long_degs + (long_mins / 60.0) +
324
(long_secs / 3600.0)) * (sign / 1000000.0);
331
getGpsSatellites (gaiaExifTagListPtr tag_list, char *str, int len, int *ok)
333
/* trying to retrieve the GPSSatellites */
339
tag = tag_list->First;
342
if (tag->Gps && tag->TagId == 0x08)
344
/* ok, this one is the GPSSatellites tag */
348
l = strlen (tag->StringValue);
350
strcpy (str, tag->StringValue);
353
memset (str, '\0', len);
354
memcpy (str, tag->StringValue, len - 1);
365
getGpsDirection (gaiaExifTagListPtr tag_list, int *ok)
367
/* trying to retrieve the GPS direction */
370
double direction = -DBL_MAX;
376
tag = tag_list->First;
379
if (tag->Gps && tag->TagId == 0x10)
381
/* ok, this one is the GPSDirectionRef tag */
383
dir_ref = *(tag->StringValue);
385
if (tag->Gps && tag->TagId == 0x11)
387
/* ok, this one is the GPSDirection tag */
388
if (tag->Type == 5 && tag->Count == 1)
390
dblval = gaiaExifTagGetRationalValue (tag, 0, &xok);
397
if ((dir_ref == 'T' || dir_ref == 'M') && direction != -DBL_MAX)
403
getGpsTimestamp (gaiaExifTagListPtr tag_list, char *str, int len, int *ok)
405
/* trying to retrieve the GPS Timestamp */
409
double hours = -DBL_MAX;
410
double mins = -DBL_MAX;
411
double secs = -DBL_MAX;
422
strcpy (date, "0000-00-00");
423
tag = tag_list->First;
426
if (tag->Gps && tag->TagId == 0x1D)
428
/* ok, this one is the GPSDateStamp tag */
431
strcpy (date, tag->StringValue);
436
if (tag->Gps && tag->TagId == 0x07)
438
/* ok, this one is the GPSTimeStamp tag */
439
if (tag->Type == 5 && tag->Count == 3)
441
dblval = gaiaExifTagGetRationalValue (tag, 0, &xok);
444
dblval = gaiaExifTagGetRationalValue (tag, 1, &xok);
447
dblval = gaiaExifTagGetRationalValue (tag, 2, &xok);
454
if (hours != -DBL_MAX && mins != -DBL_MAX && secs != -DBL_MAX)
457
hh = (int) floor (hours);
458
mm = (int) floor (mins);
459
ss = (int) floor (secs);
460
millis = (int) ((secs - ss) * 1000);
461
sprintf (timestamp, "%s %02d:%02d:%02d.%03d", date, hh, mm, ss,
463
l = strlen (timestamp);
465
strcpy (str, timestamp);
468
memset (str, '\0', len);
469
memcpy (str, timestamp, len - 1);
476
isExifGps (gaiaExifTagListPtr tag_list)
478
/* checks if this one is a GPS-tagged EXIF */
481
gaiaExifTagPtr pT = tag_list->First;
484
if (pT->Gps && pT->TagId == 0x04)
486
if (pT->Gps && pT->TagId == 0x02)
488
if (gps_long && gps_lat)
496
updateExifTables (sqlite3 * handle, const unsigned char *blob, int sz,
497
gaiaExifTagListPtr tag_list, int metadata, const char *path)
499
/* inserting an EXIF photo into the DB */
511
char satellites[1024];
514
char *err_msg = NULL;
516
sqlite3_int64 pk = 0;
522
gaiaGeomCollPtr geom;
523
unsigned char *geoblob;
525
/* starts a transaction */
526
strcpy (sql, "BEGIN");
527
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
528
if (ret != SQLITE_OK)
530
printf ("BEGIN error: %s\n", err_msg);
531
sqlite3_free (err_msg);
534
/* feeding the ExifPhoto table; preparing the SQL statement*/
536
"INSERT INTO ExifPhoto (PhotoId, Photo, PixelX, PixelY, CameraMake, CameraModel, ");
538
"ShotDateTime, GpsGeometry, GpsDirection, GpsSatellites, GpsTimestamp, FromPath) ");
540
"VALUES (NULL, ?, ?, ?, ?, ?, JulianDay(?), ?, ?, ?, JulianDay(?), ?)");
541
ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
542
if (ret != SQLITE_OK)
544
printf ("INSERT INTO ExifPhoto error: %s\n", sqlite3_errmsg (handle));
547
sqlite3_bind_blob (stmt, 1, blob, sz, SQLITE_STATIC);
548
val64 = getPixelX (tag_list, &ok);
550
sqlite3_bind_int64 (stmt, 2, val64);
552
sqlite3_bind_null (stmt, 2);
553
val64 = getPixelY (tag_list, &ok);
555
sqlite3_bind_int64 (stmt, 3, val64);
557
sqlite3_bind_null (stmt, 3);
558
getMake (tag_list, make, 1024, &ok);
560
sqlite3_bind_text (stmt, 4, make, strlen (make), SQLITE_STATIC);
562
sqlite3_bind_null (stmt, 4);
563
getModel (tag_list, model, 1024, &ok);
565
sqlite3_bind_text (stmt, 5, model, strlen (model), SQLITE_STATIC);
567
sqlite3_bind_null (stmt, 5);
568
getDate (tag_list, date, 32, &ok);
570
sqlite3_bind_text (stmt, 6, date, strlen (date), SQLITE_STATIC);
572
sqlite3_bind_text (stmt, 6, "0000-00-00 00:00:00", 19, SQLITE_STATIC);
573
getGpsCoords (tag_list, &longitude, &latitude, &ok);
576
geom = gaiaAllocGeomColl ();
578
gaiaAddPointToGeomColl (geom, longitude, latitude);
579
gaiaToSpatiaLiteBlobWkb (geom, &geoblob, &geosize);
580
gaiaFreeGeomColl (geom);
581
sqlite3_bind_blob (stmt, 7, geoblob, geosize, SQLITE_TRANSIENT);
585
sqlite3_bind_null (stmt, 7);
586
dblval = getGpsDirection (tag_list, &ok);
588
sqlite3_bind_double (stmt, 8, dblval);
590
sqlite3_bind_null (stmt, 8);
591
getGpsSatellites (tag_list, satellites, 1024, &ok);
593
sqlite3_bind_text (stmt, 9, satellites, strlen (satellites),
596
sqlite3_bind_null (stmt, 9);
597
getGpsTimestamp (tag_list, timestamp, 32, &ok);
599
sqlite3_bind_text (stmt, 10, timestamp, strlen (timestamp),
602
sqlite3_bind_text (stmt, 10, "0000-00-00 00:00:00", 19, SQLITE_STATIC);
603
sqlite3_bind_text (stmt, 11, path, strlen (path), SQLITE_STATIC);
604
ret = sqlite3_step (stmt);
605
if (ret == SQLITE_DONE || ret == SQLITE_ROW)
609
printf ("sqlite3_step() error: %s\n", sqlite3_errmsg (handle));
610
sqlite3_finalize (stmt);
613
sqlite3_finalize (stmt);
614
pk = sqlite3_last_insert_rowid (handle);
617
/* feeding the ExifTags table; preparing the SQL statement */
619
"INSERT OR IGNORE INTO ExifTags (PhotoId, TagId, TagName, GpsTag, ValueType, ");
620
strcat (sql, "TypeName, CountValues) VALUES (?, ?, ?, ?, ?, ?, ?)");
621
ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
622
if (ret != SQLITE_OK)
624
printf ("INSERT INTO ExifTags error: %s\n",
625
sqlite3_errmsg (handle));
628
for (i = 0; i < gaiaGetExifTagsCount (tag_list); i++)
630
pT = gaiaGetExifTagByPos (tag_list, i);
633
gaiaExifTagGetName (pT, tag_name, 128);
634
switch (gaiaExifTagGetValueType (pT))
640
type_desc = "STRING";
649
type_desc = "RATIONAL";
655
type_desc = "UNDEFINED";
658
type_desc = "SSHORT";
664
type_desc = "SRATIONAL";
670
type_desc = "DOUBLE";
673
type_desc = "UNKNOWN";
676
/* INSERTing an Exif Tag */
677
sqlite3_reset (stmt);
678
sqlite3_clear_bindings (stmt);
679
sqlite3_bind_int64 (stmt, 1, pk);
680
sqlite3_bind_int (stmt, 2, gaiaExifTagGetId (pT));
681
sqlite3_bind_text (stmt, 3, tag_name, strlen (tag_name),
683
sqlite3_bind_int (stmt, 4, gaiaIsExifGpsTag (pT));
684
sqlite3_bind_int (stmt, 5, gaiaExifTagGetValueType (pT));
685
sqlite3_bind_text (stmt, 6, type_desc, strlen (type_desc),
687
sqlite3_bind_int (stmt, 7, gaiaExifTagGetNumValues (pT));
688
ret = sqlite3_step (stmt);
689
if (ret == SQLITE_DONE || ret == SQLITE_ROW)
693
printf ("sqlite3_step() error: %s\n",
694
sqlite3_errmsg (handle));
695
sqlite3_finalize (stmt);
700
sqlite3_finalize (stmt);
701
/* feeding the ExifValues table; preparing the SQL statement */
703
"INSERT OR IGNORE INTO ExifValues (PhotoId, TagId, ValueIndex, ByteValue, ");
705
"StringValue, NumValue, NumValueBis, DoubleValue, HumanReadable) VALUES ");
706
strcat (sql, "(?, ?, ?, ?, ?, ?, ?, ?, ?)");
707
ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
708
if (ret != SQLITE_OK)
710
printf ("INSERT INTO ExifValues error: %s\n",
711
sqlite3_errmsg (handle));
714
for (i = 0; i < gaiaGetExifTagsCount (tag_list); i++)
716
pT = gaiaGetExifTagByPos (tag_list, i);
719
gaiaExifTagGetHumanReadable (pT, human, 1024, &ok_human);
720
for (iv = 0; iv < gaiaExifTagGetNumValues (pT); iv++)
722
/* INSERTing an Exif Tag */
723
sqlite3_reset (stmt);
724
sqlite3_clear_bindings (stmt);
725
sqlite3_bind_int64 (stmt, 1, pk);
726
sqlite3_bind_int (stmt, 2, gaiaExifTagGetId (pT));
727
sqlite3_bind_int (stmt, 3, iv);
728
if (gaiaExifTagGetValueType (pT) == 1
729
|| gaiaExifTagGetValueType (pT) == 6
730
|| gaiaExifTagGetValueType (pT) == 7)
732
sqlite3_bind_blob (stmt, 4, pT->ByteValue,
733
pT->Count, SQLITE_STATIC);
734
sqlite3_bind_null (stmt, 5);
735
sqlite3_bind_null (stmt, 6);
736
sqlite3_bind_null (stmt, 7);
737
sqlite3_bind_null (stmt, 8);
739
if (gaiaExifTagGetValueType (pT) == 2)
741
sqlite3_bind_null (stmt, 4);
742
sqlite3_bind_text (stmt, 5, pT->StringValue,
743
strlen (pT->StringValue),
745
sqlite3_bind_null (stmt, 6);
746
sqlite3_bind_null (stmt, 7);
747
sqlite3_bind_null (stmt, 8);
749
if (gaiaExifTagGetValueType (pT) == 3)
751
sqlite3_bind_null (stmt, 4);
752
sqlite3_bind_null (stmt, 5);
754
gaiaExifTagGetShortValue (pT, iv, &ok);
756
sqlite3_bind_null (stmt, 6);
758
sqlite3_bind_int64 (stmt, 6, val64);
759
sqlite3_bind_null (stmt, 7);
760
sqlite3_bind_null (stmt, 8);
762
if (gaiaExifTagGetValueType (pT) == 4)
764
sqlite3_bind_null (stmt, 4);
765
sqlite3_bind_null (stmt, 5);
766
val64 = gaiaExifTagGetLongValue (pT, iv, &ok);
768
sqlite3_bind_null (stmt, 6);
770
sqlite3_bind_int64 (stmt, 6, val64);
771
sqlite3_bind_null (stmt, 7);
772
sqlite3_bind_null (stmt, 8);
774
if (gaiaExifTagGetValueType (pT) == 5)
776
sqlite3_bind_null (stmt, 4);
777
sqlite3_bind_null (stmt, 5);
779
gaiaExifTagGetRational1Value (pT, iv,
782
sqlite3_bind_null (stmt, 6);
784
sqlite3_bind_int64 (stmt, 6, val64);
786
gaiaExifTagGetRational2Value (pT, iv,
789
sqlite3_bind_null (stmt, 7);
791
sqlite3_bind_int64 (stmt, 7, val64);
793
gaiaExifTagGetRationalValue (pT, iv, &ok);
795
sqlite3_bind_null (stmt, 8);
797
sqlite3_bind_double (stmt, 8, dblval);
799
if (gaiaExifTagGetValueType (pT) == 9)
801
sqlite3_bind_null (stmt, 4);
802
sqlite3_bind_null (stmt, 5);
804
gaiaExifTagGetSignedLongValue (pT, iv,
807
sqlite3_bind_null (stmt, 6);
809
sqlite3_bind_int64 (stmt, 6, val64);
810
sqlite3_bind_null (stmt, 7);
811
sqlite3_bind_null (stmt, 8);
813
if (gaiaExifTagGetValueType (pT) == 10)
815
sqlite3_bind_null (stmt, 4);
816
sqlite3_bind_null (stmt, 5);
818
gaiaExifTagGetSignedRational1Value (pT,
822
sqlite3_bind_null (stmt, 6);
824
sqlite3_bind_int64 (stmt, 6, val64);
826
gaiaExifTagGetSignedRational2Value (pT,
830
sqlite3_bind_null (stmt, 7);
832
sqlite3_bind_int64 (stmt, 7, val64);
834
gaiaExifTagGetSignedRationalValue (pT, iv,
837
sqlite3_bind_null (stmt, 8);
839
sqlite3_bind_double (stmt, 8, dblval);
841
if (gaiaExifTagGetValueType (pT) == 11)
843
sqlite3_bind_null (stmt, 4);
844
sqlite3_bind_null (stmt, 5);
845
sqlite3_bind_null (stmt, 6);
846
sqlite3_bind_null (stmt, 7);
848
gaiaExifTagGetFloatValue (pT, iv, &ok);
850
sqlite3_bind_null (stmt, 8);
852
sqlite3_bind_double (stmt, 8, dblval);
854
if (gaiaExifTagGetValueType (pT) == 12)
856
sqlite3_bind_null (stmt, 4);
857
sqlite3_bind_null (stmt, 5);
858
sqlite3_bind_null (stmt, 6);
859
sqlite3_bind_null (stmt, 7);
861
gaiaExifTagGetDoubleValue (pT, iv, &ok);
863
sqlite3_bind_null (stmt, 8);
865
sqlite3_bind_double (stmt, 8, dblval);
868
sqlite3_bind_null (stmt, 9);
870
sqlite3_bind_text (stmt, 9, human,
873
ret = sqlite3_step (stmt);
874
if (ret == SQLITE_DONE || ret == SQLITE_ROW)
878
printf ("sqlite3_step() error: %s\n",
879
sqlite3_errmsg (handle));
880
sqlite3_finalize (stmt);
883
if (gaiaExifTagGetValueType (pT) == 1
884
|| gaiaExifTagGetValueType (pT) == 2
885
|| gaiaExifTagGetValueType (pT) == 6
886
|| gaiaExifTagGetValueType (pT) == 7)
892
sqlite3_finalize (stmt);
894
/* commits the transaction */
895
strcpy (sql, "COMMIT");
896
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
897
if (ret != SQLITE_OK)
899
printf ("COMMIT error: %s\n", err_msg);
900
sqlite3_free (err_msg);
904
/* rolling back the transaction */
905
strcpy (sql, "ROLLBACK");
906
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
907
if (ret != SQLITE_OK)
909
printf ("ROLLBACK error: %s\n", err_msg);
910
sqlite3_free (err_msg);
916
load_file (sqlite3 * handle, const char *file_path, int gps_only, int metadata)
918
/* importing a single EXIF file */
926
unsigned char *blob = NULL;
927
gaiaExifTagListPtr tag_list = NULL;
928
fl = fopen (file_path, "rb");
931
sprintf (msg, "exif_loader: cannot open file '%s'", file_path);
935
if (fseek (fl, 0, SEEK_END) == 0)
941
rd = fread (blob, 1, sz, fl);
944
tag_list = gaiaGetExifTags (blob, sz);
948
if (gps_only && !isExifGps (tag_list))
953
if (!updateExifTables
954
(handle, blob, sz, tag_list, metadata, file_path))
962
printf ("file '%s' doesn't seems to be a valid EXIF\n", file_path);
967
("file '%s' is a valid EXIF, but doesn't contains any GPS info\n",
970
printf ("SQL error(s): file '%s' was not loaded\n", file_path);
973
printf ("file '%s' successfully loaded\n", file_path);
977
gaiaExifTagsFree (tag_list);
983
load_dir (sqlite3 * handle, const char *dir_path, int gps_only, int metadata)
985
/* importing EXIF files from a whole DIRECTORY */
986
#if defined(_WIN32) && !defined(__MINGW32__)
987
/* Visual Studio .NET */
988
struct _finddata_t c_file;
991
char file_path[1024];
992
if (_chdir (dir_path) < 0)
994
fprintf (stderr, "exif_loader: cannot access dir '%s'", dir_path);
997
if ((hFile = _findfirst ("*.*", &c_file)) == -1L)
998
fprintf (stderr, "exif_loader: cannot access dir '%s' [or empty dir]\n",
1004
if ((c_file.attrib & _A_RDONLY) == _A_RDONLY
1005
|| (c_file.attrib & _A_NORMAL) == _A_NORMAL)
1007
sprintf (file_path, "%s\\%s", dir_path, c_file.name);
1008
cnt += load_file (handle, file_path, gps_only, metadata);
1010
if (_findnext (hFile, &c_file) != 0)
1017
/* not Visual Studio .NET */
1019
char file_path[4096];
1021
struct dirent *entry;
1022
DIR *dir = opendir (dir_path);
1025
sprintf (msg, "exif_loader: cannot access dir '%s'", dir_path);
1031
/* scanning dir-entries */
1032
entry = readdir (dir);
1035
sprintf (file_path, "%s/%s", dir_path, entry->d_name);
1036
cnt += load_file (handle, file_path, gps_only, metadata);
1044
checkExifTables (sqlite3 * handle)
1046
/* creates the EXIF DB tables / or checks existing ones for validity */
1049
char *err_msg = NULL;
1056
int ok_shotDateTime;
1058
int ok_gpsDirection;
1059
int ok_gpsSatellites;
1060
int ok_gpsTimestamp;
1074
int ok_humanReadable;
1078
int ok_valueIndexPk;
1085
/* creating the ExifPhoto table */
1086
strcpy (sql, "CREATE TABLE IF NOT EXISTS ExifPhoto (\n");
1087
strcat (sql, "PhotoId INTEGER PRIMARY KEY AUTOINCREMENT,\n");
1088
strcat (sql, "Photo BLOB NOT NULL,\n");
1089
strcat (sql, "PixelX INTEGER,\n");
1090
strcat (sql, "PixelY INTEGER,\n");
1091
strcat (sql, "CameraMake TEXT,\n");
1092
strcat (sql, "CameraModel TEXT,\n");
1093
strcat (sql, "ShotDateTime DOUBLE,\n");
1094
strcat (sql, "GpsGeometry BLOB, ");
1095
strcat (sql, "GpsDirection DOUBLE, ");
1096
strcat (sql, "GpsSatellites TEXT,\n");
1097
strcat (sql, "GpsTimestamp DOUBLE, ");
1098
strcat (sql, "FromPath TEXT");
1100
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
1101
if (ret != SQLITE_OK)
1103
printf ("CREATE TABLE ExifPhoto error: %s\n", err_msg);
1104
sqlite3_free (err_msg);
1107
/* checking the ExifPhoto table for sanity */
1114
ok_shotDateTime = 0;
1116
ok_gpsDirection = 0;
1117
ok_gpsSatellites = 0;
1118
ok_gpsTimestamp = 0;
1122
strcpy (sql, "PRAGMA table_info(\"ExifPhoto\")");
1123
ret = sqlite3_get_table (handle, sql, &results, &rows, &columns, &err_msg);
1124
if (ret != SQLITE_OK)
1126
printf ("PRAGMA table_info(\"ExifPhoto\") error: %s\n", err_msg);
1127
sqlite3_free (err_msg);
1134
for (i = 1; i <= rows; i++)
1136
name = results[(i * columns) + 1];
1137
if (atoi (results[(i * columns) + 5]) == 0)
1141
if (strcasecmp (name, "PhotoId") == 0)
1143
if (strcasecmp (name, "Photo") == 0)
1145
if (strcasecmp (name, "PixelX") == 0)
1147
if (strcasecmp (name, "PixelY") == 0)
1149
if (strcasecmp (name, "CameraMake") == 0)
1151
if (strcasecmp (name, "CameraModel") == 0)
1153
if (strcasecmp (name, "ShotDateTime") == 0)
1154
ok_shotDateTime = 1;
1155
if (strcasecmp (name, "GpsGeometry") == 0)
1157
if (strcasecmp (name, "GpsDirection") == 0)
1158
ok_gpsDirection = 1;
1159
if (strcasecmp (name, "GpsTimestamp") == 0)
1160
ok_gpsTimestamp = 1;
1161
if (strcasecmp (name, "FromPath") == 0)
1165
if (strcasecmp (name, "PhotoId") == 0)
1172
sqlite3_free_table (results);
1173
if (ok_photoId && ok_photo && ok_pixelX && ok_pixelY && ok_cameraMake
1174
&& ok_cameraModel && ok_shotDateTime && ok_gpsGeometry
1175
&& ok_gpsDirection && ok_gpsTimestamp && ok_fromPath && ok_photoIdPk
1181
("ERROR: table 'ExifPhoto' already exists, but has incompatible columns\n");
1184
/* creating the ExifTags table */
1185
strcpy (sql, "CREATE TABLE IF NOT EXISTS ExifTags (\n");
1186
strcat (sql, "PhotoId INTEGER NOT NULL,\n");
1187
strcat (sql, "TagId INTEGER NOT NULL,\n");
1188
strcat (sql, "TagName TEXT NOT NULL,\n");
1189
strcat (sql, "GpsTag INTEGER NOT NULL CHECK (GpsTag IN (0, 1)),\n");
1191
"ValueType INTEGER NOT NULL CHECK (ValueType IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)),\n");
1192
strcat (sql, "TypeName TEXT NOT NULL,\n");
1193
strcat (sql, "CountValues INTEGER NOT NULL,\n");
1194
strcat (sql, "PRIMARY KEY (PhotoId, TagId)");
1196
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
1197
if (ret != SQLITE_OK)
1199
printf ("CREATE TABLE ExifTags error: %s\n", err_msg);
1200
sqlite3_free (err_msg);
1203
/* checking the ExifTags table for sanity */
1214
strcpy (sql, "PRAGMA table_info(\"ExifTags\")");
1215
ret = sqlite3_get_table (handle, sql, &results, &rows, &columns, &err_msg);
1216
if (ret != SQLITE_OK)
1218
printf ("PRAGMA table_info(\"ExifTags\") error: %s\n", err_msg);
1219
sqlite3_free (err_msg);
1226
for (i = 1; i <= rows; i++)
1228
name = results[(i * columns) + 1];
1229
if (atoi (results[(i * columns) + 5]) == 0)
1233
if (strcasecmp (name, "PhotoId") == 0)
1235
if (strcasecmp (name, "TagId") == 0)
1237
if (strcasecmp (name, "TagName") == 0)
1239
if (strcasecmp (name, "GpsTag") == 0)
1241
if (strcasecmp (name, "ValueType") == 0)
1243
if (strcasecmp (name, "TypeName") == 0)
1245
if (strcasecmp (name, "CountValues") == 0)
1249
if (strcasecmp (name, "PhotoId") == 0)
1251
else if (strcasecmp (name, "TagId") == 0)
1258
sqlite3_free_table (results);
1259
if (ok_photoId && ok_tagId && ok_tagName && ok_gpsTag && ok_valueType
1260
&& ok_typeName && ok_countValues && ok_photoIdPk && ok_tagIdPk
1266
("ERROR: table 'ExifTags' already exists, but has incompatible columns\n");
1269
/* creating the ExifValues table */
1270
strcpy (sql, "CREATE TABLE IF NOT EXISTS ExifValues (\n");
1271
strcat (sql, "PhotoId INTEGER NOT NULL,\n");
1272
strcat (sql, "TagId INTEGER NOT NULL,\n");
1273
strcat (sql, "ValueIndex INTEGER NOT NULL,\n");
1274
strcat (sql, "ByteValue BLOB,\n");
1275
strcat (sql, "StringValue TEXT,\n");
1276
strcat (sql, "NumValue INTEGER,\n");
1277
strcat (sql, "NumValueBis INTEGER,\n");
1278
strcat (sql, "DoubleValue DOUBLE,\n");
1279
strcat (sql, "HumanReadable TEXT,\n");
1280
strcat (sql, "PRIMARY KEY (PhotoId, TagId, ValueIndex)");
1282
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
1283
if (ret != SQLITE_OK)
1285
printf ("CREATE TABLE ExifValues error: %s\n", err_msg);
1286
sqlite3_free (err_msg);
1289
/* checking the ExifValues table for sanity */
1298
ok_humanReadable = 0;
1301
ok_valueIndexPk = 0;
1303
strcpy (sql, "PRAGMA table_info(\"ExifValues\")");
1304
ret = sqlite3_get_table (handle, sql, &results, &rows, &columns, &err_msg);
1305
if (ret != SQLITE_OK)
1307
printf ("PRAGMA table_info(\"ExifValues\") error: %s\n", err_msg);
1308
sqlite3_free (err_msg);
1315
for (i = 1; i <= rows; i++)
1317
name = results[(i * columns) + 1];
1318
if (atoi (results[(i * columns) + 5]) == 0)
1322
if (strcasecmp (name, "PhotoId") == 0)
1324
if (strcasecmp (name, "TagId") == 0)
1326
if (strcasecmp (name, "ValueIndex") == 0)
1328
if (strcasecmp (name, "ByteValue") == 0)
1330
if (strcasecmp (name, "StringValue") == 0)
1332
if (strcasecmp (name, "NumValue") == 0)
1334
if (strcasecmp (name, "NumValueBis") == 0)
1336
if (strcasecmp (name, "DoubleValue") == 0)
1338
if (strcasecmp (name, "HumanReadable") == 0)
1339
ok_humanReadable = 1;
1342
if (strcasecmp (name, "PhotoId") == 0)
1344
else if (strcasecmp (name, "TagId") == 0)
1346
else if (strcasecmp (name, "ValueIndex") == 0)
1347
ok_valueIndexPk = 1;
1353
sqlite3_free_table (results);
1354
if (ok_photoId && ok_tagId && ok_valueIndex && ok_byteValue
1355
&& ok_stringValue && ok_numValue && ok_numValueBis && ok_doubleValue
1356
&& ok_humanReadable && ok_photoIdPk && ok_tagIdPk && ok_valueIndexPk
1362
("ERROR: table 'ExifValues' already exists, but has incompatible columns\n");
1365
/* creating the ExifView view */
1366
strcpy (sql, "CREATE VIEW IF NOT EXISTS \"ExifMetadata\" AS\n");
1367
strcat (sql, "SELECT p.\"PhotoId\" AS 'PhotoId', ");
1368
strcat (sql, "t.\"TagId\" AS 'TagId', ");
1369
strcat (sql, "t.\"TagName\" AS 'TagName',");
1370
strcat (sql, "t.\"GpsTag\" AS 'GpsTag',\n");
1371
strcat (sql, "t.\"ValueType\" AS 'ValueType',");
1372
strcat (sql, "t.\"TypeName\" AS 'TypeName', ");
1373
strcat (sql, "t.\"CountValues\" AS 'CountValues', ");
1374
strcat (sql, "v.\"ValueIndex\" AS 'ValueIndex',\n");
1375
strcat (sql, "v.\"ByteValue\" AS 'ByteValue', ");
1376
strcat (sql, "v.\"StringValue\" AS 'StringValue', ");
1377
strcat (sql, "v.\"NumValue\" AS 'NumValue', ");
1378
strcat (sql, "v.\"NumValueBis\" AS 'NumValueBis',\n");
1379
strcat (sql, "v.\"DoubleValue\" AS 'DoubleValue', ");
1380
strcat (sql, "v.\"HumanReadable\" AS 'HumanReadable'\n");
1382
"FROM \"ExifPhoto\" AS p, \"ExifTags\" AS t, \"ExifValues\" AS v\n");
1384
"WHERE t.\"PhotoId\" = p.\"PhotoId\" AND v.\"PhotoId\" = t.\"PhotoId\" AND v.\"TagId\" = t.\"TagId\"");
1385
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
1386
if (ret != SQLITE_OK)
1388
printf ("CREATE VIEW ExifMetadata error: %s\n", err_msg);
1389
sqlite3_free (err_msg);
1398
spatialite_autocreate (sqlite3 * db)
1400
/* attempting to perform self-initialization for a newly created DB */
1403
char *err_msg = NULL;
1410
/* checking if this DB is really empty */
1411
strcpy (sql, "SELECT Count(*) from sqlite_master");
1412
ret = sqlite3_get_table (db, sql, &results, &rows, &columns, NULL);
1413
if (ret != SQLITE_OK)
1419
for (i = 1; i <= rows; i++)
1420
count = atoi (results[(i * columns) + 0]);
1422
sqlite3_free_table (results);
1427
/* all right, it's empty: proceding to initialize */
1428
strcpy (sql, "SELECT InitSpatialMetadata()");
1429
ret = sqlite3_exec (db, sql, NULL, NULL, &err_msg);
1430
if (ret != SQLITE_OK)
1432
fprintf (stderr, "InitSpatialMetadata() error: %s\n", err_msg);
1433
sqlite3_free (err_msg);
1441
/* printing the argument list */
1442
fprintf (stderr, "\n\nusage: exif_loader ARGLIST\n");
1444
"==============================================================\n");
1446
"-h or --help print this help message\n");
1448
"-d or --db-path pathname the SpatiaLite db path\n");
1450
"-D or --dir dir_path the DIR path containing EXIF files\n");
1451
fprintf (stderr, "-f or --file file_name a single EXIF file\n");
1452
fprintf (stderr, "you can specify the following options as well\n");
1453
fprintf (stderr, "--any-exif *default*\n");
1454
fprintf (stderr, "--gps-exif-only\n\n");
1455
fprintf (stderr, "--metadata *default*\n");
1456
fprintf (stderr, "--no-metadata\n\n");
1460
main (int argc, char *argv[])
1462
/* the MAIN function simply perform arguments checking */
1464
int next_arg = ARG_NONE;
1466
char *dir_path = NULL;
1467
char *file_path = NULL;
1474
for (i = 1; i < argc; i++)
1476
/* parsing the invocation arguments */
1477
if (next_arg != ARG_NONE)
1488
file_path = argv[i];
1491
next_arg = ARG_NONE;
1494
if (strcasecmp (argv[i], "--help") == 0
1495
|| strcmp (argv[i], "-h") == 0)
1500
if (strcasecmp (argv[i], "--db-path") == 0)
1502
next_arg = ARG_DB_PATH;
1505
if (strcmp (argv[i], "-d") == 0)
1507
next_arg = ARG_DB_PATH;
1510
if (strcasecmp (argv[i], "--dir-path") == 0)
1515
if (strcmp (argv[i], "-D") == 0)
1520
if (strcasecmp (argv[i], "--file-path") == 0)
1522
next_arg = ARG_FILE;
1525
if (strcmp (argv[i], "-f") == 0)
1527
next_arg = ARG_FILE;
1530
if (strcasecmp (argv[i], "--any-exif") == 0)
1535
if (strcasecmp (argv[i], "--gps-exif-only") == 0)
1540
if (strcasecmp (argv[i], "--metatada") == 0)
1545
if (strcasecmp (argv[i], "--no-metadata") == 0)
1550
fprintf (stderr, "unknown argument: %s\n", argv[i]);
1558
/* checking the arguments */
1561
fprintf (stderr, "did you forget setting the --db-path argument ?\n");
1564
if (!dir_path && !file_path)
1567
"did you forget setting the --dir_path OR --file_path argument ?\n");
1570
if (dir_path && file_path)
1573
"--dir_path AND --file_path argument are mutually exclusive\n");
1581
/* trying to connect the SpatiaLite DB */
1583
sqlite3_open_v2 (path, &handle,
1584
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
1585
if (ret != SQLITE_OK)
1587
fprintf (stderr, "cannot open '%s': %s\n", path,
1588
sqlite3_errmsg (handle));
1589
sqlite3_close (handle);
1592
spatialite_autocreate (handle);
1593
if (!checkExifTables (handle))
1596
"An EXIF table is already defined, but has incompatibles columns\n\nSorry ...\n");
1597
sqlite3_close (handle);
1601
cnt = load_dir (handle, dir_path, gps_only, metadata);
1603
cnt = load_file (handle, file_path, gps_only, metadata);
1604
ret = sqlite3_close (handle);
1605
if (ret != SQLITE_OK)
1606
fprintf (stderr, "sqlite3_close() error: %s\n",
1607
sqlite3_errmsg (handle));
1610
"\n\n*** %d EXIF photo%s successfully inserted into the DB\n",
1611
cnt, (cnt > 1) ? "s where" : " was");