-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Allen Fowler wrote:
> Indeed, I am aware that SQL is not a "traditional" programming language
> per-se and have will now be writing the calendar logic at the application
> level. (Looking at Python...)
You may want to get a good understanding of what SQL actually is
(relational calculus). Mike Owens' "The Definitive Guide to SQLite"
covers it in depth.
Quite simply if you can't express what you want in another general
purpose language like Python then you have zero hope of expressing it in
SQL.
As to how to write something like Python that is easy to translate to
SQL, use for loops as I stated. For example:
for row in events:
if row["start-time"]>xxx: continue
...
group[row["id"]].append(row)
Would turn into a select with where and groupby phrases.
> In this application, there are going to be a very large number of concurrent
> "readers" that want to know at any given time who is doing what. Where
> "doing what" is defined as the most specific event the user currently has
> scheduled.
There is a pseudo "free/busy" standard for that which many existing
calendaring systems can generate.
> The "event table" I have been asking about is something that I envision my
> app will generate on a daily/hourly basis as the *output* of all the
> calendering logic.
Note that is effectively premature optimisation :-) It seemed unclear
if you have worked out how to do the underlying calculations first. You
may find that you can do them fast enough without caching the results.
In any event if you do choose Python then you can use the APSW wrapper
to write a virtual table. (Disclosure: I am the author of APSW). You
can present the results of the calculations as the virtual table
contents. The BestIndex callback tells you what the constraints are.
This approach means that you will never be out of date since the
calculated data is not physically stored in the database. It has the
drawback that the virtual tables are only available in processes that
have loaded your virtual table code.
> In this way, I hope to let sqlite, and eventually an SQL server, handle all
> the multi-user connection and scaling issues.
Are you certain you will have scaling issues? You'll certainly have
some if calculated generated event items are stuff into the database
(how far into the past and future will they go, how many updates would
need to be done if an event is changed?)
You may want to investigate using an existing calendar server rather
than reinventing most of the wheel. As all the messages keep pointing
out, calendaring is a horrible messy thing with lots of tedious petty
details and several projects have already done that work for you :-)
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
iEYEARECAAYFAkoy79oACgkQmOOfHg372QTmMgCdGky6/ipTjNN/T4kmDdvQj+2o
ksEAn1aHs/gy4F7j2qoBXhhgFZrQ6sMq
=hE/G
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
