[sqlite] sqlite3_open() exclusive?

[John Richard Moser]

Is sqlite3_open() safe?

By safe, I mean does it create the database file with O_EXCL if it
doesn't exist, with permissions either matching umask() or with 0600
permissions? I don't want to encounter random races here.

- --
All content of all messages exchanged herein are left in the
Public Domain, unless otherwise explicitly stated.

[D. Richard Hipp]

Post by John Richard Moser
Is sqlite3_open() safe?
By safe, I mean does it create the database file with O_EXCL if it
doesn't exist, with permissions either matching umask() or with 0600
permissions? I don't want to encounter random races here.

SQLite databases are designed to be shared by two or more
processes, so no it does not use O_EXCL. New files are
created using 0644. If you want a different permission
setting, do something like

close(open(zFilename, O_CREAT, 0600));

prior to opening.

Temporary files and rollback journals are opened using
both O_EXCL and O_NOFOLLOW and permissions 0600. And
temporary file names contain a lot of randomness. All
these measures are to combat unforeseen vulnerabilities
due to races, though to be honest, it isn't clear what
kind of vulnerabilities might be created if these
measures were not use.

[John Richard Moser]

I thought sqlite databases weren't supposed to be opened with two sqlite
processes at once. There are unimplemented locking commands due to this
right?

I'm bouncing back and forth in my head trying to decide if I should use
mysql or sqlite to write a package manager. I'd like to use SQLite
because it's a single library reliant on pthreads and libc, and thus
lightweight and potentially ok for embedded systems; worst case, I write
a RDBMS shell around SQLite especially for the package manager.

D. Richard Hipp wrote:
| SQLite databases are designed to be shared by two or more
| processes, so no it does not use O_EXCL. New files are
| created using 0644. If you want a different permission
| setting, do something like
|
| close(open(zFilename, O_CREAT, 0600));
|
| prior to opening.
|

or just mkstemp()

| Temporary files and rollback journals are opened using
| both O_EXCL and O_NOFOLLOW and permissions 0600. And

should use mkstemp() (files) and mkdtemp() (directories)

| temporary file names contain a lot of randomness. All
| these measures are to combat unforeseen vulnerabilities

Which is why mkstemp() and mkdtemp() were invented.
| due to races, though to be honest, it isn't clear what
| kind of vulnerabilities might be created if these
| measures were not use.

http://www.ubuntulinux.org/wiki/wiki/USNAnalysis

Second most common vulnerability: Temp file races.
|

- --
All content of all messages exchanged herein are left in the
Public Domain, unless otherwise explicitly stated.

[D. Richard Hipp]

Post by John Richard Moser
I thought sqlite databases weren't supposed to be opened with two sqlite
processes at once. There are unimplemented locking commands due to this
right?

Where did you hear that?

Locking works great (and always has) on both unix and windows. If
you are running on something else, then you must be using somebody
else's port and I cannot speak for them. But as long as you stick
with mainstream workstations you should have no problems.

If the database file sits on an NFS filesystem or a windows shared
filesystem, then you might have problems due to bugs in the locking
logic of both those platforms. If you have a NFS that really works or
the very latest version of windows (which I've also heard really works)
then things should go ok. If the filesystems locking primitives do
not work correctly, however, you might end up corrupting a database.
You're safest bet is to not use SQLite on a network filesystem. If
you ignore this advice and use a network filesystem anyhow, you
might be disappointed with the performance due to high latency
that network filesystems introduce. Database engines (not just SQLite
but *all* database engines) really want to talk to a local disk drive.

[John Richard Moser]

D. Richard Hipp wrote:
| John Richard Moser wrote:
|
|>
|> I thought sqlite databases weren't supposed to be opened with two sqlite
|> processes at once. There are unimplemented locking commands due to this
|> right?
|>
|
| Where did you hear that?
|

Somewhere I can't find again. I thought there was a page talking about
compatibility. . . said the thing was compatible with ANSI SQL except
for a few things. . .

Didn't sqlite.org look a whole lot different several months ago? o.o;

Oh found it. http://sqlite.org/omitted.html I must misremember.

However wtf

"ALTER TABLE To change a table you have to delete it (saving its
contents to a temporary table) and recreate it from scratch."

This isn't good is it? I'm going to be indexing all files installed by
a package manager . . . I'll be altering large tables quite frequently.

Am I trying to solve a problem that I shouldn't be solving with SQLite?

MySQL seems to take ~6M of memory to run (I couldn't get a good reading,
so I watched my free ram drop when I started it). It's a 20M install
though isn't it? SQLite is ~1M installed, and I'm sure I'd use less
memory in an SQLite based program than from MySQL.

| Locking works great (and always has) on both unix and windows. If
| you are running on something else, then you must be using somebody
| else's port and I cannot speak for them. But as long as you stick
| with mainstream workstations you should have no problems.
|
| If the database file sits on an NFS filesystem or a windows shared
| filesystem, then you might have problems

Given. Try running ReiserFS from an image file on vfat once; it eats
itself. I'd imagine running *SQL over network would be slow and highly
unstable if there was a disaster.

| due to bugs in the locking
| logic of both those platforms. If you have a NFS that really works or
| the very latest version of windows (which I've also heard really works)
| then things should go ok. If the filesystems locking primitives do
| not work correctly, however, you might end up corrupting a database.
| You're safest bet is to not use SQLite on a network filesystem. If
| you ignore this advice and use a network filesystem anyhow, you
| might be disappointed with the performance due to high latency
| that network filesystems introduce. Database engines (not just SQLite
| but *all* database engines) really want to talk to a local disk drive.
|

- --
All content of all messages exchanged herein are left in the
Public Domain, unless otherwise explicitly stated.

[Roger Binns]

This post might be inappropriate. Click to display it.

[Andrew Piskorski]

Post by John Richard Moser
I thought sqlite databases weren't supposed to be opened with two sqlite
processes at once. There are unimplemented locking commands due to this
right?
I'm bouncing back and forth in my head trying to decide if I should use
mysql or sqlite to write a package manager. I'd like to use SQLite

This seems like a rather strange design question to be "bouncing back
and forth" on. Do you want to use a client server or an embedded
database for your application? If you want client server, then you
get to choose from MySQL, PostgreSQL, Oracle, etc. etc. If you want
an embedded database, then you'd be looking at things like SQLite,
Metakit, etc.

The particular or peculiar attributes of each such piece of software
might even lead you to re-consider whether you want client server or
embedded, but simply jumping ahead to "MySQL vs. SQLite" seems decidly
innappropriate. Perhaps you have other unmentioned constraints, but I
personally can't think of ANY case where I would conclude, "Oh, I
can't use SQLite for that, so I have to use MySQL." - nor vice versa,
either.

Post by John Richard Moser
because it's a single library reliant on pthreads and libc, and thus
lightweight and potentially ok for embedded systems; worst case, I write
a RDBMS shell around SQLite especially for the package manager.

[John Richard Moser]

Andrew Piskorski wrote:
| On Fri, Dec 24, 2004 at 07:32:07PM -0500, John Richard Moser wrote:
|
|
|>I thought sqlite databases weren't supposed to be opened with two sqlite
|>processes at once. There are unimplemented locking commands due to this
|>right?
|>
|>I'm bouncing back and forth in my head trying to decide if I should use
|>mysql or sqlite to write a package manager. I'd like to use SQLite
|
|
| This seems like a rather strange design question to be "bouncing back
| and forth" on. Do you want to use a client server or an embedded
| database for your application?

That's the thing. I'm not sure if this is something I want; but "if I
want it later," I can't just gearshift to MySQL. On the other hand, I
want a lightweight environment. Part of flexing my muscles is squeezing
my head through the bars without breaking it. My design goals are great
for embedded systems because they will allow more to be installed than
can actually be stored (even accounting for compression).

I'm thinking of practical applications for my ideas, still haven't found
a job (so that I can work on this stuff without slacking) or time to sit
and play with it. But wouldn't it be interesting to have a system smart
enough to maintain itself?

Imagine you have an iPaq. 128M of ram, 64M of flash. In the back you
have a 1G storage card for music and documents. For our purposes, let's
say that we can discard the consideration that flash has a limited
number of writes, because i'm definitely going to destroy it by the end
of the day.

So set up Linux on it. It uses the 64M of flash to store the base
system and a handfull of applications. Uses about 17M.

Now you install things. Under my scheme (adapted from 0installer's at
0install.net), they'd be installed to the flash drive. They'd be
monitored. A database would be kept in /var about them. And of course,
space is used.

Now you've installed firefox and everything. You're out of space and
still installing. My PM will look for packages not marked "critical"
(system will break without) or "hold" (user wants them) and compresses
the least used ones. Then it starts scping the compressed files into a
server you set up and pointed it to, cryptographically signed with your
key of course.

You now have 135M of stuff installed on a 64M flash media. You go out
to the store, get some coffee, sit down. Pull the ipaq out, run
firefox. It's there. You browse the web, and then run abiword. Via
dazuko, the PM is informed, and finds that. . . ABIWORD WAS SHIPPED OFF!
~ OK, time to mess with things. XMMS gets shipped out and erased.
AbiWord and needed libs get shipped down. 15 seconds later AbiWord starts.

2 hours later you try to run AbiWord again. It runs immediately, since
it's already here. Of course XMMS will have to be reloaded from your
server at your house over the Internet, but eh.

I'd originally considered that I may want to do remote administration.
I despise the concept of giving a few "Next-generation" features and
leaving off other useful ones. That being said, I can probably write my
own client-server backing to the PM if it becomes a useful and wanted
concept.

| If you want client server, then you
| get to choose from MySQL, PostgreSQL, Oracle, etc. etc. If you want
| an embedded database, then you'd be looking at things like SQLite,
| Metakit, etc.
|
| The particular or peculiar attributes of each such piece of software
| might even lead you to re-consider whether you want client server or
| embedded, but simply jumping ahead to "MySQL vs. SQLite" seems decidly
| innappropriate. Perhaps you have other unmentioned constraints, but I
| personally can't think of ANY case where I would conclude, "Oh, I
| can't use SQLite for that, so I have to use MySQL." - nor vice versa,
| either.
|

Like I said, remote administration. Thinking more, I can't come up with
a single case where I couldn't do it another way. Modifying the DB
remotely may pose to be annoying anyway; htf is my PM going to say "oh
look he set things in the DB i better install them"?

I guess it'd be possible to do it remotely so that on access, they could
be "Reacquired" (downloaded and installed) rather than reloaded (copied
from localnet server or uncompressed from localhost cache). Still, eh.
~ Is it really easier to have a remote administration panel that does it
all automatically on thousands of machines than it is to just write a
script to ssh to each machine and run commands to do it? . . . right. .
. I'll make sure there's a client/server for the package manager itself,
but not to the DB.


Oh well, I can't do it anyway. I don't have a cool name. I need
something original, strange, alien sounding to call it. All kinds of
spectacular graphics come to mind, but no words with that kind of impact.

|
|>because it's a single library reliant on pthreads and libc, and thus
|>lightweight and potentially ok for embedded systems; worst case, I write
|>a RDBMS shell around SQLite especially for the package manager.
|
|
I had thought about being an ass and writing an RDBMS that 'could'
compete with MySQL, just supplying the basic necessary functionality,
around SQLite. Just to be funny, you know. Wouldn't be hard to do
authentication. Symetrically encrypt copies of the DB encryption key
with each user's password. Access to the private key is write (which
needs read); access to the public key is read. Direct access gets you a
DB full of garbage. Tables, yes, the names and column headings of which
are hideously twisted along with the data.

But nah. what's the point.
- --
All content of all messages exchanged herein are left in the
Public Domain, unless otherwise explicitly stated.

[aleks ponjavic]

What I want to do is drop and add columns, couldn't find something
appropriate with sqlite, atleast it doesn't seem as ALTER TABLE works, how
can I do it instead?
Maybe it isn't possible?

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

[Peter Bartholdsson]

What I want to do is drop and add columns, couldn't find something appropriate with sqlite, atleast it doesn't seem as ALTER TABLE works, how can I do it instead?
Maybe it isn't possible?

Or simply read the FAQ:
http://www.sqlite.org/faq.html#q13

Regards,
Peter Bartholdsson

[Tito Ciuro]

Post by aleks ponjavic
What I want to do is drop and add columns, couldn't find something
appropriate with sqlite, atleast it doesn't seem as ALTER TABLE works,
how can I do it instead?
Maybe it isn't possible?

Please check the archives. It's been discussed already:

http://www.mail-archive.com/sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org

-- Tito