**I REREAD Keiths reply, then Alex's second question.... I goofed thinking
that Keith was saying the attributes were what was causing the slow downs,
but I missed the point of Alex's question. I had written the following,
re-read the posts above, went OH CRAP, and decided to post anyways with the
hopeful understanding that what I write here helps someone else, and
doesn't sound like I'm "calling out" Keith. ;) **
@Alex - Keith has it. SQLite does zero for file management beyond file
creation, so you will have to have code something that will manage the
attributes and permissions, or as he suggests, changing the directories
permissions. I'd strongly recommend, however, that you move the database
outside the view of IIS so no one on the internet can directly access said
database. If you were to create the directories outside of IIS's view, you
could open up the directory so the IIS user/group has full access using the
same method as you've already done. You will, of course, also have to
change the path in your OPEN statement.
Now, for my initial reply....
I've never looked at the code base for SQLite, but I "smell" that this is
something to do with SQLite timing out on initial read on a R/O file. I
sense that either the CLI is doing some additional check, or, the SQLite
engine itself is doing a really quick, OS independent check on the status
of the files writing availability. I might be able to go deeper and check
exactly where in my SQLite3 wrappers is causing the delay, but, suffice it
to say... meh.... {smirk}
The delays I've experienced are through the CLI, my applications attempting
to read a +R file, and via SQLite Manager. The advice you gave to grant
permissions based on the directory *does hold true without question*, as
SQLite would probably freak out if it couldn't write the WAL file, but, for
any "file->open" on a R/O SQLite file, even when it is just the file
ATTRIBUTE, not the NTFS permissions, the time it takes to open is at about
2 seconds. My VCS changes the file attributes on check-in/outs, and I
constantly run into this (Most of my databases aren't all that huge, and if
they are, I typically do one CHECK-IN, change the attribute on the file,
then go on my way). The only change I make is changing the file attribute,
and time the initial access to the database is fast. I just tested by
creating a database via the CLI, changed the file attribute, and re-opened
the database. I note a two second delay. I quit the CLI, change the
permissions back to not read-only, and retry and get back into the database
immediately. Just for "Ships and Googles", I changed the attribute back,
and again, noted the two seconds. My commands are at the bottom of my
reply here. My system is no where near a slouch. Dual SSDs in RAID-0,
12gig memory, and first gen I7 running at 3.3ghz. Right at the moment,
Firefox is using most of the memory on the system. ;)
I don't believe for a second that because the file attribute (Again, NOT
NTFS permission) for a file is the limiting factor for a fast load up due
to the OS.
Note: For the people that don't know, "File Attribute" and "File
Permissions" are two very different things. "File Attribute" goes back to
the floppy-disk OS type of systems, where you tell the OS a file is read
only, hidden, system or archive. File Permissions is deeper than that by
telling the OS a specific set of rules have to be set for a particular
file. Going back to the DOS days FAT12->FAT32 always used "File
Attributes" to lock files down, if you will, and there was no concept of
ownership. *Nix file systems and MSofts NTFS (The only other two file
systems I'm very familiar with) have a further lock down mechanism to allow
only certain people or groups have permission to do certain things to the
file. As a matter of fact, *Nix doesn't have the concept of "read only",
"hidden", "archive", or "system" attributes. The files are locked down
based on file ownership and permissions (Owner, Group, Everyone).
So, "File Attribute" = Status for everyone; "File Permissions" =
permissions set per user and/or group.
My timing tests:
C:\Users\stephen>sqlite3 test.mydb
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table Test (TestID integer primary key, TestSt char);
sqlite> .q
C:\Users\stephen>attrib +r test.mydb
C:\Users\stephen>sqlite3 test.mydb
{{Noticeable delay here}}
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> .q
C:\Users\stephen>attrib -r test.mydb
C:\Users\stephen>sqlite3 test.mydb
{{No delay}}
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .q
C:\Users\stephen>attrib +r test.mydb
C:\Users\stephen>sqlite3 test.mydb
{{Noticeable delay here}}
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .q
On Thu, Mar 7, 2013 at 1:35 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> This is windows behaviour not SQLite. On file creation Windows assigns
> the permissions of the containing directory.
>
> So modify the permissions of the directory to grant Users modify
> permissions to contained files and that will be done.
>
> Also remember that permissions in windows are an attribute of the file and
> do not change when you move the file. To get permissions inherited from
> the new parent you need to copy rather than move.
>
> If you are all Hooey Gooied and dont know the difference between move and
> copy, poke the mouse in the other eye while dragging :)
>
>
>
> ---
> Sent from Samsung Mobile
>
>
> -------- Original message --------
> From: Alexander Barkov <b...@mnogosearch.org>
> Date:
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] sqlite3_open() takes 1.5 seconds
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
