RE: [sqlite] Problems opening db in win9x and utf8 filename
> > I no longer have a win98 system to test with, but based on my > understanding... > > os_win.c attempts to convert the filename from UTF-8 to UTF-16. If it > succeeds, it calls CreateFileW; Actually, there is a flag there that caused the convertion to UTF-16 to 'fail' (it doesn't really fail, just that the utf8ToUnicode returns 0). > if it fails, it calls CreateFileA with > the original string. Exactly > CreateFileW is a nonfunctional stub on win98, so > when you pass a UTF-8 filename sqlite takes that codepath and fails. > An ANSI filename won't pass the UTF-8 conversion, so CreateFileA is > called with the ANSI string. Actually, in Win98 it will pass the conversion, but as I said above, the function fails by a check: "if (!isNT())" > That doesn't necessarily explain win2k though. Perhaps the current > user locale does not match the ANSI encoding of the filename you're > passing in? Internally win2k only uses the Unicode version, so > CreateFileA must do an implict conversion to Unicode using the current > user codepage. Now that I checked the code, it actually does. Unfortunately, the way the code is setup makes it necessary for the caller to check in which OS it runs and either use UTF8 paths or ansii ones. I think this is not a good technique (and not actually intended from what I have read in the docs) since the sqlite3_open does not give a truly uniform interface to the caller. My suggestion is this: The sqlite3_open should always expect a utf8 path (as the docs say). If in win2k everything works fine. If in win98 it should convert the path to utf16 and THEN convert it to ansii using the CP_ACP (current ansii code page). This will work for 99.9% cases since in non-English win9x OS, almost 99.9% ansii strings are in the system's locale. I think this is also the expected behavior (and what I have programmed my app to do, until I tested it in win98). To make these changes, all the logic of os_win.c should change to accommodate the above. I would certainly say that the way it currently works is wrong (bug). Of course, there is the problem of breaking existing code (since many win9x user will not have read the docs, or else someone would have mentioned this behavior looong time agoe). To maintain compatibility (e.g. to accept ansii non-utf8 paths), a check can be made on whether the supplied path is in utf8 (heuristically this has almost 100% success) and then do the above. Costas > MSLU does provide a functional CreateFileW wrapper for win9x, but I > don't believe the stock sqlite binaries are built with it. > > > On 8/5/06, Peter Cunderlik <[EMAIL PROTECTED]> wrote: > > > I think you will never succeed using UTF-8 encoded filenames on those > > systems. I don't know how it can be done programmatically, but each > > file or directory name has its 8.3 name as well, i.e. "Program Files" > > would be "progra~1". I think this is the safest way how to pass > > filenames to SQLite. It should work on Win 9x as well as 2K and XP. > > NTFS can have 8.3 shortname creation disabled. Systems running > without it are not common but do exist, so you should avoid relying on > them if at all possible.
Re: [sqlite] Problems opening db in win9x and utf8 filename
On 8/5/06, Costas Stergiou <[EMAIL PROTECTED]> wrote: According to the docs, the file path should be utf8 encoded. If in the path there are non-ansii chars, the following method fails in win98. Tried the following: 1. Created an sqlite3 db in a path containing greek chars 2. Tried to open it by passing the path in utf8: it works on win2k, fails on win98 3. Tried to open it by passing the path as an ansii string: works on win98 (why?), fails in win2k Can anyone confirm this issue? I no longer have a win98 system to test with, but based on my understanding... os_win.c attempts to convert the filename from UTF-8 to UTF-16. If it succeeds, it calls CreateFileW; if it fails, it calls CreateFileA with the original string. CreateFileW is a nonfunctional stub on win98, so when you pass a UTF-8 filename sqlite takes that codepath and fails. An ANSI filename won't pass the UTF-8 conversion, so CreateFileA is called with the ANSI string. That doesn't necessarily explain win2k though. Perhaps the current user locale does not match the ANSI encoding of the filename you're passing in? Internally win2k only uses the Unicode version, so CreateFileA must do an implict conversion to Unicode using the current user codepage. MSLU does provide a functional CreateFileW wrapper for win9x, but I don't believe the stock sqlite binaries are built with it. On 8/5/06, Peter Cunderlik <[EMAIL PROTECTED]> wrote: I think you will never succeed using UTF-8 encoded filenames on those systems. I don't know how it can be done programmatically, but each file or directory name has its 8.3 name as well, i.e. "Program Files" would be "progra~1". I think this is the safest way how to pass filenames to SQLite. It should work on Win 9x as well as 2K and XP. NTFS can have 8.3 shortname creation disabled. Systems running without it are not common but do exist, so you should avoid relying on them if at all possible.
[sqlite] Re: sqlite using whole-file (not byte-range) locking
[EMAIL PROTECTED] writes: > In the latest versions of SQLite (3.3.0 and later) you can provide > SQLite with customized locking code at run-time. So you can > easily add AFS support that uses whole-file locking instead of > the goofy byte-range stuff I have to do for Win95. That's fantastic. One other thing -- and this is absolutely not SQLite's concern, and if it isn't handled it's no big deal -- AFS's handling of whole-file locks is great, but currently the way it handles byte-range locks is extremely dangerous: it always "pretends to" grant them. That's right, it always returns success on a byte-range lock that covers anything less than the whole file. Terrifying. I recently confirmed that you can easily corrupt databases this way. Would it be possible for the on-disk database file format to be able ot signal that a particular kind of locking must be used? Could it be done in a way that would prevent older versions of SQLite from accidentally opening the file (ie appear incompatible)? If this is not possible or seems like a bad design choice, I completely understand. I am not at all happy about the fact that AFS pretends to grant byte-range locks (the correct behavior should be to always refuse them if they cannot be supported), and this is really not SQLite's problem. > If you can wait, you might want to add AFS locking to that > module as another option. I'd be happy to. Please let me know when the right time to do this would be. - a -- PGP/GPG: 5C9F F366 C9CF 2145 E770 B1B8 EFB1 462D A146 C380
Re: [sqlite] Problems opening db in win9x and utf8 filename
On 8/5/06, Costas Stergiou <[EMAIL PROTECTED]> wrote: Hello, I am encountering a problem trying to open a sqlite3 db (ver 3.3.6) using the sqlite3_open function. According to the docs, the file path should be utf8 encoded. If in the path there are non-ansii chars, the following method fails in win98. AFAIK the Win9x systems can only use FAT16/32 and these filesystems cannot use Unicode, everything has to be translated into charset used by the system. I think you will never succeed using UTF-8 encoded filenames on those systems. I don't know how it can be done programmatically, but each file or directory name has its 8.3 name as well, i.e. "Program Files" would be "progra~1". I think this is the safest way how to pass filenames to SQLite. It should work on Win 9x as well as 2K and XP. Peter
Re: [sqlite] Insert delay!
Cesar David Rodas Maldonado wrote: I was thinking a lot in the next month in how can I do the delay insert and I found something for do that, is basic because I don't have a lot of knowledge, I'm just start the University. OK, I need with SQLite to select all the time as possible, and delay the Insert, I don't care the time that took insert something. These is my needs with SQLite. Is something Interesting on my idea? If there is one I will share my idea. Your English is not very clear, but as I understand your idea it could be realized by performing your INSERTs in a thread, ideally set to a low priority and fed from a FIFO queue. You need to be aware of the locking constraints. Have success with your studies. Are you studying Math and Computer Science?
[sqlite] Problems opening db in win9x and utf8 filename
Hello, I am encountering a problem trying to open a sqlite3 db (ver 3.3.6) using the sqlite3_open function. According to the docs, the file path should be utf8 encoded. If in the path there are non-ansii chars, the following method fails in win98. Tried the following: 1. Created an sqlite3 db in a path containing greek chars 2. Tried to open it by passing the path in utf8: it works on win2k, fails on win98 3. Tried to open it by passing the path as an ansii string: works on win98 (why?), fails in win2k Tried the above with the following 2 programs: - SQLiteSpy (3.3.6) (exactly the same behavior: fails to open utf8 encoded path in win9x - SQLiteExplorer: opened the file in Win98 (using ansii), failed on win2k. Can anyone confirm this issue? TIA, Costas
Re: [sqlite] date data types
Will Leshner wrote: On 8/4/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Adding DATE and TIMEINTERVAL types to SQLite would require an incompatible file format change. And it would go against the basic philosophy of SQLite. I wonder if it might not be useful to be able to ask SQLite if a value is a date or time. I'm assuming that at some point SQLite has to figure out if a value in a field is a date or time, and perhaps that logic could be exposed as an API call somehow. You can declare it to be a date or a time or both. Sqlite lets you make the declared type of a column anything you want it to be. It is smart enough to maintain the underlying storage type a float. What we do with dates is add a couple extra functions into Sqlite (simple things like a month number) and lift the date handling logic out of Sqlite and place it is a support library for use in applications. The way Sqlite cleverly uses a FLOAT for a date and time type and relates it to the theoretically correct epoch makes for an excellent date processing system. If you need it you can add functions to resolve not just Gregorian dates but Arabic, Hebrew, and various oriental dates. Timestamping is made simple and efficient. Since your application or a function can access the declared type from Sqlite your application (or a custom function) can decide on how to interpret the FP date/time stamp.
Re: [sqlite] Insert delay!
Your all right! what i am doing right know is cache the insert query and execute when the is not select, and that is almost working. When i was developing my "insert delay" I thought that will be great is SQLite support an option like MySQL (insert delay). And i dont wanna use Postgresql, cause this is client-server, I like SQLite, and with my "insert delay" SQLite for me is more useful than postgresql On 8/5/06, Clay Dowling <[EMAIL PROTECTED]> wrote: Cesar David Rodas Maldonado wrote: > I was thinking a lot in the next month in how can I do the delay insert and > I found something for do that, is basic because I don't have a lot of > knowledge, I'm just start the University. > > OK, I need with SQLite to select all the time as possible, and delay the > Insert, I don't care the time that took insert something. These is my > needs with SQLite. Is something Interesting on my idea? If there is one I > will share my idea. If you're trying to save your inserts until your app has some idle time, the best technique I can come up with is to cache those inserts in your application. When your activity monitoring thread detects that there is no other database activity it can fire off a process to perform the inserts. That keeps your app very responsive to user queries. If you find that you need a higher degree of concurrency you might consider PostgreSQL. But you'll probably be okay with SQLite as long as your app can find the time to run the inserts often enough. Clay -- http://www.lazarusid.com/notes/ Lazarus Notes Articles and Commentary on Web Development
Re: [sqlite] Insert delay!
Cesar David Rodas Maldonado wrote: I was thinking a lot in the next month in how can I do the delay insert and I found something for do that, is basic because I don't have a lot of knowledge, I'm just start the University. OK, I need with SQLite to select all the time as possible, and delay the Insert, I don't care the time that took insert something. These is my needs with SQLite. Is something Interesting on my idea? If there is one I will share my idea. If you're trying to save your inserts until your app has some idle time, the best technique I can come up with is to cache those inserts in your application. When your activity monitoring thread detects that there is no other database activity it can fire off a process to perform the inserts. That keeps your app very responsive to user queries. If you find that you need a higher degree of concurrency you might consider PostgreSQL. But you'll probably be okay with SQLite as long as your app can find the time to run the inserts often enough. Clay -- http://www.lazarusid.com/notes/ Lazarus Notes Articles and Commentary on Web Development
Re: [sqlite] Re: Help on triggers
Hi Igor, thanks for your help. Is the 'WHEN' clause used very often when creating triggers or do you think I could get away with leaving it out? I am unsure as what to put in the expression part of the WHEN clause in order to test it thoroughly. Also does anyone have a test database I could use to test out my triggers thoroughly as the one I created doesn't really lend itself to logical updates of other tables. Here is the one I created for testing purposes :- CREATE TABLE owner (ownerID number(2), surname varchar2(10), forename varchar2(10), address varchar2(20), primary key(ownerID)); CREATE TABLE vet (vetID number(3), name varchar2(10), primary key (vetID)); CREATE TABLE pettype (pettypeID number(2), pettype varchar2(10), legcount number(2), primary key(pettypeID)); CREATE TABLE pet (petID number(2), ownerID number(2), pettypeID number(2), petname varchar2(10), primary key (petID)); CREATE TABLE food (foodID number(2), food varchar2(20), primary key(foodID)); CREATE TABLE diet (pettypeIDnumber(2), foodID number(2), primary key (pettypeID,foodID)); CREATE TABLE visit (visitID number(5), petID number(2), vetID number(3), treatment varchar2(20),cost number(4,2), primary key (visitID)); INSERT INTO owner values (1,'Abbott','Andy','1 the Avenue'); NSERT INTO owner values (2,'Abbott','Zak','2 the Ambles'); INSERT INTO owner values (3,'Bailey','Bill','3 the Boulavard'); INSERT INTO owner values (4,'Charles','Cynthia','4 the Court'); INSERT INTO owner values (5,'Duck','Howard','5 the Downs'); INSERT INTO owner values (6,'Evans','Ethel','6 the Elms'); INSERT INTO owner values(7,'Flintstone','Fred','7 the Fells'); INSERT INTO vet values (100,'Williams'); INSERT INTO vet values (200,'Xavier'); INSERT INTO vet values (300,'Young'); INSERT INTO vet values (400,'Zorro'); INSERT INTO pettype values (1,'Dog',4); INSERT INTO pettype values (2,'Cat',4); INSERT INTO pettype values (3,'Mouse',4); INSERT INTO pettype values (4,'Fish',0); INSERT INTO pettype values (5,'Ant',6); INSERT INTO pettype values (6,'Spider',8); INSERT INTO pettype values (7,'Starfish',5); INSERT INTO pettype values (8,'Snake',0); INSERT INTO pettype values (9,'Duck',2); INSERT INTO food values (1,'dog food'); INSERT INTO food values (2,'cat food'); INSERT INTO food values (3,'biscuits'); INSERT INTO food values (4,'cheese'); INSERT INTO food values (5,'worms'); INSERT INTO food values (6,'flies'); INSERT INTO food values (7,'amoeba'); INSERT INTO food values (8,'grass'); INSERT INTO food values (9,'pond weed'); INSERT INTO food values (10,'wood'); INSERT INTO food values (11,'nectar'); INSERT INTO food values (12,'chocolate drops') INSERT INTO diet values (1,3); INSERT INTO diet values (1,12); INSERT INTO diet values (2,2); INSERT INTO diet values (2,12); INSERT INTO diet values (3,4); INSERT INTO diet values (4,5); INSERT INTO diet values (4,6); INSERT INTO diet values (4,7); INSERT INTO diet values (4,9); INSERT INTO diet values (5,10); INSERT INTO diet values (5,11); INSERT INTO diet values (6,6); INSERT INTO diet values (7,7); INSERT INTO diet values (8,5); INSERT INTO diet values (8,6); INSERT INTO diet values (9,8); INSERT INTO diet values (9,9); INSERT INTO diet values (1,1); INSERT INTO pet values (1,1,1,'Fido'); INSERT INTO pet values (2,1,1,'Gnasher'); INSERT INTO pet values (3,1,1,'Wolfie'); INSERT INTO pet values (4,2,2,'Fluff'); INSERT INTO pet values (5,2,2,'Felix'); INSERT INTO pet values (6,2,1,'Poochie'); INSERT INTO pet values (7,3,4,'Nemo'); INSERT INTO pet values (8,3,4,'Wanda'); INSERT INTO pet values (9,3,4,'Fred'); INSERT INTO pet values (10,3,4,'Titus'); INSERT INTO pet values (11,3,8,'Hiss'); INSERT INTO pet values (12,3,8,'Sid'); INSERT INTO pet values (13,4,2,'Tom'); INSERT INTO pet values (14,4,2,'Garfield'); INSERT INTO pet values (15,4,3,'Jerry'); INSERT INTO pet values (16,4,3,'Mickey'); INSERT INTO pet values (17,5,6,'Incy'); INSERT INTO pet values (18,5,6,'Wincy'); INSERT INTO pet values (19,5,9,'Daffy'); INSERT INTO pet values (20,6,1,'Woof'); INSERT INTO pet values (21,6,3,'Squeek'); INSERT INTO pet values (22,6,5,'Tony'); INSERT INTO pet values (23,6,7,'Spider'); INSERT INTO pet values (24,6,9,'Jenny'); INSERT INTO pet values (25,7,2,'Jinx'); INSERT INTO visit values (1,1,100,'Broken leg',20.00); INSERT INTO visit values (2,1,100,'Remove cast',5.50); INSERT INTO visit values (3,2,200,'Worming',15.25); INSERT INTO visit values (4,4,100,'Checkup',10.00); INSERT INTO visit values (5,5,100,'checkup',10.00); INSERT INTO visit values (6,6,200,'Worming',15.25); INSERT INTO visit values (7,6,100,'Virus infection',14.60); INSERT INTO visit values (8,6,300,'Eye drops',5.00); INSERT INTO visit values (9,6,400,'Cut eye',12.40); INSERT INTO visit values (10,7,300,'Fin rot',3.00); INSERT INTO visit values (11,7,300,'Fin rot',3.00); INSERT INTO visit values (12,11,200,'checkup',13.00); INSERT INTO visit values (13,11,100,'scale infection',7.00); INSERT INTO visit values (14,12,300,'Eye drops',5.00); INSERT INTO visit
[sqlite] Re: Help on triggers
John Newby <[EMAIL PROTECTED]> wrote: do you only need the column name when "UPDATE OF" is chosen and not the other 3? Correct. do you need either "BEFORE" or "AFTER" only when "INSERT", "DELETE", or "UPDATE" are chosen, but not when "UPDATE OF" is chosen? BEFORE and AFTER keywords are optional. You may choose to use them, or omit them, equally with all four kinds of triggers. when would the "WHEN" option be required Never. It's always optional. and what is the "expression" part of the WHEN clause? An arbitrary expression, possibly using fields of the table the trigger is on. The trigger only fires when the expression evaluates to true. Igor Tandetnik
[sqlite] Help on triggers
Hi, I am creating a GUI to SQLite for my final year projectat University so that the user need not know SQL in order to create a database. I am having trouble when trying to create triggers. The example shown on th SQLite website only shows one example for "UPDATE OF" which seems to be the easist of all triggers to create. I am unsure of the following things and would like some help on the answers please:- do you only need the column name when "UPDATE OF" is chosen and not the other 3? do you need either "BEFORE" or "AFTER" only when "INSERT", "DELETE", or "UPDATE" are chosen, but not when "UPDATE OF" is chosen? when would the "WHEN" option be required and what is the "expression" part of the WHEN clause? Many thanks John
[sqlite] Insert delay!
I was thinking a lot in the next month in how can I do the delay insert and I found something for do that, is basic because I don't have a lot of knowledge, I'm just start the University. OK, I need with SQLite to select all the time as possible, and delay the Insert, I don't care the time that took insert something. These is my needs with SQLite. Is something Interesting on my idea? If there is one I will share my idea.
Re: [sqlite] UNICODE Support
On Fri, Aug 04, 2006 at 10:02:58PM -0700, Cory Nelson wrote: > On 8/4/06, Trevor Talbot <[EMAIL PROTECTED]> wrote: > >On 8/4/06, Cory Nelson <[EMAIL PROTECTED]> wrote: > > > >> But, since you brought it up - I have no expectations of SQLite > >> integrating a full Unicode locale library, however it would be a great > >> improvement if it would respect the current locale and use wcs* > >> functions when available, or at least order by standard Unicode order > >> instead of completely mangling things on UTF-8 codes. > > > >What do you mean by "standard Unicode order" in this context? > > > > Convert UTF-8 to UTF-16 (or both to UCS-4 if you want to be entirely > correct) while sorting, to at least make them follow the same pattern. Huh? UTF-8 handled in the naive way (using "memcmp", like sqlite does) will automagically give you sorting by unicode codepoint (probably the only useful meaning of "standard Unicode order" here). UTF-16 handled in the naive way (either using "memcmp" or lexicographically on 2-byte integers) will sort things by codepoint, mostly, sort of, and otherwise by a weird order that falls out of details of the UTF-16 standard accidentally.[1] Perhaps you're using a legacy system that standardized on UTF-16 before the BMP ran out, and want to be compatible with its idiosyncratic sorting -- then converting things to UTF-16 before comparing makes sense. But that's not really appropriate to make as a general recommendation... better to convert UTF-16 to UTF-8, if you want to be entirely correct :-). [1] see e.g. http://icu.sourceforge.net/docs/papers/utf16_code_point_order.html -- Nathaniel -- Details are all that matters; God dwells there, and you never get to see Him if you don't struggle to get them right. -- Stephen Jay Gould
Re: [sqlite] From Windows file format to MacOSX (unsuccessfull)
Well, it didn't seem to work when I copied the file to MacOSX. It said (after accepting to go into command line mode) "invalid file format". I don't have an intel box to test now, so I will try again on monday at the office. My other option is to save the data in 2.8 format and convert from 2.8 to 3.1 with the dump command as explained in the documentation. I installed the 2.8 version from darwinports on MacOSX. Obviously, I would rather have the first solution working. Thanks, Alex -- Alexander Lamb [EMAIL PROTECTED] On Aug 4, 2006, at 6:11 PM, [EMAIL PROTECTED] wrote: Alexander Lamb <[EMAIL PROTECTED]> wrote: Well, I am afraid it didn't work. Somehow, the legacy_file_format info is not "sticky". The "legacy_file_format" pragma does not appear to be sticky, but it is. The value reported back by PRAGMA legacy_file_format is incorrect. But the legacy file format did get set. Mario Frasca <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] wrote: Adding DATE and TIMEINTERVAL types to SQLite would require an incompatible file format change. well, yes, that was already clear. but: where is the type of the data being stored? aren't there a few spare bits to use for 'future additions', that is, new data types? sure, a file containing date data would not be understood by executables where this has not been defined, but maybe it is possible to do it so that they see a 'text'... or maybe not... Mario: Look back over this thread, and others before it, and observe all the grief that gets caused by file format changes. I've learned my lesson: No more file format changes except too fix a serious bug. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] UNICODE Support
On 8/4/06, Cory Nelson <[EMAIL PROTECTED]> wrote: On 8/4/06, Trevor Talbot <[EMAIL PROTECTED]> wrote: > On 8/4/06, Cory Nelson <[EMAIL PROTECTED]> wrote: > > > But, since you brought it up - I have no expectations of SQLite > > integrating a full Unicode locale library, however it would be a great > > improvement if it would respect the current locale and use wcs* > > functions when available, or at least order by standard Unicode order > > instead of completely mangling things on UTF-8 codes. > What do you mean by "standard Unicode order" in this context? Convert UTF-8 to UTF-16 (or both to UCS-4 if you want to be entirely correct) while sorting, to at least make them follow the same pattern. Ah, so Unicode codepoint order. Unfortunately this isn't accurate: UTF-8 and UTF-32/UCS-4 are both naturally in codepoint order (UTF-8 because of the MSB-first style format), but UTF-16 isn't due to the way surrogate pairs are constructed. UTF-16 is actually the oddball here :P
