Re: [sqlite] SQLite for Win32 TCHAR
So finally Ticket 239 http://www.sqlite.org/cvstrac/tktview?tn=239,2 will be fixed soon, after 1 year the ticket was submit? (by adding 'A' to all file handling API in OS_WIN) Aside from it, since Win9X is really diminishing, SQLite should start to adapt itself to 'NT way' if it purports Windows support... -- KL On Wed, 17 Mar 2004 6:48:55 + "Nuno Lucas" <[EMAIL PROTECTED]> wrote: > Oops... > I realize now that the fix for compiling for windows when UNICODE is defined is in > my TODO list. > I was waiting for it to be fixed in the main code, to minimize the changes from the > original code. > In the total, there are only a dozen changes to the code in "os.c". I can also make > a patch, if that is desired. > > Regards, > ~Nuno Lucas > > === On 2004-03-17, Nuno Lucas wrote === > >Just a litle correction > > > >=== On 2004-03-17, Andrew Francis wrote === > >... > >> > >>Regardless, I'd start by looking at the sqlite ports to Windows CE / > >>PocketPC. From memory, there are a couple around. Windows CE provides > >>only Unicode versions of the Win32 functions, so generally WinCE code > >>has to be TCHAR/Unicode 'clean.' > >> > > > >All WinCE ports I know of (including the one I try to maintain - > >sqlite-wince.sf.net) just wrap the OS specific functions to work with the Unicode > >only available Win32 API functions (and a few tweaks to make synchronization work). > >The sqlite API is unchanged, just the UTF8 is enabled by default. > >I don't see any advantage in creating a Unicode specific sqlite API, as this can be > >done by a simple wraper made by the user (that all C++ programmers out there would > >make, anyway by creating their object encapsulation to sqlite C API). > >The only thing I can say about my WinCE port is that it compiles on normal windows, > >too, reverting to the original sqlite source code (compiling with or without > >UNICODE defined). > > > >For the original poster: > >If you want a hint on how to do your wraping, see the help on mbstowcs/wcstombs > >functions of the C runtime, or MultiByteToWideChar/WideCharToMultiByte in the Win32 > >API. > > > >Best regards, > >~Nuno Lucas > > > > -- > /"\ > \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > X - AGAINST MS ATTACHMENTS > / \ > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite for Win32 TCHAR
Oops... I realize now that the fix for compiling for windows when UNICODE is defined is in my TODO list. I was waiting for it to be fixed in the main code, to minimize the changes from the original code. In the total, there are only a dozen changes to the code in "os.c". I can also make a patch, if that is desired. Regards, ~Nuno Lucas === On 2004-03-17, Nuno Lucas wrote === >Just a litle correction > >=== On 2004-03-17, Andrew Francis wrote === >... >> >>Regardless, I'd start by looking at the sqlite ports to Windows CE / >>PocketPC. From memory, there are a couple around. Windows CE provides >>only Unicode versions of the Win32 functions, so generally WinCE code >>has to be TCHAR/Unicode 'clean.' >> > >All WinCE ports I know of (including the one I try to maintain - sqlite-wince.sf.net) >just wrap the OS specific functions to work with the Unicode only available Win32 API >functions (and a few tweaks to make synchronization work). >The sqlite API is unchanged, just the UTF8 is enabled by default. >I don't see any advantage in creating a Unicode specific sqlite API, as this can be >done by a simple wraper made by the user (that all C++ programmers out there would >make, anyway by creating their object encapsulation to sqlite C API). >The only thing I can say about my WinCE port is that it compiles on normal windows, >too, reverting to the original sqlite source code (compiling with or without UNICODE >defined). > >For the original poster: >If you want a hint on how to do your wraping, see the help on mbstowcs/wcstombs >functions of the C runtime, or MultiByteToWideChar/WideCharToMultiByte in the Win32 >API. > >Best regards, >~Nuno Lucas > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Re: [sqlite] SQLite for Win32 TCHAR
Just a litle correction === On 2004-03-17, Andrew Francis wrote === .. > >Regardless, I'd start by looking at the sqlite ports to Windows CE / >PocketPC. From memory, there are a couple around. Windows CE provides >only Unicode versions of the Win32 functions, so generally WinCE code >has to be TCHAR/Unicode 'clean.' > All WinCE ports I know of (including the one I try to maintain - sqlite-wince.sf.net) just wrap the OS specific functions to work with the Unicode only available Win32 API functions (and a few tweaks to make synchronization work). The sqlite API is unchanged, just the UTF8 is enabled by default. I don't see any advantage in creating a Unicode specific sqlite API, as this can be done by a simple wraper made by the user (that all C++ programmers out there would make, anyway by creating their object encapsulation to sqlite C API). The only thing I can say about my WinCE port is that it compiles on normal windows, too, reverting to the original sqlite source code (compiling with or without UNICODE defined). For the original poster: If you want a hint on how to do your wraping, see the help on mbstowcs/wcstombs functions of the C runtime, or MultiByteToWideChar/WideCharToMultiByte in the Win32 API. Best regards, ~Nuno Lucas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite for Win32 TCHAR
I understand what he means... I am using SQLite 2.8.11 and I did notice this too... Some Win32 fucntions should have 'A' attached to the end. This way it does not complain when using SQLite embedded in a UNICODE project. [ I even think I submitted a diff with things you would have to change to have absolutely no warnings any more (for a Win32 unicode build that is)] greets... At 06:11 17/03/2004, Andrew Francis wrote: Michael Smith wrote: Has anyone ported the SQLite code to support the Win32 TCHAR type and API? I'll confess I'm not actually sure what you mean by "ported" - do you mean using TCHAR (which is, btw, just a typedef for char or short depending on whether UNICODE is #define'd) in the sqlite library interface, or using TCHAR for Win32 calls? Regardless, I'd start by looking at the sqlite ports to Windows CE / PocketPC. From memory, there are a couple around. Windows CE provides only Unicode versions of the Win32 functions, so generally WinCE code has to be TCHAR/Unicode 'clean.' -- Andrew Francis Software Developer Family Health Software - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] G00fy, (aka KaReL, aka Steven) Main Webpage : http://komma.cjb.net CQ Database : http://lid.fragland.net/ ICQ #: 35217584
Re: [sqlite] SQLite for Win32 TCHAR
Michael Smith wrote: Has anyone ported the SQLite code to support the Win32 TCHAR type and API? I'll confess I'm not actually sure what you mean by "ported" - do you mean using TCHAR (which is, btw, just a typedef for char or short depending on whether UNICODE is #define'd) in the sqlite library interface, or using TCHAR for Win32 calls? Regardless, I'd start by looking at the sqlite ports to Windows CE / PocketPC. From memory, there are a couple around. Windows CE provides only Unicode versions of the Win32 functions, so generally WinCE code has to be TCHAR/Unicode 'clean.' -- Andrew Francis Software Developer Family Health Software - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Database/Table Overhead
Can someone explain to me why, after populating a new table
in a new database file, with no indexes, that the "overhead"
is around 50%?
That's the question.. to understand my justification for the
figure, I've explained it below. Not a complaint, just would
like to understand why and how I may possibly help the
situation.
Thanks for any help!
// CHRIS
..
..
* *
* BACKGROUND *
* *
I've written a program called Power-Grab that downloads binary
files from usenet newsgroups. I keep all my data in basically
flat files of custom-serialized objects. For the most part,
it's simple and suffices.
One problem I face is the sorting of headers (by Subject:) after
I scan a newsgroup. I wrote my own implementation of QuickSort
that sorts objects that actually point to records on disk; so it's
basically a "disk-based" QuickSort, with some very simple caching
to help performance.
I've always wanted to find a good relatively fast database engine
that I could embed into my program without a lot of overhead or cost.
Offerings I found were either free and not useful or useful and
expensive (eg, CodeBase.) Looks like SQLite is exactly what I've
been waiting for (over 4 years now!)
I just started playing around with it, and I'm trying to determine
if I can really use it (performance-wise.)
For Reference:
System: Windows 2000 Pro (With SP4), P4 @ 2.4 GHz, 1GB RAM
Language: Visual C++ 7.0
SQLite: Version 2.8.13, using sqlite.dll from www.sqlite.org
* *
* THE PROBLEM *
* *
I have a data file that represents the real data from one of the larger
newsgroups:
Filename: alt.binaries.cd.image.xbox.XOV
Size: 376,878,446
Records: 1,932,225
The file is basically the raw output from an "XOVER" command to a news
server.
It consists of records like this:
{ int FLAGS, int ID, int LINES, int BYTES, char[] SUBJECT, char[] SENDER,
char[] MSGID }
The goal is relatively simple:
(1) Create a database file
(2) Dump all records to the database file
(3) Create an index
* *
* FIRST TRY*
* *
< Created Database File "XOVER.DB" >
CREATE TABLE XOVER (ID INT, FLAGS INT, LINES INT, BYTES INT, SUBJECT TEXT,
SENDER TEXT, MSGID TEXT);
PRAGMA synchronous = OFF;
(repeat 1,932,225 times)
INSERT INTO XOVER (ID, FLAGS, LINES, BYTES, SUBJECT, SENDER, MSGID)
VALUES (%d,%d,%d,%d,'%s','%s','%s');
CREATE INDEX IDX_SUBJ ON XOVER (SUBJECT);
* *
* RESULTS *
* *
First, reading the XOV data file and doing all the formatting but NOT
calling SQLite,
it took between 48 and 51 seconds (I tested 3 times.) So we can safely
subtract that
from the run-time to get an estimate of how long it takes SQLite to
populate the table.
INSERT 1,932,225 RECORDS
TIME: 3 Hours, 39 Minutes, 24 Seconds (13,164 Sec is about 146.78
INSERT/sec)
CREATE INDEX IDX_SUBJ ON XOVER (SUBJECT)
TIME: 10 Minutes, 24 Seconds
SELECT * FROM XOVER; (using Callback function that only does
"++nRecords;")
TIME: 4 Minutes, 31 Seconds
The SQLite database file was a bit larget than I expected, after all said
an done:
728,733,696 XOVER.DB (Before Index Built)
980,514,816 XOVER.DB (After Index Built)
Compared to the original file:
376,878,446 alt.binaries.cd.image.xbox.XOV
Not excited about the time to create the table;
about 3.5 hours to add about 2 million records.
* *
* SECOND TRY *
* *
Same as the FIRST TRY, but wrapped in a transaction:
< Created Database File "XOVER.DB" >
CREATE TABLE XOVER (ID INT, FLAGS INT, LINES INT, BYTES INT, SUBJECT TEXT,
SENDER TEXT, MSGID TEXT);
PRAGMA synchronous = OFF;
BEGIN;
(repeat 1,932,225 times)
INSERT INTO XOVER (ID, FLAGS, LINES, BYTES, SUBJECT, SENDER, MSGID)
VALUES (%d,%d,%d,%d,'%s','%s','%s');
COMMIT;
BEGIN;
CREATE INDEX IDX_SUBJ ON XOVER (SUBJECT);
COMMIT;
* *
* RESULTS *
* *
Great- Much better performance!
INSERT 1,932,225 RECORDS
TIME: 3 Minutes, 49 Seconds (229 Sec ==> 8,437 INSERT/sec)
CREATE INDEX IDX_SUBJ ON XOVER (SUBJECT)
TIME: 7 Minutes, 14 Seconds
SELECT * FROM XOVER; (using Callback function that only does
"++nRecords;")
TIME: 2 Minutes, 36 Seconds
* *
* CONCLUSION *
* *
As I said, the SQLite database file was a bit larger than I expected,
can anyone shed some light on this?
The original file:
376,878,446 alt.binaries.cd.image.xbox.XOV
Is composed mainly of text. I realize that SQLite stores everything
as text (eg, numbers are in ascii text format). But I didn't expect
such a large increase in size.
Some rough calculations..
The
[sqlite] sqlite_encode_binary() is now a supported API
The sqlite_encode_binary() and sqlite_decode_binary() routines that have been included in the SQLite source tree for ages but which were not built into the library by default have now been fully integrated into the build and are a supported, standard part of the library. They will be included in the next release, and are already included for anyone adventuresome enough to download the lastest code from CVS and build it yourself. A word of warning: During the process of making these routines official, the encoding was changed slightly (to make it a little faster.) That means that a string encoded by an older version of sqlite_encode_binary() will not correctly decode using the newer version of sqlite_decode_binary(). So if you have been using these routines in the past, you should continue to use the old versions, not the new ones that are now built into the library. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Re: Report writer
Regarding: ...though I am ultimately interested in having my product work natively on Linux Then I'm even more out-of-my-league here, but there's a free beta-ish JDBC wrapper for SQLITE at: http://www.ch-werner.de/javasqlite/overview-summary.html and Linux OpenOffice supports JDBC: http://www.openoffice.org/product/dbase.html Under OpenOffice, you can define queries somewhat like the Access GUI method: http://www.openoffice.org/product/pix/dbase-big.png or use SQL. If that combination worked, any chance it would be just what you're looking for? Donald [writing on behalf of myself, and not my company] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Re: Report writer
On Tuesday 16 March 2004 06:42 am, Griggs, Donald wrote: > > Are you developing for Windows? If so, I don't know if one of these might > be of help: > > 1) I see there's a commercial, but not expensive product (US$40), > SqliteManager Thanks, Donald (and other who've responded). I will investigate SqliteManager, though I am ultimately interested in having my product work natively on Linux as well (it currently does work under WINE on Linux, but not as well as under native Windows). -- My GPG public key is at http://ronware.gotdns.com/ fingerprint: 4E91 06E9 2020 114C 8BCD 55B2 0816 60AF 2B3D 4C51 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQLite for Win32 TCHAR
Hello, Has anyone ported the SQLite code to support the Win32 TCHAR type and API? Thanks, Mike
RE: [sqlite] Report writer
Not wishing to appear biased, but... I use both Borland's Delphi and C++ Builder. Both use a common DB access known as DBExpress. I have the option of using any number of third party report writers, including the biggie Seagate Crystal Reports, plus the two "internal" report writers supplied with the Borland products. For you Linux junkies there is Borland's Klix, (Delphi on a real OS.) Perhaps you could choke down enough Pascal to use it. At least it ain't Visual Basic et. all :-) Actually I have not done it but I think it (Klix) supports both C++ and Object Pascal. Fred -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 16, 2004 8:43 AM To: 'Ron Aaron'; [EMAIL PROTECTED] Subject: RE: [sqlite] Report writer Ron Aaron [mailto:[EMAIL PROTECTED] wrote: Does anyone have a generic report writer which could be used with SQLite? Hi Ron, Are you developing for Windows? If so, I don't know if one of these might be of help: 1) I see there's a commercial, but not expensive product (US$40), SqliteManager, that has a basic report capability based on (plain text) template files. I copied an example template at the end of this email. Templates and SQL are stored in the sqlite database itself. See http://sqlabs.net (and this has no apparent connection with an open-source, French-based PHP project of the same name at http://sqlitemanager.org). And *I* have no connection with either. ;-) 2) There's a nifty open ODBC driver for SQLITE from Germany at: http://www.ch-werner.de/sqliteodbc/ If perhaps your clients already have M$ Excel or Access and familiarity with them, they can use this to link directly to your sqlite database. 3) Using the ODBC driver above, I think you can use OpenOffice.org's great open-source software to link the data instead of using a commercial package, using either the spreadsheet application or using a form from within the word processor. Regards, Donald Griggs [Not writing on behalf of my company] Example of template which will dump entire table into an html file. Other types of output accommodated. (Taken from sqlabs.net's "Reports ReadMe.txt" contained in demo download. I've changed the brackets on the two HTML tags to possibly prevent some mail systems from over-interpreting this template) [[html]] [LOOPCOLUMNS][FIELD.LABEL][/LOOPCOLUMNS] [LOOPROWS] [LOOPCOLUMNS] [FIELD.VALUE] [/LOOPCOLUMNS] [/LOOPROWS] [[/html]] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Report writer
Ron Aaron [mailto:[EMAIL PROTECTED] wrote: Does anyone have a generic report writer which could be used with SQLite? Hi Ron, Are you developing for Windows? If so, I don't know if one of these might be of help: 1) I see there's a commercial, but not expensive product (US$40), SqliteManager, that has a basic report capability based on (plain text) template files. I copied an example template at the end of this email. Templates and SQL are stored in the sqlite database itself. See http://sqlabs.net (and this has no apparent connection with an open-source, French-based PHP project of the same name at http://sqlitemanager.org). And *I* have no connection with either. ;-) 2) There's a nifty open ODBC driver for SQLITE from Germany at: http://www.ch-werner.de/sqliteodbc/ If perhaps your clients already have M$ Excel or Access and familiarity with them, they can use this to link directly to your sqlite database. 3) Using the ODBC driver above, I think you can use OpenOffice.org's great open-source software to link the data instead of using a commercial package, using either the spreadsheet application or using a form from within the word processor. Regards, Donald Griggs [Not writing on behalf of my company] Example of template which will dump entire table into an html file. Other types of output accommodated. (Taken from sqlabs.net's "Reports ReadMe.txt" contained in demo download. I've changed the brackets on the two HTML tags to possibly prevent some mail systems from over-interpreting this template) [[html]] [LOOPCOLUMNS][FIELD.LABEL][/LOOPCOLUMNS] [LOOPROWS] [LOOPCOLUMNS] [FIELD.VALUE] [/LOOPCOLUMNS] [/LOOPROWS] [[/html]] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
