Re: [sqlite] Stricter parsing rules

[NSRT Mail account.]

Hi Dan,

I had some free time and looked into your request. Bear in mind I don't really 
know what I'm doing, but I managed to whip up this: 
http://paste.nachsoftware.com/SQLite3/BrksDfe9d421e8ed00d34f0fc50a59956af21byK

Results:
> ./sqlite3
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT "cows";
Error: no such column: cows
sqlite> PRAGMA dblquoted_str_lit=1;
sqlite> SELECT "cows";
cows
sqlite> PRAGMA dblquoted_str_lit=0;
sqlite> SELECT "cows";
Error: no such column: cows


I believe this is what you wanted. I imagine the naming would be improved if 
this code became mainline, and the ifdefs would be handled better, but I guess 
this is pretty much what you wanted.

Note: This changes the default behavior of SQLite. A proper patch of course 
would keep the default behavior. Although I couldn't figure out the pragma 
system enough to see how to specify what default pragma settings are.




 From: Dan Freundel 
To: General Discussion of SQLite Database  
Sent: Thursday, November 15, 2012 7:35 PM
Subject: Re: [sqlite] Stricter parsing rules
 
I agree as well. I tested out the original poster's patch and it works great. 
Any way this can be made into a "PRAGMA strict_mode" so that it would be 
usage-specific rather than compile-specific?
I asked about this on stackoverfolw as 
well:http://stackoverflow.com/questions/13383763/is-there-a-way-disable-lax-quoting-rules-in-sqlite/

--- On Thu, 11/15/12, John Gabriele  wrote:

From: John Gabriele 
Subject: Re: [sqlite] Stricter parsing rules
To: "General Discussion of SQLite Database" 
Date: Thursday, November 15, 2012, 1:56 PM

On Wed, Nov 14, 2012 at 5:22 PM, Simon Slavin  wrote:
>
> On 14 Nov 2012, at 9:46pm, BareFeetWare  wrote:
>
>> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
>> errors. I'd really like to see the rules tightened to remove ambiguous input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in 
> SQLite4.

Me four. The OP's practice of always using double-quotes for
identifiers seems like a sound one, and it would be useful if sqlite
could tell me if the thing I *think* is an identifier is actually not.

---John
___
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Two processes accessing same DB lock problems

[Simon Slavin]

On 15 Nov 2012, at 11:35pm, TR Shaw  wrote:

> Process 2: 
> 
> $sql = "SELECT value FROM key_value WHERE key=='updated'";
> $result = $db_conn->query($sql);
> $row = $result->fetch();
> $result->closeCursor();
> if (empty($row)) $updated_time = 0;
> 
> never receives a value

I think you're mixing two models of PDO calling.  Can you rewrite your code to 
look a little closer to the example on



?  You should be calling ->closeCursor() on a statement, not on the result of a 
query.

> $sigs_created = time();
> $sql = "INSERT OR REPLACE INTO key_value (key, value) VALUES ('sigs_created', 
> $sigs_created)";
> $result = $db_conn->exec($sql);
> 
> aborts with: SQLSTATE[HY000]: General error: 5 database is locked code:HY000

If you call this without doing the initial stuff with the query and fetch, does 
it work ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_prepare_v2() causing Address-out-of-Bounds error

[Simon Slavin]

On 15 Nov 2012, at 11:16pm, Rajeev Sharma  wrote:

> Could someone please let me know whether this is a known issue or whether it 
> has already been fixed?

Which operating system.  If Windows, are you using 64-bit or 32-bit ?

Are you calling the C API directly or using a pre-made library ?  What language 
are you writing the code in which calls the library ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[NSRT Mail account.]

Hello Everyone,

I'm glad to see I'm not the only one who wants this.

I'm thinking perhaps I wasn't clear enough in my first e-mail that I created a 
patch for the time being.
You can download the patch, along with already modified source, static 
libraries and binaries for Linux i386, Linux AMD64, and Windows here:

http://nsrt.nachsoftware.org/sqlite3-3.7.14.1-without-dblquote-string.tar.bz2
467a12059d50850f394066195d70c659
c15bc452d9db38ac47a557d06f89ff0658220861


If you're compiling yourself, add -DSQLITE_OMIT_DBLQUOTED_STRINGS to your flags 
to remove double quoted string literals from being supported.
I really wanted to know what people think of the patch itself.


@Tom
>this tolerance by SQLite for misquoted identifiers allows a lot of 
errors.
The case I'm dealing with here is actually misquoted string literals, which 
affects misspelled identifiers. I assume that's what you meant. While I see 
some wanting the ability to drop [] and `` from identifier support for the sake 
of purity, I don't know of a case where keeping it actually breaks any valid 
SQL.

@Simon
>Perhaps this and a couple of similar things will be fixed in SQLite4.

While I hope that too, I would like a fix to become mainline in SQLite3. As is, 
you can play with my patch right now in the latest version of SQLite3!

@Dave
>The behaviour of quoting with [identifier] or `identifier` seems to do what
you want, but, as the docs say, these are not standard SQL.

Nice idea! Definitely one to consider without any kind of patch. However if 
you're trying to write cross platform SQL, that can be a bit painful. :(

@Dan
>I tested out the original poster's patch and it works great. 
Any way this can be made into a "PRAGMA strict_mode" so that it would be 
usage-specific rather than compile-specific?

Thanks for testing it. I appreciate the feedback and glad to hear it works well 
for you.
I don't know a lot about SQLite's source code. I just really wanted this fixed, 
and thank God after 15 minutes of grepping the source, I happened upon the code 
which seems to control double quoted string literals. I don't really know 
SQLite's source code, let alone the pragma system, but if I have time, I'll see 
what I can do.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Two processes accessing same DB lock problems

[TR Shaw]

This is probably really simple but I don't know how to fix. 

I have two processes that access the same sqlite3 db file using php. Process 1 
starts first and is loading data in spurts into the db. Process 2 cannot access 
the db oto get data even when Process 1 is not doing anything.  All file have 
the same unix permissions and are running under user.

Any help would be appreciated

-rwxr-xr-x1 user  staff   5149 Nov 15 18:08 Process 2
-rw-rw-rw-1 user  staff  142910464 Nov 15 18:15 db.sql3
-rwxr-xr-x1 user  staff   7976 Oct 31 19:24 Process 1.php

PHP

$db_conn = new PDO('sqlite:'. 'db.sql3');
$db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Process 1:

$sql = "INSERT INTO md5_sigs (date, md5, size, sig, filename, refnum) VALUES 
($date, '$md5', $size, '$sig', '$name', '')";
$result = $db_conn->exec($sql);
$update_time = time();
$sql = "INSERT OR REPLACE INTO key_value (key, value) VALUES ('updated', 
$update_time)";
$result = $db_conn->exec($sql);

Process 2: 

$sql = "SELECT value FROM key_value WHERE key=='updated'";
$result = $db_conn->query($sql);
$row = $result->fetch();
$result->closeCursor();
if (empty($row)) $updated_time = 0;

never receives a value

$sigs_created = time();
$sql = "INSERT OR REPLACE INTO key_value (key, value) VALUES ('sigs_created', 
$sigs_created)";
$result = $db_conn->exec($sql);

aborts with: SQLSTATE[HY000]: General error: 5 database is locked code:HY000

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_prepare_v2() causing Address-out-of-Bounds error

[Rajeev Sharma]

Hello,

We recently upgraded our SQLite library to 3.7.14.1 and we are seeing that 
sqlite3_prepare_v2 function is throwing a segmentation fault with pzTail being 
address-out-of-bounds. This happens sometimes but normally works fine. But it 
is not so rare that we can ignore it and we need to fix it.

That is:
SQLITE_API int sqlite3_prepare_v2(
  sqlite3 *db,  /* Database handle. */
  const char *zSql, /* UTF-8 encoded SQL statement. */
  int nBytes,   /* Length of zSql in bytes. */
  sqlite3_stmt **ppStmt,/* OUT: A pointer to the prepared statement */
  const char **pzTail   /* OUT: End of parsed string */
)

In the above function pzTail is being address-out-of-Bounds. From the 
documentation it looks like pzTail will be a pointer inside zSql array and 
there seems to be some problem with the pointers.

Could someone please let me know whether this is a known issue or whether it 
has already been fixed?

I don't have steps to reproduce as this is intermittent.

Thanks,
Rajeev

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting list of columns in a SELECT statement inside a Virtual Table

[Marcelo Mottalli]

Hi,I am in the process of creating a virtual table and I need to get the list 
of columns that will be retrieved from that VT in a given SELECT statement. I 
need to do this *before* the xColumn methods are invoked by SQLite, preferably 
on the call to xBestIndex.

I am able to get the plain names of the columns via the pVdbe->aColName array, 
but this returns the names of *all* the columns in a given query, and these 
column names may have aliases, so there's no way to link this column name to a 
column in the virtual table itself.

What would be the best way to accomplish this?

Thanks and best regards,
-- Marcelo Mottalli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory leak in transactions

[Rob Richardson]

Do you have your inserts wrapped in a single transaction?  It used to be that I 
wasn't worrying about transactions in my projects, but I noticed things were 
very slow.  I realized that it was creating and committing one transaction for 
each insert I was doing.  When I wrapped all inserts into a single transaction, 
speed jumped dramatically.

But, of course, this doesn't address your memory issue.  Unless the increased 
memory is somewhere in the transaction management logic.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of rui
Sent: Thursday, November 15, 2012 12:21 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] memory leak in transactions

Hi,

I am seeing explosive memory growth when i am using transactions using 
System.Data.SQLite.

I see this when the transactions which were before taking fraction of second, 
taking 15 sec for 50 row insert into a table.

I have tried to use sqlite3_db_release_memory but that only frees upto 2mb 
after every transaction.

After some time say half an hour - i do see memory usage drop but that's not 
acceptable.

All the object are properly disposed from trasnsaction to command etc.

The SQLiteConnection is kept open for the life time of the session, which could 
span hours.

I would really appreciate if somebody can help me in getting the reason for 
such excessive memory usage, i have seen working set(memory)go up from 70 mb to 
400 mb in 1 minute where three transactions only doing 50 insert in a table.

Regards,
Raj
___
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

Re: [sqlite] Stricter parsing rules

[Dan Freundel]

I agree as well. I tested out the original poster's patch and it works great. 
Any way this can be made into a "PRAGMA strict_mode" so that it would be 
usage-specific rather than compile-specific?
I asked about this on stackoverfolw as 
well:http://stackoverflow.com/questions/13383763/is-there-a-way-disable-lax-quoting-rules-in-sqlite/

--- On Thu, 11/15/12, John Gabriele  wrote:

From: John Gabriele 
Subject: Re: [sqlite] Stricter parsing rules
To: "General Discussion of SQLite Database" 
Date: Thursday, November 15, 2012, 1:56 PM

On Wed, Nov 14, 2012 at 5:22 PM, Simon Slavin  wrote:
>
> On 14 Nov 2012, at 9:46pm, BareFeetWare  wrote:
>
>> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
>> errors. I'd really like to see the rules tightened to remove ambiguous input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in 
> SQLite4.

Me four. The OP's practice of always using double-quotes for
identifiers seems like a sound one, and it would be useful if sqlite
could tell me if the thing I *think* is an identifier is actually not.

---John
___
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

[sqlite] memory leak in transactions

[rui]

Hi,

I am seeing explosive memory growth when i am using transactions using
System.Data.SQLite.

I see this when the transactions which were before taking fraction of
second, taking 15 sec for 50 row insert into a table.

I have tried to use sqlite3_db_release_memory but that only frees upto
2mb after every transaction.

After some time say half an hour - i do see memory usage drop but
that's not acceptable.

All the object are properly disposed from trasnsaction to command etc.

The SQLiteConnection is kept open for the life time of the session,
which could span hours.

I would really appreciate if somebody can help me in getting the
reason for such excessive memory usage, i have seen working
set(memory)go up from 70 mb to 400 mb in 1 minute where three
transactions only doing 50 insert in a table.

Regards,
Raj
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

[Ryan Johnson]

On 14/11/2012 8:17 PM, Vladislav Bolkhovitin wrote:

Nico Williams, on 11/13/2012 02:13 PM wrote:

declaring groups of internally-unordered writes where the groups are
ordered with respect to each other... is practically the same as
barriers.


Which barriers? Barriers meaning cache flush or barriers meaning 
commands order, or barriers meaning both?


There's no such thing as "barrier". It is fully artificial 
abstraction. After all, at the bottom of your stack, you will have to 
translate it either to cache flush, or commands order enforcement, or 
both.
Isn't that  why we *have* "the stack" in the first place? So apps 
*don't* have to worry about how the OS implements an artificial (= 
high-level and portable) abstraction on a given device?




Are you going to invent 3 types of barriers?

One will do, it just needs to be a good one.

Maybe I'm missing something here, so I'm going to back up a bit and 
recap what I understand.


The filesystem abstracts the concept of encoding patterns of bits in 
some physical media (data), and making it easy to find and retrieve 
those bits later (metadata, incl. file name). When users read(), they 
expect to see whatever they most recently sent to write(). They also 
expect that what they write will still be there later,  in spite of any 
failure that leaves the disk itself intact.


Operating systems cheat by not actually writing to disk -- for 
performance reasons -- and users are (mostly, usually) OK with that, 
because the performance gains are so attractive and things usually work 
out anyway. Disks cheat too, in the same way and for the same reason.


The cheating works great most of the time, but breaks down -- badly -- 
if we actually care about what is on disk after a crash (or if we use a 
network filesystem). Enough people do care that fsync() was added to the 
toolbox. It is defined to transfer "all modified in-core data of the 
file referred to by the file descriptor fd to the disk device" and 
"blocks until the device reports that the transfer has completed" 
(quoting from the fsync(2) man page). Translation: "Stop cheating. Make 
sure the stuff I already wrote actually got written. And tell the disk 
to stop cheating, too."


Problem is, this definition is asymmetric: it says what happens to 
writes issued before the fsync, but nothing about those issued after the 
fsync starts and before it returns [1]. The reader has to assume  
fsync() makes no promises whatsoever about these later writes: making 
fsync capture them exposes callers of fsync() to DoS attacks, and them 
from reaching disk until all outstanding fsync calls complete would add 
complexity the spec doesn't currently demand, leading to understandable 
reluctance by kernel devs to code it up. Unfortunately, we're left with 
the filesystem equivalent of what we in the database world call 
"eventual consistency" -- easy to implement, nice and fast, but very 
difficult to write reliable code against unless you're willing to pay 
the cost of being fully synchronous, all the time. Having tried that for 
a few years, many people are "returning" to better-specified concurrency 
models, trading some amount of performance for comfort that the app will 
at least work predictably when things go wrong in strange and 
unanticipated ways.


The request, then, is to tighten up fsync semantics in two conceptually 
straightforward ways [2]: First, guarantee that later writes to an fd do 
not hit disk until earlier calls to fsync() complete. Second, make the 
call asynchronous. That's all.


Note that both changes are necessary. The improved ordering semantic 
useless by itself, because it's still not safe to request a blocking 
fsync from one thread and and then let other threads continue issuing 
writes: there's a race between broadcasting that fsync has begun and 
issuing the actual syscall that begins it. An asynchronous fsync is also 
useless by itself, because it only benefits uncoordinated writes (which 
evidently don't care what data actually reaches disk anyway).


The easiest way to implement this fsync would involve three things:
1. Schedule writes for all dirty pages in the fs cache that belong to 
the affected file, wait for the device to report success, issue a cache 
flush to the device (or request ordering commands, if available) to make 
it tell the truth, and wait for the device to report success. AFAIK this 
already happens, but without taking advantage of any request ordering 
commands.
2. The requesting thread returns as soon as the kernel has identified 
all data that will be written back. This is new, but pretty similar to 
what AIO already does.
3. No write is allowed to enqueue any requests at the device that 
involve the same file, until all outstanding fsync complete [3]. This is 
new.


The performance hit for #1 can be reduced significantly if the storage 
hardware at hand happens to support some form of request ordering. The 
amount of reduction could vary greatly depending on 

Re: [sqlite] light weight write barriers

[杨苏立 Yang Su Li]

On Thu, Nov 15, 2012 at 10:29 AM, Simon Slavin  wrote:

>
> On 15 Nov 2012, at 4:14pm, 杨苏立 Yang Su Li  wrote:
>
> > 1. fsync actually does two things at the same time: ordering writes (in a
> > barrier-like manner), and forcing cached writes to disk. This makes it
> very
> > difficult to implement fsync efficiently. However, logically they are two
> > distinctive functionalities, and user might want one but not the other.
> > Particularly, users might want a barrier, but doesn't care about
> durability
> > (much). I have no idea why ordering and durability, which seem quite
> > different, ended up being bundled together in a single fsync call.
> >
> > 2. fsync semantic in POSIX is a bit vague (at least to me) in a
> concurrent
> > setting. What is the expected behavior when more than one thread write to
> > the same file descriptor, or different file descriptor associated with
> the
> > same file?
>
> And, as has been posted many times here and elsewhere, on many systems
> fsync does nothing at all.  It is literally implemented as a 'noop'.  So
> you cannot use it as a basis for barriers.
>

I think it is because it's so difficult to implement fsync efficiently,
some systems just stop trying.

>
> > In modern file system we do all kind of stuff to ensure ordering, and I
> > think I can see how leveraging ordered commands (when it is available
> from
> > hardware) could potentially boost performance.
>
> Similarly, on many hard disk subsystems (the circuit board and firmware
> provided with the hard disk), the 'wait until cache has been written'
> operation does nothing.  So even if you /could/ depend on fsync you still
> couldn't depend on the hardware.  Read the manufactuer's documentation:
> they don't hide it, they boast about it because it makes the hard drive far
> faster.  If you really want this feature to work you have to buy expensive
> server-quality hard drives and set the jumpers in the right positions.
>

When you cannot trust hardware, there are still somethings you can do to
ensure durability and consistency. There are some work from UW-Madsion,
talks about how do you do that without trusting the hardware, say, using
coerced cache eviction. Of course, this is expensive, thus we want to
decouple ordering and durability even more.

Suli

>
> Simon.
> ___
> 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

Re: [sqlite] light weight write barriers

[Simon Slavin]

On 15 Nov 2012, at 4:14pm, 杨苏立 Yang Su Li  wrote:

> 1. fsync actually does two things at the same time: ordering writes (in a
> barrier-like manner), and forcing cached writes to disk. This makes it very
> difficult to implement fsync efficiently. However, logically they are two
> distinctive functionalities, and user might want one but not the other.
> Particularly, users might want a barrier, but doesn't care about durability
> (much). I have no idea why ordering and durability, which seem quite
> different, ended up being bundled together in a single fsync call.
> 
> 2. fsync semantic in POSIX is a bit vague (at least to me) in a concurrent
> setting. What is the expected behavior when more than one thread write to
> the same file descriptor, or different file descriptor associated with the
> same file?

And, as has been posted many times here and elsewhere, on many systems fsync 
does nothing at all.  It is literally implemented as a 'noop'.  So you cannot 
use it as a basis for barriers.

> In modern file system we do all kind of stuff to ensure ordering, and I
> think I can see how leveraging ordered commands (when it is available from
> hardware) could potentially boost performance.

Similarly, on many hard disk subsystems (the circuit board and firmware 
provided with the hard disk), the 'wait until cache has been written' operation 
does nothing.  So even if you /could/ depend on fsync you still couldn't depend 
on the hardware.  Read the manufactuer's documentation: they don't hide it, 
they boast about it because it makes the hard drive far faster.  If you really 
want this feature to work you have to buy expensive server-quality hard drives 
and set the jumpers in the right positions.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting out with AbeBooks, Homebase 3 and SQLite

[Simon Slavin]

On 15 Nov 2012, at 4:13pm, Charlie Lewis  wrote:

> HomeBase appears to create an SQLite database, in this case
> homebase.sqlite.db. I'd like to be able to interrogate this database from a
> Search page, and display results for the user.
> 
> 
> 
> So, where do I start? I'd appreciate any guidance you can give.

First, you might like to contact the publishers of that software and ask.  They 
may already have tools, and API, or other help for doing this.

Second, there's a standard shell tool you can download from the SQLite site.  
Look in the sections called 'Precompiled Binaries' on this page for download



and here on how to use it



Third, the writers of that software may have chosen to encrypt their database 
in which case you won't be able to read it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

[杨苏立 Yang Su Li]

On Thu, Nov 15, 2012 at 6:07 AM, David Lang  wrote:

> On Wed, 14 Nov 2012, Vladislav Bolkhovitin wrote:
>
>  Nico Williams, on 11/13/2012 02:13 PM wrote:
>>
>>> declaring groups of internally-unordered writes where the groups are
>>> ordered with respect to each other... is practically the same as
>>> barriers.
>>>
>>
>> Which barriers? Barriers meaning cache flush or barriers meaning commands
>> order, or barriers meaning both?
>>
>> There's no such thing as "barrier". It is fully artificial abstraction.
>> After all, at the bottom of your stack, you will have to translate it
>> either to cache flush, or commands order enforcement, or both.
>>
>
> When people talk about barriers, they are talking about order enforcement.
>
>
>  Your mistake is that you are considering barriers as something real,
>> which can do something real for you, while it is just a artificial
>> abstraction apparently invented by people with limited knowledge how
>> storage works, hence having very foggy vision how barriers supposed to be
>> processed by it. A simple wrong answer.
>>
>> Generally, you can invent any abstraction convenient for you, but farther
>> your abstractions from reality of your hardware => less you will get from
>> it with bigger effort.
>>
>> There are no barriers in Linux and not going to be. Accept it. And start
>> instead thinking about offload capabilities your storage can offer to you.
>>
>
> the hardware capabilities are not directly accessable from userspace (and
> they probably shouldn't be)
>
> barriers keep getting mentioned because they are a easy concept to
> understand. "do this set of stuff before doing any of this other set of
> stuff, but I don't care when any of this gets done" and they fit well with
> the requirements of the users.
>

Well, I think there are two questions to be answered here: what primitive
should be offered to the user by the file system (currently we have fsync);
and what primitive should be offered by the lower level and used by the
file system (currently we have barrier, or flushing and FUA).

I do agree that we should keep what is accessible from user-space simple
and stupid. However if you look into fsync semantics a bit closer, I think
there are two things to be noted:

1. fsync actually does two things at the same time: ordering writes (in a
barrier-like manner), and forcing cached writes to disk. This makes it very
difficult to implement fsync efficiently. However, logically they are two
distinctive functionalities, and user might want one but not the other.
Particularly, users might want a barrier, but doesn't care about durability
(much). I have no idea why ordering and durability, which seem quite
different, ended up being bundled together in a single fsync call.

2. fsync semantic in POSIX is a bit vague (at least to me) in a concurrent
setting. What is the expected behavior when more than one thread write to
the same file descriptor, or different file descriptor associated with the
same file?

So I do think in the user space, we need some kind of barrier (or other)
primitive which is not tied to durability guarantees; and hopefully this
primitive could be implemented more efficiently than fsync. And of course,
this primitive should be simple and intuitive, abstracting the complexity
out.


On the other hand, we have the questions of what should file system use.
Traditionally block layer provides barrier primitive, and now I think they
are moving to flushing and FUA, or even ordered commands. (
http://lwn.net/Articles/400541/).

In terms of whether file system should be exposed with the hardware
capability, in this case, ordered commands. I personally think it should.
In modern file system we do all kind of stuff to ensure ordering, and I
think I can see how leveraging ordered commands (when it is available from
hardware) could potentially boost performance. And all the complexity of,
say, topological order, is dealt within the file system, and is not visible
to the user.

Of course, there are challenges in when you want to do ordered writes in
file system. As Ts'o mentioned, *when you have entagled metadata updates,
i.e., *you update file A, and file B, and file A and B might share
metadata, it could be difficult to get the ordering right without
sacrificing performance. But I personally think it is worth exploring.

Suli


>
> Users readily accept that if the system crashes, they will loose the most
> recent stuff that they did, but they get annoyed when things get corrupted
> to the point that they loose the entire file.
>
> this includes things like modifying one option and a crash resulting in
> the config file being blank. Yes, you can do the 'write to temp file, sync
> file, sync directory, rename file" dance, but the fact that to do so the
> user must sit and wait for the syncs to take place can be a problem. It
> would be far better to be able to say "write to temp file, and after it's
> on disk, rename the file" and not have the user 

[sqlite] FW: Starting out with AbeBooks, Homebase 3 and SQLite

[Charlie Lewis]

I'm re-posting this as I originally had a message saying I wasn't yet a
member of the forum. I've since been accepted. Apologies for any
duplication.

 

Charlie

 

 

From: Charlie Lewis [mailto:charlie.le...@honest-it.com] 
Sent: 15 November 2012 08:34
To: SQLite Users
Subject: Starting out with AbeBooks, Homebase 3 and SQLite

 

I'm just starting to build a website for a bookseller. He uses HomeBase 3.0
(http://www.abebooks.com/homebase/software-inventory-management-system-catal
og/homebase3.shtml) to create and maintain his catalogue, mainly because he
needs to upload some of his titles to AbeBooks (http://www.abebooks.co.uk),
and that facility is in HomeBase.

 

HomeBase appears to create an SQLite database, in this case
homebase.sqlite.db. I'd like to be able to interrogate this database from a
Search page, and display results for the user.

 

So, where do I start? I'd appreciate any guidance you can give.

 

Best regards,

 

Charlie Lewis

 

 

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

[Simon Slavin]

On 15 Nov 2012, at 5:24am, LMHmedchem  wrote:

> On 13 Nov 2012, at 5:41pm, Simon Slavin-3 wrote: 
>> By the way, can I ask what iH2, iH3, iH4 are ?  I think I've figured out
>> iH1.
> 
> Yes iH1 is an intergerized form of the molecular weight rounded to two [snip]

Hey, thanks for that.  I read and understand the whole thing.  You piqued my 
curiosity.  My Chemistry 'O' Level is now about 30 years in the past.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[John Gabriele]

On Wed, Nov 14, 2012 at 5:22 PM, Simon Slavin  wrote:
>
> On 14 Nov 2012, at 9:46pm, BareFeetWare  wrote:
>
>> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of 
>> errors. I'd really like to see the rules tightened to remove ambiguous input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in 
> SQLite4.

Me four. The OP's practice of always using double-quotes for
identifiers seems like a sound one, and it would be useful if sqlite
could tell me if the thing I *think* is an identifier is actually not.

---John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

[Igor Tandetnik]

LMHmedchem  wrote:
> Igor,
> It seem as if you are saying that string values like a name should be passed
> in SQL commands as variables, is that right?

Personally, I tend to do this for all values, whether strings or numbers or 
otherwise.

> At the moment, I am accessing
> SQLite through the ruby-sqlite3 interface

For the record, I'm completely unfamiliar with that.

> so the inserts look like,
> 
> @db.execute "INSERT INTO #{table_name.capitalize}
> VALUES(#{insert_into_fields(table_name, values_array)})"
> 
> (I haven't got to queries yet)
> 
> the data is contained in the values_array, but I guessing that will still
> create a problem if there are single quotes in one of the values?

Well, it's possible that insert_into_fields function does, or can be made to 
do, the necessary escaping.

> I have
> looked at the link you sent (and to the very funny cartoon, thanks), but it
> will take a bit to decipher. Since I am using the ruby interface and not the
> C interface, it may be that I need to look at the ruby-sqlite3 doc, but I am
> amusing/hoping that there is something similar.

A quick Google search reveals

http://sqlite-ruby.rubyforge.org/sqlite3/faq.html#538670816

> It looks like you set the
> value of a parameter to your string value and then pass the parameter
> instead of the literal string.

You embed a placeholder in your statement where a literal would be. Each 
placeholder corresponds to a parameter (multiple placeholders may correspond to 
the same parameter). Then you bind actual values to parameters, and finally you 
execute the statement.

> Is that more or less the idea? I guess it
> would be easy enough to double the quotes in the ruby code before inserts or
> queries, as Simon suggested, and then un-double them on the way out if data
> was being retrieved from the database to be written somewhere else.

You don't need this last part. The text you get from SELECT is the original, 
without escaping.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger logic with INSERT OR REPLACE

[kyan]

On Wed, Nov 14, 2012 at 6:01 PM, Simon Slavin  wrote:
>  It's also worth knowing about
>
> UPDATE OR REPLACE
>
> which is similarly not MERGE, but has more MERGE-like behaviour.  Though what 
> it does I had to figure out myself since the documentation on the 'UPDATE' 
> page doesn't describe it.

>From what I've seen UPDATE OR REPLACE will *delete* existing records
if a PK or unique constraint is violated:

insert into master values (1, '1');
insert into master values (2, '2');
insert into detail values (1, 1, '1');
insert into detail values (2, 2, '2');

update or replace master set id = 1, descr = 'a' where id = 2

select * from master;
1   'a'

select * from detail;
2   1   '2'

In this case detail record 1 - 1 - '1' seems to have been
cascade-deleted and 2 - 2 - '2' cascade-updated.

Still nothing to do with MERGE behaviour IMHO. There doesn't seem to
be a way to perform "UPSERT"s in SQLite. An UPSERT modifies an
existing record or inserts one if a matching one is not found. It
never deletes one.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

[LMHmedchem]

Keith,

Thank you for the thoughtful and informative response. It is very helpful to
have a better background of how the underlying technology works. I gather
that I probably don't need the AUTOINCREMENT keyword. Since I am inserting
the value of "id" from Structure in to other tables (as Structure_id) for
the purposes of keeping track of data from the same record spread across
multiple tables, it would seem as if using an explicitly defined id primary
key (instead of the implicit rowid) makes sense for the Structure table. I
don't think that the FOREIGN KEY reference to Structure(id) in the satellite
tables provides an enormous amount of protection, but there are some
situations where I can see it as being helpful and it looks like that could
not be done with rowid.

> or have caught a nasty case of the object-oriented disease...
It's funny that you should say that. I had a conversation with a friend the
other day who is a database programmer, and he advised having an explicit
primary key in each of my satellite tables that is independent of the
Structure_id column. I as planning on just using Structure_id as the primary
key, but he advised me to keep an different AUTOINCREMENT key because it may
be useful to access rows independent of the Structure_id value. This
programmer frequently uses an OO database system, so I thought your comment
was interesting. What is it about OO that would lead to always explicitly
defining the id?


Igor,
It seem as if you are saying that string values like a name should be passed
in SQL commands as variables, is that right? At the moment, I am accessing
SQLite through the ruby-sqlite3 interface, so the inserts look like,

@db.execute "INSERT INTO #{table_name.capitalize}
VALUES(#{insert_into_fields(table_name, values_array)})"

(I haven't got to queries yet)

the data is contained in the values_array, but I guessing that will still
create a problem if there are single quotes in one of the values? I have
looked at the link you sent (and to the very funny cartoon, thanks), but it
will take a bit to decipher. Since I am using the ruby interface and not the
C interface, it may be that I need to look at the ruby-sqlite3 doc, but I am
amusing/hoping that there is something similar. It looks like you set the
value of a parameter to your string value and then pass the parameter
instead of the literal string. Is that more or less the idea? I guess it
would be easy enough to double the quotes in the ruby code before inserts or
queries, as Simon suggested, and then un-double them on the way out if data
was being retrieved from the database to be written somewhere else. The
single quotes in the names have specific chemical meaning.


On 13 Nov 2012, at 5:41pm, Simon Slavin-3 wrote: 
> By the way, can I ask what iH2, iH3, iH4 are ?  I think I've figured out
> iH1.

Yes iH1 is an intergerized form of the molecular weight rounded to two
decimal places (float*100 with a round off correction on the resulting int).
Each of the other three is the sum of a class of molecular structure
descriptors.

The iH2 sums a group of descriptors based on an atom level quantification of
the valence electron density at each atom (similar to partial charge). These
atom level indices have been grouped in various ways, such as by functional
group, atom-type, and bond type. There are ~1000 of these different indices
that are summed and intergerized.  This sum varies a great deal with the
size and structure features of the molecule. There will, of course, be
instances where two different compounds have the same value for the iH2 sum,
as is true with the iH1 molecular weight key.

The iH3 sum is also a sum of structure indices, but these indices relate to
the connections of the molecular graph, such as the number and size of
rings, fused rings, branch points, paths of various lengths, and the
location of heteroatoms in these subgraphs. The iH3 indices are relatively
independent of the iH2 and serve to discriminate between compounds that may
have identical iH2 sums.

The iH4 key is a sum of indices that describe the overall shape and
connection complexity of the molecule (elongated/globular, highly
interconnected, etc.).

As far as I have been able to determine, this set of 4 ints (including the
mw int) is unique for any compound. Since names are problematic for
identifying a compound (there can be dozens of legal names and they have
characters that are difficult to deal with, especially where multiple
software applications are used in a tool chain), I am looking into how this
set of ints can be used to register a compound into a database instead of
some other identifier. These ints are produced by software that processed
chemical structure files (molecular structures stored in text files). I
receive such files from vendors, and not only can the compound have a
variety of names, it often can be drawn in a variety of forms, so the
process of determining if you already have a record for this compound or not
is 

Re: [sqlite] light weight write barriers

[Vladislav Bolkhovitin]

Nico Williams, on 11/13/2012 02:13 PM wrote:

declaring groups of internally-unordered writes where the groups are
ordered with respect to each other... is practically the same as
barriers.


Which barriers? Barriers meaning cache flush or barriers meaning commands order, 
or barriers meaning both?


There's no such thing as "barrier". It is fully artificial abstraction. After all, 
at the bottom of your stack, you will have to translate it either to cache flush, 
or commands order enforcement, or both.


Are you going to invent 3 types of barriers?


There's a lot to be said for simplicity... as long as the system is
not so simple as to not work at all.

My p.o.v. is that a filesystem write barrier is effectively the same
as fsync() with the ability to return sooner (before writes hit stable
storage) when the filesystem and hardware support on-disk layouts and
primitives which can be used to order writes preceding and succeeding
the barrier.


Your mistake is that you are considering barriers as something real, which can do 
something real for you, while it is just a artificial abstraction apparently 
invented by people with limited knowledge how storage works, hence having very 
foggy vision how barriers supposed to be processed by it. A simple wrong answer.


Generally, you can invent any abstraction convenient for you, but farther your 
abstractions from reality of your hardware => less you will get from it with 
bigger effort.


There are no barriers in Linux and not going to be. Accept it. And start instead 
thinking about offload capabilities your storage can offer to you.


Vlad

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

[Vladislav Bolkhovitin]

Alan Cox, on 11/13/2012 12:40 PM wrote:

Barriers are pretty much universal as you need them for power off !


I'm afraid, no storage (drives, if you like this term more) at the moment 
supports
barriers and, as far as I know the storage history, has never supported.


The ATA cache flush is a write barrier, and given you have no NV cache
visible to the controller it's the same thing.


The cache flush is cache flush. You can call it barrier, if you want to continue 
confusing yourself and others.



Instead, what storage does support in this area are:


Yes - the devil is in the detail once you go beyond simple capabilities.


None of those details brings anything not solvable. For instance, I already 
described in this thread a simple way how requested order of commands can be 
carried through the stack and implemented that algorithm in SCST.


Vlad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[Dave McKee]

The behaviour of quoting with [identifier] or `identifier` seems to do what
you want, but, as the docs say, these are not standard SQL.

http://www.sqlite.org/lang_keywords.html

It's not a great option, since it forces you to have nonstandard SQL, but
it's a potentially useful one to have on the table.

Dave.

sqlite> create table jam (valid int);
sqlite> insert into jam values (12);
sqlite> select [invalid] from [jam];
Error: no such column: invalid
sqlite> select `invalid` from jam;
Error: no such column: invalid
sqlite> select invalid from jam;
Error: no such column: invalid
sqlite> select "invalid" from jam;
invalid
sqlite> select 'invalid' from jam;
invalid




On Wed, Nov 14, 2012 at 10:22 PM, Simon Slavin  wrote:

>
> On 14 Nov 2012, at 9:46pm, BareFeetWare  wrote:
>
> > I agree. this tolerance by SQLite for misquoted identifiers allows a lot
> of errors. I'd really like to see the rules tightened to remove ambiguous
> input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in
> SQLite4.
>
> Simon.
> ___
> 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

Re: [sqlite] light weight write barriers

[David Lang]

On Wed, 14 Nov 2012, Vladislav Bolkhovitin wrote:


Nico Williams, on 11/13/2012 02:13 PM wrote:

declaring groups of internally-unordered writes where the groups are
ordered with respect to each other... is practically the same as
barriers.


Which barriers? Barriers meaning cache flush or barriers meaning commands 
order, or barriers meaning both?


There's no such thing as "barrier". It is fully artificial abstraction. After 
all, at the bottom of your stack, you will have to translate it either to 
cache flush, or commands order enforcement, or both.


When people talk about barriers, they are talking about order enforcement.

Your mistake is that you are considering barriers as something real, which 
can do something real for you, while it is just a artificial abstraction 
apparently invented by people with limited knowledge how storage works, hence 
having very foggy vision how barriers supposed to be processed by it. A 
simple wrong answer.


Generally, you can invent any abstraction convenient for you, but farther 
your abstractions from reality of your hardware => less you will get from it 
with bigger effort.


There are no barriers in Linux and not going to be. Accept it. And start 
instead thinking about offload capabilities your storage can offer to you.


the hardware capabilities are not directly accessable from userspace (and they 
probably shouldn't be)


barriers keep getting mentioned because they are a easy concept to understand. 
"do this set of stuff before doing any of this other set of stuff, but I don't 
care when any of this gets done" and they fit well with the requirements of the 
users.


Users readily accept that if the system crashes, they will loose the most recent 
stuff that they did, but they get annoyed when things get corrupted to the point 
that they loose the entire file.


this includes things like modifying one option and a crash resulting in the 
config file being blank. Yes, you can do the 'write to temp file, sync file, 
sync directory, rename file" dance, but the fact that to do so the user must sit 
and wait for the syncs to take place can be a problem. It would be far better to 
be able to say "write to temp file, and after it's on disk, rename the file" and 
not have the user wait. The user doesn't really care if the changes hit disk 
immediately, or several seconds (or even 10s of seconds) later, as long as there 
is not any possibility of the rename hitting disk before the file contents.


The fact that this could be implemented in multiple ways in the existing 
hardware does not mean that there need to be multiple ways exposed to userspace, 
it just means that the cost of doing the operation will vary depending on the 
hardware that you have. This also means that if new hardware introduces a new 
way of implementing this, that improvement can be passed on to the users without 
needing application changes.


David Lang
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users