-
Notifications
You must be signed in to change notification settings - Fork 129
Frequently Asked Questions
A very lightweight firefox extension (originally) to manage all your sqlite databases.
The list includes Mozilla Firefox, Mozilla Thunderbird, Seamonkey, Songbird, Komodo IDE and Komodo Edit. Complete details of the applications (along with versions) which are supported are available [SupportedApplications].
In Firefox 3, Songbird and Flock, you will find a menuitem called "SQLite Manager" under the Tools menu. Clicking it will start the manager.
In Firefox 4, if the menu bar is not hidden, you can still access SQLite Manager from the Tools menu as described above. However, if the menu bar is hidden, SQLite Manager can be accessed from the Web Developer menu under the Firefox menu on top left.
You can also add an icon for SQLite Manager to your toolbar by customizing it.
If you want to connect to a database which is in the profile directory of firefox (or the target application you are using), you can use the dropdown menu on top right in the menubar. All files with the extension that you have specified in the preferences are shown in this dropdown menu. The default extension is "sqlite".
To connect to an sqlite db file from any other directory, use the "Connect Database" option from the menu or the toolbar.
Go to *Tools* menu - *Options* - *Main* - "*Default file extension for SQLite files*" textbox. Here you can set the extension to whatever you want. You can also type multiple extensions separated by commas. For example, typing *sqlite,db* will display all files ending with .sqlite and .db in the dropdown boxes. When you exit the options dialog, the profile database dropdown list would have been properly re-populated.
If you get an error, an alert shows the exception name: NS_ERROR_FILE_CORRUPTED. This could be due to (a) the file you are opening is not an sqlite db file, or (b) you are trying to open an sqlite db file created with an older library of sqlite.
To convert from sqlite2 to sqlite3, please follow the instructions at Convert_sqlite2_to_sqlite3_format
This extension does not install sqlite library. It uses the sqlite library that is a part of the target application (firefox, sunbird, xulrunner, etc.). The version is shown in the statusbar on the left. For example, in Firefox 3.5.7, the version would be displayed as *SQLite 3.6.16.1*. To see exactly how it looks (and also other info shown in statusbar), see the screenshot titled The Status Bar (version 0.3.11) on ScreenShots page.
This is explained in the screenshot titled The Status Bar (version 0.3.11) on ScreenShots page.
SQLite has views. You can create a view with the desired sql statement as under: CREATE VIEW view-name AS select-statement After the view has been created it will be shown in the tree on the left.
Also, you can enable to save the successfully executed queries in execute sql tab by enabling table for extension data under General menu. That will bring up images for previous, next sql and for clearing all sqls in the history.
This feature is under implementation.
When you enter these constants in the add record dialog, edit record dialog, etc. they are interpreted as constants whereas all other entries are treated as strings. If you leave the textbox blank, it is treated as an empty string (), not as null.
* BLOB values are not shown in the browse tab. Instead, a text "BLOB" is displayed alongwith size of BLOB data. * Or, you can set a size limit so that BLOB data upto that size should be converted to string and displayed. * You can disable the display of size using options dialog to increase the speed of queries. * TODO: list the preferences and what they do
* In the Edit/Add Record dialog, you can add BLOB data by selecting any file; you can save BLOB data to a file and you can remove or change any BLOB data. * These features are accessed using the image buttons next to every input field
Summary of all the changes made for handling attached database:
# Database menu has menuitems: "Attach Database" and "Detach Database" # Attached db are shown in a menulist on the left (above the db objects tree) where there used to be the name of the opened database # Right clicking on the menu also provides "Detach Database" in a context menu # Attaching a db adds the dbname and the file leafname to the menu; detaching removes the corresponding item after confirmation from the user. The full path of the attached db can be seen as a tooltip by hovering the mouse over the menuitem # On selecting an attached db, the corresponding db objects are shown in the tree. # temp db objects are now available by selecting "temp" in the menu. No separate tab for temp objects anymore # removed the master tab; instead, added the master as a node in the structure tree. This node contains the master table sqlite_master (for the main and all attached db) and sqlite_temp_master (for temp) # Handled pragma queries using "pragma dbname.table_info()" syntax. But there is an issue when temp db has a table of the same name as the main db. This will be fixed in sqlite 3.6.2 only (see http://www.sqlite.org/cvstrac/tktview?tn=3320) # fixed rename table, add column and drop column to work for attached db too. # Export dialog: lists the master table alongwith all the tables of the selected main, temp or attached db. Thus you can export a table in attached db too. # Dialogs for create table, create index, create view and create trigger: Now there is a dropdown box for the db list (main, temp and attached db), and wherever relevant the table list gets populated according to the selected db. However, if temporary checkbox is selected, the object is always created in temp db. # Copy Table: major improvements here: (a) no more acrobatics by renaming tables, creating unnecessary tables, etc. Now, we have a simple create table statement to create the new table. (b) It is now possible to copy table from any of the available db (main, temp or attached db) to the same or another db. # Fixed add record, edit record, delete records and search facilty for attached databases.
* This feature is enabled only when you are already connected to a database. * You can export both tables and views from the main or temp or any of the attached databases. * CSV, XML and SQL formats are supported. * Import and export wizards open as a tab after the "Database Info" tab. * Press Cancel or 'x' button on the tabbar to exit the import/export wizard. * When importing, both UTF-8 and UTF-16 character encoded files are supported. The user can specify that the character encoding by checking the appropriate radiobutton. * When importing from CSV file, it is possible to modify the table created by import. * When importing from XML file, multiple tables can be imported at the same time. So the user will be prompted for modifying each new table. When importing from XML file, the user cannot edit the column names. * When importing, data can also be imported into an existing table.
Export can be accessed in these ways:
# from the menu: Table/View -> Export Table/View # Structure tab -> Export button # right click on a table/view in the tree on the left -> Export Table/View in the context menu
Import can be accessed in these ways:
# from the menu: Database -> Import # using the import toolbar button
* available under the Database menu * exports all the CREATE statements
* available under the Database menu * produces the same output as produced by the export for individual tables in sql format as well as the CREATE TABLE statements
* available under the Database menu * same as 'Export All Tables' together with all the CREATE statements for views, indexes and triggers too.
* CSV export: the export is done almost as by docs.google.com and MS Excel. That is, double quotes enclose fields (always or those that contain separator and/or double quotes). To escape the double quotes in the value, they are repeated. * CSV import: can import the de-facto standard csv as described above.
* 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 () 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.
* Exporting to SQL: The user can decide whether the exported SQL statements are enclosed within BEGIN TRANSACTION and COMMIT statements or not. * Import data using SQL statements: There is an option which, if checked, it is assumed that, in the selected file, the first and last statements are BEGIN TRANSACTION and COMMIT respectively. The extension will exclude these and execute all the remaining statements in a single transaction. If there are transaction related statements in between too these should be manually removed from the file before importing. * Import splits the sql statements using semicolon as the delimiter. * Blob data is exported as hex string (thanks to Bigane)
* Rename table: is supported. Can be done using rename button in structure tab or through a menuitem in the menu for the table.
* Add Column: in the structure tab, where columns are shown, add column button is provided (implemented using ALTER TABLE tabname ADD COLUMN ...)
* Drop Column: now you can drop columns from existing tables. The drop column button is available for each column under the structure tab.
* *Warning for Drop Column*: sqlite does not support drop column; so we actually drop the table and again create a table with same name minus the dropped column; therefore, drop column will result in loss of associated indexes, triggers and any schema info that is not available through pragma table_info query. Since most of the tools for sqlite implement this feature even more weakly than this, it was considered alright to implement it as above. For simple cases, there will be no problem at all.
if a db is open within this extension and the db is changed using tools outside this extension, you can refresh the database in one of the following 3 ways:
# refresh toolbar button
# Database -> Refresh menuitem
# Shift + F5
SQLite Manager can be run using XULRunner. [XulrunnerApplication]