如果sqlite3满足条件则插入表
如果我有两个表:files(id, Owner)
和 share(file_id, user)
,其中所有者和用户将是理论用户表中的主 ID,那么如何仅当进行共享的用户拥有该文件时,我才在 share
中插入条目?
这是一个精简的示例,因此我将仅使用执行共享操作的文字 - 通常该值来自会话。因此,如果我有:
files: id: 1, owner: 1
并且用户 2 想要查看文件 1,我会使用此查询:
insert into share values (1, 2)
但这是不允许的 - 用户 2 不拥有文件 1,而用户 1 拥有。我试图在一个查询中执行此操作,但我无法弄清楚。我试过了:
case when (select owner from files where id=1) is 2
then (insert into share values (1, 2));
case (select owner from files where id=1) is 2
then (insert into share values (1, 2));
insert into share values (1, 2)
where 2 not in (select owner from files where id=1)
都是语法错误。我是通过 Python 执行此操作的,因此在此查询之后,我只需检查 Cursor.rowcount 来查看它是 1 还是 0,如果是 0,则用户没有权限来完成操作。
我怎样才能正确地编写这个查询?
if I have two tables: files(id, owner)
and share(file_id, user)
, where owner and user would be primary IDs from a theoretical user table, how can I insert an entry into share
only if the user doing the sharing owns that file?
This is a stripped down example, so I'll just use a literal for the one doing the share operation -- normally this value would come from a session. So if I had:
files: id: 1, owner: 1
and user 2 wants to see file 1, I would use this query:
insert into share values (1, 2)
but that isn't allowed -- user 2 doesn't own file 1, user 1 does. I am trying to do this in one query, but I can't figure it out. I have tried:
case when (select owner from files where id=1) is 2
then (insert into share values (1, 2));
case (select owner from files where id=1) is 2
then (insert into share values (1, 2));
insert into share values (1, 2)
where 2 not in (select owner from files where id=1)
and they are all syntax errors. I'm doing this from Python, so after this query, I would just check the Cursor.rowcount
to see if it is 1 or 0, and if it's 0, then the user didn't have permission to complete the operation.
How can I properly write this query?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
请允许我建议我的方法作为答案:
共享表中不需要
not null
子句或外键,没有异常可以捕获。Allow me to suggest my approach as an answer:
No need for a
not null
clause or foreign key in your Share table, no exception to catch.好吧,我找到了一种方法,尽管这不是我希望找到的解决方案。我可以将
not null
和/或外键添加到share
表中的file_id
列,然后尝试运行此查询:这样如果用户不拥有文件 ID,则选择查询将不返回任何内容,并且非空和/或外键约束将失败,并且我将在 Python 中得到异常。
如果有人知道真正的解决方案,请告诉我,使用这种方法我感觉非常肮脏。
Well, I found one way I could do it, though it's not the solution I was hoping to find. I could add
not null
and/or a foreign key to thefile_id
column in theshare
table, and then try running this query:that way the select query will return nothing if the user doesn't own the file id, and the not null and/or foreign key constraint will fail and I will get an exception in Python.
If anyone knows the real solution to this please let me know, I feel awfully dirty using this approach.