Re: [sqlite] Appropriate class for database
In my experience you have to create a custom control. If you are doing this under MFC, subclass listbox or grid. IIRC there might be a table class that is a good starter. I did something similar in a recent app (BackupBuddy 2) using wxWidgets. Another alternative we are looking at seriously is RealBasic since it is cross platform. There are lots of shareware utility classes depending on your environment -- CodeProject (.NET), Duff's Device, SourceForge or CodeGuru are all good places to find pre-fab utility code. CodeProject is in a tutorial format so it can be quite helpful. HTH. michael At 08:34 AM 1/5/2005, aleks ponjavic wrote: I am creating a program for basketball coaches. I am having big problems choicing a suitable class for my database. It should be one for players i.e player shirt-nr age rating and so on in columns then beneath the user should be able to add in a row and by pressing the columns that columns get sorted and the rows are still connected to eachother. I have tried out grid and listbox, listbox didn't have columns and I don't find grid as appropriate as I'd want it to be. Any suggestions!? I have looked a little at listctrl but it doesn't seem to be what I'm looking for... _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
Re: [sqlite] malformed disk image
At 05:33 AM 12/23/2004, you wrote: Inserting binary data without encoding it should *not* corrupt the database. It might make it difficult to get your data back, but other records in the database should be uneffected and the kinds of errors you were seeing from PRAGMA INTEGRITY_CHECK should not occur. Ok, thanks. A stray pointer or a buffer overrun in the application that SQLite links with might corrupt some internal SQLite data structures which could lead to database corruption. Experience suggests that the more likely outcome is a segfault, but corruption is a distinct possiblity. Yes, that is my thought. I will try to run it under a memory checker (wish I was on linux). We have not seen any SQLite bugs leading to database corruption in about a year (version 2.8.12) and SQLite has been heavily used since then. This could be some obscure SQLite bug, but that seems unlikely. It might be a bug that is only manifested on your PalmOS port, which is not a standard SQLite distribution. This is a Win32 app that interfaces to the palm, but is not running on the palm. Corruption can appear in a database long before you notice it. Until this problem is tracked down, you might consider modifying your code to do a PRAGMA INTEGRITY_CHECK periodically. That will help to find the problem as soon as it occurs. I do an integrity check at init time when I open the database and again when our app completes. The interesting part on this one is that it did not appear to trip on the completion check, but did on the next run's init. So, that would lead me to believe it is an external issue. If someone was doing a defragment run while a transaction closed, would that cause such an issue? At this point I am trying to figure out the fastest way to add redundancy so that my db is "backed up" and if I detect a fault the last good one can be restored. Sort of like a top level transaction (I already use transactions to allow multi-threading to work, so cannot add a top level one using the supplied mech). Is there something more high-performance and expedient than copying the db file to a backup after it passes integrity check and restoring if it fails? thanks michael
Re: [sqlite] malformed disk image
At 03:43 PM 12/22/2004, you wrote: See section 6.0 in http://www.sqlite.org/lockingv3.html. That article is on SQLite version 3, but the methods for corrupting a database apply equally well to version 2. Thanks. Unfortunately, none of these seem terribly likely. The user reported that nothing unusual occurred. The corruption occurred after installing some new software on his Palm which created a new entry in the main table (DBBackup). He is an IT guy, so reasonably knowledgable. He claims that he hasn't had any incidents of disk corruption to date. Short of writing randomly into the database file, is there any other software means I could corrupt the db than to write binary data without encoding it? Is there any way to tell if that is what likely occurred if I trace into the validation pragma query? thanks. michael
[sqlite] upgrade?
Hi, I am currently using SQLite v 2.8.13 for a commercial product for Palm Handhelds. I am noticing some performance issues; most notably when I do a database validate() to verify my integrity at app open and close. I see that version 3.0.8 has some code optimizations, but am unsure if upgrading from the 2.8 series to the 3.0 series is just a drop in or if my code will need to change. Can anyone tell me if the API has changed in a notable way? Also, is my performance going to improve with the 3.0.8 over 2.8.13? If not, what about 2.8.15? thanks! Michael Hunley Managing Partner/Senior Engineer PocketPurchase Consultants
[sqlite] Re: [inbox] [sqlite] Future Features
At 05:41 AM 3/4/2004 -0600, TBrowder wrote: Adding a blob capability (i.e., allowing a byte string as data which is defined by length rather than null termination) I have a C++ interface class that gives you blobs in the current SQLite (2.8.8-2.8.11+) without breaking the optimization target of keeping row data small (<230 bytes). I have been considering submitting, but it will require a couple slight mods for general use (it uses some of our data types that would be baggage for everyone else). If there is enought interest I will post the code for use/review. Would this be valuable to anyone? michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Re: [inbox] [sqlite] About ORDER BY results...
At 10:32 AM 2/27/2004 +0100, Eric Morand wrote: This was a mistake by me ! Here is the order actually returned by SQLite : Celine Céline Eric Marc Zoe céline eric Éric éric Do someone know how to have the ORDER BY statement return values ordered with anything else that memcmp() order ? This method completely make the ORDER BY useless when dealing with accented caracters... Two solutions come to mind, but hopefully someone more conversant with SQL/SQLite will chime in with an easier solution. Both my solutions involve adding your own function to SQLite (See the docs on the topic on the website). Solution 1: implement your own OrderBy function and call that instead Solution 2: Implement a Flatten function that converts accented characters to their non-accented and, optionally, converts all uppercase to lower (or vice-versa). Then use ORDER BY FLATTEN(Name) I think solution 2 is the better one, since you can continue to use the ASC or DESC modifiers on ORDER BY and you could use flatten for other things as needed. HTH. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: [inbox] Re: [sqlite] Data encryption
At 11:36 AM 1/31/2004 -0600, Kurt Welgehausen wrote: You'll have to encrypt each column independently. If you use the same key and initialization vector, you should be able to search, but of course, only for exact matches. Also, the size of each field will probably grow to the next multiple of your cipher's block size, so you'll need to pad the data before you encrypt; and you'll have to remove nulls from the encrypted data. I think the sequence would have to be If you use an encryption method that makes this true, it is less secure. Random salt is generally required to maintain security, but it is the usual risk versus ease of use balance. the other response, using a SHA (or similar) hash for the search index and a secure encryption scheme on the actual data, is much better and works well in practice. That still restricts you to exact match (no using "LIKE" and similar). HTH. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] qualified join
I have the following two table defs: CREATE TABLE t1(id INTEGER PRIMARY KEY,name VARCHAR(64)); CREATE TABLE t2(id INTEGER,time INTEGER,deleted INTEGER(1) PRIMARY KEY(id,time)); My question is, how can I: "select t1.name,t2.deleted from t1 join t2 using id" but only keep each row where t2.time is the max for that id? To put that another way, is there a single select I can issue which does the equivalent of doing: CREATE TEMPORARY TABLE temp(id INTEGER,time INTEGER); INSERT INTO temp (id,time) SELECT id,MAX(time),deleted FROM t2 GROUP BY id; SELECT t1.name,t2.deleted FROM temp JOIN t1 USING id JOIN t2 USING id WHERE t2.time=temp.time; I am hoping for some syntax that is the valid SQL/SQLite equivalent of: SELECT t1.name,t2.deleted FROM t1 join t2 USING id WHERE t2.time=MAX(t2.time) thanks for any help. Michael Hunley Senior Engineer PocketPurchase, Inc. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] integer primary key initial value
This may be a totally rooky question, but better safe than sorry If I declare a table with an INTEGER PRIMARY KEY field s.t. it is an auto-increment, do the first INSERT into it and call sqlite_last_insert_rowid(), will I get back a 0 or something else? Will I ever get 0 back (in case it wraps or something)? thanks. Michael Hunley Senior Engineer PocketPurchase, Inc.
Re: [sqlite] Re: [inbox] Re: [sqlite] Optimizing a query
At 11:12 AM 1/14/2004 -0500, D. Richard Hipp wrote: SELECT count(*) FROM table WHERE col1>'abc' AND col1<'xyz'; In the original query, the result was indeed a count(*) so no access to the data we required there. But access to the data was required in order to evaluate the WHERE clause. So it is still O(NlogN). What is different between his where clause and the one you cite as an example that only takes O(N)? Is it just that in your example col1 is (part of) the index? So, wouldn't Ken be able to do the same, except that he needs to step through two indices? That, it seems to me, is the crux of the issue. I don't mean to belabor this issue, but I am curious as to the workings. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: [inbox] Re: [sqlite] Optimizing a query
At 10:37 AM 1/14/2004 -0500, D. Richard Hipp wrote: In some cases you can avoid the O(logN) lookup of the main table entry and just use the index. For example: SELECT count(*) FROM table WHERE col1>'abc' AND col1<'xyz'; Wasn't that the original question, Ken? Except it was a count(*) on a JOIN. Dr Hipp, would the same optimization apply if it is stepping through two indices? In which case Ken should see a huge speed improvement to his original question by adding an index and updating to the latest SQLite (after you release 2.8.11, that is ;). Just trying to keep track. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: [inbox] Re: [sqlite] Optimizing a query
At 07:17 PM 1/13/2004 -0500, D. Richard Hipp wrote: Actually, SQLite implements JOIN USING by translating the USING clausing into some extra WHERE clause terms. It does the same with NATURAL JOIN and JOIN ON. So while those constructs might be helpful to the human reader, they don't really make any difference to SQLite's query optimizer. Thanks for the specifics, very good to know. My suggestion was based on the idea that, while it may be equivalent now, you could roll out a new version tomorrow with optimizations for JOINS. It is far less likely to roll out a new optimizer that would catch convolutions of where clauses that could be optimized. The latter would be more universally useful, but immensely harder to write, I expect. thanks again. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: [inbox] [sqlite] Optimizing a query
At 05:16 PM 1/13/2004 -0600, Williams, Ken wrote: SELECT count(*) FROM propositions p, output o WHERE p.verb_id=o.verb_id AND p.tag=o.tag AND (p.stop!=o.stop OR p.start!=o.start); I don't think this will be much help and is very implementation specific, I expect, but Your first two where clauses are effectively a "JOIN USING verb_id,tag", which has a much better chance of using any built in optimizations for cross indexing. Hopefully someone much more knowledgable than I will pipe up, but I hope this helps a little. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: [inbox] Re: [sqlite] error when using AS with JOIN USING
At 05:20 PM 12/11/2003 -0500, [EMAIL PROTECTED] wrote: Try this alternate syntax: SELECT U.b, I.c FROM User U, UserInfo I WHERE U.a = I.a; Thanks. I can work around it easily, but there are advantages to the AS that might also be broken that could not be easily re-worked, like joining a table to itself. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] error when using AS with JOIN USING
I just ran into an annoying bug using SQLite 2.8.6 on Win XP compiled from source. If you do a select and use the "AS 'alias'" for a table and also do a "JOIN USING ", you get a sql error: no such column .. So, if you have table User with fields a,b and table UserInfo with fields a,c, the following gives the error "User.a no such column": SELECT U.b,I.c FROM User as U JOIN UserInfo as I USING a; whereas, the following sql will work just fine: SELECT User.b,UserInfo.c FROM User JOIN UserInfo USING a; Of course, "SELECT U.b FROM User as U;" works just fine. According to the docs on the website, this should be supported. If you have lots of fields to extract from multiple tables, this can get painful depending on the length of the table names you use. I have submitted a bug report. Michael Hunley Senior Engineer PocketPurchase, Inc. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] autoincrement columns & primary key
I have just begun experimenting with SQLite for an embedded project. I am
relatively new to this list, so apologies if this is old news. Is there an
archive of the emails with a good search facility so I can check for old
questions?
I was trying out some table creation and insert semantics in a simple test
app. I tried several variations on my create statement and the two PRIMARY
KEY syntax as listed at http://www.hwaci.com/sw/sqlite/lang.html. I have
noticed a number of important truths, a couple of which I question as bugs
(or user error ;).
1. In order to actually get autoincrement functionality, the table creation
has to specify the column explicitly as INTEGER without further
"decoration". I had tried specifying "UNSIGNED INTEGER (10)" and "INTEGER
(10)" as I would for mysql and I could not get autoinc to work. Is this
true? Is there a reason for this? It seems reasonable to specify the size
of an autoincrement integer as well as signedness. Or is it that all
autoincrement are 32-bit?
2. I tried specifying multiple columns as part of the primary key. If I
did it in the column definition I got back an error that there were
multiple columns in the primary key. If I did it as part of a general
constraint as "PRIMARY KEY (Col1, Col2, Col3)" the statement succeeded, but
the integer that was supposed to be autoincrement did not auto increment
even if specified correctly. If the only column in the list was the
integer column it worked fine and did the auto increment. So,
CREATE TABLE User ( Name VARCHAR (40),
UID INTEGER NOT NULL,
DeviceID VARCHAR (64) DEFAULT 'Unknown',
PRIMARY KEY (Name, UID, DeviceID) );
works, but
CREATE TABLE User ( Name VARCHAR (40) PRIKMARY KEY,
UID INTEGER PRIMARY KEY NOT NULL,
DeviceID VARCHAR (64) PRIMARY KEY DEFAULT
'Unknown' );
does not.
3. Related to #2 above, PRIMARY KEY in the list form does not seem to be
strict enough on unique when a null value is involved. If I have 3 columns
as part of the PRIMARY KEY list and do an insert that fills in 2 of the
fields I can repeat the insert and I get duplicate entries. E.g.:
CREATE TABLE User ( Name VARCHAR (40),
UID INTEGER,
DeviceID VARCHAR (64) DEFAULT 'Unknown',
PRIMARY KEY (Name, UID, DeviceID) );
INSERT INTO User (Name,DeviceID) VALUES ('Michael','Test');
INSERT INTO User (Name,DeviceID) VALUES ('Michael','Test');
Gets me two rows with ('Michael',NULLm'Test').
I think that 3 is a bug. I think 2 might hide a bug, unless I
misunderstand the syntax and intent (quite possible). 1 seems counter to
the goal of SQL syntax compatibility and should at least make it into the
FAQ that talks about AUTOINCREMENT.
If I'm missing something or wrong about any part, please let me know.
thanks. HTH.
Michael Hunley
Senior Engineer
PocketPurchase, Inc.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Re: [inbox] Re: [sqlite] double email
At 12:49 PM 11/21/2003 -0600, Scott Lamb wrote: Many people (myself included) prefer it the way this list does it. I'm on a lot of lists which are sorted into subfolders. If you send a message to the list, I might or might not see it. If you "reply all" to a message of mine (or to a "reply all" to a message of mine, to whatever depth), it'll sort into my main inbox and I'll be sure to see it. Actually, it is the practice of replying to all that is causing the double send. I got yours twice. Once for your reply to me and again because I am on the list. The right answer is for the list to see that an address on the list is already on the TO or CC list of the original message and skip that. Not as straight forward, but not too difficult for a filter. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: SELECT syntax, use NOT on fields
At 02:04 PM 11/21/2003 +0200, you wrote: Your question fails into same category as "Can I SELECT ... FROM * ; ?" It is not that easy to explain why, but you should never ask such questions. Umm...That sounds a little harsh and totalitarianist. Did you actually mean that people shouldn't ask those kinds of questions? Or did you mean that this is not the proper list for such questions? I think that the latter is reasonable, since it is a general SQL syntax question and should be directed at a general SQL list. Your example question is more obviously incorrect SQL, though not necessarily obviously so to inexperienced users, but Seth's original question looks much more reasonable. In fact it could be quite useful for things like re-populating a table or in an INSERT...SELECT statement and perhaps it should be asked on a SQL list. I certainly would find it disconcerting and potentially horrifying if a list dedicated to improving a SQL code base told its subscribers not to ask prodding questions. I hope that was not the intent of your answer. cheers. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: [inbox] Re: [sqlite] double email
At 05:35 PM 11/21/2003 +0100, you wrote: Will you stop sending your emails twice, this one, I also got twice I am only getting them once. Are others getting them twice? Bert, are you sure it is not something going on in your email server? Or perhaps a double email address mapping in the list? While I am occupying people's time, does anyone know why this list does not do as others do and make the email reply-to address be the list instead of the individual who sent it? That is much more generally beneficial and less error-prone for people (like me) who don't pay strict attention the the 'To' when replying. michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
