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
;
273 GetSystemTime(¤tTime
);
274 SystemTimeToFileTime(¤tTime
, &lastAccess
);
275 intervals
= Int32x32To64(mtime
, 10000000) + 116444736000000000;
276 lastWrite
.dwLowDateTime
= (DWORD
)intervals
;
277 lastWrite
.dwHighDateTime
= intervals
>> 32;
279 zFile
, FILE_WRITE_ATTRIBUTES
, 0, NULL
, OPEN_EXISTING
,
280 FILE_FLAG_BACKUP_SEMANTICS
, NULL
282 if( hFile
!=INVALID_HANDLE_VALUE
){
283 BOOL bResult
= SetFileTime(hFile
, NULL
, &lastAccess
, &lastWrite
);
289 #elif defined(AT_FDCWD) && 0 /* utimensat() is not univerally available */
291 struct timespec times
[2];
292 times
[0].tv_nsec
= times
[1].tv_nsec
= 0;
293 times
[0].tv_sec
= time(0);
294 times
[1].tv_sec
= mtime
;
295 if( utimensat(AT_FDCWD
, zFile
, times
, AT_SYMLINK_NOFOLLOW
) ){
300 struct timeval times
[2];
301 times
[0].tv_usec
= times
[1].tv_usec
= 0;
302 times
[0].tv_sec
= time(0);
303 times
[1].tv_sec
= mtime
;
304 if( utimes(zFile
, times
) ){
314 ** Implementation of the "writefile(W,X[,Y[,Z]]])" SQL function.
315 ** Refer to header comments at the top of this file for details.
317 static void writefileFunc(
318 sqlite3_context
*context
,
325 sqlite3_int64 mtime
= -1;
327 if( argc
<2 || argc
>4 ){
328 sqlite3_result_error(context
,
329 "wrong number of arguments to function writefile()", -1
334 zFile
= (const char*)sqlite3_value_text(argv
[0]);
335 if( zFile
==0 ) return;
337 mode
= (mode_t
)sqlite3_value_int(argv
[2]);
340 mtime
= sqlite3_value_int64(argv
[3]);
343 res
= writeFile(context
, zFile
, argv
[1], mode
, mtime
);
344 if( res
==1 && errno
==ENOENT
){
345 if( makeDirectory(zFile
, mode
)==SQLITE_OK
){
346 res
= writeFile(context
, zFile
, argv
[1], mode
, mtime
);
350 if( argc
>2 && res
!=0 ){
352 ctxErrorMsg(context
, "failed to create symlink: %s", zFile
);
353 }else if( S_ISDIR(mode
) ){
354 ctxErrorMsg(context
, "failed to create directory: %s", zFile
);
356 ctxErrorMsg(context
, "failed to write file: %s", zFile
);
362 ** SQL function: lsmode(MODE)
364 ** Given a numberic st_mode from stat(), convert it into a human-readable
365 ** text string in the style of "ls -l".
367 static void lsModeFunc(
368 sqlite3_context
*context
,
373 int iMode
= sqlite3_value_int(argv
[0]);
375 if( S_ISLNK(iMode
) ){
377 }else if( S_ISREG(iMode
) ){
379 }else if( S_ISDIR(iMode
) ){
385 int m
= (iMode
>> ((2-i
)*3));
386 char *a
= &z
[1 + i
*3];
387 a
[0] = (m
& 0x4) ? 'r' : '-';
388 a
[1] = (m
& 0x2) ? 'w' : '-';
389 a
[2] = (m
& 0x1) ? 'x' : '-';
392 sqlite3_result_text(context
, z
, -1, SQLITE_TRANSIENT
);
395 #ifndef SQLITE_OMIT_VIRTUALTABLE
398 ** Cursor type for recursively iterating through a directory structure.
400 typedef struct fsdir_cursor fsdir_cursor
;
401 typedef struct FsdirLevel FsdirLevel
;
404 DIR *pDir
; /* From opendir() */
405 char *zDir
; /* Name of directory (nul-terminated) */
408 struct fsdir_cursor
{
409 sqlite3_vtab_cursor base
; /* Base class - must be first */
411 int nLvl
; /* Number of entries in aLvl[] array */
412 int iLvl
; /* Index of current entry */
413 FsdirLevel
*aLvl
; /* Hierarchy of directories being traversed */
418 struct stat sStat
; /* Current lstat() results */
419 char *zPath
; /* Path to current entry */
420 sqlite3_int64 iRowid
; /* Current rowid */
423 typedef struct fsdir_tab fsdir_tab
;
425 sqlite3_vtab base
; /* Base class - must be first */
429 ** Construct a new fsdir virtual table object.
431 static int fsdirConnect(
434 int argc
, const char *const*argv
,
435 sqlite3_vtab
**ppVtab
,
441 rc
= sqlite3_declare_vtab(db
, "CREATE TABLE x" FSDIR_SCHEMA
);
443 pNew
= (fsdir_tab
*)sqlite3_malloc( sizeof(*pNew
) );
444 if( pNew
==0 ) return SQLITE_NOMEM
;
445 memset(pNew
, 0, sizeof(*pNew
));
447 *ppVtab
= (sqlite3_vtab
*)pNew
;
452 ** This method is the destructor for fsdir vtab objects.
454 static int fsdirDisconnect(sqlite3_vtab
*pVtab
){
460 ** Constructor for a new fsdir_cursor object.
462 static int fsdirOpen(sqlite3_vtab
*p
, sqlite3_vtab_cursor
**ppCursor
){
464 pCur
= sqlite3_malloc( sizeof(*pCur
) );
465 if( pCur
==0 ) return SQLITE_NOMEM
;
466 memset(pCur
, 0, sizeof(*pCur
));
468 *ppCursor
= &pCur
->base
;
473 ** Reset a cursor back to the state it was in when first returned
476 static void fsdirResetCursor(fsdir_cursor
*pCur
){
478 for(i
=0; i
<=pCur
->iLvl
; i
++){
479 FsdirLevel
*pLvl
= &pCur
->aLvl
[i
];
480 if( pLvl
->pDir
) closedir(pLvl
->pDir
);
481 sqlite3_free(pLvl
->zDir
);
483 sqlite3_free(pCur
->zPath
);
493 ** Destructor for an fsdir_cursor.
495 static int fsdirClose(sqlite3_vtab_cursor
*cur
){
496 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
498 fsdirResetCursor(pCur
);
499 sqlite3_free(pCur
->aLvl
);
505 ** Set the error message for the virtual table associated with cursor
506 ** pCur to the results of vprintf(zFmt, ...).
508 static void fsdirSetErrmsg(fsdir_cursor
*pCur
, const char *zFmt
, ...){
511 pCur
->base
.pVtab
->zErrMsg
= sqlite3_vmprintf(zFmt
, ap
);
517 ** Advance an fsdir_cursor to its next row of output.
519 static int fsdirNext(sqlite3_vtab_cursor
*cur
){
520 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
521 mode_t m
= pCur
->sStat
.st_mode
;
525 /* Descend into this directory */
526 int iNew
= pCur
->iLvl
+ 1;
528 if( iNew
>=pCur
->nLvl
){
530 int nByte
= nNew
*sizeof(FsdirLevel
);
531 FsdirLevel
*aNew
= (FsdirLevel
*)sqlite3_realloc(pCur
->aLvl
, nByte
);
532 if( aNew
==0 ) return SQLITE_NOMEM
;
533 memset(&aNew
[pCur
->nLvl
], 0, sizeof(FsdirLevel
)*(nNew
-pCur
->nLvl
));
538 pLvl
= &pCur
->aLvl
[iNew
];
540 pLvl
->zDir
= pCur
->zPath
;
542 pLvl
->pDir
= opendir(pLvl
->zDir
);
544 fsdirSetErrmsg(pCur
, "cannot read directory: %s", pCur
->zPath
);
549 while( pCur
->iLvl
>=0 ){
550 FsdirLevel
*pLvl
= &pCur
->aLvl
[pCur
->iLvl
];
551 struct dirent
*pEntry
= readdir(pLvl
->pDir
);
553 if( pEntry
->d_name
[0]=='.' ){
554 if( pEntry
->d_name
[1]=='.' && pEntry
->d_name
[2]=='\0' ) continue;
555 if( pEntry
->d_name
[1]=='\0' ) continue;
557 sqlite3_free(pCur
->zPath
);
558 pCur
->zPath
= sqlite3_mprintf("%s/%s", pLvl
->zDir
, pEntry
->d_name
);
559 if( pCur
->zPath
==0 ) return SQLITE_NOMEM
;
560 if( lstat(pCur
->zPath
, &pCur
->sStat
) ){
561 fsdirSetErrmsg(pCur
, "cannot stat file: %s", pCur
->zPath
);
566 closedir(pLvl
->pDir
);
567 sqlite3_free(pLvl
->zDir
);
574 sqlite3_free(pCur
->zPath
);
580 ** Return values of columns for the row at which the series_cursor
581 ** is currently pointing.
583 static int fsdirColumn(
584 sqlite3_vtab_cursor
*cur
, /* The cursor */
585 sqlite3_context
*ctx
, /* First argument to sqlite3_result_...() */
586 int i
/* Which column to return */
588 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
591 sqlite3_result_text(ctx
, &pCur
->zPath
[pCur
->nBase
], -1, SQLITE_TRANSIENT
);
596 sqlite3_result_int64(ctx
, pCur
->sStat
.st_mode
);
600 sqlite3_result_int64(ctx
, pCur
->sStat
.st_mtime
);
604 mode_t m
= pCur
->sStat
.st_mode
;
606 sqlite3_result_null(ctx
);
607 #if !defined(_WIN32) && !defined(WIN32)
608 }else if( S_ISLNK(m
) ){
610 char *aBuf
= aStatic
;
615 n
= readlink(pCur
->zPath
, aBuf
, nBuf
);
617 if( aBuf
!=aStatic
) sqlite3_free(aBuf
);
619 aBuf
= sqlite3_malloc(nBuf
);
621 sqlite3_result_error_nomem(ctx
);
626 sqlite3_result_text(ctx
, aBuf
, n
, SQLITE_TRANSIENT
);
627 if( aBuf
!=aStatic
) sqlite3_free(aBuf
);
630 readFileContents(ctx
, pCur
->zPath
);
638 ** Return the rowid for the current row. In this implementation, the
639 ** first row returned is assigned rowid value 1, and each subsequent
640 ** row a value 1 more than that of the previous.
642 static int fsdirRowid(sqlite3_vtab_cursor
*cur
, sqlite_int64
*pRowid
){
643 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
644 *pRowid
= pCur
->iRowid
;
649 ** Return TRUE if the cursor has been moved off of the last
652 static int fsdirEof(sqlite3_vtab_cursor
*cur
){
653 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
654 return (pCur
->zPath
==0);
660 static int fsdirFilter(
661 sqlite3_vtab_cursor
*cur
,
662 int idxNum
, const char *idxStr
,
663 int argc
, sqlite3_value
**argv
665 const char *zDir
= 0;
666 fsdir_cursor
*pCur
= (fsdir_cursor
*)cur
;
668 fsdirResetCursor(pCur
);
671 fsdirSetErrmsg(pCur
, "table function fsdir requires an argument");
675 assert( argc
==idxNum
&& (argc
==1 || argc
==2) );
676 zDir
= (const char*)sqlite3_value_text(argv
[0]);
678 fsdirSetErrmsg(pCur
, "table function fsdir requires a non-NULL argument");
682 pCur
->zBase
= (const char*)sqlite3_value_text(argv
[1]);
685 pCur
->nBase
= (int)strlen(pCur
->zBase
)+1;
686 pCur
->zPath
= sqlite3_mprintf("%s/%s", pCur
->zBase
, zDir
);
688 pCur
->zPath
= sqlite3_mprintf("%s", zDir
);
691 if( pCur
->zPath
==0 ){
694 if( lstat(pCur
->zPath
, &pCur
->sStat
) ){
695 fsdirSetErrmsg(pCur
, "cannot stat file: %s", pCur
->zPath
);
703 ** SQLite will invoke this method one or more times while planning a query
704 ** that uses the generate_series virtual table. This routine needs to create
705 ** a query plan for each invocation and compute an estimated cost for that
708 ** In this implementation idxNum is used to represent the
709 ** query plan. idxStr is unused.
711 ** The query plan is represented by bits in idxNum:
713 ** (1) start = $value -- constraint exists
714 ** (2) stop = $value -- constraint exists
715 ** (4) step = $value -- constraint exists
716 ** (8) output in descending order
718 static int fsdirBestIndex(
720 sqlite3_index_info
*pIdxInfo
722 int i
; /* Loop over constraints */
726 const struct sqlite3_index_constraint
*pConstraint
;
727 pConstraint
= pIdxInfo
->aConstraint
;
728 for(i
=0; i
<pIdxInfo
->nConstraint
; i
++, pConstraint
++){
729 if( pConstraint
->usable
==0 ) continue;
730 if( pConstraint
->op
!=SQLITE_INDEX_CONSTRAINT_EQ
) continue;
731 if( pConstraint
->iColumn
==4 ) idx4
= i
;
732 if( pConstraint
->iColumn
==5 ) idx5
= i
;
736 pIdxInfo
->idxNum
= 0;
737 pIdxInfo
->estimatedCost
= (double)(((sqlite3_int64
)1) << 50);
739 pIdxInfo
->aConstraintUsage
[idx4
].omit
= 1;
740 pIdxInfo
->aConstraintUsage
[idx4
].argvIndex
= 1;
742 pIdxInfo
->aConstraintUsage
[idx5
].omit
= 1;
743 pIdxInfo
->aConstraintUsage
[idx5
].argvIndex
= 2;
744 pIdxInfo
->idxNum
= 2;
745 pIdxInfo
->estimatedCost
= 10.0;
747 pIdxInfo
->idxNum
= 1;
748 pIdxInfo
->estimatedCost
= 100.0;
756 ** Register the "fsdir" virtual table.
758 static int fsdirRegister(sqlite3
*db
){
759 static sqlite3_module fsdirModule
= {
762 fsdirConnect
, /* xConnect */
763 fsdirBestIndex
, /* xBestIndex */
764 fsdirDisconnect
, /* xDisconnect */
766 fsdirOpen
, /* xOpen - open a cursor */
767 fsdirClose
, /* xClose - close a cursor */
768 fsdirFilter
, /* xFilter - configure scan constraints */
769 fsdirNext
, /* xNext - advance a cursor */
770 fsdirEof
, /* xEof - check for end of scan */
771 fsdirColumn
, /* xColumn - read data */
772 fsdirRowid
, /* xRowid - read data */
782 int rc
= sqlite3_create_module(db
, "fsdir", &fsdirModule
, 0);
785 #else /* SQLITE_OMIT_VIRTUALTABLE */
786 # define fsdirRegister(x) SQLITE_OK
790 __declspec(dllexport
)
792 int sqlite3_fileio_init(
795 const sqlite3_api_routines
*pApi
798 SQLITE_EXTENSION_INIT2(pApi
);
799 (void)pzErrMsg
; /* Unused parameter */
800 rc
= sqlite3_create_function(db
, "readfile", 1, SQLITE_UTF8
, 0,
803 rc
= sqlite3_create_function(db
, "writefile", -1, SQLITE_UTF8
, 0,
804 writefileFunc
, 0, 0);
807 rc
= sqlite3_create_function(db
, "lsmode", 1, SQLITE_UTF8
, 0,
811 rc
= fsdirRegister(db
);