create table mytable (f1, f2, f3, ..., data blob); -- duplicate the data OR create table mytable (f1, f2, f3, ..., dref integer); -- reference the data (e.g. record position in file)
and providing the values of the internal fields on insert -----Ursprüngliche Nachricht----- Von: Hayden Livingston [mailto:halivings...@gmail.com] Gesendet: Samstag, 31. Mai 2014 04:46 An: General Discussion of SQLite Database Betreff: [sqlite] Storing opaque data, but would like sorted indexes on some fields I have a binary format that is effectively structured data. I currently have multiple indexes but none of them are sorted, because in my toy system ORDER BY's are not supported, an implicit ORDER BY time of record inserted exists because it is a single threaded application. My indexing story is that I know a priori what my index will be, and I just double write them. I want to support ORDER BY on basically one additional field, and I'm having a hard time coming up with a solution that doesn't involved effectively writing on my B+Tree which has knowledge of my serialization binary format. I've thought to myself, maybe I could just store a duplicate copy of the data sorted by this field into SQLite. The data footprint is not small, but I'm willing to pay that disk cost if that's the only option. My only concern with moving to SQLite is performance design decisions that are built into a database. My system is basically only doing the work needed to iterate over my data. No concurrency, sequential reads. When/If I move to SQLite, what kind of things will I start to pay for? Concurrency? Other fluff data structure needs for SQLite? Obviously I imagine that whatever field I choose to get a sorted index on, I expose as it as a field. Then the non SQLite question: how much work would it be to duplicate a B-tree like data structure for my binary data? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ----------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
