TW + sqlite = happiness?
Zap
an sqlite db to TW. the idea, of course, is that the tiddlers would
go into an sqlite file instead of the html file.
my reasons for considering such a thing are:
- it's a way to avoid TW file bloat as the tiddlers pile up.
- it's a way to possibly extend the reach of TW without (seriously)
breaking the ultra-portable ideal of TW.
- it's a way to have a personal wiki that can scale up _and_ have
access to all (or most?) of the cool stuff that's been built for TW.
i've searched "the archives" fairly thoroughly and i see that TW +
sqlite has been discussed now and again but i don't see that it's
actually ever been done ... and that's got me wondering:
are there any obvious reasons why this is a bad idea?
is it duplicating something that's more or less already been done?
(see below re: TiddlyWeb)
am i mistaken in thinking that portability would now be TW file +
sqlite file instead of just the TW file alone?
any obvious pitfalls that one should consider before launching into
such a thing?
i'm hoping that the addition of the sqlite stuff would (a) be a non-
issue as far as the end user is concerned (ie. they wouldn't see it)
and (b) fairly non-invasive insofar as TW's core goes (my programming
skills are a bit rusty so i'm not sure how thin the ice is here).
at the recent TW get-together in Paris it was suggested that TiddlyWeb
might be a good candidate for just such a project but as far as i can
see from poking around the whole "server side" concept of TiddlyWeb
pretty much renders the 'max portability' ideal of TW DOA: TW + sqlite
would be two files and you're on your way whereas TiddlyWeb is python
+ whatever else = a lot more than two simple files in order to be up
and running. i'd happily be corrected on this if i'm out to lunch
here.
so, there it is. very much looking forward to the thoughts of your
esteemed selves.
--zap.
FND
Not as far as I can tell.
Of course it breaks the single-file paradigm, but that doesn't mean it's
not acceptable.
I would guess that it's simply a case of nobody having gotten around
implementing anything - so I'm sure such a project would be very welcome.
> am i mistaken in thinking that portability would now be TW file +
> sqlite file instead of just the TW file alone?
I don't know much about SQLite, but I'm fairly sure that's the case. (Of
course the respective SQLite drives would be required on the client
machine.)
I'm also not sure whether JavaScript can access SQLite - though a quick
Google search suggests this might be possible:
http://tinyurl.com/5zbudy
(http://www.mail-archive.com/sqlite...@sqlite.org/msg34583.html)
> the whole "server side" concept of TiddlyWeb pretty much renders the
> 'max portability' ideal of TW DOA: TW + sqlite would be two files and
> you're on your way whereas TiddlyWeb is python + whatever else = a lot
> more than two simple files in order to be up and running
Well, you have to distinguish between client- and server scenarios.
Essentially, in the client scenario (i.e. when using a regular
TiddlyWiki), TW provides both the back- and front-end. However, in a
server scenario, you typically have different requirements (e.g.
multi-user support), with TiddlyWiki serving as the (smart) front-end.
Those two will continue to coexist.
-- F.
Zap
> Essentially, in the client scenario (i.e. when using a regular
> TiddlyWiki), TW provides both the back- and front-end. However, in a
> server scenario, you typically have different requirements (e.g.
> multi-user support), with TiddlyWiki serving as the (smart) front-end.
> Those two will continue to coexist.
mention that i was looking at TiddlyWeb from the point of view of
setting it up on my pc, so the "server" side and client side would all
be on the same machine. of course it would require all the server-
side support stuff like python, etc and that's where i started to back
away from the idea.
my thoughts are that going from a single-file paradigm to a two-file
paradigm is a worthy compromise if it opens up huge possibilities for
TW in terms of allowing large amounts of data without also creating a
massively bloated TW file.
--zap.
FND
> paradigm is a worthy compromise if it opens up huge possibilities for
> TW in terms of allowing large amounts of data without also creating a
> massively bloated TW file.
I agree that this could be very useful.
However, thinking about this, I'm not sure whether changing the store
mechanism alone actually solves the problem.
You'd still be shoving the entire data to memory on startup - so it
doesn't really make much of a difference where that data comes from.
We've run into this issue with the server-sides, and concluded that some
kind of on-demand loading will be required. However, that's a tricky
issue, and there are several potential solutions ("lazy loading" of
entire tiddlers or serving "skinny tiddlers" with only the metadata).
All of these impact search, lists of tiddler titles or tags etc.
-- F.
Zap
> doesn't really make much of a difference where that data comes from.
when they started migrating stuff into sqlite files for FF3. they've
said they need a db file in order to handle large amounts of data ...
but if that just gets loaded into memory then obviously they'd be
facing the same troubles we're speculating here.
> ... there are several potential solutions ("lazy loading" of
> All of these impact search, lists of tiddler titles or tags etc.
something on the order of a few 10s of milliseconds here and there? i
expect that's something we'd probably just have to try and see.
on the other hand i'm thinking we could get a ballpark idea by messing
with something already in FF. what if we imported a seriously bloated
bookmarks file? supposedly that would end up creating a large
places.sqlite file and that might give us some metrics as to what we
could expect if we migrated TW data out to sqlite, in terms of memory
usage and access times, etc.
i realize that this is kind of hunting moose by chasing mice but
still ... might be interesting. i think i'll noodle about with some
of this to see what happens.
as to the "lazy loading" and "skinny tiddlers" ideas they sound
great. has TW related work been done on these? i seem to
remember ... yes, it's something they were looking at for ccTiddy. i
wonder how applicable that stuff would be to a sqlite scenario.
--zap.
Paul Downey
>> You'd still be shoving the entire data to memory on startup - so it
>> doesn't really make much of a difference where that data comes from.
>
> good point. this makes me wonder what the Mozilla folks had in mind
> when they started migrating stuff into sqlite files for FF3. they've
> said they need a db file in order to handle large amounts of data ...
> but if that just gets loaded into memory then obviously they'd be
> facing the same troubles we're speculating here.
I guess Fred is eluding to the way TiddlyWiki loads tiddlers
from a store, not so much how sqllite works, hence the mention of
ideas for generic TiddlyWiki optimisation such as Lazy Loading,
skinny tiddlers, etc.
>> ... there are several potential solutions ("lazy loading" of
>> entire tiddlers or serving "skinny tiddlers" with only the metadata).
>> All of these impact search, lists of tiddler titles or tags etc.
>
> i can certainly see that they would ... but is it a serious hit or
> something on the order of a few 10s of milliseconds here and there? i
> expect that's something we'd probably just have to try and see.
yeah, as with any performance issues, they're difficult to predict,
and a lot depends on how the shape of, and the interactions with
the data. I wouldn't expect sqllite to be any kind of silver bullet,
but it does sound like an interesting idea for experiment.
I'd encourage you to noodle and to let us know what you come up with!
Best,
Paul
--
http://blog.whatfettle.com
Simon Baird
hello everyone, i'm interested in your collective thoughts on adding
an sqlite db to TW. the idea, of course, is that the tiddlers would
go into an sqlite file instead of the html file.
--
simon...@gmail.com
Clint Checketts
-Clint
Zap
thinking that it would be interesting to see what might happen if
someone gave it a shot so i think i'll start moving in that
direction. as i think i mentioned my programming skills are a little
rusty so it's going to take me some time to come up with anything.
as to Google Gears, i've got a general prejudice against Google (it is
the Borg y'know, ;) ) but i'll certainly look into it. my main goal
is to have this thing almost as easy to move around as TW itself is --
i'd like to have the whole thing easily, on a USB for instance -- so
any installation requirements are going to be a show stopper from my
pov.
if anyone has further thoughts on this -- suggestions,
recommendations, caveats, whatever -- please don't hesitate to chip
in. it's a pretty blank slate at the moment so input now is good and
welcome.
--zap.
FND
> interesting to see what might happen if someone gave it a shot
> so i think i'll start moving in that direction
That's great - looking forward to hearing about your progress!
> as to Google Gears, i've got a general prejudice against Google [...]
> my main goal is to have this thing almost as easy to move around as TW
> itself is
FWIW, I share those concerns.
-- F.
FND
>> doesn't really make much of a difference where that data comes from.
>
> good point. this makes me wonder what the Mozilla folks had in mind
> when they started migrating stuff into sqlite files for FF3.
Well, I assume there is some additional logic mediating between the
back- and front-end - essentially doing on-demand loading.
But that's a bit of a lopsided comparison, because tiddlers are
presumably quite different from bookmarks.
> as to the "lazy loading" and "skinny tiddlers" ideas they sound
> great. has TW related work been done on these?
Many discussions - no actual implementations yet.
Essentially, TiddlyWiki would have to be aware of the fact that content
is missing from the local store and has to be requested separately. As
mentioned before, that affects all sorts of operations - from full-text
processing (e.g. search, slices) to metadata evaluation (e.g. tags).
One of the biggest obstacles is that third-party plugins need the same
awareness.
> i wonder how applicable that stuff would be to a sqlite scenario.
A satisfying solution would most likely be applicable to any "detached
storage" scenario.
-- F.
Zap
> presumably quite different from bookmarks.
on our particular interest ... or not. at least it would be a place
to start.
> Essentially, TiddlyWiki would have to be aware of the fact that content
> is missing from the local store and has to be requested separately.
that all data is there and ready to be accessed en masse could be a
real stumbling block. needless to say one would like the sqlite part
of the picture to be transparent to pretty much all of TW, third party
plug-ins in particular. i can see i've got my work cut out for me on
this one. :o
--zap.
jpick
on how to write an 'adaptor' to do it.
Hopefully I can get a proof-of-concept functional in the next week or
so.
There's already SQLite support in the nightly Webkit builds, and it's
already enabled on the iPhone.
http://webkit.org/blog/126/webkit-does-html5-client-side-database-storage/
http://webkit.org/misc/DatabaseExample.html
http://jsgt.org/mt/archives/01/002157.html
http://www.sproutcore.com/static/photos/ (demo)
There might be some support in the IE8 betas, but I haven't tried it
yet. My understanding was that Microsoft was going to put it in.
There's Google Gears, of course, now for Safari/Webkit too:
http://code.google.com/apis/gears/api_database.html
For Firefox 2+, there's
http://developer.mozilla.org/en/Storage
For some of the things I want to do in the "TiddlyWiki as a Platform"
realm, I require this capability - so I'm motivated. :-)
I can see the vertical TiddlyWiki apps I'm envisioning saving
everything in a single html file for backups/exporting, but utilizing
a smaller html file + SQL for regular use (for specific applications
or larger wikis).
It should be possible to use HTML5 manifest files / Gears LocalServer
implementation for offline access as well (or Data URIs for
persistence on the iPhone). Using those technologies, it's possible
to have a local/offline TiddlyWiki without having to save to a
filesystem.
I really liked the way Trimpath Junction worked - I'd like to make
some plugins to make TiddlyWiki work in a similar way.
Cheers,
- Jim
Zap
one.
very interesting to hear that we're thinking along similar lines with
this, though i'm looking more in the sqlite direction than at Gears.
my thoughts were that since FF is already committed to sqlite it might
be wise to follow that lead rather than head off in a new direction.
well, that and my basic aversion to the Google plan to dominate the
webby universe.
fyi, i've found something somewhat related, "Offline SQLiteSVG
database applications with Firefox", that mentions the mozStorage
reference:
(here: http://svgopen.org/2008/papers/79-Offline_SQLiteSVG_database_applications_with_Firefox/)
per my exchange with FND i'm now realizing that the real challenge of
this project is to keep the database stuff from having a disruptive
ripple effect through the rest of TW, especially all those wonderful
plugins that everyone has written.
of course the ideal would be for it to be more or less completely
hidden at the "back end" of TW, so that tiddly reads and writes were
basically unaffected insofar as TW and the plugins were concerned. i
realize that that's unlikely to be universal -- i'll bet there are a
few plugins out there who access tiddlers in funky (read: troublesome)
ways -- but if it was generally true then it's probably still worth
the effort.
--zap.
Anthony Muscio
Tiddlers could be "archived" to the database, then using a database related plugin a query could return the tiddlers to visability in the virtual flat file.
I would recomend that a boot strap tiddler for the SQL service be created, by which I mean that the SQL Plugin, on initialising can perhaps read the databse file header and load and run the SQL Support code, such that it is only loaded once required. Of importiant note here is the SQL code should be contained either in the flat tiddlywiki file or within the database itself such that no seperate install is required.
FND
Yes - however, TiddlyWiki doesn't load data from file when it's
required, but only once during startup, pushing it all into memory.
Thus all processing expects the respective data (e.g. tiddler.tags) to
be available without having to go through a read cycle first.
If all data retrieval were further abstracted, encapsulated in a
function (e.g. tiddler.tags()), introducing on-demand loading should be
possible without breaking backwards compatibility - but as things are
now, I don't know how it could be done.
-- F.
Anthony Muscio
Import Tiddlers, Include tiddlers, external tiddlers etc.. "load data from file when it's required" do they not ?
Surly the only issue we have here is to be able to access a large number of tiddlers, on demand from an external file or service, and permit tiddler level locking of said tiddlers, then the subsequent return of changed tiddlers.
But consider;
Minimal (or otherwise) tiddlyWiki opens with minimal tiddlers, including sql access plugin.
SQL Access plugin reloads tiddlywiki but this time includes additional tiddlers (in memory from records flagged in DB), all of which are tagged as from the DB and some of which are temporary. Can also include plugins then executed by runtiddler plugin process.
Now one uses the tiddlywiki as if it were a single file. See advanced use below.
There are then two forms of save;
Save all in memory to file (Dont save temporary tiddlers) - good for single file export to include DB provided tiddlers.
All changed tiddlers tagged DB are "saved to the DB", unchanged tiddlers are unlocked.Save all tiddlers not tagged DB to File, Dont save temporary tiddlers.
Advanced Use.
Whilst operating in memory allow queries to be executed against the DB, these queries can just result in a rendered list or they can "import" external tiddlers as needed, or even tiddler headers (without Content) but containing a link that will import the tiddler from the DB. The external DB tiddlers will only consume memory when loaded.
Design considerations:
If possible keep the DB in a tiddlyWiki format or allow import export to any DB
Provide a random access to tiddlers in DB method
Perhaps a special DB Access tiddler can be populated that containes the index to each or sets of records in the DB file.
We can use a flatfile, random access DB model, or transaction based read, read update (lock), write changed unlock, don't change unlock, abandon etc...
Anyway - for what it is worth
jpick
Adaptor:
http://jimpick.com/test/clientside-storage.html
http://github.com/jpick/tiddlywiki-clientside-storage/tree/master
It's a bit incomplete, but it works (at least, using a WebKit nightly
build or an iPhone). I'm going to attempt to put together a
screencast to explain it a bit better.
Cheers,
- Jim
BidiX
http://BidiX.info
jpick
Obviously, it would be good to pair it up with iTW and see if I can
make an iTW that could work offline (maybe loaded through a Data
URI?). I'm going to try that out this week. There's a "MobileCamp
Vancouver" event on Saturday, so it would be nice to have something to
show off.
Cheers,
- Jim