SQLite SQL Syntax

If you are having trouble converting MySQL  tables to SQLite tables then this tutorial is for you. On the other hand, if you have no idea what I just said then let me explain that both MySQL and SQLite are free, open source databases. While MySQL is the most used open source database in the world, SQLite is catching on as a great database for people who don't need a complex system to manage their data. I personally have been looking into it as a possibility for running my personal blog.

    SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen(). - the SQLite site

Anyway, if you have already started working with SQLite but have encountered problems with SQL syntax - I would like to explain some of the main differences in creating a table. Lets start with a basic MySQL table.

/* Table structure for mySQL table */

CREATE TABLE `tablename` (
`id` int(11) NOT NULL auto_increment,
`title` text NOT NULL default '',
`text` text NOT NULL,
`time` int(10) NOT NULL default '0000000000',
UNIQUE KEY `id` (`id`),
) TYPE=MyISAM;

If you try to just port that over into SQLite - you'll get so many errors it's not funny. First lets start with the "INT" column type. In SQLite it is "INTEGER" not "INT". So, that is the first thing that needs to be changed.

Second, in SQLite "auto_increment" is a given if the key is a PRIMARY KEY. So we can delete the "auto_increment" part of the MySQL query. This now leaves us with the following:

-- Table structure for NEW SQLite table
CREATE TABLE `tablename` (
`id` INTEGER NOT NULL ,
`title` text NOT NULL default '',
`text` text NOT NULL,
`time` INTEGER NOT NULL default '0000000000',
UNIQUE KEY `id` (`id`),
) TYPE=MyISAM;

Third, we don't need the "UNIQUE KEY `id` (`id`)," line. In SQLite, that data goes on the same line as the item that is set to the "PRIMARY KEY".

-- Table structure for NEW SQLite table
CREATE TABLE `tablename` (
`id` INTEGER PRIMARY KEY NOT NULL ,
`title` text NOT NULL default '',
`text` text NOT NULL,
`time` INTEGER NOT NULL default '0000000000',
) TYPE=MyISAM;

Forth, remove the bottom " TYPE=MyISAM" as there is no such type in SQLite. In MySQL you can chose from one of several DB types. However, SQLite only has 1 type - so there is no need to specify it.

Now the last thing we need to do is get rid of the " ` " characters in the code as SQLite doesn't like them in queries. So we just replace all the " ` " with double-quotes (NOT single quotes). You can also just leave them off as they are NOT required. Also, delete the ending comma. Our final output is:

-- Table structure for NEW SQLite table
CREATE TABLE "tablename" (
"id" INTEGER PRIMARY KEY NOT NULL ,
"title" text NOT NULL default "",
"text" text NOT NULL,
"time" INTEGER NOT NULL default "0000000000"
);

The following tables show how you can use double-quotes in any order without fear of trouble:

<?php

$query = 'CREATE TABLE temp2 (
                    id INTEGER PRIMARY KEY NOT NULL,
                    title TEXT NOT NULL ,
                    text TEXT NOT NULL ,
                    time INTEGER NOT NULL
                );
CREATE TABLE "temp3" (
                    "id" INTEGER PRIMARY KEY NOT NULL,
                    "title" TEXT NOT NULL ,
                    "text" TEXT NOT NULL ,
                    "time" INTEGER NOT NULL
                );
CREATE TABLE temp4 (
                    "id" INTEGER PRIMARY KEY NOT NULL,
                    "title" TEXT NOT NULL ,
                    "text" TEXT NOT NULL ,
                    "time" INTEGER NOT NULL
                );
CREATE TABLE temp5 (
                    id INTEGER PRIMARY KEY NOT NULL,
                    "title" TEXT NOT NULL ,
                    text TEXT NOT NULL ,
                    "time" INTEGER NOT NULL
                );';
?>

SQL As Understood By SQLite is a great read for anyone who wants to check advanced queries against the SQL standard. The SQLite Wiki also has a great list of command-line & desktop programs that you can use to create SQLite tables for you if you are still unsure about all this. I personally like SQLiteMan as I can create the database and tables right on my Windows desktop and then export a SQL Schema for use in my
soccer uniform | cheap jerseys | football shirt shop

Last edited by Neeraj555 (2013-09-13 08:43:59)