SQLiteDBMS Puppy-DB ?

Under development: PCMCIA, wireless, etc.
Post Reply
Message
Author
Igo AtM
Posts: 25
Joined: Thu 16 Nov 2006, 14:36
Location: UK

SQLiteDBMS Puppy-DB ?

#1 Post by Igo AtM »

I have chosen to open a new topic for SQLiteDBMS to see if there is interest amongst puppy people for my continued work to get this a functioning Puppy solution for web based DB access.
I have linked this from motion/LightMon where it was originally floored as a potential solution to db back-end access to sqlite3 in Puppy. From the feed back I received I have incorporated some changes most of which are evident in the HTML Client Browser. However there are many more in the Server in preparation for the release soon of the new all singing all dancing JavaScript client (the existing one is a js/IE monster but sort of working)
Installation should be no more than a normal uploaded pet install and then in an open terminal type sqlitedbms and you should be guided from there.
Anybody who tested the previous version should upgrade to this one as it is more stable (see known issues below) and offers enhanced functionality.
A quick glimpse of the new HTML client shows some of these additions
The Server now sends XHTML headers so there is a new config option to include style sheets ( css only for now ) Hence the output is formated. I have also included a small scratch pad to one side as a sudo replacement for a script free environment this allows for copy and pasting data from the lower output frame into the main menu/SQL editor. there is also a hint/memo drop down for the Extended SQL at the bottom of the main menu and some measure of click and send using options.
assembling SQL statements work from left to right and top to bottom any visible text will entered into the transmitted request.
The rowid is a useful method of selecting individual rows or groups and is inserted for all tables in all databases by the server (Sqlite master table but thats another issue)

Known problems with this version:
The Server cache prevents proper updating of client window (disabled in default config)
Often the server/client cache will cause update problems when initialising this is due to problems when using send and buffers a solution is on its way for now keep clearing your local cache. This tends to be more of an issue on slower machines
For now DO NOT use 'Explain' in the JavaScript SQL window it is likely to trip the server depending on the version of sqlite that generated the data base
Opera HTML client is O.K but JavaScript client is not working. It is in the new development version so I am not wasting time hacking the old beast
MD5 for SQLiteDBMS is
9e5cc367d493f5af2cf8b12391c52f06 sqlitedbms-beta-0.5.3.pet
Comments however rude will be appreciated
I have included a larger db to play with. It was generated by motionMon and is the main reason for the size increase in this pet but it can be deleted and give the option to test the client with a real database
its a GPL thingy Enjoy
Attachments
html-client.jpg
The new SQLiteDBMS / PUP-DB front end
(27.96 KiB) Downloaded 1703 times

User avatar
BarryK
Puppy Master
Posts: 9392
Joined: Mon 09 May 2005, 09:23
Location: Perth, Western Australia
Contact:

#2 Post by BarryK »

great! I might put it into the next release of Dingo, alpha4, give it a bit more exposure.

I think that the guys who are really into databases may not have seen your recent posts about getting sqlitedbms to work on Puppy, as they may not have logged into the forum for awhile -- it being the festive season or they're otherwise occupied.

Anyway, we'll entice some more testers soon.
[url]https://bkhome.org/news/[/url]

brucehohl
Posts: 58
Joined: Thu 07 Jun 2007, 11:47
Location: Ohio

#3 Post by brucehohl »

Nice work getting SQLiteDBMS working this well on Puppy! In the past I have spent literally hundreds of hours creating queries/look-ups, reports and small traking tools to supplement formal accounting systems using various report writers, MSAccess and Postgres+PHP. IMHO combining SQLite with a light weight dedicated web server is an excellent approach. My comments are from the perspective of one who would like to see SQLiteDBMS become useful as a general data tool like those mentioned above.

HTML client suggestions/wish list:
1- Add text tags for "Submit" and "Clear" buttons.
2- Add tag/button that will clear the SQL input box (or change the current clear functionality so that host and DB are not cleared.
3- Add method to execute the "Submit" and "Clear" buttons from the keyboard (or change the location/tab order of the buttons to be just after the SQL input box).
4- For select query output make the width of the field headers the same as the width of the data columns.
5- SeaMonkey shows and empty box near the top right.

Functionality wish list:
6- Ability to import data from a text file via from the web interface (in lieu of command line "sqlite> .import file table").
7- Ability/option to send sql results to a new browser window instead of the lower frame.
8- Ability to save SQL statements to the database and present these saved SQL statements so that they can be selected/clicked for reuse. (I have already saved some SQL statements in a table with a grouping scheme but I was thinking of something more formal and part of the interface.)
9- Ability to create "parameter" queries, that is, saved queries which when run prompt the user for the values of one or more variables (look-ups).
10- Ability to create html "forms" for data input which would be saved into the database.

Again nice progress on this.
Regards, BH

Igo AtM
Posts: 25
Joined: Thu 16 Nov 2006, 14:36
Location: UK

JS client on its way

#4 Post by Igo AtM »

Thanks for your interest Barry. I have to say that this sort of application is more for the purists rather than the normal user so I'm not to fazed by the slow response. It will be further down the line that I would hope that the real benefits of a quality db back end start to become apparent. It is one of the reasons I'm being rather pedantic about the nature of the Client JavaScript upgrade. I am trying to implement it with an almost transparent HTML accessible server connection. It should then be possible to use the base script in many diverse applications. To enhance the pick up on this I am also using open source code for drag-n-drop widget like tools for the db client front end. This will potentially offer a designer kit where drop downs, input boxes and the like are db aware and can be connected to tables and columns by user selection.
It's a fair bit of work and a long step away from the original SQLiteDBMS but so far its looking good. If in its current form it is ready for inclusion in a Puppy release I'm not sure. I don't really wish to drive puppy users away from the potential by delivering a buggy and limited beta version too soon but I leave such decisions in your capable hands Me I'm just a code monkey!
For now it's back to gcc send and it's buggy server response

Igo AtM
Posts: 25
Joined: Thu 16 Nov 2006, 14:36
Location: UK

#5 Post by Igo AtM »

Hi Brucehohl think you caught me on line and in appreciation of your feed back I'll try and respond now:
1- Add text tags for "Submit" and "Clear" buttons.
Will do on its way
2- Add tag/button that will clear the SQL input box (or change the current clear functionality so that host and DB are not cleared.
This is more difficult as the clear function is just a normal form clear. So it would mean having to split the forms for db connection details and SQL so POST would not be complete on submit.
You would either send db connection or SQL data as there is no way to POST separate forms from a single input without some form of script. It is possible to place connection details in the hidden inputs but then you can not change selections for server and db connection. Still see what can be done
3- Add method to execute the "Submit" and "Clear" buttons from the keyboard (or change the location/tab order of the buttons to be just after the SQL input box).
6- Ability to import data from a text file via from the web interface (in lieu of command line "sqlite> .import file table").
Will do version 0.5.4
4- For select query output make the width of the field headers the same as the width of the data columns.
Not sure what you are referring to here if it is to the lower screen output then this can be done with a XLST style sheet but that would limit accessibility to browsers that support it. Also it is not always so efficient with space if you have a single digit column called THE_BEER_I_IMBIBED_THE_LAST_TIME_I_WENT_TO_THE_BAR then the heading is always going to waste an enormous amount of space in comparison to the content by separating them the browser can distribute space as it finds most efficient.
5- SeaMonkey shows and empty box near the top right.
ERR ?? can you send me an image as I am not getting any format issues my end
6- Ability to import data from a text file via from the web interface (in lieu of command line "sqlite> .import file table").
7- Ability/option to send sql results to a new browser window instead of the lower frame.
Nice ideas will have a look at them they should be very easy to implement
8- Ability to save SQL statements to the database and present these saved SQL statements so that they can be selected/clicked for reuse. (I have already saved some SQL statements in a table with a grouping scheme but I was thinking of something more formal and part of the interface.)
9- Ability to create "parameter" queries, that is, saved queries which when run prompt the user for the values of one or more variables (look-ups).
10- Ability to create HTML "forms" for data input which would be saved into the database.
These last three will be coming on line in the HTML version of the browser as the JavaScript version nears completion.
I had already considered the waste of an active db connection and how it was underused in terms of the ability to backup working practices so they are in hand. One of the issues that I have considered is that this type of facility is really an administration biased need so such services were to be offered via SSL and to select users. But you can look forward to this type of facility in the very near future.
In terms of the HTML forms if you see the post I was placing above whilst you were also making this one I hope this coincides with your ideas
Thanks again for your considered response Brucehohl now code monkey best get coding!!

User avatar
BarryK
Puppy Master
Posts: 9392
Joined: Mon 09 May 2005, 09:23
Location: Perth, Western Australia
Contact:

Re: JS client on its way

#6 Post by BarryK »

Igo AtM wrote:SQLiteDBMS but so far its looking good. If in its current form it is ready for inclusion in a Puppy release I'm not sure. I don't really wish to drive puppy users away from the potential by delivering a buggy and limited beta version too soon but I leave such decisions in your capable hands Me I'm just a code monkey!
For now it's back to gcc send and it's buggy server response
Ok, I'll hold off for now. When yourself and the testers think it's reasonably usable I'll put it in.
[url]https://bkhome.org/news/[/url]

Igo AtM
Posts: 25
Joined: Thu 16 Nov 2006, 14:36
Location: UK

A cup of tea works wonders

#7 Post by Igo AtM »

Hi again brucehohl
Having considered your post whilst brewing a tea it occurred to me that may be I should have made that box to the top right a bit more clear in its purpose.?
I am of the opinion that the HTML browser should remain active script free so we are limited to what the server returns into a none script active browser. This given the fact that we will have a active script environment client as a option.
Due to this limitation there are curtain hard and fast rules that we cannot work around one being that we cannot cross data from one frame to another we can only post one form at a time to the server etc,etc
In view of these limitations what I have done is offer a compromise so the idea of the box in the top right is it is just a plain everyday textbox with a clear button on the top. But it is separate to all other HTML elements (really just a stray form textbox ) What it offers is a scratch pad in a script free environ so you can cut and past into it and the data will remain whatever you do to the lower frame or menu/SQL interface so data items can be carried through here into your SQL statement build. As I say not ideal but a compromise. hence if you wish to clear the main menu/SQL form but retain the connection details then you can just past them into the window and back again after clearing. Same with the data window just copy table data into the scratch pad and your data is saved so you can call up another form and use the saved info. I hope this was what you were referring to
5- SeaMonkey shows and empty box near the top right.
or I have just wasted a lot of effort on a formating issue??

brucehohl
Posts: 58
Joined: Thu 07 Jun 2007, 11:47
Location: Ohio

#8 Post by brucehohl »

Hello Igo AtM, in response to your posts:

- From the box at the top right I now understand its purpose as a persistent cut and paste area. Perhaps an appropriate label should be given to the box. That box does not fit well on my screen at 1024x768. See attached png file.

- Regarding my suggestion for a way to clear the SQL box only, I did not realize the design challenges involved. Perhaps that suggestion should be set aside for now. In use if I used the clear button in between SQL statements I had to repopulate the host and DB fields. Since I rarely change these values I found it easier to clear the SQL box with triple click then backspace. Really though for good efficiency I would like not to have to move my hands from the keyboard while navigating a database with SQL statements. That is why I asked about changing the clear button. The form would be a bit closer to the wish of not leaving the keyboard if the user could tab from the SQL box to the submit button and press enter to execute.

- If you can add Ability to import a text file to a table from the web interface it would be nice to also give the ability to export a table to a file.

- Related note on this: Where I have used html tables to display the result of a query on my Postgres+PHP application the user can highlight the table results and copy and paste the result into a spreadsheet. Upon paste in the spreadsheet the data set is seen as a csv file and thus automatically pasted. I tried this from the html form but the data did not have any field separators (not csv) and thus could not practically be pasted into a spreadsheet in a usable form (data seen as fixed width). Please see web_query_copied.png and web_query_pasted.png regarding this. It is important for users to be able to easily get data into and out of the database application.

- Field header width versus data width (my original wish #4 regarding the lower screen output). It is true that mismatch in size of the table field name which becomes the header name and the data itself can result in the wasting of space. This can be corrected by using an alias for the field name in the SQL query. I have done just that in the attached web_query_pasted.png. I think from a user's perspective leaving the mismatch makes the output harder to use and appears to be a format error rather than an intention to save space.

- Regarding wish #7 Ability/option to send sql results to a new browser window instead of the lower frame the following might be a bug: If I (1) enter a query into the SQL box, (2) tab 12? times until I get to the "submit" button, (3) press enter, (4) this causes the form to clear, (5) Enter a sql query and DB value, (6) click "Submit", (7) Results now appear in a new Window until browser is closed and reopened.

Regards, BH
Attachments
attached_files.zip
(109.18 KiB) Downloaded 837 times

Igo AtM
Posts: 25
Joined: Thu 16 Nov 2006, 14:36
Location: UK

Issues with layout

#9 Post by Igo AtM »

Most of the issues you raise are related to the fact that I have deliberately placed the onus of operation on the HTML Client to function with no active scripting. This means that any formating of the data at the client is left to the limitations of CSS. CSS will allow the generation of pre and post element content however we have an issue with Mozilla and such content selection. try the following

If you try adding the following to your default-xml.css in /root/my-documents/sqlitedbms/htdocs

data:after {content: ",";}

now if you try sqlitedbms restart in a terminal You should find that the data list is now inter spaced with commas however using the Mozilla engine I know of no way such browser generated content can be selected. In Opera it will select and past but not SeaMonkey or Firefox. and much as I would like it otherwise I think the Mozilla approach to be right as a select request should be for the server sent content and not what the browser created. So that puts the problem back with the server and I am not keen on changing the transmission format or inserting none user generated elements for csv selection in the none active script browser. In saying that the intention is to enhance the existing HTML browser with a option for limited js and XSLT in which case we have no problem to service the enhanced functionality.

With column headers again it is a case of perspective I feel you are right that if your intention is to use the database output in the form of a spreadsheet then you can alias names and limit length but it will still potentially wast viewing space if I have a column named "Index" then I would need to have 99999 rows to warrant the loss of viewing space for a table header that is no longer in view ( if I display all results in one go ). This is a simple example as the column names get more involved then the space loss becomes more of an issue or the alias names become more perverse and user functionality is effected. The way I approach this issue in the JavaScript version is to enforce a maximum width for table headers and then use a mouse hover to detect the current column the full headers are then displayed in a fixed positioned box. Again we come up against the issue of active scripting in the HTML browser !
The other issue here I think is if the user is viewing content in a none spreadsheet manner ie no obvious table layout. Say an address book or a circuit diagram. At its simplest level this is now happening in the HTML version with error messages and version information. To alias the header data in this type of output would seriously undermine the potential for usability. My take on this is to continue with the current system what I shall try to do is insert generated content (index numbers) to match column names to columns this is the path most spreadsheet developers go when confronted by this same issue. Your thoughts on this will be of value.

Re-the key board bug and opening in a new window It is just that a bug. I tend to regard the keyboard as a enemy not an friend so stepping through tabs to a button is not something that even occurs to me. What you have managed to uncover is the underlying hack that allows hover buttons on IE as I use a method of inserting an anchor tag into a form something that should not be possible but via some very perverse coding there is a way to do this. The problem is it leaves hidden href links (hidden to the mouse that is). It is now fixed and will appear in the next version. But serendipity rules as you have also found the perfect way to offer the user an option to display the results in a new tab/window. I just need to activate the button as a separate option on the interface
As far as the display issues with the box overhanging the SQL window. Its a horses for courses problem. I am trying to fit as much data into the interface as is viable. Whilst Mozilla SeaMonkey at any rate set there default font size to a very large value 14 I believe it then becomes difficult to squeeze all the data into the window. I run SeaMonkey with a font size of 12 and set minimum font size to none both these settings are in edit -> preferences -> appearance -> fonts. I have noted that you seem to be displaying bold fonts as well the menu should be normal fonts? If you look in
/root/my-documents/sqlitedbms/htdocs/client and the file html_menu.htm
if you find a css line
TD {
font-family:tahoma, arial;
font-size:10pt;
color:#333;
font-weight:bold;<<<< this wants changing to font-weight:normal;
}

Thanks for your efforts brucehohl you should find the JavaScript version much more viable for you purposes and your feed back is useful in terms of its development direction

brucehohl
Posts: 58
Joined: Thu 07 Jun 2007, 11:47
Location: Ohio

#10 Post by brucehohl »

- Regarding cutting and pasting data from the html client: After reading all the technicalities I went back and checked my Postgres+PHP application: data is displayed in a html table, when copied from a Firefox 2.0x browser the data is tab separated and hence pastes directly into OO_Calc or MS_Excel. I do think users will want cut and paste as this functionality is available from MS_Access and OO_Base.

- On the column header stuff I do actually alias column names to be generally no larger than the data in the column. So 'Index' might become simply 'I' or 'inx'. Also typically I set all columns to a fixed width for a predictible layout for printing and display. Regarding viewing content in non spreadsheet manner: probably about 99% of my reports are tabular/spreadsheet in format.

- Regarding keyboard as an enemy not friend ... hmmm ... careful here as sometimes the mouse can reduce productivity particularly when doing repetitive tasks which can not be automated. In these cases one can get more done if one can keep the hands on the keyboard, thus, being able to tab to an execute or save button then back to data entry can be useful.

- Regarding "display issues with the box overhanging the SQL window" you are quite correct I did have my fonts set too large.

Looking forward to the js version.
Regards, BH

Igo AtM
Posts: 25
Joined: Thu 16 Nov 2006, 14:36
Location: UK

#11 Post by Igo AtM »

I think we are going a little bit in circles on the issue of cut and paste. If you are comparing the facility offered by an application that includes server side scripting ie PHP then the piped format can be in just about any form you wish. The issue here is I am displaying data directly from the server connected to the db into a none script browser. Somewhere from the db to the browser data must be added to the stream in order for the selection process to include tabs or commas. Or the selection process its self must parse the data and add the missing elements and with no script this just can not be done. The version of the graphic interface we are discussing is the most basic access and exists to offer the broadest compatibility possible. The bottom line is that if css parse of xml data canot insert seperators into the data then there is no way it can be done. I feel this is a mote issue as in reality a user option will allow limited scripting and thus problem solved. Also one of the new elements that will be included soon is the ability to include a default XSL style sheet in the lower browser again this will allow for the tab stops to permit cut n paste.

Regard the column headers I can only reiterate what I posted last time. I am trying to keep a perspective on this in that the browser can and should be used by the widest application base. If the column names are to be limited by display restrictions that service spreadsheet conformity it places a huge limitation on the colum names for all data passing into the db. At the end of the day this type of application is meant to facilitate the inspection and insertion of data into the db not effectively limit or constrain the data you can put there.

Re the keyboard well most people can read past typos but processors tend to segfault so I cut n past quite a lot. Also for film editing and graphic design give me Mr Mouse any day it's a work related thing.

I appreciate your perspective on these issues and within the bounds of possibility will try to include your suggestion in up coming releases

brucehohl
Posts: 58
Joined: Thu 07 Jun 2007, 11:47
Location: Ohio

#12 Post by brucehohl »

Saw this at http://sqlitedbms.sourceforge.net/doc/changes.htm

2006-03-15 (0.1.6)
* Fix #1450192. wrong idle time response when show status SQL
* Fix #1450184. wrong content-length response headers
* Improve #1450193. ctrl+enter -> execute SQL in HTML version client tool

So 'ctrl+enter' is available to execute SQL in HTML version client tool. (Hooray!)

Igo AtM
Posts: 25
Joined: Thu 16 Nov 2006, 14:36
Location: UK

#13 Post by Igo AtM »

HI brucehohl
OK reference accesskey= a html standard element try this site
http://www.cs.tut.fi/~jkorpela/forms/accesskey.html
I do not like the use of the access key for the same reason as the author of this information as it presupposes the environment that the application is being run in. I do a lot of web development so I run with a whole host of extensions and they are in constant competition for the limited button based resource, to attempt to second guess what assignment to give so as not to cause the user to self implode their browser is impossible. Secondly the only reason that the former SQLiteDBMS HTML client could ( when running ) offer accesskey functionality was because it was not a HTML browser but a JavaScript dependent hack that ran badly on some versions of IE and not at all in most compliant browsers. Accepting the fact that the facility is in place for the user to select the option of running a JavaScript version of the Client browser. One of the first things I chose to do was remove active script dependency from the HTML client so it was a HTML client. And as such gave the broadest range of user base to basic Sqlite3 database access. If you disable JavaScript in you browser you will find that full functionality remains to the extent that basic HTML and CSS methods allow. If the user requires a more advanced set of options then this is the intention of providing the Scripted version. I have the advantage of the pre-release on my machine here and I can assure you the range of options are far in access of anything the original version offered as well as being WC3 compliant and stable. Amongst some of the new functionality is a user preference selector which will allow configuration of access keys to fit the specific user requirements. I am of the opinion that to attempt to introduce such facilities into the HTML version far from an enhancement would if it worked at all most likely cause it to limit the base of potential users due to incompatibility issues with their environment configuration.
* Fix #1450192. wrong idle time response when show status SQL
* Fix #1450184. wrong content-length response headers
Picking up on these type of issues are always of great help however in this case both these issues are no longer of relevance in Puppy's version as I have rewritten the code in question in the server implementation and they are fixed in our version but I shall take a look at them for any potential hints as there still is a cache to browser refresh issue in the server.
Must get back to working on the JS Client or it will never get to the puppy public
regards Igo AtM

lurahxp
Posts: 17
Joined: Wed 28 Nov 2007, 07:01
Location: indonesia, jawa tengah,jawa barat
Contact:

where i cant find other libray to run sqlite dbms?

#14 Post by lurahxp »

i try install SQLiteDBMS / PUP-DB front end
but its dont have menu
what i type in rvxt to run it?
seem. its need other library, where i can find them?

Igo AtM
Posts: 25
Joined: Thu 16 Nov 2006, 14:36
Location: UK

Quick How To SQLiteDBMs

#15 Post by Igo AtM »

Hi lurahxp
TO START THE SERVER
If you have installed the latest version of SQLiteDBMS (ver 5.3) as a .pet then you should only need to open a terminal rxvt whatever and type sqlitedbms 'ENTER'
your terminal should reply with
$ sqlitedbms
$ Usage: sqlitedbms {start|stop|restart|status|notes}
so for any option it is just a case of
$ sqlitedbms start
OR
$ sqlitedbms stop
etc
If this is not happening then something is very wrong with your install and I would be interested to know.
For further information if you type
$ sqlitedbms notes
It should return a short description of the programs configuration and release information
if you try
$ sqlitedbms status
this should show you if the backend server is running and where its files are on your system
TO VIEW THE CLIENT
As SQLiteDBMS is a web based application once the server is running ( tested by 'sqlitedbms status' as mentioned above ) Then you will need to open your favorite browser and for the connection URL type (or just click the page link)
http://127.0.0.1:6543
this should open a local connection to the server and all being well give you access to the HTML and JavaScript CLIENTS
Again if not I would be interested
If you have a local network then you can connect to the client by using its machine ip address

Code: Select all

http://IP . NUM . goes . here : 6543
or if you have DNS or some local name resolution

Code: Select all

http://DNS-Name:6543
The current version is still a test version and most of the work you can currently see has gone into the backend server and the html browser.
I am in the process of updating the JavaScript Client and it should be available for download in the next few weeks. When ready it promises a big advance in user facilities from the current version.
If you have any issues or problems with running SQLiteDBMS on Puppy please let me know as it is now that problems can be resolved in the development process[/code]

ol_smokey
Posts: 80
Joined: Wed 06 Jun 2007, 08:47
Location: Blackpool, UK

#16 Post by ol_smokey »

There's a new add-on for Firefox which allows you to manage sqlite databases that might be useful.

Here: https://addons.mozilla.org/en-US/firefox/addon/5817

smokey

nic2109
Posts: 405
Joined: Mon 01 Jan 2007, 20:24
Location: Hayslope, near Middlemarch, Midlands, England

#17 Post by nic2109 »

I've just come across this thread. What's the current state of SQLiteDBMS? I'd be happy to join in with some testing if wanted.

I've used SQLite as part of the XAMPP package, and have set up my own (local network only) Web server under Puppy for a Wordpress blog.

That all worked pretty easily, so how does this SQLite differ?

Post Reply