From DBF/CDX to SQLite

Moderator: Rathinagiri

User avatar
Hazael
Posts: 109
Joined: Thu Jun 24, 2010 11:37 am
Location: France

From DBF/CDX to SQLite

Post by Hazael » Thu Jun 24, 2010 1:09 pm

Hello,

I am trying to move from DBF/CDX to SQLite because of the many features and advantages I learned from here: http://www.sqlite.org/features.html
I was studing previous interesting posts with real examples about this subject and I am creating a simple program in CUI mode (Character User Interface) using Harbour + MingW32 + GTWVT + SQLite (all last versions available).
I could create a SQLite database, open it, do basic search with SELECT but I would like to do a equivalent to a BROWSE() from DBF/CDX.
Please keep in mind I never used SQLite before (nor any SQL like database) and my assumptions may be wrong (hopefully). Correct me, please.
The information I could collect is that I should use a SQL query to populate an ARRAY and them browse that array (maybe with ACHOICE?).
Other idea would be to use HBMEMIO to create a "DBF on memory", populate it and browse it.
Both ideas seem not really good to me because its kind of risky to depend on computers memory in special for big databases with zillions of records not to say other factors out of my control just to mention.
Any idea how to solve this? Any suggestions?
Please note that this is not a HMG specific question but since I found the most interesting information around HMG forums I gave a try to find more... Simple example code would be valuable too. (in special if it could be in simple text mode).
I am trying to make SQLite a reality for my application but I am not sure it will really replace DBF/CDX with advantage. Just wondering.
Thanks for any help.

Qatan
Harbour | GTWVT | MingW | Visual Studio Code

User avatar
Rathinagiri
Posts: 5181
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 141 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Thu Jun 24, 2010 2:45 pm

Hi Qatan,

IMHO, SQLite can't replace DBF/CDX as such. Both of the formats have their own merits and demerits.

Through SQLite we can have,

1. Single file database with multiple tables
2. Networking (useful for a small network only)
3. Data manipulation thru SQL queries
4. Speed

The problem is we don't have record level locking.

In the case of SQLite, we give request to the database connection and get the results (as an array). Then we can browse.
its kind of risky to depend on computers memory in special for big databases with zillions of records not to say other factors out of my control just to mention.
Since we can use LIMIT queries, we can just see a particular number of records for faster and optimal performance. Further, it will not affect the database file even the power is out.

If you want, I can give the link for easy UDF to deal with SQLite files.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
Hazael
Posts: 109
Joined: Thu Jun 24, 2010 11:37 am
Location: France

Post by Hazael » Thu Jun 24, 2010 3:23 pm

Hello Rathinagiri,

Thank you for helping.
I am tending to use DBF/CDX because I feel more confortable and more secure with it for this new project.
I think you are talking about sql1.prg, right? If so I already have it from your good example (Ware-Soft). If not, of course I will be interested.
Do you have an example of this LIMIT query while browsing? Seems to be an interesting idea.

Regards,

Qatan
Harbour | GTWVT | MingW | Visual Studio Code

User avatar
Rathinagiri
Posts: 5181
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 141 times
Been thanked: 131 times
Contact:

Post by Rathinagiri » Thu Jun 24, 2010 3:38 pm

Yep. It is sql1.prg. :)

This is from SQLite doc.

Image

The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradiction is intentional - it maximizes compatibility with legacy SQL database systems.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
Hazael
Posts: 109
Joined: Thu Jun 24, 2010 11:37 am
Location: France

Post by Hazael » Thu Jun 24, 2010 10:35 pm

Well, I am not sure I understand it.
I think harbour code with a simple example of it while browsing would help to understand.
Let's see... in a database with 800,000 records if I limit the query to get only 1,000 records then what I will have to show (and browse data) are only the first 1,000 records, right? And how could I show the other 799,000 records (and browse them)? With DBF and Browse() when I press Ctrl+PgUp I go to the first record and when I press Ctrl+PgDw I go to last one. How it would work in real application with this LIMIT context?
Thanks for your patience.

Qatan
Harbour | GTWVT | MingW | Visual Studio Code

User avatar
sudip
Posts: 1444
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 4 times

Post by sudip » Fri Jun 25, 2010 4:27 am

Hello Qatan,

DBFCDX may be a better choice for you. :)

Please check \hmg\Samples\RDD.SQL\MySql\demo.prg for SQLMIX RDD with MySql.

There is another example of SQLMIX RDD with Sqlite in Minigui Extended. This example can also be used with HMG with some minor modifications.
With best regards,
Sudip

User avatar
mol
Posts: 2879
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 136 times
Been thanked: 72 times
Contact:

Post by mol » Fri Jun 25, 2010 7:52 am

Hi Qatan!
I think, if you wanna see next records, you can use phrase OFFSET - you will get next portion of information. It could be done when user reach end of table while browsing results.
Marek

User avatar
Hazael
Posts: 109
Joined: Thu Jun 24, 2010 11:37 am
Location: France

Post by Hazael » Wed Jun 30, 2010 12:54 pm

Thank you all for your help!

I found that SQLMIX works well and may be a good choice.
Marek and Ratinaghiri's idea of OFFSET is good too. It will require to create a special tbrowse() do deal with it. Seems that I will have to do the hard job of making it but you already gave the first steps (Thank you).

Ratinaghiri, could you please document (comment) more the sql1.prg? I am sure it would be usefull for others too.

There is also an option to browse array. This may be a good option too. Please look this example code in CUI mode:


Code: Select all


/******************************************************************
* Procedure: BRW_MATRIZ()
*
* Description: Creates a browse with an array in place of DBF file
*
*              You can do a progressive search in any column 
*              typing the text on any column. To stop it just
*              press any arrow key.
*
* Designed for Clipper 5.x and RtLink but can compile with Harbour
*
* Originaly developed by Paulo Cesar Toledo (Clipper On Line)
* Site: www.pctoledo.com.br
* e-mail: toledo@expressnet.com.br
*
* Hint: For better results execute in a folder with many files
* 
* Little modifications and tranlated to English by Qatan
*
******************************************************************/
#Include "INKEY.CH"
setmode(25,80)
clear
PRIVATE aMatriz1
SET DATE BRITISH
SET CENTURY ON
SET SCOREBOARD OFF
aMatriz1:=DIRECTORY("*.*")  // create array with file information
ASORT(aMatriz1,,,{|x,y| x[1]<y[1]})
J_ANELA(1,12,22,68,"B+/B","N/B","W/B",'['+CURDIR()+']') // 
BRW_MATRIZ(aMatriz1)
retu


PROC BRW_MATRIZ
PARAMETERS brw_mat
brw:=TBrowseNew(4,13,21,67)
private n:= 1, ntot:=len(brw_mat)

brw:colorspec := "W+/B,B/W,W+/R,W+/BG,GR+/GR"
brw:headsep:=chr(205)+chr(209)+chr(205)
brw:colsep:=" "+chr(179)+" "
brw:gotopblock({|| n:= 1})
brw:gobottomblock({|| n:=ntot})
brw:skipblock({|_1| (n:= n + _1, iif(n < 1, _1:= _1 - n + (n:= ;
   1), iif(n > ntot, _1:= _1 - (n - (n:= ;
   ntot)), Nil)), _1)})


brw:addcolumn(tbcolumnnew("File",{|| TRANS(brw_mat[n,1],"@!")}))
brw:getcolumn(1):width := 12
brw:getcolumn(1):cargo := {"@!"}
brw:addcolumn(tbcolumnnew("Size",{|| TRANS(brw_mat[n,2],"@E 99,999,999")}))
brw:getcolumn(2):width := 10
brw:getcolumn(2):cargo := {"@E 99,999,999"}
brw:addcolumn(tbcolumnnew("Date",{|| TRANS(brw_mat[n,3],"@D")}))
brw:getcolumn(3):width := 10
brw:getcolumn(3):cargo := {"@!"}
brw:addcolumn(tbcolumnnew("Time",{|| TRANS(brw_mat[n,4],"@!")}))
brw:getcolumn(4):width := 8
brw:getcolumn(4):cargo := {"@!"}
volta_db=.t.
st_pesq:=""
idx=1
DO WHILE volta_db
   SETCOLOR("W+/B")
   SET CURSO OFF
   e=1
   DO WHILE !brw:stabilize() .AND. NEXTKEY()=0
   ENDD
   READINSERT(.f.)
   x_ = COL() ; y_ = ROW()
   IF LEN(st_pesq)>0
      cp_:=brw_mat[n,brw:colpos]
      cp_masc:=brw:getcolumn(brw:colpos):cargo()[1]
      IF LEN(st_pesq) <= brw:getcolumn(brw:colpos):width
         i_=SETCOLOR("GR+/GR")
         t=1
         l_m=TRAN(cp_,cp_masc)
         FOR j=1 TO LEN(l_m)
            IF SUBS(st_pesq,t,1)=SUBS(l_m,j,1)
               t++
               IF t>LEN(st_pesq)
                  @ y_,x_ SAY LEFT(l_m,j)
                  EXIT
               ENDI
            ENDI
         NEXT
         SETCOLOR(i_)
      ENDI
   ENDI
   
   tecl_p=INKEY(0)
   carac_ = UPPER(CHR(tecl_p))
   IF tecl_p>31
         st_p=st_pesq+carac_
         cp_:=brw_mat[n,brw:colpos]
         tp_cp:=VALTYPE(cp_)
         IF idx!=brw:colpos
          IF tp_cp="D"
            ASORT(brw_mat,,,{|x,y| DTOS(x[brw:colpos])<DTOS(y[brw:colpos])})
          ELSEIF tp_cp="N"
            ASORT(brw_mat,,,{|x,y| STR(x[brw:colpos])<STR(y[brw:colpos])})
          ELSE
            ASORT(brw_mat,,,{|x,y| x[brw:colpos]<y[brw:colpos]})
          ENDIF
          idx:=brw:colpos
         ENDIF
         brw:RefreshAll()
         IF tp_cp="D"
           op=ASCAN(brw_mat,{|e| st_p==LEFT(DTOC(e[brw:colpos]),len(st_p))})
         ELSEIF tp_cp="N"
           op=ASCAN(brw_mat,{|e| st_p==LEFT(LTRIM(STR(e[brw:colpos])),len(st_p))})         
         ELSE
           op=ASCAN(brw_mat,{|e| st_p==LEFT(e[brw:colpos],len(st_p))})         
         ENDIF
         IF op>0
          st_pesq=st_p
          n:=op
          brw:rowpos=1
          brw:configure()
         ELSE
          TONE(400,2)
         ENDIF
         LOOP
   ENDIF
   SET CURSO ON
   brw:dehilite()
   DO CASE
         CASE tecl_p = K_ESC
            volta_db=.f.
         CASE tecl_p = K_UP
              brw:up()
         CASE tecl_p = K_DOWN
            brw:down()
         CASE tecl_p = K_RIGHT
            brw:right()
         CASE tecl_p = K_LEFT
            brw:left()
         CASE tecl_p = K_HOME   
            brw:home()
         CASE tecl_p = K_END   
            brw:end()
         CASE tecl_p = K_PGUP   
            brw:pageup()
         CASE tecl_p = K_PGDN   
            brw:pagedown()
         CASE tecl_p = K_CTRL_PGDN
            brw:gobottom()
         CASE tecl_p = K_CTRL_PGUP
            brw:gotop()
         CASE tecl_p = K_CTRL_END   
            brw:panend()
         CASE tecl_p = K_CTRL_HOME   
            brw:panhome()
         CASE tecl_p = K_CTRL_LEFT   
            brw:panleft()
         CASE tecl_p = K_CTRL_RIGHT   
            brw:panright()
            

   ENDC
   st_pesq=""
ENDD
SET CURSO ON
setcolor('')
cls
RETU

FUNCTION J_ANELA
PARAMETERS L1,C1,L2,C2,C_OR1,C_OR2,C_OR3,T_ITULO
CORR=SETCOLOR()
SET COLOR TO &C_OR1
@ L1,C1 CLEAR TO L2,C2
@ L1,C1 SAY 'Ú'+REPLICATE(CHR(196),C2-C1-1)+'¿'
SET COLOR TO &C_OR3
@ L1,(80-LEN(T_ITULO))/2 SAY T_ITULO
FOR A=L1+1 TO L2-1
   SET COLOR TO &C_OR1
   @ A,C1 SAY '³'
   SET COLOR TO &C_OR2
   @ A,C2 SAY '³'
NEXT A
SET COLOR TO &C_OR2
@ L2,C1 SAY 'À'+REPLICATE(CHR(196),C2-C1-1)+'Ù'
SETCOLOR(CORR)
RETURN

Harbour | GTWVT | MingW | Visual Studio Code

User avatar
Hazael
Posts: 109
Joined: Thu Jun 24, 2010 11:37 am
Location: France

Post by Hazael » Wed Jun 30, 2010 3:44 pm

mol wrote:Hi Qatan!
I think, if you wanna see next records, you can use phrase OFFSET - you will get next portion of information. It could be done when user reach end of table while browsing results.
Marek
Hello Marek,

Would you mind to provide a code example of this?
It could be simple in native Harbour.
Your idea seems interesting and may be a good solution to give that effect of browsing records like in DBFCDX.

Qatan
Harbour | GTWVT | MingW | Visual Studio Code

User avatar
Hazael
Posts: 109
Joined: Thu Jun 24, 2010 11:37 am
Location: France

Post by Hazael » Fri Jul 02, 2010 7:51 pm

rathinagiri wrote:Yep. It is sql1.prg. :)

The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradiction is intentional - it maximizes compatibility with legacy SQL database systems.
Hello Rathinagiri,

No news from you about an example, yet.
I belive you are too busy but whenever you have an oportunity and, of course, inspiration to provide a example using OFFSET in a browse. I think in simple CUI mode would be easier to understand (could be GUI/HMG if you prefer). It would be valuable to learn.

Thanks

Qatan
Harbour | GTWVT | MingW | Visual Studio Code

Post Reply