Learning SQL - Changing Table Definitions - Part 3

This section is specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

Setting it all up - again!

Let's start again - recreate the table from part two.

Now let's recreate the wrongly named QiSQL_Bbooks table and drop our temporary table copy of the data ( all in one format, so you can just copy and past the whole lot into the SQL screen of QiSQL and Execute it):

drop table if exists QiSQL_Bbooks;
drop table if exists TMP_QiSQL_BBooks;
drop table if exists QiSQL_Books;
drop view if exists johnsbooks ;
create table QiSQL_Bbooks ( id integer primary key,
book_title text not null,
author text not null ,
book_cost numeric not null,
book_price numeric not null,
unique (book_title , author ) );
alter table QiSQL_Bbooks add ISBN text;
create unique index Q_Bbooks_ix1 on QiSQL_Bbooks ( ISBN );
create trigger QiSQL_Bbooks_No_delete
before delete on QiSQL_BBooks
for each row begin
select raise ( ROLLBACK , 'Delete not allowed' );
end;
insert into QiSQL_Bbooks (book_title,ISBN,book_cost,book_price, author) values
('Big Book' ,'123-123-123' , 23.99 , 47.99 ,'John Smith');
insert into QiSQL_Bbooks (book_title,ISBN,book_cost,book_price,author ) values
('Small Book' ,'123-123-456' , 19.99,38.99 ,'Fred Bloggs');
create view johnsbooks as select * from QiSQL_Bbooks where author = 'John Smith';

What have we done?

Now we're going to use the sqlite_master table to see what the database thinks we have done so far:

select type, name from sqlite_master
where tbl_name like 'QiSQL_Bbooks%';

This should give you this result:

type name
table QiSQL_Bbooks
index sqlite_autoindex_QiSQL_Bbooks_1
index Q_Bbooks_ix1
trigger QiSQL_Bbooks_No_delete

Excellent! This is just what we would have expected. Please note that our view is NOT shown here - a view could be made up from MANY tables joined together, so unless you know that a view accesses a particular table you could have big problems! Maybe a Data Dictionary like QiSQL for SQLite tables is a good idea?

Now, what we want to do is rename this naughty table and get rid of the extraneous Bee! But first we have to create a Tempoarary table and find index and trigger information from our original table ( also Views but that gets a bit too complicated for now! ). You will need to retain some of this information in your head, on a piece of paper or ideally in your application!

Find Column Definitions

First we create a temporary table using the structure and data from QiSQL_Bbooks. To do this we have to identify all the Columns in our original Table using a Pragma query:

pragma table_info ( QiSQL_Bbooks)

That SQLite Pragma query will return a list of all our column definition data for the original Table:

cid name type notnull dflt_value pk
0 id integer 0   1
1 book_title text 99   0
2 author text 99   0
3 book_cost numeric 99   0
4 book_price numeric 99   0
5 ISBN text 0   0

cid - is the internal SQLite Column Id number (the Primary Key). The dflt_value column would show default values but we haven't assigned any. If a Column has been defined as NOT NULL then 99 will be the value in the notnull column. If a column is the Primary Key then the value in pk will be 1. Why 99 and 1? - who knows!

Now we have our Column Information - we need to get our Index buidling information.

Just to make it MUCH more complicated - the Pragma table_info does not tell us if our Primary Keys are set to use AutoIncrement * see the section on Primary Keys for more info on Autoincrement. We have to query the table created by sqlite - sqlite_sequence to see if there is a record for this table!!!

Find the Index stuff

Let's use Pragma to find out what indexes we have on our original table:

pragma index_list (QiSQL_Bbooks);

That gives us our indexes:

Seq Name Unique
0 Q_Books_ix1 1
1 sqlite_autoindex_QiSQL_Bbooks_1 1

We can see that both of these indexes are Unique as the Unique column shows a value of 1.

So we can now find the column information for these indexes, for EACH index we execute this Pragma:

pragma index_info ( Q_Bbooks_ix1 );

seqno cid name
0 1 book_title
1 2 author

pragma index_info (sqlite_autoindex_QiSQL_Bbooks_1);

seqno cid name
0 5 ISBN

FYI - 'seqno' is the sequence number and 'cid' is the column id number.

Now we can build these indexes and build our temporary table (the copy of the original QiSQL_Bbooks). In your own programming language you would do something like this

What about Foreign Keys?

I've already said that SQLite does NOT enforce Foreign Key relationships so what's the problem. SQLite does not ENFORCE foreign keys but it does let you add them. Maybe they are planning to add enforcement later.

The only way you can identify Foreign Keys is to look at the SQL column ins sqlite_master. There is no Pragma information available.

select * from sqlite_master
where tbl_name like 'QiSQL_Bbooks%'
and type = 'table';

You will now have to manually identify and recreate the Foreign Keys on your temporary table, as there is no 'set' way of defining a Foreign Key in the Table Creation SQL! We MUST build the Foreign Key creation stuff into our create Table statement, as we can not add Foreign Key Constrainst later.

Create The Temporary Table

So programmatically you would be doing something like this:

Start Build Temporary Table

Loop - pragma table_ino (table_name)

   for each column 

       Check if PK

If PK Then Query sqlite_sequnce (if it exists) to see if record for this table exists If found the Primary Key is AutoIncrement
Check if Not Null Check if Default Value Build and add column definition , Build and add Insert List from Old table Identify Foreign Keys and add Loop - pragma index_list (table) for each index if unique then create unique index NEW NAME else create index NEW NAME (
Loop - pragma index_info (index_name)
for each column - add NAME to index

Got it? Your create statements would end up looking like this:

create table TMP_QiSQL_Bbooks (
id integer primary key,
book_title text not null,
author text not null,
book_cost numeric not null,
book_price numeric not null,
ISBN text ) ;

create unique index qisql_books_newindex1 on TMP_QISQL_Bbooks ( book_title , author );

create unique index qisql_books_newindex2 on TMP_QISQL_Bbooks (ISBN ) ;

insert into TMP_QiSQL_Bbooks (
id ,
book_title ,
author ,
book_cost ,
book_price ,
ISBN )
select id ,
book_title ,
author ,
book_cost ,
book_price ,
ISBN from QiSQL_BBooks;

Just to remind you - you have to do all that Manually or write an application in your Programming language - it is not trivial - we don't even have any Foreign Keys in our example!. So make sure you get you database design right first time.

If you were dropping or adding a column you would adjust you Create and insert statements above accordingly.

Hunt The Triggers

Now we have to find any triggers, this is where it gets really interesting. SQLite does provide a prgama statement for triggers so we have to go back to good old sqlite_master:

select * from sqlite_master
where tbl_name like 'QiSQL_Bbooks%'
and type = 'trigger';

Here's what we get:

CREATE TRIGGER QiSQL_Bbooks_No_delete
before delete on QiSQL_BBooks
for each row begin
select raise ( ROLLBACK , 'Delete not allowed' );
end

So now we have to do some clever code substitution to change references to our original table to our temporary table. We now have to substitute the old trigger name with the new name and the table name ( in the 'on' clause) to our new table name - whether you do this in code or manually it's up to you. This would end up looking like this:

CREATE TRIGGER QiSQL_Books_No_delete
before delete on QiSQL_Books
for each row begin
select raise ( ROLLBACK , 'Delete not allowed' );
end;

DO NOT RUN THIS YET! Hold on until after we have renamed the table - or you may get all that database corruption nastiness again - a trigger existing without a table!

Drop it and rename it

Now, at last, we can drop our old table and rename our temporary table to the correct name!

drop table QiSQL_Bbooks;

You MUST drop the original Table, or you will end up with the old Trigger exists for a table that doesn't exist probelm - which mayl corrupt your database!

alter table TMP_QiSQL_Bbooks rename to QiSQL_Books;

Recreate The Triggers

NOW, you can run the new Trigger creation command you created earlier:

CREATE TRIGGER QiSQL_Books_No_delete
before delete on QiSQL_Books
for each row begin
select raise ( ROLLBACK , 'Delete not allowed' );
end;

Where are the views?

I don't know! If you don't know which of your views access the table you want to change you will have to read through every view record in sqlite_master and check them manually!

select * from sqlite_master where type = 'view' ;

This is very painful! If you can think of a way to do it programmatically I'd love to hear from you!

Either keep a copy of your View creation scripts and which tables they access or use a Data Dictionary application like QiSQL!

Luckily in this example case we know that there is only one view accessing the table (unlikely to happen in the real world!) - so we can rewrite the script, drop the original view and re-apply it to the newly created table:

drop view johnsbooks;

CREATE VIEW johnsbooks as select * from QiSQL_Books where author = 'John Smith';

Check it out

You can now execute the sqlite_master queries and pragmas to check if everything is as expected.You know how to do it by now!

By storing all our schema information in a data dictionary QiSQL Database Manager deals with all this nastiness!