on-rev remote database

Mon Oct 18 11:27:25 EDT 2010

Ien,

See what i wrote to days ago to Bill... See the requests example jointed below. Is't the use rev list a cool library ? ;-)

Hello Bill,

> I'm not even sure if I'm asking the question correctly.
> 
> I've made a simple time tracking database for my colleagues. Dates, fill-ins, drop downs etc. I'm not using any of the sophisticated Rev DB tools or SQL. I am just saving each collection of responses in separate fields. I also have a button that exports as a text file all info with records on separate lines, commas between items. Each teacher keeps their own stack on their laptop. When our supervisor needs to do a report for the school districts, each of the 13 teacher/consultants will generate the data-text file and send it to her. She will then import each text file into excel to do her sorts and reports.
> 
> Every time she needs to do an update, she'll need to ask us to send our latest data and create a new excel to keep the data correct. I do have an on-rev server account. I did attend and have the dvds for rev-live Las Vegas. I do own the DVDs from the On-Rev Edinburgh conference, but haven't had time to watch them yet. I know RunRev/Live Code fairly well. I do not know SQL.
> 
> Questions
> 
> 1. Can I put my existing stack on my on-rev server account for multiple people to use at one time?
> 
> 2. Can I put my existing stack on our school server for multiple people to use at one time?
> 
> 3. Am i up-that-creek for not knowing SQL?
> 
> 4. If (I) and (2) are possible with or without SQL what's my best option to learn how to do whatever I'll need to do.

SQL is not hard to learn at all when it's used in the LiveCode+On-Rev environment. In fact, if you figure it's just a special subset of LiveCode and spend some hours to test the main revDB commands/functions, you will be happy to discover how easy it is. If you do so, choose directly PostgreSQL as the db target. RevDB is full db agnostic (MySQL and PostgreSQL, at least) but PostgreSQL is lots more transparent (stability, BSD license, etc...) than MySQL.

The advantages of using an SQL backend in a n-tier server-side environment :

- SQL db are from ground build to handle in the best ways multi-users accesses in write-mode where LiveCode is't (out of the box, at least !)
- SQL db are storing data in text files too but in a lots more sophisticated way we can do in storing data in LiveCode objects or text files (concurrency in read and write modes, journalised transactions, db dump, etc...
- the best db back-ends don't require a more heavy learning curve than the less solid's ones but they are ACID-complient (PostgreSQL, FireBird, Oracle,...) witch means you will never lost data in using them
- To the end, it will always be more reliable to store big amount of data in SQL backends than in LiveCode in about speed of use in a n-tier server-sided environment and this will always be good for both your data, the server's response time average to your requests and the mutualised on-rev LiveCode server engine (top use of it in % of processor's time).
...

The main revDB to explore to become OK with SQL over LiveCode :

> put revOpenDatabase (DbTarget,"localhost",DbName,DbUser,DBPasswd) into myDatabaseID
> 	if myDatabaseID is not a number 
> 	then return myDatabaseID 
> 	else
...

> put "SELECT * FROM" && yourTable && "WHERE yourColumn LIKE '" & trim(tparam2) & "'" into sqlStatement
> put revQueryDatabase(myDatabaseID,sqlStatement) into dbCursorID
> if dbCursorID is not a number 
> then return dbCursorID
> else
> 
> 	repeat until revQueryIsAtEnd(dbCursorID)
> 
> 		put trim(revDatabaseColumnNamed(dbCursorID,"oneOfYourColumns")) into article_titre
....
> 				
> put "SELECT column1,column2 FROM" && yourTable && "WHERE column3 <= current_date AND column4 LIKE '" & toUpper(tparam2) & "\|%' AND column5 = 'Y' ORDER BY column6 DESC" into sqlStatement
> 		
...
> 						
> put "UPDATE" && yourTable && "SET column3 = '" & active_record & \
> 							"' WHERE column1 = '" & tparam1 & "' AND column4 = '" & anonymousclt["wsanonymousclt"] & "'" into sqlStatement3
> revExecuteSQL myDatabaseID,sqlStatement3
> if the result is not a number
> then return the result
...
> 								
> put "INSERT INTO" && yourTable && "(column1, column2,column3,column4)" && \
> 	"VALUES ('" & tparam1 & "', 'record_activation', '" & tparam2 & "', 'anonymous')" into sqlStatement4 ###
> 
> revExecuteSQL myDatabaseID,sqlStatement4
> if the result is not a number 
> then return the result 
...

> put "DELETE FROMyourTable WHERE tparam1 < '" & the seconds - 10800 & "'" into sqlStatement
> revExecuteSQL myDatabaseID,sqlStatement
> if the result is not a number
> then return the result
> else return ""

...
> revCloseDatabase(myDatabaseID)

HTH,

Best,

Pierre
> 
> Thanks,
> 
> Bill
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
> 

--
Pierre Sahores
mobile : (33) 6 03 95 77 70

www.woooooooords.com
www.sahores-conseil.com

Le 18 oct. 2010 à 16:25, Len Morgan a écrit :

> Pierre,
> 
> It's only a security risk if I let the users WRITE to the database which I have no intention of doing.  The reason I'm trying to avoid and irev script in the middle is that I don't want to have to connect and disconnect every time a user asks for data.  If you have a sample irev script that would allow several hundred (I hope) users to read from this database, I'd love to see it.  Maybe it's not as bad (i.e., establishing a new connection to the database every time) as I'm thinking.
> 
> Thanks!
> 
> len
> On 10/18/2010 6:39 AM, Pierre Sahores wrote:
>> Ien,
>> 
>> If you just need to have a LiveCode client able to speak with the on-rev PostgreSQL backend from anyware all over the world, why don't you choose to send HTTP Posts from your client to an irev script witch will interact in localhost mode with the PostgreSQL backend in full read/write before sending back the pg response to the client ? I do this all the time and it works perfect without any security lacks.
>> 
>> In tunning the pg config, anyone can make PostgreSQL available in full cloud access mode but it's, at least, not a safe way to go...
>> 
>> HTH,
>> 
>> Pierre
>> 
> 
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
> 

--
Pierre Sahores
mobile : (33) 6 03 95 77 70

www.woooooooords.com
www.sahores-conseil.com