SQLite Manager - Extension for Firefox, Sunbird, Flock, etc.

Announce and Discuss the Latest Theme and Extension Releases.
Post Reply
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Post by mrinal.kant »

version 0.2.9.1 is available at http://addons.songbirdnest.com/extensions/detail/89
It shall be available at https://addons.mozilla.org/en-US/firefox/addon/5817 after review by an editor which should not take too long.

In this version
New:
- there is an preference (option dialog -> main tab) to configure the number of records to display under browse tab. Which means no long waits in case the table/view has too much data. Set it to a negative number to see all records. This value is set to -1 by default.
- browse tab has navigation buttons: First, Previous, Next and Last to browse large tables/views.
- navigation buttons are also available for search results under the browse tab.

Bugs fixed:
- Searching an empty view caused a dialog to pop which had no columns. Now, the user is alerted that there are no records to search.
- repeating exactly the same search on a table/view without any other search in between did not show the result. This was because the search query was executed on change of the where clause. And in the case just described, the where clause did not change, so the query was not fired. Fixed.

The complete changelog can be seen at https://addons.mozilla.org/en-US/firefo ... sions/5817
WorkingDay
Posts: 1
Joined: November 27th, 2007, 12:49 am
Contact:

Bugreport: show data

Post by WorkingDay »

Hi!

You created a really nice tool!

But for some tables I always encounter "Loading data failed" message. If you wish to debug here is my TABLE creation script

====
CREATE TABLE announcements (
-- announcement table; referred to 'users'; '~common' is a special word means shared announcement
-- webpath/filepath == compressed/uncompressed
username TEXT NOT NULL CHECK( username != "" ),
shortname TEXT NOT NULL CHECK( shortname != "" ),
length INTEGER DEFAULT 0 NOT NULL,
uploaded INTEGER DEFAULT 0 NOT NULL,
webpath TEXT,
filepath TEXT,
description TEXT,
PRIMARY KEY ( username, shortname ) );
====

Then you can add single entry.

====
INSERT INTO announcements VALUES('denikin', 'English', 4, 1192794493, 'snd/~common/announcements/defaultAnnouncement_compressed.wav', 'compressed.wav', 'Default message')
====

Adding OK, but unable to see data. Viewing in SQLiteSpy is OK, DB valid.

Thanks and regards,
Dennis
User avatar
shantanuo
Posts: 70
Joined: January 22nd, 2004, 9:31 am
Location: Mumbai
Contact:

Post by shantanuo »

length INTEGER DEFAULT 0 NOT NULL,

length is a Reserved word. Remove or rename the column name to something else like length1
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Re: Bugreport: show data

Post by mrinal.kant »

WorkingDay wrote:Hi!

You created a really nice tool!

But for some tables I always encounter "Loading data failed" message. If you wish to debug here is my TABLE creation script

====
CREATE TABLE announcements (
-- announcement table; referred to 'users'; '~common' is a special word means shared announcement
-- webpath/filepath == compressed/uncompressed
username TEXT NOT NULL CHECK( username != "" ),
shortname TEXT NOT NULL CHECK( shortname != "" ),
length INTEGER DEFAULT 0 NOT NULL,
uploaded INTEGER DEFAULT 0 NOT NULL,
webpath TEXT,
filepath TEXT,
description TEXT,
PRIMARY KEY ( username, shortname ) );
====

Then you can add single entry.

====
INSERT INTO announcements VALUES('denikin', 'English', 4, 1192794493, 'snd/~common/announcements/defaultAnnouncement_compressed.wav', 'compressed.wav', 'Default message')
====

Adding OK, but unable to see data. Viewing in SQLiteSpy is OK, DB valid.

Thanks and regards,
Dennis


Hi Dennis,
Thanks for the properly described bug. And also for your encouraging remarks.

The bug was due to a column called 'length' as already pointed out by shantanu.

The problem has been fixed. Version 0.2.9.2 is available at http://addons.songbirdnest.com/extensions/detail/89
It shall be available at https://addons.mozilla.org/en-US/firefox/addon/5817 after review by an editor which should not take too long.

In version 0.2.9.2:
New:
- some code change to improve performance

Bugs fixed:
- It was possible to create a table with column called 'length' and add a record to it. But the display under browse tab failed. Some other javascript keywords could also cause this problem. Fixed. Thanks to WorkingDay for reporting this bug at forums.mozillazine.org

The complete changelog can be seen at https://addons.mozilla.org/en-US/firefo ... sions/5817
darshanmarathe
Posts: 2
Joined: November 26th, 2007, 2:21 am
Location: India
Contact:

Post by darshanmarathe »

Dear mrinal

OS windows XP SP2
Firefox 2.0.0.10
SqliteManager 0.2.9.2

And its still opening the download window


Regards
Darshan Marathe
Regards
Darshan Marathe
www.ajaxguy.i.am
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Post by mrinal.kant »

darshanmarathe wrote:Dear mrinal

OS windows XP SP2
Firefox 2.0.0.10
SqliteManager 0.2.9.2

And its still opening the download window


Regards
Darshan Marathe


Hi Darshan,

My apologies for not attending to that. But I have not been able to reproduce this problem on any PC I have tried it on. So, I must trouble you a little further. What happens exactly? Does the download window open when you click on export button or export table menuitem? Does it open alongwith the export dialog of sqlitemanager? Or does the download window open at the time you click OK button in the export dialog?

Mrinal.
Ima Freman
Posts: 1
Joined: November 30th, 2007, 7:48 pm

Terminate Firefox in three easy steps or major BUG?

Post by Ima Freman »

'Profile Database:>' Load search.qslite '> GO'
'Enter SQL:>' SELECT name FROM engine_data WHERE name="order" '>Run SQL Statement'
'Enter SQL:>' [clear the dialog box] '>Run SQL Statement'

POOF - normal critical error termination

Firefox Version: 2.0.0.11
SQLite Manager 2.9.2
Windows 2000 sp4
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Re: Terminate Firefox in three easy steps or major BUG?

Post by mrinal.kant »

Ima Freman wrote:'Profile Database:>' Load search.qslite '> GO'
'Enter SQL:>' SELECT name FROM engine_data WHERE name="order" '>Run SQL Statement'
'Enter SQL:>' [clear the dialog box] '>Run SQL Statement'

POOF - normal critical error termination

Firefox Version: 2.0.0.11
SQLite Manager 2.9.2
Windows 2000 sp4


Hi Ima,
Thanks for reporting the bug.

The problem has been fixed. Version 0.2.9.3 is available at http://addons.songbirdnest.com/extensions/detail/89
It shall be available at https://addons.mozilla.org/en-US/firefox/addon/5817 after review by an editor which should not take too long.

In version 0.2.9.3:
Changes:
- a couple of queries required to display info under structure tab were being executed even when that tab was not selected. This slowed the execution. Fixed.
- under the "execute sql" tab, trying to run a statement which contained only spaces, \r, \n and \t caused firefox to crash. Fixed. Thanks to Ima Freman at forums.mozillazine.org for reporting this problem.

The complete changelog can be seen at https://addons.mozilla.org/en-US/firefo ... sions/5817
husayt
Posts: 6
Joined: December 3rd, 2007, 7:25 am

Insert/autoincrement bug

Post by husayt »

First of all thanks for this great plugin. It is actually full featured application, rather than just a plugin.

i have noticed a small, but annoying bug.
In sqlite when you create a column with "integer primary key not null" it means it is autoincremented column. So you don't need values for primary key column when inserting values. Indeed SQLITE inserts work fine in this case. However when i try to insert data using plugin's entry form it reports an error, untill i enter the value for primary key column.

Could you please, investigate this.


Regards
husayt
Posts: 6
Joined: December 3rd, 2007, 7:25 am

Post by husayt »

here is another bug.

Default values entered for column go in as string in quotation marks.

for example, I want to do this using UI

CREATE TABLE times ( id int,
time NOT NULL DEFAULT CURRENT_TIMESTAMP );

i fill in the values and execute. What happens is next

CREATE TABLE times ( id int,
time NOT NULL DEFAULT 'CURRENT_TIMESTAMP' );

It puts quoation marks around CURRENT_TIMESTAMP setting it as a string value rather than command.

I hope I explained the problem clearly enough.
Thanks again.
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Re: Insert/autoincrement bug

Post by mrinal.kant »

husayt wrote:First of all thanks for this great plugin. It is actually full featured application, rather than just a plugin.

i have noticed a small, but annoying bug.
In sqlite when you create a column with "integer primary key not null" it means it is autoincremented column. So you don't need values for primary key column when inserting values. Indeed SQLITE inserts work fine in this case. However when i try to insert data using plugin's entry form it reports an error, untill i enter the value for primary key column.

Could you please, investigate this.


Regards


Hi husayt,
Thanks for reporting the bug.

The problem has been fixed. Version 0.2.9.4 is available at http://addons.songbirdnest.com/extensions/detail/89
It shall be available at https://addons.mozilla.org/en-US/firefox/addon/5817 after review by an editor which should not take too long.

In version 0.2.9.4:
Changes:
- In the Add New Record dialog, the user can now leave a column of type "integer primary key" blank for autoincrementing it. Thanks to husayt at forums.mozilaazine.org for reporting this problem.

The complete changelog can be seen at https://addons.mozilla.org/en-US/firefo ... sions/5817
husayt
Posts: 6
Joined: December 3rd, 2007, 7:25 am

thanks for fix

Post by husayt »

Great. Thank you for fix.
i will appreciate if you could have a look at the second problem I reported, it is no less annoying.


All the best,
husayt
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Post by mrinal.kant »

husayt wrote:here is another bug.

Default values entered for column go in as string in quotation marks.

for example, I want to do this using UI

CREATE TABLE times ( id int,
time NOT NULL DEFAULT CURRENT_TIMESTAMP );

i fill in the values and execute. What happens is next

CREATE TABLE times ( id int,
time NOT NULL DEFAULT 'CURRENT_TIMESTAMP' );

It puts quoation marks around CURRENT_TIMESTAMP setting it as a string value rather than command.

I hope I explained the problem clearly enough.
Thanks again.


Yes husayt, the explanation is very clear. Thanks for that.

The problem has been fixed. Version 0.2.9.5 is available at http://addons.songbirdnest.com/extensions/detail/89
It shall be available at https://addons.mozilla.org/en-US/firefox/addon/5817 after review by an editor which should not take too long.

In version 0.2.9.5:
Changes:
- add record: if a column value is not given, the behaviour is as follows - (a) if column is INTEGER PRIMARY KEY, the value will be autoincremented (b) if the column has a default value, the default value is used (c) otherwise, the value is regarded as an empty string(i.e. '')
- now, you can use constants like CURRENT_TIME, CURRENT_DATE and CURRENT_TIMESTAMP when adding a new record, editing a record and defining default values at the time of creating a table. All by selecting the constant from a dropdown listbox. Thanks to husayt at mozillazine.forums.org for pointing to the limitation that constants were getting enclosed in quotes.
- for searching within a table/view, we now have 2 kinds of = (equality operator): one for strings and the other for numbers, constants, etc. Plus, the dropdown box facility as above to select the constants.
- some changes in the code for handling preferences.

The complete changelog can be seen at https://addons.mozilla.org/en-US/firefo ... sions/5817

However, one consequence of the above changes is that it will not be possible to insert a string "CURRENT_TIME", etc. through the dialogs. Will talk about that in my next post.
husayt
Posts: 6
Joined: December 3rd, 2007, 7:25 am

Post by husayt »

This tool is becoming better and better.
Just another note regarding:

- add record: if a column value is not given, the behaviour is as follows - (a) if column is INTEGER PRIMARY KEY, the value will be autoincremented (b) if the column has a default value, the default value is used (c) otherwise, the value is regarded as an empty string(i.e. '')


I don't think the value should be regarded as empty string.
What if the column data type is date or int???
I think for non-string columns default value should be null.
Empty string in date column breaks type integrity of db.

Thanks again
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Post by mrinal.kant »

husayt wrote:This tool is becoming better and better.
Just another note regarding:

- add record: if a column value is not given, the behaviour is as follows - (a) if column is INTEGER PRIMARY KEY, the value will be autoincremented (b) if the column has a default value, the default value is used (c) otherwise, the value is regarded as an empty string(i.e. '')


I don't think the value should be regarded as empty string.
What if the column data type is date or int???
I think for non-string columns default value should be null.
Empty string in date column breaks type integrity of db.

Thanks again


Yes, when to use/not to use quotes automatically is an issue I am yet to resolve.

The problem: how to tell whether the user input is a string or not? This is obviously important because null != '' and current_date != 'current_date', etc.

One solution is to not assume anything about the user input. That is, if the user intends to input a string then have the user to input the value alongwith the quotes. But it could be a little annoying to key in the quotes too many times.

Another solution is to have a checkbox next to the value field to indicate whether the input is a string or not. It can be checked by default to indicate that the value is to be treated as a string. Thus entering quotes is avoided. But for null, constants, etc. the user will have the additional task of unchecking the checkbox.

Any other ideas? I request the users of this extension to help deciding the best possible way to resolve this issue.

Thanks in advance.
Post Reply