storm, sqlite and string representation
Arghhh today I’ve discovered reading more ...-->
Arghhh today I’ve discovered reading more ...-->
a while ago I
wrote about enabling the sqlite3 extension with storm . This is
how you do it with the Django ORM. The collation is the same and all
details are in the old post. The only tricky part is to establish the
connection with cursor = connection.cursor()
before calling the
function to enable the extension. Failing to do so, will result in an
error as the connection object will be null.
def add_collation():
from django.db import connection
import sqlitext
cursor = connection.cursor()
sqlitext.enable_extension(connection.connection,1)
cursor.execute("SELECT load_extension('sqlite/libcollate_debian.so')")
I’ve been looking for a while for a tool to generate graphs from sql schemas. This obvious operation seems implemented in many graphical design tools for database, but up until now, I didn’t manage to find a command line tool to output a simple graph. Well, the answer came from sqlfairy ( http://sqlfairy.sourceforge.net/ ), a perl library to manipulate structured data definitions.
for example, to generate a nice picture from an existing sqlite database, this is the pipeline :
echo ".schema" | sqlite3 database > schema.sql
sqlt-graph --from=SQLite -o schema.png schema.sql
UPDATE: if you actually want to display relations between your tables,
you can use FOREIGN KEY / REFERENCE
definitions. Sqlite3
actually parses these definitions, but does not enforce them. On the
other hand sqlfairy does not accept foreign key definitions if using
the sqlite parser backend. The solution is to use the MySQL parser
backend instead so to generate a nice graph.
An example is attached.
UPDATE: if you use postgresql, you should definitely have a look at pg_autodoc : http://www.rbt.ca/autodoc/index.html
Recently I started a small project to build an incremental sql database of meta information related to debian packages. In the process I discovered that the newly available feature of sqlite3 to add custom collation doesn’t work well with the sqlite3 bindings present in python 2.5 . The reason is that in order to load a collation at run time, you must explicitly enable the extension loading mechanism. The binding present in python 2.5 (fixed in python 2.6) do not expose this C function.
In order to enable call this function, and instead of patching and recompiling python, I wrote a small python module using swig.
briefly (not tested, but you should get the idea):
import storm.database
import sqlitext
db_url = 'sqlite:db'
database = stm.create_database(db_url)
store = stm.Store(database)
sqlitext.enable_extension(store._connection._raw_connection,1)
store.execute('''SELECT load_extension('./libcollate_debian.so')''')
The code is available here: https://gforge.inria.fr/plugins/scmsvn/viewcvs.php/trunk/torchone/sqlite/?root=sodiac