PDA

View Full Version : Using Lianja as a NoSQL database



pablokuster@gmail.com
2012-10-13, 05:10
Our request is articulated in two functionalities:

1.- A system class with the required methods properties and events to connect to a web server using REST protocol (SOAP is less important) through an http request and have the events oriented to control of request session to the complete download of the response or XML, may be html too, document.

2.- A XML adapter class were we can describe the binding between a column table and a data inside of the XML document, opportunely mapped with xpath, that can describe to kinds of relations 1:1 or 1:n, the second one, to load data contained in childs nodes of the specified parent through xpath.

Tia

barrymavin
2012-10-13, 05:54
There is already a function getUrl( cUrl [,nTimeout [, oHeaders] ] ) that can be used to read from web services into a string. This is currently limited to 64k in size.

The headers is an associative array (same as those in PHP which we added into the VFP syntax) that lets you specify the http headers to send e.g. authentication.

Let me provide a better usage scenario.



declare oHeaders[]
oHeaders["username"] = "barry"



Associative arrays can also be initialized when created too like this. (borrowed from PHP syntax and added into VFP).



oHeaders = array("name" => "barry", "age" => 10)


The values assigned to the "properties" can themselves be nested dynamic arrays or static arrays.



oheaders = array("name" => "barry", "age" => 10, "table" => array(10,20,30))



You can inspect associative arrays (and static arrays) like this in the console:



? oHeaders



Which will display:



Dynarray (refcnt=1)
(
[name] => barry
[age] => 10
)


or in the latter case:



Dynarray (refcnt=1)
(
[name] => barry
[age] => 10
[table] => Dynarray (refcnt=1)
(
[1] => 10
[2] => 20
[3] => 30
)
)


Now you can reference the elements just like objects:



? oHeaders.table[2]


Which will display:



20


Notice how objects and arrays are reference counted just as they are in .NET (and automatically garbage collected when not referenced anywhere).

Once you have an "object" or "associative array" or "static array" you can encode it as JSON like this:



? json_encode(oHeaders)


Which will display:



{"name":"barry","age":10,"table":{"00000001":10,"00000002":20,"00000003":30}}


You can decode JSON like this:



? json_decode( json_encode(oHeaders) )


which will display:



Object (refcnt=1)
(
[name] => barry
[age] => 10
[table] => Dynarray (refcnt=1)
(
[1] => 10
[2] => 20
[3] => 30
)
)


Which you can then use to update records in tables using the NAME clause.

So in a nutshell, JSON is well supported for both import and export and for passing arguments to web services and receiving back the result in JSON format. This is particularly useful when working with WebViewWidgets.

pablokuster@gmail.com
2012-10-13, 06:09
Perfect this is a very good alternative.

Thanks.

barrymavin
2012-10-13, 06:11
As well as working with JSON. You can work with XML and treat it as (nested) objects.



oheaders = array("name" => "barry", "age" => 10)




? xml_encode( oHeaders )


Which will display:



<?xml version="1.0" ?>
<data>
<name>barry</name>
<age>10</age>
</data>


You can specify the "master" tag like this:



? xml_encode( oHeaders, "customer" )


Which will display:



<?xml version="1.0" ?>
<customer>
<name>barry</name>
<age>10</age>
</customer>


If oHeaders is an array of objects (associative arrays):



oheaders = array(array("name" => "barry", "age" => 10),array("name" => "pablo", "age" => 20))




? xml_encode( oHeaders, "customers", "customer" )




<?xml version="1.0" ?>
<customers>
<customer>
<name>barry</name>
<age>10</age>
</customer>
<customer>
<name>pablo</name>
<age>20</age>
</customer>
</customers>


Note also that the xml_decode( ) function works in the opposite direction just as json_decode( ) does.

barrymavin
2012-10-13, 06:40
Also worth mentioning is that both the Visual FoxPro REPLACE command and SQL UPDATE command when applied to a memo field will store all the JSON or XML encoded string in the memo/varchar field which can be queried and updated.



use customers
seek "mycustomer"
replace mymemo with xml_encode( oHeaders, "customers", "customer" )


or alternatively:



update customers set mymemo = xml_encode( oHeaders, "customers", "customer" ) where custname = "mycustomer"


Then you can simply retrieve the XML (or JSON) data later and use the xml_decode( ) or json_decode( ) functions to put them back into objects.

Lots of nice uses for this.

As a sidenote, if the field in the table is an "Object" you can store images and documents in it too just by specifying the file path:



use customers
seek "mycustomer"
replace mypicture with "c:\somefolder\image.png"


or in SQL:



update customers set mypicture = "c:\somefolder\image.png" where custname = "mycustomer"


You can use the objectRead( ) and objectWrite( ) functions with these too.

lazyfox
2012-10-13, 08:04
Also worth mentioning is that both the Visual FoxPro REPLACE command and SQL UPDATE command when applied to a memo field will store all the JSON or XML encoded string in the memo/varchar field which can be queried and updated.



use customers
seek "mycustomer"
replace mymemo with xml_encode( oHeaders, "customers", "customer" )

Then you can simply retrieve the XML (or JSON) data later and use the xml_decode( ) or json_decode( ) functions to put them back into objects.
Lots of nice uses for this.

Which is exactly the feature/facet of the "modern NoSQL" databases I see as most useful fo my use cases, as the massive parallell is not needed here.
Get a feel for intended changes without altering the DB first by testing those chenges via an object put in memo.
A definite plus to have this integrated seamlessly.


As a sidenote, if the field in the table is an "Object" you can store images and documents in it too just by specifying the file path:


use customers
seek "mycustomer"
replace mypicture with "c:\somefolder\image.png"

You can use the objectRead( ) and objectWrite( ) functions with these too.

Just to be sure "document" here is meant as Word Doc stream, not as document/graph as perhaps used in an object database,
but stored in binary ? Or is this intended also as target for the object base methods .saveobj and .loadobj ?

pablokuster@gmail.com
2012-10-13, 08:23
fantastic, you go beyond my requirements.

many thanks

lianjasupport
2012-10-13, 08:28
Yes any type of binary file of any size can be stored as the database engine uses 64 bit file access. Space is automatically reclaimed on updates.

yes, you are correct, with this functionality Lianja can easily be used as a NoSQL key/value database just create a table with two fields, key and value.


create table NoSQL (key char(20) foreign key, value varchar)

you can then perform very fast key/value lookups of JSON or XML encoded data stored in the value column.

lianjasupport
2012-10-13, 08:53
And of course the inherent problem of distributed updates in NoSQL databases does not exist in Lianja. Everyone sees the updates immediately.

When configured correctly (in a cluster) for high avaliability (HA) and fault tolerance using load balanced App Servers and master/slave Data Servers with real-time replication, the Lianja database engine is extremly fast and robust. We have this database engine running 24x7 in large enterprises in banks, finance companies and medical claims processing companies with very high transaction volumes.

pablokuster@gmail.com
2012-10-14, 15:39
Yes, you describe a very interesting scenario, the combination of SQL and NoSQL DB in a product with the characteristics of Lianja will be a very powerful instrument, in my professional life, data centric DB like foxpro, play a very important role. I see it as a laboratory with instruments that are simple to use and amplify our capacity to produce sample and optimised solutions to complex problems in very complex ecosystems.

barrymavin
2012-10-14, 21:21
One of the interesting features of the Lianja database engine is its built-in support for Visual FoxPro style filtered indexes which can be used in this "NoSQL" scenario. The CONTAINS( ) function is of particular interest (and in fact this is used extensively inside Lianja for "Instant Selections").

For example take the following code:



create table NoSQL (key char(20) foreign key, value varchar)
use NoSQL
index on key tag usa for contains("usa", .t.)
index on key tag uk for contains("uk", .t.)
index on key tag germany for contains("usa", .t.)
index on key tag any for contains("usa,uk,germany", .t.)


Now we have three filtered indexes that contain only those rows in the table that have "usa", "uk", "germany" or all of these words contained anywhere in the rows. The second parameter to CONTAINS( ) tells it to look in memos. (incidentally these are case insensitive also).

This can be used in a NoSQL usage scenario with good performance advantages. So, the key/value pairs can be filtered based on the "value" content.

See this thread (http://www.lianja.com/community/showthread.php?712-Using-Lianja-as-a-NoSQL-database&p=2705#post2705) for details on how to work with JSON and XML and encode/decode it into objects that can be stored in a key/value store.

lazyfox
2012-10-15, 04:14
There is already a function getUrl( cUrl [,nTimeout [, oHeaders] ] ) that can be used to read from web services into a string. This is currently limited to 64k in size.

I can understand the reasoning of limiting memory strings to 64k so as to keep memory thrashing of large blocks away,
as Lianja has no StringBuilder/StringBuffer analogue. But one of my vfp customer has to exchange data with a multitude of gov
installations - ranging from SFTP over XML/RPC, SOAP and REST. Data sizes can be substantial, well over 64k when returning
data sets as answers to queries. Having the interface implemented via size-limited strings seems counter-productive -
perhaps writing out a stream into a file should be the base implementation pattern,
as from there we can always take string sized steps for further processing.
Will make an ER if that customer has interest in porting his stuff, but perhaps if you implement data gathering from other sources
which is currently not supported you could change the implementation pattern to be size-independant from start on.

pablokuster@gmail.com
2012-10-15, 06:46
My personal opinion about this argument is articulated in two points:

1.- Architectural, Lianja team here has the last word, they have knowledge of the product architecture and are the more indicated to decide the technologies that best fit into Lianja architecture, the support of java, php and python open doors two thousand of open source libraries to evaluate and integrate into lianja, the problem here is decide for the best and more sample of use without become dispersive.

2.- Community (LianjaX), that extend the Lianja functionality to others technologies, a sample of this can be the integration of NoSQL database exposing the API of each specific product, like marklogic or Basex both have java API, building a class that interface it and assimilate the implicit complexity of use, in my fantasy an sample can be a command like: "use /uri/customers/customer[@id="nnnn"]/invoice[@id="mmmm"] at http(s)://[domain or server IP] as user : password", a sample foxpro/recital command that using standards like :XPATH, XQuery and HTTP(s) (opportunely integrated into Lianja architecture), find a resource into the web, inside very complex and multidimensional data models, get the fragment of the requested document and load it asynchronously into a local table, tons of very complex work made ease.

Maybe, We can make true this dream, thank to Lianja team, that actually create the conditions and the developer community, working in the process of: "Knowledge Distillation" and optimization with a very important professional ROI for everyone of us.

Thanks to everyone.

lazyfox
2012-10-15, 10:40
One of the interesting features of the Lianja database engine is its built-in support for Visual FoxPro style filtered indexes which can be used in this "NoSQL" scenario. The CONTAINS( ) function is of particular interest (and in fact this is used extensively inside Lianja for "Instant Selections").
...
Now we have three filtered indexes that contain only those rows in the table that have "usa", "uk", "germany" or all of these words contained anywhere in the rows. The second parameter to CONTAINS( ) tells it to look in memos. (incidentally these are case insensitive also).

Wow. From Docs I would never have guessed that contains() would look for each word separated by ",",
but would have expected it to look for the string "usa,uk,germany" in any of the fields:

"The CONTAINS() function returns .T. (True) if the current record contains the specified character string, <expC>."



create cursor t1(c1 c(250), m1 memo)
append blank
replace c1 with "usa uk", m1 with "uk,germany"
? contains("usa,germany,uk")
? contains("usa,germany,uk", .t.)
? contains("usa.germany.uk", .t.)
? contains("usa;germany;uk", .t.)
replace c1 with "usauk", m1 with "ukgermany"
? contains("usa,germany,uk", .t.)



nice feature! more examples in code might make things clearer for us poor continentals...

lianjasupport
2012-10-15, 10:55
Also worth pointing out is that The "words" or "phrases" can be separated by spaces or commas.

lazyfox
2012-10-16, 04:01
Also worth pointing out is that The "words" or "phrases" can be separated by spaces or commas.

Typical for me to miss the in hindsight obvious - thx.