4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
11 ******************************************************************************
13 ** This SQLite extension implements SQL functions readfile() and
14 ** writefile(), and eponymous virtual type "fsdir".
16 ** WRITEFILE(FILE, DATA [, MODE [, MTIME]]):
18 ** If neither of the optional arguments is present, then this UDF
19 ** function writes blob DATA to file FILE. If successful, the number
20 ** of bytes written is returned. If an error occurs, NULL is returned.
22 ** If the first option argument - MODE - is present, then it must
23 ** be passed an integer value that corresponds to a POSIX mode
24 ** value (file type + permissions, as returned in the stat.st_mode
25 ** field by the stat() system call). Three types of files may
26 ** be written/created:
28 ** regular files: (mode & 0170000)==0100000
29 ** symbolic links: (mode & 0170000)==0120000
30 ** directories: (mode & 0170000)==0040000
32 ** For a directory, the DATA is ignored. For a symbolic link, it is
33 ** interpreted as text and used as the target of the link. For a
34 ** regular file, it is interpreted as a blob and written into the
35 ** named file. Regardless of the type of file, its permissions are
36 ** set to (mode & 0777) before returning.
38 ** If the optional MTIME argument is present, then it is interpreted
39 ** as an integer - the number of seconds since the unix epoch. The
40 ** modification-time of the target file is set to this value before
43 ** If three or more arguments are passed to this function and an
44 ** error is encountered, an exception is raised.
48 ** Read and return the contents of file FILE (type blob) from disk.
54 ** SELECT * FROM fsdir($path [, $dir]);
56 ** Parameter $path is an absolute or relative pathname. If the file that it
57 ** refers to does not exist, it is an error. If the path refers to a regular
58 ** file or symbolic link, it returns a single row. Or, if the path refers
59 ** to a directory, it returns one row for the directory, and one row for each
60 ** file within the hierarchy rooted at $path.
62 ** Each row has the following columns:
64 ** name: Path to file or directory (text value).
65 ** mode: Value of stat.st_mode for directory entry (an integer).
66 ** mtime: Value of stat.st_mtime for directory entry (an integer).
67 ** data: For a regular file, a blob containing the file data. For a
68 ** symlink, a text value containing the text of the link. For a
71 ** If a non-NULL value is specified for the optional $dir parameter and
72 ** $path is a relative path, then $path is interpreted relative to $dir.
73 ** And the paths returned in the "name" column of the table are also
74 ** relative to directory $dir.
76 #include "sqlite3ext.h"
77 SQLITE_EXTENSION_INIT1
82 #include <sys/types.h>
85 #if !defined(_WIN32) && !defined(WIN32)
89 # include <sys/time.h>
94 # include "test_windirent.h"
95 # define dirent DIRENT
99 # define mkdir(path,mode) _mkdir(path)
100 # define lstat(path,buf) stat(path,buf)
106 #define FSDIR_SCHEMA "(name,mode,mtime,data,path HIDDEN,dir HIDDEN)"
109 ** Set the result stored by context ctx to a blob containing the
110 ** contents of file zName.
112 static void readFileContents(sqlite3_context
*ctx
, const char *zName
){
117 in
= fopen(zName
, "rb");
119 fseek(in
, 0, SEEK_END
);
122 pBuf
= sqlite3_malloc( nIn
);
123 if( pBuf
&& 1==fread(pBuf
, nIn
, 1, in
) ){
124 sqlite3_result_blob(ctx
, pBuf
, nIn
, sqlite3_free
);
132 ** Implementation of the "readfile(X)" SQL function. The entire content
133 ** of the file named X is read and returned as a BLOB. NULL is returned
134 ** if the file does not exist or is unreadable.
136 static void readfileFunc(
137 sqlite3_context
*context
,
142 (void)(argc
); /* Unused parameter */
143 zName
= (const char*)sqlite3_value_text(argv
[0]);
144 if( zName
==0 ) return;
145 readFileContents(context
, zName
);
149 ** Set the error message contained in context ctx to the results of
150 ** vprintf(zFmt, ...).
152 static void ctxErrorMsg(sqlite3_context
*ctx
, const char *zFmt
, ...){
156 zMsg
= sqlite3_vmprintf(zFmt
, ap
);
157 sqlite3_result_error(ctx
, zMsg
, -1);
163 ** Argument zFile is the name of a file that will be created and/or written
164 ** by SQL function writefile(). This function ensures that the directory
165 ** zFile will be written to exists, creating it if required. The permissions
166 ** for any path components created by this function are set to (mode&0777).
168 ** If an OOM condition is encountered, SQLITE_NOMEM is returned. Otherwise,
169 ** SQLITE_OK is returned if the directory is successfully created, or
170 ** SQLITE_ERROR otherwise.
172 static int makeDirectory(
176 char *zCopy
= sqlite3_mprintf("%s", zFile
);
182 int nCopy
= (int)strlen(zCopy
);
185 while( rc
==SQLITE_OK
){
189 for(; zCopy
[i
]!='/' && i
<nCopy
; i
++);
190 if( i
==nCopy
) break;
193 rc2
= stat(zCopy
, &sStat
);
195 if( mkdir(zCopy
, mode
& 0777) ) rc
= SQLITE_ERROR
;
197 if( !S_ISDIR(sStat
.st_mode
) ) rc
= SQLITE_ERROR
;
210 ** This function does the work for the writefile() UDF. Refer to
211 ** header comments at the top of this file for details.
213 static int writeFile(
214 sqlite3_context
*pCtx
, /* Context to return bytes written in */
215 const char *zFile
, /* File to write */
216 sqlite3_value
*pData
, /* Data to write */
217 mode_t mode
, /* MODE parameter passed to writefile() */
218 sqlite3_int64 mtime
/* MTIME parameter (or -1 to not set time) */
220 #if !defined(_WIN32) && !defined(WIN32)
222 const char *zTo
= (const char*)sqlite3_value_text(pData
);
223 if( symlink(zTo
, zFile
)<0 ) return 1;
228 if( mkdir(zFile
, mode
) ){
229 /* The mkdir() call to create the directory failed. This might not
230 ** be an error though - if there is already a directory at the same
231 ** path and either the permissions already match or can be changed
232 ** to do so using chmod(), it is not an error. */
235 || 0!=stat(zFile
, &sStat
)
236 || !S_ISDIR(sStat
.st_mode
)
237 || ((sStat
.st_mode
&0777)!=(mode
&0777) && 0!=chmod(zFile
, mode
&0777))
243 sqlite3_int64 nWrite
= 0;
246 FILE *out
= fopen(zFile
, "wb");
247 if( out
==0 ) return 1;
248 z
= (const char*)sqlite3_value_blob(pData
);
250 sqlite3_int64 n
= fwrite(z
, 1, sqlite3_value_bytes(pData
), out
);
251 nWrite
= sqlite3_value_bytes(pData
);
257 if( rc
==0 && mode
&& chmod(zFile
, mode
& 0777) ){
261 sqlite3_result_int64(pCtx
, nWrite
);
270 SYSTEMTIME currentTime
;
274 extern LPWSTR
sqlite3_win32_utf8_to_unicode(const char*);
276 GetSystemTime(¤tTime
);
277 SystemTimeToFileTime(¤tTime
, &lastAccess
);
278 intervals
= Int32x32To64(mtime
, 10000000) + 116444736000000000;
279 lastWrite
.dwLowDateTime
= (DWORD
)intervals
;
280 lastWrite
.dwHighDateTime
= intervals
>> 32;
281 zUnicodeName
= sqlite3_win32_utf8_to_unicode(zFile
);
283 zUnicodeName
, FILE_WRITE_ATTRIBUTES
, 0, NULL
, OPEN_EXISTING
,
284 FILE_FLAG_BACKUP_SEMANTICS
, NULL
286 sqlite3_free(zUnicodeName
);
287 if( hFile
!=INVALID_HANDLE_VALUE
){
288 BOOL bResult
= SetFileTime(hFile
, NULL
, &lastAccess
, &lastWrite
);
294 #elif defined(AT_FDCWD) && 0 /* utimensat() is not univerally available */
296 struct timespec times
[2];
297 times
[0].tv_nsec
= times
[1].tv_nsec
= 0;
298 times
[0].tv_sec
= time(0);
299 times
[1].tv_sec
= mtime
;
300 if( utimensat(AT_FDCWD
, zFile
, times
, AT_SYMLINK_NOFOLLOW
) ){
305 struct timeval times
[2];
306 times
[0].tv_usec
= times
[1].tv_usec
= 0;
307 times
[0].tv_sec
= time(0);
308 times
[1].tv_sec
= mtime
;
309 if( utimes(zFile
, times
) ){
319 ** Implementation of the "writefile(W,X[,Y[,Z]]])" SQL function.
320 ** Refer to header comments at the top of this file for details.
322 static void writefileFunc(
323 sqlite3_context
*context
,
330 sqlite3_int64 mtime
= -1;
332 if( argc
<2 || argc
>4 ){
333 sqlite3_result_error(context
,
334 "wrong number of arguments to function writefile()", -1
339 zFile
= (const char*)sqlite3_value_text(argv
[0]);
340 if( zFile
==0 ) return;
342 mode
= (mode_t
)sqlite3_value_int(argv
[2]);
345 mtime
= sqlite3_value_int64(argv
[3]);
348 res
= writeFile(context
, zFile
, argv
[1], mode
, mtime
);
349 if( res
==1 && errno
==ENOENT
){
350 if( makeDirectory(zFile
, mode
)==SQLITE_OK
){
351 res
= writeFile(context
, zFile
, argv
[1], mode
, mtime
);
355 if( argc
>2 && res
!=0 ){
357 ctxErrorMsg(context
, "failed to create symlink: %s", zFile
);
358 }else if( S_ISDIR(mode
) ){
359 ctxErrorMsg(context
, "failed to create directory: %s", zFile
);
361 ctxErrorMsg(context
, "failed to write file: %s", zFile
);
367 ** SQL function: lsmode(MODE)
369 ** Given a numberic st_mode from stat(), convert it into a human-readable
370 ** text string in the style of "ls -l".
372 static void lsModeFunc(
373 sqlite3_context
*context
,
378 int iMode
= sqlite3_value_int(argv
[0]);
381 if( S_ISLNK(iMode
) ){
383 }else if( S_ISREG(iMode
) ){
385 }else if( S_ISDIR(iMode
) ){
391 int m
= (iMode
>> ((2-i
)*3));
392 char *a
= &z
[1 + i
*3];
393 a
[0] = (m
& 0x4) ? 'r' : '-';
394 a
[1] = (m
& 0x2) ? 'w' : '-';
395 a
[2] = (m
& 0x1) ? 'x' : '-';
398 sqlite3_result_text(context
, z
, -1, SQLITE_TRANSIENT
);
401 #ifndef SQLITE_OMIT_VIRTUALTABLE
404 ** Cursor type for recursively iterating through a directory structure.
406 typedef struct fsdir_cursor fsdir_cursor
;
407 typedef struct FsdirLevel FsdirLevel
;
410 DIR *pDir
; /* From opendir() */
411 char *zDir
; /* Name of directory (nul-terminated) */
414 struct fsdir_cursor
{
415 sqlite3_vtab_cursor base
; /* Base class - must be first */
417 int nLvl
; /* Number of entries in aLvl[] array */
418 int iLvl
; /* Index of current entry */
419 FsdirLevel
*aLvl
; /* Hierarchy of directories being traversed */
424 struct stat sStat
; /* Current lstat() results */
425 char *zPath
; /* Path to current entry */
426 sqlite3_int64 iRowid
; /* Current rowid */
429 typedef struct fsdir_tab fsdir_tab
;
431 sqlite3_vtab base
; /* Base class - must be first */
435 ** Construct a new fsdir virtual table object.
437 static int fsdirConnect(
440 int argc
, const char *const*argv
,
441 sqlite3_vtab
**ppVtab
,
450 rc
= sqlite3_declare_vtab(db
, "CREATE TABLE x" FSDIR_SCHEMA
);
452 pNew
= (fsdir_tab
*)sqlite3_malloc( sizeof(*pNew
) );
453 if( pNew
==0 ) return SQLITE_NOMEM
;
454 memset(pNew
, 0, sizeof(*pNew
));
456 *ppVtab
= (sqlite3_vtab
*)pNew
;
461 ** This method is the destructor for fsdir vtab objects.
463 static int fsdirDisconnect(sqlite3_vtab
*pVtab
){
469 ** Constructor for a new fsdir_cursor object.
471 static int fsdirOpen(sqlite3_vtab
*p
, sqlite3_vtab_cursor
**ppCursor
){
474 pCur
= sqlite3_malloc( sizeof(*pCur
) );
475 if( pCur
==0 ) return SQLITE_NOMEM
;
476 memset(pCur
, 0, sizeof(*pCur
));
478 *ppCursor
= &pCur
->base
;
483 ** Reset a cursor back to the state it was in when first returned
486 static void fsdirResetCursor(fsdir_cursor
*pCur
){
488 for(i
=0; i
<=pCur
->iLvl
; i
++){
489 FsdirLevel
*pLvl
= &pCur
->aLvl
[i
];
490 if( pLvl
->pDir
) closedir(pLvl
->pDir
);
491 sqlite3_free(pLvl
->zDir
);
493 sqlite3_free(pCur
->zPath
);
494 sqlite3_free(pCur
->aLvl
);
505 ** Destructor for an fsdir_cursor.
507 static int fsdirClose(sqlite3_vtab_cursor
*cur
){
508 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
510 fsdirResetCursor(pCur
);
516 ** Set the error message for the virtual table associated with cursor
517 ** pCur to the results of vprintf(zFmt, ...).
519 static void fsdirSetErrmsg(fsdir_cursor
*pCur
, const char *zFmt
, ...){
522 pCur
->base
.pVtab
->zErrMsg
= sqlite3_vmprintf(zFmt
, ap
);
528 ** Advance an fsdir_cursor to its next row of output.
530 static int fsdirNext(sqlite3_vtab_cursor
*cur
){
531 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
532 mode_t m
= pCur
->sStat
.st_mode
;
536 /* Descend into this directory */
537 int iNew
= pCur
->iLvl
+ 1;
539 if( iNew
>=pCur
->nLvl
){
541 int nByte
= nNew
*sizeof(FsdirLevel
);
542 FsdirLevel
*aNew
= (FsdirLevel
*)sqlite3_realloc(pCur
->aLvl
, nByte
);
543 if( aNew
==0 ) return SQLITE_NOMEM
;
544 memset(&aNew
[pCur
->nLvl
], 0, sizeof(FsdirLevel
)*(nNew
-pCur
->nLvl
));
549 pLvl
= &pCur
->aLvl
[iNew
];
551 pLvl
->zDir
= pCur
->zPath
;
553 pLvl
->pDir
= opendir(pLvl
->zDir
);
555 fsdirSetErrmsg(pCur
, "cannot read directory: %s", pCur
->zPath
);
560 while( pCur
->iLvl
>=0 ){
561 FsdirLevel
*pLvl
= &pCur
->aLvl
[pCur
->iLvl
];
562 struct dirent
*pEntry
= readdir(pLvl
->pDir
);
564 if( pEntry
->d_name
[0]=='.' ){
565 if( pEntry
->d_name
[1]=='.' && pEntry
->d_name
[2]=='\0' ) continue;
566 if( pEntry
->d_name
[1]=='\0' ) continue;
568 sqlite3_free(pCur
->zPath
);
569 pCur
->zPath
= sqlite3_mprintf("%s/%s", pLvl
->zDir
, pEntry
->d_name
);
570 if( pCur
->zPath
==0 ) return SQLITE_NOMEM
;
571 if( lstat(pCur
->zPath
, &pCur
->sStat
) ){
572 fsdirSetErrmsg(pCur
, "cannot stat file: %s", pCur
->zPath
);
577 closedir(pLvl
->pDir
);
578 sqlite3_free(pLvl
->zDir
);
585 sqlite3_free(pCur
->zPath
);
591 ** Return values of columns for the row at which the series_cursor
592 ** is currently pointing.
594 static int fsdirColumn(
595 sqlite3_vtab_cursor
*cur
, /* The cursor */
596 sqlite3_context
*ctx
, /* First argument to sqlite3_result_...() */
597 int i
/* Which column to return */
599 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
602 sqlite3_result_text(ctx
, &pCur
->zPath
[pCur
->nBase
], -1, SQLITE_TRANSIENT
);
607 sqlite3_result_int64(ctx
, pCur
->sStat
.st_mode
);
611 sqlite3_result_int64(ctx
, pCur
->sStat
.st_mtime
);
615 mode_t m
= pCur
->sStat
.st_mode
;
617 sqlite3_result_null(ctx
);
618 #if !defined(_WIN32) && !defined(WIN32)
619 }else if( S_ISLNK(m
) ){
621 char *aBuf
= aStatic
;
626 n
= readlink(pCur
->zPath
, aBuf
, nBuf
);
628 if( aBuf
!=aStatic
) sqlite3_free(aBuf
);
630 aBuf
= sqlite3_malloc(nBuf
);
632 sqlite3_result_error_nomem(ctx
);
637 sqlite3_result_text(ctx
, aBuf
, n
, SQLITE_TRANSIENT
);
638 if( aBuf
!=aStatic
) sqlite3_free(aBuf
);
641 readFileContents(ctx
, pCur
->zPath
);
649 ** Return the rowid for the current row. In this implementation, the
650 ** first row returned is assigned rowid value 1, and each subsequent
651 ** row a value 1 more than that of the previous.
653 static int fsdirRowid(sqlite3_vtab_cursor
*cur
, sqlite_int64
*pRowid
){
654 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
655 *pRowid
= pCur
->iRowid
;
660 ** Return TRUE if the cursor has been moved off of the last
663 static int fsdirEof(sqlite3_vtab_cursor
*cur
){
664 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
665 return (pCur
->zPath
==0);
671 static int fsdirFilter(
672 sqlite3_vtab_cursor
*cur
,
673 int idxNum
, const char *idxStr
,
674 int argc
, sqlite3_value
**argv
676 const char *zDir
= 0;
677 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
679 fsdirResetCursor(pCur
);
682 fsdirSetErrmsg(pCur
, "table function fsdir requires an argument");
686 assert( argc
==idxNum
&& (argc
==1 || argc
==2) );
687 zDir
= (const char*)sqlite3_value_text(argv
[0]);
689 fsdirSetErrmsg(pCur
, "table function fsdir requires a non-NULL argument");
693 pCur
->zBase
= (const char*)sqlite3_value_text(argv
[1]);
696 pCur
->nBase
= (int)strlen(pCur
->zBase
)+1;
697 pCur
->zPath
= sqlite3_mprintf("%s/%s", pCur
->zBase
, zDir
);
699 pCur
->zPath
= sqlite3_mprintf("%s", zDir
);
702 if( pCur
->zPath
==0 ){
705 if( lstat(pCur
->zPath
, &pCur
->sStat
) ){
706 fsdirSetErrmsg(pCur
, "cannot stat file: %s", pCur
->zPath
);
714 ** SQLite will invoke this method one or more times while planning a query
715 ** that uses the generate_series virtual table. This routine needs to create
716 ** a query plan for each invocation and compute an estimated cost for that
719 ** In this implementation idxNum is used to represent the
720 ** query plan. idxStr is unused.
722 ** The query plan is represented by bits in idxNum:
724 ** (1) start = $value -- constraint exists
725 ** (2) stop = $value -- constraint exists
726 ** (4) step = $value -- constraint exists
727 ** (8) output in descending order
729 static int fsdirBestIndex(
731 sqlite3_index_info
*pIdxInfo
733 int i
; /* Loop over constraints */
736 const struct sqlite3_index_constraint
*pConstraint
;
739 pConstraint
= pIdxInfo
->aConstraint
;
740 for(i
=0; i
<pIdxInfo
->nConstraint
; i
++, pConstraint
++){
741 if( pConstraint
->usable
==0 ) continue;
742 if( pConstraint
->op
!=SQLITE_INDEX_CONSTRAINT_EQ
) continue;
743 if( pConstraint
->iColumn
==4 ) idx4
= i
;
744 if( pConstraint
->iColumn
==5 ) idx5
= i
;
748 pIdxInfo
->idxNum
= 0;
749 pIdxInfo
->estimatedCost
= (double)(((sqlite3_int64
)1) << 50);
751 pIdxInfo
->aConstraintUsage
[idx4
].omit
= 1;
752 pIdxInfo
->aConstraintUsage
[idx4
].argvIndex
= 1;
754 pIdxInfo
->aConstraintUsage
[idx5
].omit
= 1;
755 pIdxInfo
->aConstraintUsage
[idx5
].argvIndex
= 2;
756 pIdxInfo
->idxNum
= 2;
757 pIdxInfo
->estimatedCost
= 10.0;
759 pIdxInfo
->idxNum
= 1;
760 pIdxInfo
->estimatedCost
= 100.0;
768 ** Register the "fsdir" virtual table.
770 static int fsdirRegister(sqlite3
*db
){
771 static sqlite3_module fsdirModule
= {
774 fsdirConnect
, /* xConnect */
775 fsdirBestIndex
, /* xBestIndex */
776 fsdirDisconnect
, /* xDisconnect */
778 fsdirOpen
, /* xOpen - open a cursor */
779 fsdirClose
, /* xClose - close a cursor */
780 fsdirFilter
, /* xFilter - configure scan constraints */
781 fsdirNext
, /* xNext - advance a cursor */
782 fsdirEof
, /* xEof - check for end of scan */
783 fsdirColumn
, /* xColumn - read data */
784 fsdirRowid
, /* xRowid - read data */
797 int rc
= sqlite3_create_module(db
, "fsdir", &fsdirModule
, 0);
800 #else /* SQLITE_OMIT_VIRTUALTABLE */
801 # define fsdirRegister(x) SQLITE_OK
805 __declspec(dllexport
)
807 int sqlite3_fileio_init(
810 const sqlite3_api_routines
*pApi
813 SQLITE_EXTENSION_INIT2(pApi
);
814 (void)pzErrMsg
; /* Unused parameter */
815 rc
= sqlite3_create_function(db
, "readfile", 1, SQLITE_UTF8
, 0,
818 rc
= sqlite3_create_function(db
, "writefile", -1, SQLITE_UTF8
, 0,
819 writefileFunc
, 0, 0);
822 rc
= sqlite3_create_function(db
, "lsmode", 1, SQLITE_UTF8
, 0,
826 rc
= fsdirRegister(db
);