>>From what I experienced, the exported functions from the DLL were >simple to use and did everything you wanted, though perhaps slightly >differently from the Delphi DBE way some people may be used to
I wouldn't sell the BDE-style components short, as they may be useful for some, if well-written and well-documented, but I like simple. >I found a simple wrapper class worked better than trying to use a >bunch of componants pretending to be the BDE, but of course, a lot >depends on what you want to do and how confident you are using >exported DLL functions I'm a big fan of small and lightweight, and have chosen that route myself. If you're talking to a bare-bones DLL interface, be careful of memory leaks. Much of the "Delphi wrapper plus" code I've tried has problems I've caught even as a rank beginner. I think some mid- level code is a good idea, though, even if only to get it right one time and insulate programmers from pointers, leaks and details. >2) Indexes and DB structure are important. Fairly generic DB rule one >I suppose, but this is an SQL DB, its VITAL you add the indexs you >need, and even MORE important you *DON'T* add the indexs you don't >need. >Plan your DB before hand with n eye that everything has a function, >if you don't use it, don't have it. There is more about this on the >SQLite docs > >3) The default cluster size for a Windows NTFS system seems to be >4096 bytes. Setting the SQLite database page size to the same size >will speed up your database on systems where the cluster size is the >same (Note, Linux cluster I believe to 1024 which is the default for >new SQLite databases) >Easiest way to tell your cluster size is to defragment your drive and >analyze. It tells you in there > >To set the SQLite page size, create a new *EMPTY* database and do a > PRAGMA page_size=4096; >Now create your tables immedeatly (if you close down the SQLite >commandline program and reopen the DB, the page size is reset to >1024). The page size must be set before the first table is created. >Once that tables made, you can't change the size > >Typing > PRAGMA page_size; >will tell you what it is currently set at > >4) SQLite doesn't support clustered indexes (simply, indexes that >force the data in the database to be physically layed down in the >SAME order as the index needs it to be in.) >This means that if your index is sequential INTEGER, the records are >physically layed out in the database in that INTEGERs order, 1 then 2 >then 3. > >You can't make a Clustered index, but you CAN sort your data in order >so that any historical data is ordered nicely. Of course, as the >database matures, you lose that, but it helps > >Someone else posted this, and it is a nice example to use, so I will. >If you have a table WIBBLE whose field KEY you want to access a lot, >it would be nice if everything was in order. Using the command line >tool, you can create a fake cluster by doing the following: > > create table wibble2 as select * from wibble; > delete from wibble; > insert into wibble select * from wibble2 order by key; > drop table wibble2; > >5) Ok, as a reward for reading this far, here is the dumb thing. > >Be *VERY, VERY* careful what you name your database, especially the >extension > >For example, if you give all your databases the extension .sdb >(SQLite Database, nice name hey?) you discover that the SDB extension >is already associated with APPFIX PACKAGES. > >Now, here is the cute part, APPFIX is an executable/package that >Windows XP recognizes, and it will, (emphasis mine) *ADD THE DATABASE >TO THE SYSTEM RESTORE FUNCTIONALITY* > >This means, stay with me here, every time you write ANYTHING to the >database, the Windows XP system thinks a bloody executable has >changed and copies your ENTIRE 800 meg database to the system restore >directory.... > >I recommend something like DB or DAT. Thanks for mentioning the bits and pieces. I'll keep them handy. Nathan
