Lazarus

Programming => Databases => Topic started by: madref on November 29, 2017, 10:39:22 am

Title: [Solved] Database in The Cloud
Post by: madref on November 29, 2017, 10:39:22 am
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?
Title: Re: Database in The Cloud
Post by: tudi_x on November 29, 2017, 12:53:28 pm
did you consider Amazon Web Services (AWS)?
Title: Re: Database in The Cloud
Post by: Leledumbo on November 29, 2017, 01:19:16 pm
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.
Title: Re: Database in The Cloud
Post by: DonAlfredo on November 29, 2017, 01:50:50 pm
Quote
did 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 !!
Title: Re: Database in The Cloud
Post by: marcov on November 29, 2017, 01:56:54 pm
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.

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.
Title: Re: Database in The Cloud
Post by: madref on November 29, 2017, 02:39:20 pm
I have an own website (which i pay for) and it runs a cms.


Can i install something on that?

Title: Re: Database in The Cloud
Post by: Almir.Bispo on November 29, 2017, 07:00:38 pm
You can do something like this:
part 1
https://www.youtube.com/watch?v=2XytLSkgyQw (https://www.youtube.com/watch?v=2XytLSkgyQw)
part 2:
https://www.youtube.com/watch?v=QmI3kIdPVK4 (https://www.youtube.com/watch?v=QmI3kIdPVK4)
Cloud (Can you create a dedicated server):
https://www.youtube.com/watch?v=7DSTotVI1_I&t=0s (https://www.youtube.com/watch?v=7DSTotVI1_I&t=0s)
Title: Re: Database in The Cloud
Post by: madref on November 29, 2017, 09:47:47 pm
Damn i don't speak that foreign language and i am using SQLite
And i am not working in windows but osx


Title: Re: Database in The Cloud
Post by: madref on November 29, 2017, 09:54:31 pm
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?
Title: Re: Database in The Cloud
Post by: taazz on November 29, 2017, 10:31:29 pm
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?
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.
Title: Re: Database in The Cloud
Post by: Phil on November 29, 2017, 10:36:34 pm
And 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.
Title: Re: Database in The Cloud
Post by: madref on November 29, 2017, 10:41:24 pm
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
Title: Re: Database in The Cloud
Post by: Phil on November 29, 2017, 10:43:03 pm
I 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?

Title: Re: Database in The Cloud
Post by: madref on November 29, 2017, 10:57:27 pm
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.



Title: Re: Database in The Cloud
Post by: Phil on November 29, 2017, 11:02:51 pm
I 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.
Title: Re: Database in The Cloud
Post by: madref on November 29, 2017, 11:22:54 pm
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
Title: Re: Database in The Cloud
Post by: lainz on November 29, 2017, 11:40:36 pm
I have a PHP code like this to access a phpmyadmin database:
Code: PHP  [Select]
  1. <?PHP
  2. $root = $_SERVER['DOCUMENT_ROOT'];
  3. include($root . "/emulators/api/index.php");
  4.  
  5. header('Content-Type: application/json');
  6. $data = array();
  7. $tag = false;
  8. $search = false;
  9.  
  10. // Create connection
  11. $conn = new mysqli($servername, $username, $password, $dbname);
  12. // Check connection
  13. if ($conn->connect_error) {
  14.     die("Connection failed: " . $conn->connect_error);
  15. }
  16.  
  17. parse_str($_SERVER['QUERY_STRING']);
  18.  
  19. if ($tag) {
  20.     // single tag must match
  21.     $sql = "SELECT * FROM emulators WHERE tags LIKE '%" . $tag . "%' ORDER BY downloads DESC";
  22. } else if ($search) {
  23.     // each word must match
  24.     $search = explode(" ", $search);
  25.     $find = "";
  26.     $c = count($search);
  27.     for ($i = 0; $i < $c; $i++) {
  28.         if ($i < $c-1) {
  29.             $find = $find . " (name LIKE '%" . $search[$i] . "%' OR description LIKE '%" . $search[$i] . "%') AND";
  30.         } else {
  31.             $find = $find . " (name LIKE '%" . $search[$i] . "%' OR description LIKE '%" . $search[$i] . "%')";
  32.         }
  33.     }
  34.     $sql = "SELECT * FROM emulators WHERE" . $find;
  35. } else {
  36.     // everything else
  37.     $sql = "SELECT * FROM emulators WHERE 1 ORDER BY downloads DESC";
  38. }
  39.  
  40. $result = $conn->query($sql);
  41.  
  42. if ($result->num_rows > 0) {
  43.     // output data of each row
  44.     while($row = $result->fetch_assoc()) {
  45.         $row["tags"] = explode(",", $row["tags"]);
  46.         $data[] = $row;
  47.     }
  48. }
  49.  
  50. $conn->close();
  51. echo json_encode($data);
  52. ?>

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.
Title: Re: Database in The Cloud
Post by: madref on November 29, 2017, 11:51:37 pm
Can't i access it directly from Lazarus?

Title: Re: Database in The Cloud
Post by: taazz on November 30, 2017, 12:02:30 am
Can'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.
Title: Re: Database in The Cloud
Post by: lainz on November 30, 2017, 12:04:47 am
Can'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.
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 12:10:04 am
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?
Title: Re: Database in The Cloud
Post by: taazz on November 30, 2017, 12:13:44 am
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?
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.
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 12:20:00 am
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?
Title: Re: Database in The Cloud
Post by: lainz on November 30, 2017, 12:36:41 am
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?

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?
Title: Re: Database in The Cloud
Post by: taazz on November 30, 2017, 12:59:33 am
So 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.
Or 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.
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 08:58:47 am
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'.
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 09:00:49 am
Or is there another way to achieve this?
Title: Re: Database in The Cloud
Post by: taazz on November 30, 2017, 01:01:12 pm
Or 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).
Title: Re: Database in The Cloud
Post by: lainz on November 30, 2017, 03:04:54 pm
Or 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.
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 03:47:43 pm
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?

Title: Re: Database in The Cloud
Post by: taazz on November 30, 2017, 03:55:49 pm
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?
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.
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 04:09:08 pm
No i don't want to use the website to access the data.
i want to use the apps
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 04:13:27 pm
And then i completed have to remake this little fellow.
Title: Re: Database in The Cloud
Post by: taazz on November 30, 2017, 04:14:15 pm
No 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.
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 04:21:07 pm
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
Title: Re: Database in The Cloud
Post by: rvk on November 30, 2017, 04:25:12 pm
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
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?
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 04:26:41 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



And how are you going to create the iPad/iPhone app?

Well that is an entire different discussion  ;)



Title: Re: Database in The Cloud
Post by: taazz on November 30, 2017, 04:40:44 pm
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
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.
Title: Re: Database in The Cloud
Post by: madref on November 30, 2017, 06:42:37 pm
Well thank you all for your thoughts