SQL and other databases

Tue Mar 22 13:29:20 EDT 2011

Hi Bob,
I'm slowly putting together a list of differences between SQLite and MySQL.  I'm concentrating on things that SQLite allows and MySQL does not.  I haven't looked at extra functionality provided by MySQL over and above what SQLite provides since right now I just want to get to the point that my SQLite schema definitions and data manipulation statements work in MySQL

Before getting to the list, a couple of observations on the issues you've come across.  

I'm not seeing the requirement to have NOT NULL in conjunction with DEFAULT.  Here's a snippet which was accepted just fine by mySQL:

`BandTrakSalesID` int(11) DEFAULT '0',
  `Selected` tinyint(1) DEFAULT '1',

I seem to remember another email from you regrading the various types of quote characters.   MySQL's default is that identifiers (dabatabase names, table names, etc) are enclosed in the back tick character and strings can be enclosed in either single or double quotes.  Single or double quotes within a string that uses the same delimiter can be escaped by doubling them up or preceding them with a backslash.  However by setting the SQL_MODE ANSI_QUOTES variable, you can make mySQL behave like SQLite - identifiers are enclosed in double quotes and strings are enclosed in single quotes.  I believe that parameter can be set at the server level and for individual sessions using the SET command.  I found this very useful when executing the CREATE statements generated by a Firefox SQLIte Manager Export command since it encloses identifiers in double quotes.

Now on to the list.

SQLite allows the use of TEXT columns as primary keys, allows you to build Indexes on them and have them as Foreign Keys.  MySQL does not allow any of those things for TEXT fields.

I had a problem with BEGIN/END in CREATE TRIGGER statements that only had one action between them (got syntax errors).  Removing the BEGIN/END pair fixed the problem.

I'm still investigating the various ways in which MySQL stores and handles dates but there are some major differences.  However, the standard text representation of dates in SQLite looks like it will be recognised by MySQL (YYYY-MM-DD HH:MM:SS).  MySQL does not have a built in Julian day function whereas SQLite does.  SQLIte has date, time, datetime, julianday, and strftime functions which don't exist in MySQL although it provides the same functionality, just not with the same names.  So if you have any data manipulation statements that use those SQLite functions, they'll have to be changed to use the MySQL equivalents.

SQLite recognises foreign key definitions within column definitions.  MySQL  ignores Foreign Key definitions embedded in column definitions, eg "col1 REFERENCES TableA(col1)".  What I mean by that is that they are not enforced, neither are they recorded in the information_schema database.  In order to have a foreign key enforced and recorded in information_schema, you have to use the CONSTRAINT clause of the CREATE TABLE statement, eg CONSTRAINT FOREIGN KEY (col1) REFERENCES TableA(col1).

SQLite allows the optional use of the keyword TRANSACTION in BEGIN, COMMIT, and ROLLBACK statements, MySQL does not.  SQLite has DEFERRED, IMMEDIATE and EXCLUSIVE as keywords to the BEGIN statement which affect the way locks are placed.  MySQL does not have an equivalent in its BEGIN statement although I suspect there are other ways to get the same behavior, just haven't had time to investigate yet.  

SQLite has a set of PRAGMA statements that set/return information about the structure of the database and the settings of various parameters that affect its operation.  mySQL doesn't have PRAGMA statements.  There are SHOW and SET statements that fulfil the same function, eg SHOW COLUMNS FROM tablename returns a list of column information for a table.  In some cases, you need to access the information_schema database to do the equivalent of a PRAGMA statement, eg, there is a PRAGMA that returns information about foreign keys but in MySQL you have to access the information_schema table KEY_COLUMN_USAGE to get hold of that information.

That's what I've found so far, maybe there'll be more as I get deeper into using MySQL.

Pete Haworth

On Mar 21, 2011, at 10:08 AM, Bob Sneidar wrote:

> Hi Peter. 
> 
> Well I don't have a complete listing because I am finding them out as I go. My current project gets the SQL to create a table from the sqlite_master table, then massages it to create the same table in mySQL. MySQL uses SELECT CREATE TABLE to get this information. 
> 
> One gotcha is that sqLite allows enumeration (is that the right word??) in their datetime column (although why I cannot tell). That is you can declare datetime(30) but you cannot do this in mySQL. 
> 
> I have also discovered that sqLite allows you to declare a default value for a char type column without using NOT NULL. mySQL requires NOT NULL if you declare a default. You cannot have a default for a TEXT type in mySQL (if the manual is correct). 
> 
> I am not sure but I don't think sqLite uses what I call sequel quotes the ` character (shift-tilde). 
> 
> Someone a while back posted a link to a web page that described a number of things you could NOT do in adding columns to an existing sqLite table. The consensus was that it was better to simply dump the data and create the new table, and then re-import the data. For instance you can insert a column between existing columns in mySQL. You cannot in sqLite. 
> 
> sqLite is NOT multiuser, in the sense that you cannot have 2 read/write connections at one time. Although you CAN have many read only connections, only one can be read/write. 
> 
> In your case, going from sqLite to mySQL, I don't think you will have any problems with your actual data queries. Select statements are pretty standard (although I have been told by my web dev friend that MS SQL differs significantly in their query standards. What a surprise!) 
> 
> Bob
> 
> 
> On Mar 21, 2011, at 9:34 AM, Peter Haworth wrote:
> 
>> Bob,
>> I'm about to make the leap from sqlite to mySQL so I'm very interested in the differences you mentioned.  I think I've seen some of them in posts on the list here but any chance you could summarise them?  I guess I am expecting some functional improvements in mySQL over sqlite since sqlite advertises itself as a subset of the sql standards but it sounds like you've found some syntactical differences as well.
>> 
>> Pete Haworth
>> 
>> On Mar 21, 2011, at 9:19 AM, Bob Sneidar wrote:
>> 
>>> I have encountered a number of differences in different engines relating to syntax, when to use regular quotes, when to use sequel quotes, what column types can or cannot be enumerated, etc. 
>> 
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> 
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
> 