Búsqueda por archivo: 2011

Mysql, SQLite3 and ANSI SQL thoughts

sql , sqlite3 , mysql , ansi sql
Jose Luis Canciani

I recently did a refactor for one of the tools I maintain to switch from MySQL to SQLite3. It's and error reporter tool, so we need it to be available even if MySQL fails Tongue out

Even though it's a small app, I hit several things that are worth mentioning. Please note that this is not a recommendation to start doing things differently, it's just a series of issues I found in the process.


The standard way is to enclose stringsin SQL in simple quotes. No need to escape anything but the simple quote itself. Escaping is done with another simple quote.

insert into contactInfo (contactId, lastName) values (100, 'O''Neal');

Escaping % and _ in the LIKE function is done by adding an explicit the escape character:

select * from contactInfo where firstName like 'hola\_mundo' escape '\';

In Mysql we were using double quotes, and we escape with backslash. This is not cross platform (see ANSI_QUOTES bellow).

Object names - identifiers- (tables, columns, functions, etc)

Almost all database engines are case insensitive when referring to object names. Double quotes is used when you want to use a reserved word, special characters, unicode characters or case sensitivity. If you don't use double quotes, then the names are usually translated to uppercase in the database dictionary, but it remains case insensitive when referring to it.

In Mysql, table names are case sensitive (because they are files in a case sensitive filesystem, at least on unix/linux). This could be changed using the lower_case_table_names parameter. Mysql would convert all table names to lowercase for storage and lookups.

But also in MySQL, columns names are not case sensitive. So
  select FirstName from contactInfo;
  select firstname from contactInfo;
is the same, no matter how you write the SQL to create the table.

In Mysql you can use, like we were doing, backquotes; but that won't help in setting a case to column names (or index, store routines and column aliases)

Mysql                         Standard SQL
create table `table` (        create table "table" (
  `select` varchar(10)          "select" varchar(10)
);                            );

Regarding the use of quotes, we could change the behavior to use the standard doble quotes by setting the ANSI_QUOTES sql mode. Note that once you do change it, all double-quoted strings become identifiers, so all strings are required to be enclosed by single quotes.

SQL statement diferences

INSERT INTO TABLE SET col1 = val1, col2 = val2 is not a valid SQL in other database apart from Mysql. The correct use is INSERT INTO table (<columnlist>) values (<valuelist>), although I have to say that I like this syntax.

SQLite3 does not support TRUNCATE TABLE, but when doing a DELETE without a where clause, it is treated as a truncate statement.

and SQLite3's
are not standard SQL. The correct SQL to do these tasks is the MERGE statement, but non of them supports it (today). It would be a good practice to wrap all this queries using classes, that way time and work for switching dbs -if we need to- will be greatly reduce.

SQLite3 Types

SQLite3 supports less types than mysql:

            SQLITE3_INTEGER => 'int',
            SQLITE3_FLOAT => 'float',
            SQLITE3_NULL => 'null',
            SQLITE3_TEXT => 'text',
            SQLITE3_BLOB => 'blob'

You don't need to specify a character set for a text column, you simply insert what you have in a variable. If its UTF8, you insert UTF8, if its ASCII, you insert ASCII.

Bind Variables (or Bound Parameters)

Both databases now support bind variables. We do not use it today, but when running the same query several times Binding variables have good performance advantages since the query is only parsed once. It also removes the SQL Injection problem, since there is no need to escape strings anymore.

Here's a quick example, inserting 10000 rows in a table:

    if ($testType == 'Bind') {
        $stmt = $conn->prepare("insert into test (a,b,c,d,e,f,g,h,i,j) values (?,?,?,?,?,?,?,?,?,?)");
        for($i=1;$i<=10000;$i++) {
            $stmt->bind_param("iiiiiiiiii", $i,$i,$i,$i,$i,$i,$i,$i,$i,$i);
    } else {
        for($i=1;$i<=10000;$i++) {
            $stmt = $conn->prepare("insert into test (a,b,c,d,e,f,g,h,i,j) values ($i,$i,$i,$i,$i,$i,$i,$i,$i,$i)");

Bind 10000 inserts done in 0.57499408721924 seconds
No bind 10000 inserts done in 1.006609916687 seconds

The difference could be even greater on SELECT statements with a lot of tables, since the time to parse and generate an execution plan will increase.

When having hundreds of queries per second, like we do, time and CPU reduction could be considerable. Since MySQL 5.1.17, prepared SQL Statements are cached too. 



Time Machine with OSX Lion and a Linux server

lion , ubuntu , netatalk , avahi , macosx , debian , macbookair
Jose Luis Canciani


I got my new Mac Book Air, 13", 2011, Core i7, 256GB SSD. Incredible speed and portability. And the new Mac OS Lion.

Everything worked great except... Time Machine. I have a fitPC1 ( from several years ago that I used as a File Server, SVN repo and LAMP server (among other things like torrent client). It worked great with Snow Leopard, but it was time for an upgrade.

Apparently the old version of NetATalk I had installed does not worked with Lion anymore. No new packages (of anything) were to be found for my Ubuntu 8.10. So I started with the long -but no so painful- upgrade process: 8.10 => 9.04 => 9.10 => 10.04.3 LTS (I could have made a clean install, but didn't want to reconfigure everything).

After that, I installed netatalk 2.2 from source (download at )


./configure --enable-debian 
make install


Everything default but with the debian keyword to have scripts added in my ubuntu init. Avahi daemon was already setup from my previous install, if you don't have it, see next link).

Differently from the binary package I used the first time (as mentioned here: HowTo: Make Ubuntu A Perfect Mac File Server And Time Machine Volume ), configure put my config files in /usr/local/etc/netatalk by default, so I created a symbolic link from /etc/netatalk => /usr/local/etc/netatalk and replaced/updated my old files as I see fit.

I tried to add the to the afpd.conf file, which was suggested in every forum I visited, with not so much luck. Lion saw the volumes, but kept giving me permissions errors. I had to change from cdb to dbd in the AppleVolumes.default file, and deleted all .Apple* from all directories in the shared folders just to be sure there was no conflic with the old db).

After that, everything was working but Time Machine. I could not see the drive available in the Time Machine setup screen.

I googled and googled, tried several things until I got it working. Let me make a list of what I did:
This is a coLinux tutorial (running netatalk on Windows with coLinux). It had useful scripts to configure some files. These one I used:

sed -i s/Xserve/TimeCapsule6,106/ /etc/avahi/services/afpd.service
uuid=`uuidgen | tr '[a-z]' '[A-Z]'`;mac=$(ip link show eth0 | awk '/ether/ {print $2}')
echo -e '\n\n\n\t\n\t\t_adisk._tcp\n\t\t9\n\t\tsys=waMA='$mac',adVF=0x100\n\t\tdk0=adVF=0x81,adVN=TimeMachine,adVU='$uuid'\n\t\n' >/etc/avahi/services/adisk.service
mkdir -p /media/tm # for log file
echo - -tcp -noddp -uamlist, -nosavepassword -setuplog \"default log_warn /media/tm/.afpd.log\" >>/etc/netatalk/afpd.conf
echo \"TimeMachine\" $uuid >>/etc/netatalk/afp_voluuid.conf

It still didn't work, but helped me setup several files that didn't exists. There is an "option:tm" in this tutorial that I missed (since I already had my AppleVolumes.default configured and working, so I didn't pay attention to modification of that file). It would have help me later to save some time instead of keep googling.

I kept googling and found this one: Time Machine meet Netatalk
Down there there's a simple touch that did the magic:

touch /pool/backup/time_machine/

(change /pool/backup to your time machine shared directory, in my case /mnt/seagate640).

Magically the drive appeared in Time Machine, altough not sure if after or before doing this on my macbook air:

sudo defaults write TMShowUnsupportedNetworkVolumes 1

I could select the disk, configure Time Machine and start the backup. But, after some seconds, I received this error: "The network backup disk does not support the required AFP features."

I kept googling until I found this newsletter: that mentioned: "Please check if you're really setting options:tm for the Time Machine volume"

That was it! I modified by AppleVolumes.default:

:DEFAULT: options:upriv,usedots
/mnt/seagate640 seagate640 allow:joe,melina cnidscheme:dbd options:usedots,upriv,tm
/var/cache/torrentflux torrentflux allow:joe,melina cnidscheme:dbd options:usedots,upriv

I added "tm" to my options and it worked! I now have my Lion backing up to my Fit-PC Ubuntu 10.04 server, with a Seagate 640GB Password drive attached.

I hope this helps someone to avoid hours googling and trying over and over again :)


Últimos comentarios
  • Posteado por: Ezequiel Sanson

    "Muy buena la pagina... habra que entrar mas seguido!..." »leer y comentar
  • Posteado por: Carla Ferfolja

    "Testing Carli..." »leer y comentar
  • Posteado por: Jose Luis Canciani

    "Gracias por el aviso!..." »leer y comentar
  • Posteado por: ikobopyjomedek

    "ikobopyjomedek... Mimsy F..." »leer y comentar
  • Posteado por: eqetitijefajon

    "eqetitijefajon... Beat..." »leer y comentar

2010 Copyright © 4TM - todos los derechos reservados