A few more features would make it useful for prototyping. The README says that it uses indexes to speed up queries, but it doesn't, at least not in current HEAD (05786eada4); it always queries all the records. It would be nice if it allowed comparisons on relationships other than just equality; for example, >, <, and "LIKE." Ideally, it would also handle handle nested structures.
I pushed the README before the indexing code, but it's coming by next week. I'm also planning to have other operators, as you suggest (basically, anything SQLite allows will be allowed).
Nested structures will take a bit more work, but I don't see why they shouldn't be possible (the index would just look like "mydict.somekey" rather than "mydict").
I'd like to keep the project small, but I don't see the above changes taking a lot of code. The index creation code is just 7 more lines.
Fair enough. Might I suggest you put a comment to the effect of "indexes are a work in progress" in the README for now? Would help users that just run into it on Github randomly.
Caveat: indexes don't currently work, I'm writing that code as we speak. SQLite supports any type of data in colums, regardless of what the column type actually is, which is what allows you to just define your fields to be indexed and have it work.
I think this project is a good fit for small, exploratory/prototyping projects that need powerful querying capabilities but don't know what the final schema will look like yet.
i don't follow why you need an extra table for each index. why can't you just define an index on the existing columns in the original table?
also, why do you manage the use of indices yourself? you say that you scan the largest index first. but if you just use sql indices then sql should do whatever is smartest automatically.
perhaps what i am missing is how you are storing the schemaless data? does that somehow drive the above? but even if that drives the need for two tables (one 'schemaless' in some way, and one for indices) you can get by with only a single extra table for indices (i think) - just add columns using "alter table". and that will allow the sql engine to select indices for you.
Well, the main table has a uuid and a blob that is a pickled dictionary. Your point about the extra table is correct, we can have one table with all the fields to be indexed in it and then just have SQLite create indexes on the groups we want.
I'll have to think a bit about that implementation, but it's most likely that that's the design that will win in the end, unless there's a complication that means it can't be done that way (however, I don't see a problem with it now).
The problem here is specifically with how SQLite does not implement ALTER TABLE fully. From the documentation:
"SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table."
16 comments
[ 4.0 ms ] story [ 47.6 ms ] threadhttp://news.ycombinator.com/item?id=3599633
I pushed the README before the indexing code, but it's coming by next week. I'm also planning to have other operators, as you suggest (basically, anything SQLite allows will be allowed).
Nested structures will take a bit more work, but I don't see why they shouldn't be possible (the index would just look like "mydict.somekey" rather than "mydict").
I'd like to keep the project small, but I don't see the above changes taking a lot of code. The index creation code is just 7 more lines.
I think this project is a good fit for small, exploratory/prototyping projects that need powerful querying capabilities but don't know what the final schema will look like yet.
That's what I'm using it for, anyway.
If anyone wants to write any unit tests, I would be happy to merge them into trunk.
also, why do you manage the use of indices yourself? you say that you scan the largest index first. but if you just use sql indices then sql should do whatever is smartest automatically.
perhaps what i am missing is how you are storing the schemaless data? does that somehow drive the above? but even if that drives the need for two tables (one 'schemaless' in some way, and one for indices) you can get by with only a single extra table for indices (i think) - just add columns using "alter table". and that will allow the sql engine to select indices for you.
I'll have to think a bit about that implementation, but it's most likely that that's the design that will win in the end, unless there's a complication that means it can't be done that way (however, I don't see a problem with it now).
"SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table."
http://www.sqlite.org/lang_altertable.html
I don't think it's too much of a problem to have different tables, though, most people would have single indexes, I guess.