MySql, MariaDB and PostgeSQL are FWMARIADB's compatible?

[dutch]

Dear Mr.Rao&Antonio,

Is it possible the make FWMARIADB to use all (MySql, MariaDB and PostgeSQL) with one code in the future?
It will be useful for user to choose RDBMS?

Regards,
Dutch

[nageswaragunupudi]

With FWMARIADB we can work with either MySql server or MariaDB server with the same code and exe.

I started working with PostGreSQL now. Aim is what you asked. But it is too early for us to promise anything.

[dutch]

Dear Mr.Rao,

Thank you so much, it is really good news today.
Dutch

[nageswaragunupudi]

But please tell me why do you want postgresql?
Isn't mariadb enough?

[fafi]

But please tell me why do you want postgresql?
Isn't mariadb enough?

Mr. Rao..

a litle faster than mysql for Insert command

Please test here :

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "dbstruct.ch"
#include "mysql.ch"

#include "DbStruct.ch"

#define _STRU_FIELDNAME             1
#define _STRU_FIELDTYPE             2
#define _STRU_FIELDLEN              3
#define _STRU_FIELDDEC              4
#define _STRU_TABLE                 5
#define _STRU_TABLECOL              6

REQUEST DBFNTX
REQUEST DBFCDX
REQUEST DBFFPT
REQUEST DBFDBT

#define DB_ALIAS                        1
#define DB_FILE                         2
#define DB_QUERY                        3
#define DB_ROW                          4
#define DB_FETCH                        5

function main()
     
   mysqlservername := "192.168.1.2"
   username := "simrs"
   password := "8091016"
     
     
    oserver:= TMySQLServer():New(mysqlservername,username,password)
     
   IF oServer:NetErr()
      ?oServer:Error()
   ENDIF
   
   oServer:SelectDB( "blu" )
   
   IF oServer:NetErr()
      ?oServer:Error()
   ENDIF
   
   
   cFileORIG := "customer.dbf"
   cFileDEST := "mst_customer"  
   lAppend := .t.
   
   ConvertToSQL(cFileORIG,cFileDEST,lAppend,oServer)
   
   oserver:destroy()
     
return


static function ConvertToSQL(cFileORIG,cFileDEST,lAppend,oServer)

   local cComm, apCode, cOut
   local nErr, nPos
   LOCAL vEmp := {}
   Local nCnn, s,oSql
   local aReturn := {}
   local aReturnX := {}
   cFileORIG  := lower(alltrim(cFileORIG))
   cFileDEST  := lower(alltrim(cFileDEST))

   define dialog oDlgStock from 1,1 to 40,400 pixel style nOR( WS_CAPTION ) title "Tunggu Sebentar"
   activate dialog oDlgStock centered nowait
   
   oDlgStock:cTitle := cFileDEST
   SysRefresh()

   dbCloseAll()
   cSql := "DROP TABLE IF EXISTS "+cFileDEST
   oQuery := oServer:Query( cSql )
   

   IF oQuery:NetErr()
      ?oQuery:Error()
   ENDIF
   oQuery:Destroy()
   

   dbCloseAll()
   use (cFileORIG) new shared alias orig

 
 
  aStruct := orig->(DbStruct())
 
  cField := ""
 
  for i := 1 to len(aStruct)
     cFieldName := alltrim(aStruct[i][DBS_NAME])
     if lower(cFieldName) == "index"
         cFieldName := "XINDEX"
     endif
     cType      := aStruct[i][DBS_TYPE]
     cLen       := alltrim(Str( aStruct[i][DBS_LEN ],  3 ))
     cDec       := alltrim(Str( aStruct[i][DBS_DEC ],  3 ))
     cOke := ""
     
     
     if cType == "C"
        cOke := "  "+cFieldName+" CHAR ("+cLen+") , "
     endif
     
     if cType == "M"
        cOke := "  "+cFieldName+" CHAR (150) , "
     endif
     
     if cType == "N"
        if val(cDec) == 0
           cOke := "  "+cFieldName+" NUMERIC ( "+cLen+", 0) , "
        else
           cOke := "  "+cFieldName+" NUMERIC ( "+cLen+", 2) , "
        endif
     endif
     
     if cType == "D"
        cOke := "  "+cFieldName+" DATE, "
     endif
     
     if cType == "L"
        cOke := "  "+cFieldName+" BOOLEAN, "
     endif
     cField += cOke
  next
 
  cField := upper(cField)

   cSql := "CREATE TABLE "+cFileDEST+" ( recno_key    serial primary key, "
   
   
   cSQL += cField
   cSQL += " edited_date DATE, edited_time CHAR(8) "
   
   
     cSQL += " );"
   
   
   
        ?cSql
   
   
     oQuery := oServer:Query( cSql )
     IF oQuery:NetErr()
      ?oQuery:Error()
   ENDIF
   oQuery:Destroy()
       
   

if lAppend
   dbCloseAll()
   use (cFileORIG) new shared alias orig
   
           nMulai  := 0
           nPersen := 0
           nRecord := orig->(lastrec())
           
           
           orig->(dbGotop())  
           do while !orig->(eof())
           
              ++nMulai
              nPersen := ( nMulai / nRecord ) * 100
              oDlgStock:cTitle := cFileDEST +" "+str(nMulai,12)+"/"+str(nRecord,12)+"="+str(nPersen,12)+"%"
              SysRefresh()
              cFieldJalan := alltrim(orig->(FieldName(1)))
              cDatanya := orig->&cFieldJalan
              if valtype(cDatanya) == "N"
                 cDatanya := alltrim(str(cDatanya,14,2))
              else
                 cDatanya := "'"+upper(alltrim(cDatanya))+"'"
              endif
             
              cSQL := "INSERT INTO "+alltrim(cFileDEST)+" ( "+cFieldJalan+" ) VALUES (  "+cDatanya+"  )"
             
     oQuery := oServer:Query( cSql )
     IF oQuery:NetErr()
      ?oQuery:Error()
   ENDIF
   oQuery:Destroy()
     
         
         for x := 2 to orig->(fcount())
                     cFieldJalan := alltrim(orig->(FieldName(x)))
                     cDatanya := orig->&cFieldJalan
                     
                          if valtype(cDatanya) == "N"
                         cDatanya := alltrim(str(cDatanya,14,2))
                      endif
                     
                      if valtype(cDatanya) == "D"
                         cTahun   := strzero(year(cDatanya),4)
                         cBulan   := strzero(month(cDatanya),2)
                         cTgl     := strzero(day(cDatanya),2)
                         cDatanya := cTahun+"-"+cBulan+"-"+cTgl
                      endif
                     
                      if valtype(cDatanya) == "L"
                         if cDatanya
                            cDatanya := "1"
                         else
                            cDatanya := "0"  
                         endif
                      endif
                         
                      if valtype(cDatanya) == "C"
                         cChar := ""
                         for xx := 1 to len(cDatanya)
                            cOke := subs(cDatanya,xx,1)
                            if cOke == "'"
                               cOke := ""
                            endif
                            cChar += cOke
                         next
                         cDatanya := "'"+upper(alltrim(cChar))+"'"
                      endif
                     
                      if valtype(cDatanya) == "M"
                         cDatanya := "'"+upper(alltrim(cDatanya))+"'"
                      endif
                     
                        cSql :=  "UPDATE "+alltrim(cFileDEST)+" SET "+cFieldJalan+" = "+cDatanya+" WHERE recno_key = "+alltrim(str(nMulai,12))
                       
                                 oQuery := oServer:Query( cSql )
     IF oQuery:NetErr()
      ?oQuery:Error()
   ENDIF
   oQuery:Destroy()
                 
                 next            
           
             orig->(dbSkip())
             
           enddo
         
       dbCloseAll()
   

endif
**********************

       
oDlgStock:End()

return nil   



 

Fafi

[nageswaragunupudi]

a litle faster than mysql for Insert command

I am not sure.
Even then that alone can not be a reason to give up mysql. There should be other solid reasons.

Insert speeds depend on many factors including our database design. Even then it is considered a serious issue by organizations who have a hit rate of several transactions per second. Even there, there are several ways of fine tuning server and database performance.

When we are using extremely primitive libraries like tmysql, hbpgsql there is no point in discussing these issues.

[Marcelo Via Giglio]

Hola,

only a point of view, why PG, no instead of

The lincence
The features
The Reliablility
The Robusness

There is a company EnterpriseDB (https://www.enterprisedb.com) who are developing an Oracle DataBase clone based on Postgres, it means to much.

Postgres is a RDMS is a OODB too, this can work like NoSql, GIS, .....

Yes, some people can say the same from MySQL or MariaDB, at the end is a personal election, but I think that PG borned with the idea to be a enterprise database, MySql no, then the pression obligate to add Innodb to resolve problems with integrity.... I don't want to say which is better, is only an opinion.

But I'm happy that FW has support to PG

Thanks and best regards

Marcelo Vía

[nageswaragunupudi]

Mr Marcelo

Thank you.

at the end is a personal election

I agree.

But I'm happy that FW has support to PG

We are only beginning by providing better compatibility with (x)Harbour's libraries hbpgsql and pgsql and also provide enhanced functionality. We are still far from providing our own library like our mariadb.

I have no opinion or bias against or in favour of PG. I am only trying to learn from other knowledgeable colleagues why do they consider PG to be better than MySql/MariaDB.

This also gives us an idea how many users will be interested if we invest our time to provide a better featured libraray for PG.

Licence: I think with Mariadb, licence is not an issue
Features: This is what I am interested in learning what are those extra features that are attracting our FWH users. This also gives us an idea where to focus more in our development.

NoSql and GIS are also available with MariaDB. I agree I did not compare the features in detail.

Though I am sure we are never going to use it, MariaDB is also coming up with Column Store in the lines of Oracle, which greatly enhances OLAP performance without sacrificing OLTP performance. All this is irrelevant for our volumes of data.

[dutch]

Dear Mr.Rao,

But please tell me why do you want postgresql?
Isn't mariadb enough?

I just think that, if FWMARIADB is compatible with all. It will be great and PostgreSQL is a good alternative choice, if the modification is not too complicated.

My prefer choice is still MySql and MariaDB.

Just idea and my opinion.

Thanks&regards,
Dutch

[sygecom]

PostgreSQL
+1