Re: [sqlite] Conversion mysql -> sqlite
On 15-mrt-05, at 21:26, Peter Jay Salzman wrote: It appears that the error is a "syntax error" near where my arrow (<--) is. Can someone throw some charity my way? Is there anything obviously wrong here? Yes, remove the "TYPE=MyISAM" part. This is a MySQL specific extension. HTH, - Rob Laveaux Pluggers Software Thijssestraat 203 2521 ZG Den Haag The Netherlands Email: [EMAIL PROTECTED] Website: http://www.pluggers.nl
RE: [sqlite] Conversion mysql -> sqlite
Peter Jay Salzman wrote:
> Hi all,
>
> I know very little about mysql and sqlite - I've only played
> around a little bit with databases, so I'm a newbie in this area.
>
> There's a blogger called Wheatblog that I've been trying to
> convert from mysql to sqlite so I don't have to run a full
> RDMBS deamon on my underpowered machine for just a single
> blog program.
>
> I think I've successfully replaced all the mysql calls with
> wrapper functions that use sqlite functions. There's just a
> few bits left that are causing errors, and I'm having trouble fixing
> them.
>
> Here's the bit of mysql sql that creates the database used by
> Wheatblog. It came in the Wheatblog zip file. I'm surprised
> to see the accent grave (`):
>
>
>CREATE TABLE `wheatblog_categories` (
> `id` int(11) NOT NULL auto_increment,
> `category` varchar(30) NOT NULL default '',
> PRIMARY KEY (`id`)
>) TYPE=MyISAM;
>
>CREATE TABLE `wheatblog_comments` (
> `id` int(11) NOT NULL auto_increment,
> `comment_author_name` varchar(100) NOT NULL default '',
> `comment_author_email` varchar(100) NOT NULL default '',
> `comment_author_url` varchar(100) NOT NULL default '',
> `comment_body` mediumtext NOT NULL,
> `post_id` int(11) NOT NULL default '0',
> `comment_month` int(2) NOT NULL default '0',
> `comment_date` int(2) NOT NULL default '0',
> `comment_year` int(4) NOT NULL default '0',
> PRIMARY KEY (`id`)
>) TYPE=MyISAM;
>
>CREATE TABLE `wheatblog_posts` (
> `id` int(11) NOT NULL auto_increment,
> `day` varchar(10) default '',
> `month` int(2) NOT NULL default '0',
> `date` int(2) NOT NULL default '0',
> `year` int(4) NOT NULL default '0',
> `category` int(1) NOT NULL default '1',
> `showpref` tinyint(1) default '1',
> `body` mediumtext NOT NULL,
> `title` varchar(100) default '',
> `number_of_comments` int(11) NOT NULL default '0', PRIMARY
>KEY (`id`) ) TYPE=MyISAM;
>
>INSERT INTO `wheatblog_categories` (id, category)
> values (null, 'unfiled');
>
>
> And here's how I've tried to implement it on sqlite using
> PHP. Note that
> DB_query() is a wrapper for sqlite_query(). I've changed the
> "auto_increment" to "INTEGER PRIMARY KEY", as the FAQ said.
> I'm very new to SQL, but I guess "INTEGER PRIMARY KEY" and
> "NOT NULL" aren't separated by commas?
>
>
>function Create_Wheatblog_Database( $db )
>{
> $cmd =
> "CREATE TABLE 'wheatblog_categories' (
> 'id' INTEGER PRIMARY KEY NOT NULL,
> 'category' varchar(30) NOT NULL default '',
> ) TYPE=MyISAM;"; <---
>
> echo "$cmd";
> DB_query($cmd, $db);
>
>
> $cmd =
> "CREATE TABLE 'wheatblog_comments' (
> 'id' INTEGER PRIMARY KEY NOT NULL,
> 'comment_author_name' varchar(100) NOT NULL default '',
> 'comment_author_email' varchar(100) NOT NULL default '',
> 'comment_author_url' varchar(100) NOT NULL default '',
> 'comment_body' mediumtext NOT NULL,
> 'post_id' int(11) NOT NULL default '0',
> 'comment_month' int(2) NOT NULL default '0',
> 'comment_date' int(2) NOT NULL default '0',
> 'comment_year' int(4) NOT NULL default '0', )
> TYPE=MyISAM;";
>
> DB_query($cmd, $db);
>
>
> $cmd =
> "CREATE TABLE 'wheatblog_posts' (
> 'id' INTEGER PRIMARY KEY NOT NULL,
> 'day' varchar(10) default '',
> 'month' int(2) NOT NULL default '0',
> 'date' int(2) NOT NULL default '0',
> 'year' int(4) NOT NULL default '0',
> 'category' int(1) NOT NULL default '1',
> 'showpref' tinyint(1) default '1',
> 'body' mediumtext NOT NULL,
> 'title' varchar(100) default '',
> 'number_of_comments' int(11) NOT NULL default '0', )
> TYPE=MyISAM;";
>
> DB_query($cmd, $db);
>
>
> $cmd =
> "INSERT INTO 'wheatblog_categories' (id, category)
> values (null, 'unfiled');";
>
> DB_query($cmd, $db);
>
>}
>
>
> It appears that the error is a "syntax error" near where my arrow
> (<--) is. Can someone throw some charity my way? Is there
> anything obviously
> wrong here?
>
> Thanks,
> Pete
try changing
'number_of_comments' int(11) NOT NULL default '0', )
> TYPE=MyISAM;";
to
'number_of_comments' int(11) NOT NULL default '0')
reid
Re: [sqlite] Conversion mysql -> sqlite
On Tue 15 Mar 05, 9:36 PM, Rob Laveaux <[EMAIL PROTECTED]> said: > > On 15-mrt-05, at 21:26, Peter Jay Salzman wrote: > > >It appears that the error is a "syntax error" near where my arrow > >(<--) > >is. Can someone throw some charity my way? Is there anything > >obviously > >wrong here? > > Yes, remove the "TYPE=MyISAM" part. This is a MySQL specific extension. > > HTH, > > - Rob Laveaux Hi Rob and Reid, Thanks for the help! I really apprecaite you guys! I got over an important hurdle - Wheatblog now uses wrapper functions for all database access, and I've written a sqlite backend for the wrapper functions. And it actually runs. w00t! :) It appears that the SQL between sqlite and mysql isn't as compatible as I had hoped. Looks like my accomplishment was the easy part. This might be a bit more difficult with my current knowledge. I'll do some more reading and think some more to see if I can come up with the answer myself before posting for help. At least the application doesn't obviously fail. It even appears go work, as long as you don't do anything that requires SQL. LOL! Thanks! Pete -- Save Star Trek Enterprise from extinction: http://www.saveenterprise.com GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
Re: [sqlite] Conversion mysql -> sqlite
At 3:26 PM -0500 3/15/05, Peter Jay Salzman wrote: And here's how I've tried to implement it on sqlite using PHP. Note that DB_query() is a wrapper for sqlite_query(). I've changed the "auto_increment" to "INTEGER PRIMARY KEY", as the FAQ said. I'm very new to SQL, but I guess "INTEGER PRIMARY KEY" and "NOT NULL" aren't separated by commas? Having NOT NULL with a PRIMARY KEY is redundant, if not an outright error. Defining something as a primary key is implicitly defining it to be both not null and distinct. -- Darren Duncan
Re: [sqlite] Conversion mysql -> sqlite
Darren Duncan said: > Having NOT NULL with a PRIMARY KEY is redundant, if not an outright > error. Defining something as a primary key is implicitly defining it > to be both not null and distinct. -- Darren Duncan You'll find NOT NULL and PRIMARY KEY to be perfectly compatible, at least in SQLite and MySQL. Many DB systems do allow NULLs in the primary key, even if that's not strictly to the standard. Heaven help the poor souls who do put NULLs in the primary key, but some systems assume that you know what you're doing. Clay Dowling -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] Conversion mysql -> sqlite
On Tue, Mar 15, 2005 at 03:26:26PM -0500, Peter Jay Salzman wrote: > Hi all, > > I know very little about mysql and sqlite - I've only played around a little > bit with databases, so I'm a newbie in this area. > > There's a blogger called Wheatblog that I've been trying to convert from > mysql to sqlite so I don't have to run a full RDMBS deamon on my > underpowered machine for just a single blog program. [snip] There is a nice tool called SQLFairy[1] which can do sql translation from one database's sql to another's. I've used it many times for mysql to sqlite sql conversion. Right now it really only works with the definition portion of the SQL (CREATE statements) and not with the data manipulation statements. enjoy, -jeremy [1] http://sqlfairy.sourceforge.net/ -- Jeremy Hinegardner [EMAIL PROTECTED]
RE: [sqlite] Conversion mysql -> sqlite
Peter Jay Salzman wrote:
> Hi all,
>
> I know very little about mysql and sqlite - I've only played
> around a little bit with databases, so I'm a newbie in this area.
>
> There's a blogger called Wheatblog that I've been trying to
> convert from mysql to sqlite so I don't have to run a full
> RDMBS deamon on my underpowered machine for just a single
> blog program.
>
> I think I've successfully replaced all the mysql calls with
> wrapper functions that use sqlite functions. There's just a
> few bits left that are causing errors, and I'm having trouble fixing
> them.
>
> Here's the bit of mysql sql that creates the database used by
> Wheatblog. It came in the Wheatblog zip file. I'm surprised
> to see the accent grave (`):
>
>
>CREATE TABLE `wheatblog_categories` (
> `id` int(11) NOT NULL auto_increment,
> `category` varchar(30) NOT NULL default '',
> PRIMARY KEY (`id`)
>) TYPE=MyISAM;
>
>CREATE TABLE `wheatblog_comments` (
> `id` int(11) NOT NULL auto_increment,
> `comment_author_name` varchar(100) NOT NULL default '',
> `comment_author_email` varchar(100) NOT NULL default '',
> `comment_author_url` varchar(100) NOT NULL default '',
> `comment_body` mediumtext NOT NULL,
> `post_id` int(11) NOT NULL default '0',
> `comment_month` int(2) NOT NULL default '0',
> `comment_date` int(2) NOT NULL default '0',
> `comment_year` int(4) NOT NULL default '0',
> PRIMARY KEY (`id`)
>) TYPE=MyISAM;
>
>CREATE TABLE `wheatblog_posts` (
> `id` int(11) NOT NULL auto_increment,
> `day` varchar(10) default '',
> `month` int(2) NOT NULL default '0',
> `date` int(2) NOT NULL default '0',
> `year` int(4) NOT NULL default '0',
> `category` int(1) NOT NULL default '1',
> `showpref` tinyint(1) default '1',
> `body` mediumtext NOT NULL,
> `title` varchar(100) default '',
> `number_of_comments` int(11) NOT NULL default '0', PRIMARY
>KEY (`id`) ) TYPE=MyISAM;
>
>INSERT INTO `wheatblog_categories` (id, category)
> values (null, 'unfiled');
>
>
> And here's how I've tried to implement it on sqlite using
> PHP. Note that
> DB_query() is a wrapper for sqlite_query(). I've changed the
> "auto_increment" to "INTEGER PRIMARY KEY", as the FAQ said.
> I'm very new to SQL, but I guess "INTEGER PRIMARY KEY" and
> "NOT NULL" aren't separated by commas?
>
>
>function Create_Wheatblog_Database( $db )
>{
> $cmd =
> "CREATE TABLE 'wheatblog_categories' (
> 'id' INTEGER PRIMARY KEY NOT NULL,
> 'category' varchar(30) NOT NULL default '',
> ) TYPE=MyISAM;"; <---
>
> echo "$cmd";
> DB_query($cmd, $db);
>
>
> $cmd =
> "CREATE TABLE 'wheatblog_comments' (
> 'id' INTEGER PRIMARY KEY NOT NULL,
> 'comment_author_name' varchar(100) NOT NULL default '',
> 'comment_author_email' varchar(100) NOT NULL default '',
> 'comment_author_url' varchar(100) NOT NULL default '',
> 'comment_body' mediumtext NOT NULL,
> 'post_id' int(11) NOT NULL default '0',
> 'comment_month' int(2) NOT NULL default '0',
> 'comment_date' int(2) NOT NULL default '0',
> 'comment_year' int(4) NOT NULL default '0', )
> TYPE=MyISAM;";
>
> DB_query($cmd, $db);
>
>
> $cmd =
> "CREATE TABLE 'wheatblog_posts' (
> 'id' INTEGER PRIMARY KEY NOT NULL,
> 'day' varchar(10) default '',
> 'month' int(2) NOT NULL default '0',
> 'date' int(2) NOT NULL default '0',
> 'year' int(4) NOT NULL default '0',
> 'category' int(1) NOT NULL default '1',
> 'showpref' tinyint(1) default '1',
> 'body' mediumtext NOT NULL,
> 'title' varchar(100) default '',
> 'number_of_comments' int(11) NOT NULL default '0', )
> TYPE=MyISAM;";
>
> DB_query($cmd, $db);
>
>
> $cmd =
> "INSERT INTO 'wheatblog_categories' (id, category)
> values (null, 'unfiled');";
>
> DB_query($cmd, $db);
>
>}
>
>
> It appears that the error is a "syntax error" near where my arrow
> (<--) is. Can someone throw some charity my way? Is there
> anything obviously
> wrong here?
>
> Thanks,
> Pete
you might want to make changes along the lines of:
CREATE TABLE `wheatblog_categories` (
`id` int(11) NOT NULL auto_increment,
`category` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`)
)
to
CREATE TABLE wheatblog_categories (
id int(11) NOT NULL auto_increment,
category varchar(30) NOT NULL default '',
PRIMARY KEY (id)
)
reid
