Re: [sqlite] Appropriate uses for SQLite
On Feb 2, 2007, at 8:59 AM, Clark Christensen wrote: FWIW, I'm not convinced Samba has locking working correctly. Using a very recent Samba version, I managed to corrupt a SQLite database last fall by (I think) doing simultaneous writes from the Linux host box, and my WinXP client box (via a SMB drive map). I'm guessing the XP writes started first. It seems unlikely it would have happened had the Linux host started first. This is a situation where you really need all file locking managed by the operating system, or you need to access the file only via a single mechanism (e.g. only via SMB, even on the Linux host box from which the SMB mount is exported). If you have clients accessing a file via different remote mechanisms, these mechanisms *may not* share an underlying locking infrastructure, which can easily lead to corruption. Only if they are *guaranteed to* share an underlying locking infrastructure is it safe to access a file that requires any sort of locking via multiple different remote mechanisms. This is a general issue with any shared-access remote filesystem and any operating system, not something specific to SQLite, NFS, SMB, Windows, or Linux. -- Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Unicode Help
On Dec 5, 2006, at 8:42 AM, Igor Tandetnik wrote: Da Martian <[EMAIL PROTECTED]> wrote: So if I look at a name with umlaughts in the database via sqlite3.exe I get: St├ñdt. Klinikum Neunkirchen gGmbH -- | an "a" with two dots on top "A with umlaut" is represented as two bytes in UTF-8. This is a huge simplification. At a bare minimum, 'ä' can be represented as either one or two Unicode code points -- one code point represented 'ä' or one representing 'a' and one representing the '¨' combining mark. How *that* is represented in the UTF-8 encoding of Unicode is another issue, that depends on the exact values of the code points involved. The particular example of 'ä' be represented as two bytes in UTF-8 in both cases (I don't know offhand) but that's not something that can be generalized. -- Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Q about new SQLite API
On Nov 7, 2006, at 5:17 AM, [EMAIL PROTECTED] wrote:
QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function. What should the official name be?
What about introducing an options mechanism in place of a second
prepare mechanism? Do you foresee any situations where a developer
may want to use both the existing and the enhanced prepare/step
mechanism on the same database handle?
Here's a rough cut at what I'd expect an options mechanism to look like.
/* in sqlite3.h */
struct sqlite3_options {
int option;
void *parameter;
};
#define SQLITE3_OPTION_ENHANCED_PREPARE 1 /* enhanced prepare/step
behavior */
/* in user code */
struct sqlite3_options options = {
SQLITE3_OPTION_ENHANCED_PREPARE,
(void *) 1 /* turn the option on */
};
err = sqlite3_set_options(&db, 1, &options);
/* After this point the new prepare and step behavior is in force
for the
database handle db. The option could be turned off if
necessary, but
it always applies to all operations on the database handle db. */
QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid. (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?
I think SQLITE_SCHEMA would not be unreasonable, especially since it
would represent a much more significant situation than SQLITE_SCHEMA
as returned by sqlite3_prepare used to.
-- Chris
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Wish to store a C structure in sqlite column
On Sep 21, 2006, at 7:18 AM, Narendran wrote:
as far as my knowledge SQLITE allows me to declare the column types
suppoted by the programming languare or say i am using blob . My
requirement
is i wish to store a structure in the SQLite column.
Instead of storing the structure in a single column, create a
separate table to represent the structure, and then use a column to
reference a row in that table via a foreign key.
Given the extremely lightweight nature of the structure that you
later posted:
typedef struct ethernetcard1
{
char port[10];
char ipaddress[20];
char mask[20];
int bandwidth;
}
as well as the fact that sooner or later you're likely to want to
query on it, you may as well just store it as real data rather than
as a BLOB:
CREATE TABLE 'ETHERNETCARD' (
ID INTEGER PRIMARY KEY,-- SQLite does this implicitly as ROWID
PORT VARCHAR(10),
IPADDRESS VARCHAR(20),
MASK VARCHAR(20),
BANDWIDTH INTEGER
);
Of course, you might also want to encode your IP address and netmask
into network-byte-order integers rather than store them as strings,
but I think the above gives you an idea of what I mean. And if
"port" refers to a physical port name (such as "en1") you might even
want to have a separate table for ports, and have the port column
just contain a foreign key referencing that table...
If you start to decompose your use of SQLite in this fashion, you'll
actually be using the database *as* a database, and you'll be much
better able to leverage it to do new and interesting things in the
future.
-- Chris
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
On Nov 2, 2005, at 7:36 AM, [EMAIL PROTECTED] wrote: Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? I don't think you're alone. I do think you're wrong. By declaring column a as INTEGER, and column b as REAL, I am *explicitly* asking for SQLite to treat the values I put in a as INTEGERs and in b as REALs. If I declare column a as NUMERIC, or without a type, I would expect your proposed behavior. And of course if I ask for a value as a particular type - whether through the SQLite API or using a cast expression - I expect it to be treated as that type. I like to think of this as a "have my cake and eat it too" approach: The typing can be as static or as dynamic as I need it to be for a particular application, and I have a full set of tools for making that determination. The database isn't preventing me from doing what I need in *either* direction. To give you an analogy, Objective-C is a dynamic object-oriented extension to C that's modeled on Smalltalk. All message dispatch is dynamic and happens at runtime; however, users can declare variables that point to objects using either an "id" type that means "any kind of object" or by explicitly specifying a class. If you do specify a class, the compiler will do what type checking it can and warn you if it notices something out of the ordinary (or give you an error for a type/class it's never encountered before). This lets you avoid extensive compile-time type checking when you don't want it, but also obtain extensive compile-time type checking when you do want it. This is the kind of behavior I think would be very valuable in SQLite. If I specify a particular affinity, SQLite should adhere to it, because I probably had a reason for specifying it. But if I *didn't* specify an affinity, I probably had a reason for *that too*. They are both valuable options, and I think it would be very valuable for SQLite to let me choose rather than force them one way (always require affinity, e.g. the SQL standard way) or the other (never enforce affinity, e.g. the proposed SQLite way). Does anybody have a real-world example where any of this will actually make a difference, or is this really just an academic argument? It could lead to cascading inaccuracy in situations where developers do expect, plan for, or rely upon traditional integer truncation behavior. One area where this may be the case is in graphics: Many developers are used to working in whole-pixel units with integer truncation on division, and performing more exact calculations as part of SELECT statements may actually give erroneous results. Particularly if they declared columns as having type INTEGER and expect those columns to have such affinity. An example of this might be in locating images that can be trivially scaled (e.g. by sampling every second pixel) to fit a particular display area. The desired-area calculation could occur in the SELECT, and the proposed SQLite behavior could weed out images that would actually fit (e.g. (x/2 < u) is false in SQLite but true in C). I think behaving differently from the SQL standard in this situation -- if an affinity is specified -- would be seen as a bug in SQLite. -- Chris
