def test_select_depth(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_directory("level_1")
fs.add_directory("level_1/level_2")
fs.add_directory("level_1/level_2/level_3")
fs.add_file(path="level_0_file", size=10)
fs.add_file(path="level_1/level_1_file", size=10)
fs.add_file(path="level_1/level_2/level_2_file", size=10)
fs.add_file(path="level_1/level_2/level_3/level_3_file", size=10)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse(
"SELECT file_name, depth FROM . WHERE file_type = 'F'")
result = list(database.query(query, path_id_cache))
assert len(result) == 4
expected = [
("level_0_file", 1),
("level_1_file", 2),
("level_2_file", 3),
("level_3_file", 4),
]
sorted_result = sorted(result, key=operator.attrgetter("file_name"))
assert [(r.file_name, r.depth) for r in sorted_result] == expected
def test_select_case(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="first_file", size=10)
fs.add_file(path="second_file", size=100)
fs.add_file(path="third_file", size=200)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("""
SELECT file_name, CASE
WHEN file_size < 100 then '<100'
WHEN file_size > 100 then '>100'
ELSE '=100' END
AS 'case_query'
from . WHERE file_type = 'F'
""")
result = list(database.query(query, path_id_cache))
assert len(result) == 3
assert result[0].file_name == "third_file"
assert result[0][1] == ">100"
assert result[1].file_name == "second_file"
assert result[1][1] == "=100"
assert result[2].file_name == "first_file"
assert result[2][1] == "<100"
def test_select_all_items_multiple_levels(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_directory("subdir1")
fs.add_directory("subdir2")
fs.add_directory("subdir2/subdir3")
fs.add_file(path="file1", size=100)
fs.add_file(path="subdir1/file2", size=200)
fs.add_file(path="subdir1/file3", size=300)
fs.add_file(path="subdir2/file4", size=400)
fs.add_file(path="subdir2/subdir3/file5", size=500)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("SELECT * FROM .")
result = list(database.query(query, path_id_cache))
assert len(result) == 9
expected = [
".",
"file1",
"file2",
"file3",
"file4",
"file5",
"subdir1",
"subdir2",
"subdir3",
]
assert sorted(r.file_name for r in result) == expected
def test_select_group_concat(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_directory("dir1")
fs.add_directory("dir2")
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("SELECT GROUP_CONCAT(file_name) FROM .")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0][0] == ".,dir1,dir2"
def test_expression(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="file1", size=100)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse(
"SELECT (file_size-1)*10 FROM . WHERE file_name = 'file1'")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0][0] == 990
def test_select_collate(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="file1", size=100)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("""
SELECT file_name FROM . WHERE file_name = 'fIlE1' COLLATE NOCASE
""")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0].file_name == "file1"
def test_select_avg(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="first_file", size=3)
fs.add_file(path="second_file", size=100)
fs.add_file(path="third_file", size=200)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("SELECT avg(file_size) FROM . WHERE file_type = 'F'")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0][0] == 101.0
def test_select_where_like(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="first_file", size=100)
fs.add_file(path="second_file", size=100)
fs.add_file(path="third_file", size=100)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("SELECT * from . WHERE file_name LIKE 'second_%'")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0].file_name == "second_file"
def test_select_alias(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="file1", size=100)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse(
"""SELECT file_name as file_name_alias FROM . WHERE file_name_alias = 'file1'"""
)
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0].file_name_alias == "file1"
def test_select_count(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_directory("dir1")
fs.add_directory("dir2")
fs.add_directory("dir3")
fs.add_directory("dir4")
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("SELECT COUNT(*) FROM .")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0][0] == 5
def test_select_distinct(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="file1", size=100)
fs.add_file(path="file2", size=100)
fs.add_file(path="file3", size=100)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse(
"SELECT DISTINCT file_size FROM . WHERE file_type = 'F'")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0][0] == 100
def test_select_datetime_function(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="file1", size=100)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("""
SELECT strftime('%m', creation_time) as ctime
FROM . WHERE file_name = 'file1'
""")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert int(result[0].ctime) == datetime.datetime.now().month
def test_select_where_between(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="first_file", size=10)
fs.add_file(path="second_file", size=100)
fs.add_file(path="third_file", size=200)
fs.add_file(path="fourth_file", size=1000)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("SELECT * from . WHERE file_size BETWEEN 50 AND 500")
result = list(database.query(query, path_id_cache))
assert len(result) == 2
assert result[0].file_name == "third_file"
assert result[1].file_name == "second_file"
def test_select_where_expr(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="file1", size=11)
fs.add_file(path="file2", size=22)
fs.add_file(path="file3", size=33)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse(
"SELECT * from . WHERE (file_size % 2) == 0 AND file_type = 'F'")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0].file_name == "file2"
assert result[0].file_size == 22
def test_select_one_file_one_level(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="file1", size=100)
fs.add_file(path="file2", size=200)
fs.add_file(path="file3", size=300)
fs.add_file(path="file4", size=400)
fs.add_file(path="file5", size=500)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("SELECT * FROM . WHERE file_name = 'file5'")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
expected = ["file5"]
assert sorted(r.file_name for r in result) == expected
def test_select_where(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="file1", size=10)
fs.add_file(path="file2", size=20)
fs.add_file(path="file3", size=30)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse(
"SELECT file_name, file_size FROM . WHERE file_type = 'F' AND file_size = 20"
)
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0].file_name == "file2"
assert result[0].file_size == 20
def test_select_where_not_in(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_file(path="first_file", size=100)
fs.add_file(path="second_file", size=100)
fs.add_file(path="third_file", size=100)
fs.add_file(path="fourth_file", size=100)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse(
"SELECT * from . WHERE file_name NOT IN ('first_file', 'second_file')")
result = list(database.query(query, path_id_cache))
assert len(result) == 3
assert result[0].file_name == "."
assert result[1].file_name == "third_file"
assert result[2].file_name == "fourth_file"
def test_select_all_files_from_subdir(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_directory("subdir1")
fs.add_directory("subdir2")
fs.add_directory("subdir2/subdir3")
fs.add_file(path="file1", size=100)
fs.add_file(path="subdir1/file2", size=200)
fs.add_file(path="subdir1/file3", size=300)
fs.add_file(path="subdir2/file4", size=400)
fs.add_file(path="subdir2/subdir3/file5", size=500)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("SELECT * FROM subdir1 WHERE file_type = 'F'")
result = list(database.query(query, path_id_cache))
assert len(result) == 2
expected = ["file2", "file3"]
assert sorted(r.file_name for r in result) == expected
def test_select_sum_group_by_having(fs, database, parser):
import ifsql.database
import ifsql.analyse
path_id_cache = {}
fs.add_directory("dir1")
fs.add_directory("dir2")
fs.add_file(path="dir1/file1", size=10)
fs.add_file(path="dir1/file2", size=100)
fs.add_file(path="dir2/file1", size=20)
fs.add_file(path="dir2/file2", size=200)
ifsql.analyse.walk(fs.root, database, path_id_cache)
query = parser.parse("""
SELECT file_name, sum(file_size) as total_sum FROM .
WHERE file_type = 'F' GROUP BY file_name HAVING total_sum < 100
""")
result = list(database.query(query, path_id_cache))
assert len(result) == 1
assert result[0][1] == 30
