«
»

SPDO 1.0b Release Candidate 1

SPDO version 1.0 beta Release Candidate 1 is out the door. This version adds the following:

Three custom Exception classes: SPDOError, ConnectionFailedError and ForeignKeyViolationError. Which are used (in order) when the program encounters an unknown sql error, a failure to connect to the database, and a violation of a foreign key constraint. Which means, of course, that foreign key constraints are now part of SPDO.

How this was accomplished is through the use of the new DbBuilder class. The DbBuilder class takes a multi-dimensional dict (Python) or array (PHP) of table and field data to build the tables and create triggers in MySQL and SQLite and add foreign keys in PostgreSQL. As an example consider the following:

In Python:

Python [Show Plain Code]:
  1. structure = {
  2.     ‘authors’:{
  3.         ‘id’:[‘primary_auto’],
  4.         ‘first_name’:[‘varchar(50)’],
  5.         ‘last_name’:[‘varchar(50)’, ‘NONULL’, ‘INDEX’]
  6.         },
  7.     ‘books’:{
  8.         ‘id’:[‘primary_auto’],
  9.         ‘author_id’:[‘int’, "FK_CASCADE(‘authors’,'id’)", ‘INDEX’],
  10.         ‘title’:[‘varchar(100)’,‘NONULL’, ‘INDEX’]
  11.         }
  12.     }
  13. dbb = DbBuilder(structure)
  14. dbb.create()

In PHP:

PHP [Show Plain Code]:
  1. $structure = array(
  2.         ‘authors’=>array(
  3.         ‘id’=>array(‘primary_auto’),
  4.         ‘first_name’=>array(‘varchar(50)’),
  5.         ‘last_name’=>array(‘varchar(50)’, ‘NONULL’, ‘INDEX’)
  6.     ),
  7.     ‘books’=>array(
  8.         ‘id’=>array(‘primary_auto’),
  9.         ‘author_id’=>array(‘int’, "FK_CASCADE(‘authors’,'id’)", ‘INDEX’),
  10.         ‘title’=>array(‘varchar(100)’,‘NONULL’, ‘INDEX’)
  11.     )
  12. );
  13. $dbb = DbBuilder($structure)
  14. $dbb->create()

This results in the following queries to be executed:

In PostgreSQL:

SQL [Show Plain Code]:
  1. CREATE TABLE authors (
  2.     first_name VARCHAR(50) ,
  3.     last_name VARCHAR(50) NOT NULL,
  4.     id SERIAL PRIMARY KEY
  5.  );
  6. CREATE TABLE books (
  7.     author_id INTEGER ,
  8.     id SERIAL PRIMARY KEY ,
  9.     title VARCHAR(100) NOT NULL
  10. );
  11. CREATE INDEX authors_last_name_idx ON authors(last_name_id);
  12. CREATE INDEX books_author_id_idx ON books(author_id);
  13. CREATE INDEX books_title_idx ON books(title);
  14. ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE;

In MySQL:

SQL [Show Plain Code]:
  1. CREATE TABLE authors (
  2.     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.     first_name VARCHAR(50),
  4.     last_name VARCHAR(50) NOT NULL
  5. );
  6. CREATE TABLE books (
  7.     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  8.     author_id INTEGER NOT NULL,
  9.     title VARCHAR(100) NOT NULL
  10. );
  11. CREATE TABLE fk_error_msg (
  12.     error_msg VARCHAR(100) NOT NULL PRIMARY KEY
  13. );
  14. INSERT INTO fk_error_msg VALUES (‘Foreign Key Constraint Violated!’);
  15. ALTER TABLE authors ADD INDEX(last_name);
  16. ALTER TABLE books ADD INDEX(author_id);
  17. ALTER TABLE books ADD INDEX(title);
  18. CREATE TRIGGER fki_books_author_id
  19.     BEFORE INSERT ON books
  20.     FOR EACH ROW BEGIN
  21.         IF
  22.             0 = (SELECT COUNT(*) FROM authors WHERE id=new.author_id)
  23.         THEN
  24.             INSERT fk_error_msg VALUES (‘Foreign Key Constraint Violated!’);
  25.         END IF;
  26.     END;
  27. CREATE TRIGGER fku_books_author_id
  28.     BEFORE UPDATE ON books
  29.     FOR EACH ROW BEGIN
  30.         IF
  31.             0 = ( SELECT COUNT(*) FROM authors WHERE id = new.author_id )
  32.         THEN
  33.             INSERT INTO fk_error_msg VALUES (‘Foreign Key Constraint Violated!’);
  34.         END IF ;
  35.     END;
  36. CREATE TRIGGER fkdc_books_author_id
  37.     BEFORE DELETE ON authors
  38.     FOR EACH ROW BEGIN
  39.         DELETE FROM books WHERE author_id=old.id;
  40.     END;

In SQLite:

SQL [Show Plain Code]:
  1. CREATE TABLE authors (
  2.     id INTEGER NOT NULL PRIMARY KEY,
  3.     first_name VARCHAR(50),
  4.     last_name VARCHAR(50) NOT NULL
  5. );
  6. CREATE TABLE books (
  7.     id INTEGER NOT NULL PRIMARY KEY,
  8.     author_id INTEGER NOT NULL,
  9.     title VARCHAR(100) NOT NULL
  10. );
  11. CREATE INDEX authors_last_name_idx ON authors(last_name);
  12. CREATE INDEX books_author_id_idx ON books(author_id);
  13. CREATE INDEX books_title_idx ON books(title);
  14. CREATE TRIGGER fki_books_author_id
  15.     BEFORE INSERT ON [books]
  16.         FOR EACH ROW BEGIN
  17.             SELECT RAISE(ROLLBACK, ‘insert on table "books" violates foreign key constraint "fki_books_author_id"’)
  18.             WHERE NEW.author_id IS NOT NULL AND (SELECT id FROM authors WHERE id = NEW.author_id) IS NULL;
  19.         END;
  20. CREATE TRIGGER fku_books_author_id
  21.     BEFORE UPDATE ON [books]
  22.         FOR EACH ROW BEGIN
  23.             SELECT RAISE(ROLLBACK, ‘update on table "books" violates foreign key constraint "fku_books_author_id"’)
  24.             WHERE NEW.author_id IS NOT NULL AND (SELECT id FROM authors WHERE id = NEW.author_id) IS NULL;
  25.         END;
  26. CREATE TRIGGER fkdc_books_author_id
  27.     BEFORE DELETE ON authors
  28.         FOR EACH ROW BEGIN
  29.             DELETE FROM books WHERE books.author_id = OLD.id;
  30.         END;

Be sure to check out the wiki, and the source code is all readable there, or download and enjoy. There are still a few edits that need to happen in the comments (like versions need to be updated, and changelogs added and there are a few typos probably still lurking about.)

Post to Twitter Post to Yahoo Buzz Buzz This Post Post to Delicious Delicious Post to Digg Digg This Post Post to Ping.fm Ping This Post Post to Reddit Reddit Post to StumbleUpon Stumble This Post

No Responses so far »

Comment RSS · TrackBack URI

Say your words