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

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,

Re: [sqlite] Membership operator 'IN' documentation

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.

[sqlite] Membership operator 'IN' documentation

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

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

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

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

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

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

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

Re: [sqlite] PRAGMA Documentation typo

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_

Re: [sqlite] Unexpected cascading delete

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) <

[sqlite] PRAGMA Documentation typo

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.

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

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

Re: [sqlite] Unexpected cascading delete

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;

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

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

Re: [sqlite] Unexpected cascading delete

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

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

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

[sqlite] Unexpected cascading delete

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] Inconsistent SELECT results

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

[sqlite] Inconsistent SELECT results

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

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

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

[sqlite] [ANN] SQLiteConverter

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

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

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

Re: [sqlite] Propose minor incompatible API change

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

Re: [sqlite] 3.7.4 possible restore bug

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

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

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

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

Re: [sqlite] Propose minor incompatible API change

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

Re: [sqlite] 3.7.4 possible restore bug

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

[sqlite] INDEX not working after insert's

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

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

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 >

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

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,_RegExpFunction,0,0) to tell SQLITE to map

[sqlite] Using stored Regular Expressions to match given String

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

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

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

Re: [sqlite] 3.7.4 possible restore bug

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

Re: [sqlite] 3.7.4 possible restore bug

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

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

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?

Re: [sqlite] Propose minor incompatible API change

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