Re: [sqlite] Regarding CoC
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?
> 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?]
>> 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
> 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
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
> 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
>> 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
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
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?
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
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
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
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
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
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)
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.
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
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
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
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
> 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...
> 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
> 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
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
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
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
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
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
> 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
> 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
> 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
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
> 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?
> 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
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
> 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
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
> 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
> 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.
> 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
> 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
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
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
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
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
> 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
> 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?
> 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
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
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
