Re: [sqlite] How to track a record if the aliased id is changed

[Max Vlasov]

On Thu, Jan 13, 2011 at 2:28 AM, Simon Slavin  wrote:

>
> On 12 Jan 2011, at 10:54pm, Max Vlasov wrote:
>
> > On Thu, Jan 13, 2011 at 1:07 AM, Simon Slavin 
> wrote:
> >
> >> [snip] If you're just
> >> letting your user change whatever data they want, why are you bothering
> to
> >> keep track of rowids ?
> >>
> >>
> > Thanks to the portability of sqlite, sometimes I just want to do things
> easy
> > at the fingers. So you have a grid, you walk on it, change values,
> sometimes
> > the program says "sorry, you can not do this", in other cases it just
> does
> > what it was asked for. And for for maintaining such simplicity one
> sometimes
> > have to implement a tricky algorithm like this :)
>
> I didn't make my question clear.  Why are you keeping track of rowids
> through changes.  Since a change to any one record can make changes to any
> number of records in any number of trees you have to redraw the screen after
> every change anyway.  So why are you bothering to keep track of the rowid of
> just one of the records which were changed ?  As you redraw the display you
> can work out the new rowids.
>
>
Maybe I didn't explain fully my situation. Sorry If I repeat some things
already clarified, but it's also for Nicolas, who answered here too.

It was about a single row change in an editable grid control (not some
random user query). So the user is at some row of this grid changing values
in the cells and making some 'apply" after changing. This apply ends with
final "Update table set value1=...,value2=... where rowid=..." and  the
"rowid=" here because it's the best way to identify a row from the table for
deleting and updating. After that apply I want to allow user to repeat the
same steps (moving cursor from cell to cell, changing and pressing apply).
Everything is ok if rowid is unaffected by the cells change (I just have
full set of rowids for the whole table), but if some of cells represents the
aliased rowid, this row becomes invalid after the change and I no longer can
identify it for any next change. If I simply reread all the data (I don't
want to since sometimes it takes time, but let's assume), I still won't be
able to place the cursor at the changed row since with rowid changes it will
no longer point to a valid row.

To Nikolas Williams: I'd be glad to forbid rowid changes, but they're from
virtually unknown aliased column.

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

Re: [sqlite] Membership operator 'IN' documentation

[Jay A. Kreibich]

On Wed, Jan 12, 2011 at 07:09:16PM -0500, sub sk79 scratched on the wall:
> Hi,
> 
> The 'IN' operator syntax diagram shows a possible table name operand
>IN  [Database_Name DOT ] Table_Name
> But its description seems to be missing from the paragraph about 'IN'
> operator down on the page.

  From "Using SQLite" ( http://oreilly.com/catalog/9780596521196/ ),
  page 355:

 The last way to define the test group is by providing a table
 name.  The table must consist of only a single column.  You cannot
 provide a table and column, it must be a single-column table.
 This final style is most frequently used with temporary tables.
 If you need to execute the same test multiple times, it can be
 more efficient to build a temporary table (for example, with
 CREATE TEMP TABLE...AS SELECT), and use it over and over, rather
 than using a subquery as part of the IN operator.

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Membership operator 'IN' documentation

[sub sk79]

Hi,

The 'IN' operator syntax diagram shows a possible table name operand
   IN  [Database_Name DOT ] Table_Name
But its description seems to be missing from the paragraph about 'IN'
operator down on the page.

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

Re: [sqlite] How to track a record if the aliased id is changed

[Simon Slavin]

On 12 Jan 2011, at 10:54pm, Max Vlasov wrote:

> On Thu, Jan 13, 2011 at 1:07 AM, Simon Slavin  wrote:
> 
>> [snip] If you're just
>> letting your user change whatever data they want, why are you bothering to
>> keep track of rowids ?
>> 
>> 
> Thanks to the portability of sqlite, sometimes I just want to do things easy
> at the fingers. So you have a grid, you walk on it, change values, sometimes
> the program says "sorry, you can not do this", in other cases it just does
> what it was asked for. And for for maintaining such simplicity one sometimes
> have to implement a tricky algorithm like this :)

I didn't make my question clear.  Why are you keeping track of rowids through 
changes.  Since a change to any one record can make changes to any number of 
records in any number of trees you have to redraw the screen after every change 
anyway.  So why are you bothering to keep track of the rowid of just one of the 
records which were changed ?  As you redraw the display you can work out the 
new rowids.

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

Re: [sqlite] How to track a record if the aliased id is changed

[Nicolas Williams]

On Wed, Jan 12, 2011 at 10:07:36PM +, Simon Slavin wrote:
> On 12 Jan 2011, at 9:57pm, Max Vlasov wrote:
> > Simon, your reply led me to the following sequence:
> > - I know the rowid of the record I'm changing. I remember all integers (and
> > all other data) I'm going to change in the Update query (it' comparatively
> > easy task)
> > - I check this rowid after the change. If it exists, the record did not
> > change the rowid and if it does not I form SELECT .. where rowid= or
> > rowid=.. containing all the integers I used and compare the rest of the data
> > only with this result set. If there's only single match, this is the answer,
> > but if not ... I should think about it :)
> > 
> > Does it sound reasonable?
> 
> It will deal with most cases, but it can still be fooled by creative
> use of TRIGGERs, or by bad coincidences in the numbers stored.  A
> question worth asking might be why you need to maintain these rowids.
> If you're just letting your user change whatever data they want, why
> are you bothering to keep track of rowids ?

That's my view as well.  If you're not in control over what the DB
itself is doing (through triggers, for example) or what the user is
doing (you're given them too much rope) then you can't reliably detect
rowid changes (assuming the DB allows them).

Assuming this is for a generic data browser type application, I'd say:
a) don't show rowids to users, b) on every update re-run the query that
produced the rows displayed to the user and refresh the display.  If you
can guarantee that the rowid won't change then you can skip (b) and
instead just do a query for the modified row to get its new column
values.

If you can at all help it, then disallow arbitrary rowid changes.

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

Re: [sqlite] How to track a record if the aliased id is changed

[Max Vlasov]

On Thu, Jan 13, 2011 at 1:07 AM, Simon Slavin  wrote:

>
> On 12 Jan 2011, at 9:57pm, Max Vlasov wrote:
>
> > Simon, your reply led me to the following sequence:
> > - I know the rowid of the record I'm changing. I remember all integers
> (and
> > all other data) I'm going to change in the Update query (it'
> comparatively
> > easy task)
> > - I check this rowid after the change. If it exists, the record did not
> > change the rowid and if it does not I form SELECT .. where rowid= or
> > rowid=.. containing all the integers I used and compare the rest of the
> data
> > only with this result set. If there's only single match, this is the
> answer,
> > but if not ... I should think about it :)
> >
> > Does it sound reasonable?
>
> It will deal with most cases, but it can still be fooled by creative use of
> TRIGGERs, or by bad coincidences in the numbers stored.  A question worth
> asking might be why you need to maintain these rowids.  If you're just
> letting your user change whatever data they want, why are you bothering to
> keep track of rowids ?
>
>
Thanks to the portability of sqlite, sometimes I just want to do things easy
at the fingers. So you have a grid, you walk on it, change values, sometimes
the program says "sorry, you can not do this", in other cases it just does
what it was asked for. And for for maintaining such simplicity one sometimes
have to implement a tricky algorithm like this :)

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

Re: [sqlite] PRAGMA Documentation typo

[Richard Hipp]

On Wed, Jan 12, 2011 at 5:10 PM, Marian Cascaval
wrote:

> Hi!
>
> There's a typo in thePRAGMA Statementsdocumentation page:
> http://www.sqlite.org/pragma.html#toc
>
> The original text:
> "... and are only available _which_ SQLite is compiled..."
>
> Instead of _which_ should be _when_.
>
> This is a trivial documentation typo and I've identified quite a few of
> them.
> Should I continue reporting this kind of typos or not?
>

Yes.  We appreciate bug reports.  http://www.sqlite.org/docsrc/ci/1927e7c245


>
> Thank you.
>
>
> Marian Cascaval
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Unexpected cascading delete

[Duquette, William H (318K)]

Richard,

I was afraid you were going to tell me that; it makes all
too much sense, once I thought about.

Thanks for the definitive word.

Will

On 1/12/11 2:08 PM, "Richard Hipp"  wrote:

On Wed, Jan 12, 2011 at 4:54 PM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> I've just discovered that a REPLACE can trigger a
> cascading delete.  Is this expected behavior?
>
> I have an undo scheme where I grab entire rows from the
> database before they are changed; then, on undo I
> simply put the rows back using "INSERT OR REPLACE".
> My assumption was that doing a REPLACE was
> equivalent to doing an UPDATE on the non-key
> values given the key values.  Apparently not.
>

Nope.  REPLACE is a shorthand for DELETE followed by INSERT.  Note that if
there are multiple uniqueness constraints on a table, a REPLACE might get
translated into multiple DELETEs (one for each uniqueness constraint)
followed by a single INSERT.  Hence a REPLACE can reduce the number of rows
in a table.



>
> Here's some SQL that illustrates the problem:
>
> PRAGMA foreign_keys=1;
>
> CREATE TABLE parent(
>parent_id TEXT PRIMARY KEY,
>value TEXT);
>
> CREATE TABLE child(
>child_id  INTEGER PRIMARY KEY,
>parent_id TEXT REFERENCES parent(parent_id)
>   ON DELETE CASCADE
>   DEFERRABLE INITIALLY DEFERRED,
>value TEXT);
>
> INSERT INTO parent(parent_id, value) VALUES('FRED',   1);
> INSERT INTO parent(parent_id, value) VALUES('GEORGE', 2);
>
> INSERT INTO child(parent_id, value) VALUES('FRED',   'FOO');
> INSERT INTO child(parent_id, value) VALUES('FRED',   'BAR');
> INSERT INTO child(parent_id, value) VALUES('GEORGE', 'FOO');
>
> -- Causes the records in child that reference FRED
> -- to be deleted.
> REPLACE INTO parent(parent_id, value) VALUES('FRED',3);
>
>
>
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

[sqlite] PRAGMA Documentation typo

[Marian Cascaval]

Hi!

There's a typo in thePRAGMA Statementsdocumentation page:
http://www.sqlite.org/pragma.html#toc

The original text:
"... and are only available _which_ SQLite is compiled..."

Instead of _which_ should be _when_.

This is a trivial documentation typo and I've identified quite a few of them.
Should I continue reporting this kind of typos or not?

Thank you.


Marian Cascaval



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

Re: [sqlite] Using stored Regular Expressions to match given String

[Simon Slavin]

On 12 Jan 2011, at 9:42pm, Bruno Augusto wrote:

> SELECT `Application`, `Class`, `Method`, `RequiredParams`, `OptionalParams`
> FROM `Routes` WHERE `RequestMethod` = "GET" AND "/" LIKE `URI`

Just a note that the characters in the line I quoted above are directional 
quotes.  The character you're meant to use for SQLite is an undirectional 
quote, in other words, the ASCII apostrophe character.

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

Re: [sqlite] Unexpected cascading delete

[Richard Hipp]

On Wed, Jan 12, 2011 at 4:54 PM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> I've just discovered that a REPLACE can trigger a
> cascading delete.  Is this expected behavior?
>
> I have an undo scheme where I grab entire rows from the
> database before they are changed; then, on undo I
> simply put the rows back using "INSERT OR REPLACE".
> My assumption was that doing a REPLACE was
> equivalent to doing an UPDATE on the non-key
> values given the key values.  Apparently not.
>

Nope.  REPLACE is a shorthand for DELETE followed by INSERT.  Note that if
there are multiple uniqueness constraints on a table, a REPLACE might get
translated into multiple DELETEs (one for each uniqueness constraint)
followed by a single INSERT.  Hence a REPLACE can reduce the number of rows
in a table.



>
> Here's some SQL that illustrates the problem:
>
> PRAGMA foreign_keys=1;
>
> CREATE TABLE parent(
>parent_id TEXT PRIMARY KEY,
>value TEXT);
>
> CREATE TABLE child(
>child_id  INTEGER PRIMARY KEY,
>parent_id TEXT REFERENCES parent(parent_id)
>   ON DELETE CASCADE
>   DEFERRABLE INITIALLY DEFERRED,
>value TEXT);
>
> INSERT INTO parent(parent_id, value) VALUES('FRED',   1);
> INSERT INTO parent(parent_id, value) VALUES('GEORGE', 2);
>
> INSERT INTO child(parent_id, value) VALUES('FRED',   'FOO');
> INSERT INTO child(parent_id, value) VALUES('FRED',   'BAR');
> INSERT INTO child(parent_id, value) VALUES('GEORGE', 'FOO');
>
> -- Causes the records in child that reference FRED
> -- to be deleted.
> REPLACE INTO parent(parent_id, value) VALUES('FRED',3);
>
>
>
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] How to track a record if the aliased id is changed

[Simon Slavin]

On 12 Jan 2011, at 9:57pm, Max Vlasov wrote:

> Simon, your reply led me to the following sequence:
> - I know the rowid of the record I'm changing. I remember all integers (and
> all other data) I'm going to change in the Update query (it' comparatively
> easy task)
> - I check this rowid after the change. If it exists, the record did not
> change the rowid and if it does not I form SELECT .. where rowid= or
> rowid=.. containing all the integers I used and compare the rest of the data
> only with this result set. If there's only single match, this is the answer,
> but if not ... I should think about it :)
> 
> Does it sound reasonable?

It will deal with most cases, but it can still be fooled by creative use of 
TRIGGERs, or by bad coincidences in the numbers stored.  A question worth 
asking might be why you need to maintain these rowids.  If you're just letting 
your user change whatever data they want, why are you bothering to keep track 
of rowids ?

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

Re: [sqlite] Unexpected cascading delete

[Gerry Snyder]

On 1/12/2011 2:54 PM, Duquette, William H (318K) wrote:
> I've just discovered that a REPLACE can trigger a
> cascading delete.  Is this expected behavior?
>
> I have an undo scheme where I grab entire rows from the
> database before they are changed; then, on undo I
> simply put the rows back using "INSERT OR REPLACE".
> My assumption was that doing a REPLACE was
> equivalent to doing an UPDATE on the non-key
> values given the key values.  Apparently not.

 From the "ON CONFLICT" section of the docs:

"When a UNIQUE constraint violation occurs, the REPLACE algorithm 
deletes pre-existing rows that are causing the constraint violation 
prior to inserting or updating the current row and the command continues 
executing normally."

This seems to make it expected behavior.


Gerry (JPL retiree)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to track a record if the aliased id is changed

[Max Vlasov]

On Thu, Jan 13, 2011 at 12:21 AM, Simon Slavin  wrote:

>
> On 12 Jan 2011, at 9:02pm, Max Vlasov wrote:
>
> > It's about the utility, when the data is presented with a grid and every
> > cell of opened db and * fields of table can be edited.. I'm aware that my
> > own actions can lead to constraint failure, but even for a legitimate
> change
> > I currently can not point to the same record, and even if I could (data
> in
> > this case is simply cached) I would not do this next time since this
> record
> > after this change have another .rowid and it's not possible to format new
> > "update where rowid=" query correctly.
>
> Ah, okay.  You have to have access to the value that was stored in the cell
> before it was changed.
>
> You can pre-scan the columns of a table to figure out which ones are likely
> to be useful because they're UNIQUE.  If you don't find one, then use
> 'rowid'.  But I don't think there's a simple perfect solution for this. You
> are in the position of needing to reread the record you just changed to find
> out if change they made to one column TRIGGERed some other change to that
> record.
>
>

Simon, your reply led me to the following sequence:
- I know the rowid of the record I'm changing. I remember all integers (and
all other data) I'm going to change in the Update query (it' comparatively
easy task)
- I check this rowid after the change. If it exists, the record did not
change the rowid and if it does not I form SELECT .. where rowid= or
rowid=.. containing all the integers I used and compare the rest of the data
only with this result set. If there's only single match, this is the answer,
but if not ... I should think about it :)

Does it sound reasonable?

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

[sqlite] Unexpected cascading delete

[Duquette, William H (318K)]

I've just discovered that a REPLACE can trigger a
cascading delete.  Is this expected behavior?

I have an undo scheme where I grab entire rows from the
database before they are changed; then, on undo I
simply put the rows back using "INSERT OR REPLACE".
My assumption was that doing a REPLACE was
equivalent to doing an UPDATE on the non-key
values given the key values.  Apparently not.

Here's some SQL that illustrates the problem:

PRAGMA foreign_keys=1;

CREATE TABLE parent(
parent_id TEXT PRIMARY KEY,
value TEXT);

CREATE TABLE child(
child_id  INTEGER PRIMARY KEY,
parent_id TEXT REFERENCES parent(parent_id)
   ON DELETE CASCADE
   DEFERRABLE INITIALLY DEFERRED,
value TEXT);

INSERT INTO parent(parent_id, value) VALUES('FRED',   1);
INSERT INTO parent(parent_id, value) VALUES('GEORGE', 2);

INSERT INTO child(parent_id, value) VALUES('FRED',   'FOO');
INSERT INTO child(parent_id, value) VALUES('FRED',   'BAR');
INSERT INTO child(parent_id, value) VALUES('GEORGE', 'FOO');

-- Causes the records in child that reference FRED
-- to be deleted.
REPLACE INTO parent(parent_id, value) VALUES('FRED',3);



Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] Using stored Regular Expressions to match given String

[Igor Tandetnik]

On 1/12/2011 4:42 PM, Bruno Augusto wrote:
> So, it would be:
>
> SELECT `Application`, `Class`, `Method`, `RequiredParams`, `OptionalParams`
> FROM `Routes` WHERE `RequestMethod` = "GET" AND "/" LIKE `URI`
>
> As "GET" is the default Request Method, and the string I'm passing is a
> single slash.
>
> If so, I didn't receive any result when, if I use PHP preg_match() function,
> I receive a match of the first Regular Expression: /(.*?)

REGEXP and LIKE have different pattern syntax. You mant

'/' REGEXP URI

assuming you have an appropriate custom function registered with SQLite.

Where does /(.*?) come from? In the image you referenced a couple posts 
back, nothing like that appeared.

> Igor Tandetnik said it's not needed the slashes, because they are
> delimiters.

No, I said the opposite: I thought you were using slashes as delimiters, 
and pointed out that they are *not* treated as such. Now I understand 
that you want to match slashes literally. This is what happens out of 
the box; just ignore that part of the discussion.
-- 
Igor Tandetnik

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

Re: [sqlite] Using stored Regular Expressions to match given String

[Bruno Augusto]

So, it would be:

SELECT `Application`, `Class`, `Method`, `RequiredParams`, `OptionalParams`
FROM `Routes` WHERE `RequestMethod` = "GET" AND "/" LIKE `URI`

As "GET" is the default Request Method, and the string I'm passing is a
single slash.

If so, I didn't receive any result when, if I use PHP preg_match() function,
I receive a match of the first Regular Expression: /(.*?)

Igor Tandetnik said it's not needed the slashes, because they are
delimiters.

But it's not the case. The left slash is the first character of Request URL
(after the Host) and the right slash, when present, is a bugfix to make the
system compativble with all browsers behaviours.

Best regards

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

Re: [sqlite] Using stored Regular Expressions to match given String

[Simon Slavin]

On 12 Jan 2011, at 9:14pm, Bruno Augusto wrote:

> When querying, I will NOT pass the Regular Expression, I will pass the
> string to be used as target for the stored patterns.

The previous responses were fine.  Operators take more than one operand, and 
you will need to specify all of them each time you use the operator.  Each 
operand can be specified in the SELECT command, or a reference to a column in 
the TABLE you're SELECTing from.  So if you choose to use, for example, LIKE  
you might do

SELECT * FROM myTable WHERE filename LIKE 'x_.com'

but you might instead do

SELECT * FROM myTable WHERE 'x2.com' LIKE pattern

One matches a supplied pattern with all values stored in the TABLE, the other 
checks a supplied value against all the patterns stored in the TABLE.  The 
second of these is what you asked for, I think.

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

Re: [sqlite] How to track a record if the aliased id is changed

[Simon Slavin]

On 12 Jan 2011, at 9:02pm, Max Vlasov wrote:

> It's about the utility, when the data is presented with a grid and every
> cell of opened db and * fields of table can be edited.. I'm aware that my
> own actions can lead to constraint failure, but even for a legitimate change
> I currently can not point to the same record, and even if I could (data in
> this case is simply cached) I would not do this next time since this record
> after this change have another .rowid and it's not possible to format new
> "update where rowid=" query correctly.

Ah, okay.  You have to have access to the value that was stored in the cell 
before it was changed.

You can pre-scan the columns of a table to figure out which ones are likely to 
be useful because they're UNIQUE.  If you don't find one, then use 'rowid'.  
But I don't think there's a simple perfect solution for this. You are in the 
position of needing to reread the record you just changed to find out if change 
they made to one column TRIGGERed some other change to that record.

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

Re: [sqlite] Using stored Regular Expressions to match given String

[Bruno Augusto]

Maybe I'm doing some kind of confusion or I can't express myself entirely.

Please take a look in the image:
http://img268.imageshack.us/img268/5526/74988733.png

This is the structure of database that will be used as part of a MVC Router,
part of a framework I'm developing.

The only column that is important for the question is "URI", which is where
my Regular Expressions are stored.

When querying, I will NOT pass the Regular Expression, I will pass the
string to be used as target for the stored patterns.

And that is the main point of my question. I don't know how (and IF is
possible) to do this.

I already have an alternative: Search for all patterns and test them, one by
one, with PHP. But one single access would take much time. Can you imagine
1000 simultaneous accesses?

Best Regards

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

Re: [sqlite] How to track a record if the aliased id is changed

[Max Vlasov]

On Wed, Jan 12, 2011 at 11:35 PM, Simon Slavin  wrote:

> If you're writing an arbitrary SQL utility, I think the answer depends on
> why you want to keep track of a particular record.  You either want to
> refresh the display or you don't, and either way the connection between old
> and new rowids doesn't matter because you're going to have to redraw more
> than one record.
>
>

It's about the utility, when the data is presented with a grid and every
cell of opened db and * fields of table can be edited.. I'm aware that my
own actions can lead to constraint failure, but even for a legitimate change
I currently can not point to the same record, and even if I could (data in
this case is simply cached) I would not do this next time since this record
after this change have another .rowid and it's not possible to format new
"update where rowid=" query correctly.

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

Re: [sqlite] How to track a record if the aliased id is changed

[Simon Slavin]

On 12 Jan 2011, at 7:31pm, Max Vlasov wrote:

> for queries like UPDATE ... WHERE rowid=... one can in most cases reread the
> record (based on the rowid) and keep for example the cursor in the grid at
> the same record. But what if one of changed field is aliased to rowid, is
> there a way to find/track the changed record? In other words, how to find
> out the new rowid value for this record

Are you writing a real application here, or some sort of arbitrary SQLite 
management utility ?

In real life -- and real applications -- you never let anyone change rowids.  
In fact users should never even see rowids: they're purely for the convenience 
of the programmer.  If there's some record number (e.g. customer number) users 
are aware of and might want to change, it's not a good candidate for a rowid 
alias precisely because users might want to change it and this would require a 
ripple-effect change in related records in other tables.  Use two different 
columns: recordID and customerID, and keep the recordID (which can be a rowid 
alias) hidden from the user.

If you're writing an arbitrary SQL utility, I think the answer depends on why 
you want to keep track of a particular record.  You either want to refresh the 
display or you don't, and either way the connection between old and new rowids 
doesn't matter because you're going to have to redraw more than one record.

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

[sqlite] How to track a record if the aliased id is changed

[Max Vlasov]

Hi,
for queries like UPDATE ... WHERE rowid=... one can in most cases reread the
record (based on the rowid) and keep for example the cursor in the grid at
the same record. But what if one of changed field is aliased to rowid, is
there a way to find/track the changed record? In other words, how to find
out the new rowid value for this record

I thought about a partial solution: to track that the rowid used in the
query is no longer exists so there was a change to some new value, but what
the value is unknown.

There's also pragma table_info returning pk info, and I probably can track
the aliased fields but there's at least one exception in the docs...

Any thoughts?

Thanks,

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

Re: [sqlite] Inconsistent SELECT results

[Igor Tandetnik]

On 1/12/2011 1:59 PM, Ed Nolan wrote:
> --- this fails (no result) 
> 
>
> CREATE TABLE f_main (id INTEGER PRIMARY KEY,pathid INTEGER,name TEXT);
> INSERT INTO f_main VALUES(4,5,"my_filename");
>
> CREATE TABLE f_path (pathid INTEGER PRIMARY KEY,name TEXT);
> INSERT INTO f_path VALUES(5,"md5");
>
> CREATE TABLE f_stats (id INTEGER PRIMARY KEY,size INTEGER,md5 TEXT);
> INSERT INTO f_stats VALUES(4,973,"16f5a3c8edc1668d7318f6113b810009");
>
> SELECT s.id,s.size,s.md5
> FROM f_main m,f_stats s,f_path p
> WHERE m.name="my_filename" AND p.name="md5" AND m.pathid=p.pathid AND 
> m.id=s.id;

In SQL, single quotes 'like this' are used to enclose string literals, 
while double quotes "like this" are used to enclose identifiers (the 
latter is to allow identifiers that include spaces or other special 
characters, or are the same as SQL keywords).

As an extension, SQLite accepts string literals enclosed in double 
quotes. It first checks whether the string matches any identifier 
present in the query, and if not, assumes it to be a string literal.

Thus, in your SELECT statement "md5" is not a string literal, but a 
reference to f_stats.md5 column. It so happens that the condition 
p.name=s.md5 doesn't hold for any rows, hence you get no results.

I suggest you get into the habit of consistently using single quotes 
around string literals in SQL.
-- 
Igor Tandetnik

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

[sqlite] Inconsistent SELECT results

[Ed Nolan]

Hi,

I've been using Sqlite on and off for a while now, with great results.  Awesome
piece of software :)  Yesterday I ran into an odd problem which has me stumped.
Perhaps I'm overlooking something ridiculously simple?  My original 
implementation
is quite a bit more complicated, so I've simplified it just enough to be 
reproducable
(I hope ...)

When I run:

rm ./testme.db ; sqlite3 ./testme.db < input.sql

The first sql batch sequence below returns nothing (unexpected).  The second 
sql 
sequence returns the value that I expected. The only difference between the two 
sequences is one uses a TEXT value of "md5" and the other uses a TEXT value of 
"hu5"

--- this fails (no result) 


CREATE TABLE f_main (id INTEGER PRIMARY KEY,pathid INTEGER,name TEXT);
INSERT INTO f_main VALUES(4,5,"my_filename");

CREATE TABLE f_path (pathid INTEGER PRIMARY KEY,name TEXT);
INSERT INTO f_path VALUES(5,"md5");

CREATE TABLE f_stats (id INTEGER PRIMARY KEY,size INTEGER,md5 TEXT);
INSERT INTO f_stats VALUES(4,973,"16f5a3c8edc1668d7318f6113b810009");

SELECT s.id,s.size,s.md5 
FROM f_main m,f_stats s,f_path p 
WHERE m.name="my_filename" AND p.name="md5" AND m.pathid=p.pathid AND m.id=s.id;


-- but this succeeds:  4|973|16f5a3c8edc1668d7318f6113b810009 
---

CREATE TABLE f_main (id INTEGER PRIMARY KEY,pathid INTEGER,name TEXT);
INSERT INTO f_main VALUES(4,5,"my_filename");

CREATE TABLE f_path (pathid INTEGER PRIMARY KEY,name TEXT);
INSERT INTO f_path VALUES(5,"hu5");

CREATE TABLE f_stats (id INTEGER PRIMARY KEY,size INTEGER,md5 TEXT);
INSERT INTO f_stats VALUES(4,973,"16f5a3c8edc1668d7318f6113b810009");

SELECT s.id,s.size,s.md5 
FROM f_main m,f_stats s,f_path p 
WHERE m.name="my_filename" AND p.name="hu5" AND m.pathid=p.pathid AND m.id=s.id;




I get the same results on 3 different Linux machines, across 3 different Sqlite
versions.  2 versions were Ubuntu-based and the third (3.7.4) I compiled from
source obtained from the sqlite.org website.

Linux outpost3 2.6.24-23-xen #1 SMP Mon Jan 26 03:09:12 UTC 2009 x86_64 
GNU/Linux
a virtual server (xen based) running Ubuntu 8
/dev/sda1 on / type ext3 (rw,relatime,errors=remount-ro)
sqlite3 3.4.2
sqlite3 3.7.4

Linux castle 2.6.32-23-generic #37-Ubuntu SMP Fri Jun 11 07:54:58 UTC 2010 i686 
GNU/Linux
a desktop machine running Ubuntu 10
/dev/sda1 on / type reiserfs (rw,relatime,notail)
sqlite3 3.6.22
sqlite3 3.7.4

Linux guardian 2.4.28 #1 Fri Dec 10 18:55:16 CST 2004 i686 unknown unknown 
GNU/Linux
an older server running slackware 10
/dev/hda2 on / type reiserfs (rw)
sqlite3 3.7.4

So, am I missing something?  Or is this a bug of some sort?


Thanks for any help,
Ed Nolan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] huge performance decrease after deleting/creating a table !

[Philip Graham Willoughby]

On 12 Jan 2011, at 15:12, Vander Clock Stephane wrote:

> before it's return in 100 ms now 30 secondes :(
> 
> What i do wrong and how to correct it ?

Issue the SQL command:

ANALYZE

This will help the query planner understand which indexes are best to use; I 
have seen it dramatically improve matters in my tests. Obviously your database, 
data and queries are different to mine so it may not help you.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

[sqlite] [ANN] SQLiteConverter

[Marco Bambini]

SQLabs today is pleased to announce SQLiteConverter, the fastest and easiest 
way to convert your mySQL, PostgreSQL, Oracle (natively) and a wide range of 
ODBC compliant databases (like Microsoft SQL Server, Access, FoxPro and many 
others) to sqlite. It combines a very intuitive interface with powerful 
features so you can convert an existing remotely hosted database to sqlite in 
few steps.

Thanks to a very intuitive wizard you'll be guide step by step to 5 easy 
operations in order to completely convert your remote databases. The entire 
process depends on how much data you need to convert but most of the time 
you'll be able to complete it in few seconds.

SQLiteConverter is the ideal tool not only to complete a conversion between 
different databases to sqlite but it is also the ideal solution to backup your 
remote data to another disk based relational sql database. You always have full 
control over the conversion process, you can decide to convert the entire 
database or just select the individual tables you want to backup.

More information available from:
http://www.sqlabs.com/sqliteconverter.php

--
Marco Bambini
http://www.sqlabs.com

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

[sqlite] huge performance decrease after deleting/creating a table !

[Vander Clock Stephane]

hello,

i want to update a column name in a table, but the only way for that is 
to redo the table
the table have around 15 000 000 records

so i do like this :

ALTER TABLE PICTURE_HASH_ID RENAME TO PICTURE_HASH_ID_OLD;
DROP INDEX PICTURE_HASH_ID_PIC_IDX;
CREATE TABLE PICTURE_HASH_ID(
   HASH_ID INTEGER PRIMARY KEY ASC,
   SERVER_ID INTEGER,
   VOLUME_ID INTEGER,
   NEEDLE_ID UNSIGNED INTEGER,
   NEEDLE_KEY UNSIGNED BIG INT
);
CREATE UNIQUE INDEX PICTURE_HASH_ID_PIC_IDX on PICTURE_HASH_ID 
(SERVER_ID ASC, VOLUME_ID ASC, NEEDLE_ID ASC, NEEDLE_KEY ASC);

INSERT INTO PICTURE_HASH_ID(HASH_ID, SERVER_ID, VOLUME_ID, NEEDLE_ID, 
NEEDLE_KEY) SELECT HASH_ID, SERVER_ID, VOLUME_ID, PICTURE_ID, 
PICTURE_KEY FROM PICTURE_HASH_ID_OLD;

DROP TABLE PICTURE_HASH_ID_OLD;


but now the speed of the insert/select is completely crazy.
before it's take 100 ms max and now it's take 30 secondes !


this the database definition :

PRAGMA page_size = 1024;
PRAGMA encoding = "UTF-8";
PRAGMA legacy_file_format = 0;
PRAGMA auto_vacuum = NONE;

CREATE VIRTUAL TABLE PICTURE_HASH_1 USING rtree(
   HASH_ID INTEGER PRIMARY KEY ASC,
   x1_y1_min UNSIGNED TINYINT, x1_y1_max UNSIGNED TINYINT,
   x1_y2_min UNSIGNED TINYINT, x1_y2_max UNSIGNED TINYINT,
   x1_y3_min UNSIGNED TINYINT, x1_y3_max UNSIGNED TINYINT,
   x1_y4_min UNSIGNED TINYINT, x1_y4_max UNSIGNED TINYINT,
   x1_y5_min UNSIGNED TINYINT, x1_y5_max UNSIGNED TINYINT
);

CREATE VIRTUAL TABLE PICTURE_HASH_2 USING rtree(
   HASH_ID INTEGER PRIMARY KEY ASC,
   x2_y1_min UNSIGNED TINYINT, x2_y1_max UNSIGNED TINYINT,
   x2_y2_min UNSIGNED TINYINT, x2_y2_max UNSIGNED TINYINT,
   x2_y3_min UNSIGNED TINYINT, x2_y3_max UNSIGNED TINYINT,
   x2_y4_min UNSIGNED TINYINT, x2_y4_max UNSIGNED TINYINT,
   x2_y5_min UNSIGNED TINYINT, x2_y5_max UNSIGNED TINYINT
);

CREATE VIRTUAL TABLE PICTURE_HASH_3 USING rtree(
   HASH_ID INTEGER PRIMARY KEY ASC,
   x3_y1_min UNSIGNED TINYINT, x3_y1_max UNSIGNED TINYINT,
   x3_y2_min UNSIGNED TINYINT, x3_y2_max UNSIGNED TINYINT,
   x3_y3_min UNSIGNED TINYINT, x3_y3_max UNSIGNED TINYINT,
   x3_y4_min UNSIGNED TINYINT, x3_y4_max UNSIGNED TINYINT,
   x3_y5_min UNSIGNED TINYINT, x3_y5_max UNSIGNED TINYINT
);

CREATE VIRTUAL TABLE PICTURE_HASH_4 USING rtree(
   HASH_ID INTEGER PRIMARY KEY ASC,
   x4_y1_min UNSIGNED TINYINT, x4_y1_max UNSIGNED TINYINT,
   x4_y2_min UNSIGNED TINYINT, x4_y2_max UNSIGNED TINYINT,
   x4_y3_min UNSIGNED TINYINT, x4_y3_max UNSIGNED TINYINT,
   x4_y4_min UNSIGNED TINYINT, x4_y4_max UNSIGNED TINYINT,
   x4_y5_min UNSIGNED TINYINT, x4_y5_max UNSIGNED TINYINT
);

CREATE VIRTUAL TABLE PICTURE_HASH_5 USING rtree(
   HASH_ID INTEGER PRIMARY KEY ASC,
   x5_y1_min UNSIGNED TINYINT, x5_y1_max UNSIGNED TINYINT,
   x5_y2_min UNSIGNED TINYINT, x5_y2_max UNSIGNED TINYINT,
   x5_y3_min UNSIGNED TINYINT, x5_y3_max UNSIGNED TINYINT,
   x5_y4_min UNSIGNED TINYINT, x5_y4_max UNSIGNED TINYINT,
   x5_y5_min UNSIGNED TINYINT, x5_y5_max UNSIGNED TINYINT
);

CREATE TABLE PICTURE_HASH_ID(
   HASH_ID INTEGER PRIMARY KEY ASC,
   SERVER_ID INTEGER,
   VOLUME_ID INTEGER,
   NEEDLE_ID UNSIGNED INTEGER,
   NEEDLE_KEY UNSIGNED BIG INT
);
CREATE UNIQUE INDEX PICTURE_HASH_ID_PIC_IDX on PICTURE_HASH_ID 
(SERVER_ID ASC, VOLUME_ID ASC, NEEDLE_ID ASC, NEEDLE_KEY ASC);


and i do only this kind of SQL :


Select
P1.SERVER_ID,
P1.VOLUME_ID,
P1.NEEDLE_ID,
P1.NEEDLE_KEY
from
PICTURE_HASH_1 H1
JOIN PICTURE_HASH_2 H2 ON H2.HASH_ID=H1.HASH_ID
JOIN PICTURE_HASH_3 H3 ON H3.HASH_ID=H1.HASH_ID
JOIN PICTURE_HASH_4 H4 ON H4.HASH_ID=H1.HASH_ID
JOIN PICTURE_HASH_5 H5 ON H5.HASH_ID=H1.HASH_ID
JOIN PICTURE_HASH_ID P1 ON P1.HASH_ID=H1.HASH_ID
where
H1.x1_y1_min <= xxx and
H1.x1_y1_max >= yyy and
...
H5.x5_y5_min <= www and
H5.x5_y5_max >= zzz

following by some insert

insert into PICTURE_HASH_1 ..
insert into PICTURE_HASH_2 ..
insert into PICTURE_HASH_3 ..
insert into PICTURE_HASH_4 ..
insert into PICTURE_HASH_5 ..
insert into PICTURE_HASH_ID ..


before it's return in 100 ms now 30 secondes :(

What i do wrong and how to correct it ?
i m on Windows 2008 R2 64 bit

thanks you by advance
stéphane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Propose minor incompatible API change

[Philip Graham Willoughby]

On 12 Jan 2011, at 12:49, Andy Gibbs wrote:

> On Wednesday, January 12, 2011 10:08 AM, Philip Graham Willoughby wrote:
> 
>> unfortunately 3.7.2 shipped in Ubuntu Maverick and
>> 3.6.23.1 shipped in a maintenance update for Fedora
>> Core 14. So lots of people already have both behaviours
>> in the wild.
> 
> Actually, the first alteration happened *after* 3.6.23.1 which released on 
> 30th March; the alteration happened 17th April --  
> http://www.sqlite.org/src/info/3e646e3f4c -- so maybe that limits a little 
> the number of people affected?

Well, it means Fedora doesn't have the change, and anyone with 3.7.0 or later 
does.

The list I gave was based on which operating systems were easy for me to find 
out about rather than any attempt to be exhaustive.

Incidentally sqlite3_libversion() on iOS 4.2.1 (as used on iPhone/iPad) reports 
3.6.23.2, which is not listed on http://www.sqlite.org/changes.html but was 
presumably cut at some point after 3.6.23.1: your guess is as good as mine as 
to whether it contains the April 17 change or not.

The docs say versions 'after version 3.6.23.1' have the relevant behavioural 
change, so perhaps that means a lot of smartphones already have it.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] 3.7.4 possible restore bug

[Max Vlasov]

On Wed, Jan 12, 2011 at 3:48 PM, Richard Hipp  wrote:

> On Wed, Jan 12, 2011 at 5:27 AM, Max Vlasov  wrote:
>
> > On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov 
> wrote:
> >
> > > Hi,
> > > I experimented with artificial power loss (using hd box) and 3.7.4 both
> > > library and shell didn't restore the files to the initial state. 3.6.10
> > > restores successfully.
> > >
> >
> >
> > This is a kind of repost, there wasn't any answer for my initial one.
> > Please
> > let me know is this bug or not a bug, I can't sleep without knowing the
> > truth :)
> >
>
>
> The journal file is "stale".  It is not a "hot journal".  And it won't (and
> can't) be rolled back.  It's just a junk file that got left around.  It
> will
> get cleaned up on the next write transaction.  I guess you are asking for
> an
> enhancement for it to be cleaned up on the next read transaction.
>
>
Richard, thanks for the clarification, it was just a little strange that
previous versions (at least 3.6.10 I mentioned) deletes the same journal
file upon simple opening (no writing) so possibly something was introduced
after that made things a little more complex as you described.

Also generally speaking the presence of -journal always was a kind of visual
indication that either a write operation in progress (if connection is live)
or something ended unexpectedly on previous session (if it's closed), but it
appears that currently even several read-only sessions after that can keep
this file around for a long period of time and this logic no longer works.

Anyway I can live with that (and also sleep:)

Thanks

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

Re: [sqlite] Using stored Regular Expressions to match given String

[Simon Slavin]

I like how this question got three completely different answers in less than 90 
minutes.

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

Re: [sqlite] Using stored Regular Expressions to match given String

[Igor Tandetnik]

Bruno Augusto  wrote:
> So, I know I need a user function to use the REGEXP operator. But most of
> the implementations I'd found (in PHP, I have to say) requires TWO
> parameters, the Regular Expression and the string to match.

That's not a problem. SQLite takes an expression of the form

a REGEXP b

and automatically rewrites it as regexp(a, b), calling the user-defined 
function.

> I created an SQLITE database where the Regular Expressions is already
> stored, and I would like to send a raw string to retrieve the correspondent
> result.
> 
> E.g.:
> 
> In the database's column 'URI', I have the following values: /(.*?) and
> /main(.*?)
> 
> I would like to send a query similar to: SELECT * FROM `table` WHERE `URI`
> REGEXP( '/main/' )

Drop forward slashes. They have no special meaning in regexp syntax, and are 
treated as literal characters. This means that '/main(.*?)' doesn't in fact 
match '/main/', but does match 'main'. Some programming languages use forward 
slashes as delimiters around regular expressions, but SQL doesn't.

Also, while it's not quite clear what you are trying to do, it seems that the 
values in URI column resemble the regexp syntax. Do you want to use them as 
regexps to match against a string literal? You can write

WHERE '/main/' REGEXP URI

Arguments on either side of REGEXP operator can be arbitrary SQL expressions, 
they are not limited to string literals.

Finally, for a simple substring search, you can use LIKE or GLOB operators, the 
implementation for which is built into SQLite:

SELECT * FROM MyTable WHERE URI LIKE '%main%';
SELECT * FROM MyTable WHERE URI GLOB '*main*';

-- 
Igor Tandetnik

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

Re: [sqlite] Propose minor incompatible API change

[Andy Gibbs]

On Wednesday, January 12, 2011 10:08 AM, Philip Graham Willoughby wrote:

> unfortunately 3.7.2 shipped in Ubuntu Maverick and
> 3.6.23.1 shipped in a maintenance update for Fedora
> Core 14. So lots of people already have both behaviours
> in the wild.

Actually, the first alteration happened *after* 3.6.23.1 which released on 
30th March; the alteration happened 17th April --  
http://www.sqlite.org/src/info/3e646e3f4c -- so maybe that limits a little 
the number of people affected?

Andy

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

Re: [sqlite] 3.7.4 possible restore bug

[Richard Hipp]

On Wed, Jan 12, 2011 at 5:27 AM, Max Vlasov  wrote:

> On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov  wrote:
>
> > Hi,
> > I experimented with artificial power loss (using hd box) and 3.7.4 both
> > library and shell didn't restore the files to the initial state. 3.6.10
> > restores successfully.
> >
>
>
> This is a kind of repost, there wasn't any answer for my initial one.
> Please
> let me know is this bug or not a bug, I can't sleep without knowing the
> truth :)
>

I suppose it would be a nice-to-have if SQLite were more aggressive about
deleting stale journal files.  But it is not a serious problem.  There is no
danger of database corruption here, or anything like that.

The journal file is "stale".  It is not a "hot journal".  And it won't (and
can't) be rolled back.  It's just a junk file that got left around.  It will
get cleaned up on the next write transaction.  I guess you are asking for an
enhancement for it to be cleaned up on the next read transaction.

So even though this is not currently a problem, "fixing" it might create
real corruptions issues.  The deletion of a journal files need to be done
with extreme caution, lest they be deleted when they is still hot and hence
cause database corruption.  It will take us a lot of study and testing to
verify that the deletion is "safe".  So this is not a simple fix.



>
> Steps to reproduce without additional test data
>
> 1. download windows shell 3.7.4 binary
>
> 2.
> > sqlite testdb
> > CREATE TABLE [TableOne] ([VALUE] INTEGER);
> > .quit
>
> 3.
> > sqlite testdb
> > BEGIN TRANSACTION;
> > INSERT INTO TableOne (Value) VALUES (123);
>
> 4.
> while still running the shell, "unexpectedly" interrupt the executable with
> some external tools (task manager for example)
> after this there's a file called testdb-journal near testdb
>
> 5.
> > sqlite testdb
> > .quit
>
> The result:
>  testdb-journal is still there.
> Expected result:
>  the journal file should be deleted by sqlite.
>
> Max Vlasov
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] INDEX not working after insert's

[srl309]

I know its better to recreate the index after the insert, but it seems like
the index is not being updated or is for some reason no longer useful after
the insert.

Before the inserts i have an index on visitors(suburb);

This is my insert statement i insert 1 values:
INSERT INTO visitors VALUES ( :id, strftime('%s','now', 'localtime'), :name,
:suburb, :town, strftime('%s','now', 'localtime'));
sqlite3_prepare_v2 (db, sql, strlen (sql) + 1, &stmt, NULL);

i am also deleting the values after the insert
sqlite3_exec(db, "DELETE FROM visitors where id >20;", NULL, NULL, 0);

after queries above i run this
select suburb, count(*) from visitors WHERE entry_time >= (strftime('%s'
,'now', 'start of day', 'localtime')) group by suburb;
CPU Time: user 3.057620 sys 0.358802
0|0|0|SCAN TABLE visitors (~19998 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY

time after i recreate the index 
CPU Time: user 1.934412 sys 0.343202
0|0|0|SCAN TABLE visitors USING INDEX idx (~19998 rows)
-- 
View this message in context: 
http://old.nabble.com/INDEX-not-working-after-insert%27s-tp30652638p30652638.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Using stored Regular Expressions to match given String

[Simon Slavin]

On 12 Jan 2011, at 11:40am, Bruno Augusto wrote:

> I hope I'm doing the right thing. I never used Mailing Lists before.

So far, so good.

> So, I know I need a user function to use the REGEXP operator. But most of
> the implementations I'd found (in PHP, I have to say) requires TWO
> parameters, the Regular Expression and the string to match.
> 
> I created an SQLITE database where the Regular Expressions is already
> stored, and I would like to send a raw string to retrieve the correspondent
> result.

Look at the LIKE operator:



You will also see described there the GLOB operator which may or may not be 
more help.

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

Re: [sqlite] Using stored Regular Expressions to match given String

[Michele Pradella]

it's possible to use  REGEXP in a select statement like this:
SELECT * from table_name WHERE Path REGEXP 'regular expression'

before you can use REGEXP you have to use the function
sqlite3_create_function(db, "regexp", 2, 
SQLITE_ANY,(void*)pAppPointer,&sqlite3_RegExpFunction,0,0)
to tell SQLITE to map REGEXP keyword to your function

and than you have to implement the function
static void sqlite3_RegExpFunction(sqlite3_context* context, int argc, 
sqlite3_value** values){
 CAppPointer* pAppPointer=(CAppPointer*)sqlite3_user_data(context);
 char* reg = (char*)sqlite3_value_text(values[0]);
 char* text = (char*)sqlite3_value_text(values[1]);

 if ( (argc != 2) || (reg == 0) || (text == 0) ){
 sqlite3_result_error(context, "SQL function regexp() called 
with invalid arguments.\n", -1);
 return;
 }

 //item not match
 return sqlite3_result_int(context, 0);
 //item match
   return sqlite3_result_int(context, 1);
}


Il 12/01/2011 12.40, Bruno Augusto ha scritto:
> Hi,
>
> I hope I'm doing the right thing. I never used Mailing Lists before.
>
> So, I know I need a user function to use the REGEXP operator. But most of
> the implementations I'd found (in PHP, I have to say) requires TWO
> parameters, the Regular Expression and the string to match.
>
> I created an SQLITE database where the Regular Expressions is already
> stored, and I would like to send a raw string to retrieve the correspondent
> result.
>
> E.g.:
>
> In the database's column 'URI', I have the following values: /(.*?) and
> /main(.*?)
>
> I would like to send a query similar to: SELECT * FROM `table` WHERE `URI`
> REGEXP( '/main/' )
>
> And the record to be returned would be, in the example case, the second,
> which have the text "main".
>
> Is that possible? Maybe adding an UDF?
>


-- 
Selea s.r.l.


Michele Pradella R&D


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using stored Regular Expressions to match given String

[Bruno Augusto]

Hi,

I hope I'm doing the right thing. I never used Mailing Lists before.

So, I know I need a user function to use the REGEXP operator. But most of
the implementations I'd found (in PHP, I have to say) requires TWO
parameters, the Regular Expression and the string to match.

I created an SQLITE database where the Regular Expressions is already
stored, and I would like to send a raw string to retrieve the correspondent
result.

E.g.:

In the database's column 'URI', I have the following values: /(.*?) and
/main(.*?)

I would like to send a query similar to: SELECT * FROM `table` WHERE `URI`
REGEXP( '/main/' )

And the record to be returned would be, in the example case, the second,
which have the text "main".

Is that possible? Maybe adding an UDF?

-- 
Best regards

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

Re: [sqlite] long running INSERT (up to now ~ 11 hours and still working)

[Oliver Peters]

Hello,

[...]

deleting

 PRAGMA foreign_keys = ON;

led to a miracle: everything completed after 30 minutes.

Because of the enormous difference (~ 24h without finish compared to 30 minutes)
I can imagine that there are ways to otimize the speed with the use of FKs - but
that seems to be a question only the programmers can answer.

Oliver

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

Re: [sqlite] 3.7.4 possible restore bug

[Hakki Dogusan]

Hi,

12/01/2011 12:27, Max Vlasov wrote:
> On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov  wrote:
>
>> Hi,
>> I experimented with artificial power loss (using hd box) and 3.7.4 both
>> library and shell didn't restore the files to the initial state. 3.6.10
>> restores successfully.
>>
>
>
> This is a kind of repost, there wasn't any answer for my initial one. Please
> let me know is this bug or not a bug, I can't sleep without knowing the
> truth :)
>
> Steps to reproduce without additional test data
> [snip]

3.7.2 behaves identically.

Before quitting:
- select shows nothing
- inserting another record removes journal


>
> Max Vlasov


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

Re: [sqlite] 3.7.4 possible restore bug

[Max Vlasov]

On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov  wrote:

> Hi,
> I experimented with artificial power loss (using hd box) and 3.7.4 both
> library and shell didn't restore the files to the initial state. 3.6.10
> restores successfully.
>


This is a kind of repost, there wasn't any answer for my initial one. Please
let me know is this bug or not a bug, I can't sleep without knowing the
truth :)

Steps to reproduce without additional test data

1. download windows shell 3.7.4 binary

2.
> sqlite testdb
> CREATE TABLE [TableOne] ([VALUE] INTEGER);
> .quit

3.
> sqlite testdb
> BEGIN TRANSACTION;
> INSERT INTO TableOne (Value) VALUES (123);

4.
while still running the shell, "unexpectedly" interrupt the executable with
some external tools (task manager for example)
after this there's a file called testdb-journal near testdb

5.
> sqlite testdb
> .quit

The result:
  testdb-journal is still there.
Expected result:
  the journal file should be deleted by sqlite.

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

Re: [sqlite] long running INSERT (up to now ~ 11 hours and still working)

[Oliver Peters]

Dan Kennedy  writes:

[...]

> >
> > Most INSERTS are done into the table Verteilerdaten (>10,000,000).
> > I think the time depends heavily on the activated FOREIGN KEYs - is
> > my assumption correct and is this a behaviour I only can avoid by not
> > switching this PRAGMA on?
 

[...]

> 
> If you insert a row into a table that has a foreign key constraint,
> it has to search for the corresponding key in the parent table. So
> if the parent table is also large (too large for the cache), those
> searches might be slowing you down significantly. So if you can get
> away with doing the inserts with foreign keys turned off, it is
> worth trying.

[...]

Now I'm pretty sure that mass INSERTs and FOREIGN KEYs are not a couple
belonging together (at least in sqlite) - after almost 24 hours I stopped the
whole thing. Next try will be without FKs. I'll post the results - for those
that are interested.

Oliver

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

Re: [sqlite] Propose minor incompatible API change

[Philip Graham Willoughby]

Please don't top-post and include everything that went before.

On 11 Jan 2011, at 17:10, Scott A Mintz wrote:

> There's the issue of "this is what I meant" vs. "this is what I did." When 
> you have a couple hundred customer's, changing the code is painful but 
> doable.  When you have a couple million customer's, then what is "out 
> there" is the "true" API and must be kept around.

If that were generally true no-one would ever change (or even add) anything in 
any software library product.

The problem here is that there are two mutually incompatible versions out there 
already, pre-3.6.23.1 which generates SQLITE_MISUSE in many cases and 
post-3.6.23.1 which generates SQLITE_MISUSE practically never. Introducing a 
third incompatible version (as proposed to start this thread) which generates 
SQLITE_MISUSE with a frequency between the two is unlikely to make things 
better; if anything it will make things worse.

If we knew that no-one had shipped dynamic libraries for a version >=3.6.23.1 
and http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users