Re: [sqlite] SQLite for single user data manipulation
Hello 'Liters! I'd like to move to SQLite from Postgres, but have two quick questions. I'm a social scientist looking to manipulate a large dataset (5 billion transactions, 700gb). I do not need multiple connections, and will only ever run one query at a time. I started in Postgres, but discovered that in Windows one cannot increase some of the per-query RAM memory caps above 2gb (I know -- I would love to switch operating systems, but it's beyond my control). So I'm thinking of moving to SQLite. So may I understand more fully. 1. If you perform a SELECT * FROM XXX_5B_ROW_TABLE you are unable to retrieve that result set becasue if exceeds per-query RAM memory cap 2GB on Windows? 2. Or in trying to stuff that result set into a data structure you exceed the RAM allocation cap? Before I make the move, I was hoping you kind people could answer two quick questions for me: -- am I going to have problems using all 16gb of ram on my Windows 8 machine for data manipulations if I switch to SQLite? Or will SQLite set me free? If 2. above I do not see how moving to SQLite is going to help you. As indicated below no tool is going to allow the manipulation of large datasets like that in memory. I have found in processing data that most software makes this mistake. The proper approach is to process that data in chunks for your visualation or algorithmn. -- Is there any reason I should NOT use SQLite for manipulation of large datasets like this (for example, pulling out unique pairs of transaction participants, averages across users, etc.)? All the literature I can find talks about SQL database choices for people setting up databases that will be queried by lots of people, and I just can't find any input for people like me who just want a data manipulation tool for data that's too big to read into RAM and manipulate with the usual suspects (R, Stata, Matlab, etc.). Thanks all! Nick I have been working on a routine for the MyJSQLView project that could pull a query from PostgreSQL and create a local database either in memory or file, say SQLite, so that processing could take place for analysis. The main reason this came about is because in processing large datasets from a networked RDBS it became apparent that to speed up analysis it may be easier to have the query result stored locally in a memory or file database. Dana M. Proctor MyJSQLView Project Manager ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database to SQLite Population
On 4/5/2014 12:33 PM, da...@dandymadeproductions.com wrote: The assumption is that the networked database, datasource, could be on the local lan or Internet. So am I to understand that you indeed are concerned (1) that the pipe could be slow, and (2) that the server may be heavily loaded? Alright. The 'snapshot' would not necessarily be everything, but based on a SELECT statement of a set of the datasource content. Okay, that's good. The benefit I see from this local file/memory database is that I have found some processing of data for analysis occurs over and over to derive comparison results. By having the data local the user can perform these analysis without constantly re-querying the production database. That makes sense, though there are costs, but you say... It is assumed that the user knows that data can be stale at any point beyond the initial load. Okay, also good that this limitation is known and accepted. The analysis tools can also remain unchanged since the data is still coming from a RDBM. Yes, good point. This is definitely a massive plus over rolling your own caching mechanisms. The only reason the queue was considered is because it is quite conceivable that a network datasource would be the choke point so a queue being filled by several threads in process 1 would speed up the population. I'm trying to understand your statement because at first glance it seems contradictory. You assert the data source may be slow, but the solution you present is normally used when interfacing with a slow data *sink*. My best guess is you're considering simultaneously running multiple source queries simultaneously to better utilize (monopolize, really) the network, and this design indeed typically needs a queue to serialize its output so it can be fed to a sink that accepts only one thing at a time. Yes, and is assumed as you point out later fast at populating data. I have two answers for you. Pick whichever you like. I apologize in advance for the level of detail in answer #2. I just want to be clear so you can make the right decision. Also I can't help but plug the Wibble web server (featuring Tcl coroutines) since it's my baby. Answer 1: Keep it simple. Have one connection only, and just write whatever you receive as you get it. SQLite will not be your bottleneck. One, it's very fast. Two, you already say the source is the choke point. No need to complicate things. Answer 2: You really do need to have multiple connections at a time, and you're willing to have a more complex system to support this approach. I do not want to clog up the mailing list with further additional details, and have not included those details from 2. In short would prefer solution 1. and have to contemplate the benefit of some performance increase for the level of complexity introduced in solution 2. Again thank you Andy for your input. I will have to take time to digest the insight you have provided in the details of Answer 2. I will review this information to more fully understand the possibilities. ~ ~ ~ So to sum up, you want to repeatedly analyze one or more data sets which just so happen to have been read from a remote database, but you don't want to make that remote database do all the work because it could be at the nether end of the Internet. You want to do this analysis using your existing codebase which was designed to operate using SQL. You see SQLite as a good fit because it's compatible (useful subset of SQL) and is trivial to set up (link it into your program and you're done). That all seems totally reasonable to me. -- Andy Goth | andrew.m.goth/at/gmail/dot/com I think you summed it up pretty well. Dana M. Proctor MyJSQLView Project Manager ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database to SQLite Population
On 4/4/2014 1:21 PM, da...@dandymadeproductions.com wrote: On working with the MyJSQLView database GUI access tool it has been determined that a local file/memory database would be valuable to perform recurring analysis on datasets from the connected datasource. Sqlite is being considered as the local database. If I understand you correctly, you're suggesting making a local snapshot of a networked database to optimize performance. I'm not sure what remote database you're using, but it seems to me with properly designed prepared statements there won't be much gain in downloading everything in advance to the local machine, especially since the download will certainly include more data than is actually needed. Additionally consider the loss of coherency when the upstream database is modified but the local snapshot becomes stale. The assumption is that the networked database, datasource, could be on the local lan or Internet. The 'snapshot' would not necessarily be everything, but based on a SELECT statement of a set of the datasource content. The application already has a mechanism in place that the user can store queries in a bucket for reuse. I guess a similar commercial term for this would be ETL, but without the transform perhaps. One of the commercial tools out there called Tableau I believe is using this exact concept. The benefit I see from this local file/memory database is that I have found some processing of data for analysis occurs over and over to derive comparison results. By having the data local the user can perform these analysis without constantly re-querying the production database. The analysis tools can also remain unchanged since the data is still coming from a RDBM. It is assumed that the user knows that data can be stale at any point beyond the initial load. All the underlining code has been created for conversion between datasource and local database. The code is now being developed to perform the DB to DB transfer population. The basic algorithm being considered is: Process 1, Loop: 1,2,3 1. Read Datasource row from query. 2. Create SQL INSERT statement. 3. Write SQL INSERT into queue. Process 2, Loop: 4,5 4. Read SQL INSERT from queue. 5. Write SQL INSERT to SQLite db.} The queue seems to be an unnecessary intermediary. Simply alternate between reading from the remote database and writing the received data to the SQLite database. This simpler design is also more amenable to prepared statements which offer indispensible performance and security benefits. Thank you for that input. My general thoughts were along your statement of a simpler design. The only reason the queue was considered is because it is quite conceivable that a network datasource would be the choke point so a queue being filled by several threads in process 1 would speed up the population. Do keep in mind that an SQL database consists not only of INSERTs (the data) but also CREATE TABLEs (the schema) plus indexes and triggers and views. The data is the only interest here beside indexes for the new table data. Completed routines have already been created to re-create the structure of the datasource database. Perhaps someone from this forum could possibly comment on another open source project with similar type of db to db transfer that could be studied or alternative algorithm. It doesn't matter what database you use, the algorithm remains the same: read source, write destination, repeat. Many database systems have convenient import routines for common data sources. For example, the SQLite shell has the .import command for loading a file into a table. -- Andy Goth | andrew.m.goth/at/gmail/dot/com These import routines are exactly how I have had to populate a local SQLite database when testing analysis plugins. The process of setting up the database table(s) exporting from datasource data and importing to the local file/memory database would be much simply for non-expert users if automated so they can focus on deriving results from analysis with a local higher performance file/memory database. Thank you Andy for your comments. Dana M. Proctor MyJSQLView Project Manager ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database to SQLite Population
Hello, On working with the MyJSQLView database GUI access tool it has been determined that a local file/memory database would be valuable to perform recurring analysis on datasets from the connected datasource. Sqlite is being considered as the local database. All the underlining code has been created for conversion between datasource and local database. The code is now being developed to perform the DB to DB transfer population. The basic algorithm being considered is: Process 1, Loop: 1,2,3 1. Read Datasource row from query. 2. Create SQL INSERT statement. 3. Write SQL INSERT into queue. Process 2, Loop: 4,5 4. Read SQL INSERT from queue. 5. Write SQL INSERT to SQLite db.} Perhaps someone from this forum could possibly comment on another open source project with similar type of db to db transfer that could be studied or alternative algorithm. Thanks, Dana M. Proctor MyJSQLView Open Source Project Manager. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct PostgreSQL to SQLite connection?
Hello, I'm currently working on a feature to MyJSQLView that allows DB to DB table creation for use with creating in memory or local file data for anaylsis. The feature is really being made to facilitate the oposite of your goal of SQLite to PostgreSQL, but rather PostgreSQL/MySQL/Other to something like SQLite, HSQL or Derby. Have no experience with a foreign data wrapper like mentioned, but usually some of these scipts do not fit most specific case uses that are desired. I think I'm with some others. This would a be simple script or a plugin for MyJSQLView to perform the SQLite to PosgreSQL insertions that does exactly what is desired. Seems like a good task for a post graduate student. Dana Proctor MyJSQLView Project Manager. Subject: [sqlite] Direct PostgreSQL to SQLite connection? Message-ID: 5243132b.10...@tanguaylab.com Content-Type: text/plain; charset=UTF-8; format=flowed Looking for a solution to connect directly from a PostgreSQL database to a SQLite database. We need PostgreSQL for it's Window/Analytic functions and other features that SQLite doesn't have. Yet, SQLite is great for collecting data. Every one of our experiments adds 300MB to the SQLite database. Rather than exporting to CSV and then importing to PostgreSQL, we'd like to directly access SQLite from PostgreSQL. Does anyone have good experience using 'Foreign Data Wrapper for sqlite' or another method? https://github.com/gleu/sqlite_fdw Joe Fisher Oregon State University ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dynamically generate SQL statements for SQLite
Subject: Re: [sqlite] Dynamically generate SQL statements for SQLite Message-ID: 1379089163135-71243.p...@n5.nabble.com Content-Type: text/plain; charset=us-ascii I agree with the other poster. You can just use any string manipluation routines you like to assemble the SQL into a string and then execute it. Take care with literals, eg where name = 'fred' might have to become where name = ''fred'' in Delphi (thats two single quotes at each end). If it's any help, two brilliant tools that I use are SQLinForm (http://www.sqlinform.com) and SQLite Expert (http://www.sqliteexpert.com/). A while back I did some research on dynamic generated SQL tools, open source ones, and traced some of the code back to a project on Sourceforge. A project there used a Graphical SQL Query Builder. I started the search I think from code seen in JasperReports. There now appears to be a fork of that tool. I also integrated the Visual QueryBuilder into my MyJSQLView project. Sourceforge Projects: http://sourceforge.net/projects/sqleonardo/ http://sourceforge.net/projects/sqleo/ Dana Proctor MyJSQLView Project Manager ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting Constraints Details
For some reason I'm using a more complicated version of that same query for constraints. Perhaps its based on some older information that may have been in the table. SELECT type, sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type!='meta' AND sql NOT NULL AND name='MyTable' danap. Vijay Khurdiya wrote: How to get details of constraints associated with Data in SQLite3. There is no API to get individual properties; you have to look up the original SQL statement: SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'MyTable' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MyJSQLView Version 3.44 Released
MyJSQLView Version 3.44 Released The MyJSQLView project is pleased to release v3.44 to the public. The release's main composition is for the support of an additional database, but in so doing expands the capability to use external data sources for plugins. Modifications have been coded in to standardize the connection properties in the Connection Manager and proper close of memory connections for those database that support such. The Login Frame now supports via the database entry field the passing of parameters like, myDB;loglevel=1, to more customize connections. Also included with this release comes changes to the Query Frame UI, and enhancements to the HeatMapper plugin. Dana M. Proctor MyJSQLView Project Manager http://dandymadeproductions.com/projects/MyJSQLView/ MyJSQLView provides an easy to use Java based user interface front-end for viewing, adding, editing, or deleting entries in several mainstream databases. A query frame allows the building of complex SQL statements and a SQL Query Bucket for saving such. The application allows easy sorting, searching, and import/export of table data. A plug-in framework has allowed the inclusion of tools to visual build queries, profile and plot data for analysis. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help needed with query to tell a FK from a PK
Hi, I'm a complete sql novice so please go easy :) I'm trying to create two queries so I can determine which of the keys for a given sqlite3 table is the primary key and which keys are foreign. Hello, I did not fully review your question detail, but perhaps this may help. The MyJSQLView application can generate a table DDL definition which I pretty sure does properly identify primary foreign keys for a table. Please see: http://code.google.com/p/myjsqlview/ Code-- source-- utilities-- TableDefinitionGenerator.createSQLiteTableDefinition() danap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Subject: Re: Simple SQLite-based spreadsheet?
Hello I need to enter a bunch of items into a table that I can later read from a web app. I was wondering if someone had come up with a Windows application that would... - use Sqlite to host the data - make it very easy to add a table anc columns (possibly several related tables) - display a spreadsheet to make it easy to add data ? Namely, a basic, no brainer MS Access relying on SQLite. Considering so many people use Excel as poor man's database because anyone can use it while Access takes some learning... I think there's an opportunity here. Thank you. Or perhaps MyJSQLView Be sure to install Xerial JDBC jar in jre/lib/ext/ http://myjsqlview.org http://www.xerial.org danap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subject: SQLite driver for Java
Well its been a while since I did a search on it, but a few years ago the best current one I could come up with is the xerial.org SQLiteJDBC. My project, MyJSQLView, uses this to provide a GUI to a SQLite database File. Dana M. Proctor MyJSQLView Project Manager Howdy! What driver are people using to access SQLite databases from Java applications? Will -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory It's amazing what you can do with the right tools. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MyJSQLView Version 3.40 Released
MyJSQLView Version 3.40 Released The MyJSQLView project is pleased to release v3.40 to the public. The release marks a major reorganizational effort to the source code and plugin management. Though the public will notice little with regard to the source subpackaging the plugin management changes bring the ability to now load plugins from remote locations. Plugin repositories can now be created by individuals or organizations that wish to provide custom plugins on the basis of a realtime network loading capability. In that regard the MyJSQLView project has added to its own public repository a free open source plugin that contains a graphical SQL query builder. MyJSQLView works in conjuction with the Xerial SQLite JDBC to access a SQlite database file. Dana M. Proctor MyJSQLView Project Manager http://dandymadeproductions.com/projects/MyJSQLView/ http://www.xerial.org MyJSQLView provides an free open source easy to use Java based user interface frontend for viewing, adding, editing, or deleting entries in several mainstream databases. A query frame allows the building of complex SQL statements and a SQL Query Bucket for saving such. The application allows easy sorting, searching, and import/export of table data. A plug-in framework has allowed the inclusion of tools to profile and plot data for analysis. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you load a .csv and skip the first line?
Is there some way to load a .csv file into a SQLite database table when the first row (record) of the file contains headers? This does not work? -- .separator , .import test.csv ld_assay Here's my database / table: CREATE TABLE [ld_assay] ( [id] INTEGER PRIMARY KEY, [barcode_id] NCHAR(9), [light] BOOLEAN, [bpm] REAL); Here's my test.csv: id,barcode_id,light,bpm 1,02455,1,180.2 2,02455,1,168.3 3,02455,1,189.4 I'm pretty sure MyJSQLView will do this for you. Been a while since I tested it it, but it should use the first line to compose a SQL insert statement that only adds the columns specified. Left out columns should take default values. Since MyJSQLView is a Java based GUI the Zentus's SQLite JDBC must be used as the commnuncation interface to the SQLite database file. Add it to your java/lib/ext directory to have it loaded automatically. danap. MyJSQLView http://myjsqlview.org SQLite JDBC http://www.xerial.org/trac/Xerial /wiki/SQLiteJDBC#WhatisdifferentfromZentussSQLiteJDBC ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Example database with lots of types?
I'm working on getting the Mac version of the SQLite ODCB connector fully functional. I'm having some problems getting examples of lots of different data types - for instance, my northwind copy has a decimal stored as a varchar. Does anyone have a small test DB they would be willing to part with so I could see lots of different data types and what happens when they come through the adaptor? During the development for MyJSQLView for SQLite we created a test tables that includes all the data types supported in SQLite. As Simmon indicated there is not much there. These test scripts and a db file are in the download for the application. If you do not want to download, then here is links to the script and db. Enjoy design dress for Princess Leia. Dana M. Proctor MyJSQLView Project Manager http://dandymadeproductions.com/projects/MyJSQLView/temp/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JDBC Drivers--which one?
I have found both the Xerial and Zentus SQLite JDBC Drivers on the net. This leads me to wonder are there other JDBC drivers for SQLite and if so what are they? Also, what are the pros and cons of the available drivers? Which one is the recommended one for use with SQLite? Thanks, LA I think you have found them. During the research for a SQLite JDBC solution for the MyJSQLView application these are two that turned up. The Zentus one I think is dated, while Xerial appears to be the newer. I have noticed that the latter has not been updated since last year. Which leads me to wonder abouts its future. I do known the Xerial driver allowed a pure Java mode which allows no dlls to be installed. Meaning nothing is needed besides your Java code and the driver. This does impose a performance hit. In my latest testing with the Xerial JDBC I have discovered a lack of ability to handle large imports and exports to the database for inserts and selects. A lot of tweeking was done with batch and commit/non-commit modes to no available. I believe the JDBC is not properly handling the memory consumption during these process. The same code works fine with other databases, but of course these are server based not like SQLite. danap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MyJSQLView Version 3.35 Release
MyJSQLView Version 3.35 Released The MyJSQLView project is pleased to release v3.35 to the public. The release is a maintenance update to address several issues that manifest themselves when using the SQL Query Bucket and Query Frame. Drops to the Query Bucket from the main summary table fail to properly create the syntax as derived from the search and sort parameters in the Summary Table. Fixes to the Query Frame now allow the use of aliases. The application has also been updated to remove legacy data structures that had decrease the efficiency of the application. Dana M. Proctor MyJSQLView Project Manager http://dandymadeproductions.com/projects/MyJSQLView/ http://www.xerial.org/trac/Xerial MyJSQLView provides an easy to use free open source Java based user interface frontend for viewing, adding, editing, or deleting entries in several mainstream databases. A query frame allows the building of complex SQL statements and a SQL Query Bucket for saving such. The application allows easy sorting, searching, and import/export of table data. A plug-in framework has allowed the inclusion of tools to profile and plot data for analysis. MyJSQLView uses the Xerial JDBC driver to communication with the SQLite database files. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Java JDBC type Interface to SQLite
Currently the MyJSQLView application uses the Xerial SQLiteJDBC interface to interact with a SQLite database. Does anyone know of a more native type of JDBC interface mechanism that Java may use to access a SQLite database? The project would really like to be able to create a local file or memory database like SQLite to re-analyze database data once it is initially collected via a query from a remote database. Dana Proctor MyJSQLView Project Manager. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GUI for SQLite
Message: 8 Date: Wed, 7 Mar 2012 21:47:28 + From: Rose, John B jbr...@utk.edu To: sqlite-users@sqlite.org sqlite-users@sqlite.org Subject: [sqlite] GUI for SQLite Message-ID: cb7d419f.20e24%jbr...@utk.edu Content-Type: text/plain; charset=us-ascii We are new to SQLite and have experimented with a few GUIs, Firefox plugin, SQLite DB Browser, and Navicat. Is there a single GUI that is considered the best, with the most features? Thanks Guess I can't speak for which SQLite GUI is best, but I have been working on supporting SQLite with MyJSQLView. Got most of the basic features in place like add, editing, deleting, import and export of data. In addition I have created a plugin environment for the application to support various analysis. Currently the app comes with two a TableFieldProfiler and Heatmap plotter. You can create your own plugin, tutorial outlines how. Plugin Use Examples: Table Field Pattern Analysis Map: http://dandymadeproductions.com/projects/MyJSQLView/analysis/latitude_pattern.html Ocean Temperatures Heat Map: http://dandymadeproductions.com/temp/ocean_temperatures.jpg MyJSQLView requires the Java Runtime Environment 1.5+, SQLiteJDBC, and is free open source tool. See http://www.xerial.org for SQLiteJDBC. Install into jre/lib/ext/ Dana M. Proctor MyJSQLView Project Manager myjsqlview.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite, portable, image store
Hi I would like some help to use SQLite as a general information resource on my pc's. I would like to use open source software but will consider proprietary software. Even better would be portable software, I could take all my photos and the means of searching them on a disk drive. In fact I would like my first project to be a database of all my photos. I think I need: dbadmin tool, db front end, image viewer from BLOB to screen view. Means of storing image in db. To get me started could anyone suggest software tools please. Intend to learn ADO, ColdFusion, I program a little in DBA with msAccess, html, CSS, basic. Have used C. Hello Scriptham, I think MyJSQLView can do this for you. Download and extract the folder contents to your hard drive. Install the Xerail SQLite JDBC to your Java Runtime lib/ext directory. Use default settings for SQLite login, specifying the database file. You can use the test sqliteType.sql file to create your own table with a id key, and blob type. Import the table to create the table in the database. Though MyJSQLView does not have a image viewer for the blob, pictures, I have seen some. You could modify the Java source to view them or make a plugin. If you need additional help let me know I may have some other projects that do have a image viewer that I could steer you to. danap MyJSQLView - myjsqlview.org SQLiteJDBC - xerial.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 50, Issue 26
Hi I would like some help to use SQLite as a general information resource on my pc's. I would like to use open source software but will consider proprietary software. Even better would be portable software, I could take all my photos and the means of searching them on a disk drive. In fact I would like my first project to be a database of all my photos. I think I need: dbadmin tool, db front end, image viewer from BLOB to screen view. Means of storing image in db. To get me started could anyone suggest software tools please. Intend to learn ADO, ColdFusion, I program a little in DBA with msAccess, html, CSS, basic. Have used C. Many thanks Scriptham. Hello Scriptham, I think MyJSQLView can do this for you. Download and extract the folder contents to your hard drive. Install the Xerail SQLite JDBC to your Java Runtime lib/ext directory. Use default settings for SQLite login, specifying the database file. You can use the test sqliteType.sql file to create your own table with a id key, and blob type. Import the table to create the table in the database. Though MyJSQLView does not have a image viewer for the blob, pictures, I have seen some. You could modify the Java source to view them or make a plugin. If you need additional help let me know I may have some other projects that do have a image viewer that I could steer you to. danap MyJSQLView - myjsqlview.org SQLiteJDBC - xerial.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MyJSQLView Version 3.33 Released
MyJSQLView Version 3.33 Released The MyJSQLView project is pleased to release v3.33 to the public. The release includes improvements in the efficiency of use for the SQL Query Bucket and an Aggregation/GROUP BY feature for the Advanced Sort/Search Form. The new release also brings a new default plugin called HeatMapper that allows the plotting of multi-variable numeric data. Initial tests for the HeatMapper plugin used latitude, longitude, and temperature data from measurements in the Alantic with over 90k records. MyJSQLView uses the Xerial SQLite JDBC to establish the connection to a SQLite Database via it be file or memory. Dana M. Proctor MyJSQLView Project Manager http://dandymadeproductions.com/projects/MyJSQLView/ http://www.xerial.org/trac/Xerial MyJSQLView provides an easy to use Java based user interface frontend for viewing, adding, editing, or deleting entries in serveral databases including SQLite. A query frame allows the building of complex SQL statements. The application allows easy sorting, searching, and import /export of table data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Wanted - a straightforward 'grid' data entry tool for sqlite
I think MyJSQLView should do what you suggest. Most of the commands can be entered via keyboard for add, edit, delete, etc. for data manipulation. Free and open source. http://sourceforge.net/projects/myjsqlview/ To use with SQLite download Xerial JDBC and install in java/lib/ext/ directory. http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC#WhatisdifferentfromZentussSQLiteJDBC danap. I'm after an application which will allow me to enter data into a sqlite database using a 'grid' layout of the data. I.e. I want the existing contents of the database displayed as a table and I want to be able to click on a field in a row and just edit the data in place. In addition I want to be able to use the TAB key to move from field to field (another key would be OK, just not the mouse). There should be an empty row at the bottom for entering a new row. I'm happy to use a design tool of some sort to do this although a ready made application would be easier/quicker of course. I don't want to code the whole thing from scratch though. What I want is what MS Access calls a tabular form in its design wizard and is also what it presents you with by default if you open a table directly. I need this to run under Linux as a desktop application or alternatively a web application would be fine, I have apache2 with PHP etc. running on my desktop machine here. I've searched around quite a lot for something that can do this for me but haven't found anything yet. There are lots of database management programs (such as sqlitebrowser and sqliteman) but while they do a good job of managing a database they're really not built for entering data. I just want an executable I can run with database name and table name as parameters, e.g.:- myDataEditor somedatabase.db tablexyz ... and it fires up a window with the rows/columns in a grid and allows me to edit. I don't want to have to navigate through menus and options to get there. Any ideas or suggestions would be very welcome. -- Chris Green ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] GROUP BY With ASC/DESC
Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? SQLite version 3.7.2 3.7.9. danap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] GROUP BY With ASC/DESC
Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? SQLite version 3.7.2 3.7.9. danap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY With ASC/DESC
Message: 23 Date: Fri, 11 Nov 2011 10:57:22 -0500 From: da...@dandymadeproductions.com To: sqlite-users@sqlite.org Subject: [sqlite] GROUP BY With ASC/DESC Message-ID: 5ed601b698a020a8d790240cc05c8714.squir...@dandymadeproductions.com Content-Type: text/plain;charset=iso-8859-1 Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? SQLite version 3.7.2 3.7.9. danap. -- Message: 24 Date: Fri, 11 Nov 2011 16:10:04 + From: Black, Michael (IS) michael.bla...@ngc.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] GROUP BY With ASC/DESC Message-ID: ABF72471-0160-4FBE-A249-5CFBC96DD19B@mimectl Content-Type: text/plain; charset=iso-8859-1 I think you want ORDER BY COLOR,NAME Since color is a primary key you can't have dups so what good is the group by? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems Currently I'm implementing an interface for the MyJSQLView SQL GUI to use the GROUP BY aspect of SQL statements. Upon testing with SQLite I received the above indicated exeception. So the context given is only an example with no predefined objective of meaningful results. Perhaps a more meanful example with the same results: Given: ROP TABLE IF EXISTS General_Expenses; CREATE TABLE General_Expenses ( id INTEGER NOT NULL, date date NOT NULL, company TEXT NOT NULL, record_type TEXT NOT NULL, payment_method TEXT NOT NULL, account TEXT NOT NULL, description TEXT, cost REAL NOT NULL, PRIMARY KEY (id) ); The query with GROUP BY account works fine, but if ASC or DESC included with the field GROUP BY an exception is given. (fine) SELECT date, company, account, Sum(cost) AS cost FROM General_Expenses WHERE date LIKE '2011%' GROUP BY account ORDER BY account ASC (exception) SELECT date, company, account, Sum(cost) AS cost FROM General_Expenses WHERE date LIKE '2011%' GROUP BY account ASC ORDER BY account ASC SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) SQLState: null VendorError: 0 Is this a bug, since the documentation indicates ASC and DESC can be used with GROUP BY statements? danap. Dana M. Proctor MyJSQLView Project Manager http://myjsqlview.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MyJSQLView Version 3.31 Released
MyJSQLView Version 3.31 Released The MyJSQLView project is pleased to release v3.31 to the public. Included with this update is several corrections for bugs involved with the import of large SQL dumps and table definition generation for unique keys. A multi-language PDF export inclusion is now implemented via unicode embedded fonts. The Query Frame in the application has now been expanded to allow input of direct SQL statements for execution. Dana M. Proctor MyJSQLView Project Manager http://dandymadeproductions.com/projects/MyJSQLView/ MyJSQLView provides an easy to use free Open Source Java based user interface frontend for viewing, adding, editing, or deleting entries in several mainstream databases including SQLite. A query frame allows the building of complex SQL statements. The application allows easy sorting, searching, and import/export of table data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MyJSQLView Version 3.30 Released
The MyJSQLView project has released a preliminary production version to address several reported problems with the application. Since this release is to correct these bugs a more fully vetted release will be forth coming by the end of the month or early next. The release Version 3.30 corrects the running of the application on specifically the Ubuntu Linux OS with the Gnome desktop. Because of the type of bug that was discovered it is possible this may effect all uses of the application on the Gnome desktop. The second issued addressed by the release is the lack to properly export PDF Summary Table data. A failure on the project to properly build the last release with an updated iText library file inclusion was the cause. Dana M. Proctor MyJSQLView Project Manager http://dandymadeproductions.com/projects/MyJSQLView/ MyJSQLView provides an easy to use open source Java based user interface frontend for viewing, adding, editing, or deleting entries in the HSQL, MySQL, Oracle, PostgreSQL, and SQLite databases. A query frame allows the building of complex SELECT SQL statements. The application allows easy sorting, searching, and import/export of table data. The MyJSQLView application uses the Xerial SQLiteJDBC to communicate with the SQLite database. http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC#WhatisdifferentfromZentussSQLiteJDBC ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
