是否可以建立与在线.sqlite文件的只读连接? [英] Is it possible to establish a read-only connection to an online .sqlite file?

查看:179
本文介绍了是否可以建立与在线.sqlite文件的只读连接?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在我的项目中,我试图使用托管在Web服务器上的在线数据库,并且希望用户能够从其客户对我的数据库执行查询.

有没有一种我可以让他们发出和执行查询的方法,而不必让他们每次都下载整个数据库?

我尝试过的事情:

这是我正在使用的东西:(C#)

In my project, I am trying to use a online database which is hosted on my web server, and I want for users to be able to perform queries from their clients on my database.

Is there a way for me to allow them to issue and execute queries without having them download the entire database every time?

What I have tried:

Here is what I am working with: (C#)

namespace Test
{
    class Program
    {
        public static SQLiteConnection m_dbConnection;

        static void Main(string[] args)
        {
            m_dbConnection = new SQLiteConnection(@"Data Source=https://dl.mydomain.com/Urls.sqlite");
            m_dbConnection.Open();
            test();
        }

        private static void test()
        {
            String query = "SELECT * FROM table";
            SQLiteCommand prepQuery = new SQLiteCommand(query, m_dbConnection);
            SQLiteDataReader reader = prepQuery.ExecuteReader();

            Console.WriteLine(reader["testcolumn"].ToString());
        }
    }
}

推荐答案

不,您不能.客户端无法通过HTTP使用基于文件的数据库.

使用服务器上的SqlLite,您唯一的解决方案是创建客户端可以调用以对其进行数据库操作的Web Service或WebApi.
No, you can''t. The clients cannot use a file-based database over HTTP.

Using SqlLite on the server, your only solution is to create a Web Service or WebApi the clients can call to do the database operations for the them.


我建​​议阅读 SQLite文档 [ ^ ],它与我在问题评论中提到的内容完全相同.

I''d suggest to read SQLite documentation[^], which says exactly the same as i mentioned in the comment to the question.

报价:


服务器端数据库

系统设计人员报告说,使用SQLite作为在数据中心中运行的服务器应用程序上的数据存储,或者换句话说,使用SQLite作为特定于应用程序的数据库服务器的基础存储引擎,已经取得了成功.

在这种模式下,整个系统仍然是客户端/服务器:客户端将请求发送到服务器,并通过网络获得回复.但是,客户端请求和服务器响应不是高层SQL而是特定于应用程序,而不是发送通用SQL并获取原始表内容.服务器将请求转换为多个SQL查询,收集结果,进行后处理,过滤和分析,然后构造仅包含必要信息的高级答复.

开发人员报告说,在这种情况下,SQLite通常比客户端/服务器SQL数据库引擎更快.数据库请求由服务器序列化,因此并发不是问题.通过数据库分片"还可以提高并发性:对不同的子域使用单独的数据库文件.例如,服务器可能为每个用户有一个单独的SQLite数据库,因此服务器可以处理数百或数千个同时连接,但是每个SQLite数据库仅由一个连接使用.

...

客户端/服务器RDBMS可能工作得更好的情况

客户端/服务器应用程序

如果有许多客户端程序通过网络将SQL发送到同一数据库,请使用客户端/服务器数据库引擎而不是SQLite. SQLite可以在网络文件系统上工作,但是由于与大多数网络文件系统相关的延迟,因此性能不会很好.而且,文件锁定逻辑在许多网络文件系统实现中(在Unix和Windows上)都存在问题. .如果文件锁定无法正常工作,则两个或多个客户端可能会尝试同时修改同一数据库的同一部分,从而导致损坏.由于此问题是由底层文件系统实现中的错误引起的,因此SQLite无法阻止它.


Server-side database

Systems designers report success using SQLite as a data store on server applications running in the datacenter, or in other words, using SQLite as the underlying storage engine for an application-specific database server.

With this pattern, the overall system is still client/server: clients send requests to the server and get back replies over the network. But instead of sending generic SQL and getting back raw table content, the client requests and server responses are high-level and application-specific. The server translates requests into multiple SQL queries, gathers the results, does post-processing, filtering, and analysis, then constructs a high-level reply containing only the essential information.

Developers report that SQLite is often faster than a client/server SQL database engine in this scenario. Database requests are serialized by the server, so concurrency is not an issue. Concurrency is also improved by "database sharding": using separate database files for different subdomains. For example, the server might have a separate SQLite database for each user, so that the server can handle hundreds or thousands of simultaneous connections, but each SQLite database is only used by one connection.

...

Situations Where A Client/Server RDBMS May Work Better

Client/Server Applications

If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.


这篇关于是否可以建立与在线.sqlite文件的只读连接?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆