{"id":306,"date":"2009-03-08T13:21:37","date_gmt":"2009-03-08T20:21:37","guid":{"rendered":"http:\/\/www.evardsson.com\/blog\/?p=306"},"modified":"2010-07-29T12:25:27","modified_gmt":"2010-07-29T19:25:27","slug":"spdo-1b-release-candidate-1","status":"publish","type":"post","link":"https:\/\/www.evardsson.com\/blog\/2009\/03\/08\/spdo-1b-release-candidate-1\/","title":{"rendered":"SPDO 1.0b Release Candidate 1"},"content":{"rendered":"<p><a href=\"http:\/\/www.evardsson.com\/wiki\/SPDO\">SPDO<\/a> version 1.0 beta Release Candidate 1 is <a href=\"http:\/\/www.evardsson.com\/files\/spdo-complete-1.0bRC-1.tar.bz2\">out the door<\/a>. This version adds the following:<\/p>\n<p>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.<\/p>\n<p>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: <\/p>\n<p>In Python:<\/p>\n<pre class=\"brush: python\">structure = {\n    'authors':{\n        'id':['primary_auto'],\n        'first_name':['varchar(50)'],\n        'last_name':['varchar(50)', 'NONULL', 'INDEX']\n        },\n    'books':{\n        'id':['primary_auto'],\n        'author_id':['int', \"FK_CASCADE('authors','id')\", 'INDEX'],\n        'title':['varchar(100)','NONULL', 'INDEX']\n        }\n    }\ndbb = DbBuilder(structure)\ndbb.create()\n<\/pre>\n<p>In PHP:<\/p>\n<pre class=\"brush: php\">$structure = array(\n        'authors'=>array(\n        'id'=>array('primary_auto'),\n        'first_name'=>array('varchar(50)'),\n        'last_name'=>array('varchar(50)', 'NONULL', 'INDEX')\n    ),\n    'books'=>array(\n        'id'=>array('primary_auto'),\n        'author_id'=>array('int', \"FK_CASCADE('authors','id')\", 'INDEX'),\n        'title'=>array('varchar(100)','NONULL', 'INDEX')\n    )\n);\n$dbb = DbBuilder($structure)\n$dbb->create()\n<\/pre>\n<p>This results in the following queries to be executed:<\/p>\n<p>In PostgreSQL:<\/p>\n<pre class=\"brush: sql\">CREATE TABLE authors (\n    first_name VARCHAR(50) ,\n    last_name VARCHAR(50) NOT NULL,\n    id SERIAL PRIMARY KEY \n );\nCREATE TABLE books (\n    author_id INTEGER ,\n    id SERIAL PRIMARY KEY ,\n    title VARCHAR(100) NOT NULL\n);\nCREATE INDEX authors_last_name_idx ON authors(last_name_id);\nCREATE INDEX books_author_id_idx ON books(author_id);\nCREATE INDEX books_title_idx ON books(title);\nALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE;<\/pre>\n<p>In MySQL:<\/p>\n<pre class=\"brush: sql\">CREATE TABLE authors (\n    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,\n    first_name VARCHAR(50),\n    last_name VARCHAR(50) NOT NULL\n);\nCREATE TABLE books (\n    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,\n    author_id INTEGER NOT NULL,\n    title VARCHAR(100) NOT NULL\n);\nCREATE TABLE fk_error_msg (\n    error_msg VARCHAR(100) NOT NULL PRIMARY KEY\n);\nINSERT INTO fk_error_msg VALUES ('Foreign Key Constraint Violated!');\nALTER TABLE authors ADD INDEX(last_name);\nALTER TABLE books ADD INDEX(author_id);\nALTER TABLE books ADD INDEX(title);\nCREATE TRIGGER fki_books_author_id\n    BEFORE INSERT ON books\n    FOR EACH ROW BEGIN\n        IF\n            0 = (SELECT COUNT(*) FROM authors WHERE id=new.author_id)\n        THEN\n            INSERT fk_error_msg VALUES ('Foreign Key Constraint Violated!');\n        END IF;\n    END;\nCREATE TRIGGER fku_books_author_id\n    BEFORE UPDATE ON books\n    FOR EACH ROW BEGIN \n        IF\n            0 = ( SELECT COUNT(*) FROM authors WHERE id = new.author_id )\n        THEN\n            INSERT INTO fk_error_msg VALUES ('Foreign Key Constraint Violated!');\n        END IF ;\n    END;\nCREATE TRIGGER fkdc_books_author_id\n    BEFORE DELETE ON authors\n    FOR EACH ROW BEGIN\n        DELETE FROM books WHERE author_id=old.id;\n    END;\n<\/pre>\n<p>In SQLite:<\/p>\n<pre class=\"brush: sql\">CREATE TABLE authors (\n    id INTEGER NOT NULL PRIMARY KEY,\n    first_name VARCHAR(50),\n    last_name VARCHAR(50) NOT NULL\n);\nCREATE TABLE books (\n    id INTEGER NOT NULL PRIMARY KEY,\n    author_id INTEGER NOT NULL,\n    title VARCHAR(100) NOT NULL\n);\nCREATE INDEX authors_last_name_idx ON authors(last_name);\nCREATE INDEX books_author_id_idx ON books(author_id);\nCREATE INDEX books_title_idx ON books(title);\nCREATE TRIGGER fki_books_author_id\n    BEFORE INSERT ON [books]\n        FOR EACH ROW BEGIN\n            SELECT RAISE(ROLLBACK, 'insert on table \"books\" violates foreign key constraint \"fki_books_author_id\"')\n            WHERE NEW.author_id IS NOT NULL AND (SELECT id FROM authors WHERE id = NEW.author_id) IS NULL;\n        END;\nCREATE TRIGGER fku_books_author_id\n    BEFORE UPDATE ON [books]\n        FOR EACH ROW BEGIN\n            SELECT RAISE(ROLLBACK, 'update on table \"books\" violates foreign key constraint \"fku_books_author_id\"')\n            WHERE NEW.author_id IS NOT NULL AND (SELECT id FROM authors WHERE id = NEW.author_id) IS NULL;\n        END;\nCREATE TRIGGER fkdc_books_author_id\n    BEFORE DELETE ON authors\n        FOR EACH ROW BEGIN\n            DELETE FROM books WHERE books.author_id = OLD.id;\n        END;\n<\/pre>\n<p>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.)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,11,33,34,88],"tags":[152,148,170,171,202],"class_list":["post-306","post","type-post","status-publish","format-standard","hentry","category-database","category-development","category-php","category-python","category-spdo","tag-database","tag-development","tag-php","tag-python","tag-spdo"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pxT7i-4W","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/posts\/306","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/comments?post=306"}],"version-history":[{"count":4,"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/posts\/306\/revisions"}],"predecessor-version":[{"id":420,"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/posts\/306\/revisions\/420"}],"wp:attachment":[{"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/media?parent=306"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/categories?post=306"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.evardsson.com\/blog\/wp-json\/wp\/v2\/tags?post=306"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}