1. Juergen Hofinger
  2. Valentina Database ADK
  3. Samstag, September 16 2017, 07:08 PM
  4.  Abonnieren via E-Mail
Documentation suggests to subclass VDatabase and VTable as a preferred design pattern (Articles/Choosing a Style of Coding). Since the only way to get a VTable instance is to use the .table method of the factory class VDatabase I don't see any possibility to get a subclass of VTable that is connected to the database server.

Example (Xojo):

Class myDatabase As VDatabase …
Class myTable As VTable …

myTable = myDatabase.table("tableName";)

If I override the .table method in myDatabase it will generate an empty subclass of VTable. If I don't it will throw a cast exception, since a superclass can't be downcasted to it's subclass. Did I miss here something?

I would very much appreciate any suggestions for a best practice to implement application specific code based on the Valentina classes.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Juergen,

I think it is not so preferable. Maybe we need check article to remove such impression.

Each way has own advantages and disadvantages.
Some of them technical some of them subjective enough, we have seen developers which say
- API is my favor, I love to write funcs and be verbose, while others
- prefer SQL

API way in Valentina DB ADKs is natural low level API of Valentina DB engine, which is made in C++.

SQL engine itself uses API functions of course. SQL part itself is comparable by size to low level part.

* You may know, that around many RDBMS developers tend develop ORM -- i.e. some functions API, which hide SQL.
In Valentina DB we have to open API level to developers. This really do not have SQL behind.

* On the other hand SQL is cool. Especially for client-server.


So we always have trade-off.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
We have Examples/Class_way folder, which contains few examples on this point.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
VTable class do not care where it works, locally or client-server.

Its only VDatabase class defines the environment when you do db.open(file) OR db.Connect( host ... )
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
To give you advice, can you describe kind of your application?
I guess you want client-server. Only client server?

How many tables you going to have?
How many users?
local network or over-sea DB servers?
etc ...
Kommentar
There are no comments made yet.
Juergen Hofinger Akzeptierte Antwort
Hi Ruslan,

thanks for your reply. I am going to build up an inhouse solution for documents storage in a small company. So the core is a database to store and organize documents (mainly PDF but also other formats as "originals";). It should not stop at this stage though and finally every document that is generated in the company should be generated by the system and each version of a document should be converted to a pdf file and stored in the document area. The amount of tables will be small at the beginning but can grow huge at the end.

So the access is mainly inhouse (external work should be possible via vpn)
The number of users will be limited (currently not more than 10, in the future maybe not more than 100)
I intend to use Xojo as framework
Maybe I will start with a web application (for easier deployment), but I am not sure about that.

In the meantime I have done some experiments with Xojo and Valentina. I have written my own classes and implemented either a VCursor (SQL approach) or VTable (API approach) as properties which both worked fine. Since I am not used to SQL a lot I feel more comfortable with the API approach. Here are my thoughts on this topic so far:

- I understand that the API approach is faster than SQL. It's not that SQL with Valentina is slow, it's just that using the API is even much faster. A assume that would not be critical for me. But I would always prefer faster of course.

- I will have a multi user environment. With the API it seems not be possible to lock records. I don't know how to do it with sql but I guess there is a way. On the other side it is very unlikely in my database solution that two people will work in the same record. Since I implement versioning for the documents users will generate new documents in most cases and responsibilities for documents will be well defined. If they do, the worst thing that could happen is changes of one user (the one who commits to early) will be lost. On the other side locking is always half way to deadlock. Another idea was to implement locking myself with additional fields in the table, if I later feel that I need it.

- Changes in one table will sometimes need changes in other tables. Is there something like a transaction model to rollback changes if the whole operation has not been successful? I am not sure how critical this can be for my solution. Inconsistent records could be the consequence but table operations are very fast and if I do not have any locking I don't see any reason why updating tables should not succeed (except a crash).

Your advice would be very welcome.
Thanks in advance.
Kommentar
There are no comments made yet.
Juergen Hofinger Akzeptierte Antwort
Because API and SQL approaches are very different, I think it would be very helpful to have a comparison chart to demonstrate how things are done in each way. In SQL I can request data from different tables with complex conditions. How am I supposed to do that through the API? I can only search in distinct tables as I understood.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Juergen.

- I understand that the API approach is faster than SQL.

In fact it is faster for LOCAL single user dbs. YES.

But if you work with REMOTE db server, then API can easy be slower and much slower of SQL, because you need do MANY MANY calls to server. For fast local network, API also can be okay.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
- I will have a multi-user environment. With the API it seems not be possible to lock records.


That is right, We always did not have time to develop methods for API to lock records. It is possible, but not implemented. In theory, user self can do that using a temporary boolean column in Table.

This is why for your task, I'd advise

1) START with SQL way. Keep in mind that you can easy MIX SQL and API actually.

2) For example, you can have method

MyDb.CreateSchema()
db.SqlExecute( "CREATE TABLE T1( f1 LONG INDEXED)" )
db.SqlExecute( "CREATE TABLE T2( f1 LONG INDEXED)" )
end

or in API style:

MyDb.CreateSchema()
tbl = db.CreateTable( "T1" )
tbl.CreateLongField( "f1", fIndexed )

...
end


In this case SQL looks shorter. In fact this is true for most tasks: SQL looks more short.


3) Again, because you can MIX both ways, you can at any time do some search in some table using API way.
But I believe you will prefer SQL :) ValentinaDB itself is very fast.


4) Notice that VCursor (part of SQL) is much more powerful than e.g. in mySQL or PostgreSQL
You can choose client-sdie or server-side cursor.

Server side cursor can be editable. This make things much simpler if you need edit/update 1-2 fields of a record.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
to start develop DB -- best of all probably - use diagrams.
Then you can ask Vstudio generate tables and links.

Also you can get a SQL dump of that empty db with ready tables.

CREATE TABLE commands you can copy from Vstudio or from dump file into Xojo code.
some Xojo devs just do db.LoadDump( file )

All depends on develop habits.
We give many ways.
Kommentar
There are no comments made yet.
Juergen Hofinger Akzeptierte Antwort
Thanks Ruslan,

the advice will save me a lot of time.

Juergen
Kommentar
There are no comments made yet.
Scott Akzeptierte Antwort
I don't see any possibility to get a subclass of VTable that is connected to the database server


I am not sure I understand this.

When accessing the .table method of a vDatabase, it doesn't matter if it is a local or remote db.


dim myFancyTable as VTable
myFancyTable = mDatabase.table("mySuperTable")


This instantiates a VTable using the mDatabase instance. The only difference lies in how you instantiated your VDatabase, Using a local method or through VConnection to a remote server. Therefore, all the .table methods are available to both local and remote db's.

For local DB:


dim f as folderitem
f = getfolderitem("localDBname")
dim mDatabase as New VDatabase
mDatabase.open ( f )
dim myFancyTable as VTable
myFancyTable = mDatabase.table("mySuperTable")


You can now access all the myFancyTable methods on the local DB.

For a Remote Server:


dim f as folderitem
f = getfolderitem("serverDBname")
dim mConnection as New VConnection("server IP address","userName","userPwd")
mConnection,Open
dim mDatabase as New VDatabase(mConnection)
mDatabase.open ( f )
dim myFancyTable as VTable
myFancyTable = mDatabase.table("mySuperTable")


Same access to .table methods - just using a remote server. I think this is what you were asking.

Through trial and error, I came up with a few pointers when I write VDB solutions. Your mileage may vary.

If it will be a local application, I would recommend the API method for almost everything except very complex and specific searches to the db. API access to a valentina db is brutally fast. But then again, it depends on what you're searching for and what and how many records you will be retrieving. In a single user local app, you have exclusive access to the db and you can leave the connection open and just pick out everything you need piece by piece - and although that sounds inefficient (and technically is), the API method to a local db is beyond BLAZING fast (I think I said that already...). Using the table methods .addRecord(), .updateRecord(), etc is just too easy.

If a client/server app sits on a modern fast Gb ethernet local network, you can still rely on the API method for most operations. There is a small performance hit. Use SQL where needed to increase efficiency. Table methods still work here reasonably well. You can use the API method to lock records by changing a record flag before and after operations. Make a column called "editable" and make it boolean. Just change the value as needed. Make sure that your check for this flag before writes.

If the client/server app sits on a physically remote server, stay with SQL. It's more efficient making usually one call to the server for all your information versus going back and forth. I would use .SQLSELECT(), or .SQLExecute(), etc.. methods in this case.

I would absolutely concern yourself with record locks in any multi-user environment. Not addressing this issue will cause more errors or cause data corruption that will cost you more than you will ever know. VCursor record locks are very easy and ensures your data stays safe. Just lock the server side cursor and destroy it when done. When making writes to the db, use a try/catch block and look for the verror stating the record is locked and let user know. I write my methods to check for a lock, and to make several attempts to write/update (whatever) while giving the user something to look at to distract him/her. If it takes longer than (whatever you determine as a timeout) inform the use and give them the ability to either message the user locking the record (valentina's api notification class works like a champ!) or cancel out of the operation.

When linking records, I can't say enough goodness about binary links. I use them almost exclusively. Makes retrieving data effortless. Although this will make Ruslan and Ivan cringe, you can pull almost limitless fields (and as many record sets as needed) from numerous tables using a pretty simple request:

SELECT fld1, fld2... fld100 FROM tbl1, tbl2, tbl3, tbl4 (etc) WHERE tbl1.RecID = (whatever)


As long as all your tables have their various links, this just works. (Of course, this is over simplified - but you get the point of how easy binary links can be)

When storing documents, I would not typically store them directly in the db. I would rather store them on the server's file system and just store the path variable in the db. When looking for a document, search for its name, or whatever you want and get its file path from the db. Then use Xojo's file methods to get the document. It's much more work to do this, but after time has passed and you now have bazillions of documents - your db may become unwieldy and humongous.

OK - where I was going with this I have no idea... But, you will find VDB a relatively easy and powerful solution. And, to add more fuel to the fire - I am not sure how you are generating your PDFs, but you should take a look at VReports. It's another outstanding tool from the Valentina guys.

Scott
Kommentar
There are no comments made yet.
Juergen Hofinger Akzeptierte Antwort
Hi Scott,

thank you very much for your extensive and very helpful comments!

For the subclassing:
The documentation suggested subclassing of VTable (i.e. defining a new class called myNewTableClass based on the VTable class). I would then generate an instance of this new class:

Dim myFancyNewTable As myNewTableClass //where myNewTableClass is a subclass of VTable

There is no way to get a connection to the database of this new instance because that would require a constructor of the table class that takes the database as parameter. At the moment the api only allows instantiation of the VTable class by the VDatabase factory class. And this will only result in a VTable object and not in a self made subclass of it.

This is not a problem, it's just that the documents suggested this and I wondered if I have missed here something.

For the record locks:
Did I get it right that one would that one would retrieve a list of records with a cursor with read only locking first to show it in a listbox? When a user selects a record for edit a second cursor set to read/write locking is then retrieved?

Juergen
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Did I get it right that one would that one would retrieve a list of records with a cursor with read only locking first to show it in a listbox? When a user selects a record for edit a second cursor set to read/write locking is then retrieved?


* Use no_lock to just read something.

* Few users can have READ lock on a record

* IF record has READ lock, nobody can set WRITE lock

* If record get WRITE lock, nobody can read it until free.

* use client-side cursors to get list of items from server, no locks, all items go into RAM of client computer. Such cursor always is read-only, because it works on a copy of records.

* server-side cursor can have WRITE mode. It exists on server and client computers in the same time. It keep Read/Write locks as it was specified.
Server Side cursor is good for HUGE selections, because you not load and send all found records, only one.
On the other hand it can be slower because each next/perv record jumps to server.
Kommentar
There are no comments made yet.
Juergen Hofinger Akzeptierte Antwort
Hi Scott, Ruslan,

"When storing documents, I would not typically store them directly in the db. I would rather store them on the server's file system and just store the path variable in the db."

yes, most people seem to prefer storing files outside the database. For I client server application the files have to be transferred from the client to the server. Http would be obvious in regards to web clients but maybe also the most critical for security. What is your advice? What about transferring through the database and then exporting to the file system on the server?

"OK - where I was going with this I have no idea... But, you will find VDB a relatively easy and powerful solution. And, to add more fuel to the fire - I am not sure how you are generating your PDFs, but you should take a look at VReports."

Yes, that seems to be true after my experiences so far. Not to mention the excellent support for newbies like me :-). Initially I was looking for a report server. So that was the main reason why a had a look an Valentina.

Juergen
Kommentar
There are no comments made yet.
  • Seite :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories