Re: [sqlite] UPDATE / locking woes
Joe Stump <[EMAIL PROTECTED]> wrote: > I've read through the docs and I'm completely perplexed as to what's > going on here. I've got an install of Trac (which uses sqlite3). I > can query the database without issue using PHP's PDO extension. > However, when I go to *update* any records my PHP script takes > upwards of 2 minutes to run, returns true, but doesn't modify any data. > > Here's a snippet of my code: > > $sql = "UPDATE ticket_custom > SET value = ? > WHERE ticket = ? AND >name = ?"; > $stmt = $db->prepare($sql); > $stmt->bindParam(1, $i, PDO::PARAM_STR); > $stmt->bindParam(2, $row['ticket'], PDO::PARAM_INT); > $stmt->bindParam(3, $pField, PDO::PARAM_STR); > $result = $stmt->execute(); > > $result is true according to my var_dump() ... The odd part is that > the query fails to update the data from the command line as well - if > I can run it, which is rare since 90% of the time it says the > database is locked (not likely since I'm the only one using it). > I don't know much about Trac. Can you show us the schema? In particular, it would really be nice to know what indices are on the ticket_custom table. Though I don't know anything about Trac internals, I get the impression from what I've seen posted in various newsgroups that it hammers on the database pretty hard. People keep complaining about concurrency issues when using SQLite and switch to PostgreSQL. CVSTrac also uses SQLite and the instance of CVSTrac running on the www.sqlite.org website takes 80K hits/day running on a 1/16th slice of a server and seems to do just fine, so it is not at all clear to me why Trac is having such problems. I also hear (and this is just a rumor mind you) that Trac takes several seconds to render a timeline versus milliseconds for CVSTrac. I can only conclude that Trac must be doing a whole lot more behind the scenes than CVSTrac does. Trac certainly have better eye candy - I guess that doesn't come for free... I also do not understand why the database is locked. Are you sure you don't have a hung Trac process doing something to the database in the background? You're running on a local filesystem and not on an NFS mount, right? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UPDATE / locking woes
I've read through the docs and I'm completely perplexed as to what's going on here. I've got an install of Trac (which uses sqlite3). I can query the database without issue using PHP's PDO extension. However, when I go to *update* any records my PHP script takes upwards of 2 minutes to run, returns true, but doesn't modify any data. Here's a snippet of my code: $sql = "UPDATE ticket_custom SET value = ? WHERE ticket = ? AND name = ?"; $stmt = $db->prepare($sql); $stmt->bindParam(1, $i, PDO::PARAM_STR); $stmt->bindParam(2, $row['ticket'], PDO::PARAM_INT); $stmt->bindParam(3, $pField, PDO::PARAM_STR); $result = $stmt->execute(); $result is true according to my var_dump() ... The odd part is that the query fails to update the data from the command line as well - if I can run it, which is rare since 90% of the time it says the database is locked (not likely since I'm the only one using it). Ideas? --Joe - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can't get results from PRAGMA
Hi, Hakki Dogusan wrote: Hi, (I'm cc'ing to you) Paul Simpson wrote: Hi, (I hope we are not getting off-topic) We seem to be, sorry everyone! I trued e-mailing you directly - I hope you don't mind, but your mail server refused the mail! But I can get messages, as you can see :) I'll prepare a sample Code::Blocks project using SQLite, wx, wxSQLite3. I'll send a message here and your address when ready. [snip] I've put a zip file: http://www.dogusan.net/dogusanh/download/cbwxMinimal.zip (58kb) File contains: - Code::Blocks project - wx minimal sample (modified for creating, populating, querying code for db) - wxSQLite3 source - sqlite3.dll import lib Requirements: - Mingw - wx - Code::Blocks - sqlite3.dll Hope it helps... -- Regards, Hakki Dogusan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Appropriate uses for SQLite
I'm late too, but here you have my opinion At 02:03 05/02/2007, you wrote: FWIW I don't interpret any posts on this thread as an attempt to change SQLite, either. But there seems to be some who see value in more clearly defining *when* SQLite *does* work. I guess that there is a lot of enthusiasm for SQLite's ability and performance and it's nice to be able to prove that SQLite does actually compete in areas it was not even designed for. Perhaps an argument for less complex design as a generic software design strategy. Well, I am a powerpc developer and i really love Risc architecture, not only on hardware, in software too. SQLite is a true Risc application, it's small, easy to use, fast for the things it do, and a lot of things it can't do they can be done using the fast existing ones(yes, there are things it can't do). Also it's structure (SQL Parser + VDBE + Pager/Btree) has low complexity and completly separate and independent so adding important features to one (adding the last SQL standard, change file format, f.e.) only means changes in one and they can run at different speeds. Perhaps it's a bit hard to understand, but if you know how Risc work i hope you understand me. The principal problem i see is "How can i do that without **whatever** feature i had on another RDBMS?" It's in fact the same problem CISC guys have when doing the switch to RISC. The answer usually is "Make it simpler". For example, when someone asks, "I have a database with a superbig table, how can i speed it up?" answer, "Simplify the problem using more tables with simple designs, aka database normalization."; or when other asks "I have a huge database with 30 tables but can't access for write when already writing to it" answer, "Simplify the problem using, where apropiate, 1 table in 1 database, so you can access 2 tables/databases for write simultaneously". Resuming, using SQLite needs a change in thinking/designing/using databases and going back to the first question, generally reading a SQL book for refreshing knowledge or redesigning the query is enough. P.S. Don't know, but is VDBE threaded? Or in other words, Has VDBE state machine/processor, not C code of VDBE, threads/fibers/parallels or can run 2 different querys vdbe code simultanely? - Useful Acronymous : FAQ = Frequently 'Answered' Questions - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] OR in virtual tables
Hi All, I'm playing with virtual tables and found that when i do SELECT * FROM vtable WHERE x = 'a' OR x = 'b' xBestIndex is called without constraints. Is there a way to circumvent this? I dont want sqlite to traverse all the rows just because of the OR statement. The version I'm using is 3.3.13. Using info->estimatedCost = 10; does not trigger additional calls to xBestIndex. Cheers, Jos - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SELECT DISTINCT but ignore one column?
I came across this problem a while ago and not sure now why I couldn't do that. Maybe I needed to keep the column, but then I suppose I could do: select distinct col1, null as col2, coll3 from table Maybe I needed the max or min from the ignore column and that works indeed fine with SR Neff's suggestion. RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 15 February 2007 23:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] SELECT DISTINCT but ignore one column? RB Smissaert wrote: > It sometimes would be very useful if you could do a SELECT DISTINCT, but > ignoring the data in one (or maybe more) particular column. > So for example > > col1 col2 col3 > - > ABC > ADC > > Then doing SELECT DISTINCT (IGNORE col2) * from table > would produce: > > ABC > > It wouldn't matter for me if it produced the above or > ADC > > But there could be rules/logic to that. > > Is this possible in SQLite or would it be possible to add this as a new > option? > > > > Why can't you simply do this? select distinct col1, col3 from table; If you don't care about the value returned for col2, why bother returning anything? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT DISTINCT but ignore one column?
RB Smissaert wrote: It sometimes would be very useful if you could do a SELECT DISTINCT, but ignoring the data in one (or maybe more) particular column. So for example col1 col2 col3 - ABC ADC Then doing SELECT DISTINCT (IGNORE col2) * from table would produce: ABC It wouldn't matter for me if it produced the above or ADC But there could be rules/logic to that. Is this possible in SQLite or would it be possible to add this as a new option? Why can't you simply do this? select distinct col1, col3 from table; If you don't care about the value returned for col2, why bother returning anything? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite-3.3.13
[EMAIL PROTECTED] wrote: Martin Jenkins <[EMAIL PROTECTED]> wrote: Raised as ticket http://www.sqlite.org/cvstrac/tktview?tn=2232 Thanks. Bug reports are always welcomed. But this problem was fixed yesterday. When you see problems in SQLite, especially problems that have been discussed on this mailing list, it is useful to visit the timeline to see if they have been fixed already. Noted, my bad. There weren't any comments on the list so I assumed I was talking to myself. Warnock applies. ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SELECT DISTINCT but ignore one column?
Thanks for the tip, will try that. RBS -Original Message- From: Samuel R. Neff [mailto:[EMAIL PROTECTED] Sent: 15 February 2007 22:27 To: sqlite-users@sqlite.org Subject: RE: [sqlite] SELECT DISTINCT but ignore one column? You could do this with a group by and use a min or max aggregate function on the "ignored" column HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Thursday, February 15, 2007 4:37 PM To: sqlite-users@sqlite.org Subject: [sqlite] SELECT DISTINCT but ignore one column? It sometimes would be very useful if you could do a SELECT DISTINCT, but ignoring the data in one (or maybe more) particular column. So for example col1 col2 col3 - ABC ADC Then doing SELECT DISTINCT (IGNORE col2) * from table would produce: ABC It wouldn't matter for me if it produced the above or ADC But there could be rules/logic to that. Is this possible in SQLite or would it be possible to add this as a new option? RBS - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SELECT DISTINCT but ignore one column?
You could do this with a group by and use a min or max aggregate function on the "ignored" column HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Thursday, February 15, 2007 4:37 PM To: sqlite-users@sqlite.org Subject: [sqlite] SELECT DISTINCT but ignore one column? It sometimes would be very useful if you could do a SELECT DISTINCT, but ignoring the data in one (or maybe more) particular column. So for example col1 col2 col3 - ABC ADC Then doing SELECT DISTINCT (IGNORE col2) * from table would produce: ABC It wouldn't matter for me if it produced the above or ADC But there could be rules/logic to that. Is this possible in SQLite or would it be possible to add this as a new option? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SELECT DISTINCT but ignore one column?
It sometimes would be very useful if you could do a SELECT DISTINCT, but ignoring the data in one (or maybe more) particular column. So for example col1 col2 col3 - ABC ADC Then doing SELECT DISTINCT (IGNORE col2) * from table would produce: ABC It wouldn't matter for me if it produced the above or ADC But there could be rules/logic to that. Is this possible in SQLite or would it be possible to add this as a new option? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ORDER BY not providing the expected result
I wrote, D. Richard Hipp wrote, To: Sent: Thursday, February 15, 2007 3:41 PM Subject: Re: [sqlite] ORDER BY not providing the expected result "jose isaias cabrera" <[EMAIL PROTECTED]> wrote: Greetings! I have this schema, CREATE TABLE LSOpenJobs ( id integer primary key, ProjID, parent, children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, vEmail, invoice, ProjFund, A_No, wDir, notes, status ); When I do a, SELECT * FROM LSOpenJobs WHERE status='o' ORDER BY ProjID, PSubClass, parent, bdate, ddate, edate; the ORDERing, or sorting, is done alphabetically and not numerically. Is there a possibility to get this ORDER BY done numberically? ProjID is an integer, and so I end up with this, The easiest way to do this is to say "ProjID INTEGER" instead of just "ProjID" in your CREATE TABLE statement. Another approach is to say "CAST(ProjId AS INTEGER)" instead of just "ProjID" in the ORDER BY clause. But this second approach will silently convert non-integer strings into zero, which might not be exactly what you want to do with them. Never even thought about defining it as an integer. The second one would also work, since there is a function that acts upon ProjID having a value of 0, which will make that scenario safe for the data in db. Worked like a champ! Woowoowoowoowooo! thanks again. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ORDER BY not providing the expected result
D. Richard Hipp wrote, To: Sent: Thursday, February 15, 2007 3:41 PM Subject: Re: [sqlite] ORDER BY not providing the expected result "jose isaias cabrera" <[EMAIL PROTECTED]> wrote: Greetings! I have this schema, CREATE TABLE LSOpenJobs ( id integer primary key, ProjID, parent, children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, vEmail, invoice, ProjFund, A_No, wDir, notes, status ); When I do a, SELECT * FROM LSOpenJobs WHERE status='o' ORDER BY ProjID, PSubClass, parent, bdate, ddate, edate; the ORDERing, or sorting, is done alphabetically and not numerically. Is there a possibility to get this ORDER BY done numberically? ProjID is an integer, and so I end up with this, The easiest way to do this is to say "ProjID INTEGER" instead of just "ProjID" in your CREATE TABLE statement. Another approach is to say "CAST(ProjId AS INTEGER)" instead of just "ProjID" in the ORDER BY clause. But this second approach will silently convert non-integer strings into zero, which might not be exactly what you want to do with them. Never even thought about defining it as an integer. The second one would also work, since there is a function that acts upon ProjID having a value of 0, which will make that scenario safe for the data in db. thanks, josé - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ORDER BY not providing the expected result
"jose isaias cabrera" <[EMAIL PROTECTED]> wrote: > Greetings! > > I have this schema, > > CREATE TABLE LSOpenJobs > ( > id integer primary key, ProjID, parent, children, login, cust, > proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, > vEmail, invoice, ProjFund, A_No, wDir, notes, status > ); > > When I do a, > > SELECT * FROM LSOpenJobs WHERE status='o' ORDER BY ProjID, PSubClass, > parent, bdate, ddate, edate; > > the ORDERing, or sorting, is done alphabetically and not numerically. Is > there a possibility to get this ORDER BY done numberically? ProjID is an > integer, and so I end up with this, > The easiest way to do this is to say "ProjID INTEGER" instead of just "ProjID" in your CREATE TABLE statement. Another approach is to say "CAST(ProjId AS INTEGER)" instead of just "ProjID" in the ORDER BY clause. But this second approach will silently convert non-integer strings into zero, which might not be exactly what you want to do with them. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ORDER BY not providing the expected result
Greetings! I have this schema, CREATE TABLE LSOpenJobs ( id integer primary key, ProjID, parent, children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, vEmail, invoice, ProjFund, A_No, wDir, notes, status ); When I do a, SELECT * FROM LSOpenJobs WHERE status='o' ORDER BY ProjID, PSubClass, parent, bdate, ddate, edate; the ORDERing, or sorting, is done alphabetically and not numerically. Is there a possibility to get this ORDER BY done numberically? ProjID is an integer, and so I end up with this, ProjID, cust, proj,..., status 100, ... 100, ... 100, ... 114, ... 114, ... 114, ... 114, ... 114, ... 116, ... 116, ... 98, ... 98, ... 98, ... 63, ... 63, ... 63, ... etc. What I would like is to have 63 63 98 98 100 100 114 114 116 116 Is this possible without me having to sort it out? I know I could probably add 0's to the entry, and so 00063... 00116, but I want sqlite to provide this to me. :-) thanks, josé - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Appropriate uses for SQLite
Thank you for your thorough replies to my supplementary remarks on this topic. It seems to me clear now that it is best not to take a careful disclaimer etc (on site networking support) as typically/generally indicating a limitation on usefulness of sqlite. Rather it is a matter of taking care & testing the system carefully when multiple writes may be an issue due to bugs in the file system locking. Moreover, like sqlite, other databases are known to work well relying on the system file locking. David --- David M X Green |||"Mike Owens" (2007-02-12 15:13) wrote: |||>>> Hey, sorry I'm a little late on this one (as usual). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Triggers+callbacks = GUI?
Wesley wrote: As an example: in an instant messenger a new text message arrives. The application puts it into the message log. The chat window automatically updates with the new text. The statistics window about total # of messages updates. Possibly other things happen. The point is I don't want to have to think about the 'possible other things'. If the program gets fat, I'll screw this up. Sam wrote: Personally I think a database should provide long-term storage for your application's state, not drive the application's UI. If you need to update views based on state then that state should be in memory with mechanisms to easily detect changes. True. http://en.wikipedia.org/wiki/Model-view-controller A change to the Model would raise events that the View can react to. Hope this helps, - Trey - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Triggers+callbacks = GUI?
On Feb 15, 2007, at 4:01 PM, [EMAIL PROTECTED] wrote: "Wesley W. Terpstra" <[EMAIL PROTECTED]> wrote: The approach I'd much prefer is to register a trigger to update the GUI... Note that the trigger runs on the client-side of the process that makes the change - not on the client side of the process that is implementing the GUI. Yes, I'm aware of this limitation. There will only be one database user: the application itself. You might implement something like [UndoRedo] then have your GUI process poll only changes table. The changes table will normally be empty, except after some other process makes a change. So when the changes table is non-empty, that is the GUI process's cue to redraw its screen based on the latest database content. You are assuming I intend to track changes from another application. I do not. I already know when a change happens; I made it. So, I don't need a log to tell me when to refresh. My goals are: 1. simplify reasoning about the application (this program will be bloated and grotesque in short order, so I want to be responsible for as little cross-component interaction as possible) 2. avoid the cost of refreshing big tree widgets with thousands of entries (polling doesn't give me a 'diff') You suggestion helps with #2, but so would putting triggers on the base tables as Michael suggested. His suggestion doesn't completely address concern #1, but it is certainly a step in the right direction (it's not too hard to reason about which base tables can affect the view if you have the query). As an example: in an instant messenger a new text message arrives. The application puts it into the message log. The chat window automatically updates with the new text. The statistics window about total # of messages updates. Possibly other things happen. The point is I don't want to have to think about the 'possible other things'. If the program gets fat, I'll screw this up. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Triggers+callbacks = GUI?
Personally I think a database should provide long-term storage for your application's state, not drive the application's UI. If you need to update views based on state then that state should be in memory with mechanisms to easily detect changes. If you're too far along and need to use the database then you can at least partially handle updates outside the db. If you funnel all of your database calls through a central application interface then that can broadcast events to the rest of your app when something changes. Even if it's not detailed and watches only for insert/update/delete SQL and broadcasts a "something changed" event then you can query the db to see what changed and it'd be more efficient than constantly polling. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Wesley W. Terpstra [mailto:[EMAIL PROTECTED] Sent: Thursday, February 15, 2007 9:33 AM To: sqlite-users@sqlite.org Subject: [sqlite] Triggers+callbacks = GUI? I intend to write a GUI application backed by SQL. Several of the windows display status that would best be represented as a database view. What I've been thinking about is how to update the GUI when the view changes. First the obvious approach: polling. Every X seconds re-execute the query and redraw the GUI. Certainly this will work, and I might still do this. For windows with very large state, however, this is not very desirable. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Triggers+callbacks = GUI?
On Feb 15, 2007, at 3:46 PM, Michael Schlenker wrote: Wesley W. Terpstra schrieb: I intend to write a GUI application backed by SQL. Several of the windows display status that would best be represented as a database view. What I've been thinking about is how to update the GUI when the view changes. [snip] Thus, a window simply provides the VIEW definition and insert/ update/delete callbacks. Some support code creates the view and insert()s the current contents. Then it hooks the triggers invoking the methods to catch future updates. On widget death, the view and triggers are dropped. Triggers only react on update/delete/insert operations, which are not generally available for a arbitrary view. Databases that implement immediate materialized views propagate the changes from the base tables to the view. I would have expected an update/delete/insert trigger on a view to trigger under the same criteria as when a materialized view would be updated to reflect changes in the base tables. I'm positive that before/after triggers work on a materialized view in this way under Oracle. But if you slightly change your api, to provide a view definition and a list of tables/columns to watch you can do it, just create the triggers on the tables not on the view and take the appropriate action in your callbacks. You mean to basically create the hooks manually that would've been created for a materialized view? This is certainly possible, just error prone. I wonder how hard it would be to automatically translate a trigger on a view into triggers over the base tables. I was hoping for something of this nature to relieve me of responsibility for thinking of all the ways base tables could change the view. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Triggers+callbacks = GUI?
"Wesley W. Terpstra" <[EMAIL PROTECTED]> wrote: > > The approach I'd much prefer is to register a trigger to update the > GUI... > Note that the trigger runs on the client-side of the process that makes the change - not on the client side of the process that is implementing the GUI. Nevertheless, your technique is useful for doing things like implementing Undo/Redo. See, for example, http://www.sqlite.org/cvstrac/wiki?p=UndoRedo You might implement something like this then have your GUI process poll only changes table. The changes table will normally be empty, except after some other process makes a change. So when the changes table is non-empty, that is the GUI process's cue to redraw its screen based on the latest database content. The details are left as an exercise to the reader... -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Triggers+callbacks = GUI?
Wesley W. Terpstra schrieb: I intend to write a GUI application backed by SQL. Several of the windows display status that would best be represented as a database view. What I've been thinking about is how to update the GUI when the view changes. [snip] Thus, a window simply provides the VIEW definition and insert/update/delete callbacks. Some support code creates the view and insert()s the current contents. Then it hooks the triggers invoking the methods to catch future updates. On widget death, the view and triggers are dropped. Triggers only react on update/delete/insert operations, which are not generally available for a arbitrary view. But if you slightly change your api, to provide a view definition and a list of tables/columns to watch you can do it, just create the triggers on the tables not on the view and take the appropriate action in your callbacks. Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Triggers+callbacks = GUI?
I intend to write a GUI application backed by SQL. Several of the
windows display status that would best be represented as a database
view. What I've been thinking about is how to update the GUI when the
view changes.
First the obvious approach: polling. Every X seconds re-execute the
query and redraw the GUI. Certainly this will work, and I might still
do this. For windows with very large state, however, this is not very
desirable.
The approach I'd much prefer is to register a trigger to update the
GUI as follows:
create temp view StatusWidget as select some query ...;
create temp trigger StatusWidgetUpdate after update on StatusWidget
for each row
begin
select statusWidgetUpdateFn(OLD.key, NEW.key, NEW.value1,
NEW.value2, ...);
end;
ditto for Add/Delete
Then I create custom functions 'statusWidget{Add,Update,Delete}Fn'
that take the values and update the GUI.
Thus, a window simply provides the VIEW definition and insert/update/
delete callbacks. Some support code creates the view and insert()s
the current contents. Then it hooks the triggers invoking the methods
to catch future updates. On widget death, the view and triggers are
dropped.
It seems to me this would be a very bug-free way to design even
complicated applications. Whenever you create a window, back its
state with SQLite. Actions taken by the network or the user simply
modify state in the database. This in turn updates all the relevant
GUI windows automatically. You don't need to track the changes; you
let the database do it. This is only possible since callback
functions can be bound to triggers. AFAIK, no other database can do
this, since the triggers exist on the server-side.
However, the problem I'm running into is that I can't create before/
after triggers on a view:
SQL error: cannot create BEFORE trigger on view: main.popup
SQL error: cannot create AFTER trigger on view: main.popup
This isn't documented as an unimplemented SQL feature, so is this a bug?
If SQLite doesn't support triggers on views, does anyone know of a
database which does AND allows triggers to invoke a client-side
callback? Is supporting triggers on views planned for the future? Can
I help?
Thanks.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Can't get results from PRAGMA
Hi, (I'm cc'ing to you) Paul Simpson wrote: Hi, (I hope we are not getting off-topic) We seem to be, sorry everyone! I trued e-mailing you directly - I hope you don't mind, but your mail server refused the mail! But I can get messages, as you can see :) I'll prepare a sample Code::Blocks project using SQLite, wx, wxSQLite3. I'll send a message here and your address when ready. [snip] Meanwhile you may want to investigate my BookWorm application - Written in Lua using wxLua, SQLite. ::) -- Regards, Hakki Dogusan http://www.dynaset.org/dogusanh http://www.dogusan.net/dogusanh - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite-3.3.13
Martin Jenkins <[EMAIL PROTECTED]> wrote: > Raised as ticket http://www.sqlite.org/cvstrac/tktview?tn=2232 > Thanks. Bug reports are always welcomed. But this problem was fixed yesterday. When you see problems in SQLite, especially problems that have been discussed on this mailing list, it is useful to visit the timeline to see if they have been fixed already. http://www.sqlite.org/cvstrac/timeline The items with the blue dots beside them are check-ins. I try to put descriptive comments on each check-in to make it clear what has changed. If you want more information, you can click on the link (the [3645] in this case) to see more details, including complete diffs. The timeline is a very useful tool to see what has been going on with the SQLite source code. If you are not already familiar with the timeline, please let me call it to your attention. Please do not interpret this message as criticism for opening a redundant ticket - I don't mind that. I am just using this opportunity to point out the utility of the timeline to the many viewers of this list (there are over 1200 subscribers) who might not be aware of its usefulness. I also want to point out that while I do not frequently respond to reports of issues on the mailing list, I do in fact read them all and sometimes actually act upon them. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite-3.3.13
Raised as ticket http://www.sqlite.org/cvstrac/tktview?tn=2232 Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Storing contacts list for each user
Hi Paul, Thanks for the detailed reply... The app would be on these lines, download the phone book for several users onto the linux PC and give access to their downloaded phone books for reading at later time, each user should not see other user contacts list. Thanks, Pavan. On 2/15/07, Paul Simpson <[EMAIL PROTECTED]> wrote: > /home/user/user1/user1.db [User1 will have table to hold contacts list] > /home/user/user2/user2.db [User2 will have table to hold contacts list] > . > . > . > /home/user/userN/userN.db [UserN will have table to hold contacts list] This would imply that you are writing an application where each user has their own file structure (like a word processor would, for example). i.e. the user logs in and runs the app, accessing their own files. If this is the case, then yes, what you are suggesting is correct since the various contact lists are isolated entities. If, however, the users share the app and there is one data file, then the normalization argument kicks in. In this case you would have two, or possibly three tables. The first would contain all your user's details (including, if appropriate, security information to allow the application to authenticate them) One column would be a unique ID number, known as the primary key. A second table would contain the contact details. One column of that would be the id of the "owning" user (known as a foreign key). So when your application wants to list a user's contacts it searches for only those contacts that are "owned" by the user (i.e. the contacts whose foreign key is the user's primary key). It gets better! If, say, two users have the same contact then there will be two entries in the contacts table and therefore two entries to keep up to date. Let's say you want to be able to share contacts. Now what you do is to have a primary key on both the users and contacts tables (I always have a primary key on every table anyway) and a third table which contains two columns of foreign keys, one for the user and one for the contact. Now, each user can have multiple contacts and each contact can have multiple users. In this case, I would probably include information in the relationships table to control which user can edit the contact, but the point is if, say, I had you in my contacts list and I shared that contact with my wife, if your details changed and I edited them, my wife's version would change too. That, in a nutshell, is normalization. I hope that helps, but like I said at the start, it depends how you see your app working. Your original question implied that you would have 1 file and many tables in it (1 for each user) which would be wrong (well, inefficient!) I hope that helps. -- Paul - To unsubscribe, send email to [EMAIL PROTECTED] - -- ' Always finish stronger than you start *
Re: [sqlite] Difference between sqlite and sqlite3
Dan Kennedy wrote: On Thu, 2007-02-15 at 11:49 +0100, Pavan wrote: Can anyone tell me what is the difference between sqlite and sqlite3. Also: "SQLite - an embedded database library" "sqlite3 - a shell to allow command line access to SQLite" Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Storing contacts list for each user
Pavan wrote: The idea is that at any point of time user1 should not access user2 details and vice-versa. SQLite doesn't have a concept of access control so if you want to stop user1 from seeing user2's data then you'll either have to use a database that does provide access control or , as you suggest, provide a separate file for each user and control access via OS permissions. In that case you needn't worry too much about normalising the data and you might even find that an SQL database is overkill. Does it make sense conceptually and is it feasible technically ? Only you can answer the former ;) If you need that access control and want to use SQLite then I guess separate files is as good as you'll get. You could provide your own access control mechanism to a single file but that'll be more work. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Storing contacts list for each user
> /home/user/user1/user1.db [User1 will have table to hold contacts list] > /home/user/user2/user2.db [User2 will have table to hold contacts list] > . > . > . > /home/user/userN/userN.db [UserN will have table to hold contacts list] This would imply that you are writing an application where each user has their own file structure (like a word processor would, for example). i.e. the user logs in and runs the app, accessing their own files. If this is the case, then yes, what you are suggesting is correct since the various contact lists are isolated entities. If, however, the users share the app and there is one data file, then the normalization argument kicks in. In this case you would have two, or possibly three tables. The first would contain all your user's details (including, if appropriate, security information to allow the application to authenticate them) One column would be a unique ID number, known as the primary key. A second table would contain the contact details. One column of that would be the id of the "owning" user (known as a foreign key). So when your application wants to list a user's contacts it searches for only those contacts that are "owned" by the user (i.e. the contacts whose foreign key is the user's primary key). It gets better! If, say, two users have the same contact then there will be two entries in the contacts table and therefore two entries to keep up to date. Let's say you want to be able to share contacts. Now what you do is to have a primary key on both the users and contacts tables (I always have a primary key on every table anyway) and a third table which contains two columns of foreign keys, one for the user and one for the contact. Now, each user can have multiple contacts and each contact can have multiple users. In this case, I would probably include information in the relationships table to control which user can edit the contact, but the point is if, say, I had you in my contacts list and I shared that contact with my wife, if your details changed and I edited them, my wife's version would change too. That, in a nutshell, is normalization. I hope that helps, but like I said at the start, it depends how you see your app working. Your original question implied that you would have 1 file and many tables in it (1 for each user) which would be wrong (well, inefficient!) I hope that helps. -- Paul - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference between sqlite and sqlite3
On Thu, 2007-02-15 at 11:49 +0100, Pavan wrote: > Hi, > > Can anyone tell me what is the difference between sqlite and sqlite3. By itself, "sqlite" probably means SQLite version 2. Same concept as version 3 - a client library to access an SQL database stored in a single file -, but a different file format and API. Version 2 is no longer actively developed. Use version 3 for new code if at all possible. > Thanks, > Pavan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Storing contacts list for each user
Hi Martin, Thanks for the quick replyI have gone throu the link and it gives good insight of data managment from tables perspective... But, from users point of view can it be something like this /home/user/user1/user1.db [User1 will have table to hold contacts list] /home/user/user2/user2.db [User2 will have table to hold contacts list] . . . /home/user/userN/userN.db [UserN will have table to hold contacts list] The idea is that at any point of time user1 should not access user2 details and vice-versa. Does it make sense conceptually and is it feasible technically ? Thanks, Pavan. On 2/15/07, Martin Jenkins <[EMAIL PROTECTED]> wrote: Pavan wrote: > Should i create different tables for each user, so hat at any point > of time only one users data will be accessed ? Should i store all the > information in one table and then acess it ? The word you want is "normalisation" and you could have a look at, say, http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/ The URL suggests it's for MySQL but it's pretty generic stuff. Martin - To unsubscribe, send email to [EMAIL PROTECTED] - -- ' Always finish stronger than you start *
[sqlite] Difference between sqlite and sqlite3
Hi, Can anyone tell me what is the difference between sqlite and sqlite3. Thanks, Pavan. -- ' Always finish stronger than you start *
Re: [sqlite] Storing contacts list for each user
Pavan wrote: Should i create different tables for each user, so hat at any point of time only one users data will be accessed ? Should i store all the information in one table and then acess it ? The word you want is "normalisation" and you could have a look at, say, http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/ The URL suggests it's for MySQL but it's pretty generic stuff. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Storing contacts list for each user
Hi, I would like to use sqlite for my data storage and quite new to this environment. Have a few queries regarding sqlite. I have to store and read say 'n' users data. Each user will have his own contacts(Name,phonenumber) list. At any point of only one users data will be written or read. What is the best way to store this data ?. Should i create different tables for each user, so hat at any point of time only one users data will be accessed ? Should i store all the information in one table and then acess it ? Thanks in advance, Pavan. -- ' Always finish stronger than you start *
Re: [sqlite] Can't get results from PRAGMA
> Hi, > > (I hope we are not getting off-topic) We seem to be, sorry everyone! I trued e-mailing you directly - I hope you don't mind, but your mail server refused the mail! > (I'm using Code::Blocks IDE. If you wish I can send sqlite project > file for it. Project file has gcc,vc,bcc,dmc release and debug build > targets.) I tried to install Code::Blocks but hit a wall with wxWidgets and couldn't get any help. Specifically, Code::Blocks launches and a test app compiles fine. I get the option to create a wxWidgets project, however when I click finish at the end of the wizard, I get an error message saying "A matching configuration file cannot be found in the wxWidgets directory you specified. This means your project will not build." On the third page of the wizard, it asks me where wxWidgets has been installed and I have pointed it at the sub-directory of c:codeblocks that I used (containing lib and include, as the dialog suggests). Could you talk me through (assume I'm a total idiot and don't worry about aiming too low!!!) how you got yours installed? > I think you need an import library. Don't know whether gcc creates it with > def file automatically. > > dlltool -dllname sqlite3.dll -d sqlite3.def --output-lib libsqlite3dll.a I know I'm asking a lot, but if you could go on with a "click here and type this there" type explanation to get the SQLite sample app working with the config, that'd ber REALLY good :-) > >> I really am lost here, all this C++ stuff is new to me. SQL is old hat >> (I've used it with php, java, Access etc etc) and I'm now of the age >> (feeling a very old 41!!) where I don't want hassle so I'm not sure that >> having direct knowledge of the beast is really beneficial! >> > > There is a big conflict: You don't want hassle and choosed C++ ;) > Seriously, if your project does not need C++ then give a chance to Lua > and wxLua. I have been developing the app in Java, but it's just too slow. I also want it to have a more native feel on (initially windows) the host machine. >> If you can help me get this working, you really will have my eternal >> gratitude. >> > > Let me first sell Code::Blocks to you :) I promise I'll send you a > complete wx-sqlite3 sample project! I have no particular preference for any IDE. I only have 2 requirements:- 1) It must work (without needing a PHd in computing to make that happen!) 2) It must cost me very little (i.e. nothing!!!) So, Code::Blocks is ok if only 1) can be solved! Can you help? -- Paul - To unsubscribe, send email to [EMAIL PROTECTED] -
