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

Announce and Discuss the Latest Theme and Extension Releases.
Post Reply
myotisone
Posts: 12
Joined: January 31st, 2007, 6:55 am

editing create table sql

Post by myotisone »

I'm probably missing the obvious, but should I be able to edit the SQL used to create a new table. And should I be able to link a field in one table to another table.

Thanks,

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

Re: editing create table sql

Post by mrinal.kant »

myotisone wrote:should I be able to edit the SQL used to create a new table

after a table has been created, sqlite allows renaming the table and dropping the column. That is all. Do you mean that you want to edit the sql that is generated using the create table dialog in the extension? As of now, it is not possible. Let me know if u need it.

myotisone wrote:And should I be able to link a field in one table to another table.

let me quote from sqlite.org documentation: "FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers."
Also, create table syntax in sqlite is available at http://www.sqlite.org/lang_createtable.html and the create index syntax at http://www.sqlite.org/lang_createindex.html

Mrinal.
myotisone
Posts: 12
Joined: January 31st, 2007, 6:55 am

Re: editing create table sql

Post by myotisone »

Mrinal

mrinal.kant wrote:
myotisone wrote:should I be able to edit the SQL used to create a new table

after a table has been created, sqlite allows renaming the table and dropping the column. That is all. Do you mean that you want to edit the sql that is generated using the create table dialog in the extension? As of now, it is not possible. Let me know if u need it.

myotisone wrote:And should I be able to link a field in one table to another table.

let me quote from sqlite.org documentation: "FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers."
Also, create table syntax in sqlite is available at http://www.sqlite.org/lang_createtable.html and the create index syntax at http://www.sqlite.org/lang_createindex.html

Mrinal.


Ah Ok, thanks for the help. I don't specifically want to edit the SQL, I was just browsing through an SQLite book and couldn't see how I could replicate the Create table SQL, but its not important.

Thanks for a great extension.

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

Post by mrinal.kant »

Version 0.2.14 has been released.
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.14:
New:
- support for Songbird 0.4
Bug Fixed:
- on starting a session of sqlitemanager with the last used db, if the db file was not found, the extension created a new sqlite db with the same path. Fix: there is an alert that tells the user that the file does not exist anymore.

Complete changelog: https://addons.mozilla.org/en-US/firefo ... sions/5817
Homepage: http://sqlitemanager.mozdev.org/
FAQs: http://sqlitemanager.mozdev.org/faq.html
CanyonCasa
Posts: 2
Joined: January 1st, 2008, 12:44 pm
Location: Albuquerque
Contact:

Features Requests

Post by CanyonCasa »

Thanks for the great extension! I have converted all my databases to SQLite since working with PHP and this is the perfect browser based tool for having "non-db-skilled" web authors perform maintenance updates.

I would like to suggest a few features. (I haven't used it much so excuse me if make suggest something that already exists) REF: version 2.14...

- Default Extension only appears to work with the New Database dialog. It would be nice to have a similar filter list for the Open Database Dialog, such as *.sqlite;*.sdb;*.db3,..., similar to common dialogs.

- It would be nice if the browser would recognize a MIME type for user specified file types such as *.sqlite;*.sdb;*.db3,... and automatically open URLs with SQLite Manager as other MIME applications do. Likewise, file upload or FTP support to return the file to a server would be equally useful to have full integrated web access.

- In the Create Table dialog, the "Data Type" list should include DATETIME, ENUM, and BLOB types. Although SQLite is typeless, it specifically doesn't prohibit such data types. This allows databases created and edited with SQLite to be more portable to other tools.

- In the "Edit Record" dialog dropdown lists it would helpful to add a few other keywords including NULL and EMPTY_STRING (since this is not the same as NULL in database lingo). This would help eliminate the quirks related to EXPLICIT and IMPLICIT modes and not require quoting all fields. Further, in IMPLICIT mode the keyword terms ought to be exempt from quoting. For example, it makes no sense to quote CURRENT_TIME just like NULL is not quoted.

- In the same context it would be helpful to append to each Edit record list an enumerated set of values already defined for each of the fields. For example, a record that includes a "shirtsize" column might have the values SMALL, MEDIUM, LARGE that a user could select from the list for that column rather than having to type it in every time. Laziness is a users idiom.

- In the Execute Query tab it would be nice to have a query history (i.e. buffer) to recall previous (errorless) queries, similar to recalling previously executed shell commands with the arrow keys. Besides being lazy less input reduces human errors.

- In the Execute Query tab the REPLACE command could be added to the "Data Manipulation" list.

- Add a DB Setting to not require all the confirmations.

- BUG: In the Execute Query tab, under PRAGMA, The entry PRAGMA table_info occurs twice and executing a PRAGMA table_info on a table that does not exist does not result in an error.

I'm not a XUL programmer, yet, but seeing the power of this app may change that. If I can help with more examples or beta testing so be it. Again, thanks.
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Re: Features Requests

Post by mrinal.kant »

CanyonCasa wrote:Thanks for the great extension!


Thanks.

CanyonCasa wrote:Default Extension only appears to work with the New Database dialog. It would be nice to have a similar filter list for the Open Database Dialog, such as *.sqlite;*.sdb;*.db3,..., similar to common dialogs.

in version 0.2.15, open database dialog will have *.abc filter shown, where abc is the default sqlite file extension as specified under preferences.

CanyonCasa wrote:It would be nice if the browser would recognize a MIME type for user specified file types such as *.sqlite;*.sdb;*.db3,... and automatically open URLs with SQLite Manager as other MIME applications do. Likewise, file upload or FTP support to return the file to a server would be equally useful to have full integrated web access.

will require considerable effort on my part. yet to consider doing this.

CanyonCasa wrote:In the Create Table dialog, the "Data Type" list should include DATETIME, ENUM, and BLOB types.

added DATETIME and BLOB types in version 0.2.15. For ENUM, read below.

CanyonCasa wrote:In the same context it would be helpful to append to each Edit record list an enumerated set of values already defined for each of the fields. For example, a record that includes a "shirtsize" column might have the values SMALL, MEDIUM, LARGE that a user could select from the list for that column rather than having to type it in every time. Laziness is a users idiom.

AFAIK, there is no ENUM type in sqlite. How do you propose your suggestion should be implemented?

CanyonCasa wrote:In the "Edit Record" dialog dropdown lists it would helpful to add a few other keywords including NULL and EMPTY_STRING (since this is not the same as NULL in database lingo). This would help eliminate the quirks related to EXPLICIT and IMPLICIT modes and not require quoting all fields. Further, in IMPLICIT mode the keyword terms ought to be exempt from quoting. For example, it makes no sense to quote CURRENT_TIME just like NULL is not quoted.

I will comment on this in another post.

CanyonCasa wrote:In the Execute Query tab it would be nice to have a query history (i.e. buffer) to recall previous (errorless) queries, similar to recalling previously executed shell commands with the arrow keys. Besides being lazy less input reduces human errors.

Great idea! should the recall be restricted to the current session or should it be across sessions too?

CanyonCasa wrote:In the Execute Query tab the REPLACE command could be added to the "Data Manipulation" list.

added in version 0.2.15.

CanyonCasa wrote:Add a DB Setting to not require all the confirmations.

please give examples of the confirmations not already covered under preferences (general menu -> options)

CanyonCasa wrote:BUG: In the Execute Query tab, under PRAGMA, The entry PRAGMA table_info occurs twice and executing a PRAGMA table_info on a table that does not exist does not result in an error.

Duplicate menu item has been removed in version 0.2.15. As for the error, the sqlite library returns no error, so the extension reports none.

Thanks for your valuable feedback.
gene.thomas
Posts: 1
Joined: January 2nd, 2008, 5:15 pm

Problems getting SQLite Manager to import with CSV files

Post by gene.thomas »

Hello,
I have just installed and tried using this extension in FireFox. When I try to import using a CSV file it fails. I tried both 2.14 and 2.15 of the extension. It appears that the extension does not handle CSV files properly or as other programs do. I have some values replaced by a ",". No matter what I do, I am unable to process a CSV file for importing data into a new SQLite DB using the extension under FireFox.

I can give you a test CSV file and screen shots of the process I use and the errors I am getting. Let me know how you want me to get these to you.

Regards,

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

Re: Problems getting SQLite Manager to import with CSV files

Post by mrinal.kant »

gene.thomas wrote:Hello,
I have just installed and tried using this extension in FireFox. When I try to import using a CSV file it fails. I tried both 2.14 and 2.15 of the extension. It appears that the extension does not handle CSV files properly or as other programs do. I have some values replaced by a ",". No matter what I do, I am unable to process a CSV file for importing data into a new SQLite DB using the extension under FireFox.

I can give you a test CSV file and screen shots of the process I use and the errors I am getting. Let me know how you want me to get these to you.

Regards,

Gene


pl email the problematic csv file to me.
Pijoter
Posts: 5
Joined: January 4th, 2008, 2:33 am

Browse and Search

Post by Pijoter »

This isn't bug but is really hard to discover column names and row data when there is (too) many columns in single table (in Browse & Search page). Since all data must fit in the window area - every column width is reduced to few pixels. Maybe a scrollable window will remove this disability?

http://images.rowery.olsztyn.pl/rera.png

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

Re: Browse and Search

Post by mrinal.kant »

Pijoter wrote:This isn't bug but is really hard to discover column names and row data when there is (too) many columns in single table (in Browse & Search page). Since all data must fit in the window area - every column width is reduced to few pixels. Maybe a scrollable window will remove this disability?

http://images.rowery.olsztyn.pl/rera.png

Pijoter


this is a known problem which I had attempted to solve by providing horizontal scroll but some other issues had got introduced. I will explore that solution again.

However, I was hoping (you may think it funny) that nobody would come across this problem until firefox 3 is launched where the support for horizontal scroll is inbuilt.
Pijoter
Posts: 5
Joined: January 4th, 2008, 2:33 am

Post by Pijoter »

Well, as I said before - this isnt a bug. Only minor disadavantage I can live with because SQLite manager has many cool things. If you could make browse window scrollable - will be great. If not I'll wait till FF3 be stable enough. Thank you for this superb plugin.

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

Re: Problems getting SQLite Manager to import with CSV files

Post by mrinal.kant »

gene.thomas wrote:Hello,
It appears that the extension does not handle CSV files properly or as other programs do. I have some values replaced by a ",". No matter what I do, I am unable to process a CSV file for importing data into a new SQLite DB using the extension under FireFox.

I can give you a test CSV file and screen shots of the process I use and the errors I am getting. Let me know how you want me to get these to you.

Regards,

Gene


Thanks for giving the sample csv file.

Version 0.2.16 has been released.
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.16:
Bugs fixed:
- Import: if import failed, the message was "-1 records imported". Changed that to "Import failed".
- Import CSV file: fixed an error to correctly import rows like
'2','Lname2','Fname2', (note the , at the end)
'4','Lname4',,'555-5554' (note the ,,)

Please note that Import CSV feature in this version is better than before, but not perfect.
(thanks to gene.thomas for reporting these bugs)

Complete changelog: https://addons.mozilla.org/en-US/firefo ... sions/5817
Homepage: http://sqlitemanager.mozdev.org/
FAQs: http://sqlitemanager.mozdev.org/faq.html
Pijoter
Posts: 5
Joined: January 4th, 2008, 2:33 am

Post by Pijoter »

Pijoter wrote:this isnt a bug. Only minor disadavantage

I run into troubles. I cant update records (via browse page/edit selected) because there is no place for such big window (too many columns in table and too many rows in edit window). Maybe this windows should also be "scrollable" (vertical)?
I think about small improvement. When I type SQL (execute SQL page) there is no immediate hints about column names. Maybe column names should be displayed below table name at left side of window?

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

Post by mrinal.kant »

Pijoter wrote:I cant update records (via browse page/edit selected) because there is no place for such big window (too many columns in table and too many rows in edit window). Maybe this windows should also be "scrollable" (vertical)?

yes, it should be; just wait for version 0.2.17

Pijoter wrote:When I type SQL (execute SQL page) there is no immediate hints about column names. Maybe column names should be displayed below table name at left side of window?

worth attempting, i feel. Let me examine this.
mrinal.kant
Posts: 81
Joined: June 28th, 2005, 1:38 pm

Post by mrinal.kant »

Pijoter wrote:I cant update records (via browse page/edit selected) because there is no place for such big window (too many columns in table and too many rows in edit window). Maybe this windows should also be "scrollable" (vertical)?


Version 0.2.17 has been released.
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.17:
Bugs fixed:
- Edit Record Dialog/Search dialog: all columns are not shown if there are too many columns in table. Fix: these dialog windows are now vertically scrollable.
(thanks to Pijoter for reporting these bugs)

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