TiddlyWeb sqlstore (testers required)

[chris...@gmail.com]

Over the past couple of days I got inspired to write a storage system
for TiddlyWeb that uses Python's SQLAlchemy[1] library to interact
with an RDBMS of your choice[2]. I've run the tiddlyweb test suite
against both sqlite3 and mysql by simply changing a line in the
tiddlywebconfig.py file.

Things seem to be working correctly, in that the tests are passing and
I can run instances using the new store, but I would appreciate some
testing and review by other interested folk for at least three
reasons:

* Bugs are probably lurking.

* Writing for SQLAlchemy and RDBMS is not really my thing and I'm sure
I've made things more crufty than they really need to be.[3] I decided
it was best to make it work, and then make it right later.

* The install and use process is probably entirely too obscure, so
don't take on this task hoping it will just work without first needing
to read the READMEs and such.

The code can be found on github in the tiddlyweb-plugins repo:
http://github.com/tiddlyweb/tiddlyweb-plugins/tree/master/sqlstore

To make it work with few warnings, you will need the very latest
TiddlyWeb which has been updated to be a bit more unicode savvy.

If you want to use it with an existing instance store, there is a
plugin called migrate[4] which can migrate the content of one store to
another. I recommend you backup your content before doing this. That
plugin is at:
http://github.com/tiddlyweb/tiddlyweb-plugins/tree/master/migrate

It's expected that this store will become the basis for some kind of
"enterprise(y) ready"[5] storage system for TiddlyWeb; more work will
be done on it to make it high performance than will be done on the
default text store which is designed primarily to be inspected.

If you are able to do some work with this and encounter bugs or have
suggestions, please post.

[1] http://www.sqlalchemy.org/

[2] sqlalchemy has an abstract interface to various python libraries
that talk to various database systems. If there is python library
support for the database, then it is likely sqlalchemy can talk to it,
from the same code. I've tested with sqlite3 and mysql, as that's what
I have access to.

[3] My main concern is that to get things initially working I mapped
TiddlyWeb objects to new objects in the storage module, which are then
mapped by SQLAlchemy into the database. This could probably be
shortened to just one mapping, but this may have an adverse impact on
the TiddlyWeb objects (as demonstarted by the need to reestablish the
'roles' set on the User object in user_get).

[4] I'm quite proud of this very simple plugin. It demonstrates nicely
why the storage interface system on TiddlyWeb is a good thing.

[5] Whatever that means.

[Oveek]

I'm glad you started working on this when you did, because I was
thinking about it as well. To use TiddlyWeb seriously--my intended use
case is as a collaboration tool in a corporate environment--I think a
database backend becomes necessary.

Before I plunge in I just wanted to find out what the situation is
generally? I'd like to try using PostgreSQL instead of MySQL.

On May 23, 11:14 pm, "cd...@peermore.com" <chris.d...@gmail.com>
wrote:

[chris...@gmail.com]

On Jun 3, 2:52 am, Oveek <mov...@gmail.com> wrote:
> I'm glad you started working on this when you did, because I was
> thinking about it as well. To use TiddlyWeb seriously--my intended use
> case is as a collaboration tool in a corporate environment--I think a
> database backend becomes necessary.

This seems to be the general consensus, although its mostly
speculation. There are definitely hotspots in the text store when bags
gets especially large or the number of revisions per tiddler gets
especially high.

I meant to update http://tiddlyweb.peermore.com/ to use the sql store
last night, but got distracted writing a thing called multistore which
lets you write to or read from multiple stores.[1]

> Before I plunge in I just wanted to find out what the situation is
> generally? I'd like to try using PostgreSQL instead of MySQL.

According to the SQLAlchemy docs, using PostgreSQL should "just work"
by changing the db_config part of server_store in tiddlywebconfig.py:

'server_store': ['sql', {'db_config': 'sqlite:///test.db'}],

This section of the SQLAlchemy docs has more detail:

http://www.sqlalchemy.org/docs/05/dbengine.html#supported-dbapis

Please report on how it goes for you. Thanks!

[1] http://github.com/tiddlyweb/tiddlyweb-plugins/tree/master/multistore

[tony]

On May 23, 8:14 am, "cd...@peermore.com" <chris.d...@gmail.com> wrote:
> If you are able to do some work with this and encounter bugs or have
> suggestions, please post.
>
> [1]http://www.sqlalchemy.org/

Hi,

I post this to any who may have had difficulty with installation.

Installation is not difficult but I don't know any better.

+1 completed successful test under winxp i686-pc with cygwin!

Whew!

My journey:

Step 1: install mysql or sqlite since there is no package for cygwin

compile from source sqlite-3.6.14.2

** important I tried compiling at the typical windows dir /cygdrive/c/
Documents and Settings/<username>
and kept getting configuration errors:

/bin/sh: /cygdrive/c/Documents: No such file or directory
configure: WARNING: `missing' script is too old or missing

UGH!
I could not install mysql so I tried sqlite instead, but the principal
is the same with that crap spaces in the directory and probably an
easy fix but took me forever to figure it out.

instead move the sqlite-3.6.14.2 to /cygdrive/c

and unpack tar zvxf sqlite-amalgamation-3.6.14.2.tar.gz
do a ./configure; make; make install

Step 2. Make sure you do an easy_install -U tiddlyweb to get the
latest version per README
This is TiddlyWeb 0.9.37

Step 3. move the sql plugin into your store instance

mywikistuff is my store instance

~/mywikistuff

here's my store contents:

$ ls
migrate.py migrate.pyc sql.py sql.pyc store test tiddlyweb.log
tiddlywebconfig.py tiddlywebconfig.pyc

I threw a lot of other crap in there because I didn't know what would
make the sql plugin work.

Then guess what, I couldn't find it in the README, but you must
install sqlacademy!!! <smacks head>

Step 4. easy_install sqlalchemy

Step 5. Edit or make sure your tiddlyconfig file has some config.

Here's my tiddlywebconfig (sorry about the comments, I tried so many
things!!!):

$ less tiddlywebconfig.py
# A default config, make your own changes here.
config = {
# 'twanager_plugins': ['migrate'],
# 'server_store': ['text', {'store_root': 'store'}],
# 'target_store': ['sql', {'db_config': 'sqlite:///test.db'}],
# using sqlite

'server_store': ['sql', {'db_config': 'sqlite:///test.db'}],

# using mysql
#'server_store': ['sql',
# {'db_config': 'mysql://localhost/tw?charset=utf8'}],
}

Step 6. start up the server with ye olde twanager server 127.0.0.1
8080 or whatever localhost

Step 7. open up the browser and go to http://127.0.0.1:8080/

Step 8. enjoy no bags but now a nice genuine test.db will be in your
store and you completed the test!!

Welcome to satori.

Best,
tony

[Alex Hough]

Hi Chris,

I would like to test.
I've been using TW for a while, but have  no knowledge of SQL and databases.
There are some very basic things I need to learn.

ALex

2009/6/4 tony <cca...@gmail.com>

--
t: 0161 442 2202
m: 0781 372 50 17
skype: alexhough
delicious: alexhough

[Alex Hough]

Hi Chris,

before i start climbing a potentially steep learning curve;

Is this flavour of TiddlyWeb going to be the standard one?
In conversation with Jeremy, I picked up that relational databases along with hierarchies we not his favourite concepts. Being a TiddlyFan,

I am wondering (without any knowledge - which perhaps is a paradox?)  if I should get into relational databases if TiddlyWeb and all things generally are in the anti relational camp.

Jeremy also put me on to Ted Nelson's zzSturucture [1].  I don't want to become too focused on a particular tehcnology if it may be a sunk cost in the near future.

Basically I want to run the coolest TiddlyWeb, not the most corporate friendly.

Alex
[1] http://xanadu.com/zigzag/

2009/6/4 Alex Hough <r.a....@googlemail.com>

[chris...@gmail.com]

On Jun 4, 1:55 am, tony <cca...@gmail.com> wrote:
> Step 3. move the sql plugin into your store instance
>
> mywikistuff is my store instance
>
> ~/mywikistuff

As you noticed, getting sql plugin in there is not enough for your
exiting content to show up in the database using, you have to use the
migrate plugin.

> Then guess what, I couldn't find it in the README, but you must
> install sqlacademy!!! <smacks head>
>
> Step 4. easy_install sqlalchemy

I've added this step to the README.

> Step 5. Edit or make sure your tiddlyconfig file has some config.
>
> Here's my tiddlywebconfig (sorry about the comments, I tried so many
> things!!!):

I've deleted everything that isn't relevant to give the simple
version:

config = {
 'server_store': ['sql', {'db_config': 'sqlite:///test.db'}],

}

> Step 8. enjoy no bags but now a nice genuine test.db will be in your
> store and you completed the test!!

Yeah, this is because nothing has been migrated. The README in the
migrate plugin[1] explains how to do that. Make sure you make a
backup!

[1] http://github.com/tiddlyweb/tiddlyweb-plugins/tree/master/migrate

[chris...@gmail.com]

On Jun 4, 8:06 am, Alex Hough <r.a.ho...@googlemail.com> wrote:
> before i start climbing a potentially steep learning curve;
>
> Is this flavour of TiddlyWeb going to be the standard one?

I don't intend for any particular store to be a standard. The core
distribution of TiddlyWeb will always include the text store, and it
will always behave reasonable well under usual circumstances. Other
stores will be created for special circumstances, hopefully lots of
them from lots of different people.

> In conversation with Jeremy, I picked up that relational databases along
> with hierarchies we not his favourite concepts. Being a TiddlyFan,

I'm not a fan of RDBMS and hierarchies either but using one with
TiddlyWeb does change TiddlyWeb's nature in any essential way, just
changes its performance characteristics[1].

> I am wondering (without any knowledge - which perhaps is a paradox?)  if I
> should get into relational databases if TiddlyWeb and all things generally
> are in the anti relational camp.

Unless you have some driving desire or need to use the sqlstore with
TiddlyWeb, I wouldn't bother. It exists for people who think they have
a performance need that might be satisfied by such a thing, have a
commitment to storing their data in a relational DB, or just like such
things. If none of those things map to you, then I should worry too
much, especially if you experience with hacking around in Python and
installing Python based web services is short.

If you have time and interest to learn, there's a lot to learn with
this stuff. I've certainly learned a _great_ deal while working on
TiddlyWeb.

> Jeremy also put me on to Ted Nelson's zzSturucture [1].  I don't want to
> become too focused on a particular tehcnology if it may be a sunk cost in
> the near future.

ZigZag is very cool, something I've always wanted to play with and
never had the chance.

> Basically I want to run the coolest TiddlyWeb, not the most corporate
> friendly.

There are bound to be far cooler TiddlyWeb stores in the future.

[1] There is an untested assumption that a properly optimized and
indexed RDBMS based store for TiddlyWeb will be faster and more
scalable than some other options.

[tony]

Thank you Chris!

This is a great example of a wonderful plugin that highlights
TiddlyWeb's extensibility.

Simply awesome!!!

For those following at home,
First I added the migrate.py to my instance, then edited the
tiddlywebconfig.py flipping over Chris' example of text store to
sqlite.db
then ran the migrate command on twanager like so:

$ twanager migrate
migrate users
migrate recipes
putting recipe default
migrate bags
putting tiddler ServerSideSavingPlugin:1 in bag system
putting tiddler TiddlyWebAdaptor:1 in bag system
putting tiddler TiddlyWebConfig:1 in bag system

Now there is a sqlite db added to my instance
$ ls
migrate.py sql.py store test.db tiddlywebconfig.py
wiki.db
migrate.pyc sql.pyc test tiddlyweb.log tiddlywebconfig.pyc

Then I comment the migrate lines (so I don't forget them in the
future) and add the sql config to the tiddlywebconfig.py.
This will allow Tiddlyweb to use the newly created sqlite db as a
store!

Here's my tiddlywebconfig.py

# A default config, make your own changes here.
config = {
#'twanager_plugins': ['migrate'],
#'server_store': ['text', {'store_root': 'store'}],

#'target_store': ['sql', {'db_config': 'sqlite:///wiki.db'}],
# using sqlite
'server_store': ['sql', {'db_config': 'sqlite:///wiki.db'}],

# using mysql
#'server_store': ['sql',
# {'db_config': 'mysql://localhost/tw?charset=utf8'}],
}

Finally to validate that indeed it worked, I opened up tiddlyweb,
saved a test tiddler then went back and checked it using sqlite.

Voila, ici:

~/mywikistuff
$ sqlite3 wiki.db
SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from tiddlers;
433843e0-0171-4fab-a3f6-e4df671abe28|ServerSideSavingPlugin|system
8ea350de-ee94-4bbb-8154-843420a2f7aa|TiddlyWebAdaptor|system
44b50275-9428-4dd8-8d4c-bd249cca1f51|TiddlyWebConfig|system
3e3ab514-5719-4dfc-8a7b-33d5e5f8f865|TestTiddler|common

Best,
tony

[tony]

Hi,

More testing..

I was able to successfully save to the sqlite3 db located in a shared
drive so it's great that TiddlyWeb can access the sql store from both
one's local machine and another shared volume.

I wasn't able to post the issue on github due to a high demand, but I
found I couldn't get local image links to work whether accessing the
sql store from local or shared drives.

For some reason, the image doesn't render. It renders fine on a plain
tiddlywiki though.

All these image links in a tiddler work [1] in a plain TiddlyWiki but
only the http link works in TiddlyWeb

I did get an 'Error saving <tiddler>: edit conflict' notification, but
it only occurs for this particular tiddler test case.

The odd thing is when I tried to delete the offending tiddler I
deleted? it (not in timeline or all) but still got a 'Error removing
<tiddler>' notification

I thought the error came from when I bungled the img link forgetting
the file:/// protocol, but I can't seem to reproduce the error.

Unfortunately the offending 'deleted' tiddler reappears when the
tiddlyweb is accessed again. It won't die!

More peculiar, I couldn't find errors with the tiddlyweb.log [2]

Time to try this on a Mac. ;-)

Best,
tony


[1] image links
[img[hello|file:///Z:/path0/path1/path2/images/path3/Actinomyces
%20israelii.jpg]]
[img[file:///C:\Documents and Settings\tony\My Documents\My Pictures
\flow.png]]
[img[file:///C:/Documents%20and%20Settings/tony/My%20Documents/My
%20Pictures/flow.png]]
[img[labyrinth|file:///C:/Documents%20and%20Settings/tony/My
%20Documents/My%20Pictures/Labyrinth_2_(from_Nordisk_familjebok).png]]
[img[http://www.google.com/intl/en_ALL/images/logo.gif]]

[2] some server log
2009-06-04 12:27:41,680 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:27:41 ] "PUT /bags/common/tiddlers/TestTiddlerImages?
nocache=0.16741631370414622 HTTP/1.1" 412 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Windows;
U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:27:46,055 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:27:46 ] "DELETE /bags/common/tiddlers/TestTiddlerImag
es?nocache=0.4106210587202004 HTTP/1.1" 412 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Window
s; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:30:09,180 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:30:09 ] "DELETE /bags/common/tiddlers/TestTiddlerImag
es?nocache=0.040967934125232586 HTTP/1.1" 412 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Wind
ows; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:30:09,243 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:30:09 ] "PUT /recipes/default/tiddlers/2009.06.04%20T
hu?nocache=0.7001774076251019 HTTP/1.1" 204 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Window
s; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:30:14,899 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:30:14 ] "DELETE /bags/common/tiddlers/TestTiddlerImag
es?nocache=0.18145093328484996 HTTP/1.1" 412 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Windo
ws; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:30:14,961 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:30:14 ] "PUT /recipes/default/tiddlers/2009.06.04%20T
hu?nocache=0.7815806525289227 HTTP/1.1" 204 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Window
s; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:31:09,524 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:31:09 ] "DELETE /bags/common/tiddlers/TestTiddlerImag
es?nocache=0.23266469622063257 HTTP/1.1" 412 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Windo
ws; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:31:09,586 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:31:09 ] "PUT /recipes/default/tiddlers/2009.06.04%20T
hu?nocache=0.1373033720151 HTTP/1.1" 204 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Windows;
U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:31:26,180 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:31:26 ] "DELETE /bags/common/tiddlers/TestTiddlerImag
es?nocache=0.6628137899863641 HTTP/1.1" 412 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Window
s; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:31:26,258 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:31:26 ] "PUT /recipes/default/tiddlers/2009.06.04%20T
hu?nocache=0.5913644829250273 HTTP/1.1" 204 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Window
s; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"
2009-06-04 12:32:14,805 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:32:14 ] "GET /recipes/default/tiddlers.wiki HTTP/1.1"
200 - "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:
1.9.0.10) Gecko/2009042316 Firefox/3.0.10"
2009-06-04 12:32:16,024 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:32:16 ] "GET /status?nocache=0.8680913802761882 HTTP/
1.1" 404 - "http://127.0.0.1:8080/recipes/default/tiddlers.wiki"
"Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9
.0.10) Gecko/2009042316 Firefox/3.0.10"
2009-06-04 12:32:23,290 INFO 127.0.0.1 - GUEST [04/Jun/
2009:12:32:23 ] "DELETE /bags/common/tiddlers/TestTiddlerImag
es?nocache=0.07538497271587852 HTTP/1.1" 412 - "http://127.0.0.1:8080/
recipes/default/tiddlers.wiki" "Mozilla/5.0 (Windo
ws; U; Windows NT 5.1; en-US; rv:1.9.0.10) Gecko/2009042316 Firefox/
3.0.10"

[chris...@gmail.com]

On Jun 3, 10:05 am, "cd...@peermore.com" <chris.d...@gmail.com> wrote:
> I meant to update http://tiddlyweb.peermore.com/ to use the sql store
> last night, but got distracted writing a thing called multistore which
> lets you write to or read from multiple stores.

This has now been done. http://tiddlyweb.peermore.com/ (now also
acting as http://tiddlyweb.com/) is running using the sql store to
talk to sqlite3. It's also using the caching-store to have a memcached
layer above the sql store. It's unlikely that performance is being
impacted to any significant degree by either of these things because
the performance blocks on that machine are going to be the same
whatever the situation (it's memory bound, this will be fixed soon).

> > Before I plunge in I just wanted to find out what the situation is
> > generally? I'd like to try using PostgreSQL instead of MySQL.
>
> According to the SQLAlchemy docs, using PostgreSQL should "just work"
> by changing the db_config part of server_store in tiddlywebconfig.py:
>
>     'server_store': ['sql', {'db_config': 'sqlite:///test.db'}],

For those following along at home, Oveek and I got his migration to
using PostgreSQL with TiddlyWeb working this evening, doing a bit of
iterative bugfixing in the #tiddlyweb IRC channel. There were issues
with how strict PostgreSQL is compared to Mysql and sqlite.

[tony]

On Jun 4, 1:18 pm, tony <cca...@gmail.com> wrote:

> For some reason, the image doesn't render. It renders fine on a plain
> tiddlywiki though.
>
> All these image links in a tiddler work [1] in a plain TiddlyWiki but
> only the http link works in TiddlyWeb

Opps apologies, I realize my error, of course these file:/// don't
work

I keep forgetting, I'm so used to TiddyWiki metaphor!

workarounds:
http://trac.tiddlywiki.org/ticket/884
http://tiddlywiki.org/wiki/Firefox#Local_File_Links

Best,
tony

[tony]

On Jun 4, 1:18 pm, tony <cca...@gmail.com> wrote:

> The odd thing is when I tried to delete the offending tiddler I
> deleted? it (not in timeline or all) but still got a 'Error removing
> <tiddler>' notification

I was finally able to pinpoint some of the errors on saving, removing,
server actions, etc I had encountered to a mistake I made with imwiki.

It has nothing to do with the sql store.

User tip: before importing a tiddlywiki into the store, remember you
have to make a bag for it!

$twanager imwiki ~/<sometiddlywiki.html> <your bag> will work and
raise no exception from bag absence, but create odd saving behavior
later.

What should happen first is:
$twanager bag <your bag that will house the import>
then $twanager imwiki ~/<sometiddlywiki.html> <your bag that will
house the import>

>
> Time to try this on a Mac. ;-)

After many hiccups from wikklytext.com not available to upgrading
setuptools to 0.6c9 in Leopard, I finally got the TiddlyWeb to run
from a sqlite db. I can now sympathize with those having some
difficulty with installation, but persevere, it indeed can be done!

The whole interesting thing I find about the tiddlyweb --> sql store
is that it brings Tiddlyweb back to a single entity/file like its
TiddlyWiki roots. In some instances, a single file as store is easier
to work with, ie email attachments

Kudos to flexibility :-)

Best,
tony

[chris...@gmail.com]

On Jun 8, 7:00 am, tony <cca...@gmail.com> wrote:
> User tip: before importing a tiddlywiki into the store, remember you
> have to make a bag for it!
>
> $twanager imwiki ~/<sometiddlywiki.html> <your bag> will work and
> raise no exception from bag absence, but create odd saving behavior
> later.

When I try to imwiki without the bag existing I get an error from
twanager:

matter:tiddlyweb cdent$ twanager imwiki index.html barnabas
IOError: store/bags/barnabas/tiddlers does not exist
[and then the usage message]

> > Time to try this on a Mac. ;-)
>
> After many hiccups from wikklytext.com not available to upgrading
> setuptools to 0.6c9 in Leopard, I finally got the TiddlyWeb to run
> from a sqlite db. I can now sympathize with those having some
> difficulty with installation, but persevere, it indeed can be done!

I've contacted the wikklytext author in case he doesn't know his
server is out of reach.

> The whole interesting thing I find about the tiddlyweb --> sql store
> is that it brings Tiddlyweb back to a single entity/file like its
> TiddlyWiki roots. In some instances, a single file as store is easier
> to work with, ie email attachments

Well, it's close to a single file: you still need tiddlywebconfig.py

[tony]

On Jun 8, 6:55 am, "cd...@peermore.com" <chris.d...@gmail.com> wrote:
> On Jun 8, 7:00 am, tony <cca...@gmail.com> wrote:
> When I try to imwiki without the bag existing I get an error from
> twanager:
>
> matter:tiddlyweb cdent$ twanager imwiki index.html barnabas
> IOError: store/bags/barnabas/tiddlers does not exist
> [and then the usage message]

I feel like I'm in the twilight zone of alternate TiddlyWeb
realities...

You are indeed correct:

$ twanager imwiki ~/mywikistuff/base.html BugsBunny
IOError: store/bags/BugsBunny/tiddlers does not exist

Unfortunately back on cygwin I'm getting the error on saving again.
ugh.

I'm going to post on the other thread about saving to tiddlyweb as my
issue has nothing to do with sql store.

> I've contacted the wikklytext author in case he doesn't know his
> server is out of reach.

thanks
I posted to the wikklytext group and the developer reset the server
allowing the download of wikklytext for my Mac install, but I find
that wikklytext.com/ is taking a long time again. :-\

Best,
tony

[tony]

On Jun 8, 6:55 am, "cd...@peermore.com" <chris.d...@gmail.com> wrote:

> On Jun 8, 7:00 am, tony <cca...@gmail.com> wrote:
> > > Time to try this on a Mac. ;-)

After many starts and stops and testing, I finally got TiddlyWeb
running from a sqlite store eg wiki.db:
+Windows/cygwin on local and usb drives
+Mac Leopard on local and usb drives

(I think) My key mistake was importing a TiddlyWiki that had existing
server saver plugins when I did a static view of TiddlyWeb
tiddlers.wiki?download=temp.html into a bag. Deleting these plugins
then doing the import into a bag then making a recipe with the system
bag resolved the error on saving/removing/edit conflict issues.

It is a testament to my excitement with attaining this feature that I
didn't even bother catching up with today's Apple's WWDC
announcements!

Best,
tony

[Oveek]

I hit another Postgre foreign key related store problem.

It happens when twanager is used to add an user with a role. The
problem is with the order stuff is committed to the database.
Currently an user's role is committed to the database before the
actual user is.

The roles table has a foreign key dependence on the users table, so
postgre wants the users table populated before the roles table.

Relocating one line was enough to get things going.
Moved self._map_sroles(user) out of _map_suser() and into user_put().

user_put now looks like:

def user_put(self, user):
suser = self._map_suser(user)
self.session.merge(suser)
self._map_sroles(user)
self.session.commit()

On Jun 9, 9:18 am, tony <cca...@gmail.com> wrote:

> After many starts and stops and testing, I finally got TiddlyWeb
> running from a sqlite store eg wiki.db:
> +Windows/cygwin on local and usb drives
> +Mac Leopard on local and usb drives

Nice work. Any particular reason you used cygwin on Windows?

[chris...@gmail.com]

On Jun 9, 10:20 am, Oveek <mov...@gmail.com> wrote:
> I hit another Postgre foreign key related store problem.

Good thing you exist. Thanks. It's fixed and the new code is on
github.

[tony]

On Jun 9, 2:20 am, Oveek <mov...@gmail.com> wrote:
> Nice work. Any particular reason you used cygwin on Windows?

I figured I should test on as many configurations to my avail.
Plus, unfortunately, computing is imprisoned by MS product in many
corporations and I didn't know how to work with twanager with DOS or
Powershell.
Another consideration is that I carry the sql store on a USB flash
drive which affords some portability.
I didn't try this with the text store but I found surprisingly that
TiddlyWeb with sql works on Chrome2, Safari4, FF3 with no need for jar
files. :-)
The only bits missing for me are having the ability for import/export/
sync with sqlite, learning up in revisions/roles/policies/multistore
and possibly interoperability with TiddlyWiki and Cocoa touch.

So much to learn, so little time

be fun!

Best,
tony

[alex]

Hi Chris,

I'm still planning my learning curve.
Jeremy mentioned CoucheDB. I've checked it out, i almost understand
it! It makes sense in the overall discussions of databases.

Will it be possible that TiddlyWeb will work with CoucheDB in the
future? If so, i'll start learning about it.


ALex

[Oveek]

On Jun 10, 1:58 am, tony <cca...@gmail.com> wrote:
> So much to learn, so little time

My feelings exactly.

On Jun 9, 6:51 pm, "cd...@peermore.com" <chris.d...@gmail.com> wrote:
> Good thing you exist. Thanks. It's fixed and the new code is on
> github.

Thanks, I echo that sentiment (the first part) at you and the
TiddlyWiki/Web community.


So my rocky trip through database land continues.

The test machine I'm using at work has 256 MB of RAM so I've had a
close eye on the number of running processes and memory usage. With
the basic TiddlyWeb environment setup I started adding, deleting, and
editing tiddlers and discovered a problem...

Every single request to TiddlyWeb is creating a new connection to
postgre and the connections are rapidly eating up available memory. I
did pretty much all my testing in postgre, but a quick check using
mysql revealed the same behavior.

I reduced the max_connections cap to 10 in postgre to test further.
Since any request (GET, PUT,...) creates a new connection, the
connection limit is hit within a minute or so of doing stuff in
TiddlyWeb. Edit a tiddler 5 times and there will be 5 new processes.
Hitting the connection limit causes sqlalchemy to crash with a
friendly message from the database:

FATAL: connection limit exceeded for non-superusers

First I was confused why existing connections weren't being reused. I
looked at the sqlalchemy docs and by default create_engine() uses
connection pooling with pool_size = 5, so there should never be more
than 5 idle processes listening for connections. When I first noticed
the problem I had around 40 separate open connections/processes, most
idle.

Now I have a good idea why the connections are proliferating. On every
request TiddlyWeb initializes the wrappers, and the Store's __init__
method is among those invoked. That means every request calls the
Store's __init__ method and creates a new session and database
connection with its own connection pool. The problem is completed
requests leave behind idle sessions that never get reused because
subsequent requests go and create new connections anyway.


After skimming the sqlalchemy docs on Sessions a bit, I think there
are a couple options. One option I'm confident will work is to clean
up the session created in each request by calling Session.remove()
after the request is done. An alternative (not sure if it's possible)
would be to try to connect a session to an existing connection created
by a previous request.

Considering the Session.remove() approach, the question is where to
make the call to remove? It needs to be called after the database
accesses are done. One way might be to put the call in a wrapper that
gets called after all Store related work is done. I though it might be
possible to use a destructor, a __del__ method, to do it, but I found
the destructor is not called after each request. That itself raises
some questions.

Anyway before getting into too much detail about any possible
remedies, I'll wait for your take on the situation.

interestingly I'm getting the impression that you're not getting this
problem using the SQLite database on peermore, because the site seems
to be doing okay. I think I saw somewhere that SQLite, being a single
file, handles multiple connections differently so that might account
for it. Either that or your server has a lot of memory and the
incremental increase in memory usage hasn't had an effect yet. If you
are getting new connections on each request you may be in for an
unpleasant surprise somewhere down the line.

[Oveek]

Got more details after some investigating. I should have mentioned I'm
running this test setup under Linux.

First I should make an important ammendment to my statement about
"rapidly eating up available memory." In reality available memory is
eaten up very slowly once Apache has allocated memory for all its
listener processes.

To clarify: the first few requests to TiddlyWeb cause large jumps in
memory usage because Apache allocates memory for its sleeping listener
processes, around 8 or 9 megs per process in my case. Once the
available listeners are all active, you don't see the large jumps in
memory usage after each request. With the listeners active, all
subsequent requests to TiddlyWeb cause a very small but steady
increase in memory usage (due to creation of new database
connections).

Really the increase is so small that it will likely take thousands of
requests before there is an actual problem due to memory running out.
But before that happens the database will reach its max_connections
limit (defaults to 100 in postgre).

The upshot is that on either mysql or postgre with default setings,
you are going to hit your max connection limit in under 200 requests.
If the max_connections setting was sky high, eventually memory would
be the limiting factor. On my test system it takes between 8 and 18
new TiddlyWeb requests / new database connections to consume one
additional MB of memory.

Now about resolving this problem...these TiddlyWeb requests
essentially need to close the door on the database connections on
their way out. I have a hack in place but it's got some problems.

First another correction. The Session.remove() method is only
available in a different session type, in our case the method to close
the connection involves disposing the engine. I found out how to do
this on sqlalchemy's GG here:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/e040a586c8ffe30?tvc=2&q=%09[sqlalchemy]+How+to+totally+close+down+db+connection

The second post in that thread has a list of calls that can be used to
fully close down a connection opened for a session.

As a test I added a method, _close_connection(), to the sql Store()
class. I then added a call to _close_connection() to the end of the
various handlers like recipe_get(), recipe_put(), tiddler_get(), etc.
Adding the calls had the desired effect of closing the connections
immediately after each request.

Still the question remains where is the best place to do this
connection closing? It looks to me there are generally two ways to
handle it. One in the sqlstore, where each handler does its own
closing; or two outside the store with a single call to the connection
closing code.

Doing it in the store (the way I did for testing purposes) has some
problems...

but I don't have time to finish this right now, I'll come back later
on and pick up where I left off.

[chris...@gmail.com]

On Jun 10, 2:58 pm, alex <r.a.ho...@googlemail.com> wrote:
> I'm still planning my learning curve.
> Jeremy mentioned CoucheDB. I've checked it out, i almost understand
> it! It makes sense in the overall discussions of databases.
>
> Will it be possible that TiddlyWeb will work with CoucheDB in the
> future? If so, i'll start learning about it.

I have no immediate plans to write a storage adaptor for CouchDB,
simply because I don't have time right now.

I would guess it wouldn't be too hard for someone else to do, though.
There's a library for accessing a couchdb from python:

http://code.google.com/p/couchdb-python/

and this blog posting has some sample code:

http://davidwatson.org/2008/02/python-couchdb-rocks.html

If somebody else doesn't do it, I probably eventually will.

An interesting thing is that in some ways, TiddlyWeb and CouchDB and
inhabiting the same space.

[chris...@gmail.com]

On Jun 10, 5:41 pm, Oveek <mov...@gmail.com> wrote:
> So my rocky trip through database land continues.

Ah. Yes. I'll do some responding here before moving on into your newer
message.

> Every single request to TiddlyWeb is creating a new connection to
> postgre and the connections are rapidly eating up available memory. I
> did pretty much all my testing in postgre, but a quick check using
> mysql revealed the same behavior.

Ah. Yes. This is unfortunate but not surprising. As you've figured out
SQLAlchemy's connection pool is not getting reused. I feared something
like this might happen, because a new engine is created every time
__init__ is called (again, as you've figured out). I was
optimistically hoping that SQLAlchemy would just handle it through
some kind of magic (it has plenty of other magic), but no. I hoped
that when self.engine went out of scope things would be cleaned up.
Clearly this is not the case, but I bet we can fix it with a bit of
jiggery pokery.

> After skimming the sqlalchemy docs on Sessions a bit, I think there
> are a couple options. One option I'm confident will work is to clean
> up the session created in each request by calling Session.remove()
> after the request is done. An alternative (not sure if it's possible)
> would be to try to connect a session to an existing connection created
> by a previous request.

One option would be for the wsgi middleware that established the
tiddlweb.store entry in the environ to do session removal on the way
back up the stack.

Another would be to establish some module level variables that are
used.

> I though it might be
> possible to use a destructor, a __del__ method, to do it, but I found
> the destructor is not called after each request. That itself raises
> some questions.

It certainly does. This episode is going to be very productive in
exposing issues in the system. I'm glad we did this while still pre
1.0. When I was writing the first few rounds of sql.py and adjusting
the tests to work with it, I found all sorts of problems with how
things are scoped, so here we seem to have another one.

What ought to happen is that the store object stays in scope all the
way out to to the top of the WSGI stack and then gets finalized. But
it wouldn't surprise me if a reference is being kept somewhere.

Are you doing your tests in mod_python, mod_wsgi or the built in
server? They each may have different handling of the WSGI environment.

> Anyway before getting into too much detail about any possible
> remedies, I'll wait for your take on the situation.

The perfect solution is one where a single connection pool of suitable
size is created and used by all the subsequent requests. This should
be possible with proper scoping of some variables. A singleton of some
type holding onto session and engine stuff is probably the way to go.

> interestingly I'm getting the impression that you're not getting this
> problem using the SQLite database on peermore, because the site seems
> to be doing okay. I think I saw somewhere that SQLite, being a single
> file, handles multiple connections differently so that might account
> for it. Either that or your server has a lot of memory and the
> incremental increase in memory usage hasn't had an effect yet. If you
> are getting new connections on each request you may be in for an
> unpleasant surprise somewhere down the line.

I am using sqlite on peermore and I think it basically doesn't use
connections at all: it's just file operations with fancy semaphores
for concurrency handling. I think. The server has 168MB of RAM so it's
not that there's tons of space.

More in the next message.

[chris...@gmail.com]

On Jun 11, 5:17 am, Oveek <mov...@gmail.com> wrote:
> The upshot is that on either mysql or postgre with default setings,
> you are going to hit your max connection limit in under 200 requests.
> If the max_connections setting was sky high, eventually memory would
> be the limiting factor. On my test system it takes between 8 and 18
> new TiddlyWeb requests / new database connections to consume one
> additional MB of memory.

I can start doing similar testing on my end with mysql and hopefully
we'll meet in the middle.

> The second post in that thread has a list of calls that can be used to
> fully close down a connection opened for a session.

That's a useful tidbit.

> Still the question remains where is the best place to do this
> connection closing? It looks to me there are generally two ways to
> handle it. One in the sqlstore, where each handler does its own
> closing; or two outside the store with a single call to the connection
> closing code.

Maybe in tiddlyweb.web.wsgi.StoreSet:

def __call__(self, environ, start_response):
database = Store(environ['tiddlyweb.config']['server_store']
[0], environ)
environ['tiddlyweb.store'] = database
output = self.application(environ, start_response)
# XXX completely untested pseudo code!!!
try:
database.storage._close_connection()
except AttributeError:
pass # current storage has no _close_connect
return output

'storage' is the particular StorageInterface implementation object.

Something like that may be a good stopgap, but doesn't reuse the pool.

I haven't tried the above. If you get a chance to try it before me,
let me know how it goes.

[chris...@gmail.com]

On Jun 11, 1:56 pm, "cd...@peermore.com" <chris.d...@gmail.com> wrote:
> On Jun 11, 5:17 am, Oveek <mov...@gmail.com> wrote:
>
> > The upshot is that on either mysql or postgre with default setings,
> > you are going to hit your max connection limit in under 200 requests.
> > If the max_connections setting was sky high, eventually memory would
> > be the limiting factor. On my test system it takes between 8 and 18
> > new TiddlyWeb requests / new database connections to consume one
> > additional MB of memory.
>
> I can start doing similar testing on my end with mysql and hopefully
> we'll meet in the middle.

Oveek, have a look at this commit:

http://github.com/tiddlyweb/tiddlyweb/commit/8c3c8371718df8275350b3c32164a0a3f7f2da4d

and install Tiddlyweb 0.9.39 (just released) and see if that changes
the behavior at all.

Basically what I did to get to this point was try to determine why the
store was not going out of scope, and while doing some logging
discovered that the StoreSet middleware is only initialized once per
process, but every time __call__ is called a new Store is created. So
I changed it so only one is created.

What this should do is make sure only one sqlstore is created per
process, and is reused per request, which means that the connection
pool is reused.

But I don't have a good testing scenario so I'm sure.

BTW: I've also committed a change to the sql store to ensure
tiddler_written is called in tiddler_delete and tiddler_put. This
shouldn't impact your testing, but was important for the use of
cachinghoster on peermore.

[chris...@gmail.com]

On Jun 12, 5:18 pm, "cd...@peermore.com" <chris.d...@gmail.com> wrote:
> On Jun 11, 1:56 pm, "cd...@peermore.com" <chris.d...@gmail.com> wrote:
> Oveek, have a look at this commit:
>

> http://github.com/tiddlyweb/tiddlyweb/commit/8c3c8371718df8275350b3c3...

>
> and install Tiddlyweb 0.9.39 (just released) and see if that changes
> the behavior at all.

Ah, nevermind don't do that. 0.9.39 is broken. As tony has discovered
things go wrong with the sql store and that change. sqlalchemy is
reporting:

InvalidRequestError: This connection is closed

from the pool.

Any ideas?

I'll investigate this later in the day. Things are working okay with
the text store, so 0.9.39 doesn't break things for most people so I'll
try not to worry too much.

[Oveek]

Okay I've been doing some testing. I think your latest commit in the
StoreSet has hit the problem at its source, but I'm seeing some things
that still aren't making sense.

Going back to the earlier posts first...

On Jun 11, 8:38 pm, "cd...@peermore.com" <chris.d...@gmail.com> wrote:

> One option would be for the wsgi middleware that established the
> tiddlweb.store entry in the environ to do session removal on the way
> back up the stack.

I was thinking that, but wasn't sure where to do it. That session
closing pseudo code you suggested in the StoreSet does work--the
majority of connections clean up properly--but there are some cases
where _close_connection mysteriously never gets called and connections
marked 'idle in transaction' are left behind. I don't think we need to
go this route so I'll leave it for now.

> When I was writing the first few rounds of sql.py and adjusting
> the tests to work with it, I found all sorts of problems with how
> things are scoped, so here we seem to have another one.

The scope and lifetime of objects is something I've been wondering
about for a while. Based on some of the logging I did, I got the
impression none of the objects in the stack persist across requests,
which I found kind of odd

> What ought to happen is that the store object stays in scope all the

> way out to to the top of the WSGI stack and then gets finalized. But...

> it wouldn't surprise me if a reference is being kept somewhere.

It turns out the __del__ method is not a good indicator of an object's
status because if I understand correctly, according to Python docs
there is no guarantee that it will be called even on interpreter exit.
Do you know a way to find out when an object gets finalized?

> Are you doing your tests in mod_python, mod_wsgi or the built in
> server? They each may have different handling of the WSGI environment.

All my testing has been under mod_wsgi. In the very beginning I was
using CherryPy, but that was quite a while ago.

> The perfect solution is one where a single connection pool of suitable
> size is created and used by all the subsequent requests. This should
> be possible with proper scoping of some variables. A singleton of some
> type holding onto session and engine stuff is probably the way to go.

I agree this is the best solution. One point worth noting, I think
using a connection pool has a higher memory overhead on average than
closing sessions per request.

> I am using sqlite on peermore and I think it basically doesn't use
> connections at all: it's just file operations with fancy semaphores
> for concurrency handling. I think. The server has 168MB of RAM so it's
> not that there's tons of space.

That's good news for me. How much of that RAM is usually being used?
I'm curious how well things will hold up under load. Based on the
tests on my laptop, I'm going to have to do considerable tuning,
because a heavy load with connection pooling enabled is causing memory
consumption to jump a lot when there are many concurrent requests.

[Oveek]

On Jun 13, 12:18 am, "cd...@peermore.com" <chris.d...@gmail.com>
wrote:

> Oveek, have a look at this commit:
>

> http://github.com/tiddlyweb/tiddlyweb/commit/8c3c8371718df8275350b3c3...

>
> and install Tiddlyweb 0.9.39 (just released) and see if that changes
> the behavior at all.

Disregard what I said about the strange behavior. This commit looks to
be working perfectly, and in only 4 lines! I was originally confused
because I was still seeing multiple instances of the StoreSet being
initialized, but I was overlooking that one will be initialized for
each apache process as you mentioned below. I can verify the Store is
initialized only once per process, and the connection pool is being
reused. Also looks like the default pool size is 10, and not 5. When
there are a bunch of simultaneous requests, the numbers of connections
grows to accommodate them, then falls back to 10 connections once the
requests complete.

By the way, I've actually been running directly out of git. I pulled
in your commit, and in my tests never I encountered the
InvalidRequestError you mention in your following post. I don't know
where that would be coming from.

> discovered that the StoreSet middleware is only initialized once per
> process, but every time __call__ is called a new Store is created. So
> I changed it so only one is created.

> What this should do is make sure only one sqlstore is created per
> process, and is reused per request, which means that the connection
> pool is reused.

Nice work, this appears to be working as intended. Connection pool is
definitely being reused. I'm going to do more testing tomorrow, but I
think you've nailed it. Again, that error in 0.9.39 is strange because
running against current git master I'm not having any problems.

[chris...@gmail.com]

On Jun 13, 8:38 pm, Oveek <mov...@gmail.com> wrote:
> Okay I've been doing some testing. I think your latest commit in the
> StoreSet has hit the problem at its source, but I'm seeing some things
> that still aren't making sense.

Unfortunately my change screws things up for sqlite, while working for
other DBs. So I'm exploring some options at the moment. Should have
something for you to try out in a few minutes.

> The scope and lifetime of objects is something I've been wondering
> about for a while. Based on some of the logging I did, I got the
> impression none of the objects in the stack persist across requests,
> which I found kind of odd

I think it may be less predictable than desired and it may also change
depending on which wsgi server is being used.

> It turns out the __del__ method is not a good indicator of an object's
> status because if I understand correctly, according to Python docs
> there is no guarantee that it will be called even on interpreter exit.
> Do you know a way to find out when an object gets finalized?

Unfortunately, no.

> That's good news for me. How much of that RAM is usually being used?
> I'm curious how well things will hold up under load. Based on the
> tests on my laptop, I'm going to have to do considerable tuning,
> because a heavy load with connection pooling enabled is causing memory
> consumption to jump a lot when there are many concurrent requests.

On the same server, but with different mod_wsgi daemon processes, a
tiddlyweb using the sql store using sqlite has a VSIZE of 142028. A
similarly configured instance using the text store is 109296. The
resident size of the sqlite one is 13788. This is
tiddlyweb.peermore.com.

In my experience from larger installations with Postgres, it's really
hard to get it right in terms of memory use and connection handling.
The default settings are not ideal: seem to lead to a large number of
processes using a large amount of RAM.

[chris...@gmail.com]

On Jun 13, 9:23 pm, Oveek <mov...@gmail.com> wrote:
> Disregard what I said about the strange behavior. This commit looks to
> be working perfectly, and in only 4 lines! I was originally confused
> because I was still seeing multiple instances of the StoreSet being
> initialized, but I was overlooking that one will be initialized for
> each apache process as you mentioned below. I can verify the Store is
> initialized only once per process, and the connection pool is being
> reused. Also looks like the default pool size is 10, and not 5. When
> there are a bunch of simultaneous requests, the numbers of connections
> grows to accommodate them, then falls back to 10 connections once the
> requests complete.

Unfortunately the stuff that is working just fine for mysql and
postgres is not working just fine for sqlite. sqlite has its own
special connection pooling class because it get confused in multi-
threaded environments. After a few requests in CherryPy, it will start
throwing InvalidRequestError or complain about a different thread
trying to a use a connection existing in another thread.

So I've done some rather baroque munging that seems to get around
these issues, and may preserve the connection pooling, but I'm not
sure. Check out my latest commits to both tiddlyweb and tiddlyweb-
plugins/sqlstore.

It changes the strategy a bit: a new store is created per request,
otherwise sqlite gets thread confused.

However in any given process, if we're not using sqlite, we should
reuse a session object that is stored on the class (not the instance).

> By the way, I've actually been running directly out of git. I pulled
> in your commit, and in my tests never I encountered the
> InvalidRequestError you mention in your following post. I don't know
> where that would be coming from.

Seems to be just sqlite.

> Nice work, this appears to be working as intended. Connection pool is
> definitely being reused. I'm going to do more testing tomorrow, but I
> think you've nailed it. Again, that error in 0.9.39 is strange because
> running against current git master I'm not having any problems.

Thanks, let me know if the newest stuff does the right thing (even if
differently).

[tony]

On Jun 14, 5:23 am, "cd...@peermore.com" <chris.d...@gmail.com> wrote:
> On Jun 13, 9:23 pm, Oveek <mov...@gmail.com> wrote:

> Seems to be just sqlite.

> Thanks, let me know if the newest stuff does the right thing (even if
> differently).

Thanks for the concerted efforts; it sounds like you're taming a multi-
armed kraken!

I have good news and not as good news.

Good news:
I was able to save to the default recipe in plain text and sqlite
store under 0.9.40 with Safari4 and FF3.0.10 under Leopard
tada: 2009.06.14 22:50:58 Sun hello world on 0.9.40

Waah:
Unfortunately I could not save to an imported tiddlywiki both from
plain text and sqlite store.

Also when I open a recipe from an imported tw, the
chkHttpReadOnly=True and I have to go into Advanced Options to reset,
reload and retest if I could save.

I was using 0.9.40 from easy_install

hth

Best,
tony

[Oveek]

On Jun 14, 8:23 pm, "cd...@peermore.com" <chris.d...@gmail.com> wrote:

> Unfortunately the stuff that is working just fine for mysql and
> postgres is not working just fine for sqlite. sqlite has its own
> special connection pooling class because it get confused in multi-
> threaded environments. After a few requests in CherryPy, it will start
> throwing InvalidRequestError or complain about a different thread
> trying to a use a connection existing in another thread.

I may have to toss sqlite into my mix of databases to play with...I've
gotten curious about the performance of various combinations of
software "stacks". I've thought about creating a virtual machine that
mimics the specs of the test server at work, and experimenting with
various combinations of servers, python environments, and stores--see
how they compare.

> So I've done some rather baroque munging that seems to get around
> these issues, and may preserve the connection pooling, but I'm not
> sure. Check out my latest commits to both tiddlyweb and tiddlyweb-
> plugins/sqlstore.

Your 'baroque munging' is working well for me. It's a little
unfortunate that a database specific workaround is needed, but on the
other hand it's pretty amazing that so many databases are supported
transparently via sqlalchemy. When I wanted to quickly check how
things go with MySQL compared to postgres, I was able create an
instance in MySQL in less than 5 minutes.

> Thanks, let me know if the newest stuff does the right thing (even if
> differently).

The connection pool is still being reused under the new strategy. My
initial loadtesting of the default apache + postgres setup shows I
need considerable tuning. I may switch one or both of them. I'll
continue experimenting and report any other problems that crop up.

[Oveek]

On Jun 15, 2:25 pm, tony <cca...@gmail.com> wrote:
> Unfortunately I could not save to an imported tiddlywiki both from
> plain text and sqlite store.
>
> Also when I open a recipe from an imported tw, the
> chkHttpReadOnly=True and I have to go into Advanced Options to reset,
> reload and retest if I could save.
>
> I was using 0.9.40 from easy_install

Sounds like you might have plugins missing from your imported tw's
recipe.
One of the plugins needed for saving, the ServerSideSavingPlugin, sets
the option 'readOnly = false' which will enable editing over HTTP when
it's present. In your case you had to change the setting manually
which indicates that plugin is missing or not tagged systemConfig.

Did you include the 'system' bag in the recipe for your imported
tiddlywiki? When you open it in your browser you may want to check
these plugins are listed under the systemConfig tag :

ServerSideSavingPlugin
TiddlyWebAdaptor
TiddlyWebConfig

[Oveek]

On Jun 15, 5:19 pm, Oveek <mov...@gmail.com> wrote:
> When you open it in your browser you may want to check
> these plugins are listed under the systemConfig tag :

Just to be clear, when I said "open it in your web browser" I meant
open the imported tiddlywiki and make sure you can see those plugins
tagged with systemConfig.

[chris...@gmail.com]

On Jun 15, 9:34 am, Oveek <mov...@gmail.com> wrote:
> I may have to toss sqlite into my mix of databases to play with...I've
> gotten curious about the performance of various combinations of
> software "stacks". I've thought about creating a virtual machine that
> mimics the specs of the test server at work, and experimenting with
> various combinations of servers, python environments, and stores--see
> how they compare.

One of the things I like about sqlite is the tidiness of it: It's just
that one file, no DB server to worry about. On the other hand, you
don't get the flexibility and rigor that some of the servers provide.

> Your 'baroque munging' is working well for me. It's a little
> unfortunate that a database specific workaround is needed, but on the
> other hand it's pretty amazing that so many databases are supported
> transparently via sqlalchemy. When I wanted to quickly check how
> things go with MySQL compared to postgres, I was able create an
> instance in MySQL in less than 5 minutes.

Excellent.

> The connection pool is still being reused under the new strategy. My
> initial loadtesting of the default apache + postgres setup shows I
> need considerable tuning. I may switch one or both of them. I'll
> continue experimenting and report any other problems that crop up.

One thing to keep in mind is that there may be scheme adjustments that
make sense. May be missing an index in a crucial place. Or some of the
SQL being generated could be entirely lame.

We may wish to add a connection pool size configuration item to the
server_store line when using the sql store.

I'm glad to hear that it is pretty much working for you. You've been a
huge help.

Now on to some other plugin or feature that should hopefully uncover a
bunch of bugs. Any suggestions?

[tony]

On Jun 15, 2:19 am, Oveek <mov...@gmail.com> wrote:
> On Jun 15, 2:25 pm, tony <cca...@gmail.com> wrote:
>
> Sounds like you might have plugins missing from your imported tw's
> recipe.

Hi Oveek,

Yes that is my usual recipe: system + new bag, then I work with
http://localhost:8080/recipes/<new bag>/tiddlers.wiki

Thanks for the pointer, it helped me find my mistake.

I think 0.9.40 is good to go.

For those interested as to why:
I checked again on the windows side and did 2 imports, one tw with a
lot of tiddlers and the other with just a few plugins to compare if
the error on saving had to do with the
This time, I had no problem with editing over HTTP as readOnly = false
in the ServerSideSavingPlugin and more importantly I had no issue with
saving to the imported tw that had only a few tiddlywiki plugins.
But I was still not able to save to the bigger import. I checked the
source and found I had old versions of these system bag plugins ie v
0.2 of ServerSideSavingPlugin so they were probably conflicting with
the newer versions in the recipe.
I was not able to see the ServerSideSavingPlugin in the tiddlers.wiki
view but able to see it in wikklytext tiddlers view.

Lesson learned: do not import a tiddlywiki with pre-existing system
plugins

whew!

Best,
tony