Re: [sqlite] Regarding CoC

[dmp]

Code Of Conduct, misplaced disposition on the individuals of
an organization rather than the results of their work on
intent.

I have had a simple statement with my open source software
downloads for years.

"Dandy Made Productions would like to assure individuals that
 any applications downloaded from this site are free from any
 malicious code as so created. Great pride is taken in trying
 to create ethical software that does not knowingly modify, or
 change files or a system's configuration beyond the user's
 request. In addition no software downloaded from this site
 performs any type of monitoring or reporting on the user's
 behavior in use of said application. Every reasonable attempt
 is made to maintain the integrity of the downloaded software
 packages at this site."

Dana M. Proctor

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[dmp]

> I have a bunch of hyperlinks in an SQLite database, and need to read each
> page for validation before deleting the record.
> To make it easier, the DB manager should launch the default web browser
when
> I double click on a column that contains a hyperlink.

> Before I build a GUI, is there a Windows SQLite database manager that
can do
> this?

Hello,

Though my application does not do what you desire it is a framework
for plugins. The GUI, database connection stuff and a tutorial for
creating plugins is available as open source. Seems a lot of work
to create a new GUI for the purpose stated.

The other response on creating a HTML file seems quicker.

http://ajqvue.com/documentation.html
http://ajqvue.com/docs/Plugins/PluginBasics/Ajqvue_PluginBasics.html
http://ajqvue.com/docs/Plugins/PluginAdvanced/Ajqvue_PluginAdvanced.html

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[dmp]

>> On Oct 9, 2018, at 7:19 PM, Warren Young  wrote:
>>
>> The arguments about mailing lists vs forums have all been had.

> It’s not clear why this entire conversation is presented in such stark
terms: LIST
> *OR* FORUM. Puzzling.
>
> If this is all driven by DRH edict that “all things shall run on fossil”
, then you
> know what to do: list2forum + forum2list
>
> There is no problem, if you don’t dogmatically insist on making it one.

Thank you. So far I have seen mostly only one insisted advocate
for a forum with no comment seems from the actually owner of this
list. If the owner wishes to transition to only a forum then please
just state it and get on with it; your idea seems better though.
Maybe then this thread will die.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ajqvue v2.10 Released

[dmp]

> Looks good.
>
> Would pronouncing ajqvue -- achoo? -- send Mr. Mxyzptlk back to the 5th
> dimension for 90 days?
> Ned

Perhaps if it was said three times while clicking your heels together.

Ajqvue - A Java Query Vue

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ajqvue v2.10 Released

[dmp]

Ajqvue Version 2.10 Released

The Ajqvue project is pleased to release v2.10 to the public. The release
is minor in nature with a new feature to allow opening local file databases
in the Login Frame via a file chooser. Work was also completed to support
the DB_to_FileMemoryDB plugin, package utilities.db, and update the
QueryBuilder plugin.

Dana M. Proctor
Ajqvue Project Manager
http://ajqvue.com
https://github.com/danap/ajqvue


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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB To DB Transfer Time

[dmp]

> Why aren't you measuring the time spent only in the SQLite writing thread?
>
> That would eliminate the overhead from the read-thread(s) and the queue in
> the middle,
> measuring only work by SQLite, instead of including the *waiting* for work
> in the queue.
>
> I wrote a similarly "piped" ETL not too long ago, and I output overall
> "wall" time of course,
> but also time spent reading, time spent writing, but important times when
> reader/writer threads
> were "stalled", because the pipe is full/empty (respectively). --DD

Hello,

Too the question, because it is unnecessary coding, time wasted. If I have
an idea already of the goal and with the timing overall can determine where
to focus the effort it is more efficient use of my time. The monitoring of
the pipe, one coding action, already gives me an idea of when read/writer
are stalled. So no need in to have timing for those.

Yesterday I put the threads on equal footing and derived the following
result which are close to my goal.

50,000 rows queried from a networked MariaDB, fields (integer, real, text,
blob).

SQLite - 114.838 seconds
H2 - 115.868 seconds
Derby - 136.984 seconds
HSQL - 1291.808 seconds

Mind you that the plugin needs to use any query to any of the supported
databases, MariaDB, Oracle, PostgreSQL, etc. and derive a comparable
schema table from the query to create a file/memory database.

Looks like SQLite or H2 are the most likely candidates at this time.

All of this is a lot of variables that effect timing. Focusing on only
the data transfer timing, above, or writes to the sink db is only part
of the timing, though probably the place to derive the most benefit.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB To DB Transfer Time

[dmp]

>> On Sep 25, 2018, at 11:14 AM, dmp  wrote:
>>
>> The result for the 50K file db test of SQLite was 370.184
>> seconds. Is this a reasonable transfer speed, given the
>> conditions noted?

> You haven't specified how much of that time was spent in SQLite. For all
we know,
> 370 seconds was spent in MariaDB and your own code, and it only took
.184 sec for
> SQLite to insert the rows :)

> Try running your code with a profiler. Or at least wrap some quick &
dirty timing
> code around your functions that call SQLite.

> Also, make sure to insert as many rows as possible within a transaction.
Committing
> a transaction in SQLite is quite expensive due to filesystem flushing.

> —Jens


Hello,

The DB to DB code is a Java plugin for my application. It takes
a query to the current database, converts that into a schema,
creates a new table in the selected sink database of a comparable
schema, then begins transferring data.

The data transfer is setup with two threads, the source database
thread loading data and the sink database thread inserting via
prepared statements in a batch mode. The threads are connected
via an ArrayBlockingQueue pipe. I log the pipe capacity as the
process takes place.

The timing given takes place at the beginning of the transfer
process and stops when completed, threads join.

Now from the users of the forum, I did some testing to establish
a reference transfer time. After some thought I realized I could
get that number by just performing a direct file transfer of
the created SQLite database file from the MariaDB server to the
client machine. Results 54 seconds.

Upon further testing, increasing the pipe size, 250 to 1000.
I was able to get the time for the SQLite test to 164.721 sec.
Average pipe capacity, 85 objects. That tells me that I could
do better perhaps using two loading threads. I already suspected
the loading thread filler to be slower and therefore had coded
it with an increased priority of one.

The testing so far shows SQLite comparable to the other tested
memory databases. SQLite beat out all but one with a file
database. One of the databases did miserable with the pipe
capacity close to saturated and a time of 1374.614 sec.

Testing:
Transfer 50,000 rows, (Integer, Real, 0-10k Text, 0-10k blob)
Resulting SQLite file database size: 404Mb.

Updated SQLite Log Database:
https://github.com/danap/db_to_filememorydb/blob/master/docs/dblog/db_To_db_Parameters-log.db

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB To DB Transfer Time

[dmp]

Hello,

Thank you for the responses, Stephen and Dominique.

I was generally looking for an idea if the transaction
time seemed reasonable, given a low end MariaDB server,
standard 100Mb LAN, and mid range user processing desktop
machine.

I realize that the question as answered is; Hard to say!
My initial reaction was slow, not good enough.

The log db, was created because of the parameters involved
in evaluating the transfer had many factors. This allowed
a way to track changes in those parameters with results.
It also gives additional insight to the testing hardware.
SQLite was one of four local file/memory databases evaluated.

https://github.com/danap/db_to_filememorydb/blob/master/docs/dblog/db_To_db_Parameters-log.db

Again thanks for responses. The idea of using transfers
with the SQLite built in tools, .dump, .backup if setup
with a similar scenario can provide some reference point
to answer my question.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DB To DB Transfer Time

[dmp]

Hello,

I have created a database to database transfer plugin for
my application. The plugin is functional and I have
collected a log of testing results stored in a SQLite db.
The links below are of the database and definition of
parameters fields. The main exception, dbTypeMemory,
meaning memory or file db.

https://github.com/danap/db_to_filememorydb/blob/master/docs/dblog/db_To_db_Parameters-log.db
https://github.com/danap/db_to_filememorydb/blob/master/lib/plugins/DB_To_FileMemoryDB/transfer-param.config

Testing was done with a 50K entry MariaDB table on a low
end local network machine. Four fields in the table, Integer,
Double, Text, and Blob. Text and Blob fields of arbitrary
length to 10K.

Resulting file database sizes for all 50K rows were in the
range of 390MB to 1.83GB.

The application is Java based and uses JDBCs.

The result for the 50K file db test of SQLite was 370.184
seconds. Is this a reasonable transfer speed, given the
conditions noted?

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best practices for forward conversion of database formats?

[dmp]

Randall wrote:
~
~
> Any application that involves a "persistent" database, i.e., one where
the data is
> long-lived and expected to exist and evolve over time, sooner or later
has the issue
> of moving customers from a V1 database to a V2 database.  Obviously at
least one
> technical issue (there are probably others) is how to convert existing
data from one
> DB format to another, given some arbitrary set of changes in the
database schema in
> the interim.

> Are there accepted or best practices in the industry for handling this
in general,
> or with SQLite in particular?  Can anyone who has implemented this make
useful
> suggestions?  Or are there published resources I am missing?

Hello,

I should suppose pretense with I'm wholly ignorant to such a process, but
even
has such may have some wisdom. The only reason I'm replying because I have
been
working on I guess an ORM, data converter it seems.

One, it seems data is best preserved in a standard raw format such as CSV.
SQL
statements from one database do not translate to another databases well and
perhaps of the same database v1 to v2.

Two, with changes to schema it seems the simple way to go would be export
data
in a query fashion, to match the new schema. Leave out columns not needed and
a default for new.

Three, this in hindsight, but data should be always be in it simplest form as
possible, Integer, Char, etc. The most intolerant data is temporal
information.
For these ALWAYS use a SQL or a common appropriate standard.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time Precision

[dmp]

Keith Medcalf wrote:
> Are you running Windows or Unix?  I am sending this to you as I was just
looking
> into this again and although SQLite maintains time internally with a
millisecond
> precision, the API used on Windows to read the time is limited by the Clock
> Resolution (usually about 16.5 ms).  If you are using Windows 8 or
later, then you
> can edit the SQLite3 amalgamation code (and/or the winfvs source) and
use the
> GetSystemTimePreciseAsFileTime call rather than the
GetSystemTimeAsFileTime call
> (around line 40866 in the aamalgamation code)

The time precision issue revolved around the lack of date, time, etc.
types in SQLite
and my Java application GUI using a JDBC. The java.sql.Time class is
defined as:

Time(long time)
Constructs a Time object using a milliseconds time value.

A JDBC setTime() or getTime() expects this long integer. Most databases I
would
assume stores a time type as a long intger with precision of milliseconds
even
though the ISO-8601 standard defines precsion to seconds.

The conversions using the datetime functions with SQLite as I was using them
did not give me milliseconds precsion. With a greater understanding of those
functions I was able to create the precision needed.

Seems it was one of the of the recommended solutions provided by you that
solved
my issues.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The upcoming alter table rename column

[dmp]

Still doesn't work when saving directly to file. Nice
though. Modified version follows. Thanks.

danap.

Fixes/Adds:

1. Header comment added --.
2. Added drop IF EXISTS.
3. Each CREATE VIEW added space after AS.
4. Placed end of statement semicolon directly after parenthesis.
5. Has only \n for end of lines.


--  Schema Info Views
--
-- This is a set of views that supply queryable Schema information for
-- SQLite DBs in
-- table format.
--

DROP VIEW IF EXISTS SysIndexColumns;
DROP VIEW IF EXISTS SysIndexes;
DROP VIEW IF EXISTS SysColumns;
DROP VIEW IF EXISTS SysObjects;

CREATE VIEW SysObjects AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE
   FROM (SELECT type AS ObjectType, name AS ObjectName
   FROM sqlite_master
  WHERE type IN ('table', 'view', 'index')
);

CREATE VIEW SysColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, ColumnID
COLLATE NOCASE,
ColumnName COLLATE NOCASE, Type COLLATE NOCASE, Affinity COLLATE
NOCASE,
IsNotNull, DefaultValue, IsPrimaryKey
   FROM (SELECT ObjectType, ObjectName, cid AS ColumnID, name AS
ColumnName, type AS Type,
CASE
  WHEN trim(type) = '' THEN 'Blob'
  WHEN instr(UPPER(type),'INT' )>0 THEN 'Integer'
  WHEN instr(UPPER(type),'CLOB')>0 THEN 'Text'
  WHEN instr(UPPER(type),'CHAR')>0 THEN 'Text'
  WHEN instr(UPPER(type),'TEXT')>0 THEN 'Text'
  WHEN instr(UPPER(type),'BLOB')>0 THEN 'Blob'
  WHEN instr(UPPER(type),'REAL')>0 THEN 'Real'
  WHEN instr(UPPER(type),'FLOA')>0 THEN 'Real'
  WHEN instr(UPPER(type),'DOUB')>0 THEN 'Real'
  ELSE 'Numeric'
END AS Affinity,
"notnull" AS IsNotNull, dflt_value as DefaultValue, pk
AS IsPrimaryKey
   FROM SysObjects
   JOIN pragma_table_info(ObjectName)
);

CREATE VIEW SysIndexes AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
IndexID, IsUnique COLLATE NOCASE, IndexOrigin COLLATE NOCASE,
isPartialIndex
   FROM (SELECT ObjectType,ObjectName,name AS IndexName, seq AS IndexID,
"unique" AS isUnique, origin AS IndexOrigin, partial AS
isPartialIndex
   FROM SysObjects
   JOIN pragma_index_list(ObjectName)
);

CREATE VIEW SysIndexColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
IndexColumnSequence, ColumnID, ColumnName COLLATE NOCASE,
isDescendingOrder, Collation, isPartOfKey
   FROM (SELECT ObjectType, ObjectName, IndexName, seqno AS
IndexColumnSequence, cid AS ColumnID,
name AS ColumnName, "desc" AS isDescendingOrder, coll AS
Collation, key AS isPartOfKey
   FROM SysIndexes
   JOIN pragma_index_xinfo(IndexName)
);

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ajqvue Version 2.0 Released

[dmp]

The Ajqvue project is pleased to release v2.0 to the public. The release
is a major maintenance, cleanup, and consolidation of the code base. In
part most of the work was driven by a DB_to_FileMemoryDB plugin and insuring
a more robust handling of SQLite affinity. The sqlite-jdbc library has again
been included with the application. All libraries have been updated. The
plugin DataCharts included with Ajqvue has been updated with JFreeChart,
1.5.0.

Dana M. Proctor
Ajqvue Project Manager
http://ajqvue.com
https://github.com/danap/ajqvue


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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite .dump

[dmp]

Simon Slavin wrote:
> I'm sorry, but I don't see a question in your post.

As intended. It was just a statement based on observation,
with regard to SQLite .dump, my GUI dump, and other database
dump outputs. MySQL also uses a short version without
specifying column names, but does quotes identifiers.

Warren Young wrote:
> and using the database's
> identifier quoting character.

> It does that at need already:

> sqlite> create table "x y" ("a b" INTEGER);
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE IF NOT EXISTS "x y" ("a b" INTEGER);
> COMMIT;

Seems only when the initial dll specified.

sqlite> create table x (a INTEGER);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x (a INTEGER);
COMMIT;

There is no intention to indicate a bug, or other aspect
about a deficiency in SQLite .dump.

The reason my tool provides columns is because the
dump is made to allow users to selectively save data
from only the specified columns.

The reason I always quote identifiers, is because people
do stuff like this for names, "keY_cOlumn2".

If that was not quoted in dml then it might be interpreted
as KEY_COLUMN2 for example in some databases, and then throw
an error.

NO SUCH COLUMN.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite .dump

[dmp]

Hello,

Recently in testing my GUI tool I made a comparison from the
tool's dump and SQLite's command line .dump tool.

Seems .dump uses a short output of skipping the column names.
According to some of my research for various databases I use
one of these as options for SQL dump output:

http://ajqvue.com/docs/Manual/Ajqvue_Manual.html#Preferences_Data_Export_SQL
Preferences Data Export SQL

Always specifying the column names list and using the database's
identifier quoting character.

danap.

===
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE datatypes (data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
int_type INTEGER, real_type REAL, text_type TEXT,
blob_type BLOB, date_type DATE, time_type TIME,
datetime_type DATETIME, timestamp_type TIMESTAMP);

INSERT INTO datatypes VALUES(1,100,200.18864,'some text',
 X'6162630a',153051120,68715000,1530343358000,
 1530559371079);

===
GUI tool:
--
-- Dumping data for table "datatypes"
--

INSERT INTO "datatypes" ("data_type_id", "int_type", "real_type",
"text_type",
 "blob_type", "date_type", "time_type",
"datetime_type",
 "timestamp_type") VALUES(1, 100, 200.2, 'some text',
 x'6162630a', 153051120, 68715000, 1530343358000,
 1530559371079);

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[dmp]

On 7/12/18, danap wrote:
>> I use a dump
>> in my interface which I used with diff to compare changes in my
>> personal expense database. This was to insure changes introduced in work
>> on the interface were not screwing things up. Very helpful to insure
>> your not introducing bugs.

D. Richard Hipp wrote:
> I am glad that has been working for you.  But there is a caveat:  The
> ".dump" format can (and does) change slightly from one release of
> SQLite to the next.  So you are welcomed to continue using ".dump"
> this way, but just be careful that you do not compare the .dump output
> from two different versions of SQLite.

Well, sorry Mr. Hipp, but the dump was from my own SQLite GUI. So what
I was doing is insuring some coding changes to the GUI's dump had not
changed anything, by comparing to prior dumps of data with the same
tool. So I was really doing exactly what you caution about to debug
between versions.

Idea being, dump ASCII data, compare over time with some kind of diff
tool to see changes. So to user's question, be sure to use same version
of the dump tool as you indicate.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

[dmp]

Randall wrote:

> My wishlist is:
> (o) Allow humans to view the contents of a DB without custom tools.

If what is meant here is a generic tool that opens/views any particular
file format, db context here, then there are tools including
the generic db gui that I have been working on for years.

> (o) Have a way to see what has changed between V1 and V2 of a database,
> e.g., for a "change review."
> (o) Have a way to merge two independent sets of database changes into
> a single result in an understandable way.

This has already been answered, .dump diff and sqldiff. I use a dump
in my interface which I used with diff to compare changes in my
personal expense database. This was to insure changes introduced in work
on the interface were not screwing things up. Very helpful to insure
your not introducing bugs.

> (o) Have a way to make changes (update, insert, delete) to the DB data
  in a  pinch without specialized tools.

My generic db gui will do all these. Once more it really is a plugin
framework so it is very easy to introduce your own code to extend its
behavior.

Seems parts of the wishlist could be provided more effectly by client/server
db rather than SQLite. My personal general expenses database is not
SQLite, but a client/server db. Just an old box back in the corner. Why,
because I use the same db server with my dad's, in his 90s, expenses which
we both can add, edit, search, review, and aggregate accounts at the
end of the year.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Subject: Re: Date Search

[dmp]

Keith wrote:
> Correct.
> You have stored integer Unix Epoch timestamps.  You cannot do "string"
> searches on integers (at least not ones like what you have asked for,
> which involves conversion of an integer representing a Unix Epoch offset
> to an ISO-8601 string, not to an ordinary "string representation of
> the integer".
>
> There is no "DATE" type in SQLite3 -- only INTEGER, REAL, TEXT, and
> BLOB -- and no magical conversion of integers into ISO-8601 strings
> or v/v.
> ~
> ~
> If you want your date_type integer to be converted to a date string,
> you need to use the function for converting integer unix epoch offsets
> into ISO-8601 date strings:
>
> SELECT id, date_type FROM date_types WHERE date(date_type, 'unixepoch')
> LIKE '2018-%-%';
>
> The usual caveats apply for knowing what your timezone is and handling
> such conversions appropriately for your platform (OS).

WHERE date(date_type, 'unixepoch') seems the way to go and will now focus
on that as a solution.

danap.

David Raymond wrote:
> You have to make everything the same type, either numeric or text.
> ~
> ~

R Smith:
> Why not add to the table both the converted Integer date[i.e:
> strftime('%s', '2017-01-01')] AND the ISO8601 date [i.e: '2017-01-01
> 00:00:00']?
>
> That way you can reference either, use any of them for calculations, and
> filter/lookup by whichever one suits the occasion best.
> ~
> ~

Thanks for the repies all. David and R. that would be nice, but the context
here is with a GUI for users so I do not get to define db schema.

I do actually have a test data types table that is loaded with Date data
with both Integer and Text content. Along with also Time, Datetime,
and Timestamp.

Yes I know all those could be Real or a Blob, but I'm trying to pick the
most likely content that users would store for those types. Integer and
Text seem the most appropriate.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time Precision

[dmp]

danap wrote:
> SELECT CAST((SELECT (julianday('now', 'localtime') -
> julianday('1970-01-01'))*24*60*60*1000) AS INTEGER);

Keith wrote:
> Are you sure you want to be mixing up timezones?
>
> julianday('1970-01-01') returns the julianday timestamp
> for 1970-01-01 00:00:00 GMT julianday('now', 'localtime')
> returns the julianday timestamp for 'now' in your
> current timezone.

No. You are correct and after my initial posting reply, above, fixed
the Time Precision to your initial suggested example, 'now', only in
the code.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Date Search

[dmp]

Given:

SELECT STRFTIME('%s', '2018-01-01');
1514764800
SELECT STRFTIME('%s', '2017-01-01');
1483228800

CREATE TABLE date_types(
  id INTEGER PRIMARY KEY,
  date_type DATE
);

INSERT INTO "date_types" ("id", "date_type") VALUES(1, 1514764800);
INSERT INTO "date_types" ("id", "date_type") VALUES(2, 1483228800);

date_type: Affinity now Integer.

This does not seem to be possible, without some type of conversion to a
Integer(s)
value(s) then searching in a numeric range for the given TEXT date?

SELECT "id", "date_type" FROM "date_types" WHERE "date_type" LIKE '2018-%-%';

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time Precision

[dmp]

> Igor wrote:
> select (julianday('now') - julianday('1970-01-01'))*24*60*60*1000

> Keith wrote:
> select (julianday() - 2440587.5) * 86400.0

Both of these got me on my way, Igor's a little more clearer. I'll
doing a little more checking to insure the solution below is correct,
but seems good. Thanks.

danap.

Solution:

SELECT CAST(
(SELECT (julianday('now', 'localtime') -
julianday('1970-01-01'))*24*60*60*1000)
AS INTEGER);

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Back on-line. Was: Mailing list shutting down...

[dmp]

> Mailing lists are now back on-line and once again accepting
> subscriptions.  I have implemented measures to block the subscription
> robots and to better log subscription activity to better detect future
> mischief.

> I consider this to be a stop-gap measure that will buy me some time
> to implement and test a better log-term solution. .

> D. Richard Hipp
> d...@sqlite.org

As a comment, again with past post with regard to Mailing List.

This mailing list is a very informative, simple, and a conveniant
method to disperse information in a bulk format. A change to a web
interface, (forum, other), that requires a login each day is most
likely going to push me away.

Hope a fix can be accomplished.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subject: Re: SQL Date Import

[dmp]

> SQLite doesn't have a DATE type.  You can store dates in a SQLite
> database as text, or integers or floating point numbers (e.g. "20180602",
> a number of days, a number of seconds).  But when you ask for a value,
> that's what you'll get back.  Any interpretation of that value as a
> date is up to you or your software.

> Simon.

That is the real crux of the situation, I have a handle on how to display
DATEs whether they are NUMERIC or TEXT. Users really gather no meaning
from temporal values as numbers.

The real issue I suppose after some thought is the export/import of a
DATE, TIME, TIMESTAMP. Most Databases for both SQL, INSERT example,
and CSV is done with TEXT. Example being standard SQL DATE, -MM-DD.

I think that the case is to preserve the users data in the type they
have chosen for SQL export, have to test each entry for NUMERIC or TEXT.

The import of the SQL INSERT statements will return the database entries
to the same data. I have no control on those since they are passed
directly to SQLite.

CSV export will be TEXT, since that is the most likely way a spreadsheet
will recognize the data.

I will have to test probably import of CSV for NUMERIC or TEXT and not
just assume TEXT. A determination can be made of the typeof() for the
DATE field as NUMERIC or TEXT then transition the data accordingly if
need be.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Subject: Re: SQL Date Import

[dmp]

Keith Medcalf wrote:
> Yes, and the database will store the data as entered/bound if it
> cannot be converted to the requested storage type (column affinity).

Yes, that was my understanding and there-lies the problem. A column
of type DATE, TIME, or TIMESTAMP that may have NUMERIC and TEXT data.

> This is VERY IMPORTANT for you to understand fully and completely
> including all the rules for storage class and affinity conversions
> and how they are determined.  I would recommend that you SAY what
> you mean, and not confuse things (including yourself) by using
> "prayerful" data type declarations that are NOT in the proper set
> (INTEGER / REAL / NUMERIC / TEXT / BLOB).

Unfortunately as a middle man between a database designer and a user,
who maybe the table creator, I do not get to decide what they define
and what they put in a table, a combination of types for Date perhaps,
or maybe just TEXT. The example given INSERT could give you the former.

By the way, most databases give exactly that INSERT when dumping data
for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
type for storage here.

Thank you for the response. I think I will go ahead and install SQLite
on a machine so that I can experiment some more directly via the command
line, before deciding on an action to take on the issues I'm having.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL Date Import

[dmp]

Currently I do not have SQLite setup to run on its own on a computer.
I figured the answer to my question would be faster if it was posed
to this list.

Given:

CREATE TABLE exdate (
  id INTEGER,
  mydate DATE NOT NULL,
  PRIMARY KEY (id)
);

Once a number, numeric, is stored. Can a command line import
in standard SQL for DATEs be done, if at all?

INSERT INTO exdate (id, mydate) VALUES(1, '2018-06-01');

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[dmp]

1. Define in documentation as < 1Mb. (Don't have to visit again.)

2. Continue to strive to keep in the 0.5-1MB range.

3. Add some information on building a MINIMUM size for those
   concerned that is relatively easy to accomplish without
   a lot of expertise if possible.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[dmp]

Scott Robinson wrote:

> What version of SQLite are you using for this? I just did the
> following and do not see the string quoted values you are describing:

I'm sorry, the example given was just the current one I was
using to test the fix. In general I found the application was
quoting all Numbers and I was WRONG about the meta data stored
in the db file. It seems in almost all cases Sqlite is doing
a very good job of storing meta data I think.

Too clarify my application is coded in Java and is using a JDBC.

The example belows indicates that even when a mistake is made for
instance in declaring a type of TINYIN, the SQL type stored or
derived from the JDBC for the meta data is, 4, INTEGER. Good
guess!

CREATE TABLE exnumeric(
weight TINYNT NOT NULL,
speed DOUBLE);

NameJava Class   SQLTypeSQL Type Name
weight  java.lang.Object4   TINYNT
speed   java.lang.Object8   DOUBLE

In general if a table type is declared outside the defined SQL
types it still, for example (weight, kilograms), is defined as
TEXT it seems.

The issue I was having had to do with keying off only the Java
Class which in all cases is java.lang.Object. Most other databases
return for example the speed column, as java.lang.Double
from a Java ResultSetMetaData.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[dmp]

Suppose outside the subject of this thread, but in the document.


Sustainability factors

Self-documentation:
"The database format incorporates technical and structural metadata
 needed to interpret and manipulate the data itself. For example,
 a database file will include the CREATE TABLE declarations that
 define tables and columns. To the extent that meaningful names are
 used for tables and columns, the nature and context of the data
 may be recorded. However, there is no explicit structure within
 the file for storing fuller descriptive and contextual metadata.
 Nor is there a capability to embed in the file a metadata object
 conforming to a schema outside the SQLite specification."


This constantly bites me. This morning I had to generate a fix
to correct context of exported SQL statements for a dump of data.

The numeric values were being quoted as strings so therefore when
imported back in, they would have been treated as strings instead of
numbers.

DROP TABLE IF EXISTS mySinkDBTable;
CREATE TABLE mySinkDBTable (
key_id1 INTEGER UNSIGNED NOT NULL,
key_id2 INTEGER UNSIGNED NOT NULL,
text VARCHAR
);

--
-- Dumping data for table mySinkDBTable
--

INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES('1', '8', '51');
Corrected:
INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES(1, 8, '51');

Since the user is allowed to store the metadata for the table
types, example above, it is difficult for tools too determine
the proper processing for the data. I understand the flexibility,
and perhaps typeof() would solve most of my issues, but it would
be nice to have metadata field type stored as INTEGER, REAL,
NONE, TEXT, or BLOB.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 125, Issue 28

[dmp]

> I tried to create a shareable in-memory database as per the documentation
>  provided on SQLite  Site. But I end up finding the solution to the
> problem.
>
> *Here is my code in C#*:
>
>
> var connectionString = "Data
> Source=sharedmemdb;Mode=Memory;Cache=Shared";
>
>
> using (var connection1 = new SQLiteConnection(connectionString)) {
> connection1.Open();

I know nothing of C# or the argument requirements for SQLiteConnection(),
but did you try:

var connectionString = "Data Source=:memory:;Mode=Memory;Cache=Shared";

or

var connectionString = "Data Source=file::memory:;Mode=Memory;Cache=Shared";

per the documentation.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Handle BigInt

[dmp]

> Also note that if you store your numbers as strings, indexes on those
> values will order them as strings.  In other words, searching and sorting
> will work incorrectly.

> It should be possible to get your numbers from a Java numeric variable to
> a database numeric value without passing them through a string at any
> point.  And, of course, back out of the database into a numeric variable.
> If your database library does not allow this, you have a serious problem.
>
>
> As a solution purely about SQLite, SQLite has a "black box" type of BLOB.
> BLOB is used to store bytes, without putting any interpretation on those
> bytes.  Although technically you can search and sort BLOBs, it's probably
> a sign of faulty thinking.  If I was trying to store something in a
> database which I didn't want interpreted in any way, I'd use a BLOB.
>
> Simon.

Hello,

Since the purpose of the code is to replicate a database SQL query
to a memory/file SQLite database then it seems appropriate to maintain
the integrity of the fields as closely as possible.

I shall look at treating the fields for numbers as a generic numeric
variable in Java. The original code derived from obtaining input from
a user, so therefore strings, and parsing to check for valid input
before storing.

PostgreSQL max/min BigInt are fitting in fine as SQLite's Integer.

Thank you for comments.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Handle BigInt

[dmp]

> SQLite integers are all 64 bit - I don't about postgress, so unless
> postgress allows integers bigger than 64 bit, and you use them, you should
> be OK with your table definitions above.

> Paul

Hello,

That really provides insight to the real issue, I was having and so therefore
the question.

Since I'm using Java and JDBC I was retrieving numeric fields in PostgreSQL
with getString(), handles all, then using Integer.parseInt(stringValue) for
BigInts in storing to SQLite.

There lies the problem since BigInt values were exceeding the range of
Java Integer. My original solution was to store these as strings, will now
just use Long.parseLong(stringValue) and store has SQLite Integers
properly.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to Handle BigInt

[dmp]

Hello,

Given a conversion from a database table that contains BigInt, long,
field from PostgreSQL to a SQLite similar table.

CREATE TABLE postgresqltypes (
  data_type_id serial,
  bigInt_type bigint)

CREATE TABLE sqlitetypes (
  data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
  int_type INTEGER)

How to store, for values outside range for Integer,
String or Real?

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC driver experience

[dmp]

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?

> You can post on this mailing list or send your reply directly to me.

1. Which JDBC are you using?

Current Xerial SQLite JDBC
https://github.com/xerial/sqlite-jdbc

Others that I have tried are a Werner-JDBC and Zentus-JDBC. The
Xerial if I remember was derived from one of those. The most
current functional up to date is I believe Xerial.

2. For how long have you been using it?

My Java project, Ajqvue, began support for SQLite in 2014 and
that is when most of the research was done on finding a decent
functional JDBC.

3. What issues you had with this driver?

Ajqvue is a generic GUI for connecting to several databases to
include SQLite. That means I need to connect to a database and
display any table data and provide functionality for import/export
of data. The application also comes with plotting, analysis,
and profiling capabilities.

SQLite was one of the easiest database to support because it
only has four data types. Most of all my problems with JDBCs
have arrive with data types, mainly temporal ones, Date, Time,
and the dreaded Timestamp. Currently that is one of the main
issues I have with the current Xerial JDBC.

https://github.com/danap/ajqvue/issues/2

See:

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

Overall:
  * Does anyone respond to issues? :) Thats about it!

4. What advise do you have for avoiding problems in this driver?

Since my main experience with a driver is through a user GUI
and I'm not using in production I'm sure others could answer
this better. Currently I trying to complete a plugin for Ajqvue
that transfers data from others databases to SQLite. So I'm
sure I may come across some other discrepancies.

https://github.com/danap/db_to_filememorydb

From my perspective your database table schemas should be
based on the SQLite four data types, NO others. Of course
since I was instructed in structured programmer, I really
don't think a specified data type, can be any desired object
and the database really doesn't care. It really confuses me!

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Free tool to visualize a schema?

[dmp]

> Hi,
> I think the visualization of a schema helps to develop a sqlite database.
>
> I am searching for a free software, like SchemaCrawler.
> It is good, but can't show CONSTRAINT and FOREIGN KEY names in the
> diagram.
>
> I just tried out the Sqleton but it can't be start on my system so far.
> Do you uses such a free tool?
>
> Best, Pali

Hello Pali,

Ajqvue,
A free open source database access tool.
http://ajqvue.com/

It has a visual QueryBuilder, plugin.
http://ajqvue.com/plugins.html
http://ajqvue.com/images/screenshots/querybuilder/querybuilder_diagram.html

The application is in Java, and uses a JDBC to access a SQLite
database, file. At this time I no longer bundle a JDBC, with the
project for SQLite. One can be found though at:

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

Place its Jar in your jre/lib/ext folder and Ajqvue will have
access to it. I have not tried this current library and usually
build my own.

Dana M. Proctor
Ajquve Project Manager

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[dmp]

Well I think some have pointed out the issues with a online web
forum, logging in, lack of email notifications?

Mailing lists are one aspect of the Internet that in the last
25yrs has not disappointed me. Keeping the mailing list seems
to work or the irc option perhaps.

With that being said I would really like the Internet too take
a major change, to a model that is more distributed instead of
web server oriented. Something more like Steam.

With that in mind I have been working on a framework, and I'm
now trying to come up with a mechanism for I guess might be called
a forum of some type. One way or another I will implement something
and would be willing to work/experiment on this for perhaps a
solution that might meet communications needs of the list.

Dana Proctor
http://dandymadeproductions.com/projects/lindyFrame/lindyFrame_about.html

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to develop a GUI front-end

[dmp]

> Date: Mon, 13 Nov 2017 15:54:42 -0600
> From: Balaji Ramanathan 
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Best way to develop a GUI front-end
>
> Hi,
>
> I have been using SQLite without any programming language so far.  I
> maintain the data in 
> ~
> ~
> ~
> Is there a third party free tool like MS Access that would allow me to
> connect to a SQLite db in the back-end and enable me to create a custom
> front-end to it with forms and reports?  All my searches for this kind of
>  tool only lead me to tools like SqliteStudio, which is a GUI front end
> for SQLite, but not a programmable one like I want.  If anyone can point
> me towards a programmable GUI front-end development tool (preferrably
> open-source, but just free and well-maintained is sufficient), I would
> appreciate it greatly.
>
> Thank you very much.
> Balaji Ramanathan

Hello,

I have several times posted, indicating the release of my database GUI,
Ajqvue. It is a well maintained, mature open source project that was
started in 2005 then renamed in 2016, because of a cease-desist from
Oracle.

The project language is in Java and was started on Sourceforge and is
now hosted on GitHub.

https://github.com/danap/ajqvue

Ajqvue is a framework that so happens to have as its main plugin a
database interface. The interface is more in line with a spreadsheet
application rather than a tree db/table selector display, with a manual
query entry form. The database interface is JDBC, which most of the
databases have available. For SQLite I have been using:

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

Ajqvue comes with several additional plugins for analysis, and plotting.
I believe the most versatile aspect of Ajqvue though is the ability
through the Query Bucket to save composed SQL queries. Those queries
can then be dragged/dropped into plugins to allow data processing.

A plugin of a custom form should be relatively easy to create if you
know Java. There is a tutorial available on the documentation page
of the site along with videos demonstrating the desktop application.
Once a plugin is created it can be sourced locally, LAN, or on the
Internet.

If you do create a plugin GUI I recommend Swing over JavaFX. I have
had issues on various, Linux platforms with JavaFX and just switched
the charting plugin from JavaFX to JFreeChart.

Dana Proctor
Ajqvue Project Manager
http://ajqvue.com


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Port/ODBC Driver Question

[dmp]

Hello Vishal,

As far as I know the Java version of the Werner javasqlite
driver does not use a port and the odbc may not also.

I have use this Java driver, but a more current active
driver project for Java is at GitHub:

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

As indicated SQLite is local file system database. Though
I have tested the sqlite-jdbc driver on Win with a mapped
network drive. The stability of that mode of operation is
questionable, as can be searched on this forum's discussions.

danap.

> Hi,
> Am trying to open a firewall to the machine having sqlite database. Does
the SQLite
> database use a specific port number ? If not, then does the ODBC
connection to
> SQLite using ODBC driver use a port ?

> Any help will be greatly appreciated.

> SQLite ODBC Driver:
> http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe

> Regards,
> Vishal Shukla

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Subject: Re: sqlite with Java

[dmp]

> From: Sylvain Pointeau
>
> Why don't you provide an up-to-date version? this lib seems dead when we
> look at the website, also why don't you put the sources on git it would be
>  easier to contribute or raise a bug if any...
>
> --

I first supported SQLite in my project Ajqvue in 2010 and started out
with Werner's library. At some point I moved on to the Github xerial
sqlite-jdbc mainly for that reason, was being updated.

I can think of no real reason other than that. Maybe in the documentation
something about the Pure Java Mode.

There should be no problem between the H2 -> SQlite, Ajqvue supports
both, though H2 has the more convention database Data Types. There are
some SQL statement that H2 supports and not SQLite. Been awhile since
reviewed might not be so now.

Data Type Conversions:
https://github.com/danap/ajqvue/blob/master/src/com/dandymadeproductions/ajqvue/datasource/TypesInfoCache.java

SQL Statements See: Table 1.
http://ajqvue.com/docs/Manual/Ajqvue_Manual.html

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] confused getting started

[dmp]

> I'm not a computer novice, but also not a nerd/geek.
> Been using PCs since the mid 1980s and have used dBase III+ and Approach
> previously.
>
> I can't seem to grasp what I need to download / install to use SQLite?
>
> Can someone guide me through the process?  Looking to use it with
> RootsMagic.
>
> Thank you.
> John

Hello John,

If you just wish to quickly get access to a SQLite database file
then perhaps Ajqvue may help. I have had no problem with accessing
non-password protected files, such as places.sqlite that Firefox
uses. An encrypted database file though may not work with Aqjvue.

See the Quick Tutorial and use the default example database,
Site | LocalHost_SQLite | test/sqlite_db/factbook.db, to get
started.

http://ajqvue.com/
http://ajqvue.com/docs/Videos/Ajqvue_Quick.mp4

The command line tool as Simon indicated is much more powerful
and can then be explored to futher your goals.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[dmp]

> I'm still trying to figure out a graceful way to implement
> a "scratch" database table that has the following properties:

> o Can be created on demand while a database connection is open.
> o Can include references to other material in the database to
>   ensure integrity.
> o Will be automatically deleted when the database is closed.
>
> TEMPORARY tables can't do the second bullet, as I understand it.
>
> Right now I have application code that manages such tables,
> but it seems clunky and error-prone.  Am I missing any SQLite
>  feature that would let me do this more easily?
>
> Thanks for any suggestions.
>
> Randall.

I have a Java program that is used as a plugin for my application
that can before a query on a database that then creates a mem/file
database of the data. Not sure if it would meet point 2. Still
needs some work because it works with other databases also, but
the SQLite to SQLite should be fine.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Tutorial error

[dmp]

> Message: 21
> Date: Wed, 4 Jan 2017 22:10:59 -0600
> From: Ken Wagner 
> To: SQLite mailing list 
> Subject: Re: [sqlite] SQLite3 Tutorial error

> Yes, I am beginning to understand that. SQLite3 is its own GLOB standard.

> I will abide by that.

> It is just very confusing when 5 other apps using SQLite as their DB
> engine all report the opposite.
>  The SQLite versions they use are 3.9.2, 3.10.1, 3.11.0 and 3.13.0.

> Example: the SQLite Manager in FireFox 50 uses SQLite 3.13.0 and
> faithfully removes the names with digits in them.

I tried the example from R. Smith and got the exact same results
in my own SQLite Interface, Ajqvue, using the SQLITE JDBC.

I don't know what those other apps may be doing, but they should
just pass the query created by the user "DIRECTLY" to the SQLite DB
engine without any modification. Likewise the result should also
not be mucked with before presentation.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ajqvue 1.11 Released

[dmp]

The Ajqvue project is pleased to release v1.11 to the public. The
release is a minor one to highlight an update to the QueryBuilder
plugin. In addition to the QueryBuilder plugin the application
comes with by the HeatMapper, JavaFX Charts, and Table Field
Profiler plugins. The SQLite JDBC is an included library along
with the World Factbook database.

Dana M. Proctor
Ajqvue Project Manager
http://ajqvue.com

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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ajqvue Version 1.10 Released

[dmp]

Ajqvue Version 1.10 Released

The Ajqvue project is pleased to release v1.10 to the public. The
release marks a complete code review and cleanup. Updated libraries have
also been included with this release along with the Table Field Profiler
plugin. There have been many fixes in this release that stabilizes the
application.

The Ajqvue application is the recent result of replacing a 11 year old
Open Source program that has been challenged by a major player in the
database market for trademark infringement. Unable to defend the project
from such a mammoth that project is now dead, and replaced with Ajqvue.
As a result much of the old project as now been consolidated and resulted
in a much cleaner code base and website. An overview video of the
capabilities of the application is available at the link below. Ajqvue
uses the SQLite JDBC Driver and is included as a library with the
application.

http://ajqvue.com/docs/Videos/Ajqvue_Overview.mp4
https://github.com/xerial/sqlite-jdbc

Dana M. Proctor
Ajqvue Project Manager
http://ajqvue.com

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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MyJSQLView Version 7.08 Released

[dmp]

MyJSQLView Version 7.08 Released

The MyJSQLView project is pleased to release v7.08 to the public. The
release marks a complete code review and cleanup. Updated libraries have
also been included with this release along with the Table Field Profiler
plugin. There have been many fixes in this release that stabilizes the
application.

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

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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

[dmp]

I'm not interested in mobile browsing, just that the site is
still acceptable after changes for desktop. I use unconventional
browsers included one of my own design.

The draft site looks and works fine, is fast for loads, doesn't
seem to require scripting, GOOD!

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL to SQLite

[dmp]

> Thanks Dana,

> I think you're talking about doing what I did, but I'm not 100% sure.

> Below is my solution.  Could you let me know if yours is the same or
> something more elegant?

Hello,

I'm not sure of the programming language, VBasic, ?, but generally
looking through it looks like essentially what I indicated and my code
does.

So to summarize your options, others indicated also.

Note: 1. & 2. imply different source database.

1. Dump the data, CSV/SQL, format from the source database,
   then import into the new destination SQLite database.
   Somewhat easy, but manual so slow. Could code the import
   export together to improve efficiency.

2. Do a database to database transfer, much harder, to get
   right, mainly because the data type conversions. Looks
   like you code is taking into account and SQLMate could
   help. Relatively fast.

3. If using SQLite to SQLite, looks like indicated on the
   mailing list use ATTACH. Seems the easiest approach
   and fastest.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL to SQLite

[dmp]

> Message: 2
> Date: Tue, 19 Jul 2016 15:01:55 -0700
> From: 
> To: 
> Subject: [sqlite] SQL to SQLite
> Message-ID: <004d01d1e209$2a356360$7ea02a20$@spotlightmusic.com>
> Content-Type: text/plain; charset="us-ascii"
>
> What's the best way to Insert or Update records from a connected SQL
> database to the connected SQLite database?
>
> I don't mean just once, but to do every so often.
> Thanks.

> Thanks for your response.

> Programming language is definitely the way I want to go, and in fact I
> programmed a workaround, but I assume I took the long way around and that
> there's a more standard way to do it.

Hello,

   If the source database is different, or SQLite, than the destination
database, SQLite, can be done and I have been working on a bridge in Java
that will perform the transfer. It is functional, but needs further work
and testing.

Essentially:

Create an ArrayBlockingQueue, start a LoadThread and a InsertPrepareThread.
Define the ArrayBlockingQueue as objects of a relation row element. Have
the load thread fill the blocking queue then the insert prepare thread
consume the table row elements from the queue.

A single SQL query can be used to define the SQLite table then fill it
with the source database data. A type definitions conversion needs to
take place to correctly transfer db --> db data types.

I have defined these type info conversions for various database and
it is available as part of my MyJSQLView project. At this time the
db --> db code is a plugin for MyJSQLView and is not been released to
the public.

https://github.com/danap/myjsqlview/blob/master/src/com/dandymadeproductions/myjsqlview/datasource/TypesInfoCache.java

Dana Proctor
MyJSQLView Project Manager

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why is empty string not equal to empty blob?

[dmp]

 > You explicitly put strings into both columns.  The two values are the same
 > even though you declared the columns differently.  As I told you before,
 > if you want to see what type something is, use typeof(thing).
 >
 > ~
 > ~
 > ~
 > ~
 >
 > Simon.

Thank you guys. I follow this list so I may keep informed to the
happenins with SQLite for my project.

I have been having an issue on how to handle a defined DATE column.
Be it NUMBER, or TEXT with the sqlite-jdbc. If its value is TEXT
and I try to retrieve with getDate() a parsing error will occur
if its format is not correct, but if I handle it with getString()
no problem, don't care.

These responses helped to clarify what is going on and how to deal
with mixed type column content.

typeof(thing). :)

danap.

[sqlite] GROUP BY With ASC/DESC

[dmp]

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.23 Released

[dmp]

MyJSQLView Version 3.23 Released

The MyJSQLView project is pleased to provide the release of Version
3.23 to the public. The release marks the addition of support for
the SQLite database. New to this release of MyJSQLView is also the
support for in memory databases that is featured for both the SQLite
and HSQL databases. What this means is data sets can now be imported
into a table and MyJSQLView should be able to perform quicker searches
and sorts of the in memory data. The group has also abandoned the
practice of not including some of the open source plugins for the
application that it produces. The last release of the application
did not include the TableFieldProfiler plugin with MyJSQLView and
as a result the downloads of that plugin amounted to less then
5% of the total MyJSQLView downloads. The group thought users were
really missing out on a valuable tool which had been expanded and
included internationalization. So the TableFieldProfiler is now
included with the standard MyJSQLView download.

This release of MyJSQLView has also enhanced the plugin architecture
by letting users manually load plugins from alternative directories
than the default lib/plugins directory. You will find access to
the Plugin Management Tool in the top main tab menu for the application.
Speaking of plugins, developers will now find the Plugin Basics Tutorial
has been updated and an advanced one has also been created under
the documentation of the web site.

Since this is a release that supports a new database make sure and
check out the additional entries that have been given for the connection
parameters in the example reference myjsqlview.xml file. We would
also like to let you know that a new database is available at the
web site for the US Congress. The data set is of the legislative
branches' representatives, courtesy of Sunlight Labs.

Dana M. Proctor
MyJSQLView Project Manager
http://dandymadeproductions.com/projects/MyJSQLView/

MyJSQLView provides an easy to use Java based user interface
frontend for viewing, adding, editing, or deleting entries in
the the 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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users