SQLite 2.8.2

Manolo · Post by **Manolo** » Wed May 21, 2003 4:13 pm

Hi all,
The latest SQLite version is 2.8.2 created on 2003/05/17 17:35:13 UTC

Download from: http://www.hwaci.com/sw/sqlite/

Regards,
Manolo

El_Choni · Post by **El_Choni** » Wed May 21, 2003 4:17 pm

Oh, no!

gnozal · Post by **gnozal** » Wed May 21, 2003 4:39 pm

Thanks.
Is El Choni's excellent library SQLite_132 still working with this version ?

Manolo · Post by **Manolo** » Wed May 21, 2003 4:54 pm

gnozal wrote:Thanks.
Is El Choni's excellent library SQLite_132 still working with this version ?

Yes, no problem. Only bit changes, but not affect to the library.

Code: Select all

2003 May 17 (2.8.2) 

Fix a problem that will corrupt the database file if you drop a table from the main database that has a TEMP index. 

2003 May 16 (2.8.1) 

Reactivated the VACUUM command that reclaims unused disk space in a database file. 
Added the ATTACH and DETACH commands to allow interacting with multiple database files at the same time. 
Added support for TEMP triggers and indices. 
Added support for in-memory databases. 
Removed the experimental sqlite_open_aux_file(). Its function is subsumed in the new ATTACH command. 
The precedence order for ON CONFLICT clauses was changed so that ON CONFLICT clauses on BEGIN statements have a higher precedence than ON CONFLICT clauses on constraints. 
Many, many bug fixes and compatibility enhancements. 

2003 May 17 (2.8.2) 

Fix a problem that will corrupt the database file if you drop a table from the main database that has a TEMP index.

Regards,
Manolo

Saboteur · Post by **Saboteur** » Wed May 21, 2003 6:06 pm

Mmmm

I want it.

I coded a CD catalog with SQLite 2.8.? and library SQLite_133 and it works perfect. But I have to review it, because first version, with Access was faster (I don`t know why). Anyway, I'm very happy with my 62 CD's database using all days.

Thanks... El_Choni

El_Choni · Post by **El_Choni** » Wed May 21, 2003 6:14 pm

Saboteur: can you show some code which is now slower? Maybe performance can be improved either by changing your code or mine (in the lib).

Saboteur · Post by **Saboteur** » Wed May 21, 2003 9:04 pm

No no no... I think the problem is database, I tried with index fields, but speed is same, and size is twice. Perhaps I have to make views or something. I get 10 seconds in each search. The search time is getting in SQLiteGetTable command.

If you want to try, click this:
http://www.arrakis.es/~saboteur/temp/cddb.zip
(comments are in spanish, sorry... not you, El_Choni

)
Ejem: type 'videos' in search field, and wait. If you look at database directory, you'll see SQLite works.

HPW · Post by **HPW** » Wed May 21, 2003 9:36 pm

D. Richard Hipp wrote on yahoo:

In that sense, please consider version 2.8.2 to be beta software.

This is meant for the new features I think.

Manolo · Post by **Manolo** » Wed May 21, 2003 9:43 pm

HPW wrote:
D. Richard Hipp wrote on yahoo:

In that sense, please consider version 2.8.2 to be beta software.
This is meant for the new features I think.

Yes, but all GNU`s programs are beta software, inclusive Linux and MySql. Only M$ and colegues wroking in end release. (Full of bugs "features", hehe).

Manolo

dige · Post by **dige** » Thu May 22, 2003 9:31 am

Manolo wrote:
gnozal wrote:Thanks.
Is El Choni's excellent library SQLite_132 still working with this version ?
Yes, no problem. Only bit changes, but not affect to the library.

Regards,
Manolo

Sorry, but there is is still a little bug in it.
For example, this code does not work correctly:

Code: Select all

SQL.s = "SELECT * FROM ..."
SQLiteGetTable ( #DBIndex, SQL.s )
Rows = SQLiteRows ()
txt.s = "[1. string] " + SQL_Data ( 1, 0 ) + " [2.string]"

=> "[2.string]" will not be added...

Whenever I use SQL_Data (), I cant add strings afterwards.
I have to add it in the next line, like this:

Code: Select all

txt.s = "[1. string] " + SQL_Data ( 1, 0 ) 
txt.s + " [2.string]"

cya dige

El_Choni · Post by **El_Choni** » Thu May 22, 2003 2:00 pm

It was a bug, dige, thanks a lot for the report. New version with some other minor changes has been sent to the resources site and should be available in 24 hours:

http://www.reelmediaproductions.com/pb

Bye,

PS: Manolo, how do you do? We haven't talked lately. It'll soon be finished, I think.

El_Choni · Post by **El_Choni** » Thu May 22, 2003 3:01 pm

Saboteur, I'd suggest to change some little things in your code to make it work faster. The things that slow it most are function calls in loops, and some of them are not needed. Try changing (in GetNombreCDS() procedure):

Code: Select all

  If SQLiteGetTable(SQL)=#SQLITE_OK
    If SQLiteRows()>0
      For f=1 To SQLiteRows()
        AddElement(NombreCDS())
        NombreCDS()\idcd=Val(SQLiteField(f,"idcd"))
        NombreCDS()\cd=SQLiteField(f,"cd")
        AddGadgetItem(#gdNcds, -1, NombreCDS()\cd)
      Next f
    EndIf
    SQLiteRemoveData()
  EndIf

to:

Code: Select all

  If SQLiteGetTable(SQL)=#SQLITE_OK
    For i=0 To SQLiteCols()
      If SQLiteData(0, i) = "idcd"
        idcdIndex = i
      EndIf
      If SQLiteData(0, i) = "cd"
        cdIndex = i
      EndIf
    Next i
    rows = SQLiteRows()
    If rows
      For f=1 To rows
        AddElement(NombreCDS())
        NombreCDS()\idcd=Val(SQLiteData(f, idcdIndex))
        NombreCDS()\cd=SQLiteData(f, cdIndex)
        AddGadgetItem(#gdNcds, -1, NombreCDS()\cd)
      Next f
    EndIf
    SQLiteRemoveData()
  EndIf

I now you like using SQLiteField() instead of SQLiteData(), but I warn you it'll be slower in a big db like you're using, because the given header must be looked up -each loop iteration!- and compared with table headers, etc. Better use it out of the loop, but it's up to you.

Also: use GetTextExtentPoint32_() to find out text pixel width instead of hardcoding gadget and window width, the textgadget at the right is clipped in my computer. You can use this if you want and resize window/gadgets accordingly (I miss this in PB):

Code: Select all

Procedure TextWidth(text$)
  GetTextExtentPoint32_(GetDC_(0), text$, Len(text$), sz.SIZE)
  ProcedureReturn sz\cx
EndProcedure

Saboteur · Post by **Saboteur** » Thu May 22, 2003 4:17 pm

Sorry, but that not speed up the search. The problem is SQLiteGetTable is slow (but remember, the table hasn't index fields). Have you tried a little program that only open a big database?
.... wait 8O I can't believe it ...

I tried this:

Code: Select all

#SQLITE_OK = 0
If InitSQLite()
  If SQLiteOpen("cddb.db")
    tm.f=GetTickCount_()
    If SQLiteGetTable("select * from archivos")=#SQLITE_OK
      tm=(GetTickCount_()-tm)/1000
      MessageRequester("Base de datos abierta","Tiempo: "+StrF(tm)+Chr(10)+Str(SQLiteCols()) + " x " + Str(SQLiteRows()),0)
      SQLiteRemoveData()
    EndIf
  EndIf
EndIf

and speed is 0.33..... I'll look slowly later

Ok... when change SQL to "select * from cds inner join archivos on cds.idcd=archivos.idcd order by cd, archivo", time go to 5 seconds...
Inner join and order, slow the access. I've to read manual again.

El_Choni · Post by **El_Choni** » Thu May 22, 2003 4:55 pm

I see... Well, it makes sense that sqlite.dll takes longer if you ask for some operations to be done with the data. Ok, maybe not that longer...

Saboteur · Post by **Saboteur** » Sun Jun 01, 2003 12:27 am

I found one interesting thing while playing with SQL.

With next command, I get 10 seconds to search:
SQL.s="select * from cds, archivos where cds.idcd=archivos.idcd and archivo like '%"+c+"%'"

But, changing the order of table, I get 0,13 seconds 8O ... yes
SQL.s="select * from archivos,cds where archivos.idcd=cds.idcd and archivo like '%"+c+"%'"

I see it's important to select the correct order of fields

Go back to code...