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

Announce and Discuss the Latest Theme and Extension Releases.
Post Reply
husayt
Posts: 6
Joined: December 3rd, 2007, 7:25 am

Post by husayt »

Thanks for asking our opinion.

what i think would be best, if the all the dafult values would be <null>. Empty string '' doesn't mean anything for dateTime or integer columns. So default value for these types should DEFINETELY be <null>.

Another aspect in favour of this solution is that you could specify default value for Null values on creation of table. For Example
CREATE TABLE 'Category'
("CategoryId" INTEGER PRIMARY KEY NOT NULL ,
'Name' varchar(20) NOT NULL DEFAULT '',
'ParentCategoryId' INTEGER NOT NULL DEFAULT '15')

This way user could decide individually for each column the default behavior. So this way whenever the value is entered as <null> sqlite would provide default values, i.e. empty string '' for 'Name' column and 15 for 'ParentCategoryId'.

This argument actually suggests that by default all values to be suggested as <null>. Then if user wants an empty string instead, he would delete '<null>' making the value empty string.

This will also be in line to default database bahavior, as sql assumes the value to be null if it is not explicitly entered.
So if you do insert command and only supply values for some of the columns, then the rest would get either null or default values suggested on creation of table.
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Post by mrinal.kant »

husayt wrote:... whenever the value is entered as <null> sqlite would provide default values, i.e. empty string '' for 'Name' column and 15 for 'ParentCategoryId'.

This argument actually suggests that by default all values to be suggested as <null>. Then if user wants an empty string instead, he would delete '<null>' making the value empty string.

Does that mean that the input box should actually display the string <null> which would be understood as null internally by the tool?

Looks like a good suggestion. This would have the following consequences:
1. create table: "default value" dropdown box would be shown as <null> which stands for null. The user can change it, or completely delete it for an empty string.
2. add new record: the dropdown box for the field values would, by default, display <null> and the behaviour when <null> is encountered as a value would be as follows - (a) autoincrement and use default values where possible (b) otherwise, treat it as null.
3. search in table/view: as of now, columns for which no value is input are ignored. Show an explicit <ignore> in the value or operator field by default so that search can be done on empty string too. To search for null, use <null> (possibly, from the dropdown options)

Does that look OK? I mean, is this what you mean? To me, it surely appears better than the current state of affairs.

Of course, there would be the problem of how to input a string "<null>" in a column? That is, how to distinguish <null> from "<null>"?
The same kind of problem occurs for other constants too. That is, how to distinguish a constant from a string (current_date vs. "current_date") that equals the name of the constant?

Any suggestion for that? Perhaps, by allowing for special delimiters around constants (including null) and by allowing the user to specify the delimiter under preferences, this problem could be solved. That is, when the user thinks that a string "<null>" has to be entered, he/she could change the angular brackets to something else temporarily.
husayt
Posts: 6
Joined: December 3rd, 2007, 7:25 am

Post by husayt »

1. yes
2. yes
3. yes

as for <null> vs "<null>".
Yes, I agree with you suggestion. Just want to stress that by default all the constants and commands should be treated as they are, unless they are surrounded by some delimeters.

Looking forward for this.
Thanks.
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Post by mrinal.kant »

There were too many assumptions regarding the inputs in default value field while creating table/adding columns. Similarly, for inputs while adding/editinf/searching records. The assumptions resulted in inability to use certain constants and the strings from the dialog. The problem has been fixed by making no assumptions on the inputs. They are treates as they are provided by the user. For details, read the following passage.

Version 0.2.9.6 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.6:
Changes:
- create table, add column : default values for strings must now be provided by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two consecutive single quotes. However, when using double quotes to enclose, do not encode the internal single quotes as prescribed in the previous statement. This allows to distinguish the number 56 from the string '56' which was earlier not possible. Also, one may use the constant current_date or the string 'current_date' by explicitly using the single quotes. This is in greater conformance with the sqlite syntax.
- add new record : null is shown by default as the value. If it is left unchanged, fields will be autoincremented or non-null default values will be used wherever possible. Strings will now have to be explicitly enclosed in single quotes as in create table described above. No assumptions are made by the code about the type of input value.
- edit record : existing string values are shown with quotes, etc. as described above. The user is expected to input exact values as in sql. No assumptions are made by the code about the type of input value.
- search in table/view: columns where no values are input are ignored. In case of "contains", "begins with", "ends with" no enclosing quotes are necessary and any single quote as part of search need not be encoded. However, for other operators strings should be specified as mentioned in the above points. This enables the user to search for empty strings too which was earlier not possible.
- fixed a bug in the preferences handling.

The complete changelog can be seen at https://addons.mozilla.org/en-US/firefo ... sions/5817
Eric.Armstrong
Posts: 1
Joined: December 7th, 2007, 11:39 am

minor bug

Post by Eric.Armstrong »

After clicking on a header to sort a table I get:

Error: treeView is not defined
Source File: chrome://sqlitemanager/content/sqlitemanager.js
Line: 1623

It is not a big bug as everything that needs to be done is all ready done when the exception in thrown, but the code around there looks like it needs a clean up.

-Eric
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Re: minor bug

Post by mrinal.kant »

Eric.Armstrong wrote:After clicking on a header to sort a table I get:

Error: treeView is not defined
Source File: chrome://sqlitemanager/content/sqlitemanager.js
Line: 1623

It is not a big bug as everything that needs to be done is all ready done when the exception in thrown, but the code around there looks like it needs a clean up.

-Eric


Thanks Eric.

Corrected. You will find the correction in the next version which I am testing on Firefox 3.0b1

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

Version 0.2.9.9 released

Post by mrinal.kant »

Version 0.2.9.9 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.9:
Changes:
- XML import and export is supported.
- XML import feature can import multiple tables at the same time.
- XML format is identical to that made by phpMyAdmin 2.10.* plus a type attribute described below.
- Additionally, you can export the type as an attribute for each field. So that when importing, SQLite Manager can distinguish strings, integers, nulls, etc. from each other.
- because of issues with E4X, the extension assumes that the xml PI (<xml>) will be the first line in the file to import. If not, the file needs to be manually corrected.
- also, comments in the exported xml should not contain "--". Pl. see http://www.w3.org/TR/REC-xml/#sec-comments . I mention it here because xml export from phpMyAdmin 2.10.1 makes that error. If you edit the exported file to remove the "--" within the comment's content, the extension imports as expected.

The complete changelog can be seen at https://addons.mozilla.org/en-US/firefo ... sions/5817
CharlieCL
Posts: 3
Joined: December 10th, 2007, 4:47 pm

Post by CharlieCL »

How to open a sqlite file on a web server through a http address?
User avatar
Philip Chee
Posts: 6475
Joined: March 1st, 2005, 3:03 pm
Contact:

Post by Philip Chee »

CharlieCL wrote:How to open a sqlite file on a web server through a http address?
You don't.

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

Version 0.2.10 released

Post by mrinal.kant »

Two users of this extension had very strong words to tell me regarding the recent changes in this extension. Strong, but well-reasoned. Unfortunately, both have chosen not to be quoted on this forum. So, all I will say is that they had very convincing arguments in favour of keeping data entry for columns and default values simple; even if it meant taking away the flexibility that was provided to the user by letting him/her specify the exact value without the tool making any assumptions.

As a result of these arguments, I have released the version 0.2.10 which now supports data entry in 2 modes: the simple mode which existed upto version 0.2.9.6 and the explicit mode where the extension would make no assumptions but simply use the data entered by the user in the sql. There is a preference for changing the mode. The simpler mode is the default one.

More below.

Version 0.2.10 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.10:
Changes:
- support for 2 modes of data entry which alters the way data entered in the following fields is interpreted:
1. input value in add record dialog
2. input value in edit record dialog
3. default value field corresponding to add column button
4. default value fields in create table dialog

The 2 modes of data entry are
1. explicit mode: users have to enter the data exactly as they want the default value or the values in tables. The user is responsible for using quotes, constants, etc. correctly. This mode is necessary for flexibility in allowing all kinds of data entry.
2. implicit mode: that is, if the input field is empty or has "null" as the value, the value is interpreted as null. In all other cases the value is enclosed in single quotes and any single quotes in the string are properly escaped. This was the mode originally built into this extension. It is easy for the layman and saves quite a few keystrokes.

The implicit mode is the default mode. To change the mode, go to menu General -> Options -> Main -> "All entries in add/edit records and default columns will be implicitly quoted" checkbox.

The complete changelog can be seen at https://addons.mozilla.org/en-US/firefo ... sions/5817
blacknick
Posts: 1
Joined: December 15th, 2007, 5:18 am
Location: Ukraine

code page

Post by blacknick »

1. can you add cp1251 support for database entries?
2. I suggest that "import data from text file" option will be very useful :)

and thank you for extension. it is great for sqlite db managing :)
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Re: code page

Post by mrinal.kant »

blacknick wrote:1. can you add cp1251 support for database entries?
2. I suggest that "import data from text file" option will be very useful :)

and thank you for extension. it is great for sqlite db managing :)


import data from csv format is now supported in the latest version 0.2.11.
Version 0.2.11 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.11:
New:
- import from csv supported
- the extension remembers the last selected table/view/etc. and also the last selected tab (structure/browse&search/etc.) from your previous session when you restart the extension

The complete changelog can be seen at https://addons.mozilla.org/en-US/firefo ... sions/5817
jmcm
Posts: 1
Joined: December 18th, 2007, 6:47 am
Location: Australia

Post by jmcm »

Edit Selected Record in Browse and Search tab.

I have a number of tables where the first column is not unique - eg. "create table t (a,b,c, primary key (a,b))". The edit record facility generates an update statement like "update t set a = 'a', b = 'b', c = 'c' where a = 'a'". This would in this case cause multiple records to be updated when I only one updated.

As an alternative I would suggest that the update statement be referenced to the table rowid and not the first column eg. "update t set a = 'a', b = 'b', c = 'c' where rowid = 'rowid'".
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Post by mrinal.kant »

jmcm wrote:Edit Selected Record in Browse and Search tab.

I have a number of tables where the first column is not unique - eg. "create table t (a,b,c, primary key (a,b))". The edit record facility generates an update statement like "update t set a = 'a', b = 'b', c = 'c' where a = 'a'". This would in this case cause multiple records to be updated when I only one updated.

As an alternative I would suggest that the update statement be referenced to the table rowid and not the first column eg. "update t set a = 'a', b = 'b', c = 'c' where rowid = 'rowid'".


That was a big error. Of course, criteria should be rowid in such cases. Will fix soon. Thanx.
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Post by mrinal.kant »

jmcm wrote:Edit Selected Record in Browse and Search tab.

I have a number of tables where the first column is not unique - eg. "create table t (a,b,c, primary key (a,b))". The edit record facility generates an update statement like "update t set a = 'a', b = 'b', c = 'c' where a = 'a'". This would in this case cause multiple records to be updated when I only one updated.

As an alternative I would suggest that the update statement be referenced to the table rowid and not the first column eg. "update t set a = 'a', b = 'b', c = 'c' where rowid = 'rowid'".


Thanks for your interest and help. The bug has been fixed in version 0.2.12
Download from songbirdnest.com: http://addons.songbirdnest.com/extensions/detail/89
Download from AMO: https://addons.mozilla.org/en-US/firefox/addon/5817 (will be available after review by an editor which should not take too long)

In version 0.2.12:
Bug Fixed:
- In the "Edit Selected Record": If a table had primary key set on multiple columns, one of these columns (say, colx) being of type integer, then the update statement used to edit was of the form "update ... where colx = ...". This was a bug that could cause updation of many records when only one was intended. Fixed.

Complete changelog: https://addons.mozilla.org/en-US/firefo ... sions/5817
Homepage: http://sqlitemanager.mozdev.org/
FAQs: http://sqlitemanager.mozdev.org/faq.html
Post Reply