[sqlite] Time Precision
The time precision treated with and defined, ISO-8601, seems to be with regard to seconds. Storage of an Integer for time as an example in SQLite: sqlite> SELECT STRFTIME('%s', 'now', 'localtime'); 1530446557 A 10 digit value. The issue I'm having is with regard to storage of time, in milliseconds, a 13 digit value. I would assume a more appropriate precision for a scientific community. Looking briefly at the c library for strftime() it does not seem to provide the possibility for a millisecond precision? STRFTIME('%ms'), Integer or TIME(D), 13 digits, Text. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> Bob wrote: > Affinity is only a hint and not an assurance of anything. > I solve the problem by bloating the schema with checks like this: > > foo integer default 1234 > check (typeof(foo) == 'integer'), > > This enforces that someone can't put "Hello world" where an integer > belongs. I don't get to control others schema in providing a generic gui for users. > Simon wrote: > The problem, as far as SQLite is concerned, is that the column > should have been declared "TEXT' not STRING. Then it works correctly: > SQLite version 3.22.0 2017-12-05 15:00:17 [...] > sqlite> CREATE TABLE a (col1 STRING); > sqlite> INSERT INTO a (col1) VALUES ("3.0"); > sqlite> SELECT * from a; > 3 > sqlite> DROP TABLE a; > sqlite> CREATE TABLE a (col1 TEXT); > sqlite> INSERT INTO a (col1) VALUES ("3.0"); > sqlite> SELECT * from a; > 3.0 > But course the dev team cannot correct the understanding of 'STRING' > for backward compatibility reasons. And using affinities rather than > types means that feeding a string into a numeric column does not > generate an error. So the programmer never figured out that using > 'STRING' as a type didn't do the right thing. > It's a problem with multiple causes. And it cannot be fixed in SQLite3. Nor should it! Ryan provided ample argument for a light DB engine that SQLite seems to satisfy for millions with FLEXIBILITY! If you wish a more constrained light RDBMS then H2, HSQL, and others are available. I'm just happy at least I get a column affinity for type checking to give some sanity. Thank you. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
"This flexible type-name arrangement works because SQLite is very forgiving about you putting non-proscribed values into columns - it tries to convert if it can do so without loss of information but if it cannot do a reversible type conversion it simply stores whatever you give it. Hence if you store a string '3456' into an INT column, it converts the string into an integer, but if you store a string 'xyzzy' in an INT column it will actually store the string value." > From: David Burgess This is an important feature of SQLite. In hindsight, an excellent decision. Unless your trying to create a generic user interface. I have spent the last month trying to solve affinity with the columns. The only way it seems to me to guarantee to solve the issue is to test every retrieved column value and test its affinity. The easier approach chosen just check the column affinity and assume thats what is going to be stored there. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 | 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 | 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] 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
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
[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"> To: "sqlite-users@sqlite.org" > Subject: [sqlite] GUI for SQLite > Message-ID: > 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
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] 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] 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