[sqlite] Sqlite incompatibility with Postgres

[da...@dandymadeproductions.com]

> I used to know SQL92 very well.  There's no facility for doing anything
> like LIMIT or OFFSET in it.  You had to use your programming language to
> work your way through all the results and skip the ones you didn't want.
>
> It is because there was no standard for this that each of the big SQL
> implementations came up with their own syntax, and even now they still
> haven't standardised on one.  I think the syntax SQLite implements is by
> far the simplest to understand, and I'm surprised that the other SQL
> engines haven't all implemented it as well as maintaining their own.
>
> Simon.

SQLite, MySQL, & PostgreSQL do share commonality in this area and
indeed there appears to descrepanices with LIMIT. Try building a
generic SQL Database access tool, one of the main areas of frustration,
Limits.

Select one row from database.

Derby:
SELECT * FROM schemaTableName FETCH FIRST ROW ONLY

HSQL: (Believe it does support post LIMIT now.)
SELECT LIMIT 0 1 * FROM schemaTableName

MSAccess: (Looks like no limit support.)
SELECT * FROM schemaTableName

MSSQL:
SELECT TOP 1 * FROM schemaTableName

MySQL, PostgreSQL, SQLite:
SELECT * FROM schemaTableName LIMIT 1

Oracle:
SELECT * FROM schemaTableName WHERE ROWNUM=1

danap.

[sqlite] Sqlite incompatibility with Postgres

[da...@dandymadeproductions.com]

> It's a minor point, but can someone confirm that:
>
> Postgres accepts (but Sqlite does not)
> LIMIT ALL
> LIMIT ALL OFFSET nnn
> OFFSET nnn
>
> Sqlite accepts (but Postgres does not):
> LIMIT -1 OFFSET nnn
> LIMIT -1
>
> These all have the same meaning of no limit, but there is no common
> ground in the syntax.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org

According to my testing, correct.

PostgreSQL
SQLException: ERROR: LIMIT must not be negative SQLState: 2201W
VendorError: 0

SQLite
SQLException: [SQLITE_ERROR] SQL error or missing database (near
"ALL":syntax error) SQLState: null VendorError: 1

SELECT * FROM key_table4 OFFSET 2
SQLException: [SQLITE_ERROR] SQL error or missing database (near "2":
syntax error) SQLState: null VendorError: 1

[sqlite] SQLite Pronunciation

[da...@dandymadeproductions.com]

> On 3/16/16, Daniel Telvock  wrote:
>
>> I was at the Investigative Reporters and Editors Conference last week
>> and the presenter for SQLite courses 1 and 3 said that it is actually
>> pronounced SQ Lite. Even he thought that was odd considering SQL is a
>> term or acronym.
>
> I wrote SQLite, and I think it should be pronounced "S-Q-L-ite".  Like
> a mineral.  But I'm cool with y'all pronouncing it any way you want. :-)
>
>
> --
> D. Richard Hipp
> drh at sqlite.org


Thank you all for the responses, video reference, and apologizes for
the origin post of pasting directly from the Wikepedia webpage, they
have (eskju:el'lait) or (si:kwel.lait).

I'm making some tutorial videos for my project and just wished to
appear to know slightly what I was talking about when using SQLite,
pronouncing, for the examples database connection for demonstration.

Dana Proctor

[sqlite] SQLite Pronunciation

[da...@dandymadeproductions.com]

Hello,

Please grant me some leeway here since as someone who has
not been in an academic school for years and is mainly self
taught. I have Mainly deriving information from reading
manuals and occasionally viewing some videos.

Maybe I'm wrong, but according to Wikepedia SQLite appears
to be pronounced the same has it is spelled,
(sikwl.lat).
Maybe not a long A there perhaps.

Where as I first heard Microsoft's MSSQL pronounce (sequent),
which I have also heard in academic videos by professors.
Following that logic, SQLite, (sequent.light)?

Dana Proctor

[sqlite] Exporting

[da...@dandymadeproductions.com]

> On 12/6/15, Thomas Morris  wrote:
>
>> I recently took over a Java based application that has SQLite embedded
>> and now I need to export the database into a CSV format.  Reading
>> online, I have found that using (dot)mode csv, or any (dot) command is
>> not possible when SQLite is embedded, simply due to the (dot) commands
>> being for use within the shell.  Is there an alternative way to export,
>> other than by using (dot) mode, or am I simply out of luck?
>>

> Hi Gunter,
>
> Where would I get the CSV module from?
>
> regards, Robert
>

Hello,

There has been a project over on Sourceforge that is a module
for CSV with a JDBC for years. Several other Java applications
are also out there that will do the work, including mine,
MyJSQLView.

Mine does not export BLOB, doesn't seem to make sense in that format
to me, do it in SQL instead. I may to look change that though, I'll
take a look.

Sourceforge: https://sourceforge.net/projects/csvjdbc/

MyJSQLView io last code:
http://code.google.com/p/myjsqlview/source/browse/trunk/myjsqlview/src/com/dandymadeproductions/myjsqlview/io

danap

[sqlite] Dont Repeat Yourself (DRY) and SQLite

[da...@dandymadeproductions.com]

> On 25 Nov 2015, at 2:23pm, Domingo Alvarez Duarte
 wrote:
>
>> This way we repeat the string everywhere then it's not DRY !
>
> You know, I think you're the first person to mention DRY here.  I had to
look it up.
>
> 
>
> For some reason it seems that under DRY repeating a string in lots of
places is bad,
> but repeating a number in lots of places is good.  I'm not sure about
the logic
> behind that.
>
> Okay, do this instead:
>
> CREATE TABLE enum_type (the_ID INTEGER PRIMARY KEY, the_value TEXT UNIQUE);
> INSERT INTO enum_type (the_value) VALUES ('simple'), ('tuple');
>
> CREATE TABLE use_mytype (...
>...
>one_type TEXT REFERENCES enum_type(theID) DEFAULT 2
> )
>
> It is now harder to know which value to insert for one_type.
>
> Simon.

Thank you. I have been just about completed a plugin for my tool,
MyJSQLView, that will convert a PostgreSQL database query to a
SQLite table, then transfer the data to the newly created memory
or file database table.

I left out ENUM types as a possible data type since it is relatively
new to PostgreSQL, well a year or few perhaps.

That should work nicely.

danap.

[sqlite] convert a PostgreSQL to sqlite

[da...@dandymadeproductions.com]

c.buhtz wrote:
> Is there a way (free, open source, Ubuntu) to convert a
> PostgreSQL-database with data in it to a sqlite database?

Hello,

1. Download the free open source Java application MyJSQLView.

   http://myjsqlview.com/download.html

2. Insure the PostgreSQL JDBC driver is installed in the class
   path, best just put in /java/jre/lib/ext/ directory of
   the installed OS.

   https://jdbc.postgresql.org/download.html

3. Open the PostgreSQL database.

   A. Export Database Scheme:
  Data | Export | SQL | Database Scheme

   At this point you can dump database tables individually
   in SQL or CSV or as a whole. Now depending on the tables
   size this can time consumming.

   B. Export Database SQL/CSV
  Data | Export | SQL | Table
  Data | Export | CSV | Table

4. Recreate the SQLite required tables from the exported
   PostgreSQL Database Scheme, from 3.A. Example datatype
   tables can help from the test directory of the MyJSQLView
   application.

5. Open the Desired clean SQLite database.

   A. Import your recreated SQL tables files derived from
  4.
  Data | Import | SQL Dump

   B. Import you data for each table derived from either
  3.B. SQL/CSV.
  Data | Import | SQL Dump/CSV File

As indicated by others if you have special PG data types,
arrays, GIS, others then you going to have to resolve the
conversion data. I have done this for recreating virtual
tables with other lite database, but not SQLite. That is
a plugin that is not included with the MyJSQLView application.
See below code for Data Type Conversion information.

 
http://code.google.com/p/myjsqlview/source/browse/trunk/myjsqlview/src/com/dandymadeproductions/myjsqlview/datasource/TypeID.java
 
http://code.google.com/p/myjsqlview/source/browse/trunk/myjsqlview/src/com/dandymadeproductions/myjsqlview/datasource/TypesInfoCache.java

A good part of this can be done as indicated by others with PostgreSQL
pgdump, but that usually gives a lot of other data related to recreating
a PostgreSQL database that is not going to apply to your liter SQLite
database. I would recommmend chunking this into bites rather then
trying to import all or nothing approach.

Dana M. Proctor
MyJSQLView Project Manager.

[sqlite] MyJSQLView Version 7.03 Released

[da...@dandymadeproductions.com]

The MyJSQLView project is pleased to release v7.03 to the public. The
release is to fix the plugin management tool proxy setting property.
Remote HTTP repositories can not be created or loaded. The release
also is for archiving purposes to finalize the last release before
the move of the code version control from Google Code to an alternative
private server.

Dana M. Proctor
MyJSQLView Project Manager
http://myjsqlview.com

MyJSQLView provides an 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 visually build queries, profile and
plot data for analysis.

[sqlite] MyJSQLView Version 7.02 Released

[da...@dandymadeproductions.com]

The MyJSQLView project is pleased to release v7.02 to the public. The
release is mainly oriented around bringing the application into compliance
with Java 1.7. In that vein considerable enthusiasm has been generated
in enabling the JavaFX framework for plugins. The graphic and media
enhancements in that framework has already brought work on some future
plugins to be released later this year. The v7.02 release also includes
an update for the support of the Oracle 11g database and the bundling
of a library for incorporating SQLite directly into the app.

Dana M. Proctor
MyJSQLView Project Manager
http://myjsqlview.org

MyJSQLView provides an 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 visually build queries, profile and
plot data for analysis.

[sqlite] PRAGMA index_list() SQLException

[da...@dandymadeproductions.com]

> On 2015-03-21 06:27 AM, danap at dandymadeproductions.com wrote:
>> Hello,
>>
>> PRAGMA index_list(table-name)
>>
>> In Sqlite's past history has PRAGMA index_list(table-name) returned a
>> SQLException indicating no ResultSet or a non-empty ResultSet that may
>> throw an exception on accessing the fields in the normal way?
>
> Nope, not in the past and not currently. (There are very many test
> suites that check for this and has to be passed before any release date).
>
> That specific pragma very often returns an empty result-set, not the
> usual case with other queries, so that might be worth testing your code
> for. Other than that it has much the same internal process than any
> other query.
>
> If you post some code, some people here are bound to use the same
> platform / wrapper / interface and may have gotten the same kind of
> thing before.
>
> Good luck,
> Ryan

Thank you for the response Ryan,

The issues I was having has been addressed by the latest code that I
found over at github, which does do the empty result-set test on PRAGMA
index_list() before returning results, getIndexInfo().

The project is for sqlite-jdbc that appears to have moved from bitbucket
to github. The latest bitbucket JAR file has issues and recommend anyone
using sqlite with Java make note to use the github code.

https://github.com/xerial/sqlite-jdbc

Dana Proctor
MyJSQLView Project Manager.

[sqlite] PRAGMA index_list() SQLException

[da...@dandymadeproductions.com]

Hello,

PRAGMA index_list(table-name)

In Sqlite's past history has PRAGMA index_list(table-name) returned a
SQLException indicating no ResultSet or a non-empty ResultSet that may
throw an exception on accessing the fields in the normal way?

{rs.getInt(1), rs.getString(2), rs.getInt(3)}

danap.