From DBF/CDX to SQLite
Moderator: Rathinagiri
From DBF/CDX to SQLite
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
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
- Rathinagiri
- Posts: 5471
- Joined: Tue Jul 29, 2008 6:30 pm
- DBs Used: MariaDB, SQLite, SQLCipher and MySQL
- Location: Sivakasi, India
- Contact:
Re: From DBF/CDX to SQLite
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.
If you want, I can give the link for easy UDF to deal with SQLite files.
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.
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.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.
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.
South or North HMG is worth.
...the possibilities are endless.
Re: From DBF/CDX to SQLite
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
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
- Rathinagiri
- Posts: 5471
- Joined: Tue Jul 29, 2008 6:30 pm
- DBs Used: MariaDB, SQLite, SQLCipher and MySQL
- Location: Sivakasi, India
- Contact:
Re: From DBF/CDX to SQLite
Yep. It is sql1.prg.
This is from SQLite doc.
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.
This is from SQLite doc.
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.
South or North HMG is worth.
...the possibilities are endless.
Re: From DBF/CDX to SQLite
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
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
Re: From DBF/CDX to SQLite
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.
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
Sudip
Re: From DBF/CDX to SQLite
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
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
Re: From DBF/CDX to SQLite
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:
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
Re: From DBF/CDX to SQLite
Hello Marek,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
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
Re: From DBF/CDX to SQLite
Hello Rathinagiri,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.
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