Re: [sqlite] blocking when locking
I was talking about this example by 2009/9/19 Igor Tandetnik "Imagine the classic example, where a transaction first verifies that the balance in a bank account is sufficient, then performs a withdrawal. If it relinquishes all locks between these two steps, then somebody else may record a withdrawal from that account, so that the write operation would then make the balance negative, thus violating an invariant." What I want to say is in this example, there should be only one step, because the transaction knows it will do 'write'. Then the txn should start a write lock before the select. And this is not a good example to explain dead lock, I think. 2009/9/19 Pavel Ivanov > Wenbo, are you talking about what do you want to see in DBMS or are > you trying to explain how SQLite works? > If the latter then you're wrong. In SQLite 'read lock' is designed for > transaction that _made_ any reads, 'write lock' - for transaction that > _made_ any writes. > > Pavel > > On Sat, Sep 19, 2009 at 12:18 AM, Wenbo Zhao wrote: > > This is not a good example i think. > > If a transaction is intent to update after the select, it should start > > a write lock before the select. > > And as described in previous 'dead lock' example, the update in this > > example could fail due to 'dead lock' > > I believe the 'read lock' is designed for a 'read only' transaction, > > and the 'write lock' is for a transaction that 'may write something'. > > > > 2009/9/19 Igor Tandetnik > > > >> Angus March wrote: > >> >Yes, I see. So what is key to the problem is that someone tries to > >> > change their read lock to a write lock. I guess I just thought that > >> > the kernel that manages fcntl() would have a way of dealing with > >> > this. Can this situation not be averted if at step 3, transaction A > >> > releases its read lock before requesting a write lock? > >> > >> Then it wouldn't be much of a transaction, now would it? Imagine the > >> classic example, where a transaction first verifies that the balance in > >> a bank account is sufficient, then performs a withdrawal. If it > >> relinquishes all locks between these two steps, then somebody else may > >> record a withdrawal from that account, so that the write operation would > >> then make the balance negative, thus violating an invariant. > >> > >> Of course, if that's what the application wants, it can simply perform > >> the read and the write operations in two separate transactions. > >> > >> Igor Tandetnik > >> > >> > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > > > -- > > > > Best Regards, > > ZHAO, Wenbo > > > > === > > ___ > > 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 > -- Best Regards, ZHAO, Wenbo === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Jay A. Kreibich wrote: > On Sat, Sep 19, 2009 at 01:14:56PM -0700, Darren Duncan scratched on the wall: >> 3c. I would like to have the option for SQLite to never have duplicate >> unqualified column names; for example, if one said "foo NATURAL INNER >> JOIN bar" then only a single column with the common data would be in >> the result, rather than 2; > > Umm... it does work that way. That's part of the SQL standard. > > NATURAL JOINs and JOIN ... USING( ) will only return one copy of > each column pair used in the JOIN. My memory must be rusty then, because while I believe that is what should happen, I recalled using SQL DBMSs that behaved differently (which is, NATURAL or USING just controlled what records joined with what records, and that all of the non-distinct input columns were still output); I did not check recently though. >> likewise for inner joins with explicit join conditions of "foo.a = >> bar.a" would just return a single "a" in the result. > > This goes against the SQL standard and, in this case, I think > this would be a *very* Bad Idea. You're taking a command format that > is designed to take an arbitrary expression and changing the output > format based off the particulars of that expression. That strikes me > as extremely dangerous. For example, if someone has the first line > of code and changes it to something like the second line of code, > suddenly their output changes! > > ...t1 JOIN t2 ON t1.a = t2.a... => ..., a, ... > ...t1 JOIN t2 ON toupper(t1.a) = toupper(t2.a)... => ..., t1.a, t2.a, ... > > If you only want one column because you're using a straight equality, > use NATURAL or USING. USING is particularly useful to JOIN across a > sub-set of the commonly named columns. The more general solution here to the duplicate column name problem is to be stricter than the SQL standard and treat attempts to return duplicate column names as a syntax or constraint error. For example, if you had 2 tables 'foo' and 'bar' with columns named (a, b) and (b, c), then a plain "select * from foo inner join bar on ..." should throw an exception because there would be two 'b' in the result. And so, proper NATURAL or USING behavior is one way to say "select * from foo inner join bar ..." with success, and spelling out the result column list rather than using "*" is another way. But you have to deal with it explicitly or the SQL will refuse to run, is what the DBMS should do, or the DBMS should be customizable so it can be thusly strict. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reading "packed" data
Hi! An embedded SQL-based database that we used earlier had a concept of packed fetches - this would mean that we could create a certain buffer for results, prepare a query, execute it and read back the results in "groups" of 10 or 20 or 1000 (or "n") results per call.. this was significantly faster than reading the results one at a time. Is there a parallel? Or is it possible to implement such a thing? Thanks, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Date problem
A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT date(253392451200.0, 'unixepoch');" The result was "-09-09". But now I use SQLite 3.6.18 replace it,this SQL execute result is "-1413-03-01". Is this a Bug? -- View this message in context: http://www.nabble.com/SQLite-Date-problem-tp25527521p25527521.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] Most wanted features of SQLite ?
On Sat, Sep 19, 2009 at 01:14:56PM -0700, Darren Duncan scratched on the wall: > 3c. I would like to have the option for SQLite to never have duplicate > unqualified column names; for example, if one said "foo NATURAL INNER > JOIN bar" then only a single column with the common data would be in > the result, rather than 2; Umm... it does work that way. That's part of the SQL standard. NATURAL JOINs and JOIN ... USING( ) will only return one copy of each column pair used in the JOIN. > likewise for inner joins with explicit join conditions of "foo.a = > bar.a" would just return a single "a" in the result. This goes against the SQL standard and, in this case, I think this would be a *very* Bad Idea. You're taking a command format that is designed to take an arbitrary expression and changing the output format based off the particulars of that expression. That strikes me as extremely dangerous. For example, if someone has the first line of code and changes it to something like the second line of code, suddenly their output changes! ...t1 JOIN t2 ON t1.a = t2.a... => ..., a, ... ...t1 JOIN t2 ON toupper(t1.a) = toupper(t2.a)... => ..., t1.a, t2.a, ... If you only want one column because you're using a straight equality, use NATURAL or USING. USING is particularly useful to JOIN across a sub-set of the commonly named columns. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
The word 'Stored Procedures ' when used in the context of DBMS is used to refer to several meanings: 1. Efficiency - compile once when 'stored' and run multiple-times. 2. Data Encapsulation & Access control for DB - DB owns and controls access to its API 'stored' in it. 3. Client-server design - server 'stores' the procedures that any client can then use. 4. Procedural language - a language which supports procedural statements (in addition to the declarative ones provided by SQL): loops, conditionals, variables etc. 5. Physically residing in DB - API resides and moves with the DB. This is the meaning most people are familiar with. StepSqlite satisfies #1, #2, #3 and #4 today and looks forward to support for #5 being implemented in future versions of SQLite. As for the question about distributing libs, StepSqlite gives users two ways to integrate the compiled PL/SQL code into their SQLite applications: 1. Generate a loadable SQLite extension and distribute it with DB. 2. If distributing loadable extensions is a concern (security or otherwise), StepSqlite also has an option to generate a regular C++ library instead. This library can be linked into the user's application code and thus becomes part of the user's code just like any other library would. Wherever the app goes the stored procedures/functions go too. http://www.metatranz.com/stepsqlite Regards, -sk On Sat, Sep 19, 2009 at 10:33 AM, Alexey Pechnikov wrote: > Hello! > > On Saturday 19 September 2009 02:17:39 Subsk79 wrote: >> StepSqlite brings powerful Stored Procedure support with full power of >> PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere >> 'wrapper' so it generates much more efficient code than any wrapper >> could ever achieve - for instance, it pre-compiles all SQL in your >> code right when the lib is loaded - no compile-overload at runtime - >> this is exactly what one expects from a true 'Stored' Procedure. > > Do you have support for compiled extension stored into database table? > It's not good way to distribute external libs. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > 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] Most wanted features of SQLite ?
Alexey Pechnikov wrote: > Hello! > > On Sunday 20 September 2009 00:14:56 Darren Duncan wrote: >> 3b. I would like to have the option for SQLite to always operate using >> 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply >> treated >> as another value of its own singleton type that is disjoint from all other >> types >> same as Integer, Numeric, Text, Blob are disjoint. And so, one could then >> just >> use ordinary equality or not-equality tests to check for NULL, and NULL >> would >> equal NULL but not equal anything else, and boolean tests would always >> return >> true or false, not null. Once again, this would mean that behavior is more >> like >> what users actually expect and bugs can be avoided, and the query optimizer >> can >> be more efficient again, allowing more reorganization knowing at answers >> wouldn't change due to this. > > NULL is the old RDBMS problem. And SQLite Tcl interface has no equal > availability > for NULL values because we can't operate with non-defined variables. So we > can > translate NULL values into empty Tcl strings but not vice versa. Well we could also ditch NULL entirely in the database as the relational model doesn't actually require it and it is simply a convenient way to say we know we don't have normal data somewhere. Though NULL is also deficient in that way because it doesn't say *why* we don't have normal data (eg, not applicable versus applicable but unknown). I would argue for the elimination of NULL entirely and just let people design their databases to explicitly say "this point is missing for this reason", but what I proposed above was meant to be a softer intermediate approach to let NULL-depending people down easier. As for Tcl, well one solution there is to create a new singleton TCL type and use its one value to correspond to NULL, and so then empty string will continue to just mean empty string, as it should. Not being able to distinguish known-to-be-an-empty-string from unknown-or-N/A value is a *bad* thing. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 00:14:56 Darren Duncan wrote: > 3b. I would like to have the option for SQLite to always operate using > 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply > treated > as another value of its own singleton type that is disjoint from all other > types > same as Integer, Numeric, Text, Blob are disjoint. And so, one could then > just > use ordinary equality or not-equality tests to check for NULL, and NULL would > equal NULL but not equal anything else, and boolean tests would always return > true or false, not null. Once again, this would mean that behavior is more > like > what users actually expect and bugs can be avoided, and the query optimizer > can > be more efficient again, allowing more reorganization knowing at answers > wouldn't change due to this. NULL is the old RDBMS problem. And SQLite Tcl interface has no equal availability for NULL values because we can't operate with non-defined variables. So we can translate NULL values into empty Tcl strings but not vice versa. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Sat, 19 Sep 2009 21:57:13 +0100, Simon Slavin wrote: > >On 19 Sep 2009, at 9:14pm, Darren Duncan wrote: > >> Simon Slavin wrote: >>> On 18 Sep 2009, at 9:43pm, Noah Hart wrote: Stored Procedures >>> >>> How do those differ from what can be done with triggers ? >> >> A stored procedure is an arbitrary-sized named sequence of >> statements to >> execute, which is stored in the database as data (same as table or >> view or >> trigger definitions), and which generally is explicitly invoked as a >> statement. >> >> A trigger is a stimulus-response rule that says when a particular >> event happens >> then a particular stored procedure is to be executed automatically. >> In the >> general case, this is like an event handler in a typical application >> that >> responds to mouse clicks or network connections or whatever. Some >> DBMSs support >> this in the more general sense of "do this when this happens" but >> most DBMSs >> that support "triggers" just handler more limited situations, such >> as "do this >> before/after a record is inserted/updated/deleted in this table". > >Ah. Okay, so in SQLite3 you can emulate stored procedures using >triggers. Just define a trigger to operate on something that doesn't >matter to you. For instance inserting a record in a table that you >never bother reading. Every so often you delete all rows in the table >just to keep it from taking up pointless space. Yes, or UPDATE a VIEW which has an INSTEAD OF trigger defined for it. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 19 Sep 2009, at 9:14pm, Darren Duncan wrote: > Simon Slavin wrote: >> On 18 Sep 2009, at 9:43pm, Noah Hart wrote: >>> Stored Procedures >> >> How do those differ from what can be done with triggers ? > > A stored procedure is an arbitrary-sized named sequence of > statements to > execute, which is stored in the database as data (same as table or > view or > trigger definitions), and which generally is explicitly invoked as a > statement. > > A trigger is a stimulus-response rule that says when a particular > event happens > then a particular stored procedure is to be executed automatically. > In the > general case, this is like an event handler in a typical application > that > responds to mouse clicks or network connections or whatever. Some > DBMSs support > this in the more general sense of "do this when this happens" but > most DBMSs > that support "triggers" just handler more limited situations, such > as "do this > before/after a record is inserted/updated/deleted in this table". Ah. Okay, so in SQLite3 you can emulate stored procedures using triggers. Just define a trigger to operate on something that doesn't matter to you. For instance inserting a record in a table that you never bother reading. Every so often you delete all rows in the table just to keep it from taking up pointless space. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie needing help to get set up for C++ in Windows
I am brand new to SQLite and need to set up a Windows environment for use with C++. I am using NetBeans 6.5.1 as my development environment and Cygwin (GNU) C++. I have seen a lot of documentation on the interface, but I have not found much in the way of getting set up. What, exactly do I need to do in order to get a library that I can link to my application? I've seen a DLL. Do I just need to download that DLL and put it in my system directory? Or is there another library type that I need to download? If the DLL is all that I need, could someone give me a hint as to how to link to it in NetBeans? All my NetBeans work to date has been in Java and I just create a library that points to a jar file. Or if there is a good "Getting Started" document source that explains this, I would appreciate a link. Thanks. -- View this message in context: http://www.nabble.com/Newbie-needing-help-to-get-set-up-for-C%2B%2B-in-Windows-tp25525319p25525319.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] Understanding ICU collations: Where needed?
Hello!
You can add code for load collation for all database connections.
As example, in function sqlite3IcuInit() before "return rc;" add these lines:
UErrorCode status = U_ZERO_ERROR;
UCollator *pUCollator = ucol_open("ru_RU", &status);
if( !U_SUCCESS(status) ){
return SQLITE_ERROR;
}
rc = sqlite3_create_collation_v2(db, "russian", SQLITE_UTF16, (void
*)pUCollator,.
icuCollationColl, icuCollationDel
);
if( rc!=SQLITE_OK ){
ucol_close(pUCollator);
}
And you collation will be loaded automatically:
$ sqlite3
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma collation_list;
0|russian
1|NOCASE
2|RTRIM
3|BINARY
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > On 18 Sep 2009, at 9:43pm, Noah Hart wrote: >> Stored Procedures > > How do those differ from what can be done with triggers ? A stored procedure is an arbitrary-sized named sequence of statements to execute, which is stored in the database as data (same as table or view or trigger definitions), and which generally is explicitly invoked as a statement. A trigger is a stimulus-response rule that says when a particular event happens then a particular stored procedure is to be executed automatically. In the general case, this is like an event handler in a typical application that responds to mouse clicks or network connections or whatever. Some DBMSs support this in the more general sense of "do this when this happens" but most DBMSs that support "triggers" just handler more limited situations, such as "do this before/after a record is inserted/updated/deleted in this table". -- As for my own wishlist, well I'll name a few items. 1. If I were asked a year ago I would say top of the list is support for child transactions, but these were already delivered in 3.6.8 so my greatest wish is already met. 2. While stored procedures would be very valuable, I do not see them necessary for SQLite itself to implement, since these at least can be effectively done at the user level, especially when you consider that you would want to have parameters and variables so to either use the same user input with multiple statements in the procedure or feed results of one statement to input to another, and you'd probably want conditionals or loops etc, which host languages already provide for your use. 3. I would like to see a pragma and/or compile-time option (or several for finer graining) that subtly alters some SQL semantics or allowed syntax when in use, to deal with some SQL mis-features. 3a. I would like to have the option for SQLite to always operate using set semantics rather than bag semantics, automatically, so that for example any join or union or select-list or group or count() or aggregate etc would always just return unique rows and never treat duplicates as being distinct. Using this mode would first of all be more likely to give the results that users actually want, avoiding common bugs, and also allow for the SQLite query optimizer to be much more efficient as it could safely make more rearrangements of the query without worry that doing so would change the answer in the presence of duplicates. 3b. I would like to have the option for SQLite to always operate using 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply treated as another value of its own singleton type that is disjoint from all other types same as Integer, Numeric, Text, Blob are disjoint. And so, one could then just use ordinary equality or not-equality tests to check for NULL, and NULL would equal NULL but not equal anything else, and boolean tests would always return true or false, not null. Once again, this would mean that behavior is more like what users actually expect and bugs can be avoided, and the query optimizer can be more efficient again, allowing more reorganization knowing at answers wouldn't change due to this. 3c. I would like to have the option for SQLite to never have duplicate unqualified column names; for example, if one said "foo NATURAL INNER JOIN bar" then only a single column with the common data would be in the result, rather than 2; likewise for inner joins with explicit join conditions of "foo.a = bar.a" would just return a single "a" in the result. Such things as this, especially 3a,3b, *are* best implemented at the internal guts level of SQLite, for what is hopefully obvious reasons. Now writing SQL that targets these semantics may not be fully portable, but it would be a lot more correct and trouble-free for people just using SQLite, or other DBMSs that support those semantics I proposed. And a point is that an implementation of what I proposed would be *simpler*/*liter* than what is required to implement the standard SQL semantics that support duplicates and 3VL, and it generally does what people actually want. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] full outer join questions
Pavel Ivanov wrote: >> select coalesce(RT1.PID, RT2.PID) as PID, RT1.V1, RT2.V2 ... >> > > I find function ifnull() more readable in such cases. ;-) > > thanks guys, both (of course ;-) works perfectly. I have to study the functions in SQLite some more !! cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance in a case of big columns number
Thanks! I hope that it will work faster. If you have any links to articles describing such dark sides of SQLite or some techniques of using it such as this, I'll be very grateful if you write them here. Pavel Ivanov wrote: > Yes, you will be able to find information you need. You can store your > data like this: > > time | val_num | value | > |---|---| > [time_1] 1 [value_1.1] > [time_1] 2 [value_2.1] > ... > [time_1] [value_.1] > [time_2] 1 [value_1.2] > [time_2] 2 [value_2.2] > ... > [time_2] [value_.2] > [time_3] 1 [value_1.3] > [time_3] 2 [value_2.3] > ... > [time_3] [value_.3] > > Then your select will look like this: > > SELECT ... > FROM table_name t1, table_name t2, table_name t456, table_name t654 > WHERE t1.time > 1000 AND t1.time < 1500 > AND t1.time = t2.time > AND t1.time = t456.time > AND t1.time = t654.time > AND t1.val_num = 1 > AND t2.val_num = 2 > AND t456.val_num = 456 > AND t654.val_num = 654 > AND (t1.value > t2.value + 3 OR t456.value != t654.value) > > > It looks like it's more complicated but I believe it will work faster > than your multi-column approach. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] full outer join questions
> select coalesce(RT1.PID, RT2.PID) as PID, RT1.V1, RT2.V2 ... I find function ifnull() more readable in such cases. ;-) Pavel On Fri, Sep 18, 2009 at 7:21 PM, Igor Tandetnik wrote: > Stef Mientki wrote: >> create table RT1 ( PID integer, V1 text ); >> insert into RT1 values ( '684', 'aap' ); >> insert into RT1 values ( '685', 'other empty' ); >> create table RT2 ( PID integer, V2 text ); >> insert into RT2 values ( '684', 'beer' ); >> insert into RT2 values ( '686', 'other empty' ); >> select RT1.*, RT2.* >> from RT1 >> left join RT2 on RT1.PID = RT2.PID >> union >> select RT1.*, RT2.* >> from RT2 >> left join RT1 on RT1.PID = RT2.PID >> where RT1.PID IS NULL; >> >> Now I want to combine the columns PID, so the result would look like >> >> PID V1 V2 >> 686 from RT2 >> 684 from RT1 from RT2 >> 685 from RT1 > > select coalesce(RT1.PID, RT2.PID) as PID, RT1.V1, RT2.V2 ... > > Modifying the second select clause is left as an exercise for the > reader. > > Igor Tandetnik > > > > ___ > 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] blocking when locking
Wenbo, are you talking about what do you want to see in DBMS or are you trying to explain how SQLite works? If the latter then you're wrong. In SQLite 'read lock' is designed for transaction that _made_ any reads, 'write lock' - for transaction that _made_ any writes. Pavel On Sat, Sep 19, 2009 at 12:18 AM, Wenbo Zhao wrote: > This is not a good example i think. > If a transaction is intent to update after the select, it should start > a write lock before the select. > And as described in previous 'dead lock' example, the update in this > example could fail due to 'dead lock' > I believe the 'read lock' is designed for a 'read only' transaction, > and the 'write lock' is for a transaction that 'may write something'. > > 2009/9/19 Igor Tandetnik > >> Angus March wrote: >> > Yes, I see. So what is key to the problem is that someone tries to >> > change their read lock to a write lock. I guess I just thought that >> > the kernel that manages fcntl() would have a way of dealing with >> > this. Can this situation not be averted if at step 3, transaction A >> > releases its read lock before requesting a write lock? >> >> Then it wouldn't be much of a transaction, now would it? Imagine the >> classic example, where a transaction first verifies that the balance in >> a bank account is sufficient, then performs a withdrawal. If it >> relinquishes all locks between these two steps, then somebody else may >> record a withdrawal from that account, so that the write operation would >> then make the balance negative, thus violating an invariant. >> >> Of course, if that's what the application wants, it can simply perform >> the read and the write operations in two separate transactions. >> >> Igor Tandetnik >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > > Best Regards, > ZHAO, Wenbo > > === > ___ > 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] blocking when locking
On Fri, 18 Sep 2009 14:27:00 -0400, Angus March wrote: >Pavel Ivanov wrote: >>> Hell if I know why they use fcntl() for locks, and don't even give >>> you the option to block. >>> >> >> I think because they need to detect dead locks. BTW, I believe in case >> of dead lock even busy_handler will not be called, just SQLITE_BUSY is >> returned... >> >I guess that makes sense, in cases where multiple tables are involved. > >>>I think we are a long way from me screwing around with sqlite's >>> source. What I'm trying for is a solution with my own source code. >>> >> >> Then neither flock() nor fcntl() will not help you. Your own code have >> control only over the busy handler which gains control only when >> database is locked. And it should understand somehow when other >> process not calling any busy handlers unlocks database... And I >> believe there's no solution here any better than simple >> sleep-and-retry. >> > >How does this preclude me from coming up w/my own lock file with >POSIX locks? If a bunch of process start making incompatible requests on >a single lock file, then they'll be queued and processed in order. I think SQLite supports this with BEGIN EXCLUSIVE TRANSACTION; So, there is no need for an extra lock file. >I don't see how you can have a deadlock when you have multiple processes >putting locks on a single, entire file. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance in a case of big columns number
> Unfortunately I can't use such design because in this case I will not > able to find in the database such data as I need Yes, you will be able to find information you need. You can store your data like this: time | val_num | value | |---|---| [time_1] 1 [value_1.1] [time_1] 2 [value_2.1] ... [time_1] [value_.1] [time_2] 1 [value_1.2] [time_2] 2 [value_2.2] ... [time_2] [value_.2] [time_3] 1 [value_1.3] [time_3] 2 [value_2.3] ... [time_3] [value_.3] Then your select will look like this: SELECT ... FROM table_name t1, table_name t2, table_name t456, table_name t654 WHERE t1.time > 1000 AND t1.time < 1500 AND t1.time = t2.time AND t1.time = t456.time AND t1.time = t654.time AND t1.val_num = 1 AND t2.val_num = 2 AND t456.val_num = 456 AND t654.val_num = 654 AND (t1.value > t2.value + 3 OR t456.value != t654.value) It looks like it's more complicated but I believe it will work faster than your multi-column approach. Pavel On Sat, Sep 19, 2009 at 1:18 AM, Konishchev Dmitry wrote: >> It'll work, but SQLite does not use a balanced tree to store the >> columns for a particular record. So if you're seeking the 700th >> column of a particular row, it has to look through 699 others before >> it gets to it. Unless you always handle all the columns of a row >> together, it'll be slow. > Thanks for this information, it is helpful for me. > >> Because of speed, and the difficulty of correctly handling such a long >> INSERT line, it's usually better to break this down into properties. >> So instead of >> >> ID prop1 prop2 prop3 prop4 >> -- - - - - >> 1 rec1p1 rec1p2 rec1p3 rec1p4 >> 2 rec2p1 rec2p2 rec2p3 rec1p4 >> >> Do >> >> ID propNumber propValue >> -- -- - >> 1 1 rec1p1 >> 1 2 rec1p2 >> 1 3 rec1p3 >> 1 4 rec1p4 >> 2 1 rec2p1 >> 2 2 rec2p2 >> 2 3 rec2p3 >> 2 4 rec2p4 > Unfortunately I can't use such design because in this case I will not > able to find in the database such data as I need (I described problems > with which I faced in > http://www.mail-archive.com/sqlite-users@sqlite.org/msg46229.html). > ___ > 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] Most wanted features of SQLite ?
On 18.09.2009 21:56 CE(S)T, Simon Slavin wrote: > * Support for multiple concurrent clients/processes Doesn't that already work? You need common file system access, right, but then it should work afaik. What I'd like to see is foreign key integrity enforcement. You can already do it with triggers but it would be way easier if the system did that for me. -- Yves Goergen "LonelyPixel" Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello!
On Saturday 19 September 2009 02:32:03 Roger Binns wrote:
> If you are using the C api then use
> sqlite3_auto_extension - http://www.sqlite.org/c3ref/auto_extension.html -
> to register a callback that is called whenever a new db is opened.
For "autoload" extension we must call this function? It's very strange.
I think this code in openDatabase function is more useful for statically
linked extensions:
#ifdef SQLITE_ENABLE_RTREE
if( !db->mallocFailed && rc==SQLITE_OK){
rc = sqlite3RtreeInit(db);
}
#endif
More interesting may be loading extensions from database table but it's not
realised now, really?
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replace extra carriage returns?
> Select Replace(field, '\n','') from table but it doesn't return the > data unchanged at all. If I take the \n out of single quotes I just > get an error. What made you think that SQLite will understand C-style escape-sequences? It doesn't do that. If you're running this query from sqlite3 command line utility then try approach that Donald showed you (though maybe you'll need to add casts to text for both constants). If you're running this query from you application then things are a whole lot easier - just put into quotes real CR/LF codes using escaping that your language supports. Pavel On Thu, Sep 17, 2009 at 11:43 AM, Matt Williamson wrote: > I'm trying to clean out a SQLite table that has a text field with > multiple carriage returns. I can't figure out how to represent a > carriage return in a replace function.This is on a Windows system. > I've tried > > Select Replace(field, '\n','') from table but it doesn't return the > data unchanged at all. If I take the \n out of single quotes I just > get an error. > > TIA > > Matt > ___ > 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] Most wanted features of SQLite ?
On Sat, Sep 19, 2009 at 9:21 AM, John Stanton wrote: > Alexey Pechnikov wrote: >> Hello! >> >> On Saturday 19 September 2009 00:43:18 Noah Hart wrote: >> >>> Stored Procedures >>> >> >> There are Tiny C compiler extension and realization of >> stored procedures for SQLite 2 and Lua extension and other. >> So you can use one or all of these. >> >> Best regards, Alexey Pechnikov. >> http://pechnikov.tel/ >> > There is a PL/SQL implementation available and we use Javascript as a > stored procedure capability in Sqlite. It integrates nicely with WWW > applications. well, can you share the above with the rest of the community? -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 02:17:39 Subsk79 wrote: > StepSqlite brings powerful Stored Procedure support with full power of > PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere > 'wrapper' so it generates much more efficient code than any wrapper > could ever achieve - for instance, it pre-compiles all SQL in your > code right when the lib is loaded - no compile-overload at runtime - > this is exactly what one expects from a true 'Stored' Procedure. Do you have support for compiled extension stored into database table? It's not good way to distribute external libs. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 18:21:22 John Stanton wrote: > There is a PL/SQL implementation available and we use Javascript as a > stored procedure capability in Sqlite. It integrates nicely with WWW > applications. I don't know this. Can you show link to docs and examples? Thx. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding ICU collations: Where needed?
Lukas Haase wrote: > When I use the ICU module and I create a collation, where is the > collation "stored"? The collation name is part of the database schema. The actual comparison algorithm is not represented in the database in any way: your application must ensure that all clients install compatible collations behind that name. > Do I have to call icu_create_collation everytime I > start up the database? Every time you open a connection, yes. > Or just one time? Or only when inserting data? > What happens if I omit the call? When you try to prepare a statement that needs the collation, you'll get an error "unknown collation". > This leads to the second question: If I only insert data on the Linux > side and I have an index on the column using the ICU collation, do I > need to have ICU support on client side? Yes, if you run SELECT statements that perform comparisons or sort on columns associated with this collation. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Alexey Pechnikov wrote: > Hello! > > On Saturday 19 September 2009 00:43:18 Noah Hart wrote: > >> Stored Procedures >> > > There are Tiny C compiler extension and realization of > stored procedures for SQLite 2 and Lua extension and other. > So you can use one or all of these. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > There is a PL/SQL implementation available and we use Javascript as a stored procedure capability in Sqlite. It integrates nicely with WWW applications. > ___ > 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] replace extra carriage returns?
I'm trying to clean out a SQLite table that has a text field with multiple carriage returns. I can't figure out how to represent a carriage return in a replace function.This is on a Windows system. I've tried Select Replace(field, '\n','') from table but it doesn't return the data unchanged at all. If I take the \n out of single quotes I just get an error. TIA Matt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance in a case of big columns number
Please sorry for my terrible Engilsh. :) Thanks for the answer. Yes, I know that it is bad design in the common case. But I have to use it because I have data which has following format: time | value_1 | value_2|value_ |---|-| [time_1] [value_1.1] [value_2.1] ... [value_.1] [time_2] [value_1.2] [value_2.2] ... [value_.2] [time_3] [value_1.3] [value_2.3] ... [value_.3] ... And I must filter it by comparing some values, for example, as this: SELECT FROM table_name WHERE time > 1000 AND time < 1500 AND value_1 > value_2 + 3 OR value_456 != value_654 I asked this question because SQLite may use such algorithms which are very slow with very big number of colums and may be in this case it will work rapidly if, for example, I will combine values in such manner: time | value_1_to_value_100 | value_101_to_value_200 | ... |-|| [time_1] [value_1_to_value_100.1] [value_101_to_value_200.1] ... [time_2] [value_1_to_value_100.2] [value_101_to_value_200.2] ... [time_3] [value_1_to_value_100.3] [value_101_to_value_200.3] ... And I will write some functions for extracting values from them and will filter data by following query: SELECT FROM table_name WHERE time > 1000 AND time < 1500 AND get_value(value_1_to_value_100, 1) > get_value(value_1_to_value_100, 2) + 3 OR get_value(value_400_to_value_500, 56) != get_value(value_600_to_value_700, 54) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Understanding ICU collations: Where needed?
Hi, I have a database which is built on a Linux System with PHP and pdo_sqlite and used (read-only) on a Windows platform. When I use the ICU module and I create a collation, where is the collation "stored"? Do I have to call icu_create_collation everytime I start up the database? Or just one time? Or only when inserting data? What happens if I omit the call? This leads to the second question: If I only insert data on the Linux side and I have an index on the column using the ICU collation, do I need to have ICU support on client side? Thank you, Luke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Force the use of a specified index?
I tried that and it picked up the index, but the query was slower plus adding the compound index took some time as well, so the other way is better. RBS On Sat, Sep 19, 2009 at 6:12 AM, Jon Dixon wrote: > Out of curiosity, would it work any faster to switch the date clause to be > T1.ADDED_DATE > DATE(T2.ADDED_DATE,'-15 month') > and used an index (PATIENT_ID,ADDED_DATE)? > > Jon > > You said: > > OK, it looks the construction with DATE instead of Julianday is a bit > faster, so best option here seems to be: > > DELETE FROM TABLE1 WHERE > ENTRY_ID NOT IN ( > SELECT T1.ENTRY_ID FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON > (T1.PATIENT_ID = T2.PATIENT_ID) > WHERE > DATE(T1.ADDED_DATE, '+15 month') > T2.ADDED_DATE) > > > From: Bart Smissaert > To: Jon Dixon > Sent: Friday, September 18, 2009 6:38:34 PM > Subject: Re: [sqlite] Force the use of a specified index? > > Had at this suggestion now and it works and uses the PATIENT_ID, > ADDED_DATE index, but it is as slow as my delete with Julianday. It > looks Pavel's suggestion is the way to do this. Just will have a look > now and see if doing the construction with DATE( instead of Julianday > is any faster. > > RBS > > > On Fri, Sep 18, 2009 at 10:48 PM, Jon Dixon wrote: >> From: >> "Bart Smissaert" >> Then the SQL I was trying to improve: >> >> DELETE >> FROM >> TABLE2 >> WHERE >> PATIENT_ID NOT IN ( >> SELECT >> TABLE1.PATIENT_ID >> FROM >> TABLE1 >> WHERE >> JULIANDAY(TABLE2.START_DATE, '-14 month') > >> JULIANDAY(TABLE1.START_DATE) AND >> TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) >> >> - >> >> To my understanding, SQLite will not use indices on function results. I >> wonder if you would have more luck (since START_DATE is -mm-dd) using >> >> DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( >> SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE TABLE1.START_DATE < >> DATE(TABLE2.START_DATE,'-14 month') AND TABLE1.PATIENT_ID = >> TABLE2.PATIENT_ID >> ) >> >> I believe this will make use of a joint index on PATIENT_ID and >> START_DATE, >> with the preferred order depending on which is the more restrictive term >> (I'd guess best would be and index on (PATIENT_ID, START_DATE)). >> >> Jon Dixon >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to create operator "IS Empty" similar to "IS NULL"
Hello!
Please see ticket
http://www.sqlite.org/src/info/15e11a2c87
Function IFEmpty may be simple:
static void ifemptyFunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
int i;
for(i=0; ihttp://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
