sqlite-manager cannot open databases that are stored as alternate data streams (NTFS)

[GoogleCodeExporter]

Attempting to open a sqlite database that is stored as an alternate data
stream (using the "c:\filename:adsname.db" syntax") fails with a "filename
is not valid" error. 

Original issue reported on code.google.com by david.ba...@synergi.com.au on 23 Jul 2008 at 12:05

[GoogleCodeExporter]

Wow... Are you seriously storing entire databases in ADS?  I'm curious, for 
what purpose?

AFAIK, the ADS notation isn't commonly accepted for file input, even by the 
Windows
API.  And since it's NTFS-only, I doubt that it will be supported by Mozilla's
components...

Original comment by tinus.coppoolse on 7 Oct 2008 at 8:47

[GoogleCodeExporter]

Sorry for forgetting about this issue. I know very little about ADS. And, I do 
not
plan to support the requested feature. This is more due to my ignorance about 
ADS and
a little because ADS, afaik, is a rarely used stuff.

Unless there is a really important reason why ADS support is indispensable, I 
will
keep this issue as a wontfix.

Original comment by mrinal.k...@gmail.com on 7 Oct 2008 at 11:52

• Changed state: WontFix
• Added labels: Type-Enhancement, Priority-Low, OpSys-Windows
• Removed labels: Type-Defect, Priority-Medium

[GoogleCodeExporter]

tinus:

Yes, seriously. We have a system in which general user 'session' data is stored 
in a
sqlite database (normal file), and we then attach multiple auxillary sqlite 
databases
(via ADS) to that file. We use the auxillery databases to hold cached and 
temporary
data. We use SQLite as a generic storage mechanism, and we cache individual 
report
results in seperate multi-table databases that can then be queried (or 
sub-queried)
as desired without having to incur the entire cost of rerunning the base 
report. When
the user logs off we simply delete the main file, and all the cache files are 
deleted
as well, so we do not need a complicated temporary file management strategy. It 
also
keeps our session store nice and neat. There is no limit (afaik) to the number 
or
size of files stored as ADS, although sometimes explorer, and other tools, get
confused when reporting the size of the file (they tend to ignore ADS). As far 
as the
syntax being supported, read below.

Mrinal:
I won't spend a lot of time trying to convince you that this is a necessary 
feature -
I realize its a bit of a fringe request, however, as far as Windows is 
concerned the
colon ADS syntax ('filename:adsname') is fully supported by the windows api, 
and even
standard C functions under Windows support this syntax. For example, consider 
the
following program:

#include <stdlib.h>
#include <memory.h>

#define BLOCK_SIZE 1024

void dumpfile(const char *filename);

int main(int argc, char* argv[]) {

    FILE *fp = fopen("C:\\zz\\test.txt", "w");  
    fprintf(fp, "Here in the main file");
    fclose(fp);
    fp = fopen("c:\\zz\\test.txt:ad1.txt", "w");
    fprintf(fp, "Here in the ads!");
    fclose(fp);
    dumpfile("C:\\zz\\test.txt");   
    dumpfile("C:\\zz\\test.txt:ad1.txt");

    return 0;
}

void dumpfile(const char *filename) {
    printf("\nDumping %s\n", filename);
    FILE *fp = fopen(filename, "r");
    char buf[ BLOCK_SIZE + 1 ];
    while (!feof(fp)) {
        memset(buf, 0, BLOCK_SIZE + 1);
        size_t bytes = fread(buf, 1, BLOCK_SIZE, fp);
        printf("%s", buf);
    }

    fclose(fp);
}

The above program writes and reads from an ADS using fopen, fread and fclose, no
problems. Also, the Sqlite library itself has no problems with dealing with this
filename syntax (i.e we have not modified the SQLite code in order to use ADS). 

I'm guessing that the problem with ADS and SQLite manager is simply that the 
dialog
box that allows you to select a sqlite file attempts to validate the filename 
before
opening it (possibly by looking for invalid characters or patterns?), and may 
have an
outdated view of what constitutes a valid filename (i.e. no colons after an 
initial
drive letter). 

Original comment by david.ba...@synergi.com.au on 7 Oct 2008 at 11:12

[GoogleCodeExporter]

I went through http://www.heysoft.de/nt/ntfs-ads.htm and tried creating a file 
with
ADS (on Vista) according to it. But, I think, I failed. Could you help me 
create a
file with ADS or attach one here with an sqlite db as ads?

Original comment by mrinal.k...@gmail.com on 10 Oct 2008 at 11:48

[GoogleCodeExporter]

Hi Mrinal,

The following code snippet creates and queries a sqlite database in an ADS. It
assumes that the SQLite3 amalgamation source file and header are being compiled
alongside. I'm using VS2005, and can supply you with a solution if that makes 
things
easier.

#include <stdlib.h>
#include <memory.h>
#include <stdio.h>
#include <iostream>

#include "sqlite3.h"

#define DB_NAME "c:\\zz\\file1.txt:db.sqlite"

int exec(sqlite3 *db, const char *sql);

int main(int argc, char* argv[]) {

    sqlite3 *db = NULL;

    // erase the file , just it case it already exists.
    FILE *fp = fopen(DB_NAME, "w");
    fclose(fp);

    // Open the database
    int rc = sqlite3_open(DB_NAME, &db);
    if (rc) {
        fprintf(stderr, "Failed to open database: %s (%d)", DB_NAME, rc);
        exit(1);
    }

    // create and populate a dummy table
    exec(db, "create table t1 (col1, col2, col3)");
    for (int i = 0; i < 10; ++i) {
        char szInsert[1024];
        sprintf(szInsert, "insert into t1 values ('abc%d', 'def%d', 'hij%d');", i, i, i);
        exec(db, szInsert);
    }

    // Dump the table
    exec(db, "select * from t1");

    // clean up
    sqlite3_close(db);

    return 0;
}

int callback(void *memento, int colcount, char **values, char **colnames) {

    for (int i = 0; i < colcount; ++i) {
        printf("%s\t", values[i]);      
    }
    printf("\n");
    return 0;
}

int exec(sqlite3 *db, const char *sql) {
    char *szErrMsg = 0;
    int rc = sqlite3_exec(db, sql, callback, 0, &szErrMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error: %s (%d)", szErrMsg, rc);
        sqlite3_free(szErrMsg);
    }
    return rc;
}

A few other things worth mentioning:

I'm running this on Windows Server 2008, which is basically Vista SP1. We run 
our
production system on all versions of windows from Windows 2000 and above without
issue. The volume on which you create the database must be NTFS. If its fat32, 
or
some other filesystem, then it won't work. In fact, if you copy a file with an 
ADS
from NTFS to FAT32, and back again, the ADS gets stripped off.

The makers of FlexHex (a hex editor for Windows) supply a free collection of ADS
tools that I find really useful. See http://www.flexhex.com/download/ and look 
for
streamtools.zip. The "ls" tool that comes in this zip allows you to see all the
streams on a file. For example, on my system, after running the compiled output 
of
the program above, when I run their 'ls' command I get the following ouput:

C:\>ls c:\zz\file1.txt
c:\zz\file1.txt
  :                                       0
  :db.sqlite                              2048
Total size: 2048 bytes.

which indicates that the 'main' stream is 0 bytes, and the 'db.sqlite' stream
contains 2048 bytes.

I hope this helps. Let me know if I can be of any more assistance.

Cheers, David

Original comment by david.ba...@synergi.com.au on 12 Oct 2008 at 10:37

[GoogleCodeExporter]

Hi David,
Thanks for the help and guidance. This issue has helped me learn about ADS and 
use it. 

It is possible to open ADS in files using mozilla code. Also I have been able 
to open
sqlite in an ADS with this extension. But there are no functions to look into a 
file
and discover ADS automatically.

One way to provide support it is to ask the user to select a file and specify 
the ADS
name himself/herself. Another way is to have the user download ls.exe from
streamtools.zip mentioned by you and use it to display the list of ADS to the 
user to
select the appropriate one.
Of course, these would depend upon a preference which is available to Windows 
users only.

What do you think?

Original comment by mrinal.k...@gmail.com on 24 Oct 2008 at 10:13

• Changed state: Accepted

[GoogleCodeExporter]

Hi Mrinal,

Thanks for looking into this. I have no problem with having to specify the ads 
name
when choosing a file (i.e. select a file, then adding the ':adsname.db' 
manually). I
don't think its worth modifying the file chooser dialog to support ADS, 
especially as
its such a fringe feature. Just being able to open an ADS that I know the name 
of is
enough.

Obviously, in a perfect world, it would be neat to be able to browse the ADSs 
of each
file, but I think it would require a significant effort, and would only be 
relevant
to Windows/NTFS anyway.

Cheers, David.

Original comment by david.ba...@intermine.com on 24 Oct 2008 at 10:34

[GoogleCodeExporter]

Original comment by mrinal.k...@gmail.com on 24 Oct 2008 at 1:27

• Added labels: Milestone-Release_0.3.16

[GoogleCodeExporter]

Adde a menuitem called "Connect to ADS Database" under database menu. This 
option
prompts the user to select a file and then specify the name of the ADS stream 
in the
selected file.
Once the file has been opened, it can be opened the next time easily from the 
MRU list.

Original comment by mrinal.k...@gmail.com on 24 Oct 2008 at 1:30

• Changed state: Fixed

[GoogleCodeExporter]

new version with this issue fixed.

Original comment by mrinal.k...@gmail.com on 24 Oct 2008 at 1:35

Attachments:

• SQLiteManager_0.3.16b1.xpi

[GoogleCodeExporter]

Because not all users may want to clutter their menus with a menuitem they do 
not
use, I have provided a new preference "extensions.sqlitemanager.handleADS" 
which must
be set to 1 to enable the menuitem on Windows OS.
This preference can also be set through the Options dialog's "Main" pane.

Original comment by mrinal.k...@gmail.com on 31 Oct 2008 at 12:14