RDD SQL!

HMG Samples and Enhancements

Moderator: Rathinagiri

User avatar
Roberto Lopez
HMG Founder
Posts: 3980
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 27 times
Been thanked: 168 times

RDD SQL!

Post by Roberto Lopez » Thu Apr 02, 2009 2:31 am

Hi All,

Attached to this message is a zip file containing the following

- A 'Compile.bat' file replacement.
- A 'libmysqldll.a' file replacement.
- A new librddsql.a library file.
- A new 'libsddmy.a' library file.
- 'test.prg' sample (by Grigory Filatov) with minor modifications.

You must merge this files with your current 2.7.x installation.

Besides that, you'll need a copy of the 'libmysql.dll' file (available in any MySql distribution) located in the same folder as the sample.

And... of course, access to a MySql server :)

Thanks to Grigory Filatov for the sample.

Regards,

Roberto.
Attachments
rddsql.zip
(29.78 KiB) Downloaded 519 times
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

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

Post by Rathinagiri » Thu Apr 02, 2009 3:06 am

Thanks Roberto. Many are eager regarding this.
Roberto Lopez wrote: - A 'libmysqldll.a' file replacement.
Does this mean, TMySQLServer() would be permanently done away with in the future? :(
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
Roberto Lopez
HMG Founder
Posts: 3980
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 27 times
Been thanked: 168 times

Post by Roberto Lopez » Thu Apr 02, 2009 3:16 am

rathinagiri wrote:Thanks Roberto. Many are eager regarding this.
Roberto Lopez wrote: - A 'libmysqldll.a' file replacement.
Does this mean, TMySQLServer() would be permanently done away with in the future? :(
I'm not sure about understood correctly your question.

I've just tested TMySql samples prior to upload and it still working fine.

Should be not be 'interference' problems between RDDSQL and TMYSQL at all.

You may select which method you use to communicate with MySql.

If you mean another thing, please, let me know.


Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

User avatar
Roberto Lopez
HMG Founder
Posts: 3980
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 27 times
Been thanked: 168 times

Post by Roberto Lopez » Thu Apr 02, 2009 3:19 am

Mindaugas Kavaliauskas, wrote this on Harbour developers list, this text about RDDSQL. I hope it be useful for those experimenting with it.

Code: Select all

===================================================================
                     Simple SQL Interface for Harbour



1. Introduction

    Simple SQL interface implements accessing SQL query result via RDD
interface. It is not intended to be replacement for "transparent" move of
DBFCDX application to SQL world.

    I want to discuss this in more detail. Many current RDDs for SQL servers
(ex. SQLRDD from xHarbour.com) tries to make a feeling you are working with
DBF file, but not with SQL database. SQL server does not support many
features, ex. RECNO(), deleted flag, file locks, record locks. These RDDs
are emulating these features to make feeling of DBF. DELETED() function is
emulated by creating additional table columns to store delete flag. Some
"hidden system" tables are used to register locking operations and emulate
record and file locks in DBF style. The idea of SQL query is also lost. If
you do a simple loop

  DBUSEAREA(, "select * from my_table")
  DO WHILE ! EOF()
    somefunc( FIELD->some_sql_field )
    DBSKIP()
  ENDDO

RDD usualy will read SQL rows in portions, let's say 100 records per query.
So, hidden queries are generated. If you are using indexes these queries
are really complicated. Let's have index on FIELD1 + STR(FIELD2). A seek to
value cValue1 + STR(nValue2) will generate a query like:

  SELECT * FROM my_table
      WHERE (FIELD1 == cValue1 and FIELD2 >= nValue2) or FIELD1 > cValue1
      ORDER BY FIELD1, FIELD2, _RECNO
      LIMIT 100

After evaluation of first 100 cached records, next query will be generated:

  SELECT * FROM my_table
      WHERE (FIELD1 == cLastField1 and FIELD2 == nLastValue2 and _RECNO > nLastRecno) or
            (FIELD1 == cLastField1 and FIELD2 > nLastValue2) or
            FIELD1 > cLastValue1
      ORDER BY FIELD1, FIELD2, _RECNO
      LIMIT 100

To optimize these queries the SQL index expresion should be
"FIELD1,FIELD2,_RECNO", but not "FIELD1,FIELD2" as written in INDEX ON
command.

    "Simple SQL interface" is too long to repeat every time I want to
address this library. I'll also use acronym "SSI" to address it.

    The idea of SSI is different. It does not make hidden queries. All
queries should be made explicitly by programmer. SSI gives access to query
result via RDD interface, it does not tries to emulate DBF and be
"plug-and-play" solution for DBF to SQL migration. If you do

  DBUSEAREA(, "select * from my_table")

all query (it could contain millions of records!) will be cached.

    The features of SSI approach are:

- It's possible to access SQL database of other applications. Other
  applications usualy does not follow agreement of "plug-and-play" SQL drivers
  about additional DELETED column, _RECNO in the end of index expression, etc.
  Access of SQL database of other applications is sometimes not possible.

- It's query oriented. That means a simple DO WHILE ! EOF() loop will iterate
  each records once and only once. This is not true for "plug-and-play" SQL
  drivers, if indexing is used. Just like in the case of loop over DBF file.
  It is not guaranteed that all records are included! Yes! If key value of the
  first record in index is changed to be the last record in index during the
  phase of record processing, DO WHILE ! EOF() loop will iterate only this
  single records even if the database contains millions of records. Your sould
  do FLOCK() on DBF to guarantee the records are not changed. Do you use FLOCK()
  before readonly DO WHILE ! EOF() loops? :)



2. Architecture


              +-------------+
              |             |
              | SQLMIX RDD  |
              |             |
              +-------------+
                   |  ^
                   V  |
              +-------------+    +---------+
              |             |--->|         |
              | SQLBASE RDD |    |   SDD   |
              |             |<---|         |
              +-------------+    +---------+


    SQLBASE RDD implements basic functionality for accessing SQL query result
via RDD interface. This RDD could be used, if indexing of query result is not
necessary or all indexing is done by SQL server (by using ORDER BY clause).

    SQLMIX RDD implements indexing of query result. This indexing is not
related to SQL server ORDER BY clause. SQLMIX do indexing of the query on the
client side.

    SDD is acronym for Sql Database Driver. RDD is used to implement access
of different database formats like DBF, SDF, etc. SDD is used to implement
access of different SQL databases. Every SQL server (MySQL, PostgreSQL, etc.)
has a corresponding SDD. SDD driver implements a specific part of data
exchange interface between SQLBASE and SQL server.

    A few additional functions are also implemented, ex. HB_SQLCONNECT().
Usualy these functions are just a shorter version of corresponding RDDINFO()
call.



3. Modifying database

    SSI presents a query result via RDD interface and generates no hidden
SQL queries. So, how database can be changed? Does DBAPPEND() and FIELDPUT()
works, or is it readonly SQL interface?
    DBAPPEND(), FIELDPUT() and other similiar functions work on cached query
result, i.e. query can be appended by new rows and field values can be
changed, but SQL database is not changed. DBCREATE() function can also be
used to create an "empty query result" but no table is created on SQL server.
So, SSI can also be used as implementation of "array RDD".
    The programmer must call SQL command explicitly to modify SQL tables.
SSI provides a method to detect which cached rows was changed or appended.

=================================================================== 
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

User avatar
Roberto Lopez
HMG Founder
Posts: 3980
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 27 times
Been thanked: 168 times

Post by Roberto Lopez » Thu Apr 02, 2009 3:33 am

Please note this:
Roberto Lopez wrote:

Code: Select all

    DBAPPEND(), FIELDPUT() and other similiar functions work on cached query
result, i.e. query can be appended by new rows and field values can be
changed, but SQL database is not changed. 
According this, if you change the sample I've uploaded adding the 'EDIT' clause to Browse, you'll see the changes you've made to the table on your screen, but those changes will be never saved to the table on the server.

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

User avatar
Roberto Lopez
HMG Founder
Posts: 3980
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 27 times
Been thanked: 168 times

Post by Roberto Lopez » Thu Apr 02, 2009 3:49 am

Roberto Lopez wrote:

Code: Select all

SSI provides a method to detect which cached rows was changed or appended.
If someone discovers how to do that, please, let us know.

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

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

Post by Rathinagiri » Thu Apr 02, 2009 4:00 am

Roberto Lopez wrote: You may select which method you use to communicate with MySql.
Thank you. This is what I wanted.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
sudip
Posts: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

Post by sudip » Thu Apr 02, 2009 5:29 am

Hello Roberto,

Thank you very much! The sample runs fine. But can't save changes (as specified in your message).

I found something RDDI_AFFECTEDROWS, which may be used as RDDINFO(RDDI_AFFECTEDROWS, ...)

I am tried RDDINFO(RDDI_AFFECTEDROWS, .T.), but nothing happened :)

Searched through net. But no solution found till now.

With best regards.

Sudip
With best regards,
Sudip

User avatar
Roberto Lopez
HMG Founder
Posts: 3980
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 27 times
Been thanked: 168 times

Post by Roberto Lopez » Thu Apr 02, 2009 11:25 am

sudip wrote:Hello Roberto,

Thank you very much! The sample runs fine. But can't save changes (as specified in your message).

I found something RDDI_AFFECTEDROWS, which may be used as RDDINFO(RDDI_AFFECTEDROWS, ...)

I am tried RDDINFO(RDDI_AFFECTEDROWS, .T.), but nothing happened :)

Searched through net. But no solution found till now.

With best regards.

Sudip
I'm not sure, but I guess that it should return the number of affected rows.

I've wrote a private message to the rdd author (Mindaugas Kavaliauskas) asking about that.

I'll post here any info regarding that.

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

User avatar
sudip
Posts: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

Post by sudip » Thu Apr 02, 2009 11:43 am

Hello Roberto,
Roberto wrote:
I'm not sure, but I guess that it should return the number of affected rows.

I've wrote a private message to the rdd author (Mindaugas Kavaliauskas) asking about that.

I'll post here any info regarding that.
Yes you are absolutely correct. RDDI_AFFECTEDROWS returns number off affected rows, I checked it. In the sample it returns 7, which is the number of rows affected in the last query.
Thanks a lot!
Ok, I shall check this thread time to time :)

With best regards.

Sudip
With best regards,
Sudip

Post Reply