This is an archived copy of the forum for reference purposes

SQLite and column named 'weight' or 'time'; or something is wrong here...

posted by audvare 8 years ago

I am working on making my site compatible with SQLite3(so I can demo it live running on my iPhone without having to run MySQL). First I figured out that translatedQuery() is not going to fix a lot of the problems between SQLite and MySQL like the following:

MySQL to SQLite:

AUTO_INCREMENT -> AUTOINCREMENT (although this is unnecessary but if a PRIMARY KEY clause exists it must remain)
- id INT PRIMARY KEY NOT NULL AUTO_INCREMENT -> id INT PRIMARY KEY
CREATE TABLE tablename(...) ENGINE=InnoDB DEFAULT CHARSET=utf8; -> CREATE TABLE tablename(...);

Also, all indexes are unique across tables unlike in MySQL in SQLite.

This table seems to fail to find its column named 'weight' with fActiveRecord:

DROP TABLE IF EXISTS css_files;
CREATE TABLE css_files (
  cid INT PRIMARY KEY,
  filename VARCHAR(128) NOT NULL,
  media VARCHAR(128) NOT NULL DEFAULT 'all',
  weight INT SIGNED NOT NULL DEFAULT 0
);
CREATE INDEX idx_css_files_filename_media ON css_files (filename, media);

    $cssFiles = fRecordSet::build('Base_Css', array(), array('weight' => 'asc'));
    if ($cssFiles->count() > 0) {
      foreach ($cssFiles as $record) {
        $attr['href'] = $baseUrl.$record->getFilename();
        $attr['media'] = $record->getMedia();
        $this->head->createElement('link', $attr);
      }
    }

Flourish parts of exception:

#!text/html
Uncaught exception 'fProgrammerException' with message 'The column specified, weight, does not exist in the table css_files' in /private/var/www/sutra/Libraries/Flourish/fSchema.php:2339
Stack trace:
#0 /private/var/www/sutra/Libraries/Flourish/fORMDatabase.php(417): fSchema->getColumnInfo('css_files', 'weight', 'type')
#1 /private/var/www/sutra/Libraries/Flourish/fRecordSet.php(233): fORMDatabase::addOrderByClause(Object(fDatabase), Object(fSchema), Array, 'css_files', Array)

This happened with a column named time for me as well so I renamed that to stamp (thinking it might be a naming conflict with reserved word time) but that didn't actually fix it. Instead it just cannot find the column stamp now.

One other questions regarding Flourish SQL:

Why doesn't Flourish resolve to using InnoDB by default if it finds support in MySQL? Or does it use what the table default is? This is part of the reason why all my SQL CREATE TABLE statements mention the engine and DEFAULT CHARSET=utf8.

Thanks

The main issues you are having with FlourishSql is that you are almost, but not quite following the standardized syntax. If you look at the page, you'll notice that integer signed is not a standard data type across the databases. If you drop the signed everything will work as you expect. If you open a ticket I can try to be more flexible about the schema detection with SQLite, but it is not the simplest task since users can use any number of almost any word for a data type in SQLite.

In terms of the autoincrementing primary key, you need to do integer autoincrement primary key. If you don't put the autoincrement between integer and primary key it won't translate properly.

For CREATE TABLE statements, fSQLTranslation does currently set the table to use ENGINE=InnoDB. If you aren't seeing that, then perhaps there is some issue with it translating properly.

Are you running all of your CREATE TABLE statements through fDatabase::translatedQuery() or fDatabase::translatedExecute()? If not, this would explain a number of the issues you are seeing.

I have been working on some great enhancements to fDatabase, fSchema and fSQLTranslation. If things go well, they should be pushed live today or tomorrow. One of the fixes in there is the addition of specifying the character set for MySQL tables. In addition, it will be possible to pass SQL into a method on fDatabase and have it spit out the translated SQL, without actually running it.

posted by wbond 8 years ago

#!text/html
Are you running all of your CREATE TABLE statements through fDatabase::translatedQuery() or fDatabase::translatedExecute()? If not, this would explain a number of the issues you are seeing.

Which one should I use for CREATE TABLE?

I have fixed the SQL and this works so far on MySQL and SQLite without issue. The main thing is that for the moment all my SQL has been written to be executed in MySQL not anything else (hence ENGINE and etc).

Perhaps sometime I'll create a readline or something similar that will use fDatabase and translatedQuery() as an alternative to running CLI MySQL. (Mode of development just for speed purposes has been to write the create table syntax, make the fActiveRecord class file, add it to my relationships list, and paste the SQL into CLI MySQL where it expects nothing but MySQL syntax).

posted by audvare 8 years ago

Also with SQLite 3 having trouble finding the column stamp on this table:

DROP TABLE IF EXISTS event_log;
CREATE TABLE event_log (
  eid INTEGER AUTOINCREMENT PRIMARY KEY,
  class VARCHAR(128) NOT NULL,
  line INTEGER UNSIGNED NOT NULL,
  location VARCHAR(255) NOT NULL,
  referrer VARCHAR(255) NOT NULL DEFAULT '',
  message TEXT NOT NULL,
  severity INTEGER UNSIGNED NOT NULL DEFAULT 0,
  uid INTEGER UNSIGNED NOT NULL DEFAULT 0,
  ip VARCHAR(64) NOT NULL DEFAULT '',
  stamp INTEGER NOT NULL
);

#!text/html
Query time was 0.00023293495178223 seconds for:
SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'event_log'

The column specified, stamp, does not exist' in Libraries/Flourish/fActiveRecord.php:2608
Stack trace:
#0 Libraries/Flourish/fActiveRecord.php(895): fActiveRecord->set('stamp', 1304466628)
#1 EventLogEntry.php(30): fActiveRecord->__call('setStamp', Array)

Removing SIGNED on the weight column fixed the issue with the css_files table but removing it here has not.

EDIT: Was running into this problem because it kept using old versions of a file with the SQL that created the table. Removed UNSIGNED and SIGNED from the table and all works now.

posted by audvare 8 years ago