New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL abstraction layer (such as SQLAlchemy) #61

Open
posativ opened this Issue Jan 31, 2014 · 8 comments

Comments

Projects
None yet
5 participants
@posativ
Copy link
Owner

posativ commented Jan 31, 2014

Support for MySQL and PostgreSQL should also be added for users or hosting environments that prefer MySQL/PGSQL. As database abstraction layer SQLAlchemy seems to be the best solution for Python (supports Python2/3, available in Debian, extensive documentation).

This also (hopefully) eases the task of changing the database schema to support new features such as #27 (which is currently non-functional).

If possible, SQLAlchemy can use its own connection pool or set up on the existing locking mechanism for threads/processes/uWSGI.

@lucian1900

This comment has been minimized.

Copy link

lucian1900 commented Jan 31, 2014

SQLAlchemy is indeed great and Alembic is quite nice for migrations.

@jgraichen jgraichen referenced this issue Feb 1, 2014

Closed

Use SQLAlchemy [WIP] #63

2 of 6 tasks complete
@khromov

This comment has been minimized.

Copy link

khromov commented Feb 6, 2014

This would be very nice!

@shulegaa

This comment has been minimized.

Copy link

shulegaa commented Mar 3, 2014

As much as I'm a fan of both PostgreSQL and SQLAlchemy, timing is everything. First, notice that comments on web pages (e.g. blog posts) is neither big data nor complex data. This isn't like some enterprise resource planning application - or an electronic medical records application.

IMHO, a maximally simple data schema (drawn over a maximally simple data model - e.g. relational) is a darn good fit. Witness the success of SQLite with isso already. Martin Fowler, and Eric Evans (off DDD fame), has complained about the proliferation of "anemic domain models" - but when you do without object relational mapping (ORM) - to step up to a richer domain model - it would be tragic to miss the opportunity to do keep it simple and straight-forward (KISS).

I can see going for ORM/SQLAlchemy, and all the good reasons for adding SQLAlchemy ... and maybe even more pressing good reasons to support PostgreSQL. I'm just saying that this might we pretty far down the list of upcoming isso features/functions.

For example, I was just noticing that isso doesn't (yet) install/operate behind a NATing firewall - even when co-resident with a static (Pelican-generated) blog. Then there's boring stuff like documentation and packaging and logging/diagnostics ... and who knows ... maybe even more comprehensive QA automation. I get the feeling that SQLAlchemy (and PostgreSQL support) might wait for the v2.x (second generation) of isso.

Heck, I'd love to see isso --version supported before PostgreSQL and/or ORM (internals). Not my call - but just trying to help isso win the most traction/success in the shortest possible amount of time ...

@posativ

This comment has been minimized.

Copy link
Owner Author

posativ commented Mar 6, 2014

If isso --version is not working for you, please open a new ticket ;)

But I get your point. An ORM is admittedly an overkill currently, but what is really needed is glue code for writing SQL queries compatible with SQLite, MySQL and PostgreSQL as well as managing different databases and connection pooling (managing three different lock types is really ugly).

While SQLite is really simple to use and works out-of-the-box(tm), it has its drawbacks that it may not integrate well in backups. If you use PostgreSQL or MySQL you or your provider automatically backups the whole database. For SQLite there's an API not used by rsync and you may end up with an inconsistent database (unless you backup read-only filesystem snapshots which I do ;-). For the reason I dislike projects which do only work with MySQL or PostgreSQL, people may dislike project with some weird single-file database writeable by www-data.

I do not work on this issue. You are right, there are higher prioritory issues. But if someone else implements an adapter, I'll happily merge it.

@lucian1900

This comment has been minimized.

Copy link

lucian1900 commented Mar 6, 2014

I think it would be a good strategy to move to the SQL-only layer in SQLAlchemy and ignore the ORM for now. That should be a mostly mechanical transformation and give database portability almost automatically.

Later on Alembic migrations and ORM can be used, if deemed useful.

I also am not working on this, though :)

@posativ posativ added this to the 0.10 milestone Aug 18, 2014

@wprater

This comment has been minimized.

Copy link

wprater commented Oct 22, 2014

It would be great to get this running on the Google App Engine, but is not currently possible because the lack of support for their Datastore or Cloud SQL.

@posativ

This comment has been minimized.

Copy link
Owner Author

posativ commented Oct 22, 2014

I've been working on SQLAlchemy integration, which supports the db dialect of GAE. The feature is not yet merged into the master, though.

@wprater

This comment has been minimized.

Copy link

wprater commented Oct 22, 2014

@posativ nice to hear! If end up using isso over here, I can help with your integration, if needed!

@posativ posativ modified the milestones: 0.10, 1.0 Nov 25, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment