Which database to choose for which kind of rev application ?

verlsnake · Post by **verlsnake** » Fri Feb 22, 2008 7:35 pm

I own an older version of the Valentina database since recently; I was excited about what I could read:
- It is said to be "blazingly fast", leaving other databases in the dust
- In internet-aware rev apps, it can be used on the client as well as on the server side

But I have also read somewhere that it is a single-user database ...

So I wonder myself when to choose which database in the Revolution world.

I want to build an internet-aware rev app which shall use a database on the
- server-side which must cope with much traffic from many concurrent users.
- client-side, too, to enhance the user experience considerably.

What are the recommendations with regard to the app's database architecture then ?

Are there comprehensive examples for those kinds of rev apps available ?

FourthWorld · Post by **FourthWorld** » Sat Feb 23, 2008 12:49 am

Most web hosting companies offer MySQL for free on their servers. Hosting companies like Dreamhost and JaguarPC also support custom compiled CGIs, such as Rev. So you can use Rev on the client desktop, have MySQL on the server, and even use Rev CGIs as needed.

verlsnake · Post by **verlsnake** » Sat Feb 23, 2008 6:33 pm

Hello Richard !

I am really grateful for Your thorough answer in the undo/redo/history/app state management thread. Your post here, however, doesn't really answer my question

I have NOT asked about cheap/free hosting - I have a VPS in mind where I can deploy my own software stuff.

What I WANT to know is: What is the praise for Valentina all about ? Can I put it to good use in the scenario described above - or NOT ? Before entering the land of Runtime Revolution, I had never heard about the Valentina database and am therefore curious if it can offer me advantages which only wait to be exploited ...

Thanks in advance for a clarifying answer !

Kai

FourthWorld · Post by **FourthWorld** » Sun Feb 24, 2008 8:04 pm

verlsnake wrote:What I WANT to know is: What is the praise for Valentina all about ?

That's not one I can answer. Never used Valentina myself. My own custom data management engine serves me well for mos desktop apps, larger data sets are covered adequately with the pubic domain SQLite, and on the server I use MySQL or my own Rev-based stuff.

I hear Valentina is very fast, but I've never had a data set large enough where the performance difference between it and anything else would make enough of a practical difference to warrant moving all of my data to a closed-source system.

verlsnake · Post by **verlsnake** » Thu Feb 28, 2008 1:51 am

O.K., Valentina seems to be not so widespread in use in the Revolution community as I thought ...

Then I would still appreciate very much hints showing comprehensive client-side integration of a database like SQLLite; the trend in (web-based) software is going towards handling large data sets on the client-side to provide a superior user experience; it would be fine to know that in the Revolution community approaches already exist for client-side database based componentry.

verlsnake · Post by **verlsnake** » Fri Mar 28, 2008 9:15 pm

I cannot hold my breath any longer

Today I have stumbled upon this: AIR API - Introduction to the SQLite Database:
http://www.insideria.com/2008/03/air-ap ... he-sq.html

What has Runtime Revolution in store when it comes to client-side database integration ? E.g. integration with SQLite OR Valentina, which may offer advantages over SQLLite.

Any pointers anyone ?

RRers: Please do NOT disappoint Your new, fresh, hopeful and open-minded community members

!!!

Mark · Post by **Mark** » Fri Mar 28, 2008 11:09 pm

Hi verlsnake,

I'm not sure what you are asking, but you might want to search the built-in docs for "revData", "revdb", "database" and "revExecuteSQL" (and all terms containing these strings).

Reading this discussion, it seems to me you will want to use either MySQL or SQLite. MySQL needs to be installed but can easily be used in a multi-user environment. SQLite comes with Revolution and can be iincluded with your standalone, but I don't think it is easy to set it up for a multi-user environment. Also, MySQL can be used by everybody, while I am currently not sure whether SQLite will be free for Media and Studio licensees in the future --I guess I need to ask Support about this.

Best,

Mark

Janschenkel · Post by **Janschenkel** » Mon Mar 31, 2008 11:56 am

Revolution Media has no database support - you'll need at least Revolution Studio. Revolution Studio ships with drivers for MySQL, Valentina, SQLite, PostgreSQL and ODBC. Revolution Enterprise adds an Oracle driver to the mix.

Which database to choose, depends on your needs.
- MySQL is pretty fast, wide-spread and easy to configure.
- PostgreSQL is stable, but not as fast as MySQL.
- SQLite is stable and faster than MySQL, but is meant for embedded use rather than client-server, and performance degrades as the database grows beyond 2GB.
- Valentina is indeed very fast, and comes both in embedded and client-server solutions.

To be honest with you, I will only use a databse that is ACID-compliant. In a previous life, I've had to use databases that weren't transaction-safe and this can be a serious pain in the posterior when things go wrong.
So for small embedded databases, I use SQLite - and when it comes to client-server or large databases, I go for PostgreSQL.

Just as a note of interest: do not hook up your client applications directly to a server database; always go through an application server. This will same you many security headaches.

Hope this helped,

Jan Schenkel.

alex298 · Post by **alex298** » Thu Apr 03, 2008 3:38 pm

Hi,

do not hook up your client applications directly to a server database; always go through an application server.

I am not quite understand "always go through an application server". Could you please explain this a more details?

Thanks and best regards

Janschenkel · Post by **Janschenkel** » Thu Apr 03, 2008 8:17 pm

Hi Alex,

When I say, 'always use an application server,' I mean: don't connect a client application directly to the database, but communicate with an application running on the server, which does the actual database communication.

One way of accomplishing this, is using the Revolution CGI-engine. The Common Gateway Interface is a standard extension mechanism used by Apache and MS IIS web servers, where the web server starts command-line applications and exchanges information using standard input/output streams.
Once the cgi-script has finished executing, the web server will then send the output back to the client computer. While this is mÃ¹ostly used for web applications, you can use Revolution 'get URL' and 'post to URL' commands to communicate with the server without ever directly displaying the data in a web browser.

Another way of keeping the database communication entirely on the server, is by writing a Revolution standalone application that employs socket communication to exchange data with the client applications.
You can think up your own protocol, or use the HTTP protocol - that's up to your particular needs. For a good example of how to implement this, head on over to http://www.andregarzia.com/RevOnRockets/

The most important reasons not to directly connect your client applications to the database are:
- Security - fewer open ports on the server means less ways for potential hackers to crack open the door (I remember a particularly bad problem with an older version of MS SQLServer, where a bug allowed worms to bring entire companies to a grinding halt)
- Scalability - if you have hundreds of clients, and they all need a continuously open connection to the database, that eats quite a few resources. Some databases require a license per client, so if you can just keep a small number of connection open on the server, you pay less.
- Version conflicts - when you update your application, you have to install the update everywhere at the same time. Forget a single client and your database records may be filled with incorrect or incomplete data.

Hope this clarified my remark,

Jan Schenkel.

Bernard · Post by **Bernard** » Sun Apr 13, 2008 10:41 am

FourthWorld wrote:
verlsnake wrote:What I WANT to know is: What is the praise for Valentina all about ?
That's not one I can answer. Never used Valentina myself. My own custom data management engine serves me well for mos desktop apps, larger data sets are covered adequately with the pubic domain SQLite, and on the server I use MySQL or my own Rev-based stuff.

I believe Richard means Rev itself when he talks about his own "data management engine". In one of my projects I had to perform hundreds of lookups and calculations in order to present the result to the user. No SQL database could provide an adequate performance. But storing the several tables involved in this query as Rev dictionaries (where the SQL PK was the Rev Dictionary key), produced sub-second performance. The SQL versions were taking over 10 seconds to produce the results.

If you need to write data, then Rev may not be the solution, especially in a multi-user context. However, when speed really matters, Rev-based data may be the answer.

LiveCode Forums

Which database to choose for which kind of rev application ?

Which database to choose for which kind of rev application ?

Rev is a database