Serendipity Forums

User and developer community
https://board.s9y.org/

some plugins seems have problem with SQLite?

https://board.s9y.org/viewtopic.php?t=3121

Page 2 of 2

Posted: Fri Oct 21, 2005 8:59 am
by Guest
Forgot one thing: serendipity-20051019
(I also tried php-5.0.5)

Top messages of the Backup Interface:
Warning: sqlite_query() [function.sqlite-query]: no such table: serendipity_dma_sqlbackup in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(39) "SELECT * FROM serendipity_dma_sqlbackup"
Warning: Invalid argument supplied for foreach() in /var/www/html/s9y/plugins/serendipity_event_backup/serendipity_event_backup.php on line 1068

Warning: sqlite_query() [function.sqlite-query]: near "SHOW": syntax error in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(11) "SHOW TABLES"
Warning: Invalid argument supplied for foreach() in /var/www/html/s9y/plugins/serendipity_event_backup/serendipity_event_backup.php on line 1091

Warning: sqlite_query() [function.sqlite-query]: no such table: serendipity_dma_htmlbackup in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(40) "SELECT * FROM serendipity_dma_htmlbackup"
Warning: Invalid argument supplied for foreach() in /var/www/html/s9y/plugins/serendipity_event_backup/serendipity_event_backup.php on line 1216

Backup Interface->SQL backup(Structure and data)
the backup file always only 1024 bytes(2005-10-21-xx-xx_sqlbackup.tar)


Click DownloadManager:
Warning: sqlite_query() [function.sqlite-query]: no such column: node1.hidden in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(746) " SELECT node1.node_id AS node_id, node1.root_id AS root_id, node1.payload AS payload, node1.lft AS lft, node1.rgt AS rgt, node1.hidden AS hidden, round((node1.rgt-node1.lft-1)/2,0) AS subcats, ((min(node2.rgt)-node1.rgt-(node1.lft>1))/2) > 0 AS lower, (( (node1.lft-max(node2.lft)>1) )) AS upper, COUNT(*) AS level FROM serendipity_dma_downloadmanager_categories AS node1, serendipity_dma_downloadmanager_categories AS node2 WHERE node1.lft BETWEEN node2.lft AND node2.rgt AND (node2.root_id = node1.root_id) AND (node2.node_id != node1.node_id OR node1.lft = 1) AND (node2.root_id = 1) GROUP BY node1.lft, node1.rgt, node1.node_id, node1.root_id, node1.payload "
Warning: Invalid argument supplied for foreach() in /var/www/html/s9y/plugins/serendipity_event_downloadmanager/serendipity_event_downloadmanager.php on line 1791

Downloadmanager
Provides full downloadmanager capabilities to your s9y. When uninstalling, all related tables will be dropped! Visit http://blog.linux-stats.org for the downloadable plugin.

Add new category
Category name: _____ Sub-category of: _____

Posted: Fri Oct 21, 2005 10:45 am
by garvinhicking
As I mentioned, the backup plugin sadly does not work with SQLite, as it does not impelemt a "SHOW TABLES" method. But with SQLite you can easily backup your whole s9y by just packign the directory, as the database is inside there as a plain file.

The Downloadmanager SQL query seems to have a problem with the table; can you check if the mentioned "hidden" column exists for your serendipity_dma_downloadmanager_categories table?

Best regards,
Garvin

Posted: Fri Oct 21, 2005 1:08 pm
by Guest
/*
* wtcSQLite 1.1.0 Export
* Database: /var/www/html/wtcSQLite/data/s9y.db
* PHP Version: 5.1.0RC1
* SQLite Version: 2.8.16
*/


/*
* Structure for serendipity_dma_downloadmanager_categories
*/
CREATE TABLE serendipity_dma_downloadmanager_categories (
node_id INTEGER PRIMARY KEY,
root_id int(10) NOT NULL default '0',
payload varchar(64),
lft int(10) NOT NULL default '0',
rgt int(10) NOT NULL default '0'
);

no hidden column, why?

Posted: Fri Oct 21, 2005 1:21 pm
by Guest
I checked the serendipity_event_downloadmanager.php file in line 355,
create table only these columns:

node_id INTEGER PRIMARY KEY,
root_id int(10) NOT NULL default '0',
payload varchar(64),
lft int(10) NOT NULL default '0',
rgt int(10) NOT NULL default '0'

:?:

Posted: Fri Oct 21, 2005 1:25 pm
by garvinhicking
Ah, this is because the downloadmanager plugin adds columns afterwards by the upgrade process.

Sadly, SQLite does not support ALTER TABLE, which is why the plugin fails to bring the DB structure up to the wanted feature list.

Well, this missing ALTER TABLE for SQLite is really problem, as it needs to be emulated pretty bad:

Code: Select all

create table {PREFIX}tempplugins (
  name varchar(128) not null,
  placement varchar(6) not null default 'right',
  sort_order int(4) not null default '0',
  authorid int(11) default '0',
  PRIMARY KEY(name)
);

INSERT INTO {PREFIX}tempplugins (name, placement, sort_order, authorid) SELECT name, placement, sort_order, authorid FROM {PREFIX}plugins;
DROP TABLE {PREFIX}plugins;

create table {PREFIX}plugins (
  name varchar(128) not null,
  placement varchar(6) not null default 'right',
  sort_order int(4) not null default '0',
  authorid int(11) default '0',
  path varchar(255) default null,
  PRIMARY KEY(name)
);

INSERT INTO {PREFIX}plugins (name, placement, sort_order, authorid) SELECT name, placement, sort_order, authorid FROM {PREFIX}tempplugins;
DROP TABLE {PREFIX}tempplugins;

This is the emulation code for what can be done in MySQL/POstgreSQL with this:

Code: Select all

ALTER TABLE {PREFIX}plugins ADD COLUMN path varchar(255) default null
Pretty, eh? :-D

I've notified the author of the downloadmanager and forum plugins so he might be able to fix the table creation code to work instantly in SQLite instead of requiring ALTER TABLE.

Regards
Garvin

Posted: Fri Oct 21, 2005 1:28 pm
by dma147
That's a really dirty and ugly solution for the missing ALTER TABLE in SQLite... :(

Well, thanks for the note. I'll change that for the next versions. ;)

Posted: Fri Oct 21, 2005 6:44 pm
by Guest
wtcSQLite Admin Tool:

ALTER TABLE Documentation

Unfortunately, SQLite does not yet support the 'ALTER TABLE' syntax like many of the other popular databasing systems. However, fortunately for you, wtcSQLite will take the 'ALTER TABLE' syntax as a query anywhere in wtcSQLite, and do the all the dirty work for you. However, there are a few restrictions:

* You may only run one ALTER TABLE query in one transaction- as where with other queries, you can run many in one transaction.
* Do not try to add or delete indexes with the ALTER TABLE query. Instead use the 'CREATE INDEX' and 'DROP INDEX' statements.
* You may only use parathesis to add multiple fields at once. If you are changing or dropping many fields, then you will need to separate each change/drop statement with a comma.

Syntax

ALTER TABLE tbl_name ADD|CHANGE|DROP create table syntax [,]*
Examples that will work

* ALTER TABLE tbl_name ADD (col_name1, col_name2 VARCHAR (255), col_name3 INTEGER PRIMARY KEY), DROP col_name4, CHANGE col_name5 col_name6 INT
* ALTER TABLE tbl_name ADD col_name1, DROP col_name2, DROP col_name3, ADD col_name4
* ALTER TABLE tbl_name CHANGE col_name4 col_name0 INTEGER PRIMARY KEY

Example that won't work

* ALTER TABLE tbl_name ADD INDEX(col_name3) - Add and drop indexes with 'CREATE INDEX' and 'DROP INDEX' statements
* ALTER TABLE tbl_name DROP (col_name1, col_name2, col_name3) - Should be: DROP col_name1, DROP col_name2, DROP col_name3
* ALTER TABLE tbl_name CHANGE (col_name0 col_name1 VARCHAR (255), col_name3 col_name3 INTEGER PRIMARY KEY) - Only 'ADD' statement can use parathesis

Does these help?

Posted: Mon Oct 24, 2005 11:43 am
by garvinhicking
Thanks for forwarding this to us, however the code I pasted above is basically the same emulation.

The mentioned plugins have been fixed so that ALTER TABLE is no longer necessary when installing the plugin from fresh. However backup still won't work because the backup plugin makes use of mysql-specific SQL commands... (SHOW CREATE TABLE and stuff like that)


Regards,
Garvin

Posted: Mon Oct 24, 2005 2:33 pm
by dma147
FIXED downloadmanager and forum plugins.

Both new versions should be available through spartacus within the next 24 hours. I've not created a package for manually downloading yet.

Posted: Mon Oct 24, 2005 8:49 pm
by Guest
Great! I'll try these plugins in the next 24 hours.
Thanks!

Posted: Wed Oct 26, 2005 3:43 pm
by Guest
My testing box:
Mandriva 2006 cooker(2006.1)
apache-2.0.55-2mdk
php-5.1.0-0.RC1.1mdk
s9y-20051025

DownloadManager plugin v. 0.9:
When I add a category, the top of messages:

Warning: sqlite_query() [function.sqlite-query]: near "LOCK": syntax error in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(60) "LOCK TABLES serendipity_dma_downloadmanager_categories WRITE"
Warning: sqlite_query() [function.sqlite-query]: near "UNLOCK": syntax error in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(13) "UNLOCK TABLES"

Forum plugin v. 0.7:
The process of installation completed, then show the messages in the left side of Administration:

Warning: sqlite_query() [function.sqlite-query]: near "SHOW": syntax error in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(11) "SHOW TABLES"
Warning: Invalid argument supplied for foreach() in /var/www/html/s9y/plugins/serendipity_event_forum/serendipity_event_forum.php on line 408

Warning: sqlite_query() [function.sqlite-query]: index postid already exists in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(62) "CREATE INDEX postid ON serendipity_dma_forum_uploads (postid);"
Warning: sqlite_query() [function.sqlite-query]: near "SHOW": syntax error in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(47) "SHOW COLUMNS FROM serendipity_dma_forum_uploads"
Warning: Invalid argument supplied for foreach() in /var/www/html/s9y/plugins/serendipity_event_forum/serendipity_event_forum.php on line 459

Warning: sqlite_query() [function.sqlite-query]: near "SHOW": syntax error in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(47) "SHOW COLUMNS FROM serendipity_dma_forum_threads"
Warning: Invalid argument supplied for foreach() in /var/www/html/s9y/plugins/serendipity_event_forum/serendipity_event_forum.php on line 471

Warning: sqlite_query() [function.sqlite-query]: no such column: authorid in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(301) "INSERT INTO serendipity_dma_forum_uploads (postid, uploadid, authorid, uploaddate, sysfilename, realfilename, dlcount) SELECT postid, uploadid, authorid, uploaddate, sysfilename, realfilename, dlcount FROM serendipity_dma_forum_uploads_tmp;"
Warning: sqlite_query() [function.sqlite-query]: index authorid already exists in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(66) "CREATE INDEX authorid ON serendipity_dma_forum_uploads (authorid);"
Warning: sqlite_query() [function.sqlite-query]: no such column: filesize in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(321) "INSERT INTO serendipity_dma_forum_uploads (postid, uploadid, authorid, uploaddate, filesize, sysfilename, realfilename, dlcount) SELECT postid, uploadid, authorid, uploaddate, filesize, sysfilename, realfilename, dlcount FROM serendipity_dma_forum_uploads_tmp;"
Warning: sqlite_query() [function.sqlite-query]: no such column: announce in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(403) "INSERT INTO serendipity_dma_forum_threads (boardid, threadid, title, replies, views, flag, announce, notifymails, lastauthorid, lastauthorname, lastpostid, lastposttime) SELECT boardid, threadid, title, replies, views, flag, announce, notifymails, lastauthorid, lastauthorname, lastpostid, lastposttime FROM serendipity_dma_forum_threads_tmp;"
Warning: sqlite_query() [function.sqlite-query]: near "SHOW": syntax error in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(47) "SHOW COLUMNS FROM serendipity_dma_forum_threads"
Warning: Invalid argument supplied for foreach() in /var/www/html/s9y/plugins/serendipity_event_forum/serendipity_event_forum.php on line 604

Warning: sqlite_query() [function.sqlite-query]: near "ALTER": syntax error in /var/www/html/s9y/include/db/sqlite.inc.php on line 137
bool(false) string(74) "ALTER TABLE serendipity_dma_forum_threads ADD notifymails text AFTER flag;"
All times are UTC+02:00
Page 2 of 2

Powered by phpBB® Forum Software © phpBB Limited