Re: [sqlite] quetion on editing a sqlite database
john white [via SQLite] wrote: I have a bit of a problem. I am moving a program (sickbeard) from windows to ubuntu. The program has an sqlite database. It lists a variety of things with their physical location on disk. The problem is that ubuntu (linux) location information is slightly different between windows and ubuntu so I must change those locations. Oh, there is a location column with this information. I can do them, one at a time, but that is going to take a LOT of time. I was hoping there would be a way to select them all and then simply do a find and replace thing. An example would be: C:\TV\ which should now be '/TV/. Even better would be to change all the \ to / and then just delete the leading C:. Thank you... If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/quetion-on-editing-a-sqlite-database-tp71297.html To unsubscribe from SQLite, click here http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=2code=bG1oX3VzZXJzLWdyb3Vwc0Btb2xjb25uLmNvbXwyfC02ODY4MzM2NjI=. NAML http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml You might try to export the database file to something like csv. One you have it in a text file format, you could do things like find and replace in a text editor or excel. Once you have things sorted, you could re-import the csv into SQLite. The Firefox SQLite manager lets you export a table from a database and also to import one, so that might work for you. LMH -- View this message in context: http://sqlite.1065341.n5.nabble.com/quetion-on-editing-a-sqlite-database-tp71297p71298.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] inserting record datinto mutliple tables with a composite primary key
I have more or less completed this project, so I thought I would post the
final script. The script is in ruby and accepts the name of an input file
and the name of the database,
ruby loader.rb inputFilename databaseName
The input file uses metadata rows to identify the database table that the
column will go to, the data type, and the column name. The unique fields
iK1, iK2, iK3, iK4 are how the data for the primary table is registered for
each input record. If a record is inserted and the database already contains
a record with the same value for these 4 ints, there will be an exception.
The script will create the database if it doesn't exist, but can also be
used to insert records to an existing database. The structure table is
required with all 9 fields not null. The other satellite tables are
optional. Additional tables can be added by adding new columns with the
table name in the first row, the data type for the column in the second row,
and the column name in the third row. All fields in the satellite tables are
not null by default.
The script is below, but I have also attached it with a sample input file.
sample_files_sqlite_ruby_loader.zip
http://sqlite.1065341.n5.nabble.com/file/n69543/sample_files_sqlite_ruby_loader.zip
Thanks for the assistance. I am now working on a script to add/change data
in the existing records in a database, as well as a simple query script to
export data to a delimited text file.
*LMHmedchem*
# script loader.rb
require 'rubygems'
require 'sqlite3'
require 'logger'
class DataLoader
attr_accessor :db_name, :current_row, :table_names, :field_names,
:data_types, :line_number, :db, :ready, :structure_id
UNIQUE_FIELDS = %w(iK1 iK2 iK3 iK4)
def initialize(db_path)
@db_name = db_path
@table_names = []
@field_names = {}
@data_types = {}
@ready = false
@log = Logger.new('sqlite_data_loader.log', 'weekly')
db_connect
end
def db_connect
@db = SQLite3::Database.open @db_name
# Enabling foreign_keys features
@db.execute('PRAGMA foreign_keys = ON')
@ready = true
end
def load_data(tsv_file)
@line_number = 1
File.open(tsv_file, 'r') do |f1|
while line = f1.gets
@current_row = line.strip.split(\t)
next if @current_row.empty?
if @line_number = 3
collect_table_info
# After processing third line, we can create tables with the
information collected.
create_tables if @line_number == 3
else
collect_table_data
end
@line_number += 1
end
end
end
def collect_table_info
if @line_number == 1
@current_row.uniq.each do |name|
@table_names [name, @current_row.count(name)]
end
else
offset = 0
@table_names.each do |table_name, fields_count|
@data_types[table_name] = @current_row[offset..(offset +
fields_count - 1)] if @line_number == 2
@field_names[table_name] = @current_row[offset..(offset +
fields_count - 1)] if @line_number == 3
offset += fields_count
end
end
end
def create_tables
@table_names.each do |table_name, arity|
create_table table_name
end
end
def create_table(table_name)
query = CREATE TABLE IF NOT EXISTS #{table_name}(#{fields_for
table_name})
@log.debug query
@db.execute query
unless 'structure' == table_name
@log.debug Creating index on #{table_name}
@db.execute CREATE INDEX IF NOT EXISTS idx_#{table_name}_structure_id
ON #{table_name}(structure_id)
end
rescue SQLite3::Exception = e
@log.debug Exception occured #{e.class}: #{e.message}
end
def fields_for(table_name)
list = []
if 'structure' == table_name
list 'id INTEGER PRIMARY KEY AUTOINCREMENT'
else
list 'structure_id INTEGER NOT NULL'
end
fields = []
@field_names[table_name].each_with_index do |field, index|
next if 'primary_key' == field
next if fields.member?(field)
list '#{field}' #{field_data_type(table_name, index)}
fields field
end
list add_unique_fields(table_name)
list add_foreign_key_constraint(table_name)
list.compact.join(', ')
end
def field_data_type(table_name, index)
kind = @data_types[table_name][index].upcase
kind = 'TEXT' if kind.start_with?('STR')
#{kind}#{' NOT NULL' if 'structure' == table_name}
end
def add_unique_fields(table_name)
available_fields = (@field_names[table_name] UNIQUE_FIELDS)
unless available_fields.empty?
UNIQUE(#{available_fields.join(', ')}) ON CONFLICT ROLLBACK
end
end
def add_foreign_key_constraint(table_name)
unless 'structure' == table_name
'FOREIGN KEY(structure_id) REFERENCES Structure(id)'
end
end
def insert_into_fields(table_name, values)
field_names = @field_names[table_name]
values_list, attr_names = [], []
if 'structure' == table_name
attr_names 'id'
values_list nil
elsif
Re: [sqlite] JDBC Drivers for SQLite?
I have sqlite-jdbc-3.7.2.jar that I use as the sqlite driver for squirrel-sql. It is 3.1MB, so I don't think I can send it as an email attachment. You may want to search for that file name. If you can't find it, let me know and I will put is somewhere on the net for you. LMH Tilsley, Jerry M. [via SQLite] wrote: All, Might be a silly question, but does anyone know if any JDBC drivers exist for SQLite? Thanks, Jerry Disclaimer This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of St. Claire Regional Medical Center. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of the email is strictly prohibited. If you received this email in error please notify the St. Claire Regional Helpdesk by telephone at 606-783-6565. ___ sqlite-users mailing list [hidden email] /user/SendEmail.jtp?type=nodenode=66105i=0 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/JDBC-Drivers-for-SQLite-tp66105.html To unsubscribe from SQLite, click here http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=2code=bG1oX3VzZXJzLWdyb3Vwc0Btb2xjb25uLmNvbXwyfC02ODY4MzM2NjI=. NAML http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml -- View this message in context: http://sqlite.1065341.n5.nabble.com/JDBC-Drivers-for-SQLite-tp66105p66109.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] inserting record datinto mutliple tables with a composite primary key
This looks like a more or less complete solution for creating the tables and doing inserts. Primary table: CREATE TABLE Structure( 'id' INTEGER PRIMARY KEY, 'name' TEXT NOT NULL, 'filePath' TEXT NOT NULL, 'iH1' INTEGER NOT NULL, 'iH2' INTEGER NOT NULL, 'iH3' INTEGER NOT NULL, 'iH4' INTEGER NOT NULL, 'formula' TEXT NOT NULL, 'fw' FLOAT NOT NULL, UNIQUE(iH1, iH2, iH3, iH4) ON CONFLICT FAIL ) Satellite table: CREATE TABLE Project1( 'Structure_id' INTEGER NOT NULL, 'class' STRING, 'status' STRING, 'RI17-1' FLOAT, FOREIGN KEY(Strucutre_id) REFERENCES Structure(id) ) There are three cases for doing inserts of the data for phosphoserine, which is distributed over both tables. record phosphoserine, data for Structure table: name = phosphoserine filePath = phosphoserine.mol iH1 = 185073 iH2 = 856147 iH3 = 73543 iH4 = 25338 formula= C3H8NO6P fw = 185.073 record phosphoserine, data for Project1 table: class = C0248 status = M RI17-1 = 15.0 these statements include some ruby pseudocode 1. Insert data to Structure table only, do not insert Project1 data @db.execute INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw) VALUES(null,'phosphoserine', 'phosphoserine.mol', 185073, 856147, 73543, 25338, 'C3H8NO6P', 185.073) 2. Sequentially insert data to Structure and then Project1 @db.execute INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw) VALUES(null,'phosphoserine', 'phosphoserine.mol', 185073, 856147, 73543, 25338, 'C3H8NO6P', 185.073) # capture the row number where phosphoserine was inserted to Structure @Structure_id = @db.last_insert_row_id # use the value of Structure_id to link phosphoserine data in Project1 to phosphoserine data in Structure # the implicit rowid will be the primary key for Project1 and so is not handled explicitly @db.execute INSERT INTO Project1(Structure_id, class, status, RI17-1) VALUES(Structure_id, C0248, M, 15.0) 3. Insert phosphoserine data to Project1 at some later time, meaning in a situation where there is already a record in Structure for phosphoserine # lookup the row number in Structure where the phosphoserine record is stored using the 4 int key values @Structure_id = @db.execute SELECT id FROM Structure WHERE iH1 = 185073 AND iH2 = 856147 AND iH3 = 73543 AND iH4 = 25338; # use the value of Structure_id to link phosphoserine data in Project1 to phosphoserine data in Structure # the implicit rowid will be the primary key for Project1 and so is not handled explicitly @db.execute INSERT INTO Project1(Structure_id, class, status, RI17-1) VALUES(Structure_id, C0248, M, 15.0) Excepting that the ruby is probably not quite right here and that the SQL instructions are using literals and not parameters, does this look like a reasonable setup? The Structure table will have more records than the satellite tables and data will be added to the satellite tables more frequently. I think this allows for addition of new tables of data related to previously stored structures (Project2, Project3, etc) as needed, and also allows for a quick method of looking up structures that might have been registered under a different name. Is ON CONFLICT FAIL what I want for the Structure table, or would ABORT/ROLLBACK make more sense? *LMHmedchem* -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p65567.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] inserting record datinto mutliple tables with a composite primary key
. Using these int keys will allow me to just process the structure file with the software the generates the key values and try an insert. If a record for the compound exists, I will know immediately, even if the compound was entered under a different name, or the structure was drawn differently. I would be happy to explain more about this if you are interested, but hopefully the above is enough to give a general idea. Tomorrow I will post a more complete version of the database definitions and insert syntax. *LMHmedchem* -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p65549.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] inserting record datinto mutliple tables with a composite primary key
Thanks for the clarification. So my proper syntax for inserts with
AUTOINCREMENT is one of,
INSERT INTO Structure
VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);
or
INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw)
VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);
or
INSERT INTO Structure(name, filePath, iH1, iH2, iH3, iH4, formula, fw)
VALUES('phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);
As it happens, some of the text strings that will be added to the database
have single quotes,
N,N'-dimethylethylenediamine
do I need to do anything different for these, such as to escape the single
single-quote in some way? There will never be double quotes, but there could
be any number of single quotes.
N,N',N''-trimethylbis(hexamethylene)triamine
These single-quotes will never appear as the first or last character, so
possibly nothing needs to be done?
*LMHmedchem*
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p65524.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] inserting record datinto mutliple tables with a composite primary key
Hello, I have made some progress. This is what my Structure table looks like now, CREATE TABLE Structure( 'id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' TEXT NOT NULL, 'filePath' TEXT NOT NULL, 'iH1' INTEGER NOT NULL, 'iH2' INTEGER NOT NULL, 'iH3' INTEGER NOT NULL, 'iH4' INTEGER NOT NULL, 'formula' TEXT NOT NULL, 'fw' FLOAT NOT NULL, UNIQUE(iH1, iH2, iH3, iH4) ON CONFLICT FAIL ) This is the main table and I am linking all other tables using the id value from Structure (Structure_id), I have a question about the syntax for inserts. If I have the following data to be inserted to Structure, Structure Data for phosphoserine: name = phosphoserine filePath = phosphoserine.mol iH1 = 185073 iH2 = 856147 iH3 = 73543 iH4 = 25338 formula= C3H8NO6P fw = 185.073 It looks like my insert syntax should be, INSERT INTO Structure VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073); where using null for id invokes AUTOINCREMENT. Is this the right syntax for sqlite? I have also seen versions of insert where the Table fields are defined and look like, INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw) VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073); Would I still use null here for the id, or do I not have this right? Is there some reason for preferring one version over the other if both are valid? Satellite table would look like the following where Structure_id is the common field linking data in Structure and Project1. CREATE TABLE Project1( id INTEGER PRIMARY KEY AUTOINCREMENT, 'Structure_id' INTEGER NOT NULL, 'class' STRING, 'status' STRING, 'RI17-1' FLOAT, FOREIGN KEY(Strucutre_id) REFERENCES Structure(id) ) I will post a bit about how to do the inserts to satellite tables when I have the inserts for the structure table setup up correctly. If Simon is reading this, I have not ignored your comment about having 500 columns in a table being an issue, I just am trying to get the basic syntax working first. None of those fields will ever have a null value. There are many zero's but those are not nulls and have meaning, like a family having 0 children. *LMHmedchem* -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p65496.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] inserting record datinto mutliple tables with a composite primary key
Hello Igor, thank you for the information, it is a big help. If you have a one-to-one relationship between two tables, is there a reason why you don't simply combine the two into a single, wider table? They way I think about a database is that you subdivide the data based on how you may want to retrieve it later. The main table is structure, and all of it's fields are mandatory not null (each record is a chemical structure). The other tables contain other data (about the chemical structure), such as available vendors, prices, experimentally measured values, and computer generated data. These fields may be null. Some of the other tables are fairly large (500-2500 cols), so I thought it would help make the query process more efficient if you could just search on the tables with the data you need and ignore others. If I am incorrect in thinking about the setup in this way, I would appreciate knowing about that. but the notion of having multiple primary keys doesn't seem quite right. Why is that? Basically, every table needs a primary key, whether composite or otherwise. If this tuple of integers is the natural key for your data, I don't see a problem. I guess what I was thinking was that tables should not have independent primary keys if there is a 1:1 relationship in the data between the tables. The way I was thinking about this is that the primary key value assigned to a record when it was inserted to the first table would be copied and used to insert into the second table, etc. In my spreadsheet way of thinking, that is having a single primary key that is used in multiple tables. I'm trying to learn to think database and not spreadsheet. There is - see http://sqlite.org/autoinc.html . Change your table to create table Structure ( id integer primary key, i1 integer not null, i2 integer not null, i3 integer not null, i4 integer not null, ... unique (i1, i2, i3, i4) ); Now, you can insert a record while leaving 'id' column out, and it will be automatically assigned a unique integer value, which you can retrieve with sqlite3_last_insert_rowid. You can then use that ID when inserting records into your satellite tables. Later on, when I need to lookup data from a record using the 4 key values, there would have to be a way to retrieve the unique integer value ROWID that corresponds to the 4 keys (was assigned by AUTOINCREMENT). If I use unique like above, how would the lookup on the 4 keys work? LMHmedchem -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p64902.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] subscribe to mailing list
I'm not having much luck with this. When I try to go to, http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users I get a failed to connect, The connection was refused when attempting to contact sqlite.org:8080. I tried in both seamonkey and ie and get the same message. LMHmedchem -- View this message in context: http://sqlite.1065341.n5.nabble.com/subscribe-to-mailing-list-tp64888p64903.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] inserting record datinto mutliple tables with a composite primary key
Hello, I have data that I am loading into a sqlite database from a text file. I am using a composite primary key for four ints for the main table. create table Structure ( i1 integer not null, i2 integer not null, i3 integer not null, i4 integer not null, name string not null, filePath string not null, SMILES string not null, formula string not null, fw float not null, primary key (i1, i2, i3, i4) ) For this table, there should be a reasonable syntax to insert and select using the composite primary key values to find things. My understanding is that the composite key values will be hashed, leading to a fast look up. I am not entirely sure of the syntax, but I believe that this is a sound structure. There is more data for each record that will go into other tables. The question I have is how to best keep the data in the different tables in registration, meaning to make sure that I can retrieve all of the data from the record from all tables using the same 4 primary key values. I could create the same primary key in every table, create table Identifier( i1 integer not null, i2 integer not null, i3 integer not null, i4 integer not null, CSpider string, KEGG string, CAS string, primary key (i1, i2, i3, i4) ) but the notion of having multiple primary keys doesn't seem quite right. It also seems as if there should be a way to record the rowid of where a record went in the first table and I should be able to use that to insert data from the same record to the same rowid of other tables. That assumes that there would be a way to look up the rowid associated with a set of 4 key values and use that to retrieve data from any table where that rowid was used to insert data. Am I going about this in the best way, or even in a reasonable way? Suggestions and criticisms would be appreciated and a link to some examples or a tutorial would be fantastic. LMHmedchem -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-datinto-mutliple-tables-with-a-composite-primary-key-tp64874.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
