4 ms·

I'm going in the opposite direction from NoSQL. I'd like as much sql as possible. I'd enjoy using a language with an in memory database, one where you could c

• gaius 17y ago
  This is a common practice in Sybase-derived RDBMSs.
• mahmud 17y ago
  (connect '("localhost" "" "username" "passwd") :database-type :mysql) (create-database :users) (define-view-class user () ((id :initarg :id :accessor user-id :type integer :db-constraints :key :not-null :auto-increment) ((name :initarg :name :accessor user-id :type string) (passwd :initarg :passwd :accessor user-passwd :type string))) (create-view-from-class 'user) Then in the REPL CL-USER 1> (list-tables) ("users") Now for some interaction: CL-USER 2> (make-instance 'user :name "Popeye" :passwd "sailorM0n") #<user> ; <- instantiating an object creates a users.user record Some time later, perhaps after a machine restart CL-USER 1> (select 'user) ((#<user>)) CL-USER 2> (select 'user :where [= :name "Popeye"]) (#<user>) Say, you wanna change Popeye's password. CL-USER 3> (setf (user-passwd (select 'user :where [= :name "Popeye"])) "new-password") But typically you would just define a function, say, FIND-USER which will get you an object corresponding with a user record. (defun find-user (by val) (caar (select 'user :where (sql-= by val)))) That way you can find an object by ANY slot (or in SQL, find a record by the value of a column. CL-USER> (user-name (find-user 'id 0)) "Popeye" CL-USER> (user-id (find-user 'name "Popeye")) 0 CL-USER> (user-passwd (find-user 'name "Popeye")) "new-password" That last value is due to our last update. So you're bored with this class, and the auto-incremented primary key ID is MySQL only. Fine, redefine the ID slot mapping its value to a sequence CL-USER 3> (create-sequence 'user) CL-USER 4> (defclass user () ((id :initform (sequence-next 'user))) The rest of the class definition stays the same. mysql > show tables; user _clsql_sequence_user :-) so you're bored with the database. drop the classes/tables one by one or just drop the whole database. CL-USER 5> (drop-sequence 'user) NIL CL-USER 6> (drop-view-from-class 'user) NIL CL-USER 7> (drop-database 'user) NIL You can use the same code in MySQL, Postgres, DB2, Oracle, ODBC, Sqlite?, and an in-memory Sqlite3 implementation. This shit is industrial-strength eye candy. Scan the manual: http://clsql.b9.com/manual/ http://clsql.b9.com/manual/ Tutorial: http://www.ravenbrook.com/doc/2002/09/13/common-sql/ http://www.ravenbrook.com/doc/2002/09/13/common-sql/ It comes in fully open source and corporate supported versions: The OSS one is called CLSQL and its compatible with LispWorks' CommonSQL.
• kragen 17y ago
  You can do that pretty easily with SQLite: conn = sqlite3.connect(':memory:') conn.execute(''' create table visits (plano text, "column" text, "row" text, route text) ''') conn.execute(''' create table square (plano text, "column" text, "row" text, x int, y int) ''') for line in infile: if line.startswith('#'): continue plano, column, row, routes = line.split(',') column = column.lower() x, y = square_coords(plano, column, row) conn.execute('''insert into square (plano, "column", "row", x, y) values (?, ?, ?, ?, ?) ''', (plano, column, row, x, y)) However, the trouble is that SQL is still kind of verbose, not type-safe, etc. I've been writing up some thoughts on how to do better at http://canonical.org/~kragen/binary-relations.html http://canonical.org/~kragen/binary-relations.html but I have a long way to go still.
  • geebee 17y ago
    That's pretty cool. The thing is, I'm actually not looking for a "conn" (connection" of any sort). I'm thinking of database as a container class. Kind of like... db = New db db.addTable(colname, list[names], primaryKey) db.addTable(joinTable, foreignKey, colname, list[names]) Map a = db.(select col a, col b from ...) db = nil and so forth... in other words, we're going the opposite direction from database as storage. Instead, we're going to database as something that is as in-memory as a hash (probably more likely to be persisted at some point, but not necessarily). Sometimes, I see classes created that are bending over backward to recreate a bit of sql, and the model and logic really only exist for one purpose - sql-like methods on the data. People rarely write their own list, set or map classes, but often they will essentially recreate a bug-ridden 1/2 of common sql in their app... Anyway, looks like you've been thinking about this for a while, I'll definitely take a look at the link. Thanks for posting it.
    • kragen 17y ago