Recent

Author Topic: SQLite3 database file in website folder  (Read 11095 times)

Elmug

  • Hero Member
  • *****
  • Posts: 849
SQLite3 database file in website folder
« on: July 06, 2012, 11:25:31 pm »
Hi everyone,

Windows7, SQLite3.

Once the application xxx.exe works with the database file xxx.s3db in my laptop,  can I just put the xxx.s3db in a website's folder where one has read/write power, and knowing the remote location of that file, can one then open the remote database identically with the same xxx.exe (just changing the database file location)?

Does it matter if the website runs in linux or is microsoft based; or, what to look for?

This is just for testing/learning purposes with little data in the database, and I don't have a website, yet.

Thanks a lot!
« Last Edit: July 06, 2012, 11:27:53 pm by Elmug »

denver

  • Jr. Member
  • **
  • Posts: 67
Re: SQLite3 database file in website folder
« Reply #1 on: July 07, 2012, 12:21:40 am »
You want to use the xxx.exe to open/read/write a remote SQLite database file vs internet ?
The answer is Yes and No

Yes -> Use a file server ( samba server / windows file sharing ) open a share folder on internet and your xxx.exe point to \\xxx.xxx.xxx.xxx\sharefolder\xxx.s3db

No -> It is not  practical  cause it is not stable and very slow.

 

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: SQLite3 database file in website folder
« Reply #2 on: July 07, 2012, 07:47:37 am »
You want to use the xxx.exe to open/read/write a remote SQLite database file vs internet ?
The answer is Yes and No

Yes -> Use a file server ( samba server / windows file sharing ) open a share folder on internet and your xxx.exe point to \\xxx.xxx.xxx.xxx\sharefolder\xxx.s3db

No -> It is not  practical  cause it is not stable and very slow.

Thanks a lot, denver.

I'll see if I can figure this out. :)

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: SQLite3 database file in website folder
« Reply #3 on: July 07, 2012, 08:02:00 am »
Hi again, denver, anyone:

If instead of an .exe, it were a Linux-Lazarus-made application, would a file-server such as Samba still be requiered?

Thanks for any replies.

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: SQLite3 database file in website folder
« Reply #4 on: July 07, 2012, 08:20:27 am »
Also, could something like this work?

Create an Online Version of a Local Area Network–the Free and Easy Way:
http://gigaom.com/collaboration/create-an-online-version-of-a-local-area-network-the-free-and-easy-way/

Thanks for any replies/opinions.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite3 database file in website folder
« Reply #5 on: July 07, 2012, 08:25:20 am »
Let's first look at how it works with an executable and sqlite. The executable loads the sqlite library (dll or so) into its process to get sqlite functionality.
Then the sqlite .so talks with the database file if it is asked to do something (open file, read/write data etc).

It uses the underlying file system provided by the operating system to access that file.
Ok, so one approach is to try and provide a network filesystem (NFS, Samba/Windows filesharing, WebDAV etc) so you can access the remote database.
The only "web server like" filesystem I know would be WebDAV - but there might be more.
You could also use a regular Linux server with SSH or NFS or Samba and use the SSHFS filesystem, NFS or Samba filesystem drivers.

The thing is, with a lot of these you don't have encryption that provides confidentiality/integrity mechanisms. If you want that, you can use SSHFS, or perhaps tunnel one of the other protocols over a VPN (e.g. openvpn) or SSH.
Perhaps Webdav also speaks SSL/TLS which would serve the same purpose.

You'll notice that all these variations don't involve modifying your program, but using other programs/manipulating operating system settings.

A second, very much more complicated way of doing things, is to write a filesystem driver on the client end that maps file operations on the database file in some way to operations on the remote server with whatever "read/write power" you have.
I'll leave that as unpractical

A third, even more unpractical way of doing things would be to modify the sqlite code so it directly reads/write to the web server using whatever "read/write power" you have instead of regular file operations.

Fourth: you could just download/read the database file before your program accesses the database, modify the file locally and upload/write the result when done. Here you will realistically obviously need the application's source code.

Fifth: you could write and install an application that acts as a proxy/communications channel using sqlite, and put it on the remote server with the database. Then adapt your local program to contact the remote proxy instead of the database directly.
This is actually fairly common; you can implement the communication protocol with e.g. web services using e.g. SOAP or JSON messages; there's a web services toolkit available for FreePascal (have a look at the wiki).

Finally: what is your purpose with this? Sqlite is designed as an embedded, file based database environment. Using a remote server could be done, but I think the only elegant solution would be solution 5.
An alternative could be to use a web host (or just hosting service, e.g. something like Amazon, RedHat Openshift, depending on their offerings, conditions etc), and use a "proper" client/server database on those; most likely mysql or PostgreSQL.
I wouldn't recommend mysql (see other threads on having to update the drivers because mysql seems to break compatibility on purpose)... but for testing it can be ok I suppose.

Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite3 database file in website folder
« Reply #6 on: July 07, 2012, 08:29:25 am »
can I just put the xxx.s3db in a website's folder where one has read/write power, and knowing the remote location of that file, can one then open the remote database identically with the same xxx.exe (just changing the database file location)?
As you can understand from the posts, it matters a lot what kind of read/write access you get: do you only have file upload/download functionality via http(s)? Do you have samba/smb access? Do you have ssh access? Etc.

Are you just looking for "web" servers?
Looking at the rest of your question, I suppose you're also asking "what kind of read/write acccess do I need to make this happen". Hope my longer post above gives you some ideas, but note: only http upload/download access will severely limit the options.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite3 database file in website folder
« Reply #7 on: July 07, 2012, 08:32:33 am »
Also, could something like this work?

Create an Online Version of a Local Area Network–the Free and Easy Way:
http://gigaom.com/collaboration/create-an-online-version-of-a-local-area-network-the-free-and-easy-way/

Thanks for any replies/opinions.
It could work - but then you're not talking about a remote web server - you're talking about a server where you can install this software.
It seems to be basically the same as using OpenVPN (it could well be based on it, in fact...) but presumably with a nice GUI.

You will have to indicate what you want again. I think you're not talking about a web server at all, but about accessing sqlite databases on a remote system in general.
What scenario do you have in mind? Getting to your home desktop from your brother's laptop? Something like that?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: SQLite3 database file in website folder
« Reply #8 on: July 07, 2012, 09:01:08 am »
Also, could something like this work?

Create an Online Version of a Local Area Network–the Free and Easy Way:
http://gigaom.com/collaboration/create-an-online-version-of-a-local-area-network-the-free-and-easy-way/

Thanks for any replies/opinions.
It could work - but then you're not talking about a remote web server - you're talking about a server where you can install this software.
It seems to be basically the same as using OpenVPN (it could well be based on it, in fact...) but presumably with a nice GUI.

You will have to indicate what you want again. I think you're not talking about a web server at all, but about accessing sqlite databases on a remote system in general.
What scenario do you have in mind? Getting to your home desktop from your brother's laptop? Something like that?

No, BigChimp,

The personal computer would only be clientes.

Wouldn't it be nice if a site would host a folder that would be like a local folder to a local pc, and one could update files without having to download them, using the same software already in the local pc.

That's what I am wondering if there is such a thing.

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: SQLite3 database file in website folder
« Reply #9 on: July 07, 2012, 09:21:51 am »
I've found this:

Windows Virtual Desktop Hosting:
http://www.ourinternet.us/virtual_desktop_hosting.html

This maybe of interest to what I was asking about.

Any thoughts/opinions/experience on using this?

Thanks!

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite3 database file in website folder
« Reply #10 on: July 07, 2012, 09:59:54 am »
You will have to indicate what you want again. I think you're not talking about a web server at all, but about accessing sqlite databases on a remote system in general.
What scenario do you have in mind? Getting to your home desktop from your brother's laptop? Something like that?


No, BigChimp,

The personal computer would only be clientes.

Elmug, I think you are misunderstanding me. I was asking you if you really were talking about a webserver, I indicated why I thought you aren't thinking of a webserver, then I asked you to confirm that you are talking about accessing a remote machine in general - not just a web server and I asked for what higher level goal you wanted to achieve. I then gave you an example, expecting you to correct me with the goal you have in mind instead of just saying "no, the example you gave is wrong"... which adds not much information to the discussion.

If you had told about your higher level goal, the confusion about server/web server/remote server would become irrelevant as we then talk on a different level.

In your post above, you still didn't confirm that you are not thinking about a web server after all.

Wouldn't it be nice if a site would host a folder that would be like a local folder to a local pc, and one could update files without having to download them, using the same software already in the local pc.
This is exactly why it is important to tell us what kind of server you are talking about, and what communication protocols it supports.... and incidentally is the question I answered in my earlier long post.

Your last post on virtual desktops etc implicitly confirms again that you are not talking about web servers at all.

It seems you and I just don't understand each other on this forum, no matter how hard I try.... and it's better for me to just not answer at all, and let others answer...
However, could I suggest once again to follow the FAQ on asking questions on the forums: it helps if you state your higher level goal as well, in case there is a much more efficient or easy way to reach that goal using different means.
If you are unsure about terminology (as an example: the web server in this thread) you can explicitly specify that (e.g. "some kind of remote server, I'm assuming a web server").
Clarifying those things up front makes it much easier to understand more precisely what you mean, what you want and even a bit what you know.

Good luck,
BigChimp
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: SQLite3 database file in website folder
« Reply #11 on: July 07, 2012, 10:05:08 am »
On windows vista or newer or on Linux you can mount a WebDAV directory as a local drive. WebDAV is a HTTP extension for read/write access to files.
More info:
http://en.wikipedia.org/wiki/WebDAV
http://smallvoid.com/article/winnt-webdav-network-drive.html
http://camille.wordpress.com/2006/03/12/webdav-mounting-under-linux/

On Linux you can also mount remote files accessible over SSH. http://en.wikipedia.org/wiki/SSHFS

OpenVPN type of solutions are also possible but require full control of the server.

All these solutions will be slow and you need to carefully investigate the consequence of simultaneous access to files from multiple users.


eny

  • Hero Member
  • *****
  • Posts: 1587
Re: SQLite3 database file in website folder
« Reply #12 on: July 07, 2012, 10:29:27 am »
Hi everyone,

Windows7, SQLite3.

Once the application xxx.exe works with the database file xxx.s3db in my laptop,  can I just put the xxx.s3db in a website's folder where one has read/write power
yes
Quote
, and knowing the remote location of that file, can one then open the remote database identically with the same xxx.exe (just changing the database file location)?
No.

Quote
Does it matter if the website runs in linux or is microsoft based; or, what to look for?
Yes.

Quote
This isI just for testing/learning purposes with little data in the database, and I don't have a website, yet.

Thanks a lot!
Ok.
All posts based on: Win10 (Win64); Lazarus 1.8.0 'stable' (#56594 win64) unless specified otherwise...

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: SQLite3 database file in website folder
« Reply #13 on: July 07, 2012, 10:58:56 am »
Hi BigChimp, Ludob, Eny, and thanks everyone for replying/helping.

I think I have found what I was after, and even more as offered by Thinkgrid:
http://www.thewhir.com/web-hosting-news/cloud-firm-thinkgrid-launches-microsoft-windows-7-hosted-virtual-desktops

They offer business solutions consisting of virtual Windows7 desktops that are accessible via the web.

One can put his own applications, and from any where, any pc, used one's virtual PC. Connections are secure, response is said to likely faster than if one is using local machine.

For instance, if I were to put an SQLite database there, and put the lazarus application.exe there too, even with large amounts of data, it would run EVERYTHING THERE, while I watch and interact requests from my local PC. Plus, it'd likely run faster than on my local PC.

I can also install on that virtual PC the PostgresQL server and hit it with apps installed on that local machine, so in a way, seems to me, I would have a web-based SQL server too, with likely not much different than having a web-hosted sql server.

In addition, anyone else who wants to group into the account, would have to have his private virtual machine, in the same manner, and there is a common folder that can be accessed by those grouped.

Part of the beauty is that the real local machines have nothing to do with the processing power of the virtual machines, and can be run from linux, and other operating systems.

This mode IS NOT a public model, which even fits my thoughts better. It is a business model, which is the reason for my o.p.

I am impressed with it, and off to verify things via a free trial. Costs seem reasonable, specially for business solutions.

Thanks everyone!

This is a very convincing video on the Virtual Windows7 desktops:
http://hvdi.com/video/8x6-30JuneHVDthinkgrid/8x6-30JuneHVDthinkgrid.html

P.S.: They also have exactly what I was asking for:
Hosted File Server
An alternative to a NAS box or office file server. A hosted file server is delivered securely from ThinkGrid's data centres and provides exactly the same experience as having the file server in your office. The only difference is that it can be accessed anywhere by anyone you wish to grant security clearance to, and we provide backup, security and archiving for your data so it will never be lost.
http://thinkgrid.com/file-collaboration-hostedfileserver.aspx
« Last Edit: July 07, 2012, 11:06:19 am by Elmug »