[Solved] Database in The Cloud lazarus / 16-Databases ---------------------------------------------------------------------- madref · 2017-11-29 09:39 I have build a database and now i want to expand it to the cloud. But i have no freaking way how to get this solved Has anyone a good suggestion on how to get things started? ---------------------------------------------------------------------- tudi_x · 2017-11-29 11:53 did you consider Amazon Web Services (AWS)? ---------------------------------------------------------------------- Leledumbo · 2017-11-29 12:19 The easiest but most unsafe way is just to expose the port worldwide, then your app just needs to change the host address. The better way would be to wrap db operations in a webservice so it's limited to only what endpoints you provide. ---------------------------------------------------------------------- DonAlfredo · 2017-11-29 12:50 Quotedid you consider Amazon Web Services (AWS)?+10 ! My servers, based on the mORMot, run on AWS. Very cheap, VERY reliable. AWS is easy to try: you can get a server for free for one year. Just get one, and install a Linux version. Get yourself the mORMot. Run the http database server, open a port and you are ready to go !! ---------------------------------------------------------------------- marcov · 2017-11-29 12:56 Quote from: Leledumbo on November 29, 2017, 01:19:16 pmThe easiest but most unsafe way is just to expose the port worldwide, then your app just needs to change the host address. The better way would be to wrap db operations in a webservice so it's limited to only what endpoints you provide. If that means you must also maintain a webserver, there are a lot of things that can go wrong configuring and maintaining a webserver too. Specially if it doesn't just serve static content. ---------------------------------------------------------------------- madref · 2017-11-29 13:39 I have an own website (which i pay for) and it runs a cms. Can i install something on that? ---------------------------------------------------------------------- Almir.Bispo · 2017-11-29 18:00 You can do something like this: part 1 https://www.youtube.com/watch?v=2XytLSkgyQw part 2: https://www.youtube.com/watch?v=QmI3kIdPVK4 Cloud (Can you create a dedicated server): https://www.youtube.com/watch?v=7DSTotVI1_I&t=0s ---------------------------------------------------------------------- madref · 2017-11-29 20:47 Damn i don't speak that foreign language and i am using SQLite And i am not working in windows but osx ---------------------------------------------------------------------- madref · 2017-11-29 20:54 Or can i just put the database (which is now 175 Kb) as a file (which it is now) on a certain spot on my website and access it as a normal file and load it into memory and when i am done put it all back? or does SQLite always has acces to that file? ---------------------------------------------------------------------- taazz · 2017-11-29 21:31 Quote from: madref on November 29, 2017, 10:39:22 amI have build a database and now i want to expand it to the cloud. But i have no freaking way how to get this solved Has anyone a good suggestion on how to get things started? well this is very generic specification.... actually there is nothing specific about it. What does it mean to expand it on the cloud? being able to edited from a web page? to be able to update its data from your client application through the internet? there are a number of ways to work with, for example, you can send an email to your address with the updated data and have an application download and import them automatically to a local database. You can upload them to a server (ftp, http, email doesn't make any difference) and do the same with a scheduled task on that machine. Settle down on what you want to do and your choices will present them self or we can suggest various routes, in any way expand in to the cloud is something that water vapour does well. ---------------------------------------------------------------------- Phil · 2017-11-29 21:36 Quote from: madref on November 29, 2017, 09:47:47 pmAnd i am not working in windows but osx You probably need to familiarize yourself with Web technologies. A good way to start would be to investigate AWS, as suggested above. Here's some notes on getting started: https://macpgmr.github.io/MacXPlatform/PascalDynLibs_4.html Unlike a Web site, where typically you're restricted on what you can do, with an AWS server you can install and do just about anything you want. A common use for an AWS server is to run a Web server (eg, IIS with Windows or Apache with Linux). Once you've spent some time with AWS, think about what kind of Web access you want to your database and then investigate what it will take to program that access - eg, as a SOAP or REST Web service, CGI app, etc. The notes above reference a CGI app that you can take a look at. You can develop and test your server-side program on Mac and then recompile it on your Windows or Linux server. ---------------------------------------------------------------------- madref · 2017-11-29 21:41 This is the idea i have.My database is in the cloud on some server (preferably my own webspace). My application accesses this database and inserts, edits or deletes data. The same as a do now, but then just on my laptop locally. I think that is the best course of action for now.So my initial question still stands...How do i do that? P.S. The things i am thinking of using: * Lazarus * Database for now is in SQLite. Prefer to keep it that way. * I have own webspace that runs a CMS ---------------------------------------------------------------------- Phil · 2017-11-29 21:43 Quote from: madref on November 29, 2017, 10:41:24 pmI have own webspace that runs a CMS[/font] I don't know what a "webspace" is. Can you install and run binary executables on it? IIS or Apache? ---------------------------------------------------------------------- madref · 2017-11-29 21:57 I really don't know. Because it runs a CMS (WordPress) i have access to my website and i have access to the database behind it. I can do that directly or through phpMyAdmin. I can also make and delete databases from my webspace. So to keep it simple for me i was thinking of recreating the same database i have local (on my laptop) and then some kind of way of accessing that. ---------------------------------------------------------------------- Phil · 2017-11-29 22:02 Quote from: madref on November 29, 2017, 10:57:27 pmI really don't know. Sometimes Web sites will provide PHP and allow you to run PHP software. They may also provide some database support. For example, WordPress needs PHP and MySQL (and HTTPS support) so your Web site probably has all of that. I would guess though that you would not be able to run just anything (eg, compiled Pascal executable, SQLite, etc.). Again, I would suggest AWS at least for the learning opportunity it will provide. You could always move WordPress over to an AWS server too. ---------------------------------------------------------------------- madref · 2017-11-29 22:22 Let's say i want to stick to my plan and recreate my database in phpmyadmin. How can i access this data? And let's say that this are the names and logins. Code: [Select]Database: cloud_referee Host: localhost username: cloud_referee password: JustAWord001 ---------------------------------------------------------------------- lainz · 2017-11-29 22:40 I have a PHP code like this to access a phpmyadmin database: Code: PHP  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i connect_error) {    die("Connection failed: " . $conn->connect_error);}  parse_str($_SERVER['QUERY_STRING']); if ($tag) {    // single tag must match    $sql = "SELECT * FROM emulators WHERE tags LIKE '%" . $tag . "%' ORDER BY downloads DESC";} else if ($search) {    // each word must match    $search = explode(" ", $search);    $find = "";    $c = count($search);    for ($i = 0; $i < $c; $i++) {        if ($i < $c-1) {            $find = $find . " (name LIKE '%" . $search[$i] . "%' OR description LIKE '%" . $search[$i] . "%') AND";        } else {            $find = $find . " (name LIKE '%" . $search[$i] . "%' OR description LIKE '%" . $search[$i] . "%')";        }    }    $sql = "SELECT * FROM emulators WHERE" . $find;} else {    // everything else    $sql = "SELECT * FROM emulators WHERE 1 ORDER BY downloads DESC";} $result = $conn->query($sql); if ($result->num_rows > 0) {    // output data of each row    while($row = $result->fetch_assoc()) {      $row["tags"] = explode(",", $row["tags"]);        $data[] = $row;    }} $conn->close();echo json_encode($data);?> This creates a JSON with the data, so you upload your .php file say to url/api/getdata Then you call that from JavaScript with a get('url/api/getdata') and you do whathever you want with the data. ---------------------------------------------------------------------- madref · 2017-11-29 22:51 Can't i access it directly from Lazarus? ---------------------------------------------------------------------- taazz · 2017-11-29 23:02 Quote from: madref on November 29, 2017, 11:51:37 pmCan't i access it directly from Lazarus? No, it is insecure to do so and all server installation have taken steps to ensure that mysql is only accessible from the LAN that the web server is part of. You need to write some sort of service (or a middle tire if you will) to give access to the database. ---------------------------------------------------------------------- lainz · 2017-11-29 23:04 Quote from: madref on November 29, 2017, 11:51:37 pmCan't i access it directly from Lazarus? Taaz is right And i already give you th ecode. Just do a get from Lazarus and parse the JSON. ---------------------------------------------------------------------- madref · 2017-11-29 23:10 hmmm...complicated ... Back to another thought: Since my database is stored locally now. How does lazarus access this database when i open it? Does it load the complete database into memory? Does it only load the part that is requested? In short how does lazarus do it? In other words what would happen if i put this file (database) on a certain spot on the internet and try to access that? ---------------------------------------------------------------------- taazz · 2017-11-29 23:13 Quote from: madref on November 30, 2017, 12:10:04 amhmmm...complicated ... Back to another thought: Since my database is stored locally now. How does lazarus access this database when i open it? Does it load the complete database into memory? Does it only load the part that is requested? In short how does lazarus do it? It allows the library used for the database to decide that. In general for every sql based database it only loads what ever the sql requests from the server/database (what ever a TSQlquery control has in memory) . The rest are simple locked on disk from access in case of sqlite. ---------------------------------------------------------------------- madref · 2017-11-29 23:20 So it would be possible to download the entire database (<200Kb) into memory. Or to keep the connection to that file open so that TSQLQuery can access it anytime. Is that recommendable? ---------------------------------------------------------------------- lainz · 2017-11-29 23:36 Quote from: madref on November 30, 2017, 12:20:00 amSo it would be possible to download the entire database (<200Kb) into memory. Or to keep the connection to that file open so that TSQLQuery can access it anytime. Is that recommendable? I think you will get a good response with a good question. What you want to achieve with all this? What are your goals? What you will do with the database once it is fully loaded into memory? ---------------------------------------------------------------------- taazz · 2017-11-29 23:59 Quote from: madref on November 30, 2017, 12:20:00 amSo it would be possible to download the entire database (<200Kb) into memory. yes you can download the complete database in memory or temp files and 200KB is smaller than most html pages out there. Quote from: madref on November 30, 2017, 12:20:00 amOr to keep the connection to that file open so that TSQLQuery can access it anytime. Is that recommendable? No. SQLite is a local database the file opened must be on the same computer the library is running. Once a connection is made to the database, the database is locked for all other connections. You need a single service that can accept multiple connections. You need to write a middle tire no way around it. The real question is why not share the database with your application or include it in the installation? If needed some kind of update procedure can be coded that will download updates from a server and apply them to a local database. ---------------------------------------------------------------------- madref · 2017-11-30 07:58 the purpose of this database is that i want to be able to access it anywhere in the world. So where ever i am i want to access this wether i am on my laptop or iPad or iPhone. And if i updated it on my iPad i want to see it on my laptop too. i have build a referee tracking database that i want to access rink- side. Discuss the game a referee did and give him or her a report, email it and being able to review it again when i am home. When i am on the road i want to use my iPad and not my laptop. When i am at home i want to use my laptop and not my iPad. So that is why i want to make it 'mobile' by using 'the cloud'. ---------------------------------------------------------------------- madref · 2017-11-30 08:00 Or is there another way to achieve this? ---------------------------------------------------------------------- taazz · 2017-11-30 12:01 Quote from: madref on November 30, 2017, 09:00:49 amOr is there another way to achieve this? create a small web page to work with. You can use brook or any pascal framework for that if you want, I would go with a more web available software though (php, asp.net etc). ---------------------------------------------------------------------- lainz · 2017-11-30 14:04 Quote from: madref on November 30, 2017, 09:00:49 amOr is there another way to achieve this? A website is the way to go, more easy than building a single app for desktop, another for tablet/mobile. The best is that is cross platform for every single device that can show a website. ---------------------------------------------------------------------- madref · 2017-11-30 14:47 ok... So i need to do the following steps: 1. Create the database (almost done) 2. Create a website. 3. Create the interface 4. Create the desktop version 5. Create the iPad version. Or am i forgetting something? ---------------------------------------------------------------------- taazz · 2017-11-30 14:55 Quote from: madref on November 30, 2017, 03:47:43 pmok... So i need to do the following steps: 1. Create the database (almost done) 2. Create a website. 3. Create the interface 4. Create the desktop version 5. Create the iPad version. Or am i forgetting something? almost here is the list proposed. 1. Create the database (almost done) 2. Create a website. 3. use a web browser to access the web site and edit/view your data. ---------------------------------------------------------------------- madref · 2017-11-30 15:09 No i don't want to use the website to access the data. i want to use the apps ---------------------------------------------------------------------- madref · 2017-11-30 15:13 And then i completed have to remake this little fellow. ---------------------------------------------------------------------- taazz · 2017-11-30 15:14 Quote from: madref on November 30, 2017, 04:09:08 pmNo i don't want to use the website to access the data. i want to use the apps then use your existing website to upload the database file, download it from your application and reupload it, replacing the old one if its needed and forget all about the web in any other form. Too much work for no benefits. ---------------------------------------------------------------------- madref · 2017-11-30 15:21 So then the steps are: 1. Download the database as a temp-file 2. Rename database on the server-side to ensure no other app can use it. 3. Run app 4. On/before exit app upload new database-file. 5. on upload complete delete old database server-side 6. delete temp-file ---------------------------------------------------------------------- rvk · 2017-11-30 15:25 Quote from: madref on November 30, 2017, 04:21:07 pmSo then the steps are: 1. Download the database as a temp-file 2. Rename database on the server-side to ensure no other app can use it. 3. Run app 4. On/before exit app upload new database-file. 5. on upload complete delete old database server-side 6. delete temp-file For the Lazarus side you could do it like that (and hope nothing goes wrong when your application crashes). (step 1&2 and 4&5 could probably be done by one PHP-page for downloading/uploading) And how are you going to create the iPad/iPhone app? ---------------------------------------------------------------------- madref · 2017-11-30 15:26 Quote from: rvk on November 30, 2017, 04:25:12 pm(step 1&2 and 4&5 could probably be done by one PHP-page for downloading/uploading)But you always build in failsafes. That is why i rename the database server-side so that you always have the old database when app crashes for instance Quote from: rvk on November 30, 2017, 04:25:12 pm And how are you going to create the iPad/iPhone app? Well that is an entire different discussion ---------------------------------------------------------------------- taazz · 2017-11-30 15:40 Quote from: madref on November 30, 2017, 04:21:07 pmSo then the steps are: 1. Download the database as a temp-file 2. Rename database on the server-side to ensure no other app can use it. 3. Run app 4. On/before exit app upload new database-file. 5. on upload complete delete old database server-side 6. delete temp-file depends on the degree of freedom you need/want. My personal preference would be to go the other way around. eg 1. Download the database as a temp-file 2. Run app 3. On/before exit app upload new database-file. 4. upload the changed database 5. Rename the old database on the server-side to ensure no data loss. 6. make sure that only the new database file can be downloaded 7. keep the temp database file and only re download it if the data need updating. when you are at home you download all the old files and merge them in one to make sure that no data is lost replacing or not the existing database file on the server if needed. In any case since this is going to be a single user application ee no way to have two version of the file edited in parallel by multiple downloads I guess your version looks safer but it does not cover in between updates or long execution/crashes etc. ---------------------------------------------------------------------- madref · 2017-11-30 17:42 Well thank you all for your thoughts